Hello !
I did for one project a kind of sqlite3 extension using UNQL
(http://unql.sqlite.org/index.html/wiki?name=UnQL) it has some flaws (and I
did some bug fixes) like only performing linear scan on collections, but now
with index expressions support on sqlite3 it could be revived back with
indexes and work very well, I say this because UNQL has a lot of sugar to
work with json.
With something like this sqlite will even be more usefull on a lot more
projects.
Anyway to revive UNQL and make it as extension to sqlite officially ?
Cheers !
Example:
________output of "test-unql-json.nut"
cyan
cyan
{"color":"cyan","has_eyes":true}
{"id":1,"name":"Domingo","color":"cyan","has_eyes":true}
________
________test-unql-json.nut
local function unql_exec(db, sql)
{
??? local stmt = db.prepare(sql);
??? local rc = stmt.step();
??? stmt.finalize();
??? return rc;
}
local function unql_exec_get_one(db, sql)
{
??? local stmt = db.prepare(sql);
??? local rc = stmt.step();
??? local value = stmt.value();
??? stmt.finalize();
??? return value;
}
local sqlite3_db = SQLite3("unql_json.db");
local unql_db = xjd1(sqlite3_db);
sqlite3_db.exec_dml("DROP TABLE IF EXISTS users;");
sqlite3_db.exec_dml("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY,
name TEXT, json_misc TEXT);");
sqlite3_db.exec_dml("CREATE VIEW IF NOT EXISTS users_unql AS SELECT json_misc
AS x FROM users;");
sqlite3_db.exec_dml([==[INSERT INTO users(id, name, json_misc) VALUES(1,
'Domingo', '{"color":"cyan", "has_eyes": true}')]==]);
unql_exec(unql_db, "DROP COLLECTION unql_users;");
unql_exec(unql_db, "CREATE COLLECTION unql_users;");
local rc = unql_exec(unql_db, [==[INSERT INTO unql_users VALUE {"id":1,
"name":"Domingo", "color":"cyan", "has_eyes": true}]==]);
print(sqlite3_db.exec_get_one("SELECT json_extract(json_misc, '$.color') AS
color FROM users"));
print(sqlite3_db.exec_get_one("SELECT json_extract(x, '$.color') AS color
FROM unql_users"));
print(unql_exec_get_one(unql_db, "SELECT FROM users_unql WHERE
users_unql.color ==? \"cyan\";"));
print(unql_exec_get_one(unql_db, "SELECT FROM unql_users WHERE
unql_users.color ==? \"cyan\";"));
//print(unql_exec_get_one(unql_db, "SELECT FROM unql_users WHERE
json_extract(unql_users, \"$.color\") ==? \"cyan\";"));
unql_db.close();
sqlite3_db.close();
________
