Torque Relational Database Table Editor

Download Code File

I developed this code after I accidentally deleted the inventory item editor I had been working on. I had made several panels and handmade each fieldname and label. I had gone through the trouble of making a panel for each subtable like spelleffects, itemflags, prerequisites, etc. Then while prototyping a new subgui I accidentally saved over my big one.

Alas. I am not a quitter, so I thought about how to get back to where I was in a quicker way and came up with the idea of reflection. I could use the schema information mysql presents about tables and columns to generate all the information I need to create an ingame table editor. What's more, and maybe the best part, is that if you properly setup your tables with Foreign Keys and Primary Keys, you gain the benefit of data integrity and ease of authoring through the GUI. It will pull in the related tables and instead of an edit control, will generate a dropdown box with the valid values from the foreign key table.


This is handy, for example, when making a new item and wanting to change its itemcategory. We use simple integers to point back to itemcategory, so instead of trying to memorize which number means which category, the tool creates a dropdown box with the actual friendly display name of the foreign key field. So now you can just select the correct value. You can make this association by simple adding a display:friendlycolumnname to the COMMENTS field of the column definition. I plan on using the COMMENTS field to reproduce other behavior I had made, like displaying a browse button to enter in a filename, and a guiObjectView to display a shape, and a guiBitmapControl to display an icon, as in this shot:


But this one is now dead and only lives on in screenshots, alas.

Other features:
Pulls in extra column information like fieldtype, nullable, max length, and provides automatic data validation for the fields when you click Save.

You can clone an existing record by hitting Insert as New, and Delete records. Jump to first, last, previous, next.

The database system underneath is the one honed in our Gryphon project and is a dramatically customized and effective combination of Tony Richard's Event Driven database resource and mysql coding. So the queries and resultset handling are separated in the script. If you use something different you will need to modify the database syntax accordingly.

If anything, I hope it's something you can learn from :)

Tabledef Example: We have an itemcategories table, with a primary key ID, and a friendly category name. It's table def looks like this:

DROP TABLE IF EXISTS 'rpgdatabase'.'itemcategories';
CREATE TABLE  'rpgdatabase'.'itemcategories' (
  'itemcategoryid' int(10) unsigned NOT NULL auto_increment COMMENT 'display:categoryname',
  'categoryname' varchar(45) NOT NULL,
  PRIMARY KEY  ('itemcategoryid')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now my itemtypes table, which has an itemtype column foreign-keyed to this table's itemcategoryid field, will create a popup with the itemcategoryid underneath, and the categoryname as the display text field.

I don't know how well the queries hold up to non-relational tables or to other database schemas, but you should be able to modify them. This script contains lots of cool code to learn from.

Requirements/Notes:
To launch this, I created the command gote(); which is like 'go table editor'. There's also a te(); function which is used to reload the script file, I hate typing exec blah blah blah all the time.
You MUST have integer based primary keys on your tables or things won't work well
Columns used as foreign key columns can have a display:fieldname in the COMMENTS field, which will specify an alternate column to use as a display value.
You must have a connection to the database server and change the $DatabaseName = "rpgdatabase"; line at the top of the script file.
There is validation support for int, float, and varchar. If you need others, check the validation routine and add em.

This uses Orion Elenzil's Script Array resource too. He's the man and I use his resources a lot, sometimes even cyberstalk his profile to look at old stuff that could be useful. Learn from others!!!

Improvements coming in my personal version:
I want to fly around the world as a GM and click on someone, have that UniqueID get pulled in, the GUI will pull up the available DB info, and be able to modify that NPC's DB based information and inventory, etc. I want to create vendors and add items, Set loot tables on chests, generate datablocks for items, etc. With this tool in my pipeline I can do it all from within Torque and not have to leave. I want to edit quests, dialog, spawns, etc. A full fledged table editor like this one will enforce referential data integrity and healthy database coding practices while giving me a persistent editor I can really use.



The item editor will gain back the guiObjectView, Icon, and filename boxes it once had. I also plan on adding random generator to this screen so I can click in a field and press a button to generate a context-correct random number within ranges I set using the COMMENTS field in my database. After that comes the MAKE MY MMO button.

Have fun!!

[Update]
I've made it much easier to add in associations now, and included some mysql table definitions so you can see how I use the lookup table and the comments fields to make the magic happen.

Note that the lookup fields are going to phase out the complicated lookup: style comments, making it much easier to do foreign table style dropdowns.

In this update I've also included support for custom image and shape comments, which show a browse button and bitmap/guiobjectview respectively