ID:1916274
 
(See the best response by Nadrew.)
I'm not looking for any advanced answers or anything to read to make me a professional after I have read it. Just something to set me on the path to properly designing and programming the database/saving.

What I learn here will be used to convert some systems over as well.

Question 1: Should I use a different database for each type of thing I am saving? One for player stats, one for objects that need saving, one for and one for global variables?

Question 2: What's a suggested way of organizing a database for efficiency?

Question 3: What are the limitations? (how many games can access that database if you wanted to have multiple hosted games access a database, how many players will be able to call from it -- even though I should call for access once and load it in to memory for further use, etc)

Question 4: Any basic code examples I can use to achieve the saving of player stats, objects, datums, and global variables?
Best response
Are you talking MySQL databases, or SQLite databases? The ability and functionality is similar for both but there are dramatic differences as well.

SQLite is basically SQL-based savefiles, the database is stored in a file on the host system and loaded/accessed as queries are passed through it, spitting out nicely formatted data for you.

It's also implemented a bit more directly into the language than MySQL which has allowed it to store things like icon data directly in the database and load it back without any extra work from the developer.

It's not designed to be used over a network, and in fact will choke if you try loading the data across a network share, which is the source of one of the most common reasons for the pager to fail to load.

MySQL is what you're probably used to, the basic 'connect to a MySQL server, talk to it, get some data back and use it', this works pretty much exactly how it would from PHP or a similar language. It doesn't have the special handling for DM data structures like SQLite does, but it has the ability to be used over a network cooked right in.

Once you clarify what you're asking I can probably help you a bit more with your other questions, I have quite a bit of experience using both database handlers BYOND provides.
You can use one database and store them in tables, or you can use separate. They generally do not use very much space so (we're talking Kbs).

However many games you want can access the same database.

I don't know of any Mysql saving system libraries on byond (I haven't done a search on this, though). The only things I tried to do were account setup/login and encrypted passwords but eventually gave up when I wasn't satisfied with my encryption level. The library for Mysql login (which might get you started in the right direction) is Qubic's MySQL Login Demo.
In response to Nadrew
SQLite is what I am looking for currently. I have a very minor question though. While you said trying to get it to talk over the network is a bad idea, is it a bad idea to get it to talk between games on the same computer (e.g. throwing recent chat logs in to the database for other servers to load where all the servers are on the same box)? Just a capability question.

Also, I am more familiar with SQLite than I am with MySQL. I always dreaded setting up MySQL up. Especially every time I get a new server box to host my websites.

This is what I currently have. It was taken from the documentation and slightly modified. It's unfinished but I got started on it directly after I posted this thread.
// Saving.dm
// This script will handle all of the saving
// and loading for players.

#define PLAYER_FOLDER "Saves/Players/"

mob
attackable
player
var
saving = 0

proc
Save()
if(!src.saving)
saving = 1

var/database/db = new("[PLAYER_FOLDER]players.db")
var/database/query/q = new("SELECT * FROM players WHERE name=?", src.key)

if(q.Execute(db) && q.NextRow())
// returns a list such as list(name="MyName", score=123)
return q.GetRowData()
// no data found
return null


I just don't want to go too far in to it without the proper conception on how things should function in a database system if one were looking for efficiency.

Edit:

// Saving.dm
// This script will handle all of the saving
// and loading for players.

#define PLAYER_FOLDER "Saves/Players/"

mob
attackable
player
var
saving = 0

proc
Save()
if(!src.saving)
saving = 1

var/database/db = new("[PLAYER_FOLDER]players.db")
var/database/query/q = new(("INSERT INTO [src.key] (name, squad) VALUES (?,?)", src.key, src.squad)
q.Execute(db)


// Soldier.dm
// This script will control and handle the player's
// soldiers. Things such as skills and easy locating
// will be beneficial here.

mob
attackable
player
var
squad = new/Squad

Soldier
var/name = "Soldier"
var/class = "N/A"

New(_name, _class)
if(_name)
name = _name

if(_class)
class = _class

Squad
var/name = "My Squad"
var/description = "This is your squad."
var/Soldier/soldier1 = new/Soldier("Soldier 1")
var/Soldier/soldier2 = new/Soldier("Soldier 2")
var/Soldier/soldier3 = new/Soldier("Soldier 3")
var/Soldier/soldier4 = new/Soldier("Soldier 4")
Worry about efficiency after you have a working base. :)
As for SQLite it sounds like that is exactly its purpose if it isn't configured for networks. (And Mysql isn't so bad! Better with PHPMyAdmin on a local server)
Pertaining the code I presented after my edit, I assume I am going to need a query for creating the table if it doesn't exist? I'm inexperienced with SQL queries but I do know that it's something like "IF NOT EXISTS".

