Database interface design advice.

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?

What's an example problem that you would solve with such a thing? That can help narrow down what good solutions would be.

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.

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.

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.

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.

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?

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:



This is perhaps a little verbose, but it lets you represent multiple-length modifiers without creating lots of tables or having to parse strings.

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:



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.



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

Finally we look for stat mods:



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

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.

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.

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.

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:



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

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.

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.