User Tools

Site Tools


sqlite

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

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

References

sqlite.txt · Last modified: 2012/07/09 18:13 by mantis