I'm also still unsure about how to properly organize my databases. Which I'm sure Nadrew is probably getting around to answering. I'd like to get the "sense" of efficiency now rather than later. Considering that I'm going to be using what I learn here on 2 larger projects later. So efficiency is key. :)
Well I have a Mysql server if you need something to test on, although, it is just as easy to setup your own.
In response to AERProductions
AERProductions wrote:
Well I have a Mysql server if you need something to test on, although, it is just as easy to setup your own.

I prefer SQLite for now.
Generally, what I do is check if the database file exists, if it doesn't I run the normal "CREATE" query to populate the tables. After that you can just access it without worrying about it (unless you update the structure, at which point you'll have to update the file too).

Storing and getting data from SQLite is really efficient, even on larger amounts from what I've seen so far, and the file size for large amounts of data is minimal.

My latest project is using SQLite for quite a large amount of different types of data, for player saves I'm using multiple database files to split things up and make it easier to manage things. I have inventory, stats, skills, and misc data all in their own files. It's not really required though, you can get away with dumping it all into one file if you want.

Sharing the stuff between two servers might be tricky as SQLite locks the files while they're loaded into memory, so you might need to resort to some creative loading and unloading of /database types.

You should definitely not be using one giant file for ALL player data across all players, that would be a massive mess and become hell to manage later on. Stick with individual files for each player.
In response to Nadrew
Okay.

// Saving.dm
// This script will handle all of the saving
// and loading for players.

#define PLAYER_FOLDER "Saves/Players/"

mob
attackable
player
var
saving = 0

proc
SetupDatabase()
var/database/db = new("[PLAYER_FOLDER]players.db")
var/database/query/q = new("CREATE TABLE `players` ( `key` TEXT, `name` TEXT)")
q.Execute(db)
q = new("CREATE TABLE `squad` ( `key` TEXT, `name` TEXT, `description` TEXT)")
q.Execute(db)
q = new("CREATE TABLE `soldier 1` ( `key` TEXT, `name` TEXT, `class` TEXT)")
q.Execute(db)
q = new("CREATE TABLE `soldier 2` ( `key` TEXT, `name` TEXT, `class` TEXT)")
q.Execute(db)
q = new("CREATE TABLE `soldier 3` ( `key` TEXT, `name` TEXT, `class` TEXT)")
q.Execute(db)
q = new("CREATE TABLE `soldier 4` ( `key` TEXT, `name` TEXT, `class` TEXT)")
q.Execute(db)

Save()
if(!src.saving)
saving = 1

SetupDatabase()

var/database/db = new("[PLAYER_FOLDER]players.db")

var/database/query/q = new("INSERT INTO players (key, name) VALUES (?,?)", src.key, src.name)
q.Execute(db)
var/Squad/s = src.squad
q = new("INSERT INTO squad (key, name, description) VALUES (?,?,?)", src.key, s.name, s.description)
q.Execute(db)
var/Soldier/sol = s.soldier1
q = new("INSERT INTO `soldier 1` (key, name, class) VALUES (?,?, ?)", src.key, sol.name, sol.class)
q.Execute(db)
sol = s.soldier2
q = new("INSERT INTO `soldier 2` (key, name, class) VALUES (?,?, ?)", src.key, sol.name, sol.class)
q.Execute(db)
sol = s.soldier3
q = new("INSERT INTO `soldier 3` (key, name, class) VALUES (?,?, ?)", src.key, sol.name, sol.class)
q.Execute(db)
sol = s.soldier4
q = new("INSERT INTO `soldier 4` (key, name, class) VALUES (?,?, ?)", src.key, sol.name, sol.class)
q.Execute(db)


I currently threw all the data in to one database file. I'll change it up so each player gets their own file.

