Kexi/Assistants/Lookup Column Assistant

Design document for KEXI Issue #124

Objectives

The Lookup Column Assistant allows you to add a database relation for a single column. Once such relation is defined (one-to-many), it allows user to select one of many values for a given field using some sort of combo box. The task is to deliver intelligent GUI for this assistant. This task doesn't include a code for creation the column itself.

Note

Original wiki page: [1]

Pages

Page: WELCOME. Select lookup type

There are two types of definition for the "many" side of this relation:

• type#1: Picking an existing table or query, so their rows will be displayed for selection
• type#2: Simple version: Entering rows by hand, what creates a simple dictionary.

The 1st type is more generic, and allows to reuse once defined data schemas.

Sequence Diagram
 WELCOME:SelectLookupType ->
    (type#1)-> TableOrQueryDataSource Page -> FieldsSelection Page -> SortOrder Page ->
            ColWidth Page -> SelectBindingColumn Page -> EnterColumnName Page -> END Page
    (type#2)->EnteringDataByHand Page -> SelectBindingColumn Page ->
           EnterColumnName Page -> END Page

Notes:

• SelectBindingColumn and EnterColumnName pages are identical for either 1st and 2nd type.

TableOrQueryDataSource Page (type #1)

• For tables/queries list, general-purpose Kexi widget should be used (not yet implemented). Only one item can be selected here.
• Unlike presented on the screenshot, do not add "tables/queries/Both" radio buttons, but tables and queries should be displayed on the list (2 columns), eg.:

 Type    Name
 ----    ----
 Table   [icon]Table1
 Table   [icon]Table2
 Query   [icon]Query1

The 2nd column contains an icon ("table" or "query").

FieldsSelection Page (type #1)

Inherit from KActionSelector class here to implement KexiFieldSelectionWidget class.

Note: this widget can be reused later in other areas of KEXI.

SortOrder Page (type #1)

Do not implement this page exactly the way you see on the screenshot below. Use KexiTableView widget together with not-db-aware KexiTableViewData. See formeditor/connectiondialog.cpp or plugins/tables/kexialtertabledialog.cpp for example use. Following columns should be visible:

 Field    Sorting
 -----    -------

Notes:

• The "Field" column shouldn't be editable.
• The sorting column should be a combo box (just like field type combo in kexialtertabledialog), having three possible items: "No sorting" (the default), "Ascending" and "Descending".
• The table view shouldn't allow insertions.

ColWidth Page (type #1)

Like for the SortOrder page, use here KexiTableView widget together with KexiTableViewData. But this time, the table view is db-aware -- should be filled with data from a table or query selected on TableOrQueryDataSource page.

The table view shouldn't allow data editing or insertions, only setting column width.

SelectBindingColumn Page (type #1)

RULE: Skip this page if either:

• there's only one field in the selected lookup table/query, or
• the table or query has a single-field primary key defined.

Note: currently we're not supporting bindings using more than one fields at a time.

EnterColumnName Page (type #1)

EnteringDataByHand Page (type #2)

This page is similar to ColWidth page. Use the KexiTableView widget together with KexiTableViewData. But this time, the table view is not db-aware, because the values will be entered by hand.

Notes:

• By default, set "Number of columns" field to 1. This value should be in a range (1..32). Use KIntNumInput class for this field.
• Create tableview with only one column and no rows. The tableview should allow row adding, deleting and editing.
• For "Number of columns" widget, on focus-out event and valueChanged(int) signal, update table data by adding or removing columns within it. Do it by modifying KexiTableViewData. Existing rows should be preserved, if possible.

SelectBindingColumn Page (type #2)

Identical to the SelectBindingColumn type #2 (see the screenshot and notes)

EnterColumnName Page (type #2)

Identical to the EnterColumnName type #2 (see the screenshot and notes)

Implementation Notes

KF/Kexi Classes

• Use KAssistantDialog for the assistant
• Use KF widgets instead of Qt when possible.
• Use Kexi widgets if possible, and KDb API to get show required data to a user

TODO......

Data structures

• The assistant should take KexiLookupColumnData structure at input and modifies just this structure. Do not create anything in the database backend. On accepting KAssistantDialog (i.e. when Finish was clicked) that structure will be used to create given column data.
• You should do your best to assert the data structure always contains valid information.
• As long as the dialog is modal, you do not need to care about errors caused by modification of the table schema. Note that this is true is only single user accesses the project. So the column creation code that will use the data structure will perform all necessary validity checks needed.

See also

• Lookup Tables in Access: [2] or [3] (pages 11+).