User Tools

Site Tools


sqlite

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
sqlite [2012/05/23 16:26]
mantis [Usage]
sqlite [2012/07/09 18:13] (current)
mantis
Line 1: Line 1:
 +
 +
 +====== Introduction ======
 +<​code>​
 +  "​SQLite is not designed to replace Oracle. It is designed to replace fopen()."​
 +</​code>​
 +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 =====
 +<​code>​
 +  sudo apt-get install sqlite3
 +</​code>​
 +===== Windows =====
 +Surf to http://​sqlite.org/​download.html ​ and find the Windows Binary that best fits your taste
 +
 +====== Using ======
 +<​code>​
 +  sqlite3 myDb.sqlite # if myDb.sqlite does not exist, it is created
 +</​code>​
 +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.
 +
 +<​code>​
 +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
 +</​code>​
 +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 =====
 +<​code>​
 +  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;
 +</​code>​
 +===== 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:
 +
 +<​code>​
 +  sqlite> .mode list
 +  sqlite> .separator |
 +  sqlite> .output test_file_1.txt
 +  sqlite> select * from tbl1;
 +  sqlite> .exit
 +  $ cat test_file_1.txt
 +</​code>​
 +In Bashish:
 +
 +<​code>​
 + echo -e '.mode list \n.separator ;\n.header on \n.output muh.txt\n select * from aggregation;​ \n' | sqlite3 ./my.db
 +</​code>​
 +So if you have lots of sqlite-files to export to csv, you might want to  do
 +
 +<​code>​
 + 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
 +</​code>​
 +===== Export a DB =====
 +A good way to make an archival copy of a database is this:
 +
 +<​code>​
 +  $ echo '​.dump'​ | sqlite3 ex1 | gzip -c >​ex1.dump.gz
 +</​code>​
 +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:
 +
 +<​code>​
 +  $ zcat ex1.dump.gz | sqlite3 ex2
 +</​code>​
 +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:
 +
 +<​code>​
 +    $ createdb ex2
 +    $ sqlite3 ex1 .dump | psql ex2
 +</​code>​
 +===== 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 ====
 +<​code>​
 +  SELECT trip_id, strftime("​%H",​timestamp) as hour from trips;
 +</​code>​
 +==== Select week day ====
 +Weekdays
 +
 +<​code>​
 + ​SELECT * FROM lm_onroad WHERE  strftime('​%w',​datetime(timestamp)) in ('​6','​0'​)
 +</​code>​
 +Weekends
 +
 +<​code>​
 + ​SELECT * FROM lm_onroad WHERE  strftime('​%w',​datetime(timestamp)) in ('​1','​2','​3','​4','​5'​)
 +</​code>​
 +==== Subtract Timestamps ====
 +To subtract one timestamp from the other, you have to use strftime to  convert both timestamps to seconds. For Example:
 +
 +<​code>​
 +  SELECT trip_id, start_time,​end_time,​STRFTIME('​%s',​end_time) - STRFTIME('​%s',​start_time) FROM delay; # use this only for dates after 1970!
 +</​code>​
 +===== 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:
 +
 +<​code>​
 +/**
 +  * 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;
 +}
 +</​code>​
 +===== Explore the Database =====
 +SQLite supports two (rather primitive) commands to explore the database:
 +
 +<​code>​
 +  .tables ​  #​shows the names of all tables in the DB
 +  .schema
 +</​code>​
 +==== 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:
 +
 +<​code>​
 +  select * from sqlite_master;​
 +</​code>​
 +This will get you the names of all the tables, and the corresponding ​ CREATE statements
 +
 +===== Spatialite =====
 +==== Install ====
 +<​code>​
 +sudo apt-get install python-pysqlite2 spatialite-bin
 +</​code>​
 +
 +To prepare a database for spatial data:
 +
 +<​code>​
 +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
 +</​code>​
 +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:​
 +
 +<​code>​
 +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);​
 +</​code>​
 +
 +==== Usage ====
 +Here'​s ​ the  [[[http://​www.gaia-gis.it/​spatialite/​spatialite-sql-2.3.1.html|function ​ reference]]].
 +
 +Loading the spatialite module:
 +<code python>
 +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)
 +</​code>​
 +
 +
 +====== 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]]]
  
sqlite.txt ยท Last modified: 2012/07/09 18:13 by mantis