Addons/data/sqlite/Examples

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       │
└────────┴─────────────────┴───────┴─────────────────────────────────────┴────────┘