diff options
Diffstat (limited to 'Database/dbmain.php')
| -rw-r--r-- | Database/dbmain.php | 181 |
1 files changed, 131 insertions, 50 deletions
diff --git a/Database/dbmain.php b/Database/dbmain.php index 43e39ef..532bb7c 100644 --- a/Database/dbmain.php +++ b/Database/dbmain.php @@ -10,7 +10,7 @@ // protected: accesible depuis l'extérieur et donc via extend mais non modifiable // public: accessible et modifiable depuis l'extérieur // final: ne peut etre ré-écris (overload) -// + class DbMain { // Salt pour chiffrement private $salt = "cTKXHBoN3oxymlhNem2h"; @@ -19,54 +19,120 @@ class DbMain { private $host = "127.0.0.1"; private $user = "root"; private $pass = ""; + private $debug = True; // Déclaration des tables - private $database = "ExoPhp"; - // UserAccount, infos du compte nécessaire à la connection + private $database = "alphajob"; + // Timestamp auto si non renseigné - private $createTableUserAccount = "userAccount ( - id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, - email VARCHAR(128) NOT NULL, - password VARCHAR(128) NOT NULL, - tokenAutoLogin VARCHAR(128), - inscriptionDate TIMESTAMP NOT NULL, - isUser BOOL NOT NULL, - isConsultant BOOL NOT NULL, - isAdmin BOOL NOT NULL - )"; - // Infos complémentaires des utilisateur, liée à userAccount par userId à id - private $createTableUserInfo = "userInfo ( - lastname VARCHAR(128) NOT NULL, - firstname VARCHAR(128) NOT NULL, - job varchar(128), - degree VARCHAR(128), - capability TEXT, - description TEXT, - phoneNumber VARCHAR(10), - adress VARCHAR(128), - zipCode VARCHAR(6), - city VARCHAR(128), - userId INT UNSIGNED PRIMARY KEY, - FOREIGN KEY (userId) - REFERENCES userAccount(id) - )"; - // Infos sur les missions effectuées, liée à userAccount par userId et consultantId à id - private $createTableMission = "mission ( - id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, - date TIMESTAMP NOT NULL, - subject VARCHAR(128), - note INT(1) UNSIGNED, - review TEXT, - userId INT UNSIGNED NOT NULL, - FOREIGN KEY (userId) - REFERENCES userAccount(id), - consultantId INT UNSIGNED NOT NULL, - FOREIGN KEY (consultantId) - REFERENCES userAccount(id) - )"; - + // UserAccount, infos du compte nécessaire à la connection + private $createTableUserAccount = "CREATE TABLE IF NOT EXISTS `alphajob`.`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) NOT NULL, + `isPro` TINYINT(1) NOT NULL, + `isAdmin` TINYINT(1) NOT NULL, + PRIMARY KEY (`userId`) + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;"; + + // Infos complémentaires des utilisateur, liée à userAccount par userId + private $createTableUserInfo = "CREATE TABLE IF NOT EXISTS `alphajob`.`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 `alphajob`.`userAccount` (`userId`) + ON DELETE CASCADE + ON UPDATE CASCADE + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;"; + + // Liste des categories d'emploie possible + private $createTableJobCategory = "CREATE TABLE IF NOT EXISTS `alphajob`.`jobCategory` ( + `jobCategoryId` INT UNSIGNED NOT NULL, + `categoryName` VARCHAR(128) NOT NULL, + PRIMARY KEY (`jobCategoryId`) + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;"; + + // Assosications des emploies à un utilisateur + private $createTableUserJob = "CREATE TABLE IF NOT EXISTS `alphajob`.`userJob` ( + `userId` INT UNSIGNED NOT NULL, + `jobCategoryId` INT UNSIGNED NOT NULL, + INDEX `fk_userInfo_userId_idx` (`userId` ASC) VISIBLE, + INDEX `fk_jobCategory_jobCategoryId_idx` (`jobCategoryId` ASC) VISIBLE, + CONSTRAINT `fk_userJob_userId` + FOREIGN KEY (`userId`) + REFERENCES `alphajob`.`userAccount` (`userId`) + ON DELETE NO ACTION + ON UPDATE CASCADE, + CONSTRAINT `fk_userJob_jobCategoryId` + FOREIGN KEY (`jobCategoryId`) + REFERENCES `alphajob`.`jobCategory` (`jobCategoryId`) + ON DELETE NO ACTION + ON UPDATE CASCADE + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;"; + + // Infos sur les missions effectuées, liée à userAccount par userId et consultantId + private $createTableMission = "CREATE TABLE IF NOT EXISTS `alphajob`.`mission` ( + `missionId` INT UNSIGNED NOT NULL AUTO_INCREMENT, + `date` TIMESTAMP NOT NULL, + `subject` VARCHAR(128) NULL DEFAULT NULL, + `note` INT UNSIGNED NULL DEFAULT NULL, + `review` TEXT NULL DEFAULT NULL, + `acceptedByPro` TIMESTAMP NULL DEFAULT NULL, + `validatedByClient` TIMESTAMP NULL DEFAULT NULL, + `validatedByPro` TIMESTAMP NULL DEFAULT NULL, + `clientId` INT UNSIGNED NOT NULL, + `proId` INT UNSIGNED NOT NULL, + `jobCategoryId` 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_jobCategoryId_idx` (`jobCategoryId` ASC) VISIBLE, + CONSTRAINT `fk_mission_clientId` + FOREIGN KEY (`clientId`) + REFERENCES `alphajob`.`userAccount` (`userId`) + ON DELETE NO ACTION + ON UPDATE CASCADE, + CONSTRAINT `fk_mission_proId` + FOREIGN KEY (`proId`) + REFERENCES `alphajob`.`userAccount` (`userId`) + ON DELETE NO ACTION + ON UPDATE CASCADE, + CONSTRAINT `fk_mission_jobCategoryId` + FOREIGN KEY (`jobCategoryId`) + REFERENCES `alphajob`.`jobCategory` (`jobCategoryId`) + ON DELETE NO ACTION + ON UPDATE CASCADE + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;"; + // Noms des tables protected $tableUserAccount = "userAccount"; protected $tableUserInfo = "userInfo"; + protected $tableJobCategory = "jobCategory"; + protected $tableUserJob = "userJob"; protected $tableMission = "mission"; // Création de la DB si elle n'existe pas @@ -85,13 +151,24 @@ class DbMain { // Création des tables si elles n'existent pas private function create_table() { - $reqInitUserAccount = "CREATE TABLE IF NOT EXISTS ".$this->createTableUserAccount; + // userAccount + $reqInitUserAccount = $this->createTableUserAccount; $res = $this->conn->prepare($reqInitUserAccount); $res->execute(); - $reqInitUserInfo = "CREATE TABLE IF NOT EXISTS ".$this->createTableUserInfo; + // userInfo + $reqInitUserInfo = $this->createTableUserInfo; $res = $this->conn->prepare($reqInitUserInfo); $res->execute(); - $reqInitMission = "CREATE TABLE IF NOT EXISTS ".$this->createTableMission; + // jobCategory + $reqInitJobCategory = $this->createTableJobCategory; + $res = $this->conn->prepare($reqInitJobCategory); + $res->execute(); + // userJob + $reqInitUserJob = $this->createTableUserJob; + $res = $this->conn->prepare($reqInitUserJob); + $res->execute(); + // mission + $reqInitMission = $this->createTableMission; $res = $this->conn->prepare($reqInitMission); $res->execute(); } @@ -127,8 +204,8 @@ class DbMain { } // Vérifie si l'id est un consultant dans la table userAccount de la BDD - final public function check_exist_consultant($id) { - $reqCheckExistConsultant = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE id = ? AND isConsultant = '1')"; + final public function check_exist_pro($id) { + $reqCheckExistConsultant = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE id = ? AND isPro = '1')"; $data = $this->exec_cmd($reqCheckExistConsultant, array($id))->fetchAll(PDO::FETCH_NUM); // Retourne 1 si existe, sinon 0 return $data[0][0]; @@ -137,7 +214,11 @@ class DbMain { // Fonction "auto", déclenchée à l'instanciation final public function __construct() { // Essaie de se connecter a la BDD - $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass); + if ($this->debug) { + $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); + } else { + $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT)); + } } } ?>
\ No newline at end of file |