What additional metadata could we define for improved interoperability?

[mikepizzo]

Column Expression? Database Description?

What about DateTime Precision?

Encoding? so the client can determine whether they can use SQL_C_CHAR/SQL_C_WCHAR? Or require drivers to return in encoding advertised by driver?

[monty241]

entityfield.pdf We are working on an ODBC-driver for our SQL engine that have backing stores in REST, XML, Teradata, etc. It works great for .NET applications, but ODBC is hard, due to large legacy and variants found even when not covering Linux or Mac.

This is a full list of the non-trivial properties we can have on a column in our lowest layer, it is more functional than the current ODBC implementations and I understand the question, but in fact our developer community would benefit from publishing more metadata also through ODBC instead of querying the platform-dependent native metadata:

    /// <param name="dotnetDataType">The .NET data type of the field.</param>
    /// <param name="databaseDataType">The data type as exposed by the database system. Oracle for example could give 'clob' and 'varchar' to define a 'string'.</param>
    /// <param name="maxLength">The maximum size of a field.</param>
    /// <param name="fetchByDefault">True if the field is fetched by default, or false if it is being fetched when accessed.</param>
    /// <param name="documentation">Documentation of the field.</param>
    /// <param name="example">Example of the field.</param>
    /// <param name="displayControl">The control to use in to display the field.</param>
    /// <param name="precision">The precision in case of a numeric field.</param>
    /// <param name="scale">The scale in case of a numeric field.</param>
    /// <param name="labelSingular">The translatable label in singular.</param>
    /// <param name="labelPlural">The translatable label in plural.</param>
    /// <param name="labelSingularPrefix">The translatable label prefix in singular.</param>
    /// <param name="labelPluralPrefix">The translatable label prefix in plural.</param>
    /// <param name="nullable">True if the field is nullable. False if not.</param>
    /// <param name="derivedValue">True if the field contains a value derived by an expression. False if not.</param>
    /// <param name="displayInFilter">True if the field should be included in a search control. False if not.</param>
    /// <param name="displayInLOV">True if the field should be included in a picklist control. False if not.</param>
    /// <param name="displayInRecord">True if the field should be included in a single record control. False if not.</param>
    /// <param name="displayInResults">True if the field should be included in a multiple records/list control. False if not.</param>
    /// <param name="influencesInsert">True if the field contains a value stored in the database. False if not.</param>
    /// <param name="userChangeable">True if the field's value may be changed at any time by a human user with sufficient access rights. False if not.</param>
    /// <param name="displayOrder">Relative position of the field compared to other fields when displayed.</param>
    /// <param name="displayLengthAverage">Average length in characters of the displayed field's value.</param>
    /// <param name="displayLengthSD">Standard deviation of length in characters of the displayed field's value.</param>
    /// <param name="labelHeading">Label of an optional heading to be inserted just before this field when displayed.</param>
    /// <param name="checkSpelling">True if the value of the field should preferably be checked for spelling and grammar errors in a user interface. False if not.</param>
    /// <param name="minimumLength">The minimum required length for a text field.</param>
    /// <param name="minimumLengthPadding">The side on which padding has to be applied if the minimum length is not met.</param>

These are supplemented for a column by the following properties when we use our own metadata repository (no .NET documentation available, so manually added): fields (created by, in, when, session & last updated when, by, in, session) of the metadata entry

• Whether and where the column is included in the primary key (technical key, such as 123123)
• Whether and where the column is included in the natural key (business key, such as "Lexus", "RX")
• In what order the field was added to the metadata to aid data model migrations.
• What the conversion rules are (only simplistic, like defaulting a value or basing it upon other contents of the row).
• Whether the contents and metadata of the column are intended for the user to configure (flexible fields, user extensible fields or whatever apps call them)
• Whether the contents are confidential based upon the applicable regulations and laws (this helps apps when copying/extracting the data decide to obfuscate the contents, we work a lot in banking and insurance and not always the data is obfsucated correctly for instance when copied to test or a relatively open datawarehouse)
• Description and example forinclusion in a reference manual (mor eelaborate texts in MD).

From a friended con-collegeau metadata repository party I know they open up similar properties to their developers community on the data model.

On some platforms we've also metadata on partitioning: when the data is physically partitioned (across servers or schemas or an ORG_ID column or whatever), there are some indicators what columns are involved in the partitioning.

I will add extra comment regarding tables and encoding.

[monty241]

For precisions/scale/size and encoding, I would be happy when ODBC in case of character sequence would be able to incorporate some standardized encoding information on the column metadata.

The metadata itself I think should always either be ASCII 7-bit or UTF8 or UTF16 with legacy additions, advertising somewhere how to interpret them differently when necessary.

For datetime and other non-character types I think you could piggy-back on encoding the precision in some standards. The value space from which the values are taken differs between UTF8 and ASCII 7-bit (although it a discrete space), and the same holds for date with seconds precision, milli or even more accurate.

[monty241]

ObjectDefinition.pdf

For a table we utilize a base definition, which includes (see attached PDF):

• Privileges (simplified)
• Hierarchy, assuming data container (which is a physical database platform) -> catalog -> schema -> table
• Simple statistics to allow choosing the right approach to retrieve data for an SQL engine such as estimated number of rows in the table. Column information with histograms would be better, but these are almost never available :-(.

When supplemented by a metadata repository, we also use:

• Relationships (when possible with cardinality), this helps choosing the right approach for an SQL engine for join execution.
• Data category: seed / reference / transactions / other. Seed is shipped by developer, reference is set up by application administration, transactions grows forever, other: the rest (seldom anything).
• Metadata audit information.
• List of default columns to be used when the table is referenced from some other table using a relationship, for instance a description and the fields of the natural key. This helps generators of user interfaces to choose the right set of data to display automatically.
• List of default columns to be used when the table (or view) is shown as a flat list.
• Derived indicator: is the contents automatically calculated by an automated process such as a daily job or a list of triggers. This allows metadata-driven applications to know whether they should never offer a user the option to change data unless the user is an app administrator or dba.
• Interface indicator: Whether the table is loaded from external sources and then processed. These are typically tables that grow very large and get emptied again, which can cause problems with the wrong statistics being used for SQL optimization.
• Partitioned yes/no.
• time travel: some tables or views allow time travel to the past by changing the point-in-time point of view. Does this table support it?
• time travel name: some tables or views allow time travel but not through their own name. What alternative name to use to get a point-in-time view of the data (typically banking, compliance, etc.)