最近搞一个小程式,打算从原来的MySQL,转移到postgreSql阵线,aardio中对postgreSql的使用,好像没多少人在用,好在官方有一个库
所以在官方libpg原版的基础上扩展了一些方便使用的方法,实际使用,还需要进一步完善。
添加了事务函数,批量插入,简化增,删,改,查,以及构建原始SQL语句等

演示代码:
import postgreSql;
import console
//创建数据库连接
var db,err = postgreSql(
host = "localhost";
port = 5432;
dbname = "aardioTest";
user = "postgres";
password = "haosql"
);
if(!db){
console.log("数据库连接失败");
return;
}
//创建测试表
var ok,err = db.exec("
DROP TABLE IF EXISTS test_users;
CREATE TABLE test_users (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INTEGER,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
");
if(!ok){
console.log("创建表失败:",err);
return;
}
db.tableName = "test_users"
console.dump("快速插入,返回Id",db.insert({
name = "aardio",
email = "[email protected]"
}))
console.dump("快速更新,返回执行结果",db.update("test_users",{
name = "aardio",
age = 30,
email = "[email protected]"
},{name="aardio"}))
console.dump("快速删除,返回执行结果",db.delete({name="aardio"}))
sql,v = db.buildSql("insert","test_users",{
namex = "张三",
age = 30
})
console.dump("生成插入语句:",sql);
sql1,v1 = db.buildSql("update","test_users",{
name = "李四季",
age = 30
},
{
name = "张三"
})
console.dump("生成更新语句:",sql1);
sql2,v2 = db.buildSql("delete","test_users",,{
name = "张三",
age = 30
})
console.dump("生成删除语句:",sql2);
console.dump("执行事务失败")
console.dump("")
db.beginTran()
res,err = db.execParams(sql,table.unpack(v))
console.dump("事务执行插入",res,err)
res,err = db.execParams(sql1,table.unpack(v1))
console.dump("事务执行更新",res,err)
res,err = db.execParams(sql2,table.unpack(v2))
console.dump("事务执行删除",res,err)
if (err) {
db.rollback()
}else {
db.commitTran()
}
sql,v = db.buildSql("insert","test_usersx",{
name = "张三",
age = 30
})
console.dump("执行事务")
try{
db.beginTran()
assert(db.execParams(sql,table.unpack(v)))
assert(db.execParams(sql1,table.unpack(v1)))
assert(db.execParams(sql2,table.unpack(v2)))
db.commitTran()
console.dump("事务执行成功")
}
catch(e){
db.rollback()
console.dump("事务执行失败",e)
}
var sortedUsers,err = db.query("test_usersx",db.formatSqlParameters("age>10 and name=@name",{name="李四季"}),{"id","name"}/*需要的字段*/);
console.dump("查询失败",sortedUsers,err)
var sortedUsers,err = db.query("test_users",db.formatSqlParameters("age>10 and name=@name",{name="李四季"}),{"id","name"}/*需要的字段*/);
console.dump("查询成功",sortedUsers,err)
var result,err = db.query("test_users", "age between 18 and 30" , "*", {
age = "desc"
}, 5, 0,
);
console.dump("复杂查询:",result,err)
// 批量插入
var data = {
{name="张三",age=20},
{name="李四",age=21},
{name="王五",age=22}
}
console.dump("批量插入",db.batchInsert("test_users",data));
console.dump("单条记录:",db.getOne("test_users",{name="李四"}));
console.dump("记录数:",db.count("test_users",{name="李四"}));
console.dump("是否存在:",db.exists("test_users",{name="王五"}));
db.close();
console.pause();