Addons/data/sqlite/Definitions
Overview | Basic Usage | Create | Definitions | zFns | Installation | Nulls | Examples | Quirks
Functions to open/close databases are called in the psqlite locale. Once a database is open, functions to access it are called in the database locale. Some examples assume the sandp database is open in locale db.
Call SQL commands
sqlcmd
Call SQL commands, with no result data expected, i.e. the result is the command return code. The argument is one or more semicolon-separated SQL statements.
sqlcmd__db 'update s set name="brown" where rowid=2' 0 sqlreads__db 's where rowid=2' ┌───┬─────┬──────┬─────┐ │sid│name │status│city │ ├───┼─────┼──────┼─────┤ │s2 │brown│10 │paris│ └───┴─────┴──────┴─────┘
Database Definitions
sqlclose
Close database.
sqlclose__db''
sqlcopy
Copy a database (from;to) and open the copy.
db=: sqlcopy_psqlite_ '~addons/data/sqlite/db/sandp.db';'~temp/sandp.db'
sqlcreate
Create a database, overwriting any existing database.
db=: sqlcreate_psqlite_ '~temp/sandp.db'
sqlopen
Open a database, returning database locale.
db=: sqlopen_psqlite_ '~addons/data/sqlite/db/sandp.db'
sqltables
Return tables in database (except for sqlite system tables). A non-empty string argument is used to return names that match.
sqltables__db'' ┌─┬─┬──┐ │p│s│sp│ └─┴─┴──┘ sqltables__db 'p' ┌─┬──┐ │p│sp│ └─┴──┘
sqlviews
Return views in database. A non-empty string argument is used to return names that match.
sqlviews__db''
Table Definitions
sqlcols
Return column names in table.
sqlcols__db 's' ┌───┬────┬──────┬────┐ │sid│name│status│city│ └───┴────┴──────┴────┘
sqlexist
Check if table exists.
sqlexist__db 'sp' 1 sqlexist__db 'spx' 0
sqlmeta
Return table information.
sqlmeta__db 'sp' ┌───┬────┬────┬───────┬──────────┬──┐ │cid│name│type│notnull│dflt_value│pk│ ├───┼────┼────┼───────┼──────────┼──┤ │0 │sid │text│0 │NULL │0 │ │1 │pid │text│0 │NULL │0 │ │2 │qty │int │0 │NULL │0 │ └───┴────┴────┴───────┴──────────┴──┘
sqlname
Return database filename.
sqlname__db'' /home/elmo/j8/user/temp/sandp.db
sqlschema
Return table definition.
sqlschema__db 's' CREATE TABLE s (sid text primary key, name text, status int, city text);
sqlsize
Number of records in table.
sqlsize__db 'sp' 12
Table Reads
sqlread
This returns a pair: column names, list of column_values. Other reading functions call sqlread under the covers.
sqlread__db 's' ┌──────────────────────┬──────────────────────────────────────────────────────────────.. │┌───┬────┬──────┬────┐│┌────────────────┬───────────────────────────────┬────────────.. ││sid│name│status│city│││┌──┬──┬──┬──┬──┐│┌─────┬─────┬─────┬─────┬─────┐│20 10 30 20 .. │└───┴────┴──────┴────┘│││s1│s2│s3│s4│s5│││smith│jones│blake│clark│adams││ .. │ ││└──┴──┴──┴──┴──┘│└─────┴─────┴─────┴─────┴─────┘│ .. │ │└────────────────┴───────────────────────────────┴────────────.. └──────────────────────┴──────────────────────────────────────────────────────────────..
sqlreadm
This returns the data as matrix:
sqlreadm__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││ │ │└──┴─────┴──┴──────┘│ └──────────────────────┴────────────────────┘
sqlreads
This returns a formatted display:
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│ └───┴─────┴──────┴──────┘
sqlhead/sqltail
These return the first/last x formatted records (default 10).
sqlhead__db 'sp' NB. first 10 records ┌───┬───┬───┐ │sid│pid│qty│ ├───┼───┼───┤ │s1 │p1 │300│ │s1 │p2 │200│ │s1 │p3 │400│ │s1 │p4 │200│ │s4 │p5 │100│ │s1 │p6 │100│ │s2 │p1 │300│ │s2 │p2 │400│ │s3 │p2 │200│ │s4 │p2 │200│ └───┴───┴───┘ 5 sqltail__db 'sp' NB. last 5 records ┌───┬───┬───┐ │sid│pid│qty│ ├───┼───┼───┤ │s2 │p2 │400│ │s3 │p2 │200│ │s4 │p2 │200│ │s4 │p4 │300│ │s4 │p5 │400│ └───┴───┴───┘
sqldict
This returns a dictionary:
sqldict__db 's' ┌──────┬──────────────────────────────────┐ │sid │┌──┬──┬──┬──┬──┐ │ │ ││s1│s2│s3│s4│s5│ │ │ │└──┴──┴──┴──┴──┘ │ ├──────┼──────────────────────────────────┤ │name │┌─────┬─────┬─────┬─────┬─────┐ │ │ ││smith│jones│blake│clark│adams│ │ │ │└─────┴─────┴─────┴─────┴─────┘ │ ├──────┼──────────────────────────────────┤ │status│20 10 30 20 30 │ ├──────┼──────────────────────────────────┤ │city │┌──────┬─────┬─────┬──────┬──────┐│ │ ││london│paris│paris│london│athens││ │ │└──────┴─────┴─────┴──────┴──────┘│ └──────┴──────────────────────────────────┘
sqlexec
This returns the values only as a boxed list. A single column is opened.
sqlexec__db 's' ┌────────────────┬───────────────────────────────┬──────────────┬──────────────.. │┌──┬──┬──┬──┬──┐│┌─────┬─────┬─────┬─────┬─────┐│20 10 30 20 30│┌──────┬─────┬.. ││s1│s2│s3│s4│s5│││smith│jones│blake│clark│adams││ ││london│paris│.. │└──┴──┴──┴──┴──┘│└─────┴─────┴─────┴─────┴─────┘│ │└──────┴─────┴.. └────────────────┴───────────────────────────────┴──────────────┴──────────────.. sqlexec__db 'name from s' ┌─────┬─────┬─────┬─────┬─────┐ │smith│jones│blake│clark│adams│ └─────┴─────┴─────┴─────┴─────┘
Table Write
sqlinsert
The argument is a table name, followed by a pair: column_names, column_values (the same format as the result of sqlread).
cls=: ;:'sid name status city'
dat=: ('s6';'s7');('brown';'eaton');40 10;<'rome';'madrid'
sqlinsert__db 's';cls;<dat
sqlupdate
The argument is a table name, a where statement, followed by a pair: column_names, column_values (as for sqlinsert).
sqlupdate__db 'p';'weight=12';('name';'city');<'hammer';'vienna'
sqlupsert
This is a variant of sqlinsert that updates existing records on given keys, and inserts any remaining records. The argument is a table name, list of keys, column_names and column_values:
cls=: ;:'sid name status city'
dat=: ('s5';'s8');('adams';'scott');50 60;<'lisbon';'berlin'
sqlupsert__db 's';'sid';cls;<dat
Parameterized Queries
sqlparm
This was added in v1.0.23.
A parametrized query is an ordinary SQL query that has one or more ? values, which are replaced by the data given. The data is in the form: types;values, where types are defined in the psqlite locale:
SQLITE_INTEGER=: 1 SQLITE_FLOAT=: 2 SQLITE_TEXT=: 3 SQLITE_BLOB=: 4
For example, an update using parameters:
sel=. 'update test set bfloat=? where aint=?' typ=. SQLITE_FLOAT_psqlite_,SQLITE_INTEGER_psqlite_ dat=. 123.45;75 sqlparm__locDB sel;typ;<dat
Note that the addon functions sqlinsert, sqlupdate, sqlupsert already use parameterized calls internally, so sqlparm is only needed for other types of queries.
Also, the only way to use blob values that contain ascii 0 is to use parameterized calls.
Meta Definitions
sqldebug
Set debug messages on/off.
sqldebug__db 1
sqlerror
Return last error message.
sqlerror__db '' unable to copy database ...
sqlite_extversion
Return sqlite extension version (major.minor):
sqlite_extversion_psqlite_'' 1.01
sqlite_info
Return sqlite info.
sqlite_info_psqlite_'' ┌──────┬────────────────────────────────────────────────────────────┐ │3.15.1│2016-11-04 12:08:49 1136863c76576110e710dd5d69ab6bf347c65e36│ └──────┴────────────────────────────────────────────────────────────┘
sqlreset
Reset sqlite, closing any open databases.
sqlreset_psqlite_''