diff options
Diffstat (limited to 'src/database/dbmain.py')
| -rw-r--r-- | src/database/dbmain.py | 217 |
1 files changed, 217 insertions, 0 deletions
diff --git a/src/database/dbmain.py b/src/database/dbmain.py new file mode 100644 index 0000000..602cec6 --- /dev/null +++ b/src/database/dbmain.py @@ -0,0 +1,217 @@ + +############################################################################ +# # +# Description: Partie commune des deux ensembles de méthodes # +# pour la gestion de la BDD # +# # +############################################################################ + +import mysql.connector +from passlib.hash import sha512_crypt + +# const si CAPS +# https://www.w3schools.com/python/python_mysql_getstarted.asp +# https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html +# https://stackoverflow.com/questions/11565487/python-equivalent-of-php-mysql-fetch-array + + +class DbMain(): + # **************************************************************************** + # INFOS GLOBAL ET DE CONNEXION + # **************************************************************************** + # Pour gérer si "en production" + DEBUG = False + # Configuration du serveur MYSQL / MARIADB + HOST = "127.0.0.1" + USER = "root" + PASSWORD = "" + DATABASE = "alphajob" + + # **************************************************************************** + # DECLARATION DES TABLES + # **************************************************************************** + # UserAccount, infos du compte nécessaire à la connection + 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, + `userStatus` TINYINT(1) UNSIGNED NOT NULL, + PRIMARY KEY (`userId`) + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;""" + + # Infos complémentaires des utilisateur, liée à userAccount par userId + 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, + `degree` VARCHAR(128) NULL DEFAULT NULL, + `capability` VARCHAR(256) NULL DEFAULT NULL, + `description` VARCHAR(256) 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 + CREATETABLEJOBCATEGORY = """CREATE TABLE IF NOT EXISTS `alphajob`.`jobCategory` ( + `jobCategoryId` INT UNSIGNED NOT NULL AUTO_INCREMENT, + `jobCategoryNameEn` VARCHAR(128) NOT NULL, + `jobCategoryNameFr` VARCHAR(128) NOT NULL, + PRIMARY KEY (`jobCategoryId`) + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;""" + + # Assosications des emploies à un utilisateur + 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 CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_userJob_jobCategoryId` + FOREIGN KEY (`jobCategoryId`) + REFERENCES `alphajob`.`jobCategory` (`jobCategoryId`) + ON DELETE CASCADE + ON UPDATE CASCADE + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8;""" + + # Infos sur les missions effectuées, liée à userAccount par userId et consultantId + 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` TINYINT(1) UNSIGNED NULL DEFAULT NULL, + `review` VARCHAR(256) 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 CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_mission_proId` + FOREIGN KEY (`proId`) + REFERENCES `alphajob`.`userAccount` (`userId`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_mission_jobCategoryId` + FOREIGN KEY (`jobCategoryId`) + REFERENCES `alphajob`.`jobCategory` (`jobCategoryId`) + ON DELETE CASCADE + ON UPDATE CASCADE + ) + ENGINE = InnoDB + DEFAULT CHARACTER SET = utf8""" + + # Noms des tables + TABLEUSERACCOUNT = "userAccount" + TABLEUSERINFO = "userInfo" + TABLEJOBCATEGORY = "jobCategory" + TABLEUSERJOB = "userJob" + TABLEMISSION = "mission" + + # **************************************************************************** + # FONCTIONS PRINCIPALES + # **************************************************************************** + # Constructeur, fonction "auto" déclenchée à l'instanciation + def __init__(self): + if self.DEBUG: + self.conn = mysql.connector.connect( + host=self.HOST, + user=self.USER, + password=self.PASSWORD, + database=self.DATABASE, + raise_on_warnings=self.DEBUG + ) + else: + self.conn = mysql.connector.connect( + host=self.HOST, + user=self.USER, + password=self.PASSWORD, + database=self.DATABASE + ) + self.cur = self.conn.cursor() + + # Création de la DB si elle n'existe pas + def create_db(self): + reqInitDb = "CREATE DATABASE IF NOT EXISTS " + self.DATABASE + self.cur.execute(reqInitDb) + + # Sélection de la DB + def select_db(self): + reqSelectDb = "USE " + self.DATABASE + self.cur.execute(reqSelectDb) + + # Création des tables si elles n'existent pas + def create_table(self): + # userAccount + reqInitUserAccount = self.CREATETABLEUSERACCOUNT + self.cur.execute(reqInitUserAccount) + # userInfo + reqInitUserInfo = self.CREATETABLEUSERINFO + self.cur.execute(reqInitUserAccount) + # jobCategory + reqInitJobCategory = self.CREATETABLEJOBCATEGORY + self.cur.execute(reqInitJobCategory) + # userJob + reqInitUserJob = self.CREATETABLEUSERJOB + self.cur.execute(reqInitUserJob) + # mission + reqInitMission = self.CREATETABLEMISSION + self.cur.execute(reqInitMission) + + # Déclenche les méthodes pour créer la BDD + def init(self): + self.create_db() + self.select_db() + self.create_table() + + # Chiffre la data reçue + def crypt_pass(self, password): + return sha512_crypt.hash(password, rounds=10000) + + # Prépare la requète pour mitiger (se protéger) les injections SQL et + # initialise si nécessaire la BDD à chaque requète + def exec_cmd(self, req, arg=None): + self.init() + if arg: + self.cur.execute(req, arg) + else: + self.cur.execute(req) + + # Vérifie si l'email existe dans la table userAccount de la BDD + def check_exist_email(self, email): + reqCheckEmailExist = "SELECT EXISTS(SELECT * FROM " + \ + self.TABLEUSERACCOUNT + " WHERE email = %s)" + self.exec_cmd(reqCheckEmailExist, [email]) + # Retourne 1 si existe, sinon 0 + return self.cur.fetchall()[0][0] |