DBC handling for mangos

Day 4: Final Touches.

Wed, 17 Aug 2016 12:10:00 +0000

I have put the final touches to the DBC wiki exporter and if you look at the wiki section for DBC files (for Zero Only ATM) you will see the results.

The DBC databases will end up in a github repo soon and I'll post the URL here.

Any comments or suggestions, please let me know.

The next stage of this project is a dbcDocs editor. I have the skeleton of this designed and just need to find the time to complete it.

It will have two modes of operation: local and remote

Local edits a local dbcDocs database

Remote edits the mangos hosted dbcDocs database

Day 3: Realisations and redesign time

Fri, 29 Jul 2016 10:56:40 +0000

The Wiki is the one area where a majority of information is cross core applicable.

- During my testing a came across a few problems.

in the dbcField table we recorded the dbc File, fieldname, description and field position but this hid a more fundamental problem.
- Different cores have the same field with the same meaning, but in a different position. The current DB design would mean that the description information would have to be entered multiple times - each time the field was in a different location.

So.... A db redesign was needed.

I created a new dbcFieldDescriptions table, containing the following fields:
dbcFieldDescriptionId, dbcFileName, dbcFieldName, dbcFieldDescription, dbcFieldNotes

Then modified the dbcField table by adding a dbcFieldDescriptionId and removing dbcFieldName, dbcFieldDescription and dbcFieldNotes.

I'll post the table defs later on, now time for some more testing.

Day 2: Working with the data

Wed, 27 Jul 2016 10:18:01 +0000

Now that we had the data in the DB it was time for the next step.

I wrote a quick app to read through the DBC table entries and add an entry to dbcFile, I also wrote a helper function to calculate the client masks based on the selected core.

Worked like a dream

I then extended it to use the second query to populate the dbcField table with the fields from each dbc file.

Next I also added a function to read the DBC_definition.xml from mangos extractor to populate any additional information held in the file regarding the fields.

Finally to top off this section of work, I created a template example in the new wiki so that I could:
a) Check that it looks ok
b) Can double check that the data is extracted ok

Next stage will be run the entire process to squirt the data out and create all the entries.

Day 1: Querying the DB

Tue, 26 Jul 2016 13:28:18 +0000

Now that we have a majority of the data already in the Database, we now need to work out how to get meaningful information back out !

Thankfully MySQL does provide us with a way of doing this. In the following example my main database containing the DBC data and we are going to query for records for MangosZero - so tables beginning dbc0_

SELECT DISTINCT TABLE_NAME AS DBCFilename FROM information_schema.columns
WHERE table_schema = 'dbc' AND table_name LIKE 'dbc0_%'

This returns a nice list of DBC filenames belonging to Zero, we can easily write a tool to read through that list and process the information.

DBCFilename
dbc0_animationdata
dbc0_areapoi

For the purpose of a demonstration of how to get the field information for a certain table, see the following query. Again we are using the database 'dbc' but this time we are looking for a specific table (dbc0_animationdata)

SELECT COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,COLUMN_TYPE FROM information_schema.columns
WHERE table_schema = 'dbc' AND table_name = 'dbc0_animationdata%'

This returns the following:

"COLUMN_NAME"         "ORDINAL_POSITION"    "DATA_TYPE"    "COLUMN_TYPE"
"AnimationDataId"            "1"               "int"         "int(11)"
"Name"                       "2"               "text"        "text"
"WeaponFlags"                "3"               "int"         "int(11)"
"Bodyflags"                  "4"               "int"         "int(11)"
"Flags"                      "5"               "int"         "int(11)"
"FallbackAnimationDataId"    "6"               "int"         "int(11)"
"PreviousAnimationDataId"    "7"               "int"         "int(11)"

For the guts of the data we need, this is everything we need... until next time

Day 1: DB table design

Tue, 26 Jul 2016 10:21:00 +0000

Lets start with the basics...

For each DBC File we want to store the following information

A unique Id for this file (primary key and autonumber).
The Filename.
A short description of what the File is/does.
Some notes about the File.
Which client(s) it's present in.
Whether MaNGOS actually uses the file.
Whether to show the contents of the file in the wiki.

so the SQL table definition will be:

/*Table structure for table `dbcfile` */

