-- Migration from 043 to 044\012\012--------------------------------------------------------------------------------------------------\012-- Add ordinal and section columns to ETPT tables.\012-- 1. Ordinal column is a positive and not null integer to entity type property type tables. \012-- Initially ordinal values of property types of one entity type start from 1 \012-- and increase with increase of ETPT id (so initial order will depend on registration order). \012-- Ordinals should be unique inside one ETPT table but because we use bulk update we can't\012-- easily create this constraint.\012-- 2. Section - string (can be null, don't have to create uniquely named blocks with order)\012--------------------------------------------------------------------------------------------------\012\012-- add a common domain\012CREATE DOMAIN ordinal_int AS bigint CHECK (VALUE > 0);\012\012ALTER TABLE controlled_vocabulary_terms ALTER COLUMN ordinal TYPE ORDINAL_INT;\012\012-- samples\012ALTER TABLE sample_type_property_types ADD COLUMN section DESCRIPTION_1000;\012ALTER TABLE sample_type_property_types ADD COLUMN ordinal ORDINAL_INT;\012UPDATE sample_type_property_types SET ordinal = (\012\011SELECT count(*) FROM sample_type_property_types stpt \012\011WHERE stpt.saty_id = sample_type_property_types.saty_id AND stpt.id <= sample_type_property_types.id\012);\012ALTER TABLE sample_type_property_types ALTER COLUMN ordinal SET NOT NULL;\012\012-- experiments\012ALTER TABLE experiment_type_property_types ADD COLUMN section DESCRIPTION_1000;\012ALTER TABLE experiment_type_property_types ADD COLUMN ordinal ORDINAL_INT;\012UPDATE experiment_type_property_types SET ordinal = (\012\011SELECT count(*) FROM experiment_type_property_types etpt \012\011WHERE etpt.exty_id = experiment_type_property_types.exty_id AND etpt.id <= experiment_type_property_types.id\012);\012ALTER TABLE experiment_type_property_types ALTER COLUMN ordinal SET NOT NULL;\012\012-- data sets\012ALTER TABLE data_set_type_property_types ADD COLUMN section DESCRIPTION_1000;\012ALTER TABLE data_set_type_property_types ADD COLUMN ordinal ORDINAL_INT;\012UPDATE data_set_type_property_types SET ordinal = (\012\011SELECT count(*) FROM data_set_type_property_types dstpt \012\011WHERE dstpt.dsty_id = data_set_type_property_types.dsty_id AND dstpt.id <= data_set_type_property_types.id\012);\012ALTER TABLE data_set_type_property_types ALTER COLUMN ordinal SET NOT NULL;\012\012-- materials\012ALTER TABLE material_type_property_types ADD COLUMN section DESCRIPTION_1000;\012ALTER TABLE material_type_property_types ADD COLUMN ordinal ORDINAL_INT;\012UPDATE material_type_property_types SET ordinal = (\012\011SELECT count(*) FROM material_type_property_types mtpt \012\011WHERE mtpt.maty_id = material_type_property_types.maty_id AND mtpt.id <= material_type_property_types.id\012);\012ALTER TABLE material_type_property_types ALTER COLUMN ordinal SET NOT NULL;\012