Skip to content

Latest commit

 

History

History
505 lines (434 loc) · 22.2 KB

extension_schema.adoc

File metadata and controls

505 lines (434 loc) · 22.2 KB

Schema

Introduction

The schema 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.

Extension Author

GeoPackage SWG, author_name gpkg

Extension Name

gpkg_schema

Extension Type

New requirement dependent on clauses [features], [attributes], and [extension_mechanism].

Applicability

This extension may apply to any [feature_user_tables], [attributes_user_tables], or extension tables (see [attributes_user_tables]).

Scope

Read-write

Requirements

Table Definitions
Data Columns
Requirement 103

A GeoPackage MAY contain a table named gpkg_data_columns. If present it SHALL be defined per Table 1 and gpkg_data_columns Table Definition SQL.

Table 1. Data Columns Table Definition
Column Name Column Type Column Description Null Key

table_name

TEXT

Name of a table specified in gpkg_contents.table_name or gpkg_extensions.table_name

no

PK, UK

column_name

TEXT

Name of the table column

no

PK

name

TEXT

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

yes

UK

title

TEXT

A human-readable formal title for the column_name content

yes

description

TEXT

A human-readable description for the column_name content

yes

mime_type

TEXT

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

yes

constraint_name

TEXT

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

yes

GeoPackage applications MAY [K38] use the gpkg_data_columns table to store minimal application schema identifying, descriptive and MIME [I21] type [K39] information about columns in user vector feature and tile matrix data tables that supplements the data available from the SQLite sqlite_master table and pragma table_info(table_name) SQL function. The gpkg_data_columns data CAN be used to provide more specific column data types and value ranges and application specific structural and semantic information to enable more informative user menu displays and more effective user decisions on the suitability of GeoPackage contents for specific purposes.

Warning

In versions 1.2.1 and earlier, the table_name column had a foreign key to gpkg_contents.table_name. This constraint has been relaxed but software that edits GeoPackages should be aware that this constraint will exist in many existing files.
Data Column Constraints
Requirement 107

A GeoPackage MAY contain a table named gpkg_data_column_constraints. If present it SHALL be defined per Table 2 and gpkg_data_columns Table Definition SQL.

The gpkg_data_column_constraints table contains data to specify restrictions on basic data type column values. The constraint_name column is referenced by the constraint_name column in the gpkg_data_columns table defined in Table 1.

Table 2. Data Column Constraints Table Definition
Column Name Column Type Column Description Null Key

constraint_name

TEXT

Name of constraint (lowercase)

no

Jointly Unique

constraint_type

TEXT

Type name of constraint: 'range' | 'enum' | 'glob'

no

Jointly Unique

value

TEXT

Specified case sensitive value for 'enum' or 'glob' or NULL for 'range' constraint_type

yes

Jointly Unique

min

NUMERIC

Minimum value for 'range' or NULL for 'enum' or 'glob' constraint_type

yes

min_is_inclusive

BOOLEAN

0 (false) if min value is exclusive, or 1 (true) if min value is inclusive

yes

max

NUMERIC

Maximum value for 'range' or NULL for 'enum' or 'glob' constraint_type

yes

max_is_inclusive

BOOLEAN

0 (false) if max value is exclusive, or 1 (true) if max value is inclusive

yes

description

TEXT

For ranges and globs, describes the constraint; for enums, describes the enum value.

yes

The min and max columns are defined as NUMERIC to be able to contain range values for any numeric data column defined with a data type from Table 1. These are the only exceptions to the data type rule stated in Req 5.

Warning

In GeoPackage 1.0, this table had column names minIsInclusive and maxIsInclusive instead of min_is_inclusive and max_is_inclusive. This was corrected in GeoPackage 1.1 but it is possible that some older GeoPackages may have rows in this table and use the incorrect column names.
Table Data Values
gpkg_extensions
Requirement 141

GeoPackages with rows in the gpkg_extensions table with an extension_name of "gpkg_schema" SHALL comply with this extension. GeoPackages complying with this extension SHALL have rows in the gpkg_extensions table as described in Table 3 (below).

Warning

Requirement 141 was updated as part of GeoPackage 1.2.1. In 1.1.0 and 1.2.0, the details of required gpkg_extensions rows were inadvertently left unspecified. While the executable test suite running on an older GeoPackage version will not generate a failure due to missing gpkg_extensions rows, it is recommended to update these rows to comply with the updated requirement on older versions as well.
Table 3. Extension Table Records
table_name column_name extension_name definition scope

