View source for KDb/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


== 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, KDb, 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 KDbLookupFieldSchema class. Read this documentation for explanation of the data members.

LookupFieldSchema objects can be owned by KDbTableSchema 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 KDB_LOOKUP_FIELD_DEFAULT_HEADERS_VISIBLE)
   </show-column-headers>
   <list-rows>
    <number>1..100</number> (default is 8 as defined by
                             KDB_LOOKUP_FIELD_DEFAULT_LIST_ROWS in
                             KDbLookupFieldSchema.h; the maximum = 100
                             is defined by KDB_LOOKUP_FIELD_MAX_LIST_ROWS)
   </list-rows>
   <limit-to-list>
    <bool>true/false</bool> (default is true, as defined by
                             KDB_LOOKUP_FIELD_DEFAULT_LIMIT_TO_LIST)
   </limit-to-list>
   <display-widget>
    combobox/listbox (default is combobox, as defined by KDB_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 KDB_LOOKUP_FIELD_DEFAULT_* macros in KDbLookupFieldSchema.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:

# 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.
# Lookup Column Wizard, allowing the same action step by step. Documented at [[Kexi/Assistants/Lookup Column Assistant]].