Internals

Why Do You Care?

● So you can better understand what your query
is doing.
● So that you can appreciate how much work the
database is saving you.
● So that you can fix things when they go wrong.
● So that you can write better SQL that runs
faster and uses less memory and disk space.
 About These Notes
● Nothing in these notes is required
– to write applications that use SQL and/or
SQLite
– to query an SQL and/or SQLite database
– to maintain or enhance SQL and/or SQLite-
based software
● Everything in these notes is required in order to
be an SQL and/or SQLite guru.
 Key Concept
● SQL is a peculiar programming language
– Each SQL statement is a separate program
– SQL describes what instead of how
● An RDBMS consists of...
– Compiler to translate SQL into procedures
– Virtual Machine to evaluate the procedures
 Example

SELECT * FROM table1;

Translates into:

Open database file containing table1
Rewind the file
while not at end­of­file
  read all columns out of current record
  return the columns to the caller
  advance file to the next record
end­while
close the file
Imagine what this translates into:
SELECT eqptid, enclosureid 
  FROM eqpt
 WHERE typeid IN (
   SELECT typeid FROM typespec
    WHERE attrid=(
      SELECT attrid FROM attribute
       WHERE name='detect_autoactuate'
    )
    AND value=1
   INTERSECT
   SELECT typeid FROM typespec
    WHERE attrid=(
      SELECT attrid FROM attribute
       WHERE name='algorithm'
    )
    AND value IN ('sensor','wetbulb')
 )
 Or This....
SELECT h.url, h.title, f.url, 
       (SELECT b.parent
          FROM moz_bookmarks b
               JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1
         WHERE b.type = 1 AND b.fk = h.id
         ORDER BY b.lastModified DESC LIMIT 1) AS parent,
       (SELECT b.title
          FROM moz_bookmarks b
               JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1
         WHERE b.type = 1 AND b.fk = h.id
         ORDER BY b.lastModified DESC LIMIT 1) AS bookmark,
       (SELECT GROUP_CONCAT(t.title, ',')
          FROM moz_bookmarks b
               JOIN moz_bookmarks t ON t.id = b.parent AND t.parent = ?1
         WHERE b.type = 1 AND b.fk = h.id) AS tags,
       h.visit_count, h.typed, h.frecency
  FROM moz_places_temp h
       LEFT OUTER JOIN moz_favicons f ON f.id = h.favicon_id
 WHERE h.frecency <> 0
UNION ALL
 SELECT h.url, h.title, f.url,
       (SELECT b.parent
          FROM moz_bookmarks b
               JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1
         WHERE b.type = 1 AND b.fk = h.id
         ORDER BY b.lastModified DESC LIMIT 1) AS parent,
       (SELECT b.title
          FROM moz_bookmarks b
               JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1
         WHERE b.type = 1 AND b.fk = h.id
         ORDER BY b.lastModified DESC LIMIT 1) AS bookmark,
       (SELECT GROUP_CONCAT(t.title, ',')
          FROM moz_bookmarks b
               JOIN moz_bookmarks t ON t.id = b.parent AND t.parent = ?1
         WHERE b.type = 1 AND b.fk = h.id) AS tags,
       h.visit_count, h.typed, h.frecency
  FROM moz_places h
       LEFT OUTER JOIN moz_favicons f ON f.id = h.favicon_id
 WHERE h.id NOT IN (SELECT id FROM moz_places_temp)
   AND h.frecency <> 0
ORDER BY 9 DESC
 The Whole Point Of SQL...
● A few lines of SQL generates the equivalent of
hundreds or thousands of lines of procedural
code.
● By adding an index, entirely new procedures
are used without recoding.
● The SQL Query Optimizer is tasked with picking
the algorithm
– so that you, the developer, don't have to
 Ins and Outs of SQL

Compile SQL Prep'ed Run the

SQL into a program Stmt program Result

Front Half Back Half

 Ins and Outs of SQL

Compile SQL Prep'ed Run the

SQL into a program Stmt program Result

See Aho & Ullman Virtual Machine

● Byte code
The “Dragon Book” ● Tree of structures

● Native machine code

 Ins and Outs of SQL

Compile SQL Prep'ed Run the

SQL into a program Stmt program Result

sqlite3_prepare_v2() sqlite3_stmt object sqlite3_step()

 Ins and Outs of SQL

Compile SQL Prep'ed Run the

SQL into a program Stmt program Result

sqlite3_prepare_v2() sqlite3_step()

sqlite3_exec(){
sqlite3_prepare_v2();
while( sqlite3_step()!=DONE ){};
sqlite3_finalize();
}
Architecture Of SQLite
Parser

Code Generator

Virtual Machine

B-Tree

Pager

OS Interface
Architecture Of SQLite
Parser

Code Generator

Virtual Machine

B-Tree

Pager

OS Interface
 Compile SQL Prep'ed Run the
