[SOLVED] Databases / Dealing with lots of data

[PiFace]

You, of course, shouldn't load graphics for all 3.2k cards all at once - a measly 256x256 image is full 256 kilobytes of data, times 3.2k that becomes 800 megs of graphics memory. Only load it when it has to show up on the screen.

Yes, card pics will only be loaded when they show up. Decks consist of 30 cards and you can repeat them up to 3 times, so that's usually less than 60 card images per match. Deck edit screen won't show every the images right away either...
-----
Can I ask one more question? I realized that having two tables was not practical at all so I merged them together before going back to löve to test if I can keep any data from the db file in a table in lua, but even working with the callback function, there are some errors:

Code: Select all

--require stuff
local sqlite3 = require "sqlite3"
local db = sqlite3.open("cards.db")
local cards = {}

function love.load()

    --build the cards table
    if db then       
        local sQuery = 'SELECT * FROM base;'
        db:exec(sQuery,to_table)
    end
    
    --print(cards[13].name)  --this is commented out because it caused the error
    
end

--callback for the db:exec
function to_table(udata,cols,values,names)

    print('table for a card created')
    cards[values[1]] = {}    
    
    for i=1,cols do        
        cards[values[1]][names[i]] = values[i]
        print('table element: ' .. cards[values[1]][names[i]])
    end
    
    return 0
    
end

(the first column values[1] is the id of each card, so I created a table for each card and inside their respective table, their own data is added) it seems like it works fine, it prints all 118 entries I have so far, but I can't use it outside the function whatsoever. a simple print after the function being executed gives the the 'attempt to index a nil value' error, as if none of the values were registered or no index was created, even though there are more than 13 entries and there is a column called 'names' in the database. What is wrong here? If you want the whole .love file I can post it here. Thank you!

[raidho36]

Well yeah, the table with results only exists during callback function execution and is deleted immediately after it returns.

[PiFace]

So there's probably some other function in the docs that allow me to actually use the data, and not just... print it. I'm gonna read it again.

[raidho36]

I think the problem might be that you're reading from wrong fields. Try fetching 1 card and printing out the whole table to see what it looks like after readout.

[PiFace]

What you mean is I should try selecting 1 row, execute that select query, then try to print it somewhere else outside the callback?

EDIT: wait wait, something is fishy here, I stopped trying to use an index I thought would work, and instead, as you suggested, printed the table itself (the cards table) outside the function, and the hex identifiers of all cards were there.

this, for example, gave me the names of every card, but of course not in order.

Code: Select all

for i, v in pairs( cards ) do
        print(i, v.name)
end

but if I try with a regular number index, there is the 'nil index' error. Maybe the id "numbers" of the database are not behaving as such, so when I type cards[1], the game can't find the card, because there is not 1 index, but something that looks like 1 but is not 1. the console prints 1 when I tell it to, but errors occur when using 1 as index. Any thoughts?

[Positive07]

Note that:

Code: Select all

tab = {[1] = "Number", ["1"]="String"}
print(tab[1], tab["1"]) -- Output: Number String

So index may be strings and not numbers and that is why cards[1] doesn't work, try cards[tostring(1)] where 1 can be any valid index.

Also note that #cards won't tell you how many values are in the table since the keys are not valid numbers and the table can't be parsed as an array.

An option would be to do:

Code: Select all

local newcards = {}
for k,v in pairs(cards) do
   newcards[tonumber(k)]=v
end
cards = newcards

print(cards[1])

[raidho36]

I just realized, you seem to be trying to access the table immediately after you query the database. As airstruck said, the library is asynchronous, so when you do this kind of fetch operation you shouldn't expect that there will be any data until after callback was actually called. The "exec" function only tells database driver it wants some results and it wants it to call your function once it's done fetching; it sets off the process but itself it doesn't actually do anything and returns immediately. Before you can access your data you should wait until it's actually there.

The idea behind non-blocking (asynchronous) operations is that the request function will return immediately and the program will continue execution uninterrupted. As opposed to blocking function call which will halt the program until it returns - which may not even happen at all, in some pathological scenarios. This is relevant for input/output and especially network. A filesystem or socket operation may take a very long time and using blocking interface causes program to freeze for the whole period. With non-blocking interface the program never freezes due to waiting on function to complete, but it can't expect that function call will immediately do anything either. So you'll have to code your program in a way that expects that result of function call will be there some time after it's been called, and not necessarily very soon.

[PiFace]

An option would be to do:

Code: Select all

local newcards = {}
for k,v in pairs(cards) do
   newcards[tonumber(k)]=v
end
cards = newcards

print(cards[1])

YES!!! That's exaclty what happened, the numbers were actually strings. But to avoid making another table, I just used tonumber in the callback function so it would be stored as a number already:

Code: Select all

function to_table(udata,cols,values,names)
    cards[tonumber(values[1])] = {}    
    for i=1,cols do        
        cards[tonumber(values[1])][names[i]] = values[i]        
    end
    return 0
end

I'll have to do the same for all other numeric values in the database, though, so I don't have to say 'tonumber' all the time later. Something like this should do the work:

Code: Select all

function to_table(udata,cols,values,names)
    cards[tonumber(values[1])] = {}
    local insertValue
    for i=1,cols do                
        if --[[check if values[i] is a numeric value, and if it returns true]] then
            insertValue = tonumber(values[i])
        else
            insertValue = values[i]
        end
        cards[tonumber(values[1])][names[i]] = insertValue
    end
    return 0
end

I just have to find a way to do this check, so I'll go to the lua docs. Everything is working fine now, thank you guys!

@raidho36 even after this worked ok in the load function, do you think if I have the whole 3.2k entries it would take too long to save all of them into the table right away and thus they won't be ready immediatly after?

EDIT: by the way, I've read in the forum rules we should give karma to people who help us, but I can't see any '+' button, is it disabled for new users?

[zorg]

The karma system is long gone, hence why it was crossed out in the forum rules.
Also:

Code: Select all

if type(values[i]) == 'number' then

Then again, comparisons like that may be more costly because of branching than calling tonumber on all elements... not sure though, would need to benchmark to see.

[raidho36]

At this point I'm just talking out of my ass here because this library is nothing like I expect it would be like. It uses callbacks yet it's not async? It outputs numerical values as strings? I don't know what the hell is wrong with that thing.