MySQLDAO.php

<?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;

use PDO;

/**
 * MySQL implementation of the DAO interface. See comments on the DAO interface.
 */
class MySQLDAO implements DAO {

    private $config;

    public function __construct($config) {
        $this->config = $config;
    }

    public function getDBHandler() {
        $connectionString = "mysql:host=" . $this->config['hostname'] . ";dbname=" . $this->config['dbname'];
        $dbh = new PDO($connectionString, $this->config['username'], $this->config['password']);
        // For transaction errors (see https://stackoverflow.com/a/9659366/771431)
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $dbh;
    }

    public function createLoginToken($token, $userId) {

        $dbh = $this->getDBHandler();
        $stmt = $dbh->prepare("INSERT INTO login_token (token, user_id) VALUES(:token, :user_id)");

        $params = array(
            ':token' => $token,
            ':user_id' => $userId
        );

        if ($stmt->execute($params)) {
            return $token;
        } else {
            error_log($stmt->errorInfo()[2]);
            throw new \Exception("SQL error while storing user token");
        }
    }

    public function findLoginToken($token) {

        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("SELECT user_id FROM login_token WHERE token = :token AND CURRENT_TIMESTAMP < TIMESTAMPADD(MINUTE,1,creation_time)");
        $stmt->bindParam(':token', $token);

        $stmt->execute();

        foreach ($stmt->fetchAll() as $row) {
            return $row['user_id'];
        }

        return null;
    }

    public function deleteLoginToken($token) {

        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("DELETE FROM login_token WHERE token = :token");
        $stmt->bindParam(':token', $token);
        $stmt->execute();
    }

    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 = $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($userId) {

        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 `name` LIKE :name OR `surname` LIKE :surname"
                . " OR CONCAT(`name`,' ',`surname`) LIKE :namesurname)";

        $stmt = $dbh->prepare($query);

        $searchParam = $searchText . '%';
        $emailPartSearchParam = '%.' . $searchText . '%';
        $stmt->bindParam(':email', $searchParam);
        $stmt->bindParam(':emailPart', $emailPartSearchParam);
        $stmt->bindParam(':name', $searchParam);
        $stmt->bindParam(':surname', $searchParam);
        $stmt->bindParam(':namesurname', $searchParam);

        $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 createJoinRequest($token, $applicantUserId, $targetUserId) {

        if ($applicantUserId === $targetUserId) {
            throw new \Exception("Invalid target user id");
        }

        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("INSERT INTO `join_request`(`token`, `applicant_user_id`, `target_user_id`)"
                . " VALUES(:token, :applicant_user_id, :target_user_id)");

        $stmt->bindParam(':token', $token);
        $stmt->bindParam(':applicant_user_id', $applicantUserId);
        $stmt->bindParam(':target_user_id', $targetUserId);

        $stmt->execute();
    }

    public function findJoinRequest($token) {
        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("SELECT `applicant_user_id`, `target_user_id` FROM `join_request` WHERE `token` = :token");
        $stmt->bindParam(':token', $token);
        $stmt->execute();

        $result = $stmt->fetchAll();

        switch (count($result)) {
            case 0:
                return null;
            case 1:
                $row = $result[0];
                return [$row['applicant_user_id'], $row['target_user_id']];
            default:
                throw new Exception("Found multiple join request with the same token");
        }
    }

    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 join requests
            $stmt3 = $dbh->prepare("DELETE FROM `join_request` WHERE `target_user_id` = :tid2 OR `applicant_user_id` = :aid2");
            $stmt3->bindParam(':tid2', $userId2);
            $stmt3->bindParam(':aid2', $userId2);
            $stmt3->execute();

            // Deleting user2
            $stmt4 = $dbh->prepare("DELETE FROM `user` WHERE `id` = :id2");
            $stmt4->bindParam(':id2', $userId2);
            $stmt4->execute();

            $dbh->commit();
        } catch (Exception $ex) {
            $dbh->rollBack();
            throw $ex;
        }
    }

    public function deleteJoinRequest($token) {
        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("DELETE FROM `join_request` WHERE `token` = :token");
        $stmt->bindParam(':token', $token);
        $stmt->execute();
    }

}