SQL into a program Stmt program Result

Parser

Code Generator

Virtual Machine

B-Tree

Pager

OS Interface
 Compile SQL Prep'ed Run the
SQL into a program Stmt program Result

Parser

Code Generator

Virtual Machine

B-Tree

Pager

OS Interface
 Compile SQL Prep'ed Run the
SQL into a program Stmt program Result

Parser

Code Generator

Virtual Machine

B-Tree
● “Storage Engine”
● Fast, transactional, Pager

ordered, key/value OS Interface

store
 Architecture Of SQLite
● Byte code interpreter Parser

● Big switch Code Generator

statement inside a
Virtual Machine
for loop.
B-Tree
● Other engines walk a
tree of structures Pager

● Similar to JVM or OS Interface

Parrot or P-Code
 EXPLAIN SELECT price FROM tab WHERE fruit='Orange'

addr  opcode         p1    p2    p3    p4             p5  comment      
­­­­  ­­­­­­­­­­­­­  ­­­­  ­­­­  ­­­­  ­­­­­­­­­­­­­  ­­  ­­­­­­­­­­­­­
0     Init           0     12    0                    00  Start at 12  
1     OpenRead       0     2     0     3              00  root=2 iDb=0; tab
2     Explain        0     0     0     SCAN TABLE tab 00               
3     Rewind         0     10    0                    00               
4       Column         0     0     1                    00  r[1]=tab.Fruit
5       Ne             2     9     1     (BINARY)       69  if r[2]!=r[1] goto 9
6       Column         0     2     3                    00  r[3]=tab.Price
7       RealAffinity   3     0     0                    00               
8       ResultRow      3     1     0                    00  output=r[3]  
9     Next           0     4     0                    01               
10    Close          0     0     0                    00               
11    Halt           0     0     0                    00               
12    Transaction    0     0     1     0              01               
13    TableLock      0     2     0     tab            00  iDb=0 root=2 write=0
14    String8        0     2     0     Orange         00  r[2]='Orange'
15    Goto           0     1     0                    00      
 EXPLAIN SELECT price FROM tab WHERE fruit=?1

addr  opcode         p1    p2    p3    p4             p5  comment      
­­­­  ­­­­­­­­­­­­­  ­­­­  ­­­­  ­­­­  ­­­­­­­­­­­­­  ­­  ­­­­­­­­­­­­­
0     Init           0     12    0                    00  Start at 12  
1     OpenRead       0     2     0     3              00  root=2 iDb=0; tab
2     Explain        0     0     0     SCAN TABLE tab 00               
3     Rewind         0     10    0                    00               
4       Column         0     0     1                    00  r[1]=tab.Fruit
5       Ne             2     9     1     (BINARY)       69  if r[2]!=r[1] goto 9
6       Column         0     2     3                    00  r[3]=tab.Price
7       RealAffinity   3     0     0                    00               
8       ResultRow      3     1     0                    00  output=r[3]  
9     Next           0     4     0                    01               
10    Close          0     0     0                    00               
11    Halt           0     0     0                    00               
12    Transaction    0     0     1     0              01               
13    TableLock      0     2     0     tab            00  iDb=0 root=2 write=0
14    Variable       1     2     0     ?1             00  r[2]=parameter(1,?1)
15    Goto           0     1     0                    00      
 Architecture Of SQLite
● Ordered key/value Parser
pairs with unique
Code Generator
keys
Virtual Machine
● O(logN) insert, seek,
and delete B-Tree

● O(1) next and Pager

previous
OS Interface
Architecture Of SQLite
Parser

Code Generator

Virtual Machine

B-Tree

Pager

OS Interface
 Architecture Of SQLite
● Atomic commit and Parser
rollback
Code Generator
● Uniform size pages
Virtual Machine
numbered from 1
● No interpretation of B-Tree

page content Pager

● Cache OS Interface
 Architecture Of SQLite
● Platform-specific Parser
interface to the OS
Code Generator
● Run-time changeable
Virtual Machine
● Portability layer
B-Tree

Pager

OS Interface
 Multiplex Shim
● Inserted in between Parser

Pager and OS
Code Generator
Interface
Virtual Machine
● Looks like one big file
to the Pager B-Tree

● Actually multiple Pager

smaller files on disk
Multiplex Shim

OS Interface
 ZIPVFS Shim
● Compresses and Parser
encryptions outbound
Code Generator
● Decrypts and
Virtual Machine
decompresses
inbound B-Tree

Pager

ZIPVFS Shim

Multiplex Shim

OS Interface
Logical View of SQL Table Storage

64bit integer key Arbitrary length data in

“rowid” “record” format
Variable Length Integers
1xxxxxxx - high bit set. 7 bits of data

0xxxxxxx - high bit clear. 7 bits of data

xxxxxxxx - 8 bits of data

