GUACAMOLE-363: Fix up JDBC maps for proper SQL Server syntax.
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/75f51f22 Tree: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/tree/75f51f22 Diff: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/diff/75f51f22 Branch: refs/heads/master Commit: 75f51f2244ac537c70c24283586b35975abfb663 Parents: b6e88d3 Author: Nick Couchman <[email protected]> Authored: Tue Aug 15 14:28:20 2017 -0400 Committer: Nick Couchman <[email protected]> Committed: Thu Sep 28 07:00:52 2017 -0400 ---------------------------------------------------------------------- .../jdbc/connection/ConnectionRecordMapper.xml | 8 ++------ .../ConnectionGroupPermissionMapper.xml | 10 ++++------ .../permission/ConnectionPermissionMapper.xml | 20 +++++++++----------- .../SharingProfilePermissionMapper.xml | 20 +++++++++----------- .../jdbc/permission/SystemPermissionMapper.xml | 16 +++++++--------- .../jdbc/permission/UserPermissionMapper.xml | 10 ++++------ .../SharingProfileParameterMapper.xml | 2 +- 7 files changed, 36 insertions(+), 50 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/75f51f22/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml index ec077db..2b873bb 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml @@ -90,7 +90,7 @@ <!-- Search for specific connection records --> <select id="search" resultMap="ConnectionRecordResultMap"> - SELECT + SELECT TOP (#{limit,jdbcType=INTEGER}) [guacamole].[connection_history].connection_id, [guacamole].[connection_history].connection_name, [guacamole].[connection_history].remote_host, @@ -139,14 +139,12 @@ <if test="sortPredicate.descending">DESC</if> </foreach> - LIMIT #{limit,jdbcType=INTEGER} - </select> <!-- Search for specific connection records --> <select id="searchReadable" resultMap="ConnectionRecordResultMap"> - SELECT + SELECT TOP (#{limit,jdbcType=INTEGER}) [guacamole].[connection_history].connection_id, [guacamole].[connection_history].connection_name, [guacamole].[connection_history].remote_host, @@ -209,8 +207,6 @@ <if test="sortPredicate.descending">DESC</if> </foreach> - LIMIT #{limit,jdbcType=INTEGER} - </select> </mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/75f51f22/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml index 2890ab3..a799888 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml @@ -117,12 +117,10 @@ #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_group_id </foreach> AS permissions - WHERE (user_id, permission, connection_group_id) NOT IN ( - SELECT - [guacamole].[connection_group_permission].user_id, - [guacamole].[connection_group_permission].permission, - [guacamole].[connection_group_permission].connection_group_id - FROM [guacamole].[connection_group_permission] + WHERE NOT EXISTS (SELECT 1 FROM [guacamole].[connection_group_permission] + WHERE [guacamole].[connection_group_permission].user_id = permissions.user_id AND + [guacamole].[connection_group_permission].permission = permissions.permission AND + [guacamole].[connection_group_permission].connection_group_id = permissions.connection_group_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/75f51f22/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml index 8ea85bc..08e875c 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml @@ -87,12 +87,12 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> DELETE FROM [guacamole].[connection_permission] - WHERE (user_id, permission, connection_id) IN + WHERE <foreach collection="permissions" item="permission" - open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, - #{permission.type,jdbcType=VARCHAR}, - #{permission.objectIdentifier,jdbcType=INTEGER}) + open="(" separator=" OR " close=")"> + (user_id = #{permission.userID,jdbcType=INTEGER} AND + permission = #{permission.type,jdbcType=VARCHAR} AND + connection_id = #{permission.objectIdentifier,jdbcType=INTEGER}) </foreach> </delete> @@ -117,12 +117,10 @@ #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_id </foreach> AS permissions - WHERE (user_id, permission, connection_id) NOT IN ( - SELECT - [guacamole].[connection_permission].user_id, - [guacamole].[connection_permission].permission, - [guacamole].[connection_permission].connection_id - FROM [guacamole].[connection_permission] + WHERE NOT EXISTS ( SELECT 1 FROM [guacamole].[connection_permission] + WHERE [guacamole].[connection_permission].user_id = permissions.user_id AND + [guacamole].[connection_permission].permission = permissions.permission AND + [guacamole].[connection_permission].connection_id = permissions.connection_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/75f51f22/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml index cb706b8..354dfb5 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml @@ -87,12 +87,12 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> DELETE FROM [guacamole].[sharing_profile_permission] - WHERE (user_id, permission, sharing_profile_id) IN + WHERE <foreach collection="permissions" item="permission" - open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, - #{permission.type,jdbcType=VARCHAR}, - #{permission.objectIdentifier,jdbcType=INTEGER}) + open="(" separator=" OR " close=")"> + (user_id = #{permission.userID,jdbcType=INTEGER} AND + permission = #{permission.type,jdbcType=VARCHAR} AND + sharing_profile_id = #{permission.objectIdentifier,jdbcType=INTEGER}) </foreach> </delete> @@ -117,12 +117,10 @@ #{permission.objectIdentifier,jdbcType=INTEGER} AS sharing_profile_id </foreach> AS permissions - WHERE (user_id, permission, sharing_profile_id) NOT IN ( - SELECT - [guacamole].[sharing_profile_permission].user_id, - [guacamole].[sharing_profile_permission].permission, - [guacamole].[sharing_profile_permission].sharing_profile_id - FROM [guacamole].[sharing_profile_permission] + WHERE NOT EXISTS (SELECT 1 FROM [guacamole].[sharing_profile_permission] + WHERE [guacamole].[sharing_profile_permission].user_id = permissions.user_id + AND [guacamole].[sharing_profile_permission].permission = permissions.permission + AND [guacamole].[sharing_profile_permission].sharing_profile_id = permissions.sharing_profile_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/75f51f22/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml index d9e622b..0488779 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml @@ -63,11 +63,11 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> DELETE FROM [guacamole].[system_permission] - WHERE (user_id, permission) IN + WHERE <foreach collection="permissions" item="permission" - open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, - #{permission.type,jdbcType=VARCHAR}) + open="(" separator=" OR " close=")"> + (user_id = #{permission.userID,jdbcType=INTEGER} + AND permission = #{permission.type,jdbcType=VARCHAR}) </foreach> </delete> @@ -89,11 +89,9 @@ #{permission.type,jdbcType=VARCHAR} AS permission </foreach> AS permissions - WHERE (user_id, permission) NOT IN ( - SELECT - [guacamole].[system_permission].user_id, - [guacamole].[system_permission].permission - FROM [guacamole].[system_permission] + WHERE NOT EXISTS (SELECT 1 FROM [guacamole].[system_permission] + WHERE [guacamole].[system_permission].user_id = permissions.user_id + AND [guacamole].[system_permission].permission = permissions.permission ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/75f51f22/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml index 595c326..606719d 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml @@ -124,12 +124,10 @@ </foreach> AS permissions JOIN [guacamole].[user] ON [guacamole].[user].username = permissions.username - WHERE (permissions.user_id, permissions.permission, [guacamole].[user].user_id) NOT IN ( - SELECT - [guacamole].[user_permission].user_id, - [guacamole].[user_permission].permission, - [guacamole].[user_permission].affected_user_id - FROM [guacamole].[user_permission] + WHERE NOT EXISTS (SELECT 1 FROM [guacamole].[user_permission] + WHERE [guacamole].[user_permission].user_id = permissions.user_id + AND [guacamole].[user_permission].permission = permissions.permission + AND [guacamole].[user_permission].affected_user_id = [guacamole].[user].user_id ); </insert> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/75f51f22/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml index 8835350..8f4a3fa 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml @@ -57,7 +57,7 @@ ) VALUES <foreach collection="parameters" item="parameter" separator=","> - (#{parameter.sharingProfileIdentifier,jdbcType=INTEGER} + (#{parameter.sharingProfileIdentifier,jdbcType=INTEGER}, #{parameter.name,jdbcType=VARCHAR}, #{parameter.value,jdbcType=VARCHAR}) </foreach>
