Tuesday, December 01, 2009


I used a Sqlite database for a school project last night. It is more a substitute for Excel or Access than for SQL Server, and I am not sure how we would use it here, but it is generally useful, and something I wish I learned about years ago.

Some highlights:
1) Free, cross platform and easy to install. One .exe file for a Windows machine, or apt-get for Debian based Linux.
2) You can copy/backup/move a database as 1 file (like Excel and Access, unlike SQL Server).javascript:void(0)
3) Open source.
4) Command line interface only (no GUI). I found something called “Sqlite browser” but am not happy with it.

It has a steep but short learning curve:
1) If you want to work with a database file ‘mydata.db’, you type ‘sqlite mydata.db’ to get started. I am not sure what just typing ‘sqlite’ does, but it starts the program, and then you really cannot do anything until you exit (or at least I haven’t figured out how to do anything useful).
2) Creating a new database is similar, ‘sqlite filename.db’, where filename.db is the file name you want to use.
3) You can exit with ‘.exit’
4) You cannot use ‘SELECT TOP 100 * FROM FileMain’, you have to use ‘SELECT * FROM FileMain LIMIT 100;’
5) The command line interface allows line breaks, which is useful for large queries. You need to type a ‘;’ to process the line. This can be really frustrating at first, since if you have anything on the line, and you type .help or .exit and hit enter, you just get a line break, and you end up having to CTL-Alt-Delete the program unless you can figure out you need to type a ‘;’