WikiLerni/models/update-2020-11.sql

37 lines
1.8 KiB
SQL

--
-- Évolutions base de données nouvelle version WikiLerni avec notamment ajout des quizs groupés.
--
CREATE TABLE `Groups` (
`id` int(11) NOT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`slug` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
`introduction` text COLLATE utf8mb4_unicode_ci,
`publishingAt` datetime DEFAULT NULL COMMENT 'If null, the questionnaire with the questions for each element of the group is not published, but its elements may be.',
`language` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fr',
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
`CreatorId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `Groups`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `Questionnaires_slug_unique` (`slug`),
ADD KEY `CreatorId` (`CreatorId`);
ALTER TABLE `Groups`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `Answers` CHANGE `QuestionnaireId` `QuestionnaireId` INT(11) NULL DEFAULT NULL;
ALTER TABLE `Answers` ADD `GroupId` INT(11) NULL DEFAULT NULL AFTER `QuestionnaireId`;
ALTER TABLE `Answers` ADD CONSTRAINT `Answers_ibfk_9` FOREIGN KEY (`GroupId`) REFERENCES `Groups`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Questionnaires` ADD `rankInGroup` INT(2) UNSIGNED NULL DEFAULT NULL COMMENT 'Allows you to classify the questionnaire if it belongs to a group.' AFTER `estimatedTime`;
ALTER TABLE `Questionnaires` ADD `GroupId` INT(11) NULL DEFAULT NULL AFTER `CreatorId`;
ALTER TABLE `Questionnaires` ADD CONSTRAINT `Questionnaires_ibfk_2` FOREIGN KEY (`GroupId`) REFERENCES `Groups`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `Subscriptions`
DROP `noticeOk`;
ALTER TABLE `Users`
DROP `newsletterOk`;