Register forum user name Search FAQ

Gammon Forum

Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the password reset link.
 Entire forum ➜ MUSHclient ➜ General ➜ Database interface design advice.
Database interface design advice.

It is now over 60 days since the last post. This thread is closed.     Refresh page

Posted by Blainer   (191 posts)  Bio
Date Wed 03 Jun 2009 07:31 AM (UTC)
Message
Not sure if interface is the right word for what I am trying to create. It's basically a front end to SQLite. I pass the command and the data to the plugin. The plugin then runs SQL commands on the database. But I am stuck at the planning stage.

I can't decide if it would be better to make a generic interface that handles all SQL and does no formating or cutting up of strings just gets variables with the data and the place they need to go in the database and then create all the processing of that data in the functions calling the database interface plugin. If I have a Items table and Mobs table am I just repeating code?

Or to create one huge plugin that can handle all types of data passed to it and decides what to do with the data. Am I just making this one plugin too complicated.

Or would I be better to create a plugin to handle each type of information to be stored containing all the SQLite stuff. Say an inventory plugin and a Mobs plugin each one opening and modifying database as needed.

How do you guys do it?
Top
Posted by David Haley   USA  (3,881 posts)  Bio
Date Reply #1 on Wed 03 Jun 2009 03:45 PM (UTC)
Message
What's an example problem that you would solve with such a thing? That can help narrow down what good solutions would be.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
Top
Posted by Blainer   (191 posts)  Bio
Date Reply #2 on Thu 04 Jun 2009 03:28 AM (UTC)

Amended on Thu 04 Jun 2009 03:32 AM (UTC) by Blainer

Message
MUD sends inventory command output.

Output:
<unique number><description>
00001 : Potion of Healing
00002 : Potion of Healing
00003 : Potion of Healing
00004 : Potion of Healing
00005 : A Sword

-Put this information into a database in ITEMS table creating a unique record for each <description> except where the item already has a record, in this case one record is created using <description> to test.
-Maintain INVENTORY table records storing current inventory items, one record per item.
-Add / Remove / Search this database as needed.
-Display current INVENTORY table with MUD commands for each item, stacking multiple items.

This is the over all problem this plugin will solve along with MOBs and Players latter. I am stuck on how best split the tasks up into separate plugins or weather to make one huge one baring in mind I intend to add a lot more to the database.

I have my database design document pretty much finished outlining all the tables to be created. And I am doing the pseudo code for the function or plugin (not sure which it will be yet) to handle the SQL commands and database creation.

Top
Posted by David Haley   USA  (3,881 posts)  Bio
Date Reply #3 on Thu 04 Jun 2009 07:02 PM (UTC)
Message
The individual problems seem pretty simple, so I don't think you need a very sophisticated general purpose database engine. As you write code for the various pieces you will quickly see patterns where you can combine code. I would recommend that you just get started instead of trying to predict all possible general uses: frankly, being too general might mean that you end up coding a bunch of stuff you'll never use, and therefore will be a waste of time.

As for one plugin vs. several, I would keep it in one for now. That'll make is easiest to share code.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
Top
Posted by Blainer   (191 posts)  Bio
Date Reply #4 on Fri 05 Jun 2009 02:35 AM (UTC)
Message
Thanks Dave.

I'll create one plugin to handle everything broken into functions to make adding additional features latter easier.

For instance,

Function to get data.
Function to open, close create db.
Function to get data from inventory command.
Function to get data from who command.
etc.

Defining the problem to someone else helped me to understand the steps needed more clearly as well. I'll remember that next time I start planning something.
Top
Posted by David Haley   USA  (3,881 posts)  Bio
Date Reply #5 on Fri 05 Jun 2009 05:10 PM (UTC)
Message
No problem :-) That sounds like a good approach. You'll probably see similarities between the various 'get' methods, and then later when you add information via 'set' methods of some kind. That's where you can start writing more general database methods: methods that solve the problems you're actually running into.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
Top
Posted by Blainer   (191 posts)  Bio
Date Reply #6 on Tue 16 Jun 2009 02:06 PM (UTC)
Message
Ok I got all the data from "invdata" into a db with tables for spells, target, types, wear location to reference latter. The code is probably terrible but if anyone wants to see it let me know.

Now I am looking at setting up a table to store items permanently. Got all the triggers and stuff to get the data into variables now I am stuck on design again.

The problem is items have different mods on them, for instance MUD Output for one item:

156099982,,a @WT@wrusty @RA@wylorian @YM@wace,9,5,0,-1,-1

{invdetails}
{invheader}156099982|9|Weapon|60|5|wield|v3|||||1|
>>{weapon}mace|12|pound|Bash|
>>{statmod}Damage roll|1
{/invdetails}

The lines marked ">>" are mods, the items can have different types of mods and the data in these lines is not constant. So I was thinking about creating one table per item, checking each item is unique. The table name would be the serial for the item. This would allow flexible tables to fit the varying mod data for each item.

