Select Git revision
MySQLUserDAO.php
-
Sonia Zorba authoredSonia Zorba authored
MySQLUserDAO.php 9.41 KiB
<?php
/* ----------------------------------------------------------------------------
* INAF - National Institute for Astrophysics
* IRA - Radioastronomical Institute - Bologna
* OATS - Astronomical Observatory - Trieste
* ----------------------------------------------------------------------------
*
* Copyright (C) 2016 Istituto Nazionale di Astrofisica
*
* This program is free software; you can redistribute it and/or modify it under
* the terms of the GNU General Public License Version 3 as published by the
* Free Software Foundation.
*
* This program is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
* FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
* details.
*
* You should have received a copy of the GNU General Public License along with
* this program; if not, write to the Free Software Foundation, Inc., 51
* Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
namespace RAP;
/**
* MySQL implementation of the DAO interface. See comments on the DAO interface.
*/
class MySQLUserDAO extends BaseMySQLDAO implements UserDAO {
public function __construct(Locator $locator) {
parent::__construct($locator);
}
public function insertIdentity(Identity $identity, $userId) {
$dbh = $this->getDBHandler();
$stmt = $dbh->prepare("INSERT INTO identity(`user_id`, `type`, `email`, `name`, `surname`, `institution`, `typed_id`, `eppn`)"
. " VALUES(:user_id, :type, :email, :name, :surname, :institution, :typed_id, :eppn)");
$stmt->bindParam(':user_id', $userId);
$stmt->bindParam(':type', $identity->type);
$stmt->bindParam(':email', $identity->email);
$stmt->bindParam(':name', $identity->name);
$stmt->bindParam(':surname', $identity->surname);
$stmt->bindParam(':institution', $identity->institution);
$stmt->bindParam(':typed_id', $identity->typedId);
$stmt->bindParam(':eppn', $identity->eppn);
$stmt->execute();
return $dbh->lastInsertId();
}
public function createUser() {
$dbh = $this->getDBHandler();
$stmt = $dbh->prepare("INSERT INTO user() VALUES()");
$stmt->execute();
return $dbh->lastInsertId();
}
private function getIdentityByRow($row) {
$identity = new Identity($row['type']);
$identity->id = $row['id'];
$identity->primary = boolval($row['primary']);
$identity->typedId = $row['typed_id'];
$identity->email = $row['email'];
$identity->name = $row['name'];
$identity->surname = $row['surname'];
$identity->institution = $row['institution'];
$identity->eppn = $row['eppn'];
return $identity;
}
public function findUserById(string $userId): ?User {
if (!filter_var($userId, FILTER_VALIDATE_INT)) {
return null;
}
$dbh = $this->getDBHandler();
$stmt = $dbh->prepare("SELECT (u.`primary_identity` = i.`id`) AS `primary`,"
. " i.`id`, `type`, `typed_id`, `email`, `name`, `surname`, `institution`, `eppn`"
. " FROM identity i"
. " JOIN `user` u on u.id = i.user_id"
. " WHERE i.user_id = :user_id");
$stmt->bindParam(':user_id', $userId);
$stmt->execute();
$result = $stmt->fetchAll();
if (count($result) === 0) {
return null;
}
$user = new User();
$user->id = $userId;
foreach ($result as $row) {
$identity = $this->getIdentityByRow($row);
$user->addIdentity($identity);
}
return $user;
}
public function setPrimaryIdentity($userId, $identityId) {
$dbh = $this->getDBHandler();
$stmt = $dbh->prepare("UPDATE `user` SET `primary_identity` = :identity_id WHERE `id` = :user_id");
$stmt->bindParam(':identity_id', $identityId);
$stmt->bindParam(':user_id', $userId);
$stmt->execute();
}
public function findUserByIdentity($type, $identifier) {
$dbh = $this->getDBHandler();
$query = "SELECT user_id FROM identity WHERE type = :type AND typed_id = :typed_id";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':type', $type);
$stmt->bindParam(':typed_id', $identifier);
$stmt->execute();
$result = $stmt->fetchAll();
if (count($result) === 0) { return null;
}
if (count($result) > 1) {
throw new Exception("Found multiple users associated to the same identity!");
}
$userId = $result[0]['user_id'];
return $this->findUserById($userId);
}
public function searchUser($searchText) {
$dbh = $this->getDBHandler();
$query = "SELECT `user_id`, (u.`primary_identity` = i.`id`) AS `primary`,"
. " i.`id`, `type`, `typed_id`, `email`, `name`, `surname`, `institution`, `eppn`"
. " FROM identity i"
. " JOIN `user` u on u.id = i.user_id"
. " WHERE i.user_id IN"
. " (SELECT user_id FROM identity"
. " WHERE `email` LIKE :email OR `email` LIKE :emailPart"
. " OR `eppn` LIKE :eppn"
. " OR `name` LIKE :name OR `surname` LIKE :surname"
. " OR CONCAT(`name`,' ',`surname`) LIKE :namesurname)";
$stmt = $dbh->prepare($query);
$searchParam = $searchText . '%';
if (count_chars($searchText) > 4) {
$searchParam = '%' . $searchParam;
}
$emailPartSearchParam = '%.' . $searchText . '%';
$stmt->bindParam(':email', $searchParam);
$stmt->bindParam(':emailPart', $emailPartSearchParam);
$stmt->bindParam(':eppn', $searchParam);
$stmt->bindParam(':name', $searchParam);
$stmt->bindParam(':surname', $searchParam);
$stmt->bindParam(':namesurname', $searchParam);
return $this->getUsersListFromStatement($stmt);
}
public function getUsers(array $identifiers): array {
if (count($identifiers) === 0) {
return [];
}
$dbh = $this->getDBHandler();
$query = "SELECT `user_id`, (u.`primary_identity` = i.`id`) AS `primary`,"
. " i.`id`, `type`, `typed_id`, `email`, `name`, `surname`, `institution`, `eppn`"
. " FROM identity i"
. " JOIN `user` u on u.id = i.user_id"
. " WHERE i.user_id IN (";
$first = true;
foreach ($identifiers as $id) {
if (!$first) {
$query .= ',';
}
$query .= ':id_' . $id;
if ($first) {
$first = !$first;
}
}
$query .= ')';
$stmt = $dbh->prepare($query);
foreach ($identifiers as &$id) {
$stmt->bindParam(':id_' . $id, $id);
}
return $this->getUsersListFromStatement($stmt);
}
private function getUsersListFromStatement(\PDOStatement $stmt): array {
$stmt->execute();
$userMap = array();
foreach ($stmt->fetchAll() as $row) {
$identity = $this->getIdentityByRow($row);
$userId = $row['user_id'];
if (array_key_exists($userId, $userMap)) {
$user = $userMap[$userId];
} else {
$user = new User();
$user->id = $userId;
$userMap[$userId] = $user;
}
array_push($user->identities, $identity);
}
$users = [];
foreach ($userMap as $userId => $user) {
array_push($users, $user);
}
return $users;
}
public function joinUsers($userId1, $userId2) {
$dbh = $this->getDBHandler();
try {
$dbh->beginTransaction();
// Moving identities from user2 to user1
$stmt1 = $dbh->prepare("UPDATE `identity` SET `user_id` = :id1 WHERE `user_id` = :id2");
$stmt1->bindParam(':id1', $userId1);
$stmt1->bindParam(':id2', $userId2);
$stmt1->execute();
// Deleting user2
$stmt2 = $dbh->prepare("DELETE FROM `user` WHERE `id` = :id2");
$stmt2->bindParam(':id2', $userId2);
$stmt2->execute();
$dbh->commit();
} catch (Exception $ex) {
$dbh->rollBack();
throw $ex;
}
}
function isAdmin($userId): bool {
$dbh = $this->getDBHandler();
$query = "SELECT user_id FROM rap_permissions WHERE permission = 'ADMIN' AND user_id = :userId";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':userId', $userId);
$stmt->execute();
$result = $stmt->fetchAll();
return count($result) === 1;
}
function updateIdentity(Identity $identity): void {
$dbh = $this->getDBHandler();
$query = "UPDATE identity SET email = :email, name = :name, surname = :surname, institution = :institution"
. " WHERE id = :id";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':email', $identity->email);
$stmt->bindParam(':name', $identity->name);
$stmt->bindParam(':surname', $identity->surname);
$stmt->bindParam(':institution', $identity->institution);
$stmt->bindParam(':id', $identity->id);
$stmt->execute();
}
}