Getting in to using SQL Saving

Page: 1 2
Aug 12 2015, 5:52 pm (Edited on Aug 12 2015, 6:00 pm)
In response to MrStonedOne
MrStonedOne wrote:
Generally, when designing a database, You want to lay it out like 2d excel spreadsheets,

Never liked spreadsheets all that much.

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

Got it.

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.

So, what you're telling me is that I can never add a new column or remove one? I always thought you could so long as the data wasn't dependent elsewhere (foreign keys and such)

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.

I chose BYOND keys as my primary key. Because, well... BYOND keys are unique. To the best of my knowledge at least. Ckeys I believe can be duplicates sometimes I think.

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))

That's one thing I learned in my database design course I was required to take in College two semesters ago. I made sure to keep things nice and neat.

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.

Never learned about how to do this but it's always been talked about.



Also, I love PHPMyAdmin. I've never used Adminer. But, who knows, I may like it.



Also, could anyone provide some code for easily setting up a database that would save a users name, key, a variable and a datum that belongs to that user? Just some typical data so I could see what format would be normally used.
Aug 12 2015, 5:57 pm
Here's how I (generally) save characters in Severed World:

    var/gender = (P.gender == "male" ? 1 : 0)

    var/header = "(`Name`, `Ckey`, `Slot`, `Coords`, `Dir`, `Preview`, `Avatar`, \
        `Gender`, `HP`, `MP`, `EXP`, `EXP_Next`, `Level`, `Max_HP`, `Max_MP`, \
        `Melee`, `Agility`, `Spell`, `Techniques`, `Accessories`, `Spell_Points`, \
        `Stat_Points`, `Class`, `PVP_Score`, `Craft_Mem`, `Checkpoint`, `Misc_Data`, `Guild`, `Guild_Rank`)"

    var/coords = "[P.x];[P.y];[P.z]"

    var/values = "('[P.name]','[ckey(P.key)]',[slot],'[coords]',[P.dir],\
        '[encoded_preview]','[encoded_avatar]',[gender],[P.hp],[P.mp],[P.exp],[P.exp_next],[P.lvl],[P.max_hp],\
        [P.max_mp],[P.melee],[P.agility],[P.spell],'[encoded_techs]','[P.write_accessories()]',\
        [P.spell_points],[P.stat_points],'[P.class]',[P.pvp_score],'[list2text(P.crafting_memory, ";")]','[P.checkpoint]','[assoc2text(P.misc_data)]','None',[P.guild_rank])"

    query = dbcon.NewQuery("INSERT INTO `player_savefiles` [header] VALUES [values]")
    query.Execute()


Not the most efficient way to do it, certainly, but the most raw and simplistic.

Saving items is just as easy:

// Delete old entries
    query = dbcon.NewQuery("SELECT * FROM `global_items` WHERE `Source`='[P.name]'")
    query.Execute()
    if(query.RowCount() > 0)
        query = dbcon.NewQuery("DELETE FROM `global_items` WHERE `Source`='[P.name]'")
        query.Execute()

    for(var/obj/item/I in P.inventory)
        var/I_header = "(`Source`, `Type`, `Cvars`)"
        var/I_values = "('[P.name]','[I.type]','[I.write_vars()]')"
        query = dbcon.NewQuery("INSERT INTO `global_items` [I_header] VALUES [I_values]")
        query.Execute()


My `Cvars` row in this table serves to keep track of variables that have been overwritten in runtime. So if a user has an item that has been given a custom name, it'll be saved as "name=New Object" within `Cvars`.

