Schema Documentation Enhancement Request

Having the database schema, or at least element data types/lengths (e.g. INTEGER, VARCHAR(255), etc.) would be exceptionally helpful). It's not terrible to try and infer some of the data types/lengths, but not having to guess would be even better.

Along slightly similar lines, knowing which (if any) fields are localized (e.g. will we always get "male", or can you get "masculins"?) would be another useful feature.

Is there a query to return that information currently, or documentation that I'm overlooking?

 

Not yet, beyond the "constants" query.  I'm working on more complete information.  It will be somewhat dynamic, but we should be able to put more structure around it than what is currently there.

One thing to know, is that there is not a schema in the classic sense.  We have conventions and patterns, but there is actually no formal schema to the data.  Obviously, some of the data comes from a database (or multiple sources), but the format is loosely defined so that the game teams may have the most flexibility in what the choose to surface.

-dan

 

I assume the schema question is probably best answered by Zoltaroth in this case.  The reason for the question is, a few months ago both RosterMaster projects broke when the characterID became too large to fit in a 16-bit signed integer value, forcing us to update our own DB schema to use a bigint.  So just to avoid having similar issues in the future with the other ID fields related to EQ2, it would be useful to know which field is a 16-bit signed int, which is a 32-bit unsigned, what is the max size of the strings, etc...

 

I don't think he's asking for a SQL schema as much as a "data range" schema, per type.

Most of this can be guessed.  Anything with "id" I made into a 8-byte integer, for instance, and anything that seems likely to eventually exceed 4 byte unsigned I went ahead and did the same.  Floats I round when importing anyway, to one decimal or perhaps two, to match in-game data.  I don't think people care if their crit chance is 150.1 or 150.100000000001

Perhaps we can start an informal schema of community recommended sizes to help bootstrap this?  If only we had a wiki

 

I think what we're really asking for is a data dictionary vs. database schema.  A document that gives field names, meanings, data types, and sizes.  Most of it we can guess at.  It's just bad when we guess incorrectly.