SQL for the IoT Move the query to the data, not the data to the - PowerPoint PPT Presentation

SQL for the IoT

Move the query to the data, not the data to the query.

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 Run the Prep'ed SQL Result into a program program Stmt Front Half Back Half

Ins and Outs of SQL Compile SQL Run the Prep'ed SQL Result into a program program Stmt sqlite3_step() sqlite3_prepare_v2( sqlite3_stmt object )

Ins and Outs of SQL Compile SQL Run the Prep'ed SQL Result into a program program Stmt sqlite3_step() sqlite3_prepare_v2( ) sqlite3_exec(){ sqlite3_prepare_v2(); while( sqlite3_step()!=DONE ){}; sqlite3_finalize(); }

Architecture Of SQLite Parser Code Generator Virtual Machine B-Tree Pager OS Interface

Compile SQL Run the Prep'ed SQL Result into a program program Stmt Parser Code Generator Virtual Machine B-Tree Pager OS Interface

Compile SQL Run the Prep'ed SQL Result into a program program Stmt Parser Code Generator Virtual Machine B-Tree Pager OS Interface

Compile SQL Run the Prep'ed SQL Result into a program program Stmt Parser Code Generator Virtual Machine B-Tree ● “Storage Engine” Pager ● Fast, transactional, ordered, key/value store OS Interface

Architecture Of SQLite ● Byte code interpreter Parser ● Big switch statement Code Generator inside a for loop. Virtual Machine ● Other engines walk a B-Tree tree of structures Pager ● Similar to JVM or Parrot or P-Code OS Interface

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 pairs Parser with unique keys Code Generator ● O(logN) insert, seek, Virtual Machine and delete B-Tree ● O(1) next and previous Pager OS Interface

Architecture Of SQLite ● Atomic commit and Parser rollback Code Generator ● Uniform size pages Virtual Machine numbered from 1 B-Tree ● No interpretation of 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

Logical View of SQL Table Storage 64bit integer Arbitrary length data in “record” format key “rowid”

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 Non-leaf pages hold only keys than once in the tree. Between 50 and 8000 keys/page depending on page size. Integer key Pointer to lower page ● Key + Data in leaves Binary ● Combined key+data is a “cell” 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