Play with the sqlite3 shell

Before using an SQLite database in an app, we will play with the sqlite3 shell interactively. Open the Terminal window of your Mac, or the cmd.exe window of your Windows PC.

Install sqlite3

Sqlite3 for Android people

Android people already have sqlite3 (or sqlite3.exe). They installed it when they installed Android. It’s in the sdk-tools directory, and the name of this directory is already in their path. You can run sqlite3 on your Mac or PC. You can also run it on the Android Emulator:

adb shell
# cd data/data/edu.nyu.scps.myproject/databases
# pwd
# ls -l
# sqlite3 database.db

Sqlite3 on Mac for iOS people

See if your Mac already has sqlite3. Open a Terminal window and say

cd ~/Desktop
pwd

which sqlite3
/usr/bin/sqlite3

find / -type f -name sqlite3 2> /dev/null
/usr/bin/sqlite3

sqlite3 -help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd command         run "command" before reading stdin
   -csv                 set output mode to 'csv'
   -echo                print commands before execution
   -init filename       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -nullvalue 'text'    set text string for NULL values
   -separator 'x'       set output field separator (|)
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS

sqlite3 -version
3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc

Install the current version of sqlite3 on your Mac

Install gcc on your Mac

You need to have the GNU gcc compiler for the language C on your Mac before you can install sqlite3. Dominick Ciano provided the following instructions for getting gcc. Go to http://connect.apple.com/ and sign in with your Apple Developer ID and password. Download “Command Line Tools (OS X Lion) for Xcode - July 2012” by clicking on the hyperlink for it. Then double-click on the xcode44cltools_10_76938107a.dmg file that came down as the download.

Download sqlite3 to your Mac

In the Terminal window, create a shell variable containing the current version number (3.7.13).

v=sqlite-autoconf-3071300
echo $v
sqlite-autoconf-3071300

ftp http://www.sqlite.org/$v.tar.gz
1845778 bytes retrieved in 00:00 (2.21 MiB/s)

ls -l $v.tar.gz
-rw-r--r--  1 myname  staff  1845778 Jul 27 12:00 sqlite-autoconf-3071300.tar.gz

Decompress and unpack

gunzip is the GNU version of unzip. tar stands for “tape archive”.

gunzip $v.tar.gz
ls -l $v.tar
-rw-r--r--  1 myname  staff  7260160 Jul 27 12:00 sqlite-autoconf-3071300.tar

tar xvf $v.tar

ls -ld $v
drwxr-xr-x  23 myname  staff  782 Jun 11 05:34 sqlite-autoconf-3071300

rm $v.tar

cd $v
pwd

ls -l README INSTALL configure
-rw-r--r--  1 myname  staff  9498 Jun 11 05:33 INSTALL
-rw-r--r--  1 myname  staff  1144 Jun 11 05:33 README
-rwxr-xr-x  1 myname  staff  697817 Jun 11 05:33 configure

Compile and install

The INSTALL file says that sqlite3 is installed into the directory /usr/local by default. For example, the executable sqlite3 program itself is installed in the subdirectory /usr/local/bin, and the C header file sqlite3.h is installed in the subdirectory /usr/local/include. The --prefix=/Users/myname option of ./configure is needed if you want to install sqlite3 into a directory other than /usr/local. I had to give it this option because I have no permission to put files into /usr/local on the Mac I use. I had to install sqlite3 into the directory /Users/myname instead.

./configure --prefix=/Users/myname

make
make check
make install

ls -l /Users/myname/bin/sqlite3
-rwxr-xr-x  1 myname  staff  55212 Jul 27 12:05 /Users/myname/bin/sqlite3

Change your PATH variable

The directory that holds the new sqlite3 executable is /Users/myname/bin. I added this directory to the front of the list of directories in my PATH.

echo $PATH
/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/opt/X11/bin:/usr/X11/bin

export PATH=/Users/myname/bin:$PATH
echo $PATH
/Users/myname/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/opt/X11/bin:/usr/X11/bin

Did it work? Do we now have the current version of sqlite3?

which sqlite3
/Users/myname/bin/sqlite3

sqlite3 -version
3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc

Install the current version of sqlite3 on your PC

Go to the download page, Precompiled Binaries For Windows, and download the command line shell.

Play with sqlite3

Play with the following commands interactively now. They will be executed by an app via the functions sqlite3_exec and the trio of functions sqlite3_prepare_v2/sqlite3_step/sqlite3_finalize. Documentation for the sqlite3 shell and the SQL language.

cd ~/Desktop
pwd

Create a database file

The name of our database file will be /Users/myname/Desktop/database.db.

sqlite3 database.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .help

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /Users/myname/Desktop/database.db

Put a table into the database

A table is a rectangle made of rows and columns. The columns are things like lastname, firstname, social security number. The datatype of each column could be text, integer (a number without a decomal point or fraction), real (a number with a decimal point and fraction), etc. text is called varchar in other databases. The rows are the records (Tom, Dick, and Harry).

