====== Introduction ======
"SQLite is not designed to replace Oracle. It is designed to replace fopen()."
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform.
See http://www.sqlite.org/whentouse.html for a list of appropriate uses for SQLite..
====== Installing ======
===== Linux =====
sudo apt-get install sqlite3
===== Windows =====
Surf to http://sqlite.org/download.html and find the Windows Binary that best fits your taste
====== Using ======
sqlite3 myDb.sqlite # if myDb.sqlite does not exist, it is created
SQLite supports [[[http://www.sqlite.org/lang.html|MOST]]] of the SQL92 features. A list of unsupported features can be found at http://www.sqlite.org/omitted.html
====== Howto ======
===== Import values from CSV File =====
For this example we have a semicolon-separated csv file called emissions.csv and want to create a new database called emissions.sqlite. The csv file also has a header row, which we import and then delete.
create table emissions (stringCol text, doubleCol real, ...); #manually create table according to header row
.separator ";"
.import emissions.csv emissions
delete from emissions where XXXXXXX; # delete the first row that contains the header
select count(*) from emissions; # check nr of imported entries
Note: be sure to remove trailing separator, otherwise you and sqlite will think differently about the number of columns to import.
===== Import values from an other sqlite DB =====
attach '20080802_trips.db' as source;
INSERT INTO trips SELECT fleet_id,trip_id,timestamp,x,y FROM source.trips WHERE trip_id IN (SELECT trip_id FROM source.trips WHERE x > 16.395 AND x < 16.434 AND y>48.269 AND y<48.304);
detach source;
===== Write results to a file =====
You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to begin writing to standard output again. Example:
sqlite> .mode list
sqlite> .separator |
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
In Bashish:
echo -e '.mode list \n.separator ;\n.header on \n.output muh.txt\n select * from aggregation; \n' | sqlite3 ./my.db
So if you have lots of sqlite-files to export to csv, you might want to do
for i in `ls *db`; do echo -e ".mode list \n.separator ;\n.header on \n.output $i.csv\n select road_id as RoadId,lane_id as LaneId,inroaddir as Direction,speed as Velocity,length as Length,timestamp as Timestamp,trip_id as TripId,fleet_id as Source from lm; \n" | sqlite3 $i; done
===== Export a DB =====
A good way to make an archival copy of a database is this:
$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz
This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:
$ zcat ex1.dump.gz | sqlite3 ex2
The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:
$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2
===== Working with Dates =====
Although SQLite stores all dates as Strings, it provides some primitive functions to deal with Date-Strings. See http://www.sqlite.org/lang_datefunc.html
==== Select hour ====
SELECT trip_id, strftime("%H",timestamp) as hour from trips;
==== Select week day ====
Weekdays
SELECT * FROM lm_onroad WHERE strftime('%w',datetime(timestamp)) in ('6','0')
Weekends
SELECT * FROM lm_onroad WHERE strftime('%w',datetime(timestamp)) in ('1','2','3','4','5')
==== Subtract Timestamps ====
To subtract one timestamp from the other, you have to use strftime to convert both timestamps to seconds. For Example:
SELECT trip_id, start_time,end_time,STRFTIME('%s',end_time) - STRFTIME('%s',start_time) FROM delay; # use this only for dates after 1970!
===== In-memory databases =====
In-memory databases must be named :memory: , which tells SQLite to create the database in memory.
The general approach for copying an existing database into memory is:
1. Create a new in-memory database (IMDB)
1. Open the on-disk database (ODDB) and attach it to IMDB
1. Copy table definitions from ODDB to IMDB
1. Copy data from ODDB to IMDB
1. Copy other definitions like indices from ODDB to IMDB
1. Detach ODDB and happily work with your fast IMDB (but beware of the fact that all changes to IMDB are not persistent)
An exhaustive example on how to do this in Java:
/**
* Open a connection to the database and copy it into RAM. This results
* in better performance for queries but also means that any changes
* will not be persistent.
*/
private boolean openConnectionAndCopyDbInMemory(String sqliteFile) {
File file = new File(sqliteFile);
System.out.println("Loading " + file.getAbsolutePath() + " into memory");
try {
// load the sqlite driver
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite::memory:");
stmt = conn.createStatement();
stmt2 = conn.createStatement();
rs = rs2 = null;
// attach local database to in-memory database
String attachStmt = "ATTACH '" + file.getAbsolutePath() + "' AS src";
stmt.execute(attachStmt);
// copy table definition
String tableQuery = "SELECT sql FROM src.sqlite_master WHERE type='table';";
rs = stmt.executeQuery(tableQuery);
while(rs.next()) {
stmt.execute(rs.getString(1));
}
// copy data
String tableNameQuery = "SELECT name FROM sqlite_master WHERE type='table'";
rs = stmt.executeQuery(tableNameQuery);
while(rs.next()) {
String copyDataQuery =
"INSERT INTO " + rs.getString(1) + " SELECT * FROM src." + rs.getString(1);
stmt.execute(copyDataQuery);
}
// copy other definitions (i.e. indices)
String nonTableQuery = "SELECT sql FROM src.sqlite_master WHERE type!='table';";
rs = stmt.executeQuery(nonTableQuery);
while(rs.next()) {
stmt.execute(rs.getString(1));
}
// detach local db
String detachStmt = "DETACH src";
stmt.execute(detachStmt);
// test if everything went well
String sqlQuery = "SELECT sql FROM sqlite_master";
String recordsQuery = "SELECT COUNT(*) FROM emissions";
rs = stmt.executeQuery(sqlQuery);
System.out.println("Database in memory has the following definition:");
while(rs.next())
System.out.println(rs.getString(1));
rs = stmt.executeQuery(recordsQuery);
while(rs.next())
System.out.println("Database contains " + rs.getInt(1) + " records");
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
===== Explore the Database =====
SQLite supports two (rather primitive) commands to explore the database:
.tables #shows the names of all tables in the DB
.schema
==== Using the Master Table ====
The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database:
select * from sqlite_master;
This will get you the names of all the tables, and the corresponding CREATE statements
===== Spatialite =====
==== Install ====
sudo apt-get install python-pysqlite2 spatialite-bin
To prepare a database for spatial data:
wget http://www.gaia-gis.it/spatialite/init_spatialite-2.3.sql.gz
gunzip init_spatialite-2.3.sql.gz
spatialite mydatabase.db < init_spatialite-2.3.sql
Afterwards you'll find additional tables like
spatial_ref_sys: contains [[[http://spatialreference.org/ref/epsg/|spatial reference systems]]]
Now you can for example add a column for GPS coordinates:
spatialite trip.db
>select AddGeometryColumn('trips','the_geom_4326',4326,'Point',2);
>UPDATE trips SET the_geom_4326 = GEOMETRYFROMTEXT('POINT('|x|' '|y |')',4326); # or use MakePoint( x Double precision , y Double precision , [ , SRID Integer] ) : Geometry
>SELECT RecoverGeometryColumn('trips','the_geom_4326',4326,'POINT',2);
==== Usage ====
Here's the [[[http://www.gaia-gis.it/spatialite/spatialite-sql-2.3.1.html|function reference]]].
Loading the spatialite module:
from pysqlite2 import dbapi2 as sqlite3
# Load the spatialite extension of sqlite
con = sqlite3.connect(db)
con.enable_load_extension(True)
con.execute("select load_extension('libspatialite.so')") # maybe you'll need a symlink to libspatialite.so.x
con.enable_load_extension(False)
====== Performance Issues ======
Some insights can be found here:
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
http://katastrophos.net/andre/blog/2007/01/04/sqlite-performance-tuning-and-optimization-on-embedded-systems/
===== Avoid LIKE or GLOB =====
Never use the LIKE or GLOB statement. A less-elegant AND/OR construct is magnitudes faster. Tests with an in-memory DB and LIKE queries from Java resulted in a runtime difference of 10^3! More insights can also be found on http://wiki.tcl.tk/17365.
===== Use Indices =====
Placing indices on columns that are used in joins or where statements are a good way to increase query speed.
====== Using SQLite in programs ======
* [[[http://code.ca-net.org/Python%20Cookbook#SQLite|Python]]]
====== References ======
* [[[http://www.sqlite.org/|SQLite Homepage]]]