Saving Lists in MySQL

ID:1128156
 
Jan 17 2013, 3:14 pm
(See the best response by Pirion.)
Code:
    SaveCharacter(mob/M)
        var/combineStatements = "";
        var/loop = 0
        var/list/VARIABLES = M.vars
        for(var/V=1 to M.vars.len)
        {
            if(!ignored_vars.Find(VARIABLES[V]))
            {
                if(loop>0) { combineStatements += ", "; }
                if(islist(VARIABLES[V]))
                {
                    combineStatements += "[VARIABLES[V]]='[implodetext(VARIABLES[VARIABLES[V]],"|")]'"
                    loop++;
                    continue
                }
                if(isnull(VARIABLES[VARIABLES[V]]))
                {
                    combineStatements += "[VARIABLES[V]]='null'"
                    loop++;
                    continue
                }
                combineStatements += "[VARIABLES[V]]='[VARIABLES[VARIABLES[V]]]'";
                loop++;
            }
        }
        var/query = "UPDATE `[sql_table]` SET [combineStatements] WHERE name='[M.name]';"
        return query


Problem description:

I'm trying to save lists in MySQL but it just ends up being /list can anyone point out what I'm doing wrong?
Jan 17 2013, 3:18 pm
var/list/VARIABLES = M.vars
Jan 17 2013, 3:20 pm
In response to A.T.H.K
A.T.H.K wrote:
var/list/VARIABLES = M.vars

Hmm i think i understand maybe. sec
Jan 17 2013, 3:22 pm
OH sorry! I wasn't meant to post that, but yes that is an issue .. I really wish BYOND had a mobile friendly site... sigh
Jan 17 2013, 3:24 pm
lol hmm i cant figure it out still meh could you elaborate a bit more sorry im a noob still learning mistakes :(
Jan 17 2013, 3:25 pm
Best response
You would have to save each item in the list, like you are doing with the mob.vars list.


There are two ways to do this, I prefer the first as it is a more relational approach in the database.

1) Create a new table for the list contents, and store the list item in this table, if it is a named list, then you can store the name, value pair. Your table would look something like this:

MobNameId, ListNameInCode, ListIndexOrNamed, ListItemValue

then when loading you select `ListIndexOrName`, `ListItemValue` from `listTable` where MobNameId = [id] and ListNameInCode = 'myList'

or:

You can do a list2params and store the result in a text field. (This makes it harder if you want to do a lookup on the values though)

it would be stored in the format 'Name=Value&OtherName=OtherValue&soforth=onanon'


Like I said, the first is more preferred. Just remember if you are storing datums in your list, you'll need to store a reference to all of the information to recreate that datum, as we only pass text to the database.
Jan 17 2013, 4:16 pm
In response to Pirion
Pirion wrote:
You would have to save each item in the list, like you are doing with the mob.vars list.


There are two ways to do this, I prefer the first as it is a more relational approach in the database.

1) Create a new table for the list contents, and store the list item in this table, if it is a named list, then you can store the name, value pair. Your table would look something like this:

MobNameId, ListNameInCode, ListIndexOrNamed, ListItemValue

then when loading you select `ListIndexOrName`, `ListItemValue` from `listTable` where MobNameId = [id] and ListNameInCode = 'myList'

or:

You can do a list2params and store the result in a text field. (This makes it harder if you want to do a lookup on the values though)

it would be stored in the format 'Name=Value&OtherName=OtherValue&soforth=onanon'


Like I said, the first is more preferred. Just remember if you are storing datums in your list, you'll need to store a reference to all of the information to recreate that datum, as we only pass text to the database.

Yeah i was hoping to do some sorta shortcut.. anyways i guess ill go with way 1 since that is how i did my mob contents too.
Jan 17 2013, 4:35 pm
Btw one more question if possible

        LoadIgnoreList()
            var/DBQuery/qry = Query("SELECT * FROM `IgnoreList` WHERE `Owner`='[mob.name]';")
            mob.ignorelist.Cut()
            if(qry.RowCount() > 0)
                while(qry.NextRow())
                    var/list/row_data = qry.GetRowData()
                    for(var/D in row_data)
                        mob.ignorelist.Add(row_data["Ignored"])


