Addons/data/sqlite/Examples
< Addons | data/sqlite
Jump to navigation
Jump to search
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 │ └────────┴─────────────────┴───────┴─────────────────────────────────────┴────────┘