Sqlite数据库

import com.m8test.script.GlobalVariables.*

/**
 * Sqlite 与 SqliteManager 接口测试脚本
 * 全局变量: _sqliteManager
 */

// 辅助函数
fun assertLog(name: String, actual: Any?, expected: Any?) {
    if (actual == expected) {
        _console.log("✅ [$name] Passed. Result: $actual")
    } else {
        _console.error("❌ [$name] Failed.\n   Expected: $expected\n   Actual  : $actual")
    }
}

_console.log("🚀 开始测试 Sqlite 数据库模块...")

// ==========================================
// 1. 准备工作 & SqliteManager 测试
// ==========================================
val dbName = "test_m8_data.db"
val tableName = "users"

// 确保测试前环境干净
if (_sqliteManager.exists(dbName)) {
    _sqliteManager.delete(dbName)
}
assertLog("exists (初始状态)", _sqliteManager.exists(dbName), false)

// 打开/创建数据库
// 此时会返回一个 Sqlite 接口对象
val db = _sqliteManager.open(dbName)

if (db != null && db.isOpen()) {
    _console.log("✅ 数据库打开成功: $dbName")
} else {
    _console.error("❌ 数据库打开失败")
    // 这里用 return@file 或直接 throw 异常，kts 中直接 return 可能会有上下文限制，
    // 但作为脚本顶层可以直接停止执行，这里为了代码块完整性不做强制退出，但逻辑上应该退出
    throw RuntimeException("数据库打开失败")
}

// 验证文件现在是否存在
assertLog("exists (打开后)", _sqliteManager.exists(dbName), true)

// ==========================================
// 2. 表结构操作 (DDL)
// ==========================================

// createTable
// 定义列: id 自增主键, name 文本, age 整数, score 浮点
// Kotlin Map 语法
val columns = mapOf(
    "id" to "INTEGER PRIMARY KEY AUTOINCREMENT",
    "name" to "TEXT NOT NULL",
    "age" to "INTEGER",
    "score" to "REAL"
)

val createRes = db.createTable(tableName, columns)
assertLog("createTable", createRes, true)

// hasTable
assertLog("hasTable", db.hasTable(tableName), true)

// ==========================================
// 3. 数据操作 (CRUD)
// ==========================================

// --- Insert ---
_console.log("\n--- 测试 Insert ---")
val user1 = mapOf("name" to "Alice", "age" to 25, "score" to 88.5)
val rowId1 = db.insert(tableName, null, user1)
_console.log("插入 Alice, RowID: " + rowId1)

val user2 = mapOf("name" to "Bob", "age" to 30, "score" to 92.0)
val rowId2 = db.insert(tableName, null, user2)
_console.log("插入 Bob, RowID: " + rowId2)

if (rowId1 != -1L && rowId2 != -1L) { // 假设 rowId 返回 Long 或 Int
    _console.log("✅ Insert 成功")
} else {
    _console.error("❌ Insert 失败")
}

// --- Query ---
_console.log("\n--- 测试 Query ---")
// 注意：query 方法的 bindArgs 需要 String 数组
val querySql = "SELECT * FROM $tableName WHERE name = ?"
// Kotlin 中 arrayOf 创建数组
val results = db.query(querySql, arrayOf("Alice"))

if (results.size > 0) {
    val user = results[0]
    _console.log("查询结果: " + user)
    // 验证数据 (注意：接口返回的值统一为 String，所以对比字符串 "25")
    if (user["name"] == "Alice" && user["age"] == "25") {
        _console.log("✅ Query 数据验证通过")
    } else {
        _console.error("❌ Query 数据验证失败")
    }
} else {
    _console.error("❌ Query 未找到数据")
}

// --- Update ---
_console.log("\n--- 测试 Update ---")
val updateValues = mapOf("score" to 95.5) // 修改 Alice 的分数
val rowsAffected = db.update(tableName, updateValues, "name = ?", arrayOf("Alice"))
assertLog("update (受影响行数)", rowsAffected, 1)

// 验证更新是否生效
val verifyUpdate = db.query("SELECT score FROM $tableName WHERE name = 'Alice'", null)
if (verifyUpdate[0]["score"] == "95.5") {
    _console.log("✅ Update 生效验证通过")
} else {
    _console.error("❌ Update 生效验证失败, 实际值: " + verifyUpdate[0]["score"])
}

// --- Delete ---
_console.log("\n--- 测试 Delete ---")
val deleteRows = db.delete(tableName, "name = ?", arrayOf("Bob"))
assertLog("delete (受影响行数)", deleteRows, 1)

// ==========================================
// 4. 原生 SQL 与 事务 (Transaction)
// ==========================================
_console.log("\n--- 测试 Transaction & execSQL ---")

db.beginTransaction()
try {
    // 使用 execSQL 插入一条数据
    // 注意：bindArgs 是 Array<Any?>，可以传 int/string 等
    val sql = "INSERT INTO $tableName (name, age, score) VALUES (?, ?, ?)"
    db.execSQL(sql, arrayOf<Any?>("Charlie", 20, 60.0))

    // 标记事务成功
    db.setTransactionSuccessful()
    _console.log("✅ 事务执行完毕 (Committed)")
} catch (e: Exception) {
    _console.error("❌ 事务执行异常: " + e)
} finally {
    // 结束事务（如果之前没调用 setTransactionSuccessful，这里会自动回滚）
    db.endTransaction()
}

// 验证 Charlie 是否存在
val charlieRes = db.query("SELECT * FROM $tableName WHERE name = 'Charlie'", null)
assertLog("事务插入验证", charlieRes.size, 1)

// ==========================================
// 5. 清理与关闭
// ==========================================
_console.log("\n--- 测试 清理操作 ---")

// dropTable
assertLog("dropTable", db.dropTable(tableName), true)
assertLog("hasTable (删除后)", db.hasTable(tableName), false)

