path: root/src/com/juick/server/UserQueries.java

/*
 * Juick
 * Copyright (C) 2008-2011, Ugnich Anton
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.juick.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Random;
import java.util.UUID;

/**
 *
 * @author Ugnich Anton
 */
public class UserQueries {
    
    static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    
    public static String getSignUpHashByJID(Connection sql, String jid) {
        String hash = SQLHelpers.getString(sql, "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", jid);
        
        if (hash == null) {
            hash = UUID.randomUUID().toString();
            PreparedStatement stmt = null;
            try {
                stmt = sql.prepareStatement("INSERT INTO jids(jid,loginhash) VALUES (?,?)");
                stmt.setString(1, jid);
                stmt.setString(2, hash);
                stmt.executeUpdate();
            } catch (SQLException e) {
                System.err.println(e);
            } finally {
                Utils.finishSQL(null, stmt);
            }
        }
        
        return hash;
    }
    
    public static int createUser(Connection sql, String username, String password) {
        int uid = 0;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("INSERT INTO users(nick,passw) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.executeUpdate();
            rs = stmt.getGeneratedKeys();
            if (rs.first()) {
                uid = rs.getInt(1);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        
        SQLHelpers.executeInt(sql, "INSERT INTO useroptions(user_id) VALUES (?)", uid);
        SQLHelpers.executeInt(sql, "INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);
        
        return uid;
    }
    
    public static com.juick.User getUserByUID(Connection sql, int uid) {
        com.juick.User user = null;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT nick FROM users WHERE id=?");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = uid;
                user.UName = rs.getString(1);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }
    
    public static com.juick.User getUserByName(Connection sql, String username) {
        com.juick.User user = null;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE nick=?");
            stmt.setString(1, username);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }
    
    public static com.juick.User getUserByJID(Connection sql, String jid) {
        com.juick.User user = null;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)");
            stmt.setString(1, jid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                user.JID = jid;
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }
    
    public static ArrayList<com.juick.User> getUsersByName(Connection sql, ArrayList<String> unames) {
        ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE nick IN (" + Utils.convertArrayString2String(unames) + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                users.add(user);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return users;
    }
    
    public static ArrayList<com.juick.User> getUsersByID(Connection sql, ArrayList<Integer> uids) {
        ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User u = new com.juick.User();
                u.UID = rs.getInt(1);
                u.UName = rs.getString(2);
                users.add(u);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        
        return users;
    }
    
    public static boolean fillUsersByID(Connection sql, ArrayList<com.juick.User> users) {
        boolean ret = false;
        
        String uids = "";
        final int usersSize = users.size();
        for (int i = 0; i < usersSize; i++) {
            if (i > 0) {
                uids += ",";
            }
            uids += users.get(i).UID;
        }
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + uids + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                int uid = rs.getInt(1);
                for (int i = 0; i < usersSize; i++) {
                    if (users.get(i).UID == uid) {
                        users.get(i).UName = rs.getString(2);
                        ret = true;
                    }
                }
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        
        return ret;
    }
    
    public static ArrayList<com.juick.User> getUsersByJID(Connection sql, ArrayList<String> jids) {
        ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT users.id,users.nick,jids.jid FROM users INNER JOIN jids ON jids.user_id=users.id WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                user.JID = rs.getString(3);
                users.add(user);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return users;
    }
    
    public static String getJIDbyUID(Connection sql, int uid) {
        return SQLHelpers.getString(sql, "SELECT jid FROM jids WHERE user_id=? AND active=1", uid);
    }
    
    public static int getUIDbyJID(Connection sql, String jid) {
        return SQLHelpers.getInt(sql, "SELECT user_id FROM jids WHERE jid=?", jid, 0);
    }
    
    public static int getUIDbyName(Connection sql, String uname) {
        return SQLHelpers.getInt(sql, "SELECT id FROM users WHERE nick=?", uname, 0);
    }
    
    public static int getUIDbyHash(Connection sql, String hash) {
        return SQLHelpers.getInt(sql, "SELECT user_id FROM logins WHERE hash=?", hash, 0);
    }
    
    public static com.juick.User getUserByHash(Connection sql, String hash) {
        com.juick.User user = null;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT logins.user_id,users.nick FROM logins INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?");
            stmt.setString(1, hash);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                user.AuthHash = hash;
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }
    
    public static String getHashByUID(Connection sql, int uid) {
        String hash = SQLHelpers.getString(sql, "SELECT hash FROM logins WHERE user_id=?", uid);
        
        if (hash == null) {
            hash = generateHash(16);
            PreparedStatement stmt = null;
            try {
                stmt = sql.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)");
                stmt.setInt(1, uid);
                stmt.setString(2, hash);
                stmt.executeUpdate();
            } catch (SQLException e) {
                System.err.println(e);
            } finally {
                Utils.finishSQL(null, stmt);
            }
        }
        
        return hash;
    }
    
    public static String generateHash(int len) {
        Random rnd = new Random();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            sb.append(ABCDEF.charAt(rnd.nextInt(ABCDEF.length())));
        }
        return sb.toString();
    }
    
    public static boolean checkUserNameValid(String uname) {
        return uname != null && uname.length() >= 2 && uname.length() <= 16 && uname.matches("[a-zA-Z0-9\\-]+");
    }
    
    public static int checkPassword(Connection sql, String username, String password) {
        int uid = 0;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,passw FROM users WHERE nick=?");
            stmt.setString(1, username);
            rs = stmt.executeQuery();
            if (rs.first()) {
                if (password.equals(rs.getString(2))) {
                    uid = rs.getInt(1);
                } else {
                    uid = -1;
                }
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return uid;
    }
    
    public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) {
        int ret = defaultValue;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT " + option + " FROM useroptions WHERE user_id=?");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }
    
    public static void setUserOptionInt(Connection sql, int uid, String option, int value) {
        PreparedStatement stmt = null;
        try {
            stmt = sql.prepareStatement("UPDATE useroptions SET " + option + "=? WHERE user_id=?");
            stmt.setInt(1, value);
            stmt.setInt(2, uid);
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(null, stmt);
        }
    }
    
    public static boolean getCanMedia(Connection sql, int uid) {
        boolean ret = false;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT users.lastphoto-UNIX_TIMESTAMP() FROM users WHERE id=?");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) < 3600;
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }
    
    public static boolean isInWL(Connection sql, int uid, int check) {
        boolean ret = false;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM wl_users WHERE user_id=? AND wl_user_id=?");
            stmt.setInt(1, uid);
            stmt.setInt(2, check);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }
    
    public static boolean isInBL(Connection sql, int uid, int check) {
        boolean ret = false;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE user_id=? AND bl_user_id=?");
            stmt.setInt(1, uid);
            stmt.setInt(2, check);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }
    
    public static boolean isInBLAny(Connection sql, int uid, int uid2) {
        boolean ret = false;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE (user_id=? AND bl_user_id=?) OR (user_id=? AND bl_user_id=?)");
            stmt.setInt(1, uid);
            stmt.setInt(2, uid2);
            stmt.setInt(3, uid2);
            stmt.setInt(4, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }
    
    public static ArrayList<Integer> checkBL(Connection sql, int visitor, ArrayList<Integer> uids) {
        ArrayList<Integer> ret = new ArrayList<Integer>();
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + Utils.convertArrayInt2String(uids) + ")");
            stmt.setInt(1, visitor);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                ret.add(rs.getInt(1));
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        
        return ret;
    }
    
    public static boolean isSubscribed(Connection sql, int uid, int check) {
        boolean ret = false;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?");
            stmt.setInt(1, uid);
            stmt.setInt(2, check);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }
    
    public static ArrayList<Integer> getUserRead(Connection sql, int uid) {
        return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid);
    }
    
    public static ArrayList<Integer> getUserReaders(Connection sql, int uid) {
        return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid);
    }
    
    public static ArrayList<com.juick.User> getUserBLUsers(Connection sql, int uid) {
        ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT users.id,users.nick FROM users INNER JOIN bl_users ON(bl_users.bl_user_id=users.id) WHERE bl_users.user_id=? ORDER BY users.nick");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User u = new com.juick.User();
                u.UID = rs.getInt(1);
                u.UName = rs.getString(2);
                users.add(u);
            }
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        
        return users;
    }
    
    public static int getStatsIRead(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", uid, 0);
    }
    
    public static int getStatsMyReaders(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE user_id=?", uid, 0);
    }
    
    public static int getStatsMessages(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM messages WHERE user_id=?", uid, 0);
    }
    
    public static int getStatsReplies(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM replies WHERE user_id=?", uid, 0);
    }
}