SQL help

Games
#1

Im trying to build a database from a text file containing scrabble words. I cant get the following code to work though, any ideas what Im doing wrong?

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

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

local tablesetup = [[CREATE TABLE IF NOT EXISTS WORD_DATA (WORD_ID TEXT PRIMARY KEY);]]
db:exec( tablesetup )

local path = system.pathForFile( “twl06.txt”, system.DocumentsDirectory )
local fh = io.open( path )

for line in fh:lines() do
print(line)
local tablefill =“INSERT INTO WORD_DATA (WORD_ID) VALUES(’”…line…" ); "
db:exec( tablefill )
end

for row in db:nrows(“SELECT * FROM WORD_DATA”) do
print(row.WORD_ID … " ")
end
[/code] [import]uid: 28912 topic_id: 10414 reply_id: 310414[/import]

#2

Line 20 is missing ’ after double quite.

[lua]local tablefill =“INSERT INTO WORD_DATA (WORD_ID) VALUES(’”…line…"’ ); "[/lua] [import]uid: 61610 topic_id: 10414 reply_id: 37916[/import]

#3

I checked the code and changed this line as well

[lua]local path = system.pathForFile( “twl06.txt” )[/lua]

Just so the text file was inside my project. All works now. [import]uid: 61610 topic_id: 10414 reply_id: 37917[/import]

#4

Thanks for the help. I noticed that it is taking a very long time to build the database. So Ive tried to changed the code to speed up the process, seem to be having trouble with the new code as well though.

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

local tablesetup = [[CREATE TABLE IF NOT EXISTS WORD_DATA (WORD_ID TEXT PRIMARY KEY);]]
db:exec( tablesetup )

local path = system.pathForFile( “twl06.txt”, system.DocumentsDirectory )
local fh = io.open( path )

local tablefill = “”
local lines = {}
local i = 1
for line in fh:lines() do
lines[i] = line
–print(lines[i])
tablefill = tablefill… “INSERT INTO WORD_DATA (WORD_ID) VALUES(’”…lines[i]…"’); "
if i == 100 then
print(tablefill)
db:exec( tablefill )
return true
end
i = i + 1
end

for row in db:nrows(“SELECT WORD_ID FROM WORD_DATA”) do
print(row.WORD_ID … " ")
end
–Runtime:addEventListener(“enterFrame”, onSystemEvent)

fh:close()
[/code] [import]uid: 28912 topic_id: 10414 reply_id: 37942[/import]

#5

[code]
local file = io.open( path, “r” )
if file then
– read all contents of file into a string
contents = file:read( “*a” )
io.close( file )
end

local word = string.find(contents, " “…testWord…”")
print(word)
if word then
print(testWord)
else
print(“Thats not a word”)
end
[/code] [import]uid: 28912 topic_id: 10414 reply_id: 37999[/import]

#6

Hi HavocHare.

I decided to have a look at this and benchmark a little bit.

Your first solution took ca 24 sec’s on my computer, which would equal to forever on an actual telephone. Mind you that I did not have your word file so I created my own with 62.000 words.

Then I did the following, I add transactions, and the time went down to 0. You can’t optimize past that without going back in time.

Here is the code :

[lua]require “sqlite3”
require “os”
local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

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

local tablesetup = [[CREATE TABLE IF NOT EXISTS WORD_DATA (WORD_ID TEXT PRIMARY KEY);]]
db:exec( tablesetup )
local t = os.time()
local path = system.pathForFile( “twl06.txt” )
local fh = io.open( path )
db:exec(“begin transaction”)
local i = 0
for line in fh:lines() do
– print(line)
local tablefill =“INSERT INTO WORD_DATA (WORD_ID) VALUES(’”…line…i…"’ ); "
db:exec( tablefill )
i = i + 1
–print ("The counter is "…i)
end
db:exec(“commit”)
for row in db:nrows(“SELECT * FROM WORD_DATA”) do
–print(row.WORD_ID … " ")
end

print ("This took : "…os.difftime( os.time(), t ) )
print “done”[/lua]
Enjoy.

If you have any sql questions, I’ll gladly help you out of course [import]uid: 61610 topic_id: 10414 reply_id: 38131[/import]

#7

THe one I was working with was around 170,000 words. It took about 25 hours for the following code to build the database.

  
require "sqlite3"  
local path = system.pathForFile("data.db", system.DocumentsDirectory)  
db = sqlite3.open( path )   
  
-- local function onSystemEvent( event )  
 -- if( event.type == "applicationExit" ) then   
 -- db:close()  
 -- end  
-- end  
   
   
local tablesetup = [[CREATE TABLE IF NOT EXISTS WORD\_DATA (WORD\_ID TEXT PRIMARY KEY);]]  
db:exec( tablesetup )  
  
local path = system.pathForFile( "twl06.txt", system.DocumentsDirectory )  
local fh = io.open( path )  
   
  
for line in fh:lines() do  
print(line)  
local tablefill ="INSERT INTO WORD\_DATA (WORD\_ID)VALUES'"..line.."'); "  
 db:exec( tablefill )  
end  
  
for row in db:nrows("SELECT \* FROM WORD\_DATA") do  
--print(row.WORD\_ID .. " ")  
end

Thats some pretty sweet optimization though if you got it down to 0 seconds. I wonder if the print statement slowed the code down alot? Im curious what the following lines of code do?

db:exec("begin transaction")  
db:exec("commit")

Ive actually decided to just put all the words from my word txt file into a variable, and then search that variable instead of using sqlite to search thru a database, it works very well so far, although I cant say for sure which works better as I havent compared my current method vs searching thru a db. [import]uid: 28912 topic_id: 10414 reply_id: 38159[/import]

#8

wow i didn’t know transaction were supported in SQLite.

Transactions is feature which allows you to perform action on sql database “in memory” instead of actual db. The changes are made to the db only when you commit the transaction. You can also rollback transactions if something goes wrong before you commit them. [import]uid: 48521 topic_id: 10414 reply_id: 38163[/import]

#9

ah, ok. I guess that explains why his code runs like 1000x’s faster than mine did haha. [import]uid: 28912 topic_id: 10414 reply_id: 38179[/import]

#10

@traustitj

If i would use sqlite for position, score, level etc and pull that info from a database into a tableview in a game, how would that be setup the best way?

David [import]uid: 34126 topic_id: 10414 reply_id: 38182[/import]