Addons/data/sqlite/Examples

Overview | Basic Usage | Create | Definitions | zFns | Installation | Nulls | Examples | Quirks

There are two sample databases included with the addon in subdirectory db. Other sqlite example databases are readily available on the web.

• sandp is the standard Suppliers and Parts database

• chinook is an open source database that is an alternative to Northwind. A selection of tables are included with the addon.

sandp

sandp is available in the addon db directory, and also as a script which defines verb buildsandp. For example:

1. Using the distributed database:

 load 'data/sqlite'
 db=: sqlopen_psqlite_ '~addons/data/sqlite/db/sandp.db'
 sqlhead__db 'sp where qty=400'
┌───┬───┬───┐
│sid│pid│qty│
├───┼───┼───┤
│s1 │p3 │400│
│s2 │p2 │400│
│s4 │p5 │400│
└───┴───┴───┘

2. Using the buildsandp verb:

 load 'data/sqlite/sandp'
 db=: buildsandp '~temp/t1.db'
 sqlhead__db 'sp left join s on sp.sid=s.sid where qty=400'
┌───┬───┬───┬───┬─────┬──────┬──────┐
│sid│pid│qty│sid│name │status│city │
├───┼───┼───┼───┼─────┼──────┼──────┤
│s1 │p3 │400│s1 │smith│20 │london│
│s2 │p2 │400│s2 │jones│10 │paris │
│s4 │p5 │400│s4 │clark│20 │london│
└───┴───┴───┴───┴─────┴──────┴──────┘

chinook

For example, using the sqlite z-functions:

 load 'data/sqlite/sqlitez'
 
 dbopen '~addons/data/sqlite/db/chinook.db'
 dbtables''
┌──────┬───────┬─────────┬──────┬───────────┬──────┐
│albums│artists│customers│genres│media_types│tracks│
└──────┴───────┴─────────┴──────┴───────────┴──────┘
 dbhead 'trackid,name,composer from tracks where name like "%snow%"'
┌───────┬───────────────────────┬────────────────────────────────────────────────────────┐
│TrackId│Name │Composer │
├───────┼───────────────────────┼────────────────────────────────────────────────────────┤
│ 9 │Snowballed │Angus Young, Malcolm Young, Brian Johnson │
│ 145 │Snowblind │ │
│ 161 │Snowblind │Tony Iommi, Bill Ward, Geezer Butler, Ozzy Osbourne │
│2412 │By-Tor And The Snow Dog│Geddy Lee And Alex Lifeson/Geddy Lee And Neil Peart/Rush│
│3277 │Snowblind │ │
└───────┴───────────────────────┴────────────────────────────────────────────────────────┘
 6 dbhead 'artists left join albums where artists.artistid=albums.artistid'
┌────────┬─────────────────┬───────┬─────────────────────────────────────┬────────┐
│ArtistId│Name │AlbumId│Title │ArtistId│
├────────┼─────────────────┼───────┼─────────────────────────────────────┼────────┤
│1 │AC/DC │1 │For Those About To Rock We Salute You│1 │
│1 │AC/DC │4 │Let There Be Rock │1 │
│2 │Accept │2 │Balls to the Wall │2 │
│2 │Accept │3 │Restless and Wild │2 │
│3 │Aerosmith │5 │Big Ones │3 │
│4 │Alanis Morissette│6 │Jagged Little Pill │4 │
└────────┴─────────────────┴───────┴─────────────────────────────────────┴────────┘

• SQLite D.5.2.3
• SQLite D.2.9
• SQLite R.12.9
• SQLite R.2.2.3
• Data R.2.1.4