introduction to sqlite: the most popular database in the world
Post on 08-Jul-2015
492 Views
Preview:
DESCRIPTION
TRANSCRIPT
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
top related