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