[SOLVED] Databases / Dealing with lots of data

[PiFace]

Hi everyone, this is almost my first post here, and I know this matter has been discussed around here before but the respective threads are long dead, so I thought it would be more efficient to create another one and ask what I need anyway. What I'm mostly looking for is some opinion/advice on what solution to use for my project, cause I'm a bit lost

What I'm trying to achieve is a pc version of a card game, and I'll focus on making at least a menu to edit the decks, and an online or LAN mode to play against other people (I would later try to make an AI mode for offilne play, IF I suceeded doing the rest, lol) and then I got to the main problem about making this: there are about 3.2k cards in the game, what is the best way to keep their data accessible for the code? What is the most efficient way to ease the task of whoever's gonna type all that data? (actually a lot of copy-pasting, but you get the ideia)

Yes, I'm trying the SQLite3 library available on the wiki, but there are no references about how to allow LÖVE to read the data. (at least I haven't seen them. If somebody knows how, please tell me, it would solve most of the problems ) So maybe I should get to know other methods, including the ones I've read around the forums, like simply making the database in .lua. The problem is typing in the data in a plain lua file takes a lot more time, although it doesn't need any library, plugin or anything, just require the file and it is ready to use. This is plan B if other solutions don't work.

Another problem is about checking if both players that are connected have no differences in their databases. If the database were purely online, there couldn't be any offline mode, so the database should probably be local... but I'm not sure, do you guys have any ideas? Any idea on the general approach would be highly appreciated, thank you!

(by the way, is local networking a thing in Löve? Because as I know it has online support built in, I supposed LAN is just as simple, but correct me if I'm wrong.)

[raidho36]

Well 3.2k is not that many and you can simply load them all to a single table, using card name as key and table describing the card as value. As opposed to only loading the necessary data once it's needed and discarding it once it's no longer needed.

Code: Select all

cards = { "foo" = { bar = 123, etc } }

Of course exactly how you will do it depends on how your data is stored already, to avoid converting it back and forth. If it's already in JSON or XML format it should be trivial to load it. But if it's just a text file with standardized card descriptions, that is easily loadable as well. If it's in the database, you simply SELECT * WHERE 1 = 1 and that returns list of all cards, you simply read it row by row to load into your game. I have no clue what library you work with but they all fairly similar for any language and any database. Some of them have to work with cursor but I'm sure the one you're using just returns a full table of results.

One way to check if same database file is used is to compare its hash sum. MD-5 is a common method but you can use SHA-2 for which no collisions have been reported to date, i.e. it's not practically possible to make a different file produce same SHA-2 sum. Otherwise you might just send data about specific cards being used and compare them to some original. Note that forcing players to use some standard database will prevent modding - just in case this wasn't immediately obvious.

[PiFace]

Yeah, using that SQLite3 library I tried to mimic the example given by the creator, doing the first steps, requiring the library and opening the database. (the database is called cards, there are two tables, one for text and the other for the numeric data)

Code: Select all

local sqlite3 = require "sqlite3"
local cards = sqlite3.open("cards.db")
local test

if cards then
        local sQuery = "SELECT name FROM text WHERE id = 10;"  --this 10 could be any ID, just a random entry
        test = cards:execute(sQuery)
        print(test)
end

Here is CentauriSoldier's example on github:

Code: Select all

--create/open database
    local hDB = sqlite3.open("mydatabase.db");

      if hDB then
      local sQuery = "CREATE TABLE test (id INTEGER PRIMARY KEY   AUTOINCREMENT, name CHAR(20));";
      hDB:execute(sQuery);

      hDB:close();
      end

The problem is that when I run and check the console (or love.graphics.print, I tried with it too) the value printed is not the string, it's just a zero. For any value selected, it only returns the number zero. I understand the basics of sqlite commands, it's very simple, I'm just confused about how I should use it inside this example. If it worked like this at least once, I could figure out how to access the rest of the data, but it didn't .-. I'm pretty sure it's a stupid mistake, happens all the time haha.

but about the database check... yeah I was still considering including modding or not, but I was mostly worried about cheating and stuff. Probably only me and my friends are gonna play this anyway, so that actually shouldn't be a problem... but I'll keep your tip in mind.

[Positive07]

Well he just wrapped Josefnpat's builds of the Lua SQLlite3 wrappers library so you may as well use that and look at the documentation. Note that the builds on that thread Josefnpat made are for LuaSQLlite3 0.9.1, there have been two minor releases since then and it is now at 0.9.3

[Beelz]

If it were me, I would go with the XML route just to avoid all that copy and pasting... You can google 'convert database to xml' and then load that file into your game with something like this. Although a google search may find a way to convert a database to Lua. Not sure, havn't tried...

EDIT:
Didn't see Positive07's above post, that's probably a better idea

[raidho36]

I guess the 0 is an error code. It means success. Now the database controller object stores result of your query, you should get the rows using appropriate function.

[airstruck]

For any value selected, it only returns the number zero.

LuaSQLite3 API is asynchronous, you need to pass a callback to db:exec. Take another look at the docs.

[Evine]

Well 3.2k is not that many and you can simply load them all to a single table, using card name as key and table describing the card as value. As opposed to only loading the necessary data once it's needed and discarding it once it's no longer needed.

This. Even if you have a massive amount of properties on each card the total dataset will only be a few megabytes. So I think you should focus on how to create the card data in the first place.

I'd suggest either making a simple formatted file that you parse. Or make a minimal editor in love2d that writes .lua files. Both of these methods can help you avoid doing simple copy-paste mistakes.

[PiFace]

@Positive07, @raidho36 and @airstruck - Thank you a lot! I really had to check the docs again, and what you guys said helped me understand how the luasqlite works. I can loop through data in the callbakc function for the exec, store it inside a table or something and use the retrieved data elsewhere if needed.

@Evine - yeah, I'm aware the file size is not a big deal. What you and raidho36 suggested is retrieving all the data on load and keeping it in a table, right? As you said, 3.2k is not that much so it wouldn't be hard for the system to manage it at runtime. (or would it?)

and again, thank you guys! you helped me a lot!

[raidho36]

Depending on how much data there is per card, that should be between under a megabyte and few tens of megabytes. 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. Nowdays there's a solid chance that host machine will handle it anyway but that's still a poor idea.