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
Android
people already have
sqlite3
(or
sqlite3.exe
sdk-tools
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
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
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.
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
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
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
./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
/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
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
Go to the download page, Precompiled Binaries For Windows, and download the command line shell.
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
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
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 );
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
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;
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;
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;
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
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;
sqlite> delete from people where lastname == 'Wu'; sqlite> delete from people;
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
Press the space bar to tell
more
to release the next screenful of the dump,
or
q
to quit.
sqlite3 database.db .dump | more