I was originally planning to write about using the C APIs of PostgreSQL, MySQL and SQLite, but as I don't have time for that today,I'm going to talk about something I've been doing the past couple of days: Migrating from SQLite to PostgreSQL for both my forum and my MUD.

First you might be wondering why I'm using the less supported PostgreSQL instead of MySQL. Frankly I will not enter that hornet's nest of a discussion here. Flame wars rage on the internet on that subject, and it's just as silly as "my programming language is better than yours". You use the tools that fits your purposes. Simple as that. In the following I'll talk about migrating both the forum and the mud in a single discussion, because they're both SQLite -> PostgreSQL anyway.

The first thing I did was to dump the entire databases into a text file of SQL statements. All SQL engines I've seen have tools for doing this. Technically you can simply read that same file into another database. However, it's rarely that simple. PostgreSQL has different syntax that SQLite for such things as making an autoincrement field:

SQLite:
CREATE TABLE test_table (
        a INTEGER PRIMARY KEY,
        b INTEGER
);

PostgreSQL:
CREATE TABLE test_table (
        a SERIAL,
        b INT
);

So obviously I had to change a few schemas. Using these autoincrement fields is also different:

SQLite:
INSERT INTO test_table VALUES ( NULL, 123 );

PostgreSQL:
INSERT INTO test_table VALUES ( DEFAULT, 123 );

So this forced me to change a few SQL statements scattered around the code as well. But it doesn't stop there. Case insensitive matching in queries also differ:

SQLite:
DELETE FROM players WHERE name LIKE 'caius';

PostgreSQL:
DELETE FROM players WHERE name ~* 'caius';

This is because the LIKE operator has slightly different meaning in the two engines. Again I had to change quite a few SQL statements in my code. At this point importing the data into the PostgreSQL database, and reading from it worked properly. However, I soon encountered another problem. Postgres refused to insert new rows into tables with autoincrement fields! Arg! The reason is that the engine keeps a separate sequence table to keep track of the index. So I had to update this counter manually. If we assume the top id in the above table is 14, we must send the following query to the database:

PostgreSQL:
ALTER SEQUENCE test_table_id_seq RESTART WITH 15;

So it's in the form "table name" + "field name" + "seq". Now, FINALLY, everything ran smoothly.

Now, you may wonder why the database engines doesn't do these things similarly? The reason is that they implement extra features that aren't a part of the SQL standard, and they each do it their own way. This is why migrating SQL data can be a pain, especially if you use non-standard features.

That's it for today. Cheers.