// 关闭连接
db.close()
assertLog("isOpen (关闭后)", db.isOpen(), false)

// 删除数据库文件
val deleteDbRes = _sqliteManager.delete(dbName)
assertLog("SqliteManager.delete", deleteDbRes, true)
assertLog("exists (最终检查)", _sqliteManager.exists(dbName), false)

_console.log("🎉 Sqlite 接口测试完毕。")

_script.getThreads().getMain().setBackground(true)

/**
 * Sqlite 与 SqliteManager 接口测试脚本
 * 全局变量: $sqliteManager
 */

$console.log("🚀 开始测试 Sqlite 数据库模块...")

// ==========================================
// 1. 准备工作 & SqliteManager 测试
// ==========================================
def dbName = "test_m8_data.db"
def tableName = "users"

// 确保测试前环境干净
if ($sqliteManager.exists(dbName)) {
    $sqliteManager.delete(dbName)
}
assertLog("exists (初始状态)", $sqliteManager.exists(dbName), false)

// 打开/创建数据库
// 此时会返回一个 Sqlite 接口对象
def db = $sqliteManager.open(dbName)

if (db != null && db.isOpen()) {
    $console.log("✅ 数据库打开成功: ${dbName}")
} else {
    $console.error("❌ 数据库打开失败")
    return // 无法继续
}

// 验证文件现在是否存在
assertLog("exists (打开后)", $sqliteManager.exists(dbName), true)

// ==========================================
// 2. 表结构操作 (DDL)
// ==========================================

// createTable
// 定义列: id 自增主键, name 文本, age 整数, score 浮点
def columns = [
        "id": "INTEGER PRIMARY KEY AUTOINCREMENT",
        "name": "TEXT NOT NULL",
        "age": "INTEGER",
        "score": "REAL"
]

def createRes = db.createTable(tableName, columns)
assertLog("createTable", createRes, true)

// hasTable
assertLog("hasTable", db.hasTable(tableName), true)

// ==========================================
// 3. 数据操作 (CRUD)
// ==========================================

// --- Insert ---
$console.log("\n--- 测试 Insert ---")
def user1 = ["name": "Alice", "age": 25, "score": 88.5]
def rowId1 = db.insert(tableName, null, user1)
$console.log("插入 Alice, RowID: " + rowId1)

def user2 = ["name": "Bob", "age": 30, "score": 92.0]
def rowId2 = db.insert(tableName, null, user2)
$console.log("插入 Bob, RowID: " + rowId2)

if (rowId1 != -1 && rowId2 != -1) {
    $console.log("✅ Insert 成功")
} else {
    $console.error("❌ Insert 失败")
}

// --- Query ---
$console.log("\n--- 测试 Query ---")
// 注意：query 方法的 bindArgs 需要 String 数组
def querySql = "SELECT * FROM $tableName WHERE name = ?"
// 根据接口定义，返回值 Map 的 Value 都是 String 类型
def results = db.query(querySql, ["Alice"] as String[])

if (results.size() > 0) {
    def user = results[0]
    $console.log("查询结果: " + user)
    // 验证数据 (注意：接口返回的值统一为 String，所以对比字符串 "25")
    if (user["name"] == "Alice" && user["age"] == "25") {
        $console.log("✅ Query 数据验证通过")
    } else {
        $console.error("❌ Query 数据验证失败")
    }
} else {
    $console.error("❌ Query 未找到数据")
}

// --- Update ---
$console.log("\n--- 测试 Update ---")
def updateValues = ["score": 95.5] // 修改 Alice 的分数
def rowsAffected = db.update(tableName, updateValues, "name = ?", ["Alice"] as String[])
assertLog("update (受影响行数)", rowsAffected, 1)

// 验证更新是否生效
def verifyUpdate = db.query("SELECT score FROM $tableName WHERE name = 'Alice'", null)
if (verifyUpdate[0]["score"] == "95.5") {
    $console.log("✅ Update 生效验证通过")
} else {
    $console.error("❌ Update 生效验证失败, 实际值: " + verifyUpdate[0]["score"])
}

// --- Delete ---
$console.log("\n--- 测试 Delete ---")
def deleteRows = db.delete(tableName, "name = ?", ["Bob"] as String[])
assertLog("delete (受影响行数)", deleteRows, 1)

// ==========================================
// 4. 原生 SQL 与 事务 (Transaction)
// ==========================================
$console.log("\n--- 测试 Transaction & execSQL ---")

db.beginTransaction()
try {
    // 使用 execSQL 插入一条数据
    // 注意：bindArgs 是 Array<Any?>，可以传 int/string 等
    def sql = "INSERT INTO $tableName (name, age, score) VALUES (?, ?, ?)"
    db.execSQL(sql, ["Charlie", 20, 60.0] as Object[])

    // 标记事务成功
    db.setTransactionSuccessful()
    $console.log("✅ 事务执行完毕 (Committed)")
} catch (Exception e) {
    $console.error("❌ 事务执行异常: " + e)
} finally {
    // 结束事务（如果之前没调用 setTransactionSuccessful，这里会自动回滚）
    db.endTransaction()
}

// 验证 Charlie 是否存在
def charlieRes = db.query("SELECT * FROM $tableName WHERE name = 'Charlie'", null)
assertLog("事务插入验证", charlieRes.size(), 1)

// ==========================================
// 5. 清理与关闭
// ==========================================
$console.log("\n--- 测试 清理操作 ---")

// dropTable
assertLog("dropTable", db.dropTable(tableName), true)
assertLog("hasTable (删除后)", db.hasTable(tableName), false)

// 关闭连接
// Sqlite 继承自 ScriptCloseable，通常有关闭方法
db.close()
assertLog("isOpen (关闭后)", db.isOpen(), false)

