ID Consistency

[provocating]

I am having difficulty grasping a concept in the database. If you do a packet collect, and then the SQL is generated it may have something like this at the top. This is from a GFAY generated SQL.

set @StartingNPCTypeID = 54000;
set @StartingSpawnGroupID = 54000;
set @StartingSpawnEntryID = 54000;
set @StartingSpawn2ID = 54000;
set @StartingGridID = 54000;
set @StartingMerchantID = 54000;
set @BaseDoorID = 0;
set @StartingGroundSpawnID = 54000;
set @StartingObjectID = 54000;

DELETE from npc_types where id >= @StartingNPCTypeID and id <= @StartingNPCTypeID + 999 and version = 0;
DELETE from spawngroup where id >= @StartingSpawnGroupID and id <= @StartingSpawnGroupID + 999;
DELETE from spawnentry where spawngroupID >= @StartingSpawnEntryID and spawngroupID <= @StartingSpawnEntryID + 999;
DELETE from spawn2 where id >= @StartingSpawn2ID and id <= @StartingSpawn2ID + 999 and version = 0;

So this would lead me to believe the spawnentry ID's for all GFAY NPC's would be in the 54000-54999 range right ? Trying this query....

SELECT * FROM spawnentry WHERE npcid>54000 AND npcid<54999;

spawngroupID 	npcID 	chance
1488 	54048 	100
1489 	54046 	100
1490 	54049 	100
1491 	54050 	100
1492 	54041 	20
1492 	54051 	20
1492 	54238 	20
1492 	54239 	20
1492 	54251 	20
1493 	54052 	100
1494 	54007 	100

I get this odd numbering in a lot of places. It makes it hard to add things. I will find the odd NPC in live that is not in the DB, but I cannot seem to trust putting it in, because I may step on the *&*() of another zone. I am thinking this has to be me and something I am doing wrong.

Or maybe this....

SELECT max(id) FROM npc_types WHERE id>54000 AND id<54999;

Yields this result.

pID 	npcID 	chance
54001 	268167 	100
54002 	268144 	100
54003 	268177 	100
54004 	268180 	100
54005 	268181 	100
54006 	268182 	100
54007 	268170 	100
54008 	268172 	100
54009 	268183 	100
54010 	268176 	100
54011 	268179 	100
54012 	268168 	100
54013 	268184 	100
54014 	268185 	100
54015 	268174 	100
54016 	268178 	100
54017 	268186 	10

So this is showing spawns in zone 268 ?

[wolfwalkereci]

This actually confused the crap out of me for a few days because I had to adjustfire on work I was doing when taking it from my DB and inputting it to another server.

I'm sure someone that is more knowledgeable will chim in but you'll see how the spawngroup.id = spawn2.spawngroupID = spawnentry.spawngroupID
and spawn2.id is just another thing.

The spawngroupID is just a auto incrementing field and lots of stuff wont be "in order" but its not important in terms of the npcID so your query

SELECT max(id) FROM npc_types WHERE id>54000 AND id<54999;

is misleading you.

No idea why the collector is feeding the starting ID's as I've never touched that but wouldnt be that hard for you to column edit with notepad++ into usable values.

Of course I really dont know that much about this, just a little I learned by trial and breaking my db error. Kinda tired so this might not even be on topic.

[Akkadius]

From - http://www.eqemulator.org/forums/sho...ight=extractor

Originally Posted by cavedude

Found a problem, spawngroup is using the npcid for its id instead of the one you specify. The funny thing is, spawnentry is using the proper spawngroupID (meaning, the one you specify, and not the npcid.) In my testing, all other IDs look to be OK.

Just a friendly word of warning, if you are building zones against the PEQ database, the program defaults often conflict with existing data, which is destroyed when you source in the output.

Nevertheless, this is the most exciting thing to come out in a long time for EQEmu in my mind.

There are also several continuing pages talking about default ID conflicts with the database. As this starts to work fine with the higher zone ID's without confliction, it conflicts with the lower zone ID's quite frequently.

Depending on what you are trying to submit to, your own or the PEQ database, you may want Cavedude's input on how some zones are inserted. But to insert against your own database cleanly with free ID's you can use something similar to this:

set @zone = "moors";
SELECT MAX(id) + 1 AS npc_types FROM npc_types;
SELECT MAX(doorid) + 1 AS Doors FROM doors WHERE zone = @zone;
SELECT MAX(id) + 1 AS Spawngroup FROM spawngroup;
SELECT MAX(spawngroupID) + 1 AS Spawnentry FROM spawnentry;
SELECT MAX(id) + 1 as Spawn2 FROM spawn2;
SELECT MAX(id) + 1 As Objects FROM object;
SELECT MAX(merchantid) + 1 as merchantlists FROM merchantlist;
SELECT MAX(id) + 1 as Grid FROM grid;

Just define your zone at the top and it will grab the max free ID's from your database to inject upon. Derision should have a check-box option for this but that would include implementing a Mysql connection option of sorts (Tool is still very awesome regardless, thank you Derision ).

Still not understanding completely what you are trying to achieve even though you should know by now that ID's aren't completely consistent across the board. Maybe this query will help you figure out ID ranges of a particular zone:

