diff options
Diffstat (limited to 'juick-core')
26 files changed, 3100 insertions, 0 deletions
diff --git a/juick-core/build.gradle b/juick-core/build.gradle new file mode 100644 index 00000000..bb10c52b --- /dev/null +++ b/juick-core/build.gradle @@ -0,0 +1,15 @@ +apply plugin: 'java' + +repositories { + mavenCentral() +} + +dependencies { + compile project(':deps:com.juick.xmpp') + compile 'org.apache.httpcomponents:httpclient:4.5.1' + compile 'org.apache.commons:commons-lang3:3.4' + compile "org.springframework:spring-jdbc:4.3.1.RELEASE" + compile 'org.json:json:20151123' +} + +compileJava.options.encoding = 'UTF-8'
\ No newline at end of file diff --git a/juick-core/src/main/java/com/juick/Group.java b/juick-core/src/main/java/com/juick/Group.java new file mode 100644 index 00000000..008d3af2 --- /dev/null +++ b/juick-core/src/main/java/com/juick/Group.java @@ -0,0 +1,29 @@ +/* + * Juick + * Copyright (C) 2008-2013, 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; + +/** + * + * @author Ugnich Anton + */ +public class Group { + + public String Name = null; + public int GID = 0; + public int UsersCnt = 0; +} diff --git a/juick-core/src/main/java/com/juick/Message.java b/juick-core/src/main/java/com/juick/Message.java new file mode 100644 index 00000000..c128b4f2 --- /dev/null +++ b/juick-core/src/main/java/com/juick/Message.java @@ -0,0 +1,244 @@ +/* + * 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; + +import java.util.*; + +/** + * + * @author Ugnich Anton + */ +public class Message implements Comparable { + + private int MID = 0; + + private int RID = 0; + + public int ReplyTo = 0; + private String Text = null; + private User User = null; + public List<String> Tags = new ArrayList<>(); + private Date date; + public int TimeAgo = 0; + public int Privacy = 1; + public boolean FriendsOnly = false; + public boolean ReadOnly = false; + public boolean Hidden = false; + public boolean VisitorCanComment = true; + public int Replies = 0; + public String RepliesBy = null; + public String AttachmentType = null; + public String Photo = null; + public String Video = null; + public Place Place = null; + public int Likes = 0; + private boolean liked = false; + public List<Message> childs = new ArrayList<>(); + private Optional<PM> PM = Optional.empty(); + private Optional<Recommendation> Recommendation = Optional.empty(); + + public Message() { + } + + public Message(Message msg) { + setMID(msg.getMID()); + setRID(msg.getRID()); + ReplyTo = msg.ReplyTo; + setText(msg.getText()); + setUser(msg.getUser()); + Tags = msg.Tags; + setDate(msg.getDate()); + TimeAgo = msg.TimeAgo; + Privacy = msg.Privacy; + FriendsOnly = msg.FriendsOnly; + ReadOnly = msg.ReadOnly; + Hidden = msg.Hidden; + Replies = msg.Replies; + AttachmentType = msg.AttachmentType; + Photo = msg.Photo; + Video = msg.Video; + Place = msg.Place; + Likes = msg.Likes; + setLiked(msg.isLiked()); + childs = msg.childs; + PM = msg.PM; + Recommendation = msg.Recommendation; + } + + public void parseTags(String strTags) { + if (strTags != null) { + Tags.addAll(Arrays.asList(strTags.split(" "))); + } + } + + @Override + public boolean equals(Object obj) { + if (!(obj instanceof Message)) { + return false; + } + Message jmsg = (Message) obj; + return (this.getMID() == jmsg.getMID() && this.getRID() == jmsg.getRID()); + } + + @Override + public int compareTo(Object obj) throws ClassCastException { + if (!(obj instanceof Message)) { + throw new ClassCastException(); + } + Message jmsg = (Message) obj; + + if (this.getMID() != jmsg.getMID()) { + if (this.getMID() > jmsg.getMID()) { + return -1; + } else { + return 1; + } + } + + if (this.getRID() != jmsg.getRID()) { + if (this.getRID() < jmsg.getRID()) { + return -1; + } else { + return 1; + } + } + + return 0; + } + + public int getChildsCount() { + int cnt = childs.size(); + for (Message child : childs) { + cnt += child.getChildsCount(); + } + return cnt; + } + + public void cleanupChilds() { + if (!childs.isEmpty()) { + for (Message child : childs) { + child.cleanupChilds(); + } + childs.clear(); + } + } + + public String getAttachmentURL() { + if (AttachmentType != null) { + String url = "http://i.juick.com/"; + url += AttachmentType.equals("mp4") ? "video" : "photos-1024"; + url += "/" + getMID(); + if (getRID() > 0) { + url += "-" + getRID(); + } + url += "." + AttachmentType; + return url; + } else { + return null; + } + } + + public String getTagsString() { + String ret = ""; + if (!Tags.isEmpty()) { + for (String Tag : Tags) { + ret += " *" + Tag; + } + if (FriendsOnly) { + ret += " *friends"; + } + if (Privacy == -2) { + ret += " *private"; + } + if (Privacy == -1) { + ret += " *friends"; + } + if (Privacy == 2) { + ret += " *public"; + } + if (ReadOnly) { + ret += " *readonly"; + } + } + return ret; + } + + public int getMID() { + return MID; + } + + public void setMID(int MID) { + this.MID = MID; + } + + public int getRID() { + return RID; + } + + public void setRID(int RID) { + this.RID = RID; + } + + public com.juick.User getUser() { + return User; + } + + public void setUser(com.juick.User user) { + User = user; + } + + public String getText() { + return Text; + } + + public void setText(String text) { + Text = text; + } + + public Date getDate() { + return date; + } + + public void setDate(Date date) { + + this.date = date; + } + + public Optional<com.juick.PM> getPM() { + return PM; + } + + public void setPM(com.juick.PM PM) { + this.PM = Optional.ofNullable(PM); + } + + public Optional<Recommendation> getRecommendation() { + return Recommendation; + } + public void setRecommendation(Recommendation recommendation) { + this.Recommendation = Optional.ofNullable(recommendation); + } + + public boolean isLiked() { + return liked; + } + + public void setLiked(boolean liked) { + this.liked = liked; + } +} diff --git a/juick-core/src/main/java/com/juick/PM.java b/juick-core/src/main/java/com/juick/PM.java new file mode 100644 index 00000000..658cafc9 --- /dev/null +++ b/juick-core/src/main/java/com/juick/PM.java @@ -0,0 +1,16 @@ +package com.juick; + +/** + * Created by vt on 08/02/16. + */ +public class PM { + private User to; + + public User getTo() { + return to; + } + + public PM(User to) { + this.to = to; + } +} diff --git a/juick-core/src/main/java/com/juick/Place.java b/juick-core/src/main/java/com/juick/Place.java new file mode 100644 index 00000000..7174ed6e --- /dev/null +++ b/juick-core/src/main/java/com/juick/Place.java @@ -0,0 +1,33 @@ +/* + * 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; + +/** + * + * @author Ugnich Anton + */ +public class Place { + + public int pid = 0; + public double lat = 0; + public double lon = 0; + public String name = null; + public int users = 0; + public int messages = 0; + public int distance = 0; +} diff --git a/juick-core/src/main/java/com/juick/Recommendation.java b/juick-core/src/main/java/com/juick/Recommendation.java new file mode 100644 index 00000000..4cfa67c2 --- /dev/null +++ b/juick-core/src/main/java/com/juick/Recommendation.java @@ -0,0 +1,16 @@ +package com.juick; + +/** + * Created by vt on 08/02/16. + */ +public class Recommendation { + User from; + + public User getFrom() { + return from; + } + + public Recommendation(User from) { + this.from = from; + } +} diff --git a/juick-core/src/main/java/com/juick/Tag.java b/juick-core/src/main/java/com/juick/Tag.java new file mode 100644 index 00000000..119aad99 --- /dev/null +++ b/juick-core/src/main/java/com/juick/Tag.java @@ -0,0 +1,41 @@ +/* + * 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; + +import java.util.Objects; + +/** + * + * @author Ugnich Anton + */ +public class Tag { + + public String Name = null; + public int TID = 0; + public int SynonymID = 0; + public int UsageCnt = 0; + + @Override + public boolean equals(Object o) { + boolean equal = false; + if (o != null && o instanceof Tag) { + equal = Objects.equals(Name, ((Tag) o).Name); + } + return equal; + } +} diff --git a/juick-core/src/main/java/com/juick/User.java b/juick-core/src/main/java/com/juick/User.java new file mode 100644 index 00000000..b9111d8f --- /dev/null +++ b/juick-core/src/main/java/com/juick/User.java @@ -0,0 +1,96 @@ +/* + * 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; + +/** + * + * @author Ugnich Anton + */ +public class User { + + private int UID = 0; + private String UName = null; + public Object Avatar = null; + private String FullName = null; + private String JID = null; + public int MessagesCount = 0; + private String AuthHash = null; + public boolean Banned = false; + + public User() { + } + + public User(User u) { + setUID(u.getUID()); + setUName(u.getUName()); + Avatar = u.Avatar; + setFullName(u.getFullName()); + setJID(u.getJID()); + MessagesCount = u.MessagesCount; + setAuthHash(u.getAuthHash()); + Banned = u.Banned; + } + + @Override + public boolean equals(Object obj) { + return (obj instanceof User && ((User) obj).getUID() == this.getUID()); + } + + public int getUID() { + return UID; + } + + public void setUID(int UID) { + this.UID = UID; + } + + public String getUName() { + return UName; + } + + public void setUName(String UName) { + this.UName = UName; + } + + public String getFullName() { + return FullName; + } + + public void setFullName(String fullName) { + FullName = fullName; + } + + public String getJID() { + return JID; + } + + public void setJID(String JID) { + this.JID = JID; + } + + public String getAuthHash() { + return AuthHash; + } + + public void setAuthHash(String authHash) { + AuthHash = authHash; + } + public Integer getUnreadCount() { + return MessagesCount; + } +} diff --git a/juick-core/src/main/java/com/juick/json/JSONSerializer.java b/juick-core/src/main/java/com/juick/json/JSONSerializer.java new file mode 100644 index 00000000..142cacf0 --- /dev/null +++ b/juick-core/src/main/java/com/juick/json/JSONSerializer.java @@ -0,0 +1,54 @@ +/* + * To change this license header, choose License Headers in Project Properties. + * To change this template file, choose Tools | Templates + * and open the template in the editor. + */ +package com.juick.json; + +import java.text.ParseException; +import java.util.Iterator; +import java.util.List; +import org.json.JSONObject; + +/** + * + * @author vt + * @param <T> + */ +public abstract class JSONSerializer<T> { + + /** + * + * @param json + * @return + */ + public abstract T deserialize(JSONObject json) throws ParseException; + + /** + * + * @param obj + * @return + */ + public abstract JSONObject serialize(T obj); + + /** + * + * @param objs + * @return + */ + public String serializeList(List<T> objs) { + String json = "["; + + Iterator<T> i = objs.iterator(); + while (i.hasNext()) { + T m = i.next(); + if (json.length() > 1) { + json += ","; + } + json += serialize(m).toString(); + } + + json += "]"; + return json; + } +} diff --git a/juick-core/src/main/java/com/juick/json/MessageSerializer.java b/juick-core/src/main/java/com/juick/json/MessageSerializer.java new file mode 100644 index 00000000..e8402d51 --- /dev/null +++ b/juick-core/src/main/java/com/juick/json/MessageSerializer.java @@ -0,0 +1,138 @@ +/* + * 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.json; + +import com.juick.Message; +import org.json.JSONArray; +import org.json.JSONException; +import org.json.JSONObject; + +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.TimeZone; +import java.util.logging.Level; +import java.util.logging.Logger; + +/** + * + * @author Ugnich Anton + */ +public class MessageSerializer extends JSONSerializer<Message> { + + private final static Logger LOGGER = Logger.getLogger(MessageSerializer.class.getName()); + + UserSerializer userSerializer = new UserSerializer(); + PlaceSerializer placeSerializer = new PlaceSerializer(); + + final SimpleDateFormat df; + + public MessageSerializer() { + df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + df.setTimeZone(TimeZone.getTimeZone("UTC")); + } + + @Override + public Message deserialize(JSONObject json) throws JSONException, ParseException { + com.juick.Message jmsg = new com.juick.Message(); + jmsg.setMID(json.getInt("mid")); + if (json.has("rid")) { + jmsg.setRID(json.getInt("rid")); + } + if (json.has("replyto")) { + jmsg.ReplyTo = json.getInt("replyto"); + } + + jmsg.FriendsOnly = json.has("friendsonly"); + jmsg.ReadOnly = json.has("readonly"); + + jmsg.setText(json.getString("body").replace(""", "\"")); + jmsg.setUser(userSerializer.deserialize(json.getJSONObject("user"))); + + jmsg.setDate(df.parse(json.getString("timestamp"))); + + if (json.has("tags")) { + JSONArray tags = json.getJSONArray("tags"); + for (int n = 0; n < tags.length(); n++) { + jmsg.Tags.add(tags.getString(n).replace(""", "\"")); + } + } + + if (json.has("replies")) { + jmsg.Replies = json.getInt("replies"); + } + + if (json.has("photo")) { + jmsg.Photo = json.getJSONObject("photo").getString("small"); + } + + return jmsg; + } + + @Override + public JSONObject serialize(Message msg) { + JSONObject json = new JSONObject(); + + try { + if (msg.getMID() > 0) { + json.put("mid", msg.getMID()); + } + if (msg.getRID() > 0) { + json.put("rid", msg.getRID()); + } + if (msg.ReplyTo > 0) { + json.put("replyto", msg.ReplyTo); + } + if (msg.FriendsOnly) { + json.put("friendsonly", 1); + } + if (msg.ReadOnly) { + json.put("readonly", 1); + } + if (msg.getText() != null) { + json.put("body", msg.getText()); + } + if (msg.getDate() != null) { + json.put("timestamp", df.format(msg.getDate())); + } + if (msg.getUser() != null) { + json.put("user", userSerializer.serialize(msg.getUser())); + } + if (msg.Tags != null && msg.Tags.size() > 0) { + json.put("tags", new JSONArray(msg.Tags)); + } + if (msg.Replies > 0) { + json.put("replies", msg.Replies); + } + if (msg.Place != null) { + json.put("place", placeSerializer.serialize(msg.Place)); + } + if (msg.AttachmentType != null) { + String fname = msg.getMID() + (msg.getRID() > 0 ? "-" + msg.getRID() : "") + "." + msg.AttachmentType; + JSONObject photo = new JSONObject(); + photo.put("thumbnail", "http://i.juick.com/ps/" + fname); + photo.put("small", "http://i.juick.com/photos-512/" + fname); + photo.put("medium", "http://i.juick.com/photos-1024/" + fname); + json.put("photo", photo); + } + } catch (JSONException e) { + LOGGER.log(Level.SEVERE, "JSON Exception", e); + } + + return json; + } +} diff --git a/juick-core/src/main/java/com/juick/json/PlaceSerializer.java b/juick-core/src/main/java/com/juick/json/PlaceSerializer.java new file mode 100644 index 00000000..f433f7f0 --- /dev/null +++ b/juick-core/src/main/java/com/juick/json/PlaceSerializer.java @@ -0,0 +1,73 @@ +/* + * 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.json; + +import com.juick.Place; +import org.json.JSONException; +import org.json.JSONObject; + +/** + * + * @author Ugnich Anton + */ +public class PlaceSerializer extends JSONSerializer<Place> { + + @Override + public Place deserialize(JSONObject json) throws JSONException { + Place jplace = new Place(); + + jplace.pid = json.getInt("pid"); + jplace.lat = json.getDouble("lat"); + jplace.lon = json.getDouble("lon"); + jplace.name = json.getString("name").replace(""", "\""); + if (json.has("users")) { + jplace.users = json.getInt("users"); + } + if (json.has("messages")) { + jplace.messages = json.getInt("messages"); + } + if (json.has("distance")) { + jplace.distance = json.getInt("distance"); + } + + return jplace; + } + + @Override + public JSONObject serialize(Place place) { + JSONObject json = new JSONObject(); + + try { + if (place.pid > 0) { + json.put("pid", place.pid); + } + if (place.lat >= -90 && place.lat <= 90) { + json.put("lat", place.lat); + } + if (place.lon >= -180 && place.lon <= 180) { + json.put("lon", place.lon); + } + if (place.name != null) { + json.put("name", place.name); + } + } catch (JSONException e) { + } + + return json; + } +} diff --git a/juick-core/src/main/java/com/juick/json/UserSerializer.java b/juick-core/src/main/java/com/juick/json/UserSerializer.java new file mode 100644 index 00000000..390f4bf6 --- /dev/null +++ b/juick-core/src/main/java/com/juick/json/UserSerializer.java @@ -0,0 +1,66 @@ +/* + * 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.json; + +import com.juick.User; +import org.json.JSONException; +import org.json.JSONObject; + +/** + * + * @author Ugnich Anton + */ +public class UserSerializer extends JSONSerializer<User> { + + @Override + public User deserialize(JSONObject json) throws JSONException { + User juser = new User(); + juser.setUID(json.getInt("uid")); + juser.setUName(json.getString("uname")); + if (json.has("fullname")) { + juser.setFullName(json.getString("fullname")); + } + return juser; + } + + @Override + public JSONObject serialize(User user) { + JSONObject json = new JSONObject(); + + try { + if (user.getUID() > 0) { + json.put("uid", user.getUID()); + } + if (user.getUName() != null) { + json.put("uname", user.getUName()); + } + if (user.getFullName() != null) { + json.put("fullname", user.getFullName()); + } + if (user.getJID() != null) { + json.put("jid", user.getJID()); + } + if (user.getUnreadCount() > 0) { + json.put("unreadCount", user.getUnreadCount()); + } + } catch (JSONException e) { + } + + return json; + } +} diff --git a/juick-core/src/main/java/com/juick/server/AdsQueries.java b/juick-core/src/main/java/com/juick/server/AdsQueries.java new file mode 100644 index 00000000..06590817 --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/AdsQueries.java @@ -0,0 +1,51 @@ +/* + * Juick + * Copyright (C) 2008-2011, ugnich + * + * 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 org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; + +/** + * + * @author ugnich + */ +public class AdsQueries { + + public static int getAdMID(JdbcTemplate sql, int uid) { + if (uid > 0) { + try { + return sql.queryForObject("SELECT message_id FROM ads_messages " + + "WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? " + + "AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", + Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } else { + try { + return sql.queryForObject("SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", Integer.class); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } + } + + public static void logAdMID(JdbcTemplate sql, int uid, int mid) { + sql.update("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())", uid, mid); + } +} diff --git a/juick-core/src/main/java/com/juick/server/CrosspostQueries.java b/juick-core/src/main/java/com/juick/server/CrosspostQueries.java new file mode 100644 index 00000000..04898d0a --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/CrosspostQueries.java @@ -0,0 +1,62 @@ +/* + * Juick + * Copyright (C) 2008-2013, 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 org.apache.commons.lang3.tuple.Pair; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; + +import java.util.Optional; + +/** + * + * @author ugnich + */ +public class CrosspostQueries { + + public static Optional<Pair<String, String>> getTwitterTokens(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } + } + + public static Optional<String> getFacebookToken(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL " + + "AND crosspost=1", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } + } + + public static Optional<Pair<String, String>> getVKTokens(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } + } +} diff --git a/juick-core/src/main/java/com/juick/server/MessagesQueries.java b/juick-core/src/main/java/com/juick/server/MessagesQueries.java new file mode 100644 index 00000000..a0242543 --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/MessagesQueries.java @@ -0,0 +1,663 @@ +/* + * 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 com.juick.Message; +import com.juick.User; +import com.juick.server.helpers.PrivacyOpts; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.dao.IncorrectResultSizeDataAccessException; +import org.springframework.jdbc.core.ConnectionCallback; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; + +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.Statement; +import java.sql.Types; +import java.util.ArrayList; +import java.util.Collections; +import java.util.List; +import java.util.logging.Logger; + +/** + * + * @author Ugnich Anton + */ +public class MessagesQueries { + + private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName()); + + public static int createMessage(JdbcTemplate sql, int uid, String txt, String attachment, List<com.juick.Tag> tags) { + KeyHolder holder = new GeneratedKeyHolder(); + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", + Statement.RETURN_GENERATED_KEYS); + stmt.setInt(1, uid); + if (attachment != null) { + stmt.setString(2, attachment); + } else { + stmt.setNull(2, Types.VARCHAR); + } + return stmt; + }, holder); + + int mid = holder.getKey().intValue(); + + + if (mid > 0) { + + String tagsNames = ""; + String tagsIDs = ""; + + for (int i = 0; i < tags.size(); i++) { + if (i > 0) { + tagsNames += " "; + tagsIDs += ","; + } + tagsNames += tags.get(i).Name; + tagsIDs += "(" + mid + "," + tags.get(i).TID + ")"; + } + if (tags.size() > 0) { + sql.execute("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); + } + final String finalTagsNames = tagsNames; + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) " + + "VALUES (?,?,?)", Statement.NO_GENERATED_KEYS); + stmt.setInt(1, mid); + if (finalTagsNames.isEmpty()) { + stmt.setNull(2, Types.VARCHAR); + } else { + stmt.setString(2, finalTagsNames); + } + stmt.setString(3, txt); + return stmt; + }); + } + + return mid; + } + + public static int createReply(JdbcTemplate sql, int mid, int rid, int uid, String txt, String attachment) { + int ridnew = getReplyIDIncrement(sql, mid); + + sql.update( con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id," + + "replyto,attach,txt) VALUES (?,?,?,?,?,?)", Statement.NO_GENERATED_KEYS); + stmt.setInt(1, mid); + stmt.setInt(2, ridnew); + stmt.setInt(3, uid); + stmt.setInt(4, rid); + if (attachment != null) { + stmt.setString(5, attachment); + } else { + stmt.setNull(5, Types.VARCHAR); + } + stmt.setString(6, txt); + return stmt; + }); + + if (ridnew > 0) { + sql.update("UPDATE messages SET replies=replies+1 WHERE message_id=?", mid); + } + + return ridnew; + } + + public static int getReplyIDIncrement(JdbcTemplate sql, int mid) { + return sql.execute((ConnectionCallback<Integer>) conn -> { + conn.setAutoCommit(false); + try (PreparedStatement ps = conn.prepareStatement("UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?")) { + ps.setInt(1, mid); + if (ps.executeUpdate() != 1) { + throw new IncorrectResultSizeDataAccessException("Cannot find a message to update: " + mid, 1, 0); + } + } + try (PreparedStatement ps = conn.prepareStatement("SELECT maxreplyid FROM messages WHERE message_id=?")) { + ps.setInt(1, mid); + try (ResultSet resultSet = ps.executeQuery()) { + if (resultSet.next()) { + int rv = resultSet.getInt(1); + conn.commit(); + return rv; + } else { + throw new IncorrectResultSizeDataAccessException("while getting getReplyIDIncrement, mid=" + mid, 1, 0); + } + } + } + }); + + } + + public static boolean recommendMessage(JdbcTemplate sql, int mid, int vuid) { + boolean res = sql.update("INSERT IGNORE INTO favorites(user_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + if (res) { + sql.update("UPDATE messages SET likes=likes+1 WHERE message_id=?", mid); + } + return res; + } + + public static boolean canViewThread(JdbcTemplate sql, int mid, int uid) { + PrivacyOpts privacyOpts; + try { + privacyOpts = sql.queryForObject("SELECT user_id,privacy FROM messages WHERE messages.message_id=?", + (rs, rowNum) -> { + PrivacyOpts res = new PrivacyOpts(); + res.setUid(rs.getInt(1)); + res.setPrivacy(rs.getInt(2)); + return res; + }, mid); + } catch (EmptyResultDataAccessException e) { + return true; + } + return privacyOpts.getPrivacy() >= 0 + || uid == privacyOpts.getUid() + || ((privacyOpts.getPrivacy() == -1 || privacyOpts.getPrivacy() == -2) && uid > 0 + && UserQueries.isInWL(sql, privacyOpts.getUid(), uid)); + } + + public static boolean isReadOnly(JdbcTemplate sql, int mid) { + try { + return sql.queryForObject("SELECT readonly FROM messages WHERE message_id=?", new Object[]{mid}, Integer.class) == 1; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static boolean isSubscribed(JdbcTemplate sql, int uid, int mid) { + try { + return sql.queryForObject("SELECT 1 FROM subscr_messages WHERE suser_id=? AND message_id=?", new Object[]{uid, mid}, Integer.class) == 1; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static int getMessagePrivacy(JdbcTemplate sql, int mid) { + try { + return sql.queryForObject("SELECT privacy FROM messages WHERE message_id=?", new Object[]{mid}, Integer.class); + } catch (EmptyResultDataAccessException e) { + return -4; + } + } + + + public static com.juick.Message getMessage(JdbcTemplate sql, int mid) { + try { + return sql.queryForObject("SELECT messages.user_id,users.nick," + + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," + + "messages.ts," + + "messages.readonly,messages.privacy,messages.replies," + + "messages.attach,messages.place_id,messages.lat," + + "messages.lon,messages.likes,messages.hidden," + + "txt.tags,txt.repliesby,txt.txt FROM messages " + + "INNER JOIN users ON messages.user_id=users.id " + + "INNER JOIN messages_txt AS txt " + + "ON messages.message_id=txt.message_id " + + "WHERE messages.message_id=?", new Object[]{mid}, + (rs, rowNum) -> { + Message msg = new Message(); + msg.setMID(mid); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.TimeAgo = rs.getInt(3); + msg.setDate(rs.getTimestamp(4)); + msg.ReadOnly = rs.getBoolean(5); + msg.Privacy = rs.getInt(6); + msg.FriendsOnly = msg.Privacy < 0; + msg.Replies = rs.getInt(7); + msg.AttachmentType = rs.getString(8); + //if (rs.getInt(9) > 0) { + // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9)); + //} + if (rs.getDouble(10) != 0) { + msg.Place = new com.juick.Place(); + msg.Place.lat = rs.getDouble(10); + msg.Place.lon = rs.getDouble(11); + } + msg.Likes = rs.getInt(12); + msg.Hidden = rs.getBoolean(13); + msg.parseTags(rs.getString(14)); + msg.RepliesBy = rs.getString(15); + msg.setText(rs.getString(16)); + return msg; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static com.juick.Message getReply(JdbcTemplate sql, int mid, int rid) { + try { + return sql.queryForObject("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=?", + new Object[]{mid, rid}, (rs, num) -> { + Message msg = new Message(); + msg.setMID(mid); + msg.setRID(rid); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.ReplyTo = rs.getInt(3); + msg.setDate(rs.getTimestamp(4)); + msg.AttachmentType = rs.getString(5); + msg.setText(rs.getString(6)); + return msg; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static User getMessageAuthor(JdbcTemplate sql, int mid) { + try { + return sql.queryForObject("SELECT messages.user_id,users.nick " + + "FROM messages INNER JOIN users " + + "ON messages.user_id=users.id WHERE messages.message_id=?", + new Object[]{mid}, (rs, num) -> { + User res = new com.juick.User(); + res.setUID(rs.getInt(1)); + res.setUName(rs.getString(2)); + return res; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static List<com.juick.Tag> getMessageTags(JdbcTemplate sql, int mid) { + return sql.query("SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " + + "INNER JOIN messages_tags ON (messages_tags.message_id=? AND messages_tags.tag_id=tags.tag_id)", + new Object[]{mid}, (rs, num) -> { + com.juick.Tag t = new com.juick.Tag(); + t.TID = rs.getInt(1); + t.SynonymID = rs.getInt(2); + t.Name = rs.getString(3); + t.UsageCnt = rs.getInt(4); + return t; + }); + } + + public static List<Integer> getMessageTagsIDs(JdbcTemplate sql, int mid) { + return sql.queryForList("SELECT tag_id FROM messages_tags WHERE message_id=?", new Object[] {mid}, Integer.class); + } + + public static List<String> getMessageRecommendations(JdbcTemplate sql, int mid) { + return sql.queryForList("SELECT users.nick FROM favorites INNER JOIN users " + + "ON (favorites.message_id=? AND favorites.user_id=users.id)", + new Object[] {mid}, String.class); + } + + public static List<Integer> getAll(JdbcTemplate sql, int visitor_uid, int before) { + if (visitor_uid > 1) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id<? AND hidden=0" + + " AND (privacy>0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?)" + + " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20", + new Object[]{before, visitor_uid, visitor_uid}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE hidden=0" + + " AND (privacy>0 OR user_id=?)" + + " AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?)" + + " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20", + new Object[]{visitor_uid, visitor_uid}, Integer.class); + } + } else { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id<?" + + " AND hidden=0 AND privacy>0 AND user_id NOT IN (SELECT id from users WHERE banned=1) " + + " ORDER BY message_id DESC LIMIT 20", + new Object[]{before}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE hidden=0 AND privacy>0" + + " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20", + Integer.class); + } + } + } + + public static List<Integer> getTag(JdbcTemplate sql, int tid, int visitor_uid, int before, int cnt) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM (tags INNER JOIN messages_tags " + + "ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) " + + "INNER JOIN messages USING(message_id) WHERE messages.message_id<? " + + "AND (messages.privacy>0 OR messages.user_id=?) ORDER BY message_id DESC LIMIT ?", + new Object[]{tid, tid, before, visitor_uid, cnt}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM (tags INNER JOIN messages_tags " + + "ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) " + + "INNER JOIN messages USING(message_id) WHERE messages.privacy>0 OR messages.user_id=? " + + "ORDER BY message_id DESC LIMIT ?", + new Object[]{tid, tid, visitor_uid, cnt}, Integer.class); + } + } + + public static List<Integer> getTags(JdbcTemplate sql, String tids, int visitor_uid, int before, int cnt) { + if (before > 0) { + return sql.queryForList("SELECT messages.message_id FROM messages_tags " + + "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") " + + "AND messages.message_id<? AND (messages.privacy>0 OR messages.user_id=?) " + + "ORDER BY messages.message_id DESC LIMIT ?", new Object[]{before, visitor_uid, cnt}, Integer.class); + } else { + return sql.queryForList("SELECT messages.message_id FROM messages_tags " + + "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") " + + "AND (messages.privacy>0 OR messages.user_id=?) " + + "ORDER BY messages.message_id DESC LIMIT ?", new Object[]{visitor_uid, cnt}, Integer.class); + } + } + + public static List<Integer> getPlace(JdbcTemplate sql, int place_id, int visitor_uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE place_id=? AND message_id<? " + + "AND (privacy>0 OR user_id=?) ORDER BY message_id DESC LIMIT 20", + new Object[]{place_id, before, visitor_uid}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE place_id=? AND (privacy>0 OR user_id=?) " + + "ORDER BY message_id DESC LIMIT 20", new Object[]{place_id, visitor_uid}, Integer.class); + } + } + + public static List<Integer> getMyFeed(JdbcTemplate sql, int uid, int before) { + List<Integer> mids; + if (before > 0) { + mids = sql.queryForList("SELECT message_id FROM messages " + + "INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) " + + "WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " + + "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20", + Integer.class, uid, before, uid); + } else { + mids = sql.queryForList("SELECT message_id FROM messages " + + "INNER JOIN subscr_users ON (subscr_users.suser_id=? " + + "AND subscr_users.user_id=messages.user_id) " + + "WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " + + "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20", + Integer.class, uid, uid); + } + + + if (before > 0) { + mids.addAll(sql.queryForList("SELECT message_id FROM messages " + + "WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20", Integer.class, + uid, before)); + } else { + mids.addAll(sql.queryForList("SELECT message_id FROM messages " + + "WHERE user_id=? ORDER BY message_id DESC LIMIT 20", Integer.class, uid)); + } + + Collections.sort(mids, Collections.reverseOrder()); + int remove = mids.size() - 20; + for (int i = 0; i < remove; i++) { + mids.remove(20); + } + + return mids; + } + + public static List<Integer> getPrivate(JdbcTemplate sql, int uid, int before) { + + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND privacy<0 AND message_id<? " + + "ORDER BY message_id DESC LIMIT 20", new Object[]{uid, before}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND privacy<0 " + + "ORDER BY message_id DESC LIMIT 20", new Object[]{uid}, Integer.class); + } + } + + public static List<Integer> getDiscussions(JdbcTemplate sql, int uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM subscr_messages WHERE suser_id=? AND message_id<? " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, uid, before); + } else { + return sql.queryForList("SELECT message_id FROM subscr_messages WHERE suser_id=? " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, uid); + } + } + + public static List<Integer> getRecommended(JdbcTemplate sql, int uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM favorites WHERE user_id " + + "IN (SELECT user_id FROM subscr_users WHERE suser_id=?) AND message_id<? " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, uid, before); + } else { + return sql.queryForList("SELECT message_id FROM favorites WHERE user_id " + + "IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, uid); + } + } + + public static List<Integer> getPopular(JdbcTemplate sql, int visitor_uid, int before) { + + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id<? AND privacy>0 " + + "AND popular>0 AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, before, visitor_uid); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE privacy>0 " + + "AND popular>0 AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, visitor_uid); + } + } + + public static List<Integer> getPhotos(JdbcTemplate sql, int visitor_uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id<? AND (privacy>0 OR user_id=?) " + + "AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) " + + "AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, before, visitor_uid, visitor_uid); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE (privacy>0 OR user_id=?) " + + "AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) " + + "AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, visitor_uid, visitor_uid); + } + } + + public static List<Integer> getSearch(JdbcTemplate sql, JdbcTemplate sqlSearch, String search, int before) { + List<Integer> mids; + + if (before > 0) { + mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE MATCH(?) AND id<? " + + "ORDER BY id DESC LIMIT 25", new Object[]{search, before}, Integer.class); + } else { + mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE MATCH(?) " + + "ORDER BY id DESC LIMIT 25", new Object[]{search}, Integer.class); + } + if (mids.size() > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id " + + "IN (" + Utils.convertArrayInt2String(mids) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20", + Integer.class); + } + return mids; + } + + public static List<Integer> getUserBlog(JdbcTemplate sql, int UID, int privacy, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND message_id<? AND privacy>=" + + privacy + " ORDER BY message_id DESC LIMIT 20", new Object[]{UID, before}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + + " ORDER BY message_id DESC LIMIT 20", new Object[]{UID}, Integer.class); + } + } + + public static List<Integer> getUserTag(JdbcTemplate sql, int UID, int TID, int privacy, int before) { + if (before > 0) { + return sql.queryForList("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_id<? AND messages.privacy>=" + privacy + + " ORDER BY messages.message_id DESC LIMIT 20", + new Object[] {UID, TID, before}, Integer.class); + } else { + return sql.queryForList("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>=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20", + new Object[]{UID, TID}, Integer.class); + } + } + + public static List<Integer> getUserRecommendations(JdbcTemplate sql, int UID, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM favorites " + + "WHERE user_id=? AND message_id<? " + + "ORDER BY message_id DESC LIMIT 20", Integer.class, + new Object[]{UID, before}); + } else { + return sql.queryForList("SELECT message_id FROM favorites " + + "WHERE user_id=? ORDER BY message_id DESC LIMIT 20", + Integer.class, new Object[]{UID}); + + } + } + + public static List<Integer> getUserPhotos(JdbcTemplate sql, int UID, int privacy, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND message_id<? AND privacy>=" + + privacy + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", + new Object[] {UID, before}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", + new Object[] {UID}, Integer.class); + } + } + + public static List<Integer> getUserSearch(JdbcTemplate sql, JdbcTemplate sqlSearch, int UID, String search, int privacy, int before) { + List<Integer> mids; + + + if (before > 0) { + mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE user_id=? AND MATCH(?) AND id<? " + + "ORDER BY id DESC LIMIT 20", new Object[] {UID, search, before}, Integer.class); + } else { + mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE user_id=? AND MATCH(?) " + + "ORDER BY id DESC LIMIT 20", new Object[] {UID, search}, Integer.class); + } + + if (mids.size() > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id IN (" + + Utils.convertArrayInt2String(mids) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC", + Integer.class); + } + return mids; + } + + public static List<com.juick.Message> getMessages(JdbcTemplate sql, List<Integer> mids) { + List<com.juick.Message> msgs = new ArrayList<>(20); + + return sql.query("SELECT messages.message_id,messages.user_id,users.nick," + + "messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt," + + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," + + "messages.ts,messages.replies," + + "messages_txt.repliesby,messages.attach,messages.lat," + + "messages.lon,messages.likes " + + "FROM (messages INNER JOIN messages_txt " + + "ON messages.message_id=messages_txt.message_id) " + + "INNER JOIN users ON messages.user_id=users.id " + + "WHERE messages.message_id " + + "IN (" + Utils.convertArrayInt2String(mids) + ") " + + "ORDER BY messages.message_id DESC", (rs, rowNum) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setUser(new User()); + + msg.setMID(rs.getInt(1)); + msg.getUser().setUID(rs.getInt(2)); + msg.getUser().setUName(rs.getString(3)); + if (rs.getString(4) != null) { + msg.parseTags(rs.getString(4)); + } + msg.ReadOnly = rs.getInt(5) == 1; + msg.Privacy = rs.getInt(6); + msg.FriendsOnly = msg.Privacy < 0; + msg.setText(rs.getString(7)); + msg.TimeAgo = rs.getInt(8); + msg.setDate(rs.getTimestamp(9)); + msg.Replies = rs.getInt(10); + msg.RepliesBy = rs.getString(11); + msg.AttachmentType = rs.getString(12); + if (rs.getDouble(13) != 0) { + msg.Place = new com.juick.Place(); + msg.Place.lat = rs.getDouble(13); + msg.Place.lon = rs.getDouble(14); + } + msg.Likes = rs.getInt(15); + return msg; + }); + } + + public static List<com.juick.Message> getReplies(JdbcTemplate sql, int mid) { + return sql.query("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt," + + "TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),replies.ts,replies.attach FROM replies INNER JOIN users " + + "ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC", + new Object[]{mid}, (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setMID(mid); + msg.setRID(rs.getInt(1)); + msg.ReplyTo = rs.getInt(2); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(3)); + msg.getUser().setUName(rs.getString(4)); + msg.getUser().Banned = rs.getBoolean(5); + msg.setText(rs.getString(6)); + msg.TimeAgo = rs.getInt(7); + msg.setDate(rs.getTimestamp(8)); + msg.AttachmentType = rs.getString(9); + return msg; + }); + } + + public static boolean setMessagePopular(JdbcTemplate sql, int mid, int popular) { + boolean ret; + switch (popular) { + case -2: + ret = sql.update("UPDATE messages SET hidden=1 WHERE message_id=?", mid) > 0; + break; + case -1: + ret = sql.update("UPDATE messages SET popular=0 WHERE message_id=?", mid) > 0; + break; + default: + ret = sql.update("UPDATE messages SET popular=? WHERE message_id=?", popular, mid) > 0; + break; + } + if (popular == -1) { + ret = sql.update("INSERT INTO top_ignore_messages VALUES (?)", mid) > 0; + } + return ret; + } + + public static boolean setMessagePrivacy(JdbcTemplate sql, int mid) { + return sql.update("UPDATE messages SET privacy=1 WHERE message_id=?", + mid) > 0; + } + public static boolean deleteMessage(JdbcTemplate sql, int uid, int mid) { + if (sql.update("DELETE FROM messages WHERE message_id=? AND user_id=?", mid, uid) > 0) { + sql.update("DELETE FROM messages_txt WHERE message_id=?", mid); + sql.update("DELETE FROM replies WHERE message_id=?", mid); + sql.update("DELETE FROM subscr_messages WHERE message_id=?", mid); + sql.update("DELETE FROM messages_tags WHERE message_id=?", mid); + return true; + } + return false; + } + +} diff --git a/juick-core/src/main/java/com/juick/server/PMQueries.java b/juick-core/src/main/java/com/juick/server/PMQueries.java new file mode 100644 index 00000000..74d5df3e --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/PMQueries.java @@ -0,0 +1,145 @@ +/* + * 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 com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; + +import java.util.List; + +/** + * + * @author Ugnich Anton + */ +public class PMQueries { + + public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) { + boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)", + uid_from, uid_to, body) > 0; + + if (ret) { + sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) " + + "VALUES (?,?,NOW(),1) " + + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1", + uid_from, uid_to); + } + return ret; + } + + public static boolean addPMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)", + uid, jid) > 0; + } + + public static boolean removePMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0; + } + + public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { + List<Integer> res = sql.queryForList("SELECT 1 FROM pm_inroster " + + "WHERE user_id=? AND jid=?", Integer.class, + uid, jid); + return res.size() > 0; + } + + public static String getLastView(JdbcTemplate sql, int uid_from, int uid_to) { + try { + return sql.queryForObject("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?", String.class, + uid_from, uid_to); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { + List<User> qusers = sql.query("SELECT user_id,unread FROM pm_streams " + + "WHERE user_id_to=? " + + "ORDER BY unread DESC, lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.MessagesCount = rs.getInt(2); + return u; + }, uid); + if (!qusers.isEmpty()) { + UserQueries.fillUsersByID(sql, qusers); + } + + return qusers; + } + + public static boolean haveUserInArray(List<com.juick.User> arr, int uid) { + for (User user : arr) { + if (user.getUID() == uid) { + return true; + } + } + return false; + } + + public static List<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) { + List<com.juick.Message> msgs = sql.query("SELECT user_id,txt,ts FROM pm " + + "WHERE (user_id=? AND user_id_to=?) " + + "OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20", + (rs, rowNum) -> { + com.juick.Message msg = new com.juick.Message(); + int uuid = rs.getInt(1); + msg.setUser(new User()); + msg.getUser().setUID(uuid); + msg.setText(rs.getString(2)); + msg.setDate(rs.getTimestamp(3)); + return msg; + }, uid, uid_to, uid, uid_to); + + sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 " + + "WHERE user_id_to=? AND user_id=?", uid, uid_to); + + return msgs; + } + + public static List<com.juick.Message> getLastPMInbox(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts " + + "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); + } + + public static List<com.juick.Message> getLastPMSent(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW())," + + "pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " + + "WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); + } +} diff --git a/juick-core/src/main/java/com/juick/server/PushQueries.java b/juick-core/src/main/java/com/juick/server/PushQueries.java new file mode 100644 index 00000000..54528993 --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/PushQueries.java @@ -0,0 +1,74 @@ +/* + * To change this template, choose Tools | Templates + * and open the template in the editor. + */ +package com.juick.server; + +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.util.StringUtils; + +import java.util.List; +import java.util.Optional; + +/** + * + * @author ugnich + */ +public class PushQueries { + + public static Optional<String> getAndroidRegID(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT regid FROM android WHERE user_id=?", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } + + } + + public static List<String> getAndroidSubscribers(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT regid FROM android INNER JOIN subscr_users " + + "ON (subscr_users.user_id=? AND android.user_id=subscr_users.suser_id)", String.class, uid); + } + + public static List<String> getAndroidTokens(JdbcTemplate sql, List<Integer> uids) { + return sql.queryForList("SELECT regid FROM android INNER JOIN users " + + "ON (users.id=android.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); + } + + public static Optional<String> getWinPhoneURL(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT url FROM winphone WHERE user_id=?", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } + } + + public static List<String> getWinPhoneSubscribers(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT url FROM winphone INNER JOIN subscr_users " + + "ON (subscr_users.user_id=? AND winphone.user_id=subscr_users.suser_id)", String.class, uid); + } + + public static List<String> getWindowsTokens(JdbcTemplate sql, List<Integer> uids) { + return sql.queryForList("SELECT url FROM winphone INNER JOIN users " + + "ON (users.id=winphone.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); + } + + public static Optional<String> getAPNSToken(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT token from ios WHERE user_id=?", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } + } + + public static List<String> getAPNSSubscribers(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT token FROM ios INNER JOIN subscr_users " + + "ON (subscr_users.user_id=? AND ios.user_id=subscr_users.suser_id)", String.class, uid); + } + public static List<String> getAPNSTokens(JdbcTemplate sql, List<Integer> uids) { + return sql.queryForList("SELECT token FROM ios INNER JOIN users " + + "ON (users.id=ios.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); + } + +} diff --git a/juick-core/src/main/java/com/juick/server/SQLHelpers.java b/juick-core/src/main/java/com/juick/server/SQLHelpers.java new file mode 100644 index 00000000..13ebe98a --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/SQLHelpers.java @@ -0,0 +1,168 @@ +/* + * To change this template, choose Tools | Templates + * and open the template in the editor. + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; + +/** + * + * @author ugnich + */ +public class SQLHelpers { + + private static final Logger logger = Logger.getLogger(SQLHelpers.class.getName()); + + public static int execute(Connection sql, String query) { + int ret = -1; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement(query); + ret = stmt.executeUpdate(); + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(null, stmt); + } + return ret; + } + + public static int executeInt(Connection sql, String query, int param) { + int ret = -1; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement(query); + stmt.setInt(1, param); + ret = stmt.executeUpdate(); + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(null, stmt); + } + return ret; + } + + public static int getInt(Connection sql, String query, int defvalue) { + int ret = defvalue; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getInt(1); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static int getInt(Connection sql, String query, int param, int defvalue) { + int ret = defvalue; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setInt(1, param); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getInt(1); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static int getInt(Connection sql, String query, String param, int defvalue) { + int ret = defvalue; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setString(1, param); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getInt(1); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static String getString(Connection sql, String query, int param) { + String ret = null; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setInt(1, param); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getString(1); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static String getString(Connection sql, String query, String param) { + String ret = null; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setString(1, param); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getString(1); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static List<Integer> getArrayInteger(Connection sql, String query, int param) { + List<Integer> ret = new ArrayList<>(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setInt(1, param); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + ret.add(rs.getInt(1)); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql exception", e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return ret; + } +} diff --git a/juick-core/src/main/java/com/juick/server/ShowQueries.java b/juick-core/src/main/java/com/juick/server/ShowQueries.java new file mode 100644 index 00000000..3ddff412 --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/ShowQueries.java @@ -0,0 +1,32 @@ +package com.juick.server; + +import com.juick.User; + +import java.util.List; +import org.springframework.jdbc.core.JdbcTemplate; + +/** + * Created by vt on 10/01/16. + */ +public class ShowQueries { + + public static List<String> getRecommendedUsers(JdbcTemplate sql, User forUser) { + return sql.queryForList("SELECT users.nick FROM subscr_users INNER JOIN users " + + "ON subscr_users.user_id=users.id " + + "WHERE subscr_users.user_id NOT IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + + "AND subscr_users.suser_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + + "AND subscr_users.user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + + "AND subscr_users.user_id!=? AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + + "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10", + String.class, new Object[] {forUser.getUID(), forUser.getUID(), forUser.getUID(), forUser.getUID()}); + } + + public static List<String> getTopUsers(JdbcTemplate sql) { + return sql.queryForList("SELECT users.nick,COUNT(subscr_users.suser_id) AS cnt " + + "FROM (subscr_users INNER JOIN users ON subscr_users.user_id=users.id) " + + "INNER JOIN useroptions ON users.id=useroptions.user_id " + + "WHERE useroptions.privacy_view>0 AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + + "AND users.id!=2 GROUP BY subscr_users.user_id ORDER BY cnt DESC LIMIT 10", + String.class); + } +} diff --git a/juick-core/src/main/java/com/juick/server/SubscriptionsQueries.java b/juick-core/src/main/java/com/juick/server/SubscriptionsQueries.java new file mode 100644 index 00000000..7c30285b --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -0,0 +1,104 @@ +/* + * To change this template, choose Tools | Templates + * and open the template in the editor. + */ +package com.juick.server; + +import com.juick.Tag; +import com.juick.User; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.util.StringUtils; + +import java.util.ArrayList; +import java.util.HashSet; +import java.util.List; +import java.util.Set; +import java.util.logging.Logger; +import java.util.stream.Collectors; + +/** + * + * @author ugnich + */ +public class SubscriptionsQueries { + + private static final Logger logger = Logger.getLogger(SubscriptionsQueries.class.getName()); + + public static List<String> getJIDSubscribedToUser(JdbcTemplate sql, int uid, boolean friendsonly) { + if (friendsonly == false) { + return sql.queryForList("SELECT jids.jid FROM subscr_users INNER JOIN jids " + + "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1", + String.class, uid); + } else { + return sql.queryForList("SELECT jids.jid FROM subscr_users INNER JOIN jids " + + "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1 " + + "AND jids.user_id IN (SELECT wl_user_id FROM wl_users WHERE user_id=?)", String.class, uid, uid); + } + } + + public static List<User> getSubscribedUsers(JdbcTemplate sql, int uid, int mid) { + List<User> userids = UserQueries.getUserReaders(sql, uid); + Set<Integer> set = new HashSet<>(); + set.addAll(userids.stream().map(User::getUID).collect(Collectors.toList())); + List<Integer> tags = MessagesQueries.getMessageTagsIDs(sql, mid); + if (tags.size() > 0) { + List<Integer> tagUsers = sql.queryForList("SELECT suser_id FROM subscr_tags " + + "WHERE tag_id IN (" + StringUtils.arrayToCommaDelimitedString(tags.toArray()) + ") AND suser_id!=" + uid, Integer.class); + set.addAll(tagUsers); + } + return UserQueries.getUsersByID(sql, new ArrayList<>(set)); + } + + public static List<User> getUsersSubscribedToComments(JdbcTemplate sql, int mid, int ignore_uid) { + List<Integer> userids = sql.queryForList("SELECT suser_id FROM subscr_messages WHERE message_id=? AND suser_id!=?", + new Object[] {mid, ignore_uid}, Integer.class); + if (userids.size() > 0) { + return UserQueries.getUsersByID(sql, userids); + } else { + return new ArrayList<>(); + } + } + + public static List<User> getUsersSubscribedToUserRecommendations(JdbcTemplate sql, int uid, int mid, int muid) { + List<Integer> tags = MessagesQueries.getMessageTagsIDs(sql, mid); + + String query = "SELECT suser_id FROM subscr_users WHERE user_id=" + uid; + query += " AND user_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=" + muid + ")"; + query += " AND user_id NOT IN (SELECT suser_id FROM subscr_users WHERE user_id=" + muid + ")"; + query += " AND user_id NOT IN (SELECT suser_id FROM subscr_messages WHERE message_id=" + mid + ")"; + query += " AND user_id NOT IN (SELECT user_id FROM favorites WHERE message_id=" + mid + ")"; + query += " AND user_id NOT IN (SELECT subscr_users.suser_id FROM subscr_users INNER JOIN favorites ON (favorites.message_id=" + mid + " AND subscr_users.user_id=favorites.user_id AND favorites.user_id!=" + uid + "))"; + if (!tags.isEmpty()) { + String tagsStr = Utils.convertArrayInt2String(tags); + query += " AND user_id NOT IN (SELECT suser_id FROM subscr_tags WHERE tag_id IN (" + tagsStr + "))"; + query += " AND user_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))"; + } + List<Integer> userids = sql.queryForList(query, Integer.class); + return UserQueries.getUsersByID(sql, userids); + } + + public static boolean subscribeMessage(JdbcTemplate sql, int mid, int vuid) { + return sql.update("INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + } + public static boolean unSubscribeMessage(JdbcTemplate sql, int mid, int vuid) { + return sql.update("DELETE FROM subscr_messages WHERE message_id=? AND suser_id=?", + mid, vuid) > 0; + } + public static boolean subscribeUser(JdbcTemplate sql, User user, User toUser) { + return sql.update("INSERT IGNORE INTO subscr_users(user_id,suser_id) VALUES (?,?)", + user.getUID(), toUser.getUID()) == 1; + } + public static boolean unSubscribeUser(JdbcTemplate sql, User user, User fromUser) { + return sql.update("DELETE FROM subscr_users WHERE suser_id=? AND user_id=?", + user.getUID(), fromUser.getUID()) > 0; + } + public static boolean subscribeTag(JdbcTemplate sql, User user, Tag toTag) { + return sql.update("INSERT IGNORE INTO subscr_tags(tag_id,suser_id) VALUES (?,?)", + toTag.TID, user.getUID()) == 1; + } + public static boolean unSubscribeTag(JdbcTemplate sql, User user, Tag toTag) { + return sql.update("DELETE FROM subscr_tags WHERE tag_id=? AND suser_id=?", + toTag.TID, user.getUID()) > 0; + } + +} diff --git a/juick-core/src/main/java/com/juick/server/TagQueries.java b/juick-core/src/main/java/com/juick/server/TagQueries.java new file mode 100644 index 00000000..2258a8ea --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/TagQueries.java @@ -0,0 +1,166 @@ +/* + * 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 com.juick.Tag; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; + +import java.sql.PreparedStatement; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.Collections; +import java.util.List; +import java.util.regex.Matcher; +import java.util.regex.Pattern; +import java.util.stream.Collectors; + +/** + * + * @author Ugnich Anton + */ +public class TagQueries { + + public static com.juick.Tag getTag(JdbcTemplate sql, int tid) { + try { + return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?", + new Object[]{tid}, (rs, num) -> { + Tag ret = new Tag(); + ret.TID = tid; + ret.SynonymID = rs.getInt(1); + ret.Name = rs.getString(2); + return ret; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) { + Tag ret = null; + try { + ret = sql.queryForObject("SELECT tag_id,synonym_id,name FROM tags WHERE name=?", + new Object[]{tag}, (rs, rowNum) -> { + Tag ret1 = new Tag(); + ret1.TID = rs.getInt(1); + ret1.SynonymID = rs.getInt(2); + ret1.Name = rs.getString(3); + return ret1; + }); + } catch (EmptyResultDataAccessException e) { + // tag not found + } + if (ret == null && autoCreate) { + ret = new com.juick.Tag(); + ret.TID = createTag(sql, tag); + ret.Name = tag; + } + + return ret; + } + + public static List<com.juick.Tag> getTags(JdbcTemplate sql, String[] tags, boolean autoCreate) { + List<Tag> ret = new ArrayList<>(); + + for (String tag : tags) { + if (!tag.isEmpty()) { + Tag t = getTag(sql, tag, autoCreate); + if (t != null) { + ret.add(t); + } + } + } + + return ret; + } + + public static boolean getTagNoIndex(JdbcTemplate sql, int tag_id) { + try { + return sql.queryForObject("SELECT noindex FROM tags WHERE tag_id=?", Integer.class, tag_id) == 1; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static int createTag(JdbcTemplate sql, String name) { + KeyHolder holder = new GeneratedKeyHolder(); + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO tags(name) VALUES (?)", + Statement.RETURN_GENERATED_KEYS); + stmt.setString(1, name); + return stmt; + }, holder); + + return holder.getKey().intValue(); + } + + public static List<com.juick.Tag> getUserTagsAll(JdbcTemplate sql, int uid) { + return sql.query("SELECT tags.name,COUNT(messages.message_id) " + + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + + "AND messages.message_id=messages_tags.message_id)) " + + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", + (rs, rowNum) -> { + Tag t = new Tag(); + t.Name = rs.getString(1); + t.UsageCnt = rs.getInt(2); + return t; + }, uid); + } + + public static List<String> getUserBLTags(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT tags.name FROM tags INNER JOIN bl_tags " + + "ON (bl_tags.user_id=? AND bl_tags.tag_id=tags.tag_id) ORDER BY tags.name", + String.class, uid); + } + + public static List<String> getPopularTags(JdbcTemplate sql) { + return sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class); + } + public static List<Tag> updateTags(JdbcTemplate sql, int mid, List<Tag> newTags) { + List<Tag> currentTags = MessagesQueries.getMessageTags(sql, mid); + newTags.stream().filter(currentTags::contains) + .forEach(t -> sql.update("DELETE FROM messages_tags WHERE message_id=? AND tag_id=?", mid, t.TID)); + newTags.stream().filter(t -> !currentTags.contains(t)) + .forEach(t -> sql.update("INSERT INTO messages_tags(message_id,tag_id) VALUES (?,?)", mid, t.TID)); + return MessagesQueries.getMessageTags(sql, mid); + } + + public static List<Tag> fromString(JdbcTemplate sql, String txt, boolean tagsOnly) { + String patternString = tagsOnly ? "^(?:(?:\\*[^ \\r\\n\\t]+)|\\s)+$" : "^\\*([^ \\r\\n\\t]+)\\s+([\\s\\S]+)"; + Pattern tagsPattern = Pattern.compile(patternString); + if (tagsPattern.matcher(txt).matches()) { + Pattern tagPattern = Pattern.compile("\\*([^ \\r\\n\\t]+)"); + Matcher tagMatcher = tagPattern.matcher(txt); + List<Tag> tags = new ArrayList<>(); + // TODO: process readonly, private, friends, public + while (tagMatcher.find()) { + for (int i = 1; i <= tagMatcher.groupCount(); i++) { + tags.add(getTag(sql, tagMatcher.group(i), true)); + } + } + return tags; + } + return Collections.emptyList(); + } + public static String toString(List<Tag> tags) { + return tags.stream().map(t -> " *" + t.Name) + .collect(Collectors.joining()); + } +} diff --git a/juick-core/src/main/java/com/juick/server/UserQueries.java b/juick-core/src/main/java/com/juick/server/UserQueries.java new file mode 100644 index 00000000..db380d7a --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/UserQueries.java @@ -0,0 +1,440 @@ +/* + * 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 com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; +import org.springframework.util.StringUtils; + +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.*; + +/** + * + * @author Ugnich Anton + */ +public class UserQueries { + + static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; + + public static class UserMapper implements RowMapper<User> { + @Override + public User mapRow(ResultSet rs, int rowNum) throws SQLException { + User user = new User(); + user.setUID(rs.getInt(1)); + user.setUName(rs.getString(2)); + user.Banned = rs.getBoolean(3); + return user; + } + } + + public static String getSignUpHashByJID(JdbcTemplate sql, String jid) { + String hash = sql.queryForObject("SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", + String.class, jid); + if (hash == null) { + hash = UUID.randomUUID().toString(); + sql.update("INSERT INTO jids(jid,loginhash) VALUES (?,?)", jid, hash); + } + return hash; + } + public static String getSignUpHashByTelegramID(JdbcTemplate sql, Long telegramId, String username) { + try { + return sql.queryForObject("SELECT loginhash FROM telegram WHERE tg_id=? AND user_id IS NULL", + String.class, telegramId); + } catch (EmptyResultDataAccessException e) { + String hash = UUID.randomUUID().toString(); + sql.update("INSERT INTO telegram(tg_id, loginhash, tg_name) VALUES (?, ?, ?)", telegramId, hash, username); + return hash; + } + } + + public static int createUser(JdbcTemplate sql, String username, String password) { + KeyHolder holder = new GeneratedKeyHolder(); + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO users(nick,passw) VALUES (?,?)", + Statement.RETURN_GENERATED_KEYS); + stmt.setString(1, username); + stmt.setString(2, password); + return stmt; + }, holder); + + int uid = holder.getKey().intValue(); + + sql.update("INSERT INTO useroptions(user_id) VALUES (?)", uid); + sql.update("INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid); + + return uid; + } + + public static Optional<User> getUserByUID(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT id, nick,banned FROM users WHERE id=?", + new UserMapper(), uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } + } + + public static User getUserByName(JdbcTemplate sql, String username) { + try { + return sql.queryForObject("SELECT id,nick,banned FROM users WHERE nick=?", + new UserMapper(), + username); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static User getUserByJID(JdbcTemplate sql, String jid) { + try { + return sql.queryForObject("SELECT id,nick,banned FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)", + new UserMapper(), jid); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static List<User> getUsersByName(JdbcTemplate sql, List<String> unames) { + if (!unames.isEmpty()) { + return sql.query("SELECT id,nick,banned FROM users WHERE nick IN (" + Utils.convertArrayString2String(unames) + ")", + new UserMapper()); + } + return Collections.emptyList(); + } + + public static List<User> getUsersByID(JdbcTemplate sql, List<Integer> uids) { + if (!uids.isEmpty()) { + return sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")", + new UserMapper()); + } + return Collections.emptyList(); + } + + public static boolean fillUsersByID(JdbcTemplate sql, List<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).getUID(); + } + + sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + uids + ")", + (rs, num) -> { + User u = new User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }); + + return true; + } + + public static List<com.juick.User> getUsersByJID(JdbcTemplate sql, List<String> jids) { + return sql.query("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, rowNum) -> { + com.juick.User user = new com.juick.User(); + user.setUID(rs.getInt(1)); + user.setUName(rs.getString(2)); + user.setJID(rs.getString(3)); + return user; + }); + } + + public static List<String> getJIDsbyUID(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT jid FROM jids WHERE user_id=? AND active=1", String.class, uid); + } + + public static int getUIDbyJID(JdbcTemplate sql, String jid) { + return sql.queryForObject("SELECT user_id FROM jids WHERE jid=?", Integer.class, jid); + } + + public static int getUIDbyName(JdbcTemplate sql, String uname) { + try { + return sql.queryForObject("SELECT id FROM users WHERE nick=?", Integer.class, uname); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } + + public static int getUIDbyHash(JdbcTemplate sql, String hash) { + try { + return sql.queryForObject("SELECT user_id FROM logins WHERE hash=?", Integer.class, hash); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } + + public static com.juick.User getUserByHash(JdbcTemplate sql, String hash) { + try { + User user = sql.queryForObject("SELECT logins.user_id,users.nick, users.banned FROM logins " + + "INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?", + new UserMapper(), hash); + user.setAuthHash(hash); + return user; + } catch (EmptyResultDataAccessException e) { + return new User(); + } + } + + public static String getHashByUID(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT hash FROM logins WHERE user_id=?", String.class, uid); + } catch (EmptyResultDataAccessException e){ + String hash = generateHash(16); + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)"); + stmt.setInt(1, uid); + stmt.setString(2, hash); + return 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(JdbcTemplate sql, String username, String password) { + try { + String realPassword = sql.queryForObject("SELECT passw FROM users WHERE nick=?", String.class, username); + if (realPassword.equals(password)) { + User user = UserQueries.getUserByName(sql, username); + if (user != null) { + return user.getUID(); + } else { + return -1; + } + } else { + return -1; + } + } catch (EmptyResultDataAccessException e) { + return -1; + } + } + + public static int getUserOptionInt(JdbcTemplate sql, int uid, String option, int defaultValue) { + try { + return sql.queryForObject("SELECT " + option + " FROM useroptions WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return defaultValue; + } + } + + public static void setUserOptionInt(JdbcTemplate sql, int uid, String option, int value) { + sql.update("UPDATE useroptions SET " + option + "=? WHERE user_id=?", value, uid); + } + + public static boolean getCanMedia(JdbcTemplate sql, int uid) { + try { + int res = sql.queryForObject("SELECT users.lastphoto-UNIX_TIMESTAMP() FROM users WHERE id=?", + Integer.class, uid); + return res < 3600; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static boolean isInWL(JdbcTemplate sql, int uid, int check) { + try { + return sql.queryForObject("SELECT 1 FROM wl_users WHERE user_id=? AND wl_user_id=?", + Integer.class, uid, check) == 1; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static boolean isInBL(JdbcTemplate sql, int uid, int check) { + try { + return sql.queryForObject("SELECT 1 FROM bl_users WHERE user_id=? AND bl_user_id=?", + Integer.class, uid, check) == 1; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static boolean isInBLAny(JdbcTemplate sql, int uid, int uid2) { + try { + return sql.queryForObject("SELECT 1 FROM bl_users " + + "WHERE (user_id=? AND bl_user_id=?) " + + "OR (user_id=? AND bl_user_id=?)", new Object[]{uid, uid2, uid2, uid}, Integer.class) == 1; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static List<Integer> checkBL(JdbcTemplate sql, int visitor, List<Integer> uids) { + if (!uids.isEmpty()) { + return sql.queryForList("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + + StringUtils.collectionToCommaDelimitedString(uids) + ")", Integer.class, visitor); + } else { + return new ArrayList<>(); + } + } + + public static boolean isSubscribed(JdbcTemplate sql, int uid, int check) { + try { + return sql.queryForObject("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?", + Integer.class, uid, check) == 1; + } catch (EmptyResultDataAccessException e) { + return false; + } + } + + public static List<Integer> getUserRead(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid); + } + + public static List<com.juick.User> getUserReadLeastPopular(JdbcTemplate sql, int uid, int cnt) { + return sql.query("SELECT users.id,users.nick FROM (subscr_users " + + "INNER JOIN users_subscr ON (subscr_users.suser_id=? " + + "AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users " + + "ON subscr_users.user_id=users.id ORDER BY cnt LIMIT ?", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid, cnt); + } + + public static List<User> getUserReaders(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id, users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.suser_id=users.id " + + "WHERE subscr_users.user_id=? ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid); + } + + public static List<User> getUserFriends(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id,users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.user_id=users.id " + + "WHERE subscr_users.suser_id=? AND users.id!=? " + + "ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid, uid); + } + + public static List<com.juick.User> getUserBLUsers(JdbcTemplate sql, int uid) { + return sql.query("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", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid); + } + + public static boolean linkTwitterAccount(JdbcTemplate sql, User user, String accessToken, + String accessTokenSecret, String screenName) { + if (sql.update("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + + "VALUES (?,?,?,?)" + + " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?", + user.getUID(), accessToken, accessTokenSecret, screenName, accessToken, accessTokenSecret, screenName) > 0) { + return sql.update("INSERT INTO subscr_users(user_id,suser_id,jid) " + + "VALUES (?,1741,'juick\\@twitter.juick.com')", user.getUID()) > 0; + } + return false; + + } + + public static int getStatsIRead(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } + + public static int getStatsMyReaders(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM subscr_users WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } + + public static int getStatsMessages(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM messages WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } + + public static int getStatsReplies(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM replies WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } + } + + public enum ActiveStatus { + Inactive, + Active + } + + public static boolean setActiveStatusForJID(JdbcTemplate sql, String JID, ActiveStatus jidStatus) { + User user = getUserByJID(sql, JID); + if (user != null) { + return sql.update(con -> { + PreparedStatement preparedStatement = con.prepareStatement( + "UPDATE jids SET active=? WHERE user_id=? AND jid=?"); + int newStatus = jidStatus == ActiveStatus.Active ? 1 : 0; + preparedStatement.setInt(1, newStatus); + preparedStatement.setInt(2, user.getUID()); + preparedStatement.setString(3, JID); + return preparedStatement; + + }) >= 0; + } + return false; + } +} diff --git a/juick-core/src/main/java/com/juick/server/Utils.java b/juick-core/src/main/java/com/juick/server/Utils.java new file mode 100644 index 00000000..1968150e --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/Utils.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 <http://www.gnu.org/licenses/>. + */ +package com.juick.server; + +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.List; + +/** + * + * @author Ugnich Anton + */ +public class Utils { + + public static String convertArrayInt2String(List<Integer> mids) { + String q = ""; + for (int i = 0; i < mids.size(); i++) { + if (i > 0) { + q += ","; + } + q += mids.get(i); + } + return q; + } + + public static String convertArrayString2String(List<String> unames) { + String q = ""; + for (int i = 0; i < unames.size(); i++) { + if (i > 0) { + q += ","; + } + q += "\"" + unames.get(i) + "\""; + } + return q; + } + + public static String buildQueryArray(String query1, int length, String query2) { + String ret = query1; + for (int i = 0; i < length; i++) { + if (i > 0) { + ret += ","; + } + ret += "?"; + } + ret += query2; + return ret; + } + + public static void stmtSetStringArray(PreparedStatement stmt, int offset, String strs[]) throws SQLException { + for (int i = 0; i < strs.length; i++) { + stmt.setString(offset + i, strs[i]); + } + } + + 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) { + } + } + } +} diff --git a/juick-core/src/main/java/com/juick/server/helpers/PrivacyOpts.java b/juick-core/src/main/java/com/juick/server/helpers/PrivacyOpts.java new file mode 100644 index 00000000..66cf9410 --- /dev/null +++ b/juick-core/src/main/java/com/juick/server/helpers/PrivacyOpts.java @@ -0,0 +1,29 @@ +package com.juick.server.helpers; + +/** + * Created by vt on 16/01/16. + */ +public class PrivacyOpts { + private int uid; + private int privacy; + + public PrivacyOpts() { + + } + + public int getUid() { + return uid; + } + + public void setUid(int uid) { + this.uid = uid; + } + + public int getPrivacy() { + return privacy; + } + + public void setPrivacy(int privacy) { + this.privacy = privacy; + } +} diff --git a/juick-core/src/main/java/com/juick/xmpp/extensions/JuickMessage.java b/juick-core/src/main/java/com/juick/xmpp/extensions/JuickMessage.java new file mode 100644 index 00000000..885b2375 --- /dev/null +++ b/juick-core/src/main/java/com/juick/xmpp/extensions/JuickMessage.java @@ -0,0 +1,184 @@ +/* + * 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.xmpp.extensions; + +import com.juick.xmpp.utils.XmlUtils; +import com.juick.xmpp.*; +import java.io.IOException; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.TimeZone; + +import org.xmlpull.v1.XmlPullParser; +import org.xmlpull.v1.XmlPullParserException; + +/** + * + * @author Ugnich Anton + */ +public class JuickMessage extends com.juick.Message implements StanzaChild { + + public final static String XMLNS = "http://juick.com/message"; + public final static String TagName = "juick"; + private SimpleDateFormat df; + + public JuickMessage() { + df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + df.setTimeZone(TimeZone.getTimeZone("UTC")); + } + + public JuickMessage(com.juick.Message msg) { + super(msg); + df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + df.setTimeZone(TimeZone.getTimeZone("UTC")); + } + + @Override + public String getXMLNS() { + return XMLNS; + } + + @Override + public JuickMessage parse(XmlPullParser parser) throws XmlPullParserException, IOException, ParseException { + JuickMessage jmsg = new JuickMessage(); + + final String sMID = parser.getAttributeValue(null, "mid"); + if (sMID != null) { + jmsg.setMID(Integer.parseInt(sMID)); + } + final String sRID = parser.getAttributeValue(null, "rid"); + if (sRID != null) { + jmsg.setRID(Integer.parseInt(sRID)); + } + final String sReplyTo = parser.getAttributeValue(null, "replyto"); + if (sReplyTo != null) { + jmsg.ReplyTo = Integer.parseInt(sReplyTo); + } + final String sPrivacy = parser.getAttributeValue(null, "privacy"); + if (sPrivacy != null) { + jmsg.Privacy = Integer.parseInt(sPrivacy); + } + final String sFriendsOnly = parser.getAttributeValue(null, "friendsonly"); + if (sFriendsOnly != null) { + jmsg.FriendsOnly = true; + } + final String sReadOnly = parser.getAttributeValue(null, "readonly"); + if (sReadOnly != null) { + jmsg.ReadOnly = true; + } + String ts = parser.getAttributeValue(null, "timestamp"); + if (ts != null) { + jmsg.setDate(df.parse(ts)); + } + jmsg.AttachmentType = parser.getAttributeValue(null, "attach"); + + while (parser.next() == XmlPullParser.START_TAG) { + final String tag = parser.getName(); + final String xmlns = parser.getNamespace(); + if (tag.equals("body")) { + jmsg.setText(XmlUtils.getTagText(parser)); + } else if (tag.equals(JuickUser.TagName) && xmlns != null && xmlns.equals(JuickUser.XMLNS)) { + jmsg.setUser(new JuickUser().parse(parser)); + } else if (tag.equals("tag")) { + jmsg.Tags.add(XmlUtils.getTagText(parser)); + } else { + XmlUtils.skip(parser); + } + } + return jmsg; + } + + @Override + public String toString() { + String ret = ""; + + ret = "<" + TagName + " xmlns=\"" + XMLNS + "\""; + if (getMID() > 0) { + ret += " mid=\"" + getMID() + "\""; + } + if (getRID() > 0) { + ret += " rid=\"" + getRID() + "\""; + } + if (ReplyTo > 0) { + ret += " replyto=\"" + ReplyTo + "\""; + } + ret += " privacy=\"" + Privacy + "\""; + if (FriendsOnly) { + ret += " friendsonly=\"1\""; + } + if (ReadOnly) { + ret += " readonly=\"1\""; + } + if (getDate() != null) { + ret += " ts=\"" + df.format(getDate()) + "\""; + } + if (AttachmentType != null) { + ret += " attach=\"" + AttachmentType + "\""; + } + ret += ">"; + if (getUser() != null) { + ret += JuickUser.toString(getUser()); + } + if (getText() != null) { + ret += "<body>" + XmlUtils.escape(getText()) + "</body>"; + } + if (!Tags.isEmpty()) { + for (int i = 0; i < Tags.size(); i++) { + ret += "<tag>" + XmlUtils.escape(Tags.get(i)) + "</tag>"; + } + } + ret += "</" + TagName + ">"; + + return ret; + } + + @Override + public boolean equals(Object obj) { + if (!(obj instanceof JuickMessage)) { + return false; + } + JuickMessage jmsg = (JuickMessage) obj; + return (this.getMID() == jmsg.getMID() && this.getRID() == jmsg.getRID()); + } + + @Override + public int compareTo(Object obj) throws ClassCastException { + if (!(obj instanceof JuickMessage)) { + throw new ClassCastException(); + } + JuickMessage jmsg = (JuickMessage) obj; + + if (this.getMID() != jmsg.getMID()) { + if (this.getMID() > jmsg.getMID()) { + return -1; + } else { + return 1; + } + } + + if (this.getRID() != jmsg.getRID()) { + if (this.getRID() < jmsg.getRID()) { + return -1; + } else { + return 1; + } + } + + return 0; + } +} diff --git a/juick-core/src/main/java/com/juick/xmpp/extensions/JuickUser.java b/juick-core/src/main/java/com/juick/xmpp/extensions/JuickUser.java new file mode 100644 index 00000000..edc6749a --- /dev/null +++ b/juick-core/src/main/java/com/juick/xmpp/extensions/JuickUser.java @@ -0,0 +1,75 @@ +/* + * 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.xmpp.extensions; + +import com.juick.xmpp.utils.XmlUtils; +import com.juick.xmpp.*; +import java.io.IOException; +import org.xmlpull.v1.XmlPullParser; +import org.xmlpull.v1.XmlPullParserException; + +/** + * + * @author Ugnich Anton + */ +public class JuickUser extends com.juick.User implements StanzaChild { + + public final static String XMLNS = "http://juick.com/user"; + public final static String TagName = "user"; + + public JuickUser() { + } + + public JuickUser(com.juick.User user) { + super(user); + } + + @Override + public String getXMLNS() { + return XMLNS; + } + + @Override + public JuickUser parse(final XmlPullParser parser) throws XmlPullParserException, IOException { + JuickUser juser = new JuickUser(); + String strUID = parser.getAttributeValue(null, "uid"); + if (strUID != null) { + juser.setUID(Integer.parseInt(strUID)); + } + juser.setUName(parser.getAttributeValue(null, "uname")); + XmlUtils.skip(parser); + return juser; + } + + public static String toString(com.juick.User user) { + String str = "<" + TagName + " xmlns='" + XMLNS + "'"; + if (user.getUID() > 0) { + str += " uid='" + user.getUID() + "'"; + } + if (user.getUName() != null && user.getUName().length() > 0) { + str += " uname='" + XmlUtils.escape(user.getUName()) + "'"; + } + str += "/>"; + return str; + } + + @Override + public String toString() { + return toString(this); + } +} |