// 删除数据库文件
def deleteDbRes = $sqliteManager.delete(dbName)
assertLog("SqliteManager.delete", deleteDbRes, true)
assertLog("exists (最终检查)", $sqliteManager.exists(dbName), false)

$console.log("🎉 Sqlite 接口测试完毕。")

// ==========================================
// 辅助函数
// ==========================================
def assertLog(name, actual, expected) {
    if (actual == expected) {
        $console.log("✅ [${name}] Passed. Result: ${actual}")
    } else {
        $console.error("❌ [${name}] Failed.\n   Expected: ${expected}\n   Actual  : ${actual}")
    }
}

$script.getThreads().getMain().setBackground(true)

/**
 * Sqlite 与 SqliteManager 接口测试脚本
 * 全局变量: $sqliteManager, $reflectors, $arrays, $maps
 */

$console.log("🚀 开始测试 Sqlite 数据库模块...");

// ==========================================
// 0. 准备 Class 对象
// ==========================================
// 修正：使用 $reflectors 获取 Class 对象，避免硬编码
const StringClass = $reflectors.reflectClassName("java.lang.String").getTargetClass();
const ObjectClass = $reflectors.reflectClassName("java.lang.Object").getTargetClass();


// ==========================================
// 1. 准备工作 & SqliteManager 测试
// ==========================================
const dbName = "test_m8_data.db";
const tableName = "users";

// 确保测试前环境干净
if ($sqliteManager.exists(dbName) == true) {
    $sqliteManager.delete(dbName);
}
assertLog("exists (初始状态)", $sqliteManager.exists(dbName), false);

// 打开/创建数据库
const db = $sqliteManager.open(dbName);

if (db != null && db.isOpen() == true) {
    $console.log("✅ 数据库打开成功: " + dbName);
} else {
    $console.error("❌ 数据库打开失败");
    throw new Error("数据库打开失败，测试终止");
}

// 验证文件现在是否存在
assertLog("exists (打开后)", $sqliteManager.exists(dbName), true);

// ==========================================
// 2. 表结构操作 (DDL)
// ==========================================

// createTable
const columns = $maps.mapOf(
    $maps.pairOf("id", "INTEGER PRIMARY KEY AUTOINCREMENT"),
    $maps.pairOf("name", "TEXT NOT NULL"),
    $maps.pairOf("age", "INTEGER"),
    $maps.pairOf("score", "REAL")
);

const createRes = db.createTable(tableName, columns);
assertLog("createTable", createRes, true);

// hasTable
assertLog("hasTable", db.hasTable(tableName), true);

// ==========================================
// 3. 数据操作 (CRUD)
// ==========================================

// --- Insert ---
$console.log("\n--- 测试 Insert ---");
const user1 = $maps.mapOf($maps.pairOf("name", "Alice"), $maps.pairOf("age", 25), $maps.pairOf("score", 88.5));
const rowId1 = db.insert(tableName, null, user1);
$console.log("插入 Alice, RowID: " + rowId1);

const user2 = $maps.mapOf($maps.pairOf("name", "Bob"), $maps.pairOf("age", 30), $maps.pairOf("score", 92.0));
const rowId2 = db.insert(tableName, null, user2);
$console.log("插入 Bob, RowID: " + rowId2);

if (rowId1 != -1 && rowId2 != -1) {
    $console.log("✅ Insert 成功");
} else {
    $console.error("❌ Insert 失败");
}

// --- Query ---
$console.log("\n--- 测试 Query ---");
const querySql = "SELECT * FROM " + tableName + " WHERE name = ?";
const results = db.query(querySql, $arrays.arrayOf(StringClass, "Alice"));

if (results.size() > 0) {
    const user = results.get(0);
    $console.log("查询结果: " + user);
    if (user.get("name") == "Alice" && user.get("age") == "25") {
        $console.log("✅ Query 数据验证通过");
    } else {
        $console.error("❌ Query 数据验证失败");
    }
} else {
    $console.error("❌ Query 未找到数据");
}

// --- Update ---
$console.log("\n--- 测试 Update ---");
const updateValues = $maps.mapOf($maps.pairOf("score", 95.5));
const rowsAffected = db.update(tableName, updateValues, "name = ?", $arrays.arrayOf(StringClass, "Alice"));
assertLog("update (受影响行数)", rowsAffected, 1);

// 验证更新是否生效
const verifyUpdate = db.query("SELECT score FROM " + tableName + " WHERE name = 'Alice'", null);
if (verifyUpdate.get(0).get("score") == "95.5") {
    $console.log("✅ Update 生效验证通过");
} else {
    $console.error("❌ Update 生效验证失败, 实际值: " + verifyUpdate.get(0).get("score"));
}

// --- Delete ---
$console.log("\n--- 测试 Delete ---");
const deleteRows = db.delete(tableName, "name = ?", $arrays.arrayOf(StringClass, "Bob"));
assertLog("delete (受影响行数)", deleteRows, 1);

// ==========================================
// 4. 原生 SQL 与 事务 (Transaction)
// ==========================================
$console.log("\n--- 测试 Transaction & execSQL ---");

db.beginTransaction();
try {
    const sql = "INSERT INTO " + tableName + " (name, age, score) VALUES (?, ?, ?)";
    db.execSQL(sql, $arrays.arrayOf(ObjectClass, "Charlie", 20, 60.0));
    db.setTransactionSuccessful();
    $console.log("✅ 事务执行完毕 (Committed)");
} catch (e) {
    $console.error("❌ 事务执行异常: " + e);
} finally {
    db.endTransaction();
}

// 验证 Charlie 是否存在
const charlieRes = db.query("SELECT * FROM " + tableName + " WHERE name = 'Charlie'", null);
assertLog("事务插入验证", charlieRes.size(), 1);

// ==========================================
// 5. 清理与关闭
// ==========================================
$console.log("\n--- 测试 清理操作 ---");

