Examples with QueryStatement com.djrapitops.plan.storage.database.queries.QueryStatement used on opensource projects

Example 1 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class NetworkActivityIndexQueries method activityIndexForNewPlayers.

public static Query<Collection<ActivityIndex>> activityIndexForNewPlayers(long after, long before, Long threshold) {
    String selectNewUUIDs = SELECT + UsersTable.USER_UUID + FROM + UsersTable.TABLE_NAME + WHERE + UsersTable.REGISTERED + "<=?" + AND + UsersTable.REGISTERED + ">=?";
    String sql = SELECT + "activity_index" + FROM + '(' + selectNewUUIDs + ") n" + INNER_JOIN + '(' + selectActivityIndexSQL() + ") a on n." + SessionsTable.USER_UUID + "=a." + SessionsTable.USER_UUID;
    return new QueryStatement<Collection<ActivityIndex>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, before);
            statement.setLong(2, after);
            setSelectActivityIndexSQLParameters(statement, 3, threshold, before);
        }

        @Override
        public Collection<ActivityIndex> processResults(ResultSet set) throws SQLException {
            Collection<ActivityIndex> indexes = new ArrayList<>();
            while (set.next()) {
                indexes.add(new ActivityIndex(set.getDouble("activity_index"), before));
            }
            return indexes;
        }
    };
}

Example 2 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class NetworkActivityIndexQueries method averageActivityIndexForRetainedPlayers.

public static Query<ActivityIndex> averageActivityIndexForRetainedPlayers(long after, long before, Long threshold) {
    String selectNewUUIDs = SELECT + UsersTable.USER_UUID + FROM + UsersTable.TABLE_NAME + WHERE + UsersTable.REGISTERED + "<=?" + AND + UsersTable.REGISTERED + ">=?";
    String selectUniqueUUIDs = SELECT + "DISTINCT " + SessionsTable.USER_UUID + FROM + SessionsTable.TABLE_NAME + WHERE + SessionsTable.SESSION_START + ">=?" + AND + SessionsTable.SESSION_END + "<=?";
    String sql = SELECT + "AVG(activity_index) as average" + FROM + '(' + selectNewUUIDs + ") n" + INNER_JOIN + '(' + selectUniqueUUIDs + ") u on n." + SessionsTable.USER_UUID + "=u." + SessionsTable.USER_UUID + INNER_JOIN + '(' + selectActivityIndexSQL() + ") a on n." + SessionsTable.USER_UUID + "=a." + SessionsTable.USER_UUID;
    return new QueryStatement<ActivityIndex>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, before);
            statement.setLong(2, after);
            // Have played in the last half of the time frame
            long half = before - (before - after) / 2;
            statement.setLong(3, half);
            statement.setLong(4, before);
            setSelectActivityIndexSQLParameters(statement, 5, threshold, before);
        }

        @Override
        public ActivityIndex processResults(ResultSet set) throws SQLException {
            return set.next() ? new ActivityIndex(set.getDouble("average"), before) : new ActivityIndex(0.0, before);
        }
    };
}

Example 3 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class SessionQueries method fetchServerSessionsWithoutKillOrWorldData.

public static Query<List<FinishedSession>> fetchServerSessionsWithoutKillOrWorldData(long after, long before, ServerUUID serverUUID) {
    String sql = SELECT + SessionsTable.ID + ',' + SessionsTable.USER_UUID + ',' + SessionsTable.SESSION_START + ',' + SessionsTable.SESSION_END + ',' + SessionsTable.DEATHS + ',' + SessionsTable.MOB_KILLS + ',' + SessionsTable.AFK_TIME + FROM + SessionsTable.TABLE_NAME + WHERE + SessionsTable.SERVER_UUID + "=?" + AND + SessionsTable.SESSION_START + ">=?" + AND + SessionsTable.SESSION_START + "<=?";
    return new QueryStatement<List<FinishedSession>>(sql, 1000) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, serverUUID.toString());
            statement.setLong(2, after);
            statement.setLong(3, before);
        }

        @Override
        public List<FinishedSession> processResults(ResultSet set) throws SQLException {
            List<FinishedSession> sessions = new ArrayList<>();
            while (set.next()) {
                UUID uuid = UUID.fromString(set.getString(SessionsTable.USER_UUID));
                long start = set.getLong(SessionsTable.SESSION_START);
                long end = set.getLong(SessionsTable.SESSION_END);
                int deaths = set.getInt(SessionsTable.DEATHS);
                int mobKills = set.getInt(SessionsTable.MOB_KILLS);
                int id = set.getInt(SessionsTable.ID);
                long timeAFK = set.getLong(SessionsTable.AFK_TIME);
                DataMap extraData = new DataMap();
                extraData.put(FinishedSession.Id.class, new FinishedSession.Id(id));
                extraData.put(DeathCounter.class, new DeathCounter(deaths));
                extraData.put(MobKillCounter.class, new MobKillCounter(mobKills));
                sessions.add(new FinishedSession(uuid, serverUUID, start, end, timeAFK, extraData));
            }
            return sessions;
        }
    };
}

Example 4 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class WebUserQueries method matchUsers.

public static Query<List<User>> matchUsers(String partOfUsername) {
    String sql = SELECT + '*' + FROM + SecurityTable.TABLE_NAME + LEFT_JOIN + UsersTable.TABLE_NAME + " on " + SecurityTable.LINKED_TO + "=" + UsersTable.USER_UUID + WHERE + "LOWER(" + SecurityTable.USERNAME + ") LIKE LOWER(?)";
    return new QueryStatement<List<User>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, '%' + partOfUsername + '%');
        }

        @Override
        public List<User> processResults(ResultSet set) throws SQLException {
            List<User> users = new ArrayList<>();
            while (set.next()) {
                String username = set.getString(SecurityTable.USERNAME);
                String linkedTo = set.getString(UsersTable.USER_NAME);
                UUID linkedToUUID = linkedTo != null ? UUID.fromString(set.getString(SecurityTable.LINKED_TO)) : null;
                String passwordHash = set.getString(SecurityTable.SALT_PASSWORD_HASH);
                int permissionLevel = set.getInt(SecurityTable.PERMISSION_LEVEL);
                List<String> permissions = WebUser.getPermissionsForLevel(permissionLevel);
                users.add(new User(username, linkedTo != null ? linkedTo : "console", linkedToUUID, passwordHash, permissionLevel, permissions));
            }
            return users;
        }
    };
}

Example 5 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class WebUserQueries method fetchUserLinkedTo.

public static Query<Optional<User>> fetchUserLinkedTo(String playerName) {
    String sql = SELECT + '*' + FROM + SecurityTable.TABLE_NAME + LEFT_JOIN + UsersTable.TABLE_NAME + " on " + SecurityTable.LINKED_TO + "=" + UsersTable.USER_UUID + WHERE + UsersTable.USER_NAME + "=? LIMIT 1";
    return new QueryStatement<Optional<User>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, playerName);
        }

        @Override
        public Optional<User> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                String linkedTo = set.getString(UsersTable.USER_NAME);
                UUID linkedToUUID = linkedTo != null ? UUID.fromString(set.getString(SecurityTable.LINKED_TO)) : null;
                String passwordHash = set.getString(SecurityTable.SALT_PASSWORD_HASH);
                int permissionLevel = set.getInt(SecurityTable.PERMISSION_LEVEL);
                List<String> permissions = WebUser.getPermissionsForLevel(permissionLevel);
                return Optional.of(new User(playerName, linkedTo != null ? linkedTo : "console", linkedToUUID, passwordHash, permissionLevel, permissions));
            }
            return Optional.empty();
        }
    };
}