Initial SQLite schema (work in progress)

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)
+);