Is this a bad idea, will I run into trouble latter?
Top
Posted by David Haley   USA  (3,881 posts)  Bio
Date Reply #7 on Tue 16 Jun 2009 04:02 PM (UTC)
Message
Creating a table per item sounds like a very expensive approach; databases aren't really intended to have that many tables.

An easy approach is to just have a string field into which you store delimited strings; you'd have to parse these yourself, but at least it's unified.

Is the order of the columns important? One thing you could do would be to have a "mod" table, with columns like this:

Parent object
Mod number
Sequence number
Value contents

Then you would do something like this:


Parent object: 156099982
Mod number: 0
Sequence: 0
Contents: mace

Parent object: 156099982
Mod number: 0
Sequence: 1
Contents: 12

Parent object: 156099982
Mod number: 0
Sequence: 2
Contents: pound

Parent object: 156099982
Mod number: 0
Sequence: 3
Contents: Bash

Parent object: 156099982
Mod number: 1
Sequence: 0
Contents: Damage roll

Parent object: 156099982
Mod number: 1
Sequence: 1
Contents: 1


This is perhaps a little verbose, but it lets you represent multiple-length modifiers without creating lots of tables or having to parse strings.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
Top
Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #8 on Tue 16 Jun 2009 09:27 PM (UTC)

Amended on Tue 16 Jun 2009 09:28 PM (UTC) by Nick Gammon

Message
Quote:

Is this a bad idea, will I run into trouble latter?


It is a very bad idea, not to put too fine a point on it. :-)

Databases are designed to have one row per item - not a table.

What you have here is a classic case of many-to-many items, which is easily solved. (Many inventory items, each of which can have many stats).

By the by, surely each inventory item only has one weapon line? After all, it is a mace or it isn't?

I think I would handle this with the following tables:


  1. The inventory table which has the base information (ID, level etc.) which applies to every inventory item. The key would be the item ID, which is unique.

  2. An inventory-type table per type (eg. weapon, container, armour, etc.). This is because the extra stats per item vary. The key could still be the item ID.

  3. A statmod table, which shows the mods for an item. As there are likely to be more than one per item, you have to have the key as a generated number (auto-increment). A "foreign key" would be the item ID (that is, a secondary key).


So this is how it would work - first you get the inventory list. Say you have the following returned from the MUD:


{inventory}
   44319594/0,HG,Sword of Aardwolf,210
{/inventory}

{invdetails}
{invheader}44319594|0|210|Weapon|1,000|35|wield|unique, glow, hum, magic, v3, precious|Lasher|||||
{weapon}sword|630|slice|Slash|flaming
{statmod}Damage roll|21
{statmod}Hit roll|14
{/invdetails}


Now we add the following stuff:


  • inventory table: 

    
  ID = 44319594
  Level = 210
  Name = Sword of Aardwolf
  type = weapon

... other details here


  • weapon table:

    
  ID = 44319594  (so we know it is this particular Sword of Aardwolf)
  type = sword
  avg_damage = 630
  dmg_noun  = slice
  dmg_type = Slash


  • statmod table (2 rows):

    
  ID = 123456  (auto-increment)
  item_ID = 44319594  (so we know it is this particular Sword of Aardwolf)
  modifies = Damage roll
  by = 21

  ID = 123457  (auto-increment)
  item_ID = 44319594  (so we know it is this particular Sword of Aardwolf)
  modifies = Hit roll
  by = 14




With all this it is easy to reconstruct an inventory item. First a search if the inventory table reveals everything in the inventory.

Then a search of the appropriate sub-table gives the extra details, eg.


if type == "weapon" then
  sql = "SELECT * FROM weapon_table WHERE ID = '44319594' "
end -- if


This returns a row with the weapon details (and you do a similar thing for armor, containers etc.).

Finally we look for stat mods:


 sql = "SELECT * FROM statmod_table WHERE item_ID = '44319594' "


This returns zero or more rows which are the item mods.
- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by David Haley   USA  (3,881 posts)  Bio
Date Reply #9 on Tue 16 Jun 2009 09:36 PM (UTC)
Message
It's true that things are easy if the list of possible modifiers is known in advance. But if modifiers are themselves of dynamic length, you wouldn't be able to capture them all in tables, because, well, you don't know how many to capture or what the characteristics are.

And yes, I should be been clearer in my post: you really don't want to create tables for each of these things.

BTW, it would appear that I misread your original post, and didn't realize that the weapon line wasn't a modifier like the others. So yes, I agree with Nick that you'd capture that stuff in its own table.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
Top
Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #10 on Tue 16 Jun 2009 09:43 PM (UTC)
Message
Quote:

It's true that things are easy if the list of possible modifiers is known in advance.


For the statmod, you don't need to know in advance the things, only that a stat modifies <something> by <some amount>.

