1<?php
2
3use dokuwiki\Extension\AdminPlugin;
4use dokuwiki\Form\Form;
5use dokuwiki\Form\InputElement;
6use dokuwiki\plugin\sqlite\QuerySaver;
7use dokuwiki\plugin\sqlite\SQLiteDB;
8use 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 */
16class 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