-- phpMyAdmin SQL Dump -- version 4.6.6deb5 -- https://www.phpmyadmin.net/ -- -- Client : localhost:3306 -- Généré le : Mer 21 Octobre 2020 à 17:55 -- Version du serveur : 5.7.31-0ubuntu0.18.04.1 -- Version de PHP : 7.2.24-0ubuntu0.18.04.7 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Base de données : `fabblab_wikilerni` -- -- -------------------------------------------------------- -- -- Structure de la table `Answers` -- CREATE TABLE `Answers` ( `id` int(11) NOT NULL, `nbQuestions` int(2) UNSIGNED NOT NULL COMMENT 'The number of questions in the quiz may change between answers.', `nbCorrectAnswers` int(2) UNSIGNED NOT NULL, `duration` int(5) UNSIGNED DEFAULT NULL, `createdAt` datetime NOT NULL, `UserId` int(11) NOT NULL, `QuestionnaireId` int(11) DEFAULT NULL, `GroupId` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Structure de la table `Choices` -- CREATE TABLE `Choices` ( `id` int(11) NOT NULL, `text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `isCorrect` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Necessary to designate the correct answers to a quiz.', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `QuestionId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `Groups` -- 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; -- -------------------------------------------------------- -- -- Structure de la table `Illustrations` -- CREATE TABLE `Illustrations` ( `id` int(11) NOT NULL, `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Part of the url corresponding to the file name.', `alt` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `caption` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `QuestionnaireId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `Links` -- CREATE TABLE `Links` ( `id` int(11) NOT NULL, `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `anchor` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `QuestionnaireId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `Pauses` -- CREATE TABLE `Pauses` ( `id` int(11) NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `startingAt` datetime NOT NULL, `endingAt` datetime NOT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `SubscriptionId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `Payments` -- CREATE TABLE `Payments` ( `id` int(11) NOT NULL, `clientName` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `amount` int(4) UNSIGNED NOT NULL, `codeCommande` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `UserId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `QuestionnaireAccesses` -- CREATE TABLE `QuestionnaireAccesses` ( `createdAt` datetime NOT NULL, `selfCreatedOk` tinyint(1) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'True if the user accesses the questionnaire on his own by answering it.', `QuestionnaireId` int(11) NOT NULL, `UserId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Structure de la table `QuestionnaireClassifications` -- CREATE TABLE `QuestionnaireClassifications` ( `createdAt` datetime NOT NULL, `QuestionnaireId` int(11) NOT NULL, `TagId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Structure de la table `Questionnaires` -- CREATE TABLE `Questionnaires` ( `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 NOT NULL, `keywords` text COLLATE utf8mb4_unicode_ci COMMENT 'Not published but only used for research.', `publishingAt` datetime DEFAULT NULL COMMENT 'If null, the questionnaire is not published (=draft).', `isPublished` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'May depend on factors other than the date of publication.', `language` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fr', `estimatedTime` enum('short','medium','long') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'medium' COMMENT 'Provides an estimate of the time required to complete this questionnaire.', `rankInGroup` int(2) UNSIGNED DEFAULT NULL COMMENT 'Allows you to classify the questionnaire if it belongs to a group.', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `CreatorId` int(11) NOT NULL, `GroupId` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `Questions` -- CREATE TABLE `Questions` ( `id` int(11) NOT NULL, `text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `explanation` text COLLATE utf8mb4_unicode_ci COMMENT 'Allows you to display explanation for this question after checking the user''s answers.', `rank` int(2) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Ranking of the answer among those proposed.', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `QuestionnaireId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `Subscriptions` -- CREATE TABLE `Subscriptions` ( `id` int(11) NOT NULL, `numberOfDays` int(6) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The duration in number of days of the subscription', `receiptDays` varchar(7) DEFAULT '1234567' COMMENT 'Days on which the user has chosen to receive new questionnaires. The same numbers as in Mysql''s DAYOFWEEK function.', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `lastProcessingAt` date NOT NULL DEFAULT '1970-01-01' COMMENT 'Date of last subscription processing (sending quiz, etc.).', `UserId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Structure de la table `Tags` -- CREATE TABLE `Tags` ( `id` int(11) NOT NULL, `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `slug` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `UserDeleteds` -- CREATE TABLE `UserDeleteds` ( `id` int(11) NOT NULL, `createdAt` datetime NOT NULL, `deletedAt` datetime NOT NULL, `wasValided` tinyint(1) NOT NULL DEFAULT '0', `wasPremium` tinyint(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Structure de la table `Users` -- CREATE TABLE `Users` ( `id` int(11) NOT NULL, `name` varchar(70) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `status` enum('admin','manager','creator','user') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'user', `language` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fr', `adminComments` text COLLATE utf8mb4_unicode_ci COMMENT 'Not published.', `connectedAt` datetime DEFAULT NULL, `smtp` int(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Allows to assign a different SMTP server in case of deliverability issues.', `timeDifference` int(3) NOT NULL DEFAULT '0' COMMENT 'Time difference (in minutes) from UTC.', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `GodfatherId` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Index pour les tables exportées -- -- -- Index pour la table `Answers` -- ALTER TABLE `Answers` ADD PRIMARY KEY (`id`), ADD KEY `UserId` (`UserId`), ADD KEY `QuestionnaireId` (`QuestionnaireId`), ADD KEY `GroupId` (`GroupId`); -- -- Index pour la table `Choices` -- ALTER TABLE `Choices` ADD PRIMARY KEY (`id`), ADD KEY `QuestionId` (`QuestionId`); -- -- Index pour la table `Groups` -- ALTER TABLE `Groups` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `Questionnaires_slug_unique` (`slug`), ADD KEY `CreatorId` (`CreatorId`); -- -- Index pour la table `Illustrations` -- ALTER TABLE `Illustrations` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `Illustrations_url_unique` (`url`), ADD KEY `QuestionnaireId` (`QuestionnaireId`); -- -- Index pour la table `Links` -- ALTER TABLE `Links` ADD PRIMARY KEY (`id`), ADD KEY `QuestionnaireId` (`QuestionnaireId`); -- -- Index pour la table `Pauses` -- ALTER TABLE `Pauses` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `SubscriptionId` (`SubscriptionId`); -- -- Index pour la table `Payments` -- ALTER TABLE `Payments` ADD PRIMARY KEY (`id`), ADD KEY `UserId` (`UserId`); -- -- Index pour la table `QuestionnaireAccesses` -- ALTER TABLE `QuestionnaireAccesses` ADD PRIMARY KEY (`QuestionnaireId`,`UserId`), ADD KEY `UserId` (`UserId`); -- -- Index pour la table `QuestionnaireClassifications` -- ALTER TABLE `QuestionnaireClassifications` ADD PRIMARY KEY (`QuestionnaireId`,`TagId`), ADD KEY `TagId` (`TagId`); -- -- Index pour la table `Questionnaires` -- ALTER TABLE `Questionnaires` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `Questionnaires_slug_unique` (`slug`), ADD KEY `isPublished` (`isPublished`), ADD KEY `CreatorId` (`CreatorId`), ADD KEY `GroupId` (`GroupId`); -- -- Index pour la table `Questions` -- ALTER TABLE `Questions` ADD PRIMARY KEY (`id`), ADD KEY `QuestionnaireId` (`QuestionnaireId`); -- -- Index pour la table `Subscriptions` -- ALTER TABLE `Subscriptions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `UserId` (`UserId`); -- -- Index pour la table `Tags` -- ALTER TABLE `Tags` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `Tags_name_unique` (`name`), ADD UNIQUE KEY `Tags_slug_unique` (`slug`); -- -- Index pour la table `UserDeleteds` -- ALTER TABLE `UserDeleteds` ADD PRIMARY KEY (`id`); -- -- Index pour la table `Users` -- ALTER TABLE `Users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `Users_email_unique` (`email`), ADD KEY `GodfatherId` (`GodfatherId`); -- -- AUTO_INCREMENT pour les tables exportées -- -- -- AUTO_INCREMENT pour la table `Answers` -- ALTER TABLE `Answers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36; -- -- AUTO_INCREMENT pour la table `Choices` -- ALTER TABLE `Choices` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1044; -- -- AUTO_INCREMENT pour la table `Groups` -- ALTER TABLE `Groups` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT pour la table `Illustrations` -- ALTER TABLE `Illustrations` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=42; -- -- AUTO_INCREMENT pour la table `Links` -- ALTER TABLE `Links` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38; -- -- AUTO_INCREMENT pour la table `Pauses` -- ALTER TABLE `Pauses` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT pour la table `Payments` -- ALTER TABLE `Payments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT pour la table `Questionnaires` -- ALTER TABLE `Questionnaires` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=60; -- -- AUTO_INCREMENT pour la table `Questions` -- ALTER TABLE `Questions` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=338; -- -- AUTO_INCREMENT pour la table `Subscriptions` -- ALTER TABLE `Subscriptions` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=27; -- -- AUTO_INCREMENT pour la table `Tags` -- ALTER TABLE `Tags` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=54; -- -- AUTO_INCREMENT pour la table `UserDeleteds` -- ALTER TABLE `UserDeleteds` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT pour la table `Users` -- ALTER TABLE `Users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30; -- -- Contraintes pour les tables exportées -- -- -- Contraintes pour la table `Answers` -- ALTER TABLE `Answers` ADD CONSTRAINT `Answers_ibfk_7` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `Answers_ibfk_8` FOREIGN KEY (`QuestionnaireId`) REFERENCES `Questionnaires` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `Answers_ibfk_9` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Choices` -- ALTER TABLE `Choices` ADD CONSTRAINT `Choices_ibfk_1` FOREIGN KEY (`QuestionId`) REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Illustrations` -- ALTER TABLE `Illustrations` ADD CONSTRAINT `Illustrations_ibfk_1` FOREIGN KEY (`QuestionnaireId`) REFERENCES `Questionnaires` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Links` -- ALTER TABLE `Links` ADD CONSTRAINT `Links_ibfk_1` FOREIGN KEY (`QuestionnaireId`) REFERENCES `Questionnaires` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Pauses` -- ALTER TABLE `Pauses` ADD CONSTRAINT `Pauses_ibfk_1` FOREIGN KEY (`SubscriptionId`) REFERENCES `Subscriptions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Payments` -- ALTER TABLE `Payments` ADD CONSTRAINT `Payments_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `QuestionnaireAccesses` -- ALTER TABLE `QuestionnaireAccesses` ADD CONSTRAINT `QuestionnaireAccesses_ibfk_1` FOREIGN KEY (`QuestionnaireId`) REFERENCES `Questionnaires` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `QuestionnaireAccesses_ibfk_2` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `QuestionnaireClassifications` -- ALTER TABLE `QuestionnaireClassifications` ADD CONSTRAINT `QuestionnaireClassifications_ibfk_1` FOREIGN KEY (`QuestionnaireId`) REFERENCES `Questionnaires` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `QuestionnaireClassifications_ibfk_2` FOREIGN KEY (`TagId`) REFERENCES `Tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Questionnaires` -- ALTER TABLE `Questionnaires` ADD CONSTRAINT `Questionnaires_ibfk_1` FOREIGN KEY (`CreatorId`) REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, ADD CONSTRAINT `Questionnaires_ibfk_2` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`id`); -- -- Contraintes pour la table `Questions` -- ALTER TABLE `Questions` ADD CONSTRAINT `Questions_ibfk_1` FOREIGN KEY (`QuestionnaireId`) REFERENCES `Questionnaires` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Subscriptions` -- ALTER TABLE `Subscriptions` ADD CONSTRAINT `Subscriptions_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `Users` -- ALTER TABLE `Users` ADD CONSTRAINT `Users_ibfk_1` FOREIGN KEY (`GodfatherId`) REFERENCES `Users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;