Tags:
create new tag
, view all tags

Using SQLite

SQLite is a compact, fast, flexible, embeddable and mostly standards adherent SQL engine.

Compilation and Installation

As a prerequesite, SQLite can make use of Tcl. However, tcl is not strictly required, and things work well without it.

All information and documentation on the SQLite engine can be found at the SQLite downloads.

The JDBC driver is contributed software, and can be found at SQLite JDBC.

  1. Chose the downloads to download the latest source tarballs for both, version 2 and version 3. Currently, versions sqlite-2.8.16.tar.gz and sqlite-3.2.1.tar.gz are latest. Also download the latest JDBC driver, currently javasqlite-20050608.tar.gz.
  2. Start with version 2:
    1. Unpack the tarball into a directory of your choice.
    2. Change into the directory.
    3. Run the configure script with the following options:
      • CC='gcc -march=i686'
      • configure
      • --prefix=/usr
      • --with-utf8
      • --enable-threadsafe
      • --enable-shared
      • --enable-static
      • anything else you deem worthwile
    4. Run make
    5. Run make test as non-root user.
    6. Run make install as root user.
  3. Now compile version 3. Both versions co-exist:
    1. Unpack the tarball into a directory of your choice.
    2. Change into the directory.
    3. Run the configure script with the following options:
      • CC='gcc -march=i686'
      • configure
      • --prefix=/usr
      • --enable-threadsafe
      • --enable-shared
      • --enable-static
      • --disable-tcl
      • anything else you deem worthwile
    4. Run make
    5. The make test doest not work for me due to Tcl problems.
    6. Run make install as root user.
  4. As root user, run ldconfig /usr/lib to update the shared library cache.
  5. Now install the JDBC driver - do not follow their instructions:
    1. Unpack the tarball as usual.
    2. Change into the created directory.
    3. Set your CLASSPATH environment variable to just "." (dot).
    4. Make sure your JAVA_HOME is set correctly.
    5. Run the configure script with the following options:
      • CC='gcc -march=i686'
      • ./configure
      • --prefix=/usr
      • --with-jdk=$JAVA_HOME
      • anything else you fancy.
    6. Run make
    7. Run make install.
    8. As root, copy /usr/share/java/sqlite.jar into $JAVA_HOME/jre/lib/ext. If you used a different prefix when compiling the JDBC driver, change as necessary.
    9. As root, change directory to /usr/lib and run ls libsqlite_jni* | cpio -pdmav $JAVA_HOME/jre/lib/i386. This will copy the shared libraries required by the JDBC driver during startup.
  6. As root user, run ldconfig /usr/lib to update the shared library cache again.

Important! The JDBC compilation step needs to be repeated whenever you change your java installation. These last two steps avoid having you to provide the -classpath and -Djava.library.path..= locations the JDBC documentation requires you to have.

Testing

In order to test the new database, assuming above installation locations, you first have to create a database. The sqlite and sqlite3 commandline clients let you manipuate a SQLite database file in the same manner that psql lets you manipulate a Postgres database.

$ sqlite3 test.db
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table X ( a integer primary key, b varchar(32) );
sqlite> .schema
CREATE TABLE X ( a integer primary key, b varchar(32) );
sqlite> .quit

The above snippet creates a database file called test.db. It contains a 2-column table. The primary key is integer, which is 2^31 in sqlite3, and is implicitely auto incremental. However, if should you use the key word autoincrement with that key, a slightly different, more complex algorith is taken, see SQLite Autoincrement for details.

You can now download the following SQLiteJDBC.java program. With the locations pointed out in the previous section, the program should compile and run fine. I assume that for now, you CLASSPATH is still set to "." (dot).

$ javac SQLiteJDBC.java
$ java SQLiteJDBC
insert returns 1
i=1, s=qwer
$ java SQLiteJDBC
insert returns 1
i=1, s=qwer
i=2, s=qwer

Known Caveats

While the SQLite engine is fast and powerful, has transactional integrity, support for many data types and even stored procedures, see documentation the JDBC interface to it is not as shining:

  • The JDBC driver is only JDBC2x compliant.
  • Many optional features from the JDBC spec are not implemented. The SQLite JDBC driver throws a SQLException in this case.
  • Limited range of supported data types:
    • String, short, int, float and double are supported.
    • Date, Time and Timestamp have untested support with sqlite3.
    • Apparently neither char nor boolean support.
  • Even though autoinc columns are supported, obtaining generated keys is not supported.
  • Unsure how to initiate transaction blocks from the JDBC side. The keyword begin is recognized.

-- JensVoeckler - 08 Jun 2005

Topic attachments
I Attachment Action Size Date Who Comment
Java source code filejava SQLiteJDBC.java manage 1.0 K 2005-06-08 - 17:23 UnknownUser SQLite JDBC Java test file
Topic revision: r3 - 2013-08-07 - DavidEForero
 
This site is powered by the TWiki collaboration platformCopyright © 2008-2014 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding CI Wiki? Send feedback