1 files changed, 56 insertions, 0 deletions
diff --git a/lib/database.php b/lib/database.php
new file mode 100644
index 0000000..0ffdb3a
--- /dev/null
+++ b/lib/database.php
@@ -0,0 +1,56 @@
+<?php
+
+//connect or create the database
+try {
+	$db = new PDO('sqlite:'.ROOT.DS.'posts.db');
+	$config['db_version'] = $db->query("PRAGMA user_version")->fetch(PDO::FETCH_ASSOC)['user_version'];
+} catch(PDOException $e) {
+	print 'Exception : '.$e->getMessage();
+	die('cannot connect to or open the database');
+}
+
+// first time setup
+if($config['db_version'] == 0) {
+	try {
+		$db->exec("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();
+		die('cannot set up initial database table!');
+	}
+}
+
+// upgrade database to v2
+if($config['db_version'] == 1) {
+	try {
+		$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;
+		");
+		$config['db_version'] = 2;
+	} catch(PDOException $e) {
+		print 'Exception : '.$e->getMessage();
+		die('cannot upgrade database table to v2!');
+	}
+}
+
+// upgrade database to v3
+if($config['db_version'] == 2) {
+	try {
+		$db->exec("PRAGMA user_version = 3;
+			ALTER TABLE `posts` ADD `post_guid` TEXT;
+		");
+		$config['db_version'] = 3;
+	} catch(PDOException $e) {
+		print 'Exception : '.$e->getMessage();
+		die('cannot upgrade database table to v3!');
+	}
+}
+
+// debug: get a list of post table columns
+// var_dump($db->query("PRAGMA table_info(`posts`)")->fetchAll(PDO::FETCH_COLUMN, 1));