Getting Started With SQLite|
I've successfully installed SQLite on Linux, FreeBSD, NetBSD, OpenBSD, SCO OpenServer and Solaris. On all platforms, I've had to compile it from source. SQLite is available from the SQLite home page. I usually give the configure script the --prefix=/usr/local/sqlite parameter so that SQLite will be installed entirely under /usr/local/sqlite and add /usr/local/sqlite/bin to my PATH environment variable and /usr/local/sqlite/lib to my LD_LIBRARY_PATH environment variable.
On OpenBSD and NetBSD sqlite will build cleanly but will crash at run time. To get it working, you have to edit the Makefile that the configure script generates, search for a line like:
LIBREADLINE = -lreadline
And add -lcurses to the end of it as such:
LIBREADLINE = -lreadline -lcurses
Compiling sqlite with this modification will make it work.Creating a Database
In version 1.0.x of SQLite, a database is just a directory with table files in it. In versions 2.x, the database is a single file. Unlike other relation database systems, there is no daemon managing the database, the SQLite API interprets queries and runs them against the file(s) directly. I usually create a directory /usr/local/sqlite/var and create database directories or files there as follows. SQLite database users are equivalent to unix users. Database directory and file permissions dictate what permissions a user has on the database.
For SQLite versions 2.x, it is only necessary to create the directory, the database file will get created the first time a user tries to access it.
For SQLite version 1.0.x, the following commands are necessary to create a database called testdb owned by the user testuser.
mkdir /usr/local/sqlite/var/testdbAccessing a Database
To access an SQLite database, su to the appropriate user and run the sqlite command line utility.
Once you're connected to the database, the sqlite client prompts you to enter a query. Queries may be split across multiple lines. To run a query, end it with a semicolon. To exit, type .exit
A sample sqlite session follows.
Accessing a Database With SQL Relay[testuser@localhost testuser]$ sqlite /usr/local/sqlite/var/testdb SQLite version 2.2.0 Enter ".help" for instructions sqlite> create table testtable ( ...> col1 char(40), ...> col2 integer ...> ); sqlite> .tables testtable sqlite> insert into testtable values ('hello',50); sqlite> insert into testtable values ('hi',60); sqlite> insert into testtable values ('bye',70); sqlite> select * from testtable; hi|60 hello|50 bye|70 sqlite> update testtable set col2=0 where col1='hi'; sqlite> select * from testtable; hi|0 hello|50 bye|70 sqlite> delete from testtable where col2=50; sqlite> select * from testtable; hi|0 bye|70 sqlite> drop table testtable; sqlite> .exit
Accessing SQLite from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called sqlitetest. This instance connects to the /usr/local/sqlite/var/testdb database. Note how the runasuser and runasgroup attributes of the instance tag are set to the owner of the database.
<!DOCTYPE instances SYSTEM "sqlrelay.dtd"> <instances> <instance id="sqlitetest" port="9000" socket="/tmp/sqlitetest.socket" dbase="sqlite" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="testuser" runasgroup="testuser" cursors="5" authtier="listener" handoff="pass"> <users> <user user="sqlitetest" password="sqlitetest"/> </users> <connections> <connection connectionid="sqlitetest" string="db=/usr/local/sqlite/var/testdb" metric="1"/> </connections> </instance> </instances>
Now you can start up this instance with the following command.
sqlr-start -id sqlitetest
To connect to the instance and run queries, use the following command.
sqlrsh -id sqlitetest
The following command shuts down the SQL Relay instance.