Status Report for NDSeV

2015-07-20

User: wlessguest
Pw: Qu!ckj0urn3y
15 Years of SQLite

First check-in: 2000-05-29 14:26:00 UTC

 SQLite is Everywhere
● Android ● Dropbox
● iPhone and iOS ● TurboTax, QuickBooks
● Mac ● PHP, Python
● Firefox, Chrome, Safari ● TVs, set-top cable boxes
● Skype ● Automotive multimedia
● iTunes ● Countless millions of
other applications

✓ The most widely deployed and used database engine

✓ In the top five most deployed libraries of any type

 SQLite Size Trend
600000

500000

400000

300000

200000

100000

0
2007 2008 2009 2010 2011 2012 2013 2014 2015 2016

gcc 4.8.2 linux x64 -Os -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS

 SQLite CPU Usage
2500

2000
3.8.0: 1408M cycles

1500

1000

500

trunk: 692M cycles

0
2007 2008 2009 2010 2011 2012 2013 2014 2015 2016

Millions of CPU instructions required to run a standard workload.

Measured by cachegrind and gcc 4.8.2 with -Os on x64 Ubuntu 14.04
 SQLite Performance
2.5

1.5

0.5

0
2007 2008 2009 2010 2011 2012 2013 2014 2015 2016

Computation done per CPU cycle.

Normalized so that version 3.8.0 is 1.0
 SQLite CPU Usage
2500

2000

1500

1000

500

0
2007 2008 2009 2010 2011 2012 2013 2014 2015 2016

Millions of CPU instructions required to run a standard workload.

Measured by cachegrind and gcc 4.8.2 with -Os on x64 Ubuntu 14.04
 Events Since Version 3.8.0

3.8.8
3.8

.3
3.8
.2

Dec Jan Dec Jan Dec Jan

. 8. 4

ov
ov

Fe
3

ov
Fe

Fe
3.8.7
N

N
3.8.1
b

b
Sep Oct

Mar Apr
Sep Oct

Sep Oct
Mar Apr M

Mar Apr M
2013 2014 2015
eim
Hildesh 3.8.9
g

M
g

Au

g
Au

ay

Au
ay

3.

ay
Jul Jun
0 8.
Jul Jun Jun
8.
Jul
6 1
3. . 8. 0

11
3
3.8

.
sum
3.8
.
Berlin

Har
Events In Past Year

3.8.8
Jan
Dec

ov

Fe
N

b
3.8.7

Mar
O ct 2014
2015
Sep

Apr
3.8.9

M
g
Au

ay
3.
8.
Jul Jun
6 10
. 8.
3
.11
3.8
rs um
Ha
Page Cache Bulk Memory Allocation
sqlite3_config(SQLITE_CONFIG_PAGECACHE, NULL, 0, 0);

Each page stored in memory

Each page stored in memory
obtained from malloc(). Separate
obtained from sqlite3_malloc().
malloc() or each page.

sqlite3_config(SQLITE_CONFIG_PAGECACHE, pMem, sz, N);

Use pMem to store pages.

Failover to malloc() when pMem
is used up.
Page Cache Bulk Memory Allocation
Must be NULL/zero.

sqlite3_config(SQLITE_CONFIG_PAGECACHE, NULL, 0, N);

Single malloc() sufficient to store N

pages. Individual malloc() calls for
more.

sqlite3_config(SQLITE_CONFIG_PAGECACHE, NULL, 0, -N);

Single malloc() for N*1024 bytes.

Individual malloc() calls beyond that.
Page Cache Bulk Memory Allocation
● Old default: NULL, 0, 0 Subject to
change
● New default: NULL, 0, 100
● Change means:
– About 5% faster
– Uses more memory
 Profile Incomplete SQL Stmts
● sqlite3_profile() callback normally invoked by
sqlite3_step() when the statement completes.
● Now also invoked by sqlite3_reset() or
sqlite3_finalize() if the statement did not run
to completion.
 New Extensions
● ext/misc/fileio.c
– readfile(filename)
– writefile(filename,content)
● ext/misc/eval.c
– eval(sqltext)
● ext/ota
● ext/fts5
 automerge=N in FTS4
