New Tool: Migrate NPC's (spells/faction/loot/spawn/move/items) from PEQ <=> Cavedude

[zephyr325]

This post supersedes the one I posted here.

There are several enhancements to this program over the old one, but here's a full feature list. You can:

- Move NPC's from PEQ -> Cavedude, or Cavedude -> PEQ
- It will grab all pertinent info on the NPC, including spells, faction, spawn, loot tables, loot items and movement.
- You can move a single NPC, a list of NPC ID's, or a whole zone at a time.
- For a zone move, you can tell it to pull just the NPC's in the "From" database that the "To" database doesn't already have. You can also tell it to grab all of them.
- Output can be one file per NPC (i.e., 1001-Guard_Mezzt.sql) or one big file for all NPC's.
- If you tell it to, it will adjust the key fields for all the pertinent tables so the two databases will play nicely together. This should probably be used every time you run the program.
- I've done a lot of checking into what are safe values to increment key field ID's are (information is here (http://www.verycarr.com/apps/scripts/keyfieldchanges.html)). There is a section in the program where you can change these increments if you so desire.


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 both databases into mysql; this program will read from both of them. Make sure to keep them seperate (don't source them into the same database).


Installing it:
- Make sure you

[zephyr325]

(bumping this thread - the old post I had got on top of this when I edited it to point to this new post)

[Angelox]

The stuff I put out is probably only good if you have a new database, or are downloading my whole database.
If you already have a database you are working on and have added stuff, then there's always a chance things will get crossed and mess up your database.
If you want to add my stuff to your custom database - this tool is what you need; just download my database and merge what you want to yours. This is a great tool and long-waited for.
Thank you zephyr325 !!!

[zephyr325]

Actually, you can use it with other databases as well. The key is making sure that you're incrementing your key field ID's by an amount that isn't going to step on something you already have. Once you have those values, there is a section in this program that you can edit (its towards the top of the file, and labeled) that will allow you to put those amounts in, rather than the defaults I put in.

Check out the link above as well for my listing of what I did with each table; that will be a good starting point for folks to use.

The only one I've found so far that has a hard cap on it is npc_faction - make sure you don't increment your id to be more than 50000 or EQEmu won't start up. I've got a safety in the program that won't write that insert statement out if that value is too high.

[zephyr325]

Small updates:

- Changed the -e option to not check at all for duplicate records in the target database

- put double quotes around all field values to keep the insert statement from generating an error if a field value is null

[vales]

Wow, I don't know how I missed it. :p

Thanks for the program, zephyr325!

[zephyr325]

np!

Next revision is going to work on a less verbose option both for screen and output file. Less screen time means the program will run faster, and a smaller output file means a faster load time as well as not chewing up those oh-so-expensive hard drive bytes. =)

[John Adams]

Zephyr, you are truly amazing. Thank you for this effort. It immediately simplifies anyone wanting to work adhoc on their own data and providing it to the whole community.

Gold star for you!

[zephyr325]

I've put a quick little scripts out that will run the program against all the zones I know about and create a "zonename.sql" file of inserts.

You can get to it here (http://ww2.verycarr.com:8888/apps/scripts/FullZonePull).

I run things under linux/cygwin, so if you want to use it in a DOS format you'll need to replace the "./" in front of each line with "perl " so you get a line like:

perl grabnpc.pl -k -z abysmal -o abysmal.sql

Also, if you want to get one big file of inserts, this is how I do it under linux:

for i in *.sql; do cat $i >>biginsertfile; done

If one of you DOS gurus know the translation for that, someone can probably use it. Make sure you don't create your "biginsertfile" to end with ".sql", though, or you'll get into a recursive loop. You get really big files, really fast, doing something like that. =)

[fathernitwit]

to follow on to the previous thread, making items with arbitrarily high IDs like that is really not a good idea... we have a static length array to make item management fast and big gaps in item IDs waste a lot of memory (if it works at all)

[WildcardX]

I can vouch for FNW on this. When I made a database that was a combination of the latest PEQ, Cavedude's latest and Angelox's latest work, I saw my server memory increase by 300% the normal for running just 10 dynamic zones. Atleast, this is what I think is causing it. I'm still trying to prove it, or I was, until I just read FNW's post above...

On the other hand, it sure is nice to have a database that is almost all popped up intil Omens.

[zephyr325]

Thanks for the input, both of 'ya.

The basic premise that I worked off of was trying to find a safe number that I could add to each of the id's that would work for both databases. There was a lot of room for optimization, which I've incorporated below (and updated the web page that shows what I do for each table here (http://www.verycarr.com/apps/scripts/keyfieldchanges.html)):

npc_types.id: increment by 350,000 rather than 500,000
spawngroup.id: increment by 300,000 rather than 3,000,000
spawn2.id: incremebt by 125,000 rather than 500,000
npc_spells.id: increment by 300 rather than 5,000
npc_spells_entries.id: increment by 1,800 rather than 5,000
loottable.id: increment by 13,000 rather than 500,000
lootdrop.id: increment by 50,000 rather than 500,000
faction_list.id: increment by 1,000 rather than 5,000
items.id: increment by 100,000 rather than 500,000

As to the "will it work" thing, I've been doing some pretty extensive testing and its all worked so far. Thanks for the input.

[WildcardX]

I'll give this a try. I am sure it will work, but for performance I really hope decreasing the amount of empty IDs between records will greatly reduce the server's consumption of memory.

[zephyr325]

Fathernitwit - is there some way to tell how much memory would be taken up by empty space between key id's vs. adding a bunch of new spawns/items/loot etc?

[soulshot]

If anyone wants to see the tool results in action check out the darktides server where this is being developed. Now we are going to attempt a PEQ base with cavedude added into it.

[fathernitwit]

the tables in which ID matters (all linear scale):
- items
- loottable
- lootdrop
- npc_faction
- doors

everything else shouldent matter.

[Angelox]

the tables in which ID matters (all linear scale):
- items
- loottable
- lootdrop
- npc_faction
- doors

everything else shouldent matter.

Thanks, this saves me a lot of time- I was optimizing too many

[zephyr325]

Now that I've tweaked the increments to tighten up the id ranges, and we've not had too many bug reports, I'm looking for more features to add (or maybe a new project?). Any requests?

[vales]

Excellent! :D

What about ground spawns? There are quite a few quests and items that do spawn on the floor. Oh, and items. I'm sure people with custom servers and items would like that option. I'm not one of them, but I could imagine the pain it would be to export/import everything every single time.

Um, that's all I can think of, heh.

[John Adams]

npc_types.id: increment by 350,000 rather than 500,000
spawngroup.id: increment by 300,000 rather than 3,000,000
spawn2.id: incremebt by 125,000 rather than 500,000
npc_spells.id: increment by 300 rather than 5,000
npc_spells_entries.id: increment by 1,800 rather than 5,000
loottable.id: increment by 13,000 rather than 500,000
lootdrop.id: increment by 50,000 rather than 500,000
faction_list.id: increment by 1,000 rather than 5,000
items.id: increment by 100,000 rather than 500,000
I'm still not seeing the logic behind not using PEQ's standard ID numbering convention of zoneid * 1000 where applicable. There is no risk of running into their numbering scheme if this combined effort becomes "the new base" standard. I'd prefer my NPCs in zone 202 to start at 202000 - 202999.

Nice work, still. Very helpful scripting.

[John Adams]

Zephyr, one more thing. MySQL 4.1 doesn't seem to be handling this header line very well:

print FILE "-----------------------------------------------------------------------\n";

Likely the fact it doesn't think it's a comment due to no space after the first two "--"s.

I edited my .pl and re-ran the scripts and MySQL can import the file ok. Not sure if this is everyone, or just my version (MySQL - 4.1.20-community-nt).

[zephyr325]

I'm still not seeing the logic behind not using PEQ's standard ID numbering convention of zoneid * 1000 where applicable. There is no risk of running into their numbering scheme if this combined effort becomes "the new base" standard. I'd prefer my NPCs in zone 202 to start at 202000 - 202999.

Nice work, still. Very helpful scripting.
Understood - that would be a nice "next feature"; currently, I'm not trying to look at the npc_id's field at that detail to see what would be the next available ID number. To make it simpler (for me!) I just found a range for each table that would work with both the Cavedude and PEQ databases. We'll see what I can do on that front, as it's already got a "all mobs in this zone" mode.

[zephyr325]

Zephyr, one more thing. MySQL 4.1 doesn't seem to be handling this header line very well:

print FILE "-----------------------------------------------------------------------\n";

Likely the fact it doesn't think it's a comment due to no space after the first two "--"s.

I edited my .pl and re-ran the scripts and MySQL can import the file ok. Not sure if this is everyone, or just my version (MySQL - 4.1.20-community-nt).
Whoops - thanks for the catch.

I've patched the program and re-posted it.

[John Adams]

Excellent. Thank you again for this. This script kicks ass!

[Angelox]

I can vouch for FNW on this. When I made a database that was a combination of the latest PEQ, Cavedude's latest and Angelox's latest work, I saw my server memory increase by 300% the normal for running just 10 dynamic zones. Atleast, this is what I think is causing it. I'm still trying to prove it, or I was, until I just read FNW's post above...

On the other hand, it sure is nice to have a database that is almost all popped up intil Omens.
This has a pretty simple solution, and is how I optimized my database. I did it with "MySql Query Browser" , a desktop calculator, GeorgeS "MySql Query TooL' , and some index cards to take notes on.
After you run zephyr325's tool, you'll have , new "groups" of high rows/numbers - with "MySql Query Browser", you can sort them so you can see all this; you can see where the original set of numbers end, and the new sets (you made with the tool) start and end.
So, for example, lets say lootdrop originally has ids of 1 -10, and the tool made 10 more rows 20-30. so now you have rows 1-30, the gap being rows 10 -20 so you have to optimize and move the new ten rows to start at 11.
UPDATE lootdrop SET id=id-9 WHERE (id>=20 AND id<=30);

I ran the code in a windows, mysql command shell - I made backup almost every time I ran new lines, because if I got one "beep", that ment it was all ruined, as one of changes were duplicate and I had to start over with a restore.
Here's some examples of what I touched when I fixed my database.

UPDATE lootdrop SET id=id-14878 WHERE (id>=102100 AND id<=102200);
UPDATE lootdrop_entries SET lootdrop_id=lootdrop_id-14878 WHERE (lootdrop_id>=102100 AND lootdrop_id<=102200);
UPDATE loottable SET id=id-14878 WHERE (id>=102100 AND id<=102200);
UPDATE loottable_entries SET lootdrop_id=lootdrop_id-14878 WHERE (lootdrop_id>=102100 AND lootdrop_id<=102200);

and these, I don't think mattered, but since for some reason, high numbers became ugly to me;
UPDATE npc_types SET loottable_id=loottable_id-14878 WHERE (loottable_id>=102100 AND loottable_id<=102200);
UPDATE loottable_entries SET loottable_id=loottable_id-14878 WHERE (loottable_id>=102100 AND loottable_id<=102200);

That was just the finishing touch to a "mass move" of a lot of 8 digit groups I had created.

I haven't used zephyr325's tool yet , but I'm sure the same thing happens to him as what happened to me, when this tool is run.

Also remember, just adding/merging Cavedudes' and PEQ is not the whole solution - it's just a "better start" to what needs to be done.

[Angelox]

I forgot to add the reason for the calculator ;


UPDATE lootdrop SET id=id-9 WHERE (id>=20 AND id<=30);

The highest old number was 10, and the lowest of the new numbers was 20

Highest old number(10) subtract from lowest of new numbers(20) = 10, then subtract one from your new number(10 - 1) to get 9.

You need a calculator for whan the numbers get big and ugly.

[sdabbs65]

FYI,

I tried to update a 0.57 and a 0.6.0 database and this pops up.
Use of uninitialized value in concatenation (.) or string at grabnpc.pl line 740
not a clue on what that means but line 740 is this.

print FILE "\"$Spawn2[$tmp]->{'Condition'}\",";

guessing it has to do with spawn conditions and such.

[zephyr325]

FYI,

I tried to update a 0.57 and a 0.6.0 database and this pops up.
Use of uninitialized value in concatenation (.) or string at grabnpc.pl line 740
not a clue on what that means but line 740 is this.

print FILE "\"$Spawn2[$tmp]->{'Condition'}\",";

guessing it has to do with spawn conditions and such.
That shouldn't be a critical error, at least from the grabnpc.pl program operation standpoint. That warning means that it is trying to create a variable from the data that it read from your "from" database, but that field didn't exist there. It will just write a blank variable out for that when it creates the insert statements.

A bigger question is whether or not the 0.6.0 version of the program needs that variable for something. I'm not sure about that one, but I'd just source one NPC to see how it acts.

[sdabbs65]

That shouldn't be a critical error, at least from the grabnpc.pl program operation standpoint. That warning means that it is trying to create a variable from the data that it read from your "from" database, but that field didn't exist there. It will just write a blank variable out for that when it creates the insert statements.

A bigger question is whether or not the 0.6.0 version of the program needs that variable for something. I'm not sure about that one, but I'd just source one NPC to see how it acts.

Im actually trying to convert over the old 5.7 S2K database that I got 2 years ago. it seems to be missing something
last year I was able to convert it over to 0.6.0 but I added and deleted a whole slew of fields.
The NPC sourced in but it's not showing up in the spawnlistand if i remember right I had to drop and add quite a bit of fields.
so the conversion from 0.6.0 to 0.7.0 is what im troubleshooting atm.

[sdabbs65]

this is right from the npc file that grabnpc.pl made.
After tinkering with it for a while I noticed this error, maybe it's a parseing error.

mysql> INSERT INTO spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime
, variance, pathgrid, timeleft, _condition, cond_value) VALUES ("350904","281356
","nexus","0.977219","19.124527","-30.247982","139.125000","1200","0","0","0",);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

[sdabbs65]

mysql> INSERT INTO spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime
, variance, pathgrid, timeleft, _condition, cond_value) VALUES ("350904","281356","nexus","0.977219","19.124527","-30.247982","139.125000","1200","0","0","0",);


after adding 2 more "0","0" at the end of the field it resolved the issue
but now im trying to figgure out how to mass produce that fix.

[Angelox]

after adding 2 more "0","0" at the end of the field it resolved the issue
but now im trying to figgure out how to mass produce that fix.
You should better update your sql database - try these out before you try anything else;

ALTER TABLE ground_spawns ADD temp_x float NOT NULL;
UPDATE ground_spawns SET temp_x=min_x;
UPDATE ground_spawns SET min_x=min_y;
UPDATE ground_spawns SET min_y=temp_x;
UPDATE ground_spawns SET temp_x=max_x;
UPDATE ground_spawns SET max_x=max_y;
UPDATE ground_spawns SET max_y=temp_x;
ALTER TABLE ground_spawns DROP temp_x;
ALTER TABLE object ADD temp_x float NOT NULL;
UPDATE object SET temp_x=xpos;
UPDATE object SET xpos=ypos;
UPDATE object SET ypos=temp_x;
ALTER TABLE object DROP temp_x;
ALTER TABLE npc_types ADD STR MEDIUMINT UNSIGNED NOT NULL DEFAULT '75';
ALTER TABLE npc_types ADD STA MEDIUMINT UNSIGNED NOT NULL DEFAULT '75';
ALTER TABLE npc_types ADD DEX MEDIUMINT UNSIGNED NOT NULL DEFAULT '75';
ALTER TABLE npc_types ADD AGI MEDIUMINT UNSIGNED NOT NULL DEFAULT '75';
ALTER TABLE npc_types ADD _INT MEDIUMINT UNSIGNED NOT NULL DEFAULT '80';
ALTER TABLE npc_types ADD WIS MEDIUMINT UNSIGNED NOT NULL DEFAULT '75';
ALTER TABLE npc_types ADD CHA MEDIUMINT UNSIGNED NOT NULL DEFAULT '75';
ALTER TABLE npc_types DROP fixedz;
ALTER TABLE npc_types ADD fixedz float DEFAULT '0' AFTER luclin_beard;
ALTER TABLE zone_points DROP keep_x;
ALTER TABLE zone_points DROP keep_y;

these are a "collection" of updates made to the sql database
If you just add "zeros" , things could get scrambled.

[Angelox]

I just had a thought; You must be using an old version of EqEmu? you'd have to update EqEmu binarys also

[sdabbs65]

I just had a thought; You must be using an old version of EqEmu? you'd have to update EqEmu binarys also

No im converting my database to the new 0.7.0 tables.
I sourced those in and got less errors..but still get a few others.

INSERT INTO spawngroup (id, name, spawn_limit) VALUES ("' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1

[sdabbs65]

ok basicly I tried sourceing in your values and everything still didn;t work.
the only way I was able to get it to work was by adding ,"0","0"
to the end of all the spawn2 querys.

yours that don't work makes this.
mysql> INSERT INTO spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime, variance, pathgrid, timeleft, _condition, cond_value) VALUES ("184093","184094","tutorial","-286.071","-52.3102","33.6032","745","180","0","0","0",);

mine that works
mysql> INSERT INTO spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime, variance, pathgrid, timeleft, _condition, cond_value) VALUES ("184093","184094","tutorial","-286.071","-52.3102","33.6032","745","180","0","0","0","0","0");

im not sure what fields are missing but that fixed the issue.

[Angelox]

ok basicly I tried sourceing in your values and everything still didn;t work.
the only way I was able to get it to work was by adding ,"0","0"
to the end of all the spawn2 querys.

yours that don't work makes this.
mysql> INSERT INTO spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime, variance, pathgrid, timeleft, _condition, cond_value) VALUES ("184093","184094","tutorial","-286.071","-52.3102","33.6032","745","180","0","0","0",);

mine that works
mysql> INSERT INTO spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime, variance, pathgrid, timeleft, _condition, cond_value) VALUES ("184093","184094","tutorial","-286.071","-52.3102","33.6032","745","180","0","0","0","0","0");

