Defining items by expansion

[provocating]

I was wondering. It pretty well seems Verant/Sony used incremental primary keys in their item tables. I do have a few item snapshots from different eras. Would it be pretty well east to define each item's era by using the id number? If we know the max id of lets say Luclin and then the max id of say PoP, then we can pretty well define the items that were just introduced in Luclin.

Does anyone have snapshots of the items during different points in time?

[demonstar55]

No. Sometimes they left blanks and later went back to fill in the blanks.

[provocating]

Hell I am seeing that now. I am seeing Luclin things in the 7k range....damn them. So much for being consistent on their part.

[Figback65]

If you add a custom column called expansions, will it mess anything up? Like after you edit an entry in EoC, will it error out because column count doesn't match up or will it just ignore it.

I know per coding, adding the column wont mess anything up. I had added on in the past but I am unsure via EoC editing.

[provocating]

No, I have an expansion column in most of my tables. I have edited my PeqEditor so I can flag things per era.

[Shin Noir]

No, I have an expansion column in most of my tables. I have edited my PeqEditor so I can flag things per era.

Do you have a lot of effort put into this?

[provocating]

A small amount at this point. It will take forever but I have no plans to ever stop working on my database, or the server. Right now I am just doing it one zone at a time. I also added comment fields for many things and edited the PeqEditor so I can mark things by expansion and add comments.

[NatedogEZ]

Could use the zone table as a reference and whatever zone the item drops in add that zones expansion number. Certain items that drop across multiple expansions would get annoying though :p

[provocating]

That is what I was thinking about doing. Basically you could do it from the lower number zones first, then tell it to not overwrite when you hit newer zones.

[Shin Noir]

I have a couple ugly queries you can do to at least see stat giving items with outliers per zone, like..


SELECT i.id, i.NAME, i.classes, i.slots, i.hp, i.mana, s2.zone FROM items i
INNER JOIN lootdrop_entries lde ON lde.item_id = i.id
INNER JOIN lootdrop ld ON ld.id = lde.lootdrop_id
INNER JOIN loottable_entries lte ON lte.lootdrop_id = ld.id
INNER JOIN loottable lt ON lt.id = lte.loottable_id
INNER JOIN npc_types nt ON nt.loottable_id = lt.id
INNER JOIN spawnentry se ON se.npcid = nt.id
INNER JOIN spawn2 s2 ON s2.spawngroupid = se.spawngroupid
WHERE slots > 0 AND s2.zone = 'ecommons' GROUP BY i.id;


They're really intense queries, but at least gets the obvious ones out.. Heh.

[provocating]

Yep, I have something similar

[provocating]

I have started expanding this further now. I have edited the PEQ editor to allow expansion settings in the npc, spawns, items, loot and it appears to be working. Before if I found an item or npc that was out of era I would just delete the item, now I mark it for the appropriate era. Since we are a progression server I may not necessarily want the item deleted. This will work off the expansion setting in the database. I still have a lot of testing to do, but so far it is
working well.


http://legacyoffroststone.com/forum/images/screenshots/expansion.jpg

Ignore the expansion settings I have things set for, I was just changing them and repopping to make sure it was working. I also put some effort to make sure it was not throwing the drop rates off from having to skip items.

[N0ctrnl]

Very nice. That is a much needed feature.

[provocating]

There is still a lot of work left to be done, lots of testing. The best way of testing it is using it.

[AdrianD]

Glad to see others doing this.

I've always wondered why this wasn't in the standard peq databse.

[provocating]

Glad to see others doing this.

I've always wondered why this wasn't in the standard peq databse.

One size does not fit all. You have a lot of custom servers out there that would have zero use for this. In my case I have a dire need for it. When I first started my server I was having to go and remove drops like the newbie starter gear, if I would have done this from the start I could have just flagged those items for Luclin or LoY, whatever era they came out. Instead I deleted those items, since we are progression I will have to add them back. The next stage, which is a long ways off, is to expose the expansion settings to the quest if there is not already a way to grab that (have not looked). This way you can still use the same NPC quest no matter what era your server is in.

[AdrianD]

One size does not fit all.

Sure thing.

The quests would take quite a bit of work but the same idea can be used. As you, I haven't bothered to try to figure that out yet although, ideas are noted.

[provocating]

After working on this further I have found the spawn table will not just need an end date, it will need an expansion_start and expansion_end both. There are instances when you may have a mob that only existed during a certain period. For the majority of it you can just leave the expansion start for 0 and the end for the last expansion, I am of course defaulting those columns to the first and last expansion. Also picking 99 as a developmental setting. There will be times this will be needed because you are just not ready for the expansion to go live yet, or working midstream, you basically need a staging setting.

[provocating]

I have split up my spells by era now, currently putting in Luclin spells. This is working very well and I have not seen any issues. Of course the export utility has to be modified to export the era of spells your server is currently on. I may change the way it is done but I am really thinking of sticking to one set of spells per expansion. There is really not that many spells per expansion and spells were constantly changing. My thinking is just a set of spells per expansion using a compound primary key.

Items are a different story though and I am still not happy with the items table. I wish there was a way to get full list of the items at certain eras. Unlike spells Lucy has no downloads for the items table. No API exist that I can find to download or query their database. Scraping the site would be painstaking the way the history page is for each item. It could be done but it would be horribly tedious to do.

[demonstar55]

I've always wondered why this wasn't in the standard peq databse.

It's not part of the item packet and is a crap ton of extra work that isn't needed to get shit working.

[provocating]

It's not part of the item packet and is a crap ton of extra work that isn't needed to get shit working.

Correct. The majority of servers do not need this. It is extra work and will make the database much larger, and more complicated. One size does not fit all.

[zhangshen147]

Correct. The majority of servers do not need this. It is extra work and will make the database much larger, and more complicated. One size does not fit all.

However, there will be a lot of people need, this is a good project!

[Uleat]

Sounds like you just volunteered!