diff options
Diffstat (limited to 'shim')
| -rw-r--r-- | shim/README.md | 9 | ||||
| -rwxr-xr-x | shim/shim.php | 298 |
2 files changed, 307 insertions, 0 deletions
diff --git a/shim/README.md b/shim/README.md new file mode 100644 index 0000000..80dfb3a --- /dev/null +++ b/shim/README.md @@ -0,0 +1,9 @@ +## shim + +TODO: update beacuse this is now invalid :( + +If you are runing a xssbook v1 setup, the database is fully incompatible with xssbook v2. Luckily there is a migration that exists to port over your data. XSSBook v1 has a single sqlite database file and a custom assets directory likly called `custom`. You will know you have the right directory if there are two sub directories called `avatar` and `banner`. Place the sqlite db file (called `xssbook.db`) and the `custom` directory in the `data/shim` folder of the xssbook v2 directory. If this doesnt exist please do a full setup of v2 first. Then run `docker compose up -d shim`, and you should be all set. + +> WARNING: This will delete ALL data in the database if you specify xssbook v1 files in the data path. Make sure yo only run this once and remove the files once completed. + +> NOTE: the migration will never run if the database files are not supplied. 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(); |