VPS 4 Builder
289 lines
9.4 KiB
SQL
289 lines
9.4 KiB
SQL
-- ================================================================
|
|
-- MIGRATION v2.0 - Sistema de Transformações e Gerenciamento
|
|
-- Dental Image Server - MySQL
|
|
-- ================================================================
|
|
|
|
-- Execute este arquivo no banco MySQL:
|
|
-- mysql -u root -p dental_images < migration-v2.sql
|
|
-- OU via phpMyAdmin: Import > Selecionar arquivo
|
|
|
|
-- ================================================================
|
|
-- PARTE 1: CRIAR BANCO (SE NÃO EXISTIR)
|
|
-- ================================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS dental_images
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE dental_images;
|
|
|
|
-- ================================================================
|
|
-- PARTE 2: CRIAR TABELA USERS (SE NÃO EXISTIR)
|
|
-- ================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
email VARCHAR(100) UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ================================================================
|
|
-- PARTE 3: CRIAR TABELA IMAGES
|
|
-- ================================================================
|
|
|
|
-- Drop tabela antiga se existir (CUIDADO: remove todos os dados!)
|
|
-- DROP TABLE IF EXISTS images;
|
|
|
|
-- Criar tabela images
|
|
CREATE TABLE IF NOT EXISTS images (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
image_guid VARCHAR(255) NOT NULL,
|
|
patient_name TEXT,
|
|
original_filename TEXT,
|
|
filename TEXT NOT NULL,
|
|
enabled TINYINT(1) DEFAULT 1,
|
|
tooth_number VARCHAR(10),
|
|
tooth_side VARCHAR(10),
|
|
patient_name_resumed VARCHAR(50),
|
|
original_image_id INT,
|
|
rotation INT DEFAULT 0,
|
|
flip_horizontal INT DEFAULT 0,
|
|
flip_vertical INT DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_images_enabled (enabled),
|
|
INDEX idx_images_original (original_image_id),
|
|
INDEX idx_patient_name (patient_name(100)),
|
|
INDEX idx_image_guid (image_guid),
|
|
INDEX idx_created_at (created_at),
|
|
FOREIGN KEY (original_image_id) REFERENCES images(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ================================================================
|
|
-- PARTE 4: ADICIONAR COLUNAS NOVAS (SE TABELA JÁ EXISTIR)
|
|
-- ================================================================
|
|
|
|
-- Verificar e adicionar colunas se não existirem
|
|
SET @db_name = 'dental_images';
|
|
SET @table_name = 'images';
|
|
|
|
-- Coluna enabled
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''enabled''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN enabled TINYINT(1) DEFAULT 1'',''SELECT \\''Column enabled already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Executar query se necessário
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Coluna tooth_number
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''tooth_number''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN tooth_number VARCHAR(10)'',''SELECT \\''Column tooth_number already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Coluna tooth_side
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''tooth_side''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN tooth_side VARCHAR(10)'',''SELECT \\''Column tooth_side already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Coluna patient_name_resumed
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''patient_name_resumed''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN patient_name_resumed VARCHAR(50)'',''SELECT \\''Column patient_name_resumed already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Coluna original_image_id
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''original_image_id''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN original_image_id INT'',''SELECT \\''Column original_image_id already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Coluna rotation
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''rotation''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN rotation INT DEFAULT 0'',''SELECT \\''Column rotation already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Coluna flip_horizontal
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''flip_horizontal''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN flip_horizontal INT DEFAULT 0'',''SELECT \\''Column flip_horizontal already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Coluna flip_vertical
|
|
SET @sql = CONCAT(
|
|
'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns ',
|
|
'WHERE table_schema = ? AND table_name = ? AND column_name = ''flip_vertical''; ',
|
|
'SELECT IF(@col_exists = 0, ''ALTER TABLE images ADD COLUMN flip_vertical INT DEFAULT 0'',''SELECT \\''Column flip_vertical already exists\\'' AS message'') INTO @query; '
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt USING @db_name, @table_name;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- ================================================================
|
|
-- PARTE 5: CRIAR ÍNDICES (SE NÃO EXISTIREM)
|
|
-- ================================================================
|
|
|
|
-- MySQL suporta CREATE INDEX IF NOT EXISTS apenas em algumas versões
|
|
-- Usando método alternativo:
|
|
|
|
-- Índice enabled
|
|
SELECT COUNT(*) INTO @idx_exists
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = @db_name
|
|
AND table_name = @table_name
|
|
AND index_name = 'idx_images_enabled';
|
|
|
|
SET @sql = IF(@idx_exists = 0,
|
|
'CREATE INDEX idx_images_enabled ON images(enabled)',
|
|
'SELECT "Index idx_images_enabled already exists" AS message'
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Índice original
|
|
SELECT COUNT(*) INTO @idx_exists
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = @db_name
|
|
AND table_name = @table_name
|
|
AND index_name = 'idx_images_original';
|
|
|
|
SET @sql = IF(@idx_exists = 0,
|
|
'CREATE INDEX idx_images_original ON images(original_image_id)',
|
|
'SELECT "Index idx_images_original already exists" AS message'
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Índice patient_name
|
|
SELECT COUNT(*) INTO @idx_exists
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = @db_name
|
|
AND table_name = @table_name
|
|
AND index_name = 'idx_patient_name';
|
|
|
|
SET @sql = IF(@idx_exists = 0,
|
|
'CREATE INDEX idx_patient_name ON images(patient_name(100))',
|
|
'SELECT "Index idx_patient_name already exists" AS message'
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Índice image_guid
|
|
SELECT COUNT(*) INTO @idx_exists
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = @db_name
|
|
AND table_name = @table_name
|
|
AND index_name = 'idx_image_guid';
|
|
|
|
SET @sql = IF(@idx_exists = 0,
|
|
'CREATE INDEX idx_image_guid ON images(image_guid)',
|
|
'SELECT "Index idx_image_guid already exists" AS message'
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Índice created_at
|
|
SELECT COUNT(*) INTO @idx_exists
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = @db_name
|
|
AND table_name = @table_name
|
|
AND index_name = 'idx_created_at';
|
|
|
|
SET @sql = IF(@idx_exists = 0,
|
|
'CREATE INDEX idx_created_at ON images(created_at)',
|
|
'SELECT "Index idx_created_at already exists" AS message'
|
|
);
|
|
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- ================================================================
|
|
-- PARTE 6: VALIDAÇÃO FINAL
|
|
-- ================================================================
|
|
|
|
SELECT 'Migration completed successfully!' AS result;
|
|
SELECT 'Table structure:' AS info;
|
|
DESCRIBE images;
|
|
|
|
-- ================================================================
|
|
-- FIM DA MIGRATION
|
|
-- ================================================================ |