SQL Question

[dcl]

Being somewhat of an SQL newbie (gross understatement)

How would I backup and later restore a single zone?

Any help is appreciated.

[Bigpull]

Extracting a single zone from the database isn't an easy process todo automagicly, Perhaps one of the designers for sharval or PoFire could shed some insight. In short you need to do a rather complex set of "select into" to either a file or a "temp" database.

[Lurker_005]

hmm, I think I might see what I can do with this tonight... Well extracting the data. Putting it back in without overwrighting existing stuff, now that will be a chore.

I just took a few minutes to look at what tables were used by the zones... not suprising about half the DB. Most are populated with stuff from the empty DB, but really should be backed up regardless. And I came up with 9 pieces of data that tie all those tables together.

Well nuff blabbering. gonna go see what I can make of this.

[Lurker_005]

Ok, this isn't entirely that usefull yet, but it allows making a copy of the data used for a given zone to a temporary database. I will try to make a script or VB app to do a lot more, and eventually re-import the data so that it can be mixed and matched with other zones.

anyhow, the mysql commands, be sure to replace database and zone_shortname (first and 4th lines)

use database;
drop database lurk_temp;
create database lurk_temp;

create table lurk_temp.spawn2 select * from spawn2 where zone = 'zone_shortname';
create table lurk_temp.spawnentry select spawnentry.* from spawnentry, lurk_temp.spawn2 where spawnentry.spawngroupid = lurk_temp.spawn2.spawngroupid;
create table lurk_temp.spawngroup select spawngroup.* from spawngroup, lurk_temp.spawn2 where spawngroup.id = lurk_temp.spawn2.spawngroupid;
create table lurk_temp.npc_types select npc_types.* from npc_types, lurk_temp.spawnentry where npc_types.id = lurk_temp.spawnentry.npcid;

create table lurk_temp.grid select grid.* from grid, lurk_temp.spawn2 where grid.id = lurk_temp.spawn2.pathgrid;
create table lurk_temp.city_defense select city_defense.* from city_defense, lurk_temp.npc_types where city_defense.npcid = lurk_temp.npc_types.id;
create table lurk_temp.merchantlist select merchantlist.* from merchantlist, lurk_temp.npc_types where merchantlist.merchantid = lurk_temp.npc_types.merchant_id;

create table lurk_temp.npc_faction_entries select npc_faction_entries.* from npc_faction_entries, lurk_temp.npc_types where npc_faction_entries.npc_faction_id = npc_types.npc_faction_id;
create table lurk_temp.npc_faction select npc_faction.* from npc_faction, lurk_temp.npc_faction_entries where npc_faction.id = npc_faction_entries.faction_id;
create table lurk_temp.faction_list select faction_list.* from faction_list, lurk_temp.npc_faction where faction_list.id = lurk_temp.npc_faction.faction_id;

create table lurk_temp.loottable select loottable.* from loottable, lurk_temp.npc_types where loottable.id = lurk_temp.npc_types.loottable_id;
create table lurk_temp.loottable_entries select loottable_entries.* from loottable_entries, lurk_temp.loottable where loottable_entries.loottable_id = lurk_temp.loottable.id;
create table lurk_temp.lootdrop select lootdrop.* from lootdrop, lurk_temp.loottable_entries where lootdrop.id = lurk_temp.loottable_entries.lootdrop_id;
create table lurk_temp.lootdrop_entries select lootdrop_entries.* from lootdrop_entries, lurk_temp.loottable_entries where lootdrop_entries.lootdrop_id = lurk_temp.loottable_entries.lootdrop_id;

create table lurk_temp.doors select doors.* from doors, lurk_temp.spawn2 where doors.zone = lurk_temp.spawn2.zone;
create table lurk_temp.zone select zone.* from zone, lurk_temp.spawn2 where zone.short_name = lurk_temp.spawn2.zone;
create table lurk_temp.zone_points select zone_points.* from zone_points, lurk_temp.spawn2 where zone_points.zone = lurk_temp.spawn2.zone;
create table lurk_temp.zonepoints_raw select zonepoints_raw.* from zonepoints_raw, lurk_temp.spawn2 where zonepoints_raw.zone = lurk_temp.spawn2.zone;
create table lurk_temp.forage select forage.* from forage, lurk_temp.zone where forage.zoneid = lurk_temp.zone.zoneidnumber;


Then from the dos command line:
c:\mysql\bin\mysqldump.exe lurk_temp > zone_shortname.sql
to save the results to a file.[/b]


***EDIT: was using old faction tables, updated for 4.4 DB structure***

[dcl]

Excellent... thank you.