Database Schema

[burthold]

If you have wondered what the schema looks like here you go.
http://www.rpg-forums.com/eq_44.htm
This is 0.4.4 production. It acceptes drawdes final 1.1 db with no errors.

Cheers,
Wes

[Zern]

Very nice

[burthold]

No problem, as I figure out what each field actually controls I'll add that to the document. I'm a Sr. database administrator and developer. Anything I can do to help I will.

Wes

[Trumpcard]

Can you check and make sure we're well normalized and following all the normal forms...

I haven't analyzed the schema alot but I figure we've got some DNF problems in it. Does mysql support indexing? That would probably help us out performance wise on some of the big tables like items.

[gej302]

Yes. it supports indexing

[burthold]

I have been looking over the layout I'll be more than happy to make any suggestions that will speed up the db and queries. I'm also downloading the latest CSV today or tomorrow I was a pretty decent C++ coder back in the day never got into writing gui apps in C++ but could code a mean DOS app I would suggest exploring the new innodb storage engine instead of using MyISAM files. They are fast ACID compliant and don't suffer some of the locking issues MyISAM does. On the structure side I would change the grid table and break it up so your pathing isn't limited to 50 way points. Also there are alot of text or blob fields that may not be needed in alot of cases people dump data that should be broken up and stored in a blob and parse through it on the program side of the app instead of storing it properly in the first place. I'll know more after this week for sure though. I love the emu it has come a long way since the last time I took a peek

Wes

[Lurker_005]

Well it looks like your input will definatly be of use and apreciated!


Looking forward to what you have to suggest.

[Trumpcard]

The blobs were used so packet sniffed items could be jammed directly into the database without alot of decoding, plus kept the table size down..

Now, it would probably be a good idea to convert it, would really make item manipulation alot easier...

[burthold]

Well I am going to put together a simi-normalized structure with the current database I have and we will go from there. I am also browsing through the database code from the csv just to get familiar with the queries you are using.

Wes

[IAsoulfood]

burthold, Lurker_005 and Trumpcard

I'd like it if you keep me close in the database and coding loop. I am a Senior Systems Engineer / Networking Engineer and hobbiest C++ programmer. I have spent alot of time on this code and have a great appreciation and understanding of your emulator. I have been having alot of fun working on these doors with Lurker_005's help and been using 3 different databases locally for testing...Drawde's Telmet's and a custom blend of both =) couldn't resist the last. Anything I can do to help I'd be glad to. You can always send me an email at pantora@core.com
Thanks again for an awesome piece of code
Soulfood 44th season [ex-Kane Bayle]
Pantora 38th season [ex-Kane Bayle]

[Jrodo]

Your database schema looks great !... don't happen to have a relationship chart by chance? ( many to one , one to many type chart? etc) It might help me with a problem I am having...

PROBLEM 1 :I make new spawns with the #NPCtypespawn command then use the #npcspawn add command to put in database. NP so far, now I add a custom mob like Drovlog Captian, so I have to use the "#NPCSpawn Drovlog_Captian ...etc... x x x x x " command to create the mob then use "#NPCSpawn add" command to put in database. Everything looks good, database has the new mobs in sp2, spawnentry and spawngroup tables. BUT when I restart my server only the NPCtype spawns show up the custom mobs are gone....They are still in the database though. I must be missing a link or something somewhere. Can anyone help me?

PROBLEM 2:Also, was gonna populate spiders and dogs in Velks but can find them anywhere in the database table NPC_types. I thought all the mobs were in there?

Doing great gang... Keep up the good work!

Specific command example I used..
#npctypespawn 3084 spawns a Kobald.
#npcspawn add adds targeted mob to database

and then custom mob:
#spawn Giant_rat 36 1 0 16 2 1 0 0 0 spawns custom rat
#npcspawn add adds targeted mob to database

Then reload server.. only the NPCtypespawns show up the custom one are gone!

[Merth]

I would suggest exploring the new innodb storage engine instead of using MyISAM files. They are fast ACID compliant and don't suffer some of the locking issues MyISAM does.

I missed this the first time I read this thread... But I experience several issues on my server that I suspect are related to this. I don't know mysql, however. Is this a simple fix on a per-database thing? Or will it require a major db overhault? How can I play around with this?