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;

class MySQLDAO extends DAO {

    public function getDBHandler() {
        $connectionString = "mysql:host=" . $this->config['hostname'] . ";dbname=" . $this->config['dbname'];
        return new PDO($connectionString, $this->config['username'], $this->config['password']);
    }

    public function createLoginToken($token, $userId) {

        global $log;

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

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

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

    public function findLoginToken($token) {

        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("SELECT data 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['data'];
        }

        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`, `username`, `local_db_id`, `typed_id`, `eppn`)"
                . " VALUES(:user_id, :type, :email, :name, :surname, :institution, :username, :local_db_id, :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(':username', $identity->username);
        $stmt->bindParam(':local_db_id', $identity->localDBId);
        $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->typedId = $row['typed_id'];
        $identity->email = $row['email'];
        $identity->localDBId = $row['local_db_id'];
        $identity->name = $row['name'];
        $identity->surname = $row['surname'];
        $identity->institution = $row['institution'];
        $identity->username = $row['username'];
        $identity->eppn = $row['eppn'];

        return $identity;
    }

    public function findUserById($userId) {

        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("SELECT `id`, `type`, `typed_id`, `email`, `local_db_id`, `name`, `surname`, `institution`, `username`, `eppn`"
                . " FROM identity WHERE 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);
        }

        $stmtMail = $dbh->prepare("SELECT `email` FROM `additional_email` WHERE `user_id` = :user_id");
        $stmtMail->bindParam(':user_id', $userId);
        $stmtMail->execute();
        foreach ($stmtMail->fetchAll() as $row) {
            $user->addAdditionalEmail($row['email']);
        }

        return $user;
    }

    public function findUserByIdentity($type, $identifier, $dbIdentifier) {

        $dbh = $this->getDBHandler();

        $query = "SELECT user_id FROM identity WHERE type = :type AND typed_id = :typed_id";
        if (isset($dbIdentifier) && $dbIdentifier !== null) {
            $query .= " AND local_db_id = :local_db_id";
        }

        $stmt = $dbh->prepare($query);
        $stmt->bindParam(':type', $type);
        $stmt->bindParam(':typed_id', $identifier);
        if (isset($dbIdentifier) && $dbIdentifier !== null) {
            $stmt->bindParam(':local_db_id', $dbIdentifier);
        }

        $stmt->execute();

        $result = $stmt->fetchAll();

        global $log;
        $log->debug("count = " . count($result));

        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();

        // TODO: Add additional email search...

        $query = "SELECT `user_id`, `id`, `type`, `typed_id`, `email`, `local_db_id`, `name`, `surname`, `institution`, `username`, `eppn`"
                . " FROM identity WHERE `email` LIKE :email OR `name` LIKE :name OR `surname` LIKE :surname";

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

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

        $stmt->execute();

        $userMap = array();

        //global $log;
        //$log->debug('In searchUser');

        foreach ($stmt->fetchAll() as $row) {

            //$log->debug($row['user_id']);

            $identity = $this->getIdentityByRow($row);
            //$log->debug(json_encode($identity));

            $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 addEmailToUser($email, $userId) {

        $dbh = $this->getDBHandler();

        $stmt = $dbh->prepare("INSERT INTO `additional_email`(`user_id`, `email`) VALUES(:user_id, :email)");
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':email', $email);

        $stmt->execute();
    }

    public function createJoinRequest($token, $applicantUserId, $targetUserId) {

        $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();
    }

}