 |
 |
|
 |
|
| Author |
Message |
raz
Joined: 13 May 2005
Posts: 11
|
 Posted: Tue Jun 28, 2005 9:20 am Post subject: Properties and SQL |
 |
|
Recently, I have been toying with the usage of properties in my character, zone, etc. objects. It has been working well, but I am rather unsure of a good way to store these in an SQL database. My current thinking is that I would mark certain properties (e.g. "name", "race", etc.) as defined columns. Then, any property that doesn't have a definite column would be held in an "other" column.
Is this a good/effective way of saving property lists? Would certain SQL databases handle such a task better than others? |
|
| Back to top |
|
|
|
|
 |
|
 |
|
|
|
|
| Author |
Message |
Greggen
Joined: 16 May 2005
Posts: 36
|
| Posted: Tue Jun 28, 2005 9:36 am Post subject: |
|
|
Instead of using a different table for each type of object, you'll probably be better off using something like key/value pairs. If your values can be represented as strings, this is easy enough. If you want to store other objects as values, for example, it can be a little more tricky.
The method I use is quick and dirty: I store the YAML for the object in a text blob. |
|
| Back to top |
|
|
|
|
|
|
|
|
|
|
|
| Author |
Message |
clacker
Joined: 27 Jun 2005
Posts: 2
|
| Posted: Tue Jun 28, 2005 1:41 pm Post subject: |
|
|
raz, I've been wondering this myself lately. One solution I thought of was to put all of the standard things (player_id, name, hit_points) into one table. Then I would create a second table for any special attributes that refer to the player by his ID number. That way I can use an sql search to find if a particular player has an special attributes from the special attributes table. This lets me add extra attributes without touching my original table. If the new attributes table gets to big, it can be split into a few tables, i.e. weapons, armor, charms.
I was thinking a table for players, a table for attributes (attribute_id, player_id, attribute_number, attribute_subtable), and the sub tables like weapons, shields, magic, etc. |
|
| Back to top |
|
|
|
|
|
|
|
|
|
|
|
| Author |
Message |
Tyche
Joined: 13 May 2005
Posts: 176
Location: Ohio, USA
|
| Posted: Tue Jun 28, 2005 6:24 pm Post subject: Re: Properties and SQL |
|
|
| raz wrote: | | Recently, I have been toying with the usage of properties in my character, zone, etc. objects. |
What do you mean by properties? Do you mean like Java Properties objects or Perl hashes? Are these runtime extendable? Are they nestable in heirarchies? |
|
| Back to top |
|
|
|
|
|
|
|
|
|
|
|
| Author |
Message |
raz
Joined: 13 May 2005
Posts: 11
|
| Posted: Wed Jun 29, 2005 3:57 am Post subject: |
|
|
| Greggen wrote: | | Instead of using a different table for each type of object, you'll probably be better off using something like key/value pairs. If your values can be represented as strings, this is easy enough. If you want to store other objects as values, for example, it can be a little more tricky. |
While I think this is an interesting idea, I am kind of wondering if I left some confusion in my original post. By "objects", I meant "instances of classses". At first glance, this is what I thought you meant too. Just to avoid any confusion, did you mean something else by "objects"?
| Tyche wrote: | | What do you mean by properties? |
I mean a very standard key/value pair system. I'm using C++, so my implementation of the data structure is similar to this:
| Code: |
typedef std::string key_type;
typedef boost::any value_type;
std::map<key_type,value_type> property_map;
|
| Quote: | | Do you mean like Java Properties objects or Perl hashes? |
Yes; although, they are closer to Perl hashes.
| Quote: | | Are these runtime extendable? |
Yes.
| Quote: | | Are they nestable in heirarchies? |
Should be. |
|
| Back to top |
|
|
|
|
|
|
|
|
|
|
|
| Author |
Message |
Yui Unifex
Site Admin
Joined: 11 May 2005
Posts: 47
Location: Florida
|
| Posted: Mon Jul 25, 2005 4:18 pm Post subject: |
|
|
| raz wrote: | | Recently, I have been toying with the usage of properties in my character, zone, etc. objects. It has been working well, but I am rather unsure of a good way to store these in an SQL database. My current thinking is that I would mark certain properties (e.g. "name", "race", etc.) as defined columns. Then, any property that doesn't have a definite column would be held in an "other" column. |
I'm probably doing it the naive way, but currently properties are stored in their own table with three simple pieces of data: The parent ID (pointing at an object instance) to which the property belongs, the property name, and the data as a byte array. There are no nested properties. I would certainly recommend one property value per row, instead of globbing it all into an 'other' column, though.
Only non-dynamic properties are stored directly on the class table. The distinction between dynamic and non-dynamic is simple: If we can't predict that a class will have a value (e.g., it's set by a script), it's dynamic and thus shouldn't be stored as a direct column. This allows for easy querying when necessary, but since the properties are stored in memory on the property list, it also allows for uniform access.
All of the non-dynamic properties have a wrapper setter/getter to make them appear like natural fields. |
|
| Back to top |
|
|
|
|
|
|
|
|
|
|
|
| Author |
Message |
Tyche
Joined: 13 May 2005
Posts: 176
Location: Ohio, USA
|
| Posted: Mon Jul 25, 2005 6:24 pm Post subject: |
|
|
| raz wrote: |
I mean a very standard key/value pair system. I'm using C++, so my implementation of the data structure is similar to this:
| Code: |
typedef std::string key_type;
typedef boost::any value_type;
std::map<key_type,value_type> property_map;
|
|
Sorry Raz. I asked some questions and then promptly forgot about this thread until is popped back up on recent posts.
Yui's approach is reasonable. If you are limiting yourself to primitives like boolean, integer, float, character arrays then you could store them as
obj_id integer.
name text,
value text
Any of these types can easily be read (parsed) and written as strings.
If you are doing more complex types you can use something like YAML or provide a type column, storing the data as blob data. And then parsing it accordingly. It may well be the type name can be used as another tablename reference if the value field is not a primitive. |
|
| Back to top |
|
|
|
|
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
|
|
|
|