0 to 127
128 to 16383
16384 to 2097151
2097152 to 268435455
268435456 to 34359738367
34359738368 to 4398046511103
4398046511104 to 562949953421311
562949953421312 to 72057594037927935
Less than 0 or greater than 72057594037927935

✔ Small positive ROWIDs stored more efficiently

B+tree Structure
(used by SQL tables)

Root page

Integer key

Pointer to lower page

Leaf pages
Binary content
 B+tree Structure
(used by SQL tables)

Some keys appear more than Non-leaf pages hold only keys
once in the tree.
Between 50 and 8000
keys/page depending
on page size.

Integer key

Pointer to lower page

● Key + Data in leaves
● Combined key+data is a “cell”
Binary content
● As few as one “cell” per page.
Mapping B-trees Into Pages

Page 1

Page 2

Page 3

Page 4

Page 5

Page 6

Page 7
 To see how pages are used:
● make showdb
● ./showdb database.db pgidx
Available pages: 1..1146
    1: root leaf of table [sqlite_master]
    2: root interior node of table [blob]
    3: root interior node of index [sqlite_autoindex_blob_1]
    4: root interior node of table [delta]
    5: root interior node of table [rcvfrom]
    6: root leaf of index [sqlite_autoindex_rcvfrom_1]
    7: root leaf of table [config]
    8: root leaf of index [sqlite_autoindex_config_1]
    9: root leaf of table [shun]
   10: root leaf of index [sqlite_autoindex_shun_1]
   11: root leaf of table [private]
...
  264: leaf of table [blob], child 201 of page 2
  265: leaf of table [blob], child 202 of page 2
  266: overflow 1 from cell 0 of page 268
  267: overflow 2 from cell 0 of page 268
  268: leaf of table [blob], child 203 of page 2
...
Overflow

Integer key

Pointer to another page

Binary content
 Implications Of Overflow
● Move small and
common fields near
the beginning.
● Put larger and
infrequently accessed
fields at the end.
● Move large BLOBs to
a separate table and
access via join.
 Surprising Attributes of Overflow
● Multi-megabyte
BLOBs and strings
work well.
● Faster to store
BLOBs in the
database than directly
on disk for sizes up to
15-150K.
sqlite.org/intern-v-extern-blob.html
Logical View of SQL Index Storage

Arbitrary length key in Zero data

“record” format
 B-tree Structure
(used by indexes & WITHOUT ROWID tables)

● Key only. No data. The key is the data.

● Larger binary keys, hence lower fan-out
● Each key appears in the table only once
Binary key

Pointer to lower page

 sqlite_master
CREATE TABLE sqlite_master(
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);

✔ sqlite_master always rooted at page 1

 sqlite_master
sqlite> CREATE TABLE t1(x);
sqlite> .mode line
sqlite> SELECT * FROM sqlite_master;
type = table
name = t1
tbl_name = t1
rootpage = 2
sql = CREATE TABLE t1(x)
 Virtual Machine
● Byte code interpreter Parser

● Defines the “record Code Generator

format”
Virtual Machine

Record Format
B-Tree

Pager

OS Interface

Content of tables Key of indices

 Record Format
Header Data

header type type type data data data

... ...
size 1 2 N 1 2 N

Variable length integers type-dependent content

 Integer Type Codes

Type Meaning Data Length

0 NULL 0
1 signed integer 1
2 signed integer 2
3 signed integer 3
4 signed integer 4
5 signed integer 6
6 signed integer 8
7 IEEE float 8
8 integer zero 0
9 integer one 0
10,11 not used
N>=12 and even BLOB (N-12)/2
N>=13 and odd string (N-13)/2
 Record Format Example
  CREATE TABLE t1(a,b,c);
  INSERT INTO t1 VALUES(177, NULL, 'hello');
mac02:bld drh$ sqlite3 test.db
SQLite version 3.8.5 2014­06­24 20:19:21
Enter ".help" for usage hints.
sqlite> CREATE TABLE t1(a,b,c);
sqlite> INSERT INTO t1 VALUES(177, NULL, 'hello');
sqlite> .q
mac02:bld drh$ showdb test.db 2bd
Pagesize: 1024
Available pages: 1..2
Header on btree page 2:
 000: 0d                   13  table leaf
 001: 00 00                 0  Offset to first freeblock
 003: 00 01                 1  Number of cells on this page
 005: 03 f3              1011  Offset to cell content area
 007: 00                    0  Fragmented byte count
Cell[0]:
 3f3: 0b                         payload­size: 11
 3f4: 01                         rowid: 1
 3f5: 04                         record­header­size: 4
 3f6: 02                         typecode[0]: 2 ­ int16
 3f7: 00                         typecode[1]: 0 ­ NULL
 3f8: 17                         typecode[2]: 23 ­ text(5)
 3f9: 00 b1                      data[0]: 177
 3fb: 68 65 6c 6c 6f             data[2]: 'hello'
