Schema

This extension provides a means to describe the columns of tables in a GeoPackage with more detail than can be captured by the SQL table definition directly. The information provided by this extension can be used by applications to, for instance, present data contained in a GeoPackage in a more user-friendly fashion or implement data validation logic. The extension was originally designed to describe the schema of features but it could apply to any user-defined table with user-defined columns.

gpkg_extensions

To use this extension, add the following rows to this table as described in Table 1.

Table 1: gpkg_extensions :widths: 20 15 15 40 10 :header-rows: 1

table_name

column_name

extension_name

definition

scope

gpkg_data_columns

NULL

gpkg_schema

http://www.geopackage.org/spec130/#extension_schema

read-write

gpkg_data_column_constraints

NULL

gpkg_schema

http://www.geopackage.org/spec130/#extension_schema

read-write

gpkg_data_columns

Add a row to this table for each column that needs to be further described as described in Table 2.

Table 2: gpkg_data_columns :widths: 20 80 :header-rows: 1

Column

Value

table_name

user-defined table name

column_name

user-defined column name

name

A human-readable identifier (e.g. short name) for the column_name content

title

A human-readable formal title for the column_name content

description

A human-readable description for the column_name content

mime_type

MIME type of column_name if BLOB type, or NULL for other types

constraint_name

Column value constraint name (lowercase) specified by reference to gpkg_data_column_constraints.constraint name (see below)

Using This Extension

Defining an Enumeration

To define an enumeration for a column, add one row to gpkg_data_column_constraints for each enumerated value as described in Table 3. (Other values can be NULL and are to be ignored.)

Table 3: gpkg_data_column_constraints for an enumeration :widths: 20 80 :header-rows: 1

Column

Value

constraint_name

Name of constraint (lowercase)

constraint_type

enum

value

Specified case sensitive value

description

Description of the enum value

Defining a Range

To define a numeric range for an column, add one row to gpkg_data_column_constraints as described in Table 4. (Other values can be NULL and are to be ignored.)

Defining a GLOB

A GLOB is a pattern-matching expression. You can add a GLOB constraint for a column to constrain values to those that match the specified pattern. To do this, add one row to gpkg_data_column_constraints as as described in Table 5.

Table 5: gpkg_data_column_constraints for a range :widths: 20 80 :header-rows: 1

Column

Value

constraint_name

Name of constraint (lowercase)

constraint_type

glob

value

actual GLOB expression

description

Description of the constraint

Defining a MIME Type for a BLOB

To constraint a BLOB column to a specific MIME type, set the mime_type column for that row in gpkg_data_columns. (The constraint_name column of gpkg_data_columns can be NULL and the gpkg_data_column_constraints table does not apply.)

Note

This constraint must be controlled through software – it is not possible for SQLite to control this directly.