Hello !
Sqlite is really a very nice piece of software and it could even be better,
here is things to add to the wish list:
- Implement sql "CREATE FUNCTION" even if it is as simple of only allowing a
limited set of operations, refactoring the actual trigger implementation to
allow it be called with parameters, with this we could also have generic
trigger functions that could be applied to more than one table/view in a
"DRY" way, see postgresql.
- Implement "PREPARE/EXECUTE/DEALLOC" like the previous wish several times a
view with parameters can make life a lot "DRY" easier.
____ example from a postgresql function
CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
DECLARE
v_cost float;
v_parts_id alias for $1;
BEGIN
? SELECT INTO v_cost sellprice FROM invoice i
? JOIN ap a ON (a.id = i.trans_id)
? WHERE i.parts_id = v_parts_id
? ORDER BY a.transdate desc, a.id desc
? LIMIT 1;
? IF v_cost IS NULL THEN
??? v_cost := 0;
? END IF;
RETURN v_cost;
END;
' language plpgsql;
____
The above function could be done with an even simple implementation of
"PREPARE" like this:
_______ as PREPARE (view with parameters, without parameters I don't know how
to parametrize this because of the inner variable)
PREPARE lastcost(int) AS
SELECT IFNULL(sellprice, 0) AS v_cost FROM invoice i
? JOIN ap a ON (a.id = i.trans_id)
? WHERE i.parts_id = $1
? ORDER BY a.transdate desc, a.id desc
? LIMIT 1;
_______
?
- Sqlite "C" interface function to return rows, something like: void
sqlite3_result_sql(sqlite3_context*, const char* sql, int sql_size,
void(*)(void*));
To be used like this (pseudo code):
void my_sqlite_eval_function(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
const char *ztable_name = (const char*)sqlite3_value_text(argv[0]);
char *sql = sqlite3_mprintf("select name, value from %s",ztable_name);
? sqlite3_result_sql(context, sql, -1, sqlite3_free);
}
Behind scenes it basically could be implemented as sqlite3_prepare that
extends/add new opcode and execute it after the function call.
- Also like before about generic trigger functions a "C" interface to
register "C" functions as triggers.
- Meta programming ansi catalog, it's strange that sqlite stores it's catalog
information in a normal hidden table, but do not do the same with other
parsed elements like "PRAGMA table_info", "PRAGMA foreign_keys", "PRAGMA
collation_list", "PRAGMA index_info", would be nice if at least sqlite
provides virtual views for this information that could be used through sql
queries (I mean joining with other tables to achieve several results).
Storing the parsed elements in a normalized catalog would permit things like
"ALTER TABLE RENAME COLUMN", "ALTER TABLE ADD CONSTRAINT", ..., and also when
renaming a table the views that reference it could be updated properly (or
not need update at all if it's recovered from join querying the catalog).
?
Cheers !
