xref: /plugin/issuelinks/helper/db.php

<?php
/**
 * DokuWiki Plugin issuelinks (Helper Component)
 *
 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
 * @author  Andreas Gohr <dokuwiki@cosmocode.de>
 */

// must be run within Dokuwiki
use dokuwiki\plugin\issuelinks\classes\Issue;

class helper_plugin_issuelinks_db extends DokuWiki_Plugin
{
    private $db = null;

    /**
     * Save a key value pair to the database
     *
     * @param $key
     * @param $value
     *
     * @return bool|null Returns false on error, nothing otherwise
     */
    public function saveKeyValuePair($key, $value)
    {
        $db = $this->getDB();
        if (!$db) {
            return false;
        }
        $sql = 'REPLACE INTO opts VALUES (?, ?)';
        $db->query($sql, [$key, $value]);
    }

    /**
     * Gives access to the sqlite DB.
     *
     * Returns null on error
     *
     * @return helper_plugin_sqlite|null
     * @throws Exception Only thrown in unittests
     */
    public function getDB()
    {
        if (null === $this->db) {
            /** @var helper_plugin_sqlite $sqlite */
            $sqlite = plugin_load('helper', 'sqlite');
            if (!$sqlite) {
                msg('This plugin requires the sqlite plugin. Please install it', -1);
                return null;
            }

            if ($sqlite->getAdapter()->getName() !== DOKU_EXT_PDO) {
                if (defined('DOKU_UNITTEST')) {
                    throw new \Exception('Couldn\'t load PDO sqlite.');
                }
                return null;
            }
            $sqlite->getAdapter()->setUseNativeAlter(true);

            // initialize the database connection
            if (!$sqlite->init('issuelinks', DOKU_PLUGIN . 'issuelinks/db/')) {
                return null;
            }

            $this->db = $sqlite;
        }
        return $this->db;
    }

    /**
     * Removes ALL entries from the database
     *
     * Useful when resetting the project configuration
     */
    public function reset()
    {
        $db = $this->getDB();
        if (!$db) return;

        $db->query('DELETE FROM issues');
        $db->query('DELETE FROM issue_issues');
        $db->query('DELETE FROM pagerev_issues');
        $db->query('VACUUM');
    }

    /**
     * Get a value to a stored key from the database
     *
     * @param $key
     *
     * @return bool|string
     */
    public function getKeyValue($key)
    {
        $db = $this->getDB();
        if (!$db) {
            return false;
        }
        $sql = 'SELECT val FROM opts WHERE opt = ?';
        $res = $db->query($sql, [$key]);
        $value = $db->res2single($res);
        $db->res_close($res);
        return $value;
    }


    /**
     * @param string $service The name of the repository management service
     * @param string $repo    The repository
     * @param string $id      The id of the webhook
     * @param string $secret  The secret to use when authenicationg incoming webhooks
     */
    public function saveWebhook($service, $repo, $id, $secret)
    {
        $entity = [
            'service' => $service,
            'repository_id' => $repo,
            'id' => $id,
            'secret' => $secret,
        ];
        $this->saveEntity('webhooks', $entity);
    }

    /**
     * Saves the given key-value array to the given table
     *
     * @param string $table
     * @param array  $entity associative array holding the key/value pairs
     *
     * @return bool|\SQLiteResult
     */
    private function saveEntity($table, $entity)
    {
        $db = $this->getDB();
        if (!$db) {
            return false;
        }

        $keys = implode(', ', array_keys($entity));
        $vals = array_values($entity);
        $wlds = implode(', ', array_fill(0, count($vals), '?'));

        $sql = "REPLACE INTO $table ($keys) VALUES ($wlds)";
        $ok = $db->query($sql, $vals);
        if (empty($ok)) {
            global $conf;
            msg("Saving into table $table failed!", -1);
            msg(print_r($entity, true), -1);
            if ($conf['debug']) {
                msg(dbg_backtrace(), -1);
            }
        }
        return $ok;
    }

    /**
     * Get the stored secret used to authenticate an incoming webhook
     *
     * @param string $rmservice
     * @param string $repo
     *
     * @return array
     */
    public function getWebhookSecrets($service, $repo)
    {
        $sql = "SELECT secret FROM webhooks WHERE service = ? AND repository_id = ?";
        $secrets = $this->sqlArrayQuery($sql, [$service, $repo]);
        return $secrets;
    }

    /**
     * make a provided sql query and return the resulting lines as an array of associative arrays
     *
     * @param string       $sql         the query
     * @param string|array $conditional the parameters of the query
     *
     * @return array|bool
     */
    private function sqlArrayQuery($sql, $conditional)
    {
        if (substr(trim($sql), 0, strlen('SELECT')) !== 'SELECT') {
            throw new InvalidArgumentException("SQL-Statement must be a SELECT statement! \n" . $sql);
        }
        if (strpos(trim($sql, ';'), ';') !== false) {
            throw new InvalidArgumentException("SQL-Statement must be one single statement! \n" . $sql);
        }
        $db = $this->getDB();
        if (!$db) {
            return false;
        }

        $res = $db->query($sql, $conditional);
        $result = $db->res2arr($res);
        $db->res_close($res);
        return $result;
    }