// dropTable
assertLog("dropTable", db.dropTable(tableName), true);
assertLog("hasTable (删除后)", db.hasTable(tableName), false);

// 关闭连接
db.close();
assertLog("isOpen (关闭后)", db.isOpen(), false);

// 删除数据库文件
const deleteDbRes = $sqliteManager.delete(dbName);
assertLog("SqliteManager.delete", deleteDbRes, true);
assertLog("exists (最终检查)", $sqliteManager.exists(dbName), false);

$console.log("🎉 Sqlite 接口测试完毕。");

// ==========================================
// 辅助函数
// ==========================================
/**
 * @param {string} name
 * @param {any} actual
 * @param {any} expected
 */
function assertLog(name, actual, expected) {
    if (actual == expected) {
        $console.log("✅ [" + name + "] Passed. Result: " + actual);
    } else {
        $console.error("❌ [" + name + "] Failed.\n   Expected: " + expected + "\n   Actual  : " + actual);
    }
}

$script.getThreads().getMain().setBackground(true);

-- 文件名: 5.lua

-- 引入转换所需的 Java 类
local String = require("m8test_java.java.lang.String")
local Object = require("m8test_java.java.lang.Object")

---
-- Sqlite 与 SqliteManager 接口测试脚本
-- 全局变量: _sqliteManager
--

_console:log("🚀 开始测试 Sqlite 数据库模块...")

-- ==========================================
-- 辅助函数
-- ==========================================
local function assertLog(name, actual, expected)
    if actual == expected then
        _console:log("✅ [" .. name .. "] Passed. Result: " .. tostring(actual))
    else
        _console:error("❌ [" .. name .. "] Failed.\n   Expected: " .. tostring(expected) .. "\n   Actual  : " .. tostring(actual))
    end
end


-- ==========================================
-- 1. 准备工作 & SqliteManager 测试
-- ==========================================
local dbName = "test_m8_data.db"
local tableName = "users"

-- 确保测试前环境干净
if _sqliteManager:exists(dbName) then
    _sqliteManager:delete(dbName)
end
assertLog("exists (初始状态)", _sqliteManager:exists(dbName), false)

-- 打开/创建数据库
local db = _sqliteManager:open(dbName)

if db ~= nil and db:isOpen() then
    _console:log("✅ 数据库打开成功: " .. dbName)
else
    _console:error("❌ 数据库打开失败")
    return -- 无法继续
end

-- 验证文件现在是否存在
assertLog("exists (打开后)", _sqliteManager:exists(dbName), true)

-- ==========================================
-- 2. 表结构操作 (DDL)
-- ==========================================

-- createTable
local columns = _maps:mutableMapOf(
    _maps:pairOf("id", "INTEGER PRIMARY KEY AUTOINCREMENT"),
    _maps:pairOf("name", "TEXT NOT NULL"),
    _maps:pairOf("age", "INTEGER"),
    _maps:pairOf("score", "REAL")
)

local createRes = db:createTable(tableName, columns)
assertLog("createTable", createRes, true)

-- hasTable
assertLog("hasTable", db:hasTable(tableName), true)

-- ==========================================
-- 3. 数据操作 (CRUD)
-- ==========================================

-- --- Insert ---
_console:log("\n--- 测试 Insert ---")
-- <<<<< 核心修正点 #1：将 Lua table 转换为 Java Map >>>>>
local user1 = _maps:mutableMapOf(
    _maps:pairOf("name", "Alice"),
    _maps:pairOf("age", 25),
    _maps:pairOf("score", 88.5)
)
local rowId1 = db:insert(tableName, nil, user1)
_console:log("插入 Alice, RowID: " .. tostring(rowId1))

local user2 = _maps:mutableMapOf(
    _maps:pairOf("name", "Bob"),
    _maps:pairOf("age", 30),
    _maps:pairOf("score", 92.0)
)
local rowId2 = db:insert(tableName, nil, user2)
_console:log("插入 Bob, RowID: " .. tostring(rowId2))

if rowId1 ~= -1 and rowId2 ~= -1 then
    _console:log("✅ Insert 成功")
else
    _console:error("❌ Insert 失败")
end

-- --- Query ---
_console:log("\n--- 测试 Query ---")
local querySql = "SELECT * FROM " .. tableName .. " WHERE name = ?"
local wrappedResults = _objectWrappers:wrapList(db:query(querySql, _arrays:arrayOf(String, "Alice")))

if wrappedResults:count() > 0 then
    local userMap = wrappedResults:getOrigin():get(0)
    local wrappedUser = _objectWrappers:wrapMap(userMap)
    _console:log("查询结果: " .. tostring(wrappedUser:getOrigin()))
    if wrappedUser:get("name") == "Alice" and wrappedUser:get("age") == "25" then
        _console:log("✅ Query 数据验证通过")
    else
        _console:error("❌ Query 数据验证失败")
    end
else
    _console:error("❌ Query 未找到数据")
end

-- --- Update ---
_console:log("\n--- 测试 Update ---")
-- <<<<< 核心修正点 #2：将 Lua table 转换为 Java Map >>>>>
local updateValues = _maps:mutableMapOf(
    _maps:pairOf("score", 95.5)
)
local rowsAffected = db:update(tableName, updateValues, "name = ?", _arrays:arrayOf(String, "Alice"))
assertLog("update (受影响行数)", rowsAffected, 1)

-- 验证更新是否生效
local rawVerifyUpdate = db:query("SELECT score FROM " .. tableName .. " WHERE name = 'Alice'", nil)
local wrappedVerifyUpdate = _objectWrappers:wrapList(rawVerifyUpdate)
local updatedUserMap = _objectWrappers:wrapMap(wrappedVerifyUpdate:getOrigin():get(0))
if updatedUserMap:get("score") == "95.5" then
    _console:log("✅ Update 生效验证通过")
