Some SQL / db Questions...

[Torankusu]

So I am trying to set up a db that stores two lists specific to a user in regards to quests they have completed, or not completed. I figure this could be used as a db for item storage if need be, but this was the simplest purpose I could think of.

ex column names:
owner , complete, incomplete

First question:
Would it be best to associate the owner by player.account or player.communityname ? I am currently using player.account.


Second Question / my issue..:
As far as how data is stored in a single column, if there are multiple things (ex: inc1, inc2) I have written a function to gather that data:

PHP Code:

/*-------- getQuestsIncomplete------------
getQuestsIncomplete(temp.owner)
only for incomplete quests right now**
Set arguments: incomplete , quest_users
-----------------------------------*/

function getQuestsIncomplete(temp.owner)
{ 
  temp.check = escapestring2(temp.owner);
  temp.statement = "SELECT incomplete FROM quest_users WHERE owner = '"@temp.check@"' ";
  
  //Query
  temp.req = requestSQL(temp.statement,true);
  
  //Does this exist?
  if (temp.req.rows.size() == 0)
    {
      return NULL;
    }
  return temp.req.rows[0][0]; 
} 


this will return inc1, inc2 without any " ".

If I write another function to update that list, I've tried adding to it as if it was an array, and any time I do, it just returns 0 afterwards (Echoing in rc).

This was an issue I was anticipating as I will eventually store a lot of information in a single column, which could be a bad design, but I would appreciate some insight on how to get this functioning if I am doing something wrong. Or, a better method for this wouldn't hurt either.

Thanks.

Edit: wanted to include what I'm going to use this for later on...

I was going to set up a GUI for the user to pull a list of quests they have either completed or not completed from.

The names will be internal names for a separate db that stores all the quest specific info to display once a user had selected that quest.

[fowlplay4]

If you're going to use SQL, you should leverage it else where.

Are you going to have a character slot system? If so you should establish a 'characters' table. I.e.

Characters

ID
Account

Then your other tables you should use the Character ID as the reference. I.e. For Quests:

Quests

ID - Autoincrementing integer
ScriptID - String - quest_id
Name
Description

QuestProgress

ID - Autoincrementing integer
Quest ID - Integer - Quest's ID
Character ID - Integer - Character's ID
Completed (Boolean)

Using this structure you can perform queries like...

PHP Code:

