diff options
Diffstat (limited to 'src/main')
| -rw-r--r-- | src/main/java/com/juick/service/MessagesServiceImpl.java | 87 | ||||
| -rw-r--r-- | src/main/java/com/juick/service/NullSearchService.java | 3 | ||||
| -rw-r--r-- | src/main/java/com/juick/service/UserServiceImpl.java | 25 | ||||
| -rw-r--r-- | src/main/resources/data-sqlite.sql | 11 | ||||
| -rw-r--r-- | src/main/resources/db/specific/h2/V1.22__schema.sql | 4 | ||||
| -rw-r--r-- | src/main/resources/db/specific/sqlite/V1.22__schema.sql | 377 |
6 files changed, 449 insertions, 58 deletions
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 68d47429..bb50d953 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -296,9 +296,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ if (wasDeleted > 0) { return RecommendStatus.Deleted; } else { - boolean wasAdded = getJdbcTemplate().update( - "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)", - vuid, mid, Reaction.LIKE, userUri) == 1; + var now = Instant.now().atOffset(ZoneOffset.UTC); + boolean wasAdded = getNamedParameterJdbcTemplate().update(""" + INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) + VALUES (:user_id, :message_id, :ts, :like_id, :user_uri) + """, new MapSqlParameterSource() + .addValue("user_id", vuid) + .addValue("message_id", mid) + .addValue("ts", now, Types.TIMESTAMP_WITH_TIMEZONE) + .addValue("like_id", Reaction.LIKE) + .addValue("user_uri", userUri)) == 1; if (wasAdded) { return RecommendStatus.Added; } @@ -342,9 +349,9 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ } } boolean wasAdded = getJdbcTemplate().update( - "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)", + "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, ?, ?, ?)", vuid, - mid, reaction, userUri) == 1; + mid, Instant.now().atOffset(ZoneOffset.UTC), reaction, userUri) == 1; if (wasAdded) { return RecommendStatus.Added; } @@ -659,7 +666,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "WHERE suser_id = :uid " + (to != 0 ? "AND updated < :to " : StringUtils.EMPTY) + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1" + " AND u.id = messages.user_id and u.id <> :uid) " - + "ORDER BY updated DESC, message_id DESC LIMIT 20", + + "ORDER BY updated DESC, messages.message_id DESC LIMIT 20", sqlParameterSource, (rs, rowNum) -> rs.getInt(1)); } @@ -896,27 +903,30 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List<Message> getReplies(final User user, final int mid) { List<Message> replies = getNamedParameterJdbcTemplate().query("WITH RECURSIVE banned(reply_id, user_id) AS (" - + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " - + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " - + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " - + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) " - + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " - + "replies.user_id, users.nick, users.banned, " + "replies.ts, " - + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, " - + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + "NOW(), " - + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, " - + "replies.updated_at, replies.user_uri as uri, " - + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread " - + "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id " - + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id " - + "LEFT JOIN messages_txt t on replies.message_id = t.message_id " - + "LEFT JOIN messages m on replies.message_id = m.message_id " - + "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id " - + "WHERE replies.message_id = :mid " - + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)" - + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) " - + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) " - + "ORDER BY replies.reply_id ASC", new MapSqlParameterSource("mid", mid).addValue("uid", user.getUid()), + + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " + + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) " + + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " + + "replies.user_id, users.nick, users.banned, " + "replies.ts, " + + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, " + + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, " + + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, " + + "replies.updated_at, replies.user_uri as uri, " + + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread " + + "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id " + + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id " + + "LEFT JOIN messages_txt t on replies.message_id = t.message_id " + + "LEFT JOIN messages m on replies.message_id = m.message_id " + + "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id " + + "WHERE replies.message_id = :mid " + + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)" + + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) " + + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) " + + "ORDER BY replies.reply_id ASC", new MapSqlParameterSource() + .addValue("mid", mid) + .addValue("uid", user.getUid()) + .addValue("now", Instant.now().atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE), new MessageMapper()); if (replies.size() > 0 && !user.isAnonymous()) { setRead(user, mid); @@ -1022,12 +1032,17 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public List<ResponseReply> getLastReplies(int hours) { - return getJdbcTemplate().query("SELECT users2.nick,replies.message_id,replies.reply_id," - + "users.nick,replies.txt," + "replies.ts,replies.attach,replies.ts+0, replies.html " - + "FROM ((replies INNER JOIN users ON replies.user_id=users.id) " - + "INNER JOIN messages ON replies.message_id=messages.message_id) " - + "INNER JOIN users AS users2 ON messages.user_id=users2.id " - + "WHERE replies.ts>TIMESTAMPADD(HOUR,?,NOW()) AND messages.privacy>0", (rs, rowNum) -> { + var datetime = Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC); + return getNamedParameterJdbcTemplate().query(""" + SELECT users2.nick,replies.message_id,replies.reply_id, + users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html + FROM ((replies INNER JOIN users ON replies.user_id=users.id) + INNER JOIN messages ON replies.message_id=messages.message_id) + INNER JOIN users AS users2 ON messages.user_id=users2.id + WHERE replies.ts>:datetime AND messages.privacy>0 + """, + new MapSqlParameterSource() + .addValue("datetime", datetime, Types.TIMESTAMP_WITH_TIMEZONE), (rs, rowNum) -> { ResponseReply reply = new ResponseReply(); reply.setMuname(rs.getString(1)); reply.setMid(rs.getInt(2)); @@ -1038,7 +1053,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ reply.setAttachmentType(rs.getString(7)); reply.setHtml(rs.getBoolean(8)); return reply; - }, -hours); + }); } @Transactional(readOnly = true) @@ -1109,7 +1124,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ if (ts.compareTo(messageEditingWindow) >= 0 || foreign) { return jdbcTemplate.update( "UPDATE messages_txt SET txt=?, updated_at=? WHERE messages_txt.message_id=?", body, - Timestamp.from(now), mid) > 0; + now.atOffset(ZoneOffset.UTC), mid) > 0; } } return false; @@ -1120,7 +1135,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ if (ts.compareTo(messageEditingWindow) >= 0 || foreign) { return jdbcTemplate.update( "UPDATE replies SET txt=?, updated_at=? WHERE message_id=? AND reply_id=?", body, - Timestamp.from(now), mid, rid) > 0; + now.atOffset(ZoneOffset.UTC), mid, rid) > 0; } } return false; diff --git a/src/main/java/com/juick/service/NullSearchService.java b/src/main/java/com/juick/service/NullSearchService.java index efaccd53..2fb51b07 100644 --- a/src/main/java/com/juick/service/NullSearchService.java +++ b/src/main/java/com/juick/service/NullSearchService.java @@ -18,6 +18,7 @@ package com.juick.service; import com.juick.model.User; +import org.springframework.boot.autoconfigure.condition.ConditionalOnExpression; import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; import org.springframework.stereotype.Repository; @@ -25,7 +26,7 @@ import java.util.Collections; import java.util.List; @Repository -@ConditionalOnProperty(name = "spring.sql.init.platform", havingValue = "h2") +@ConditionalOnExpression("('${spring.sql.init.platform}' == 'h2') or ('${spring.sql.init.platform}' == 'sqlite')") public class NullSearchService implements SearchService { @Override public void setMaxResult(int maxResult) { diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index b15a18e5..ab947bac 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -17,12 +17,7 @@ package com.juick.service; -import com.juick.model.AnonymousUser; -import com.juick.model.ApplicationStatus; -import com.juick.model.AuthResponse; -import com.juick.model.ExternalToken; -import com.juick.model.Message; -import com.juick.model.User; +import com.juick.model.*; import com.juick.util.UsernameTakenException; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.RandomStringUtils; @@ -32,7 +27,6 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; import org.springframework.dao.DataIntegrityViolationException; -import org.springframework.dao.DuplicateKeyException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; @@ -43,17 +37,10 @@ import org.springframework.transaction.annotation.Transactional; import javax.annotation.Nonnull; import java.net.URI; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Statement; -import java.sql.Timestamp; -import java.util.Collection; -import java.util.Collections; -import java.util.List; -import java.util.Objects; -import java.util.Optional; -import java.util.UUID; +import java.sql.*; +import java.time.Instant; +import java.time.ZoneOffset; +import java.util.*; /** * Created by aalexeev on 11/13/16. @@ -674,7 +661,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Override public void updateLastSeen(User user) { - getJdbcTemplate().update("UPDATE users SET last_seen=now() WHERE id=?", user.getUid()); + getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", Instant.now().atOffset(ZoneOffset.UTC), user.getUid()); } @Override diff --git a/src/main/resources/data-sqlite.sql b/src/main/resources/data-sqlite.sql new file mode 100644 index 00000000..bb1203a4 --- /dev/null +++ b/src/main/resources/data-sqlite.sql @@ -0,0 +1,11 @@ +INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password'); +INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password'); +INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password'); +INSERT INTO tags(tag_id, name) VALUES(2, 'juick'); +INSERT INTO reactions (like_id, description) VALUES (1, 'like'); +INSERT INTO reactions (like_id, description) VALUES (2, 'love'); +INSERT INTO reactions (like_id, description) VALUES (3, 'lol'); +INSERT INTO reactions (like_id, description) VALUES (4, 'hmm'); +INSERT INTO reactions (like_id, description) VALUES (5, 'angry'); +INSERT INTO reactions (like_id, description) VALUES (6, 'uhblya'); +INSERT INTO reactions (like_id, description) VALUES (7, 'ugh'); diff --git a/src/main/resources/db/specific/h2/V1.22__schema.sql b/src/main/resources/db/specific/h2/V1.22__schema.sql index dcb92374..de6946e8 100644 --- a/src/main/resources/db/specific/h2/V1.22__schema.sql +++ b/src/main/resources/db/specific/h2/V1.22__schema.sql @@ -169,7 +169,7 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES"( "MESSAGE_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL, "USER_ID" INTEGER NOT NULL, "LANG" ENUM('en', 'ru', 'fr', 'fa', '__') DEFAULT '__' NOT NULL, - "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL, + "TS" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL, "REPLIES" SMALLINT DEFAULT '0' NOT NULL, "MAXREPLYID" SMALLINT DEFAULT '0' NOT NULL, "PRIVACY" TINYINT DEFAULT '1' NOT NULL, @@ -181,7 +181,7 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES"( "POPULAR" TINYINT DEFAULT '0' NOT NULL, "HIDDEN" TINYINT DEFAULT '0' NOT NULL, "LIKES" SMALLINT DEFAULT '0' NOT NULL, - "UPDATED" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL + "UPDATED" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL ); ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_13" PRIMARY KEY("MESSAGE_ID"); -- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSAGES; diff --git a/src/main/resources/db/specific/sqlite/V1.22__schema.sql b/src/main/resources/db/specific/sqlite/V1.22__schema.sql new file mode 100644 index 00000000..8685cbb7 --- /dev/null +++ b/src/main/resources/db/specific/sqlite/V1.22__schema.sql @@ -0,0 +1,377 @@ +CREATE TABLE android ( + user_id INTEGER NOT NULL, + regid character varying(255) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id), + UNIQUE (regid) +); + +CREATE TABLE auth ( + user_id INTEGER, + protocol TEXT CHECK (protocol IN ('xmpp', 'email', 'sms')) NOT NULL, + account character varying(128) NOT NULL, + authcode character varying(8) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE bl_tags ( + user_id bigint NOT NULL, + tag_id bigint NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id), + FOREIGN KEY (tag_id) REFERENCES tags(tag_id) +); + +CREATE TABLE bl_users ( + user_id bigint NOT NULL, + bl_user_id bigint NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + PRIMARY KEY (user_id, bl_user_id), + FOREIGN KEY (user_id) REFERENCES users(id), + FOREIGN KEY (bl_user_id) REFERENCES users(id) +); + +CREATE TABLE emails ( + user_id bigint NOT NULL, + email character varying(128) NOT NULL, + subscr_hour smallint, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE facebook ( + user_id bigint, + fb_id numeric, + loginhash character varying(36), + access_token character varying(255), + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + fb_name character varying(64), + fb_link character varying(255) NOT NULL, + crosspost boolean DEFAULT true NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE favorites ( + user_id bigint NOT NULL, + message_id bigint NOT NULL, + ts timestamp with time zone, + like_id smallint DEFAULT 1 NOT NULL, + user_uri character varying(255) NOT NULL DEFAULT '', + FOREIGN KEY (like_id) REFERENCES reactions(like_id) +); + +CREATE TABLE followers ( + user_id bigint, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + acct character varying(64) NOT NULL, + PRIMARY KEY (user_id) + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE images ( + mid bigint NOT NULL, + rid bigint NOT NULL, + thumb bigint NOT NULL, + small bigint NOT NULL, + medium bigint NOT NULL, + height bigint NOT NULL, + width bigint NOT NULL, + PRIMARY KEY (mid, rid) +); + +CREATE TABLE ios ( + user_id bigint NOT NULL, + token character varying(64) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id), + UNIQUE (token) +); + +CREATE TABLE jids ( + user_id bigint, + jid character varying(64) NOT NULL, + active smallint DEFAULT 0 NOT NULL, + loginhash character varying(36), + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE logins ( + user_id bigint NOT NULL, + hash character varying(16) NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE mail ( + user_id INTEGER NOT NULL, + hash character varying(16) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE meon ( + id INTEGER NOT NULL, + user_id bigint NOT NULL, + link character varying(255) NOT NULL, + name character varying(32) NOT NULL, + ico smallint, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE messages ( + message_id INTEGER PRIMARY KEY NOT NULL, + user_id bigint NOT NULL, + lang TEXT DEFAULT '__' NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + replies smallint DEFAULT (0) NOT NULL, + maxreplyid smallint DEFAULT (0) NOT NULL, + privacy smallint DEFAULT (1) NOT NULL, + readonly boolean DEFAULT false NOT NULL, + attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')), + place_id bigint, + lat numeric(10,7), + lon numeric(10,7), + popular smallint DEFAULT (0) NOT NULL, + hidden smallint DEFAULT (0) NOT NULL, + likes smallint DEFAULT (0) NOT NULL, + updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE messages_access ( + message_id INTEGER NOT NULL, + user_id bigint NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id), + FOREIGN KEY (message_id) REFERENCES messages(message_id) +); + +CREATE TABLE messages_properties ( + message_id INTEGER PRIMARY KEY NOT NULL, + reply_id smallint NOT NULL, + property_key character varying(255) NOT NULL, + property_value text NOT NULL, + UNIQUE (message_id, reply_id, property_key), + FOREIGN KEY (message_id) REFERENCES messages(message_id) +); + +CREATE TABLE messages_tags ( + message_id INTEGER NOT NULL, + tag_id bigint NOT NULL, + FOREIGN KEY (message_id) REFERENCES messages(message_id) +); + +CREATE TABLE messages_txt ( + message_id INTEGER NOT NULL, + repliesby text, + txt text NOT NULL, + updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (message_id) REFERENCES messages(message_id) +); + +CREATE TABLE places ( + place_id INTEGER PRIMARY KEY NOT NULL, + lat numeric(10,7) NOT NULL, + lon numeric(10,7) NOT NULL, + name character varying(64) NOT NULL, + descr character varying(255), + url character varying(128), + user_id bigint NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL +); + +CREATE TABLE places_tags ( + place_id INTEGER NOT NULL, + tag_id bigint NOT NULL, + FOREIGN KEY (place_id) REFERENCES places(place_id) +); + +CREATE TABLE pm ( + user_id bigint NOT NULL, + user_id_to bigint NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + txt text NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id), + FOREIGN KEY (user_id_to) REFERENCES users(id) +); + +CREATE TABLE pm_inroster ( + user_id bigint NOT NULL, + jid character varying(64) NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE reactions ( + like_id INTEGER PRIMARY KEY NOT NULL, + description character varying(100) NOT NULL +); + +CREATE TABLE replies ( + message_id bigint NOT NULL, + reply_id smallint NOT NULL, + user_id bigint NOT NULL, + replyto smallint DEFAULT (0) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')), + txt text NOT NULL, + updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + user_uri character varying(255) DEFAULT NULL, + reply_uri character varying(255) DEFAULT NULL, + html smallint DEFAULT '0' NOT NULL, + FOREIGN KEY (message_id) REFERENCES messages(message_id), + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE subscr_messages ( + message_id bigint NOT NULL, + suser_id bigint NOT NULL, + last_read_rid smallint DEFAULT 0 NOT NULL, + FOREIGN KEY (message_id) REFERENCES messages(message_id), + FOREIGN KEY (suser_id) REFERENCES users(id) +); + +CREATE TABLE subscr_tags ( + tag_id bigint NOT NULL, + suser_id bigint NOT NULL, + FOREIGN KEY (tag_id) REFERENCES tags(tag_id), + FOREIGN KEY (suser_id) REFERENCES users(id) +); + +CREATE TABLE subscr_users ( + user_id bigint NOT NULL, + suser_id bigint NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id), + FOREIGN KEY (suser_id) REFERENCES users(id) +); + +CREATE TABLE tags ( + tag_id INTEGER PRIMARY KEY NOT NULL, + synonym_id bigint, + name character varying(70) COLLATE NOCASE, + top boolean DEFAULT false NOT NULL, + noindex boolean DEFAULT false NOT NULL, + stat_messages bigint DEFAULT (0) NOT NULL, + stat_users smallint DEFAULT (0) NOT NULL, + FOREIGN KEY (synonym_id) REFERENCES tags(tag_id) +); + +CREATE TABLE tags_ignore ( + tag_id bigint NOT NULL, + FOREIGN KEY (tag_id) REFERENCES tags(tag_id) +); + +CREATE TABLE tags_synonyms ( + name character varying(64) NOT NULL, + changeto character varying(64) NOT NULL +); + +CREATE TABLE telegram ( + user_id bigint, + tg_id numeric NOT NULL, + tg_name character varying(64) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + loginhash character varying(36), + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE top_ignore_messages ( + message_id bigint NOT NULL, + FOREIGN KEY (message_id) REFERENCES messages(message_id) +); + +CREATE TABLE top_ignore_tags ( + tag_id NOT NULL, + FOREIGN KEY (tag_id) REFERENCES tag(tag_id) +); + +CREATE TABLE top_ignore_users ( + user_id bigint NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE twitter ( + user_id INTEGER NOT NULL, + access_token character varying(64) NOT NULL, + access_token_secret character varying(64) NOT NULL, + uname character varying(64) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + crosspost boolean DEFAULT true NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE useroptions ( + user_id INTEGER NOT NULL, + jnotify smallint DEFAULT 1 NOT NULL, + subscr_active smallint DEFAULT 1 NOT NULL, + off_ts timestamp with time zone, + xmppxhtml smallint DEFAULT 0 NOT NULL, + subscr_notify smallint DEFAULT 1 NOT NULL, + recommendations smallint DEFAULT 1 NOT NULL, + privacy_view smallint DEFAULT 1 NOT NULL, + privacy_reply smallint DEFAULT 1 NOT NULL, + privacy_pm smallint DEFAULT 1 NOT NULL, + repliesview smallint DEFAULT 0 NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE users ( + id bigint NOT NULL, + nick character varying(64) NOT NULL COLLATE NOCASE, + passw character varying(32) NOT NULL, + lang users_lang DEFAULT '__' NOT NULL, + banned smallint DEFAULT (0) NOT NULL, + lastmessage timestamp with time zone, + lastpm bigint DEFAULT (0) NOT NULL, + lastphoto bigint DEFAULT (0) NOT NULL, + karma smallint DEFAULT (0) NOT NULL, + last_seen timestamp with time zone, + PRIMARY KEY (id) +); + +CREATE TABLE users_subscr ( + user_id bigint NOT NULL, + cnt smallint DEFAULT (0) NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE usersinfo ( + user_id bigint NOT NULL, + jid character varying(64), + fullname character varying(64), + country character varying(32), + url character varying(128), + gender character varying(32), + bday character varying(32), + descr text, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE version ( + version numeric NOT NULL +); + +CREATE TABLE vk ( + user_id bigint, + vk_id numeric NULL, + loginhash character varying(36), + access_token character varying(128) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + vk_name character varying(64) NOT NULL, + vk_link character varying(64) NOT NULL, + crosspost smallint DEFAULT (1) NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE winphone ( + user_id bigint NOT NULL, + url character varying(255) NOT NULL, + ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + FOREIGN KEY (user_id) REFERENCES users(id) +); + +CREATE TABLE wl_users ( + user_id bigint NOT NULL, + wl_user_id bigint NOT NULL, + PRIMARY KEY (user_id, wl_user_id), + FOREIGN KEY (user_id) REFERENCES users(id), + FOREIGN KEY (wl_user_id) REFERENCES users(id) +); |