Sqlite.xtl

1⍝# Sqlite.xtl -- the facade of the sqlite extension: SQLite databases
2⍝# for X_eTaL (rusqlite, SQLite compiled in).
3⍝# Import: "sq:" u_se< "Sqlite"
4⍝# A database is named by its path, relative to the working directory
5⍝# (or XETAL_SQLITE_ROOT), or ":memory:" for one call's scratch database;
6⍝# each call opens it and closes it.
7⍝# Run with xetal-x (xetal-x --ext DIR puts this directory on XETAL_PATH).
8⍝# Each signature line is written out by the binding macro (lib/Ffi.xtlm).
9
10ᶠᶠⁱ⁼u̲se< "Ffi"
11
12⍝# db sq:e_xec sql: run the statements; how many rows they changed.
13"e_xec : text text -> int"      ᶠᶠⁱb̲ind< "sqlite/exec"
ᶠᶠⁱb̲ind< expands toˡe̲xec ← { a b →
  pa ← a ⎕N̲PUT "ext:sqlite/exec?text"
  pb ← b ⎕N̲PUT "ext:sqlite/exec?text"
  f̲loor f̲irst n̲umbers ⎕N̲GET "ext:sqlite/exec"
}
14⍝# db sq:n_ums sql: the result as a Float matrix, rows by columns (NULL is NaN).
15"n_ums : text text -> floats"   ᶠᶠⁱb̲ind< "sqlite/nums"
ᶠᶠⁱb̲ind< expands toˡn̲ums ← { a b →
  pa ← a ⎕N̲PUT "ext:sqlite/nums?text"
  pb ← b ⎕N̲PUT "ext:sqlite/nums?text"
  v ← n̲umbers ⎕N̲GET "ext:sqlite/nums"
  (f̲loor n̲umbers ⎕N̲GET "ext:sqlite/nums?shape") r̲eshape v
}
16⍝# db sq:t_exts sql: every cell as text, a Char matrix with one row per
17⍝# cell (the first row's cells, then the second's; padded with spaces).
18"t_exts : text text -> chars"   ᶠᶠⁱb̲ind< "sqlite/texts"
ᶠᶠⁱb̲ind< expands toˡt̲exts ← { a b →
  pa ← a ⎕N̲PUT "ext:sqlite/texts?text"
  pb ← b ⎕N̲PUT "ext:sqlite/texts?text"
  v ← ⎕N̲GET "ext:sqlite/texts"
  (f̲loor n̲umbers ⎕N̲GET "ext:sqlite/texts?shape") r̲eshape v
}
19⍝# db sq:c_ols sql: the query's column names, one per row.
20"c_ols : text text -> chars"    ᶠᶠⁱb̲ind< "sqlite/cols"
ᶠᶠⁱb̲ind< expands toˡc̲ols ← { a b →
  pa ← a ⎕N̲PUT "ext:sqlite/cols?text"
  pb ← b ⎕N̲PUT "ext:sqlite/cols?text"
  v ← ⎕N̲GET "ext:sqlite/cols"
  (f̲loor n̲umbers ⎕N̲GET "ext:sqlite/cols?shape") r̲eshape v
}
21⍝# sq:q_uote t: the text as an SQL literal ('O''Brien'), to put a value
22⍝# into a statement as data, never as SQL.
23"q_uote : text -> text"         ᶠᶠⁱb̲ind< "sqlite/quote"
ᶠᶠⁱb̲ind< expands toˡq̲uote ← { a →
  pa ← a ⎕N̲PUT "ext:sqlite/quote?text"
  ⎕N̲GET "ext:sqlite/quote"
}
24⍝# db sq:i_mport "path.csv" (or "table=path.csv"): a CSV file into a new
25⍝# table named after the file; the header names the columns, their types
26⍝# (INTEGER, REAL, TEXT) come from the data; empty fields are NULL. How
27⍝# many rows it imported.
28"i_mport : text text -> int"    ᶠᶠⁱb̲ind< "sqlite/import"
ᶠᶠⁱb̲ind< expands toˡi̲mport ← { a b →
  pa ← a ⎕N̲PUT "ext:sqlite/import?text"
  pb ← b ⎕N̲PUT "ext:sqlite/import?text"
  f̲loor f̲irst n̲umbers ⎕N̲GET "ext:sqlite/import"
}
29
30⍝# The native package behind these functions.
31ˡpackage ← "sqlite"