gpkg_data_columns

null

gpkg_schema

see note below

read-write

gpkg_data_column_constraints

null

gpkg_schema

see note below

read-write
Note

For the definition column, use a hyperlink that describes the current implementation of this extension. While a URL like http://www.geopackage.org/spec/#extension_schema is acceptable, permalinks to specific versions are provided upon publication using the URL pattern http://www.geopackage.org/specMmP/#extension_schema where M is the major version, m is the minor version, and P is the patch. For example http://www.geopackage.org/spec121/#extension_schema is the permalink for this extension for GeoPackage 1.2.1.
Data Columns
Requirement 104

Values of the gpkg_data_columns table table_name column value SHALL reference values in the table_name column from either gpkg_contents or gpkg_extensions.

Requirement 105

The column_name column value in a gpkg_data_columns table row SHALL contain the name of a column in the SQLite table or view identified by the table_name column value.

Requirement 106

The constraint_name column value in a gpkg_data_columns table MAY be NULL. If it is not NULL, it SHALL contain a constraint_name column value (which SHALL be lowercase) from the gpkg_data_column_constraints table.

Data Column Constraints

The lowercase gpkg_data_column_constraints constraint_type column value specifies the type of constraint: "range", "enum", or "glob" (GLOB is a text pattern match - see [I33]). The case sensitive value column contains an enumerated legal value for constraint_type "enum", a pattern match string for constraint_type "glob", or NULL for constraint_type "range". The set of value column values in rows of constraint_type "enum" with the same constraint_name contains all possible enumerated values for the constraint name. The min and max column values specify the minimum and maximum valid values for constraint_type "range", or are NULL for constraint_type "enum" or "glob". The min_is_inclusive and max_is_inclusive column values contain 1 if the min and max values (respectively) are inclusive, 0 if they are exclusive, or are NULL for constraint_type "enum" or "glob". These restrictions MAY be enforced by SQL triggers or by code in applications that update GeoPackage data values.

Table 4. Sample Data Column Constraints
constraint_name constraint_type value min min_is_inclusive max max_is_inclusive

sampleRange

range

NULL

1

true

10

true

sampleEnum

enum

1

NULL

NULL

NULL

NULL

sampleEnum

enum

3

NULL

NULL

NULL

NULL

sampleEnum

enum

5

NULL

NULL

NULL

NULL

sampleEnum

enum

7

NULL

NULL

NULL

NULL

sampleEnum

enum

9

NULL

NULL

NULL

NULL

sampleGlob

glob

[1-2][0-9][0-9][0-9]

NULL

NULL

NULL

NULL
Requirement 108

The gpkg_data_column_constraints table MAY be empty. If it contains data, the lowercase constraint_type column values SHALL be one of "range", "enum", or "glob".

Requirement 109

The gpkg_data_column_constraint constraint_name values for rows with constraint_type values of "range" and "glob" SHALL be unique.

Requirement 110

The gpkg_data_column_constraints table MAY be empty. If it contains rows with constraint_type column values of "range", the value column values for those rows SHALL be NULL.

Requirement 111

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "range", the min column values for those rows SHALL be NOT NULL and less than the max column value which shall be NOT NULL.

Requirement 112

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "range", the min_is_inclusive and max_is_inclusive column values for those rows SHALL be 0 or 1.

Requirement 113

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "enum" or "glob", the min, max, min_is_inclusive and max_is_inclusive column values for those rows SHALL be NULL.

Requirement 114

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "enum" or "glob", the value column SHALL NOT be NULL.

Abstract Test Suite

Table Definition
Data Columns

Test Case ID

/extensions/schema/data_columns/table_def

Test Purpose

Verify that the gpkg_data_columns table exists and has the correct definition.

Test Method

  1. PRAGMA table_info(gpkg_data_columns)

  2. Fail if returns an empty result set

  3. Fail if column names and column definitions in the returned table_info do not match those of Table 23, including data type, nullability, default values. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant.

  4. Pass if no failures.

Reference

Annex F.9 Req 103

Test Type

Basic
Data Column Constraints

Test Case ID