● 0 <= N <= 16
● 0 is the default
● 1 means 8
● Documentation search term: “automerge”
 sqlite3_rtree_query_callback()
● Successor to
sqlite3_rtree_geometry_callback().
● Provides additional control so that large and
near objects can be prioritized over far-away
objects that only cover a few pixels.
● Search term: “query callback”
URI params: nolock, immutable
● nolock=1  Disables all file locking
● immutable=1  Assume database is on
read-only media
● Search terms: “nolock query parameter”
 SQLITE_IOCAP_IMMUTABLE
● Bit returned by
sqlite3_io_methods.xDeviceCharacteristics()
● Underlying database file is on read-only
media
 Omit CHECK in read-only DBs
● Reduce memory consumption
● Might revert in the future if CHECK
constraints are used for query planning
 Hex literals in SQL

UPDATE tab
Yes SET flags = flags | 0x1b
WHERE id=?;

No SELECT CAST('0x5e' AS INT);

 PRAGMA integrity_check
● Faster
– PRAGMA quick_check faster too
● Detection of UNIQUE and NOT NULL
constraint errors
 SQLITE_MAX_ATTACHED
● Upper bound from 62 to 125
● Very difficult to go higher
 likely(X)
● Same as: likelihood(X,0.9375)
● See also: unlikely(X)
Auto reprepare after ANALYZE
 PRAGMA threads=N
● Faster sorting for N>1 and N<=number-of-
CPU-cores.
● Faster wall-clock time. More CPU cycles.
● Sort operations:
– ORDER BY and GROUP BY
– CREATE INDEX
– DISTINCT
 New C/C++ interfaces
● sqlite3_malloc64() ● sqlite3_status64()
● sqlite3_realloc64() ● sqlite3_msize()
● sqlite3_bind_blob64() ● sqlite3_stmt_scanstatus()
● sqlite3_result_blob64() ● sqlite3_value_dup()
● sqlite3_bind_text64() ● sqlite3_value_free()
● sqlite3_result_text64()
sqlite3_table_column_metadata()
● Compiled in by default
● Test for existence of a table
● Test for existence of a column within a table
 ROLLBACK during query
● Query continues running as long as there is
no schema change.
● Prior behavior: Query would abort.
 PRAGMA data_version
● Detect changes in database content caused
by a write on another database connection
– Writer can be in a separate process
PRAGMA wal_checkpoint=TRUNCATE
● SQLITE_CHECKPOINT_TRUNCATE
SQLITE_REVERSE_UNORDERED_SELECTS

● Makes PRAGMA reverse_unordered_selects

default to ON.
● Detect undefined behavior in SQL queries
 PRAGMA multiplex_truncate
● Query or set the truncate flag on the
multiplexor extension
 VALUES(),(),...,()
● Unlimited number of terms in a VALUES
clause.
● Formerly limited by
SQLITE_MAX_COMPOUND_SELECT
 PRAGMA index_xinfo
● Enhanced version of PRAGMA index_info.
● Needed by OTA
VxWorks
Fuzz testing with AFL
SQLITE_ENABLE_DBSTAT_VTAB

https://www.sqlite.org/src/repo-tabsize
 PRAGMA cell_size_check
● Earlier detection of database corruption
● Prevents potential read past end of buffer on
a corrupt database file
 matchinfo(tab,'by')
● 'y'  The number of matches for each term.
● 'b'  Bitvector with 1 for each matching
term.
SQLITE_CONFIG_PCACHE_HDRSZ
● Number of bytes of extra space needed by
each page-cache line.
– Value is compiler and architecture specific
● Useful in combination with
SQLITE_CONFIG_PAGECACHE.
 Unicode61 tokenizer
● Now included by default
INSERT by rowid in spellfix1
 Improved Documentation
● Each object includes a list of methods
 Partial Sorting By Index
CREATE TABLE t1(a,b,c);
CREATE INDEX t1a ON t1(a);

SELECT * FROM t1 ORDER BY a, b

● Use index to order by “a”

● For each group of rows with the same “a”
value, sort by “b”
 Partial Automatic Index
CREATE TABLE t1(a,b,c);
CREATE TABLE t2(x,y);
CREATE INDEX t2(x);

SELECT * FROM t1 JOIN t2 ON t1.c=t2.y AND t1.b=5

