KexiDBDesign:

Extended Table Schema Information

 started: april 2006, jstaniek

Table of Contents    Introduction    Other subelements of the "field" tag    Notes    TODOs

Introduction

From time to time we want to add some properties for table schema. We want to avoid extending kexi_ _fields or kexi_ _objects schemas, so instead, for each table, one optional entry has been added within kexi__objectdata table, under {TABLE_ID} id and "extended_schema" subid:

 INSERT INTO kexi__objectdata o_id, o_data, o_sub_id
    ({TABLE_ID}, {DATA}, "extended_schema");

{DATA} is an UTF-8-encoded string contining well defined XML like this:

 <!DOCTYPE EXTENDED_TABLE_SCHEMA>
 <EXTENDED_TABLE_SCHEMA version="1">
 <field name="...">
  <property name="visibleDecimalPlaces">
   <number>4</number>
  </property>
  [..more properties and other elements..]
 </field>
 [..]
 </EXTENDED_TABLE_SCHEMA>

The EXTENDED_TABLE_SCHEMA element defines version, that can be used for extensibility.

Other subelements of the "field" tag

• <lookup-column> - zero or one lookup column schema; see KexiDBLookupColumns

Notes

• The extended information is stored in Connection::createTable() and retrieved from the backend in Connection::setupTableSchema() using Connection::loadExtendedTableSchemaData() internal method. Internally, the data stored by calling:

 Connection::storeDataBlock(
  tableSchema->id(), qdomDocument.toString(1), "extended_schema" )

and schema retrieving is performed by calling:

 Connection::loadDataBlock(
  tableSchema->id(), string, "extended_schema" )

• On table dropping, Connection::dropTable() calls:

 Connection::removeDataBlock( tableSchema->id(), "extended_schema")

• Only properties having values that differ from defaults are stored. For example, since default for "visibleDecimalPlaces" is -1, the property is not saved if its value is -1.

This implies that if there is no at least one extended schema property value changed in the entire table, saving that table's extended schema will cause just deleting the "extended_schema" data block. Such a strategy is safer for future maintaining extensions and moreover, it is similar to the strategy implemented in Qt UI XML format used by uic/QtDesigner.

• Most properties within the extended schema will not affect "the main" table schema, i.e. changing them will not force running ALTER TABLE commands on the table. For example, changing display parameters (like visible decimal places of a field) or input parameters (like input mask) will not be charmful for "the main" table schema. We will use this rule to optimize saving changes to tables.

OTOH, some changes made properties will force to process the existing table data to make sure the property value is valid for this content. Example: validation rule or input mask.

• For debugging, you can run "ksqlite dbnname.kexi" and type the following to show extended schema for all tables:

 select kexi_ _objects.o_id, o_name, o_data
  from kexi_ _objects, kexi_ _objectdata
  where kexi_ _objectdata.o_sub_id=="extended_schema"
   and kexi_ _objects.o_id=kexi_ _objectdata.o_id;

TODOs

TODO: In particular, to acheive upward and backward compatibility, future versions of KexiDB can seek for EXTENDED_TABLE_SCHEMA element of a newest possible version and save the information two or more times within EXTENDED_TABLE_SCHEMA element with different version, e.g. if currently the newest ver is 3:

 <EXTENDED_TABLE_SCHEMA version="1">
  ...information compatible with format v>=1...
 </EXTENDED_TABLE_SCHEMA>
 <EXTENDED_TABLE_SCHEMA version="2">
  ...information compatible with format v>=2...
 </EXTENDED_TABLE_SCHEMA>
 <EXTENDED_TABLE_SCHEMA version="3">
  ...information compatible with the most current format...
 </EXTENDED_TABLE_SCHEMA>

Thus, even if sematic has been changed in version i, chances are we can save and load it for all versions of the KexiDB. Versions <i can just ignore <EXTENDED_TABLE_SCHEMA version="i">.