NOTICE:
This content is in the "archives" of a retired blog called Gadgetopia. It has been moved to this subdomain as it is no longer considered relevant to the site. It is being hosted here for a indeterminate period of time. Its existence at this URL is not guaranteed, and it may be removed at any time. If you would like to refer to this content in the future, you are encouraged to save it to your local file system.

Some content from Gadgetopia was moved to the technical blog on deanebarker.net

SQLite

Originally published by "dbarker" on 2004-02-05 11:29:00Z

SQLite: An Embeddable SQL Database Engine: I’m just tickled with this little program. I’m not a client applications programmer, but if I was…

SQLite is a tiny, file-based database engine. The Windows exectuable is only 280KB and it’s statically compiled, so it needs nothing else to run (read: no install). You call it from a command line, specifying the data file and the SQL. It spits back a recordset as delimited text (the pipe is default, but you can change it). That’s about all there is to it.

It’s a lightning fast little bugger, and it’s apparently going to be included with PHP5. It supports views, temporary tables, and most of ANSI-SQL 92 (with the glaring exception of ALTER TABLE – to change a table, you must drop and recreate it). It doesn’t support foreign key restraints, but neither does MySQL, so I don’t weigh that too heavily.

The data files are (obviously) transportable, and they apparently work the same across different platforms. At idle on Windows XP, SQLite didn’t even consume 1MB of RAM. Now it just needs a COM wrapper.

SQLite is public domain software, which means it’s one better than open-source – you can distribute it with other apps and not have to GPL the whole thing.

The danger for little apps like this is making them any more complex than they are. SQLite is a thing of beauty, and the temptation is to keep adding to it. Let’s hope the developers resist.

Comment by "Deane" on 2004-02-06 15:24:00Z
SQLite is typeless, meaning that you can store anything in any column. Specifiying a column type in your CREATE TABLE statement is for documentation purposes only. The only exception is that you can specify an integer primary key to auto increment. More... [http://www.sqlite.org/datatypes.html](http://www.sqlite.org/datatypes.html)
Comment by "Deane" on 2004-02-06 15:49:00Z
I was doing a little testing this morning. To run PHP from the command line on Windows, you need three files: php.exe, php4ts.dll, and php.ini. I downloaded version 5 of php.exe which includes SQLite support natively. Using this, it was easy to do database programming in PHP from the command line. With PHP5, you don't even need the SQLite executable -- PHP has a built-in function library. And it's totally transferable between machines. I zipped up the entire directory with the PHP and SQLite data files and everything, then took it to a machine that has never had PHP installed. It ran just fine. Using this, you can do some advanced script programming. PHP is a much more mature language than VBScript, and SQLite would give your script persistent database storage (which, admittedly, could be accomplish with VBScript and Access, but at the cost of a lot more complexity and overheard). Shiny objects.
Comment by "Deane" on 2004-02-06 16:50:00Z
ODBC driver. Works great from VBScript. [http://www.ch-werner.de/sqliteodbc/](http://www.ch-werner.de/sqliteodbc/) I hate it when I get tunnel vision about something like this. I'm obsessed right now. [sigh] It'll pass...
Comment by "Deane" on 2004-02-06 08:07:00Z
[http://www.kraslabs.com/sqlite/](http://www.kraslabs.com/sqlite/) GUI management tool. I'll stop now. I have to get back to work.
Comment by "David Spector" on 2009-06-01 11:17:00Z
The Kraslabs SQLite Analyzer is $99. Not exactly public domain. However, free SQLite managers are available.