Adding columns to Sqlite tables behind SQLObject
So I finally got some time on the internet (stupid ISP, bad luck etc..) and one of the tasks to do was to add a feed column to the website table in my fishing directory. Since I am using Sqlite 2.8 (?) no ALTER TABLE construct is available (added with Sqlite 3) and SQLObject doesn’t make it any easier.
I did the same change while changing the rest of the code for this (so it should be a quick task once I got online and had access to the updated database) but it was a little complicated. It involved running two simultaneous SQLObject databases with one containing the extra column for feed urls and writing a special function for copying between the databases. It was in fact so complicated that i didn’t remember exactly how i did it and SQLObject started complaining about classes that were already in the class registry. And that class was part of a module I wasn’t importing this time. (Insert extremely frustrated smiley here).
This lead me to explore how this could be done with Sqlite. The recipe is quite simple. The sqlite database is in the olddatabase.db file and I will create a file named newdatabase.db for the new database.
C:\>sqlite olddatabase.db
sqlite> .output sqlitedump.sql
sqlite> .dump
sqlite> .quit
Now I had a dump of the freshest database without the feed column in sqlitedump.sql. Adding the feed column was as easy as opening sqlitedump.sql and inserting a new column into the SQL table definition. The second step was to search-and-replace ); with ,”); inside the sql for inserting the websites to account for the extra column and
C:\>sqlite newdatabase.db < sqlitedump.sql
The only things left were to add the feed column to the SQLObjectified Website class definition and point the database connection to the new database. Very simple compared to the SQLObject approach I had used before.