// Returns all quests player is currently on and has completed
public function getQuestData(pl) {
  temp.statement = format("SELECT Quests.name AS quest_name, Quests.description AS quest_description, QuestProgress.completed AS quest_completed
                             FROM Quests, QuestProgress
                            WHERE Quests.ID = QuestProgress.QuestID
                              AND QuestProgress.CharacterID = %s", formatstring2(pl.clientr.characterid));
                              
  temp.response = requestsql(temp.statement, true);
  if (temp.response.rows.size() > 0) {
    temp.data = {};
    for (temp.row: temp.response.rows) {
      temp.data.add({
        temp.row.quest_name,
        temp.row.quest_description,
        temp.row.quest_completed
      });
    }
    return temp.data;
  } else {
    return "";
  }
}

public function getQuestsCompleted(pl) {
  temp.statement = format("SELECT COUNT(Quests.ID) AS quests_completed FROM QuestProgress WHERE QuestProgress.CharacterID = %s AND quest_completed = 1", formatstring2(pl.clientr.characterid));
  temp.response = requestsql(temp.statement, true);
  return (0 + temp.response.rows[0].quests_completed);
} 


[Torankusu]

Originally Posted by fowlplay4

If you're going to use SQL, you should leverage it else where.

Not sure what this means? Leverage the completed and incomplete info elsewhere?

Are you going to have a character slot system? If so you should establish a 'characters' table. I.e.

Characters

ID
Account

I haven't considered character slots, but I guess it wouldn't hurt since I am setting up this structure to go ahead and implement them in the event we want to go that route.

Question about this also, I see you have the character's id stored in a clientr. flag designating which character they are at the moment (pl.clientr.characterid): Do the IDs have to be UNIQUE to ALL players, or just Unique to the account?

Ex: Can I have:
ID | Account
1 | Torankusu
2 | Torankusu
1 | fowlplay4

Then your other tables you should use the Character ID as the reference. I.e. For Quests.......

this looks much better than the functions I have set up, but it will take me a few days to set this up and test some things as I am busy with work all week and will be out of town next week for the holidays...

My last question is -- I couldn't find anything in scripthelp for formatstring2?

Thanks for the help and suggestions! This has given me a few things to read up on and consider as far as setting up this structure!

[fowlplay4]

I mean using it for more than just tracking quest progress. You could have a stats table. I.e.

character_id
strength
dexterity
stamina
intelligence
exp
level

ID would be auto-generating.

Characters

1 toranksu
2 toranksu
3 fp4
4 (npcserver)
5 toranksu

To get character IDs you would do:

SELECT Character.id AS character_id
WHERE acct = player.account

Your character system would keep track of what character they're currently on using a flag like clientr.character_id and you would use that with your other tables rather than hardcoding account ids.

also my bad, it's escapestring2.

[Torankusu]

Thanks a lot that clears some things up.

After reading through your examples again I thought you might have meant escapestring2.

Thanks again, ill try to post some progress in the next few days.

[Torankusu]

Originally Posted by fowlplay4

I mean using it for more than just tracking quest progress. You could have a stats table. I.e.

character_id
strength
dexterity
stamina
intelligence
exp
level

ID would be auto-generating.

Characters

1 toranksu
2 toranksu
3 fp4
4 (npcserver)
5 toranksu

To get character IDs you would do:

SELECT Character.id AS character_id
WHERE acct = player.account

Your character system would keep track of what character they're currently on using a flag like clientr.character_id and you would use that with your other tables rather than hardcoding account ids.

also my bad, it's escapestring2.

Alright, so my friend and I have a pretty solid foundation set up for the character slots and stuff, something we didn't initially intend on doing in the first place but are glad to have had the experience because it allows us to think and sort of script things we hadn't thought of having to compensate for multiple characters in the past...which leads me to my next question about something I might have overlooked earlier.

I think we will plan on having separate items (inventories, etc), between the characters linked to a specific player.

Ex: Character 1 might have, 2 apples, and one orange, and Character 2 might have 4 bananas and 3 grapes.

I had figured it would make better sense to store the inventory information in a table on sqlite, and link it to the character ID similar to how the others are being linked...

The problem I am having (and I haven't set this up yet), is determining if
A.) That is a viable option.
and
B.) If it is, what should I expect when a player ends up with 100 different items? {itemname, quantity} , etc? Would I just have

CharID | Items
Toran | {apple, 2}, {orange, 1}
Toran2 | {banana, 4}, {grape, 3}

and so on ? This seems sloppy to me, personally, but I suppose my alternative would be the same thing in clientr. flags ?

Also -- that brings me to my next point -- I could write a script to read all inventory items if I set the table up that way no problem -- but my concern is speed and effeciency? Should I load it ONCE, on the instance that the character is loaded, and then store it to clientr. flags, or should I just access that list whenever I need it? (equipping items, drawing the items needed to display in the inventory, etc...)...

Anyways, thanks for the help. I've personally never done anything like this before, and I was not familiar with SQLite until a few months ago. :P

[fowlplay4]

Here's how I would do it:

Characters

ID - Account

1 - Toran
2 - Toran2

Index on Account

Items

ID - Itemname

1 - Apple
2 - Orange
3 - Banana
4 - Grape

Index on Itemname

CharacterItems

ID - CharacterID - ItemID - Quantity

1 - 1 - 1 - 2
2 - 1 - 2 - 1
3 - 2 - 3 - 4
4 - 2 - 4 - 3

Index on CharacterID
Index on ItemID

Note: ID columns are auto-increment columns. So they just go 1, 2, 3, 4, etc. as you add rows to the table.

On login/character load, you would do a query to load all their item information with at least query and store it in a clientr flag for quick-reference.

When you're making changes (i.e. adding items), you would make them to the database first then reflect that in their clientr flags.

[Torankusu]

Originally Posted by fowlplay4

Here's how I would do it:

Characters

ID - Account

1 - Toran
2 - Toran

Index on Account

Items

ID - Itemname

1 - Apple
2 - Orange
3 - Banana
4 - Grape
5 - Sword

Index on Itemname

CharacterItems

ID - CharacterID - ItemID - Quantity

1 - 1 - 1 - 2
2 - 1 - 2 - 1
3 - 2 - 3 - 4
4 - 2 - 4 - 3

Awesome, that makes a bit more sense.

