Github user necouchman commented on a diff in the pull request:
https://github.com/apache/guacamole-client/pull/319#discussion_r220396450
--- Diff:
extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-1.0.0.sql
---
@@ -17,6 +17,319 @@
-- under the License.
--
+--
+-- Add new system-level permission
+--
+
+ALTER TABLE `guacamole_system_permission`
+ MODIFY `permission` enum('CREATE_CONNECTION',
+ 'CREATE_CONNECTION_GROUP',
+ 'CREATE_SHARING_PROFILE',
+ 'CREATE_USER',
+ 'CREATE_USER_GROUP',
+ 'ADMINISTER') NOT NULL;
+
+--
+-- Table of base entities which may each be either a user or user group.
Other
+-- tables which represent qualities shared by both users and groups will
point
+-- to guacamole_entity, while tables which represent qualities specific to
+-- users or groups will point to guacamole_user or guacamole_user_group.
+--
+
+CREATE TABLE `guacamole_entity` (
+
+ `entity_id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(128) NOT NULL,
+ `type` enum('USER',
+ 'USER_GROUP') NOT NULL,
+
+ PRIMARY KEY (`entity_id`),
+ UNIQUE KEY `guacamole_entity_name_scope` (`type`, `name`)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table of user groups. Each user group may have an arbitrary set of
member
+-- users and member groups, with those members inheriting the permissions
+-- granted to that group.
+--
+
+CREATE TABLE `guacamole_user_group` (
+
+ `user_group_id` int(11) NOT NULL AUTO_INCREMENT,
+ `entity_id` int(11) NOT NULL,
+
+ -- Group disabled status
+ `disabled` boolean NOT NULL DEFAULT 0,
+
+ PRIMARY KEY (`user_group_id`),
+
+ UNIQUE KEY `guacamole_user_group_single_entity` (`entity_id`),
+
+ CONSTRAINT `guacamole_user_group_entity`
+ FOREIGN KEY (`entity_id`)
+ REFERENCES `guacamole_entity` (`entity_id`)
+ ON DELETE CASCADE
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table of users which are members of given user groups.
+--
+
+CREATE TABLE `guacamole_user_group_member` (
+
+ `user_group_id` int(11) NOT NULL,
+ `member_entity_id` int(11) NOT NULL,
+
+ PRIMARY KEY (`user_group_id`, `member_entity_id`),
+
+ -- Parent must be a user group
+ CONSTRAINT `guacamole_user_group_member_parent_id`
+ FOREIGN KEY (`user_group_id`)
+ REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE,
+
+ -- Member may be either a user or a user group (any entity)
+ CONSTRAINT `guacamole_user_group_member_entity_id`
+ FOREIGN KEY (`member_entity_id`)
+ REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table of user group permissions. Each user group permission grants a
user
+-- or user group access to a another user group (the "affected" user
group) for
+-- a specific type of operation.
+--
+
+CREATE TABLE `guacamole_user_group_permission` (
+
+ `entity_id` int(11) NOT NULL,
+ `affected_user_group_id` int(11) NOT NULL,
+ `permission` enum('READ',
+ 'UPDATE',
+ 'DELETE',
+ 'ADMINISTER') NOT NULL,
+
+ PRIMARY KEY (`entity_id`, `affected_user_group_id`, `permission`),
+
+ CONSTRAINT `guacamole_user_group_permission_affected_user_group`
+ FOREIGN KEY (`affected_user_group_id`)
+ REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE,
+
+ CONSTRAINT `guacamole_user_group_permission_entity`
+ FOREIGN KEY (`entity_id`)
+ REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Modify guacamole_user table to use guacamole_entity as a base
+--
+
+-- Add new entity_id column
+ALTER TABLE guacamole_user ADD COLUMN entity_id int(11);
+
+-- Create user entities for each guacamole_user entry
+INSERT INTO guacamole_entity (name, type)
+SELECT username, 'USER' FROM guacamole_user;
+
+-- Update guacamole_user to point to corresponding guacamole_entity
+UPDATE guacamole_user SET entity_id = (
+ SELECT entity_id FROM guacamole_entity
+ WHERE
+ username = guacamole_entity.name
+ AND type = 'USER'
+);
+
+-- The entity_id column should now be safely non-NULL
+ALTER TABLE guacamole_user MODIFY entity_id int(11) NOT NULL;
+
+-- The entity_id column should now safely point to guacamole_entity entries
--- End diff --
Maybe this should say "The entity_id column should now safely be unique" ?
Looks like this comment duplicates the one for the block just below.
---
