1<?php
2
3use dokuwiki\plugin\sqlite\QuerySaver;
4use dokuwiki\plugin\sqlite\SQLiteDB;
5
6use dokuwiki\plugin\sql2wiki\Csv;
7
8/**
9 * DokuWiki Plugin sql2wiki (Action Component)
10 *
11 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
12 * @author  Szymon Olewniczak <it@rid.pl>
13 */
14
15class action_plugin_sql2wiki_sqlite extends \dokuwiki\Extension\ActionPlugin
16{
17    const PLUGIN_SQL2WIKI_EDIT_SUMMARY = 'plugin sql2wiki';
18    const PLUGIN_SQL2WIKI_EDIT_SUMMARY_INFINITE_LOOP = 'plugin sql2wiki: syntax commented out: query results may depend on page revision';
19
20    /** @var array databases that has changed */
21    protected $queue = [];
22
23    protected function queue_put($db, $query_name='') {
24        if (!isset($this->queue[$db])) {
25            $this->queue[$db] = [];
26        }
27        // if query_name is not specified, update all database queries and don't consider future query_name
28        if (empty($query_name)) {
29            $this->queue[$db] = true;
30        }
31        // add new query_name if we still did not request all database queries update
32        if (is_array($this->queue[$db])) {
33            $this->queue[$db][$query_name] = true;
34        }
35    }
36
37    protected function queue_filtered($sql2wiki_data) {
38        // ignore the queries that have not been changed in this request
39        $queue = $this->queue;
40        return array_filter($sql2wiki_data, function ($query) use ($queue) {
41            $db = $query['db'];
42            $query_name = $query['query_name'];
43            return isset($queue[$db]) && ($queue[$db] === true || isset($queue[$db][$query_name]));
44        });
45    }
46
47    protected function queue_clean() {
48        $this->queue = [];
49    }
50
51    /** @inheritDoc */
52    public function register(Doku_Event_Handler $controller)
53    {
54        $controller->register_hook('PLUGIN_SQLITE_QUERY_EXECUTE', 'AFTER', $this, 'handle_plugin_sqlite_query_execute');
55        $controller->register_hook('PLUGIN_SQLITE_QUERY_SAVE', 'AFTER', $this, 'handle_plugin_sqlite_query_change');
56        $controller->register_hook('PLUGIN_SQLITE_QUERY_DELETE', 'AFTER', $this, 'handle_plugin_sqlite_query_change');
57        // update pages after all saving and metadata updating has happened
58        $controller->register_hook('DOKUWIKI_DONE', 'AFTER', $this, 'update_pages_content');
59        // if we have updated the page we are currently viewing, redirect to updated version
60        // this is why we are using ACTION_HEADERS_SEND event here
61        $controller->register_hook('ACTION_HEADERS_SEND', 'AFTER', $this, 'check_current_page_for_updates');
62        // support for struct inline edits
63        $controller->register_hook('AJAX_CALL_UNKNOWN', 'AFTER', $this, 'update_pages_content');
64    }
65
66    public function check_current_page_for_updates(Doku_Event $event, $param) {
67        global $ID, $ACT;
68
69        if ($ACT != 'show') return; // update the page content only when we viewing it
70
71        $sql2wiki_data = p_get_metadata($ID, 'plugin_sql2wiki');
72        if (!$sql2wiki_data) return;
73
74        // check if we have some resutls updates
75        $something_changed = $this->update_query_results($ID, $sql2wiki_data, 1);
76        if ($something_changed) {
77            // update other pages in queue and redirect
78            $this->update_pages_content($event, [$ID]);
79            $go = wl($ID, '', true, '&');
80            send_redirect($go);
81        }
82    }
83
84    public function update_pages_content(Doku_Event $event, $param) {
85        global $ID;
86
87        $filter_ids = []; // don't update specific pages
88        if (!is_null($param)) $filter_ids = $param;
89
90        $indexer = idx_get_indexer();
91        $dbs = array_keys($this->queue);
92        $dbs_pages = $indexer->lookupKey('sql2wiki_db', $dbs);
93        $pages = array_unique(array_merge(...array_values($dbs_pages)));
94        foreach ($pages as $page) {
95            if (in_array($page, $filter_ids)) continue;
96            $sql2wiki_data = p_get_metadata($page, 'plugin_sql2wiki');
97            if (!$sql2wiki_data) continue;
98            $sql2wiki_filtered = $this->queue_filtered($sql2wiki_data);
99            // the $ID is usually updated in check_current_page_for_updates
100            // but when $ACT != 'show' the current page might be not updated yet
101            $sleep = $page == $ID ? 1 : 0;
102            $this->update_query_results($page, $sql2wiki_filtered, $sleep);
103        }
104        $this->queue_clean();
105    }
106
107    public function handle_plugin_sqlite_query_execute(Doku_Event $event, $param)
108    {
109        if ($event->data['stmt']->rowCount() == 0) return; // ignore select queries
110        $db = $event->data['sqlitedb']->getDbName();
111        $this->queue_put($db);
112    }
113
114    public function handle_plugin_sqlite_query_change(Doku_Event $event, $param)
115    {
116        $upstream = $event->data['upstream'];
117        $query_name = $event->data['name'];
118        $this->queue_put($upstream, $query_name);
119    }
120
121    protected function get_page_content_with_wrapped_tags($page_content, $sql2wiki_data) {
122        $offset = 0;
123        foreach ($sql2wiki_data as $sql2wiki_query) {
124            $pos = $sql2wiki_query['pos'] - 1;
125            $match = $sql2wiki_query['match'];
126            $wrapped_tag = '<code>' . $match . '</code>';
127            $updated_content = substr_replace($page_content, $wrapped_tag, $pos + $offset, strlen($match));
128            $offset = strlen($updated_content) - strlen($page_content);
129            $page_content = $updated_content;
130        }
131        return $page_content;
132    }
133
134    protected function get_updated_page_content($page_content, $page, $sql2wiki_data) {
135        $offset = 0;
136        $logger_details = [
137            'page' => $page,
138            'before_page_content' => $page_content,
139            'sql2wiki_data' => $sql2wiki_data,
140            'results' => []
141        ];
142
143        foreach ($sql2wiki_data as $sql2wiki_query) {
144            $sqliteDb = new SQLiteDB($sql2wiki_query['db'], '');
145            $querySaver = new QuerySaver($sqliteDb->getDBName());
146            $query = $querySaver->getQuery($sql2wiki_query['query_name']);
147            if ($query) {
148                $params = str_replace([
149                    '$ID$',
150                    '$NS$',
151                    '$PAGE$'
152                ], [
153                    $page,
154                    getNS($page),
155                    noNS($page)
156                ], $sql2wiki_query['args']);
157
158                $result = $sqliteDb->queryAll($query, $params);
159                if (isset($result[0])) { // generate header if any row exists
160                    array_unshift($result, array_keys($result[0]));
161                }
162                $query_result_csv = "\n" . Csv::arr2csv($result); // "\n" to wrap the <sql2wiki> tag
163                $logger_details['results'][] = $result;
164            } else { //unknown query - clear the results
165                $query_result_csv = "";
166                $logger_details['results'][] = null;
167            }
168
169            $start = $sql2wiki_query['start'];
170            $end = $sql2wiki_query['end'];
171            $length = $end - $start;
172            $updated_content = substr_replace($page_content, $query_result_csv, $start + $offset, $length);
173            $offset = strlen($updated_content) - strlen($page_content);
174            $page_content = $updated_content;
175        }
176        $before_sql2wiki_opening_tags = substr_count($logger_details['before_page_content'], '<sql2wiki');
177        $before_sql2wiki_closing_tags = substr_count($logger_details['before_page_content'], '</sql2wiki>');
178        $after_sql2wiki_opening_tags = substr_count($page_content, '<sql2wiki');
179        $after_sql2wiki_closing_tags = substr_count($page_content, '</sql2wiki>');
180        if ($before_sql2wiki_opening_tags != $after_sql2wiki_opening_tags ||
181            $before_sql2wiki_closing_tags != $after_sql2wiki_closing_tags ||
182            $after_sql2wiki_opening_tags != $after_sql2wiki_closing_tags
183        ) {
184            $logger_details['after_page_content'] = $page_content;
185            \dokuwiki\Logger::error('sql2wiki', $logger_details, __FILE__, __LINE__);
186        }
187        return $page_content;
188    }
189
190    protected function update_query_results($page, $sql2wiki_data, $sleep=0) {
191        $page_content = file_get_contents(wikiFN($page));
192        $updated_content = $this->get_updated_page_content($page_content, $page, $sql2wiki_data);
193        if ($page_content != $updated_content) {
194            sleep($sleep); // wait if we are processing currently viewed page
195            saveWikiText($page, $updated_content, self::PLUGIN_SQL2WIKI_EDIT_SUMMARY);
196            $next_update = $this->get_updated_page_content($page_content, $page, $sql2wiki_data);
197            // this may mean that the query results depend on page revisions which leads to infinite loop
198            if ($updated_content != $next_update) {
199                // comment out <sql2wiki> tags to prevent infinite loop
200                $wrapped_content = $this->get_page_content_with_wrapped_tags($page_content, $sql2wiki_data);
201                sleep(1); // wait for all types of updates since we have just updated the page
202                saveWikiText($page, $wrapped_content, self::PLUGIN_SQL2WIKI_EDIT_SUMMARY_INFINITE_LOOP);
203            }
204            return true;
205        }
206        return false;
207    }
208}