sqlite 扩展：注册方法，变更监听

工具方法，扩展sqlite方法

import sqlite;
import raw;
import console;


if( ! ..___sqlite_dll__ ) error("myplu.sqliteEx禁止直接导入",2);

var dll = ..___sqlite_dll__; 

// 正确定义函数原型
var sqlite_update_hook = dll.api(
    "sqlite3_update_hook",
    "pointer(pointer db, pointer callback, pointer userdata)"
);

var sqlite_create_function = dll.api(
    "sqlite3_create_function",
    "int(pointer db, string zFunctionName, int nArg, int eTextRep, pointer pApp, pointer func, pointer step, pointer final)"
); 

sqlite.value_text = dll.api("sqlite3_value_text","string(pointer value)"); 
sqlite.value_int = dll.api("sqlite3_value_int","int(pointer value)"); 
sqlite.result_int = dll.api("sqlite3_result_int","void(pointer ctx, int value)"); 

sqlite.update_hook = function(db, callback){
    if(!db) return ;
    if(type(callback) != "function") return ;  
    // 回调函数定义
    var update_hook_callback = raw.tocdecl(callback, "void(pointer userdata, int op_type, pointer dbname, pointer tblname, int64 rowid)");
    sqlite_update_hook(db,update_hook_callback);
}
sqlite.create_function = function(db, funcName, argSize, callback){ 
    if(!db) return ;
    if(!funcName) return ;
    if(!argSize) return ;
    if(type(callback) != "function") return ;  
    // 回调函数定义
    var create_function_callback = raw.tocdecl(callback, "void(pointer ctx, int argc, pointer argv)");
    sqlite_create_function(db, funcName, argSize, 0, , create_function_callback);
}

demo1:注册正则查询函数到sqlite

import console;
import myplu.sqliteEx


var db = ..sqlite(":memory:"); 

/* 注册正则函数 */
..sqlite.create_function(db, "REGEXP", 2, function(ctx, argc, argv) {
    /* 数组指针 */
    var args = ..raw.convertArray(argv, argc);
    /* 数组值 */
    var text = ..sqlite.value_text(args[1]);
    var pattern = ..sqlite.value_text(args[2]);
    
    if (!pattern || !text) {
        ..sqlite.result_int(ctx, 0);
        return;
    }

    var isMatch = ..string.find(text, pattern);
    ..sqlite.result_int(ctx, isMatch ? 1 : 0);
});

// 测试代码
db.exec("CREATE TABLE test ( name TEXT);");
db.exec("INSERT INTO test(name) VALUES('测试数据1')");
db.exec("INSERT INTO test(name) VALUES('测试数据2')");
db.exec("INSERT INTO test(name) VALUES('测试数据3')");
db.exec("INSERT INTO test(name) VALUES('测试数据4')");
db.exec("INSERT INTO test(name) VALUES('测试数据5')");
a = db.getTable("select * from test where REGEXP(name,'2')");
console.dump(a);
db.exec("INSERT INTO test(name) VALUES('测试数据5')");
console.pause();

demo2：监听数据表变更

import myplu.sqliteEx;
import console;

var db = ..sqlite(":memory:"); 
..sqlite.update_hook(db, function(userdata, op_type, dbname, tblname, rowid){
        var databaseName = raw.tostring(dbname);
        var tableName = raw.tostring(tblname);
        
        /* 操作类型映射 */
        var opMap = {
            [9] = "DELETE",
            [23] = "UPDATE",
            [18] = "INSERT"
        };
        
        
        a = db.getTable("SELECT * FROM [" ++ tableName ++ "] WHERE ROWID = " ++ rowid);
        if(..table.len(a)){
            ..console.dump(a[1]);
        }

        
        console.log(
            "数据库:", databaseName,
            "表:", tableName,
            "操作:", opMap[op_type] || "未知操作",
            "ROWID:", rowid
        );
    });
    
// 测试操作
db.exec("CREATE TABLE test ( name TEXT);");
db.exec("INSERT INTO test(name) VALUES('测试数据1')");
db.exec("INSERT INTO test(name) VALUES('测试数据2')");
db.exec("INSERT INTO test(name) VALUES('测试数据3')");
db.exec("INSERT INTO test(name) VALUES('测试数据4')");
db.exec("INSERT INTO test(name) VALUES('测试数据5')");
db.exec("UPDATE test SET name='修改数据6' WHERE ROWID=1");
db.exec("UPDATE test SET name='修改数据7' WHERE ROWID=6");
db.exec("UPDATE test SET name='修改数据8' WHERE ROWID=4");
db.exec("UPDATE test SET name='修改数据9' WHERE ROWID=3");
db.exec("UPDATE test SET name='修改数据10' WHERE ROWID=2");
db.exec("DELETE FROM test WHERE ROWID=1");