#!/usr/bin/env php84 <?php /* Copyright (c) 2024 Freya Murphy */ function wait_until_ready() { while (TRUE) { if (file_exists("/var/run/crimson/db-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();