Trade Validation Rules

Presence validations

CREATE TABLE trade_presence_validations (field_name text)

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

Numericality validations

CREATE TABLE trade_numericality_validations (field_name text)

• YEAR
• QUANTITY_1

Inclusion validations

CREATE TABLE trade_inlusion_validations (field_names text[], allowed_values text, scope hstore?)

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
• [SOURCE_CODE], SELECT name FROM trade_codes WHERE type='Source'
• [PURPOSE_CODE], SELECT name FROM trade_codes WHERE type='Purpose'
• [TERM_CODE_1], SELECT name FROM trade_codes WHERE type = 'Term'
• [UNIT_CODE_1], SELECT name FROM trade_codes WHERE type = 'Unit'
• [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