<commit_before><?php

namespace QuasselRestSearch;

require_once 'Backend.php';

class PostgresSmartBackend implements Backend
{
    private $db;
    private $options;

    function __construct(\PDO $db, array $options)
    {
        $this->db = $db;
        $this->db->exec("SET statement_timeout = 5000;");
        $this->options = $options;
    }

    public function findUser(): \PDOStatement
    {
        return $this->db->prepare("
            SELECT *
            FROM quasseluser
            WHERE quasseluser.username = :username
        ");
    }

    private function tsQueryFunction(): string
    {
        return array_key_exists('tsqueryfunction', $this->options) ? $this->options['tsqueryfunction'] : "plainto_tsquery('english', :query)";
    }

    public function findInBuffers(): \PDOStatement
    {
        $tsQueryFunction = $this->tsQueryFunction();
        return $this->db->prepare("
            SELECT
              ranked_messages.bufferid,
              ranked_messages.buffername,
              ranked_messages.networkname,
              ranked_messages.messageid,
              ranked_messages.time,
              ranked_messages.sender,
              ts_headline(replace(replace(ranked_messages.message, '<', '&lt;'), '>', '&gt;'), query, 'HighlightAll=TRUE') AS message
            FROM
              (SELECT
                 matching_messages.*,
                 network.networkname,
                 sender.sender,
                 rank()
                 OVER (
                   PARTITION BY matching_messages.bufferid
                   ORDER BY matching_messages.rank_value DESC
                   ) AS rank,
                 first_value(rank_value)
                 OVER (
                   PARTITION BY matching_messages.bufferid
                   ORDER BY matching_messages.rank_value DESC
                   ) AS max_rank_value
               FROM
                 (SELECT
                    backlog.messageid,
                    backlog.bufferid,
                    buffer.buffername,
                    buffer.networkid,
                    backlog.senderid,
                    backlog.time,
                    backlog.message,
                    query,
                    (
                      (ts_rank(tsv, query, :config_normalization) ^ :weight_content) *
                      ((CASE
                        WHEN TYPE IN (1, 4) THEN 1.0
                        WHEN TYPE IN (2, 1024, 2048, 4096, 16384) THEN 0.75
                        WHEN TYPE IN (32, 64, 128, 256, 512, 32768, 65536) THEN 0.5
                        WHEN TYPE IN (8, 16, 8192, 131072) THEN 0.25
                        ELSE 0.1 END) ^ :weight_type) *
                      ((EXTRACT(EPOCH FROM TIME) / EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)) ^ :weight_time)
                    ) AS rank_value
                  FROM
                    backlog
                    JOIN buffer ON backlog.bufferid = buffer.bufferid
                    , phraseto_tsquery_multilang(:query) query
                  WHERE buffer.userid = :userid
                    AND (:ignore_since::BOOLEAN OR backlog.time > :since::TIMESTAMP)
                    AND (:ignore_before::BOOLEAN OR backlog.time < :before::TIMESTAMP)
                    AND (:ignore_buffer::BOOLEAN OR buffer.buffername ~* :buffer)
                    AND backlog.type & 23559 > 0
                    AND backlog.tsv @@ query
                 ) matching_messages
                JOIN sender ON matching_messages.senderid = sender.senderid
                JOIN network ON matching_messages.networkid = network.networkid
                WHERE (:ignore_network::BOOLEAN OR network.networkname ~* :network)
                  AND (:ignore_sender::BOOLEAN OR sender.sender ~* :sender)
              ) ranked_messages<commit_msg>This is why you don’t code at 5am…<commit_after><?php

namespace QuasselRestSearch;

require_once 'Backend.php';

class PostgresSmartBackend implements Backend
{
    private $db;
    private $options;

    function __construct(\PDO $db, array $options)
    {
        $this->db = $db;
        $this->db->exec("SET statement_timeout = 5000;");
        $this->options = $options;
    }

    public function findUser(): \PDOStatement
    {
        return $this->db->prepare("
            SELECT *
            FROM quasseluser
            WHERE quasseluser.username = :username
        ");
    }

    private function tsQueryFunction(): string
    {
        return array_key_exists('tsqueryfunction', $this->options) ? $this->options['tsqueryfunction'] : "plainto_tsquery('english', :query)";
    }

    public function findInBuffers(): \PDOStatement
    {
        $tsQueryFunction = $this->tsQueryFunction();
        return $this->db->prepare("
            SELECT
              ranked_messages.bufferid,
              ranked_messages.buffername,
              ranked_messages.networkname,
              ranked_messages.messageid,
              ranked_messages.time,
              ranked_messages.sender,
              ts_headline(replace(replace(ranked_messages.message, '<', '&lt;'), '>', '&gt;'), query, 'HighlightAll=TRUE') AS message
            FROM
              (SELECT
                 matching_messages.*,
                 network.networkname,
                 sender.sender,
                 rank()
                 OVER (
                   PARTITION BY matching_messages.bufferid
                   ORDER BY matching_messages.rank_value DESC
                   ) AS rank,
                 first_value(rank_value)
                 OVER (
                   PARTITION BY matching_messages.bufferid
                   ORDER BY matching_messages.rank_value DESC
                   ) AS max_rank_value
               FROM
                 (SELECT
                    backlog.messageid,
                    backlog.bufferid,
                    buffer.buffername,
                    buffer.networkid,
                    backlog.senderid,
                    backlog.time,
                    backlog.message,
                    query,
                    (
                      (ts_rank(tsv, query, :config_normalization) ^ :weight_content) *
                      ((CASE
                        WHEN TYPE IN (1, 4) THEN 1.0
                        WHEN TYPE IN (2, 1024, 2048, 4096, 16384) THEN 0.75
                        WHEN TYPE IN (32, 64, 128, 256, 512, 32768, 65536) THEN 0.5
                        WHEN TYPE IN (8, 16, 8192, 131072) THEN 0.25
                        ELSE 0.1 END) ^ :weight_type) *
                      ((EXTRACT(EPOCH FROM TIME) / EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)) ^ :weight_time)
                    ) AS rank_value
                  FROM
                    backlog
                    JOIN buffer ON backlog.bufferid = buffer.bufferid
                    , $tsQueryFunction query
                  WHERE buffer.userid = :userid
                    AND (:ignore_since::BOOLEAN OR backlog.time > :since::TIMESTAMP)
                    AND (:ignore_before::BOOLEAN OR backlog.time < :before::TIMESTAMP)
                    AND (:ignore_buffer::BOOLEAN OR buffer.buffername ~* :buffer)
                    AND backlog.type & 23559 > 0
                    AND backlog.tsv @@ query
                 ) matching_messages
                JOIN sender ON matching_messages.senderid = sender.senderid
                JOIN network ON matching_messages.networkid = network.networkid
                WHERE (:ignore_network::BOOLEAN OR network.networkname ~* :network)
                  AND (:ignore_sender::BOOLEAN OR sender.sender ~* :sender)
              ) ranked_messages
{'fraction_non_alphanumeric': 0.08547008547008547, 'fraction_numerical': 0.02064431295200526, 'mean_word_length': 1.4331413947536789, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 31, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2355165', 'n_tokens_mistral': 2126, 'n_tokens_neox': 1904, 'n_words': 442}