xref: /plugin/sqlite/admin.php (revision a7a40fb292e55f99ed1ae34981feed1314e2696b)
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('Content-Disposition: attachment; filename="' . $this->db->getDbName() . SQLiteDB::FILE_EXTENSION . '"');
94                readfile($file);
95                exit(0);
96        }
97    }
98
99    /** @inheritdoc */
100    public function html()
101    {
102        global $INPUT;
103
104        echo '<div class="plugin_sqlite_admin">';
105        echo $this->locale_xhtml('intro');
106
107        if ($this->db) {
108            echo '<h2>' . $this->getLang('db') . ' "' . hsc($this->db->getDbName()) . '"</h2>';
109
110            echo '<div class="commands">';
111            $this->showCommands();
112            $this->showSavedQueries();
113            echo '</div>';
114
115            // query form
116            $form = new Form(['action' => $this->selfLink()]);
117            $form->addClass('sqliteplugin');
118            $form->addFieldsetOpen('SQL Command');
119            $form->addTextarea('sql')->addClass('edit');
120            $form->addButton('submit', $this->getLang('btn_execute'))->attr('type', 'submit');
121            $form->addTextInput('name', $this->getLang('query_name'));
122            $form->addButton('cmd[save_query]', $this->getLang('save_query'))->attr('type', 'submit');
123            $form->addFieldsetClose();
124            echo $form->toHTML();
125
126            // results
127            if ($INPUT->has('sql')) $this->showQueryResults($INPUT->str('sql'));
128        }
129        echo '</div>';
130    }
131
132    /**
133     * List all available databases in the TOC
134     *
135     * @inheritdoc
136     */
137    public function getTOC()
138    {
139        global $conf;
140        global $ID;
141
142        $toc = [];
143        $toc[] = [
144            'link' => wl($ID, ['do' => 'admin', 'page' => 'sqlite']),
145            'title' => $this->getLang('db') . ':',
146            'level' => 1,
147            'type' => 'ul',
148        ];
149        $dbfiles = glob($conf['metadir'] . '/*.sqlite3');
150        if (is_array($dbfiles)) foreach ($dbfiles as $file) {
151            $db = basename($file, '.sqlite3');
152            $toc[] = ['link' => wl($ID, ['do' => 'admin', 'page' => 'sqlite', 'db' => $db, 'sectok' => getSecurityToken()]), 'title' => $db, 'level' => 2, 'type' => 'ul'];
153        }
154
155        return $toc;
156    }
157
158    /**
159     * Execute and display the results of the given SQL query
160     *
161     * multiple queries can be given separated by semicolons
162     *
163     * @param string $sql
164     */
165    protected function showQueryResults($sql)
166    {
167        echo '<h3 id="scroll__here">Query results</h3>';
168
169        $sql = Tools::SQLstring2array($sql);
170        foreach ($sql as $s) {
171            try {
172                $time_start = microtime(true);
173                $result = $this->db->queryAll($s);
174                $time_end = microtime(true);
175            } catch (Exception $e) {
176                msg($e->getMessage(), -1);
177                continue;
178            }
179
180            $time = $time_end - $time_start;
181            $cnt = count($result);
182            msg($cnt . ' affected rows in ' . $this->microtimeToSeconds($time) . ' seconds', 1);
183            if (!$cnt) continue;
184
185            echo '<div>';
186            $ths = array_keys($result[0]);
187            echo '<table class="inline">';
188            echo '<tr>';
189            foreach ($ths as $th) {
190                echo '<th>' . hsc($th) . '</th>';
191            }
192            echo '</tr>';
193            foreach ($result as $row) {
194                echo '<tr>';
195                $tds = array_values($row);
196                foreach ($tds as $td) {
197                    if ($td === null) $td = '␀';
198                    echo '<td>' . hsc($td) . '</td>';
199                }
200                echo '</tr>';
201            }
202            echo '</table>';
203            echo '</div>';
204        }
205    }
206
207
208    /**
209     * Convert a microtime() value to a string in seconds
210     *
211     * @param float $time
212     * @return string
213     */
214    protected function microtimeToSeconds($time)
215    {
216        return ($time < 0.0001 ? substr($time, 0, 5) . substr($time, -3) : substr($time, 0, 7));
217    }
218
219    /**
220     * Construct a link to the sqlite admin page with the given additional parameters
221     *
222     * Basically a wrapper around wl() with some defaults
223     *
224     * @param string[] $params
225     * @param bool $form for use in form action?
226     * @return string
227     */
228    protected function selfLink($form = true, $params = [])
229    {
230        global $ID;
231        $params = array_merge(
232            [
233                'do' => 'admin',
234                'page' => 'sqlite',
235                'db' => $this->db ? $this->db->getDBName() : '',
236                'sectok' => getSecurityToken(),
237            ],
238            $params
239        );
240
241        return wl($ID, $params, false, $form ? '&' : '&amp;');
242    }
243
244    /**
245     * Display the standard actions for a database
246     */
247    protected function showCommands()
248    {
249        $commands = [
250            'dbversion' => [
251                'sql' => 'SELECT val FROM opts WHERE opt=\'dbversion\'',
252            ],
253            'table' => [
254                'sql' => 'SELECT name,sql FROM sqlite_master WHERE type=\'table\' ORDER BY name',
255            ],
256            'index' => [
257                'sql' => 'SELECT name,sql FROM sqlite_master WHERE type=\'index\' ORDER BY name',
258            ],
259            'export' => [
260                'cmd' => 'export'
261            ],
262            'download' => [
263                'cmd' => 'download'
264            ],
265        ];
266
267        // import form
268        $form = new Form(['action' => $this->selfLink(), 'enctype' => 'multipart/form-data', 'method' => 'post']);
269        $form->addElement(
270            (new InputElement('file', 'importfile'))
271                ->attr('required', 'required')
272                ->attr('accept', '.sql')
273        );
274        $form->addButton('cmd[import]', $this->getLang('import'));
275
276        // output as a list
277        echo '<ul>';
278        foreach ($commands as $label => $command) {
279            echo '<li><div class="li">';
280            echo '<a href="' . $this->selfLink(false, $command) . '">' . $this->getLang($label) . '</a>';
281            echo '</div></li>';
282        }
283        echo '<li><div class="li">';
284        echo $form->toHTML();
285        echo '</div></li>';
286        echo '</ul>';
287    }
288
289    /**
290     * Display the saved queries for this database
291     */
292    public function showSavedQueries()
293    {
294        $queries = $this->querySaver->getQueries();
295        if (!$queries) return;
296
297        echo '<ul>';
298        foreach ($queries as $query) {
299            $link = $this->selfLink(false, ['sql' => $query['sql']]);
300            $del = $this->selfLink(false, ['cmd' => 'delete_query', 'name' => $query['name']]);
301
302            echo '<li><div class="li">';
303            echo '<a href="' . $link . '">' . hsc($query['name']) . '</a>';
304            echo ' [<a href="' . $del . '">' . $this->getLang('delete_query') . '</a>]';
305            echo '</div></li>';
306        }
307        echo '</ul>';
308    }
309}
310