PDA

View Full Version : ImportAllZoneDataFromCavedude Stored Procedure


WildcardX
11-06-2006, 03:47 PM
I created this stored procedure to import zone data from Cavedude's last database into the database Angelox has been working on, based on the last PEQ database release. With a little editing, you can easily adapt this simple, but powerful stored procedure to work with just about any database.

When I wrote this, I made a couple of working assumptions:

1. NPC_TYPES ID column is always a six digit number and the first 3 digits are always the ZONEIDNUMBER from the ZONE table.

2. All databases have common schema.

3. All databases are MySQL 5.0 databases. (MySQL 4.1 or worst does not support stored procedures and a laundry list of other modern capabilities)

As of right now, these assumptions will work but may easily break in the future.

NOTE: I have tested this stored procedure only twice now since I wrote it a couple hours ago. You should backup all your databases before you use this stored procedure.

Oh almost forgot... This will import almost everything to support a specified zone except for the following and only because I decided to not spend the extra hour or two to code because of time constraints this evening and because, imo, this data needs more work anyway and I didn't want to import it and possibly conflict with the work Angelox is doing:

1. NPC Factions
2. Spell Entries
3. Loot Tables

Chances are, in the case of importing zone data from Cavedude into Angelox, a large percentage of these IDs will be correct anyway, but your milage may vary.

Enjoy!


DELIMITER $$

