(???) !DOCTYPE html> (???) html> (???) <head> (???) <meta charset="utf-8" /> (???) <meta name="viewport" content="width=device-width, initial-scale=1.0" /> (???) <title>SQLite</title> (???) <!-- ?no css --> (???) <link href="/slides.css" rel="stylesheet" /> (???) <link href="sqlite.css" rel="stylesheet" /> (???) <script src="/slides.js" defer></script> (???) <link href="/timer.css" rel="stylesheet" /> (???) <script src="/timer.js" defer></script> (???) </head> (???) <body> (???) <main class="slides" tabindex="0" autofocus> (???) <!-- TODO goed checken of dit de abstract waarmaakt --> (???) <!-- TODO section -> h1 --> (???) <!-- TODO section.paragraph -> h2 --> (???) <!-- TODO additional text -> p (enable/disable) --> (???) <!-- TODO add camera and speech recognition to swipe slides --> (???) <!-- TODO use id's to overlay on both sides (to navigate with mouse, does so swipe works on mobile then?) --> (???) <!-- simple demo sqlite in main thread, prefer worker + OO api + persistent storage (needs COOP/COEP)--> (???) <!-- <section class="paragraph"> (???) <pre id="sqlite-demo"></pre> (???) </section> --> (???) <!-- <script src="sqlite-wasm-3510000/jswasm/sqlite3.js"></script> (???) <script src="sqlite-demo.js"></script> --> (???) <section class="paragraph" id="navigate-hint"> (???) <p><kbd>◀</kbd> prev<br /><kbd>▶</kbd> next</p> (???) </section> (???) <!-- <section> (???) <img src="images/sqlite370_banner.svg" width="36%" class="logo" /> (???) </section> --> (???) <!-- vragen vooraf: (???) (???) - wie gebruikt mysql/postgresql/etc? (???) - wie gebruikt sqlite? (???) - merk je verschillen? (???) - wie denkt te moeten schalen als facebook? als belastingdienst op 1 maart? als (???) bol.com op black friday? als marktplaats op 1 januari? (???) (???) --> (???) <!-- <section class="hidden" id="guess-and-shoot"> (???) Schatten<br>&<br>schieten<br><br>met SQLite (???) <details open> (???) 1. heel kort: schatten, schieten (kwh, marge, supermarkt, marge, (???) reading 1 GB from disk, writing 1 GB to disk, relevant xkcd!) Voor een (???) talk van een uur, maar wellicht een paar kleine dingen eruit pakken? (???) Mensen hebben geen idee van ratio, factoren, risico's e.d. insteker: (???) wat is de winstmarge op een kWh stroom a 0.36 of een m2 gas (1.00)? (???) reading 2 GB from hd to mem reading 2 GB from ssd to mem read/write 1 (???) GB from mem to mem calculations: 2ghz -> fibonaccii until in 1s? (???) sqlite (my favorite): 1.000.000k writes/sec (meanwhile reading) (TODO (???) klopt dat?) om terug te komen op de eerste vraag: 1% (TODO in laatste (???) slide?) Het klinkt cute en wellicht kennen we het allemaal wel. We (???) gebruiken het ieder geval ongemerkt wel allemaal. Op bijna elk (???) maken er graag gebruik van. (???) apparaat staan wel 1 of meerdere sqlite databases. Telefoons, browsers (???) </details> (???) </section> (???) --> (???) <section id="sqlite"> (???) <!-- I ♥ SQLite --> (???) SQLite (???) <details open> (???) <summary>Abstract</summary> (???) <dl> (???) <dt>Titel</dt> (???) <dd>SQLite</dd> (???) <dt>Abstract</dt> (???) <dd> (???) Vrijwel iedereen gebruikt het (onbewust) als eindgebruiker. Maar (???) wanneer en hoe gebruik je het als ontwikkelaar? En wanneer niet? (???) </dd> (???) </dl> (???) </details> (???) </section> (???) <section class="paragraph" id="phonetics"> (???) ˈsiːkwəˌlaɪt ?<br /><br />ˌɛsˌkjuːˌɛlˈaɪt ? (???) <!-- <details open> (???) <summary>Uitspraak</summary> (???) Niemand weet het. Wie kent het? Waar gebruik je het voor? Waar (???) associeer je het mee? - wie kent het? - wie heeft het wel eens (???) gebruikt? - vraag aan niet gebruiker: waar associeer je het mee? - (???) vraag aan gebruiker: bevalt het? nadelen/voordelen? vragen vooraf (???) (tijdens phonetisch, probeer iemand te ontlokken hoe je het uitspreekt (???) door het zelf niet uit te spreken) (???) </details> --> (???) </section> (???) <section id="about-joris" class="about"> (???) <a href="https://joriszwart.nl" target="_blank"> (???) <img (???) src="/joriszwart-logo-uluru.png" (???) alt="Joris Zwart logo" (???) width="1000" (???) height="125" (???) /> (???) </a> (???) <!-- Title: Introductie SQLite [alle niveaus (technisch)] - Joris Zwart --> (???) <!-- Lange abstract NL (???) rijwel iedereen gebruikt SQLite - onbewust - als eindgebruiker. Maar (???) anneer en hoe gebruik je het als ontwikkelaar? En wanneer niet? Is het (???) en alternatief voor de grote monsters? (???) -> (???) <!-- Korte abstract NL (IMG) edereen gebruikt SQLite. Maar hoe gebruik je het als ontwikkelaar? En (???) anneer niet? Is het een alternatief voor de grote monsters? (???) -> (???) <details open> (???) <summary>Over Joris</summary> (???) <p> (???) Wie?<br /><br />Mijn naam is Joris Zwart (1974) woonachtig te Lent (???) (boven Nijmegen) met mijn lieftallige gezin. Online ben ik te vinden (???) op (???) <a href="https://joriszwart.nl" target="_blank">joriszwart.nl</a> (???) </p> (???) <p> (???) Wat?<br /><br /><a href="https://lastcrud.com" target="_blank" (???) >lastcrud.com</a (???) >; upcoming nocode platform, main USP: visual business rules, (???) scratch for grown-ups. (???) </p> (???) <p>Hoe?<br /><br />C#, SQLite, Vue/Vuetify.</p> (???) <p> (???) Waar?<br /><br />Ex-Planonner 2005 – 2009. PlanonNet Self-Service I (???) (nu PSS2?), PlanonTalk, reserveringen (express, outlook), SAP (???) support calls. (???) </p> (???) <!-- maker van go-sqlite3 BLOB/API, PR helaas al > 1 jaar open --> (???) </details> (???) </section> (???) <!-- demo to flabbergast, didn't work out very well... somebody predicted how fast it was --> (???) <section class="paragraph" id="benchmark"> (???) Lies, damned lies, benchmarks. (???) <!-- <br /> --> (???) <!-- <small>Vrij naar Benjamin Disraeli (1804 - 1881)</small> --> (???) <details open> (???) <summary>Benchmark</summary> (???) <pre> (???) REATE TABLE IF NOT EXISTS data ( (???) id INTEGER PRIMARY KEY, (???) content TEXT (???) STRICT (???) </pre> (???) <pre> (???) timer on (???) </pre> (???) > (???) <pre> (IMG) NSERT INTO numbers (content) (???) SELECT value FROM generate_series(1, 1000000); (???) </pre> (???) <pre> (???) explain select count(*) from numbers; (???) explain query plan select count(*) from numbers; (???) explain query plan select sum(*) from numbers; (???) </pre> (???) <pre> (IMG) NSERTs : 1.000.000 (???) uration : 1.357s (???) -------------------- (IMG) NSERTs : ~737.000/s (???) </pre> (???) Mac mini M1 2022, 16 GB, SSD (the latter is important). (???) <pre> (???) go run bench.go (???) sqlite3 bench.sqlite (???) .timers on (???) select count(*) from data; (???) explain query plan select count(*) from data; (???) select * from data limit 0, 10; (???) select * from data limit 999990, 10; (???) </pre> (???) <!-- -- sqlite doet geen statistics (om te veel btree rewriting te voorkomen) dus table scan maar nog steeds retesnel (???) show some lies (benchmarks) -> insert.go (~100.000 prepared, wal-mode (???) vs transaction etc), select.go (same, then indexed maybe). Do not (???) compare with others (but state 2-10x :-)) --> (???) </details> (???) </section> (???) <!-- These are purposefully vague without numbers on the axes etc, no vendor naming... sometimes sqlite is better, sometimes other vendor is ((write)concurrency) --> (???) <!-- response time by query period: 1 concurrent client. Aggregate per week/month/quarter/year --> (???) <!-- response time by query period: 4 concurrents client. Aggregate per week/month/quarter/year --> (???) <!-- In other words: it depends --> (???) <!-- https://lobste.rs/s/2etd7f/sqlite_postgresql_it_s_complicated --> (???) <!-- <section id="graph"> (???) <svg></svg> (???) </section> --> (???) <section id="tagline"> (???) <!-- TODO animate per word --> (???) Small. Fast. Reliable. (???) <details open> (???) <summary>Klopt dat?</summary> (???) <p>Ja, mits... WAL-mode, indexen en transactions</p> (???) <p>Tenzij..., heavy writing.</p> (???) <p> (???) ACID, auteurs garanderen dat het minstens tot 2050 meegaat (hoe dan? (???) C :-)) (???) </p> (???) <p> (???) Meest getest? Miljoenen regels testcode. Op allerlei execution (???) points (WAT als ik NU de stroom er af haal? Gelden de ACID-principes (???) nog steeds?) Diep onderzoek naar filesystemen en hun issues. Disks (???) and disk controllers lie. (???) </p> (???) <p> (???) Oorsprong Dr. Richard Hipp. 2000. Ik kwam er zelf mee in aanraking (???) rond 2003. Officieel archive format US Library of Congress. Auteurs: (???) alternatief voor (???) <code>fopen()</code> (C file open), maar daar doen ze zichzelf (???) tekort IMHO. (???) </p> (???) </details> (???) </section> (???) <section>Public domain</section> (???) <section id="fopen" class="paragraph"> (???) <del>fopen()</del> (???) <!-- <ins>SQLite</ins> --> (???) </section> (???) <section id="demo"> (???) Demo (???) <!-- TODO use https://sqlite.org/wasm/doc/trunk/demo-123.md for inline shiznit --> (???) <details> (???) <summary>CLI</summary> (???) - open database (in memory of met file) - create table - insert - ls (???) files (???) </details> (???) </section> (???) <section id="install">Installation</section> (???) <!-- TODO attribution --> (???) <section id="install-linux" class="paragraph"> (???) <!-- penguin with psycho eyes --> (???) <img src="images/linux.svg" class="no-print logo" width="20%" /> (???) apt install sqlite3 (???) </section> (???) <section id="install-macos" class="paragraph"> (???) <img src="images/apple.svg" class="no-print logo" width="20%" /> (???) brew install sqlite3 (???) </section> (???) <section id="install-freebsd" class="paragraph"> (???) <img src="images/beastie.svg" class="no-print logo" width="20%" /> (???) pkg install sqlite3 (???) </section> (???) <section id="install-windows" class="paragraph"> (???) <img src="images/windows.svg" class="no-print logo" width="20%" /> (???) choco install sqlite (???) </section> (???) <section id="fallback" class="paragraph"> (???) <a href="https://sqlite.org/download.html" target="_blank"> (???) sqlite.org/download.html (???) </a> (???) </section> (???) <section id="build-dyi" class="paragraph">./configure && make</section> (???) <section id="data-types"> (???) Five data types (???) <details open> (???) <summary>Eenvoud</summary> (???) Vergelijk met Big Monsters: (???) <ul> (???) <li>text</li> (???) <li>blob</li> (???) <li>varchar(n)</li> (???) <!-- not so varchar, must specify length most of the time --> (???) <li>nvarchar(n), wat n?</li> (???) <li>int(1)</li> (???) <li>int(11)</li> (???) <li>...</li> (???) <li>What me worry?</li> (???) </ul> (???) <p>( ͡ಠ ʖ̯ ͡ಠ) column affinity, strict tables</p> (???) </details> (???) </section> (???) <section class="paragraph" id="integer"> (???) INTEGER (???) <!-- <details open> (???) <summary>range etc</summary> (???) </details> --> (???) </section> (???) <section class="paragraph" id="real"> (???) REAL (???) <details open> (???) <summary></summary> (???) </details> (???) </section> (???) <section class="paragraph" id="text"> (???) TEXT (???) <details open> (???) <summary>Encoding</summary> (???) Standaard UTF-8, maar pragma mogelijk voor andere encodings. (???) </details> (???) </section> (???) <section class="paragraph" id="blob"> (???) BLOB (???) <details open> (???) <summary>Details</summary> (???) Binary Large Objects (afbeeldingen, documenten, apps) Streaming Blob (???) API (???) </details> (???) </section> (???) <section class="paragraph" id="null"> (???) NULL (???) <details open> (???) <summary></summary> (???) Yep, het is een type. Don't ask, maar dit heeft te maken met column (???) affinity. (???) </details> (???) </section> (???) <section class="paragraph" id="caveat-writing"> (???) Caveat: heavy writing (???) </section> (???) <section class="paragraph" id="caveat-scaling">Caveat: scaling</section> (???) <section id="features"> (???) <!-- TODO split in features and properties? (???) feature: window functions (???) property: geen n+1 problems (???) --> (???) Features (???) </section> (???) <section class="paragraph" id="relational-database"> (???) Relational database (???) </section> (???) <section class="paragraph" id="document-database"> (???) Document database (???) <details open> (???) <summary>JSON/JSONB + calculated indexen</summary> (???) </details> (???) </section> (???) <section class="paragraph" id="columnar-database"> (???) Columnar database (???) <details open> (???) <summary>Ugly hack</summary> (???) Tabel per kolom *of* verticaal gepivotteerd. (???) </details> (???) </section> (???) <section class="paragraph" id="graph-database"> (???) Graph database (???) <!-- little bit far stretched, but because there is no n+1 issue, it can work pretty well --> (???) </section> (???) <section class="paragraph" id="time-series"> (???) Time series (???) <!-- little bit far stretched --> (???) </section> (???) <section class="paragraph" id="zero-configuration"> (???) Zero configuration, but… (???) <details open> (???) <summary>True</summary> (???) Maar WAL-mode én foreign keys! Verder tunen (cache-size etc) kan (???) altijd en loont ook. (???) </details> (???) </section> (???) <!-- TODO BEGIN IMMEDIATE om een transactie te starten --> (???) <section class="paragraph" id="embedded"> (???) Embedded library (???) <details open> (???) <summary>Details</summary> (???) Library, geen standalone (server)applicatie. Self-contained. (???) In-process. Dus geen socket of netwerkoverhead. (???) </details> (???) </section> (???) <section class="paragraph" id="footprint"> (???) Small footprint (???) <details open> (???) <summary>Size</summary> (???) 500 kB – 1 MB (???) <br /><br /> (???) <small>MySQL installer: 2 MB (enkel de installer)</small> (???) </details> (???) </section> (???) <section class="paragraph" id="acid"> (???) ACID compliant (???) <details open> (???) <summary>Betekenis</summary> (???) Atomicity<br /> (???) Consistency<br /> (???) Isolation<br /> (???) Durability (???) </details> (???) </section> (???) <!-- TODO (???) (???) iets over sqlite as application format (no wal, application_id = '') (???) migreren via user_version (.dump geeft dat niet terug? caveat?) (???) (???) --> (???) <section class="paragraph" id="foreign-keys"> (???) Foreign keys, ha! (???) <details open> (???) <summary>Mental note</summary> (???) Wel even aanzetten (bij iedere connectie). (???) </details> (???) </section> (???) <section class="paragraph" id="modern-sql">Modern SQL</section> (???) <section class="paragraph" id="n-plus-1"> (???) N+1 <del>problems</del> (???) <details open> (???) <summary>Problem?!</summary> (???) Geen probleem! (???) </details> (???) </section> (???) <section class="paragraph" id="window-functions"> (???) Window functions (???) </section> (???) <!-- TODO table example window functions --> (???) <section class="paragraph" id="blob-api"> (???) BLOB API (???) <details open> (???) <summary>Details</summary> (???) Streamend schrijven en lezen van blobs. Afbeeldingen of andere (???) binaries *in* de database is hiermee geen anti-pattern meer en zelfs (???) aan te bevelen; referentiële integriteit op je assets. (???) </details> (???) </section> (???) <section class="paragraph" id="json">JSON and JSONB</section> (???) <section class="paragraph" id="bindings"> (???) Language bindings (???) <details open> (???) <summary>Details</summary> (???) Vrijwel elk taal/platform heeft bindings. C, C++, C#, Golang, Java, (???) JavaScript (Node), Python, Haskell etc. (???) <!-- TODO aanvullen met bekende bindings (veel s.v.p., als (animated) particles?) --> (???) </details> (???) </section> (???) <section class="paragraph" id="in-memory"> (???) :memory: (???) <!-- or file:/memdb1?vfs=memdb (mattn sqlite only?) --> (???) <details open> (???) <summary>Toepassingen</summary> (???) Cache (smart querying), snelle integrations tests. (???) </details> (???) </section> (???) <section id="extensions">Extensions</section> (???) <section class="paragraph" id="authorization">Authorization</section> (???) <section class="paragraph" id="compression">Compression</section> (???) <section class="paragraph" id="encryption">Encryption</section> (???) <section class="paragraph" id="fts"> (???) Full-text search (???) <details open> (???) <summary>FTS5</summary> (???) Zie ook mijn artikel (???) <a (???) href="https://joriszwart.nl/articles/full-text-search-part-1" (???) target="_blank" (???) >Full-text document indexing - part Ⅰ</a (???) > (???) over het indexeren van docx, gebruik makende van het feit dat het (???) eigenlijk gezipte XML-bestanden zijn. (???) </details> (???) </section> (???) <section class="paragraph" id="geo">Geospatial</section> (???) <section class="paragraph" id="dml"> (???) Transactional DDL (???) <details open> (???) <summary>Belangrijk?</summary> (???) <p>Data Definition Language</p> (???) <p>Waarom handig?</p> (???) <p> (???) Omdat je niet een half gelukte migratie met de hand wilt (???) terugdraaien. (???) </p> (???) </details> (???) </section> (???) <section id="write-ahead-log"> (???) Write-Ahead Log (???) <details open> (???) <summary>Details</summary> (???) <ul> (???) <li> (???) Readers blokkeren elkaar niet en worden niet geblokkeerd door (???) writers. (???) </li> (???) <li>Writers blokkeren elkaar.</li> (???) </ul> (???) <p>Ergo: single-writer.</p> (???) <p>Je kan split reading/writing (queue) doen. Zelfs aan te raden.</p> (???) <p> (???) Busy timeout (bij 5000 ms) is van belang, want default erg kort. (???) </p> (???) </details> (???) </section> (???) <section class="paragraph" id="pragma-journalmode-wal"> (???) PRAGMA journal_mode = WAL (???) <!-- <details open> (???) <summary>Meer pragma's</summary> (???) <pre> (???) RAGMA journal_mode = WAL; (???) RAGMA busy_timeout = 5000; (???) RAGMA synchronous = NORMAL; (???) RAGMA foreign_keys = true; (???) RAGMA cache_size = 1000000000; (???) RAGMA temp_store = memory; (???) </pre> (???) <pre> (???) EGIN IMMEDIATE (???) </pre> (???) </details> --> (???) </section> (???) <section class="paragraph" id="foreign-keys"> (???) PRAGMA foreign_keys = true (???) </section> (???) <section class="paragraph" id="busy-timeout"> (???) PRAGMA busy_timeout = 5000 (???) </section> (???) <section class="paragraph" id="synchronous"> (???) PRAGMA synchronous = NORMAL (???) </section> (???) <section class="paragraph" id="strict-tables">Strict tables</section> (???) <section id="vs-monsters"> (???) SQLite<br /> (???) vs<br /> (???) Big Monsters (???) <details open> (???) <!-- TODO niet echt een spoiler meer op dit punt ;-) --> (???) <!-- TODO wat is het keerpunt? hoeveel concurrent writers? is dat genoeg voor je usecase? ik heb als voorbeeld 250 writers die 40 kb per seconde schrijven --> (???) <summary><spoiler></summary> (???) Absoluut! (???) <small (???) >in 99.214% van de gevallen. We zijn geen (???) <abbr title="Meta, Apple, Netflix, Google, Amazon, Microsoft" (???) >MANGA</abbr (???) ></small (???) > (???) (Meta Amazon Netflix Google Apple) (???) </details> (???) </section> (???) <!-- <section id="must-do"> (???) Must do's (???) <details open> (???) <summary>Hints</summary> (???) <p> (???) Voor integriteit en performance: (???) </p> (???) <ul> (???) <li>WAL-mode</li> (???) <li>Foreign Keys</li> (???) <li>Strict tables</li> (???) </ul> (???) <p> (???) De rest is up to you (cache size, PRAGMA synchronous = NORMAL, be careful (???) depending on your OS) (???) </p> (???) </details> (???) </section> --> (???) <section id="synopsis">Synopsis</section> (???) <section id="sources"> (???) <ul> (???) <li><a href="https://sqlite.org" target="_blank">sqlite.org</a></li> (???) <li> (???) <a href="https://en.wikipedia.org/wiki/SQLite" target="_blank" (???) >en.wikipedia.org/wiki/SQLite</a (???) > (???) </li> (???) </ul> (???) </section> (???) <section id="inspired"> (???) <!-- Who feels inspired? --> (???) Inspired? (???) <details open> (???) <summary>Aanbod</summary> (???) <p> (???) Ik zelf ben er zeer enthousiast over en het is mijn go-to database (???) platform. (???) </p> (???) <p>Wie gaat er mee experimenteren? Vingers?</p> (???) <p> (???) Als je het serieus overweegt: ik ben er graag om je te helpen (om (???) niets). (???) </p> (???) </details> (???) </section> (???) <!-- TODO leuke dingen zoals sqlite over http, geo extension, wal2? --> (???) <section class="paragraph" id="slides"> (???) <a href="https://joriszwart.nl/sqlite" target="_blank" (???) >joriszwart.nl/sqlite</a (???) > (???) </section> (???) <!-- css target #timer? time left, wait in silence :-) --> (???) <!-- <section class="paragraph" id="timer" data-timer></section> --> (???) </main> (???) <aside class="timer" data-timer="15"></aside> (???) <!-- <div class="progress"></div> --> (???) </body> (???) /html>