Spawns: How to properly Index your spawns

[John Adams]

(edit: These posts were moved from Content to Tutorials to give you a general idea how we're cleaning things up - and are based on the results of EQ2PacketCollector data and PacketParser.exe results)


Again, this is just one concept, but seems to make the most sense. I won't sell it beyond the fact that if a spawn is somehow clearly associated with it's zone_id, it will be easier to identify and work with down the road. With that, here's what we decided long ago.

I would like to utilize chrrox original numbering concept of "zone_id * 10000" (ten thousand). An example, if Dockmaster Wilson in Antonica is our first spawned NPC, his spawn_id will be 120000 (one hundred twenty thousand). That is zone_id 12 with 4 zeros after it (lol).

Here's a snapshot of what my Antonica zone starts looking like:

spawn_id.jpg

As you can see, zone 12 (antonica) starts at 120000 and goes to a max spawn_id of 129999 for this one zone. That's a lot of unique spawns!

What defines a "unique spawn"? I will go over this in the next post about Duplicates.

Suffice it to say, the reason for buffering each zone to 10,000 is that during parsing, it is highly unlikely to generate 10,000 unique spawn types out of a single zone of collects. So 10,000 indexes gives us plenty of room to fill up the zones with spawns (dupes be damned), then remove or add stuff as needed - or re-index if we find an NPC is set as a Widget and needs to be re-designed (yet another post incoming!)

Exception to the rule! (of course, there are always exceptions)
I think we should allow one type of spawn_id to remain "neutral"; that is, belong to no zone. Harvestables is a prime example. Items that appear in *every* zone should not be duplicated in every zone_id range, unfortunately that is how they are collected and parsed, which is where the good folks on the Content Design team do their work!

For instance, look at this example:

harvest.jpg

Here you see 2 Gather nodes in 2 different zones that could actually be a single entry. I mean, how many different ways can a glowing ? appear? There are other examples like the shoal of fish, or wind felled tree. These (I feel) should be Re-Indexed as a spawn_id of 1 - 9999, that way they belong to no zone and can be used anywhere.

Note:
Since the spawn_id is basically the balloon string that ties all the spawn parts together, getting this information CONSISTENT is mandatory. If this idea is not optimal to you, voice your opinions now. My additions to the packetparser include this numbering scheme in mind. We do not want to re-ID things down the road.

Why a Master Database?
I thought you'd never ask. What happens if all of us work individually on zones in our own local databases, then we try to merge them together? Due to the above mentioned "balloon strings", it will be living hell for the DB Team to knit and sew all that data back together into one DB after getting SQL dumps from everyone.

In this, practice messing around with your clean up techniques on your local boxes - but the only data I will accept as valid cleanup work will be done on the master database (and currently that is conveniently on my server: TessEQ2 ). Once we get a zone that we all agree is "as good as it's gonna get", we can release it to the public servers via the update process.

[bobbydole]

Again, this is just one concept, but seems to make the most sense. I won't sell it beyond the fact that if a spawn is somehow clearly associated with it's zone_id, it will be easier to identify and work with down the road. With that, here's what we decided long ago.
I would like to utilize chrrox original numbering concept of "zone_id * 10000" (ten thousand). An example, if Dockmaster Wilson in Antonica is our first spawned NPC, his spawn_id will be 120000 (one hundred twenty thousand). That is zone_id 12 with 4 zeros after it (lol).

Ok I have a question. I don't want to ruffle any feathers, but why not set up a cross reference table which contains the zone_id and the spawn_id?

[John Adams]

You might not be understanding what I was getting at. The ID of the spawn dictates what zone that unique version of the NPC exists in - so if the spawn ID is 120000, we know that copy of the NPC belongs in zone 12, or Antonica.
It makes look up and referencing much easier. For example, if I wanted to know every spawn in Zone ID 12, I could write a huge multi-joined query linking spawns to spawn_npcs, to zonespawnentry to zonespawns to find the zone ID 12 and list the NPCs.
Or...
I could simply query:

Code: Select all

select * from spawn where id like '12____';

And I know everything that starts with a 12 is an antonican spawn.
There are holes in every theory... nothing is perfect, but this one makes the most sense and is a proven concept (EQEmu).
If I misunderstood you, please clarify. I am always interested in efficiency over ego.

[bobbydole]

You might not be understanding what I was getting at. The ID of the spawn dictates what zone that unique version of the NPC exists in - so if the spawn ID is 120000, we know that copy of the NPC belongs in zone 12, or Antonica.

It just seems like that would be combining two different sets of data into one. I might be miss something though...

It makes look up and referencing much easier. For example, if I wanted to know every spawn in Zone ID 12, I could write a huge multi-joined query linking spawns to spawn_npcs, to zonespawnentry to zonespawns to find the zone ID 12 and list the NPCs.

Or why not have put the zone_id as a FK on the spawn table. That way could you just do something like this:

Code: Select all

select * from spawn where zone_id = 12;

How would you do something like this:

Code: Select all

select s.* from spawn s
inner join zone z on s.zone_id = z.id
where z.name = 'Antonica';

Using your method?
Grab everything s>=z.id*10000 && s< (z.id+1)*10000?

There are holes in every theory... nothing is perfect, but this one makes the most sense and is a proven concept (EQEmu).
If I misunderstood you, please clarify. I am always interested in efficiency over ego.

Right, I don't mean to rehash things, and I don't really care all that much what you guys do, I'm just wondering what the reason was.

[John Adams]

You cannot efficiently bind a spawn/npc record specifically to one single zone in the spawn/npc record, because you have many different Spawns (a ?, for instance) spawning in many different zones. You would have to maintain a separate record for each "?" for each zone in the table and then in the least case, you have 240 or so "?" Spawn records when you really only need one.
If you have not reviews the current schema, you should. What you see is this flow:
spawn - the main spawn record, which is referenced by:
- spawn_npcs - the NPC detail data, which is referenced by:
-- npc_appearance
-- npc_appearance_equip
- spawn_object - the interactable or placeable objects
- spawn_signs - the signs/zone markers
- spawn_widgets - the clicky things in game
- spawn_script_data - links to LUA files
- spawn_loot - a spawns loot table(s)
Now to get that spawn in the game, you have to build zone spawn info:
zonespawngroup - the parent record
zonespawns - the details/coords of a given spawn/placeholder set
zonespawnentry - the intersection between spawn_id and zonespawns records
This is the short version, but you can follow this if you open the DB and look for those tables. Believe me when I say, this is the most efficient design next to putting everything in memory and hoping for the best.