1 <?php
2 
3 use dokuwiki\plugin\sqlite\QuerySaver;
4 use dokuwiki\plugin\sqlite\SQLiteDB;
5 
6 use 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 
15 class 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 }