Mise à jour des fonctions de la bdd avec le nouveau modèle, renommage des fichier pour plus de clarte et ajout des documents et infos

1 files changed, 143 insertions, 0 deletions

diff --git a/Documents_et_infos/Bdd_mysql/mysql_alphajob.sql b/Documents_et_infos/Bdd_mysql/mysql_alphajob.sql
new file mode 100644
index 0000000..d556d0a
--- /dev/null
+++ b/Documents_et_infos/Bdd_mysql/mysql_alphajob.sql
@@ -0,0 +1,143 @@
+-- MySQL Script generated by MySQL Workbench
+-- Wed Mar  2 13:18:27 2022
+-- Model: New Model    Version: 1.0
+-- MySQL Workbench Forward Engineering
+
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
+
+-- -----------------------------------------------------
+-- Schema exophp
+-- -----------------------------------------------------
+
+-- -----------------------------------------------------
+-- Schema exophp
+-- -----------------------------------------------------
+CREATE SCHEMA IF NOT EXISTS `exophp` DEFAULT CHARACTER SET utf8 ;
+USE `exophp` ;
+
+-- -----------------------------------------------------
+-- Table `exophp`.`userAccount`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `exophp`.`userAccount` (
+  `userId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `email` VARCHAR(128) NOT NULL,
+  `password` VARCHAR(128) NOT NULL,
+  `tokenAutoLogin` VARCHAR(128) NULL DEFAULT NULL,
+  `inscriptionDate` TIMESTAMP NOT NULL,
+  `isClient` TINYINT(1) UNSIGNED NOT NULL,
+  `isPro` TINYINT(1) UNSIGNED NOT NULL,
+  `isAdmin` TINYINT(1) UNSIGNED NOT NULL,
+  PRIMARY KEY (`userId`))
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8;
+
+
+-- -----------------------------------------------------
+-- Table `exophp`.`jobCategrory`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `exophp`.`jobCategrory` (
+  `jobCategoryId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `categoryName` VARCHAR(128) NOT NULL,
+  PRIMARY KEY (`jobCategoryId`))
+ENGINE = InnoDB;
+
+
+-- -----------------------------------------------------
+-- Table `exophp`.`mission`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `exophp`.`mission` (
+  `missionId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `date` TIMESTAMP NOT NULL,
+  `subject` VARCHAR(128) NOT NULL,
+  `note` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
+  `review` TEXT NULL DEFAULT NULL,
+  `acceptedByPro` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
+  `validatedByClient` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
+  `validatedByPro` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
+  `clientId` INT UNSIGNED NOT NULL,
+  `proId` INT UNSIGNED NOT NULL,
+  `jobCategroryId` INT UNSIGNED NOT NULL,
+  PRIMARY KEY (`missionId`),
+  INDEX `fk_mission_clientId_idx` (`clientId` ASC) VISIBLE,
+  INDEX `fk_mission_proId_idx` (`proId` ASC) VISIBLE,
+  INDEX `fk_mission_jobCategroryId_idx` (`jobCategroryId` ASC) VISIBLE,
+  CONSTRAINT `fk_mission_userId`
+    FOREIGN KEY (`clientId`)
+    REFERENCES `exophp`.`userAccount` (`userId`)
+    ON DELETE NO ACTION
+    ON UPDATE NO ACTION,
+  CONSTRAINT `fk_mission_consultantId`
+    FOREIGN KEY (`proId`)
+    REFERENCES `exophp`.`userAccount` (`userId`)
+    ON DELETE NO ACTION
+    ON UPDATE NO ACTION,
+  CONSTRAINT `fk_mission_jobcategrory1`
+    FOREIGN KEY (`jobCategroryId`)
+    REFERENCES `exophp`.`jobCategrory` (`jobCategoryId`)
+    ON DELETE NO ACTION
+    ON UPDATE NO ACTION)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8;
+
+
+-- -----------------------------------------------------
+-- Table `exophp`.`userInfo`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `exophp`.`userInfo` (
+  `lastName` VARCHAR(128) NOT NULL,
+  `firstName` VARCHAR(128) NOT NULL,
+  `phoneNumber` VARCHAR(10) NOT NULL,
+  `adress` VARCHAR(128) NOT NULL,
+  `zipCode` VARCHAR(6) NOT NULL,
+  `city` VARCHAR(128) NOT NULL,
+  `job` VARCHAR(128) NULL DEFAULT NULL,
+  `degree` VARCHAR(128) NULL DEFAULT NULL,
+  `capability` TEXT NULL DEFAULT NULL,
+  `description` TEXT NULL DEFAULT NULL,
+  `userId` INT UNSIGNED NOT NULL,
+  PRIMARY KEY (`userId`),
+  INDEX `fk_userinfo_userId_idx` (`userId` ASC) VISIBLE,
+  CONSTRAINT `fk_userinfo_userId`
+    FOREIGN KEY (`userId`)
+    REFERENCES `exophp`.`userAccount` (`userId`)
+    ON DELETE CASCADE
+    ON UPDATE CASCADE)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8;
+
+
+-- -----------------------------------------------------
+-- Table `exophp`.`category`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `exophp`.`category` (
+  `category_id` INT NOT NULL,
+  `name` VARCHAR(255) NOT NULL,
+  PRIMARY KEY (`category_id`));
+
+
+-- -----------------------------------------------------
+-- Table `exophp`.`userJob`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `exophp`.`userJob` (
+  `jobCategroryId` INT UNSIGNED NOT NULL,
+  `userId` INT UNSIGNED NOT NULL,
+  INDEX `fk_userJob_jobcategrory1_idx` (`jobCategroryId` ASC) VISIBLE,
+  INDEX `fk_userJob_userinfo1_idx` (`userId` ASC) VISIBLE,
+  CONSTRAINT `fk_userJob_jobCategroryId`
+    FOREIGN KEY (`jobCategroryId`)
+    REFERENCES `exophp`.`jobCategrory` (`jobCategoryId`)
+    ON DELETE NO ACTION
+    ON UPDATE NO ACTION,
+  CONSTRAINT `fk_userJob_userId`
+    FOREIGN KEY (`userId`)
+    REFERENCES `exophp`.`userInfo` (`userId`)
+    ON DELETE NO ACTION
+    ON UPDATE NO ACTION)
+ENGINE = InnoDB;
+
+
+SET SQL_MODE=@OLD_SQL_MODE;
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;