Addons/data/sqlite/Definitions

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_''