1<?php
2/**
3 * DokuWiki Plugin issuelinks (Helper Component)
4 *
5 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
6 * @author  Andreas Gohr <dokuwiki@cosmocode.de>
7 */
8
9// must be run within Dokuwiki
10use dokuwiki\plugin\issuelinks\classes\Issue;
11
12class helper_plugin_issuelinks_db extends DokuWiki_Plugin
13{
14    private $db = null;
15
16    /**
17     * Save a key value pair to the database
18     *
19     * @param $key
20     * @param $value
21     *
22     * @return bool|null Returns false on error, nothing otherwise
23     */
24    public function saveKeyValuePair($key, $value)
25    {
26        $db = $this->getDB();
27        if (!$db) {
28            return false;
29        }
30        $sql = 'REPLACE INTO opts VALUES (?, ?)';
31        $db->query($sql, [$key, $value]);
32    }
33
34    /**
35     * Gives access to the sqlite DB.
36     *
37     * Returns null on error
38     *
39     * @return helper_plugin_sqlite|null
40     * @throws Exception Only thrown in unittests
41     */
42    public function getDB()
43    {
44        if (null === $this->db) {
45            /** @var helper_plugin_sqlite $sqlite */
46            $sqlite = plugin_load('helper', 'sqlite');
47            if (!$sqlite) {
48                msg('This plugin requires the sqlite plugin. Please install it', -1);
49                return null;
50            }
51
52            if ($sqlite->getAdapter()->getName() !== DOKU_EXT_PDO) {
53                if (defined('DOKU_UNITTEST')) {
54                    throw new \Exception('Couldn\'t load PDO sqlite.');
55                }
56                return null;
57            }
58            $sqlite->getAdapter()->setUseNativeAlter(true);
59
60            // initialize the database connection
61            if (!$sqlite->init('issuelinks', DOKU_PLUGIN . 'issuelinks/db/')) {
62                return null;
63            }
64
65            $this->db = $sqlite;
66        }
67        return $this->db;
68    }
69
70    /**
71     * Removes ALL entries from the database
72     *
73     * Useful when resetting the project configuration
74     */
75    public function reset()
76    {
77        $db = $this->getDB();
78        if (!$db) return;
79
80        $db->query('DELETE FROM issues');
81        $db->query('DELETE FROM issue_issues');
82        $db->query('DELETE FROM pagerev_issues');
83        $db->query('VACUUM');
84    }
85
86    /**
87     * Get a value to a stored key from the database
88     *
89     * @param $key
90     *
91     * @return bool|string
92     */
93    public function getKeyValue($key)
94    {
95        $db = $this->getDB();
96        if (!$db) {
97            return false;
98        }
99        $sql = 'SELECT val FROM opts WHERE opt = ?';
100        $res = $db->query($sql, [$key]);
101        $value = $db->res2single($res);
102        $db->res_close($res);
103        return $value;
104    }
105
106
107    /**
108     * @param string $service The name of the repository management service
109     * @param string $repo    The repository
110     * @param string $id      The id of the webhook
111     * @param string $secret  The secret to use when authenicationg incoming webhooks
112     */
113    public function saveWebhook($service, $repo, $id, $secret)
114    {
115        $entity = [
116            'service' => $service,
117            'repository_id' => $repo,
118            'id' => $id,
119            'secret' => $secret,
120        ];
121        $this->saveEntity('webhooks', $entity);
122    }
123
124    /**
125     * Saves the given key-value array to the given table
126     *
127     * @param string $table
128     * @param array  $entity associative array holding the key/value pairs
129     *
130     * @return bool|\SQLiteResult
131     */
132    private function saveEntity($table, $entity)
133    {
134        $db = $this->getDB();
135        if (!$db) {
136            return false;
137        }
138
139        $keys = implode(', ', array_keys($entity));
140        $vals = array_values($entity);
141        $wlds = implode(', ', array_fill(0, count($vals), '?'));
142
143        $sql = "REPLACE INTO $table ($keys) VALUES ($wlds)";
144        $ok = $db->query($sql, $vals);
145        if (empty($ok)) {
146            global $conf;
147            msg("Saving into table $table failed!", -1);
148            msg(print_r($entity, true), -1);
149            if ($conf['debug']) {
150                msg(dbg_backtrace(), -1);
151            }
152        }
153        return $ok;
154    }
155
156    /**
157     * Get the stored secret used to authenticate an incoming webhook
158     *
159     * @param string $rmservice
160     * @param string $repo
161     *
162     * @return array
163     */
164    public function getWebhookSecrets($service, $repo)
165    {
166        $sql = "SELECT secret FROM webhooks WHERE service = ? AND repository_id = ?";
167        $secrets = $this->sqlArrayQuery($sql, [$service, $repo]);
168        return $secrets;
169    }
170
171    /**
172     * make a provided sql query and return the resulting lines as an array of associative arrays
173     *
174     * @param string       $sql         the query
175     * @param string|array $conditional the parameters of the query
176     *
177     * @return array|bool
178     */
179    private function sqlArrayQuery($sql, $conditional)
180    {
181        if (substr(trim($sql), 0, strlen('SELECT')) !== 'SELECT') {
182            throw new InvalidArgumentException("SQL-Statement must be a SELECT statement! \n" . $sql);
183        }
184        if (strpos(trim($sql, ';'), ';') !== false) {
185            throw new InvalidArgumentException("SQL-Statement must be one single statement! \n" . $sql);
186        }
187        $db = $this->getDB();
188        if (!$db) {
189            return false;
190        }
191
192        $res = $db->query($sql, $conditional);
193        $result = $db->res2arr($res);
194        $db->res_close($res);
195        return $result;
196    }
197
198    /**
199     * @param string $service
200     * @param string $repo
201     * @param string $id
202     */
203    public function deleteWebhook($service, $repo, $id)
204    {
205        $entity = [
206            'service' => $service,
207            'repository_id' => $repo,
208            'id' => $id,
209        ];
210        $this->deleteEntity('webhooks', $entity);
211    }
212
213    /**
214     * Deletes the given key-value array to the given table
215     *
216     * @param string $table
217     * @param array  $entity associative array holding the key/value pairs for the where clause
218     */
219    private function deleteEntity($table, $entity)
220    {
221        $db = $this->getDB();
222        if (!$db) {
223            return;
224        }
225
226        $where = implode(' = ? AND ', array_keys($entity)) . ' = ?';
227        $vals = array_values($entity);
228
229        $sql = "DELETE FROM $table WHERE $where";
230        $db->query($sql, $vals);
231    }
232
233    public function getWebhooks($service, $repo = null, $id = null)
234    {
235        $sql = 'SELECT * FROM webhooks WHERE service = ?';
236        $params = [$service];
237        if ($repo) {
238            $sql .= ' AND repository_id = ?';
239            $params[] = $repo;
240        }
241        if ($id) {
242            $sql .= ' AND id = ?';
243            $params[] = $id;
244        }
245
246        $webhooks = $this->sqlArrayQuery($sql, $params);
247        return $webhooks;
248    }
249
250    /**
251     * Save an issue into the database
252     *
253     * @param Issue $issue
254     *
255     * @return bool
256     */
257    public function saveIssue(Issue $issue)
258    {
259        $ok = $this->saveEntity('issues', [
260            'service' => $issue->getServiceName(),
261            'project' => $issue->getProject(),
262            'id' => $issue->getKey(),
263            'is_mergerequest' => $issue->isMergeRequest() ? '1' : '0',
264            'summary' => $issue->getSummary(),
265            'description' => $issue->getDescription(),
266            'type' => $issue->getType(),
267            'status' => $issue->getStatus(),
268            'parent' => $issue->getParent(),
269            'components' => implode(',', $issue->getComponents()),
270            'labels' => implode(',', $issue->getLabels()),
271            'priority' => $issue->getPriority(),
272            'duedate' => $issue->getDuedate(),
273            'versions' => implode(',', $issue->getVersions()),
274            'updated' => $issue->getUpdated(),
275        ]);
276        return (bool)$ok;
277    }
278
279    /**
280     * Query the database for the issue corresponding to the given project and issueId
281     *
282     * @param string $serviceName The name of the project management service
283     * @param string $projectKey  The short-key of a project, e.g. SPR
284     * @param int    $issueId     The id of an issue e.g. 42
285     *
286     * @return bool|array
287     */
288    public function loadIssue($serviceName, $projectKey, $issueId, $isMergeRequest)
289    {
290        $sql = 'SELECT * FROM issues WHERE service = ? AND project = ? AND id = ? AND is_mergerequest = ?';
291        $issues = $this->sqlArrayQuery($sql, [$serviceName, $projectKey, $issueId, $isMergeRequest ? 1 : 0]);
292        return blank($issues[0]) ? false : $issues[0];
293    }
294
295    public function saveIssueIssues(Issue $issue, array $issues)
296    {
297        $this->deleteEntity('issue_issues', [
298            'service' => $issue->getServiceName(),
299            'project' => $issue->getProject(),
300            'id' => $issue->getKey(),
301            'is_mergerequest' => $issue->isMergeRequest() ? 1 : 0,
302        ]);
303        foreach ($issues as $issueData) {
304            $this->saveEntity('issue_issues', [
305                'service' => $issue->getServiceName(),
306                'project' => $issue->getProject(),
307                'id' => $issue->getKey(),
308                'is_mergerequest' => $issue->isMergeRequest() ? 1 : 0,
309                'referenced_service' => $issueData['service'],
310                'referenced_project' => $issueData['project'],
311                'referenced_id' => $issueData['issueId'],
312                'referenced_is_mergerequest' => 0,
313            ]);
314        }
315    }
316
317    public function getMergeRequestsReferencingIssue($serviceName, $project, $issueId, $isMergeRequest)
318    {
319        $sql = '
320        SELECT service, project as project_id, id as issue_id, is_mergerequest
321        FROM issue_issues
322        WHERE referenced_service = ?
323        AND referenced_project = ?
324        AND referenced_id = ?
325        AND referenced_is_mergerequest = ?
326        AND is_mergerequest = 1
327        ';
328        return $this->sqlArrayQuery($sql, [$serviceName, $project, $issueId, $isMergeRequest ? 1 : 0]);
329    }
330
331    /**
332     * Query the database for pages with link-syntax to the given issue
333     *
334     * @param string $serviceName The name of the project management service
335     * @param string $projectKey  The project short-key
336     * @param int    $issue_id    The ID of the issue, e.g. 42
337     *
338     * @return array
339     */
340    public function getAllPageLinkingToIssue($serviceName, $projectKey, $issue_id, $isMergeRequest)
341    {
342        $sql = "SELECT page, rev
343                FROM pagerev_issues
344                WHERE service = ?
345                AND project_id = ?
346                AND issue_id = ?
347                AND is_mergerequest = ?
348                AND type = 'link'
349                ORDER BY rev DESC ";
350        return $this->sqlArrayQuery($sql, [$serviceName, $projectKey, $issue_id, $isMergeRequest ? 1 : 0]);
351    }
352
353    /**
354     * Delete "Link"-references to old revisions from database
355     *
356     * @param string $serviceName The name of the project management service
357     * @param string $projectKey  The short-key for the project, e.g. SPR
358     * @param int    $issue_id    The id of the issue, e.g. 42
359     * @param array  $pages
360     *
361     * @return array
362     */
363    public function removeOldLinks($serviceName, $projectKey, $issue_id, $isMergeRequest, $pages)
364    {
365        $activeLinks = [];
366
367        foreach ($pages as $linkingPage) {
368            $changelog = new PageChangelog($linkingPage['page']);
369            $currentRev = $changelog->getRelativeRevision(time(), -1);
370            if ($linkingPage['rev'] < $currentRev) {
371                $entity = [
372                    'page' => $linkingPage['page'],
373                    'issue_id' => $issue_id,
374                    'project_id' => $projectKey,
375                    'service' => $serviceName,
376                    'is_mergerequest' => $isMergeRequest ? '1' : '0',
377                    'type' => 'link',
378                ];
379                $this->deleteEntity('pagerev_issues', $entity);
380            } else {
381                $activeLinks[] = $linkingPage;
382            }
383        }
384        return $activeLinks;
385    }
386
387    /**
388     * Save the connection between a Jira issue and a revision of a page.
389     *
390     * @param string $page
391     * @param int    $rev
392     * @param string $serviceName The name of the project management service
393     * @param string $project
394     * @param int    $issue_id
395     * @param string $type
396     *
397     * @return bool
398     *
399     * @throws \InvalidArgumentException
400     */
401    public function savePageRevIssues($page, $rev, $serviceName, $project, $issue_id, $isMergeRequest, $type)
402    {
403        /** @var helper_plugin_issuelinks_util $util */
404        $util = plugin_load('helper', 'issuelinks_util');
405        if (!$util->isValidTimeStamp($rev)) {
406            throw new InvalidArgumentException("Second parameter must be a valid timestamp!");
407        }
408        if ((int)$rev === 0) {
409            $rev = filemtime(wikiFN($page));
410            $changelog = new PageChangelog($page);
411            $rev_info = $changelog->getRevisionInfo($rev);
412            $user = $rev_info['user'] ? $rev_info['user'] : $rev_info['ip'];
413            $this->savePageRev($page, $rev, $rev_info['sum'], $user);
414        }
415        /** @noinspection TypeUnsafeComparisonInspection this is done to ensure $issue_id is a natural number */
416        if (!is_numeric($issue_id) || (int)$issue_id != $issue_id) {
417            throw new InvalidArgumentException("IssueId must be an integer!");
418        }
419        $ok = $this->saveEntity('pagerev_issues', [
420            'page' => $page,
421            'rev' => $rev,
422            'service' => $serviceName,
423            'project_id' => $project,
424            'issue_id' => $issue_id,
425            'is_mergerequest' => $isMergeRequest ? '1' : '0',
426            'type' => $type,
427        ]);
428
429        return (bool)$ok;
430    }
431
432    /**
433     * Save the data about a pagerevision
434     *
435     * @param string $page
436     * @param int    $rev
437     * @param string $summary
438     * @param string $user
439     *
440     * @return bool
441     */
442    public function savePageRev($page, $rev, $summary, $user)
443    {
444        if (blank($page) || blank($rev) || blank($user)) {
445            throw new InvalidArgumentException("No empty values allowed!");
446        }
447        /** @var helper_plugin_issuelinks_util $util */
448        $util = plugin_load('helper', 'issuelinks_util');
449        if (!$util->isValidTimeStamp($rev)) {
450            throw new InvalidArgumentException("Second parameter must be a valid timestamp!");
451        }
452        $ok = $this->saveEntity('pagerevs', [
453            'page' => $page,
454            'rev' => $rev,
455            'summary' => $summary,
456            'user' => $user,
457        ]);
458        return (bool)$ok;
459    }
460
461    /**
462     * Delete ALL entries from the database that correspond to the given page, issue and type.
463     *
464     * @param string $page        the wikipage
465     * @param string $serviceName The name of the project management service
466     * @param string $projectKey  the key of the project, e.g. SPR
467     * @param int    $issueId     the id of the issue, e.g. 42
468     * @param bool   $isMergeRequest
469     * @param string $type        either 'context' or 'link'
470     */
471    public function deleteAllIssuePageRevisions($page, $serviceName, $projectKey, $issueId, $isMergeRequest, $type)
472    {
473        // todo: validation
474        $this->deleteEntity('pagerev_issues', [
475            'page' => $page,
476            'service' => $serviceName,
477            'project_id' => $projectKey,
478            'issue_id' => $issueId,
479            'is_mergerequest' => $isMergeRequest ? 1 : 0,
480            'type' => $type,
481        ]);
482    }
483}
484