Brainstorming: Partial DB Standard

[Damilis]

Greets all. I just wanted to throw down a brainstorm that I have many many months ago and see what people think the Pros/Cons are.

Since EQEmu is an open-sourced application, users can do pretty much whatever they want with any bit or byte they want. This has lead to the segregation of efforts in many different facets of the project, namely NPCIDs, Items and Quests. These three categories represent the largest portion of the database and thus the largest point of segregation. Granted, people have migrated towards consolidating DB's and we have never really had more than 3 or 4 mainstream DB distros out at any given time... this is a good thing, sort of.
I know I have spoken on these boards before about centralization of data and efforts pertaining to the EQEmu effort and have been met with very good insight from others. Here is a refined version of my thoughts:

Standardize certain portions of the database. For instance: 'Reserve' the first 250,000 NPCID's for EQLive-like mobs. Any custom created content *should* start at 250,001. Enact a similar reservation for ITEMID's, NPC types, factions etc. This should allow for the seamless creation of a Npc, Item and Quest set that would work 'right out of the box.' Also, people can offer up their custom DB mods to the public and be able to state:
"This add-on uses NPCID 300000-310000, ITEMID 300000-310000, blah blah blah"
This will allow serverops to see where/if this mod can fit in to their db. Additionally, as long as the db mod is totally self related or related to itself and the 'base' DB, then a serverop can just use a script and bulk change the NPCIDs/ITEMIDs/etc into a range that will fit on his/her server.

All the numbers I used in my example are trivial as its the concept that I would like to discuss. To answer the obvious: Yes I know this is potentially a HUGE effort and NO it won't mandatory, just standardized.

Aight, open floor! Questions, comments, concerns, ideas?

[Rhodan]

I think reserving IDs is the only sensible thing to do. Years ago I ran a UO emulator and thats exactly what they did. The standard world had reserved ID ranges - if you used those for custom things they would be overwritten during an update.

Some people even went so far as to agree between themselves which ranges individuals would use - this let certain people work on specific aspects of a world and combine their efforts when wanted.

ANd this is a timely discussion. I was just wanting to make a custom item and was coming here to ask if there were any kind of designated ID ranges ;)

[Damilis]

Some people even went so far as to agree between themselves which ranges individuals would use - this let certain people work on specific aspects of a world and combine their efforts when wanted.


I had several add-on thoughts that I didn't post, one of which being a system to allocate the non-reserved (aka 300k+) ID's for a person in 1k, 5k or 10k blocks. That way even the 'registered' db mods won't overlap.... but that would require a little more of the Devs copious amounts of free time :) so I opted out on posting it initally.

[John Adams]

Pretty sure there is already a generally accepted standard for NPC Ids, and Item IDs. 13th Floor items I believe use the same itemID you find in the live game - or it's an amazing coincidence that all my magelo gear IDs match items in EQEmu's Items table. And NPCs standard numbering is zoneID * 1000, with the first NPC ID for zone 123 being 123000. etc. Fairly sure people have tried to keep mob-specific IDs to this scheme too, though some mobs (like a rat) can spawn anywhere.

Bottom line is, your first statement was accurate indeed - open source, anyone can and will do what they want. But those who feed BACK to the official PEQ project need to follow their guidelines, or it won't become part of an official release. And very few offer their changes because many tweak for customization anyway, and again, the core project is "Live-like"...

Guess I am saying, I think we're fairly standard already. :) but always a good idea to remind the new folk.

[Damilis]

So CaveDudes and Angelox's DBs are based on PEQ?

[GeorgeS]

So CaveDudes and Angelox's DBs are based on PEQ?

That's correct

[Damilis]

Well thats certainly good news to hear. Is this 'standard' documented anywhere, or perhaps, rules for contributing to PEQ?

[bushman77]

you can always go checkout <link>http://www.projecteq.net/</link> as they are the ones working on this database.

[John Adams]

The ProjectEQ forums have a place to post SQL updates that are then considered for inclusion. I am not sure they come here to get them.

[cavedude]

That's correct


No it isn't, not starting anything here but my database is my own. I started it from scratch.

As for a standard, that is something DRP is working on. I have already begun stripping my database apart and re-organizing all IDs to fit a standard. Duplicate mobs, loot, and faction entries will all be removed as well. The problem is there are essentionally 3 different databases out there (and yes, they are all quite different and have their own strengths/weaknesses) so the other two databases would need to go through the same perging mine is to fit a standard. Any database built from EQBuilder follow the zonenumberid*1000 standard but as Sesmar and I were discussing on our forum, after the zone is initially built, new entries either get tacked on to the end of the db, or somewhere in the middle causing a mess. Our db editing tools will need to be updated to follow the standard. Again, with 3 different databases and multiple people making cool editors how will that work? While I'll continue work on my own database, I am all for a standard and I'll work with whomever I need to to create and follow one.

[Damilis]

Well, my issue is with linking NPCs with zones is that many NPCs can exists in many different zones... so what then? I am thinking that simply blocking off the first two or three hundred thousand NPCID's and just having the NPCs there, in no particular order. If you attempt to order in any way, then you introduce ambiguity to your standardizing system. Just make it so that all non-custom NPCs are ID <300,000 and standardized from db variant to db variant.

[cavedude]

We thought about that, too. I like having the NPC ID being based on the zone ID because then you can look at its entry and say hey, this guy is in Overthere. It makes world building MUCH easier. Part of the purge I am doing to my database involves deleting duplicate identical mobs and updating their spawn entries to the single NPC entry. Those mobs that are "universal" and are found in multiple zones I feel should have their own block of reserved IDs, possibly in the 800-900k range (or some other obscure number that will probably not get a zone assigned to it any time soon by SOE) That way, when world builders look at a tree snake for example, they can automatically tell by its ID that hey, this guy is a roamer.

