Addons/data/sqlite/Basic Usage

Overview

The addon is read into locale psqlite. A sqlite database is opened or created in an instance of this locale, and subsequent access uses this instance.

For example, using the sandp database included with the addon:

   load 'data/sqlite'
   db=: sqlopen_psqlite_ '~addons/data/sqlite/db/sandp.db'

Meta Data

Tables in database:

   sqltables__db''
┌─┬─┬──┐
│p│s│sp│
└─┴─┴──┘

Table description:

   sqlmeta__db 's'
┌───┬──────┬────┬───────┬──────────┬──┐
│cid│name  │type│notnull│dflt_value│pk│
├───┼──────┼────┼───────┼──────────┼──┤
│0  │sid   │text│0      │          │1 │
│1  │name  │text│0      │          │0 │
│2  │status│int │0      │          │0 │
│3  │city  │text│0      │          │0 │
└───┴──────┴────┴───────┴──────────┴──┘

Reading Data

The main function is sqlread which returns a pair: column names, list of column_values. There are several other reading functions that call sqlread under the covers.

   sqlread__db 'select * from s'
┌──────────────────────┬──────────────────────────────────────────────────────────────..
│┌───┬────┬──────┬────┐│┌────────────────┬───────────────────────────────┬────────────..
││sid│name│status│city│││┌──┬──┬──┬──┬──┐│┌─────┬─────┬─────┬─────┬─────┐│20 10 30 20 ..
│└───┴────┴──────┴────┘│││s1│s2│s3│s4│s5│││smith│jones│blake│clark│adams││            ..
│                      ││└──┴──┴──┴──┴──┘│└─────┴─────┴─────┴─────┴─────┘│            ..
│                      │└────────────────┴───────────────────────────────┴────────────..
└──────────────────────┴──────────────────────────────────────────────────────────────..

For interactive use, the function sqlreads is a cover for sqlread that returns a formatted display:

   sqlreads__db 'select * from s'
┌───┬─────┬──────┬──────┐
│sid│name │status│city  │
├───┼─────┼──────┼──────┤
│s1 │smith│20    │london│
│s2 │jones│10    │paris │
│s3 │blake│30    │paris │
│s4 │clark│20    │london│
│s5 │adams│30    │athens│
└───┴─────┴──────┴──────┘

For convenience, the select keyword and * column specifier can be omitted:

  sqlreads__db 's'
┌───┬─────┬──────┬──────┐
│sid│name │status│city  │
├───┼─────┼──────┼──────┤
│s1 │smith│20    │london│
│s2 │jones│10    │paris │
│s3 │blake│30    │paris │
│s4 │clark│20    │london│
│s5 │adams│30    │athens│
└───┴─────┴──────┴──────┘

Otherwise, the argument is a standard SQL statement (as supported by sqlite):

  sqlreads__db 's where status=30 order by city'
┌───┬─────┬──────┬──────┐
│sid│name │status│city  │
├───┼─────┼──────┼──────┤
│s5 │adams│30    │athens│
│s3 │blake│30    │paris │
└───┴─────┴──────┴──────┘

   sqlreads__db 'sid,sum(qty),max(qty) from sp group by sid'
┌───┬────────┬────────┐
│sid│sum(qty)│max(qty)│
├───┼────────┼────────┤
│s1 │1200    │400     │
│s2 │ 700    │400     │
│s3 │ 200    │200     │
│s4 │1000    │400     │
└───┴────────┴────────┘

Writing Data

The sqlinsert function takes an argument of a table, followed by a pair: column_names, column_values (the same format as the result of sqlread). For example, using a copy of the sandp database:

   db=: sqlcopy_psqlite_ '~addons/data/sqlite/db/sandp.db';'~temp/sandp.db'

   cls=: ;:'sid name status city'
   dat=: ('s6';'s7');('brown';'eaton');40 10;<'rome';'madrid'
   sqlinsert__db 's';cls;<dat

   5 sqltail__db 's'         NB. last 5 records
┌───┬─────┬──────┬──────┐
│sid│name │status│city  │
├───┼─────┼──────┼──────┤
│s3 │blake│30    │paris │
│s4 │clark│20    │london│
│s5 │adams│30    │athens│
│s6 │brown│40    │rome  │
│s7 │eaton│10    │madrid│
└───┴─────┴──────┴──────┘

The sqlupdate function (v1.0.23 and later) updates records on a where statement:

   sqlupdate__db 'p';'weight=12';('name';'city');<'hammer';'vienna'
   sqlhead__db 'p where weight=12'
┌───┬──────┬─────┬──────┬──────┐
│pid│name  │color│weight│city  │
├───┼──────┼─────┼──────┼──────┤
│p1 │hammer│red  │12    │vienna│
│p5 │hammer│blue │12    │vienna│
└───┴──────┴─────┴──────┴──────┘

Example for update only one field:

   sqlupdate__db 'p';'weight=12';(,<'name');<,<'hammer'

The sqlupsert function is a variant that will update existing records on given keys, and insert any remaining records:

   dat=: ('s5';'s8');('adams';'scott');50 60;<'lisbon';'berlin'
   sqlupsert__db 's';'sid';cls;<dat

   5 sqltail__db 's'
┌───┬─────┬──────┬──────┐
│sid│name │status│city  │
├───┼─────┼──────┼──────┤
│s4 │clark│20    │london│
│s5 │adams│50    │lisbon│
│s6 │brown│40    │rome  │
│s7 │eaton│10    │madrid│
│s8 │scott│60    │berlin│
└───┴─────┴──────┴──────┘