One thing I am wondering however, is say that for example I have a sword [itemid - 5], and I have a user's character that owns two of them.

CharacterItems

ID - CharacterID - ItemID - Quantity

1 - 1 - 1 - 2
2 - 1 - 2 - 1
3 - 2 - 3 - 4
4 - 2 - 4 - 3
5 - 1 - 5 - 2

Simple enough,
But what if I wanted to offer an option to enchant one of those two swords?
Would this be viable?

CharacterItems

ID - CharacterID - ItemID - Quantity - Enchantments

1 - 1 - 1 - 2 - Null
2 - 1 - 2 - 1 - Null
3 - 2 - 3 - 4 - Null
4 - 2 - 4 - 3 - Null
5 - 1 - 5 - 1 - str+1
6 - 1 - 5 - 1 - Null

Or is there a better alternative?

Thanks again for the help, ill see if I cant get this set up tonight to test with.

[fowlplay4]

Originally Posted by Torankusu

Awesome, that makes a bit more sense.

One thing I am wondering however, is say that for example I have a sword [itemid - 5], and I have a user's character that owns two of them.

Simple enough,
But what if I wanted to offer an option to enchant one of those two swords?
Would this be viable?

Or is there a better alternative?

Thanks again for the help, ill see if I cant get this set up tonight to test with.

That's how I would do it, except call it mods, store it as an array, and parse the array when the item is loaded to apply it to the item/clientr flags.

You could put enchantments in it's own table though. I.e.

ID - CharacterItemID - Enchantment - EnchantmentValue

1 - 5 - strength - 1

[Torankusu]

Originally Posted by fowlplay4

That's how I would do it, except call it mods, store it as an array, and parse the array when the item is loaded to apply it to the item/clientr flags.

Yeah, specific modifications was more or less what I eas going for, just thought the enchantment would be simpler to explain.

Would the storing it in an array part follow this model:

CharacterItems

ID - CharacterID - ItemID - Quantity - Modifications (better name..)

1 - 1 - 1 - 2 - Null
2 - 1 - 2 - 1 - Null
3 - 2 - 3 - 4 - Null
4 - 2 - 4 - 3 - Null
5 - 1 - 5 - 1 - {str, 1}
6 - 1 - 5 - 1 - {str, 2}, {dex, 1} /same character , same item id, it has 2 modifications..giving str +2 dex +1..

You could put enchantments in it's own table though. I.e.

ID - CharacterItemID - Enchantment - EnchantmentValue

1 - 5 - strength - 1

wouldnt this essentially mean that all items that the character owns with an item id of 5 would take on these chsracteristics? Just trying to make sure I read this right. That might be useful for something else im trying to do, but at the moment I am trying to have a single item id have the ability to have modifications , sometimes multiple modifications, done to it...

Thanks for the help.

[fowlplay4]

Originally Posted by Torankusu

wouldnt this essentially mean that all items that the character owns with an item id of 5 would take on these chsracteristics?

Yep, using a separate table would be the more 'normalized' way to do it though. You could have a 100 enchantments for the same item if you wanted.

The tricky part is deciding how you want to query and load the information. I would probably do:

1. Load characteritem data
2. Load and apply enchantments using array of character item ids

I.e.

SELECT CharacterItems.*, Items.*
FROM CharacterItems, Items
WHERE CharacterID = 1 AND Items.ID = CharacterItems.ItemID;

SELECT CharacterItemID, Enchantment, EnchantmentValue
FROM Enchantments
WHERE CharacterItemID IN (1, 2, 3)

I wrote a function that generates an escaped IN clause with an array of data:

PHP Code:

// usage: generateInClause("column", {1,2,3});
public function generateInClause(column, items) {
  temp.clause = column @ " IN (";
  temp.last   = items.size() - 1;
  for (temp.i = 0; temp.i < items.size(); temp.i++) {
    temp.item = items[temp.i];
    if (temp.i == temp.last) {
      temp.clause @= "'" @ escapestring2(temp.item) @ "'";
    } else {
      temp.clause @= "'" @ escapestring2(temp.item) @ "',";
    }
  }
  temp.clause @= ")";
  return temp.clause.link();
} 


[Torankusu]

Originally Posted by fowlplay4

Yep, using a separate table would be the more 'normalized' way to do it though. You could have a 100 enchantments for the same item if you wanted.

