ArcEmu: Some Cleanup Queries - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Hasbro  : (06 April 2011 - 07:55 PM) We will not tolerate disrespect on our forums. If you believe a member of the staff or a member of the community is abusing our forums, please report their post.
Resize Shouts Area

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Some Cleanup Queries

#1 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,179
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Location:Budapest, Hungary
  • Server OS:Linux

Posted 23 October 2010 - 10:24 PM

So I git tired of having over 9000 warnings during startup because my DB is old (NCDB r101), and put them into a box, then put the box into a bigger box, and threw it into the ocean with some simple SQL queries I wrote up.

First and most importantly it's necessary to import the `spellids` table that I provided here in this post as a download:
Attached File  spellids.zip (113.98K)
Number of downloads: 21

This tabled contains all the spellids for patch 3.3.5a, maybe when we progress with patches I will update it with the latest.

Then you can run these:
DELETE FROM `ai_agents` WHERE `spell` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `ai_threattospellid` WHERE `spell` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `playercreateinfo_spells` WHERE `spellid` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `spell_coef_override` WHERE `id` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `spell_proc` WHERE `spellID` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `spellfixes` WHERE `spellId` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `spelloverride` WHERE `overrideID` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `spelloverride` WHERE `spellId` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `SpellTargetConstraints` WHERE `SpellID` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `totemspells` WHERE `spell` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `totemspells` WHERE `castspell1` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `totemspells` WHERE `castspell2` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `totemspells` WHERE `castspell3` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `trainer_spells` WHERE `cast_spell` NOT IN ( SELECT `Id` FROM `spellids` );
DELETE FROM `trainer_spells` WHERE `learn_spell` NOT IN ( SELECT `Id` FROM `spellids` );



Enjoy
"The demand for free goods is infinite."

Check out my blog and feel free to follow me if you like!
1

#2 User is offline   Marforius 

  • Permanantly on leave
  • PipPipPipPip
  • Group: Members
  • Posts: 597
  • Joined: 07-August 08
  • IRC:Marforius
  • Gender:Male
  • Location:Rome
  • Interests:ArcEmulatorius
  • Server OS:Linux

Posted 24 October 2010 - 06:24 AM

nice
0

#3 User is offline   sip 

  • Member
  • Pip
  • Group: Members
  • Posts: 90
  • Joined: 30-December 09

Posted 25 October 2010 - 12:40 PM

Bump bump:) Nice nice Work
0

#4 User is offline   iEzri 

  • < Ace of spades >
  • Group: Contributor
  • Posts: 1,692
  • Joined: 22-December 08
  • Gender:Female
  • Interests:I'm likely to cause mischief
  • Server OS:Linux

Posted 26 October 2010 - 06:33 AM

View Postsip, on 25 October 2010 - 12:40 PM, said:

Bump bump:) Nice nice Work


we are all as excited as you, but please don't bump.
Posted Image I do not join. I lead.
0

#5 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,179
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Location:Budapest, Hungary
  • Server OS:Linux

Posted 26 October 2010 - 10:44 PM

View PostiEzri, on 26 October 2010 - 06:33 AM, said:

we are all as excited as you, but please don't bump.

Are you jealous because he has bigger bumpers than you? :)
"The demand for free goods is infinite."

Check out my blog and feel free to follow me if you like!
1

#6 User is offline   iEzri 

  • < Ace of spades >
  • Group: Contributor
  • Posts: 1,692
  • Joined: 22-December 08
  • Gender:Female
  • Interests:I'm likely to cause mischief
  • Server OS:Linux

Posted 27 October 2010 - 07:04 AM

View Postdfighter, on 26 October 2010 - 10:44 PM, said:

Are you jealous because he has bigger bumpers than you? :(


you wanna push them?

/makes pussycat-doll-move

:)
Posted Image I do not join. I lead.
1

#7 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,179
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Location:Budapest, Hungary
  • Server OS:Linux

Posted 30 October 2010 - 06:47 PM

View PostiEzri, on 27 October 2010 - 07:04 AM, said:

you wanna push them?

/makes pussycat-doll-move

:lol:

iEzri is multitouch, huh?
Making that move has just made you #6 prostitute in Kazakhstan! :P

EDIT:

Some more cleanup:

DELETE FROM `creature_proto`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `creature_quest_starter`
WHERE `id` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `creature_quest_finisher`
WHERE `id` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `ai_agents`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `battlemasters`
WHERE `creature_entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `loot_creatures`
WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `loot_skinning`
WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `loot_pickpocketing`
WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `npc_gossip_textid`
WHERE `creatureid` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `npc_monstersay`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `reputation_creature_onkill`
WHERE `creature_id` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `trainer_defs`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `trainer_spells`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `vendor_restrictions`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `vendors`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `zoneguards`
WHERE `horde_entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `zoneguards`
WHERE `alliance_entry` NOT IN ( SELECT `entry` FROM `creature_names` );


DELETE FROM `creature_spawns`
WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );

DELETE FROM `creature_formations`
WHERE `spawn_id` NOT IN ( SELECT `id` FROM `creature_spawns` );

DELETE FROM `creature_waypoints`
WHERE `spawnid` NOT IN ( SELECT `id` FROM `creature_spawns` );

DELETE FROM `creature_timed_emotes`
WHERE `spawnid` NOT IN ( SELECT `id` FROM `creature_spawns` );



EDIT:

and some more cleanup

DELETE FROM `gameobject_quest_starter`
WHERE `id` NOT IN ( SELECT `entry` FROM `gameobject_names` );

DELETE FROM `gameobject_quest_finisher`
WHERE `id` NOT IN ( SELECT `entry` FROM `gameobject_names` );

DELETE FROM `gameobject_quest_item_binding`
WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );

DELETE FROM `gameobject_quest_pickup_binding`
WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );

DELETE FROM `gameobject_teleports`
WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );

DELETE FROM `gameobject_spawns`
WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );

DELETE FROM `loot_gameobjects`
WHERE `entryid` NOT IN ( SELECT `entry` FROM `gameobject_names` );


Going to commit these.
"The demand for free goods is infinite."

Check out my blog and feel free to follow me if you like!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users