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