Differences

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

--- 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:
+. Create a new in-memory database (IMDB)
+. Open the on-disk database (ODDB) and attach it to IMDB
+. Copy table definitions from ODDB to IMDB
+. Copy data from ODDB to IMDB
+. Copy other definitions like indices from ODDB to IMDB
+. 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]]]

Small heaps of code

User Tools

Site Tools

Differences

Page Tools