mac02:bld drh$ 
CREATE TABLE tab(
Fruit TEXT,
State TEXT,
Price REAL
);

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05

Key Data
SELECT price FROM tab WHERE fruit='Peach'

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
SELECT price FROM tab WHERE rowid=4

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
CREATE INDEX idx1 ON tab(fruit)

1:1

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05

Key Key Data

SELECT price FROM tab WHERE fruit='Peach'

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05
SELECT price FROM tab
WHERE fruit='Orange'

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05
SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05
CREATE INDEX idx2 ON tab(state)

idx2 tab
state rowid rowid fruit state price
CA 5 1 Orange FL 0.85
CA 23 2 Apple NC 0.45
FL 1 4 Peach SC 0.60
FL 18 5 Grape CA 0.80
NC 2 18 Lemon FL 1.25
NC 19 19 Strawberry NC 2.45
SC 4 23 Orange CA 1.05

Key Key Data

SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

idx2 tab
state rowid rowid fruit state price
CA 5 1 Orange FL 0.85
CA 23 2 Apple NC 0.45
FL 1 4 Peach SC 0.60
FL 18 5 Grape CA 0.80
NC 2 18 Lemon FL 1.25
NC 19 19 Strawberry NC 2.45
SC 4 23 Orange CA 1.05
CREATE INDEX idx3 ON tab(fruit, state)

idx3 tab
fruit state rowid rowid fruit state price
Apple NC 2 1 Orange FL 0.85
Grape CA 5 2 Apple NC 0.45
Lemon FL 18 4 Peach SC 0.60
Orange CA 23 5 Grape CA 0.80
Orange FL 1 18 Lemon FL 1.25
Peach SC 4 19 Strawberry NC 2.45
Strawberry NC 19 23 Orange CA 1.05

Key Key Data

CREATE INDEX idx3 ON tab(fruit, state)

fruit state rowid rowid fruit state price

Apple NC 2 1 Orange FL 0.85
Grape CA 5 2 Apple NC 0.45
Lemon FL 18 4 Peach SC 0.60
Orange CA 23 5 Grape CA 0.80
Orange FL 1 18 Lemon FL 1.25
Peach SC 4 19 Strawberry NC 2.45
Strawberry NC 19 23 Orange CA 1.05
CREATE INDEX idx3 ON tab(fruit, state)

fruit state rowid rowid fruit state price

Apple NC 2 1 Orange FL 0.85
Grape CA 5 2 Apple NC 0.45
Lemon FL 18 4 Peach SC 0.60
Orange CA 23 5 Grape CA 0.80
Orange FL 1 18 Lemon FL 1.25
Peach SC 4 19 Strawberry NC 2.45
Strawberry NC 19 23 Orange CA 1.05
SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

fruit state rowid rowid fruit state price

Apple NC 2 1 Orange FL 0.85
Grape CA 5 2 Apple NC 0.45
Lemon FL 18 4 Peach SC 0.60
Orange CA 23 5 Grape CA 0.80
Orange FL 1 18 Lemon FL 1.25
Peach SC 4 19 Strawberry NC 2.45
Strawberry NC 19 23 Orange CA 1.05
 CREATE INDEX idx5 ON tab(fruit, state, price)

idx5 tab
fruit state price rowid rowid fruit state price
Apple NC 0.45 2 1 Orange FL 0.85
Grape CA 0.80 5 2 Apple NC 0.45
Lemon FL 1.25 18 4 Peach SC 0.60
Orange CA 1.05 23 5 Grape CA 0.80
Orange FL 0.85 1 18 Lemon FL 1.25
Peach SC 0.60 4 19 Strawberry NC 2.45
Strawberry NC 2.45 19 23 Orange CA 1.05

Key Key Data

 SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19
 1
SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
 2
SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05
 3
SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

idx2 tab
state rowid rowid fruit state price
CA 5 1 Orange FL 0.85
CA 23 2 Apple NC 0.45
FL 1 4 Peach SC 0.60
FL 18 5 Grape CA 0.80
NC 2 18 Lemon FL 1.25
NC 19 19 Strawberry NC 2.45
SC 4 23 Orange CA 1.05
 4
SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

fruit state rowid rowid fruit state price

Apple NC 2 1 Orange FL 0.85
Grape CA 5 2 Apple NC 0.45
Lemon FL 18 4 Peach SC 0.60
Orange CA 23 5 Grape CA 0.80
Orange FL 1 18 Lemon FL 1.25
Peach SC 4 19 Strawberry NC 2.45
Strawberry NC 19 23 Orange CA 1.05
 5
SELECT price FROM tab
WHERE fruit='Orange'
AND state='CA'

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19
SELECT price FROM tab
WHERE fruit='Orange'
OR state='CA'
SELECT price FROM tab
WHERE fruit='Orange'
OR state='CA'

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
 6
SELECT price FROM tab
WHERE fruit='Orange'
OR state='CA'

