Tool: Migrate NPC's (ID, Spawn, Loot, etc). from Lucin DB to Current EQ

[zephyr325]

This is a tool I wrote that will allow you take just about everything about an NPC from the current PEQ (Lucin RC1) database and move it into the current EQ database. Basically, you give it an NPC number and it parse through the Lucin DB to get that NPC's:

- Core character information;
- Spells
- Spawning
- Loot
- Movement

The program will then create a file of insert statements that you can source into your existing eq database.

Prerequisites:
- FIRST: THIS IS TEST CODE. BACKUP YOUR EXISTING EQ DATABASE.
- This is a perl program; you need perl installed.
- You will need to source the current PEQ Lucin RC1 database into a separate database on your server; that is what this program will read.

Installing it:
- Make sure you

[zephyr325]

Hold off on this - I've had a couple of people point some things out; I'll patch and re-release.

[Zengez]

possibly a silly question but... does it work in reverse? (i.e. take mob info from eq DB and insert it into PEQ db?)

[Angelox]

possibly a silly question but... does it work in reverse? (i.e. take mob info from eq DB and insert it into PEQ db?)

I don't see why not , just so long as the Databases you use , have all the updates to what ever version EQEMU he's using.

[zephyr325]

Found out my key problem is that the records you pull from the PEQ Lucin database sometimes steps on key fields in the exising Cavedude's database, so you can't just take one and move it over. I'm trying to work through how to do that key field adjustment in a systematic fashion.

[mattmeck]

moved to correct forum

[Zengez]

I just recently successfully added all the spawns from cavedude's database into PEQ's.... i took all teh differing id's multiplied all the unique id's by 1000 (except spawngroupid which i did 100 for lack of character space) and pulled them over, no overlaps, no problems sourced in perfect first try.

[zephyr325]

Sent you a couple of PM's about that - shoot me a note in email or I'm zephyr325@hotmail.com on msn chat.

[zephyr325]

I've put the tool back up at the link above. It is working fine.

The issue this script doesn't handle is sourcing records from PEQ to EQ (Cavedude) where there is a key field already in use. It assumes those records will work correctly based off of the id's that are in PEQ. It looks like a lot of the later zone's NPC's will work fine as there isn't a crosseover between those two. It can also be useful if you just want to pull specific items from one to the other format as there are a few slight differences in the npc_types table between the two databases.

I'm still going to work on a programmatic way to adjust the key field's ids, hopefully in a way that will allow future PEQ updates past lucin to come in cleanly. Its also going to handle some of the other differences (i.e., spell tables are different, etc.).

[zephyr325]

Updated to version 0.2.2 - insert statements now have the field id names in them, so you can pull from peq and insert into cavedude, or vice versa.

Still working on the changing key field id's.

[sdabbs65]

Updated to version 0.2.2 - insert statements now have the field id names in them, so you can pull from peq and insert into cavedude, or vice versa.

Still working on the changing key field id's.

Don't know if you know or not but your passwords are in there.
it works great good job.

[zephyr325]

Hmmm...doesn't now, might have been out there for a second. I'll change then post in the future. =)

[sdabbs65]

If it was possable to add a wildcard feature such as npc's 1 thu 10000 it would make it a lot easier,theres about 3k in npc's in my old database.

[zephyr325]

I'm working on that...

But in the interim, I wrote another script that will compare the differences between two databases (it looks at one field in one table in both of them) and prints out the differences. Pipe that to a file, then run a shell/batch script that will call this script for each of those numbers.

That comparison script is posted here:

http://www.eqemulator.net/forums/showthread.php?t=21258

[GeorgeS]

Thanks for writing this, as it's a tool we've been wanting!

GeorgeS

[zephyr325]

No probs - I'm a few days away from releasing the next version of this. I've done a pretty detailed review of the Cavedude/PEQ databases to figure out in all the fields where I can safely renumber key fields. The next version or two is going to include:

- Automatic key re-numbering
- The ability to do batches of people at a time (maybe something like zones, maybe everyone)
- Generating the scripts that will reverse the adds. For example, if something doesn't work, remove what you just added in.

[John Adams]

Most excellent work, Zephyr. I think the renumbering project was going to be enormous, and I know how to do it, just not sure what the accepted standard really is (besides simply zoneid * 1000 heh). Since loots and merchant sets could belong to NPCs in multiple zones, it mades my brains ache.

I can't wait to try this out.

[soulshot]

Loot id's and merchant id's should not change really the two databases both share the same structure for items and shared item numbers.

[John Adams]

True, so perhaps the only things really needing a re-indexing is things that are specific to a zone? Like an NPC, or it's spawn* info (both of which I think are mostly ok), or grids?

Anything else?

[Zengez]

Merchant ID's should be the more or less the same i'd think, however loot ID's will be completely different. Both DBs (from teh little i looked into it when merging them) did a serialized numbering that i believe overlaps since cavedude had fewer spawns and thus his 1000th loot id might be somewhere in omens of war, which PEQ did all the creatures puting their 1000th loot id somewhere in kunark or something (not litteral, just to give an idea) So if you truely want to merge these two and make it pretty, then you'll probably have to re-do the loot id's entirely for one or the other (I'd assume cavedude's) I don't readily see any other way to go about it...

[zephyr325]

One of the things I did as a precursor to starting to rewrite this program was to review each of the tables that we would need to touch to come up with what the existing ranges are for all of the key fields (there are sometimes more than just the ID field) to find a "safe" range.

There are several cases of where the databases are the same up to a point, but then they diverge. In cases where there is any discrepancy, I'm taking the safe route to come up with a range of id's (for all the key fields) that new records could be imported with. This will be true if you're going cavedude -> PEQ or PEQ -> cavedude. It looks like there is going to be at least one case where we're going to have to alter the database structure (by expanding a field) as the existing ranges are already using all the available space.

Part of what I'll publish when I get the program running is that analysis. That way if I missed something, I'll have lots of folks helping me troubleshoot it. =)

[zephyr325]

Tool is now written - we're beta testing it on Dark Tides right now. As long as things look good, I'll get the update out in the next couple of days.

It's gonna be a biggie. =)

Here's a precursor of what it does:

http://www.verycarr.com/apps/scripts/keyfieldchanges.html

[John Adams]

That looks very well thought out. I can't wait to give it a whirl.

One thing, what does "add 500,000" mean on Items? Does that mean your IDs will start with 500,000 and go up from there? I ask because I ran into a problem when I tried to make a custom item with am id > 100,000, and believe I tracked it to this:

Items.h in EmuShareMem

#define MMF_EQMAX_ITEMS 100000
// MMF_MEMMAX_ITEMS: Maxium number of items to load into memory. Make sure this is bigger
// than the total number of items in the server's database!



Of course, I am unfamiliar with this code, so I could be wrong. Will this effect you?

[zephyr325]

Yes, you're correct with what I'm adding to the item ID's. I haven't found a problem yet with range I'm using. Just to be sure, I've made a section that allows you to easily set what you want to increment each key field by.

I'm working on posting the code for the program now.

[zephyr325]

This thread is now superseded by the one here