GUACAMOLE-363: Clean up and refactor SQL Server schema to closely match the 
MySQL and PostgreSQL schemas. Add missing types and indexes.


Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/317c76b5
Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/317c76b5
Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/317c76b5

Branch: refs/heads/master
Commit: 317c76b5c5c38fe2deed02c7681f43f797b85922
Parents: 442fd3d
Author: Michael Jumper <mjum...@apache.org>
Authored: Sat Nov 25 17:19:27 2017 -0800
Committer: Michael Jumper <mjum...@apache.org>
Committed: Sun Nov 26 13:01:48 2017 -0800

----------------------------------------------------------------------
 .../schema/001-create-schema.sql                | 930 ++++++++++---------
 .../schema/002-create-admin-user.sql            |  93 +-
 2 files changed, 561 insertions(+), 462 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/317c76b5/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 f7a34b1..fd576e5 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
@@ -1,468 +1,556 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-/**
- * Turn on ANSI_NULLS for the entire DB to make it ISO-compliant.
- */
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+
+--
+-- Turn on ANSI_NULLS for the entire DB to make it ISO-compliant.
+--
+
 ALTER DATABASE CURRENT SET ANSI_NULLS ON;
 GO
 
-/**
- * Turn on QUOTED_IDENTIFIER for the entire DB.
- */
+--
+-- Turn on QUOTED_IDENTIFIER for the entire DB.
+--
+
 ALTER DATABASE CURRENT SET QUOTED_IDENTIFIER ON;
 GO
 
-/**
- * List for permission data type.
- */
-CREATE RULE [guacamole_permission_list] 
-    AS
-    @list IN ('READ','UPDATE','DELETE','ADMINISTER');
+--
+-- Connection group types
+--
+
+CREATE RULE [guacamole_connection_group_type_list] AS @list IN (
+    'ORGANIZATIONAL',
+    'BALANCING'
+);
+GO
+
+CREATE TYPE [guacamole_connection_group_type] FROM [nvarchar](16) NOT NULL;
+EXEC sp_bindrule
+    'guacamole_connection_group_type_list',
+    'guacamole_connection_group_type';
+GO
+
+--
+-- Object permission types
+--
+
+CREATE RULE [guacamole_object_permission_list] AS @list IN (
+    'READ',
+    'UPDATE',
+    'DELETE',
+    'ADMINISTER'
+);
 GO
 
-/**
- * List for system permission data type.
- */
-CREATE RULE [guacamole_system_permission_list] 
-    AS
-    @list IN ('CREATE_CONNECTION',
-              'CREATE_CONNECTION_GROUP',
-              'CREATE_SHARING_PROFILE',
-              'CREATE_USER',
-              'ADMINISTER');
+CREATE TYPE [guacamole_object_permission] FROM [nvarchar](16) NOT NULL;
+EXEC sp_bindrule
+    'guacamole_object_permission_list',
+    'guacamole_object_permission';
 GO
 
-/**
- * The permission data type.
- */
-CREATE TYPE [guacamole_permission] FROM [nvarchar](10) NOT NULL;
-EXEC sp_bindrule 'guacamole_permission_list','guacamole_permission';
+--
+-- System permission types
+--
+
+CREATE RULE [guacamole_system_permission_list] AS @list IN (
+    'CREATE_CONNECTION',
+    'CREATE_CONNECTION_GROUP',
+    'CREATE_SHARING_PROFILE',
+    'CREATE_USER',
+    'ADMINISTER'
+);
+GO
 
-/**
- * The system permission data type.
- */
 CREATE TYPE [guacamole_system_permission] FROM [nvarchar](32) NOT NULL;
-EXEC sp_bindrule 
'guacamole_system_permission_list','guacamole_system_permission';
+EXEC sp_bindrule
+    'guacamole_system_permission_list',
+    'guacamole_system_permission';
 GO
 
