API 文档

SQLite3: SQLite 数据库测试

@form { }

异步版本

[检视源码]

async function() {
	var arr = [
		{ id: 1, nick: "Rimifon" },
		{ id: 2, nick: "Pojin" }
	];
	var par = { json: JSON.stringify(arr) };
	var dbo = db(":memory:", "sqlite3");
	var query = await dbo.table('json_each(:json)').page('key', 15, 1, par);
	var rows = await query.select(`
		value->>'id' as id,
		value->>'nick' as nick,
		sqlite_version() as ver
	`).query();
	return { rows, pager: dbo.pager };
}

BetterSqlite3: 测试 better-sqlite3

@form { }

同步版本，参数使用 @ 命名

[检视源码]

function() {
	var dbo = db(":memory:", "better-sqlite3");
	dbo.create("test", [ [ "id integer", null, true ], "nick varchar(254)" ]);
	var arr = [
		{ id: 1, nick: "Rimifon" },
		{ id: 2, nick: "Pojin" }
	];
	var par = { json: JSON.stringify(arr) };
	return db(":memory:", "better-sqlite3").query(`
		select
			value->>'id' as id,
			value->>'nick' as nick,
			sqlite_version() as ver
		from json_each(@json)
	`, par);
}

PgSQL: PostgreSQL 数据库测试

@form { }

不支持参数化命名，仅支持 $1 这种模式

[检视源码]

async function() {
	var arr = [
		{ id: 1, nick: "Rimifon" },
		{ id: 2, nick: "Pojin" }
	];
	var par = { json: JSON.stringify(arr) };
	var cfgPg = this.cfgPg;
	var dbo = db(`host=${cfgPg.host}; port=${cfgPg.port}; user=${cfgPg.user}; password=${cfgPg.password}; database=${cfgPg.database}`, "pg");
	var query = await dbo.table("json_array_elements($1)").select(`
		value->>'id' as id,
		value->>'nick' as nick,
		version() as ver
	`).page("id", 5, 1, [ par.json ]);
	var rows = await query.query();
	return { rows, pager: dbo.pager };
}

MariaDB: MariaDB 测试

@form { }

仅支持 ? 参数，及数组传参

[检视源码]

async function() {
	var arr = [
		{ id: 1, nick: "Rimifon" },
		{ id: 2, nick: "Pojin" }
	];
	var par = { json: JSON.stringify(arr) };
	var cfg = this.cfgMySQL;
	var dbo = db(`host=${cfg.host}; user=${cfg.user}; password=${cfg.password}; database=${cfg.database}`, "mariadb");
	var query = await dbo.table("json_table(?, '$[*]' columns(ele json path '$')) a").select(`
		json_value(ele, '$.id') as id,
		json_value(ele, '$.nick') as nick,
		version() as ver, user() as user
	`).page("id", 5, 1, [ par.json ]);
	var rows = await query.query();
	return { rows, pager: dbo.pager };
}

MSSQL: MSSQL 微软数据库测试

@form { }

[检视源码]

async function() {
	var cfg = this.cfgMssql;
	sys.dbType = "mssql";
	sys.dbPath = `server=${cfg.server}; user=${cfg.user}; password=${cfg.password}; database=${cfg.database}`;
	var arr = [
		{ id: 1, nick: "Rimifon", time: new Date },
		{ id: 2, nick: "Pojin", time: new Date }
	];
	var par = { json: JSON.stringify(arr) };
	var query = await db().table("openjson(@json)").select(`
		json_value(value, '$.id') as id,
		json_value(value, '$.nick') as nick,
		json_value(value, '$.time') as time,
		@@version as ver,  getdate() as now
	`).page("id", 5, 1, par);
	var rows = await query.query();
	return { rows, pager: db().pager };
}