The tricky part is deciding how you want to query and load the information. I would probably do:

1. Load characteritem data
2. Load and apply enchantments using array of character item ids

I.e.

SELECT CharacterItems.*, Items.*
FROM CharacterItems, Items
WHERE CharacterID = 1 AND Items.ID = CharacterItems.ItemID;

SELECT CharacterItemID, Enchantment, EnchantmentValue
FROM Enchantments
WHERE CharacterItemID IN (1, 2, 3)

I wrote a function that generates an escaped IN clause with an array of data:

PHP Code:

// usage: generateInClause("column", {1,2,3});
public function generateInClause(column, items) {
  temp.clause = column @ " IN (";
  temp.last   = items.size() - 1;
  for (temp.i = 0; temp.i < items.size(); temp.i++) {
    temp.item = items[temp.i];
    if (temp.i == temp.last) {
      temp.clause @= "'" @ escapestring2(temp.item) @ "'";
    } else {
      temp.clause @= "'" @ escapestring2(temp.item) @ "',";
    }
  }
  temp.clause @= ")";
  return temp.clause.link();
} 


oh wow, thanks a lot.

I feel better knowing that what I wanted to do is in fact possible, and will post some results in a few days when I have a day off to actually write up the functions.

Was not sure exactly how to store the information
such as
-modifications, stages, enchantments, etc
that would be unique to:
-a specific character (or even just account..)
-and a specific item a character owned
(even if they owned multiples of that exact item, that did not share those same modifications, stages, and/or enchantments)

but this gives me a lot of information to structure around. :]

thanks a lot.

I should have asked outright, but is there no real downside to storing information in the column for modifications or enchantments like this? :

ENCHANTMENTS
{str,1},{dex,2}

and accessing it similar to an array:

example = {str,1}, {dex,2}

echo(example[0][0]); // str
echo(example[0][1]); // 1
echo(example[1]); // "dex,2"

I won't be able to write anything up and test it until I have a day off, but I'm trying to gather enough info to set up something the best and most correct way, and so far you've been a great help.

thanks again.

[fowlplay4]

Originally Posted by Torankusu

I should have asked outright, but is there no real downside to storing information in the column for modifications or enchantments like this? :

ENCHANTMENTS
{str,1},{dex,2}

and accessing it similar to an array:

example = {str,1}, {dex,2}

You would use: {{"str",1},{"dex",2}}

Using an array would make it harder to query against for statistical purposes, but if it's just being used to load/save then you would be fine doing it that way.

I.e. Calculate how many items that have more than 5 strength enchanted exist.

SELECT COUNT(*)
FROM CharacterItems, Enchantments
WHERE Enchantments.CharacterItemID = CharacterItems.ID
AND enchantment = 'str' AND enchantmentvalue > 5;

If you design your system properly the only part that cares how that information is loaded and stored should be easy to change from a column in an array, to it's own table if you decide.

[Torankusu]

Originally Posted by fowlplay4

You would use: {{"str",1},{"dex",2}}

Using an array would make it harder to query against for statistical purposes, but if it's just being used to load/save then you would be fine doing it that way.

this could be useful..

I.e. Calculate how many items that have more than 5 strength enchanted exist.

SELECT COUNT(*)
FROM CharacterItems, Enchantments
WHERE Enchantments.CharacterItemID = CharacterItems.ID
AND enchantment = 'str' AND enchantmentvalue > 5;

If you design your system properly the only part that cares how that information is loaded and stored should be easy to change from a column in an array, to it's own table if you decide.

dumb question, but, is this modeled after the enchantment table having two separate columns - one for the enchantment name, and the other referencing the value? (str + 5).

ENCHANTMENTS
ID - ItemID - Enchantment - EnchantmentValue
1 - 5 - str - 5


--What would happen if I had an item (itemID 5..) that had multiple enchantments?
ex: str + 5 , dex + 2 ?

Sorry for the redundant questions.

[fowlplay4]

Originally Posted by Torankusu

dumb question, but, is this modeled after the enchantment table having two separate columns - one for the enchantment name, and the other referencing the value? (str + 5).

--What would happen if I had an item (itemID 5..) that had multiple enchantments?
ex: str + 5 , dex + 2 ?

Yes, if your enchantments need more information you can just add more columns. I.e. enchantmentvalue2, 3, etc.

That item would have multiple enchantments then.