Copyright (C) 2006. Oleg Kobchenko. All rights reserved.
Provided AS IS. No warrantiles or liabilities extended.
SQLite addon -- enhanced API for J
based on sqlite 3.3.7 embedded engine.
Summary
create v opens a database connection strtbl v returns unrestricted number of rows of strings exec v excutes one or more SQL statements apply v bulk insert/update/delete limit v pagination for next query statement query v select with boxed valued results strquery v select with boxed string results colquery v select with column string results Sql is SQL test with optional parameters as ? Params is a list or matrix of atomic SQL parameters if matrix, operation is repeated for each row ParamTypes indicate 0=normal and 1=BLOB parameter Errors are raised as J signal with error message
Enhanced API
create (v) opens a database connection db=: 'psqlite'conew~ 'path/to/file.sqlite' strtbl (v) returns unrestricted number of rows of strings sqltbl Sql exec (v) excutes one or more SQL statements exec Sql apply (v) bulk insert/update/delete Params apply Sql;[ParamTypes] limit (v) pagination for next query statement limit [[skip],maxrows] query (v) select with boxed valued results Params query Sql strquery (v) select with boxed string results Params strquery Sql colquery (v) select with column string results Params colquery Sql
These are listings and results of some examples found in the sqlite folder.
require '~addons/sqlite/sqlite.ijs'
sqlSummary=: 0 : 0
select 'S' TAB, count(*) CNT from S union
select 'P' TAB, count(*) CNT from P union
select 'SP' TAB, count(*) CNT from SP union
select 'J' TAB, count(*) CNT from J union
select 'SPJ' TABE, count(*) CNT from SPJ;
)
sqlSpjBySupplierAndWeight=: 0 : 0
select S.SNAME,P.PNAME,P.WEIGHT,J.JNAME,J.CITY
from SPJ,S,P,J
where SPJ.SID=S.SID and SPJ.PID=P.PID and SPJ.JID=J.JID
and SPJ.SID=?
and SPJ.PID in (select PID from P where WEIGHT=?);
)
FDIR=: jpath '~addons/sqlite/'
'FDB FDDL FDATA'=: cut 'date.sqlite date_ddl.sql date_data.sql '
ferase FDIR,FDB
]db=: 'psqlite'conew~ FDIR,FDB
exec__db fread FDIR,FDDL
exec__db fread FDIR,FDATA
strquery__db 'select count(*) as COUNT from S;'
strquery__db 'select * from S;' [ limit__db 2 3
strquery__db 'select * from P;'
strquery__db sqlSummary
colquery__db 'select * from P;'
17 colquery__db 'select * from P where WEIGHT=?;'
('S5';17) colquery__db sqlSpjBySupplierAndWeight
(14 17)colquery__db'select * from P where WEIGHT BETWEEN ? and ?;'
strtbl__db'select * from SP where SID=''S1'';'
(12 17) query__db 'select * from P where WEIGHT=?;'
(;+/);}.query__db 'select WEIGHT from P;'
('P7';'Cog';'Red';19.0;'London') apply__db 'insert into P values (?,?,?,?,?);'
('test1';64{.a.) apply__db 'insert into PIC values (?,?);';0 1 NB. 2nd blob
(<'test1')query__db 'select * from PIC where PICNAME=?;'
(64{.a.)-:1 0{::(<'test1')query__db 'select PICVAL from PIC where PICNAME=?;'
colquery__db 'select * from WORDS;'
(1 3) apply__db'delete from WORDS where WID=?;'
exec__db'delete from WORDS where 1;'
sqlInsWords=: 'insert into WORDS (WVAL) VALUES(?);'
(;:'one two three four') apply__db sqlInsWords
(('eleven';1),:('twelve';2))query__db'update WORDS SET WVAL=? WHERE WID=?;'
destroy__db''
FDIR=: jpath '~addons/sqlite/'
'FDB FDDL FDATA'=: cut 'date.sqlite date_ddl.sql date_data.sql '
ferase FDIR,FDB
1
]db=: 'psqlite'conew~ FDIR,FDB
+-+
|5|
+-+
exec__db fread FDIR,FDDL
0 0
exec__db fread FDIR,FDATA
2 58
strquery__db 'select count(*) as COUNT from S;'
+-----+
|COUNT|
+-----+
|5 |
+-----+
strquery__db 'select * from S;' [ limit__db 2 3
+---+-----+------+------+
|SID|SNAME|STATUS|CITY |
+---+-----+------+------+
|S3 |Blake|30 |Paris |
+---+-----+------+------+
|S4 |Clark|20 |London|
+---+-----+------+------+
strquery__db 'select * from P;'
+---+-----+-----+------+------+
|PID|PNAME|COLOR|WEIGHT|CITY |
+---+-----+-----+------+------+
|P1 |Nut |Red |12 |London|
+---+-----+-----+------+------+
|P2 |Bolt |Green|17 |Paris |
+---+-----+-----+------+------+
|P3 |Screw|Blue |17 |Oslo |
+---+-----+-----+------+------+
|P4 |Screw|Red |14 |London|
+---+-----+-----+------+------+
|P5 |Cam |Blue |12 |Paris |
+---+-----+-----+------+------+
|P6 |Cog |Red |19 |London|
+---+-----+-----+------+------+
strquery__db sqlSummary
+---+---+
|TAB|CNT|
+---+---+
|J |7 |
+---+---+
|P |6 |
+---+---+
|S |5 |
+---+---+
|SP |12 |
+---+---+
|SPJ|24 |
+---+---+
colquery__db 'select * from P;'
+---+-----+-----+------+------+
|PID|PNAME|COLOR|WEIGHT|CITY |
+---+-----+-----+------+------+
|P1 |Nut |Red |12 |London|
|P2 |Bolt |Green|17 |Paris |
|P3 |Screw|Blue |17 |Oslo |
|P4 |Screw|Red |14 |London|
|P5 |Cam |Blue |12 |Paris |
|P6 |Cog |Red |19 |London|
+---+-----+-----+------+------+
17 colquery__db 'select * from P where WEIGHT=?;'
+---+-----+-----+------+-----+
|PID|PNAME|COLOR|WEIGHT|CITY |
+---+-----+-----+------+-----+
|P2 |Bolt |Green|17 |Paris|
|P3 |Screw|Blue |17 |Oslo |
+---+-----+-----+------+-----+
('S5';17) colquery__db sqlSpjBySupplierAndWeight
+-----+-----+------+-------+------+
|SNAME|PNAME|WEIGHT|JNAME |CITY |
+-----+-----+------+-------+------+
|Adams|Bolt |17 |Display|Rome |
|Adams|Bolt |17 |Console|Athens|
|Adams|Screw|17 |Console|Athens|
+-----+-----+------+-------+------+
(14 17)colquery__db'select * from P where WEIGHT BETWEEN ? and ?;'
+---+-----+-----+------+------+
|PID|PNAME|COLOR|WEIGHT|CITY |
+---+-----+-----+------+------+
|P2 |Bolt |Green|17 |Paris |
|P3 |Screw|Blue |17 |Oslo |
|P4 |Screw|Red |14 |London|
+---+-----+-----+------+------+
strtbl__db'select * from SP where SID=''S1'';'
+---+---+---+
|SID|PID|QTY|
+---+---+---+
|S1 |P1 |300|
+---+---+---+
|S1 |P2 |200|
+---+---+---+
|S1 |P3 |400|
+---+---+---+
|S1 |P4 |200|
+---+---+---+
|S1 |P5 |100|
+---+---+---+
|S1 |P6 |100|
+---+---+---+
(12 17) query__db 'select * from P where WEIGHT=?;'
+---+-----+-----+------+------+
|PID|PNAME|COLOR|WEIGHT|CITY |
+---+-----+-----+------+------+
|P1 |Nut |Red |12 |London|
+---+-----+-----+------+------+
|P5 |Cam |Blue |12 |Paris |
+---+-----+-----+------+------+
|P2 |Bolt |Green|17 |Paris |
+---+-----+-----+------+------+
|P3 |Screw|Blue |17 |Oslo |
+---+-----+-----+------+------+
(;+/);}.query__db 'select WEIGHT from P;'
+-----------------+--+
|12 17 17 14 12 19|91|
+-----------------+--+
('P7';'Cog';'Red';19.0;'London') apply__db 'insert into P values (?,?,?,?,?);'
7
('test1';64{.a.) apply__db 'insert into PIC values (?,?);';0 1 NB. 2nd blob
1
(<'test1')query__db 'select * from PIC where PICNAME=?;'
+-------+----------------------------------------------------------------+
|PICNAME|PICVAL |
+-------+----------------------------------------------------------------+
|test1 | ┌┬┐├┼┤└+┘│─ !"#$%&'()*+,-./0123456789:;<=>?|
+-------+----------------------------------------------------------------+
(64{.a.)-:1 0{::(<'test1')query__db 'select PICVAL from PIC where PICNAME=?;'
1
colquery__db 'select * from WORDS;'
+---+-----+
|WID|WVAL |
+---+-----+
|1 |one |
|2 |two |
|3 |three|
|4 |four |
+---+-----+
(1 3) apply__db'delete from WORDS where WID=?;'
1 1
exec__db'delete from WORDS where 1;'
2 64
sqlInsWords=: 'insert into WORDS (WVAL) VALUES(?);'
(;:'one two three four') apply__db sqlInsWords
1 2 3 4
(('eleven';1),:('twelve';2))query__db'update WORDS SET WVAL=? WHERE WID=?;'
destroy__db''
1