*This includes all fields of all tables besides the npc_types table, npc_types select only pulls id and name for reference

*Just put the zone shortname at the bottom of the query:

SELECT
npc_types.id,
npc_types.`name`,
spawnentry.spawngroupID,
spawnentry.npcID,
spawnentry.chance,
spawn2.id,
spawn2.spawngroupID,
spawn2.zone,
spawn2.version,
spawn2.x,
spawn2.y,
spawn2.z,
spawn2.heading,
spawn2.respawntime,
spawn2.variance,
spawn2.pathgrid,
spawn2._condition,
spawn2.cond_value,
spawn2.enabled,
spawn2.animation,
spawngroup.id,
spawngroup.`name`,
spawngroup.spawn_limit,
spawngroup.dist,
spawngroup.max_x,
spawngroup.min_x,
spawngroup.max_y,
spawngroup.min_y,
spawngroup.delay
FROM
npc_types
INNER JOIN spawnentry ON npc_types.id = spawnentry.npcID
INNER JOIN spawngroup ON spawnentry.spawngroupID = spawngroup.id
INNER JOIN spawn2 ON spawnentry.spawngroupID = spawn2.spawngroupID
WHERE
spawn2.zone = 'crushbone'

[provocating]

I am just trying to remain consistent man. If I find something and want to submit it for Cavedude to add to the database, I want to present it to him properly.

[cavedude]

I have made heavy modifications to EQExtractor and posted it here:

https://code.google.com/p/projecteqdb/downloads/list

It simplifies IDs a bit by removing many that aren't needed (since they use auto-increment), adds new features, and fixes a few bugs. I have to merge some of my work into the EQEmu SVN, as some of the bugs are severe (like for example, the grid DELETE is NOT safe - it WILL delete grids in multiple zones!) But, most of my features will remain exclusive to my version since to be honest they are more meant for experienced world builders. Derision's version is safe relatively speaking, but some of the check boxes in my version can really mess you up if you don't know what you are doing - so just a word of warning.

The IDs PEQ uses are simple... NPCID starts at zoneid*1000. spawn2, spawngroup, objects, ground_spawns are all incremental, grids are incremental BY zoneid (something that needs to be fixed in the stock EQExtractor) as are doors. Merchantlist should be just the NPCID, but admittedly we have lost our way in regards to that, however I will be fixing that soon.

By default, neither version of EQExtractor will give you the proper IDs for spawn2, spawngroup, grids, doors, or merchantlist (my version does not require IDs for other tables.) It has no connection to MySQL, so it has no way of knowing what the next available ID is. That's why Derision was awesome to have the queries use variables. That way, we can pass our outputted SQLs around, and not have to worry about IDs conflicting. PEQ SVN is ALWAYS behind (even if it was updated the same day) so I'm going to have to change IDs anyway.

[Akkadius]

Originally Posted by cavedude

I have made heavy modifications to EQExtractor and posted it here:

https://code.google.com/p/projecteqdb/downloads/list

It simplifies IDs a bit by removing many that aren't needed (since they use auto-increment), adds new features, and fixes a few bugs. I have to merge some of my work into the EQEmu SVN, as some of the bugs are severe (like for example, the grid DELETE is NOT safe - it WILL delete grids in multiple zones!) But, most of my features will remain exclusive to my version since to be honest they are more meant for experienced world builders. Derision's version is safe relatively speaking, but some of the check boxes in my version can really mess you up if you don't know what you are doing - so just a word of warning.

The IDs PEQ uses are simple... NPCID starts at zoneid*1000. spawn2, spawngroup, objects, ground_spawns are all incremental, grids are incremental BY zoneid (something that needs to be fixed in the stock EQExtractor) as are doors. Merchantlist should be just the NPCID, but admittedly we have lost our way in regards to that, however I will be fixing that soon.

By default, neither version of EQExtractor will give you the proper IDs for spawn2, spawngroup, grids, doors, or merchantlist (my version does not require IDs for other tables.) It has no connection to MySQL, so it has no way of knowing what the next available ID is. That's why Derision was awesome to have the queries use variables. That way, we can pass our outputted SQLs around, and not have to worry about IDs conflicting. PEQ SVN is ALWAYS behind (even if it was updated the same day) so I'm going to have to change IDs anyway.

I knew most tables go by zoneid * 1000, and the PEQ Editor also looks for this convention for speed reasons as well, but I also knew there were other things that didn't line up hence why I had left the rest to you considering I don't look at the PEQ DB all that much even though I've hoped to help with all of the collects .

Thanks for the help clarifying for him

Nice touches on the Extractor I didn't even know you had modified it

[provocating]

Okay, now I have an explanation, awesome.

[cavedude]

I never made a post because i didn't want to step on Derision's toes. At first, I just added some features for my own benefit. But I then came across some bugs that I needed to fix. I will be merging those bug fixes and some of the minor functionality changes into the EQEmu SVN when I get time. But the removed ID boxes, added feature check boxes, and major functionality changes will be left exclusive to my version. I don't want to be blamed when somebody updates their database and it changes all their NPC levels or something similar