From 55f03c70caeec32ae71e5afe99c4eae5b6b192d3 Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Sat, 24 Dec 2011 23:05:48 +0700 Subject: Initial commit --- src/com/juick/server/ChatQueries.java | 86 +++++ src/com/juick/server/MessagesQueries.java | 551 ++++++++++++++++++++++++++++++ src/com/juick/server/PlacesQueries.java | 55 +++ src/com/juick/server/TagQueries.java | 68 ++++ src/com/juick/server/UserQueries.java | 218 ++++++++++++ src/com/juick/server/Utils.java | 56 +++ 6 files changed, 1034 insertions(+) create mode 100644 src/com/juick/server/ChatQueries.java create mode 100644 src/com/juick/server/MessagesQueries.java create mode 100644 src/com/juick/server/PlacesQueries.java create mode 100644 src/com/juick/server/TagQueries.java create mode 100644 src/com/juick/server/UserQueries.java create mode 100644 src/com/juick/server/Utils.java (limited to 'src/com/juick') diff --git a/src/com/juick/server/ChatQueries.java b/src/com/juick/server/ChatQueries.java new file mode 100644 index 00000000..19f95637 --- /dev/null +++ b/src/com/juick/server/ChatQueries.java @@ -0,0 +1,86 @@ +/* + * 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 . + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; + +/** + * + * @author Ugnich Anton + */ +public class ChatQueries { + + public static boolean subscribeTag(Connection sql, int uid, int tagid) { + boolean ret = false; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement("INSERT INTO chat_subscr_tags(tag_id,user_id) VALUES (?,?)"); + stmt.setInt(1, tagid); + stmt.setInt(2, uid); + if (stmt.executeUpdate() > 0) { + ret = true; + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + return ret; + } + + public static boolean unsubscribeTag(Connection sql, int uid, int tagid) { + boolean ret = false; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement("DELETE FROM chat_subscr_tags WHERE tag_id=? AND user_id=?"); + stmt.setInt(1, tagid); + stmt.setInt(2, uid); + if (stmt.executeUpdate() > 0) { + ret = true; + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + return ret; + } + + public static boolean setSubscriptionsActive(Connection sql, int uid, boolean enabled) { + boolean ret = false; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement("UPDATE chat_subscr_tags SET active=? WHERE user_id=?"); + stmt.setInt(1, enabled ? 1 : 0); + stmt.setInt(2, uid); + if (stmt.executeUpdate() > 0) { + ret = true; + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + return ret; + } +} diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java new file mode 100644 index 00000000..3ff47ca7 --- /dev/null +++ b/src/com/juick/server/MessagesQueries.java @@ -0,0 +1,551 @@ +/* + * 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 . + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; + +/** + * + * @author Ugnich Anton + */ +public class MessagesQueries { + + public static com.juick.Message getMessage(Connection sql, int mid) { + com.juick.Message msg = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT messages.user_id,users.nick,messages.ts,messages.readonly,messages.attach,messages.place_id,messages.lat,messages.lon FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + msg = new com.juick.Message(); + msg.MID = mid; + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(1); + msg.User.UName = rs.getString(2); + msg.TimestampString = rs.getString(3); + msg.ReadOnly = rs.getBoolean(4); + msg.AttachmentType = rs.getString(5); + if (rs.getInt(6) > 0) { + msg.place = PlacesQueries.getPlace(sql, rs.getInt(6)); + } + if (rs.getDouble(7) != 0) { + msg.place = new com.juick.Place(); + msg.place.lat = rs.getDouble(7); + msg.place.lon = rs.getDouble(8); + } + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + if (msg == null) { + return null; + } + + try { + stmt = sql.prepareStatement("SELECT messages_txt.tags,messages_txt.txt FROM messages_txt WHERE message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + msg.parseTags(rs.getString(1)); + msg.Text = rs.getString(2); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return msg; + } + + public static com.juick.Message getReply(Connection sql, int mid, int rid) { + com.juick.Message msg = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT replies.user_id,users.nick,replies.replyto,replies.ts,replies.attach,replies.txt FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? AND replies.reply_id=?"); + stmt.setInt(1, mid); + stmt.setInt(2, rid); + rs = stmt.executeQuery(); + if (rs.first()) { + msg = new com.juick.Message(); + msg.MID = mid; + msg.RID = rid; + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(1); + msg.User.UName = rs.getString(2); + msg.ReplyTo = rs.getInt(3); + msg.TimestampString = rs.getString(4); + msg.AttachmentType = rs.getString(5); + msg.Text = rs.getString(6); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return msg; + } + + public static com.juick.User getMessageAuthor(Connection sql, int mid) { + com.juick.User user = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT messages.user_id,users.nick FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?"); + stmt.setInt(1, mid); + 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 ArrayList getAll(Connection sql, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages WHERE messages.message_id0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages WHERE messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList getTag(Connection sql, int tid, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id=? AND messages.message_id0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, tid); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id=? AND messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, tid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList getMyFeed(Connection sql, int uid, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM feed WHERE user_id=? AND message_id getPrivate(Connection sql, int uid, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages_access WHERE user_id=? AND message_id getIncoming(Connection sql, int uid, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id IN (SELECT suser_id FROM subscr_users WHERE user_id=?) AND message_id getRecommended(Connection sql, int uid, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) AND message_id getPopular(Connection sql, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 AND messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) ORDER BY message_id DESC LIMIT 20"); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList getPhotos(Connection sql, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList getSearch(Connection sql, Connection sqlSearch, String search, int before) { + ArrayList mids0 = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sqlSearch.prepareStatement("SELECT @id AS message_id FROM messages WHERE MATCH(?) AND @id mids = new ArrayList(20); + if (mids0.size() > 0) { + try { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArray2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC"); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + } + + return mids; + } + + public static ArrayList getUserBlog(Connection sql, int UID, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id0 ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>0 ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList getUserTag(Connection sql, int UID, int TID, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.message_id0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, TID); + stmt.setInt(3, before); + } else { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, TID); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList getUserRecommendations(Connection sql, int UID, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? AND message_id getUserPhotos(Connection sql, int UID, int before) { + ArrayList mids = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList getUserSearch(Connection sql, Connection sqlSearch, int UID, String search, int before) { + ArrayList mids0 = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sqlSearch.prepareStatement("SELECT @id AS message_id FROM messages WHERE user_id=? AND MATCH(?) AND @id mids = new ArrayList(20); + if (mids0.size() > 0) { + try { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArray2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC"); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + } + + return mids; + } +} diff --git a/src/com/juick/server/PlacesQueries.java b/src/com/juick/server/PlacesQueries.java new file mode 100644 index 00000000..d61c89c3 --- /dev/null +++ b/src/com/juick/server/PlacesQueries.java @@ -0,0 +1,55 @@ +/* + * 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 . + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +/** + * + * @author Ugnich Anton + */ +public class PlacesQueries { + + public static com.juick.Place getPlace(Connection sql, int pid) { + com.juick.Place place = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT lat,lon,name FROM places WHERE place_id=?"); + stmt.setInt(1, pid); + rs = stmt.executeQuery(); + if (rs.first()) { + place = new com.juick.Place(); + place.pid = pid; + place.lat = rs.getDouble(1); + place.lon = rs.getDouble(2); + place.name = rs.getString(3); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return place; + } +} diff --git a/src/com/juick/server/TagQueries.java b/src/com/juick/server/TagQueries.java new file mode 100644 index 00000000..8cb7ee1a --- /dev/null +++ b/src/com/juick/server/TagQueries.java @@ -0,0 +1,68 @@ +/* + * 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 . + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +/** + * + * @author Ugnich Anton + */ +public class TagQueries { + + public static int getTagID(Connection sql, String tag, boolean autoCreate) { + int tid = 0; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT tag_id FROM tags WHERE name=?"); + stmt.setString(1, tag); + rs = stmt.executeQuery(); + if (rs.first()) { + tid = rs.getInt(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + if (tid == 0 && autoCreate) { + try { + stmt = sql.prepareStatement("INSERT INTO tags(name) VALUES (?)", Statement.RETURN_GENERATED_KEYS); + stmt.setString(1, tag); + stmt.executeUpdate(); + rs = stmt.getGeneratedKeys(); + if (rs.first()) { + tid = rs.getInt(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + } + + return tid; + } +} diff --git a/src/com/juick/server/UserQueries.java b/src/com/juick/server/UserQueries.java new file mode 100644 index 00000000..97cae91f --- /dev/null +++ b/src/com/juick/server/UserQueries.java @@ -0,0 +1,218 @@ +/* + * 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 . + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Random; + +/** + * + * @author Ugnich Anton + */ +public class UserQueries { + + static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; + + public static com.juick.User getUserByNick(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 String getJIDbyUID(Connection sql, int UID) { + String jid = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT jid FROM jids WHERE user_id=? AND active=1"); + stmt.setInt(1, UID); + rs = stmt.executeQuery(); + if (rs.first()) { + jid = rs.getString(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return jid; + } + + public static int getUIDbyHash(Connection sql, String hash) { + int UID = 0; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT user_id FROM logins WHERE hash=?"); + stmt.setString(1, hash); + rs = stmt.executeQuery(); + if (rs.first()) { + UID = rs.getInt(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return UID; + } + + 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); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return user; + } + + public static String getHashByUID(Connection sql, int uid) { + String hash = null; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT logins.hash FROM logins WHERE user_id=?"); + stmt.setInt(1, uid); + rs = stmt.executeQuery(); + if (rs.first()) { + hash = rs.getString(2); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + if (hash == null) { + hash = generateHash(16); + 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 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); + } + } +} diff --git a/src/com/juick/server/Utils.java b/src/com/juick/server/Utils.java new file mode 100644 index 00000000..997c4f32 --- /dev/null +++ b/src/com/juick/server/Utils.java @@ -0,0 +1,56 @@ +/* + * 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 . + */ +package com.juick.server; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; + +/** + * + * @author Ugnich Anton + */ +public class Utils { + + public static String convertArray2String(ArrayList mids) { + String q = ""; + for (int i = 0; i < mids.size(); i++) { + if (i > 0) { + q += ","; + } + q += mids.get(i); + } + return q; + } + + public static void finishSQL(ResultSet rs, Statement stmt) { + if (rs != null) { + try { + rs.close(); + } catch (SQLException e) { + } + } + if (stmt != null) { + try { + stmt.close(); + } catch (SQLException e) { + } + } + } +} -- cgit v1.2.3