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