DROP PROCEDURE IF EXISTS `ax_peq`.`ImportAllZoneDataFromCavedude` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ImportAllZoneDataFromCavedude`(IN ZONENAME VARCHAR(16))
BEGIN
DECLARE SOURCEZONEID INT;
DECLARE ZONEID INT;
DECLARE NPCSTARTID INT;
DECLARE NPCENDID INT;
DECLARE TMPNPCSTART VARCHAR(6);
DECLARE TMPNPCEND VARCHAR(6);
DECLARE GRIDCOUNT INT;
DECLARE GRIDENTRYCOUNT INT;

SET ZONEID = (SELECT zoneidnumber FROM zone WHERE short_name = ZONENAME);
SET SOURCEZONEID = (SELECT zoneidnumber FROM cavedude.zone WHERE short_name = ZONENAME);

IF ZONEID = SOURCEZONEID THEN
SET TMPNPCSTART = CAST(ZONEID AS CHAR(3)) + '000';
SET TMPNPCEND = CAST(ZONEID AS CHAR(3)) + '999';
SET NPCSTARTID = CAST(TMPNPCSTART AS UNSIGNED);
SET NPCENDID = CAST(TMPNPCEND AS UNSIGNED);

DELETE FROM npc_types where id between NPCSTARTID and NPCENDID;
INSERT INTO npc_types SELECT n.id, n.name, n.lastname, n.level, n.race, n.class, n.bodytype, n.hp, n.gender, n.texture, n.helmtexture, n.size, n.hp_regen_rate, n.mana_regen_rate, n.loottable_id, n.merchant_id, n.npc_spells_id, n.npc_faction_id, n.mindmg, n.maxdmg, n.npcspecialattks, 0, n.aggroradius, 0, n.face, n.luclin_hairstyle, n.luclin_haircolor, n.luclin_eyecolor, n.luclin_eyecolor2, n.luclin_beardcolor, n.luclin_beard, 0, n.d_meele_texture1, n.d_meele_texture2, n.runspeed, n.MR, n.CR, n.DR, n.FR, n.PR, 0, n.see_invis, n.see_invis_undead, n.qglobal, n.AC, n.npc_aggro, n.spawn_limit, n.attack_speed, n.findable, n.STR, n.STA, n.DEX, n.AGI, n._INT, n.WIS, n.CHA FROM cavedude.npc_types n WHERE n.id between NPCSTARTID and NPCENDID;

delete from spawnentry where spawngroupID in (select distinct S.spawngroupID from spawn2 as S where S.zone = ZONENAME);
insert into spawnentry (spawngroupID, npcID, chance) select distinct SE.spawngroupID, SE.npcID, SE.chance from cavedude.spawnentry as SE inner join cavedude.spawn2 as S on SE.spawngroupID = S.spawngroupID where S.zone = ZONENAME;

delete from spawngroup where id in (select distinct S.spawngroupID from spawn2 as S where S.zone = ZONENAME);
insert into spawngroup (id, name) select distinct SG.id, SG.name from cavedude.spawngroup as SG inner join cavedude.spawn2 as S on SG.id = S.spawngroupID where S.zone = ZONENAME;

delete from spawn2 where zone = ZONENAME;
insert into spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime, variance, pathgrid, timeleft, _condition, cond_value) select distinct id, spawngroupID, zone, x, y, z, heading, respawntime, variance, pathgrid, timeleft, _condition, cond_value from cavedude.spawn2 where zone = ZONENAME;

SET GRIDCOUNT = (SELECT COUNT(*) FROM cavedude.grid_entries WHERE zoneid = ZONEID);
SET GRIDENTRYCOUNT = (SELECT COUNT(*) FROM cavedude.grid where zoneid = ZONEID);

IF GRIDENTRYCOUNT > 0 THEN
delete from grid_entries where zoneid = ZONEID;
insert into grid_entries (gridid, zoneid, number, x, y, z, heading, pause) select distinct GE.gridid, GE.zoneid, GE.number, GE.x, GE.y, GE.z, GE.heading, GE.pause from cavedude.grid_entries as GE where GE.zoneid = ZONEID;

IF GRIDCOUNT > 0 THEN
delete from grid where zoneid = ZONEID;
insert into grid (id, zoneid, type, type2) select distinct G.id, G.zoneid, G.type, G.type2 from cavedude.grid as G inner join cavedude.grid_entries as GE on G.id = GE.gridid where GE.zoneid = ZONEID;
END IF;
END IF;

DELETE FROM doors where zone = ZONENAME;
INSERT INTO doors (doorid, zone, name, pos_y, pos_x, pos_z, heading, opentype, guild, lockpick, keyitem, triggerdoor, triggertype, doorisopen, door_param, dest_zone, dest_x, dest_y, dest_z, dest_heading, invert_state, incline, size) SELECT d.doorid, d.zone, d.name, d.pos_y, d.pos_x, d.pos_z, d.heading, d.opentype, d.guild, d.lockpick, d.keyitem, d.triggerdoor, d.triggertype, d.doorisopen, d.door_param, d.dest_zone, d.dest_x, d.dest_y, d.dest_z, d.dest_heading, d.invert_state, d.incline, d.size FROM cavedude.doors d WHERE d.zone = ZONENAME;

DELETE FROM fishing where zoneid = ZONEID;
INSERT INTO fishing (zoneid, Itemid, skill_level, chance, npc_id, npc_chance) SELECT f.zoneid, f.Itemid, f.skill_level, f.chance, f.npc_id, f.npc_chance FROM cavedude.fishing f where f.zoneid = ZONEID;

DELETE FROM forage where zoneid = ZONEID;
INSERT INTO forage (zoneid, Itemid, level, chance) SELECT f.zoneid, f.Itemid, f.level, f.chance FROM cavedude.forage f where f.zoneid = ZONEID;

DELETE FROM ground_spawns where zoneid = ZONEID;
INSERT INTO ground_spawns (zoneid, max_x, max_y, max_z, min_x, min_y, heading, name, item, max_allowed, comment, respawn_timer) SELECT g.zoneid, g.max_x, g.max_y, g.max_z, g.min_x, g.min_y, g.heading, g.name, g.item, g.max_allowed, g.comment, g.respawn_timer from cavedude.ground_spawns g where g.zoneid = ZONEID;

DELETE FROM object where zoneid = ZONEID;
INSERT INTO object (zoneid, xpos, ypos, zpos, heading, itemid, charges, objectname, type, icon, linked_list_addr_01, linked_list_addr_02, unknown08, unknown10, unknown20, unknown24, unknown60, unknown64, unknown68, unknown72, unknown76, unknown84, unknown88) SELECT o.zoneid, o.xpos, o.ypos, o.zpos, o.heading, o.itemid, o.charges, o.objectname, o.type, o.icon, o.linked_list_addr_01, o.linked_list_addr_02, o.unknown08, o.unknown10, o.unknown20, o.unknown24, o.unknown60, o.unknown64, o.unknown68, o.unknown72, o.unknown76, o.unknown84, o.unknown88 FROM cavedude.object o WHERE o.zoneid = ZONEID;

DELETE FROM object_contents where zoneid = ZONEID;
INSERT INTO object_contents () SELECT o.zoneid, o.parentid, o.bagidx, o.itemid, o.charges, o.droptime, o.augslot1, o.augslot2, o.augslot3, o.augslot4, o.augslot5 FROM cavedude.object_contents o WHERE o.zoneid = ZONEID;

DELETE FROM traps WHERE zone = ZONENAME;
INSERT INTO traps (zone, x, y, z, chance, maxzdiff, radius, effect, effectvalue, effectvalue2, message, skill, spawnchance) SELECT t.zone, t.x, t.y, t.z, t.chance, t.maxzdiff, t.radius, t.effect, t.effectvalue, t.effectvalue2, t.message, t.skill, t.spawnchance FROM cavedude.traps t WHERE t.zone = ZONENAME;
END IF;

END $$

DELIMITER ;