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