Trade Validation Rules

All the validations are stored in a STI structure:

CREATE TABLE trade_validation_rules
(
  id serial NOT NULL,
  valid_values_view character varying(255),
  type character varying(255) NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  format_re character varying(255),
  run_order integer NOT NULL,
  column_names character varying(255)[],
  is_primary boolean NOT NULL DEFAULT true,
  scope hstore,
  CONSTRAINT trade_validation_rules_pkey PRIMARY KEY (id)
)

---

Primary validations

Presence validations

Returns records where the specified columns are NULL. In case more than one column is specified, predicates are combined using AND.

Required fields in STI structure:

• column_names

Columns validated for presence:

• TRADING_PARTNER
• TERM_CODE
• SPECIES_NAME
• APPENDIX
• QUANTITY

Numericality validations

Returns records that do not pass the ISNUMERIC test for all columns specified in column_names.

Required fields in STI structure:

• column_names

Columns validated for numericality:

• YEAR
• QUANTITY

Format validations

Returns records that do not pass the regex test for all columns specified in column_names.

Required fields in STI structure:

• column_names
• format_re

Columns validated for presence:

• YEAR

Inclusion validations (single field)

Returns records from sandbox where values in column_names are not included in valid_values_view. The valid_values_view should have the same column names and data types as the sandbox columns specified in column_names.

Columns	Valid values view	View def
TERM_CODE	valid_term_code_view	CREATE VIEW valid_term_code_view AS SELECT code AS term_code FROM trade_codes WHERE type='Term';
SOURCE_CODE	valid_source_code_view	CREATE VIEW valid_source_code_view AS SELECT code AS source_code FROM trade_codes WHERE type='Source';
PURPOSE_CODE	valid_purpose_code_view	CREATE VIEW valid_purpose_code_view AS SELECT code AS purpose_code FROM trade_codes WHERE type='Purpose';
UNIT_CODE	valid_unit_code_view	CREATE VIEW valid_unit_code_view AS SELECT code AS unit_code FROM trade_codes WHERE type='Unit';
TRADING_PARTNER	valid_trading_partner_view	CREATE VIEW valid_trading_partner_view AS SELECT iso_code2 AS trading_partner FROM geo_entities JOIN geo_entity_types ON geo_entity_types.id = geo_entities.geo_entity_type_id WHERE geo_entity_types.name IN ('COUNTRY', 'TERRITORY');
COUNTRY_OF_ORIGIN	valid_country_of_origin_view	CREATE VIEW valid_country_of_origin_view AS SELECT iso_code2 AS country_of_origin FROM geo_entities JOIN geo_entity_types ON geo_entity_types.id = geo_entities.geo_entity_type_id WHERE geo_entity_types.name IN ('COUNTRY', 'TERRITORY');
SPECIES_NAME	valid_species_name_view	CREATE VIEW valid_species_name_view AS SELECT full_name AS species_name FROM taxon_concepts JOIN taxonomies ON taxonomies.id = taxon_concepts.taxonomy_id WHERE taxonomies.name = 'CITES_EU';
APPENDIX	valid_appendix_view	CREATE VIEW valid_appendix_view AS SELECT abbreviation AS appendix FROM species_listings JOIN designations ON designations.id = species_listings.designation_id WHERE designations.name = 'CITES';

Secondary validations

Inclusion validations (multi field)

Returns records from sandbox where values in column_names are not included in valid_values_view. The valid_values_view should have the same column names and data types as the sandbox columns specified in column_names.

Columns	Scope	Valid values view	View def
[SPECIES_NAME, TERM_CODE]		valid_taxon_concept_term_view
[TERM_CODE, UNIT_CODE]		valid_term_unit_view
[TERM_CODE, PURPOSE_CODE]		valid_term_purpose_view
[SPECIES_NAME, COUNTRY_OF_ORIGIN]	source_code = W	valid_species_name_country_of_origin_view

PoV Inclusion validations

(Point of View)

Columns	Scope	Valid values view	View def
[SPECIES_NAME, EXPORTER]	source_code = W, country_of_origin = NULL	valid_species_name_country_of_origin_view

PoV Distinct Value validations

(Point of View)

Columns	Scope	Valid values view	View def
[EXPORTER, IMPORTER]
[EXPORTER, COUNTRY_OF_ORIGIN]

[SPECIES_NAME, APPENDIX, YEAR] validation

[SPECIES_NAME, SOURCE_CODE]

Not implemented yet

• Source_code D and Appendix II for reporting country not EU – (will need more thought on exact checks for this one)