Fab_Blab/WikiLerni
Fab_Blab
/
WikiLerni
1
1
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
WikiLerni/models/wikilerni-crea.sql

537 lines
17 KiB
SQL
Raw Permalink Blame History

-- 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 */;
Reference in New Issue View Git Blame Copy Permalink