else
    _console:error("❌ Update 生效验证失败, 实际值: " .. updatedUserMap:get("score"))
end

-- --- Delete ---
_console:log("\n--- 测试 Delete ---")
local deleteRows = db:delete(tableName, "name = ?", _arrays:arrayOf(String, "Bob"))
assertLog("delete (受影响行数)", deleteRows, 1)

-- ==========================================
-- 4. 原生 SQL 与 事务 (Transaction)
-- ==========================================
_console:log("\n--- 测试 Transaction & execSQL ---")

db:beginTransaction()
local ok, err = pcall(function()
    local sql = "INSERT INTO " .. tableName .. " (name, age, score) VALUES (?, ?, ?)"
    db:execSQL(sql, _arrays:arrayOf(Object, "Charlie", 20, 60.0))
    db:setTransactionSuccessful()
end)
db:endTransaction()

if not ok then
    _console:error("❌ 事务执行异常: " .. tostring(err))
else
    _console:log("✅ 事务执行完毕 (Committed)")
end

-- 验证 Charlie 是否存在
local rawCharlieRes = db:query("SELECT * FROM " .. tableName .. " WHERE name = 'Charlie'", nil)
local wrappedCharlieRes = _objectWrappers:wrapList(rawCharlieRes)
assertLog("事务插入验证", wrappedCharlieRes:count(), 1)

-- ==========================================
-- 5. 清理与关闭
-- ==========================================
_console:log("\n--- 测试 清理操作 ---")

-- dropTable
assertLog("dropTable", db:dropTable(tableName), true)
assertLog("hasTable (删除后)", db:hasTable(tableName), false)

-- close
db:close()
assertLog("isOpen (关闭后)", db:isOpen(), false)

-- delete
local deleteDbRes = _sqliteManager:delete(dbName)
assertLog("SqliteManager.delete", deleteDbRes, true)
assertLog("exists (最终检查)", _sqliteManager:exists(dbName), false)

_console:log("🎉 Sqlite 接口测试完毕。")

_script:getThreads():getMain():setBackground(true)

<?php
/** @var m8test_java\com\m8test\script\core\api\storage\SqliteManager $sqliteManager */
global $sqliteManager;
/** @var m8test_java\com\m8test\script\core\api\console\Console $console */
global $console;
/** @var m8test_java\com\m8test\script\core\api\engine\Script $script */
global $script;
/** @var m8test_java\com\m8test\script\core\api\wrapper\ObjectWrappers $objectWrappers */
global $objectWrappers;
/** @var m8test_java\com\m8test\script\core\api\collections\Maps $maps */
global $maps;
/** @var m8test_java\com\m8test\script\core\api\collections\Arrays $arrays */
global $arrays;
/** @var m8test_java\com\m8test\script\core\api\reflect\Reflectors $reflectors */
global $reflectors;


/**
 * Sqlite 与 SqliteManager 接口测试脚本
 * 全局变量: $sqliteManager
 */

$console->log(javaString("🚀 开始测试 Sqlite 数据库模块..."));

// 获取 Java Class 对象，用于创建特定类型的数组
$stringClass = $reflectors->reflectClassName("java.lang.String")->getTargetClass();
$objectClass = $reflectors->reflectClassName("java.lang.Object")->getTargetClass();

// ==========================================
// 1. 准备工作 & SqliteManager 测试
// ==========================================
$sqliteName = "test_m8_data.db";
$tableName = "users";

// 确保测试前环境干净
if ($sqliteManager->exists($sqliteName)) {
    $sqliteManager->delete($sqliteName);
}
assertLog(javaString("exists (初始状态)"), $sqliteManager->exists($sqliteName), false);

// 打开/创建数据库
// 此时会返回一个 Sqlite 接口对象
$sqlite = $sqliteManager->open($sqliteName);

if ($sqlite != null && $sqlite->isOpen()) {
    $console->log(javaString("✅ 数据库打开成功: "), $sqliteName);
} else {
    $console->error(javaString("❌ 数据库打开失败"));
    return; // 无法继续
}

// 验证文件现在是否存在
assertLog(javaString("exists (打开后)"), $sqliteManager->exists($sqliteName), true);

// ==========================================
// 2. 表结构操作 (DDL)
// ==========================================

// createTable
// 定义列: id 自增主键, name 文本, age 整数, score 浮点
// 使用 $maps->mapOf 和 $maps->pairOf 创建 Java Map
$columns = $maps->mapOf(
    $maps->pairOf("id", "INTEGER PRIMARY KEY AUTOINCREMENT"),
    $maps->pairOf("name", "TEXT NOT NULL"),
    $maps->pairOf("age", "INTEGER"),
    $maps->pairOf("score", "REAL")
);

$createRes = $sqlite->createTable($tableName, $columns);
assertLog(javaString("createTable"), $createRes, true);

// hasTable
assertLog(javaString("hasTable"), $sqlite->hasTable($tableName), true);

// ==========================================
// 3. 数据操作 (CRUD)
// ==========================================

// --- Insert ---
$console->log(javaString("\n--- 测试 Insert ---"));
$user1 = $maps->mapOf(
    $maps->pairOf("name", "Alice"),
    $maps->pairOf("age", 25),
    $maps->pairOf("score", 88.5)
);
$rowId1 = $sqlite->insert($tableName, null, $user1);
$console->log("插入 Alice, RowID: ", $rowId1);

$user2 = $maps->mapOf(
    $maps->pairOf("name", "Bob"),
    $maps->pairOf("age", 30),
    $maps->pairOf("score", 92.0)
);
$rowId2 = $sqlite->insert($tableName, null, $user2);
$console->log(javaString("插入 Bob, RowID: "), $rowId2);

