qep_enum_tables

qep_enum_tables.md

Notes for myself:

See opengisch/QgisModelBaker#511 (comment)

And qgis/QGIS#43013

And https://www.qgis.org/en/site/forusers/visualchangelog320/index.html#feature-read-field-domains-from-datasets

QGIS Enhancement: Title

Date 2022/12/12

Author Dave Signer @signedav

Contact david at opengis ch

maintainer @signedav

Version QGIS 3.24

Summary

When having enumeration values stored in lookup-tables,the use of expressions is complex in syntax and intense in computing.

As a reference to the lookup-table usually their id is used as foreign key. So we need to compare the current attribute value with the lookup-tabel's id. So we need to get this id according to the enumeration value like this:

attribute(get_feature([...])

The goal of this proposal is to improve readability and performance of this type of expressions by making those enumeration values (and ids) available as project variables.

Proposed Solution

Being able to add project variables with a type called "enumtable" where we specify the table, a key column and a value column.

The values are read from the table and written into a "key/value" map, stored as value of this project variable.

This map is always generated and not saved in the project file. Means we will have an internal helper method refreshLookupCache(layer) that is called during project load or when the configuration changes during runtime.

Like this, in the expression those values would be available with @projectvariable[value] to receive the key.

Example

Table "building_table":

tid |        name       | building_type |
----+-------------------+----------------
  1 | House in the Green|           102 | 
  2 | Big Glass House   |           104 | 
  3 | Brutalist Block   |           103 | 

Table "building_type_table":

  id | description | info   |
-----+-------------+---------
 101 | Office      | xy     | 
 102 | Appartement | xy     | 
 103 | Shop        | xy     | 
 104 | Public      | xy     | 

Expression we can (currently) use to check the color description in the table "building_table"

attribute( get_feature( 'building_type_table', 'id', building_type), 'description') = 'Office'

Being able to add project variables of type "enumtable"

• Add Project Variable called "buildingtypes" let's you choose a type (VariableDefinition) "String"/"Enumtable"/...
• When "Enumtable" is chosen you can set a Table (building_type) a key column (id) and a value column (description) -> this is stored to the VariableDefinition of the project variable.
• As well a map is stored as the value of the projet variable: ['Office':101,'Appartement':102,'Shop':103,'Public':104]

The variable "buildingtypes" is stored in the project with the type "Enumtable" and the configured table "building_type", key column "id" and value column "description". The map wont be stored in the project - it will be filled at runtime (when loading the project or changing the configuration).

Expression we can (in te future) use to check the building_type in the table "building_table"

building_type = @buildingtype['Office']

API proposal

struct VariableDefinition {
  QString name;     // buildingtype
  QString type;     // EnumTable
  QVariantMap config;  // {layer: "building_type", key: "id", value: "description"}

  // helpers to persist settings,
  writeXml();
  static readXml();
};

QList<VariableDefinition> QgsProject::variableDefinitions();

// updates based on the name
QgsProject::setVariableDefinition( const VariableDefinition& )

// delete
QgsProject::deleteVariableDefinition( const QString &name ) 

// Adjust to also return generated values --> check how other generated values are handled here:
QgsExpressionContextUtils::projectScope()

Open questions

• Should we connect to edit signals on enumumeration table layers (lookup table) to update the cached map?
• What is it the correct point in time to load the layer data (while loading the project might be too early, better post loading probably)?

Performance Implications

Could have impacts on project loading but is faster than get_feature functions in the expressions.

Backwards Compatibility

No impacts.

Votes

(required)

API proposal:

struct VariableDefinition {
  QString name; // building_type
  QString type; // EnumTable
  QVariant config; // {layer: "building_table", key: "building_type", value: "name"}

  // helpers to persist settings, 
  writeXml();
  static readXml();
};

QList<VariableDefinition> QgsProject::variableDefinitions();

// updates based on the name
QgsProject::setVariableDefinition( const VariableDefinition& )

// delete
QgsProject::deleteVariableDefinition( const QString &name ) 

// Adjust to also return generated values --> check how other generated values are handled here:
QgsExpressionContextUtils::projectScope()

Adjusting the loadProject to also pre-load / cache tables.

Open questions:

• should we connect to edit signals on enum table layers to update the cached map?
• Need to figure out the correct point in time to load layer data (while loading the project might be too early, better post loading probably)

Thanks. Considered that. You might want to have another look. Is something still unclear / missing?

"The use of expressions is annoying and computing intensive when having enumeration values stored in lookup-tables." > I think the term "annoying" in here could be improved.

Something like:

The use of expressions is complex in syntax and intense in computing when having enumeration values stored in lookup-tables.

---

"This means we adjust the loadProject to pre-load/cache the lookup table keys and values in the project variables."

When new lookup tables are configured during runtime, loadProject is not called again. So we should create an internal helper method refreshLookupCache(layer) (or similar) that is called during project load and when the configuration changes during runtime.

Re-reading: the paragraph that it's loaded in loadProject and the paragraph in "open questions" are contradictory in that the first proposes a particular implementation which the second one is unsure about. Which one should we preserve?

I don't think they are contradictory. One proposes where to do it and the second one is a question if the proposed way is the best. But we can remove the paragraph. It's written where it should do it above.

On saving those variables or on opening the project, the values are read from the table and written into a map, stored as value of this project variable.

There we can append the information you wrote here https://gist.github.com/signedav/275f126aead6e3e95d16039048386cc3#gistcomment-4001998

updated @m-kuhn

Sorry for the nitpicking, a couple of more recommendations:

• update with syntax highlighting to improve readability:

```sql
attribute( get_feature( 'building_type_table', 'id', building_type), 'description') = 'Office'
```

• change the API config to QVariantMap
• When "Enumtable" is chosen you can set a Table (building_type) a key column (id) and a value column (description) -> this is stored to the type of the project variable. > how about replacing "type" with "VariableDefinition" in here for consistency?
• "it will be filled on loading of the project." > "it will be filled at runtime (when loading the project or changing the configuration)"
• "It would be awesome to have those enumeration values (and ids) in project variables, updated on project load (in case they change in the backend)." > "The goal of this proposal is to improve readability and performance of this type of expressions by making them available as project variables."

Thanks. Considered it (changed your last sentence a bit).

Thanks @m-kuhn QEP is opened qgis/QGIS-Enhancement-Proposals#240