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