A few scripts to make maintenance a bit easier

[idgarad]

Here are a few batch files i use to help merge different repacks and backup my custom work. I am currently working on a perl script that will generate a complete town from a single .gps location fed to the script (not hard just have lots of work to do. If you want to roll your own, backup your db. generate your town. back up the db again. Get a diff of the sql dumps and note all the spawn locs. Pick 1 object in the center of town. Use that spawn as the base and take all the locations and make them relative to that one spawn. so if the center of town is 50, 50, 50 then a loc of 58,43,50 becomes 8,-7,0. The center of town then is always 0,0,0. Then set up your script to take a location as a parameter and just add the locs to that and output a new sql script. I do that at work all the time but my crap if for navigation systems)

Her are just a few quickies:

First Two Important Lists
Tablelist.txt

account_data
account_forced_permissions
account_overrides
accounts
ai_agents
ai_threattospellid
announcer_txt
areatriggers
arenateams
auctionhouse
auctions
banned_names
banned_phrases
characters
characters_insert_queue
charters
clientaddons
command_overrides
corpses
creature_formations
creature_names
creature_names_localized
creature_proto
creature_quest_finisher
creature_quest_starter
creature_spawns
creature_staticspawns
creature_timed_emotes
creature_waypoints
creatureloot
custom_teleporter
disenchantingloot
fishing
fishingloot
gameobject_names
gameobject_names_localized
gameobject_quest_finisher
gameobject_quest_item_binding
gameobject_quest_pickup_binding
gameobject_quest_starter
gameobject_spawns
gameobject_staticspawns
gm_tickets
graveyards
groups
guild_bankitems
guild_banklogs
guild_banktabs
guild_data
guild_logs
guild_ranks
guilds
instances
ipbans
item_quest_association
item_randomprop_groups
item_randomsuffix_groups
itemloot
itempages
itempages_localized
itempetfood
items
items_extendedcost
items_localized
mailbox
mailbox_insert_queue
map_checkpoint
npc_gossip_textid
npc_monstersay
npc_text
npc_text_localized
objectloot
pet_levelstats
petdefaultspells
pickpocketingloot
player_levelstats
playercooldowns
playercreateinfo
playercreateinfo_bars
playercreateinfo_items
playercreateinfo_skills
playercreateinfo_spells
playeritems
playeritems_insert_queue
playerpets
playerpetspells
playersummonspells
professiondiscoveries
prospectingloot
questlog
quests
quests_localized
recall
reputation_creature_onkill
reputation_faction_onkill
reputation_instance_onkill
server_settings
skinningloot
social_friends
social_ignores
spell_coef_override
spell_disable
spell_disable_trainers
spell_effects_override
spell_proc
spellfixes
spelloverride
teleport_coords
totemspells
trainer_defs
trainer_spells
transport_creatures
transport_data
tutorials
unit_display_sizes
vendors
weather
wordfilter_character_names
wordfilter_chat
worldmap_info
zoneguards

charactersavelist.txt

account_data
account_forced_permissions
account_overrides
accounts
arenateams
auctions
banned_names
banned_phrases
characters
characters_insert_queue
charters
corpses
gm_tickets
groups
guild_bankitems
guild_banklogs
guild_banktabs
guild_checkpoints
guild_data
guild_logs
guild_ranks
guilds
instances
ipbans
mailbox
mailbox_insert_queue
playercooldownitems
playercooldownsecurity
playeritems
playeritems_insert_queue
playerpets
playerpetspells
playersummonspells
questlog
server_settings
social
tutorials

We use those for input in a second.

I use a directory like this
Root
masters (the whoel dbs I keep around including my daily backups)
sorted (sql files that have been sorted and split)
characterbackups (toon specific data)


Script #1: SQL Dump Parser
Purpose: Takes giant sql dumps and splits them up by table
split.bat

@echo off
for /F %%a in (tablelist.txt) do type .\masters\"7-12-08 Dump.sql" | find "INSERT INTO `%%a`" | sort > .\sorted\"SORTED-%%a-solum".sql

Slow but easy. I kick it off before bed or leaving for work. Please note you can change the world solum to reflect which database you are parsing. I have 3 scripts for Mafia, Chrispee, and my main db solum.

Script #2: Save Character Data
Purpose: Extract from a SQL dump, character specific data

@echo off
echo. > .\characterbackups\solumcontegocharacters.sql
for /F %%a in (charactersavelist.txt) do type .\masters\"7-13-08 Dump.sql" | find "INSERT INTO `%%a`" | sort >> .\characterbackups\solumcontegocharacters.sql

It should look familar. Similar to the parser.

Script #3: Remerge
Purpose: Squish em all together

for %%a in (*chrispee.sql) do type %%a >> chrispee-merged.sql

*Please Note* These scripts are for data only, sctructure portions of scripts in the parsing are lost.




Why Parse Monolithic SQL files? Have you ever tried to compare and edit two 120mb files? Intolerable lag time as most live merge\diff tools recompute. I use Winmerge myself and gVim. Small Files = eaiser to manage.

The actual term for this process fyi is called a Data Extract from a flat file. (at least where I work.)

[jay08]

+Rep for the work gone into this.

[900number]

thx for this, like it