KexiDBDesign:

Lookup Columns

 started: july 2006, jstaniek
 status: core functionality implemented in 100% for 1.1.1,
         extensions (assistant, more properties) to be implemented for 2.x

Table of Contents    Introduction    The Schema Class    Schema Storage    GUIs for Setting up the Lookup Column

Introduction

What is a Lookup Column? Imagine there is a relationship between two tables cars and persons. In details, it is a relationship between cars.owner field and persons.id field. Both fields are numeric: persons.id is a unique number for a person (primary key), not very interesting for a database user; cars.owner is a number that refers (as a foreign key) to the appropriate value of persons.id.

However, in our cars table, we don't want to see the numberic identifier, or require database user to enter it by hand. Instead, we would rather like to see, say, the surname of the person that is the owner of a given car. It's largely about usability of the final database product created using Kexi.

To do this, KexiDB, a database layer should contain a data structure and a logic built in that allows to define such a lightweight relation, lookup column. The relation appears on the screen as some sort of combo box widget.

The Schema Class

Data model is defined by KexiDB::LookupFieldSchema class. Read this documentation for explanation of the data members.

LookupFieldSchema objects can be owned by KexiDB::TableSchema class, each is related to a table field, so we have the TableSchema::lookupFieldSchema( Field& field ) method to find.

void TableSchema::setLookupFieldSchema( const QString& fieldName, LookupFieldSchema *lookupFieldSchema) setter is used internally on schema loading and applying table design).

 todo: lookup columns for queries are planned

Schema Storage

XML data is used to serialize the data structure. This enables future extensions.

 <lookup-column>
  <row-source>
   {empty}
   | <type>table
           |query
           |sql
           |valuelist
           |fieldlist
     </type> #required because there can be table and query with
             # the same name
             # "fieldlist" (basically a list of column names
             # of a table/query,
             # "Field List" as in MSA)
   <name>string</name> #table/query name or a KEXISQL SELECT QUERY
   <values>
    <value>...</value> #for "valuelist" type
    <value>...</value>
           ...
   </values>
  </row-source>
  <bound-column>
   <number>number</number> #in later implementation there can be more columns
  </bound-column>
  <visible-column> #a column that has to be visible in the combo box
   <number>columnnumber1</number>
   <number>columnnumber2</number>
   ...
  </visible-column>
  <column-widths> #column widths, optional
   <number>int</number> # -1 means 'default'
   ...
   <number>int</number>
  </column-widths>
  <show-column-headers>
   <bool>true/false</bool> (default is false as defined
                            by KEXIDB_LOOKUP_FIELD_DEFAULT_HEADERS_VISIBLE)
  </show-column-headers>
  <list-rows>
   <number>1..100</number> (default is 8 as defined by
                            KEXIDB_LOOKUP_FIELD_DEFAULT_LIST_ROWS in
                            kexidb/lookupfieldschema.h; the maximum = 100
                            is defined by KEXIDB_LOOKUP_FIELD_MAX_LIST_ROWS)
  </list-rows>
  <limit-to-list>
   <bool>true/false</bool> (default is true, as defined by
                            KEXIDB_LOOKUP_FIELD_DEFAULT_LIMIT_TO_LIST)
  </limit-to-list>
  <display-widget>
   combobox/listbox (default is combobox, as defined by
                     KEXIDB_LOOKUP_FIELD_DEFAULT_DISPLAY_WIDGET)
  </display-widget>
 </lookup-column>

As the lookup column is related to a given table field, XML data is stored as a subelement in the <field> element of the extended table schema information.

 TODO: update when lookup columns for queries appear

Only properties having values that differ from defaults are stored. For example, since default for "show-column-headers" is false, the property is not saved if its value is false. Defaults are defined by KEXIDB_LOOKUP_FIELD_DEFAULT_* macros in kexidb/lookupfieldschema.h.

 TODO: in the future defaults will be overriden by the global app
       or project settings

Loading of the XML string is performed by Connection::loadExtendedTableSchemaData() in the static function LookupFieldSchema* LookupFieldSchema::loadFromXML(const QDomElement& lookupEl). Saving is provided by void LookupFieldSchema::saveToXML(LookupFieldSchema& lookupSchema, QDomElement& parentEl).

GUIs for Setting up the Lookup Column

There are two ways of how user can set up a lookup column:

1. Table Designer's side pane (another, "Lookup column" tab beside the property editor).
   
   
   
   There is an example database provided containing lookup columns (in "Ownership" table) -  download it.
   
   
2. Lookup Column Wizard, allowing the same action step by step. Already documented in details in the Developer Jobs section. To be implemented.