/extensions/schema/data_column_constraints/table_def

Test Purpose

Verify that the gpkg_data_column_constraints table exists and has the correct definition.

Test Method

  1. PRAGMA table_info(gpkg_data_column_constraints)

  2. Fail if returns an empty result set

  3. Fail if column names and column definitions in the returned table_info do not match those of Table 23, including data type, nullability, default values. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant.

  4. Pass if no failures.

Reference

Annex F.9 Req 107

Test Type

Basic
Data Values
gpkg_extensions

Test Case ID

/extensions/schema/extensions/data_values

Test Purpose

Verify that the gpkg_extensions table has the required rows.

Test Method

  1. SELECT table_name, column_name, scope FROM gpkg_extensions WHERE extension_name = 'gpkg_schema';

  2. Not testable if returns an empty result set

  3. Fail if there are not exactly two rows

  4. For each row returned from step 1

    1. Fail if scope is not "read-write"

    2. Fail if column_name is not NULL

  5. Fail if either table_name entry is not present

  6. Pass if no fails

Reference

Annex F.9 Req 141

Test Type:

Capabilities

Test Case ID

/extensions/schema/data_columns/table_name

Test Purpose

Verify that for each gpkg_data_columns row, the table_name value matches a row in gpkg_contents or gpkg_extensions.

Test Method

  1. SELECT DISTINCT gdc.table_name AS gdc_table, ge.table_name AS joined_table FROM gpkg_data_columns AS gdc LEFT OUTER JOIN gpkg_contents AS gc ON gdc.table_name = gc.table_name LEFT OUTER JOIN gpkg_extensions AS ge ON gdc.table_name = ge.table_name;

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. Fail if joined_table is NULL.

  4. Pass if no fails.

Reference

Annex F.9 Req 104

Test Type

Capability

Test Case ID

/extensions/schema/data_columns/column_name

Test Purpose

Verify that for each gpkg_data_columns row, the column_name value matches a column in the table or view identified by the table_name column value.

Test Method

  1. SELECT table_name, column_name FROM gpkg_data_columns

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. PRAGMA table_info(table_name)

    2. Fail if table_name does not contain a column matching column_name

  4. Pass if no fails

Reference

Annex F.9 Req 105

Test Type

Capability

Test Case ID

/extensions/schema/data_columns/constraint_name

Test Purpose

Verify that for each gpkg_data_columns row, if the constraint_name value is NOT NULL then the gpkg_data_column_constraints table contains at least a row with a matching constraint_name value.

Test Method

  1. SELECT constraint_name AS cn FROM gpkg_data_columns

  2. Not testable if returns an empty result set

  3. For each NOT NULL cn value from step 1

    1. SELECT 1 FROM gpkg_data_column_constraints WHERE constraint_name = cn

    2. Fail if returns an empty result set

  4. Pass if no fails

Reference

Annex F.9 Req 106

Test Type

Capability
Data Column Constraints

Test Case ID

/extensions/schema/data_column_constraints/constraint_type

Test Purpose

Verify that the gpkg_data_column_constraints constraint_type column values are one of "range", "enum", or "glob".

Test Method

  1. SELECT DISTINCT constraint_type FROM gpkg_data_column_constraints

  2. Not testable if returns an empty result set

  3. For each constraint_type value returned by step 1

    1. Fail if constraint_type NOT IN ("range", "enum", "glob").

  4. Pass if no fails.

Reference

Annex F.9 Req 108

Test Type

Capability

Test Case ID

/extensions/schema/data_column_constraints/constraint_names_unique

Test Purpose

Verify that the gpkg_data_column_constraints constraint_name column values for constraint_type values of "range", or "glob" are unique.

Test Method

  1. SELECT DISTINCT constraint_name FROM gpkg_data_column_constraints WHERE constraint_type IN ('range', 'glob')

    1. For each returned constraint_name cn

    2. SELECT count(*) FROM gpkg_data column_constraints WHERE constraint_name = cn

    3. Fail if count > 1

  2. Pass if no fails.

Reference

Annex F.9 Req 109

Test Type

Capability

Test Case ID

/extensions/schema/data_column_constraints/value_for_range

Test Purpose

Verify that the gpkg_data_column_constraints value column values are NULL for rows with a constraint_type value of "range".

