############################################################################ # # # Description: Partie commune des deux ensembles de méthodes # # pour la gestion de la BDD # # # ############################################################################ from types import NoneType import mysql.connector from passlib.hash import sha512_crypt from PyQt5.QtWidgets import QMessageBox # 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(): """ Classe principale pour gérer la BDD avec les méthode pour se connecter, créer les tables, exéctuer une requête SQL, etc ... """ # 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 # Création de la DB si elle n'existe pas def create_db(self) -> None: reqInitDb = "CREATE DATABASE IF NOT EXISTS " + self.DATABASE self.cur.execute(reqInitDb) # Sélection de la DB def select_db(self) -> None: reqSelectDb = "USE " + self.DATABASE self.cur.execute(reqSelectDb) # Création des tables si elles n'existent pas def create_table(self) -> None: # 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) -> None: self.create_db() self.select_db() self.create_table() # Chiffre la data reçue def crypt_pass(self, password: str) -> str: 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: str, arg: list = None, commit: bool = False) -> None: """Initie la BDD et execute la commande avec une list, ou pas, et commit (apporter des modification a la BDD), ou pas.""" self.init() if arg: self.cur.execute(req, arg) if commit: self.conn.commit() else: self.cur.execute(req) # Vérifie si l'email existe dans la table userAccount de la BDD def check_exist_email(self, email: str) -> int: 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] # Constructeur, fonction "auto" déclenchée à l'instanciation def __init__(self, mainWindow) -> None: try: 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() except mysql.connector.errors.DatabaseError as e: if self.DEBUG: QMessageBox.warning( mainWindow, "Erreur avec la BDD", str(e) ) else: QMessageBox.warning( mainWindow, "Erreur avec la BDD", "Veuillez vérifier si la BDD est accessible." ) exit(1)