mpsqlite [-m output] [-s] [-v] db-path query

Runs the provided query on the sqlite3 file at db-path. The query is interpreted in SSQL syntax unless the -s flag is given, whereupon it's a raw SQL string. Each record in the result is output as an alist. If -v is specified, the raw SQL query is printed to standard error before execution.

Examples

mpsqlite test.sqlite '(select * (from foods))'
=>
((id . 1) (name . "Fish"))
((id . 2) (name . "Potato"))
((id . 3) (name . "Carrot"))

mpsqlite -m input [-s] [-v] db-path query

For each input s-expression, runs the provided query on the sqlite3 file at db-path. The query is interpreted in SSQL syntax unless the -s flag is given, whereupon it's a raw SQL string. Placeholders (?) in the query are replaced with corresponding elements from each input s-expression, which must be a list. The results of each query are output as a list of alists. If -v is specified, the raw SQL queries are output before execution.

Examples

echo '(1) (2)' | mpsqlite -m input test.sqlite '(delete (from foods) (where (= id ?)))'
=>
(
)
(
)
echo '(3) (4)' | mpsqlite -m input test.sqlite '(select * (from foods) (where (= id ?)))'
=>
(
 ((id . 3) (name . "Carrot"))
 ((id . 4) (name . "Cake"))
)

mpsqlite [-m insert|replace] [-v] db-path table

Inserts (or replaces) records into the given table on the sqlite3 file at db-path. Each record read from the input is an alist. In insert mode, they are converted into INSERT statements, which will fail on a primary-key collision; in replace mode, they are converted into REPLACE statements, which will overwrite the existing records on a primary-key collision. For each query executed, the rowid created/updated is output. If -v is specified, the raw SQL query is printed to standard error before execution.

Examples

echo '((id . 10) (name . "Pie")) ((id . 11) (name . "Egg"))' |
    mpsqlite -m insert test.sqlite foods

mpsqlite -m update [-v] db-path table keys...

Updates records in the given table on the sqlite3 file at db-path. Each record read from the input is an alist. They are converted into UPDATE statements; the columns listed in the keys... on the command line are converted into equality tests in the WHERE clause, while the other columns are put into the SET clause. For each query executed, the number of rows updated is output. If -v is specified, the raw SQL query is printed to standard error before execution.

Examples

echo '((id . 10) (name . "Pastie"))' |
    mpsqlite -v -m update test.sqlite foods id
=> (standard error)
SQL: "UPDATE foods SET name = ? WHERE ((id = ?))" ("Pastie" 10)
=> (standard output)
1