fruit rowid
Apple 2
Grape 5
Lemon 18
Orange 1 rowid fruit state price
Orange 23 1 Orange FL 0.85
Peach 4 2 Apple NC 0.45
Strawberry 19 4 Peach SC 0.60
UNION

5 Grape CA 0.80
18 Lemon FL 1.25
state rowid 19 Strawberry NC 2.45
CA 5 23 Orange CA 1.05
CA 23
FL 1
FL 18
NC 2
NC 19
SC 4
CREATE INDEX idx4 ON tab(state,fruit)

idx4 tab
state fruit rowid rowid fruit state price
CA Grape 5 1 Orange FL 0.85
CA Orange 23 2 Apple NC 0.45
FL Orange 1 4 Peach SC 0.60
FL Lemon 18 5 Grape CA 0.80
NC Apple 2 18 Lemon FL 1.25
NC Strawberry 19 19 Strawberry NC 2.45
SC Peach 4 23 Orange CA 1.05

Key Key Data

 7
SELECT price FROM tab
WHERE fruit='Orange'

idx4 tab
state fruit rowid rowid fruit state price
CA Grape 5 1 Orange FL 0.85
CA Orange 23 2 Apple NC 0.45
FL Orange 1 4 Peach SC 0.60
FL Lemon 18 5 Grape CA 0.80
NC Apple 2 18 Lemon FL 1.25
NC Strawberry 19 19 Strawberry NC 2.45
SC Peach 4 23 Orange CA 1.05
SELECT * FROM tab ORDER BY fruit

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60

sorter
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
SELECT * FROM tab ORDER BY rowid

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
SELECT * FROM tab ORDER BY rowid DESC

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
 SELECT * FROM tab ORDER BY fruit

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05
 SELECT * FROM tab ORDER BY fruit LIMIT 2

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05
SELECT * FROM tab ORDER BY fruit LIMIT 2

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60

sorter
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
 SELECT * FROM tab ORDER BY fruit DESC

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05
 SELECT * FROM tab ORDER BY fruit

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19
 SELECT * FROM tab ORDER BY fruit, state

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19
 SELECT * FROM tab ORDER BY fruit DESC, state DESC

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19
SELECT * FROM tab ORDER BY fruit, state DESC

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60

sorter
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05

Version < 3.8.5

 SELECT * FROM tab ORDER BY fruit, state DESC

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19

Version >= 3.8.5

 SELECT * FROM tab ORDER BY fruit, price

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19

Version >= 3.8.5

 SELECT * FROM tab
WHERE fruit='Orange'
ORDER BY state

fruit state price rowid

Apple NC 0.45 2
Grape CA 0.80 5
Lemon FL 1.25 18
Orange CA 1.05 23
Orange FL 0.85 1
Peach SC 0.60 4
Strawberry NC 2.45 19
CREATE TABLE tab(
Fruit TEXT,
State TEXT,
Price REAL
);

rowid fruit state price

1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
CREATE TABLE tab(
Rowid INTEGER PRIMARY KEY,
Fruit TEXT,
State TEXT,
Price REAL
);
rowid fruit state price
1 Orange FL 0.85
2 Apple NC 0.45
4 Peach SC 0.60
5 Grape CA 0.80
18 Lemon FL 1.25
19 Strawberry NC 2.45
23 Orange CA 1.05
CREATE TABLE tab(
Fruit TEXT,
State TEXT,
Price REAL,
PRIMARY KEY(Fruit, State)
);
fruit state rowid rowid fruit state price
Apple NC 2 1 Orange FL 0.85
Grape CA 5 2 Apple NC 0.45
Lemon FL 18 4 Peach SC 0.60
Orange CA 23 5 Grape CA 0.80
Orange FL 1 18 Lemon FL 1.25
Peach SC 4 19 Strawberry NC 2.45
Strawberry NC 19 23 Orange CA 1.05

Key Key Data

CREATE TABLE tab(
Fruit TEXT,
State TEXT,
Price REAL,
PRIMARY KEY(Fruit, State)
);

CREATE TABLE tab(

Fruit TEXT, All mean the same
State TEXT, thing (to SQLite)
Price REAL,
UNIQUE(Fruit, State)
);

CREATE TABLE tab(

Fruit TEXT,
State TEXT,
Price REAL
);
CREATE UNIQUE INDEX idx ON tab(Fruit,State);
CREATE TABLE tab(
Fruit TEXT,
State TEXT,
Price REAL,
PRIMARY KEY(Fruit, State)
) WITHOUT ROWID;
fruit state price
Apple NC 0.45
Grape CA 0.80
Lemon FL 1.25
Orange CA 1.05
Orange FL 0.85
Peach SC 0.60
Strawberry NC 2.45

Key Data
 CREATE TABLE tab(
Fruit TEXT,
State TEXT,
Price REAL,
PRIMARY KEY(Fruit, State)
);
With Rowid:

fruit state rowid rowid fruit state price

Apple NC 2 1 Orange FL 0.85
Grape CA 5 2 Apple NC 0.45
Lemon FL 18 4 Peach SC 0.60
Orange CA 23 5 Grape CA 0.80
Orange FL 1 18 Lemon FL 1.25
Peach SC 4 19 Strawberry NC 2.45
Strawberry NC 19 23 Orange CA 1.05

Without Rowid:
fruit state price
Apple NC 0.45
Grape CA 0.80
Lemon FL 1.25
Orange CA 1.05
Orange FL 0.85
Peach SC 0.60
Strawberry NC 2.45
CREATE TABLE tab(
Fruit TEXT,
State TEXT,
Price REAL,
Amt INT,
PRIMARY KEY(Fruit, State)
) WITHOUT ROWID;
CREATE INDEX tab_amt ON tab(amt);

amt fruit state fruit state price amt

138 Peach SC Apple NC 0.45 1000
200 Grape CA Grape CA 0.80 200
375 Orange FL Lemon FL 1.25 750
750 Lemon FL Orange CA 1.05 825
825 Orange CA Orange FL 0.85 375
980 Strawberry NC Peach SC 0.60 138
1000 Apple NC Strawberry NC 2.45 980

Key Key Data

 Same As
CREATE TABLE tab(
Fruit TEXT, CREATE TABLE tab(
CREATE TABLE tab(
State TEXT, Price REAL,
Price REAL,
Price REAL, State TEXT,
State TEXT,
Amt INT, Fruit TEXT,
Fruit TEXT,
PRIMARY KEY(Fruit, State) Amt INT,
Amt INT,
PRIMARY KEY(Fruit, State)
) WITHOUT ROWID; PRIMARY KEY(Fruit, State)
) WITHOUT ROWID;
) WITHOUT ROWID;
CREATE INDEX tab_amt ON tab(amt);

amt fruit state fruit state price amt

138 Peach SC Apple NC 0.45 1000
200 Grape CA Grape CA 0.80 200
375 Orange FL Lemon FL 1.25 750
750 Lemon FL Orange CA 1.05 825
825 Orange CA Orange FL 0.85 375
980 Strawberry NC Peach SC 0.60 138
1000 Apple NC Strawberry NC 2.45 980

Key Key Data

SELECT price FROM tab WHERE fruit='Peach';

amt fruit state fruit state price amt

138 Peach SC Apple NC 0.45 1000
200 Grape CA Grape CA 0.80 200
375 Orange FL Lemon FL 1.25 750
750 Lemon FL Orange CA 1.05 825
825 Orange CA Orange FL 0.85 375
980 Strawberry NC Peach SC 0.60 138
1000 Apple NC Strawberry NC 2.45 980
SELECT price FROM tab WHERE fruit='Peach';

fruit rowid rowid fruit state price

Apple 2 1 Orange FL 0.85
Grape 5 2 Apple NC 0.45
Lemon 18 4 Peach SC 0.60
Orange 1 5 Grape CA 0.80
Orange 23 18 Lemon FL 1.25
Peach 4 19 Strawberry NC 2.45
Strawberry 19 23 Orange CA 1.05

amt fruit state fruit state price amt

138 Peach SC Apple NC 0.45 1000
200 Grape CA Grape CA 0.80 200
375 Orange FL Lemon FL 1.25 750
750 Lemon FL Orange CA 1.05 825
825 Orange CA Orange FL 0.85 375
980 Strawberry NC Peach SC 0.60 138
1000 Apple NC Strawberry NC 2.45 980
SELECT fruit, state FROM tab WHERE amt<250;

amt fruit state fruit state price amt

138 Peach SC Apple NC 0.45 1000
200 Grape CA Grape CA 0.80 200
375 Orange FL Lemon FL 1.25 750
750 Lemon FL Orange CA 1.05 825
825 Orange CA Orange FL 0.85 375
980 Strawberry NC Peach SC 0.60 138
1000 Apple NC Strawberry NC 2.45 980
SELECT fruit, state, price FROM tab WHERE amt<250;

amt fruit state fruit state price amt

138 Peach SC Apple NC 0.45 1000
200 Grape CA Grape CA 0.80 200
375 Orange FL Lemon FL 1.25 750
750 Lemon FL Orange CA 1.05 825
825 Orange CA Orange FL 0.85 375
980 Strawberry NC Peach SC 0.60 138
1000 Apple NC Strawberry NC 2.45 980
 When To Use WITHOUT ROWID
● Non-INTEGER and/or composite PRIMARY
KEYs
● No need for AUTOINCREMENT, Direct BLOB
I/O, or the update hook
● Average row size less than 1/20th of page size
– Check this using sqlite3_analyzer.exe
● Run experiments to see if it helps
 Index Column Order
SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y;

