71 lines
3.0 KiB
SQL
71 lines
3.0 KiB
SQL
-- UTF-8, kein BOM
|
||
-- EIS – Schema-Update 1.1.1
|
||
-- Ziel: fehlende Tabellen anlegen, fehlende Spalten/Indizes ergänzen.
|
||
|
||
-- ==========================================================
|
||
-- 1) Einstellungen (neu in 1.1.1)
|
||
-- ==========================================================
|
||
CREATE TABLE IF NOT EXISTS `#__eis_settings` (
|
||
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`param` VARCHAR(191) NOT NULL,
|
||
`value` TEXT NULL,
|
||
`created` DATETIME NULL DEFAULT NULL,
|
||
`modified` DATETIME NULL DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_param` (`param`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
INSERT IGNORE INTO `#__eis_settings` (`param`, `value`, `created`, `modified`)
|
||
VALUES ('document_root', '', NOW(), NOW());
|
||
|
||
-- ==========================================================
|
||
-- 2) Dokumente (Bestand absichern)
|
||
-- Falls ältere Installationen die Tabelle nicht haben.
|
||
-- ==========================================================
|
||
CREATE TABLE IF NOT EXISTS `#__eis_documents` (
|
||
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(255) NOT NULL,
|
||
`path` TEXT NOT NULL,
|
||
`parent_id` INT UNSIGNED DEFAULT NULL,
|
||
`is_folder` TINYINT(1) NOT NULL DEFAULT 0,
|
||
`title` VARCHAR(255) DEFAULT NULL,
|
||
`description` TEXT DEFAULT NULL,
|
||
`ordering` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `#__eis_folder_access` (
|
||
`folder_id` INT UNSIGNED NOT NULL,
|
||
`viewlevel_id` INT UNSIGNED NOT NULL,
|
||
`modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`folder_id`),
|
||
KEY `idx_viewlevel` (`viewlevel_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
|
||
-- Falls es Altbestände ohne neue Spalten gab, Spalten idempotent ergänzen
|
||
ALTER TABLE `#__eis_documents`
|
||
ADD COLUMN IF NOT EXISTS `title` VARCHAR(255) DEFAULT NULL,
|
||
ADD COLUMN IF NOT EXISTS `description` TEXT DEFAULT NULL,
|
||
ADD COLUMN IF NOT EXISTS `ordering` INT NOT NULL DEFAULT 0,
|
||
MODIFY COLUMN `is_folder` TINYINT(1) NOT NULL DEFAULT 0;
|
||
|
||
-- Nützliche Indizes (idempotent)
|
||
CREATE INDEX IF NOT EXISTS `idx_eis_docs_parent` ON `#__eis_documents` (`parent_id`);
|
||
CREATE INDEX IF NOT EXISTS `idx_eis_docs_ordering` ON `#__eis_documents` (`ordering`);
|
||
CREATE INDEX IF NOT EXISTS `idx_eis_docs_name` ON `#__eis_documents` (`name`);
|
||
|
||
-- Optional: Selbst-Referenz als FK (nur wenn du ON DELETE CASCADE willst)
|
||
-- Achtung: MySQL verlangt gleiche Kollation/Engine; Namen idempotent prüfen:
|
||
-- (MySQL kennt kein "ADD CONSTRAINT IF NOT EXISTS", daher defensiv erst droppen)
|
||
-- SET @fk_exists := (
|
||
-- SELECT COUNT(*)
|
||
-- FROM information_schema.REFERENTIAL_CONSTRAINTS
|
||
-- WHERE CONSTRAINT_SCHEMA = DATABASE()
|
||
-- AND CONSTRAINT_NAME = 'fk_eis_docs_parent'
|
||
-- );
|
||
-- SET @sql := IF(@fk_exists = 0,
|
||
-- 'ALTER TABLE `#__eis_documents` ADD CONSTRAINT `fk_eis_docs_parent` FOREIGN KEY (`parent_id`) REFERENCES `#__eis_documents`(`id`) ON DELETE CASCADE;',
|
||
-- 'SELECT 1');
|
||
-- PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|