SQLite sure is light…
I’m pressing on with my tinkering with SQLite and pySQLite, and things are going rather well on the python side. Less so on the SQL though. I’m quite familiar with SQL and it’s workings and how to manipulate tables. Problem is, SQLite is so light it is missing what I would term crucial features.
For example, dropping a column of a table in SQL is a one liner, pretty basic. Getting SQLite to do the same, is actually impossible directly. You have to create a temporary table, move the values in columns you want to keep to it, delete the original table, create the new table, move everything from the temporary table to the permanent one and then remove the temporary table. *phew* And this is an offcially documented method on the SQLite website. Eeep. There is also the slight quirk of needing to VACUUM your database after deleting large amounts of data, as the memory isn’t actually freed until you do so, despite the data not existing anymore.
I’m sure I will get used to these quirks, but it does raise an important point; no commands used to manipulate SQLite that differ from normal SQL should be used in our code. If they are we will be confined to SQLite in the future, when our ultimate aim is to enable the use of more advanced databases. Unless we feel like delving into the realms of DBAL…….
Need help using my Calendar plugin on your site?
Are you in my address book yet?
I've canned 3,564,021 spam blog comments so far and rising.
I have a zero tolerance on spam!
Your choices are half chance, so are everybody elses 
Nick Efford Said,
February 14, 2006 @ 3:09 pm
Some good insights into SQLite here. It is ‘only’ an embedded database, so if you are finding it too limiting then feel free to switch to something different. However, bear in mind that switch to a ‘proper’ RDBMS likely to have implications for testing and demonstrating your system…