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