Repository: incubator-guacamole-client Updated Branches: refs/heads/master 128b0ca8e -> a5ebc0734
GUACAMOLE-345: Name all PostgreSQL indexes. 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/0ad7e4e1 Tree: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/tree/0ad7e4e1 Diff: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/diff/0ad7e4e1 Branch: refs/heads/master Commit: 0ad7e4e1a38750b697b7a6cfab42b454988ee8d5 Parents: 128b0ca Author: Michael Jumper <[email protected]> Authored: Sat Jul 22 21:41:58 2017 -0700 Committer: Michael Jumper <[email protected]> Committed: Sat Jul 22 21:41:58 2017 -0700 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 69 ++++++++++++++------ .../schema/upgrade/upgrade-pre-0.9.10.sql | 16 +++-- .../schema/upgrade/upgrade-pre-0.9.11.sql | 3 +- .../schema/upgrade/upgrade-pre-0.9.9.sql | 12 +++- 4 files changed, 71 insertions(+), 29 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/0ad7e4e1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql index f0c38c6..e4015d3 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql @@ -87,7 +87,8 @@ CREATE TABLE guacamole_connection_group ( ); -CREATE INDEX ON guacamole_connection_group(parent_id); +CREATE INDEX guacamole_connection_group_parent_id + ON guacamole_connection_group(parent_id); -- -- Table of connections. Each connection has a name, protocol, and @@ -127,7 +128,8 @@ CREATE TABLE guacamole_connection ( ); -CREATE INDEX ON guacamole_connection(parent_id); +CREATE INDEX guacamole_connection_parent_id + ON guacamole_connection(parent_id); -- -- Table of users. Each user has a unique username and a hashed password @@ -200,7 +202,8 @@ CREATE TABLE guacamole_sharing_profile ( ); -CREATE INDEX ON guacamole_sharing_profile(primary_connection_id); +CREATE INDEX guacamole_sharing_profile_primary_connection_id + ON guacamole_sharing_profile(primary_connection_id); -- -- Table of connection parameters. Each parameter is simply a name/value pair @@ -221,7 +224,8 @@ CREATE TABLE guacamole_connection_parameter ( ); -CREATE INDEX ON guacamole_connection_parameter(connection_id); +CREATE INDEX guacamole_connection_parameter_connection_id + ON guacamole_connection_parameter(connection_id); -- -- Table of sharing profile parameters. Each parameter is simply @@ -244,7 +248,8 @@ CREATE TABLE guacamole_sharing_profile_parameter ( ); -CREATE INDEX ON guacamole_sharing_profile_parameter(sharing_profile_id); +CREATE INDEX guacamole_sharing_profile_parameter_sharing_profile_id + ON guacamole_sharing_profile_parameter(sharing_profile_id); -- -- Table of connection permissions. Each connection permission grants a user @@ -269,8 +274,11 @@ CREATE TABLE guacamole_connection_permission ( ); -CREATE INDEX ON guacamole_connection_permission(connection_id); -CREATE INDEX ON guacamole_connection_permission(user_id); +CREATE INDEX guacamole_connection_permission_connection_id + ON guacamole_connection_permission(connection_id); + +CREATE INDEX guacamole_connection_permission_user_id + ON guacamole_connection_permission(user_id); -- -- Table of connection group permissions. Each group permission grants a user @@ -295,8 +303,11 @@ CREATE TABLE guacamole_connection_group_permission ( ); -CREATE INDEX ON guacamole_connection_group_permission(connection_group_id); -CREATE INDEX ON guacamole_connection_group_permission(user_id); +CREATE INDEX guacamole_connection_group_permission_connection_group_id + ON guacamole_connection_group_permission(connection_group_id); + +CREATE INDEX guacamole_connection_group_permission_user_id + ON guacamole_connection_group_permission(user_id); -- -- Table of sharing profile permissions. Each sharing profile permission grants @@ -321,8 +332,11 @@ CREATE TABLE guacamole_sharing_profile_permission ( ); -CREATE INDEX ON guacamole_sharing_profile_permission(sharing_profile_id); -CREATE INDEX ON guacamole_sharing_profile_permission(user_id); +CREATE INDEX guacamole_sharing_profile_permission_sharing_profile_id + ON guacamole_sharing_profile_permission(sharing_profile_id); + +CREATE INDEX guacamole_sharing_profile_permission_user_id + ON guacamole_sharing_profile_permission(user_id); -- -- Table of system permissions. Each system permission grants a user a @@ -342,7 +356,8 @@ CREATE TABLE guacamole_system_permission ( ); -CREATE INDEX ON guacamole_system_permission(user_id); +CREATE INDEX guacamole_system_permission_user_id + ON guacamole_system_permission(user_id); -- -- Table of user permissions. Each user permission grants a user access to @@ -367,8 +382,11 @@ CREATE TABLE guacamole_user_permission ( ); -CREATE INDEX ON guacamole_user_permission(affected_user_id); -CREATE INDEX ON guacamole_user_permission(user_id); +CREATE INDEX guacamole_user_permission_affected_user_id + ON guacamole_user_permission(affected_user_id); + +CREATE INDEX guacamole_user_permission_user_id + ON guacamole_user_permission(user_id); -- -- Table of connection history records. Each record defines a specific user's @@ -405,11 +423,20 @@ CREATE TABLE guacamole_connection_history ( ); -CREATE INDEX ON guacamole_connection_history(user_id); -CREATE INDEX ON guacamole_connection_history(connection_id); -CREATE INDEX ON guacamole_connection_history(sharing_profile_id); -CREATE INDEX ON guacamole_connection_history(start_date); -CREATE INDEX ON guacamole_connection_history(end_date); +CREATE INDEX guacamole_connection_history_user_id + ON guacamole_connection_history(user_id); + +CREATE INDEX guacamole_connection_history_connection_id + ON guacamole_connection_history(connection_id); + +CREATE INDEX guacamole_connection_history_sharing_profile_id + ON guacamole_connection_history(sharing_profile_id); + +CREATE INDEX guacamole_connection_history_start_date + ON guacamole_connection_history(start_date); + +CREATE INDEX guacamole_connection_history_end_date + ON guacamole_connection_history(end_date); -- -- User password history @@ -433,4 +460,6 @@ CREATE TABLE guacamole_user_password_history ( ); -CREATE INDEX ON guacamole_user_password_history(user_id); +CREATE INDEX guacamole_user_password_history_user_id + ON guacamole_user_password_history(user_id); + http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/0ad7e4e1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql index 60ffdca..9e41204 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.10.sql @@ -125,7 +125,8 @@ CREATE TABLE guacamole_sharing_profile ( ); -CREATE INDEX ON guacamole_sharing_profile(primary_connection_id); +CREATE INDEX guacamole_sharing_profile_primary_connection_id + ON guacamole_sharing_profile(primary_connection_id); -- -- Add table of sharing profile parameters @@ -145,7 +146,8 @@ CREATE TABLE guacamole_sharing_profile_parameter ( ); -CREATE INDEX ON guacamole_sharing_profile_parameter(sharing_profile_id); +CREATE INDEX guacamole_sharing_profile_parameter_sharing_profile_id + ON guacamole_sharing_profile_parameter(sharing_profile_id); -- -- Object-level permission table for sharing profiles @@ -169,8 +171,11 @@ CREATE TABLE guacamole_sharing_profile_permission ( ); -CREATE INDEX ON guacamole_sharing_profile_permission(sharing_profile_id); -CREATE INDEX ON guacamole_sharing_profile_permission(user_id); +CREATE INDEX guacamole_sharing_profile_permission_sharing_profile_id + ON guacamole_sharing_profile_permission(sharing_profile_id); + +CREATE INDEX guacamole_sharing_profile_permission_user_id + ON guacamole_sharing_profile_permission(user_id); -- -- Add new (optional) sharing profile ID and name columns to connection history @@ -187,4 +192,5 @@ ALTER TABLE guacamole_connection_history FOREIGN KEY (sharing_profile_id) REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE SET NULL; -CREATE INDEX ON guacamole_connection_history(sharing_profile_id); +CREATE INDEX guacamole_connection_history_sharing_profile_id + ON guacamole_connection_history(sharing_profile_id); http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/0ad7e4e1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql index fad3bb4..95d6372 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql @@ -51,4 +51,5 @@ CREATE TABLE guacamole_user_password_history ( ); -CREATE INDEX ON guacamole_user_password_history(user_id); +CREATE INDEX guacamole_user_password_history_user_id + ON guacamole_user_password_history(user_id); http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/0ad7e4e1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.9.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.9.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.9.sql index 41197fa..eab419b 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.9.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.9.sql @@ -21,6 +21,12 @@ -- Ensure history entry start/end dates are indexed. -- -CREATE INDEX ON guacamole_connection_history(start_date); -CREATE INDEX ON guacamole_connection_history(end_date); -CREATE INDEX guacamole_connection_history_search_index ON guacamole_connection_history(start_date, connection_id, user_id); +CREATE INDEX guacamole_connection_history_start_date + ON guacamole_connection_history(start_date); + +CREATE INDEX guacamole_connection_history_end_date + ON guacamole_connection_history(end_date); + +CREATE INDEX guacamole_connection_history_search_index + ON guacamole_connection_history(start_date, connection_id, user_id); +
