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

Example 31 with QueryStatement

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

the class WebUserQueries method fetchUser.

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

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

        @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(username, linkedTo != null ? linkedTo : "console", linkedToUUID, passwordHash, permissionLevel, permissions));
            }
            return Optional.empty();
        }
    };
}

Example 32 with QueryStatement

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

the class WebUserQueries method fetchActiveCookies.

public static Query<Map<String, User>> fetchActiveCookies() {
    String sql = SELECT + '*' + FROM + CookieTable.TABLE_NAME + INNER_JOIN + SecurityTable.TABLE_NAME + " on " + CookieTable.TABLE_NAME + '.' + CookieTable.WEB_USERNAME + '=' + SecurityTable.TABLE_NAME + '.' + SecurityTable.USERNAME + LEFT_JOIN + UsersTable.TABLE_NAME + " on " + SecurityTable.LINKED_TO + "=" + UsersTable.USER_UUID + WHERE + CookieTable.EXPIRES + ">?";
    return new QueryStatement<Map<String, User>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, System.currentTimeMillis());
        }

        @Override
        public Map<String, User> processResults(ResultSet set) throws SQLException {
            Map<String, User> usersByCookie = new HashMap<>();
            while (set.next()) {
                String cookie = set.getString(CookieTable.COOKIE);
                User user = extractUser(set);
                usersByCookie.put(cookie, user);
            }
            return usersByCookie;
        }
    };
}

Example 33 with QueryStatement

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

the class WebUserQueries method fetchUser.

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

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            if (linkedToUUID == null) {
                statement.setNull(1, Types.VARCHAR);
            } else {
                statement.setString(1, linkedToUUID.toString());
            }
        }

        @Override
        public Optional<User> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                String username = set.getString(SecurityTable.USERNAME);
                String linkedTo = set.getString(UsersTable.USER_NAME);
                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(username, linkedTo != null ? linkedTo : "console", linkedToUUID, passwordHash, permissionLevel, permissions));
            }
            return Optional.empty();
        }
    };
}

Example 34 with QueryStatement

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

the class NetworkActivityIndexQueries method averageActivityIndexForNonRetainedPlayers.

public static Query<ActivityIndex> averageActivityIndexForNonRetainedPlayers(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" + LEFT_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 + WHERE + "n." + SessionsTable.USER_UUID + IS_NULL;
    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 35 with QueryStatement

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

the class TPSQueries method fetchTPSDataOfServer.

public static Query<List<TPS>> fetchTPSDataOfServer(long after, long before, ServerUUID serverUUID) {
    String sql = SELECT + "*" + FROM + TABLE_NAME + WHERE + SERVER_ID + "=" + ServerTable.STATEMENT_SELECT_SERVER_ID + AND + DATE + ">=?" + AND + DATE + "<=?" + ORDER_BY + DATE;
    return new QueryStatement<List<TPS>>(sql, 50000) {

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

        @Override
        public List<TPS> processResults(ResultSet set) throws SQLException {
            List<TPS> data = new ArrayList<>();
            while (set.next()) {
                TPS tps = extractTPS(set);
                data.add(tps);
            }
            return data;
        }
    };
}