Is there a major DB structure change on the horizon

[Elrach]

From a few EQEmu related boards I've visited, I came under the impression that the current database structure will undergo major changes in the next few weeks.

Is this true?

Reason I'm asking is I've been looking at making some changes of my own to some tables to add functionality. I.E. Starting location table to easily reset the starting city of new characters based on race, class and deity. Added to the table, a bind point that is different than the actual starting place a la EQLive where you're bound in the newbie zones, yet start at your guild.

Anyhow, if major changes are coming, I'd rather wait than start all over again when the new structure and EQEmu is out.

[Merth]

Yes, the database will undergo structural changes. If you are working on a 3rd party tool and/or worldbuilding, you should read this post.

These changes are still .. changing, but here are the changes I perceive to be on the horizon:

#1: Data inside of character_.profile will change. Character inventory will likely no longer be stored here. The size of this blob field is being reduced from 14,908 bytes to 3,924 bytes.

#2: New field: character_.inventory (blob) will be added. This will likely be a relatively small struct (less than 1000 bytes) with simple pointers to items in the item table.

#3: Item data will undergo changes. The structure itself has shifted around, and a lot of new fields have been added. Wes has already figured out the major changes here and has the struct. I don't yet know how charm type items will be stored.

It would probably be helpful to start from scratch with items, since so much new data has been added. I think someone will be working on converting Lucy items to the new structs (wes?).

As a side note, I believe the 32k item limit will be removed in the 0.4.5 release.

#4: *.cfg files will need to be updated. Even though they are not physically stored in the database, we can treat it as such. We will likely need people to packet collect these for as many zones as possible, including LoY zones. Anyone here have access to plane of time?

#5: Zone table structure should not change, but new zones will need to be added. I've already added Jagged Pine and hateplaneb, but I don't have LoY so I don't know those.

#6: Spawns will change - likely just the npc_types table. Data has shifted around, and new data has been added. For example, the sub titles you see under some NPC's (such as "(Rogue Guildmaster)") have been added.

#7: The doors struct did change, which will mean the doors table will change. This is obviously not a top priority for us at the moment, but it would be nice if someone took on the responsibility of packet collecting door data for as many zones as possible. If you're interested, let me know and I'll check up on pc to see if it does door capturing.

[Edgar1898]

#1: Old profile was 8460 :)

#4: Nah I fixed it where it will use the old cfg files.

#6 I currently have it hard coded for lastnames based on class, but Ill add a field to the npc_types table to pull from there instead. (names that appear in the parenthesis on live are considered the npc's last name).

[fnemo]

what about a SQL request to update from 44 to 45 because i feel bad redoing all the custom items i have (more since all are custom)

[burthold]

Personally, we should get away from using blobs all together if you are storing a structure in a blob that could easily be broken into a table so you don't bump into limitations of the blob and it will speed up the database blobs are the slowest data type to write to and read from. I have poured over the db structures and as long as all that data is stored in blobs there is no reason to try and optimize any other structures any gains would be small compaired to normailizing out the blobs holding complete structures. I have been working on it a little bit but I just don't know vc++ as a tool very well at all I bought a book but the last IDE I used to build c++ programs was borland 4 :) Lots has changed since then.

Wes

[Drawde]

Would anyone be able to post a list or .sql file with the exact DB changes?
It'd be useful to be able to "get up to speed" with my NPC parser program, if there
are any changes to the npc_types or spawn tables.

I noticed that the latest CVS has a new field "timeleft" in the spawn2 table.

Will the old item DB be convertable to the new format? I'm asking this because the current
item DB has EVERY pre-POP item, including many (items for broken or incredibly obscure quests, story and GM event items, etc.)
which aren't on Magelo et al and would be extremely hard to obtain via packet collecting.

It also sounds like old characters will be incompatible, but this isn't so much of a problem.

[Merth]

The only db structure change so far is the inventory field added to character_. All of the other changes (so far) are data within a blob field. A new .sql won't help much, but the struct .h file might. PM me if you want the current struct file, I don't know offhand a good way to post that.

I think it will be possible to convert everything to the new format, including character data. It's just a matter of finding someone to do it, and I don't think it's high on anyone's list right now.

My thought on items was that there might be new data added to existing items. Is this the case? I don't play on EQLive anymore, so someone else will have to pipe in here.. For example, aren't there spell descriptions for each spell now? Is this description appended to spell scroll items? Is there a description on the proc effect for each item?

[killspree]

Item effect descriptions aren't done 100% - mainly just effects that are right click, and even then only the most common spells that caster classes get usually have them.

Items may or may not have changed much, I'm not sure. A lot of the plane of time items have new bonuses, but those may have been around since the release of PoP, and just only discovered now that plane of time has been entered.

[Edgar1898]

I noticed that the latest CVS has a new field "timeleft" in the spawn2 table

