GitHub user mike-jumper opened a pull request:
https://github.com/apache/guacamole-client/pull/212
GUACAMOLE-363: Bring SQL Sever schema to parity with MySQL/PostgreSQL.
Addressing issues discovered during the database portion of development
related to [GUACAMOLE-96](https://issues.apache.org/jira/browse/GUACAMOLE-96),
this change updates the SQL Server schema, adding missing types, adding indexes
necessary for efficient joins on foreign keys, and copying existing formatting
and documentation to ensure the various schemas are readily comparable.
Changes overall involved
* Matching the style and documentation of the PostgreSQL and MySQL schemas
as much as possible.
* Adding indexes for foreign keys where necessary for efficient joins
* Adding missing `UNIQUE` constraints
* Restoring the `ON DELETE CASCADE` and `ON DELETE SET NULL` functionality
that the application depends on (internal failures will occur during object
deletion otherwise), in some cases through triggers
Triggers replicating the behavior of the other databases' `ON DELETE` were
necessary due to SQL Server's limitations on handling `ON DELETE`. Some
combinations of columns with `ON DELETE` result in an error like the following:
> Introducing FOREIGN KEY constraint 'FK_Table' on table 'Table' may cause
cycles or multiple cascade paths.
> Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
FOREIGN KEY constraints.
In such cases, the only possible solutions are (1) implement the delete
100% in application logic or (2) manually implement the delete in a trigger.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/mike-jumper/guacamole-client fix-sqlserver
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/guacamole-client/pull/212.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #212
----
commit 317c76b5c5c38fe2deed02c7681f43f797b85922
Author: Michael Jumper <[email protected]>
Date: 2017-11-26T01:19:27Z
GUACAMOLE-363: Clean up and refactor SQL Server schema to closely match the
MySQL and PostgreSQL schemas. Add missing types and indexes.
commit 56da0b0c756f59b06f6647653388a5448fadb9bd
Author: Michael Jumper <[email protected]>
Date: 2017-11-26T03:57:31Z
GUACAMOLE-363: Add triggers to handle cascading deletes/updates where SQL
Server does not support doing so automatically via ON DELETE actions.
commit 23084625ab7d82ecd73197ff9078f4c822af62e3
Author: Michael Jumper <[email protected]>
Date: 2017-11-26T05:54:41Z
GUACAMOLE-363: Do not take behind-the-scenes trigger operations into
account when calculating the row counts for a query.
commit 1ec76b486aa0835c6ad088c379f2066cac52fb2c
Author: Michael Jumper <[email protected]>
Date: 2017-11-26T21:45:45Z
GUACAMOLE-363: Remove ALTER DATABASE CURRENT statements (not compatible
with SQL Server 2008).
----
---
