<?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, '<', '<'), '>', '>'), 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, '<', '<'), '>', '>'), 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);
}
}