Create a database only once. - HELP

Solar2D Lua Language
#1

Hello everyone.
Here I have another question for the awesome people here in the Corona Forums.

I want to use a database to store the data of the player.

My problem is that I don’t want to insert new rows to the database every time the player initiates the app.

 

require "sqlite3" local nobeliskData = sqlite3.open\_memory() nobeliskData:exec[[CREATE TABLE slots (id INTEGER PRIMARY KEY, class, stage); INSERT INTO slots VALUES (NULL, 'empty', 1); INSERT INTO slots VALUES (NULL, 'empty', 1); INSERT INTO slots VALUES (NULL, 'empty', 1)]] end print( "version " .. sqlite3.version() ) for row in nobeliskData:nrows("SELECT \* FROM slots") do print(row.class) end

as a summarize, I want to create the database and insert the rows only once even after the user restarts the app.

I would like to know what is the best practice for doing this.

 the rows will be updated later.

Thank you very much.
Best Regards,

Lucas Peralta [Chile]

#2

Well for one, when creating the table, do this:  CREATE TABLE IF NOT EXISTS 

instead of just CREATE TABLE

Next, you can do a query to get a count of rows and then if you have data, don’t insert new stuff.

#3

Thank you dear Rob.

#4

Well for one, when creating the table, do this:  CREATE TABLE IF NOT EXISTS 

instead of just CREATE TABLE

Next, you can do a query to get a count of rows and then if you have data, don’t insert new stuff.

#5

Thank you dear Rob.

#6

I am using database to keep the highscore record. On storyboard scene change i make file to display highscore. I make database first, then table, then retrieve the record. Check if now made score is more than the existing in database. Update the first existing record.

Its working perfectly fine on the simulator, but on device it stucks on the previous scene and never changes the scene to the highscore file.

It was changing scene before database was implemented.

[lua] --Include sqlite
require “sqlite3”

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

local tablesetup = [[CREATE TABLE IF NOT EXISTS highScoreClassic (id INTEGER PRIMARY KEY, Time, Taps);]]
print(tablesetup)
db:exec( tablesetup )

local dbRows = 0
local dbTaps = 0
local dbTime = “00:00:00”

for row in db:nrows(“SELECT * FROM highScoreClassic WHERE id=1;”) do
dbRows = dbRows + 1
dbTaps = row.Taps
dbTime = row.Time
end

function isHighscore()
if myData.currentScore > dbTime then
return true
elseif myData.currentScore == dbTime and myData.taps > dbTaps then
return true
elseif myData.currentScore == dbTime and myData.taps < dbTaps then
return false
else return false end
end

function saveToDataBase()
print(“updating”)
local q = [[UPDATE highScoreClassic SET Time=’]]…myData.currentScore…[[’, Taps=]]…myData.taps…[[WHERE id=1;]]
db:exec( q )
end

function saveToDataBaseFirstTime()
print(“inserting”)
local tablefill =[[INSERT INTO highScoreClassic VALUES (NULL, ‘]]…myData.currentScore…[[’,’]]…myData.taps…[[’);]]
db:exec( tablefill )
end

if ( dbRows > 0 ) then
if ( isHighscore() ) then
print(dbTime, dbTaps)
note = display.newText( “Congratulations !!!\nNew High Score\n” … "Time: " … myData.currentScore … "\nTaps: " … myData.taps, 10 , display.contentHeight/2, “Harrowprint”, 42)
saveToDataBase()
else
note = display.newText( "Time: " … myData.currentScore … "\nTaps: " … myData.taps, 10 , display.contentHeight/2, “Harrowprint”, 42)
end

else
note = display.newText( “Congratulations !!!\nNew High Score\n” … "Time: " … myData.currentScore … "\nTaps: " … myData.taps, 10 , display.contentHeight/2, “Harrowprint”, 42)
saveToDataBaseFirstTime()
print(“firsTime”)
end[/lua]

#7

Please read this blog post:

https://www.coronalabs.com/blog/2013/07/09/tutorial-basic-debugging/

And provide the output from your device’s console log, so we can see what error(s) are being generated please.

#8

I am using database to keep the highscore record. On storyboard scene change i make file to display highscore. I make database first, then table, then retrieve the record. Check if now made score is more than the existing in database. Update the first existing record.

Its working perfectly fine on the simulator, but on device it stucks on the previous scene and never changes the scene to the highscore file.

It was changing scene before database was implemented.

[lua] --Include sqlite
require “sqlite3”

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

local tablesetup = [[CREATE TABLE IF NOT EXISTS highScoreClassic (id INTEGER PRIMARY KEY, Time, Taps);]]
print(tablesetup)
db:exec( tablesetup )

local dbRows = 0
local dbTaps = 0
local dbTime = “00:00:00”

for row in db:nrows(“SELECT * FROM highScoreClassic WHERE id=1;”) do
dbRows = dbRows + 1
dbTaps = row.Taps
dbTime = row.Time
end

function isHighscore()
if myData.currentScore > dbTime then
return true
elseif myData.currentScore == dbTime and myData.taps > dbTaps then
return true
elseif myData.currentScore == dbTime and myData.taps < dbTaps then
return false
else return false end
end

function saveToDataBase()
print(“updating”)
local q = [[UPDATE highScoreClassic SET Time=’]]…myData.currentScore…[[’, Taps=]]…myData.taps…[[WHERE id=1;]]
db:exec( q )
end

function saveToDataBaseFirstTime()
print(“inserting”)
local tablefill =[[INSERT INTO highScoreClassic VALUES (NULL, ‘]]…myData.currentScore…[[’,’]]…myData.taps…[[’);]]
db:exec( tablefill )
end

if ( dbRows > 0 ) then
if ( isHighscore() ) then
print(dbTime, dbTaps)
note = display.newText( “Congratulations !!!\nNew High Score\n” … "Time: " … myData.currentScore … "\nTaps: " … myData.taps, 10 , display.contentHeight/2, “Harrowprint”, 42)
saveToDataBase()
else
note = display.newText( "Time: " … myData.currentScore … "\nTaps: " … myData.taps, 10 , display.contentHeight/2, “Harrowprint”, 42)
end

else
note = display.newText( “Congratulations !!!\nNew High Score\n” … "Time: " … myData.currentScore … "\nTaps: " … myData.taps, 10 , display.contentHeight/2, “Harrowprint”, 42)
saveToDataBaseFirstTime()
print(“firsTime”)
end[/lua]

#9

Please read this blog post:

https://www.coronalabs.com/blog/2013/07/09/tutorial-basic-debugging/

And provide the output from your device’s console log, so we can see what error(s) are being generated please.