Dumping SQLite Table Structure Topic:Database   Date: 2006-08-26 Printer Friendly:   Mobile View:

SQLite is a small, embeddable SQL database engine. Its licensing allows it to be included in commercial products and modified without publishing the source code. REALbasic uses SQLite as its included database engine. REALbasic has a query tool; however, it is nice to have the full-blown sqlite client. Let's download the tarball and configure, compile, and install. This compile is for a Mac OS X system:

srv-5:~/sqlite usr4$ tar -xzf sqlite-3.3.7.tar.gz srv-5:~/sqlite usr4$ cd sqlite-3.3.7 srv-5:~/sqlite/sqlite-3.3.7 usr4$ ./configure checking build system type... powerpc-apple-darwin8.7.0 checking host system type... powerpc-apple-darwin8.7.0 checking for gcc... gcc checking for C compiler default output file name... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... . . . configure: creating ./config.status config.status: creating Makefile config.status: creating sqlite3.pc srv-5:~/sqlite/sqlite-3.3.7 usr4$ srv-5:~/sqlite/sqlite-3.3.7 usr4$ make sed -e s/--VERS--/3.3.7/ ./src/sqlite.h.in | \ sed -e s/--VERSION-NUMBER--/3003007/ <sqlite3.h gcc -g -O2 -o lemon ./tool/lemon.c . . . ranlib .libs/libtclsqlite3.a creating libtclsqlite3.la (cd .libs && rm -f libtclsqlite3.la && ln -s ../libtclsqlite3.la libtclsqlite3.la) srv-5:~/sqlite/sqlite-3.3.7 usr4$ srv-5:~/sqlite/sqlite-3.3.7 usr4$ su Password: srv-5:/Users/usr4/sqlite/sqlite-3.3.7 root# make install tclsh ./tclinstaller.tcl 3.3 /usr/bin/install -c -d /usr/local/lib . . . /usr/bin/install -c -m 0644 sqlite3.pc /usr/local/lib/pkgconfig; srv-5:/Users/usr4/sqlite/sqlite-3.3.7 root# srv-5:/Users/usr4/sqlite/sqlite-3.3.7 root# exit exit

The database is simply a file. Let's connect:

srv-5:~/sqlite/sqlite-3.3.7 usr4$ sqlite3 ~/mcj-2.0.src/mcj.rsd SQLite version 3.1.3 Enter ".help" for instructions sqlite>

We want to dump the table structure only, so what we want to do is make sure there are no records in the database. We need to do this anyway before publishing the application we are writing. Of course, make sure you have a backup of the data before you do this. With SQLite, everything is in a single file. If the database is closed, simply copy the file. We have one in settings:

sqlite> select * from settings; editorcommand|open -a /Applications/Vim.app ~/mcj-2.0.src/ sqlite> select * from times; sqlite> delete from settings; sqlite> select * from settings;

All clear. Let's verify the tables we have and dump the structure:

sqlite> .tables files journal places things urls images people settings times sqlite> .dump BEGIN TRANSACTION; CREATE TABLE arts(title Text, classification Text, date date, atme time, entry blob, artnum Integer, realm Text); CREATE TABLE settings(parameter text, value Text); CREATE TABLE peoples(realm Text, artnum Integer, person Text); CREATE TABLE places(realm Text, artnum integer, place Text); CREATE TABLE things(realm Text, artnum Integer, thing Text); CREATE TABLE tmes(realm text, artnum Integer, tme Text); CREATE TABLE images(realm Text, artnum Integer, tag Text, file blob); CREATE TABLE urls(realm Text, artnum Integer, tag Text); CREATE TABLE files(realm Text, artnum Integer, tag Text, file Blob); COMMIT; sqlite> .quit srv-5:~/sqlite/sqlite-3.3.7 usr4$