if ($rowId1 != -1 && $rowId2 != -1) {
    $console->log(javaString("✅ Insert 成功"));
} else {
    $console->error(javaString("❌ Insert 失败"));
}

// --- Query ---
$console->log(javaString("\n--- 测试 Query ---"));
// 注意：query 方法的 bindArgs 需要 String 数组, 使用 $arrays->arrayOf 创建
$querySql = "SELECT * FROM {$tableName} WHERE name = ?";
$bindArgs = $arrays->arrayOf($stringClass, "Alice");
$rawResults = $sqlite->query(javaString($querySql), $bindArgs);
$resultsWrapper = $objectWrappers->wrapList($rawResults);

if ($resultsWrapper->count() > 0) {
    $rawUserMap = $resultsWrapper->getOrNull(0);
    $userWrapper = $objectWrappers->wrapMap($rawUserMap);

    $console->log(javaString("查询结果: "), $userWrapper);
    // 验证数据 (注意：接口返回的值统一为 String，所以对比字符串 "25")
    if ($userWrapper->get(javaString("name")) == javaString("Alice") && $userWrapper->get(javaString("age")) == "25") {
        $console->log(javaString("✅ Query 数据验证通过"));
    } else {
        $console->error(javaString("❌ Query 数据验证失败"));
    }
} else {
    $console->error(javaString("❌ Query 未找到数据"));
}

// --- Update ---
$console->log(javaString("\n--- 测试 Update ---"));
$updateValues = $maps->mapOf($maps->pairOf("score", 95.5)); // 修改 Alice 的分数
$updateBindArgs = $arrays->arrayOf($stringClass, "Alice");
$rowsAffected = $sqlite->update($tableName, $updateValues, "name = ?", $updateBindArgs);
assertLog("update (受影响行数)", $rowsAffected, 1);

// 验证更新是否生效
$rawVerifyUpdate = $sqlite->query("SELECT score FROM {$tableName} WHERE name = 'Alice'", null);
$verifyUpdateWrapper = $objectWrappers->wrapList($rawVerifyUpdate);
$scoreMapWrapper = $objectWrappers->wrapMap($verifyUpdateWrapper->getOrNull(0));

if ($scoreMapWrapper->get("score") == "95.5") {
    $console->log(javaString("✅ Update 生效验证通过"));
} else {
    $console->error(javaString("❌ Update 生效验证失败, 实际值: "), $scoreMapWrapper->get("score"));
}

// --- Delete ---
$console->log(javaString("\n--- 测试 Delete ---"));
$deleteBindArgs = $arrays->arrayOf($stringClass, "Bob");
$deleteRows = $sqlite->delete($tableName, "name = ?", $deleteBindArgs);
assertLog(javaString("delete (受影响行数)"), $deleteRows, 1);

// ==========================================
// 4. 原生 SQL 与 事务 (Transaction)
// ==========================================
$console->log(javaString("\n--- 测试 Transaction & execSQL ---"));

$sqlite->beginTransaction();
// 使用 execSQL 插入一条数据
// 注意：bindArgs 是 Array<Any>，可以传 int/string 等
$sql = "INSERT INTO {$tableName} (name, age, score) VALUES (?, ?, ?)";
$execBindArgs = $arrays->arrayOf($objectClass, "Charlie", 20, 60.0);
$sqlite->execSQL($sql, $execBindArgs);

// 标记事务成功
$sqlite->setTransactionSuccessful();
$console->log(javaString("✅ 事务执行完毕 (Committed)"));

// 结束事务（如果之前没调用 setTransactionSuccessful，这里会自动回滚）
$sqlite->endTransaction();

// 验证 Charlie 是否存在
$rawCharlieRes = $sqlite->query(javaString("SELECT * FROM {$tableName} WHERE name = 'Charlie'"), null);
$charlieResWrapper = $objectWrappers->wrapList($rawCharlieRes);
assertLog(javaString("事务插入验证"), $charlieResWrapper->count(), 1);

// ==========================================
// 5. 清理与关闭
// ==========================================
$console->log(javaString("\n--- 测试 清理操作 ---"));

// dropTable
assertLog(javaString("dropTable"), $sqlite->dropTable($tableName), true);
assertLog(javaString("hasTable (删除后)"), $sqlite->hasTable($tableName), false);

// 关闭连接
// Sqlite 继承自 ScriptCloseable，通常有关闭方法
$sqlite->close();
assertLog(javaString("isOpen (关闭后)"), $sqlite->isOpen(), false);

// 删除数据库文件
$deleteDbRes = $sqliteManager->delete($sqliteName);
assertLog(javaString("SqliteManager.delete"), $deleteDbRes, true);
assertLog(javaString("exists (最终检查)"), $sqliteManager->exists($sqliteName), false);

$console->log(javaString("🎉 Sqlite 接口测试完毕。"));

// ==========================================
// 辅助函数
// ==========================================
function assertLog($name, $actual, $expected)
{
    global $console;
    if ($actual == $expected) {
        $console->log("✅ [" . $name . "] Passed. Result: ", $actual);
    } else {
        $console->error("❌ [" . $name . "] Failed.\n   Expected: ", $expected, "\n   Actual  : ", $actual);
    }
}

# 导入所需的全局变量
from m8test_java.com.m8test.script.GlobalVariables import _arrays
from m8test_java.com.m8test.script.GlobalVariables import _console
from m8test_java.com.m8test.script.GlobalVariables import _reflectors
from m8test_java.com.m8test.script.GlobalVariables import _script
from m8test_java.com.m8test.script.GlobalVariables import _sqliteManager

java_String = _reflectors.reflect("java.lang.String").getTargetClass()
Object = _reflectors.reflect("java.lang.Object").getTargetClass()