WHERE t2.x=99;

● Automatic index for t1(c)

● Index only covers terms where t1.b=5
 Omit superfluous DISTINCT

SELECT * FROM t1
WHERE x IN (SELECT DISTINCT b FROM t2 WHERE a>55)
IS operator can drive an index
Returns no rows if NULL, even if
one or more “x” values in the table
are NULL.

SELECT * FROM tab WHERE x = ?1;

SELECT * FROM tab WHERE x IS ?1;

Works even if bound to NULL

skip-scan enhancements
CREATE TABLE t1(a,b,c,d,e);
CREATE INDEX t1x1 ON t1(a,b,c,d);

SELECT * FROM t1
WHERE a=?1 AND c=?2
ORDER BY d;

SELECT * FROM t1
WHERE a=?1 AND c=?2
AND b IN (SELECT b FROM t1)
ORDER BY d;
OR optimized on WITHOUT ROWID
CREATE TABLE t3(
id TEXT PRIMARY KEY,
a TEXT,
b TEXT,
c BLOB
) WITHOUT ROWID;
CREATE INDEX t3a ON t3(a);
CREATE INDEX t3b ON t3(b);

SELECT * FROM t3
WHERE a='hello' OR b='world';
 sqldiff.exe
● Compare two SQLite database files
● --schema  Show only schema differences
● --summary  Summary (change counts)
● --table TAB  Table TAB only
sqlite3.exe: “.system” and “.once”

.header on
.mode csv
.once c:/work/dataout.csv
SELECT * FROM tab1;
.system c:/work/dataout.csv
sqlite3.exe: “.once”

.once “|open -f”

SELECT * FROM tab1;

.once “|open -f”

.help
 sqlite3.exe: “.binary ON/OFF”
● Disables/Enables character translations
● No-op on unix
sqlite3.exe: readfile() & writefile()

CREATE TABLE data(

id INTEGER PRIMARY KEY,
x BLOB
);

INSERT INTO data(x) VALUES(readfile('c:/xyz.txt'));

SELECT writefile(printf('c:/file%03d.bin',id), x)
FROM data;
sqlite3.exe: readfile() & writefile()
● 100 data file in c:/file001.bin, c:/file002.bin, ...
● Want to read all 100 files into the DATA table:

WITH RECURSIVE
cnt(ii) AS (VALUES(1)
UNION ALL
SELECT ii+1 FROM cnt WHERE ii<100)
INSERT INTO data(id, x)
SELECT ii, readfile(printf('c:/file%03d.bin',ii))
FROM cnt;
 sqlite3.exe: “.fullschema”
● Complete schema dump, as with “.schema”
● Also includes content of sqlite_stat1 and
sqlite_stat4 tables.
● Please use when reporting query planner
issues!
sqlite3.exe DBNAME .dbinfo
database page size: 4096
write format: 1
read format: 1
reserved bytes: 0
file change counter: 4
database page count: 14150
freelist page count: 0
schema cookie: 106
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 0
application id: 0
software version: 3008010
number of tables: 53
number of indexes: 53
number of triggers: 0
number of views: 0
schema size: 14257
 sqlite3.exe - advanced options
● Debugging compile-time options:
– -DSQLITE_DEBUG
– -DSQLITE_ENABLE_EXPLAIN_COMMENTS
– -DSQLITE_ENABLE_SELECTTRACE
– -DSQLITE_ENABLE_WHERETRACE
● .selecttrace 0xfff
● .wheretrace 0xfff
 sqlite3.exe - advanced options

.selecttrace 0xfff
SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND bool='T';

'­­ SELECT agg_flag (0x17A7380) selFlags=0x6c
    |­­ result­set
    |   '­­ AGG_FUNCTION0 'count'
    |­­ FROM
    |   |­­ {0,*} t1 tabname='t1'
    |   '­­ {1,*} t2 tabname='t2' LEFT­JOIN
    '­­ WHERE
        '­­ AND  flags=0x20005
            |­­ EQ  flags=0x20005
            |   |­­ {0:0}  flags=0x20005
            |   '­­ {1:0}  flags=0x120005
            '­­ EQ  flags=0x20005
                |­­ {1:1}  flags=0x120005
                '­­ 'T'