56 lines
1.3 KiB
SQL
56 lines
1.3 KiB
SQL
ALTER TABLE IF EXISTS document_item
|
|
ADD COLUMN IF NOT EXISTS version_number INTEGER;
|
|
|
|
UPDATE document_item
|
|
SET version_number = 1
|
|
WHERE version_number IS NULL;
|
|
|
|
ALTER TABLE IF EXISTS document_item
|
|
ALTER COLUMN version_number SET DEFAULT 1;
|
|
|
|
ALTER TABLE IF EXISTS document_item
|
|
ALTER COLUMN version_number SET NOT NULL;
|
|
|
|
ALTER TABLE IF EXISTS document_item
|
|
ADD COLUMN IF NOT EXISTS version_group_id BIGINT;
|
|
|
|
UPDATE document_item
|
|
SET version_group_id = id
|
|
WHERE version_group_id IS NULL;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'fk_document_item_version_group'
|
|
) THEN
|
|
ALTER TABLE document_item
|
|
ADD CONSTRAINT fk_document_item_version_group
|
|
FOREIGN KEY (version_group_id)
|
|
REFERENCES document_item(id)
|
|
ON DELETE SET NULL;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'document_item_document_number_key'
|
|
AND conrelid = 'document_item'::regclass
|
|
) THEN
|
|
ALTER TABLE document_item
|
|
DROP CONSTRAINT document_item_document_number_key;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_document_item_document_number_version_unique
|
|
ON document_item (document_number, version_number);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_document_item_version_group_id
|
|
ON document_item (version_group_id);
|