Jump to content
Sign in to follow this  
  • entries
    7
  • comments
    0
  • views
    1,583

Day 1: DB table design

Sign in to follow this  
antz

619 views

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;

 

Sign in to follow this  


0 Comments


Recommended Comments

There are no comments to display.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Contact Us

To contact us click here
You can also email us at [email protected]

Privacy Policy | Terms & Conditions

Repositories

The Link to the master list
of MaNGOS repositories:
Copyright © getMaNGOS. All rights Reserved.

This website is in no way associated with or endorsed by Blizzard Entertainment®
×
×
  • Create New...