Why blobs?
 

I've been wondering about this for a while. Why are we using blobs in the database? Is it for manageability of the queries? Is there a strategic advantage to using blobs? I always thought blobs were less efficient than other data types. Am I wrong?

I don't know about performance; I would assume blobs are more effecient based on how we are using them. Instead of a field by field copy, we're doing one memcpy.

However, performance is not a huge issue with our blobs. They aren't read very often. The item blob is read once and cached. The player profile/inventory data is the only data that is handled consistently throughout a session, but not often enough to worry about performance (imo).

The only reason I see for using blobs is for convenience. It's much less code.

It's a reliability / coding time thing. We use blobs for playerprofile, items, and raw zone points. All of which are cached by the server so the sql's performance isn't really an issue.

There has been talk/code for moving to field based items but the consensus was the field data should only be used to build new blob's

Why not?

Blobs are used for structures because they can easily be converted.

If it was me, I wouldn't use em for the type of data we put in, but since I'm not in charge... :)

They do make it a pain when you try to query tables, using one of the blob's fields as a key. I.E. I want a list of all leggings, wearable by warriors granting a bonus of between 20-50hp. You have to parse through the data instead of letting the SQL engine do the work.

Of course, that doesn't matter for the Emu, but makes a big diff for those making 3rd party apps.

Ok, lets say we went to multiple columns instead of a blob, you realize how many columns that would be for inventory alone?

Instead of 1 column in the table we would have:

30 columns for equiped/main inv items + 80 main inv bag columns + 18 bank slots + 180 bank item bag slots
=308 columns, but wait theres more!!!!

30 columns for equiped/main inv items CHARGES + 80 main inv bag CHARGE columns + 18 bank CHARGES slots + 180 bank item bag CHARGES slots
=616 columns, but wait theres more!!!

30 columns for equiped/main inv items COLORS + 80 main inv bag COLORS columns + 18 bank CHARGES slots + 180 bank item bag COLORS slots
=924 columns, just for inventory.

Blobs are the best format for the way that EQEmu uses them... they were NOT designed to be the best for third party programs, as that would be retarded. If you want to find all leggings that your warrior can wear.. you should probably load them all and then search just as we do.. that way there is less load on the SQL server and you can do more than one search without going through all of the data on SQL again.

Do I get the extra knife that can cut through steel pipe with that too? hehe

Yes, i do realise that. Wonder if we could come up with a happy medium. What if the inventory items for example were in a seperate table. You would have something like char_id, slot_id, item_id, charges, color and other characteristics I might forget.

Items table would be more difficult to reduce the number of columns I think, however.

I'll have a closer look at the blobs and see what I can come up with.

actually for the item table with the new 5.0 servers we used columns instead of a blob format. It ended up being around 50 columns. Hmm your idea about a separate table is interesting though.



Edit: It was determined to be a good idea by a couple other devs as well, so Ill code this into 5.0
Note: INV ONLY!

With the new expansion the way items are done i think will be obsolete.

Yes. The item system was completely redone from what has been said.

Add FIELD_ITEMS to your preprocessor defines to use the new field based item format you see in db.sql. Else, it will continue to use the old blob format.

Good deal, thanks for the heads up, Merth!