SQLite alter table

calendar Jul 25, 2010 · 1 min read  it sql  · cute embedded programming small smart sql sqlite  ·
Share on: linkedin copy

SQLite is a small, powerful embedded database. A friend of mine started using it about six years ago. Some years ago it comes also on top of  Python 2.5. It is used inside

  • FileMaker Bento: its ultra customized model is based on a big sqllite db
  • DropBox client, to store its internal state
  • iPhone: stores your SMS and also other stuff. It is widely used by apps.
  • Apple Safari uses it for HTML5 storage support
  • Google Gears uses it
  • ...and in a lot of embedded product.

I was annoyed because until version 3.1.3 SQLite did not provide an alter table syntax but... it is quite easy to emulate it with something like this, even if it required a bit of work:

[sql]BEGIN TRANSACTION; CREATE TEMPORARY TABLE TEMP_TAB(a,b); INSERT INTO TEMP_TAB SELECT a,b FROM TAB_TO_ALTER; DROP TABLE TAB_TO_ALTER; CREATE TABLE TAB_TO_ALTER(a,b,c); INSERT INTO TAB_TO_ALTER SELECT a,b,null FROM TEMP_TAB; DROP TABLE TEMP_TAB; COMMIT;[/sql] SQLite is a very 'honest' database, and will tell you when it is  the best option and when it is not.

the funny thing is SQLite provide transactions where MySQL try to avoid them and added only at huge penalty bugs.