298 lines
5.7 KiB
PHP
Executable file
298 lines
5.7 KiB
PHP
Executable file
#!/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();
|