aboutsummaryrefslogtreecommitdiff
path: root/src/main/resources/schema-sqlite.sql
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-01-29 05:44:21 +0300
committerGravatar Vitaly Takmazov2023-01-30 23:49:25 +0300
commitf8a7d417cb916b81cfa685175f3e6afbe6063cee (patch)
tree3f6923f3f32540e8506ce5b43b610460b4c67559 /src/main/resources/schema-sqlite.sql
parentdd23559a978da8980675ad4089948ade9bbc323d (diff)
SQLite support
Diffstat (limited to 'src/main/resources/schema-sqlite.sql')
-rw-r--r--src/main/resources/schema-sqlite.sql231
1 files changed, 147 insertions, 84 deletions
diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index b624d67b..8e496069 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -1,64 +1,91 @@
-CREATE TABLE IF NOT EXISTS "user_services" (
+PRAGMA journal_mode=WAL;;
+CREATE TABLE user_services (
user_id INTEGER NOT NULL,
regid character varying(1024) NOT NULL,
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, service_type varchar(255) not null default 'fcm',
+ ts DEFAULT CURRENT_TIMESTAMP 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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+ 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 bigint NOT NULL,
+ user_id INTEGER NOT NULL,
bl_user_id bigint NOT NULL,
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts 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 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 bigint NOT NULL,
+ 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 bigint,
+ user_id INTEGER,
fb_id numeric,
loginhash character varying(36),
access_token character varying(255),
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts 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 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 bigint NOT NULL,
+ user_id INTEGER 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,
+ user_id INTEGER,
+ ts DEFAULT CURRENT_TIMESTAMP 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,
@@ -68,33 +95,38 @@ CREATE TABLE images (
height bigint NOT NULL,
width bigint NOT NULL,
PRIMARY KEY (mid, rid)
-);
+);;
CREATE TABLE jids (
- user_id bigint,
+ user_id INTEGER,
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,
+ ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+ 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 bigint NOT NULL,
+ user_id INTEGER 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,
+ user_id INTEGER NOT NULL,
lang TEXT DEFAULT '__' NOT NULL,
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
replies smallint DEFAULT (0) NOT NULL,
maxreplyid smallint DEFAULT (0) NOT NULL,
privacy smallint DEFAULT (1) NOT NULL,
@@ -106,35 +138,35 @@ CREATE TABLE messages (
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,
+ updated 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,
+ 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 PRIMARY KEY NOT NULL,
+ message_id INTEGER 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,
+ updated_at 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,
@@ -143,60 +175,70 @@ CREATE TABLE places (
descr character varying(255),
url character varying(128),
user_id bigint NOT NULL,
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
-);
+ ts 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,
+ user_id INTEGER NOT NULL,
+ user_id_to INTEGER NOT NULL,
+ ts 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 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 bigint NOT NULL,
+ user_id INTEGER NOT NULL,
replyto smallint DEFAULT (0) NOT NULL,
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts 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,
+ updated_at 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,
+ 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 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 bigint NOT NULL,
- suser_id bigint NOT NULL,
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ user_id INTEGER NOT NULL,
+ suser_id INTEGER NOT NULL,
+ ts DEFAULT CURRENT_TIMESTAMP 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,
@@ -206,46 +248,56 @@ 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 bigint,
+ user_id INTEGER,
tg_id numeric NOT NULL,
tg_name character varying(64) NOT NULL,
- ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+ 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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+ ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+ id INTEGER PRIMARY KEY,
nick character varying(64) NOT NULL COLLATE NOCASE,
passw character varying(32) NOT NULL,
lang users_lang DEFAULT '__' NOT NULL,
@@ -255,15 +307,15 @@ CREATE TABLE users (
lastphoto bigint DEFAULT (0) NOT NULL,
karma smallint DEFAULT (0) NOT NULL,
last_seen timestamp with time zone,
- PRIMARY KEY (id)
-);
+ UNIQUE(nick)
+);;
CREATE TABLE users_subscr (
- user_id bigint NOT NULL,
+ user_id INTEGER NOT NULL,
cnt smallint DEFAULT (0) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
-);
+);;
CREATE TABLE usersinfo (
- user_id bigint NOT NULL,
+ user_id INTEGER NOT NULL,
jid character varying(64),
fullname character varying(64),
country character varying(32),
@@ -271,26 +323,32 @@ CREATE TABLE usersinfo (
gender character varying(32),
bday character varying(32),
descr text,
+ UNIQUE(user_id),
FOREIGN KEY (user_id) REFERENCES users(id)
-);
+);;
CREATE TABLE vk (
- user_id bigint,
+ user_id INTEGER,
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,
+ ts 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 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 bigint NOT NULL,
+ 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,
@@ -305,4 +363,9 @@ 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