From 482fd7adee5e9e0990bf5904ed7d754d315de649 Mon Sep 17 00:00:00 2001
From: Arno Richter <oelna@oelna.de>
Date: Wed, 21 Dec 2022 15:05:28 +0100
Subject: first attempt at image attachments!

---
 lib/database.php | 48 +++++++++++++++++++++++++++++++++++++++++++-----
 1 file changed, 43 insertions(+), 5 deletions(-)

(limited to 'lib/database.php')

diff --git a/lib/database.php b/lib/database.php
index 5774d95..4e5e0cd 100644
--- a/lib/database.php
+++ b/lib/database.php
@@ -15,11 +15,12 @@ try {
 // first time setup
 if($config['db_version'] == 0) {
 	try {
-		$db->exec("CREATE TABLE IF NOT EXISTS `posts` (
-			`id` integer PRIMARY KEY NOT NULL,
+		$db->exec("PRAGMA `user_version` = 1;
+			CREATE TABLE IF NOT EXISTS `posts` (
+			`id` INTEGER PRIMARY KEY NOT NULL,
 			`post_content` TEXT,
 			`post_timestamp` INTEGER
-		); PRAGMA `user_version` = 1;");
+		);");
 		$config['db_version'] = 1;
 	} catch(PDOException $e) {
 		print 'Exception : '.$e->getMessage();
@@ -30,7 +31,7 @@ if($config['db_version'] == 0) {
 // upgrade database to v2
 if($config['db_version'] == 1) {
 	try {
-		$db->exec("PRAGMA user_version = 2;
+		$db->exec("PRAGMA `user_version` = 2;
 			ALTER TABLE `posts` ADD `post_thread` INTEGER;
 			ALTER TABLE `posts` ADD `post_edited` INTEGER;
 			ALTER TABLE `posts` ADD `post_deleted` INTEGER;
@@ -45,7 +46,7 @@ if($config['db_version'] == 1) {
 // upgrade database to v3
 if($config['db_version'] == 2) {
 	try {
-		$db->exec("PRAGMA user_version = 3;
+		$db->exec("PRAGMA `user_version` = 3;
 			ALTER TABLE `posts` ADD `post_guid` TEXT;
 		");
 		$config['db_version'] = 3;
@@ -55,5 +56,42 @@ if($config['db_version'] == 2) {
 	}
 }
 
+// upgrade database to v4
+if($config['db_version'] == 3) {
+	try {
+		$db->exec("PRAGMA `user_version` = 4;
+				CREATE TABLE `files` (
+				`id` INTEGER PRIMARY KEY NOT NULL,
+				`file_filename` TEXT NOT NULL,
+				`file_extension` TEXT,
+				`file_original` TEXT NOT NULL,
+				`file_mime_type` TEXT,
+				`file_size` INTEGER,
+				`file_hash` TEXT UNIQUE,
+				`file_hash_algo` TEXT,
+				`file_meta` TEXT DEFAULT '{}',
+				`file_dir` TEXT,
+				`file_subdir` TEXT,
+				`file_timestamp` INTEGER,
+				`file_deleted` INTEGER
+			);
+			CREATE TABLE `file_to_post` (
+				`file_id` INTEGER NOT NULL,
+				`post_id` INTEGER NOT NULL,
+				`deleted` INTEGER,
+				UNIQUE(`file_id`, `post_id`) ON CONFLICT IGNORE
+			);
+			CREATE INDEX `posts_timestamp` ON posts (`post_timestamp`);
+			CREATE INDEX `files_original` ON files (`file_original`);
+			CREATE INDEX `link_deleted` ON file_to_post (`deleted`);
+			CREATE UNIQUE INDEX `files_hashes` ON files (`file_hash`);
+		");
+		$config['db_version'] = 4;
+	} catch(PDOException $e) {
+		print 'Exception : '.$e->getMessage();
+		die('cannot upgrade database table to v4!');
+	}
+}
+
 // debug: get a list of post table columns
 // var_dump($db->query("PRAGMA table_info(`posts`)")->fetchAll(PDO::FETCH_COLUMN, 1));
-- 
cgit v1.2.3