Help with a SQLITE issue

Getting Started Newbie Questions
#1

Hi

I have an app currently Live and in app I use a sqlite table to store game highscore. Then when i needed to add shuffle feature, i added a shuffle Column by first checking if it exists.

Current Code

local function setupDatabase() local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory) local db = sqlite3.open(dbPath) local playerSetup = [[CREATE TABLE playerInfo(id INTEGER PRIMARY KEY autoincrement, highscore); INSERT INTO playerInfo VALUES(NULL, '0');]] db:exec(playerSetup) if not dbColumnExists( db, "playerInfo", "shuffles") then local sql = "alter table playerInfo add column shuffles" db:exec(sql) db:close() M.shuffles = 3 -- Set to default M.savePlayerInfo() else db:close() end end setupDatabase()

Now I want to add a Save Score feature and this is my new code:

local function setupDatabase() local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory) local db = sqlite3.open(dbPath) local playerSetup = [[CREATE TABLE playerInfo(id INTEGER PRIMARY KEY autoincrement, highscore); INSERT INTO playerInfo VALUES(NULL, '0');]] db:exec(playerSetup) if not dbColumnExists( db, "playerInfo", "shuffles") then local sql = "alter table playerInfo add column shuffles" db:exec(sql) --db:close() M.shuffles = 3 -- Set to default M.savePlayerInfo() end if not dbColumnExists( db, "playerInfo", "startscore") then local sql = "alter table playerInfo add column startscore" db:exec(sql) M.savePlayerInfo() M.saveScore() end if not dbColumnExists( db, "playerInfo", "savecount") then local sql = "alter table playerInfo add column savecount" db:exec(sql) M.savecount = 3 M.savePlayerInfo() end db:close() end setupDatabase()

Issue faced:

The issue I am facing now is when someone installs the LIVE app and buys shuffles, lets say 10 credits for shuffle, and then installs the new update, the 10 credits for shuffle gets wipped out.

Is there a better way to ensure I preserve existing shuffle credits in database?

Thanks

#2

It’s possible that your CREATE TABLE is overwriting the previous version. Most people using SQLite do something like:
 

CREATE TABLE IF NOT EXISTS tablename …

That way if the table exists, it doesn’t try to create the table again.

Rob

#3

Thanks Rob. i just tried it now but still having same issue:

local playerSetup = [[

   CREATE TABLE IF NOT EXISTS playerInfo(id INTEGER PRIMARY KEY autoincrement, highscore);

   INSERT INTO playerInfo VALUES(NULL, ‘0’);

]]

db:exec(playerSetup)

#4

I think we re going to need to see your savePlayerInfo() code.

Rob

#5

This is it:

M.savePlayerInfo = function() local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory) local db = sqlite3.open(dbPath) local update = "UPDATE playerInfo SET highscore='" .. M.highscore .."', shuffles='" .. M.shuffles .. "', savecount='" .. M.savecount .. "' WHERE id=1"     db:exec(update) db:close() end
#6

It’s possible that your CREATE TABLE is overwriting the previous version. Most people using SQLite do something like:
 

CREATE TABLE IF NOT EXISTS tablename …

That way if the table exists, it doesn’t try to create the table again.

Rob

#7

Thanks Rob. i just tried it now but still having same issue:

local playerSetup = [[

   CREATE TABLE IF NOT EXISTS playerInfo(id INTEGER PRIMARY KEY autoincrement, highscore);

   INSERT INTO playerInfo VALUES(NULL, ‘0’);

]]

db:exec(playerSetup)

#8

I think we re going to need to see your savePlayerInfo() code.

Rob

#9

This is it:

M.savePlayerInfo = function() local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory) local db = sqlite3.open(dbPath) local update = "UPDATE playerInfo SET highscore='" .. M.highscore .."', shuffles='" .. M.shuffles .. "', savecount='" .. M.savecount .. "' WHERE id=1"     db:exec(update) db:close() end