ptut-vhost/build/database/repo/meta.php

351 lines
9.3 KiB
PHP

<?php
namespace database\repo;
use database\core\Repo_i;
class meta extends Repo_i {
/* (1) Creates a new professor
*
* @casLogin<String> The professor's cas login
* @firstName<String> [OPT] The professor's firstName
* @lastName<String> [OPT] The professor's lastName
*
* @return created<bool> Whether the professor has been created
*
---------------------------------------------------------*/
public function create_prof(String $casLogin, ?String $firstName, ?String $lastName) : bool{
/* (1) Create user in meta database
---------------------------------------------------------*/
/* (1) Try to insert user */
$st = $this->pdo->prepare("INSERT INTO
meta_vhost.casUser(casLogin, firstName, lastName)
VALUES(:cas_login, :first_name, :last_name)");
/* (2) Manage statement error */
if( is_bool($st) )
return FALSE;
/* (3) Try to execute */
$success = $st->execute([
':cas_login' => $casLogin,
':first_name' => $firstName,
':last_name' => $lastName
]);
/* (4) Dispatch execution error */
return $success;
}
/* (2) Check if a professor exists (by its casLogin)
*
* @casLogin<String> The professor's cas login
*
* @return exists<bool> Whether the professor exists
*
---------------------------------------------------------*/
public function prof_exists(String $casLogin) : bool{
/* (1) Prepare Statement */
$st = $this->pdo->prepare("SELECT casLogin
FROM meta_vhost.casUser
WHERE casLogin = :cas_login;");
/* (2) Statement eror */
if( is_bool($st) )
return FALSE;
/* (3) Bind params and execute */
$success = $st->execute([ ':cas_login' => $casLogin ]);
/* (4) Manage execution error */
if( !$success )
return FALSE;
/* (7) Return That it exists */
return is_array( $st->fetch() );
}
/* (3) Get available departments for a CAS login
*
* @casLogin<String> The professor's CAS username
*
* @return departments<array> The list of available departments (empty on error)
*
---------------------------------------------------------*/
public function get_prof_departments(String $casLogin) : array{
/* (1) Prepare Statement */
$st = $this->pdo->prepare("SELECT d2.iddepartement idDep, d2.label labelDep
FROM meta_vhost.casUser
JOIN meta_vhost.linkedDep D ON casUser.casLogin = D.casUser_casLogin
JOIN meta_vhost.departement d2 ON D.departement_iddepartement = d2.iddepartement
WHERE casLogin = :cas_login");
/* (2) Check if statement error */
if( is_bool($st) )
return [];
/* (3) Bind params and execute sstatement */
$success = $st->execute([ ':cas_login' => $casLogin ]);
/* (4) Manage error */
if( !$success )
return [];
/* (5) Get data */
$fetched = $st->fetchAll();
/* (6) Return [] on no result */
if( $fetched === false )
return [];
/* (7) Add all possible databases to the department */
foreach ($fetched as &$dep){
$st = $this->pdo->prepare("SELECT *
FROM meta_vhost.`databases`
WHERE departement_iddepartement = :idDep
ORDER BY meta_vhost.`databases`.`default` DESC ");
$st->execute(["idDep" => $dep["idDep"]]);
$dep["versions"] = $st->fetchAll();
}
/* (8) Return data */
return $fetched;
}
/* (4) Deletes a professor
*
* @casLogin<String> The professor's CAS username
*
* @return deleted<bool> Whether the professor have been deleeted successfully
*
---------------------------------------------------------*/
public function delete_prof(String $casLogin) : bool{
/* (1) Prepare statement */
$st = $this->pdo->prepare('DELETE FROM meta_vhost.casUser
WHERE casLogin = :cas_login');
/* (2) Manage statement error */
if( is_bool($st) )
return FALSE;
/* (3) Try to execute */
$success = $st->execute([ ':cas_login' => $casLogin ]);
/* (4) Dispatch execution error */
return $success;
}
/* (5) Check if a link exists
*
* @casLogin<String> The professor's cas login
* @idDep<int> The department id
*
* @return exists<bool> Whether the professor exists
*
---------------------------------------------------------*/
public function link_exists(String $casLogin, int $idDep) : bool{
/* (1) Prepare Statement */
$st = $this->pdo->prepare("SELECT *
FROM meta_vhost.linkedDep
WHERE departement_iddepartement = :id_dep
AND casUser_casLogin = :cas_login");
/* (2) Statement eror */
if( is_bool($st) )
return FALSE;
/* (3) Bind params and execute */
$success = $st->execute([
':id_dep' => $idDep,
':cas_login' => $casLogin
]);
/* (4) Manage execution error */
if( !$success )
return FALSE;
/* (7) Return That it exists */
return is_array( $st->fetch() );
}
/* (6) Associates a professor to a department
*
* @casLogin<String> The professor's CAS username
* @idDep<int> The department id
*
* @return linked<bool> Whether the link have been created
*
---------------------------------------------------------*/
public function link(String $casLogin, int $idDep) : bool{
/* (1) Prepare statement */
$st = $this->pdo->prepare("INSERT INTO meta_vhost.linkedDep(departement_iddepartement, casUser_casLogin)
VALUES(:id_dep, :cas_login);");
/* (2) Manage statement error */
if( is_bool($st) )
return FALSE;
/* (3) Try to execute request */
$success = $st->execute([
':id_dep' => $idDep,
':cas_login' => $casLogin
]);
/* (4) Dispatch execution error */
return $success;
}
/* (7) Remove a department from a professor
*
* @casLogin<String> The professor's CAS username
* @idDep<int> The department id
*
* @return unlinked<bool> Whether the link have been removed
*
---------------------------------------------------------*/
public function unlink(String $casLogin, int $idDep) : bool{
/* (1) Prepare statement */
$st = $this->pdo->prepare("DELETE FROM meta_vhost.linkedDep
WHERE departement_iddepartement = :id_dep
AND casUser_casLogin = :cas_login;");
/* (2) Manage statement error */
if( is_bool($st) )
return FALSE;
/* (3) Try to execute request */
$success = $st->execute([
':id_dep' => $idDep,
':cas_login' => $casLogin
]);
/* (4) Dispatch execution error */
return $success;
}
public function deleteVersion(int $version) : bool{
//get version data
$st = $this->pdo->prepare("SELECT * FROM meta_vhost.`databases` WHERE iddatabase = :id");
$st->execute(["id" => $version]);
$versionData = $st->fetch();
if( !is_array($versionData) || $versionData["default"] == 1){
return false;
}
//delete database
$versionDatabase = $versionData['dbName'];
$this->pdo->exec("DROP DATABASE $versionDatabase;");
//remove from meta
$st = $this->pdo->prepare("DELETE FROM meta_vhost.`databases` WHERE iddatabase = :id");
return $st->execute(["id" => $version]);
}
public function createVersion(String $label, String $dbName, int $depId, bool $isDefault = false) : int{
$st = $this->pdo->prepare("INSERT INTO meta_vhost.`databases`(label, dbName, `default`, departement_iddepartement)
VALUE (:label,:dbName,:default,:depId)");
$st->execute([
"label" => $label,
"dbName" => $dbName,
"default" => $isDefault? 1 : 0,
"depId" => $depId
]);
return $this->pdo->lastInsertId();
}
public function getVersionById(int $id) : array{
$st = $this->pdo->prepare("SELECT * FROM meta_vhost.`databases` WHERE iddatabase = :idVersion");
$st->execute(["idVersion" => $id]);
$fetched = $st->fetch();
return $fetched == false ? [] : $fetched;
}
public function getAllVersions(int $idDep) : array {
$st = $this->pdo->prepare("SELECT * FROM meta_vhost.`databases` WHERE departement_iddepartement = :idDep");
$st->execute(["idDep" => $idDep]);
return $st->fetchAll();
}
public function updateVersion(int $version, ?String $label, ?bool $default) : bool{
//you can't un-default a version, you have to choose another version to be the default one
if($default == false){
return false;
}
$set = "";
$execute = [];
if($label != null){
$set .= "label=:label,";
$execute["label"] = $label;
}
if($default != null){
$set .= "`default`=:default,";
$execute["default"] = $default?1:0;
//if default = true, set previous default version to non-default
if($default == true){
$req = $this->pdo->prepare("UPDATE meta_vhost.`databases` SET `default`=0 WHERE departement_iddepartement=:idDep");
$req->execute(["idDep" => $_SESSION['CurrentDepartmentId']]);
}
}
$set = rtrim($set,",");
$execute["idVersion"] = $version;
$st = $this->pdo->prepare("UPDATE meta_vhost.`databases` SET $set WHERE iddatabase=:idVersion");
return $st->execute($execute);
}
public function createDepartment(String $name) : int{
$st = $this->pdo->prepare("INSERT INTO meta_vhost.departement(label) VALUE (:label)");
$st->execute(["label" => $name]);
return $this->pdo->lastInsertId();
}
}