GUACAMOLE-363: Make table names standard in schema creation scripts.
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/c803be5c Tree: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/tree/c803be5c Diff: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/diff/c803be5c Branch: refs/heads/master Commit: c803be5c7a847bebc55790cc9d37ac0da8a24445 Parents: 70c33ef Author: Nick Couchman <[email protected]> Authored: Sat Aug 26 21:07:58 2017 -0400 Committer: Nick Couchman <[email protected]> Committed: Thu Sep 28 07:00:52 2017 -0400 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 283 ++++++++++--------- .../schema/002-create-admin-user.sql | 12 +- 2 files changed, 148 insertions(+), 147 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/c803be5c/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 759e78d..44e498e 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 @@ -22,10 +22,11 @@ */ CREATE SCHEMA [guacamole] GO + /** * List for permission data type. */ -CREATE RULE [guacamole].[permission_list] +CREATE RULE [guacamole].[guacamole_permission_list] AS @list IN ('READ','UPDATE','DELETE','ADMINISTER') GO @@ -33,7 +34,7 @@ GO /** * List for system permission data type. */ -CREATE RULE [guacamole].[system_permission_list] +CREATE RULE [guacamole].[guacamole_system_permission_list] AS @list IN ('CREATE_CONNECTION', 'CREATE_CONNECTION_GROUP', @@ -45,12 +46,12 @@ GO /** * The permission data type. */ -CREATE TYPE [guacamole].[permission] FROM [nvarchar](10) NOT NULL +CREATE TYPE [guacamole].[guacamole_permission] FROM [nvarchar](10) NOT NULL /** * The system permission data type. */ -CREATE TYPE [guacamole].[system_permission] FROM [nvarchar](32) NOT NULL +CREATE TYPE [guacamole].[guacamole_system_permission] FROM [nvarchar](32) NOT NULL GO /** @@ -58,7 +59,7 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[connection_group]( +CREATE TABLE [guacamole].[guacamole_connection_group]( [connection_group_id] [int] IDENTITY(1,1) NOT NULL, [parent_id] [int] NULL, [connection_group_name] [nvarchar](128) NOT NULL, @@ -67,7 +68,7 @@ CREATE TABLE [guacamole].[connection_group]( [max_connections_per_user] [int] NULL, [enable_session_affinity] [bit] NOT NULL, - CONSTRAINT [PK_connection_group] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacmaole_connection_group] PRIMARY KEY CLUSTERED ([connection_group_id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -80,24 +81,24 @@ CREATE TABLE [guacamole].[connection_group]( /** * Foreign keys for connection_group table. */ -ALTER TABLE [guacamole].[connection_group] - WITH CHECK ADD CONSTRAINT [FK_connection_group_connection_group] FOREIGN KEY([parent_id]) - REFERENCES [guacamole].[connection_group] ([connection_group_id]) -ALTER TABLE [guacamole].[connection_group] - CHECK CONSTRAINT [FK_connection_group_connection_group] -ALTER TABLE [guacamole].[connection_group] - WITH CHECK ADD CONSTRAINT [CK_connection_group_type] +ALTER TABLE [guacamole].[guacamole_connection_group] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_connection_group_id] FOREIGN KEY([parent_id]) + REFERENCES [guacamole].[guacamole_connection_group] ([connection_group_id]) +ALTER TABLE [guacamole].[guacamole_connection_group] + CHECK CONSTRAINT [FK_guacamole_connection_group_connection_group_id] +ALTER TABLE [guacamole].[guacamole_connection_group] + WITH CHECK ADD CONSTRAINT [CK_guacamole_connection_group_type] CHECK (([type]='BALANCING' OR [type]='ORGANIZATIONAL')) -ALTER TABLE [guacamole].[connection_group] - CHECK CONSTRAINT [CK_connection_group_type] +ALTER TABLE [guacamole].[guacamole_connection_group] + CHECK CONSTRAINT [CK_guacamole_connection_group_type] /** * Default values for connection_group table. */ -ALTER TABLE [guacamole].[connection_group] - ADD CONSTRAINT [DF_connection_group_type] DEFAULT (N'ORGANIZATIONAL') FOR [type] -ALTER TABLE [guacamole].[connection_group] - ADD CONSTRAINT [DF_connection_group_enable_session_affinity] DEFAULT ((0)) FOR [enable_session_affinity] +ALTER TABLE [guacamole].[guacamole_connection_group] + ADD CONSTRAINT [DF_guacamole_connection_group_type] DEFAULT (N'ORGANIZATIONAL') FOR [type] +ALTER TABLE [guacamole].[guacamole_connection_group] + ADD CONSTRAINT [DF_guacamole_connection_group_enable_session_affinity] DEFAULT ((0)) FOR [enable_session_affinity] GO /** @@ -105,7 +106,7 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[connection]( +CREATE TABLE [guacamole].[guacamole_connection]( [connection_id] [int] IDENTITY(1,1) NOT NULL, [connection_name] [nvarchar](128) NOT NULL, [parent_id] [int] NULL, @@ -118,7 +119,7 @@ CREATE TABLE [guacamole].[connection]( [connection_weight] [int] NULL, [failover_only] [bit] NOT NULL, - CONSTRAINT [PK_connection] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_connection] PRIMARY KEY CLUSTERED ([connection_id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -128,18 +129,18 @@ CREATE TABLE [guacamole].[connection]( ON [PRIMARY] ) ON [PRIMARY] -ALTER TABLE [guacamole].[connection] - WITH CHECK ADD CONSTRAINT [FK_connection_connection_group] FOREIGN KEY([parent_id]) -REFERENCES [guacamole].[connection_group] ([connection_group_id]) -ALTER TABLE [guacamole].[connection] - CHECK CONSTRAINT [FK_connection_connection_group] -ALTER TABLE [guacamole].[connection] +ALTER TABLE [guacamole].[guacamole_connection] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_connection_group] FOREIGN KEY([parent_id]) +REFERENCES [guacamole].[guacamole_connection_group] ([connection_group_id]) +ALTER TABLE [guacamole].[guacamole_connection] + CHECK CONSTRAINT [FK_guacamole_connection_connection_group] +ALTER TABLE [guacamole].[guacamole_connection] WITH CHECK ADD CONSTRAINT [CK_proxy_encryption_method] CHECK (([proxy_encryption_method]='SSL' OR [proxy_encryption_method]='NONE')) -ALTER TABLE [guacamole].[connection] +ALTER TABLE [guacamole].[guacamole_connection] CHECK CONSTRAINT [CK_proxy_encryption_method] -ALTER TABLE [guacamole].[connection] - ADD CONSTRAINT [DF_connection_failover_only] DEFAULT ((0)) FOR [failover_only] +ALTER TABLE [guacamole].[guacamole_connection] + ADD CONSTRAINT [DF_guacamole_connection_failover_only] DEFAULT ((0)) FOR [failover_only] GO /** @@ -147,7 +148,7 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[user]( +CREATE TABLE [guacamole].[guacamole_user]( [user_id] [int] IDENTITY(1,1) NOT NULL, [username] [nvarchar](128) NOT NULL, [password_hash] [binary](32) NOT NULL, @@ -165,7 +166,7 @@ CREATE TABLE [guacamole].[user]( [organization] [nvarchar](256) NULL, [organizational_role] [nvarchar](256) NULL, - CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_user] PRIMARY KEY CLUSTERED ([user_id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -178,10 +179,10 @@ CREATE TABLE [guacamole].[user]( /** * Defaults for user table */ -ALTER TABLE [guacamole].[user] - ADD CONSTRAINT [DF_user_disabled] DEFAULT ((0)) FOR [disabled] -ALTER TABLE [guacamole].[user] - ADD CONSTRAINT [DF_user_expired] DEFAULT ((0)) FOR [expired] +ALTER TABLE [guacamole].[guacamole_user] + ADD CONSTRAINT [DF_guacamole_user_disabled] DEFAULT ((0)) FOR [disabled] +ALTER TABLE [guacamole].[guacamole_user] + ADD CONSTRAINT [DF_guacamole_user_expired] DEFAULT ((0)) FOR [expired] GO /** @@ -190,12 +191,12 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[sharing_profile]( +CREATE TABLE [guacamole].[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, - CONSTRAINT [PK_sharing_profile] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_sharing_profile] PRIMARY KEY CLUSTERED ([sharing_profile_id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -208,13 +209,13 @@ CREATE TABLE [guacamole].[sharing_profile]( /** * Foreign keys for sharing_profile table. */ -ALTER TABLE [guacamole].[sharing_profile] - WITH CHECK ADD CONSTRAINT [FK_sharing_profile_connection] FOREIGN KEY([primary_connection_id]) - REFERENCES [guacamole].[connection] ([connection_id]) +ALTER TABLE [guacamole].[guacamole_sharing_profile] + WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_connection] FOREIGN KEY([primary_connection_id]) + REFERENCES [guacamole].[guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[sharing_profile] - CHECK CONSTRAINT [FK_sharing_profile_connection] +ALTER TABLE [guacamole].[guacamole_sharing_profile] + CHECK CONSTRAINT [FK_guacamole_sharing_profile_connection] GO /** @@ -223,12 +224,12 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[connection_parameter]( +CREATE TABLE [guacamole].[guacamole_connection_parameter]( [connection_id] [int] NOT NULL, [parameter_name] [nvarchar](128) NOT NULL, [parameter_value] [nvarchar](max) NOT NULL, - CONSTRAINT [PK_connection_parameter] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_connection_parameter] PRIMARY KEY CLUSTERED ([connection_id] ASC, [parameter_name] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -241,13 +242,13 @@ CREATE TABLE [guacamole].[connection_parameter]( /** * Foreign keys for the connection_parameter table. */ -ALTER TABLE [guacamole].[connection_parameter] - WITH CHECK ADD CONSTRAINT [FK_connection_parameter_connection] FOREIGN KEY([connection_id]) - REFERENCES [guacamole].[connection] ([connection_id]) +ALTER TABLE [guacamole].[guacamole_connection_parameter] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_parameter_connection] FOREIGN KEY([connection_id]) + REFERENCES [guacamole].[guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[connection_parameter] - CHECK CONSTRAINT [FK_connection_parameter_connection] +ALTER TABLE [guacamole].[guacamole_connection_parameter] + CHECK CONSTRAINT [FK_guacamole_connection_parameter_connection] GO /** @@ -256,12 +257,12 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[sharing_profile_parameter]( +CREATE TABLE [guacamole].[guacamole_sharing_profile_parameter]( [sharing_profile_id] [int] NOT NULL, [parameter_name] [nvarchar](128) NOT NULL, [parameter_value] [nvarchar](max) NOT NULL, - CONSTRAINT [PK_sharing_profile_parameter] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_sharing_profile_parameter] PRIMARY KEY CLUSTERED ([sharing_profile_id] ASC, [parameter_name] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -275,13 +276,13 @@ CREATE TABLE [guacamole].[sharing_profile_parameter]( * Foreign keys for the sharing_profile_parameter * table. */ -ALTER TABLE [guacamole].[sharing_profile_parameter] - WITH CHECK ADD CONSTRAINT [FK_sharing_profile_parameter_sharing_profile] FOREIGN KEY([sharing_profile_id]) - REFERENCES [guacamole].[sharing_profile] ([sharing_profile_id]) +ALTER TABLE [guacamole].[guacamole_sharing_profile_parameter] + WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_parameter_sharing_profile] FOREIGN KEY([sharing_profile_id]) + REFERENCES [guacamole].[guacamole_sharing_profile] ([sharing_profile_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[sharing_profile_parameter] - CHECK CONSTRAINT [FK_sharing_profile_parameter_sharing_profile] +ALTER TABLE [guacamole].[guacamole_sharing_profile_parameter] + CHECK CONSTRAINT [FK_guacamole_sharing_profile_parameter_sharing_profile] GO /** @@ -290,12 +291,12 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[connection_permission]( +CREATE TABLE [guacamole].[guacamole_connection_permission]( [user_id] [int] NOT NULL, [connection_id] [int] NOT NULL, - [permission] [guacamole].[permission] NOT NULL, + [permission] [guacamole].[guacamole_permission] NOT NULL, - CONSTRAINT [PK_connection_permission] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_connection_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [connection_id] ASC, [permission] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -308,20 +309,20 @@ CREATE TABLE [guacamole].[connection_permission]( /** * Foreign keys for the connection_permission table. */ -ALTER TABLE [guacamole].[connection_permission] - WITH CHECK ADD CONSTRAINT [FK_connection_permission_connection1] FOREIGN KEY([connection_id]) - REFERENCES [guacamole].[connection] ([connection_id]) +ALTER TABLE [guacamole].[guacamole_connection_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_permission_connection1] FOREIGN KEY([connection_id]) + REFERENCES [guacamole].[guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[connection_permission] - CHECK CONSTRAINT [FK_connection_permission_connection1] -ALTER TABLE [guacamole].[connection_permission] - WITH CHECK ADD CONSTRAINT [FK_connection_permission_user1] FOREIGN KEY([user_id]) - REFERENCES [guacamole].[user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_connection_permission] + CHECK CONSTRAINT [FK_guacamole_connection_permission_connection1] +ALTER TABLE [guacamole].[guacamole_connection_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_permission_user1] FOREIGN KEY([user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[connection_permission] - CHECK CONSTRAINT [FK_connection_permission_user1] +ALTER TABLE [guacamole].[guacamole_connection_permission] + CHECK CONSTRAINT [FK_guacamole_connection_permission_user1] GO /** @@ -330,12 +331,12 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[connection_group_permission]( +CREATE TABLE [guacamole].[guacamole_connection_group_permission]( [user_id] [int] NOT NULL, [connection_group_id] [int] NOT NULL, - [permission] [guacamole].[permission] NOT NULL, + [permission] [guacamole].[guacamole_permission] NOT NULL, - CONSTRAINT [PK_connection_group_permission] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_connection_group_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [connection_group_id] ASC, [permission] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -348,20 +349,20 @@ CREATE TABLE [guacamole].[connection_group_permission]( /** * Foreign keys for the connection_group_permission table. */ -ALTER TABLE [guacamole].[connection_group_permission] - WITH CHECK ADD CONSTRAINT [FK_connection_group_permission_connection_group] FOREIGN KEY([connection_group_id]) - REFERENCES [guacamole].[connection_group] ([connection_group_id]) +ALTER TABLE [guacamole].[guacamole_connection_group_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_permission_connection_group] FOREIGN KEY([connection_group_id]) + REFERENCES [guacamole].[guacamole_connection_group] ([connection_group_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[connection_group_permission] - CHECK CONSTRAINT [FK_connection_group_permission_connection_group] -ALTER TABLE [guacamole].[connection_group_permission] - WITH CHECK ADD CONSTRAINT [FK_connection_group_permission_user] FOREIGN KEY([user_id]) - REFERENCES [guacamole].[user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_connection_group_permission] + CHECK CONSTRAINT [FK_guacamole_connection_group_permission_connection_group] +ALTER TABLE [guacamole].[guacamole_connection_group_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_permission_user] FOREIGN KEY([user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[connection_group_permission] - CHECK CONSTRAINT [FK_connection_group_permission_user] +ALTER TABLE [guacamole].[guacamole_connection_group_permission] + CHECK CONSTRAINT [FK_guacamole_connection_group_permission_user] GO /** @@ -370,12 +371,12 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[sharing_profile_permission]( +CREATE TABLE [guacamole].[guacamole_sharing_profile_permission]( [user_id] [int] NOT NULL, [sharing_profile_id] [int] NOT NULL, - [permission] [guacamole].[permission] NOT NULL, + [permission] [guacamole].[guacamole_permission] NOT NULL, - CONSTRAINT [PK_sharing_profile_permission] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_sharing_profile_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [sharing_profile_id] ASC, [permission] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -388,20 +389,20 @@ CREATE TABLE [guacamole].[sharing_profile_permission]( /** * Foreign keys for the sharing_profile_permission table. */ -ALTER TABLE [guacamole].[sharing_profile_permission] - WITH CHECK ADD CONSTRAINT [FK_sharing_profile_permission_sharing_profile] FOREIGN KEY([sharing_profile_id]) - REFERENCES [guacamole].[sharing_profile] ([sharing_profile_id]) +ALTER TABLE [guacamole].[guacamole_sharing_profile_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile] FOREIGN KEY([sharing_profile_id]) + REFERENCES [guacamole].[guacamole_sharing_profile] ([sharing_profile_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[sharing_profile_permission] - CHECK CONSTRAINT [FK_sharing_profile_permission_sharing_profile] -ALTER TABLE [guacamole].[sharing_profile_permission] - WITH CHECK ADD CONSTRAINT [FK_sharing_profile_permission_user] FOREIGN KEY([user_id]) - REFERENCES [guacamole].[user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_sharing_profile_permission] + CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile] +ALTER TABLE [guacamole].[guacamole_sharing_profile_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_permission_user] FOREIGN KEY([user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[sharing_profile_permission] - CHECK CONSTRAINT [FK_sharing_profile_permission_user] +ALTER TABLE [guacamole].[guacamole_sharing_profile_permission] + CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_user] GO /** @@ -410,11 +411,11 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[system_permission]( +CREATE TABLE [guacamole].[guacamole_system_permission]( [user_id] [int] NOT NULL, - [permission] [guacamole].[system_permission] NOT NULL, + [permission] [guacamole].[guacamole_system_permission] NOT NULL, - CONSTRAINT [PK_system_permission] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_system_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [permission] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -427,13 +428,13 @@ CREATE TABLE [guacamole].[system_permission]( /** * Foreign keys for system_permission table. */ -ALTER TABLE [guacamole].[system_permission] - WITH CHECK ADD CONSTRAINT [FK_system_permission_user] FOREIGN KEY([user_id]) - REFERENCES [guacamole].[user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_system_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_system_permission_user] FOREIGN KEY([user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[system_permission] - CHECK CONSTRAINT [FK_system_permission_user] +ALTER TABLE [guacamole].[guacamole_system_permission] + CHECK CONSTRAINT [FK_guacamole_system_permission_user] GO /** @@ -442,12 +443,12 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[user_permission]( +CREATE TABLE [guacamole].[guacamole_user_permission]( [user_id] [int] NOT NULL, [affected_user_id] [int] NOT NULL, - [permission] [guacamole].[permission] NOT NULL, + [permission] [guacamole].[guacamole_permission] NOT NULL, - CONSTRAINT [PK_user_permission] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_user_permission] PRIMARY KEY CLUSTERED ([user_id] ASC, [affected_user_id] ASC, [permission] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -460,18 +461,18 @@ CREATE TABLE [guacamole].[user_permission]( /** * Foreign keys for user_permission table. */ -ALTER TABLE [guacamole].[user_permission] - WITH CHECK ADD CONSTRAINT [FK_user_permission_user] FOREIGN KEY([user_id]) - REFERENCES [guacamole].[user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_user_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user] FOREIGN KEY([user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[user_permission] - CHECK CONSTRAINT [FK_user_permission_user] -ALTER TABLE [guacamole].[user_permission] - WITH CHECK ADD CONSTRAINT [FK_user_permission_user1] FOREIGN KEY([affected_user_id]) - REFERENCES [guacamole].[user] ([user_id]) -ALTER TABLE [guacamole].[user_permission] - CHECK CONSTRAINT [FK_user_permission_user1] +ALTER TABLE [guacamole].[guacamole_user_permission] + CHECK CONSTRAINT [FK_guacamole_user_permission_user] +ALTER TABLE [guacamole].[guacamole_user_permission] + WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user1] FOREIGN KEY([affected_user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_user_permission] + CHECK CONSTRAINT [FK_guacamole_user_permission_user1] GO /** @@ -480,7 +481,7 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[connection_history]( +CREATE TABLE [guacamole].[guacamole_connection_history]( [history_id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NULL, [username] [nvarchar](128) NOT NULL, @@ -492,7 +493,7 @@ CREATE TABLE [guacamole].[connection_history]( [start_date] [datetime] NOT NULL, [end_date] [datetime] NULL, - CONSTRAINT [PK_connection_history] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_connection_history] PRIMARY KEY CLUSTERED ([history_id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -505,25 +506,25 @@ CREATE TABLE [guacamole].[connection_history]( /** * Foreign keys for connection_history table */ -ALTER TABLE [guacamole].[connection_history] - WITH CHECK ADD CONSTRAINT [FK_connection_history_connection] FOREIGN KEY([connection_id]) - REFERENCES [guacamole].[connection] ([connection_id]) +ALTER TABLE [guacamole].[guacamole_connection_history] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_connection] FOREIGN KEY([connection_id]) + REFERENCES [guacamole].[guacamole_connection] ([connection_id]) ON UPDATE CASCADE ON DELETE SET NULL -ALTER TABLE [guacamole].[connection_history] - CHECK CONSTRAINT [FK_connection_history_connection] -ALTER TABLE [guacamole].[connection_history] - WITH CHECK ADD CONSTRAINT [FK_connection_history_sharing_profile] FOREIGN KEY([sharing_profile_id]) - REFERENCES [guacamole].[sharing_profile] ([sharing_profile_id]) -ALTER TABLE [guacamole].[connection_history] - CHECK CONSTRAINT [FK_connection_history_sharing_profile] -ALTER TABLE [guacamole].[connection_history] - WITH CHECK ADD CONSTRAINT [FK_connection_history_user] FOREIGN KEY([user_id]) - REFERENCES [guacamole].[user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_connection_history] + CHECK CONSTRAINT [FK_guacamole_connection_history_connection] +ALTER TABLE [guacamole].[guacamole_connection_history] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_sharing_profile] FOREIGN KEY([sharing_profile_id]) + REFERENCES [guacamole].[guacamole_sharing_profile] ([sharing_profile_id]) +ALTER TABLE [guacamole].[guacamole_connection_history] + CHECK CONSTRAINT [FK_guacamole_connection_history_sharing_profile] +ALTER TABLE [guacamole].[guacamole_connection_history] + WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_user] FOREIGN KEY([user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE SET NULL -ALTER TABLE [guacamole].[connection_history] - CHECK CONSTRAINT [FK_connection_history_user] +ALTER TABLE [guacamole].[guacamole_connection_history] + CHECK CONSTRAINT [FK_guacamole_connection_history_user] GO /** @@ -533,14 +534,14 @@ GO */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON -CREATE TABLE [guacamole].[user_password_history]( +CREATE TABLE [guacamole].[guacamole_user_password_history]( [password_history_id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [password_hash] [binary](32) NOT NULL, [password_salt] [binary](32) NULL, [password_date] [datetime] NOT NULL, - CONSTRAINT [PK_user_password_history] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_guacamole_user_password_history] PRIMARY KEY CLUSTERED ([password_history_id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, @@ -553,11 +554,11 @@ CREATE TABLE [guacamole].[user_password_history]( /** * Foreign keys for user_password_history table */ -ALTER TABLE [guacamole].[user_password_history] - WITH CHECK ADD CONSTRAINT [FK_user_password_history_user] FOREIGN KEY([user_id]) - REFERENCES [guacamole].[user] ([user_id]) +ALTER TABLE [guacamole].[guacamole_user_password_history] + WITH CHECK ADD CONSTRAINT [FK_guacamole_user_password_history_user] FOREIGN KEY([user_id]) + REFERENCES [guacamole].[guacamole_user] ([user_id]) ON UPDATE CASCADE ON DELETE CASCADE -ALTER TABLE [guacamole].[user_password_history] - CHECK CONSTRAINT [FK_user_password_history_user] +ALTER TABLE [guacamole].[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/c803be5c/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 f71d283..fa807b2 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,18 +20,18 @@ /** * Create the default admin user account and set up full privileges. */ -INSERT INTO [guacamole].[user] (username, password_hash, password_date) +INSERT INTO [guacamole].[guacamole_user] (username, password_hash, password_date) VALUES ('guacadmin', HASHBYTES('SHA2_256', 'guacadmin'), getdate()); -INSERT INTO [guacamole].[user_permission] -SELECT [guacamole].[user].[user_id], [affected].[user_id], permission +INSERT INTO [guacamole].[guacamole_user_permission] +SELECT [guacamole].[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 [guacamole].[user] ON permissions.username = [guacamole].[user].[username] - JOIN [guacamole].[user] affected ON permissions.affected_username = affected.username; + JOIN [guacamole].[guacamole_user] ON permissions.username = [guacamole].[guacamole_user].[username] + JOIN [guacamole].[guacamole_user] affected ON permissions.affected_username = affected.username; INSERT INTO [guacamole].[system_permission] SELECT user_id, 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 [guacamole].[user] ON permissions.username = [guacamole].[user].[username]; + JOIN [guacamole].[guacamole_user] ON permissions.username = [guacamole].[guacamole_user].[username]; GO
