110 lines
3.7 KiB
SQL
110 lines
3.7 KiB
SQL
-- ============================================
|
|
-- WebVal - Mise à jour chat_conversations
|
|
-- Ajout colonnes pour tracking API (sécurisé)
|
|
-- ============================================
|
|
|
|
-- Désactiver les erreurs temporairement
|
|
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;
|
|
|
|
-- Ajouter colonnes une par une (ignore si existe déjà)
|
|
SET @s = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_conversations'
|
|
AND column_name = 'provider') > 0,
|
|
'SELECT ''Column provider already exists''',
|
|
'ALTER TABLE chat_conversations ADD COLUMN provider VARCHAR(50) DEFAULT ''ollama'''
|
|
));
|
|
PREPARE stmt FROM @s;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @s = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_conversations'
|
|
AND column_name = 'model_used') > 0,
|
|
'SELECT ''Column model_used already exists''',
|
|
'ALTER TABLE chat_conversations ADD COLUMN model_used VARCHAR(100) DEFAULT ''ministral-3:3b'''
|
|
));
|
|
PREPARE stmt FROM @s;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @s = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_conversations'
|
|
AND column_name = 'api_response_time_ms') > 0,
|
|
'SELECT ''Column api_response_time_ms already exists''',
|
|
'ALTER TABLE chat_conversations ADD COLUMN api_response_time_ms INT DEFAULT NULL'
|
|
));
|
|
PREPARE stmt FROM @s;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @s = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_conversations'
|
|
AND column_name = 'id_enseignant') > 0,
|
|
'SELECT ''Column id_enseignant already exists''',
|
|
'ALTER TABLE chat_conversations ADD COLUMN id_enseignant INT DEFAULT NULL'
|
|
));
|
|
PREPARE stmt FROM @s;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Index
|
|
SET @s = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_conversations'
|
|
AND index_name = 'idx_provider') > 0,
|
|
'SELECT ''Index idx_provider already exists''',
|
|
'ALTER TABLE chat_conversations ADD INDEX idx_provider (provider)'
|
|
));
|
|
PREPARE stmt FROM @s;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Foreign key (plus complexe, on teste avec SHOW CREATE TABLE)
|
|
SET @fk_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_conversations'
|
|
AND referenced_table_name = 'utilisateurs'
|
|
AND column_name = 'id_enseignant');
|
|
|
|
SET @s = IF(@fk_exists > 0,
|
|
'SELECT ''Foreign key already exists''',
|
|
'ALTER TABLE chat_conversations ADD FOREIGN KEY (id_enseignant) REFERENCES utilisateurs(id_utilisateur) ON DELETE SET NULL'
|
|
);
|
|
PREPARE stmt FROM @s;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Colonne api_cost dans chat_messages
|
|
SET @s = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_messages'
|
|
AND column_name = 'api_cost') > 0,
|
|
'SELECT ''Column api_cost already exists''',
|
|
'ALTER TABLE chat_messages ADD COLUMN api_cost DECIMAL(10,6) DEFAULT 0.000000'
|
|
));
|
|
PREPARE stmt FROM @s;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Restaurer les notes SQL
|
|
SET SQL_NOTES=@OLD_SQL_NOTES;
|
|
|
|
-- Remplir id_enseignant rétroactivement
|
|
UPDATE chat_conversations cc
|
|
JOIN tentatives_eleves te ON cc.id_tentative = te.id_tentative
|
|
JOIN evaluations e ON te.id_evaluation = e.id_evaluation
|
|
SET cc.id_enseignant = e.id_enseignant
|
|
WHERE cc.id_enseignant IS NULL;
|
|
|
|
SELECT '✅ Colonnes ajoutées avec succès' as Status;
|