for some reason it is loading my ignore list with 2 copies of each ignored person any idea why..?
Jan 17 2013, 4:49 pm
nevermind im a derp face its fixed lol :P
Jan 17 2013, 4:50 pm
Would you mind adding a solution post in? As this may help other developers :) thanks!
Jan 17 2013, 4:54 pm
The solution was i was looping through the query twice and it was adding each entry twice aka I'm dumb.

        LoadIgnoreList()
            var/DBQuery/qry = Query("SELECT * FROM `IgnoreList` WHERE `Owner`='[mob.name]';")
            mob.ignorelist.Cut()
            if(qry.RowCount() > 0)
                while(qry.NextRow())
                    var/list/row_data = qry.GetRowData()
                    mob.ignorelist.Add(row_data["Ignored"])


is it fixed lol.
Jan 17 2013, 5:34 pm
Hey Goku - not sure if it is just like this because you are testing, but it is good practice to only pull the fields you need.

SELECT * is generally considered bad because it can affect indexes used, and creates more page reads than you actually need.

If you have four fields in that table, by select * you pull each every column of each record, when really you only need the one record. It may not mean much now, but when you have a full database and a sluggish server or connection to sever, it can be difference in cost of upgrades for you if you get that far.
Jan 17 2013, 5:38 pm
yeah i understand but for the 2 columns i pull are all needed

thanks for helping though :)
Jan 17 2013, 5:39 pm
Sorry for the double post, but it may also be a good idea to use an id field for when you reference an entity.

Example

Players table:
MobKey,MobName,MobLevel,MobHP

IgnoreList Table
MobKey (Owner), MobKey (IgnoreList)


(Mob key being an auto incrementing number, or anything really)
That way, if you ever want to allow people to change MobName, you don't have to update it in multiple places. (In database terms, this is called an update anomaly)
Jan 17 2013, 5:41 pm
In response to Gokussj99
Gokussj99 wrote:
yeah i understand but for the 2 columns i pull are all needed

thanks for helping though :)

Well, if you ever need to go back and add a field to this table, that you didn't need here, then you would run into this loss. Sort of a future proofing concept.
Jan 18 2013, 7:41 pm (Edited on Jan 19 2013, 7:45 am)
How about using savefiles' ExportText and ImportText functions to save yourself a bunch of trouble? Ha ha :P

proc/savecharacter(mob/m,key=m.key)
    fdel("[key]_temp")
    var/savefile/f = new("[key]_temp")
    f << m
    var/text = f.ExportText() // you would also want to encrypt this or use a hash or something
    save_text_to_mysql_cell(key,text)

proc/loadcharacter(key)
    var/text = fetch_text_from_mysql_database(key)
    fdel("[key]_temp")
    var/savefile/f = new("[key]_temp")
    f.ImportText("/",text)
    f >> .

mob/Write(savefile/f)
    .=..()
    f["key"] << null
    f["ckey"] << null

Jan 20 2013, 4:59 pm
In response to Metamorphman
Metamorphman wrote:
How about using savefiles' ExportText and ImportText functions to save yourself a bunch of trouble? Ha ha :P

> proc/savecharacter(mob/m,key=m.key)
>   fdel("[key]_temp")
>   var/savefile/f = new("[key]_temp")
>   f << m
>   var/text = f.ExportText() // you would also want to encrypt this or use a hash or something
>   save_text_to_mysql_cell(key,text)
>
> proc/loadcharacter(key)
>   var/text = fetch_text_from_mysql_database(key)
>   fdel("[key]_temp")
>   var/savefile/f = new("[key]_temp")
>   f.ImportText("/",text)
>   f >> .
>
> mob/Write(savefile/f)
>   .=..()
>   f["key"] << null
>   f["ckey"] << null
>


Eh its already done lol i finally implemented mysql fully working in my first byond project ;) so happy and everything is now functioning smoothly.

Pirion wrote:
Gokussj99 wrote:
yeah i understand but for the 2 columns i pull are all needed

thanks for helping though :)

Well, if you ever need to go back and add a field to this table, that you didn't need here, then you would run into this loss. Sort of a future proofing concept.

and yeah thanks heh i guess i will select the columns then.