However, when I started saving I saw the UI take a few milliseconds longer to load. Is this perhaps because the saving wasn't under spawn? I would have have experienced such a delay with the old saving method. I'm unsure if I did something wrong.

I'm also unsure if I can put everything under one query with {" "} and a semicolon. I didn't want to try it just yet since I wasn't even sure if the current query would work or not and didn't want to go hunting to find out what was wrong even after I've added on yet another thing that would be an experiment in my current attempt.

I'll post again with my updated code.
It would be amazing if I could use this for faster saving/loading of the built-on maps in Pondera. Right now my code is just some junk that works but is not fast at all (And live saving world maps is something that should be inherently fast for user convenience!).
When you first load the database into a variable it can take a moment, but subsequent queries on the database should be much faster, it's not a bad idea to keep /database objects you use often loaded into memory so they can be utilized without the overhead of loading it and all that.
Generally, when designing a database, You want to lay it out like 2d excel spreadsheets,

You have columns, each column has a certain meaning, and that meaning doesn't change. And each row is an item..


Generally, the design/layout of the database should remain static once made. Out side of code updates, and first run creating of the database, nothing should need to change the layout of the database, including adding and removing tables, or changing their layout/columns. All modifications should be limited to adding, removing, and updating rows in the tables.

Each row should have one field (or a combination of fields) that is guaranteed unique for that row. This is called the primary key. This way you have a way of identifying that row in further queries.

When referring to a thing in a table that is detailed in another table, you generally don't want to duplicate the info, just refer to that item's primary key, and code can get the rest of the info off of that from the table that more fully details them. (like, not storing the name multiple times when you can just store it once and identify the person by their ckey (unless they could have different names))

And I would learn how to create indexes for your tables. Find any column you might want to search something by, and make sure it has an index, this greatly speeds up "select * from table where column = something" queries. If you mark something as a primary key in the table, it will already an index.

In response to Nadrew
Nadrew wrote:
When you first load the database into a variable it can take a moment, but subsequent queries on the database should be much faster, it's not a bad idea to keep /database objects you use often loaded into memory so they can be utilized without the overhead of loading it and all that.

What I always do is an "update" method of saving things. I never pull from the save file more than once unless I'm interacting between servers (which I have never had to do via a save file, only Topic). I pull what I need from the save file and save what I need back to it.

proc/WorldSave(var/list/L)
if(!worldSaving)
worldSaving = 1
var/savefile/S=new("ServerSettings.sav")
var/saveAll = 0
if(!L)
saveAll = 1
world << "Server saved."

if(("allowedSize" in L) || (saveAll))
S["allowedSize"]<<allowedSize
if(("admin" in L) || (saveAll))
S["admin"]<<admin
if(("moderator" in L) || (saveAll))
S["moderator"]<<moderator
...
sleep(10)
worldSaving = 0


That's how my saving typically works. I like it because it only saves what needs to be saved. And loading just goes ahead and loads everything when it is needed. Which, in this particular project, it is needed at the very start so I just go ahead and load everything.

I plan on keeping this general idea of saving and loading things only when they are needed to be. And have everything in memory so I only have to call UPDATE.

My only issue is how to organize my files properly. Can't really wrap my head around it.
See, even in that instance you could save CPU power by keeping the /savefile created in WorldSave() stored in a global variable and just reference when needed, instead of accessing the data over and over by creating a new stream for it every time you save.

For savefiles it's not as big of a deal, but when dealing with /database stuff it saves a lot of hassle (and a bit on resources)
I have two clustered MySQL databases for free use.. No one seems to want to use it..
In response to A.T.H.K
A.T.H.K wrote:
I have two clustered MySQL databases for free use.. No one seems to want to use it..

Perhaps nobody really knows how to use them or haven't ever been through PHPMyAdmin?
phpmyadmin sucks. adminer is best control panel. adminer is only control panel.
In response to AERProductions
AERProductions wrote:
A.T.H.K wrote:
I have two clustered MySQL databases for free use.. No one seems to want to use it..

Perhaps nobody really knows how to use them or haven't ever been through PHPMyAdmin?

Agreed.

In response to Doohl
Doohl wrote:
phpmyadmin sucks. adminer is best control panel. adminer is only control panel.

Well I take that with a grain of salt because I have no issues with phpmyadmin; remember that it only outputs what you put in! :)
Page: 1 2