SQLite
Feb 5

SQLite

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.

by Deane   February 5, 2004 5:29 PM   |
  tags: 
SQLite, MySQL

Comments

by Deane,   February 6, 2004 9:24 AM  

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

by Deane,   February 6, 2004 9:49 AM  

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.

by Deane,   February 6, 2004 10:50 AM  

ODBC driver. Works great from VBScript.

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...

by Deane,   February 6, 2004 2:07 PM  

http://www.kraslabs.com/sqlite/

GUI management tool. I'll stop now. I have to get back to work.

by David Spector,   June 1, 2009 6:17 PM  

The Kraslabs SQLite Analyzer is $99. Not exactly public domain. However, free SQLite managers are available.

Comments are Closed

Thanks to all who participated.
Want to advertise on this site? Contact FM.