Migrating from sqlite to mysql

by Keksdose, Monday, September 02, 2013, 12:38 (4027 days ago) @ nico13

Hi,

I had the same problem and made a quick and dirty solution for this. Just paste it in a file (let's say migrate.php), change the settings for sqlite and mysql and call it from the console. (php -f migrate.php)

<?php
$sqlite_db = 'data/sqlite/selfoss.db'; // enter path to selfoss db
$mysql_hostname = '<CHANGEME>'; // enter hostname of mysql server
$mysql_database = '<CHANGEME>'; // enter database name
$mysql_username = '<CHANGEME>'; // enter username
$mysql_password = '<CHANGEME>'; // enter password

$dbs = new SQLite3($sqlite_db);
$dbm = new mysqli($mysql_hostname, $mysql_username, $mysql_password, $mysql_database);

$count_items = 0;
$count_sources = 0;
$count_tags = 0;

$dbs_result = $dbs->query('SELECT * FROM items');

while($dbs_row = $dbs_result->fetchArray()) {
$dbm->query(sprintf("INSERT INTO items SET id = '%s', datetime = '%s', title = '%s', content = '%s', thumbnail = '%s', icon = '%s', unread = '%s', starred = '%s', source = '%s', uid = '%s', link = '%s'", $dbs_row['id'], $dbs_row['datetime'], $dbs_row['title'], $dbs_row['content'], $dbs_row['thumbnail'], $dbs_row['icon'], $dbs_row['unread'], $dbs_row['starred'], $dbs_row['source'], $dbs_row['uid'], $dbs_row['link']));
echo("Items: ".++$count_items."\r");
}
echo("\n");

$dbs_result = $dbs->query('SELECT * FROM sources');

while($dbs_row = $dbs_result->fetchArray()) {
$dbm->query(sprintf("INSERT INTO sources SET id = '%s', title = '%s', tags = '%s', spout = '%s', params = '%s', error = '%s', lastupdate = '%s'", $dbs_row['id'], $dbs_row['title'], $dbs_row['tags'], $dbs_row['spout'], $dbs_row['params'], $dbs_row['error'], $dbs_row['lastupdate']));
echo("Sources: ".++$count_sources."\r");
}
echo("\n");

$dbs_result = $dbs->query('SELECT * FROM tags');

while($dbs_row = $dbs_result->fetchArray()) {
$dbm->query(sprintf("INSERT INTO tags SET tag = '%s', color = '%s'", $dbs_row['tag'], $dbs_row['color']));
echo("Tags: ".++$count_tags."\r");
}
echo("\n");