# ==========================================
# 辅助函数
# ==========================================
def assertLog(name, actual, expected):
    # Java boolean is returned as string True/False
    actual_str = str(actual).lower() if isinstance(actual, str) and actual.lower() in [True, False] else str(actual)
    expected_str = str(expected).lower() if isinstance(expected, bool) else str(expected)

    if actual_str == expected_str:
        _console.log(f"✅ [{name}] Passed. Result: {actual}")
    else:
        _console.error(f"❌ [{name}] Failed.\n   Expected: {expected}\n   Actual  : {actual}")


"""
Sqlite 与 SqliteManager 接口测试脚本
全局变量: $sqliteManager
"""

_console.log("🚀 开始测试 Sqlite 数据库模块...")

# ==========================================
# 1. 准备工作 & SqliteManager 测试
# ==========================================
dbName = "test_m8_data.db"
tableName = "users"

# 确保测试前环境干净
if _sqliteManager.exists(dbName) == True:
    _sqliteManager.delete(dbName)
assertLog("exists (初始状态)", _sqliteManager.exists(dbName), "False")

# 打开/创建数据库
db = _sqliteManager.open(dbName)

if db is not None and db.isOpen() == True:
    _console.log(f"✅ 数据库打开成功: {dbName}")
else:
    _console.error("❌ 数据库打开失败")
    # In Python, we can exit using a function or just stop execution.
    # For a direct translation, we'll let it continue but it might fail later.
    # A cleaner way would be to wrap this in a main function and return.
    db = None

if db:
    # 验证文件现在是否存在
    assertLog("exists (打开后)", _sqliteManager.exists(dbName), "True")

    # ==========================================
    # 2. 表结构操作 (DDL)
    # ==========================================
    # createTable
    columns = {
        "id": "INTEGER PRIMARY KEY AUTOINCREMENT",
        "name": "TEXT NOT NULL",
        "age": "INTEGER",
        "score": "REAL"
    }
    createRes = db.createTable(tableName, columns)
    assertLog("createTable", createRes, "True")

    # hasTable
    assertLog("hasTable", db.hasTable(tableName), "True")

    # ==========================================
    # 3. 数据操作 (CRUD)
    # ==========================================
    # --- Insert ---
    _console.log("\n--- 测试 Insert ---")
    user1 = {"name": "Alice", "age": 25, "score": 88.5}
    rowId1 = db.insert(tableName, None, user1)
    _console.log(f"插入 Alice, RowID: {rowId1}")

    user2 = {"name": "Bob", "age": 30, "score": 92.0}
    rowId2 = db.insert(tableName, None, user2)
    _console.log(f"插入 Bob, RowID: {rowId2}")

    if rowId1 != -1 and rowId2 != -1:
        _console.log("✅ Insert 成功")
    else:
        _console.error("❌ Insert 失败")

    # --- Query ---
    _console.log("\n--- 测试 Query ---")
    querySql = f"SELECT * FROM {tableName} WHERE name = ?"
    # Pass Python list directly. To be formal, use _arrays.arrayOf.
    results = db.query(querySql, _arrays.arrayOf(java_String, "Alice"))

    if len(results) > 0:
        user = results[0]
        _console.log(f"查询结果: {user}")
        if user["name"] == "Alice" and user["age"] == "25":
            _console.log("✅ Query 数据验证通过")
        else:
            _console.error("❌ Query 数据验证失败")
    else:
        _console.error("❌ Query 未找到数据")

    # --- Update ---
    _console.log("\n--- 测试 Update ---")
    updateValues = {"score": 95.5}
    rowsAffected = db.update(tableName, updateValues, "name = ?", _arrays.arrayOf(java_String, "Alice"))
    assertLog("update (受影响行数)", rowsAffected, 1)

    verifyUpdate = db.query("SELECT score FROM {} WHERE name = 'Alice'".format(tableName), None)
    if verifyUpdate[0]["score"] == "95.5":
        _console.log("✅ Update 生效验证通过")
    else:
        _console.error(f"❌ Update 生效验证失败, 实际值: {verifyUpdate[0]['score']}")

    # --- Delete ---
    _console.log("\n--- 测试 Delete ---")
    deleteRows = db.delete(tableName, "name = ?", _arrays.arrayOf(java_String, "Bob"))
    assertLog("delete (受影响行数)", deleteRows, 1)

    # ==========================================
    # 4. 原生 SQL 与 事务 (Transaction)
    # ==========================================
    _console.log("\n--- 测试 Transaction & execSQL ---")
    db.beginTransaction()
    try:
        sql = f"INSERT INTO {tableName} (name, age, score) VALUES (?, ?, ?)"
        db.execSQL(sql, _arrays.arrayOf(Object, "Charlie", 20, 60.0))
        db.setTransactionSuccessful()
        _console.log("✅ 事务执行完毕 (Committed)")
    except Exception as e:
        _console.error(f"❌ 事务执行异常: {e}")
    finally:
        db.endTransaction()

    charlieRes = db.query(f"SELECT * FROM {tableName} WHERE name = 'Charlie'", None)
    assertLog("事务插入验证", len(charlieRes), 1)

    # ==========================================
    # 5. 清理与关闭
    # ==========================================
    _console.log("\n--- 测试 清理操作 ---")
    assertLog("dropTable", db.dropTable(tableName), "True")
    assertLog("hasTable (删除后)", db.hasTable(tableName), "False")
    db.close()
    assertLog("isOpen (关闭后)", db.isOpen(), "False")

    deleteDbRes = _sqliteManager.delete(dbName)
    assertLog("SqliteManager.delete", deleteDbRes, "True")
    assertLog("exists (最终检查)", _sqliteManager.exists(dbName), "False")

    _console.log("🎉 Sqlite 接口测试完毕。")

_script.getThreads().getMain().setBackground(True)

# encoding: utf-8
# Sqlite 与 SqliteManager 接口测试脚本
# 全局变量: $sqliteManager

$console.log("🚀 开始测试 Sqlite 数据库模块...")