I added this a few weeks ago so that static zones are not needed for mobs with long respawn times. Its pretty simple, when a spawn is killed it records the timeleft in ms into that field and every 5 minutes the world updates the field by decreasing the value by 300000 (5 minutes in ms). If a zone crashes before the npc has reached his respawn time, the timeleft will be loaded from the db on zonebootup and the spawn wont be spawned until the timeleft has reached 0. The reason for this is so that if a zone crashes and you had a high respawn time on a mob that was just killed, it would not be respawned immediately like it used to be. Hope I made that clear, if not post and Ill answer any questions about it further.

OK now about the fields, I am planning on adding 1 field, 'lastname' to npc_types, it will be after name. Its size will be 20. Just add it as a varchar tho.

[burthold]

I've got the latest cvs pulls thanks to tortoiuseCVS I've been going through the structs as I find them. I'll check out the struct file and see what kind of magic I can work.

Wes

[Edgar1898]

the new source isnt on the public cvs website, thats the old source and as such not being used anymore.

[Trumpcard]

We're in almost a 'freeze' at the moment with the work being done on the new client. The code has effectively split into 2 branches, and I've held off from doing anything until we can finalize the new code branch as the standard.

I havent pushed any new CVS out in a few weeks, and there hasnt been a need to lately as most work on the old branch has effectively stopped and all efforts are focused on the new codebase. Once the new codebase is ready for the big time, I'm going to switch the CVS jobs to pull it instead, then you can look to CVS as being the defacto code archive again.

[burthold]

Cool. That gives me more time to figure out vc6++ :D

[Lurker_005]

I plan to convert the current items to the new item format once I have it. If the item structure is finalized, could some one send it to me. If the blob is going to be broken down into fields in the DB, please send the field structure also. Kaiyodo and I already have code to collect and convert lucy data, but it will need to be updated.

NOTE: this is meant as a temporary measure, packetcollecting is still the BEST way to get up to date and complete item data. Once packetcollected items are being gathered that data should be used to overwrite any converted items.

BTW anyone know the new limit for item numbers? I know it is over 70,000 I think 72,500 or there abouts is the highest item I noticed on lucy.

[jbb]

I havent pushed any new CVS out in a few weeks, and there hasnt been a need to lately as most work on the old branch has effectively stopped and all efforts are focused on the new codebase. Once the new codebase is ready for the big time, I'm going to switch the CVS jobs to pull it instead, then you can look to CVS as being the defacto code archive again.

I was having a "play" with the code a month or two ago but haven't had any time since. I was thinking of having another look at it soon but didn't want to spend a lot of time figuring out how the code in CVS works only to find it's all been replaced one day so I was wondering if you could tell me :-

1. When the new code is likely to be available? (I only mean are we talking days, weeks or months...?)
2. Has it changed a lot? Will looking at the existing code be a waste of time?

I thought it was worth asking but I'll understand if you can only say that it will be ready when it's ready...

[Edgar1898]

Probably a week, we are working on getting a few more basic features working. For example, spells are still being debugged, and we would like to get those working properly before we release the source.

[jbb]

Excellent.

[kabalah]

hi all, has the new and improved db structure been released to the public yet? i wanted to help in debugging but, i have db issues...missing fields/tables?

kabalah

[Merth]

Yes, the new db.sql should be available from CVS. I haven't touched CVS in a while, so if you don't see it, let us know.

[kabalah]

ok, i checked cvs and i don't see the db.sql file anywhere in the eqemu/NewSource tree.
kabalah

[Edgar1898]

it show be under eqemu release

[kabalah]

nope, not under NewSource, do you mean directly under eqemu tree?
kabalah

[Bigpull]

the db.sql is in NightDumps/EQEMu Release/ and likely not upto date

alter table character_ add inventory blob default NULL after profile;
CREATE TABLE start_zones (
x float NOT NULL default '0',
y float NOT NULL default '0',
z float NOT NULL default '0',
zone_id int(4) NOT NULL default '0',
player_deity int(4) NOT NULL default '0',
player_race int(4) NOT NULL default '0',
player_class int(2) NOT NULL default '0',
player_choice int(2) NOT NULL default '0'
) TYPE=MyISAM;

[tcsmyworld]

Is that the only change needed so far?
Do we need to wipe accts and start fresh?

[Bigpull]

Accounts are fine character_ is another matter, untill/unless someone writes a converter, you may want to dump them to a .sql or temp table

[kabalah]

thanks bigpull!
kabalah

[mutombo]

additional i need to add timeleft in the spawn2 table to join zones.

added it this way:
timeleft int(11) NOT NULL default '0',

seems working, i can login.
npcs are there too
there seems something wrong with the items always getting :"got a bogus item, deleting it " when i want to trade with a npc, but im sure thats something with the DB, im using Drawde's Worlddata 1.1 final for CVS.