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