Test Method

  1. SELECT constraint_name, value FROM gpkg_data_column_constraints WHERE constraint_type = 'range'

  2. Not testable if returns an empty result set

  3. For each value returned by step 1

    1. Fail if value IS NOT NULL

  4. Pass if no fails.

Reference

Annex F.9 Req 110

Test Type

Capability

Test Case ID

/extensions/schema/data_column_constraints/min_max_for_range

Test Purpose

Verify that the gpkg_data_column_constraints min column values are NOT NULL and less than the max column values for rows with a constraint_type value of "range".

Test Method

  1. SELECT constraint_name, min, max FROM gpkg_data_column_constraints WHERE constraint_type = 'range'

  2. Not testable if returns an empty result set

  3. For each set of min and max values returned by step 1

    1. Fail if min IS NULL

    2. Fail if max IS NULL

    3. Fail if min >= max

  4. Pass if no fails.

Reference

Annex F.9 Req 111

Test Type

Capability

Test Case ID

/extensions/schema/data_column_constraints/inclusive_for_range

Test Purpose

Verify that the gpkg_data_column_constraints min_is_inclusive and max_is_inclusive column values are NOT NULL and either 0 or 1 for rows with a constraint_type value of "range".

Test Method

  1. SELECT constraint_name, min_is_inclusive, max_is_inclusive FROM gpkg_data_column_constraints WHERE constraint_type = 'range'

  2. Not testable if returns an empty result set

  3. For each set of values returned by step 1

    1. Fail if min_is_inclusive IS NULL

    2. Fail if max_is_inclusive IS NULL

    3. Fail if min_is_inclusive is NOT IN (0,1)

    4. Fail if max_is_inclusive is NOT IN (0,1)

  4. Pass if no fails.

Reference

Annex F.9 Req 112

Test Type

Capability

Test Case ID:

/extensions/schema/data_column_constraints/min_max_inclusive_for_enum_glob

Test Purpose:

Verify that the gpkg_data_column_constraints min, max, min_is_inclusive and max_is_inclusive column values are NULL for rows with a constraint_type value of "enum" or "glob".

Test Method:

  1. SELECT constraint_name, min, max, min_is_inclusive, max_is_inclusive FROM gpkg_data_column_constraints WHERE constraint_type IN ('enum','glob')

  2. Not testable if returns an empty result set

  3. For each set of values returned by step 1

    1. Fail if min IS NOT NULL

    2. Fail if max IS NOT NULL

    3. Fail if min_is_inclusive IS NOT NULL

    4. Fail if max_is_inclusive IS NOT NULL

  4. Pass if no fails.

Reference

Annex F.9 Req 113

Test Type

Capability

Test Case ID:

/extensions/schema/data_column_constraints/value_for_enum_glob

Test Purpose:

Verify that the gpkg_data_column_constraints value column values are NOT NULL for rows with a constraint_type value of "enum" or "glob".

Test Method:

  1. SELECT value FROM gpkg_data_column_constraints WHERE constraint_type IN ('enum','glob')

  2. Not testable if returns an empty result set

  3. For each value returned by step 1

    1. Fail if value IS NULL

  4. Pass if no fails.

Reference

Annex F.9 Req 114

Test Type

Capability

Table Definition SQL

gpkg_data_columns
gpkg_data_columns Table Definition SQL
CREATE TABLE gpkg_data_columns (
  table_name TEXT NOT NULL,
  column_name TEXT NOT NULL,
  name TEXT,
  title TEXT,
  description TEXT,
  mime_type TEXT,
  constraint_name TEXT,
  CONSTRAINT pk_gdc PRIMARY KEY (table_name, column_name),
  CONSTRAINT gdc_tn UNIQUE (table_name, name)
);
gpkg_data_column_constraints
gpkg_data_columns Table Definition SQL
CREATE TABLE gpkg_data_column_constraints (
  constraint_name TEXT NOT NULL,
  constraint_type TEXT NOT NULL, // 'range' | 'enum' | 'glob'
  value TEXT,
  min NUMERIC,
  min_is_inclusive BOOLEAN, // 0 = false, 1 = true
  max NUMERIC,
  max_is_inclusive BOOLEAN, // 0 = false, 1 = true
  description TEXT,
  CONSTRAINT gdcc_ntv UNIQUE (constraint_name, constraint_type, value)
)