#!/bin/bash

# :mode=transact-sql:

cd "$(dirname "$0")"

. ../../../lib/import.sh

map_table

psql <<'EOF'

SELECT functions.set_col_types('"SUBPLOT"', ARRAY[

  ('STATECD' , 'integer')

, ('UNITCD'  , 'integer')

, ('COUNTYCD', 'integer')

, ('PLOT'    , 'integer')

, ('INVYR'   , 'integer')

, ('SUBP'    , 'integer')

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID"     UNIQUE ("*SUBPLOT.CN")                                           $$);

SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "PLOT", "INVYR", "SUBP")$$);

SELECT functions.cluster_once('"SUBPLOT"', '"SUBPLOT.unique"');