sqlite3 数据库

概述数据库创建 ---------------------------------------------- 数据库sqlite3sqliet3 = require("lsqlite3")sqlite3db = nil-------------------------- 重载assert函数local assert_,assert = assert,function(test) if

数据库创建

---------------------------------------------- 数据库sqlite3sqlIEt3 = require("lsqlite3")sqlite3db = nil-------------------------- 重载assert函数local assert_,assert = assert,function(test)	if not test then		error(sqlite3db:errmsg(),2)	endendDBfilename = 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	endend-- 定时保存数据库function DBManager.resumeSaveDB()	if not DBManager.scheduleSaveDB then		local scheduler = cc.Director:getInstance():getScheduler()		DBManager.scheduleSaveDB = scheduler:scheduleScriptFunc(DBManager.saveDB,5,false)	endend-- 保存数据库function DBManager.saveDB()	sqlite3db:loadOrSaveDb(DBfilename,1)end-- 打开数据库 function DBManager.openDB()	local db = sqlIEt3:open_memory()	-- 定时保存数据库	DBManager.resumeSaveDB()	return dbend----------------------------------------------------------------------------- 关闭数据库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 endlocal 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()	endendfunction 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	endend-- 插入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	endend-- 更新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

总结

以上是内存溢出为你收集整理的sqlite3 数据库全部内容，希望文章能够帮你解决sqlite3 数据库所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。