1. Users and use-cases
1.1. Lots of things using it: core part of Windows 10, smartphones, browsers, Python, TVs, newer cars
1.1.1. Probably most deployed software in the world (1st is probably zlib - certainly in top 5)
1.1.2. 2B different installations in 2010
1.1.3. Airbus A350 lifetime support
1.2. Completes with fopen(): not MySQL etc!
1.3. Low volume web applications
1.3.1. sqlite.org
1.3.1.1. 400k hits/day
1.3.1.2. 80-85% static
1.3.1.3. ~200 SQL stmts/page
1.3.1.4. most pages < 100ms
1.3.1.5. 1/24th slice of a real server
1.3.1.6. 0.1 load avg
1.3.1.6.1. lots of room to go
1.3.2. switch to client-server when you need to run on multiple machines
1.4. Data analysis
1.4.1. Load up a one-off DB from CSV
1.4.2. Powerful SQL queries
1.4.3. Simple integration with analysis scripts
1.4.4. Collaborate via email attachment
1.4.5. Popular in bioinformatics
1.5. Data transfer format
1.5.1. TV guide (streaming over the satellite)
1.6. Over-the-air sync
1.6.1. Pull down parts of DB to device in SQLite
1.6.1.1. Continues to work even if telco link is down
1.7. Edge computing
2. Source
2.1. Public domain
2.2. 1 file: sqlite3.c
2.2.1. < 1MB compiled
2.2.2. Lots of machines supported
2.3. 3 developers
2.4. 100% branch test coverage: DO-178B standard
2.4.1. 22-23K branch instructions
2.4.2. 177M different tests
2.5. useful comments throughout code
2.5.1. for programmers who're not yet born
2.6. Building
2.6.1. Unix: ./configure; make TGT
2.6.2. Windows: nmake /f make.msc TGT
2.7. Fossil VCS
2.7.1. Uses SQLite on top of git
2.8. Periodically massive rewrites because of extensive test suite
2.9. "Fly what you test, test what you fly"
2.9.1. DO-178B aviation standard
2.9.2. Use only published, documented interfaces
2.9.3. Lot of time developing test infrastructure
3. When to use
3.1. Regular RDBMSes
3.1.1. Remote data?
3.1.2. Big data?
3.1.2.1. Too big to fit in a single file
3.1.3. Concurrent writers?
3.2. SQLite
3.2.1. Otherwise!
3.2.2. Not fopenI) and use a file!
3.2.2.1. Or a pile of files!
3.3. Meta: "DB engine as data filter"
3.3.1. DB engine needs to sit close to data
3.3.1.1. With SQLite, it's at the app, so network becomes a fat pipe
3.3.1.2. MySQL etc. work correctly instead (client-server)
4. More info
4.1. DBDB link
5. Features
5.1. Automatic crash recovery
5.1.1. Transactions even across reboots
5.2. Triggers
5.3. Foreign keys
5.4. Recursive common table expressions
5.5. Indexes
5.5.1. Full-text indexes
5.5.1.1. R-tree indexes for spatial data
5.5.2. Partial indexes (unlike MySQL
5.6. Cross-platform, cross-language
5.7. Higher performance
5.7.1. Faster to read files (no extra open())!
5.8. Automatic concurrency control
5.9. Simple C/C++ interface
5.9.1. prepare() is the compiler
5.10. Linked as library
5.10.1. Same address space as process
5.11. Incremental updating
5.12. Uses SQL
5.12.1. 2 powerful ideas
5.12.1.1. data abstraction
5.12.1.2. declarative language
5.12.1.2.1. ask the machine questions, rather than tell it what to do
6. Limits
6.1. 1 writer!
6.2. up to 1GB strings
6.3. up to 140TB DB
6.4. loosey-goosey data types (no datetime, bools treated like ints, varchar5 will take larger strings, short int can take bigger ints)
6.5. adding new data types will need file format change
6.5.1. goes against philosophy of keeping it future proof
7. Tools
7.1. sqlite3_analyzer
7.1.1. Which tables are taking space on disk?
7.2. Command-line tool (sqlite.exe)
7.2.1. . commands:
7.2.1.1. .tables, .schema, .import etc
7.3. sqldiff
7.3.1. Differences across databases
7.3.1.1. Example: Firefox cookies db before and after website
8. History
8.1. Written in 2000
8.1.1. Author wanted to read and write local data systematically
8.2. Database stored as file
8.2.1. Well-defined file format