However if you mean the weapon line, yes I agree. I think this is likely to be fairly static, that is inventory items will be in well-defined classes (weapons, armor, containers, keys, drinks, food etc.) and thus, whilst slightly tedious, making a table per item type would not be too bad.
- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by David Haley   USA  (3,881 posts)  Bio
Date Reply #11 on Tue 16 Jun 2009 09:50 PM (UTC)
Message
Actually I was referring to the schema of modifiers, really, or if you prefer the general form of such modifiers. If the only form is that <x> is modified by <y> the problem is very simple as you have shown. But if object modifiers can be free-form (such as, <x> is modified by y, z, w, a, b, c, ...) things get complicated.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
Top
Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #12 on Tue 16 Jun 2009 09:58 PM (UTC)
Message
I must be missing your point, David. My earlier example showed that in the case of the sword it was modified by "Damage roll by +21, Hit roll by +14".

This is handled by two entries in the statmod table, and this could be increased to any number. Thus the general design handles "<x> is modified by y, z, w, a, b, c" without any change).

To quote from one of Lasher's emails, you might see this:


{invdetails}
{invheader}44318507|0|210|Armor|100,000|10|torso|unique, glow, hum, magic, burn-proof, v3, precious|Lasher|||||
{statmod}Luck|2
{statmod}Constitution|2
{statmod}Wisdom|2
{statmod}Dexterity|2
{statmod}Strength|2
{statmod}Intelligence|2
{statmod}Hit points|125
{statmod}Mana|125
{statmod}Moves|125
{statmod}Damage roll|42
{statmod}Hit roll|42
{resistmod}Acid|100
{resistmod}Air|100
{resistmod}Cold|100
{resistmod}Earth|100
{resistmod}Energy|100
{resistmod}Fire|100
{resistmod}Holy|100
{resistmod}Light|100
{resistmod}Electric|100
{resistmod}Mental|100
{resistmod}Negative|100
{resistmod}Sonic|100
{resistmod}Shadow|100
{resistmod}Water|100
{resistmod}Magic|100
{resistmod}All physical|53
{/invdetails}


Apart from the complexity of the difference between a statmod and a resistmod (this could be two tables, or a flag in a single table), these would all just be entries in the statmod table, all identified by the same ID (not the primary key).
- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by David Haley   USA  (3,881 posts)  Bio
Date Reply #13 on Tue 16 Jun 2009 10:26 PM (UTC)
Message
Well, I'm missing some context here as I don't play the game in question. From your example, it seems that every possible modifier is of the form:
<x> modified by <y>

What I'm trying (and apparently failing :P) to express is that I could imagine that not being the case, i.e. there could exist modifiers of of different forms:

modify <commands available> by <swing, chop, stab, poke, ...>

In other words, if the number of columns is variable, you can't capture it all in a single table (without forcing additional parsing, or using some kind of sequence number to chain them).

Disregarding what would be cleaner for the MUD to do, I wasn't sure if such things were possible in this context.

Of course, I have no idea if these are actually possible: if everything is of one form, your solution is just what is needed.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
Top
Posted by Blainer   (191 posts)  Bio
Date Reply #14 on Wed 17 Jun 2009 02:27 AM (UTC)
Message
Sorry David that was my fault. All lines have a set number of fields or columns. Null values are either zero or still delimited in the case of the {invheader} line.

Heres the most varied test data I have at the moment for anyone interested. This thread has details on the tags and commands used to get them: http://www.gammon.com.au/forum/?id=9461

149707096,G,a helm made from a dragon's skull,16,7,0,-1,-1
{invdetails}
{invheader}149707096|16|Armor|16|15|head|glow, v3||||2||
{statmod}Constitution|2
{statmod}Intelligence|-1
{/invdetails}

149700508,,an evil looking black dagger,16,5,0,-1,-1
{invdetails}
{invheader}149700508|16|Weapon|15|10|wield|evil, v3||||a value||
{weapon}dagger|13|pierce|Pierce|
{statmod}Damage roll|3
{/invdetails}

149707095,,a bone sword,16,5,0,-1,-1
{invdetails}
{invheader}149707095|16|Weapon|16|40|wield|anti-good, v3||a vlaue||||
{weapon}sword|12|slice|Slash|
{statmod}Dexterity|1
{statmod}Damage roll|1
{statmod}Intelligence|-1
{/invdetails}

141529644,K,a potion of protection,5,8,0,-1,-1
{invdetails}
{invheader}141529644|5|Potion|25|1|hold|magic, held, v3|||||a value|
{spells}1|5|4|0|0|0|0
{/invdetails}

148785709,K,a well used backpack,7,11,0,-1,-1
{invdetails}
{invheader}148785709|7|Container|90|4|back|held, v3|a value|||||
{container}40|10|11|2|11|3|70
{/invdetails}

142349796,HKG,@WChalice of the @B<(@W=@YWatchmen@W=@B)>@w,1,12,1,-1,-1
{invdetails}
{invheader}142349796|1|Drink|2000|10||unique, glow, hum, magic, v3||Some guy||a value||
{drink}31|root beer|1000|620|27|30|0
{/invdetails}

Thanks guys, this will work out well as the way I process the data from MUD is already broken into sections similar to the tables.
Top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

39,570 views.

It is now over 60 days since the last post. This thread is closed.     Refresh page

Go to topic:           Search the forum

[Go to top] top

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

Home