# ==========================================
# 辅助函数
# ==========================================
def assertLog(name, actual, expected)
  if actual == expected
    $console.log("✅ [#{name}] Passed. Result: #{actual}")
  else
    $console.error("❌ [#{name}] Failed.\n   Expected: #{expected}\n   Actual  : #{actual}")
  end
end

# ==========================================
# 1. 准备工作 & SqliteManager 测试
# ==========================================
dbName = "test_m8_data.db"
tableName = "users"

# 确保测试前环境干净
if $sqliteManager.exists(dbName)
  $sqliteManager.delete(dbName)
end
assertLog("exists (初始状态)", $sqliteManager.exists(dbName), false)

# 打开/创建数据库
# 此时会返回一个 Sqlite 接口对象
db = $sqliteManager.open(dbName)

if db != nil && db.isOpen()
  $console.log("✅ 数据库打开成功: #{dbName}")
else
  $console.error("❌ 数据库打开失败")
  return # 无法继续
end

# 验证文件现在是否存在
assertLog("exists (打开后)", $sqliteManager.exists(dbName), true)

# ==========================================
# 2. 表结构操作 (DDL)
# ==========================================

# createTable
# 定义列: id 自增主键, name 文本, age 整数, score 浮点
# Ruby Hash 写法
columns = {
  "id" => "INTEGER PRIMARY KEY AUTOINCREMENT",
  "name" => "TEXT NOT NULL",
  "age" => "INTEGER",
  "score" => "REAL"
}

createRes = db.createTable(tableName, columns)
assertLog("createTable", createRes, true)

# hasTable
assertLog("hasTable", db.hasTable(tableName), true)

# ==========================================
# 3. 数据操作 (CRUD)
# ==========================================

# --- Insert ---
$console.log("\n--- 测试 Insert ---")
user1 = {"name" => "Alice", "age" => 25, "score" => 88.5}
# Ruby 中 nil 代替 null
rowId1 = db.insert(tableName, nil, user1)
$console.log("插入 Alice, RowID: " + rowId1.to_s)

user2 = {"name" => "Bob", "age" => 30, "score" => 92.0}
rowId2 = db.insert(tableName, nil, user2)
$console.log("插入 Bob, RowID: " + rowId2.to_s)

if rowId1 != -1 && rowId2 != -1
  $console.log("✅ Insert 成功")
else
  $console.error("❌ Insert 失败")
end

# --- Query ---
$console.log("\n--- 测试 Query ---")
# String 插值使用 #{var}
querySql = "SELECT * FROM #{tableName} WHERE name = ?"
# 根据接口定义，返回值 Map 的 Value 都是 String 类型
# Ruby 数组 ["Alice"]
results = db.query(querySql, ["Alice"])

if results.size > 0
  user = results[0]
  $console.log("查询结果: " + user.to_s)
  # 验证数据 (注意：接口返回的值统一为 String，所以对比字符串 "25")
  if user["name"] == "Alice" && user["age"] == "25"
    $console.log("✅ Query 数据验证通过")
  else
    $console.error("❌ Query 数据验证失败")
  end
else
  $console.error("❌ Query 未找到数据")
end

# --- Update ---
$console.log("\n--- 测试 Update ---")
updateValues = {"score" => 95.5} # 修改 Alice 的分数
rowsAffected = db.update(tableName, updateValues, "name = ?", ["Alice"])
assertLog("update (受影响行数)", rowsAffected, 1)

# 验证更新是否生效
verifyUpdate = db.query("SELECT score FROM #{tableName} WHERE name = 'Alice'", nil)
if verifyUpdate[0]["score"] == "95.5"
  $console.log("✅ Update 生效验证通过")
else
  $console.error("❌ Update 生效验证失败, 实际值: " + verifyUpdate[0]["score"].to_s)
end

# --- Delete ---
$console.log("\n--- 测试 Delete ---")
deleteRows = db.delete(tableName, "name = ?", ["Bob"])
assertLog("delete (受影响行数)", deleteRows, 1)

# ==========================================
# 4. 原生 SQL 与 事务 (Transaction)
# ==========================================
$console.log("\n--- 测试 Transaction & execSQL ---")

db.beginTransaction()
begin
  # 使用 execSQL 插入一条数据
  # 注意：bindArgs 是 Array<Any?>，可以传 int/string 等
  sql = "INSERT INTO #{tableName} (name, age, score) VALUES (?, ?, ?)"
  db.execSQL(sql, ["Charlie", 20, 60.0])

  # 标记事务成功
  db.setTransactionSuccessful()
  $console.log("✅ 事务执行完毕 (Committed)")
rescue Exception => e
  $console.error("❌ 事务执行异常: " + e.to_s)
ensure
  # 结束事务（如果之前没调用 setTransactionSuccessful，这里会自动回滚）
  db.endTransaction()
end

# 验证 Charlie 是否存在
charlieRes = db.query("SELECT * FROM #{tableName} WHERE name = 'Charlie'", nil)
assertLog("事务插入验证", charlieRes.size, 1)

# ==========================================
# 5. 清理与关闭
# ==========================================
$console.log("\n--- 测试 清理操作 ---")

# dropTable
assertLog("dropTable", db.dropTable(tableName), true)
assertLog("hasTable (删除后)", db.hasTable(tableName), false)

# 关闭连接
# Sqlite 继承自 ScriptCloseable，通常有关闭方法
db.close()
assertLog("isOpen (关闭后)", db.isOpen(), false)

# 删除数据库文件
deleteDbRes = $sqliteManager.delete(dbName)
assertLog("SqliteManager.delete", deleteDbRes, true)
assertLog("exists (最终检查)", $sqliteManager.exists(dbName), false)

$console.log("🎉 Sqlite 接口测试完毕。")

$script.getThreads().getMain().setBackground(true)

09 December 2025