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

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

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

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

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]

Originally Posted by zephyr325

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]

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

Originally Posted by sdabbs65

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.