GUACAMOLE-394: Add mapper for user login records.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/6f6b4e5d Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/6f6b4e5d Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/6f6b4e5d Branch: refs/heads/master Commit: 6f6b4e5d960f01d934155336168b9395cf349100 Parents: 5aa2172 Author: Michael Jumper <[email protected]> Authored: Tue Sep 12 12:50:09 2017 -0700 Committer: Michael Jumper <[email protected]> Committed: Mon Dec 11 23:51:57 2017 -0800 ---------------------------------------------------------------------- .../jdbc/JDBCAuthenticationProviderModule.java | 2 + .../auth/jdbc/user/UserRecordMapper.java | 113 ++++++++++++ .../auth/jdbc/user/UserRecordMapper.xml | 173 +++++++++++++++++++ .../auth/jdbc/user/UserRecordMapper.xml | 173 +++++++++++++++++++ .../auth/jdbc/user/UserRecordMapper.xml | 173 +++++++++++++++++++ 5 files changed, 634 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/6f6b4e5d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java index c9274dc..0f72559 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java @@ -76,6 +76,7 @@ import org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileParameterMapp import org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileService; import org.apache.guacamole.auth.jdbc.tunnel.RestrictedGuacamoleTunnelService; import org.apache.guacamole.auth.jdbc.user.PasswordRecordMapper; +import org.apache.guacamole.auth.jdbc.user.UserRecordMapper; import org.mybatis.guice.MyBatisModule; import org.mybatis.guice.datasource.builtin.PooledDataSourceProvider; @@ -126,6 +127,7 @@ public class JDBCAuthenticationProviderModule extends MyBatisModule { addMapperClass(SharingProfilePermissionMapper.class); addMapperClass(UserMapper.class); addMapperClass(UserPermissionMapper.class); + addMapperClass(UserRecordMapper.class); // Bind core implementations of guacamole-ext classes bind(ActiveConnectionDirectory.class); http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/6f6b4e5d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.java ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.java new file mode 100644 index 0000000..68f0c94 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.java @@ -0,0 +1,113 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.guacamole.auth.jdbc.user; + +import java.util.Collection; +import java.util.List; +import org.apache.guacamole.auth.jdbc.base.ActivityRecordModel; +import org.apache.guacamole.auth.jdbc.base.ActivityRecordSearchTerm; +import org.apache.guacamole.auth.jdbc.base.ActivityRecordSortPredicate; +import org.apache.ibatis.annotations.Param; + +/** + * Mapper for user login activity records. + */ +public interface UserRecordMapper { + + /** + * Returns a collection of all user login records associated with the user + * having the given username. + * + * @param username + * The username of the user whose login records are to be retrieved. + * + * @return + * A collection of all user login records associated with the user + * having the given username. This collection will be empty if no such + * user exists. + */ + List<ActivityRecordModel> select(@Param("username") String username); + + /** + * Inserts the given user login record. + * + * @param record + * The user login record to insert. + * + * @return + * The number of rows inserted. + */ + int insert(@Param("record") ActivityRecordModel record); + + /** + * Searches for up to <code>limit</code> user login records that contain + * the given terms, sorted by the given predicates, regardless of whether + * the data they are associated with is is readable by any particular user. + * This should only be called on behalf of a system administrator. If + * records are needed by a non-administrative user who must have explicit + * read rights, use searchReadable() instead. + * + * @param terms + * The search terms that must match the returned records. + * + * @param sortPredicates + * A list of predicates to sort the returned records by, in order of + * priority. + * + * @param limit + * The maximum number of records that should be returned. + * + * @return + * The results of the search performed with the given parameters. + */ + List<ActivityRecordModel> search(@Param("terms") Collection<ActivityRecordSearchTerm> terms, + @Param("sortPredicates") List<ActivityRecordSortPredicate> sortPredicates, + @Param("limit") int limit); + + /** + * Searches for up to <code>limit</code> user login records that contain + * the given terms, sorted by the given predicates. Only records that are + * associated with data explicitly readable by the given user will be + * returned. If records are needed by a system administrator (who, by + * definition, does not need explicit read rights), use search() instead. + * + * @param user + * The user whose permissions should determine whether a record is + * returned. + * + * @param terms + * The search terms that must match the returned records. + * + * @param sortPredicates + * A list of predicates to sort the returned records by, in order of + * priority. + * + * @param limit + * The maximum number of records that should be returned. + * + * @return + * The results of the search performed with the given parameters. + */ + List<ActivityRecordModel> searchReadable(@Param("user") UserModel user, + @Param("terms") Collection<ActivityRecordSearchTerm> terms, + @Param("sortPredicates") List<ActivityRecordSortPredicate> sortPredicates, + @Param("limit") int limit); + +} http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/6f6b4e5d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml new file mode 100644 index 0000000..0467452 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml @@ -0,0 +1,173 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" + "http://mybatis.org/dtd/mybatis-3-mapper.dtd"; > + +<!-- + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. +--> + +<mapper namespace="org.apache.guacamole.auth.jdbc.user.UserRecordMapper" > + + <!-- Result mapper for system permissions --> + <resultMap id="UserRecordResultMap" type="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel"> + <result column="remote_host" property="remoteHost" jdbcType="VARCHAR"/> + <result column="user_id" property="userID" jdbcType="INTEGER"/> + <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="start_date" property="startDate" jdbcType="TIMESTAMP"/> + <result column="end_date" property="endDate" jdbcType="TIMESTAMP"/> + </resultMap> + + <!-- Select all user records from a given user --> + <select id="select" resultMap="UserRecordResultMap"> + + SELECT + guacamole_user_history.remote_host, + guacamole_user_history.user_id, + guacamole_user_history.username, + guacamole_user_history.start_date, + guacamole_user_history.end_date + FROM guacamole_user_history + JOIN guacamole_user ON guacamole_user_history.user_id = guacamole_user.user_id + WHERE + guacamole_user.username = #{username,jdbcType=VARCHAR} + ORDER BY + guacamole_user_history.start_date DESC, + guacamole_user_history.end_date DESC + + </select> + + <!-- Insert the given user record --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel"> + + INSERT INTO guacamole_user_history ( + remote_host, + user_id, + username, + start_date, + end_date + ) + VALUES ( + #{record.remoteHost,jdbcType=VARCHAR}, + (SELECT user_id FROM guacamole_user + WHERE username = #{record.username,jdbcType=VARCHAR}), + #{record.username,jdbcType=VARCHAR}, + #{record.startDate,jdbcType=TIMESTAMP}, + #{record.endDate,jdbcType=TIMESTAMP} + ) + + </insert> + + <!-- Search for specific user records --> + <select id="search" resultMap="UserRecordResultMap"> + + SELECT + guacamole_user_history.remote_host, + guacamole_user_history.user_id, + guacamole_user_history.username, + guacamole_user_history.start_date, + guacamole_user_history.end_date + FROM guacamole_user_history + + <!-- Search terms --> + <foreach collection="terms" item="term" + open="WHERE " separator=" AND "> + ( + + guacamole_user_history.user_id IN ( + SELECT user_id + FROM guacamole_user + WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + ) + + <if test="term.startDate != null and term.endDate != null"> + OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP} + </if> + + ) + </foreach> + + <!-- Bind sort property enum values for sake of readability --> + <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/> + + <!-- Sort predicates --> + <foreach collection="sortPredicates" item="sortPredicate" + open="ORDER BY " separator=", "> + <choose> + <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when> + <otherwise>1</otherwise> + </choose> + <if test="sortPredicate.descending">DESC</if> + </foreach> + + LIMIT #{limit,jdbcType=INTEGER} + + </select> + + <!-- Search for specific user records --> + <select id="searchReadable" resultMap="UserRecordResultMap"> + + SELECT + guacamole_user_history.remote_host, + guacamole_user_history.user_id, + guacamole_user_history.username, + guacamole_user_history.start_date, + guacamole_user_history.end_date + FROM guacamole_user_history + + <!-- Restrict to readable users --> + JOIN guacamole_user_permission ON + guacamole_user_history.user_id = guacamole_user_permission.affected_user_id + AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} + AND guacamole_user_permission.permission = 'READ' + + <!-- Search terms --> + <foreach collection="terms" item="term" + open="WHERE " separator=" AND "> + ( + + guacamole_user_history.user_id IN ( + SELECT user_id + FROM guacamole_user + WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + ) + + <if test="term.startDate != null and term.endDate != null"> + OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP} + </if> + + ) + </foreach> + + <!-- Bind sort property enum values for sake of readability --> + <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/> + + <!-- Sort predicates --> + <foreach collection="sortPredicates" item="sortPredicate" + open="ORDER BY " separator=", "> + <choose> + <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when> + <otherwise>1</otherwise> + </choose> + <if test="sortPredicate.descending">DESC</if> + </foreach> + + LIMIT #{limit,jdbcType=INTEGER} + + </select> + +</mapper> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/6f6b4e5d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml new file mode 100644 index 0000000..0467452 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml @@ -0,0 +1,173 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" + "http://mybatis.org/dtd/mybatis-3-mapper.dtd"; > + +<!-- + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. +--> + +<mapper namespace="org.apache.guacamole.auth.jdbc.user.UserRecordMapper" > + + <!-- Result mapper for system permissions --> + <resultMap id="UserRecordResultMap" type="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel"> + <result column="remote_host" property="remoteHost" jdbcType="VARCHAR"/> + <result column="user_id" property="userID" jdbcType="INTEGER"/> + <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="start_date" property="startDate" jdbcType="TIMESTAMP"/> + <result column="end_date" property="endDate" jdbcType="TIMESTAMP"/> + </resultMap> + + <!-- Select all user records from a given user --> + <select id="select" resultMap="UserRecordResultMap"> + + SELECT + guacamole_user_history.remote_host, + guacamole_user_history.user_id, + guacamole_user_history.username, + guacamole_user_history.start_date, + guacamole_user_history.end_date + FROM guacamole_user_history + JOIN guacamole_user ON guacamole_user_history.user_id = guacamole_user.user_id + WHERE + guacamole_user.username = #{username,jdbcType=VARCHAR} + ORDER BY + guacamole_user_history.start_date DESC, + guacamole_user_history.end_date DESC + + </select> + + <!-- Insert the given user record --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel"> + + INSERT INTO guacamole_user_history ( + remote_host, + user_id, + username, + start_date, + end_date + ) + VALUES ( + #{record.remoteHost,jdbcType=VARCHAR}, + (SELECT user_id FROM guacamole_user + WHERE username = #{record.username,jdbcType=VARCHAR}), + #{record.username,jdbcType=VARCHAR}, + #{record.startDate,jdbcType=TIMESTAMP}, + #{record.endDate,jdbcType=TIMESTAMP} + ) + + </insert> + + <!-- Search for specific user records --> + <select id="search" resultMap="UserRecordResultMap"> + + SELECT + guacamole_user_history.remote_host, + guacamole_user_history.user_id, + guacamole_user_history.username, + guacamole_user_history.start_date, + guacamole_user_history.end_date + FROM guacamole_user_history + + <!-- Search terms --> + <foreach collection="terms" item="term" + open="WHERE " separator=" AND "> + ( + + guacamole_user_history.user_id IN ( + SELECT user_id + FROM guacamole_user + WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + ) + + <if test="term.startDate != null and term.endDate != null"> + OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP} + </if> + + ) + </foreach> + + <!-- Bind sort property enum values for sake of readability --> + <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/> + + <!-- Sort predicates --> + <foreach collection="sortPredicates" item="sortPredicate" + open="ORDER BY " separator=", "> + <choose> + <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when> + <otherwise>1</otherwise> + </choose> + <if test="sortPredicate.descending">DESC</if> + </foreach> + + LIMIT #{limit,jdbcType=INTEGER} + + </select> + + <!-- Search for specific user records --> + <select id="searchReadable" resultMap="UserRecordResultMap"> + + SELECT + guacamole_user_history.remote_host, + guacamole_user_history.user_id, + guacamole_user_history.username, + guacamole_user_history.start_date, + guacamole_user_history.end_date + FROM guacamole_user_history + + <!-- Restrict to readable users --> + JOIN guacamole_user_permission ON + guacamole_user_history.user_id = guacamole_user_permission.affected_user_id + AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} + AND guacamole_user_permission.permission = 'READ' + + <!-- Search terms --> + <foreach collection="terms" item="term" + open="WHERE " separator=" AND "> + ( + + guacamole_user_history.user_id IN ( + SELECT user_id + FROM guacamole_user + WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + ) + + <if test="term.startDate != null and term.endDate != null"> + OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP} + </if> + + ) + </foreach> + + <!-- Bind sort property enum values for sake of readability --> + <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/> + + <!-- Sort predicates --> + <foreach collection="sortPredicates" item="sortPredicate" + open="ORDER BY " separator=", "> + <choose> + <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when> + <otherwise>1</otherwise> + </choose> + <if test="sortPredicate.descending">DESC</if> + </foreach> + + LIMIT #{limit,jdbcType=INTEGER} + + </select> + +</mapper> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/6f6b4e5d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml new file mode 100644 index 0000000..fafa863 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml @@ -0,0 +1,173 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" + "http://mybatis.org/dtd/mybatis-3-mapper.dtd"; > + +<!-- + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. +--> + +<mapper namespace="org.apache.guacamole.auth.jdbc.user.UserRecordMapper" > + + <!-- Result mapper for system permissions --> + <resultMap id="UserRecordResultMap" type="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel"> + <result column="remote_host" property="remoteHost" jdbcType="VARCHAR"/> + <result column="user_id" property="userID" jdbcType="INTEGER"/> + <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="start_date" property="startDate" jdbcType="TIMESTAMP"/> + <result column="end_date" property="endDate" jdbcType="TIMESTAMP"/> + </resultMap> + + <!-- Select all user records from a given user --> + <select id="select" resultMap="UserRecordResultMap"> + + SELECT + [guacamole_user_history].remote_host, + [guacamole_user_history].user_id, + [guacamole_user_history].username, + [guacamole_user_history].start_date, + [guacamole_user_history].end_date + FROM [guacamole_user_history] + JOIN [guacamole_user] ON [guacamole_user_history].user_id = [guacamole_user].user_id + WHERE + [guacamole_user].username = #{username,jdbcType=VARCHAR} + ORDER BY + [guacamole_user_history].start_date DESC, + [guacamole_user_history].end_date DESC + + </select> + + <!-- Insert the given user record --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel"> + + INSERT INTO [guacamole_user_history] ( + remote_host, + user_id, + username, + start_date, + end_date + ) + VALUES ( + #{record.remoteHost,jdbcType=VARCHAR}, + (SELECT user_id FROM [guacamole_user] + WHERE username = #{record.username,jdbcType=VARCHAR}), + #{record.username,jdbcType=VARCHAR}, + #{record.startDate,jdbcType=TIMESTAMP}, + #{record.endDate,jdbcType=TIMESTAMP} + ) + + </insert> + + <!-- Search for specific user records --> + <select id="search" resultMap="UserRecordResultMap"> + + SELECT + [guacamole_user_history].remote_host, + [guacamole_user_history].user_id, + [guacamole_user_history].username, + [guacamole_user_history].start_date, + [guacamole_user_history].end_date + FROM [guacamole_user_history] + + <!-- Search terms --> + <foreach collection="terms" item="term" + open="WHERE " separator=" AND "> + ( + + [guacamole_user_history].user_id IN ( + SELECT user_id + FROM [guacamole_user] + WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + ) + + <if test="term.startDate != null and term.endDate != null"> + OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP} + </if> + + ) + </foreach> + + <!-- Bind sort property enum values for sake of readability --> + <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/> + + <!-- Sort predicates --> + <foreach collection="sortPredicates" item="sortPredicate" + open="ORDER BY " separator=", "> + <choose> + <when test="sortPredicate.property == START_DATE">[guacamole_user_history].start_date</when> + <otherwise>1</otherwise> + </choose> + <if test="sortPredicate.descending">DESC</if> + </foreach> + + LIMIT #{limit,jdbcType=INTEGER} + + </select> + + <!-- Search for specific user records --> + <select id="searchReadable" resultMap="UserRecordResultMap"> + + SELECT + [guacamole_user_history].remote_host, + [guacamole_user_history].user_id, + [guacamole_user_history].username, + [guacamole_user_history].start_date, + [guacamole_user_history].end_date + FROM [guacamole_user_history] + + <!-- Restrict to readable users --> + JOIN [guacamole_user_permission] ON + [guacamole_user_history].user_id = [guacamole_user_permission].affected_user_id + AND [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND [guacamole_user_permission].permission = 'READ' + + <!-- Search terms --> + <foreach collection="terms" item="term" + open="WHERE " separator=" AND "> + ( + + [guacamole_user_history].user_id IN ( + SELECT user_id + FROM [guacamole_user] + WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + ) + + <if test="term.startDate != null and term.endDate != null"> + OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP} + </if> + + ) + </foreach> + + <!-- Bind sort property enum values for sake of readability --> + <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/> + + <!-- Sort predicates --> + <foreach collection="sortPredicates" item="sortPredicate" + open="ORDER BY " separator=", "> + <choose> + <when test="sortPredicate.property == START_DATE">[guacamole_user_history].start_date</when> + <otherwise>1</otherwise> + </choose> + <if test="sortPredicate.descending">DESC</if> + </foreach> + + LIMIT #{limit,jdbcType=INTEGER} + + </select> + +</mapper>