[Pic] [Pic #2]
Aug 12 2015, 6:16 pm
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?

Generally, the people who'd need to use it already have their own setup. =/
Aug 12 2015, 6:26 pm
In response to Lavenblade
Lavenblade wrote:
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?

Generally, the people who'd need to use it already have their own setup. =/

I already have my own setup. I've just never used it for games. Only software applications that people run on their own computers. Bots, chats, website chat rooms, etc. Minor things.



@Doohl: It doesn't look so bad. At least not after I actually program it. It just feels like so much work when looking at it for the first time. That's a nice and simple example.
Aug 12 2015, 9:43 pm
I finally got a little free time. I assume Doohl's example is MySQL and not SQLite? Considering he has a "dbcon" variable.

I don't see any documentation on NewQuery(). So either it was never documented or it's a proc he created.

If all of this is true, how would you actually make a connection to the database? I'd like to try my hand at this.
Aug 12 2015, 9:45 pm
Ah, sorry. I should've explained. Yes, I'm using MySQL and not SQLite. The NewQuery() proc exists in Dantom.DB. You can find documentation on the library here.
Aug 13 2015, 2:31 am
I'm tired. Well, here's what I managed to put together in the past hour after a lot of errors. I grabbed a free SQL server from here: http://www.freemysqlhosting.net/ for testing so I don't bother my current SQL servers. It runs well. With the exception that apostrophes screw up the insertion. So, it's unfinished because of that and because I need to add a way of clearing data and making it so I'm not stuck with the current fields I've chosen.

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

mob
    attackable
        player
            var
                saving = 0

            proc
                //Used for easily creating a query from a list of items. Not intended to be ran multiple times.
                //Use once, get the query, paste it for hard-coded use.
                SetupDatabase()
                    var/list/field = list("`key`", "`name`",
                    "`squad_name`", "`squad_desc`",
                    "`squad_sol1_name`", "`squad_sol1_class`",
                    "`squad_sol2_name`", "`squad_sol2_class`",
                    "`squad_sol3_name`", "`squad_sol3_class`",
                    "`squad_sol4_name`", "`squad_sol4_class`")

                    var/list/types = list("VARCHAR(30) NOT NULL, PRIMARY KEY ([field[1]])", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL")

                    var/q = "CREATE TABLE IF NOT EXISTS `player_data` ( "
                    for(var/i = 1 to length(field))
                        if(i < length(field))
                            q += "[field[i]] [types[i]], "
                        else
                            q += "[field[i]] [types[i]] );"

                    world << q
                    CreateDatabase(q)

                //Used to actually create the database and allows for passing custom queries.
                CreateDatabase(var/q)
                    if(!q)
                        q = "CREATE TABLE IF NOT EXISTS `player_data` ( \
                        `key` VARCHAR(30) NOT NULL, PRIMARY KEY (`key`), `name` VARCHAR(30) NULL, \
                        `squad_name` VARCHAR(11) NULL, `squad_desc` VARCHAR(250) NULL, \
                        `squad_sol1_name` VARCHAR(11) NULL, `squad_sol1_class` VARCHAR(13) NULL, \
                        `squad_sol2_name` VARCHAR(11) NULL, `squad_sol2_class` VARCHAR(13) NULL, \
                        `squad_sol3_name` VARCHAR(11) NULL, `squad_sol3_class` VARCHAR(13) NULL, \
                        `squad_sol4_name` VARCHAR(11) NULL, `squad_sol4_class` VARCHAR(13) NULL );"

                    query = dbcon.NewQuery(q)
                    query.Execute()

                //Actually saves data.
                Save()
                    if(!src.saving)
                        saving = 1

                        CreateDatabase()

                        var/header = "(`key`, `name`, \
                        `squad_name`, `squad_desc`, \
                        `squad_sol1_name`, `squad_sol1_class`, \
                        `squad_sol2_name`, `squad_sol2_class`, \
                        `squad_sol3_name`, `squad_sol3_class`, \
                        `squad_sol4_name`, `squad_sol4_class`)"

                        var/Squad/s = src.squad
                        var/Soldier/sol1 = s.soldier1
                        var/Soldier/sol2 = s.soldier2
                        var/Soldier/sol3 = s.soldier3
                        var/Soldier/sol4 = s.soldier4
                        var/values = "('[src.key]', '[src.name]',\
                        '[s.name]', '[s.description]',\
                        '[sol1.name]', '[sol1.class]',\
                        '[sol2.name]', '[sol2.class]',\
                        '[sol3.name]', '[sol3.class]',\
                        '[sol4.name]', '[sol4.class]')"

                        query = dbcon.NewQuery("INSERT INTO `player_data` [header] VALUES [values]")
                        query.Execute()
                        world << query.ErrorMsg()


I went ahead and made it so there's 1 database that holds all the player data. But each player has their own table. This way there's no need to switch between database connections all day. Not sure if this was a good decision. Any input?

To get the datum saved, I just grabbed all of its data and stored it. All the variables it owns and all the variables its objects owns. I know this won't be practical in the future when I have to do this for more complex things. I wish I knew of a better way. Perhaps getting the text from its "vars" variable? *shrugs*

I also still need to figure out how to escape apostrophes and other symbols from text inputs in the SQL.

Which also reminds me... what about SQL Injections!?!?
Aug 13 2015, 5:45 am
I also still need to figure out how to escape apostrophes and other symbols from text inputs in the SQL.


Which also reminds me... what about SQL Injections!?!?

You want to use dbcon.quote() This also adds the quotes '', but i don't like that because it makes it hard to concatenate data conditionally.

So for tgstation13 we made this:
// Run all strings to be used in an SQL query through this proc first to properly escape out injection attempts.
/proc/sanitizeSQL(t as text)
    var/sqltext = dbcon.Quote(t);
    return copytext(sqltext, 2, lentext(sqltext));//Quote() adds quotes around input, but we already do that, so this removes the ones Quote adds

(this assumes your database is connected and saved to the global var, dbcon, a common setup)
Note: don't quote/sanitize numbers

So, what you're telling me is that I can never add a new column or remove one? I always thought you could so long as the data wasn't dependent elsewhere (foreign keys and such)

What I mean, is that DM code shouldn't be dynamically creating or removing columns or tables or databases outside of updating the table in relation to a code change (like if you added a new feature, and had to make a few columns to save that info) or initializing the database on a new install.

So instead of creating multiple players tables, one for each player, you would create one, and save all players to it as rows. (like in that excel spreadsheet.)

Think of it this way: If you had an excel spreadsheet, that was a phone book, it would have some columns, like name, address, home phone number, cell phone number, work phone number, fax number, etc. And each item would be a new row. You wouldn't create a new spreadsheet for each person.

Adding new databases is generally used to separate applications run on the same server. Like how you might make a new spreadsheet for your roommate's address book.


I chose BYOND keys as my primary key. Because, well... BYOND keys are unique. To the best of my knowledge at least. Ckeys I believe can be duplicates sometimes I think.

Nope, can't remember where, but this was cleared up by tom or lum. Key is just for display purposes, your real username, used by byond, is the ckey (as in, their database uses ckey as a primary key for the users/players table). So byond will not let you register an account whose key/username may be unique, but conflicts with another ckey.
Aug 13 2015, 7:37 am
In SQLite usage (not MySQL), you can utilize a sanitizing feature within Query(). This also handles quotation marks and the sort for you.

Query("SELECT * FROM my_database WHERE variable = ? AND other_variable = ?",variable_one,variable_two)


You can find more details in the reference entry on Query(), as for MySQL the Quote() proc is definitely the right choice.

As for ckey, it will always be unique, no two people will ever share a ckey. The BYOND database does in fact use ckey as the unique key (outside of the ID field), the visible key name is just data for the sake of making your key more visually appealing.
Aug 13 2015, 1:56 pm
In response to MrStonedOne
MrStonedOne wrote:
You want to use dbcon.quote() This also adds the quotes '', but i don't like that because it makes it hard to concatenate data conditionally.

So for tgstation13 we made this:
> // Run all strings to be used in an SQL query through this proc first to properly escape out injection attempts.
> /proc/sanitizeSQL(t as text)
>   var/sqltext = dbcon.Quote(t);
>   return copytext(sqltext, 2, lentext(sqltext));//Quote() adds quotes around input, but we already do that, so this removes the ones Quote adds
>

(this assumes your database is connected and saved to the global var, dbcon, a common setup)
Note: don't quote/sanitize numbers

What if the input has quotations in it to begin with? Not really sure how this works. Just trying to see if there are some easy exploits. *shrugs* Quotes could probably stand for something else for all I know.

What I mean, is that DM code shouldn't be dynamically creating or removing columns or tables or databases outside of updating the table in relation to a code change (like if you added a new feature, and had to make a few columns to save that info) or initializing the database on a new install.

So instead of creating multiple players tables, one for each player, you would create one, and save all players to it as rows. (like in that excel spreadsheet.)

Think of it this way: If you had an excel spreadsheet, that was a phone book, it would have some columns, like name, address, home phone number, cell phone number, work phone number, fax number, etc. And each item would be a new row. You wouldn't create a new spreadsheet for each person.

Ah. Okay, that's understandable. It'd be a lot of work to save them all on their own tables. I was originally going to save certain data to certain tables only because I wasn't sure about accessibility from multiple sources and felt that multiple tables would break it up. I instead copied Doohl's method and put all the player data in 1 table. And, in the future, global data will go in their own table.

In short, each type has their own table. Or at least most of them (e.g. mob/attackable/player, global, objects).

Adding new databases is generally used to separate applications run on the same server. Like how you might make a new spreadsheet for your roommate's address book.

Can you explain this a bit more? In a game scenario? Would I need multiple DBs for multiple servers? Or would I need it for just multiple games, such as Tetris and Pac-Man having separate DBs. I would assume only the latter.

Nope, can't remember where, but this was cleared up by tom or lum. Key is just for display purposes, your real username, used by byond, is the ckey (as in, their database uses ckey as a primary key for the users/players table). So byond will not let you register an account whose key/username may be unique, but conflicts with another ckey.

Alright. Well, I assume that using ckeys as a primary key would be more beneficial since it would consume less characters. I also guess I would keep the same varchar length of 30 since you can still have a ckey of 30 if you have no symbols or spaces (that's one long byond key...).
Aug 13 2015, 8:06 pm
What if the input has quotations in it to begin with? Not really sure how this works. Just trying to see if there are some easy exploits. *shrugs* Quotes could probably stand for something else for all I know.


dbcon.Quote() doesn't do what it says it does.

Basically, in the mysql reference dll, there is a command, call mysql_string_escape or something like that. And what it will do, is basically put a \ in front of anything it would have treated specially. dbcon.Quote() basically does this:

return "'[mysql_string_escape()]'"

How ever, the adding of the single quotes is annoying if you want to escape user input separately and then combine it together in the query, So my function just removes the first and last characters.

Aug 14 2015, 12:49 am (Edited on Aug 14 2015, 12:54 am)
I have saving and loading working and I get the gist of how it works, thanks to Doohl's examples. It wasn't loading for a good 20 mins until I realized it was saved under my guest key and I was logged in to my actual key. So, that was fun...

Well, here's my final product. Can anyone spot anything wrong that is noteworthy?

The top of core.dm for Dantom's DB lib.
var/DBConnection/dbcon = new(DBI, "DB_NAME", "DB_PASSWORD", 0)
var/DBQuery/query
var/DBI = "dbi:mysql:DB_NAME:DB_IP:DB_PORT"

world
    New()
        spawn()
            if(!dbcon.Connect())
                world.log << "Failed to connect to the database. Shutting down."
                del world
        ..()


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

mob
    attackable
        player
            var
                saving = 0
                loading = 0

            proc
                //Used for easily creating a query from a list of items. Not intended to be ran multiple times.
                //Use once, get the query, paste it for hard-coded use.
                SetupDatabase()
                    var/list/field = list("`ckey`", "`name`",
                    "`squad_name`", "`squad_desc`",
                    "`squad_sol1_name`", "`squad_sol1_class`",
                    "`squad_sol2_name`", "`squad_sol2_class`",
                    "`squad_sol3_name`", "`squad_sol3_class`",
                    "`squad_sol4_name`", "`squad_sol4_class`")

                    var/list/types = list("VARCHAR(30) NOT NULL, PRIMARY KEY ([field[1]])", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL",
                    "VARCHAR NULL", "VARCHAR NULL")

                    var/q = "CREATE TABLE IF NOT EXISTS `player_data` ( "
                    for(var/i = 1 to length(field))
                        if(i < length(field))
                            q += "[field[i]] [types[i]], "
                        else
                            q += "[field[i]] [types[i]] );"

                    world << q
                    CreateDatabase(q)

                //Used to actually create the database and allows for passing custom queries.
                CreateDatabase(var/q)
                    if(!q)
                        q = "CREATE TABLE IF NOT EXISTS `player_data` ( \
                        `ckey` VARCHAR(30) NOT NULL, PRIMARY KEY (`ckey`), `name` VARCHAR(30) NULL, \
                        `squad_name` VARCHAR(11) NULL, `squad_desc` VARCHAR(250) NULL, \
                        `squad_sol1_name` VARCHAR(11) NULL, `squad_sol1_class` VARCHAR(13) NULL, \
                        `squad_sol2_name` VARCHAR(11) NULL, `squad_sol2_class` VARCHAR(13) NULL, \
                        `squad_sol3_name` VARCHAR(11) NULL, `squad_sol3_class` VARCHAR(13) NULL, \
                        `squad_sol4_name` VARCHAR(11) NULL, `squad_sol4_class` VARCHAR(13) NULL );"

                    query = dbcon.NewQuery(q)
                    query.Execute()

                //Actually saves data.
                Save()
                    if(!src.saving)
                        saving = 1

                        CreateDatabase()

                        var/header = "(`ckey`, `name`, \
                        `squad_name`, `squad_desc`, \
                        `squad_sol1_name`, `squad_sol1_class`, \
                        `squad_sol2_name`, `squad_sol2_class`, \
                        `squad_sol3_name`, `squad_sol3_class`, \
                        `squad_sol4_name`, `squad_sol4_class`)"

                        var/Squad/s = src.squad
                        var/Soldier/sol1 = s.soldier1
                        var/Soldier/sol2 = s.soldier2
                        var/Soldier/sol3 = s.soldier3
                        var/Soldier/sol4 = s.soldier4
                        var/values = "('[src.ckey]', '[dbcon.sanitizeSQL(src.name)]',\
                        '[dbcon.sanitizeSQL(s.name)]', '[dbcon.sanitizeSQL(s.description)]',\
                        '[dbcon.sanitizeSQL(sol1.name)]', '[sol1.class]',\
                        '[dbcon.sanitizeSQL(sol2.name)]', '[sol2.class]',\
                        '[dbcon.sanitizeSQL(sol3.name)]', '[sol3.class]',\
                        '[dbcon.sanitizeSQL(sol4.name)]', '[sol4.class]')"

                        query = dbcon.NewQuery("INSERT INTO `player_data` [header] VALUES [values]")
                        query.Execute()

                        saving = 0

                Load()
                    if(!src.loading)
                        loading = 1

                        query = dbcon.NewQuery("SELECT * FROM `player_data` WHERE `ckey`='[src.ckey]'")
                        query.Execute()

                        // A savefile was found
                        world << query.RowCount()
                        if(query.RowCount() > 0)
                            query.NextRow()

                            src.name = query.item[2]
                            src.squad.name = query.item[3]
                            src.squad.description = query.item[4]
                            src.squad.soldier1.name = query.item[5]
                            src.squad.soldier1.class = query.item[6]
                            src.squad.soldier2.name = query.item[7]
                            src.squad.soldier2.class = query.item[8]
                            src.squad.soldier3.name = query.item[9]
                            src.squad.soldier3.class = query.item[10]
                            src.squad.soldier4.name = query.item[11]
                            src.squad.soldier4.class = query.item[12]

                        loading = 0
Page: 1 2