CREATE TABLE `dbcfile` (
  `DbcId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique Key for this file',
  `DbcFilename` varchar(250) NOT NULL COMMENT 'The Name of the DBC File',
  `DbcDescription` varchar(250) DEFAULT NULL COMMENT 'A Brief Description of the DBC File',
  `DbcNotes` text COMMENT 'Notes about the DBC File',
  `ClientMaskId` int(11) NOT NULL COMMENT 'A Lookup into the ClientMasks Table',
  `IsIncluded` tinyint(1) DEFAULT '1' COMMENT 'Whether this DBC file is used in MaNGOS',
  `IsShownInWiki` tinyint(1) DEFAULT '0' COMMENT 'Is the File shown in the Wiki',
  PRIMARY KEY (`DbcId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Then we need to store information about all the fields in the file

A unique Id for this field (primary key and autonumber)
A link to the DBC file this field belongs to.
The name of the field.
A Brief Description of the Field.
A note about the field.
The position of the field in the file (i.e. the order of the fields)
Which client(s) it's present in.
Whether MaNGOS actually uses the field.
Whether to show the contents of the field in the wiki.

so the SQL table definition will be:

/*Table structure for table `dbcfield` */

CREATE TABLE `dbcfield` (
  `DbcFieldId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'A Unique Id for this field',
  `DbcId` int(11) NOT NULL COMMENT 'A Lookup into table dbcFile',
  `DbcFieldName` varchar(250) NOT NULL COMMENT 'The Name of the field',
  `DbcFieldDescription` varchar(250) DEFAULT NULL COMMENT 'A Brief Description of the Field',
  `DbcFieldNote` text COMMENT 'A Longer Description of the field',
  `DbcFieldOrder` int(11) DEFAULT NULL COMMENT 'The position of the field in the file',
  `ClientMaskId` int(11) DEFAULT NULL COMMENT 'A Lookup into the Table ClientMask',
  `IsIncluded` tinyint(1) DEFAULT '1' COMMENT 'Indicates whether this field is used in Mangos',
  `IsShownInWiki` tinyint(1) DEFAULT '0' COMMENT 'Indicate whether this field is shown on the wiki',
  PRIMARY KEY (`DbcFieldId`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Rather than storing multiple entries of the files and fields for different clients, a cleaner method it to use a bit mask.

In this mask each client is given a unique value and multiple values can to added together when needed, for example:

Vanilla / Classic = 1
TBC = 2
WotLK = 4
Cata = 8
Mop = 16
Wod = 32

To set a file as only present in Vanilla, you would set it's ClientMaskId = 1
To set a file as only being present in Vanilla and TBC, you would set it's ClientMaskId = 3 (1+2
To set a file as only being present in Mop and Wod, you would set it' clientMaskId = 48 (16 + 32)

The ClientMask table has the following fields:

A ClientMaskId which is the primary Key and an autonumber field
ClientMaskName which contains a description of what the clients the mask supports
ClientMaskValue a list of the different bits needed to make this mask

so the SQL table definition will be:

/*Table structure for table `clientmask` */

CREATE TABLE `clientmask` (
  `ClientMaskId` int(10) unsigned NOT NULL,
  `ClientMaskName` varchar(50) NOT NULL,
  `ClientMaskValue` varchar(50) NOT NULL,
  PRIMARY KEY (`ClientMaskId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And finally for this chunk, the actual Client Mask Entries:

/*Data for the table `clientmask` */
LOCK TABLES `clientmask` WRITE;

insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (0,'NONE','0x00');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (1,'Classic','0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (2,'TBC','0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (3,'Classic + TBC','0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (4,'Wotlk','0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (5,'Wotlk + Classic','0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (6,'Wotlk + TBC','0x04+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (7,'Wotlk + TBC + Classic','0x04+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (8,'Cata','0x08');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (9,'Cata + Classic','0x08+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (10,'Cata + TBC','0x08+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (11,'Cata + TBC + Classic','0x08+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (12,'Cata + Wotlk','0x08+0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (13,'Cata + Wotlk + Classic','0x08+0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (14,'Cata + Wotlk + TBC','0x08+0x04+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (15,'Cata + Wotlk + TBC + Classic','0x08+0x04+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (16,'Mop','0x16');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (17,'Mop + Classic','0x16+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (18,'Mop + TBC','0x16+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (19,'Mop + TBC + Classic','0x16+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (20,'Mop + Wotlk','0x16+0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (21,'Mop + Wotlk + Classic','0x16+0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (22,'Mop + Wotlk + TBC + Classic','0x16+0x04+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (23,'Mop + Cata','0x16+0x08');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (24,'Mop + Cata + Classic','0x16+0x08+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (25,'Mop + Cata + TBC','0x16+0x08+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (26,'Mop + Cata + TBC + Classic','0x16+0x08+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (27,'Mop + Cata + Wotlk','0x16+0x08+0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (28,'Mop + Cata + Wotlk + Classic','0x16+0x08+0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (29,'Mop + Cata + Wotlk + TBC','0x16+0x08+0x04+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (30,'Mop + Cata + Wotlk + TBC + Classic','0x16+0x08+0x04+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (32,'WoD','0x32');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (33,'WoD + Classic','0x32+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (34,'WoD + TBC','0x32+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (35,'WoD + TBC + Classic','0x32+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (36,'WoD + Wotlk','0x32+0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (37,'WoD + Wotlk + Classic','0x32+0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (38,'WoD + Wotlk + TBC','0x32+0x04+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (39,'WoD + Wotlk + TBC + Classic','0x32+0x04+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (40,'WoD + Cata','0x32+0x08');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (41,'WoD + Cata + Classic','0x32+0x08+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (42,'WoD + Cata + TBC','0x32+0x08+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (43,'WoD + Cata + TBC + Classic','0x32+0x08+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (44,'WoD + Cata + Wotlk','0x32+0x08+0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (45,'WoD + Cata + Wotlk + Classic','0x32+0x08+0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (46,'WoD + Cata + Wotlk + TBC','0x32+0x08+0x04+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (47,'WoD + Cata + Wotlk + TBC + Classic','0x32+0x08+0x04+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (48,'WoD + Mop','0x32+0x16');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (49,'WoD + Mop + Classic','0x32+0x16+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (50,'WoD + Mop + TBC','0x32+0x16+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (51,'WoD + Mop + TBC + Classic','0x32+0x16+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (52,'WoD + Mop + Wotlk','0x32+0x16+0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (53,'WoD + Mop + Wotlk + Classic','0x32+0x16+0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (54,'WoD + Mop + Wotlk + TBC','0x32+0x16+0x04+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (55,'WoD + Mop + Wotlk + TBC + Classic','0x32+0x16+0x04+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (56,'WoD + Mop + Cata','0x32+0x16+0x08');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (57,'WoD + Mop + Cata + Classic','0x32+0x16+0x08+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (58,'WoD + Mop + Cata + TBC','0x32+0x16+0x08+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (59,'WoD + Mop + Cata + TBC + Classic','0x32+0x16+0x08+0x02+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (60,'WoD + Mop + Cata + Wotlk','0x32+0x16+0x08+0x04');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (61,'WoD + Mop + Cata + Wotlk + Classic','0x32+0x16+0x08+0x04+0x01');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (62,'WoD + Mop + Cata + Wotlk + TBC','0x32+0x16+0x08+0x04+0x02');
insert  into `clientmask`(`ClientMaskId`,`ClientMaskName`,`ClientMaskValue`) values (63,'WoD + Mop + Cata + Wotlk + TBC + Classic','0x32+0x16+0x08+0x04+0x02+0x01');

UNLOCK TABLES;

Day 1: getting the data into a DB

Tue, 26 Jul 2016 09:54:40 +0000

Using the tool Mangos Extractor, I have extracted the DBC's from MangosZero, MangosOne, MangosTwo and MangosThree

- I made a slight change to each of the files to change the tablenames from dbc_xxxx to dbc0_xxx - which 0 is the core number

I then loaded then all into a new database a created called DBC (imaginative I know :D)

The next part is mainly for use with the Wiki part of the project, but is applicable to all.

For Each core in the DB we need to:
- Get a list of all the tables and store them in a dbcFiles table along with which core they belong to
- Get a list of all the fields and properites in the tables and store them in a dbcField table along with which core they belong to

For now, that should enough to have a proper proof on concept

I'll add at this point that I am also working to incorporate localised client DBC files into this as well

Day 1: starting out new

Tue, 26 Jul 2016 09:20:11 +0000

For many many years now I have found the DBC handling in MaNGOS odd in that (to me at least) it is a perfect candidate for moving the data into a database, but has always been shunned

Using the Mangos Extractor (found in the MangosTools github repo), the data can be extracted into sql files which can then be loaded into the db.

Why bother you may ask ?

1) Having the data in a db and mangos access it from there means that the facility to correct DBC 'bugs/errors' on the server becomes an option, rather than having 'hacks' in the code to correct them

2) Having the data in a DB means that Mangos DB references can be compared to DBC references 'on the fly' to check for any discrepancies.

3) The Wiki needs the basic information regarding DBC use and fields anyway.

I had started on a design for this late last year - Which is why there is some DBC information on the Wiki

But earlier this year, I was looking to bring a few of my DBC related 'side' projects together as a single project.

Namely:

DBC to SQL convertor (Mangos Extractor)

DBC SQL importer Scripts

DBC Data Remapper Tool - For wiring up which DBC files / fields belong to which version of WOW

DBC Wiki entry creator

DBC Docs Editor (Based on the DBDocs Editor) - Including Reading / Creating the .XML files which the Mangos Extractor uses to map the fieldnames