v2.1.0, refactor w/ crimson

1 files changed, 298 insertions, 0 deletions

diff --git a/shim/shim.php b/shim/shim.php
new file mode 100755
index 0000000..eb84412
--- /dev/null
+++ b/shim/shim.php
@@ -0,0 +1,298 @@
+#!/usr/bin/env php
+<?php /* Copyright (c) 2024 Freya Murphy */
+
+function wait_until_ready() {
+	while (TRUE) {
+		if (file_exists("/status/ready")) {
+			echo "database ready!\n";
+			break;
+		}
+		echo "waiting for database...\n";
+		sleep(3);
+	}
+}
+
+if (!file_exists("/data/xssbook.db")) {
+	echo "/data/xssbook.db not found: exiting shim\n";
+	die();
+}
+
+function connect_psql() {
+	$user = getenv("POSTGRES_USER");
+	$pass = getenv("POSTGRES_PASSWORD");
+	$db	  = getenv("POSTGRES_DB");
+	$host = 'db';
+	$port = '5432';
+
+	$conn_str = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",
+        $host,
+        $port,
+        $db,
+        $user,
+		$pass
+	);
+	$conn = new \PDO($conn_str);
+	$conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
+
+	return $conn;
+}
+
+function connect_sqlite() {
+	$conn_str = sprintf("sqlite:/data/xssbook.db");
+	$conn = new \PDO($conn_str);
+	$conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
+
+	return $conn;
+}
+
+$psql = connect_psql();
+$sqlite = connect_sqlite();
+
+function error() {
+	$psql->rollBack();
+	die();
+}
+
+function get_date(
+	$day, $month, $year
+) {
+	if (checkdate($month, $day, $year)) {
+		$date = "{$year}-{$month}-{$day}";
+		return $date;
+	} else {
+		return "1970-01-01";
+	}
+}
+
+function clear_all() {
+	echo "clearing database\n";
+	extract($GLOBALS);
+	$psql->beginTransaction();
+	$psql->exec(
+		'DELETE FROM xssbook.user;
+		DELETE FROM xssbook.post;
+		DELETE FROM xssbook.comment;
+		DELETE FROM xssbook.like;
+		DELETE FROM xssbook.follow;
+		DELETE FROM xssbook.user_media;'
+	);
+}
+
+function migrate_users() {
+	echo "migrating users\n";
+	extract($GLOBALS);
+
+	// load queries
+	$query = $sqlite->prepare(
+		'SELECT user_id, firstname, lastname, email, password, gender, date, day, month, year FROM users;'
+	);
+	$submit = $psql->prepare(
+		'INSERT INTO xssbook.user
+		(id, username, password, first_name, last_name, email, gender, birth_date, created)
+		VALUES
+		(?, ?, ?, ?, ?, ?, ?, ?, to_timestamp(?));'
+	);
+
+	// load users
+	$query->execute();
+	$rows = $query->fetchAll();
+	foreach ($rows as $user) {
+		// submit each user
+		$date = get_date(
+			$user['day'], $user['month'], $user['year']
+		);
+		$joined = $user['date'] / 1000;
+		$submit->execute(array(
+			$user['user_id'],
+			$user['email'],
+			$user['password'],
+			$user['firstname'],
+			$user['lastname'],
+			$user['email'],
+			$user['gender'],
+			$date,
+			$joined
+		));
+	}
+}
+
+function migrate_posts() {
+	echo "migrating posts\n";
+	extract($GLOBALS);
+
+	// load queries
+	$query = $sqlite->prepare(
+		'SELECT post_id, user_id, content, date FROM posts;'
+	);
+	$submit = $psql->prepare(
+		'INSERT INTO xssbook.post
+		(id, user_id, content, created)
+		VALUES
+		(?, ?, ?, to_timestamp(?));'
+	);
+
+	// load posts
+	$query->execute();
+	$rows = $query->fetchall();
+	foreach ($rows as $post) {
+		$created = $post['date'] / 1000;
+		$submit->execute(array(
+			$post['post_id'],
+			$post['user_id'],
+			$post['content'],
+			$created
+		));
+	}
+}
+
+function migrate_comments() {
+	echo "migrating comments\n";
+	extract($GLOBALS);
+
+	// load queries
+	$query = $sqlite->prepare(
+		'SELECT comment_id, user_id, post_id, content, date FROM comments;'
+	);
+	$submit = $psql->prepare(
+		'INSERT INTO xssbook.comment
+		(id, user_id, post_id, content, created)
+		VALUES
+		(?, ?, ?, ?, to_timestamp(?));'
+	);
+
+	// load comments
+	$query->execute();
+	$rows = $query->fetchall();
+	foreach ($rows as $comment) {
+		$created = $comment['date'] / 1000;
+		$submit->execute(array(
+			$comment['comment_id'],
+			$comment['user_id'],
+			$comment['post_id'],
+			$comment['content'],
+			$created
+		));
+	}
+}
+
+function migrate_likes() {
+	echo "migrating likes\n";
+	extract($GLOBALS);
+
+	// load queries
+	$query = $sqlite->prepare(
+		'SELECT user_id, post_id FROM likes;'
+	);
+	$submit = $psql->prepare(
+		'INSERT INTO xssbook.like
+		(user_id, post_id)
+		VALUES
+		(?, ?);'
+	);
+
+	// load likes
+	$query->execute();
+	$rows = $query->fetchall();
+	foreach ($rows as $like) {
+		$submit->execute(array(
+			$like['user_id'],
+			$like['post_id']
+		));
+	}
+}
+
+function migrate_follow() {
+	echo "migrating follow\n";
+	extract($GLOBALS);
+
+	// load queries
+	$query = $sqlite->prepare(
+		'SELECT follower_id, followee_id FROM friends;'
+	);
+	$submit = $psql->prepare(
+		'INSERT INTO xssbook.follow
+		(follower_id, followee_id)
+		VALUES
+		(?, ?);'
+	);
+
+	// load follows
+	$query->execute();
+	$rows = $query->fetchall();
+	foreach ($rows as $follow) {
+		$submit->execute(array(
+			$follow['follower_id'],
+			$follow['followee_id']
+		));
+	}
+}
+
+function migrate_user_media($type) {
+	echo "migrating user media ($type)\n";
+	extract($GLOBALS);
+
+	// load queries
+	$submit = $psql->prepare(
+		'INSERT INTO xssbook.user_media
+		(user_id, content, mime, type)
+		VALUES
+		(?, decode(?, \'base64\'), ?, ?);'
+	);
+
+	// get dir
+	$dir = "/data/custom/{$type}";
+	if (!is_dir($dir)) {
+		return;
+	}
+
+	// load user media
+	if ($handle = opendir($dir)) {
+		while (FALSE !== ($file = readdir($handle))) {
+			if ('.' === $file) continue;
+			if ('..' === $file) continue;
+			$n = strpos($file, ".png");
+			$uid = substr($file, 0, $n);
+			$path = "{$dir}/{$file}";
+			$data = base64_encode(file_get_contents($path));
+
+			$submit->execute(array(
+				$uid,
+				$data,
+				'image/png',
+				$type
+			));
+		}
+		closedir($handle);
+	}
+}
+
+function migrate_seq() {
+	echo "migrating sequences\n";
+	extract($GLOBALS);
+
+	$tables = array('user', 'post', 'like', 'comment', 'follow', 'user_media');
+
+	foreach ($tables as $table) {
+		$sql = "SELECT setval('sys.{$table}_id_seq', (SELECT MAX(id) FROM xssbook.{$table}), true);";
+		$psql->exec($sql);
+	}
+}
+
+try {
+	wait_until_ready();
+	clear_all();
+	migrate_users();
+	migrate_posts();
+	migrate_comments();
+	migrate_likes();
+	migrate_follow();
+	migrate_user_media('avatar');
+	migrate_user_media('banner');
+	migrate_seq();
+} catch (Exception $ex) {
+	echo "$ex\n";
+	$psql->rollBack();
+	die();
+}
+
+$psql->commit();