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

[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.

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.

[Angelox]

Originally Posted by 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.

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.

[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):

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.

[John Adams]

Originally Posted by zephyr325

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.