im not sure what fields are missing but that fixed the issue.

These are just off a post I found somewhere in the forums here, and saved - I also recall reading that support would start with a certain version, might have been as of EQEmu-0.6.3 , but maybe the Devs could help you there (i'm not sure).
If you have 0.6.0 and those start at 0.6.3, then you have to figure out what they changed before 0.6.3.

Also you could check the logs at http://www.eqemulator.net/download.php , and see if any lines got left out of what I posted for you.

[zephyr325]

Sdabbs65 -

I just noticed something about the ouput you're getting from this post:mysql> INSERT INTO spawn2 (id, spawngroupID, zone, x, y, z, heading, respawntime
, variance, pathgrid, timeleft, _condition, cond_value) VALUES ("350904","281356
","nexus","0.977219","19.124527","-30.247982","139.125000","1200","0","0","0",);

Each of those lines is 80 characters long. Also, things are getting truncated at exactly 80 characters as the "MYSQL> " prompt shortens up what the program output was to get 80 characters (7 chars as a "mysql> " and 73 "INSERT into" characters). I have double checked and there is nothing in the grabnpc.pl program that will generate a linefeed or carriage return at that point, so I'm interested in seeing an actual file that the program generates.

For example, you'd ge this kind of output if you did a "type 1001-npcname.sql" in a dos box that was 80 characters long and that's how you grabbed what you posted to us. Check out the file that it creates in some sort of text editor that will let you go longer than 80 characters in width (i.e., notepad) and see if the output from the program looks different.

Also, are you sure you're using version 0.5.6 of the grabnpc.pl program? You can tell the version number by editing it and looking at the first few lines. One of the recent changes I made was to make sure to put a set of quotes around every field for every line it outputs so that a blank/null character wouldn't produce a bad insert statement.

[sdabbs65]

Sdabbs65 -

I just noticed something about the ouput you're getting from this post:

Each of those lines is 80 characters long. Also, things are getting truncated at exactly 80 characters as the "MYSQL> " prompt shortens up what the program output was to get 80 characters (7 chars as a "mysql> " and 73 "INSERT into" characters). I have double checked and there is nothing in the grabnpc.pl program that will generate a linefeed or carriage return at that point, so I'm interested in seeing an actual file that the program generates.

For example, you'd ge this kind of output if you did a "type 1001-npcname.sql" in a dos box that was 80 characters long and that's how you grabbed what you posted to us. Check out the file that it creates in some sort of text editor that will let you go longer than 80 characters in width (i.e., notepad) and see if the output from the program looks different.

Also, are you sure you're using version 0.5.6 of the grabnpc.pl program? You can tell the version number by editing it and looking at the first few lines. One of the recent changes I made was to make sure to put a set of quotes around every field for every line it outputs so that a blank/null character wouldn't produce a bad insert statement.

they stop at 80 because vBulletin truncated the lines.
as far as asking for help...... AH-HAH-HAHAH-HaHA...
your funny when your drunk dude.
I got it working thanks for the info it helped out a bit.

[rogabean]

I know this post is a bit old and I dislike bringing back an old thread, but does anyone have this tool still they could send me a link too as I'm not seeing it in the thread.

Thanks.

[cavedude]

If the purpose of this tool was to transfer data between my old db and PEQ's, then it is no longer needed. When I took over PEQ I slowly merged the two together and at this point the merger is pretty much done.

[John Adams]

This'll be a noob question (sorry, been out of it a while), but how does one get their hands on the latest-greatest PEQ.db? The link on ProjectEQ is the Luclin final release, and I think the /sql is just the schema... Is there a CVS or other downloadable source just for the current PEQ DB work?

[rogabean]

I understand that cavedude.. but I was wondering if it would be useful for importing some info from ax's database and the peq database into a new database.. I've been moving a few things by hand, but was hoping this old tool might still be around just to see if it would be of help.

and to answer John.. yes there is a CVS setup that if you check the latest server setup howto on the wiki will give you all the info on downloading it from cvs.

cheers!

[GeorgeS]

Perhaps this is the file you need

http://66.159.225.58/eqemu/grabnpc.zip

If not I have about everyfile emu related from 2002

GeorgeS

[rogabean]

Thanks! That's what I needed.

[FindingKarma]

Does this file still work?

I am wondering if I could use this to create an sql data file of an npc from the peq database?

Then I could make changes to the npc file and source it in place of the current npc. I figured this way I could make my own changes and keep both npc files as backup.

Hopefully it's not an inane question I've barely gotten started here and there's lots more to come.

[joligario]

Doubtful. However, the PEQ editor has an export option for NPCs. I haven't looked at it in a while, so I'm not sure if any of the fields need updating.