-/**
- * The connection_group table stores organizational and balancing groups.
- */
-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,
-    [type]                     [nvarchar](32) NOT NULL,
-    [max_connections]          [int] NULL,
-    [max_connections_per_user] [int] NULL,
-    [enable_session_affinity]  [bit] NOT NULL,
-
-    CONSTRAINT [PK_guacmaole_connection_group] PRIMARY KEY CLUSTERED
-               ([connection_group_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for connection_group table.
- */
-ALTER TABLE [guacamole_connection_group]
-    WITH CHECK ADD CONSTRAINT 
[FK_guacamole_connection_group_connection_group_id] FOREIGN KEY([parent_id])
-    REFERENCES                [guacamole_connection_group] 
([connection_group_id]);
-ALTER TABLE [guacamole_connection_group]
-    CHECK CONSTRAINT [FK_guacamole_connection_group_connection_group_id];
-ALTER TABLE [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_guacamole_connection_group_type];
-
-/**
- * Default values for connection_group table.
- */
-ALTER TABLE [guacamole_connection_group]
-    ADD CONSTRAINT [DF_guacamole_connection_group_type] DEFAULT 
(N'ORGANIZATIONAL') FOR [type];
-ALTER TABLE [guacamole_connection_group]
-    ADD CONSTRAINT [DF_guacamole_connection_group_enable_session_affinity] 
DEFAULT ((0)) FOR [enable_session_affinity];
+--
+-- Guacamole proxy (guacd) encryption methods.
+--
+
+CREATE RULE [guacamole_proxy_encryption_method_list] AS @list IN (
+    'NONE',
+    'SSL'
+);
 GO
 
-/**
- * The connection table, for storing connections and attributes.
- */
-CREATE TABLE [guacamole_connection](
-    [connection_id]            [int] IDENTITY(1,1) NOT NULL,
-    [connection_name]          [nvarchar](128) NOT NULL,
-    [parent_id]                [int] NULL,
-    [protocol]                 [nvarchar](32) NOT NULL,
-    [proxy_port]               [int] NULL,
-    [proxy_hostname]           [nvarchar](512) NULL,
-    [proxy_encryption_method]  [nvarchar](4) NULL,
-    [max_connections]          [int] NULL,
-    [max_connections_per_user] [int] NULL,
-    [connection_weight]        [int] NULL,
-    [failover_only]            [bit] NOT NULL,
-
-    CONSTRAINT [PK_guacamole_connection] PRIMARY KEY CLUSTERED
-       ([connection_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-ALTER TABLE [guacamole_connection]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_connection_group] 
FOREIGN KEY([parent_id])
-    REFERENCES [guacamole_connection_group] ([connection_group_id]);
-ALTER TABLE [guacamole_connection]
-    CHECK CONSTRAINT [FK_guacamole_connection_connection_group];
-ALTER TABLE [guacamole_connection]
-    WITH CHECK ADD CONSTRAINT [CK_proxy_encryption_method]
-    CHECK  (([proxy_encryption_method]='SSL' OR 
[proxy_encryption_method]='NONE'));
-ALTER TABLE [guacamole_connection]
-    CHECK CONSTRAINT [CK_proxy_encryption_method];
-ALTER TABLE [guacamole_connection]
-    ADD CONSTRAINT [DF_guacamole_connection_failover_only] DEFAULT ((0)) FOR 
[failover_only];
+CREATE TYPE [guacamole_proxy_encryption_method] FROM [nvarchar](8) NOT NULL;
+EXEC sp_bindrule
+    'guacamole_proxy_encryption_method_list',
+    'guacamole_proxy_encryption_method';
+GO
+
+--
+-- Table of connection groups. Each connection group has a name, type, and
+-- optional parent connection group.
+--
+
+CREATE TABLE [guacamole_connection_group] (
+
+    [connection_group_id]   [int] IDENTITY(1,1) NOT NULL,
+    [parent_id]             [int],
+    [connection_group_name] [nvarchar](128)     NOT NULL,
+    [type]                  [guacamole_connection_group_type]
+                            NOT NULL DEFAULT 'ORGANIZATIONAL',
+
+    -- Concurrency limits
+    [max_connections]          [int],
+    [max_connections_per_user] [int],
+    [enable_session_affinity]  [bit] NOT NULL DEFAULT 0,
+
+    CONSTRAINT [PK_guacamole_connection_group]
+        PRIMARY KEY CLUSTERED ([connection_group_id]),
+
+    CONSTRAINT [AK_guacamole_connection_group_name_parent]
+        UNIQUE ([connection_group_name], [parent_id]),
+
+    -- NOTE: SQL Server does not allow ON DELETE CASCADE for this column
+    -- due to the potential for cycles. Lacking this, deletion of connection
+    -- groups will fail if they contain other connection groups.
+    CONSTRAINT [FK_guacamole_connection_group_parent_id]
+        FOREIGN KEY ([parent_id])
+        REFERENCES [guacamole_connection_group] ([connection_group_id])
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_group_parent_id]
+    ON [guacamole_connection_group] ([parent_id]);
+GO
+
+--
+-- Table of connections. Each connection has a name, protocol, and
+-- associated set of parameters. A connection may belong to a connection group.
+--
+
+CREATE TABLE [guacamole_connection] (
+
+    [connection_id]   [int] IDENTITY(1,1) NOT NULL,
+    [connection_name] [nvarchar](128)     NOT NULL,
+    [parent_id]       [int],
+    [protocol]        [nvarchar](32)      NOT NULL,
+
+    -- Concurrency limits
+    [max_connections]          [int],
+    [max_connections_per_user] [int],
+
+    -- Connection Weight
+    [connection_weight] [int],
+    [failover_only]     [bit] NOT NULL DEFAULT 0,
+
+    -- Guacamole proxy (guacd) overrides
+    [proxy_port]              [int],
+    [proxy_hostname]          [nvarchar](512),
+    [proxy_encryption_method] [guacamole_proxy_encryption_method],
+
+    CONSTRAINT [PK_guacamole_connection]
+        PRIMARY KEY CLUSTERED ([connection_id]),
+
+    CONSTRAINT [AK_guacamole_connection_name_parent]
+        UNIQUE ([connection_name], [parent_id]),
+
+    CONSTRAINT [FK_guacamole_connection_parent_id]
+        FOREIGN KEY ([parent_id])
+        REFERENCES [guacamole_connection_group] ([connection_group_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_parent_id]
+    ON [guacamole_connection] ([parent_id]);
 GO
 
-/**
- * The user table stores user accounts, passwords, and properties.
- */
-CREATE TABLE [guacamole_user](
-    [user_id]               [int] IDENTITY(1,1) NOT NULL,
-    [username]              [nvarchar](128) NOT NULL,
-    [password_hash]         [binary](32) NOT NULL,
-    [password_salt]         [binary](32) NULL,
-    [password_date]         [datetime] NOT NULL,
-    [disabled]              [bit] NOT NULL,
-    [expired]               [bit] NOT NULL,
-    [access_window_start]   [time](7) NULL,
-    [access_window_end]     [time](7) NULL,
-    [valid_from]            [date] NULL,
-    [valid_until]           [date] NULL,
-    [timezone]              [nvarchar](64) NULL,
-    [full_name]             [nvarchar](256) NULL,
-    [email_address]         [nvarchar](256) NULL,
-    [organization]          [nvarchar](256) NULL,
-    [organizational_role]   [nvarchar](256) NULL,
-
-    CONSTRAINT [PK_guacamole_user] PRIMARY KEY CLUSTERED 
-        ([user_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Defaults for user table
- */
-ALTER TABLE [guacamole_user]
-    ADD CONSTRAINT [DF_guacamole_user_disabled] DEFAULT ((0)) FOR [disabled];
-ALTER TABLE [guacamole_user]
-    ADD CONSTRAINT [DF_guacamole_user_expired] DEFAULT ((0)) FOR [expired];
+--
+-- Table of users. Each user has a unique username and a hashed password
+-- with corresponding salt. Although the authentication system will always set
+-- salted passwords, other systems may set unsalted passwords by simply not
+-- providing the salt.
+--
+
+CREATE TABLE [guacamole_user] (
+
+    [user_id] [int] IDENTITY(1,1) NOT NULL,
+
+    -- Username and optionally-salted password
+    [username]      [nvarchar](128) NOT NULL,
+    [password_hash] [binary](32)    NOT NULL,
+    [password_salt] [binary](32),
+    [password_date] [datetime]      NOT NULL,
+
+    -- Account disabled/expired status
+    [disabled] [bit] NOT NULL DEFAULT 0,
+    [expired]  [bit] NOT NULL DEFAULT 0,
+
+    -- Time-based access restriction
+    [access_window_start] [time](7),
+    [access_window_end]   [time](7),
+
+    -- Date-based access restriction
+    [valid_from]  [date],
+    [valid_until] [date],
+
+    -- Timezone used for all date/time comparisons and interpretation
+    [timezone] [nvarchar](64),
+
+    -- Profile information
+    [full_name]           [nvarchar](256),
+    [email_address]       [nvarchar](256),
+    [organization]        [nvarchar](256),
+    [organizational_role] [nvarchar](256),
+
+    CONSTRAINT [PK_guacamole_user]
+        PRIMARY KEY CLUSTERED ([user_id]),
+
+    CONSTRAINT [AK_guacamole_user_username]
+        UNIQUE ([username])
+
+);
 GO
 
-/**
- * The sharing_profile table stores profiles that allow
- * connections to be shared amongst multiple users.
- */
-CREATE TABLE [guacamole_sharing_profile](
+--
+-- Table of sharing profiles. Each sharing profile has a name, associated set
+-- of parameters, and a primary connection. The primary connection is the
+-- connection that the sharing profile shares, and the parameters dictate the
+-- restrictions/features which apply to the user joining the connection via the
+-- sharing profile.
+--
+
+CREATE TABLE [guacamole_sharing_profile] (
+
     [sharing_profile_id]    [int] IDENTITY(1,1) NOT NULL,
-    [sharing_profile_name]  [nvarchar](128) NOT NULL,
-    [primary_connection_id] [int] NOT NULL,
-
-    CONSTRAINT [PK_guacamole_sharing_profile] PRIMARY KEY CLUSTERED 
-        ([sharing_profile_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for sharing_profile table.
- */
-ALTER TABLE [guacamole_sharing_profile]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_connection] 
FOREIGN KEY([primary_connection_id])
-    REFERENCES [guacamole_connection] ([connection_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile]
-    CHECK CONSTRAINT [FK_guacamole_sharing_profile_connection];
+    [sharing_profile_name]  [nvarchar](128)     NOT NULL,
+    [primary_connection_id] [int]               NOT NULL,
+
+    CONSTRAINT [PK_guacamole_sharing_profile]
+        PRIMARY KEY CLUSTERED ([sharing_profile_id]),
+
+    CONSTRAINT [AK_guacamole_sharing_profile_name_primary_connection]
+        UNIQUE ([sharing_profile_name], [primary_connection_id]),
+
+    CONSTRAINT [FK_guacamole_sharing_profile_primary_connection_id]
+        FOREIGN KEY ([primary_connection_id])
+        REFERENCES [guacamole_connection] ([connection_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_primary_connection_id]
+    ON [guacamole_sharing_profile] ([primary_connection_id]);
 GO
 
-/**
- * The connection_parameter table stores parameters for
- * connection objects.
- */
-CREATE TABLE [guacamole_connection_parameter](
-    [connection_id]   [int] NOT NULL,
-    [parameter_name]  [nvarchar](128) NOT NULL,
+--
+-- Table of connection parameters. Each parameter is simply a name/value pair
+-- associated with a connection.
+--
+
+CREATE TABLE [guacamole_connection_parameter] (
+
+    [connection_id]   [int]            NOT NULL,
+    [parameter_name]  [nvarchar](128)  NOT NULL,
     [parameter_value] [nvarchar](4000) NOT NULL,
 
-    CONSTRAINT [PK_guacamole_connection_parameter] PRIMARY KEY CLUSTERED 
-        ([connection_id] ASC, [parameter_name] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the connection_parameter table.
- */
-ALTER TABLE [guacamole_connection_parameter]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_parameter_connection] 
FOREIGN KEY([connection_id])
-    REFERENCES [guacamole_connection] ([connection_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_parameter]
-    CHECK CONSTRAINT [FK_guacamole_connection_parameter_connection];
+    CONSTRAINT [PK_guacamole_connection_parameter]
+        PRIMARY KEY CLUSTERED ([connection_id], [parameter_name]),
+
+    CONSTRAINT [FK_guacamole_connection_parameter_connection_id]
+        FOREIGN KEY ([connection_id])
+        REFERENCES [guacamole_connection] ([connection_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_parameter_connection_id]
+    ON [guacamole_connection_parameter] ([connection_id]);
 GO
 
-/**
- * The sharing_profile_parameter table stores parameters
- * for sharing_profile objects.
- */
-CREATE TABLE [guacamole_sharing_profile_parameter](
-    [sharing_profile_id] [int] NOT NULL,
-    [parameter_name]     [nvarchar](128) NOT NULL,
+--
+-- Table of sharing profile parameters. Each parameter is simply
+-- name/value pair associated with a sharing profile. These parameters dictate
+-- the restrictions/features which apply to the user joining the associated
+-- connection via the sharing profile.
+--
+
+CREATE TABLE [guacamole_sharing_profile_parameter] (
+
+    [sharing_profile_id] [int]            NOT NULL,
+    [parameter_name]     [nvarchar](128)  NOT NULL,
     [parameter_value]    [nvarchar](4000) NOT NULL,
 
-    CONSTRAINT [PK_guacamole_sharing_profile_parameter] PRIMARY KEY CLUSTERED 
-        ([sharing_profile_id] ASC, [parameter_name] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the sharing_profile_parameter
- * table.
- */
-ALTER TABLE [guacamole_sharing_profile_parameter]
-    WITH CHECK ADD CONSTRAINT 
[FK_guacamole_sharing_profile_parameter_sharing_profile] FOREIGN 
KEY([sharing_profile_id])
-    REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile_parameter]
-    CHECK CONSTRAINT [FK_guacamole_sharing_profile_parameter_sharing_profile];
+    CONSTRAINT [PK_guacamole_sharing_profile_parameter]
+        PRIMARY KEY CLUSTERED ([sharing_profile_id], [parameter_name]),
+
+    CONSTRAINT [FK_guacamole_sharing_profile_parameter_connection_id]
+        FOREIGN KEY ([sharing_profile_id])
+        REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX 
[IX_guacamole_sharing_profile_parameter_sharing_profile_id]
+    ON [guacamole_sharing_profile_parameter] ([sharing_profile_id]);
 GO
 
-/**
- * The connection_permission table stores permission
- * mappings for connection objects.
- */
-CREATE TABLE [guacamole_connection_permission](
-    [user_id]       [int] NOT NULL,
-    [connection_id] [int] NOT NULL,
-    [permission]    [guacamole_permission] NOT NULL,
-
-    CONSTRAINT [PK_guacamole_connection_permission] PRIMARY KEY CLUSTERED 
-        ([user_id] ASC, [connection_id] ASC, [permission] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the connection_permission table.
- */
-ALTER TABLE [guacamole_connection_permission]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_permission_connection1] 
FOREIGN KEY([connection_id])
-    REFERENCES [guacamole_connection] ([connection_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_permission]
-    CHECK CONSTRAINT [FK_guacamole_connection_permission_connection1];
-ALTER TABLE [guacamole_connection_permission]
-    WITH CHECK ADD  CONSTRAINT [FK_guacamole_connection_permission_user1] 
FOREIGN KEY([user_id])
-    REFERENCES [guacamole_user] ([user_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_permission]
-    CHECK CONSTRAINT [FK_guacamole_connection_permission_user1];
+--
+-- Table of connection permissions. Each connection permission grants a user
+-- specific access to a connection.
+--
+
+CREATE TABLE [guacamole_connection_permission] (
+
+    [user_id]       [int]                         NOT NULL,
+    [connection_id] [int]                         NOT NULL,
+    [permission]    [guacamole_object_permission] NOT NULL,
+
+    CONSTRAINT [PK_guacamole_connection_permission]
+        PRIMARY KEY CLUSTERED  ([user_id], [connection_id], [permission]),
+
+    CONSTRAINT [FK_guacamole_connection_permission_connection_id]
+        FOREIGN KEY ([connection_id])
+        REFERENCES [guacamole_connection] ([connection_id])
+        ON DELETE CASCADE,
+
+    CONSTRAINT [FK_guacamole_connection_permission_user_id]
+        FOREIGN KEY ([user_id])
+        REFERENCES [guacamole_user] ([user_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_permission_connection_id]
+    ON [guacamole_connection_permission] ([connection_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_permission_user_id]
+    ON [guacamole_connection_permission] ([user_id]);
 GO
 
-/**
- * The connection_group_permission table stores permission mappings for
- * connection_group objects.
- */
-CREATE TABLE [guacamole_connection_group_permission](
-    [user_id]             [int] NOT NULL,
-    [connection_group_id] [int] 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) ON 
[PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the connection_group_permission table.
- */
-ALTER TABLE [guacamole_connection_group_permission] 
-    WITH CHECK ADD CONSTRAINT 
[FK_guacamole_connection_group_permission_connection_group] FOREIGN 
KEY([connection_group_id])
-    REFERENCES [guacamole_connection_group] ([connection_group_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_group_permission]
-    CHECK CONSTRAINT 
[FK_guacamole_connection_group_permission_connection_group];
-ALTER TABLE [guacamole_connection_group_permission]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_permission_user] 
FOREIGN KEY([user_id])
-    REFERENCES [guacamole_user] ([user_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_group_permission]
-    CHECK CONSTRAINT [FK_guacamole_connection_group_permission_user];
+--
+-- Table of connection group permissions. Each group permission grants a user
+-- specific access to a connection group.
+--
+
+CREATE TABLE [guacamole_connection_group_permission] (
+
+    [user_id]             [int]                         NOT NULL,
+    [connection_group_id] [int]                         NOT NULL,
+    [permission]          [guacamole_object_permission] NOT NULL,
+
+    CONSTRAINT [PK_guacamole_connection_group_permission]
+        PRIMARY KEY CLUSTERED ([user_id], [connection_group_id], [permission]),
+
+    CONSTRAINT [FK_guacamole_connection_group_permission_connection_group_id]
+        FOREIGN KEY ([connection_group_id])
+        REFERENCES [guacamole_connection_group] ([connection_group_id])
+        ON DELETE CASCADE,
+
+    CONSTRAINT [FK_guacamole_connection_group_permission_user_id]
+        FOREIGN KEY ([user_id])
+        REFERENCES [guacamole_user] ([user_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX 
[IX_guacamole_connection_group_permission_connection_group_id]
+    ON [guacamole_connection_group_permission] ([connection_group_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_group_permission_user_id]
+    ON [guacamole_connection_group_permission] ([user_id]);
 GO
 
-/**
- * The sharing_profile_permission table stores permission
- * mappings for sharing_profile objects.
- */
-CREATE TABLE [guacamole_sharing_profile_permission](
-    [user_id]            [int] NOT NULL,
-    [sharing_profile_id] [int] 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) ON 
[PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the sharing_profile_permission table.
- */
-ALTER TABLE [guacamole_sharing_profile_permission]
-    WITH CHECK ADD CONSTRAINT 
[FK_guacamole_sharing_profile_permission_sharing_profile] FOREIGN 
KEY([sharing_profile_id])
-    REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile_permission]
-    CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile];
-ALTER TABLE [guacamole_sharing_profile_permission]
-    WITH CHECK ADD  CONSTRAINT [FK_guacamole_sharing_profile_permission_user] 
FOREIGN KEY([user_id])
-    REFERENCES [guacamole_user] ([user_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile_permission]
-    CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_user];
+--
+-- Table of sharing profile permissions. Each sharing profile permission grants
+-- a user specific access to a sharing profile.
+--
+
+CREATE TABLE [guacamole_sharing_profile_permission] (
+
+    [user_id]            [int]                         NOT NULL,
+    [sharing_profile_id] [int]                         NOT NULL,
+    [permission]         [guacamole_object_permission] NOT NULL,
+
+    CONSTRAINT [PK_guacamole_sharing_profile_permission]
+        PRIMARY KEY CLUSTERED ([user_id], [sharing_profile_id], [permission]),
+
+    CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile_id]
+        FOREIGN KEY ([sharing_profile_id])
+        REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
+        ON DELETE CASCADE,
+
+    CONSTRAINT [FK_guacamole_sharing_profile_permission_user_id]
+        FOREIGN KEY ([user_id])
+        REFERENCES [guacamole_user] ([user_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX 
[IX_guacamole_sharing_profile_permission_sharing_profile_id]
+    ON [guacamole_sharing_profile_permission] ([sharing_profile_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_permission_user_id]
+    ON [guacamole_sharing_profile_permission] ([user_id]);
 GO
 
-/**
- * The system_permission table stores permission mappings
- * for system-level operations.
- */
-CREATE TABLE [guacamole_system_permission](
-    [user_id]    [int] NOT NULL,
+--
+-- Table of system permissions. Each system permission grants a user a
+-- system-level privilege of some kind.
+--
+
+CREATE TABLE [guacamole_system_permission] (
+
+    [user_id]    [int]                         NOT NULL,
     [permission] [guacamole_system_permission] NOT NULL,
 
-    CONSTRAINT [PK_guacamole_system_permission] PRIMARY KEY CLUSTERED 
-        ([user_id] ASC,        [permission] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for system_permission table.
- */
-ALTER TABLE [guacamole_system_permission]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_system_permission_user] FOREIGN 
KEY([user_id])
-    REFERENCES [guacamole_user] ([user_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_system_permission]
-    CHECK CONSTRAINT [FK_guacamole_system_permission_user];
+    CONSTRAINT [PK_guacamole_system_permission]
+        PRIMARY KEY CLUSTERED ([user_id], [permission]),
+
+    CONSTRAINT [FK_guacamole_system_permission_user_id]
+        FOREIGN KEY ([user_id])
+        REFERENCES [guacamole_user] ([user_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_system_permission_user_id]
+    ON [guacamole_system_permission] ([user_id]);
 GO
 
-/**
- * The user_permission table stores permission mappings
- * for users to other users.
- */
-CREATE TABLE [guacamole_user_permission](
-    [user_id]          [int] NOT NULL,
-    [affected_user_id] [int] NOT NULL,
-    [permission]       [guacamole_permission] NOT NULL,
-
-    CONSTRAINT [PK_guacamole_user_permission] PRIMARY KEY CLUSTERED 
-        ([user_id] ASC,        [affected_user_id] ASC, [permission] ASC) ON 
[PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for user_permission table.
- */
-ALTER TABLE [guacamole_user_permission]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user] FOREIGN 
KEY([user_id])
-    REFERENCES [guacamole_user] ([user_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_user_permission]
-    CHECK CONSTRAINT [FK_guacamole_user_permission_user];
-ALTER TABLE [guacamole_user_permission]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user1] FOREIGN 
KEY([affected_user_id])
-    REFERENCES [guacamole_user] ([user_id]);
-ALTER TABLE [guacamole_user_permission]
-    CHECK CONSTRAINT [FK_guacamole_user_permission_user1];
+--
+-- Table of user permissions. Each user permission grants a user access to
+-- another user (the "affected" user) for a specific type of operation.
+--
+
+CREATE TABLE [guacamole_user_permission] (
+
+    [user_id]          [int]                         NOT NULL,
+    [affected_user_id] [int]                         NOT NULL,
+    [permission]       [guacamole_object_permission] NOT NULL,
+
+    CONSTRAINT [PK_guacamole_user_permission]
+        PRIMARY KEY CLUSTERED ([user_id], [affected_user_id], [permission]),
+
+    -- NOTE: SQL Server does not allow ON DELETE CASCADE for this column
+    -- due to the potential for multiple deletion paths. Lacking this, deletion
+    -- of users associated with permissions granted to other users will fail.
+    CONSTRAINT [FK_guacamole_user_permission_affected_user_id]
+        FOREIGN KEY ([affected_user_id])
+        REFERENCES [guacamole_user] ([user_id]),
+
+    CONSTRAINT [FK_guacamole_user_permission_user_id]
+        FOREIGN KEY ([user_id])
+        REFERENCES [guacamole_user] ([user_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_user_id]
+    ON [guacamole_user_permission] ([user_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_affected_user_id]
+    ON [guacamole_user_permission] ([affected_user_id]);
 GO
 
-/**
- * The connection_history table stores records for historical
- * connections.
- */
-CREATE TABLE [guacamole_connection_history](
+--
+-- Table of connection history records. Each record defines a specific user's
+-- session, including the connection used, the start time, and the end time
+-- (if any).
+--
+
+CREATE TABLE [guacamole_connection_history] (
+
     [history_id]           [int] IDENTITY(1,1) NOT NULL,
-    [user_id]              [int] NULL,
-    [username]             [nvarchar](128) NOT NULL,
-    [remote_host]          [nvarchar](256) NULL,
-    [connection_id]        [int] NULL,
-    [connection_name]      [nvarchar](128) NOT NULL,
-    [sharing_profile_id]   [int] NULL,
-    [sharing_profile_name] [nvarchar](128) NULL,
-    [start_date]           [datetime] NOT NULL,
-    [end_date]             [datetime] NULL,
-
-    CONSTRAINT [PK_guacamole_connection_history] PRIMARY KEY CLUSTERED 
-        ([history_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for connection_history table
- */
-ALTER TABLE [guacamole_connection_history]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_connection] 
FOREIGN KEY([connection_id])
-    REFERENCES [guacamole_connection] ([connection_id])
-        ON UPDATE CASCADE
-        ON DELETE SET NULL;
-ALTER TABLE [guacamole_connection_history]
-    CHECK CONSTRAINT [FK_guacamole_connection_history_connection];
-ALTER TABLE [guacamole_connection_history]
-    WITH CHECK ADD CONSTRAINT 
[FK_guacamole_connection_history_sharing_profile] FOREIGN 
KEY([sharing_profile_id])
-    REFERENCES [guacamole_sharing_profile] ([sharing_profile_id]);
-ALTER TABLE [guacamole_connection_history]
-    CHECK CONSTRAINT [FK_guacamole_connection_history_sharing_profile];
-ALTER TABLE [guacamole_connection_history]
-    WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_user] FOREIGN 
KEY([user_id])
-    REFERENCES [guacamole_user] ([user_id])
-        ON UPDATE CASCADE
-        ON DELETE SET NULL;
-ALTER TABLE [guacamole_connection_history]
-    CHECK CONSTRAINT [FK_guacamole_connection_history_user];
+    [user_id]              [int],
+    [username]             [nvarchar](128)     NOT NULL,
+    [remote_host]          [nvarchar](256),
+    [connection_id]        [int],
+    [connection_name]      [nvarchar](128)     NOT NULL,
+    [sharing_profile_id]   [int],
+    [sharing_profile_name] [nvarchar](128),
+    [start_date]           [datetime]          NOT NULL,
+    [end_date]             [datetime],
+
+    CONSTRAINT [PK_guacamole_connection_history]
+        PRIMARY KEY CLUSTERED ([history_id]),
+
+    CONSTRAINT [FK_guacamole_connection_history_user_id]
+        FOREIGN KEY ([user_id])
+        REFERENCES [guacamole_user] ([user_id])
+        ON DELETE SET NULL,
+
+    CONSTRAINT [FK_guacamole_connection_history_connection_id]
+        FOREIGN KEY ([connection_id])
+        REFERENCES [guacamole_connection] ([connection_id])
+        ON DELETE SET NULL,
+
+    -- NOTE: SQL Server does not allow ON DELETE SET NULL for this column
+    -- due to the potential for multiple deletion paths. Lacking this, deletion
+    -- of sharing profiles which have previously been used (and thus have
+    -- existing history records) will fail.
+    CONSTRAINT [FK_guacamole_connection_history_sharing_profile_id]
+        FOREIGN KEY ([sharing_profile_id])
+        REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_user_id]
+    ON [guacamole_connection_history] ([user_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_connection_id]
+    ON [guacamole_connection_history] ([connection_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_sharing_profile_id]
+    ON [guacamole_connection_history] ([sharing_profile_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_start_date]
+    ON [guacamole_connection_history] ([start_date]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_end_date]
+    ON [guacamole_connection_history] ([end_date]);
 GO
 
-/**
- * The user_password_history table stores password history
- * for users, allowing for enforcing rules associated with
- * reuse of passwords.
- */
-CREATE TABLE [guacamole_user_password_history](
+--
+-- The user_password_history table stores password history
+-- for users, allowing for enforcing rules associated with
+-- reuse of passwords.
+--
+
+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,
-    [password_salt]       [binary](32) NULL,
-    [password_date]       [datetime] NOT NULL,
-
-    CONSTRAINT [PK_guacamole_user_password_history] PRIMARY KEY CLUSTERED 
-        ([password_history_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for user_password_history table
- */
-ALTER TABLE [guacamole_user_password_history]
-    WITH CHECK ADD  CONSTRAINT [FK_guacamole_user_password_history_user] 
FOREIGN KEY([user_id])
-    REFERENCES [guacamole_user] ([user_id])
-        ON UPDATE CASCADE
-        ON DELETE CASCADE;
-ALTER TABLE [guacamole_user_password_history]
-    CHECK CONSTRAINT [FK_guacamole_user_password_history_user];
-GO
+    [user_id]             [int]               NOT NULL,
+
+    -- Salted password
+    [password_hash] [binary](32) NOT NULL,
+    [password_salt] [binary](32),
+    [password_date] [datetime]   NOT NULL,
+
+    CONSTRAINT [PK_guacamole_user_password_history]
+        PRIMARY KEY CLUSTERED ([password_history_id]),
+
+    CONSTRAINT [FK_guacamole_user_password_history_user_id]
+        FOREIGN KEY ([user_id])
+        REFERENCES [guacamole_user] ([user_id])
+        ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_password_history_user_id]
+    ON [guacamole_user_password_history] ([user_id]);
+GO
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/317c76b5/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 4898e57..a5ecac0 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
@@ -1,49 +1,60 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
 
-/**
- * Create the default admin user account and set up full privileges.
- */
-INSERT INTO [guacamole_user] (username, password_hash, password_salt, 
password_date)
-VALUES ('guacadmin', 
+-- Create default user "guacadmin" with password "guacadmin"
+INSERT INTO [guacamole_user] (
+    [username],
+    [password_hash],
+    [password_salt],
+    [password_date]
+)
+VALUES (
+    'guacadmin',
     0xCA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960,
     0xFE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264,
-    getdate());
+    getdate()
+);
 
-INSERT INTO [guacamole_user_permission]
-SELECT [guacamole_user].[user_id], [affected].[user_id], permission
+-- Grant this user all system permissions
+INSERT INTO [guacamole_system_permission]
+SELECT
+    [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;
+          SELECT 'guacadmin', 'CREATE_CONNECTION'
+    UNION SELECT 'guacadmin', 'CREATE_CONNECTION_GROUP'
+    UNION SELECT 'guacadmin', 'CREATE_SHARING_PROFILE'
+    UNION SELECT 'guacadmin', 'CREATE_USER'
+    UNION SELECT 'guacadmin', 'ADMINISTER'
+) [permissions] ([username], [permission])
+JOIN [guacamole_user] ON [permissions].[username] = 
[guacamole_user].[username];
 
-INSERT INTO [guacamole_system_permission]
-SELECT user_id, permission
+INSERT INTO [guacamole_user_permission]
+SELECT
+    [guacamole_user].[user_id],
+    [affected].[user_id],
+    [permission]
 FROM (
-    SELECT 'guacadmin' AS username, 'CREATE_CONNECTION' AS permission
-        UNION SELECT 'guacadmin' AS username, 'CREATE_CONNECTION_GROUP' AS 
permission
-        UNION SELECT 'guacadmin' AS username, 'CREATE_SHARING_PROFILE' AS 
permission
-        UNION SELECT 'guacadmin' AS username, 'CREATE_USER' AS permission
-        UNION SELECT 'guacadmin' AS username, 'ADMINISTER' AS permission)
-    permissions
-    JOIN [guacamole_user] ON permissions.username = 
[guacamole_user].[username];
+          SELECT 'guacadmin', 'guacadmin', 'READ'
+    UNION SELECT 'guacadmin', 'guacadmin', 'UPDATE'
+    UNION SELECT 'guacadmin', 'guacadmin', 'ADMINISTER'
+) [permissions] ([username], [affected_username], [permission])
+JOIN [guacamole_user]          ON permissions.username = 
[guacamole_user].[username]
+JOIN [guacamole_user] [affected] ON permissions.affected_username = 
affected.username;
 GO

Reply via email to