56 lines
2.4 KiB
SQL
56 lines
2.4 KiB
SQL
-- MijnBesluit - bijlagen bij kluisitems
|
|
--
|
|
-- Voegt twee kolommen toe aan `vault_files`:
|
|
-- vault_item_id : bij welk kluisitem dit bestand hoort. Leeg bij een bestand dat op zichzelf
|
|
-- staat, zoals de opname bij een videobericht.
|
|
-- deleted_at : gevuld zodra het bestand verwijderd is.
|
|
--
|
|
-- Draaien met:
|
|
-- mysql -u root -p mijnbesluit < db\04_bijlagen.sql
|
|
--
|
|
-- Opnieuw draaien mag: er gebeurt dan niets.
|
|
|
|
USE `mijnbesluit`;
|
|
|
|
-- ---------------------------------------------------------------- vault_item_id
|
|
SET @bestaat := (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'vault_files' AND COLUMN_NAME = 'vault_item_id');
|
|
|
|
SET @sql := IF(@bestaat = 0,
|
|
'ALTER TABLE `vault_files` ADD COLUMN `vault_item_id` BIGINT NULL AFTER `owner_person_id`',
|
|
'SELECT "vault_item_id bestaat al" AS melding');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- ---------------------------------------------------------------- deleted_at
|
|
SET @bestaat := (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'vault_files' AND COLUMN_NAME = 'deleted_at');
|
|
|
|
SET @sql := IF(@bestaat = 0,
|
|
'ALTER TABLE `vault_files` ADD COLUMN `deleted_at` DATETIME(3) NULL',
|
|
'SELECT "deleted_at bestaat al" AS melding');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- ---------------------------------------------------------------- index + relatie
|
|
SET @bestaat := (
|
|
SELECT COUNT(*) FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'vault_files' AND INDEX_NAME = 'ix_vault_files_item');
|
|
|
|
SET @sql := IF(@bestaat = 0,
|
|
'CREATE INDEX `ix_vault_files_item` ON `vault_files` (`vault_item_id`)',
|
|
'SELECT "index bestaat al" AS melding');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- Verdwijnt het kluisitem, dan verdwijnt de koppeling; het bestand zelf ruimt de applicatie op.
|
|
SET @bestaat := (
|
|
SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'vault_files' AND CONSTRAINT_NAME = 'fk_vf_item');
|
|
|
|
SET @sql := IF(@bestaat = 0,
|
|
'ALTER TABLE `vault_files` ADD CONSTRAINT `fk_vf_item` FOREIGN KEY (`vault_item_id`) REFERENCES `vault_items` (`id`) ON DELETE SET NULL',
|
|
'SELECT "fk_vf_item bestaat al" AS melding');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
SELECT 'Klaar: bijlagen toegevoegd aan vault_files.' AS melding;
|