CMS-CSE/maj/sql/maj.sql

DROP TABLE IF EXISTS `type_access`;
DROP TABLE IF EXISTS `access_exception`;

CREATE TABLE `access_exception` (
  `id` int NOT NULL,
  `id_access` int NOT NULL,
  `exception` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_access` (`id_access`)
) ENGINE=InnoDB;

CREATE TABLE `type_access` (
  `id` varchar(7) NOT NULL,
  `id_type` int NOT NULL,
  `id_access` int NOT NULL,
  `id_exception` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_access_type` (`id_access`),
  KEY `idx_type_access` (`id_type`),
  KEY `idx_exception` (`id_exception`)
) ENGINE=InnoDB;

ALTER TABLE `type_access`
  ADD CONSTRAINT `fk_access_type` FOREIGN KEY (`id_access`) REFERENCES `access` (`id`),
  ADD CONSTRAINT `fk_exception` FOREIGN KEY (`id_exception`) REFERENCES `access_exception` (`id`),
  ADD CONSTRAINT `fk_type_access` FOREIGN KEY (`id_type`) REFERENCES `type_user` (`id`);

ALTER TABLE `access_exception`
  ADD CONSTRAINT `id_access` FOREIGN KEY (`id_access`) REFERENCES `access` (`id`);

INSERT INTO `access_exception` (`id`, `id_access`, `exception`) VALUES
(1, 16, 'salaire'),
(2, 17, 'salaire');

INSERT INTO `type_access` (`id`, `id_type`, `id_access`, `id_exception`) VALUES
('3#10', 3, 10, NULL);

INSERT INTO `type_access` (`id`, `id_type`, `id_access`, `id_exception`) VALUES
('4#10', 4, 10, NULL),
('4#11', 4, 11, NULL),
('4#12', 4, 12, NULL),
('4#13', 4, 13, NULL),
('4#14', 4, 14, NULL),
('4#15', 4, 15, NULL),
('4#16', 4, 16, 1),
('4#17', 4, 17, 2),
('4#4', 4, 4, NULL),
('4#5', 4, 5, NULL),
('4#6', 4, 6, NULL),
('4#7', 4, 7, NULL),
('4#8', 4, 8, NULL),
('4#9', 4, 9, NULL);

INSERT INTO `type_access` (`id`, `id_type`, `id_access`, `id_exception`) VALUES
('5#1', 5, 1, NULL),
('5#10', 5, 10, NULL),
('5#11', 5, 11, NULL),
('5#12', 5, 12, NULL),
('5#13', 5, 13, NULL),
('5#14', 5, 14, NULL),
('5#15', 5, 15, NULL),
('5#16', 5, 16, NULL),
('5#17', 5, 17, NULL),
('5#2', 5, 2, NULL),
('5#4', 5, 4, NULL),
('5#5', 5, 5, NULL),
('5#6', 5, 6, NULL),
('5#7', 5, 7, NULL),
('5#8', 5, 8, NULL),
('5#9', 5, 9, NULL);

INSERT INTO `type_access` (`id`, `id_type`, `id_access`, `id_exception`) VALUES
('6#10', 6, 10, NULL),
('6#11', 6, 11, NULL),
('6#12', 6, 12, NULL),
('6#14', 6, 14, NULL),
('6#15', 6, 15, NULL),
('6#16', 6, 16, 1),
('6#17', 6, 17, 2),
('6#4', 6, 4, NULL),
('6#5', 6, 5, NULL),
('6#7', 6, 7, NULL),
('6#8', 6, 8, NULL);

INSERT INTO `type_access` (`id`, `id_type`, `id_access`, `id_exception`) VALUES
('7#1', 7, 1, NULL),
('7#16', 7, 16, NULL),
('7#17', 7, 17, NULL);