Github user mike-jumper commented on a diff in the pull request:
https://github.com/apache/incubator-guacamole-client/pull/182#discussion_r138404746
--- Diff:
extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
---
@@ -0,0 +1,558 @@
+/*
+ * 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.
+ */
+
+/**
+ * List for permission data type.
+ */
+CREATE RULE [guacamole_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');
+GO;
+
+/**
+ * The permission data type.
+ */
+CREATE TYPE [guacamole_permission] FROM [nvarchar](10) NOT NULL;
+
+/**
+ * The system permission data type.
+ */
+CREATE TYPE [guacamole_system_permission] FROM [nvarchar](32) NOT NULL;
+GO;
+
+/**
+ * The connection_group table stores organizational and balancing groups.
+ */
+SET ANSI_NULLS ON;
+SET QUOTED_IDENTIFIER ON;
+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)
+ WITH (PAD_INDEX = OFF,
+ STATISTICS_NORECOMPUTE = OFF,
+ IGNORE_DUP_KEY = OFF,
+ ALLOW_ROW_LOCKS = ON,
+ ALLOW_PAGE_LOCKS = ON)
+ 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];
+GO;
+
+/**
+ * The connection table, for storing connections and attributes.
+ */
+SET ANSI_NULLS ON;
+SET QUOTED_IDENTIFIER ON;
+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)
+ WITH (PAD_INDEX = OFF,
+ STATISTICS_NORECOMPUTE = OFF,
+ IGNORE_DUP_KEY = OFF,
+ ALLOW_ROW_LOCKS = ON,
+ ALLOW_PAGE_LOCKS = ON)
+ 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];
+GO;
+
+/**
+ * The user table stores user accounts, passwords, and properties.
+ */
+SET ANSI_NULLS ON;
+SET QUOTED_IDENTIFIER ON;
+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)
+ WITH (PAD_INDEX = OFF,
+ STATISTICS_NORECOMPUTE = OFF,
+ IGNORE_DUP_KEY = OFF,
+ ALLOW_ROW_LOCKS = ON,
+ ALLOW_PAGE_LOCKS = ON)
+ 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];
+GO;
+
+/**
+ * The sharing_profile table stores profiles that allow
+ * connections to be shared amongst multiple users.
+ */
+SET ANSI_NULLS ON;
+SET QUOTED_IDENTIFIER ON;
+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)
+ WITH (PAD_INDEX = OFF,
+ STATISTICS_NORECOMPUTE = OFF,
+ IGNORE_DUP_KEY = OFF,
+ ALLOW_ROW_LOCKS = ON,
+ ALLOW_PAGE_LOCKS = ON)
+ 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];
+GO;
+
+/**
+ * The connection_parameter table stores parameters for
+ * connection objects.
+ */
+SET ANSI_NULLS ON;
+SET QUOTED_IDENTIFIER ON;
+CREATE TABLE [guacamole_connection_parameter](
+ [connection_id] [int] NOT NULL,
+ [parameter_name] [nvarchar](128) NOT NULL,
+ [parameter_value] [nvarchar](max) NOT NULL,
--- End diff --
On recent versions of SQL Server, values up to 8000 are allowed, while a
`varchar(max)` indicates the value should can grow up to 2 GB. According to
https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql
(emphasis added):
> varchar [ ( n | max ) ] Variable-length, non-Unicode string data. n
defines the string length and can be a value from 1 through 8,000. **max
indicates that the maximum storage size is 2^31-1 bytes (2 GB)**. The storage
size is the actual length of the data entered + 2 bytes. The ISO synonyms for
varchar are charvarying or charactervarying.
Not sure if the definition of this has changed between various versions of
SQL Server, but assuming that's the case, that possibility may be another
reason to avoid `max`.
Are you testing primarily against SQL Server 2005 and its limit is 4000?
---
