summary refs log tree commit diff
path: root/database.h
diff options
context:
space:
mode:
authorJune McEnroe <june@causal.agency>2020-01-14 17:57:04 -0500
committerJune McEnroe <june@causal.agency>2020-01-14 17:57:04 -0500
commit1140dfdd3d6bbcadb0ccb1693b4fbe4e6d0792c5 (patch)
tree804d2897a5be3c12b50d5e723f40aeadc5e8fdb5 /database.h
parentAdd option for client cert and SASL EXTERNAL (diff)
downloadlitterbox-1140dfdd3d6bbcadb0ccb1693b4fbe4e6d0792c5.tar.gz
litterbox-1140dfdd3d6bbcadb0ccb1693b4fbe4e6d0792c5.zip
Add columnsize = 0 option to FTS index
Since we don't use ranking functions and I don't see them being useful,
there is no point in having columnsize, which just takes extra space in
the database.

In my database of approximately 3.5 million events, disabling columnsize
saves about 62 MB.

The migration unfortunately has to rebuild the entire index to disable
it.
Diffstat (limited to 'database.h')
-rw-r--r--database.h25
1 files changed, 22 insertions, 3 deletions
diff --git a/database.h b/database.h
index d44bb15..83be73c 100644
--- a/database.h
+++ b/database.h
@@ -31,7 +31,7 @@
 
 #define DATABASE_PATH "litterbox/litterbox.sqlite"
 
-enum { DatabaseVersion = 0 };
+enum { DatabaseVersion = 1 };
 
 #define ENUM_TYPE \
 	X(Privmsg, "privmsg") \
@@ -285,7 +285,8 @@ static const char *InitSQL = SQL(
 		network, channel, query, nick, user, target, message,
 		content = text,
 		content_rowid = event,
-		tokenize = 'porter'
+		tokenize = 'porter',
+		columnsize = 0
 	);
 
 	CREATE TRIGGER eventsInsert AFTER INSERT ON events BEGIN
@@ -300,6 +301,8 @@ static const char *InitSQL = SQL(
 		) SELECT 'delete', * FROM text WHERE event = old.event;
 	END;
 
+	PRAGMA user_version = 1;
+
 	COMMIT TRANSACTION;
 );
 
@@ -308,7 +311,23 @@ static inline void dbInit(void) {
 }
 
 static const char *MigrationSQL[] = {
-	NULL,
+	// Added columnsize = 0 option.
+	SQL(
+		BEGIN TRANSACTION;
+		DROP TABLE search;
+		CREATE VIRTUAL TABLE search USING fts5 (
+			network, channel, query, nick, user, target, message,
+			content = text,
+			content_rowid = event,
+			tokenize = 'porter',
+			columnsize = 0
+		);
+		INSERT INTO search (
+			rowid, network, channel, query, nick, user, target, message
+		) SELECT * FROM text;
+		PRAGMA user_version = 1;
+		COMMIT TRANSACTION;
+	),
 };
 
 static inline void dbMigrate(void) {