1 <?php
2 
3 use dokuwiki\Extension\AdminPlugin;
4 use dokuwiki\Form\Form;
5 use dokuwiki\Form\InputElement;
6 use dokuwiki\plugin\sqlite\QuerySaver;
7 use dokuwiki\plugin\sqlite\SQLiteDB;
8 use dokuwiki\plugin\sqlite\Tools;
9 
10 /**
11  * DokuWiki Plugin sqlite (Admin Component)
12  *
13  * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
14  * @author  Andreas Gohr <andi@splitbrain.org>
15  */
16 class admin_plugin_sqlite extends AdminPlugin
17 {
18     /** @var SQLiteDB */
19     protected $db;
20 
21     /** @var QuerySaver */
22     protected $querySaver;
23 
24     /** @inheritdoc */
25     public function getMenuSort()
26     {
27         return 500;
28     }
29 
30     /** @inheritdoc */
31     public function forAdminOnly()
32     {
33         return true;
34     }
35 
36     /** @inheritdoc */
37     public function handle()
38     {
39         global $conf;
40         global $INPUT;
41 
42         // load database if given and security token is valid
43         if ($INPUT->str('db') && checkSecurityToken()) {
44             try {
45                 $this->db = new SQLiteDB($INPUT->str('db'), '');
46                 $this->querySaver = new QuerySaver($this->db->getDBName());
47             } catch (Exception $e) {
48                 msg($e->getMessage(), -1);
49                 return;
50             }
51         }
52 
53         $cmd = $INPUT->extract('cmd')->str('cmd');
54         switch ($cmd) {
55             case 'export':
56                 $exportfile = $conf['tmpdir'] . '/' . $this->db->getDbName() . '.sql';
57                 $this->db->dumpToFile($exportfile);
58                 header('Content-Type: text/sql');
59                 header('Content-Disposition: attachment; filename="' . $this->db->getDbName() . '.sql";');
60                 readfile($exportfile);
61                 unlink($exportfile);
62                 exit(0);
63             case 'import':
64                 $importfile = $_FILES['importfile']['tmp_name'];
65 
66                 if (empty($importfile)) {
67                     msg($this->getLang('import_no_file'), -1);
68                     return;
69                 }
70 
71                 $sql = Tools::SQLstring2array(file_get_contents($importfile));
72                 try {
73                     $this->db->getPdo()->beginTransaction();
74                     foreach ($sql as $s) {
75                         $this->db->exec($s);
76                     }
77                     $this->db->getPdo()->commit();
78                     msg($this->getLang('import_success'), 1);
79                 } catch (Exception $e) {
80                     $this->db->getPdo()->rollBack();
81                     msg(hsc($e->getMessage()), -1);
82                 }
83                 break;
84             case 'save_query':
85                 $this->querySaver->saveQuery($INPUT->str('name'), $INPUT->str('sql'));
86                 break;
87             case 'delete_query':
88                 $this->querySaver->deleteQuery($INPUT->str('name'));
89                 break;
90             case 'download':
91                 $file = $this->db->getDbFile();
92                 header('Content-Type: application/vnd.sqlite3');
93                 header(
94                     'Content-Disposition: attachment; filename="'
95                     . $this->db->getDbName() . SQLiteDB::FILE_EXTENSION . '"'
96                 );
97                 readfile($file);
98                 exit(0);
99         }
100     }
101 
102     /** @inheritdoc */
103     public function html()
104     {
105         global $INPUT;
106 
107         echo '<div class="plugin_sqlite_admin">';
108         echo $this->locale_xhtml('intro');
109 
110         if ($this->db) {
111             echo '<h2>' . $this->getLang('db') . ' "' . hsc($this->db->getDbName()) . '"</h2>';
112 
113             echo '<div class="commands">';
114             $this->showCommands();
115             $this->showSavedQueries();
116             echo '</div>';
117 
118             // query form
119             $form = new Form(['action' => $this->selfLink()]);
120             $form->addClass('sqliteplugin');
121             $form->addFieldsetOpen('SQL Command');
122             $form->addTextarea('sql')->addClass('edit');
123             $form->addButton('submit', $this->getLang('btn_execute'))->attr('type', 'submit');
124             $form->addTextInput('name', $this->getLang('query_name'));
125             $form->addButton('cmd[save_query]', $this->getLang('save_query'))->attr('type', 'submit');
126             $form->addFieldsetClose();
127             echo $form->toHTML();
128 
129             // results
130             if ($INPUT->has('sql')) $this->showQueryResults($INPUT->str('sql'));
131         }
132         echo '</div>';
133     }
134 
135     /**
136      * List all available databases in the TOC
137      *
138      * @inheritdoc
139      */
140     public function getTOC()
141     {
142         global $conf;
143         global $ID;
144 
145         $toc = [];
146         $toc[] = [
147             'link' => wl($ID, ['do' => 'admin', 'page' => 'sqlite']),
148             'title' => $this->getLang('db') . ':',
149             'level' => 1,
150             'type' => 'ul',
151         ];
152         $dbfiles = glob($conf['metadir'] . '/*.sqlite3');
153         if (is_array($dbfiles)) foreach ($dbfiles as $file) {
154             $db = basename($file, '.sqlite3');
155             $toc[] = [
156                 'link' => wl($ID, ['do' => 'admin', 'page' => 'sqlite', 'db' => $db, 'sectok' => getSecurityToken()]),
157                 'title' => $db,
158                 'level' => 2,
159                 'type' => 'ul',
160             ];
161         }
162 
163         return $toc;
164     }
165 
166     /**
167      * Execute and display the results of the given SQL query
168      *
169      * multiple queries can be given separated by semicolons
170      *
171      * @param string $sql
172      */
173     protected function showQueryResults($sql)
174     {
175         echo '<h3 id="scroll__here">Query results</h3>';
176 
177         $sql = Tools::SQLstring2array($sql);
178         foreach ($sql as $s) {
179             try {
180                 $time_start = microtime(true);
181                 $result = $this->db->queryAll($s);
182                 $time_end = microtime(true);
183             } catch (Exception $e) {
184                 msg($e->getMessage(), -1);
185                 continue;
186             }
187 
188             $time = $time_end - $time_start;
189             $cnt = count($result);
190             msg($cnt . ' affected rows in ' . $this->microtimeToSeconds($time) . ' seconds', 1);
191             if (!$cnt) continue;
192 
193             echo '<div>';
194             $ths = array_keys($result[0]);
195             echo '<table class="inline">';
196             echo '<tr>';
197             foreach ($ths as $th) {
198                 echo '<th>' . hsc($th) . '</th>';
199             }
200             echo '</tr>';
201             foreach ($result as $row) {
202                 echo '<tr>';
203                 $tds = array_values($row);
204                 foreach ($tds as $td) {
205                     if ($td === null) $td = '␀';
206                     echo '<td>' . hsc($td) . '</td>';
207                 }
208                 echo '</tr>';
209             }
210             echo '</table>';
211             echo '</div>';
212         }
213     }
214 
215 
216     /**
217      * Convert a microtime() value to a string in seconds
218      *
219      * @param float $time
220      * @return string
221      */
222     protected function microtimeToSeconds($time)
223     {
224         return ($time < 0.0001 ? substr($time, 0, 5) . substr($time, -3) : substr($time, 0, 7));
225     }
226 
227     /**
228      * Construct a link to the sqlite admin page with the given additional parameters
229      *
230      * Basically a wrapper around wl() with some defaults
231      *
232      * @param string[] $params
233      * @param bool $form for use in form action?
234      * @return string
235      */
236     protected function selfLink($form = true, $params = [])
237     {
238         global $ID;
239         $params = array_merge(
240             [
241                 'do' => 'admin',
242                 'page' => 'sqlite',
243                 'db' => $this->db ? $this->db->getDBName() : '',
244                 'sectok' => getSecurityToken(),
245             ],
246             $params
247         );
248 
249         return wl($ID, $params, false, $form ? '&' : '&amp;');
250     }
251 
252     /**
253      * Display the standard actions for a database
254      */
255     protected function showCommands()
256     {
257         $commands = [
258             'dbversion' => [
259                 'sql' => 'SELECT val FROM opts WHERE opt=\'dbversion\'',
260             ],
261             'table' => [
262                 'sql' => 'SELECT name,sql FROM sqlite_master WHERE type=\'table\' ORDER BY name',
263             ],
264             'index' => [
265                 'sql' => 'SELECT name,sql FROM sqlite_master WHERE type=\'index\' ORDER BY name',
266             ],
267             'export' => [
268                 'cmd' => 'export'
269             ],
270             'download' => [
271                 'cmd' => 'download'
272             ],
273         ];
274 
275         // import form
276         $form = new Form(['action' => $this->selfLink(), 'enctype' => 'multipart/form-data', 'method' => 'post']);
277         $form->addElement(
278             (new InputElement('file', 'importfile'))
279                 ->attr('required', 'required')
280                 ->attr('accept', '.sql')
281         );
282         $form->addButton('cmd[import]', $this->getLang('import'));
283 
284         // output as a list
285         echo '<ul>';
286         foreach ($commands as $label => $command) {
287             echo '<li><div class="li">';
288             echo '<a href="' . $this->selfLink(false, $command) . '">' . $this->getLang($label) . '</a>';
289             echo '</div></li>';
290         }
291         echo '<li><div class="li">';
292         echo $form->toHTML();
293         echo '</div></li>';
294         echo '</ul>';
295     }
296 
297     /**
298      * Display the saved queries for this database
299      */
300     public function showSavedQueries()
301     {
302         $queries = $this->querySaver->getQueries();
303         if (!$queries) return;
304 
305         echo '<ul>';
306         foreach ($queries as $query) {
307             $link = $this->selfLink(false, ['sql' => $query['sql']]);
308             $del = $this->selfLink(false, ['cmd' => 'delete_query', 'name' => $query['name']]);
309 
310             echo '<li><div class="li">';
311             echo '<a href="' . $link . '">' . hsc($query['name']) . '</a>';
312             echo ' [<a href="' . $del . '">' . $this->getLang('delete_query') . '</a>]';
313             echo '</div></li>';
314         }
315         echo '</ul>';
316     }
317 }
318