CREATE INDEX t1i1 ON t1(w,x,y,z);

 Index Column Order
SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y;

WHERE clause equality terms first

CREATE INDEX t1i1 ON t1(w,x,y,z);

 Index Column Order
SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y;

ORDER BY terms second.

No gaps!
Can overlap WHERE clause.

CREATE INDEX t1i1 ON t1(w,x,y,z);

 Index Column Order
SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y;

Result set columns can appear

anywhere and in any order.

CREATE INDEX t1i1 ON t1(w,x,y,z);

 Index Column Order
SELECT * FROM t1 WHERE w=5 AND x>=6 AND y=23;

Equality terms first

Range constraints at the end

CREATE INDEX t1i1 ON t1(w,x,y,z);

Terms past the range constraint are

not usable for search.
Avoid Redundant Indices

CREATE TABLE t2(x,y,z);

CREATE INDEX t2i1 ON t2(x,y,z);
CREATE INDEX t2i2 ON t2(x,y);
CREATE INDEX t2i3 ON t2(x);
CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER, Find
  aname TEXT Findthe
thename
nameofofevery
everyalbum
album
that contains a track where
that contains a track where
);
the
thecomposer
composerisis“Bach”.
“Bach”.

for each row in composer:

0 0 TABLE composer
1 1 TABLE track if( !like(cname,'%bach%') ) continue;
2 2 TABLE album for each row in track:
if( track.cid!=composer.cid ) continue;
for each row in album:
if( album.aid!=track.aid ) continue;
output one row of result;;
endfor
endfor
endfor
CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER,
  aname TEXT
);

for each row in track:

0 1 TABLE track
1 2 TABLE album for each row in album:
2 0 TABLE composer if( track.aid!=album.aid ) continue;
for each row in composer:
if( track.cid!=composer.cid ) continue;
if( !like(cname,'%bach%') ) continue;
output one row of result;;
endfor
endfor
endfor
CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER,
  aname TEXT
);

for each row in album:

0 2 TABLE album
1 1 TABLE track for each row in track:
2 0 TABLE composer if( track.aid!=album.aid ) continue;
for each row in composer:
if( track.cid!=composer.cid ) continue;
if( !like(cname,'%bach%') ) continue;
output one row of result;;
endfor
endfor
endfor
 CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER,
  aname TEXT
);

create transient index on track(cid);

0 0 TABLE composer
1 1 TABLE track WITH AUTOMATIC INDEX create transient index on album(aid);
2 2 TABLE album WITH AUTOMATIC INDEX for each row in composer:
if( !like(cname,'%bach%') ) continue;
lookup rows in track with cid=composer.cid:
lookup rows in album with aid=track.aid:
output one row of result;;
endfor
endfor
endfor
CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER PRIMARY KEY,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER PRIMARY KEY,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER PRIMARY KEY,
  aname TEXT
);

for each row in track:

0 1 TABLE track
1 0 TABLE composer USING PRIMARY KEY lookup rows in composer with cid=track.cid
2 2 TABLE album USING PRIMARY KEY if( !like(cname,'%bach%') ) continue;
lookup rows in album with aid=track.aid:
output one row of result;;
endfor
endfor
endfor
CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER PRIMARY KEY,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER PRIMARY KEY,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER PRIMARY KEY,
  aname TEXT
);

for each row in track:

0 1 TABLE track
1 2 TABLE album USING PRIMARY KEY lookup rows in album with aid=track.aid:
2 0 TABLE composer USING PRIMARY KEY lookup rows in composer with cid=track.cid
if( !like(cname,'%bach%') ) continue;
output one row of result;;
endfor
endfor
endfor
 CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER PRIMARY KEY,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER PRIMARY KEY,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER PRIMARY KEY,
  aname TEXT
);
CREATE INDEX track_i1 ON track(cid);

for each row in composer:

0 0 TABLE composer
1 1 TABLE track WITH INDEX track_i1 if( !like(cname,'%bach%') ) continue;
2 2 TABLE album USING PRIMARY KEY lookup rows in track with cid=composer.cid:
lookup rows in album with aid=track.aid:
output one row of result;;
endfor
endfor
endfor
CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER PRIMARY KEY,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE cname LIKE '%bach%'
  tid INTEGER PRIMARY KEY,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER PRIMARY KEY,
  aname TEXT
);
CREATE INDEX track_i1 ON track(cid);

for each row in track:

0 1 TABLE track
1 0 TABLE composer USING PRIMARY KEY lookup rows in composer with cid=track.cid
2 2 TABLE album USING PRIMARY KEY if( !like(cname,'%bach%') ) continue;
lookup rows in album with aid=track.aid:
output one row of result;;
endfor
endfor
endfor
 CREATE TABLE composer( EXPLAIN QUERY PLAN
  cid INTEGER PRIMARY KEY,
SELECT aname
  cname TEXT
);   FROM composer, track, album
CREATE TABLE track(  WHERE unlikely(cname LIKE '%bach%')
  tid INTEGER PRIMARY KEY,    AND composer.cid=track.cid
  cid INTEGER,    AND album.aid=track.aid
  aid INTEGER,
  title TEXT
);
CREATE TABLE album(
  aid INTEGER PRIMARY KEY,
  aname TEXT
);
CREATE INDEX track_i1 ON track(cid);

