xref: /plugin/sqlite/admin.php (revision fe7a46b52dcd76370dc0858ec5c6760bee9b1d38) 
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;
11
12// must be run within Dokuwiki
13if(!defined('DOKU_INC')) die();
14
15class admin_plugin_sqlite extends DokuWiki_Admin_Plugin {
16
17    function getMenuSort() {
18        return 500;
19    }
20
21    function forAdminOnly() {
22        return true;
23    }
24
25    function handle() {
26        global $conf;
27        global $INPUT;
28
29        if($INPUT->bool('sqlite_rename') && checkSecurityToken()) {
30
31            $path = $conf['metadir'].'/'.$INPUT->str('db');
32            if(io_rename($path.'.sqlite', $path.'.sqlite3')) {
33                msg('Renamed database file succesfull!', 1);
34                //set to new situation
35                $INPUT->set('version', 'sqlite3');
36
37            } else {
38                msg('Renaming database file fails!', -1);
39            }
40        } elseif($INPUT->bool('sqlite_export') && checkSecurityToken()) {
41
42            /** @var $DBI helper_plugin_sqlite */
43            $DBI        = plugin_load('helper', 'sqlite');
44            $dbname = $INPUT->str('db');
45
46            $dumpfile = $DBI->dumpDatabase($dbname, DOKU_EXT_PDO, true);
47            if ($dumpfile) {
48                header('Content-Type: text/sql');
49                header('Content-Disposition: attachment; filename="'.$dbname.'.sql";');
50
51                readfile($dumpfile);
52                exit(0);
53            }
54        } elseif($INPUT->bool('sqlite_import') && checkSecurityToken()) {
55
56            /** @var $DBI helper_plugin_sqlite */
57            $DBI        = plugin_load('helper', 'sqlite');
58            $dbname = $INPUT->str('db');
59            $dumpfile = $_FILES['dumpfile']['tmp_name'];
60
61            if (empty($dumpfile)) {
62                msg($this->getLang('import_no_file'), -1);
63                return;
64            }
65
66            if ($DBI->fillDatabaseFromDump($dbname, $dumpfile, true)) {
67                msg($this->getLang('import_success'), 1);
68            }
69        } elseif($INPUT->bool('sqlite_query_save') && checkSecurityToken()) {
70            if($INPUT->str('sql') === '') {
71                msg($this->getLang('validation query_required'), -1);
72                return;
73            }
74            if($INPUT->str('name') === '') {
75                msg($this->getLang('validation query_name_required'), -1);
76                return;
77            }
78            /** @var helper_plugin_sqlite_db $db_helper */
79            $db_helper = $this->loadHelper('sqlite_db');
80            $sqlite_db = $db_helper->getDB();
81            $ok = $sqlite_db->storeEntry('queries', array(
82                'db' => $INPUT->str('db'),
83                'name' => $INPUT->str('name'),
84                'sql' => $INPUT->str('sql')
85            ));
86            if ($ok) {
87                msg($this->getLang('success query_saved'), 1);
88            }
89        } elseif($INPUT->has('sqlite_query_delete') && checkSecurityToken()) {
90            /** @var helper_plugin_sqlite_db $db_helper */
91            $db_helper = $this->loadHelper('sqlite_db');
92            $sqlite_db = $db_helper->getDB();
93
94            $ok = $sqlite_db->query("DELETE FROM queries WHERE id=?;", $INPUT->int('sqlite_query_delete'));
95            if ($ok) {
96                msg($this->getLang('success query_deleted'), 1);
97            }
98        }
99    }
100
101    function html() {
102        global $ID;
103        global $conf;
104        global $INPUT;
105
106        echo $this->locale_xhtml('intro');
107
108        if($INPUT->has('db') && checkSecurityToken()) {
109
110            echo '<h2>'.$this->getLang('db').' "'.hsc($INPUT->str('db')).'"</h2>';
111            echo '<div class="level2">';
112
113            $sqlcommandform = true;
114            /** @var $DBI helper_plugin_sqlite */
115            $DBI = plugin_load('helper', 'sqlite');
116            if($INPUT->str('version') == 'sqlite2') {
117                if(helper_plugin_sqlite_adapter::isSqlite3db($conf['metadir'].'/'.$INPUT->str('db').'.sqlite')) {
118
119                    msg('This is a database in sqlite3 format.', 2);
120                    msg(
121                        'This plugin needs your database file has the extension ".sqlite3"
122                        instead of ".sqlite" before it will be recognized as sqlite3 database.', 2
123                    );
124                    $action = wl($ID, [
125                            'do'     => 'admin',
126                            'page'   => 'sqlite',
127                            'db'     => $INPUT->str('db'),
128                            'version'=> $INPUT->str('version')
129                        ], false, '&');
130                    $form = new Form(['action' => $action]);
131                    $form->addButton('sqlite_rename', sprintf($this->getLang('rename2to3'), hsc($INPUT->str('db'))))
132                        ->attr('type', 'submit');
133                    print $form->toHTML();
134
135                    if($DBI->existsPDOSqlite()) $sqlcommandform = false;
136
137                } else {
138                    msg(
139                        'Before PDO sqlite can handle this format, it needs a conversion to the sqlite3 format.
140                        Because PHP sqlite extension is no longer supported,
141                        you should manually convert "'.hsc($INPUT->str('db')).'.sqlite" in the meta directory to "'.hsc($INPUT->str('db')).'.sqlite3".<br />
142                        See for info about the conversion '.$this->external_link('http://www.sqlite.org/version3.html').'.', -1
143                    );
144                    $sqlcommandform = false;
145                }
146            } else {
147                if(!$DBI->existsPDOSqlite()) {
148                    $sqlcommandform = false;
149                    msg('A database in sqlite3 format needs the PHP PDO sqlite plugin.', -1);
150                }
151            }
152
153            if($sqlcommandform) {
154                echo '<ul>';
155                echo '<li><div class="li"><a href="'.
156                    wl(
157                        $ID, array(
158                                  'do'     => 'admin',
159                                  'page'   => 'sqlite',
160                                  'db'     => $INPUT->str('db'),
161                                  'version'=> $INPUT->str('version'),
162                                  'sql'    => 'SELECT name,sql FROM sqlite_master WHERE type=\'table\' ORDER BY name',
163                                  'sectok' => getSecurityToken()
164                             )
165                    ).
166                    '">'.$this->getLang('table').'</a></div></li>';
167                echo '<li><div class="li"><a href="'.
168                    wl(
169                        $ID, array(
170                                  'do'     => 'admin',
171                                  'page'   => 'sqlite',
172                                  'db'     => $INPUT->str('db'),
173                                  'version'=> $INPUT->str('version'),
174                                  'sql'    => 'SELECT name,sql FROM sqlite_master WHERE type=\'index\' ORDER BY name',
175                                  'sectok' => getSecurityToken()
176                             )
177                    ).
178                    '">'.$this->getLang('index').'</a></div></li>';
179                echo '<li><div class="li"><a href="'.
180                    wl(
181                        $ID, array(
182                               'do'     => 'admin',
183                               'page'   => 'sqlite',
184                               'db'     => $INPUT->str('db'),
185                               'version'=> $INPUT->str('version'),
186                               'sqlite_export' => '1',
187                               'sectok' => getSecurityToken()
188                           )
189                    ).
190                    '">'.$this->getLang('export').'</a></div></li>';
191
192
193                $action = wl($ID, [
194                    'do'     => 'admin',
195                    'page'   => 'sqlite',
196                    'db'     => $INPUT->str('db'),
197                    'version'=> $INPUT->str('version')
198                ], false, '&');
199                $form = new Form(['action' => $action, 'enctype' => 'multipart/form-data']);
200                $form->addElement(new InputElement('file', 'dumpfile'));
201                $form->addButton('sqlite_import', $this->getLang('import'));
202                echo '<li>' . $form->toHTML() . '</li>';
203                echo '</ul>';
204
205                $action = wl($ID, [
206                    'do'     => 'admin',
207                    'page'   => 'sqlite',
208                    'db'     => $INPUT->str('db'),
209                    'version'=> $INPUT->str('version')
210                ], false, '&');
211                $form = (new Form(['action' => $action]))->addClass('sqliteplugin');
212                $form->addFieldsetOpen('SQL Command');
213                $form->addTextarea('sql')->addClass('edit')->val(hsc($INPUT->str('sql')));
214                $form->addElement(new InputElement('submit', ''));
215                $form->addTextInput('name', $this->getLang('query_name'));
216                $form->addButton('sqlite_query_save', $this->getLang('save_query'));
217                $form->addFieldsetClose();
218                print $form->toHTML();
219
220                // List saved queries
221                /** @var helper_plugin_sqlite_db $db_helper */
222                $db_helper = $this->loadHelper('sqlite_db');
223                $sqlite_db = $db_helper->getDB();
224                $res = $sqlite_db->query("SELECT id, name, sql FROM queries WHERE db=?", $INPUT->str('db'));
225                $result = $sqlite_db->res2arr($res);
226                if(count($result) > 0) {
227                    echo '<h3>' . $this->getLang('saved_queries') . '</h3>';
228                    echo '<div>';
229                    echo '<table class="inline">';
230                    echo '<tr>';
231                    echo '<th>name</th>';
232                    echo '<th>sql</th>';
233                    echo '<th></th>';
234                    echo '</tr>';
235                    foreach($result as $row) {
236                        echo '<tr>';
237                        echo '<td>'.hsc($row['name']).'</td>';
238                        $link = wl($ID, array(  'do'=> 'admin',
239                                                'page'=> 'sqlite',
240                                                'db'=> $INPUT->str('db'),
241                                                'version'=> $INPUT->str('version'),
242                                                'sql' => $row['sql'],
243                                                'sectok'=> getSecurityToken()));
244                        echo '<td><a href="'.$link.'">'.hsc($row['sql']).'</a></td>';
245
246                        $link = wl($ID, array(  'do'=> 'admin',
247                            'page'=> 'sqlite',
248                            'db'=> $INPUT->str('db'),
249                            'version'=> $INPUT->str('version'),
250                            'sqlite_query_delete' => $row['id'],
251                            'sectok'=> getSecurityToken()));
252                        echo '<td><a href="'.$link.'">delete</a></td>';
253                        echo '</tr>';
254                    }
255                    echo '</table>';
256                    echo '</div>';
257                }
258
259                if($INPUT->has('sql')) {
260                    if(!$DBI->init($INPUT->str('db'), '')) return;
261
262                    print '<h3>Query results</h3>';
263                    $sql = $DBI->SQLstring2array($INPUT->str('sql'));
264                    foreach($sql as $s) {
265                        $s = preg_replace('!^\s*--.*$!m', '', $s);
266                        $s = trim($s);
267                        if(!$s) continue;
268
269                        $time_start = microtime(true);
270
271                        $res = $DBI->query("$s;");
272                        if($res === false) continue;
273
274                        $result = $DBI->res2arr($res);
275
276                        $time_end = microtime(true);
277                        $time     = $time_end - $time_start;
278
279                        $cnt = $DBI->res2count($res);
280                        msg($cnt.' affected rows in '.($time < 0.0001 ? substr($time, 0, 5).substr($time, -3) : substr($time, 0, 7)).' seconds', 1);
281                        if(!$cnt) continue;
282
283                        echo '<div>';
284                        $ths = array_keys($result[0]);
285                        echo '<table class="inline">';
286                        echo '<tr>';
287                        foreach($ths as $th) {
288                            echo '<th>'.hsc($th).'</th>';
289                        }
290                        echo '</tr>';
291                        foreach($result as $row) {
292                            echo '<tr>';
293                            $tds = array_values($row);
294                            foreach($tds as $td) {
295                                if($td === null) $td='␀';
296                                echo '<td>'.hsc($td).'</td>';
297                            }
298                            echo '</tr>';
299                        }
300                        echo '</table>';
301                        echo '</div>';
302                    }
303                }
304
305            }
306            echo '</div>';
307        }
308    }
309
310    function getTOC() {
311        global $conf;
312        global $ID;
313
314        $toc            = array();
315        $fileextensions = array('sqlite2'=> '.sqlite', 'sqlite3'=> '.sqlite3');
316
317        foreach($fileextensions as $dbformat => $fileextension) {
318            $toc[] = array(
319                'link'  => wl($ID, array('do'=> 'admin', 'page'=> 'sqlite')),
320                'title' => $dbformat.':',
321                'level' => 1,
322                'type'  => 'ul',
323            );
324
325            $dbfiles = glob($conf['metadir'].'/*'.$fileextension);
326
327            if(is_array($dbfiles)) foreach($dbfiles as $file) {
328                $db    = basename($file, $fileextension);
329                $toc[] = array(
330                    'link'  => wl($ID, array('do'=> 'admin', 'page'=> 'sqlite', 'db'=> $db, 'version'=> $dbformat, 'sectok'=> getSecurityToken())),
331                    'title' => $this->getLang('db').' '.$db,
332                    'level' => 2,
333                    'type'  => 'ul',
334                );
335            }
336        }
337
338        return $toc;
339    }
340}
341
342// vim:ts=4:sw=4:et:
343