sqlite3 数据库

数据库创建

--------------------------------------------
-- 数据库sqlite3
sqliet3 = require("lsqlite3")

sqlite3db = nil

------------------------
-- 重载assert函数
local assert_,assert = assert,function(test)
	if not test then
		error(sqlite3db:errmsg(),2)
	end
end

DBFileName = cc.FileUtils:getInstance():getWritablePath()
DBFileName = DBFileName .. "/test.db"
DBManager = {}

-- 暂停定时保存数据库
function DBManager.pauseSaveDB()
	if DBManager.scheduleSaveDB then
		local scheduler = cc.Director:getInstance():getScheduler()
		scheduler:unscheduleScriptEntry(DBManager.scheduleSaveDB)
		DBManager.scheduleSaveDB = nil
	end
end

-- 定时保存数据库
function DBManager.resumeSaveDB()
	if not DBManager.scheduleSaveDB then
		local scheduler = cc.Director:getInstance():getScheduler()
		DBManager.scheduleSaveDB = scheduler:scheduleScriptFunc(DBManager.saveDB,5,false)
	end
end

-- 保存数据库
function DBManager.saveDB()
	sqlite3db:loadOrSaveDb(DBFileName,1)
end

-- 打开数据库 
function DBManager.openDB()
	local db = sqliet3:open_memory()
	-- 定时保存数据库
	DBManager.resumeSaveDB()
	return db
end

---------------------------------------------------------------------------
-- 关闭数据库
function DBManager.closeDB()
	assert(sqlite3db:close() == sqlite3.OK)
end

-- 删除表数据
function DBManager.deleteTable()
	local tableName = { "player", "level"}
	for name, tables, in pairs(tableName) do 
		local sqlStr = string.format("delete from '%s'",tables)
		assert(sqlite3db:exec(sqlStr) == sqlite3.OK)
	end 
end

local retrieveTableColums = function()
	LevelTable.retriveColum()
end

-- 创建表
function DBManager.createList()
	local sqlStr = [[
	create table if not exists player(
	player_id    integer    not null,
	roleid      char(60),
	primary key (player_id)
	);

	create table if not exists level(
	player_id 		integer  		not null,
	level_name		char(60),
	foreign key (player_id) references player (player_id)
	);
	create index if not exists relationship_test1_fk on bloodpoint (
	player_id asc
	);
	]]
	assert(sqlite3db:exec(sqlStr) == sqliet3.OK)

	-- 恢复数据库列表
	retrieveTableColums()
end

-- 创建数据库
function DBManager.createDB()
	
	-- 创建前，保证移除旧的数据库
	os.remove(DBFileName)
 	-- 打开数据库链接，相当于新建数据库
 	sqlite3db = DBManager.openDB()
 	-- 创建数据库表
 	DBManager.createList()
 	-- 保存数据库
 	DBManager.saveDB()
end

-- 初始化数据库
function DBManager.initDB()
	sqlite3db:exec("begin transaction")
	------------------------
	-- 初始化
	--
	------------------------

	sqlite3db:exec("commit transaction")
	sqlite3db:exec("end transaction")
end

----------------------
-- 启用数据库
function DBManager.setDBDate()
	local isExist = cc.FileUtils:getInstance():isFileExist(DBFileName)
	if not isExist then

		DBManager.createDB()
		DBManager.initDB()
		DBManager.saveDB()
	else
		sqlite3db = DBManager.openDB()
		sqlite3db:loadOrSaveDb(DBFileName,0)
		DBManager.createList()
	end
end

function main()
	DBManager.setDBDate()
end

表操作

-----------------------------------------------------------------------
-- 表操作
LevelTable = { name = "level" }

-- 恢复表列字段
function LevelTable.retriveColum()
	local str = [[select sql from sqlite_master where type = 'table' and name = '%s']]
	local sql = string.format(str, LevelTable.name)
	assert(sqlite3db:exec(sql) == sqlite3.OK)
	local curTableSql = ""
	for a in sqlite3db:nrows(sql) do
		curTableSql = a['sql']
		break
	end
	local colums = {
		player_id = "integer", 
		level_name = "char(60)",
		last_update = "data",			-- 本地时间
	}
	for columName,columType in pairs(colums) do
		if string.find(curTableSql,columName) == nil then
			local str = "ALTER TABLE '%s' ADD '%s' '%s'"
			local sql = string.format(str,LevelTable.name,columName,columType)
			assert(sqlite3db:exec(sql) == sqlite3.OK)
		end
	end
end

-- 插入
function LevelTable.insertRecord(levelid,playerid)
	-- 当前时间 datetime(CURRENT_TIMESTAMP, 'localtime')
	local str = [[insert into level (player_id,level_name,last_update) 
	values('%d','%s',datetime(CURRENT_TIMESTAMP, 'localtime'))]]
	local sql = string.format(str,playerid,levelid)
	assert(sqlite3db:exec(sql) == sqlite3.OK)
end

-- 查询
function LevelTable.selectRecord(playerid)
	local str = [[select level_name from level where player_id = %d]]
	local sql = string.format(str,playerid)
	assert(sqlite3db:exec() == sqlite3.OK)
	for a in sqlite3db:nrows(sql) do
		return a.level_name
	end
end

-- 更新
function LevelTable.updateRecord(levelid,playerid)
	local str = [[update level set level_name = '%s',last_update = datetime(CURRENT_TIMESTAMP, 'localtime') 
	where player_id = %d]]
	local sql = string.format(str,levelid,playerid)
	assert(sqlite3db:exec(sql) == sqlite3.OK)
end

-- 清空数据
function LevelTable.deleteRecord()
	local sql = string.format("delete from level")
	assert(sqlite3db:exec(sql) == sqlite3.OK)
end