Dumping SQLite Table Structure

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

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

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
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);
sqlite> .quit
srv-5:~/sqlite/sqlite-3.3.7 usr4$