SQLite

pointers

• http://www.sqlite.org/
• http://www.sqlite.org/lang.html
• UnixGeekTools - other great tools for unix geeks

features

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine. Features include:

• Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
• Zero-configuration - no setup or administration needed.
• Implements most of SQL92. (Features not supported)
• A complete database is stored in a single disk file.
• Database files can be freely shared between machines with different byte orders.
• Supports databases up to 2 terabytes (241 bytes) in size.
• Sizes of strings and BLOBs limited only by available memory.
• Small code footprint: less than 30K lines of C code, less than 250KB code space (gcc on i486)
• Faster than popular client/server database engines for most common operations.
• Simple, easy to use API.
• TCL bindings included. Bindings for many other languages available separately.
• Well-commented source code with over 90% test coverage.
• Self-contained: no external dependencies.
• Sources are in the public domain. Use for any purpose.

The SQLite distribution comes with a standalone command-line access program (sqlite) that can be used to administer an SQLite database and which serves as an example of how to use the SQLite library.

data types

• null
• integer
• real
• text
• blob

examples

    sqlite filename.db
    > CREATE TABLE test1 ( a INTEGER UNIQUE, b REAL, c TEXT );
    > .help
    > .tables
    > .schema
    > insert into test1 (keys) values( 1 );
    > insert into test1 (keys) values( 3 );
    > select * from test1;
    > select * from test1 order by percent desc;
    > copy tablename from "filename" using delimiters " "
    > .quit

Dump

- generate sql statements which could be used to re-build a table or db

    # dump all
    sqlite filename.db ".dump" > ~/tmp/table.sql

    # dump a specific table
    sqlite filename.db ".dump <table>" > ~/tmp/table.sql

problems

• does not enforce data type constraints
• big file size increases, e.g 3 x flat file (census data test)

gui

• SQuirreL - add sqlite lib
• http://sqlitebrowser.sourceforge.net/ - read only?
• sqlite control center - wouldn't open db, no configure script

JDBC

• http://www.ch-werner.de/javasqlite/overview-summary.html

    jdbc:sqlite://dirA/dirB/dbfile
    jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile
    jdbc:sqlite:///COMPUTERNAME/shareA/dirB/dbfile

problems building on osx!!! :(

someone suggested something like this:

    ./configure --with-jdk=/System/Library/Frameworks/JavaVM.framework/Home
    make
    cp .libs/libsqlite_jni.dylib libsqlite_jni.jnilib

freebsd

• download driver

    -I/usr/local/jdk1.4.2/include/freebsd -I/usr/local/jdk1.4.2/include/