GUACAMOLE-394: Use subquery for querying MAX() via SQL Server. Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/394a2898 Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/394a2898 Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/394a2898
Branch: refs/heads/master Commit: 394a289879dba9273f976a9174ad4eec45b674c2 Parents: dbd5b98 Author: Michael Jumper <mjum...@apache.org> Authored: Mon Dec 11 23:15:53 2017 -0800 Committer: Michael Jumper <mjum...@apache.org> Committed: Mon Dec 11 23:51:57 2017 -0800 ---------------------------------------------------------------------- .../auth/jdbc/connection/ConnectionMapper.xml | 28 ++++++++++++-------- .../guacamole/auth/jdbc/user/UserMapper.xml | 25 ++++++++++------- 2 files changed, 33 insertions(+), 20 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/394a2898/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml index 7e0e7fd..19c3912 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml @@ -101,15 +101,17 @@ proxy_encryption_method, connection_weight, failover_only, - MAX(start_date) AS last_active + ( + SELECT MAX(start_date) + FROM [guacamole_connection_history] + WHERE [guacamole_connection_history].connection_id = [guacamole_connection].connection_id + ) AS last_active FROM [guacamole_connection] - LEFT JOIN [guacamole_connection_history] ON [guacamole_connection_history].connection_id = [guacamole_connection].connection_id WHERE [guacamole_connection].connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} - </foreach> - GROUP BY [guacamole_connection].connection_id; + </foreach>; SELECT primary_connection_id, sharing_profile_id FROM [guacamole_sharing_profile] @@ -137,18 +139,20 @@ proxy_encryption_method, connection_weight, failover_only, - MAX(start_date) AS last_active + ( + SELECT MAX(start_date) + FROM [guacamole_connection_history] + WHERE [guacamole_connection_history].connection_id = [guacamole_connection].connection_id + ) AS last_active FROM [guacamole_connection] JOIN [guacamole_connection_permission] ON [guacamole_connection_permission].connection_id = [guacamole_connection].connection_id - LEFT JOIN [guacamole_connection_history] ON [guacamole_connection_history].connection_id = [guacamole_connection].connection_id WHERE [guacamole_connection].connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> AND [guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} - AND permission = 'READ' - GROUP BY [guacamole_connection].connection_id; + AND permission = 'READ'; SELECT primary_connection_id, [guacamole_sharing_profile].sharing_profile_id FROM [guacamole_sharing_profile] @@ -178,14 +182,16 @@ proxy_encryption_method, connection_weight, failover_only, - MAX(start_date) AS last_active + ( + SELECT MAX(start_date) + FROM [guacamole_connection_history] + WHERE [guacamole_connection_history].connection_id = [guacamole_connection].connection_id + ) AS last_active FROM [guacamole_connection] - LEFT JOIN [guacamole_connection_history] ON [guacamole_connection_history].connection_id = [guacamole_connection].connection_id WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> AND [guacamole_connection].connection_name = #{name,jdbcType=VARCHAR} - GROUP BY [guacamole_connection].connection_id </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/394a2898/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml index ec60632..24db013 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml @@ -80,15 +80,17 @@ email_address, organization, organizational_role, - MAX(start_date) AS last_active + ( + SELECT MAX(start_date) + FROM [guacamole_user_history] + WHERE [guacamole_user_history].user_id = [guacamole_user].user_id + ) AS last_active FROM [guacamole_user] - LEFT JOIN [guacamole_user_history] ON [guacamole_user_history].user_id = [guacamole_user].user_id WHERE [guacamole_user].username IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=VARCHAR} - </foreach> - GROUP BY [guacamole_user].user_id + </foreach>; </select> @@ -112,10 +114,13 @@ email_address, organization, organizational_role, - MAX(start_date) AS last_active + ( + SELECT MAX(start_date) + FROM [guacamole_user_history] + WHERE [guacamole_user_history].user_id = [guacamole_user].user_id + ) AS last_active FROM [guacamole_user] JOIN [guacamole_user_permission] ON affected_user_id = [guacamole_user].user_id - LEFT JOIN [guacamole_user_history] ON [guacamole_user_history].user_id = [guacamole_user].user_id WHERE [guacamole_user].username IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -123,7 +128,6 @@ </foreach> AND [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ' - GROUP BY [guacamole_user].user_id </select> @@ -147,12 +151,15 @@ email_address, organization, organizational_role, - MAX(start_date) AS last_active + ( + SELECT MAX(start_date) + FROM [guacamole_user_history] + WHERE [guacamole_user_history].user_id = [guacamole_user].user_id + ) AS last_active FROM [guacamole_user] LEFT JOIN [guacamole_user_history] ON [guacamole_user_history].user_id = [guacamole_user].user_id WHERE [guacamole_user].username = #{username,jdbcType=VARCHAR} - GROUP BY [guacamole_user].user_id </select>