GUACAMOLE-394: Determine last active date for users based on history table.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/8694bc80 Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/8694bc80 Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/8694bc80 Branch: refs/heads/master Commit: 8694bc802a753a9acd6950c5c7d88843df886cdc Parents: a3dd919 Author: Michael Jumper <mjum...@apache.org> Authored: Tue Sep 12 13:07:15 2017 -0700 Committer: Michael Jumper <mjum...@apache.org> Committed: Mon Dec 11 23:51:57 2017 -0800 ---------------------------------------------------------------------- .../guacamole/auth/jdbc/user/ModeledUser.java | 2 +- .../guacamole/auth/jdbc/user/UserModel.java | 32 ++++++++++++++++++++ .../guacamole/auth/jdbc/user/UserMapper.xml | 32 +++++++++++++------- .../guacamole/auth/jdbc/user/UserMapper.xml | 32 +++++++++++++------- .../guacamole/auth/jdbc/user/UserMapper.xml | 32 +++++++++++++------- 5 files changed, 96 insertions(+), 34 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/8694bc80/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java index fc43e36..7d80fac 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java @@ -796,7 +796,7 @@ public class ModeledUser extends ModeledDirectoryObject<UserModel> implements Us @Override public Date getLastActive() { - return null; + return getModel().getLastActive(); } @Override http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/8694bc80/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java index afaeb55..a4f9c7a 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java @@ -115,6 +115,12 @@ public class UserModel extends ObjectModel { private String organizationalRole; /** + * The date and time that this user was last active, or null if this user + * has never logged in. + */ + private Date lastActive; + + /** * Creates a new, empty user. */ public UserModel() { @@ -465,4 +471,30 @@ public class UserModel extends ObjectModel { this.organizationalRole = organizationalRole; } + /** + * Returns the date and time that this user was last active, or null if + * this user has never logged in. + * + * @return + * The date and time that this user was last active, or null if this + * user has never logged in. + */ + public Date getLastActive() { + return lastActive; + } + + /** + * Sets the date and time that this user was last active. This value is + * expected to be set automatically via queries, derived from user history + * records. It does NOT correspond to an actual column, and values set + * manually through invoking this function will not persist. + * + * @param lastActive + * The date and time that this user was last active, or null if this + * user has never logged in. + */ + public void setLastActive(Date lastActive) { + this.lastActive = lastActive; + } + } http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/8694bc80/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml index 4ab1182..c9e4f70 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml @@ -41,6 +41,7 @@ <result column="email_address" property="emailAddress" jdbcType="VARCHAR"/> <result column="organization" property="organization" jdbcType="VARCHAR"/> <result column="organizational_role" property="organizationalRole" jdbcType="VARCHAR"/> + <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/> </resultMap> <!-- Select all usernames --> @@ -63,8 +64,8 @@ <select id="select" resultMap="UserResultMap"> SELECT - user_id, - username, + guacamole_user.user_id, + guacamole_user.username, password_hash, password_salt, password_date, @@ -78,13 +79,16 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM guacamole_user - WHERE username IN + 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 </select> @@ -93,7 +97,7 @@ SELECT guacamole_user.user_id, - username, + guacamole_user.username, password_hash, password_salt, password_date, @@ -107,16 +111,19 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM guacamole_user JOIN guacamole_user_permission ON affected_user_id = guacamole_user.user_id - WHERE username IN + 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> AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ' + GROUP BY guacamole_user.user_id </select> @@ -124,8 +131,8 @@ <select id="selectOne" resultMap="UserResultMap"> SELECT - user_id, - username, + guacamole_user.user_id, + guacamole_user.username, password_hash, password_salt, password_date, @@ -139,10 +146,13 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM guacamole_user + LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id WHERE - username = #{username,jdbcType=VARCHAR} + guacamole_user.username = #{username,jdbcType=VARCHAR} + GROUP BY guacamole_user.user_id </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/8694bc80/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml index 569a8ac..c106a8f 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml @@ -41,6 +41,7 @@ <result column="email_address" property="emailAddress" jdbcType="VARCHAR"/> <result column="organization" property="organization" jdbcType="VARCHAR"/> <result column="organizational_role" property="organizationalRole" jdbcType="VARCHAR"/> + <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/> </resultMap> <!-- Select all usernames --> @@ -63,8 +64,8 @@ <select id="select" resultMap="UserResultMap"> SELECT - user_id, - username, + guacamole_user.user_id, + guacamole_user.username, password_hash, password_salt, password_date, @@ -78,13 +79,16 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM guacamole_user - WHERE username IN + 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 </select> @@ -93,7 +97,7 @@ SELECT guacamole_user.user_id, - username, + guacamole_user.username, password_hash, password_salt, password_date, @@ -107,16 +111,19 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM guacamole_user JOIN guacamole_user_permission ON affected_user_id = guacamole_user.user_id - WHERE username IN + 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> AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ' + GROUP BY guacamole_user.user_id </select> @@ -124,8 +131,8 @@ <select id="selectOne" resultMap="UserResultMap"> SELECT - user_id, - username, + guacamole_user.user_id, + guacamole_user.username, password_hash, password_salt, password_date, @@ -139,10 +146,13 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM guacamole_user + LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id WHERE - username = #{username,jdbcType=VARCHAR} + guacamole_user.username = #{username,jdbcType=VARCHAR} + GROUP BY guacamole_user.user_id </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/8694bc80/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 6df6cf2..ec60632 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 @@ -41,6 +41,7 @@ <result column="email_address" property="emailAddress" jdbcType="VARCHAR"/> <result column="organization" property="organization" jdbcType="VARCHAR"/> <result column="organizational_role" property="organizationalRole" jdbcType="VARCHAR"/> + <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/> </resultMap> <!-- Select all usernames --> @@ -63,8 +64,8 @@ <select id="select" resultMap="UserResultMap"> SELECT - user_id, - username, + [guacamole_user].user_id, + [guacamole_user].username, password_hash, password_salt, password_date, @@ -78,13 +79,16 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM [guacamole_user] - WHERE username IN + 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 </select> @@ -93,7 +97,7 @@ SELECT [guacamole_user].user_id, - username, + [guacamole_user].username, password_hash, password_salt, password_date, @@ -107,16 +111,19 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM [guacamole_user] JOIN [guacamole_user_permission] ON affected_user_id = [guacamole_user].user_id - WHERE username IN + 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> AND [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} AND permission = 'READ' + GROUP BY [guacamole_user].user_id </select> @@ -124,8 +131,8 @@ <select id="selectOne" resultMap="UserResultMap"> SELECT - user_id, - username, + [guacamole_user].user_id, + [guacamole_user].username, password_hash, password_salt, password_date, @@ -139,10 +146,13 @@ full_name, email_address, organization, - organizational_role + organizational_role, + MAX(start_date) AS last_active FROM [guacamole_user] + LEFT JOIN [guacamole_user_history] ON [guacamole_user_history].user_id = [guacamole_user].user_id WHERE - username = #{username,jdbcType=VARCHAR} + [guacamole_user].username = #{username,jdbcType=VARCHAR} + GROUP BY [guacamole_user].user_id </select>