Suggestion - Item Database Idea

[Kayot]

Suggestion - Item Database Idea

I'll start off by saying, I'm sure this idea will be resisted like a French invasion force. However I've put a lot of thought into it and I think it can be done.

Idea background - I was cleaning my DB out and I reached my eAthena Database (Ragnarok for those who don't know) and I dumped it, only to notice it was smaller than the EQemu dump. Most of the eqemu database is grid_entries at a staggering 26.6 mb, that is unavoidable. However items is 18.1 mb. eAthena was a hell of a lot smaller (Purged table before checking it, but the dump file was only 9 mb before 7zip compression.)

On the other hand I'm making a program that edits the eqemu DB, It uses a script engine that processes items and a whole lot of other technical crap. The script for "1001 Cloth Shirt" is the following:

ac=2
weight=2
price=0,2,0,0
augmentlevels=7,0,0,0,0
slots=head
size=1

The other things like spell effect/scroll effect etc are handeled via drag and drop.

Idea - So heres the idea, make a default value for everything an item could have (I've already done that for my program, though somethings have weird defaults) and instead of the 200ish columns of data, use one TEXT field and a few columns for truly necessary data like id, name, type, icon, and probably idfile. This should make the database smaller as most items don't use most of the blanks. Not to mention there are some columns that don't even have a value besides whatever they all have.

Downside - SQL commands wont be able to sort data (Well, not without one serious command anyway), which will make arbitrary searches harder, however most item searches are for name only. It's rare for an editor to search for a particular type of item, and then it's usually so the editor can summon the item in-game.

Any thoughts on this?

[GeorgeS]

As mentioned that would break about everything out there. Serialization was done years ago in a field, but that's gone. Relational Database functionality would also be broken. Perhaps this idea could be better applied to grid entries in some way.

However, as you know the database structure is pretty much written in stone and changes are unlikely. For your tools however, custom created tables are possible.

GeorgeS

[Kayot]

As for my tools, I've taken a don't touch the database structure approach that assumes nothing will change for the better. Hence why I avoid working on an Alt Adv editor until the concept matures.

I don't mind if my suggestion isn't used, it was just a thought. As for grid_entries, the structures as complete as it can be. A true testament to db structure. The loottable on the other hand is a nightmare of complexity. I managed to make an editor for it that handles it beautifully, however the loot table really should be just two tables. It would take up more space but wouldn't require so much cross referencing.

Anyway, ^-^ continue.

[KLS]

I don't think lookup would be an issue, we already load items into shared mem for quick access anyway. The biggest problem(s) would be how and what do we set the defaults. How do we upgrade/import without breaking everything, with this one being the biggest issue.

[Bulle]

Spoiler warning to Kayot : I'm French

My question to you Kayot is : what is the problem with having a "big" table ? (I put big in quotes because 25 MB is hardly huge, my customers often have Oracle databases with tablespaces in the GB range, and that is only meta-data, not blobs). To put it another way, what kind of processing are you doing that makes the total table size matter ? The Emu loads the items DB in totality in memory, it only takes a few seconds and is a few 10s of MB in memory size. Close to nothing.

I like being able to query items based on individual fields, though I agree some fields are kinda crazy (the augment fields for example, they are a pain to interpret) and could use some rationalizing. The problem there is it seems the items table is derived directly from some "external dump", the 13-th Floor (I may be off there, I have no real idea how all that is extracted, but that was my understanding).

What the hell are you doing with the full item table dump

[Kayot]

I'm removing the need to edit 200+ fields of data. I don't mind the size, I'm using a smaller size as a selling point ^-^.

Tell me what the following fields do, as they don't seem to have a use.

augslot1unk - 40 items = 0, the rest = 1, Field is an Integer
augslot2unk - 336 items = 0, the rest = 1, Field is an Integer
augslot3unk - 494 items = 0, the rest = 1, Field is an Integer
augslot4unk - 233 items = 0, the rest = 1, Field is an Integer
augslot5unk - 99 items = 0, the rest = 1, Field is an Integer
unknown002 - Always = 0, Field is an Integer
unknown003 - Always = 0, Field is an Integer
unknown005 - Always = 0, Field is an Integer
unknown007 - Always = 0, Field is an Integer
unknown018 - Always = 0, Field is an Integer
unknown019 - Always = 0, Field is an Integer
unknown020 - Always = 0, Field is an Integer
UNK012 - Always = 0, Field is an Integer
UNK013 - Is either -1,0,1
unknown061 - Always = 0, Field is an Integer
UNK054 - Always = 0, Field is an Integer
unknown067 - Always = 0, Field is an Integer
unknown069 - Always = 0, Field is an Integer
UNK059 - Always = 0, Field is an Integer
UNK061 - 0 or 1, is integer
unknown081 - Always = 0, Field is an Integer
unknown105 - Always = 0, Field is an Integer
unknown122 - Always = 0, Field is an Integer
unknown123 - all 0, is varchar(11)
unknown124 - all 0, is varchar(11)
UNK123 - Always = 0, Field is an Integer
UNK124 - 0 and 1, is integer
unknown133 - Always = 0, Field is an Integer
UNK127 - Always = 0, Field is an Integer
UNK134 - Is varchar and all entrys are empty
UNK137 - Always = 0, Field is an Integer
UNK142 - Always = 0, Field is an Integer
UNK147 - Always = 0, Field is an Integer
UNK152 - Always = 0, Field is an Integer
UNK157 - Always = 0, Field is an Integer
serialized - All entrys are null
serialization - All entries are null
UNK033 - Always = 0, Field is an Integer
UNK014 - -1 0 and 1
svcorruption - Should be next to the other resists and name should be or since c is cold.
UNK038 - Always = 0, Field is an Integer
UNK060 - Always = 0, Field is an Integer
augslot1unk2 - Always = 0, Field is an Integer
augslot2unk2 - 61762 are 0 rest are 1, Field is an Integer
augslot3unk2 - 61613 are 0 rest are 1, Field is an Integer
augslot4unk2 - 62113 are 0 rest are 1, Field is an Integer
augslot5unk2 - Always = 0, Field is an Integer
UNK098 - 18753 are 0, rest are 70
UNK109 - Always = 0, Field is an Integer
UNK120 - -1,0,4
UNK121 - Always = 0, Field is an Integer
UNK131 - Always = 0, Field is an Integer
UNK132 - is one of the following, 0,0000000000000000000,00000000000000000000000000000000000000 and is a TEXT field
clickunk5 - Always = 0, Field is an Integer

A lot of the list is always 0, it would be faster to have the program assume a 0 than to query. Also, when looking down the defaults in the item table, they aren't the defaults.

Classes for instant, is more likely to be 65535 than anything else. I know its a bit field, but lets face it, 60% of items are ALL/ALL.

Then theres the fact that so many of the fields are using INTEGER when they should be using BOOLEAN, some even use varchar which is a big no no on entries that will only use numbers.

The database needs work, a lot of the work is DB only. Things like field type, and defaults. I'm willing to do the DB work if the community will accept and use it, not as a small cult following but as true integration.

As for removing unused columns, I figure someone will argue that they may be used in the future. Well, thats the future. In the future there might be space ships that run on poo, should we start saving our poo now or wait till that day comes? I think it would be best to assume those entries are useless and to remove them until they are used. At which point, adding them back is a simple process of adding it to the query, removing the programs default, and pushing it in. Takes about two minutes if you know what your looking for.

Anyway, continue.

[trevius]

Maybe the item wiki will help in decision making for any changes. Here is the link:

http://www.eqemulator.net/wiki/wikka...uDBSchemaitems

[Bulle]

It is a double-headed problem here :
- those fields are most probably unused by the Emu, so during normal server operation they take up space for nothing
- those fields are supposedly accurate, meaning their value is Live-like and could be used in the (near or not so near) future, if someone cared to develop the functionality

If we remove the fields, then there is close to zero chance for them to be used someday, as the information will not be there to begin with. There is a related trap : custom DBs. The sane way to create a new item is to pick a similar one and apply your changes, that way you ensure most of the fields are consistent. Say I need to create a bag, best is to start from a similar bag and tweak a few fields. If the currently-useless fields are removed and some are reintroduced someday, they will have to be put back in (with their proper value, ie you must remember what item you started from) one by one. This can lead to long and difficult upgrades for custom worlds. I am not too worried about this problem as I keep my item definitions (as deltas to the original item) so I know I will be able to re-apply the initial definition, but not everybody works this way. I know this trap can probably be avoided with sane default values and such, but everyone should be aware of this impact.

On a related topic, could anyone clarify how the DB dump (in PEQ) is generated, how new Live-like items are introduced (manually or automatically from another source), and what the 13TH-FLOOR is ?

[Bulle]

A wild idea just occured to me, which could be used to get the best of both worlds.

We keep a full items table as it is, to save all those unused but accurate fields till we devise and have the time to spare to use them. Basically we change nothing to the original items table, items can still be changed/added in there. It is a 30 MB table, nothing to be worried about.

In addition we create an SQL View for the items table. This view would be used by the EQEmu server (or by tools not worried about the unused fields). In the view we only include the fields that are useful to the Emu. That way the Emu only reads what is necessary. In MySQL I am pretty sure the performance for reading from the view is close to what it would be to read from the original table with the unused fields removed. What is expensive when querying is process-to-process transfer (or machine-to-machinetransfer) from MySQL to the Emu, then re-distributing the item information in the C++ data structures, on the pure MySQL side the gain for having less fields will not be very significant.

Views are very powerful in SQL. You can also update, insert and delete through a view. For insertions the server will fill-in any field not in the view with the default value for the field. Then it is up to every individual to choose between working with the "simpler" view, or with the full items table. If you want to give it a try, create the following view in your Emu DB and toy with it (be careful, inserts, updates and deletes are actually done in the original table !) :

create view items2 as select id,name,weight from items;

There is just one limitation : I do not know how to specify default values for the view fields (for inserts) different from the ones in "items". It is not a blocking point as the default values in "items" could be changed with no impact on the existing data or on data inserted by specifying all the fields at once. It is just a matter of everyone aggreeing on the "best" defaults. You could even have your own patch you apply to your DB to change those defaults for your own installation. You apply it everytime you reload the table from the PEQ dump, and you are set.

Would that solution be good for you ?

[Kayot]

The items table is generated from a delimited text file. The seperator is the bar "|" symbol.

If you want to parse it into your db, make a vb.net program with the following Sub: (Some assembiliy required)

Public Sub Load_ItemTable()
    Dim objReader As New System.IO.StreamReader(items.txt)
    Do While objReader.Peek() <> -1
        Dim Split() as string = objReader.ReadLine().Split("|")
        Console.Writeline("This is the SQL command, You'll have to make me.")
    Loop
End Sub

The fields are out of order so you'll need to define values, plus there are more fields than the db takes into account. Hence why I left the above command empty.

As for http://www.eqemulator.net/wiki/wikka...uDBSchemaitems before the wiki went down, I did a bunch of the data thats in there. Mind you I don't get any credit for it at all (Due to wiki loss), which is part of the reason I no longer work on the wiki. I also made a new DB diagram http://kayosblade.googlepages.com/EQDBLayot.png that shows all the newer connection, mind you its about 1.5 years old and some of the stuff isn't link right now.

I think if we do nothing else to the DB, if we don't remove unused columns. We should at the very least, define the datatypes in the columns. Stop calling bit(1)/BOOLEAN's Integers. It takes more space in memory to store the number one then it does to store the bit 1. The number one is (0001), thats four bits for every row. Take that over 30ish columns and your waisting 90 bits per row over 62263 rows. Thats 5,603,670 bits of unused space just on unused columns that don't do anything at all. If you get rid of the column all together and you'll reclaim 7,471,560 bits or 1,867,890 bytes in ram just by removing the unused entries. Then retype all the other fields, add in defaults and god only knows how much space will be saved. Not to mention heads ache from programmers going, "What the hell does that do?" We should also add comments that explain each column (Hook it into the table design, not the tables information) so no one has to go to the wiki every time they forget what a column does.

And yes, I'll do it if it will be used. Add I have to do it edit the table structurer, the data should be fine. I'm considering removing the extra columns and seeing how it affects the emu.