The name of our table will be people. Each of the commands drop table and create table is two separate words with a space between them. Don’t forget the semicolon at the end of every SQL statement. (The statements that began with a dot are not SQL statements. They are the “meta-commands” listed in the sqlite3 manual page.) The sqlite3 continuation prompt is …>. Control-p to go back to the previous line.

sqlite> drop table if exists people;

sqlite> create table people (
           lastname text,
           firstname text,
           ss integer
        );

sqlite> .tables
people

sqlite> .schema people
CREATE TABLE people (
     lastname text,
     firstname text,
     ss integer
);

Put records into the table

sqlite> insert into people (lastname, firstname, ss) values ('Balacek', 'Carol', 000000001);
sqlite> insert into people (lastname, firstname, ss) values ('Becker', 'Samuel', 000000002);
sqlite> insert into people values ('Bourne', 'Geoffrey', 000000003);

sqlite> select count(*) from people;
3

The * in the following select statement means “all the fields in the record”.

sqlite> select * from people;
Balacek|Carol|1
Becker|Samuel|2
Bourne|Geoffrey|3

Import a file of comma-separated fields

I created a file named base.csv on oit2.scps.nyu.edu, containing comma-separated values.

~meretzkm/bin/roster 36 -realnames | awk '{print $NF "," $2 "," NR}' > base.csv
head -5 base.csv
Balacek,Carol,1
Becker,Samuel,2
Bourne,Geoffrey,3
Capursi,Sergio,4
Cattafesta,Richard,5

Save base.csv onto your hard disk as base.csv, not base.csv.txt.

sqlite> delete from people;
sqlite> select count(*) from people;

sqlite> .mode csv
sqlite> .show

sqlite> .import base.csv people
sqlite> select count(*) from people;
29
sqlite> select * from people;

Formatted output

sqlite> .headers on
sqlite> .mode column
sqlite> select * from people;
lastname    firstname   ss
----------  ----------  ----------
Balacek     Carol       1
Becker      Samuel      2
Bourne      Geoffrey    3

sqlite> select ss, firstname from people;
ss          firstname
----------  ----------
1           Carol
2           Samuel
3           Geoffrey

sqlite3> .dump
BEGIN TRANSACTION;
CREATE TABLE people (
           lastname text,
           firstname text,
           ss integer
        );
INSERT INTO "people" VALUES('Balacek','Carol',1);
INSERT INTO "people" VALUES('Becker','Samuel',2);
INSERT INTO "people" VALUES('Bourne','Geoffrey',3);
etc.
COMMIT;

Be selective

sqlite3> .headers off
sqlite3> .separator |
sqlite3> .mode list

sqlite3> select * from people;
Balacek|Carol|1
Becker|Samuel|2
Bourne|Geoffrey|3

sqlite3> select * from people where ss > 10;
Halderman|John|11
Harvey|James|12
Johnson|Kevin|13

% is a wildcard.

sqlite3> select * from people where firstname like 'J%';
Halderman|John|11
Harvey|James|12
Parris|Jamar|22

sqlite3> select * from people where length(firstname) == length(lastname);
Becker|Samuel|2
Jones|Scott|15
Juro|Todd|16
Liao|Mark|18
Noreen|Sajida|20
Wong|Yung|27

sqlite3> select * from people where firstname == 'Mark';
Liao|Mark|18
Meretzky|Mark|19

desc means “descending”.

sqlite3> select * from people order by firstname;
sqlite3> select * from people order by firstname desc;
sqlite3> select * from people where ss > 10 order by ss desc;

“And”, “Or”, “Not”

sqlite> select * from people where ss > 10 and firstname like 'J%';
Halderman|John|11
Harvey|James|12
Parris|Jamar|22

sqlite> select * from people where ss > 28 or firstname like '%v%';
Disla|Ivan|7
Johnson|Kevin|13
Williams|David|26
Yuen|Sammy|29

Three underscores.

sqlite> select * from people where not lastname like '%___%';
Wu|Clement|28

Update

The || (no space between the bars) is string concatenation.

sqlite> update people set firstname = firstname || ', Jr.' where ss > 26;
sqlite> select * from people where ss > 26;
Wong|Yung, Jr.|27
Wu|Clement, Jr.|28
Yuen|Sammy, Jr.|29

Let’s undo the above set.

sqlite> update people set firstname = replace(firstname, ', Jr.', '') where ss > 26;
sqlite> update people set ss = ss * 10;

Be destructive

sqlite> delete from people where lastname == 'Wu';
sqlite> delete from people;

Exit

After you .exit or .quit from sqlite3, you should see the new file database.db in the current directory.

sqlite> .exit
ls -l database.db

Non-interactive dump

Press the space bar to tell more to release the next screenful of the dump, or q to quit.

sqlite3 database.db .dump | more