An Introduction to

SQLiteThe Most Popular Database In the World

Jay Kreibichjay@kreibi.ch

cuDBug20 June 2013

My history with DBs

• Undergrad at UIUC, the Web 1.0 era

• Full time software developer for 17 years

• 8 years at CITES, including many network monitoring and management tools

• Almost 7 years at Volition, the last three years doing data collection and analysis

Relationship with DBs

• School: Databases are for COBOL programmers

• Early work: Databases are useful

• Later work: Database theory is amazing

My history with SQLite

• First learned about it at WWDC 2004

• Used for a number of work projects

• Tried to stuff Wikipedia dump onto an ebook reader

So I wrote a book...

So I wrote a book...

• Great White Heron

• No, I didn’t get to choose

• August 2010

• 2.5 years to write & edit

What is SQLite?

The power of a full RDBMS...

...without all the headaches.

What is SQLite?

• Database-in-a-file

• Self-Contained

• Serverless

• Zero-configuration

• Concurrent Access

• ACID compliant

• In the Public Domain

SQLite is an SQL based RDBMS that is...

Database-in-a-File

• Database instance is a common file

• File contains schema, data, blobs, etc.

• Files are cross-platform

• Endian

• Word size (32 bit & 64 bit)

• Encoding (UTF-8, UTF-16be, UTF-16le)

Self-Contained

• The database engine is compiled directly into the application

• No external libraries or processes

Serverless

Application Client lib ServerServerProcs

Typical Client/Server

SQLite

Application SQLite

Zero Configuration

• No server means no server admin, no DBA

• Application can self-configure DB

Concurrent Access

• SQLite uses file locking to allow safe concurrent access from multiple connections, threads, or processes

• Local processes only

• Network storage typically does not work

ACID Compliant

• SQLite provides full ACID transactions

• Two different transaction methods:

• Rollback Journal

• Write-Ahead Log

• Different levels of locking and access

Public Domain

• Core source code and algorithms arein the Public Domain

• Rigorously enforced

• “SQLite” name is still under trademark

• Causes more problems than it solves

SQLite Types

• Signed 64-bit Integers

• IEEE 754 64-bit Float Point

• Unicode Text

• BLOBs

• NULL

• (No date/time types; no “numeric” type)

Other Features

• Connection can “attach” multiple DBs

• SQLite uses manifest typing

• Column types are not rigidly enforced

• Very few issues

• DDL is transactional

Other Features

• Supports in-memory DBs (very fast)

• Depends on file access for Auth/Auth

• Right Outer Joins not supported (Left is)

• Triggers are supported

• Custom functions/aggs, but not in-DB

Other Features

• Virtual Tables

Virtual DB Engine

Query PlannerOptimizer

API

Virtual DB Engine

Query PlannerOptimizer

API

Application Code

Good Uses for SQLite• Database Junior

• Application Cache

• Archives and Data Stores

• Client/Server Stand-in

• Application File Format

• Teaching Tool

• Generic SQL Engine (external data source)

Bad Uses for SQLite

• High Transaction Rates

• Very Large Datasets

• Detailed Access Control

• Client/Server or Multi-Host

• Advanced Replication

SQLite Distribution

• Code for SQLite library

• Code for sqlite3 CLI application

Most popular?

SQLite is, most likely, themost popular database in the worldby number of database instances.

You use it all the time.

Most popular?

• Firefox, Safari, Chrome

• iTunes, MacOS X

• Thunderbird, Mail

• McAfee

• Adobe Air

• Dropbox

• Skype

• QuickBooks, TurboTax

• Python, PHP, WebSQL

Lots of Desktop Apps:

Most popular?

• 550M iOS devices

• 750M Android devices

• ~500M Symbian (Nokia) devices

• ~100M Blackberry devices

Lots of Mobile Devices:

Most popular?

~10,000,000,000 active database instances

Q & A

Jay Kreibichjay@kreibi.ch

@jkreibich

www.sqlite.org