stephan
953775d9ca
This commit removes the deprecated SQL views `v_eve_acces_actif` and `v_eve_acces_permissions`, replacing them with inline SQL queries in the PHP code. This change avoids issues with non-copiable DEFINER attributes in views and enhances the flexibility of access management. The new functions `fxEveAccesActifBody`, `fxEveAccesActifSql`, and `fxEveAccesPermSql` are introduced to encapsulate the SQL logic directly within the application, improving maintainability and performance.
126 lines
5.2 KiB
SQL
126 lines
5.2 KiB
SQL
/*
|
|
MSIN - Acces evenement v2 (phase 1)
|
|
Tables NEUVES uniquement. Aucune modification des tables legacy.
|
|
|
|
Legacy inchange :
|
|
inscriptions_comptes.com_eve_promoteur
|
|
inscriptions_comptes.com_rapports
|
|
inscriptions_comptes.com_eve_modifiable
|
|
|
|
Ordre :
|
|
1) Ce fichier
|
|
2) MSIN-eve-acces-v2-phase1-seed.sql
|
|
*/
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
|
|
/* 1. Catalogue des permissions */
|
|
CREATE TABLE IF NOT EXISTS `inscriptions_eve_permissions` (
|
|
`perm_key` varchar(64) NOT NULL,
|
|
`perm_group` varchar(32) NOT NULL DEFAULT 'general',
|
|
`perm_label_fr` varchar(100) NOT NULL,
|
|
`perm_label_en` varchar(100) NOT NULL,
|
|
`perm_description_fr` varchar(255) DEFAULT NULL,
|
|
`perm_description_en` varchar(255) DEFAULT NULL,
|
|
`perm_phase` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT '1=mobile API, 2=web promoteur, 3=rapports',
|
|
`perm_actif` tinyint(1) unsigned NOT NULL DEFAULT 1,
|
|
`perm_tri` smallint(5) unsigned NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`perm_key`),
|
|
KEY `idx_perm_group` (`perm_group`, `perm_actif`, `perm_tri`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='Catalogue permissions v2';
|
|
|
|
|
|
/* 2. Roles (super admin seulement) */
|
|
CREATE TABLE IF NOT EXISTS `inscriptions_eve_roles` (
|
|
`role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`role_code` varchar(32) NOT NULL,
|
|
`role_label_fr` varchar(80) NOT NULL,
|
|
`role_label_en` varchar(80) NOT NULL,
|
|
`role_description_fr` varchar(255) DEFAULT NULL,
|
|
`role_description_en` varchar(255) DEFAULT NULL,
|
|
`role_icone` varchar(32) DEFAULT NULL,
|
|
`role_systeme` tinyint(1) unsigned NOT NULL DEFAULT 1,
|
|
`role_actif` tinyint(1) unsigned NOT NULL DEFAULT 1,
|
|
`role_tri` smallint(5) unsigned NOT NULL DEFAULT 0,
|
|
`role_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`role_updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`role_id`),
|
|
UNIQUE KEY `uk_role_code` (`role_code`),
|
|
KEY `idx_role_actif` (`role_actif`, `role_tri`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='Roles v2';
|
|
|
|
|
|
/* 3. Permissions par role */
|
|
CREATE TABLE IF NOT EXISTS `inscriptions_eve_role_permissions` (
|
|
`role_id` int(10) unsigned NOT NULL,
|
|
`perm_key` varchar(64) NOT NULL,
|
|
PRIMARY KEY (`role_id`, `perm_key`),
|
|
KEY `idx_erp_perm` (`perm_key`),
|
|
CONSTRAINT `fk_erp_role`
|
|
FOREIGN KEY (`role_id`) REFERENCES `inscriptions_eve_roles` (`role_id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_erp_perm`
|
|
FOREIGN KEY (`perm_key`) REFERENCES `inscriptions_eve_permissions` (`perm_key`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='Matrice role-permission';
|
|
|
|
|
|
/* 4. Assignations compte x evenement */
|
|
CREATE TABLE IF NOT EXISTS `inscriptions_eve_acces` (
|
|
`ea_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`com_id` int(10) unsigned NOT NULL,
|
|
`eve_id` int(10) unsigned NOT NULL,
|
|
`role_id` int(10) unsigned NOT NULL,
|
|
`ea_statut` enum('actif','revoke','invite') NOT NULL DEFAULT 'actif',
|
|
`ea_expires_at` datetime DEFAULT NULL,
|
|
`ea_expire_days` smallint(5) unsigned DEFAULT NULL,
|
|
`ea_granted_by` int(10) unsigned DEFAULT NULL,
|
|
`ea_note` varchar(255) DEFAULT NULL,
|
|
`ea_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`ea_updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
|
`ea_revoked_at` datetime DEFAULT NULL,
|
|
`ea_revoked_by` int(10) unsigned DEFAULT NULL,
|
|
PRIMARY KEY (`ea_id`),
|
|
UNIQUE KEY `uk_com_eve` (`com_id`, `eve_id`),
|
|
KEY `idx_eve_actif` (`eve_id`, `ea_statut`),
|
|
KEY `idx_com_actif` (`com_id`, `ea_statut`),
|
|
KEY `idx_expires` (`ea_expires_at`, `ea_statut`),
|
|
KEY `idx_role` (`role_id`),
|
|
CONSTRAINT `fk_ea_role`
|
|
FOREIGN KEY (`role_id`) REFERENCES `inscriptions_eve_roles` (`role_id`)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='Acces v2 par compte et evenement';
|
|
|
|
|
|
/* 5. Journal audit */
|
|
CREATE TABLE IF NOT EXISTS `inscriptions_eve_acces_log` (
|
|
`log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`ea_id` int(10) unsigned DEFAULT NULL,
|
|
`com_id` int(10) unsigned NOT NULL,
|
|
`eve_id` int(10) unsigned NOT NULL,
|
|
`role_id` int(10) unsigned DEFAULT NULL,
|
|
`log_action` varchar(32) NOT NULL,
|
|
`log_detail` text DEFAULT NULL,
|
|
`log_by_com_id` int(10) unsigned DEFAULT NULL,
|
|
`log_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`log_id`),
|
|
KEY `idx_log_eve` (`eve_id`, `log_created_at`),
|
|
KEY `idx_log_com` (`com_id`, `log_created_at`),
|
|
KEY `idx_log_ea` (`ea_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='Audit acces v2';
|
|
|
|
|
|
/* 6-7. Plus de vues SQL.
|
|
Les anciennes vues v_eve_acces_actif / v_eve_acces_permissions portaient un
|
|
DEFINER non copiable par Navicat / la releve. La logique est desormais en
|
|
sous-requetes directement dans le code PHP (php/inc_fx_eve_acces.php et
|
|
v3_ci4/app/Libraries/EventAccess.php). On supprime les vues si elles existent. */
|
|
DROP VIEW IF EXISTS `v_eve_acces_permissions`;
|
|
DROP VIEW IF EXISTS `v_eve_acces_actif`;
|