    /**
     * @param string $service
     * @param string $repo
     * @param string $id
     */
    public function deleteWebhook($service, $repo, $id)
    {
        $entity = [
            'service' => $service,
            'repository_id' => $repo,
            'id' => $id,
        ];
        $this->deleteEntity('webhooks', $entity);
    }

    /**
     * Deletes the given key-value array to the given table
     *
     * @param string $table
     * @param array  $entity associative array holding the key/value pairs for the where clause
     */
    private function deleteEntity($table, $entity)
    {
        $db = $this->getDB();
        if (!$db) {
            return;
        }

        $where = implode(' = ? AND ', array_keys($entity)) . ' = ?';
        $vals = array_values($entity);

        $sql = "DELETE FROM $table WHERE $where";
        $db->query($sql, $vals);
    }

    public function getWebhooks($service, $repo = null, $id = null)
    {
        $sql = 'SELECT * FROM webhooks WHERE service = ?';
        $params = [$service];
        if ($repo) {
            $sql .= ' AND repository_id = ?';
            $params[] = $repo;
        }
        if ($id) {
            $sql .= ' AND id = ?';
            $params[] = $id;
        }

        $webhooks = $this->sqlArrayQuery($sql, $params);
        return $webhooks;
    }

    /**
     * Save an issue into the database
     *
     * @param Issue $issue
     *
     * @return bool
     */
    public function saveIssue(Issue $issue)
    {
        $ok = $this->saveEntity('issues', [
            'service' => $issue->getServiceName(),
            'project' => $issue->getProject(),
            'id' => $issue->getKey(),
            'is_mergerequest' => $issue->isMergeRequest() ? '1' : '0',
            'summary' => $issue->getSummary(),
            'description' => $issue->getDescription(),
            'type' => $issue->getType(),
            'status' => $issue->getStatus(),
            'parent' => $issue->getParent(),
            'components' => implode(',', $issue->getComponents()),
            'labels' => implode(',', $issue->getLabels()),
            'priority' => $issue->getPriority(),
            'duedate' => $issue->getDuedate(),
            'versions' => implode(',', $issue->getVersions()),
            'updated' => $issue->getUpdated(),
        ]);
        return (bool)$ok;
    }

    /**
     * Query the database for the issue corresponding to the given project and issueId
     *
     * @param string $serviceName The name of the project management service
     * @param string $projectKey  The short-key of a project, e.g. SPR
     * @param int    $issueId     The id of an issue e.g. 42
     *
     * @return bool|array
     */
    public function loadIssue($serviceName, $projectKey, $issueId, $isMergeRequest)
    {
        $sql = 'SELECT * FROM issues WHERE service = ? AND project = ? AND id = ? AND is_mergerequest = ?';
        $issues = $this->sqlArrayQuery($sql, [$serviceName, $projectKey, $issueId, $isMergeRequest ? 1 : 0]);
        return blank($issues[0]) ? false : $issues[0];
    }

    public function saveIssueIssues(Issue $issue, array $issues)
    {
        $this->deleteEntity('issue_issues', [
            'service' => $issue->getServiceName(),
            'project' => $issue->getProject(),
            'id' => $issue->getKey(),
            'is_mergerequest' => $issue->isMergeRequest() ? 1 : 0,
        ]);
        foreach ($issues as $issueData) {
            $this->saveEntity('issue_issues', [
                'service' => $issue->getServiceName(),
                'project' => $issue->getProject(),
                'id' => $issue->getKey(),
                'is_mergerequest' => $issue->isMergeRequest() ? 1 : 0,
                'referenced_service' => $issueData['service'],
                'referenced_project' => $issueData['project'],
                'referenced_id' => $issueData['issueId'],
                'referenced_is_mergerequest' => 0,
            ]);
        }
    }

    public function getMergeRequestsReferencingIssue($serviceName, $project, $issueId, $isMergeRequest)
    {
        $sql = '
        SELECT service, project as project_id, id as issue_id, is_mergerequest
        FROM issue_issues
        WHERE referenced_service = ?
        AND referenced_project = ?
        AND referenced_id = ?
        AND referenced_is_mergerequest = ?
        AND is_mergerequest = 1
        ';
        return $this->sqlArrayQuery($sql, [$serviceName, $project, $issueId, $isMergeRequest ? 1 : 0]);
    }

