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.
As of 2015, there are two major versions: 2 and 3.
On Ubuntu 15.10, you need to specify the executable name:
is version 2, and does not open version 3 databases properly:
It is likely a good idea to use
sqlite3 always for new applications.
Create a database
Now as soon as you create a table the db file is created:
CREATE TABLE t (i INTEGER);
Run command on a given database:
sqlite3 db.sqlite3 'select * from table;'
Create multiple databases
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
Schema of databases attached with attach
The initial database is always called
ATTACH DATABASE can be given arbitrary names:
Export database data.
Export to CSV
List databases and their paths.
Get the schema of all tables with their creation query:
Import CSV file
printf '1,2\n3,4\n' > a.cvs
CREATE TABLE t (i INTEGER, j INTEGER) .mode csv table_name .import a.cvs table_name
Apparently does not deal with quotes.
Only available as extensions
Types are more flexible than MySQL: they are treated more like suggestions, and can increase in size “dynamically”.
Written in C.
Version controlled with Fossil… which uses SQLite on the backend, and was written by the same person who wrote SQLite.
He is openly anti-git: https://www.youtube.com/watch?v=ghtpJnrdgbo
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.
The format is documented and fixed.
It compiles the text input into a bytecode, and then runs it.
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: