GUACAMOLE-363: Remove explicit schema, relying on account default 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/73301901 Tree: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/tree/73301901 Diff: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/diff/73301901 Branch: refs/heads/master Commit: 73301901ec8b2a856f9c8066fd759db41216d66c Parents: b72dba6 Author: Nick Couchman <[email protected]> Authored: Thu Sep 7 22:30:01 2017 -0400 Committer: Nick Couchman <[email protected]> Committed: Thu Sep 28 07:00:52 2017 -0400 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 170 +++++++++---------- .../schema/002-create-admin-user.sql | 14 +- .../auth/jdbc/connection/ConnectionMapper.xml | 38 ++--- .../connection/ConnectionParameterMapper.xml | 6 +- .../jdbc/connection/ConnectionRecordMapper.xml | 110 ++++++------ .../connectiongroup/ConnectionGroupMapper.xml | 46 ++--- .../ConnectionGroupPermissionMapper.xml | 30 ++-- .../permission/ConnectionPermissionMapper.xml | 30 ++-- .../SharingProfilePermissionMapper.xml | 30 ++-- .../jdbc/permission/SystemPermissionMapper.xml | 26 +-- .../jdbc/permission/UserPermissionMapper.xml | 52 +++--- .../sharingprofile/SharingProfileMapper.xml | 24 +-- .../SharingProfileParameterMapper.xml | 6 +- .../auth/jdbc/user/PasswordRecordMapper.xml | 22 +-- .../guacamole/auth/jdbc/user/UserMapper.xml | 26 +-- 15 files changed, 315 insertions(+), 315 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql index 80e47c1..1a1e324 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql @@ -20,7 +20,7 @@ /** * List for permission data type. */ -CREATE RULE [dbo].[guacamole_permission_list] +CREATE RULE [guacamole_permission_list] AS @list IN ('READ','UPDATE','DELETE','ADMINISTER'); GO; @@ -28,7 +28,7 @@ GO; /** * List for system permission data type. */ -CREATE RULE [dbo].[guacamole_system_permission_list] +CREATE RULE [guacamole_system_permission_list] AS @list IN ('CREATE_CONNECTION', 'CREATE_CONNECTION_GROUP', @@ -40,12 +40,12 @@ GO; /** * The permission data type. */ -CREATE TYPE [dbo].[guacamole_permission] FROM [nvarchar](10) NOT NULL; +CREATE TYPE [guacamole_permission] FROM [nvarchar](10) NOT NULL; /** * The system permission data type. */ -CREATE TYPE [dbo].[guacamole_system_permission] FROM [nvarchar](32) NOT NULL; +CREATE TYPE [guacamole_system_permission] FROM [nvarchar](32) NOT NULL; GO; /** @@ -53,7 +53,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_connection_group]( +CREATE TABLE [guacamole_connection_group]( [connection_group_id] [int] IDENTITY(1,1) NOT NULL, [parent_id] [int] NULL, [connection_group_name] [nvarchar](128) NOT NULL, @@ -75,23 +75,23 @@ CREATE TABLE [dbo].[guacamole_connection_group]( /** * Foreign keys for connection_group table. */ -ALTER TABLE [dbo].[guacamole_connection_group] +ALTER TABLE [guacamole_connection_group] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_connection_group_id] FOREIGN KEY([parent_id]) - REFERENCES [dbo].[guacamole_connection_group] ([connection_group_id]); -ALTER TABLE [dbo].[guacamole_connection_group] + REFERENCES [guacamole_connection_group] ([connection_group_id]); +ALTER TABLE [guacamole_connection_group] CHECK CONSTRAINT [FK_guacamole_connection_group_connection_group_id]; -ALTER TABLE [dbo].[guacamole_connection_group] +ALTER TABLE [guacamole_connection_group] WITH CHECK ADD CONSTRAINT [CK_guacamole_connection_group_type] CHECK (([type]='BALANCING' OR [type]='ORGANIZATIONAL')); -ALTER TABLE [dbo].[guacamole_connection_group] +ALTER TABLE [guacamole_connection_group] CHECK CONSTRAINT [CK_guacamole_connection_group_type]; /** * Default values for connection_group table. */ -ALTER TABLE [dbo].[guacamole_connection_group] +ALTER TABLE [guacamole_connection_group] ADD CONSTRAINT [DF_guacamole_connection_group_type] DEFAULT (N'ORGANIZATIONAL') FOR [type]; -ALTER TABLE [dbo].[guacamole_connection_group] +ALTER TABLE [guacamole_connection_group] ADD CONSTRAINT [DF_guacamole_connection_group_enable_session_affinity] DEFAULT ((0)) FOR [enable_session_affinity]; GO; @@ -100,7 +100,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_connection]( +CREATE TABLE [guacamole_connection]( [connection_id] [int] IDENTITY(1,1) NOT NULL, [connection_name] [nvarchar](128) NOT NULL, [parent_id] [int] NULL, @@ -123,17 +123,17 @@ CREATE TABLE [dbo].[guacamole_connection]( ON [PRIMARY] ) ON [PRIMARY]; -ALTER TABLE [dbo].[guacamole_connection] +ALTER TABLE [guacamole_connection] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_connection_group] FOREIGN KEY([parent_id]) - REFERENCES [dbo].[guacamole_connection_group] ([connection_group_id]); -ALTER TABLE [dbo].[guacamole_connection] + REFERENCES [guacamole_connection_group] ([connection_group_id]); +ALTER TABLE [guacamole_connection] CHECK CONSTRAINT [FK_guacamole_connection_connection_group]; -ALTER TABLE [dbo].[guacamole_connection] +ALTER TABLE [guacamole_connection] WITH CHECK ADD CONSTRAINT [CK_proxy_encryption_method] CHECK (([proxy_encryption_method]='SSL' OR [proxy_encryption_method]='NONE')); -ALTER TABLE [dbo].[guacamole_connection] +ALTER TABLE [guacamole_connection] CHECK CONSTRAINT [CK_proxy_encryption_method]; -ALTER TABLE [dbo].[guacamole_connection] +ALTER TABLE [guacamole_connection] ADD CONSTRAINT [DF_guacamole_connection_failover_only] DEFAULT ((0)) FOR [failover_only]; GO; @@ -142,7 +142,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_user]( +CREATE TABLE [guacamole_user]( [user_id] [int] IDENTITY(1,1) NOT NULL, [username] [nvarchar](128) NOT NULL, [password_hash] [binary](32) NOT NULL, @@ -173,9 +173,9 @@ CREATE TABLE [dbo].[guacamole_user]( /** * Defaults for user table */ -ALTER TABLE [dbo].[guacamole_user] +ALTER TABLE [guacamole_user] ADD CONSTRAINT [DF_guacamole_user_disabled] DEFAULT ((0)) FOR [disabled]; -ALTER TABLE [dbo].[guacamole_user] +ALTER TABLE [guacamole_user] ADD CONSTRAINT [DF_guacamole_user_expired] DEFAULT ((0)) FOR [expired]; GO; @@ -185,7 +185,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_sharing_profile]( +CREATE TABLE [guacamole_sharing_profile]( [sharing_profile_id] [int] IDENTITY(1,1) NOT NULL, [sharing_profile_name] [nvarchar](128) NOT NULL, [primary_connection_id] [int] NOT NULL, @@ -203,12 +203,12 @@ CREATE TABLE [dbo].[guacamole_sharing_profile]( /** * Foreign keys for sharing_profile table. */ -ALTER TABLE [dbo].[guacamole_sharing_profile] +ALTER TABLE [guacamole_sharing_profile] WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_connection] FOREIGN KEY([primary_connection_id]) - REFERENCES [dbo].[guacamole_connection] ([connection_id]) + REFERENCES [guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_sharing_profile] +ALTER TABLE [guacamole_sharing_profile] CHECK CONSTRAINT [FK_guacamole_sharing_profile_connection]; GO; @@ -218,7 +218,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_connection_parameter]( +CREATE TABLE [guacamole_connection_parameter]( [connection_id] [int] NOT NULL, [parameter_name] [nvarchar](128) NOT NULL, [parameter_value] [nvarchar](max) NOT NULL, @@ -236,12 +236,12 @@ CREATE TABLE [dbo].[guacamole_connection_parameter]( /** * Foreign keys for the connection_parameter table. */ -ALTER TABLE [dbo].[guacamole_connection_parameter] +ALTER TABLE [guacamole_connection_parameter] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_parameter_connection] FOREIGN KEY([connection_id]) - REFERENCES [dbo].[guacamole_connection] ([connection_id]) + REFERENCES [guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_connection_parameter] +ALTER TABLE [guacamole_connection_parameter] CHECK CONSTRAINT [FK_guacamole_connection_parameter_connection]; GO; @@ -251,7 +251,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_sharing_profile_parameter]( +CREATE TABLE [guacamole_sharing_profile_parameter]( [sharing_profile_id] [int] NOT NULL, [parameter_name] [nvarchar](128) NOT NULL, [parameter_value] [nvarchar](max) NOT NULL, @@ -270,12 +270,12 @@ CREATE TABLE [dbo].[guacamole_sharing_profile_parameter]( * Foreign keys for the sharing_profile_parameter * table. */ -ALTER TABLE [dbo].[guacamole_sharing_profile_parameter] +ALTER TABLE [guacamole_sharing_profile_parameter] WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_parameter_sharing_profile] FOREIGN KEY([sharing_profile_id]) - REFERENCES [dbo].[guacamole_sharing_profile] ([sharing_profile_id]) + REFERENCES [guacamole_sharing_profile] ([sharing_profile_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_sharing_profile_parameter] +ALTER TABLE [guacamole_sharing_profile_parameter] CHECK CONSTRAINT [FK_guacamole_sharing_profile_parameter_sharing_profile]; GO; @@ -285,10 +285,10 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_connection_permission]( +CREATE TABLE [guacamole_connection_permission]( [user_id] [int] NOT NULL, [connection_id] [int] NOT NULL, - [permission] [dbo].[guacamole_permission] NOT NULL, + [permission] [guacamole_permission] NOT NULL, CONSTRAINT [PK_guacamole_connection_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [connection_id] ASC, [permission] ASC) @@ -303,19 +303,19 @@ CREATE TABLE [dbo].[guacamole_connection_permission]( /** * Foreign keys for the connection_permission table. */ -ALTER TABLE [dbo].[guacamole_connection_permission] +ALTER TABLE [guacamole_connection_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_permission_connection1] FOREIGN KEY([connection_id]) - REFERENCES [dbo].[guacamole_connection] ([connection_id]) + REFERENCES [guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_connection_permission] +ALTER TABLE [guacamole_connection_permission] CHECK CONSTRAINT [FK_guacamole_connection_permission_connection1]; -ALTER TABLE [dbo].[guacamole_connection_permission] +ALTER TABLE [guacamole_connection_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_permission_user1] FOREIGN KEY([user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]) + REFERENCES [guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_connection_permission] +ALTER TABLE [guacamole_connection_permission] CHECK CONSTRAINT [FK_guacamole_connection_permission_user1]; GO; @@ -325,10 +325,10 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_connection_group_permission]( +CREATE TABLE [guacamole_connection_group_permission]( [user_id] [int] NOT NULL, [connection_group_id] [int] NOT NULL, - [permission] [dbo].[guacamole_permission] NOT NULL, + [permission] [guacamole_permission] NOT NULL, CONSTRAINT [PK_guacamole_connection_group_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [connection_group_id] ASC, [permission] ASC) @@ -343,19 +343,19 @@ CREATE TABLE [dbo].[guacamole_connection_group_permission]( /** * Foreign keys for the connection_group_permission table. */ -ALTER TABLE [dbo].[guacamole_connection_group_permission] +ALTER TABLE [guacamole_connection_group_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_permission_connection_group] FOREIGN KEY([connection_group_id]) - REFERENCES [dbo].[guacamole_connection_group] ([connection_group_id]) + REFERENCES [guacamole_connection_group] ([connection_group_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_connection_group_permission] +ALTER TABLE [guacamole_connection_group_permission] CHECK CONSTRAINT [FK_guacamole_connection_group_permission_connection_group]; -ALTER TABLE [dbo].[guacamole_connection_group_permission] +ALTER TABLE [guacamole_connection_group_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_permission_user] FOREIGN KEY([user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]) + REFERENCES [guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_connection_group_permission] +ALTER TABLE [guacamole_connection_group_permission] CHECK CONSTRAINT [FK_guacamole_connection_group_permission_user]; GO; @@ -365,10 +365,10 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_sharing_profile_permission]( +CREATE TABLE [guacamole_sharing_profile_permission]( [user_id] [int] NOT NULL, [sharing_profile_id] [int] NOT NULL, - [permission] [dbo].[guacamole_permission] NOT NULL, + [permission] [guacamole_permission] NOT NULL, CONSTRAINT [PK_guacamole_sharing_profile_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [sharing_profile_id] ASC, [permission] ASC) @@ -383,19 +383,19 @@ CREATE TABLE [dbo].[guacamole_sharing_profile_permission]( /** * Foreign keys for the sharing_profile_permission table. */ -ALTER TABLE [dbo].[guacamole_sharing_profile_permission] +ALTER TABLE [guacamole_sharing_profile_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile] FOREIGN KEY([sharing_profile_id]) - REFERENCES [dbo].[guacamole_sharing_profile] ([sharing_profile_id]) + REFERENCES [guacamole_sharing_profile] ([sharing_profile_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_sharing_profile_permission] +ALTER TABLE [guacamole_sharing_profile_permission] CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile]; -ALTER TABLE [dbo].[guacamole_sharing_profile_permission] +ALTER TABLE [guacamole_sharing_profile_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_permission_user] FOREIGN KEY([user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]) + REFERENCES [guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_sharing_profile_permission] +ALTER TABLE [guacamole_sharing_profile_permission] CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_user]; GO; @@ -405,9 +405,9 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_system_permission]( +CREATE TABLE [guacamole_system_permission]( [user_id] [int] NOT NULL, - [permission] [dbo].[guacamole_system_permission] NOT NULL, + [permission] [guacamole_system_permission] NOT NULL, CONSTRAINT [PK_guacamole_system_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [permission] ASC) @@ -422,12 +422,12 @@ CREATE TABLE [dbo].[guacamole_system_permission]( /** * Foreign keys for system_permission table. */ -ALTER TABLE [dbo].[guacamole_system_permission] +ALTER TABLE [guacamole_system_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_system_permission_user] FOREIGN KEY([user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]) + REFERENCES [guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_system_permission] +ALTER TABLE [guacamole_system_permission] CHECK CONSTRAINT [FK_guacamole_system_permission_user]; GO; @@ -437,10 +437,10 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_user_permission]( +CREATE TABLE [guacamole_user_permission]( [user_id] [int] NOT NULL, [affected_user_id] [int] NOT NULL, - [permission] [dbo].[guacamole_permission] NOT NULL, + [permission] [guacamole_permission] NOT NULL, CONSTRAINT [PK_guacamole_user_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [affected_user_id] ASC, [permission] ASC) @@ -455,17 +455,17 @@ CREATE TABLE [dbo].[guacamole_user_permission]( /** * Foreign keys for user_permission table. */ -ALTER TABLE [dbo].[guacamole_user_permission] +ALTER TABLE [guacamole_user_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user] FOREIGN KEY([user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]) + REFERENCES [guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_user_permission] +ALTER TABLE [guacamole_user_permission] CHECK CONSTRAINT [FK_guacamole_user_permission_user]; -ALTER TABLE [dbo].[guacamole_user_permission] +ALTER TABLE [guacamole_user_permission] WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user1] FOREIGN KEY([affected_user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]); -ALTER TABLE [dbo].[guacamole_user_permission] + REFERENCES [guacamole_user] ([user_id]); +ALTER TABLE [guacamole_user_permission] CHECK CONSTRAINT [FK_guacamole_user_permission_user1]; GO; @@ -475,7 +475,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_connection_history]( +CREATE TABLE [guacamole_connection_history]( [history_id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NULL, [username] [nvarchar](128) NOT NULL, @@ -500,24 +500,24 @@ CREATE TABLE [dbo].[guacamole_connection_history]( /** * Foreign keys for connection_history table */ -ALTER TABLE [dbo].[guacamole_connection_history] +ALTER TABLE [guacamole_connection_history] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_connection] FOREIGN KEY([connection_id]) - REFERENCES [dbo].[guacamole_connection] ([connection_id]) + REFERENCES [guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE SET NULL; -ALTER TABLE [dbo].[guacamole_connection_history] +ALTER TABLE [guacamole_connection_history] CHECK CONSTRAINT [FK_guacamole_connection_history_connection]; -ALTER TABLE [dbo].[guacamole_connection_history] +ALTER TABLE [guacamole_connection_history] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_sharing_profile] FOREIGN KEY([sharing_profile_id]) - REFERENCES [dbo].[guacamole_sharing_profile] ([sharing_profile_id]); -ALTER TABLE [dbo].[guacamole_connection_history] + REFERENCES [guacamole_sharing_profile] ([sharing_profile_id]); +ALTER TABLE [guacamole_connection_history] CHECK CONSTRAINT [FK_guacamole_connection_history_sharing_profile]; -ALTER TABLE [dbo].[guacamole_connection_history] +ALTER TABLE [guacamole_connection_history] WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_user] FOREIGN KEY([user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]) + REFERENCES [guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE SET NULL; -ALTER TABLE [dbo].[guacamole_connection_history] +ALTER TABLE [guacamole_connection_history] CHECK CONSTRAINT [FK_guacamole_connection_history_user]; GO; @@ -528,7 +528,7 @@ GO; */ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -CREATE TABLE [dbo].[guacamole_user_password_history]( +CREATE TABLE [guacamole_user_password_history]( [password_history_id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [password_hash] [binary](32) NOT NULL, @@ -548,11 +548,11 @@ CREATE TABLE [dbo].[guacamole_user_password_history]( /** * Foreign keys for user_password_history table */ -ALTER TABLE [dbo].[guacamole_user_password_history] +ALTER TABLE [guacamole_user_password_history] WITH CHECK ADD CONSTRAINT [FK_guacamole_user_password_history_user] FOREIGN KEY([user_id]) - REFERENCES [dbo].[guacamole_user] ([user_id]) + REFERENCES [guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE [dbo].[guacamole_user_password_history] +ALTER TABLE [guacamole_user_password_history] CHECK CONSTRAINT [FK_guacamole_user_password_history_user]; GO; http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql index f7d5b45..d348b61 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql @@ -20,20 +20,20 @@ /** * Create the default admin user account and set up full privileges. */ -INSERT INTO [dbo].[guacamole_user] (username, password_hash, password_date) +INSERT INTO [guacamole_user] (username, password_hash, password_date) VALUES ('guacadmin', HASHBYTES('SHA2_256', 'guacadmin'), getdate()); -INSERT INTO [dbo].[guacamole_user_permission] -SELECT [dbo].[guacamole_user].[user_id], [affected].[user_id], permission +INSERT INTO [guacamole_user_permission] +SELECT [guacamole_user].[user_id], [affected].[user_id], permission FROM ( SELECT 'guacadmin' AS username, 'guacadmin' AS affected_username, 'READ' AS permission UNION SELECT 'guacadmin' AS username, 'guacadmin' AS affected_username, 'UPDATE' AS permission UNION SELECT 'guacadmin' AS username, 'guacadmin' AS affected_username, 'ADMINISTER' AS permission) permissions - JOIN [dbo].[guacamole_user] ON permissions.username = [dbo].[guacamole_user].[username] - JOIN [dbo].[guacamole_user] affected ON permissions.affected_username = affected.username; + JOIN [guacamole_user] ON permissions.username = [guacamole_user].[username] + JOIN [guacamole_user] affected ON permissions.affected_username = affected.username; -INSERT INTO [dbo].[guacamole_system_permission] +INSERT INTO [guacamole_system_permission] SELECT user_id, permission FROM ( SELECT 'guacadmin' AS username, 'CREATE_CONNECTION' AS permission @@ -42,5 +42,5 @@ FROM ( UNION SELECT 'guacadmin' AS username, 'CREATE_USER' AS permission UNION SELECT 'guacadmin' AS username, 'ADMINISTER' AS permission) permissions - JOIN [dbo].[guacamole_user] ON permissions.username = [dbo].[guacamole_user].[username]; + JOIN [guacamole_user] ON permissions.username = [guacamole_user].[username]; GO; http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml index c62ca6f..3e6819f 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml @@ -51,13 +51,13 @@ <!-- Select all connection identifiers --> <select id="selectIdentifiers" resultType="string"> SELECT connection_id - FROM [dbo].[guacamole_connection] + FROM [guacamole_connection] </select> <!-- Select identifiers of all readable connections --> <select id="selectReadableIdentifiers" resultType="string"> SELECT connection_id - FROM [dbo].[guacamole_connection_permission] + FROM [guacamole_connection_permission] WHERE user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ' @@ -66,7 +66,7 @@ <!-- Select all connection identifiers within a particular connection group --> <select id="selectIdentifiersWithin" resultType="string"> SELECT connection_id - FROM [dbo].[guacamole_connection] + FROM [guacamole_connection] WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> @@ -74,9 +74,9 @@ <!-- Select identifiers of all readable connections within a particular connection group --> <select id="selectReadableIdentifiersWithin" resultType="string"> - SELECT [dbo].[guacamole_connection].connection_id - FROM [dbo].[guacamole_connection] - JOIN [dbo].[guacamole_connection_permission] ON [dbo].[guacamole_connection_permission].connection_id = [dbo].[guacamole_connection].connection_id + SELECT [guacamole_connection].connection_id + FROM [guacamole_connection] + JOIN [guacamole_connection_permission] ON [guacamole_connection_permission].connection_id = [guacamole_connection].connection_id WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> @@ -100,7 +100,7 @@ proxy_encryption_method, connection_weight, failover_only - FROM [dbo].[guacamole_connection] + FROM [guacamole_connection] WHERE connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -108,7 +108,7 @@ </foreach>; SELECT primary_connection_id, sharing_profile_id - FROM [dbo].[guacamole_sharing_profile] + FROM [guacamole_sharing_profile] WHERE primary_connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -122,7 +122,7 @@ resultSets="connections,sharingProfiles"> SELECT - [dbo].[guacamole_connection].connection_id, + [guacamole_connection].connection_id, connection_name, parent_id, protocol, @@ -133,9 +133,9 @@ proxy_encryption_method, connection_weight, failover_only - FROM [dbo].[guacamole_connection] - JOIN [dbo].[guacamole_connection_permission] ON [dbo].[guacamole_connection_permission].connection_id = [dbo].[guacamole_connection].connection_id - WHERE [dbo].[guacamole_connection].connection_id IN + FROM [guacamole_connection] + JOIN [guacamole_connection_permission] ON [guacamole_connection_permission].connection_id = [guacamole_connection].connection_id + WHERE [guacamole_connection].connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} @@ -143,9 +143,9 @@ AND user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ'; - SELECT primary_connection_id, [dbo].[guacamole_sharing_profile].sharing_profile_id - FROM [dbo].[guacamole_sharing_profile] - JOIN [dbo].[guacamole_sharing_profile_permission] ON [dbo].[guacamole_sharing_profile_permission].sharing_profile_id = [dbo].[guacamole_sharing_profile].sharing_profile_id + SELECT primary_connection_id, [guacamole_sharing_profile].sharing_profile_id + FROM [guacamole_sharing_profile] + JOIN [guacamole_sharing_profile_permission] ON [guacamole_sharing_profile_permission].sharing_profile_id = [guacamole_sharing_profile].sharing_profile_id WHERE primary_connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -171,7 +171,7 @@ proxy_encryption_method, connection_weight, failover_only - FROM [dbo].[guacamole_connection] + FROM [guacamole_connection] WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> @@ -181,7 +181,7 @@ <!-- Delete single connection by identifier --> <delete id="delete"> - DELETE FROM [dbo].[guacamole_connection] + DELETE FROM [guacamole_connection] WHERE connection_id = #{identifier,jdbcType=INTEGER} </delete> @@ -189,7 +189,7 @@ <insert id="insert" useGeneratedKeys="true" keyProperty="object.objectID" parameterType="org.apache.guacamole.auth.jdbc.connection.ConnectionModel"> - INSERT INTO [dbo].[guacamole_connection] ( + INSERT INTO [guacamole_connection] ( connection_name, parent_id, protocol, @@ -218,7 +218,7 @@ <!-- Update single connection --> <update id="update" parameterType="org.apache.guacamole.auth.jdbc.connection.ConnectionModel"> - UPDATE [dbo].[guacamole_connection] + UPDATE [guacamole_connection] SET connection_name = #{object.name,jdbcType=VARCHAR}, parent_id = #{object.parentIdentifier,jdbcType=INTEGER}, protocol = #{object.protocol,jdbcType=VARCHAR}, http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionParameterMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionParameterMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionParameterMapper.xml index 0d13aeb..08b337e 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionParameterMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionParameterMapper.xml @@ -36,21 +36,21 @@ connection_id, parameter_name, parameter_value - FROM [dbo].[guacamole_connection_parameter] + FROM [guacamole_connection_parameter] WHERE connection_id = #{identifier,jdbcType=INTEGER} </select> <!-- Delete all parameters of a given connection --> <delete id="delete"> - DELETE FROM [dbo].[guacamole_connection_parameter] + DELETE FROM [guacamole_connection_parameter] WHERE connection_id = #{identifier,jdbcType=INTEGER} </delete> <!-- Insert all given parameters --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.connection.ConnectionParameterModel"> - INSERT INTO [dbo].[guacamole_connection_parameter] ( + INSERT INTO [guacamole_connection_parameter] ( connection_id, parameter_name, parameter_value http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml index 648d16f..bca8139 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml @@ -40,28 +40,28 @@ <select id="select" resultMap="ConnectionRecordResultMap"> SELECT - [dbo].[guacamole_connection_history].connection_id, - [dbo].[guacamole_connection_history].connection_name, - [dbo].[guacamole_connection_history].remote_host, - [dbo].[guacamole_connection_history].sharing_profile_id, - [dbo].[guacamole_connection_history].sharing_profile_name, - [dbo].[guacamole_connection_history].user_id, - [dbo].[guacamole_connection_history].username, - [dbo].[guacamole_connection_history].start_date, - [dbo].[guacamole_connection_history].end_date - FROM [dbo].[guacamole_connection_history] + [guacamole_connection_history].connection_id, + [guacamole_connection_history].connection_name, + [guacamole_connection_history].remote_host, + [guacamole_connection_history].sharing_profile_id, + [guacamole_connection_history].sharing_profile_name, + [guacamole_connection_history].user_id, + [guacamole_connection_history].username, + [guacamole_connection_history].start_date, + [guacamole_connection_history].end_date + FROM [guacamole_connection_history] WHERE - [dbo].[guacamole_connection_history].connection_id = #{identifier,jdbcType=INTEGER} + [guacamole_connection_history].connection_id = #{identifier,jdbcType=INTEGER} ORDER BY - [dbo].[guacamole_connection_history].start_date DESC, - [dbo].[guacamole_connection_history].end_date DESC + [guacamole_connection_history].start_date DESC, + [guacamole_connection_history].end_date DESC </select> <!-- Insert the given connection record --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.connection.ConnectionRecordModel"> - INSERT INTO [dbo].[guacamole_connection_history] ( + INSERT INTO [guacamole_connection_history] ( connection_id, connection_name, remote_host, @@ -78,7 +78,7 @@ #{record.remoteHost,jdbcType=VARCHAR}, #{record.sharingProfileIdentifier,jdbcType=INTEGER}, #{record.sharingProfileName,jdbcType=VARCHAR}, - (SELECT user_id FROM [dbo].[guacamole_user] + (SELECT user_id FROM [guacamole_user] WHERE username = #{record.username,jdbcType=VARCHAR}), #{record.username,jdbcType=VARCHAR}, #{record.startDate,jdbcType=TIMESTAMP}, @@ -91,31 +91,31 @@ <select id="search" resultMap="ConnectionRecordResultMap"> SELECT TOP (#{limit,jdbcType=INTEGER}) - [dbo].[guacamole_connection_history].connection_id, - [dbo].[guacamole_connection_history].connection_name, - [dbo].[guacamole_connection_history].remote_host, - [dbo].[guacamole_connection_history].sharing_profile_id, - [dbo].[guacamole_connection_history].sharing_profile_name, - [dbo].[guacamole_connection_history].user_id, - [dbo].[guacamole_connection_history].username, - [dbo].[guacamole_connection_history].start_date, - [dbo].[guacamole_connection_history].end_date - FROM [dbo].[guacamole_connection_history] + [guacamole_connection_history].connection_id, + [guacamole_connection_history].connection_name, + [guacamole_connection_history].remote_host, + [guacamole_connection_history].sharing_profile_id, + [guacamole_connection_history].sharing_profile_name, + [guacamole_connection_history].user_id, + [guacamole_connection_history].username, + [guacamole_connection_history].start_date, + [guacamole_connection_history].end_date + FROM [guacamole_connection_history] <!-- Search terms --> <foreach collection="terms" item="term" open="WHERE " separator=" AND "> ( - [dbo].[guacamole_connection_history].user_id IN ( + [guacamole_connection_history].user_id IN ( SELECT user_id - FROM [dbo].[guacamole_user] + FROM [guacamole_user] WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 ) - OR [dbo].[guacamole_connection_history].connection_id IN ( + OR [guacamole_connection_history].connection_id IN ( SELECT connection_id - FROM [dbo].[guacamole_connection] + FROM [guacamole_connection] WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN connection_name) > 0 ) @@ -133,7 +133,7 @@ <foreach collection="sortPredicates" item="sortPredicate" open="ORDER BY " separator=", "> <choose> - <when test="sortPredicate.property == START_DATE">[dbo].[guacamole_connection_history].start_date</when> + <when test="sortPredicate.property == START_DATE">[guacamole_connection_history].start_date</when> <otherwise>1</otherwise> </choose> <if test="sortPredicate.descending">DESC</if> @@ -145,45 +145,45 @@ <select id="searchReadable" resultMap="ConnectionRecordResultMap"> SELECT TOP (#{limit,jdbcType=INTEGER}) - [dbo].[guacamole_connection_history].connection_id, - [dbo].[guacamole_connection_history].connection_name, - [dbo].[guacamole_connection_history].remote_host, - [dbo].[guacamole_connection_history].sharing_profile_id, - [dbo].[guacamole_connection_history].sharing_profile_name, - [dbo].[guacamole_connection_history].user_id, - [dbo].[guacamole_connection_history].username, - [dbo].[guacamole_connection_history].start_date, - [dbo].[guacamole_connection_history].end_date - FROM [dbo].[guacamole_connection_history] - LEFT JOIN [dbo].[guacamole_connection] ON [dbo].[guacamole_connection_history].connection_id = [dbo].[guacamole_connection].connection_id - LEFT JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_connection_history].user_id = [dbo].[guacamole_user].user_id + [guacamole_connection_history].connection_id, + [guacamole_connection_history].connection_name, + [guacamole_connection_history].remote_host, + [guacamole_connection_history].sharing_profile_id, + [guacamole_connection_history].sharing_profile_name, + [guacamole_connection_history].user_id, + [guacamole_connection_history].username, + [guacamole_connection_history].start_date, + [guacamole_connection_history].end_date + FROM [guacamole_connection_history] + LEFT JOIN [guacamole_connection] ON [guacamole_connection_history].connection_id = [guacamole_connection].connection_id + LEFT JOIN [guacamole_user] ON [guacamole_connection_history].user_id = [guacamole_user].user_id <!-- Restrict to readable connections --> - JOIN [dbo].[guacamole_connection_permission] ON - [dbo].[guacamole_connection_history].connection_id = [dbo].[guacamole_connection_permission].connection_id - AND [dbo].[guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} - AND [dbo].[guacamole_connection_permission].permission = 'READ' + JOIN [guacamole_connection_permission] ON + [guacamole_connection_history].connection_id = [guacamole_connection_permission].connection_id + AND [guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND [guacamole_connection_permission].permission = 'READ' <!-- Restrict to readable users --> - JOIN [dbo].[guacamole_user_permission] ON - [dbo].[guacamole_connection_history].user_id = [dbo].[guacamole_user_permission].affected_user_id - AND [dbo].[guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} - AND [dbo].[guacamole_user_permission].permission = 'READ' + JOIN [guacamole_user_permission] ON + [guacamole_connection_history].user_id = [guacamole_user_permission].affected_user_id + AND [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND [guacamole_user_permission].permission = 'READ' <!-- Search terms --> <foreach collection="terms" item="term" open="WHERE " separator=" AND "> ( - [dbo].[guacamole_connection_history].user_id IN ( + [guacamole_connection_history].user_id IN ( SELECT user_id - FROM [dbo].[guacamole_user] + FROM [guacamole_user] WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 ) - OR [dbo].[guacamole_connection_history].connection_id IN ( + OR [guacamole_connection_history].connection_id IN ( SELECT connection_id - FROM [dbo].[guacamole_connection] + FROM [guacamole_connection] WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN connection_name) > 0 ) @@ -201,7 +201,7 @@ <foreach collection="sortPredicates" item="sortPredicate" open="ORDER BY " separator=", "> <choose> - <when test="sortPredicate.property == START_DATE">[dbo].[guacamole_connection_history].start_date</when> + <when test="sortPredicate.property == START_DATE">[guacamole_connection_history].start_date</when> <otherwise>1</otherwise> </choose> <if test="sortPredicate.descending">DESC</if> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml index d1f97a7..452c0a8 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml @@ -53,13 +53,13 @@ <!-- Select all connection group identifiers --> <select id="selectIdentifiers" resultType="string"> SELECT connection_group_id - FROM [dbo].[guacamole_connection_group] + FROM [guacamole_connection_group] </select> <!-- Select identifiers of all readable connection groups --> <select id="selectReadableIdentifiers" resultType="string"> SELECT connection_group_id - FROM [dbo].[guacamole_connection_group_permission] + FROM [guacamole_connection_group_permission] WHERE user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ' @@ -68,7 +68,7 @@ <!-- Select all connection identifiers within a particular connection group --> <select id="selectIdentifiersWithin" resultType="string"> SELECT connection_group_id - FROM [dbo].[guacamole_connection_group] + FROM [guacamole_connection_group] WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> @@ -76,9 +76,9 @@ <!-- Select identifiers of all readable connection groups within a particular connection group --> <select id="selectReadableIdentifiersWithin" resultType="string"> - SELECT [dbo].[guacamole_connection_group].connection_group_id - FROM [dbo].[guacamole_connection_group] - JOIN [dbo].[guacamole_connection_group_permission] ON [dbo].[guacamole_connection_group_permission].connection_group_id = [dbo].[guacamole_connection_group].connection_group_id + SELECT [guacamole_connection_group].connection_group_id + FROM [guacamole_connection_group] + JOIN [guacamole_connection_group_permission] ON [guacamole_connection_group_permission].connection_group_id = [guacamole_connection_group].connection_group_id WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> @@ -98,7 +98,7 @@ max_connections, max_connections_per_user, enable_session_affinity - FROM [dbo].[guacamole_connection_group] + FROM [guacamole_connection_group] WHERE connection_group_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -106,7 +106,7 @@ </foreach>; SELECT parent_id, connection_group_id - FROM [dbo].[guacamole_connection_group] + FROM [guacamole_connection_group] WHERE parent_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -114,7 +114,7 @@ </foreach>; SELECT parent_id, connection_id - FROM [dbo].[guacamole_connection] + FROM [guacamole_connection] WHERE parent_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -128,16 +128,16 @@ resultSets="connectionGroups,childConnectionGroups,childConnections"> SELECT - [dbo].[guacamole_connection_group].connection_group_id, + [guacamole_connection_group].connection_group_id, connection_group_name, parent_id, type, max_connections, max_connections_per_user, enable_session_affinity - FROM [dbo].[guacamole_connection_group] - JOIN [dbo].[guacamole_connection_group_permission] ON [dbo].[guacamole_connection_group_permission].connection_group_id = [dbo].[guacamole_connection_group].connection_group_id - WHERE [dbo].[guacamole_connection_group].connection_group_id IN + FROM [guacamole_connection_group] + JOIN [guacamole_connection_group_permission] ON [guacamole_connection_group_permission].connection_group_id = [guacamole_connection_group].connection_group_id + WHERE [guacamole_connection_group].connection_group_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} @@ -145,9 +145,9 @@ AND user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ'; - SELECT parent_id, [dbo].[guacamole_connection_group].connection_group_id - FROM [dbo].[guacamole_connection_group] - JOIN [dbo].[guacamole_connection_group_permission] ON [dbo].[guacamole_connection_group_permission].connection_group_id = [dbo].[guacamole_connection_group].connection_group_id + SELECT parent_id, [guacamole_connection_group].connection_group_id + FROM [guacamole_connection_group] + JOIN [guacamole_connection_group_permission] ON [guacamole_connection_group_permission].connection_group_id = [guacamole_connection_group].connection_group_id WHERE parent_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -156,9 +156,9 @@ AND user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ'; - SELECT parent_id, [dbo].[guacamole_connection].connection_id - FROM [dbo].[guacamole_connection] - JOIN [dbo].[guacamole_connection_permission] ON [dbo].[guacamole_connection_permission].connection_id = [dbo].[guacamole_connection].connection_id + SELECT parent_id, [guacamole_connection].connection_id + FROM [guacamole_connection] + JOIN [guacamole_connection_permission] ON [guacamole_connection_permission].connection_id = [guacamole_connection].connection_id WHERE parent_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -180,7 +180,7 @@ max_connections, max_connections_per_user, enable_session_affinity - FROM [dbo].[guacamole_connection_group] + FROM [guacamole_connection_group] WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> @@ -190,7 +190,7 @@ <!-- Delete single connection group by identifier --> <delete id="delete"> - DELETE FROM [dbo].[guacamole_connection_group] + DELETE FROM [guacamole_connection_group] WHERE connection_group_id = #{identifier,jdbcType=INTEGER} </delete> @@ -198,7 +198,7 @@ <insert id="insert" useGeneratedKeys="true" keyProperty="object.objectID" parameterType="org.apache.guacamole.auth.jdbc.connectiongroup.ConnectionGroupModel"> - INSERT INTO [dbo].[guacamole_connection_group] ( + INSERT INTO [guacamole_connection_group] ( connection_group_name, parent_id, type, @@ -219,7 +219,7 @@ <!-- Update single connection group --> <update id="update" parameterType="org.apache.guacamole.auth.jdbc.connectiongroup.ConnectionGroupModel"> - UPDATE [dbo].[guacamole_connection_group] + UPDATE [guacamole_connection_group] SET connection_group_name = #{object.name,jdbcType=VARCHAR}, parent_id = #{object.parentIdentifier,jdbcType=INTEGER}, type = #{object.type,jdbcType=VARCHAR}, http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml index ff69a75..3cc0988 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml @@ -36,13 +36,13 @@ <select id="select" resultMap="ConnectionGroupPermissionResultMap"> SELECT - [dbo].[guacamole_connection_group_permission].user_id, + [guacamole_connection_group_permission].user_id, username, permission, connection_group_id - FROM [dbo].[guacamole_connection_group_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_connection_group_permission].user_id = [dbo].[guacamole_user].user_id - WHERE [dbo].[guacamole_connection_group_permission].user_id = #{user.objectID,jdbcType=INTEGER} + FROM [guacamole_connection_group_permission] + JOIN [guacamole_user] ON [guacamole_connection_group_permission].user_id = [guacamole_user].user_id + WHERE [guacamole_connection_group_permission].user_id = #{user.objectID,jdbcType=INTEGER} </select> @@ -50,14 +50,14 @@ <select id="selectOne" resultMap="ConnectionGroupPermissionResultMap"> SELECT - [dbo].[guacamole_connection_group_permission].user_id, + [guacamole_connection_group_permission].user_id, username, permission, connection_group_id - FROM [dbo].[guacamole_connection_group_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_connection_group_permission].user_id = [dbo].[guacamole_user].user_id + FROM [guacamole_connection_group_permission] + JOIN [guacamole_user] ON [guacamole_connection_group_permission].user_id = [guacamole_user].user_id WHERE - [dbo].[guacamole_connection_group_permission].user_id = #{user.objectID,jdbcType=INTEGER} + [guacamole_connection_group_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR} AND connection_group_id = #{identifier,jdbcType=INTEGER} @@ -67,7 +67,7 @@ <select id="selectAccessibleIdentifiers" resultType="string"> SELECT DISTINCT connection_group_id - FROM [dbo].[guacamole_connection_group_permission] + FROM [guacamole_connection_group_permission] WHERE user_id = #{user.objectID,jdbcType=INTEGER} AND connection_group_id IN @@ -86,7 +86,7 @@ <!-- Delete all given permissions --> <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - DELETE FROM [dbo].[guacamole_connection_group_permission] + DELETE FROM [guacamole_connection_group_permission] WHERE (user_id, permission, connection_group_id) IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> @@ -100,7 +100,7 @@ <!-- Insert all given permissions --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - INSERT INTO [dbo].[guacamole_connection_group_permission] ( + INSERT INTO [guacamole_connection_group_permission] ( user_id, permission, connection_group_id @@ -117,10 +117,10 @@ #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_group_id </foreach> AS permissions - WHERE NOT EXISTS (SELECT 1 FROM [dbo].[guacamole_connection_group_permission] - WHERE [dbo].[guacamole_connection_group_permission].user_id = permissions.user_id AND - [dbo].[guacamole_connection_group_permission].permission = permissions.permission AND - [dbo].[guacamole_connection_group_permission].connection_group_id = permissions.connection_group_id + WHERE NOT EXISTS (SELECT 1 FROM [guacamole_connection_group_permission] + WHERE [guacamole_connection_group_permission].user_id = permissions.user_id AND + [guacamole_connection_group_permission].permission = permissions.permission AND + [guacamole_connection_group_permission].connection_group_id = permissions.connection_group_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml index fa25f63..aaa555a 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml @@ -36,13 +36,13 @@ <select id="select" resultMap="ConnectionPermissionResultMap"> SELECT - [dbo].[guacamole_connection_permission].user_id, + [guacamole_connection_permission].user_id, username, permission, connection_id - FROM [dbo].[guacamole_connection_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_connection_permission].user_id = [dbo].[guacamole_user].user_id - WHERE [dbo].[guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} + FROM [guacamole_connection_permission] + JOIN [guacamole_user] ON [guacamole_connection_permission].user_id = [guacamole_user].user_id + WHERE [guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} </select> @@ -50,14 +50,14 @@ <select id="selectOne" resultMap="ConnectionPermissionResultMap"> SELECT - [dbo].[guacamole_connection_permission].user_id, + [guacamole_connection_permission].user_id, username, permission, connection_id - FROM [dbo].[guacamole_connection_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_connection_permission].user_id = [dbo].[guacamole_user].user_id + FROM [guacamole_connection_permission] + JOIN [guacamole_user] ON [guacamole_connection_permission].user_id = [guacamole_user].user_id WHERE - [dbo].[guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} + [guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR} AND connection_id = #{identifier,jdbcType=INTEGER} @@ -67,7 +67,7 @@ <select id="selectAccessibleIdentifiers" resultType="string"> SELECT DISTINCT connection_id - FROM [dbo].[guacamole_connection_permission] + FROM [guacamole_connection_permission] WHERE user_id = #{user.objectID,jdbcType=INTEGER} AND connection_id IN @@ -86,7 +86,7 @@ <!-- Delete all given permissions --> <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - DELETE FROM [dbo].[guacamole_connection_permission] + DELETE FROM [guacamole_connection_permission] WHERE <foreach collection="permissions" item="permission" open="(" separator=" OR " close=")"> @@ -100,7 +100,7 @@ <!-- Insert all given permissions --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - INSERT INTO [dbo].[guacamole_connection_permission] ( + INSERT INTO [guacamole_connection_permission] ( user_id, permission, connection_id @@ -117,10 +117,10 @@ #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_id </foreach> AS permissions - WHERE NOT EXISTS ( SELECT 1 FROM [dbo].[guacamole_connection_permission] - WHERE [dbo].[guacamole_connection_permission].user_id = permissions.user_id AND - [dbo].[guacamole_connection_permission].permission = permissions.permission AND - [dbo].[guacamole_connection_permission].connection_id = permissions.connection_id + WHERE NOT EXISTS ( SELECT 1 FROM [guacamole_connection_permission] + WHERE [guacamole_connection_permission].user_id = permissions.user_id AND + [guacamole_connection_permission].permission = permissions.permission AND + [guacamole_connection_permission].connection_id = permissions.connection_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml index 40e9907..ab40d2a 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml @@ -36,13 +36,13 @@ <select id="select" resultMap="SharingProfilePermissionResultMap"> SELECT - [dbo].[guacamole_sharing_profile_permission].user_id, + [guacamole_sharing_profile_permission].user_id, username, permission, sharing_profile_id - FROM [dbo].[guacamole_sharing_profile_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_sharing_profile_permission].user_id = [dbo].[guacamole_user].user_id - WHERE [dbo].[guacamole_sharing_profile_permission].user_id = #{user.objectID,jdbcType=INTEGER} + FROM [guacamole_sharing_profile_permission] + JOIN [guacamole_user] ON [guacamole_sharing_profile_permission].user_id = [guacamole_user].user_id + WHERE [guacamole_sharing_profile_permission].user_id = #{user.objectID,jdbcType=INTEGER} </select> @@ -50,14 +50,14 @@ <select id="selectOne" resultMap="SharingProfilePermissionResultMap"> SELECT - [dbo].[guacamole_sharing_profile_permission].user_id, + [guacamole_sharing_profile_permission].user_id, username, permission, sharing_profile_id - FROM [dbo].[guacamole_sharing_profile_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_sharing_profile_permission].user_id = [dbo].[guacamole_user].user_id + FROM [guacamole_sharing_profile_permission] + JOIN [guacamole_user] ON [guacamole_sharing_profile_permission].user_id = [guacamole_user].user_id WHERE - [dbo].[guacamole_sharing_profile_permission].user_id = #{user.objectID,jdbcType=INTEGER} + [guacamole_sharing_profile_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR} AND sharing_profile_id = #{identifier,jdbcType=INTEGER} @@ -67,7 +67,7 @@ <select id="selectAccessibleIdentifiers" resultType="string"> SELECT DISTINCT sharing_profile_id - FROM [dbo].[guacamole_sharing_profile_permission] + FROM [guacamole_sharing_profile_permission] WHERE user_id = #{user.objectID,jdbcType=INTEGER} AND sharing_profile_id IN @@ -86,7 +86,7 @@ <!-- Delete all given permissions --> <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - DELETE FROM [dbo].[guacamole_sharing_profile_permission] + DELETE FROM [guacamole_sharing_profile_permission] WHERE <foreach collection="permissions" item="permission" open="(" separator=" OR " close=")"> @@ -100,7 +100,7 @@ <!-- Insert all given permissions --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - INSERT INTO [dbo].[guacamole_sharing_profile_permission] ( + INSERT INTO [guacamole_sharing_profile_permission] ( user_id, permission, sharing_profile_id @@ -117,10 +117,10 @@ #{permission.objectIdentifier,jdbcType=INTEGER} AS sharing_profile_id </foreach> AS permissions - WHERE NOT EXISTS (SELECT 1 FROM [dbo].[guacamole_sharing_profile_permission] - WHERE [dbo].[guacamole_sharing_profile_permission].user_id = permissions.user_id - AND [dbo].[guacamole_sharing_profile_permission].permission = permissions.permission - AND [dbo].[guacamole_sharing_profile_permission].sharing_profile_id = permissions.sharing_profile_id + WHERE NOT EXISTS (SELECT 1 FROM [guacamole_sharing_profile_permission] + WHERE [guacamole_sharing_profile_permission].user_id = permissions.user_id + AND [guacamole_sharing_profile_permission].permission = permissions.permission + AND [guacamole_sharing_profile_permission].sharing_profile_id = permissions.sharing_profile_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml index d33dd3b..663b94e 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml @@ -35,12 +35,12 @@ <select id="select" resultMap="SystemPermissionResultMap"> SELECT - [dbo].[guacamole_system_permission].user_id, + [guacamole_system_permission].user_id, username, permission - FROM [dbo].[guacamole_system_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_system_permission].user_id = [dbo].[guacamole_user].user_id - WHERE [dbo].[guacamole_system_permission].user_id = #{user.objectID,jdbcType=INTEGER} + FROM [guacamole_system_permission] + JOIN [guacamole_user] ON [guacamole_system_permission].user_id = [guacamole_user].user_id + WHERE [guacamole_system_permission].user_id = #{user.objectID,jdbcType=INTEGER} </select> @@ -48,13 +48,13 @@ <select id="selectOne" resultMap="SystemPermissionResultMap"> SELECT - [dbo].[guacamole_system_permission].user_id, + [guacamole_system_permission].user_id, username, permission - FROM [dbo].[guacamole_system_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_system_permission].user_id = [dbo].[guacamole_user].user_id + FROM [guacamole_system_permission] + JOIN [guacamole_user] ON [guacamole_system_permission].user_id = [guacamole_user].user_id WHERE - [dbo].[guacamole_system_permission].user_id = #{user.objectID,jdbcType=INTEGER} + [guacamole_system_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR} </select> @@ -62,7 +62,7 @@ <!-- Delete all given permissions --> <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> - DELETE FROM [dbo].[guacamole_system_permission] + DELETE FROM [guacamole_system_permission] WHERE <foreach collection="permissions" item="permission" open="(" separator=" OR " close=")"> @@ -75,7 +75,7 @@ <!-- Insert all given permissions --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> - INSERT INTO [dbo].[guacamole_system_permission] ( + INSERT INTO [guacamole_system_permission] ( user_id, permission ) @@ -89,9 +89,9 @@ #{permission.type,jdbcType=VARCHAR} AS permission </foreach> AS permissions - WHERE NOT EXISTS (SELECT 1 FROM [dbo].[guacamole_system_permission] - WHERE [dbo].[guacamole_system_permission].user_id = permissions.user_id - AND [dbo].[guacamole_system_permission].permission = permissions.permission + WHERE NOT EXISTS (SELECT 1 FROM [guacamole_system_permission] + WHERE [guacamole_system_permission].user_id = permissions.user_id + AND [guacamole_system_permission].permission = permissions.permission ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml index a2d416f..453777d 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml @@ -36,14 +36,14 @@ <select id="select" resultMap="UserPermissionResultMap"> SELECT - [dbo].[guacamole_user_permission].user_id, - [dbo].[guacamole_user].username, + [guacamole_user_permission].user_id, + [guacamole_user].username, permission, affected.username AS affected_username - FROM [dbo].[guacamole_user_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_user_permission].user_id = [dbo].[guacamole_user].user_id - JOIN [dbo].[guacamole_user] affected ON [dbo].[guacamole_user_permission].affected_user_id = affected.user_id - WHERE [dbo].[guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + FROM [guacamole_user_permission] + JOIN [guacamole_user] ON [guacamole_user_permission].user_id = [guacamole_user].user_id + JOIN [guacamole_user] affected ON [guacamole_user_permission].affected_user_id = affected.user_id + WHERE [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} </select> @@ -51,15 +51,15 @@ <select id="selectOne" resultMap="UserPermissionResultMap"> SELECT - [dbo].[guacamole_user_permission].user_id, - [dbo].[guacamole_user].username, + [guacamole_user_permission].user_id, + [guacamole_user].username, permission, affected.username AS affected_username - FROM [dbo].[guacamole_user_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_user_permission].user_id = [dbo].[guacamole_user].user_id - JOIN [dbo].[guacamole_user] affected ON [dbo].[guacamole_user_permission].affected_user_id = affected.user_id + FROM [guacamole_user_permission] + JOIN [guacamole_user] ON [guacamole_user_permission].user_id = [guacamole_user].user_id + JOIN [guacamole_user] affected ON [guacamole_user_permission].affected_user_id = affected.user_id WHERE - [dbo].[guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR} AND affected.username = #{identifier,jdbcType=INTEGER} @@ -69,10 +69,10 @@ <select id="selectAccessibleIdentifiers" resultType="string"> SELECT DISTINCT username - FROM [dbo].[guacamole_user_permission] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_user_permission].affected_user_id = [dbo].[guacamole_user].user_id + FROM [guacamole_user_permission] + JOIN [guacamole_user] ON [guacamole_user_permission].affected_user_id = [guacamole_user].user_id WHERE - [dbo].[guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND username IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -89,11 +89,11 @@ <!-- Delete all given permissions --> <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - DELETE FROM [dbo].[guacamole_user_permission] - USING [dbo].[guacamole_user] affected + DELETE FROM [guacamole_user_permission] + USING [guacamole_user] affected WHERE - [dbo].[guacamole_user_permission].affected_user_id = affected.user_id - AND ([dbo].[guacamole_user_permission].user_id, permission, affected.username) IN + [guacamole_user_permission].affected_user_id = affected.user_id + AND ([guacamole_user_permission].user_id, permission, affected.username) IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> (#{permission.userID,jdbcType=INTEGER}, @@ -106,7 +106,7 @@ <!-- Insert all given permissions --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - INSERT INTO [dbo].[guacamole_user_permission] ( + INSERT INTO [guacamole_user_permission] ( user_id, permission, affected_user_id @@ -114,7 +114,7 @@ SELECT DISTINCT permissions.user_id, permissions.permission, - [dbo].[guacamole_user].user_id + [guacamole_user].user_id FROM <foreach collection="permissions" item="permission" open="(" separator="UNION ALL" close=")"> @@ -123,11 +123,11 @@ #{permission.objectIdentifier,jdbcType=INTEGER} AS username </foreach> AS permissions - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_user].username = permissions.username - WHERE NOT EXISTS (SELECT 1 FROM [dbo].[guacamole_user_permission] - WHERE [dbo].[guacamole_user_permission].user_id = permissions.user_id - AND [dbo].[guacamole_user_permission].permission = permissions.permission - AND [dbo].[guacamole_user_permission].affected_user_id = [dbo].[guacamole_user].user_id + JOIN [guacamole_user] ON [guacamole_user].username = permissions.username + WHERE NOT EXISTS (SELECT 1 FROM [guacamole_user_permission] + WHERE [guacamole_user_permission].user_id = permissions.user_id + AND [guacamole_user_permission].permission = permissions.permission + AND [guacamole_user_permission].affected_user_id = [guacamole_user].user_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml index d3b5c10..3b4ba09 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml @@ -33,13 +33,13 @@ <!-- Select all sharing profile identifiers --> <select id="selectIdentifiers" resultType="string"> SELECT sharing_profile_id - FROM [dbo].[guacamole_sharing_profile] + FROM [guacamole_sharing_profile] </select> <!-- Select identifiers of all readable sharing profiles --> <select id="selectReadableIdentifiers" resultType="string"> SELECT sharing_profile_id - FROM [dbo].[guacamole_sharing_profile_permission] + FROM [guacamole_sharing_profile_permission] WHERE user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ' @@ -52,7 +52,7 @@ sharing_profile_id, sharing_profile_name, primary_connection_id - FROM [dbo].[guacamole_sharing_profile] + FROM [guacamole_sharing_profile] WHERE sharing_profile_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -65,12 +65,12 @@ <select id="selectReadable" resultMap="SharingProfileResultMap"> SELECT - [dbo].[guacamole_sharing_profile].sharing_profile_id, - [dbo].[guacamole_sharing_profile].sharing_profile_name, + [guacamole_sharing_profile].sharing_profile_id, + [guacamole_sharing_profile].sharing_profile_name, primary_connection_id - FROM [dbo].[guacamole_sharing_profile] - JOIN [dbo].[guacamole_sharing_profile_permission] ON [dbo].[guacamole_sharing_profile_permission].sharing_profile_id = [dbo].[guacamole_sharing_profile].sharing_profile_id - WHERE [dbo].[guacamole_sharing_profile].sharing_profile_id IN + FROM [guacamole_sharing_profile] + JOIN [guacamole_sharing_profile_permission] ON [guacamole_sharing_profile_permission].sharing_profile_id = [guacamole_sharing_profile].sharing_profile_id + WHERE [guacamole_sharing_profile].sharing_profile_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} @@ -87,7 +87,7 @@ sharing_profile_id, sharing_profile_name, primary_connection_id - FROM [dbo].[guacamole_sharing_profile] + FROM [guacamole_sharing_profile] WHERE primary_connection_id = #{parentIdentifier,jdbcType=INTEGER} AND sharing_profile_name = #{name,jdbcType=VARCHAR} @@ -96,7 +96,7 @@ <!-- Delete single sharing profile by identifier --> <delete id="delete"> - DELETE FROM [dbo].[guacamole_sharing_profile] + DELETE FROM [guacamole_sharing_profile] WHERE sharing_profile_id = #{identifier,jdbcType=INTEGER} </delete> @@ -104,7 +104,7 @@ <insert id="insert" useGeneratedKeys="true" keyProperty="object.objectID" parameterType="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileModel"> - INSERT INTO [dbo].[guacamole_sharing_profile] ( + INSERT INTO [guacamole_sharing_profile] ( sharing_profile_name, primary_connection_id ) @@ -117,7 +117,7 @@ <!-- Update single sharing profile --> <update id="update" parameterType="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileModel"> - UPDATE [dbo].[guacamole_sharing_profile] + UPDATE [guacamole_sharing_profile] SET sharing_profile_name = #{object.name,jdbcType=VARCHAR}, primary_connection_id = #{object.parentIdentifier,jdbcType=INTEGER} WHERE sharing_profile_id = #{object.objectID,jdbcType=INTEGER} http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml index c902276..a4327b4 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml @@ -36,21 +36,21 @@ sharing_profile_id, parameter_name, parameter_value - FROM [dbo].[guacamole_sharing_profile_parameter] + FROM [guacamole_sharing_profile_parameter] WHERE sharing_profile_id = #{identifier,jdbcType=INTEGER} </select> <!-- Delete all parameters of a given sharing profile --> <delete id="delete"> - DELETE FROM [dbo].[guacamole_sharing_profile_parameter] + DELETE FROM [guacamole_sharing_profile_parameter] WHERE sharing_profile_id = #{identifier,jdbcType=INTEGER} </delete> <!-- Insert all given parameters --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileParameterModel"> - INSERT INTO [dbo].[guacamole_sharing_profile_parameter] ( + INSERT INTO [guacamole_sharing_profile_parameter] ( sharing_profile_id, parameter_name, parameter_value http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/73301901/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml index a9f44e6..562a747 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml @@ -35,16 +35,16 @@ <select id="select" resultMap="PasswordRecordResultMap"> SELECT - [dbo].[guacamole_user_password_history].user_id, - [dbo].[guacamole_user_password_history].password_hash, - [dbo].[guacamole_user_password_history].password_salt, - [dbo].[guacamole_user_password_history].password_date - FROM [dbo].[guacamole_user_password_history] - JOIN [dbo].[guacamole_user] ON [dbo].[guacamole_user_password_history].user_id = [dbo].[guacamole_user].user_id + [guacamole_user_password_history].user_id, + [guacamole_user_password_history].password_hash, + [guacamole_user_password_history].password_salt, + [guacamole_user_password_history].password_date + FROM [guacamole_user_password_history] + JOIN [guacamole_user] ON [guacamole_user_password_history].user_id = [guacamole_user].user_id WHERE - [dbo].[guacamole_user].username = #{username,jdbcType=VARCHAR} + [guacamole_user].username = #{username,jdbcType=VARCHAR} ORDER BY - [dbo].[guacamole_user_password_history].password_date DESC + [guacamole_user_password_history].password_date DESC LIMIT #{maxHistorySize} </select> @@ -52,7 +52,7 @@ <!-- Insert the given password record --> <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.user.PasswordRecordModel"> - INSERT INTO [dbo].[guacamole_user_password_history] ( + INSERT INTO [guacamole_user_password_history] ( user_id, password_hash, password_salt, @@ -65,10 +65,10 @@ #{record.passwordDate,jdbcType=TIMESTAMP} ); - DELETE FROM [dbo].[guacamole_user_password_history] + DELETE FROM [guacamole_user_password_history] WHERE password_history_id IN ( SELECT password_history_id - FROM [dbo].[guacamole_user_password_history] + FROM [guacamole_user_password_history] WHERE user_id = #{record.userID,jdbcType=INTEGER} ORDER BY password_date DESC OFFSET #{maxHistorySize}
