| Huppy |
08-16-2021 10:04 AM |
Project - NPC Wipe
I created (what I call) a "template database" for the start of a long, journey of redoing every zone on the server with custom NPC spawns, quests, etc. I have totally wiped any and all entries in the spawn tables, loot tables, grid entries, merchant lists, etc. May take 12-18 months to make progress on it, but i am in no rush. Just going to pick away at it, when I need a break from other things. :)
I put together an sql query, if anyone else is interested in doing this, but BE FOREWARNED - BACK UP YOUR DATABASE FIRST !!! This query will leave your server with NO NPC's - SERVER WIDE. The NPC's are still in the database, but all spawn tables/entries will be gone.
Cautionary note - This is based on work with the current db version 9167. In the future, as the PEQ team and Devs do updates, in some cases, it could render this query troublesome for your database. Added/deleted/altered tables and columns are common in many updates.
Copy and paste this into a query and run it.
Code:
/* WARNING - This will delete ALL entries related to NPC spawns, grids, loot tables - SERVER WIDE */
DROP TABLE IF EXISTS `grid_entries`;
CREATE TABLE `grid_entries` (
`gridid` int(10) NOT NULL DEFAULT 0,
`zoneid` int(10) NOT NULL DEFAULT 0,
`number` int(10) NOT NULL DEFAULT 0,
`x` float NOT NULL DEFAULT 0,
`y` float NOT NULL DEFAULT 0,
`z` float NOT NULL DEFAULT 0,
`heading` float NOT NULL DEFAULT 0,
`pause` int(10) NOT NULL DEFAULT 0,
`centerpoint` tinyint(4) NOT NULL DEFAULT 0,
PRIMARY KEY (`zoneid`, `gridid`, `number`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `grid`;
CREATE TABLE `grid` (
`id` int(10) NOT NULL DEFAULT 0,
`zoneid` int(10) NOT NULL DEFAULT 0,
`type` int(10) NOT NULL DEFAULT 0,
`type2` int(10) NOT NULL DEFAULT 0,
PRIMARY KEY (`zoneid`, `id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `lootdrop`;
CREATE TABLE `lootdrop` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 178218 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `lootdrop_entries`;
CREATE TABLE `lootdrop_entries` (
`lootdrop_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
`item_id` int(11) NOT NULL DEFAULT 0,
`item_charges` smallint(2) UNSIGNED NOT NULL DEFAULT 1,
`equip_item` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
`chance` float NOT NULL DEFAULT 1,
`disabled_chance` float NOT NULL DEFAULT 0,
`trivial_min_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
`trivial_max_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
`multiplier` tinyint(2) UNSIGNED NOT NULL DEFAULT 1,
`npc_min_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
`npc_max_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`lootdrop_id`, `item_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `loottable`;
CREATE TABLE `loottable` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`mincash` int(11) UNSIGNED NOT NULL DEFAULT 0,
`maxcash` int(11) UNSIGNED NOT NULL DEFAULT 0,
`avgcoin` int(10) UNSIGNED NOT NULL DEFAULT 0,
`done` tinyint(3) NOT NULL DEFAULT 0,
`min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 110705 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `loottable`;
CREATE TABLE `loottable` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`mincash` int(11) UNSIGNED NOT NULL DEFAULT 0,
`maxcash` int(11) UNSIGNED NOT NULL DEFAULT 0,
`avgcoin` int(10) UNSIGNED NOT NULL DEFAULT 0,
`done` tinyint(3) NOT NULL DEFAULT 0,
`min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 110705 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `loottable_entries`;
CREATE TABLE `loottable_entries` (
`loottable_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
`lootdrop_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
`multiplier` tinyint(2) UNSIGNED NOT NULL DEFAULT 1,
`droplimit` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
`mindrop` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
`probability` float NOT NULL DEFAULT 100,
PRIMARY KEY (`loottable_id`, `lootdrop_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `merchantlist`;
CREATE TABLE `merchantlist` (
`merchantid` int(11) NOT NULL DEFAULT 0,
`slot` int(11) NOT NULL DEFAULT 0,
`item` int(11) NOT NULL DEFAULT 0,
`faction_required` smallint(6) NOT NULL DEFAULT -100,
`level_required` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`alt_currency_cost` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
`classes_required` int(11) NOT NULL DEFAULT 65535,
`probability` int(3) NOT NULL DEFAULT 100,
`min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (`merchantid`, `slot`) USING BTREE,
UNIQUE INDEX `merchantid`(`merchantid`, `item`) USING BTREE,
INDEX `item`(`item`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `merchantlist_temp`;
CREATE TABLE `merchantlist_temp` (
`npcid` int(10) UNSIGNED NOT NULL DEFAULT 0,
`slot` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`itemid` int(10) UNSIGNED NOT NULL DEFAULT 0,
`charges` int(10) UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY (`npcid`, `slot`) USING BTREE,
INDEX `npcid_2`(`npcid`, `itemid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `respawn_times`;
CREATE TABLE `respawn_times` (
`id` int(11) NOT NULL DEFAULT 0,
`start` int(11) NOT NULL DEFAULT 0,
`duration` int(11) NOT NULL DEFAULT 0,
`instance_id` smallint(6) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`, `instance_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `spawn_condition_values`;
CREATE TABLE `spawn_condition_values` (
`id` int(10) UNSIGNED NOT NULL,
`value` tinyint(3) UNSIGNED NULL DEFAULT NULL,
`zone` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`instance_id` int(10) UNSIGNED NOT NULL,
UNIQUE INDEX `instance`(`id`, `instance_id`, `zone`) USING BTREE,
INDEX `zoneinstance`(`zone`, `instance_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `spawn_conditions`;
CREATE TABLE `spawn_conditions` (
`zone` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`id` mediumint(8) UNSIGNED NOT NULL DEFAULT 1,
`value` mediumint(9) NOT NULL DEFAULT 0,
`onchange` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`zone`, `id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `spawn_events`;
CREATE TABLE `spawn_events` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`zone` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`cond_id` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`period` int(10) UNSIGNED NOT NULL DEFAULT 0,
`next_minute` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`next_hour` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`next_day` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`next_month` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`next_year` int(10) UNSIGNED NOT NULL DEFAULT 0,
`enabled` tinyint(4) NOT NULL DEFAULT 1,
`action` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`argument` mediumint(9) NOT NULL DEFAULT 0,
`strict` tinyint(4) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 84 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `spawn2`;
CREATE TABLE `spawn2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`spawngroupID` int(11) NOT NULL DEFAULT 0,
`zone` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`version` smallint(5) NOT NULL DEFAULT 0,
`x` float(14, 6) NOT NULL DEFAULT 0.000000,
`y` float(14, 6) NOT NULL DEFAULT 0.000000,
`z` float(14, 6) NOT NULL DEFAULT 0.000000,
`heading` float(14, 6) NOT NULL DEFAULT 0.000000,
`respawntime` int(11) NOT NULL DEFAULT 0,
`variance` int(11) NOT NULL DEFAULT 0,
`pathgrid` int(10) NOT NULL DEFAULT 0,
`path_when_zone_idle` tinyint(1) NOT NULL DEFAULT 0,
`_condition` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
`cond_value` mediumint(9) NOT NULL DEFAULT 1,
`enabled` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
`animation` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
`content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `ZoneGroup`(`zone`) USING BTREE,
INDEX `spawn2_spawngroupid_idx`(`spawngroupID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 264328 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `spawnentry`;
CREATE TABLE `spawnentry` (
`spawngroupID` int(11) NOT NULL DEFAULT 0,
`npcID` int(11) NOT NULL DEFAULT 0,
`chance` smallint(4) NOT NULL DEFAULT 0,
`condition_value_filter` mediumint(9) NOT NULL DEFAULT 1,
PRIMARY KEY (`spawngroupID`, `npcID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `spawngroup`;
CREATE TABLE `spawngroup` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`spawn_limit` tinyint(4) NOT NULL DEFAULT 0,
`dist` float NOT NULL DEFAULT 0,
`max_x` float NOT NULL DEFAULT 0,
`min_x` float NOT NULL DEFAULT 0,
`max_y` float NOT NULL DEFAULT 0,
`min_y` float NOT NULL DEFAULT 0,
`delay` int(11) NOT NULL DEFAULT 45000,
`mindelay` int(11) NOT NULL DEFAULT 15000,
`despawn` tinyint(3) NOT NULL DEFAULT 0,
`despawn_timer` int(11) NOT NULL DEFAULT 100,
`wp_spawns` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 287829 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
|