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