SQLite Cheat

File based.

Not intended for production servers, but can useful for testing and use in user applications.

This type of database is called an https://en.wikipedia.org/wiki/Embedded_database

One of the most deployed software in the world.

If you ever think something like: “hey, I’d like to store this hash map persistently to disk”, then you should try this.

Versions

As of 2015, there are two major versions: 2 and 3.

On Ubuntu 15.10, you need to specify the executable name:

sqlite


is version 2, and does not open version 3 databases properly:

sqlite3


It is likely a good idea to use sqlite3 always for new applications.

Create a database

sqlite3 my.db


Now as soon as you create a table the db file is created:

CREATE TABLE t (i INTEGER);


CLI interface

Run command on a given database:

sqlite3 db.sqlite3 'select * from table;'


Create multiple databases

sqlite3 a.db


Then:

ATTACH DATABASE 'b.db' as b;


b is the name that shows on .databases, and is used to refer to it:

SELECT * FROM b.t;


This creates the b.db file.

Database name

The initial database is always called main. ATTACH DATABASE can be given arbitrary names:

out

Export database data.

Export to CSV

http://stackoverflow.com/questions/75675/how-do-i-dump-the-data-of-some-sqlite3-tables

Data types

Integers

http://stackoverflow.com/questions/7337882/sqlite-and-integer-types-int-integer-bigint

Current time:

Dot commands

help

List special . commands:

.help


exit

.exit


databases

List databases and their paths.

.databases


tables

List tables:

.tables


schema

Get the schema of all tables with their creation query:

.schema


import

Import CSV file

printf '1,2\n3,4\n' > a.cvs


Then:

CREATE TABLE t (i INTEGER, j INTEGER)
.mode csv table_name
.import a.cvs table_name


Apparently does not deal with quotes.

FULLTEXT

Only available as extensions

Types

Types are more flexible than MySQL: they are treated more like suggestions, and can increase in size “dynamically”.

Internals

Written in C.

Source code

Version controlled with Fossil… which uses SQLite on the backend, and was written by the same person who wrote SQLite.

And on top of that, you need Fossil to generate some build files… for the Git repository this can be achieved with:

git log -1 --format=format:%ci%n | sed -e 's/ [-+].*$//;s/ /T/;s/^/D /' > manifest echo$(git log -1 --format=format:%H) > manifest.uuid
make


So here is the most starred GitHub unofficial mirror: https://github.com/mackyle/sqlite

Or you can create some courage and deal with Fossil:

sudo apt-get install fossil
mkdir sqlite
cd sqlite
fossil clone http://www.sqlite.org/cgi/src/doc/trunk a
fossil open a
rm a


To run the tests you need Tcl development files. In Ubuntu 14.04:

sudo apt-get install tcl8.6-dev


TODO: what then? tcl.h is not in the path, and adding it with CPATH gives a ton of link errors.

Public domain! WOW.

Format

The format is documented and fixed.

Bytecode

It compiles the text input into a bytecode, and then runs it.

Creator

Richard Hipp https://en.wikipedia.org/wiki/D._Richard_Hipp

He is a pious Christian it seems: https://twitter.com/drichardhipp

You gotta love his Southern accent.

Index on large database

Insertion becomes really slow, I could not deal with 10M rows: