Customizing Table Designer Column Types and Constraints

The Table Designer extension field types are built with:

• a tdtype string value identifying the type e.g. "text"

• a corresponding ColumnType subclass that defines the DataStore column type, template snippets and validation rules

• a list of ColumnConstraint subclasses that apply to this type to extend the form templates and validation rules

For example when a field is defined with the “Integer” type, the field’s tdtype is set to "integer", the ColumnType subclass is IntegerColumn and the RangeConstraint class applies to limit the minimum and maximum values.

IntegerColumn sets the DataStore column type to "int8" to store a 64-bit value and adds a rule to check for integers when entering values in Excel templates with ckanext-excelforms.

New column types may be defined and existing column types replaced or removed by an extension implementing the IColumnTypes interface.

RangeConstraint adds minimum and maximum form fields to the data dictionary form, stores those values as tdminimum and tdmaximum in the field and applies a rule to ensure that no values outside those given will be accepted by the DataStore database.

RangeConstraint is separate from IntegerColumn to allow disabling or replacing it and because it applies equally to other types.

New constraints may be defined and existing constraints may be applied to new types or removed from existing types by an extension implementing the IColumnConstraints interface.

Custom Column Type Example

Let’s create a new type for storing a user rating from 1-5.

class StarRatingColumn(IntegerColumn):
    """Example 1-5 star rating column"""
    label = _('Star Rating')
    description = _('Rating between 1-5 stars')
    datastore_type = 'int2'  # smallest int type (16-bits)
    form_snippet = 'choice.html'
    view_snippet = 'choice.html'

    def choices(self):
        return {
            '1': '★',
            '2': '★★',
            '3': '★★★',
            '4': '★★★★',
            '5': '★★★★★',
        }

    def choice_value_key(self, value: int | str) -> str:
        return str(value) if value else ''

    def sql_validate_rule(self):
        error = _('Rating must be between 1 and 5')
        return f'''
        IF NOT NEW.{identifier(self.colname)} BETWEEN 1 AND 5 THEN
            errors := errors || ARRAY[[
                {literal_string(self.colname)}, {literal_string(error)}]];
        END IF;
        '''

For space efficiency our values can be stored using numbers 1-5 in the smallest PostgreSQL integer type available: int2.

We use the choice.html form snippet with a choices() method to display a drop-down in the web forms showing 1-star (★) to 5-star (★★★★★) options.

ckanext-excelforms uses the same choices() method to populate a drop-down and reference information with our options in Excel templates.

We’re storing an integer but comparing it to string keys in the form so we define a choice_value_key() to convert values before comparing.

We enforce validation server-side with sql_validate_rule(). Here we return SQL that checks that our value is BETWEEN 1 AND 5. If not it adds an error message to an errors array. This array is used to return errors from datastore_upsert() and to display errors in the web forms.

Warning

Generating SQL with string operations and user-provided data can allow untrusted code to be executed from the DataStore database. Make sure to use identifier() for column names and literal_string() for string values added to the SQL returned.

SQL rules from all the column types and constraints in a table are combined into a trigger that is executed as a data change trigger in the DataStore database. Almost any business logic can be implemented including validation across columns or tables and by using PostgreSQL extensions like PostGIS or foreign data wrappers.

Note

For column types and constraints we use a dummy gettext function _() because strings defined at the module level are translated when rendered later.

def _(x: str):
    return x

Next we need to register our new column type with an IColumnTypes plugin:

class ExampleIColumnTypesPlugin(plugins.SingletonPlugin):
    plugins.implements(IColumnTypes)

    def column_types(self, existing_types: dict[str, Type[ColumnType]]):
        return dict(
            existing_types,
            star_rating=StarRatingColumn,
        )

column_types() adds our new column type to the existing ones with a tdtype value of "star_rating". Enable our plugin and add a new star rating field to a Table Designer resource.

Custom Column Constraint Example

Let’s create a constraint that can prevent any field from being modified after it is first set to a non-empty value.

We create a templates/tabledesigner/constraint_snippets/immutable.html snippet to render an “Immutable” checkbox in the Data Dictionary form:

{%- call
  form.checkbox('fields__' ~ position ~ '__tdimmutable',
    label=_('Immutable'),
    id='field-f' ~ position ~ 'immutable',
    checked=data.get('tdimmutable', field.get('tdimmutable', '')),
    error=errors.tdimmutable,
    value='true'
  )
  -%}
  {{ form.info(
    text=_('The value may be set once then not changed afterwards')
  )}}
{%- endcall %}

When checked the ImmutableConstraint will apply for that field:

class ImmutableConstraint(ColumnConstraint):
    """Allow a field to be set once then not changed again"""
    constraint_snippet = 'immutable.html'
    view_snippet = 'immutable.html'

    def sql_constraint_rule(self):
        if not self.field.get('tdimmutable'):
            return ''

        icolname = identifier(self.colname)
        old_is_empty = self.column_type._SQL_IS_EMPTY.format(
            value='OLD.' + icolname
        )

        error = _('This field may not be changed')
        return f'''
        IF NOT ({old_is_empty}) AND NEW.{icolname} <> OLD.{icolname} THEN
            errors := errors || ARRAY[[
                {literal_string(self.colname)}, {literal_string(error)}]];
        END IF;
        '''

    @classmethod
    def datastore_field_schema(
            cls, td_ignore: Validator, td_pd: Validator) -> Schema:
        """
        Store tdimmutable setting in field
        """
        boolean_validator = get_validator('boolean_validator')
        return {
            'tdimmutable': [td_ignore, boolean_validator, td_pd],
        }

We store the tdimmutable Data Dictionary field checkbox setting with datastore_field_schema().

In sql_constraint_rule() we return SQL to access the old value for a cell using OLD.(colname). ColumnType subclasses have an _SQL_IS_EMPTY format string, normally used to enforce sql_required_rule(). We can use that string to check if a value was set previously for this column type.

We add an error message to the errors array if the old value was not empty and the new value NEW.(colname) is different.

Next we need to register our new column constraint and have it apply to all the current column types:

class ExampleIColumnConstraintsPlugin(plugins.SingletonPlugin):
    plugins.implements(IColumnConstraints)
    plugins.implements(plugins.IConfigurer)

    def update_config(self, config: CKANConfig):
        add_template_directory(config, "templates")

    def column_constraints(
            self,
            existing_constraints: dict[str, List[Type[ColumnConstraint]]],
            column_types: dict[str, Type[ColumnType]],
            ) -> dict[str, List[Type[ColumnConstraint]]]:
        """Apply immutable constraint to all types"""
        return {
            tdtype: existing_constraints.get(
                tdtype, []
            ) + [ImmutableConstraint] for tdtype in column_types
        }

We add our extension’s template directory from update_config() so that the checkbox snippet can be found.

In column_constraints() we append our ImmutableConstraint to the constraints for all existing column types.

Note

Plugin order matters here. If we want the ImmutableConstraint to apply to a new column type this plugin needs to come before the plugin that defines the type.

Interface Reference

class ckanext.tabledesigner.interfaces.IColumnTypes

Custom Column Types for Table Designer

column_types(existing_types: dict[str, Type[ColumnType]]) → dict[str, Type[ColumnType]]

return a {tdtype string value: ColumnType subclasses, …} dict

existing_types is the standard column types dict, possibly modified by other IColumnTypes plugins later in the plugin list (earlier plugins may modify types added/removed/updated by later plugins)

ColumnType subclasses are used to set underlying datastore types, validation rules, input widget types, template snippets, choice lists, examples, help text and control other table designer features.

class ckanext.tabledesigner.interfaces.IColumnConstraints

Custom Constraints for Table Designer Columns

column_constraints(existing_constraints: dict[str, List[Type[ColumnConstraint]]], column_types: dict[str, Type[ColumnType]]) → dict[str, List[Type[ColumnConstraint]]]

return a {tdtype string value: [ColumnConstraint subclass, …], …} dict

existing_constraints is the standard constraint dict, possibly modified by other IColumnConstraints plugins later in the plugin list (earlier plugins may modify constraints added/removed/updated by later plugins)

The list of ColumnConstraint subclasses are applied, in order, to all columns with a matching tdtype value. ColumnConstraint subclasses may extend the design form and validation rules applied to a column.

Column Type Reference

See also

Complete source code of these column type classes

ColumnType base class

class ckanext.tabledesigner.column_types.ColumnType(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

ColumnType subclasses define:

• PostgreSQL column type used to store data

• label, description and example value

• pl/pgsql rules for validating data on insert/update

• snippets for data dictionary field definitions and form entry

• validators for data dictionary field values

• choice lists for choice fields

• excel format and validation rules for ckanext-excelforms

Use IColumnTypes to add/modify the column types available.

label = 'undefined'

description = 'undefined'

datastore_type = 'text'

DataStore PostgreSQL column type

form_snippet = 'text.html'

snippet used for adding/editing individual records

view_snippet = None

snippet used for resource page data dictionary extra info

html_input_type = 'text'

text.html form snippet input tag type attribute value

excel_format = 'General'

ckanext-excelforms column format

_SQL_IS_EMPTY = "({value} = '') IS NOT FALSE"

used by sql_required_rule

sql_required_rule()

return SQL to enforce that primary keys and required fields are not empty.

sql_validate_rule()

Override to return type-related SQL validation. For constraints use ColumnConstraint subclasses instead.

classmethod datastore_field_schema(td_ignore: Validator, td_pd: Validator) → Schema

Return schema with keys to add to the datastore_create field schema. Convention for table designer field keys:

• prefix keys with ‘td’ to avoid name conflicts with other extensions using IDataDictionaryForm

• use td_ignore validator first to ignore input when not editing a table designer resource (schema applies to all data data dictionaries not only table designer ones)

• use td_pd validator last to store values as table designer plugin data so they can be read from datastore_info later

e.g.:

return {'tdmykey': [td_ignore, my_validator, td_pd]}
#        ^ prefix   ^ ignore non-td          ^ store value

TextColumn tdtype = "text"

class ckanext.tabledesigner.column_types.TextColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Text'

description = 'Unicode text of any length'

example = 'free-form text'

sql_validate_rule()

Return an SQL rule to remove surrounding whitespace from text pk fields to avoid accidental duplication.

ChoiceColumn tdtype = "choice"

class ckanext.tabledesigner.column_types.ChoiceColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Choice'

description = 'Choose one option from a fixed list'

example = 'b1'

datastore_type = 'text'

DataStore PostgreSQL column type

form_snippet = 'choice.html'

render a select input based on self.choices()

design_snippet = 'choice.html'

render a textarea input for valid options

view_snippet = 'choice.html'

preview choices in a table on resource page

choices() → Iterable[str] | Mapping[str, str]

Return a choice list from the field data.

sql_validate_rule()

Return SQL to validate an option against self.choices()

excel_validate_rule()

Return an Excel formula to validate options against self.choices()

classmethod datastore_field_schema(td_ignore: Validator, td_pd: Validator) → Schema

Return schema to store tdchoices in the field data as a list of strings.

EmailColumn tdtype = "email"

class ckanext.tabledesigner.column_types.EmailColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Email Address'

description = 'A single email address'

example = 'user@example.com'

datastore_type = 'text'

DataStore PostgreSQL column type

html_input_type = 'email'

text.html form snippet input tag type attribute value

sql_validate_rule()

Return SQL rule to check value against the email regex.

URIColumn tdtype = "uri"

class ckanext.tabledesigner.column_types.URIColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'URI'

description = 'Uniform resource identifier (URL or URN)'

example = 'https://example.com/page'

datastore_type = 'text'

DataStore PostgreSQL column type

html_input_type = 'url'

text.html form snippet input tag type attribute value

UUIDColumn tdtype = "uuid"

class ckanext.tabledesigner.column_types.UUIDColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Universally unique identifier (UUID)'

description = 'A universally unique identifier as hexadecimal'

example = '213b972d-75c0-48b7-b14a-5a19eb58a1fa'

datastore_type = 'uuid'

DataStore PostgreSQL column type

_SQL_IS_EMPTY = '{value} IS NULL'

used by sql_required_rule

NumericColumn tdtype = "numeric"

class ckanext.tabledesigner.column_types.NumericColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Numeric'

description = 'Number with arbitrary precision (any number of digits before and after the decimal)'

example = '2.01'

datastore_type = 'numeric'

DataStore PostgreSQL column type

_SQL_IS_EMPTY = '{value} IS NULL'

used by sql_required_rule

excel_validate_rule()

Return an Excel formula to check for numbers.

IntegerColumn tdtype = "integer"

class ckanext.tabledesigner.column_types.IntegerColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Integer'

description = 'Whole numbers with no decimal'

example = '21'

datastore_type = 'int8'

DataStore PostgreSQL column type

_SQL_IS_EMPTY = '{value} IS NULL'

used by sql_required_rule

excel_validate_rule()

Return an Excel formula to check for integers.

BooleanColumn tdtype = "boolean"

class ckanext.tabledesigner.column_types.BooleanColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Boolean'

description = 'True or false values'

example = 'false'

datastore_type = 'boolean'

DataStore PostgreSQL column type

form_snippet = 'choice.html'

snippet used for adding/editing individual records

_SQL_IS_EMPTY = '{value} IS NULL'

used by sql_required_rule

choices()

Return TRUE/FALSE choices.

choice_value_key(value: bool | str) → str

Convert bool to string for matching choice keys in the choice.html form snippet.

excel_validate_rule()

Return an Excel formula to check for TRUE/FALSE.

JSONColumn tdtype = "json"

class ckanext.tabledesigner.column_types.JSONColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'JSON'

description = 'A JSON object'

example = '{"key": "value"}'

datastore_type = 'json'

DataStore PostgreSQL column type

_SQL_IS_EMPTY = "{value} IS NULL OR {value}::jsonb = 'null'::jsonb"

used by sql_required_rule

DateColumn tdtype = "date"

class ckanext.tabledesigner.column_types.DateColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Date'

description = 'Date without time of day'

example = '2024-01-01'

datastore_type = 'date'

DataStore PostgreSQL column type

html_input_type = 'date'

text.html form snippet input tag type attribute value

excel_format = 'yyyy-mm-dd'

ckanext-excelforms column format

_SQL_IS_EMPTY = '{value} IS NULL'

used by sql_required_rule

excel_validate_rule()

Return an Excel formula to check for a date.

TimestampColumn tdtype = "timestamp"

class ckanext.tabledesigner.column_types.TimestampColumn(field: dict[str, Any], constraint_types: List[Type[ColumnConstraint]])

Bases: ColumnType

label = 'Timestamp'

description = 'Date and time without time zone'

example = '2024-01-01 12:00:00'

datastore_type = 'timestamp'

DataStore PostgreSQL column type

html_input_type = 'datetime-local'

text.html form snippet input tag type attribute value

excel_format = 'yyyy-mm-dd HH:MM:SS'

ckanext-excelforms column format

_SQL_IS_EMPTY = '{value} IS NULL'

used by sql_required_rule

excel_validate_rule()

Return an Excel formula to check for a timestamp.

Column Constraint Reference

See also

Complete source code of these column constraint classes

ColumnConstraint base class

class ckanext.tabledesigner.column_constraints.ColumnConstraint(ct: ColumnType)

ColumnConstraint subclasses define:

• pl/pgsql rules for validating data on insert/update

• validators for data dictionary field values

• excel validation rules for ckanext-excelforms

Use IColumnConstraints to add/modify column constraints available.

constraint_snippet = None

snippet used for adding/editing individual records

view_snippet = None

snippet used for resource page data dictionary extra info

classmethod datastore_field_schema(td_ignore: Validator, td_pd: Validator) → Schema

Return schema with keys to add to the datastore_create field schema. Convention for table designer field keys:

• prefix keys with ‘td’ to avoid name conflicts with other extensions using IDataDictionaryForm

• use td_ignore validator first to ignore input when not editing a table designer resource (schema applies to all data data dictionaries not only table designer ones)

• use td_pd validator last to store values as table designer plugin data so they can be read from datastore_info later

e.g.:

return {'tdmykey': [td_ignore, my_validator, td_pd]}
#        ^ prefix   ^ ignore non-td          ^ store value

RangeConstraint

Applies by default to:

• NumericColumn

• IntegerColumn

• DateColumn

• TimestampColumn

class ckanext.tabledesigner.column_constraints.RangeConstraint(ct: ColumnType)

Bases: ColumnConstraint

constraint_snippet = 'range.html'

snippet used for adding/editing individual records

view_snippet = 'range.html'

snippet used for resource page data dictionary extra info

sql_constraint_rule() → str

Return SQL to check if the value is between the minimum and maximum settings (when set).

excel_constraint_rule() → str

Return an Excel formula to check if the value is between the minimum and maximum settings (when set).

classmethod datastore_field_schema(td_ignore: Validator, td_pd: Validator) → Schema

Return schema to store tdminimum and tdmaximum values of the correct type in the field data.

PatternConstraint

Applies by default to:

• TextColumn

class ckanext.tabledesigner.column_constraints.PatternConstraint(ct: ColumnType)

Bases: ColumnConstraint

constraint_snippet = 'pattern.html'

snippet used for adding/editing individual records

view_snippet = 'pattern.html'

snippet used for resource page data dictionary extra info

sql_constraint_rule() → str

Return SQL to check if the value matches the regular expression set.

classmethod datastore_field_schema(td_ignore: Validator, td_pd: Validator) → Schema

Return schema to store tdpattern regular expression.

String Escaping Functions

ckanext.datastore.backend.postgres.identifier(s: str)

Return s as a quoted postgres identifier

ckanext.datastore.backend.postgres.literal_string(s: str)

Return s as a postgres literal string

ckanext.tabledesigner.excel.excel_literal(value: str) → str

return a quoted value safe for use in excel formulas