SQLite INSERT/ UPDATE

[matus]

Hello,

as many of us, I am new to LUA. For my game I needed a simple key,value pair database. I have come with this solution.

The code is part of settings.lua module file, therefore the settings:dbget and settings:dbset notation.

the settings:dbset function checks if entry is present in the db, if yes, it performs an update, if not, it performs an insert.

Critics and Comments are welcomed - I want to know if I did this right.

[lua]-- set table name
tname = “animals”

–Include sqlite
require “sqlite3”
–Open data.db. If the file doesn’t exist it will be created

local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

–Handle the applicationExit event to close the db
local function onSystemEvent( event )
if( event.type == “applicationExit” ) then
db:close()
end
end

–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS]]…tname…[[(id INTEGER PRIMARY KEY, pname, pvalue);]]
db:exec( tablesetup )

–setup the system listener to catch applicationExit
Runtime:addEventListener( “system”, onSystemEvent )

function settings:dbset(pname, pvalue)

local sql = [[SELECT * FROM]]…tname…[[WHERE pname = “]]…pname…[[”]]
local rowcount = 0

for row in db:nrows( sql ) do
rowcount = rowcount + 1
end

if rowcount > 0 then
–print (“update”)
db:exec ( [[UPDATE]]…tname…[[SET pvalue = “]]…pvalue…[[” WHERE pname = “]]…pname…[[”]] )
else
–print (“insert”)
db:exec ( [[INSERT INTO]]…tname…[[VALUES (NULL, “]]…pname…[[”,"]]…pvalue…[[");]] )
end

end – function dbset
function settings:dbget(pname)

local sql = [[SELECT * FROM]]…tname…[[WHERE pname = ?]]
local stmt = db:prepare(sql)
stmt:bind_values(pname)
stmt:step()
r = stmt:get_value(2)
stmt:finalize()
return ®

end – function dbget
settings:dbset(“audiolanguage”,“slovak”)

local r = settings:dbget(“audiolanguage”)
print®[/lua]
[import]uid: 10077 topic_id: 4394 reply_id: 304394[/import]

[matus]

updated the code a bit:

[lua]--------------------------------------------------------------------------------
– STORAGE ENGINE

– set table name
tname = “animals”

–Include sqlite
require “sqlite3”
–Open data.db. If the file doesn’t exist it will be created

local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

–Handle the applicationExit event to close the db
local function onSystemEvent( event )
if( event.type == “applicationExit” ) then
db:close()
end
end

–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS]]…tname…[[(id INTEGER PRIMARY KEY, pname, pvalue);]]
db:exec( tablesetup )

–setup the system listener to catch applicationExit
Runtime:addEventListener( “system”, onSystemEvent )

---

– Function to check if a property entry exists in the FB
– @param pname is the name of the property
– @returns Boolean true/false

local function isPropertySet(pname)

local sql = [[SELECT * FROM]]…tname…[[WHERE pname = “]]…pname…[[”]]
local rowcount = 0

for row in db:nrows( sql ) do
rowcount = rowcount + 1
end

return rowcount > 0

end – function checkProperty

– Function to set or update a key-value pair
– @param pname Is the of the property to set
– @param value Is value to set

function settings:dbset(pname, pvalue)

if isPropertySet(pname) then
–print (“update”)
db:exec ( [[UPDATE]]…tname…[[SET pvalue = “]]…pvalue…[[” WHERE pname = “]]…pname…[[”]] )
else
–print (“insert”)
db:exec ( [[INSERT INTO]]…tname…[[VALUES (NULL, “]]…pname…[[”,"]]…pvalue…[[");]] )
end

end – function dbset

– Function to retrieve the value of a key-value pair in the DB
– @param pname Is the name of the property whose value we need
– to retrieve
– @returns The stored value or nil

function settings:dbget(pname)

if isPropertySet(pname) then
–print (“entry exists”)
sql = [[SELECT * FROM]]…tname…[[WHERE pname = ?]]
stmt = db:prepare(sql)
stmt:bind_values(pname)
stmt:step()
r = stmt:get_value(2)
stmt:finalize()
return ®
else
–print (“entry does not exist”)
return nil
end
end – function dbget

– READ SETTIGNS FROM DB

–settings:dbset(“audiolanguage”,“slovak”)

local r = settings:dbget(“audiolanguage”)
print®
[/lua] [import]uid: 10077 topic_id: 4394 reply_id: 13720[/import]