X-fer spawn data from 1 Db to another

[chimp]

How would i X-fer spawn data from say Plane of knowledge on the EQ databse into the PEQ database? Would it be possible to dump one of the tables from the EQ database into a sql file and source it to the peq database? Or do i simply have to look at al lthe spawn data right down the stats and then x-fer it all over which would work to but be more slow.

[Aonelyn]

im not to good with sql, but i know someone around here can write you a script that will find all enteries for mobs in pok, change their spawnids to something that peq doesnt use, and you can source it in to the database.

[mrea]

I think the spawn ID's and such would clash between the two databases. Not to mention the broken loot tables and other messed up things that would occur.

[chimp]

Originally Posted by Aonelyn

im not to good with sql, but i know someone around here can write you a script that will find all enteries for mobs in pok, change their spawnids to something that peq doesnt use, and you can source it in to the database.

Hey, 1st off love yer signature, cracks me up) Do you know who can write a script to move the spawn ID's from EQ to PEQ database? if so who is it please so i can ask them) I just wanna have pok spawned on the peq database basiclly.

[fathernitwit]

its a rather non-trivial task to do this, but it is doable none the less, and if done right, such a script could be quite valuable to the community.

Basically, such a script would need to look in the spawn tables to build a list of NPCs which need to be moved. From that point, it would load all spawn the relavent data from the database, re-ID all of the NPCs, and then update all of the data loaded to reflect the new NPC IDs. (please note that PEQ numbers all of their NPCs by zoneID such that their IDs will not conflict). Then the script would need to insert all of the data into the new database, re-IDing each row of each table.

Here is a list of tables which would need to be touched:
grid
grid_entries
lootdrop
lootdrop_entries
loottable
loottable_entries
merchantlist
npc_faction
npc_faction_entries
npc_types
spawn2
spawnentry
spawngroup

not to mention all of the other zone-specific data which is not really related to spawns such as doors and world objects.

[GeorgeS]

As FNW mentioned, doable, but pretty complicated. This is something I was going to tackle, as I have a great need for it as well.

I have noticed PEQ being pretty consitent with their database revisions and maintaining NPC id's etc..

Say you create several NPC's and these NPC's are found in spawngroups among other NPC's you don't want to copy, then it get's awfully complicated.

Simply copying NPC's from one database to another and disregarding spawnid's but keeping loottable id's is possible and easier.

GeorgeS

[RangerDown]

For NPC ID's, PEQ has been going with the convention of setting aside 1,000 numbers for each zone. The NPC's for a particular zone will have ID's in the range of
<that zone's zoneid number> * 1000
thru
(<that zone's zoneid number> * 1000 + 999)

ie, north qeynos, zone #2, would have its NPC's in the range of 2000 to 2999.

So, your best bet is to put your NPC's way up higher than (the highest zone number * 1000 + 999) to ensure no conflict. Keep in mind that each expansion adds about 20 new zones so keep it way up there to avoid conflict with future expansions too.

As for other tables that get unique ID's, we pretty much let the database assign them in its usual behavior of giving the next sequence. So you need to look at the highest value that's currently in the database and then make yours start waaaaaaaaaaaaay up higher, high enough that there's little chance a future database release could conflict.

The hardest part is going to be making DAMN SURE you have also made the change in ALL fields of related tables. Example, incrementing loottable ID's also means those same changes must be made in the loottable_id field of loottable_entries, and in the loottable_id field of npc_types. Maybe one day Mysql will feature cascading updates.... one day....

(Keep in mind too that PEQ could redo its numbering convention at any time if they had a compelling need to. Right now we don't have a compelling need to, but my lawyers told me to put this disclaimer up.)