Skip to content
Snippets Groups Projects
Select Git revision
  • 3.0 default protected
  • 2.0
  • mamarley-autocomplete
  • master protected
4 results

Database.php

Blame
  • Database.php 9.89 KiB
    <?php
    
    namespace QuasselRestSearch;
    
    require_once 'User.php';
    require_once 'Config.php';
    require_once 'helper/AuthHelper.php';
    
    class Backend {
        private $storedFindBuffers;
        private $storedFindInBuffer;
        private $loadBefore;
        private $loadAfter;
    
        private $findUser;
    
        private $user;
    
        private function __construct(string $database_connector, string $username, string $password) {
            $db = new \PDO($database_connector, $username, $password);
    
            $this->storedFindBuffers = $db->prepare("
                SELECT backlog.bufferid,
                       buffer.buffername,
                       network.networkname
                FROM backlog
                JOIN buffer ON backlog.bufferid = buffer.bufferid
                JOIN network ON buffer.networkid = network.networkid,
                                plainto_tsquery('english'::REGCONFIG, :query) query
                WHERE (backlog.type & 23559) > 0
                  AND buffer.userid = :userid
                  AND backlog.tsv @@ query
                GROUP BY backlog.bufferid,
                         buffer.buffername,
                         network.networkname
                ORDER BY MIN((1 + log(GREATEST(1::DOUBLE PRECISION, EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - TIME))))) * (1 - ts_rank(tsv, query, 32)) * (1 + ln(backlog.type))) ASC;
            ");
            $this->storedFindInBufferMultiple = $db->prepare("
                SELECT tmp.bufferid,
                       tmp.messageid,
                       sender.sender,
                       tmp.time,
                       tmp.message,
                       ts_headline(replace(replace(tmp.message, '<', '&lt;'), '>', '&gt;'), query) AS preview
                FROM
                  (SELECT backlog.messageid,
                          backlog.bufferid,
                          backlog.senderid,
                          backlog.time,
                          backlog.message,
                          query,
                          rank() OVER(PARTITION BY backlog.bufferid
                                      ORDER BY (1 + log(GREATEST(1, EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - TIME))))) * (1 - ts_rank(tsv, query, 32)) * (1 + ln(backlog.type)) ASC
                                      ) AS rank
                   FROM backlog
                   JOIN buffer ON backlog.bufferid = buffer.bufferid,
                                  plainto_tsquery('english'::REGCONFIG, :query) query
                   WHERE (backlog.type & 23559) > 0
                     AND buffer.userid = :userid
                     AND backlog.tsv @@ query
                   ORDER BY (1 + log(GREATEST(1, EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - TIME))))) * (1 - ts_rank(tsv, query, 32)) * (1 + ln(backlog.type)) ASC
                  ) tmp
                JOIN sender ON tmp.senderid = sender.senderid
                WHERE tmp.rank <= :limit;
            ");
            $this->storedFindInBuffer = $db->prepare("
                SELECT backlog.messageid,
                       sender.sender,
                       backlog.time,
                       backlog.message,
                       ts_headline(replace(replace(backlog.message, '<', '&lt;'), '>', '&gt;'), query) AS preview
                FROM backlog
                JOIN sender ON backlog.senderid = sender.senderid
                JOIN buffer ON backlog.bufferid = buffer.bufferid,
                                plainto_tsquery('english'::REGCONFIG, :query) query
                WHERE (backlog.type & 23559) > 0
                  AND buffer.userid = :userid
                  AND backlog.bufferid = :bufferid
                  AND backlog.tsv @@ query
                ORDER BY (1 + log(GREATEST(1, EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - TIME))))) * (1 - ts_rank(tsv, query, 32)) * ( 1 + ln(backlog.type)) ASC
                LIMIT :limit OFFSET :offset;
            ");
            $this->loadAfter = $db->prepare("
                SELECT backlog.messageid,
                       backlog.bufferid,
                       buffer.buffername,
                       sender.sender,
                       backlog.time,
                       network.networkname,
                       backlog.message
                FROM backlog
                JOIN sender ON backlog.senderid = sender.senderid
                JOIN buffer ON backlog.bufferid = buffer.bufferid
                JOIN network ON buffer.networkid = network.networkid
                WHERE buffer.userid = :userid
                  AND backlog.bufferid = :bufferid
                  AND backlog.messageid >= :anchor
                ORDER BY backlog.messageid ASC
                LIMIT :limit;
            ");
            $this->loadBefore = $db->prepare("
                SELECT backlog.messageid,
                       backlog.bufferid,
                       buffer.buffername,
                       sender.sender,
                       backlog.time,
                       network.networkname,
                       backlog.message
                FROM backlog
                JOIN sender ON backlog.senderid = sender.senderid
                JOIN buffer ON backlog.bufferid = buffer.bufferid
                JOIN network ON buffer.networkid = network.networkid
                WHERE buffer.userid = :userid
                  AND backlog.bufferid = :bufferid
                  AND backlog.messageid < :anchor
                ORDER BY backlog.messageid DESC
                LIMIT :limit;
            ");
            $this->findUser = $db->prepare("
                SELECT *
                FROM quasseluser
                WHERE quasseluser.username = :username
            ");
        }
    
        public static function createFromOptions(string $database_connector, string $username, string $password) : Backend {
            return new Backend($database_connector, $username, $password);
        }
    
        public static function createFromConfig(Config $config) : Backend {
            return new Backend($config->database_connector, $config->username, $config->password);
        }
    
        public function authenticateFromHeader(string $header) : bool {
            $parsedHeader = AuthHelper::parseAuthHeader($header);
            return $this->authenticate($parsedHeader['username'], $parsedHeader['password']);
        }
    
        public function authenticate(string $username, string $password) : bool {
            if (!isset($username) || !isset($password)) {
                syslog(LOG_ERR, "Username or password not set");
                return false;
            }
    
            $this->findUser->bindParam(":username", $username);
            $this->findUser->execute();
    
            $result = $this->findUser->fetch(\PDO::FETCH_ASSOC);
            if ($result === FALSE) {
                syslog(LOG_ERR, "Couldn’t find user " . $username);
                return false;
            }
    
            $user = new User($result);
    
            if (!AuthHelper::initialAuthenticateUser($password, $user->password, $user->hashversion)) {
                syslog(LOG_ERR, "Password does not match for user ".$username);
                return false;
            }
    
            $this->user = $user;
            return true;
        }
    
        public function find(string $query, int $limitPerBuffer = 4) : array {
            $truncatedLimit = max(min($limitPerBuffer, 10), 0);
    
            $buffers = $this->findBuffers($query);
            $messages = $this->findInBufferMultiple($query, $truncatedLimit);
    
            $buffermap = [];
            foreach ($buffers as &$buffer) {
                $buffermap[$buffer['bufferid']] = &$buffer;
                $buffermap[$buffer['bufferid']]['messages'] = [];
            }
    
            foreach ($messages as $message) {
                $buffer = $buffermap[$message['bufferid']];
                $messages1 = $buffer['messages'];
                array_push($messages1, $message);
                $buffer['messages'] = $messages1;
                $buffermap[$buffer['bufferid']] = $buffer;
            }
    
            return array_values($buffermap);
        }
    
        public function findBuffers(string $query) : array {
            $this->storedFindBuffers->bindParam(':userid', $this->user->userid);
            $this->storedFindBuffers->bindParam(':query', $query);
            $this->storedFindBuffers->execute();
            return $this->storedFindBuffers->fetchAll(\PDO::FETCH_ASSOC);
        }
    
        public function findInBufferMultiple(string $query, int $limit = 4) : array {
            $this->storedFindInBufferMultiple->bindParam(':userid', $this->user->userid);
            $this->storedFindInBufferMultiple->bindParam(':query', $query);
            $this->storedFindInBufferMultiple->bindParam(':limit', $limit);
            $this->storedFindInBufferMultiple->execute();
            return $this->storedFindInBufferMultiple->fetchAll(\PDO::FETCH_ASSOC);
        }
    
        public function findInBuffer(string $query, int $bufferid, int $offset = 0, int $limit = 20) : array {
            $truncatedLimit = max(min($limit, 50), 0);
    
            $this->storedFindInBuffer->bindParam(':userid', $this->user->userid);
            $this->storedFindInBuffer->bindParam(':bufferid', $bufferid);
            $this->storedFindInBuffer->bindParam(':query', $query);
    
            $this->storedFindInBuffer->bindParam(':limit', $truncatedLimit);
            $this->storedFindInBuffer->bindParam(':offset', $offset);
    
            $this->storedFindInBuffer->execute();
            return $this->storedFindInBuffer->fetchAll(\PDO::FETCH_ASSOC);
        }
    
        public function context(int $anchor, int $buffer, int $loadBefore, int $loadAfter) : array {
            return array_merge(array_reverse($this->before($anchor, $buffer, $loadBefore)), $this->after($anchor, $buffer, $loadAfter));
        }
    
        public function before(int $anchor, int $buffer, int $limit) : array {
            $truncatedLimit = max(min($limit, 50), 0);
    
            $this->loadBefore->bindParam(":userid", $this->user->userid);
            $this->loadBefore->bindParam(":bufferid", $buffer);
            $this->loadBefore->bindParam(":anchor", $anchor);
            $this->loadBefore->bindParam(":limit", $truncatedLimit);
            $this->loadBefore->execute();
            return $this->loadBefore->fetchAll(\PDO::FETCH_ASSOC);
        }
    
        public function after(int $anchor, int $buffer, int $limit) : array {
            $truncatedLimit = max(min($limit + 1, 50), 1);
    
            $this->loadAfter->bindParam(":userid", $this->user->userid);
            $this->loadAfter->bindParam(":bufferid", $buffer);
            $this->loadAfter->bindParam(":anchor", $anchor);
            $this->loadAfter->bindParam(":limit", $truncatedLimit);
            $this->loadAfter->execute();
            return $this->loadAfter->fetchAll(\PDO::FETCH_ASSOC);
        }
    }