cocos2dx-lua sqlite3使用
来源：互联网 发布：cnmd是什么意思网络 编辑：程序博客网 时间：2024/05/21 17:04
转载请注明网址：
～1
首先下载luasqlite http://lua.sqlite.org/index.cgi/index 下载 Version 0.9.4
以及 sqlite http://sqlite.org/download.html 下载第一个即可
～2
在Classes 目录下 创建sqlite3目录 把 luasqlite里的 lsqlite3.c sqlite3.h sqlite3.c 三个文件 放进去，以及sqlite 目录里的sqlite3ext.h 文件放进去
～3 绑定lua 与c++
在sqlite3目录里创建lsqlite3.h 初始化如下代码
#ifndef lsqlite3_h
#define lsqlite3_h
#include "base/ccConfig.h"
#ifdef __cplusplus
extern "C" {
#endif
#include "tolua++.h"
int luaopen_lsqlite3(lua_State* L); //lsqlite3.c 中的C函数，这里注册C函数
#ifdef __cplusplus
}
#endif
#endif /* lsqlite3_h */
然后在 绑定c++的地方
luaopen_lsqlite3(L); //这句话必须写
~4 lua 调用c++ sqlite3方法
-------------------------------------------------------------------------
-- Desc: sqlite3test.lua
-- Author: sqlite3测试
-- Copyright (c) wawagame Entertainment All right reserved.
-- local sqlite3 = require("app.dataorm.sqlite3test")
-- sqlite3:getDBVersion()
-- sqlite3:openDB()
-- sqlite3:insert('numbers', {10086, 10086,"diyal"})
-- sqlite3:test()
-- sqlite3:test2()
-- sqlite3:test3()
-- sqlite3:aggregate()
-- sqlite3:crudTest()
-- sqlite3:statement()
-- sqlite3:tracing()
-- sqlite3:batchsql()
-- sqlite3:updateHook()
-------------------------------------------------------------------------
local sqlite3 = require("sqlite3")
local sqlite3test = class('sqlite3test')
local _db, _vm --数据库句柄， 数据库状态
--[[获取版本号]]
function sqlite3test:getDBVersion()
print("[SQLite] DB version : "..sqlite3.version())
end
function sqlite3test:openDB()
local dbFilePath = device.writablePath..'test.db'
local isExist = cc.FileUtils:getInstance():isFileExist(dbFilePath)
_db = sqlite3.open(dbFilePath)
if isExist then
print('[SQLite] DB File is exist')
else
print('[SQLite] DB File is not exist, created it')
--初始化表结构
self:initDB()
end
end
function sqlite3test:initDB()
-- Demo表DDL语句
local t_demo_sql=
[=[
CREATE TABLE numbers(num1,num2,str);
INSERT INTO numbers VALUES(1,11,"ABC");
INSERT INTO numbers VALUES(2,22,"DEF");
INSERT INTO numbers VALUES(3,33,"UVW");
INSERT INTO numbers VALUES(4,44,"XYZ");
SELECT * FROM numbers;
]=]
local showrow = function(udata,cols,values,names)
assert(udata == 't_demo_create')
-- for i=1,cols do
-- print('%s |-> %s',names[i],values[i])
-- end
print('[SQLite] %s rows %s', udata,table.concat( values, "-"))
return 0
end
_db:exec(t_demo_sql, showrow, 't_demo_create')
end
function sqlite3test:insert( tableName, tableParas)
local t_demo_sql=
[=[
INSERT INTO tableName VALUES(tableParas);
SELECT * FROM numbers;
]=]
local showrow = function(udata,cols,values,names)
assert(udata == 't_demo_create')
-- for i=1,cols do
-- print('%s |-> %s',names[i],values[i])
-- end
print('[SQLite] %s rows %s', udata,table.concat( values, "-"))
return 0
end
local ret = _db:exec(t_demo_sql, showrow, 't_demo_create')
if ret ~= sqlite3.OK then
print('error')
end
end
--[[test]]
function sqlite3test:test()
local sqlite3 = require('sqlite3')
local width = 78
local function line(pref, suff) --格式化函数
pref = pref or ''
suff = suff or ''
local len = width - 2 - string.len(pref) - string.len(suff)
print(pref .. string.rep('_', len) .. suff)
end
local db, vm
local assert_, assert = assert, function (test)
if (not test) then
error(db:errmsg(), 2)
end
end
-- os.remove('test.db')
db = sqlite3.open('test.db')
line(nil, 'db:exec')
db:exec('CREATE TABLE t(a, b)')
line(nil, 'prepare')
vm = db:prepare('insert into t values(?, :bork)')
assert(vm, db:errmsg())
assert(vm:bind_parameter_count() == 2)
assert(vm:bind_values(2, 4) == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:reset() == sqlite3.OK)
assert(vm:bind_names{ 'pork', bork = 'nono' } == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:reset() == sqlite3.OK)
assert(vm:bind_names{ bork = 'sisi' } == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:reset() == sqlite3.OK)
assert(vm:bind_names{ 1 } == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:finalize() == sqlite3.OK)
line("select * from t", 'db:exec')
-- assert(db:exec('select * from t', function (ud, ncols, values, names)
-- print(table.unpack(values))
-- return sqlite3.OK
-- end) == sqlite3.OK)
db:exec('select * from t', function (ud, ncols, values, names)
print(
table.concat(
{ unpack(values)}
)
)
return sqlite3.OK
end)
line("select * from t", 'db:prepare')
vm = db:prepare('select * from t')
assert(vm, db:errmsg())
print(vm:get_unames())
while (vm:step() == sqlite3.ROW) do
print(vm:get_uvalues())
end
assert(vm:finalize() == sqlite3.OK)
line('udf', 'scalar')
local function do_query(sql)
local r
local vm = db:prepare(sql)
assert(vm, db:errmsg())
print('====================================')
print(vm:get_unames())
print('------------------------------------')
r = vm:step()
while (r == sqlite3.ROW) do
print(vm:get_uvalues())
r = vm:step()
end
assert(r == sqlite3.DONE)
assert(vm:finalize() == sqlite3.OK)
print('====================================')
end
local function udf1_scalar(ctx, v)
local ud = ctx:user_data()
ud.r = (ud.r or '') .. tostring(v)
ctx:result_text(ud.r)
end
db:create_function('udf1', 1, udf1_scalar, { })
do_query('select udf1(a) from t')
line('udf', 'aggregate')
local function udf2_aggregate(ctx, ...)
local ud = ctx:get_aggregate_data()
if (not ud) then
ud = {}
ctx:set_aggregate_data(ud)
end
ud.r = (ud.r or 0) + 2
end
local function udf2_aggregate_finalize(ctx, v)
local ud = ctx:get_aggregate_data()
ctx:result_number(ud and ud.r or 0)
end
db:create_aggregate('udf2', 1, udf2_aggregate, udf2_aggregate_finalize, { })
do_query('select udf2(a) from t')
-- if (true) then
-- line(nil, '100 insert exec')
-- db:exec('delete from t')
-- local t = os.time()
-- for i = 1, 100 do
-- db:exec('insert into t values('..i..', '..(i * 2 * -1^i)..')')
-- end
-- print('elapsed: '..(os.time() - t))
-- do_query('select count(*) from t')
-- line(nil, '100000 insert exec T')
-- db:exec('delete from t')
-- local t = os.time()
-- db:exec('begin')
-- for i = 1, 100000 do
-- db:exec('insert into t values('..i..', '..(i * 2 * -1^i)..')')
-- end
-- db:exec('commit')
-- print('elapsed: '..(os.time() - t))
-- do_query('select count(*) from t')
-- line(nil, '100000 insert prepare/bind T')
-- db:exec('delete from t')
-- local t = os.time()
-- local vm = db:prepare('insert into t values(?, ?)')
-- db:exec('begin')
-- for i = 1, 100000 do
-- vm:bind_values(i, i * 2 * -1^i)
-- vm:step()
-- vm:reset()
-- end
-- vm:finalize()
-- db:exec('commit')
-- print('elapsed: '..(os.time() - t))
-- do_query('select count(*) from t')
-- end
line(nil, "db:close")
local filePath = cc.FileUtils:getInstance():fullPathForFilename('test.db')
print('path: '..filePath)
assert(db:close() == sqlite3.OK)
end
function sqlite3test:test2()
_db = sqlite3.open('test.db')
print('[SQLite] db:exec')
_db:exec('CREATE TABLE t(a, b)')
print('[SQLite] prepare')
_vm = _db:prepare('insert into t values(?, :bork)')
assert(_vm, _db:errmsg())
assert(_vm:bind_parameter_count() == 2)
assert(_vm:bind_values(2, 4) == sqlite3.OK)
assert(_vm:step() == sqlite3.DONE)
assert(_vm:reset() == sqlite3.OK)
assert(_vm:bind_names{ 'pork', bork = 'nono' } == sqlite3.OK)
assert(_vm:step() == sqlite3.DONE)
assert(_vm:reset() == sqlite3.OK)
assert(_vm:bind_names{ bork = 'sisi' } == sqlite3.OK)
assert(_vm:step() == sqlite3.DONE)
assert(_vm:reset() == sqlite3.OK)
assert(_vm:bind_names{ 1 } == sqlite3.OK)
assert(_vm:step() == sqlite3.DONE)
assert(_vm:finalize() == sqlite3.OK)
print("[SQLite] select * from t")
-- assert(_db:exec('select * from t',
-- function (ud, ncols, values, names)
-- print(table.unpack(values))
-- return sqlite3.OK
-- end) == sqlite3.OK
-- )
local ret = _db:exec('select * from t',
function (ud, ncols, values, names)
print('[SQLite] ' .. table.unpack(values))
return sqlite3.OK
end)
end
--[[test 3]]
function sqlite3test:test3()
end
--[[聚合函数]]
function sqlite3test:aggregate()
assert( _db:exec "CREATE TABLE test (col1, col2)" )
assert( _db:exec "INSERT INTO test VALUES (1, 2)" )
assert( _db:exec "INSERT INTO test VALUES (2, 4)" )
assert( _db:exec "INSERT INTO test VALUES (3, 6)" )
assert( _db:exec "INSERT INTO test VALUES (4, 8)" )
assert( _db:exec "INSERT INTO test VALUES (5, 10)" )
do
local square_error_sum = 0
local function step(ctx, a, b)
local error = a - b
local square_error = error * error
square_error_sum = square_error_sum + square_error
end
local function final(ctx)
ctx:result_number( square_error_sum / ctx:aggregate_count() )
end
assert( _db:create_aggregate("my_stats", 2, step, final) )
end
for my_stats in _db:urows("SELECT my_stats(col1, col2) FROM test")
do
print("my_stats:%d", my_stats)
end
end
--[[CRUD]]
function sqlite3test:crudTest()
local sqlite3 = require("sqlite3") --加载模块
local db = sqlite3.open_memory() --开辟内存数据库
db:exec[[ CREATE TABLE test (id INTEGER PRIMARY KEY, content) ]] --执行DDL语句
local stmt = db:prepare[[ INSERT INTO test VALUES (:key, :value) ]] --前导声明
stmt:bind_names({
key = 1,
value = "Hello World"
}) --参数绑定
-- step()
-- This is the top-level implementation of sqlite3_step(). Call
-- sqlite3Step() to do most of the work. If a schema error occurs,
-- call sqlite3Reprepare() and try again.
stmt:step() --执行
stmt:reset() --重置
stmt:bind_names({ key = 2, value = "Hello Lua" } )
stmt:step()
stmt:reset()
stmt:bind_names({ key = 3, value = "Hello Sqlite3" })
stmt:step()
stmt:finalize()
for row in db:nrows("SELECT * FROM test") do
print("%d, %s", row.id, row.content)
end
end
--[[statement]]
function sqlite3test:statement()
local sqlite3 = require("sqlite3")
local db = sqlite3.open_memory()
db:exec[[
CREATE TABLE test (
id INTEGER PRIMARY KEY,
content VARCHAR
);
]]
local insert_stmt = assert( db:prepare("INSERT INTO test VALUES (NULL, ?)") )
local function insert(data) --封装一个insert函数
insert_stmt:bind_values(data)
insert_stmt:step()
insert_stmt:reset()
end
local select_stmt = assert( db:prepare("SELECT * FROM test") )
local function select() --封装一个查询函数
for row in select_stmt:nrows() do
print("%d, %s",row.id, row.content)
end
end
insert("Hello World")
print("First:")
select()
insert("Hello Lua")
print("Second:")
select()
insert("Hello Sqlite3")
print("Third:")
select()
end
--[[tracing]]
function sqlite3test:tracing()
local sqlite3 = require("sqlite3")
local db = sqlite3.open_memory()
db:trace( function(ud, sql)
print("[Sqlite Trace]: %s", sql)
end )
db:exec[=[
CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR );
INSERT INTO test VALUES (NULL, 'Hello World');
INSERT INTO test VALUES (NULL, 'Hello Lua');
INSERT INTO test VALUES (NULL, 'Hello Sqlite3');
]=]
for row in db:rows("SELECT * FROM test") do
print(row.content)
end
end
--[[batch sql str]]
--批处理
function sqlite3test:batchsql()
local sqlite3 = require("sqlite3")
local db = sqlite3.open_memory()
local sql=[=[
CREATE TABLE numbers(num1,num2,str);
INSERT INTO numbers VALUES(1,11,"ABC");
INSERT INTO numbers VALUES(2,22,"DEF");
INSERT INTO numbers VALUES(3,33,"UVW");
INSERT INTO numbers VALUES(4,44,"XYZ");
SELECT * FROM numbers;
]=]
local showrow = function(udata,cols,values,names)
assert(udata=='test_udata')
for i=1,cols do
print('%s |-> %s',names[i],values[i])
end
return 0
end
db:exec(sql,showrow,'test_udata')
end
--[[update hook]]
--表事件监听 eg:有在一张表插入数据，则读取更新数据
function sqlite3test:updateHook()
local sqlite3 = require("sqlite3")
local db = sqlite3.open_memory()
local optbl = {
[sqlite3.UPDATE] = "UPDATE";
[sqlite3.INSERT] = "INSERT";
[sqlite3.DELETE] = "DELETE"
}
setmetatable(optbl,
{
__index=function(t,n)
return string.format("Unknown op %d",n)
end
})
local udtbl = {0, 0, 0}
db:update_hook( function(ud, op, dname, tname, rowid)
print("Sqlite Update Hook: %s,%s,%s,%d", optbl[op], dname, tname, rowid)
end, udtbl)
db:exec[[
CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR );
INSERT INTO test VALUES (NULL, 'Hello World');
INSERT INTO test VALUES (NULL, 'Hello Lua');
INSERT INTO test VALUES (NULL, 'Hello Sqlite3');
UPDATE test SET content = 'Hello Again World' WHERE id = 1;
DELETE FROM test WHERE id = 2;
]]
for row in db:nrows("SELECT * FROM test") do
print('%d %s', row.id, row.content)
end
end
return sqlite3test
- cocos2dx-lua sqlite3使用
- cocos2dx sqlite3封装使用
- cocos2dx sqlite3封装使用
- Cocos2dx学习之---使用Sqlite3的笔记
- cocos2dx使用lua和protobuf
- cocos2dx使用lua和protobuf
- cocos2dx使用lua和protobuf
- cocos2dx使用lua和protobuf
- cocos2dx使用lua和protobuf
- Cocos2dx-lua安装并使用
- cocos2dx lua 3.10 使用cjson
- cocos2dx lua 灵活使用table类
- 关于cocos2dx之lua使用TableView
- cocos2dx导入和使用lua文件
- cocos2dx lua 多个定时器同时使用
- cocos2dx是怎么使用lua引擎的
- cocos2dx中protobuf的lua使用
- cocos2dx 创建lua项目 scheduler使用
- JAVA课程1hola,word
- Servlet 上下文数据共享
- C语言初步-第36讲： 用循环处理文字(血型统计 )
- python基础入门三
- 程序设计，功能设计，数据库设计，第三方支付，web安全
- cocos2dx-lua sqlite3使用
- Windbg定位异常系列
- Python的另一种开发环境--Anaconda中的Spyder
- 3课:单片机存储器结构
- string和stringstream用法
- 数据结构与算法 ADT(抽象数据类型)
- 自定义View圆角边框
- 退出作用域时自动调用指定代码
- 16-模板和泛型编程
