xref: /plugin/sqlite/admin.php (revision 33e488b3fffebc85f02333885d5d0ead1dd65c8b) 
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    function getTOC()
127    {
128        global $conf;
129        global $ID;
130
131        $toc = array();
132        $fileextensions = array('sqlite2' => '.sqlite', 'sqlite3' => '.sqlite3');
133
134        foreach ($fileextensions as $dbformat => $fileextension) {
135            $toc[] = array(
136                'link' => wl($ID, array('do' => 'admin', 'page' => 'sqlite')),
137                'title' => $dbformat . ':',
138                'level' => 1,
139                'type' => 'ul',
140            );
141
142            $dbfiles = glob($conf['metadir'] . '/*' . $fileextension);
143
144            if (is_array($dbfiles)) foreach ($dbfiles as $file) {
145                $db = basename($file, $fileextension);
146                $toc[] = array(
147                    'link' => wl($ID, array('do' => 'admin', 'page' => 'sqlite', 'db' => $db, 'version' => $dbformat, 'sectok' => getSecurityToken())),
148                    'title' => $this->getLang('db') . ' ' . $db,
149                    'level' => 2,
150                    'type' => 'ul',
151                );
152            }
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            $s = preg_replace('!^\s*--.*$!m', '', $s);
172            $s = trim($s);
173            if (!$s) continue;
174
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        ];
266
267        // import form
268        $form = new Form(['action' => $this->selfLink(), 'enctype' => 'multipart/form-data', 'method' => 'post']);
269        $form->addElement(new InputElement('file', 'importfile'));
270        $form->addButton('cmd[import]', $this->getLang('import'));
271
272        // output as a list
273        echo '<ul>';
274        foreach ($commands as $label => $command) {
275            echo '<li><div class="li">';
276            echo '<a href="' . $this->selfLink(false, $command) . '">' . $this->getLang($label) . '</a>';
277            echo '</div></li>';
278        }
279        echo '<li><div class="li">';
280        echo $form->toHTML();
281        echo '</div></li>';
282        echo '</ul>';
283    }
284
285    /**
286     * FIXME needs to be cleaned up
287     */
288    public function showSavedQueries()
289    {
290        $queries = $this->querySaver->getQueries();
291        if (!$queries) return;
292
293        echo '<ul>';
294        foreach ($queries as $query) {
295            $link = $this->selfLink(false, ['sql' => $query['sql']]);
296            $del = $this->selfLink(false, ['cmd' => 'delete_query', 'name' => $query['name']]);
297
298            echo '<li><div class="li">';
299            echo '<a href="' . $link . '">' . hsc($query['name']) . '</a>';
300            echo ' [<a href="' . $del . '">' . $this->getLang('delete_query') . '</a>]';
301            echo '</div></li>';
302        }
303        echo '</ul>';
304    }
305}
306