aboutsummaryrefslogtreecommitdiff
path: root/src/main/resources
diff options
context:
space:
mode:
Diffstat (limited to 'src/main/resources')
-rw-r--r--src/main/resources/schema-sqlite.sql174
1 files changed, 57 insertions, 117 deletions
diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index 8e496069..39895b2e 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -1,71 +1,51 @@
-PRAGMA journal_mode=WAL;;
+PRAGMA journal_mode=WAL;
CREATE TABLE user_services (
user_id INTEGER NOT NULL,
regid character varying(1024) NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
service_type varchar(255) not null default 'fcm',
FOREIGN KEY (user_id) REFERENCES users(id),
UNIQUE (regid)
-);;
-CREATE TRIGGER INSERT_user_services AFTER INSERT ON user_services
- BEGIN
- UPDATE user_services SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
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 DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_auth AFTER INSERT ON auth
- BEGIN
- UPDATE auth SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE bl_tags (
user_id INTEGER 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 INTEGER NOT NULL,
bl_user_id bigint NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) 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 TRIGGER INSERT_bl_users AFTER INSERT ON bl_users
- BEGIN
- UPDATE bl_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE emails (
user_id INTEGER NOT NULL,
email character varying(128) NOT NULL,
subscr_hour smallint,
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
CREATE TABLE facebook (
user_id INTEGER,
fb_id numeric,
loginhash character varying(36),
access_token character varying(255),
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) 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 TRIGGER INSERT_facebook AFTER INSERT ON facebook
- BEGIN
- UPDATE facebook SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE favorites (
user_id INTEGER NOT NULL,
message_id bigint NOT NULL,
@@ -73,19 +53,14 @@ CREATE TABLE favorites (
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 INTEGER,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
acct character varying(64) NOT NULL,
PRIMARY KEY (user_id)
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_followers AFTER INSERT ON followers
- BEGIN
- UPDATE followers SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE images (
mid bigint NOT NULL,
rid bigint NOT NULL,
@@ -95,25 +70,20 @@ CREATE TABLE images (
height bigint NOT NULL,
width bigint NOT NULL,
PRIMARY KEY (mid, rid)
-);;
+);
CREATE TABLE jids (
user_id INTEGER,
jid character varying(64) NOT NULL,
active smallint DEFAULT 0 NOT NULL,
loginhash character varying(36),
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_jids AFTER INSERT ON jids
- BEGIN
- UPDATE jids SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE logins (
user_id INTEGER NOT NULL,
hash character varying(16) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
CREATE TABLE meon (
id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
@@ -121,12 +91,12 @@ CREATE TABLE meon (
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 INTEGER NOT NULL,
lang TEXT DEFAULT '__' NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
replies smallint DEFAULT (0) NOT NULL,
maxreplyid smallint DEFAULT (0) NOT NULL,
privacy smallint DEFAULT (1) NOT NULL,
@@ -138,15 +108,15 @@ CREATE TABLE messages (
popular smallint DEFAULT (0) NOT NULL,
hidden smallint DEFAULT (0) NOT NULL,
likes smallint DEFAULT (0) NOT NULL,
- updated DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ updated DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
CREATE TABLE messages_access (
message_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
CREATE TABLE messages_properties (
message_id INTEGER NOT NULL,
reply_id smallint NOT NULL,
@@ -154,19 +124,19 @@ CREATE TABLE messages_properties (
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 DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ updated_at DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) 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,
@@ -175,36 +145,31 @@ CREATE TABLE places (
descr character varying(255),
url character varying(128),
user_id bigint NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL
-);;
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) 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 INTEGER NOT NULL,
user_id_to INTEGER NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
txt text NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (user_id_to) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_pm AFTER INSERT ON pm
- BEGIN
- UPDATE pm SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
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 INTEGER NOT NULL,
replyto smallint DEFAULT (0) NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')),
txt text NOT NULL,
updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL,
@@ -213,32 +178,27 @@ CREATE TABLE replies (
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 INTEGER 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 INTEGER NOT NULL,
FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
+);
CREATE TABLE subscr_users (
user_id INTEGER NOT NULL,
suser_id INTEGER NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_subscr_users AFTER INSERT ON subscr_users
- BEGIN
- UPDATE subscr_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE tags (
tag_id INTEGER PRIMARY KEY NOT NULL,
synonym_id bigint,
@@ -248,54 +208,44 @@ CREATE TABLE tags (
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 INTEGER,
tg_id numeric NOT NULL,
tg_name character varying(64) NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
loginhash character varying(36),
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_telegram AFTER INSERT ON telegram
- BEGIN
- UPDATE telegram SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
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 INTEGER 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 DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
crosspost boolean DEFAULT true NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_twitter AFTER INSERT ON twitter
- BEGIN
- UPDATE twitter SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE users (
id INTEGER PRIMARY KEY,
nick character varying(64) NOT NULL COLLATE NOCASE,
@@ -308,12 +258,12 @@ CREATE TABLE users (
karma smallint DEFAULT (0) NOT NULL,
last_seen timestamp with time zone,
UNIQUE(nick)
-);;
+);
CREATE TABLE users_subscr (
user_id INTEGER NOT NULL,
cnt smallint DEFAULT (0) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
CREATE TABLE usersinfo (
user_id INTEGER NOT NULL,
jid character varying(64),
@@ -325,34 +275,29 @@ CREATE TABLE usersinfo (
descr text,
UNIQUE(user_id),
FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
CREATE TABLE vk (
user_id INTEGER,
vk_id numeric NULL,
loginhash character varying(36),
access_token character varying(128) NOT NULL,
- ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) 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 TRIGGER INSERT_vk AFTER INSERT ON vk
- BEGIN
- UPDATE vk SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END;;
+);
CREATE TABLE wl_users (
user_id INTEGER 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)
-);;
+);
CREATE TABLE oauth2_registered_client (
id varchar(100) NOT NULL,
client_id varchar(100) NOT NULL,
- client_id_issued_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ client_id_issued_at timestamp DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
client_secret varchar(200) DEFAULT NULL,
client_secret_expires_at timestamp DEFAULT NULL,
client_name varchar(200) NOT NULL,
@@ -363,9 +308,4 @@ CREATE TABLE oauth2_registered_client (
client_settings varchar(2000) NOT NULL,
token_settings varchar(2000) NOT NULL,
PRIMARY KEY (id)
-);;
-CREATE TRIGGER INSERT_oauth2_registered_client AFTER INSERT ON oauth2_registered_client
- BEGIN
- UPDATE oauth2_registered_client SET client_id_issued_at = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
- WHERE rowid = new.rowid;
- END
+);