SQLite support

[rigrig]

Could we have something like
#sqlite <filename> <statement> [parameters] [variable]?

Example usage would be something like

#nop simple statements;
#sqlite settings.db {CREATE TABLE settings(setting TEXT, value TEXT)};
#sqlite settings.db {INSERT INTO settings(setting, value) VALUES ('test', 123)};
#nop named parameters;
#sqlite settings.db {INSERT INTO settings(setting, value) VALUES (:setting, :value)} {:setting} {foo} {:value} {bar baz};
#nop numbered parameters;
#sqlite settings.db {INSERT INTO settings(setting, value) VALUES (?, ?)} {answer} {42};
#nop saving the result to a variable;
#sqlite settings.db {SELECT * FROM settings WHERE setting != :exclude} {:exclude} {test} {settings};
#var settings;

#VARIABLE {settings}
{
    {1} 
    {
        {setting} {foo}
        {value} {bar baz}
    }
    {2} 
    {
        {setting} {answer}
        {value} {42}
    }
}

Parsing arguments might be a bit tricky, but after you prepare a statement you can get a list of parameters, so I figure it should be doable:

1. prepare the statement
2. get the list of parameters
3. for all named parameters: look for {:name} {value} pairs, bind the value and remove them from the argument list
4. for all unnamed parameters: bind and remove the first value from the argument list
5. if there are any argument left over: use the last one as output variable

Or maybe alternatively

#list params clear;
#list params add foo;
#list params add {bar baz};
#list params add answer;
#list params add 42;
#sqlite settings.db {INSERT INTO settings(setting, value) VALUES (?,?), (?,?)} $params;
#var params;
#var params[setting] hello;
#var params[value] world;
#sqlite settings.db {INSERT INTO settings(setting, value) VALUES (:setting, :value)} $params;
#sqlite settings.db {SELECT * FROM settings WHERE setting != :exclude} {{{exclude}{test}}} {settings};
#var settings;

#VARIABLE {settings}
{
    {1} 
    {
        {setting} {foo}
        {value} {bar baz}
    }
    {2} 
    {
        {setting} {answer}
        {value} {42}
    }
    {3} 
    {
        {setting} {hello}
        {value} {world}
    }
}

At the moment I'm shelling out to sqlite3, which kind of works, but isn't ideal.

[scandum]

Not really something I want to mess with. You can use:

#list settings insert -1 {{settings}{answer} {value}{42}}

#list has a few sql-like features as well.

The indexate option prepares a table or list table for order, sort,
filter, refine, and find operations for the given key. It is similar
to the SELECT option in SQL. All entries must contain the given key.

[rigrig]

Fair enough, thanks for the reply.
(#list won't work as I've got an external database to query, but I guess shelling out will do.)