[John Adams]

That's pretty much exactly how I picture it, Cavedude. One question though - regarding memory utilization. I [assume] with items, the higher the item ID, the more memory it takes to load them all in... I assume this because of the 100k cap. Now, do NPCs have this same memory issue when a zone boots or is made static? Say you have 900 NPCs in zone 123, so NPC id 123000 - 123900. Then add 500 roamers (totally exaggerated populations, i agree) starting at 800000-800500. What will happen to that zones memory consumption? Will the zone allocate RAM for all the empty (but possible) spaces between 123900 and 800000?

I'm having a brain fart, so forgive me if I am totally on crack at the moment. I love publicly humiliating myself. Chicks dig it, too.

[Rhodan]

I'd like to know too.

If the database has two items, #1 and #2,426,333,000, will that use enough ram for 2,426,333,000 items or will it use enough for two items?

The whole idea of cutomized/allocated ID numbers flies out the window if the former is the case.

[eq4me]

I'd like to know too.

If the database has two items, #1 and #2,426,333,000, will that use enough ram for 2,426,333,000 items or will it use enough for two items?
It certainly appears so, at least with some tables that are loaded into shared memory.

The whole idea of cutomized/allocated ID numbers flies out the window if the former is the case.
I bet there is no way for the routine that is reading the tables to determine what is actual data and what is just the unused default value defined for the table. Someone with time an skill has to look into the source and find out how exactly tables are read into shared memory and change it so that not needed entries(the ones with the default value)are not using up memory. I know I am not explaining it right but I dont have much time right now. I think something in the line of double pointers. so eg. item[ID] does not hold the data but a pointer to it. So item[1001] to item[10001] could all point to the same data structure.

[Rhodan]

But does a row actually exist if it hasn't been created? I don't think so.

So in my example above, any query would only return two rows since that is all that was created.

I would think (being only an amateur programmer with little knowledge of EQEmu's inner workings) that memory would be allocated based on the number of rows returned and not the highest index number. Problem is, thats just what I would think which isn't necessarily based in reality 8)

[GeorgeS]

My tools use [Max(id)+1] as part of the sql query. In effect using the next highest id slot - and it's no problem assigning different id's - via a user defined one.

I have no issues changing tools to use different rules

GeorgeS

[John Adams]

I am no pro C programmer, so I guess the root of my initial question was, if you are filling an array for instance, with all the Item data... you can start at [0] and to to the highest # [58000] for example. If you are missing item ids between those two numbers, the array for the missing value is still allocated, no? Thus taking up memory? See, I am not sure if C++ is just smarter than me. Maybe if a few records (or 100,000!!) are skipped over to accomodate this custom numbering idea, the array doesn't allocate that ram. Or, I just have no idea what I am talking about (it's been almost 20 years since I have done actual programming that wasn't ASP/PHP - forgive my noobness).

Anyway, if I fill an array in PHP, it definitely allocates the empty spot - because when I iterate back through it, anything missing is NULL. Hmm. Maybe there's my answer.

[eq4me]

From the peq luclin beta1 database:


CREATE TABLE items (
id int(11) NOT NULL default '0',
minstatus smallint(5) NOT NULL default '0',
Name varchar(64) NOT NULL default '',
aagi int(11) NOT NULL default '0',
ac int(11) NOT NULL default '0',
accuracy int(11) NOT NULL default '0',
... and so on


will tell mysql to fill not otherwise declared entrys with these default values. Mysql itself will not allocate memory for such default entries but will answer queries to them. At least I guess it is so without having the time to validate.

I am no pro C programmer, so I guess the root of my initial question was, if you are filling an array for instance, with all the Item data... you can start at [0] and to to the highest # [58000] for example. If you are missing item ids between those two numbers, the array for the missing value is still allocated, no? Thus taking up memory?
Yes, in an standard array the memory must be allocated. There are some ways around that.
I forgot about this thread: http://www.eqemulator.net/forums/showthread.php?t=21466

[Rhodan]

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)

Eeep! Looks like its true, at least for some of the table data.

Well, we know for sure that item IDs really, really need to stay down low. Time to start checking the database and re-numbering those 180K+ numbers.

I wonder if this might be a source of some of the tradeskill and other item related problems people have been having.

[eq4me]

How about someone takes the time and finds the code that reads the items table into memory. I browsed a bit through the source but wasnt able to find it.

Just compressing the IDs is imho a very bad idea.

[John Adams]

Well, we know for sure that item IDs really, really need to stay down low. Time to start checking the database and re-numbering those 180K+ numbers.
I do not believe item ID numbers are greater than 100k right now. Back when I first started playing here, I made a custom weapon for a friend and purposely set the itemID to 100,000 to stay away from 13th Floor items - and the server wouldn't run.

[paaco]

If you want Item ID's Greater than 100k you can change the max ID in Items.h


// MMF_EQMAX_ITEMS: Make sure this is bigger than the highest item ID#
#define MMF_EQMAX_ITEMS 100000
// MMF_MEMMAX_ITEMS: Maxium number of items to load into memory. Make sure this is bigger
// than the total number of items in the server's database!
//#define MMF_MEMMAX_ITEMS 32700


We had to change this on Dragon's Flight. Just update the values and recompile :)

[John Adams]

Yeah, thanks. I knew of that as a fix, which is what started me on this whole tirade of null IDs sucking up precious RAM. :)