From 5ef424120ed694b1f69daeafaa1454455d13dee2 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Wed, 31 Jan 2024 16:50:01 +0300
Subject: CI: enable SQLite

* SQLite JDBC driver does not support generated keys feature. So we implement `returningId` helper
* only one connection should be used from connection pooler
* changes made to prevent connection locking
---
 src/main/resources/application.properties | 2 +-
 src/main/resources/schema-h2.sql          | 6 ++----
 src/main/resources/schema-sqlite.sql      | 8 +++-----
 3 files changed, 6 insertions(+), 10 deletions(-)

(limited to 'src/main/resources')

diff --git a/src/main/resources/application.properties b/src/main/resources/application.properties
index 0ce5b76a..fe206251 100644
--- a/src/main/resources/application.properties
+++ b/src/main/resources/application.properties
@@ -10,7 +10,7 @@ spring.datasource.generate-unique-name=false
 spring.flyway.enabled=false
 spring.sql.init.platform=h2
 spring.cache.type=simple
-spring.cache.cache-names=help, usernames, users_by_name, discover, discussions, messages, replies, popular_tags, profiles, twitter_user, stats, message_tags
+spring.cache.cache-names=help, usernames, users_by_name, discover, discussions, messages, replies, popular_tags, profiles, twitter_user, stats
 spring.profiles.active=@activatedProperties@
 springdoc.swagger-ui.path=/api/swagger-ui.html
 server.forward-headers-strategy=framework
diff --git a/src/main/resources/schema-h2.sql b/src/main/resources/schema-h2.sql
index 7aaee493..5fd7fff8 100644
--- a/src/main/resources/schema-h2.sql
+++ b/src/main/resources/schema-h2.sql
@@ -114,7 +114,7 @@ CREATE MEMORY TABLE "PUBLIC"."REPLIES"(
     "USER_ID" INTEGER NOT NULL,
     "REPLYTO" SMALLINT DEFAULT '0' NOT NULL,
     "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
-    "ATTACH" CHARACTER(3),
+    "ATTACH" ENUM('jpg', 'mp4', 'png') DEFAULT NULL,
     "TXT" CHARACTER VARYING NOT NULL,
     "UPDATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
     "USER_URI" CHARACTER VARYING(255) DEFAULT NULL,
@@ -131,7 +131,7 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES"(
     "MAXREPLYID" SMALLINT DEFAULT '0' NOT NULL,
     "PRIVACY" TINYINT DEFAULT '1' NOT NULL,
     "READONLY" TINYINT DEFAULT '0' NOT NULL,
-    "ATTACH" CHARACTER(3),
+    "ATTACH" ENUM('jpg', 'mp4', 'png') DEFAULT NULL,
     "PLACE_ID" INTEGER DEFAULT NULL,
     "LAT" DECIMAL(10, 7) DEFAULT NULL,
     "LON" DECIMAL(10, 7) DEFAULT NULL,
@@ -251,8 +251,6 @@ CREATE MEMORY TABLE "PUBLIC"."USER_SERVICES"(
     "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
     "SERVICE_TYPE" CHARACTER VARYING(255) DEFAULT 'fcm' NOT NULL
 );
-ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_131" CHECK("ATTACH" IN('jpg', 'mp4', 'png')) NOCHECK;
-ALTER TABLE "PUBLIC"."REPLIES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_6C" CHECK("ATTACH" IN('jpg', 'mp4', 'png')) NOCHECK;
 ALTER TABLE "PUBLIC"."LOGINS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_86" UNIQUE("USER_ID");
 ALTER TABLE "PUBLIC"."REACTIONS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_6" UNIQUE("LIKE_ID");
 ALTER TABLE "PUBLIC"."SUBSCR_TAGS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_B" UNIQUE("TAG_ID", "SUSER_ID");
diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index b0858f1b..74ba28b6 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -1,5 +1,3 @@
-PRAGMA journal_mode=WAL;
-PRAGMA busy_timeout=30000;
 CREATE TABLE user_services (
     user_id INTEGER NOT NULL,
     regid character varying(1024) NOT NULL,
@@ -44,7 +42,7 @@ CREATE TABLE facebook (
     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,
+    crosspost boolean DEFAULT TRUE NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE TABLE favorites (
@@ -91,7 +89,7 @@ CREATE TABLE messages (
     replies smallint DEFAULT (0) NOT NULL,
     maxreplyid smallint DEFAULT (0) NOT NULL,
     privacy smallint DEFAULT (1) NOT NULL,
-    readonly boolean DEFAULT false NOT NULL,
+    readonly boolean DEFAULT FALSE NOT NULL,
     attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')),
     place_id bigint,
     lat numeric(10,7),
@@ -215,7 +213,7 @@ CREATE TABLE twitter (
     access_token_secret character varying(64) NOT NULL,
     uname character varying(64) NOT NULL,
     ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
-    crosspost boolean DEFAULT true NOT NULL,
+    crosspost boolean DEFAULT TRUE NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE TABLE users (
-- 
cgit v1.2.3