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