Addons/data/sqlite/Basic Usage
Overview | Basic Usage | Create | Definitions | zFns | Installation | Nulls | Examples | Quirks
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│ └───┴─────┴──────┴──────┘