Migrating from sqlite to mysql

by nico13, Thursday, April 18, 2013, 16:32 (4078 days ago)

Hello

I would like to migrate from sqlite to mysql.
I noticed if I just modify the config files it seems I have to reimport my rss feeds.

Since I started using selfoss I imported an opml file from google reader and since then I added by hand several feeds.
How can I save / export a new opml taking into account all feeds manually added?

(or is it a simpler way to migrate to mysql??)

thanks
Nicolas

so...

by nico13, Friday, April 19, 2013, 16:47 (4077 days ago) @ nico13

no one??

Migrating from sqlite to mysql

by Keksdose, Monday, September 02, 2013, 12:38 (3941 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");

Migrating from sqlite to mysql

by Keksdose, Monday, September 02, 2013, 14:56 (3941 days ago) @ Keksdose

Just found an issue:

During the migration the characters in some fields are not encoded correctly. I had to run a "UPDATE sources SET spout = 'spouts\\rss\\feed' WHERE spout = 'spouts\rss\feed'" on the database but I only have RSS sources.

Migrating from sqlite to mysql

by nico13, Tuesday, September 03, 2013, 07:52 (3940 days ago) @ Keksdose

Hi, thanks for this, I'm going to try this.
Does your UPDATE fix the encoding problem?

Cheers
Nicolas

RSS Feed of thread
powered by my little forum