    /**
     * Query the database for pages with link-syntax to the given issue
     *
     * @param string $serviceName The name of the project management service
     * @param string $projectKey  The project short-key
     * @param int    $issue_id    The ID of the issue, e.g. 42
     *
     * @return array
     */
    public function getAllPageLinkingToIssue($serviceName, $projectKey, $issue_id, $isMergeRequest)
    {
        $sql = "SELECT page, rev
                FROM pagerev_issues
                WHERE service = ?
                AND project_id = ?
                AND issue_id = ?
                AND is_mergerequest = ?
                AND type = 'link'
                ORDER BY rev DESC ";
        return $this->sqlArrayQuery($sql, [$serviceName, $projectKey, $issue_id, $isMergeRequest ? 1 : 0]);
    }

    /**
     * Delete "Link"-references to old revisions from database
     *
     * @param string $serviceName The name of the project management service
     * @param string $projectKey  The short-key for the project, e.g. SPR
     * @param int    $issue_id    The id of the issue, e.g. 42
     * @param array  $pages
     *
     * @return array
     */
    public function removeOldLinks($serviceName, $projectKey, $issue_id, $isMergeRequest, $pages)
    {
        $activeLinks = [];

        foreach ($pages as $linkingPage) {
            $changelog = new PageChangelog($linkingPage['page']);
            $currentRev = $changelog->getRelativeRevision(time(), -1);
            if ($linkingPage['rev'] < $currentRev) {
                $entity = [
                    'page' => $linkingPage['page'],
                    'issue_id' => $issue_id,
                    'project_id' => $projectKey,
                    'service' => $serviceName,
                    'is_mergerequest' => $isMergeRequest ? '1' : '0',
                    'type' => 'link',
                ];
                $this->deleteEntity('pagerev_issues', $entity);
            } else {
                $activeLinks[] = $linkingPage;
            }
        }
        return $activeLinks;
    }

    /**
     * Save the connection between a Jira issue and a revision of a page.
     *
     * @param string $page
     * @param int    $rev
     * @param string $serviceName The name of the project management service
     * @param string $project
     * @param int    $issue_id
     * @param string $type
     *
     * @return bool
     *
     * @throws \InvalidArgumentException
     */
    public function savePageRevIssues($page, $rev, $serviceName, $project, $issue_id, $isMergeRequest, $type)
    {
        /** @var helper_plugin_issuelinks_util $util */
        $util = plugin_load('helper', 'issuelinks_util');
        if (!$util->isValidTimeStamp($rev)) {
            throw new InvalidArgumentException("Second parameter must be a valid timestamp!");
        }
        if ((int)$rev === 0) {
            $rev = filemtime(wikiFN($page));
            $changelog = new PageChangelog($page);
            $rev_info = $changelog->getRevisionInfo($rev);
            $user = $rev_info['user'] ? $rev_info['user'] : $rev_info['ip'];
            $this->savePageRev($page, $rev, $rev_info['sum'], $user);
        }
        /** @noinspection TypeUnsafeComparisonInspection this is done to ensure $issue_id is a natural number */
        if (!is_numeric($issue_id) || (int)$issue_id != $issue_id) {
            throw new InvalidArgumentException("IssueId must be an integer!");
        }
        $ok = $this->saveEntity('pagerev_issues', [
            'page' => $page,
            'rev' => $rev,
            'service' => $serviceName,
            'project_id' => $project,
            'issue_id' => $issue_id,
            'is_mergerequest' => $isMergeRequest ? '1' : '0',
            'type' => $type,
        ]);

        return (bool)$ok;
    }

    /**
     * Save the data about a pagerevision
     *
     * @param string $page
     * @param int    $rev
     * @param string $summary
     * @param string $user
     *
     * @return bool
     */
    public function savePageRev($page, $rev, $summary, $user)
    {
        if (blank($page) || blank($rev) || blank($user)) {
            throw new InvalidArgumentException("No empty values allowed!");
        }
        /** @var helper_plugin_issuelinks_util $util */
        $util = plugin_load('helper', 'issuelinks_util');
        if (!$util->isValidTimeStamp($rev)) {
            throw new InvalidArgumentException("Second parameter must be a valid timestamp!");
        }
        $ok = $this->saveEntity('pagerevs', [
            'page' => $page,
            'rev' => $rev,
            'summary' => $summary,
            'user' => $user,
        ]);
        return (bool)$ok;
    }

    /**
     * Delete ALL entries from the database that correspond to the given page, issue and type.
     *
     * @param string $page        the wikipage
     * @param string $serviceName The name of the project management service
     * @param string $projectKey  the key of the project, e.g. SPR
     * @param int    $issueId     the id of the issue, e.g. 42
     * @param bool   $isMergeRequest
     * @param string $type        either 'context' or 'link'
     */
    public function deleteAllIssuePageRevisions($page, $serviceName, $projectKey, $issueId, $isMergeRequest, $type)
    {
        // todo: validation
        $this->deleteEntity('pagerev_issues', [
            'page' => $page,
            'service' => $serviceName,
            'project_id' => $projectKey,
            'issue_id' => $issueId,
            'is_mergerequest' => $isMergeRequest ? 1 : 0,
            'type' => $type,
        ]);
    }
}