for each row in composer:

0 0 TABLE composer
1 1 TABLE track WITH INDEX track_id if( !like(cname,'%bach%') ) continue;
2 2 TABLE album USING PRIMARY KEY lookup rows in track with cid=composer.cid
lookup rows in album with aid=track.aid:
output one row of result;;
endfor
endfor
endfor
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
● Instrument your code
● Use unlikely() and likelihood() hints
● Use CROSS JOIN to force loop nesting order
● Disable WHERE terms using unary “+”
● INDEXED BY to force a particular index
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
If you have lots of:
If you have lots of:
       SELECT … FROM tab1 WHERE tab1.x=...
● Instrument your code
       SELECT … FROM tab1 WHERE tab1.x=...
Then consider adding:
● Use unlikely()
Then and
consider likelihood() hints
adding:
       CREATE INDEX tab1_x ON tab1(x);
       CREATE INDEX tab1_x ON tab1(x);
● Use CROSS JOIN to force loop nesting order
● Disable WHERE terms using unary “+”
● INDEXED BY to force a particular index
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
● Instrument your code
  “Low­quality” means:  More than 10 or 20
  “Low­quality” means:  More than 10 or 20
● Use  rows for each distinct value in the 
unlikely() and likelihood() hints
  rows for each distinct value in the 
  left­most column.
  left­most column.
● Use CROSS JOIN to force loop nesting order
  Avoid boolean and enumeration types in
  Avoid boolean and enumeration types in
● Disable WHERE terms using unary “+”
  the left­most columns of indexes.
  the left­most columns of indexes.
● INDEXED BY to force a particular index
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
● Instrument your code
● Use unlikely() and likelihood() hints
Measures the quality of indexes
Measures the quality of indexes
● Use CROSS JOIN to force loop nesting order
● Disable WHERE terms using unary “+”
● INDEXED BY to force a particular index
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
● Instrument your code
● Use unlikely() and likelihood() hints
● Use CROSS“Premature optimization is the
JOIN to force loop nesting
“Premature optimization is the
order
root of all evil.”  ­ Don Knuth
● Disable WHERE terms using unary
root of all evil.”   “+”
­ Don Knuth

● INDEXED BY to force a particular index

 Performance Monitoring
● sqlite3_status()
– Global memory usage by category
● sqlite3_db_status()
– Per connection memory usage
– Cache performance
● sqlite3_stmt_status()
– Full table scan steps
– VM steps
– Sorts not using an index
– Transient indices created
● sqlite3_trace()
Avoiding & Fixing Planner Problems
● Create appropriate indexes
… WHERE unlikely(name LIKE 'bach%')
● Avoid low-quality indexes
… WHERE unlikely(name LIKE 'bach%')
… WHERE likelihood(name LIKE 'bach%', 0.95)
… WHERE likelihood(name LIKE 'bach%', 0.95)
● Run ANALYZE
● Instrument your code
● Use unlikely() and likelihood() hints
● Use CROSS JOIN to force loop nesting order
● Disable WHERE terms using unary “+”
● INDEXED BY to force a particular index
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
… FROM composer CROSS JOIN track, album … 
… FROM composer CROSS JOIN track, album … 
● Instrument your code
● Use unlikely() and likelihood() hints
● Use CROSS JOIN to force loop nesting order
● Disable WHERE terms using unary “+”
● INDEXED BY to force a particular index
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
… WHERE +x=25 AND y=49
… WHERE +x=25 AND y=49
● Instrument your code
… WHERE x=25 AND +y=49
… WHERE x=25 AND +y=49
● Use unlikely() and likelihood() hints
● Use CROSS JOIN to force loop nesting order
● Disable WHERE terms using unary “+”
● INDEXED BY to force a particular index
Avoiding & Fixing Planner Problems
● Create appropriate indexes
● Avoid low-quality indexes
● Run ANALYZE
● Instrument your code
… FROM tab1 INDEXED BY tab1_x … 
… FROM tab1 INDEXED BY tab1_x … 
● Use unlikely() and likelihood() hints
● Use CROSS JOIN to force loop nesting order
● Disable WHERE terms using unary “+”
● INDEXED BY to force a particular index
 Query Plan Stability Guarantee

SQLite will always generate the same query

plan for the same SQL text provided:

1) The schema is not changed

2) You do not rerun ANALYZE
3) Omit SQLITE_ENABLE_STAT[34]
4) Keep the same version of SQLite