[9651] [fix]improved quest POI's code + make use of actual data

[NoFantasy]

While reviewing, i don't think the others has much time, i see in the suggested patch:

Code sql:

--

-- Table structure for table `quest_poi`

--

DROP TABLE IF EXISTS `quest_poi`;

CREATE TABLE IF NOT EXISTS `quest_poi` (

`questId` int(11) UNSIGNED NOT NULL DEFAULT '0',

`poiId` int(11) UNSIGNED NOT NULL DEFAULT '0',

`objIndex` int(11) NOT NULL DEFAULT '0',

`mapId` int(11) UNSIGNED NOT NULL DEFAULT '0',

`areaId` int(11) UNSIGNED NOT NULL DEFAULT '0',

`floorId` int(11) UNSIGNED NOT NULL DEFAULT '0',

`unk3` int(11) UNSIGNED NOT NULL DEFAULT '0',

`unk4` int(11) UNSIGNED NOT NULL DEFAULT '0',

KEY `questId` (`poiId`,`questId`),

KEY `poiId` (`poiId`,`questId`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Table structure for table `quest_poi_points`

--

DROP TABLE IF EXISTS `quest_poi_points`;

CREATE TABLE IF NOT EXISTS `quest_poi_points` (

`questId` int(11) UNSIGNED NOT NULL DEFAULT '0',

`poiId` int(11) UNSIGNED NOT NULL DEFAULT '0',

`x` int(11) NOT NULL DEFAULT '0',

`y` int(11) NOT NULL DEFAULT '0',

KEY `questId` (`poiId`,`questId`),

KEY `poiId` (`poiId`,`questId`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Code sql:

--

-- Update for existing tables

--

ALTER TABLE `quest_poi` CHANGE `questid` `questId` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0' ;

ALTER TABLE `quest_poi` ADD `poiId` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `questId` ;

ALTER TABLE `quest_poi` CHANGE `unk1` `areaId` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0' ;

ALTER TABLE `quest_poi` CHANGE `unk2` `florId` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0' ;

ALTER TABLE `quest_poi_points` CHANGE `objIndex` `poiId` INT( 11 ) NOT NULL DEFAULT '0' ;

ALTER TABLE `quest_poi` DROP PRIMARY KEY ,

ADD INDEX `questId` ( `poiId` , `questId` ),

ADD INDEX `poiId` ( `poiId` , `questId` );

ALTER TABLE `quest_poi_points` DROP INDEX `idx`,

ADD INDEX `questId` ( `poiId` , `questId` ),

ADD INDEX `poiId` ( `poiId` , `questId` );

This is really not nice. Why have some duplicate KEY in the table creations, but in the SQL updates you add duplicates INDEX? This is not consistent, nor is it correct.

From the data provided, we see that one quest can have many poiId's, but never the same poiId for the same quest. This mean it should be primary key (questId, poiId), this should prevent any mistakes.

For the other fields, i see mostly fields with not needed large possible values. INT(11) when the known values are not even close to max uint8 (255) Database does not need more than TINYINT(3).

To correct all the issues with the SQL as suggested, i propose something like:

TRUNCATE quest_poi;
TRUNCATE quest_poi_points;

ALTER TABLE quest_poi ADD COLUMN poiId tinyint(3) UNSIGNED DEFAULT '0' NOT NULL AFTER questid;
ALTER TABLE quest_poi CHANGE COLUMN questid questId mediumint(8) UNSIGNED DEFAULT '0' NOT NULL;
ALTER TABLE quest_poi CHANGE COLUMN unk1 mapAreaId mediumint(8) UNSIGNED DEFAULT '0' NOT NULL;
ALTER TABLE quest_poi CHANGE COLUMN unk2 floorId tinyint(3) UNSIGNED DEFAULT '0' NOT NULL;

ALTER TABLE quest_poi_points ADD COLUMN poiId tinyint(3) UNSIGNED DEFAULT '0' NOT NULL AFTER questId;
ALTER TABLE quest_poi_points CHANGE COLUMN questId questId mediumint(8) UNSIGNED DEFAULT '0' NOT NULL;
ALTER TABLE quest_poi_points DROP COLUMN objIndex;

ALTER TABLE quest_poi DROP PRIMARY KEY,
 ADD PRIMARY KEY idx_poi (questId, poiId);

ALTER TABLE quest_poi_points DROP INDEX idx,
 ADD KEY idx_poip (questId, poiId);

While making the PRIMARY KEY i notice that it will become many duplicates, since one of the fields used are added as 0 (if table contain any data from before). This is the reason tables might as well be truncated, as the option will be to drop all duplicates before adding the primary key, leaving the tables pretty much useless.

I assume we all want this in master though, so to me it's a far better option to just drop all existing data and apply it again.

Edit: and i also changed from the original code: unk1 -> mapAreaId, this is to avoid mixing up the field with what we usually know as areaId from AreaTable.dbc. It's really the id from WorldMapArea.

[javilico]

It works perfect. Thanks man

[xeross155]

Congratulations on making it into the master branch

[MasterTomas]

Congratulation.

Data for new DB stracture http://loveyou.techxgames.com/downloads/Patches/%5BFix%5DActual%20data%20for%20quest_poi%20and%20quest_poi_points.rar

[Darkhunter]

can somebody make it for rev 9608 if I patch for 9620 it wrote

fatal: corrupt patch at line 125

it is end where is } please help me with it

[xeross155]

Why do you need a patch it has been added to MaNGOS now.

[Darkhunter]

yes, but I want not patch again other patches like vehicles and other... It took me about a week to fix that patchfile to patch to my mangos repo correctly...

[Homenixx]

http://github.com/gc/mangos/commit/a9c7a81ceb501cf94464e6bd3ffb023e9e796c0b

Usefull??

Author: Feanordev.

[xeross155]

Don't know. Blizzy used packet logged data afaik, perhaps the DBC contains the same data.

[nyko]

splendide ! j'adore quand Mangos intègre de tels patchs à la branche principale

cette fonctionnalité est plus qu'intéressante, je dirais même nécessaire.

Toutes mes félicitations à Blizzy et à l'équipe Mangos.

[thortue]

Thanks a lot for this fix,

I suggested this note, if it could help a little to keep your quest_poi datas

Take Care.

Thorazi.