Repository: incubator-guacamole-client Updated Branches: refs/heads/master 4e0929c66 -> 16b0c6441
GUACAMOLE-5: Add sharing profiles to database schema. Project: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/commit/21f5aba5 Tree: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/tree/21f5aba5 Diff: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/diff/21f5aba5 Branch: refs/heads/master Commit: 21f5aba548e9162c2b461743c151be4392effdb9 Parents: 4e0929c Author: Michael Jumper <[email protected]> Authored: Tue Jul 19 11:21:45 2016 -0700 Committer: Michael Jumper <[email protected]> Committed: Tue Jul 19 11:21:45 2016 -0700 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 99 ++++++++++++++++-- .../schema/002-create-admin-user.sql | 1 + .../schema/upgrade/upgrade-pre-0.9.10.sql | 88 ++++++++++++++++ .../schema/001-create-schema.sql | 103 +++++++++++++++++-- .../schema/002-create-admin-user.sql | 1 + .../schema/upgrade/upgrade-pre-0.9.10.sql | 93 +++++++++++++++++ 6 files changed, 366 insertions(+), 19 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/21f5aba5/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql index 4201638..47dfe3b 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql @@ -107,6 +107,30 @@ CREATE TABLE `guacamole_user` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table of sharing profiles. Each sharing profile has a name, associated set +-- of parameters, and a primary connection. The primary connection is the +-- connection that the sharing profile shares, and the parameters dictate the +-- restrictions/features which apply to the user joining the connection via the +-- sharing profile. +-- + +CREATE TABLE guacamole_sharing_profile ( + + `sharing_profile_id` int(11) NOT NULL AUTO_INCREMENT, + `sharing_profile_name` varchar(128) NOT NULL, + `primary_connection_id` int(11) NOT NULL, + + PRIMARY KEY (`sharing_profile_id`), + UNIQUE KEY `sharing_profile_name_primary` (sharing_profile_name, primary_connection_id), + + CONSTRAINT `guacamole_sharing_profile_ibfk_1` + FOREIGN KEY (`primary_connection_id`) + REFERENCES `guacamole_connection` (`connection_id`) + ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table of connection parameters. Each parameter is simply a name/value pair -- associated with a connection. -- @@ -126,6 +150,27 @@ CREATE TABLE `guacamole_connection_parameter` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table of sharing profile parameters. Each parameter is simply +-- name/value pair associated with a sharing profile. These parameters dictate +-- the restrictions/features which apply to the user joining the associated +-- connection via the sharing profile. +-- + +CREATE TABLE guacamole_sharing_profile_parameter ( + + `sharing_profile_id` integer NOT NULL, + `parameter_name` varchar(128) NOT NULL, + `parameter_value` varchar(4096) NOT NULL, + + PRIMARY KEY (`sharing_profile_id`, `parameter_name`), + + CONSTRAINT `guacamole_sharing_profile_parameter_ibfk_1` + FOREIGN KEY (`sharing_profile_id`) + REFERENCES `guacamole_sharing_profile` (`sharing_profile_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table of connection permissions. Each connection permission grants a user -- specific access to a connection. -- @@ -178,6 +223,32 @@ CREATE TABLE `guacamole_connection_group_permission` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table of sharing profile permissions. Each sharing profile permission grants +-- a user specific access to a sharing profile. +-- + +CREATE TABLE guacamole_sharing_profile_permission ( + + `user_id` integer NOT NULL, + `sharing_profile_id` integer NOT NULL, + `permission` enum('READ', + 'UPDATE', + 'DELETE', + 'ADMINISTER') NOT NULL, + + PRIMARY KEY (`user_id`, `sharing_profile_id`, `permission`), + + CONSTRAINT `guacamole_sharing_profile_permission_ibfk_1` + FOREIGN KEY (`sharing_profile_id`) + REFERENCES `guacamole_sharing_profile` (`sharing_profile_id`) ON DELETE CASCADE, + + CONSTRAINT `guacamole_sharing_profile_permission_ibfk_2` + FOREIGN KEY (`user_id`) + REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table of system permissions. Each system permission grants a user a -- system-level privilege of some kind. -- @@ -186,7 +257,8 @@ CREATE TABLE `guacamole_system_permission` ( `user_id` int(11) NOT NULL, `permission` enum('CREATE_CONNECTION', - 'CREATE_CONNECTION_GROUP', + 'CREATE_CONNECTION_GROUP', + 'CREATE_SHARING_PROFILE', 'CREATE_USER', 'ADMINISTER') NOT NULL, @@ -232,27 +304,34 @@ CREATE TABLE `guacamole_user_permission` ( CREATE TABLE `guacamole_connection_history` ( - `history_id` int(11) NOT NULL AUTO_INCREMENT, - `user_id` int(11) DEFAULT NULL, - `username` varchar(128) NOT NULL, - `connection_id` int(11) DEFAULT NULL, - `connection_name` varchar(128) NOT NULL, - `start_date` datetime NOT NULL, - `end_date` datetime DEFAULT NULL, + `history_id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) DEFAULT NULL, + `username` varchar(128) NOT NULL, + `connection_id` int(11) DEFAULT NULL, + `connection_name` varchar(128) NOT NULL, + `sharing_profile_id` int(11) DEFAULT NULL, + `sharing_profile_name` varchar(128) DEFAULT NULL, + `start_date` datetime NOT NULL, + `end_date` datetime DEFAULT NULL, PRIMARY KEY (`history_id`), KEY `user_id` (`user_id`), KEY `connection_id` (`connection_id`), + KEY `sharing_profile_id` (`sharing_profile_id`), KEY `start_date` (`start_date`), KEY `end_date` (`end_date`), CONSTRAINT `guacamole_connection_history_ibfk_1` FOREIGN KEY (`user_id`) - REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE, + REFERENCES `guacamole_user` (`user_id`) ON DELETE SET NULL, CONSTRAINT `guacamole_connection_history_ibfk_2` FOREIGN KEY (`connection_id`) - REFERENCES `guacamole_connection` (`connection_id`) ON DELETE CASCADE + REFERENCES `guacamole_connection` (`connection_id`) ON DELETE SET NULL + + CONSTRAINT `guacamole_connection_history_ibfk_3` + FOREIGN KEY (`sharing_profile_id`) + REFERENCES `guacamole_sharing_profile` (`sharing_profile_id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/21f5aba5/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql index a0710e2..cc73a01 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql @@ -29,6 +29,7 @@ SELECT user_id, permission FROM ( SELECT 'guacadmin' AS username, 'CREATE_CONNECTION' AS permission UNION SELECT 'guacadmin' AS username, 'CREATE_CONNECTION_GROUP' AS permission + UNION SELECT 'guacadmin' AS username, 'CREATE_SHARING_PROFILE' AS permission UNION SELECT 'guacadmin' AS username, 'CREATE_USER' AS permission UNION SELECT 'guacadmin' AS username, 'ADMINISTER' AS permission ) permissions http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/21f5aba5/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.10.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.10.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.10.sql index 13a57dd..4e60132 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.10.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.10.sql @@ -94,3 +94,91 @@ ALTER TABLE guacamole_connection_history ALTER TABLE guacamole_connection_group ADD COLUMN enable_session_affinity boolean NOT NULL DEFAULT 0; +-- +-- Add new system-level permission +-- + +ALTER TABLE `guacamole_system_permission` + MODIFY `permission` enum('CREATE_CONNECTION', + 'CREATE_CONNECTION_GROUP', + 'CREATE_SHARING_PROFILE', + 'CREATE_USER', + 'ADMINISTER') NOT NULL; + +-- +-- Add sharing profile table +-- + +CREATE TABLE guacamole_sharing_profile ( + + `sharing_profile_id` int(11) NOT NULL AUTO_INCREMENT, + `sharing_profile_name` varchar(128) NOT NULL, + `primary_connection_id` int(11) NOT NULL, + + PRIMARY KEY (`sharing_profile_id`), + UNIQUE KEY `sharing_profile_name_primary` (sharing_profile_name, primary_connection_id), + + CONSTRAINT `guacamole_sharing_profile_ibfk_1` + FOREIGN KEY (`primary_connection_id`) + REFERENCES `guacamole_connection` (`connection_id`) + ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Add table of sharing profile parameters +-- + +CREATE TABLE guacamole_sharing_profile_parameter ( + + `sharing_profile_id` integer NOT NULL, + `parameter_name` varchar(128) NOT NULL, + `parameter_value` varchar(4096) NOT NULL, + + PRIMARY KEY (`sharing_profile_id`, `parameter_name`), + + CONSTRAINT `guacamole_sharing_profile_parameter_ibfk_1` + FOREIGN KEY (`sharing_profile_id`) + REFERENCES `guacamole_sharing_profile` (`sharing_profile_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Object-level permission table for sharing profiles +-- + +CREATE TABLE guacamole_sharing_profile_permission ( + + `user_id` integer NOT NULL, + `sharing_profile_id` integer NOT NULL, + `permission` enum('READ', + 'UPDATE', + 'DELETE', + 'ADMINISTER') NOT NULL, + + PRIMARY KEY (`user_id`, `sharing_profile_id`, `permission`), + + CONSTRAINT `guacamole_sharing_profile_permission_ibfk_1` + FOREIGN KEY (`sharing_profile_id`) + REFERENCES `guacamole_sharing_profile` (`sharing_profile_id`) ON DELETE CASCADE, + + CONSTRAINT `guacamole_sharing_profile_permission_ibfk_2` + FOREIGN KEY (`user_id`) + REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Add new (optional) sharing profile ID and name columns to connection history +-- + +ALTER TABLE guacamole_connection_history + ADD COLUMN sharing_profile_id INT(11); + +ALTER TABLE guacamole_connection_history + ADD COLUMN sharing_profile_name VARCHAR(128); + +ALTER TABLE guacamole_connection_history + ADD CONSTRAINT guacamole_connection_history_ibfk_3 + FOREIGN KEY (sharing_profile_id) + REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE SET NULL; http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/21f5aba5/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql index 3a84ba5..2720e37 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql @@ -44,6 +44,7 @@ CREATE TYPE guacamole_object_permission_type AS ENUM( CREATE TYPE guacamole_system_permission_type AS ENUM( 'CREATE_CONNECTION', 'CREATE_CONNECTION_GROUP', + 'CREATE_SHARING_PROFILE', 'CREATE_USER', 'ADMINISTER' ); @@ -149,6 +150,34 @@ CREATE TABLE guacamole_user ( ); -- +-- Table of sharing profiles. Each sharing profile has a name, associated set +-- of parameters, and a primary connection. The primary connection is the +-- connection that the sharing profile shares, and the parameters dictate the +-- restrictions/features which apply to the user joining the connection via the +-- sharing profile. +-- + +CREATE TABLE guacamole_sharing_profile ( + + sharing_profile_id serial NOT NULL, + sharing_profile_name varchar(128) NOT NULL, + primary_connection_id integer NOT NULL, + + PRIMARY KEY (sharing_profile_id), + + CONSTRAINT sharing_profile_name_primary + UNIQUE (sharing_profile_name, primary_connection_id), + + CONSTRAINT guacamole_sharing_profile_ibfk_1 + FOREIGN KEY (primary_connection_id) + REFERENCES guacamole_connection (connection_id) + ON DELETE CASCADE + +); + +CREATE INDEX ON guacamole_sharing_profile(primary_connection_id); + +-- -- Table of connection parameters. Each parameter is simply a name/value pair -- associated with a connection. -- @@ -170,6 +199,29 @@ CREATE TABLE guacamole_connection_parameter ( CREATE INDEX ON guacamole_connection_parameter(connection_id); -- +-- Table of sharing profile parameters. Each parameter is simply +-- name/value pair associated with a sharing profile. These parameters dictate +-- the restrictions/features which apply to the user joining the associated +-- connection via the sharing profile. +-- + +CREATE TABLE guacamole_sharing_profile_parameter ( + + sharing_profile_id integer NOT NULL, + parameter_name varchar(128) NOT NULL, + parameter_value varchar(4096) NOT NULL, + + PRIMARY KEY (sharing_profile_id, parameter_name), + + CONSTRAINT guacamole_sharing_profile_parameter_ibfk_1 + FOREIGN KEY (sharing_profile_id) + REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE CASCADE + +); + +CREATE INDEX ON guacamole_sharing_profile_parameter(sharing_profile_id); + +-- -- Table of connection permissions. Each connection permission grants a user -- specific access to a connection. -- @@ -222,6 +274,32 @@ CREATE INDEX ON guacamole_connection_group_permission(connection_group_id); CREATE INDEX ON guacamole_connection_group_permission(user_id); -- +-- Table of sharing profile permissions. Each sharing profile permission grants +-- a user specific access to a sharing profile. +-- + +CREATE TABLE guacamole_sharing_profile_permission ( + + user_id integer NOT NULL, + sharing_profile_id integer NOT NULL, + permission guacamole_object_permission_type NOT NULL, + + PRIMARY KEY (user_id,sharing_profile_id,permission), + + CONSTRAINT guacamole_sharing_profile_permission_ibfk_1 + FOREIGN KEY (sharing_profile_id) + REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE CASCADE, + + CONSTRAINT guacamole_sharing_profile_permission_ibfk_2 + FOREIGN KEY (user_id) + REFERENCES guacamole_user (user_id) ON DELETE CASCADE + +); + +CREATE INDEX ON guacamole_sharing_profile_permission(sharing_profile_id); +CREATE INDEX ON guacamole_sharing_profile_permission(user_id); + +-- -- Table of system permissions. Each system permission grants a user a -- system-level privilege of some kind. -- @@ -275,27 +353,34 @@ CREATE INDEX ON guacamole_user_permission(user_id); CREATE TABLE guacamole_connection_history ( - history_id serial NOT NULL, - user_id integer DEFAULT NULL, - username varchar(128) NOT NULL, - connection_id integer DEFAULT NULL, - connection_name varchar(128) NOT NULL, - start_date timestamptz NOT NULL, - end_date timestamptz DEFAULT NULL, + history_id serial NOT NULL, + user_id integer DEFAULT NULL, + username varchar(128) NOT NULL, + connection_id integer DEFAULT NULL, + connection_name varchar(128) NOT NULL, + sharing_profile_id integer DEFAULT NULL, + sharing_profile_name varchar(128) NOT NULL, + start_date timestamptz NOT NULL, + end_date timestamptz DEFAULT NULL, PRIMARY KEY (history_id), CONSTRAINT guacamole_connection_history_ibfk_1 FOREIGN KEY (user_id) - REFERENCES guacamole_user (user_id) ON DELETE CASCADE, + REFERENCES guacamole_user (user_id) ON DELETE SET NULL, CONSTRAINT guacamole_connection_history_ibfk_2 FOREIGN KEY (connection_id) - REFERENCES guacamole_connection (connection_id) ON DELETE CASCADE + REFERENCES guacamole_connection (connection_id) ON DELETE SET NULL, + + CONSTRAINT guacamole_connection_history_ibfk_3 + FOREIGN KEY (sharing_profile_id) + REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE SET NULL ); CREATE INDEX ON guacamole_connection_history(user_id); CREATE INDEX ON guacamole_connection_history(connection_id); +CREATE INDEX ON guacamole_connection_history(sharing_profile_id); CREATE INDEX ON guacamole_connection_history(start_date); CREATE INDEX ON guacamole_connection_history(end_date); http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/21f5aba5/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql index 9163ea8..feeb9ad 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql @@ -31,6 +31,7 @@ FROM ( VALUES ('guacadmin', 'CREATE_CONNECTION'), ('guacadmin', 'CREATE_CONNECTION_GROUP'), + ('guacadmin', 'CREATE_SHARING_PROFILE'), ('guacadmin', 'CREATE_USER'), ('guacadmin', 'ADMINISTER') ) permissions (username, permission) http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/21f5aba5/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql index baaa4ff..60ffdca 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql @@ -95,3 +95,96 @@ ALTER TABLE guacamole_connection_history ALTER TABLE guacamole_connection_group ADD COLUMN enable_session_affinity boolean NOT NULL DEFAULT FALSE; +-- +-- Add new system-level permission +-- + +ALTER TYPE guacamole_system_permission_type + ADD VALUE 'CREATE_SHARING_PROFILE' + AFTER 'CREATE_CONNECTION_GROUP'; + +-- +-- Add sharing profile table +-- + +CREATE TABLE guacamole_sharing_profile ( + + sharing_profile_id serial NOT NULL, + sharing_profile_name varchar(128) NOT NULL, + primary_connection_id integer NOT NULL, + + PRIMARY KEY (sharing_profile_id), + + CONSTRAINT sharing_profile_name_primary + UNIQUE (sharing_profile_name, primary_connection_id), + + CONSTRAINT guacamole_sharing_profile_ibfk_1 + FOREIGN KEY (primary_connection_id) + REFERENCES guacamole_connection (connection_id) + ON DELETE CASCADE + +); + +CREATE INDEX ON guacamole_sharing_profile(primary_connection_id); + +-- +-- Add table of sharing profile parameters +-- + +CREATE TABLE guacamole_sharing_profile_parameter ( + + sharing_profile_id integer NOT NULL, + parameter_name varchar(128) NOT NULL, + parameter_value varchar(4096) NOT NULL, + + PRIMARY KEY (sharing_profile_id, parameter_name), + + CONSTRAINT guacamole_sharing_profile_parameter_ibfk_1 + FOREIGN KEY (sharing_profile_id) + REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE CASCADE + +); + +CREATE INDEX ON guacamole_sharing_profile_parameter(sharing_profile_id); + +-- +-- Object-level permission table for sharing profiles +-- + +CREATE TABLE guacamole_sharing_profile_permission ( + + user_id integer NOT NULL, + sharing_profile_id integer NOT NULL, + permission guacamole_object_permission_type NOT NULL, + + PRIMARY KEY (user_id,sharing_profile_id,permission), + + CONSTRAINT guacamole_sharing_profile_permission_ibfk_1 + FOREIGN KEY (sharing_profile_id) + REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE CASCADE, + + CONSTRAINT guacamole_sharing_profile_permission_ibfk_2 + FOREIGN KEY (user_id) + REFERENCES guacamole_user (user_id) ON DELETE CASCADE + +); + +CREATE INDEX ON guacamole_sharing_profile_permission(sharing_profile_id); +CREATE INDEX ON guacamole_sharing_profile_permission(user_id); + +-- +-- Add new (optional) sharing profile ID and name columns to connection history +-- + +ALTER TABLE guacamole_connection_history + ADD COLUMN sharing_profile_id integer; + +ALTER TABLE guacamole_connection_history + ADD COLUMN sharing_profile_name varchar(128); + +ALTER TABLE guacamole_connection_history + ADD CONSTRAINT guacamole_connection_history_ibfk_3 + FOREIGN KEY (sharing_profile_id) + REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE SET NULL; + +CREATE INDEX ON guacamole_connection_history(sharing_profile_id);
