Trade Validation Rules

All the validations are stored in a STI structure:

CREATE TABLE trade_validation_rules ( id integer NOT NULL, column_names character varying(255)[] NOT NULL, valid_values_view character varying(255), format_re 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 );

---

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:

• EXPORT_COUNTRY_CODE
• IMPORT_COUNTRY_CODE
• TERM_CODE_1
• CITES_NAME
• APPENDIX
• QUANTITY_1

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 presence:

• YEAR
• QUANTITY_1

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

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';`

Note: "allowed values" will be a name of the view (so that we don't store any SQL here). The idea is to check whether this returns any rows: (SELECT field_names FROM sandbox WHERE scope) JOIN allowed_values

(neither this or the following is correct SQL, but you get the idea - TODO)

But just to show what the view should return I'll put down pseudo-SQL here:

• [CITES_NAME], SELECT full_name FROM taxon_concepts WHERE name_status IN ('A', 'H') AND -- check it's a CITES taxon concept
• [EXPORT_COUNTRY_CODE], SELECT name_en FROM geo_entities -- WHERE type is COUNTRY
• [IMPORT_COUNTRY_CODE], same as above
• [ORIGIN_COUNTRY_CODE], same as above
• [APPENDIX], ? we have I, II, III and they have 1, 2, 3
• [CITES_NAME, SHIPMENT_YEAR, APPENDIX], this one is tricky, we will need a year-by-year lookup table to know what the appendix was at a given point in time
• [CITES_NAME, TERM_CODE_1], SELECT * FROM acceptable_pairings ?
• [CITES_NAME, SOURCE_CODE], SELECT * FROM acceptable_pairings ?
• [TERM_CODE, UNIT_CODE], SELECT * FROM acceptable_pairings ?
• [TERM_CODE, PURPOSE_CODE], SELECT * FROM acceptable_pairings ?
• [CITES_NAME, ORIGIN_COUNTRY_CODE], SELECT full_name, geo_entities.name FROM distributions, SOURCE_CODE = 'W' AND ORIGIN_COUNTRY_CODE IS NOT NULL
• [CITES_NAME, EXPORT_COUNTRY_CODE], SELECT full_name, geo_entities.name FROM distributions, SOURCE_CODE = 'W' AND ORIGIN_COUNTRY_CODE IS NULL

No idea how to call validations

• EXPORT_COUNTRY_CODE = IMPORT_COUNTRY_CODE (not international trade then!)
• EXPORT_COUNTRY_CODE = ORIGIN_COUNTRY_CODE (might be ok if re-export)
• Source_code D and Appendix II for reporting country not EU – (will need more thought on exact checks for this one)