PostgreSQL to SQLite

This week I had a demo at DemoCamp, and had to get a snapshot of DrProject’s database. I had to borrow somebody’s laptop for the demo, and didn’t want to install PostgreSQL on it. So, I was challenged with a task of converting PostgreSQL database dump into SQLite database. After some time of surfing the web, I came to a conclusion that nobody has ever documented/blogged this (and I guess that makes sense – if anything, people should be moving from SQLite to PostgreSQL).

To get a dump from PostgreSQL is simple:

pg_dump -a -d db_name > dump.sql

This will create a text file with SQL statements for table definitions, and INSERTs of the data in the tables. This is what I had to begin with. The next step is to sanitize data. SQLite has no predefined data type for booleans, it uses integers with 0 or 1 for false and true, respectively. Therefore, we should replace ‘false’ with 0 and ‘true’ with 1 using ‘awk’ command on Linux. The next step is to connect to the SQLite database:

sqlite3 db_name.db

You will get a prompt like this:
sqlite>

Now, use the .read command to load the dump:
sqlite> .read dump.sql

That’s it!

Wonders of RTFM’ing

So, I just spent about 3 hours at work and at home debugging a small problem. I added functionality for a service bot to identify with the DrProject bot. This is done by the service bot sending IDENTIFY command to the DrProject bot, and waiting for a reply  “The operation succeeded”. Very simple concept, right?

INFO 2008-07-17T21:06:26 Received message from DrProjectBot: The operation succeeded.
error: “error:” is not a valid command.
error: “error:” is not a valid command.
error: “error:” is not a valid command.
error: “error:” is not a valid command.
INFO 2008-07-17T21:06:29 Ignoring *!DrProjectBot@127.0.0.1 for 600 seconds due to
an apparent invalid command flood.
WARNING 2008-07-17T21:06:29 Apparent error loop with another Supybot
observed.  Consider ignoring this bot permanently.

Basically, I didn’t RTFM. Every message received by a bot is interpreted as a command. So, when the service bot received “The operation succeeded.”, it was very angry at the DrProjectBot, because “The operation succeeded.” is not a valid command. It proceeded to yell at DrProjectBot with a message “error: “The” is not a valid command.”. Naturally, DrProjectBot also became angry, because “error: “The” is not a valid command.” is not a valid command, and the cycle started.

Thanks to good people on #supybot channel, I found a way for the bot to ignore non-command messages. Setting supybot.reply.whenNotCommand variable to False does the trick.