*/ class admin_plugin_sqlite extends AdminPlugin { /** @var SQLiteDB */ protected $db; /** @var QuerySaver */ protected $querySaver; /** @inheritdoc */ public function getMenuSort() { return 500; } /** @inheritdoc */ public function forAdminOnly() { return true; } /** @inheritdoc */ public function handle() { global $conf; global $INPUT; // load database if given and security token is valid if ($INPUT->str('db') && checkSecurityToken()) { try { $this->db = new SQLiteDB($INPUT->str('db'), ''); $this->querySaver = new QuerySaver($this->db->getDBName()); } catch (Exception $e) { msg($e->getMessage(), -1); return; } } $cmd = $INPUT->extract('cmd')->str('cmd'); switch ($cmd) { case 'export': $exportfile = $conf['tmpdir'] . '/' . $this->db->getDbName() . '.sql'; $this->db->dumpToFile($exportfile); header('Content-Type: text/sql'); header('Content-Disposition: attachment; filename="' . $this->db->getDbName() . '.sql";'); readfile($exportfile); unlink($exportfile); exit(0); case 'import': $importfile = $_FILES['importfile']['tmp_name']; if (empty($importfile)) { msg($this->getLang('import_no_file'), -1); return; } $sql = Tools::SQLstring2array(file_get_contents($importfile)); try { $this->db->getPdo()->beginTransaction(); foreach ($sql as $s) { $this->db->exec($s); } $this->db->getPdo()->commit(); msg($this->getLang('import_success'), 1); } catch (Exception $e) { $this->db->getPdo()->rollBack(); msg(hsc($e->getMessage()), -1); } break; case 'save_query': $this->querySaver->saveQuery($INPUT->str('name'), $INPUT->str('sql')); break; case 'delete_query': $this->querySaver->deleteQuery($INPUT->str('name')); break; case 'download': $file = $this->db->getDbFile(); header('Content-Type: application/vnd.sqlite3'); header( 'Content-Disposition: attachment; filename="' . $this->db->getDbName() . SQLiteDB::FILE_EXTENSION . '"' ); readfile($file); exit(0); } } /** @inheritdoc */ public function html() { global $INPUT; echo '
'; echo $this->locale_xhtml('intro'); if ($this->db) { echo '

' . $this->getLang('db') . ' "' . hsc($this->db->getDbName()) . '"

'; echo '
'; $this->showCommands(); $this->showSavedQueries(); echo '
'; // query form $form = new Form(['action' => $this->selfLink()]); $form->addClass('sqliteplugin'); $form->addFieldsetOpen('SQL Command'); $form->addTextarea('sql')->addClass('edit'); $form->addButton('submit', $this->getLang('btn_execute'))->attr('type', 'submit'); $form->addTextInput('name', $this->getLang('query_name')); $form->addButton('cmd[save_query]', $this->getLang('save_query'))->attr('type', 'submit'); $form->addFieldsetClose(); echo $form->toHTML(); // results if ($INPUT->has('sql')) $this->showQueryResults($INPUT->str('sql')); } echo '
'; } /** * List all available databases in the TOC * * @inheritdoc */ public function getTOC() { global $conf; global $ID; $toc = []; $toc[] = [ 'link' => wl($ID, ['do' => 'admin', 'page' => 'sqlite']), 'title' => $this->getLang('db') . ':', 'level' => 1, 'type' => 'ul', ]; $dbfiles = glob($conf['metadir'] . '/*.sqlite3'); if (is_array($dbfiles)) foreach ($dbfiles as $file) { $db = basename($file, '.sqlite3'); $toc[] = [ 'link' => wl($ID, ['do' => 'admin', 'page' => 'sqlite', 'db' => $db, 'sectok' => getSecurityToken()]), 'title' => $db, 'level' => 2, 'type' => 'ul', ]; } return $toc; } /** * Execute and display the results of the given SQL query * * multiple queries can be given separated by semicolons * * @param string $sql */ protected function showQueryResults($sql) { echo '

Query results

'; $sql = Tools::SQLstring2array($sql); foreach ($sql as $s) { try { $time_start = microtime(true); $result = $this->db->queryAll($s); $time_end = microtime(true); } catch (Exception $e) { msg($e->getMessage(), -1); continue; } $time = $time_end - $time_start; $cnt = count($result); msg($cnt . ' affected rows in ' . $this->microtimeToSeconds($time) . ' seconds', 1); if (!$cnt) continue; echo '
'; $ths = array_keys($result[0]); echo ''; echo ''; foreach ($ths as $th) { echo ''; } echo ''; foreach ($result as $row) { echo ''; $tds = array_values($row); foreach ($tds as $td) { if ($td === null) $td = '␀'; echo ''; } echo ''; } echo '
' . hsc($th) . '
' . hsc($td) . '
'; echo '
'; } } /** * Convert a microtime() value to a string in seconds * * @param float $time * @return string */ protected function microtimeToSeconds($time) { return ($time < 0.0001 ? substr($time, 0, 5) . substr($time, -3) : substr($time, 0, 7)); } /** * Construct a link to the sqlite admin page with the given additional parameters * * Basically a wrapper around wl() with some defaults * * @param string[] $params * @param bool $form for use in form action? * @return string */ protected function selfLink($form = true, $params = []) { global $ID; $params = array_merge( [ 'do' => 'admin', 'page' => 'sqlite', 'db' => $this->db ? $this->db->getDBName() : '', 'sectok' => getSecurityToken(), ], $params ); return wl($ID, $params, false, $form ? '&' : '&'); } /** * Display the standard actions for a database */ protected function showCommands() { $commands = [ 'dbversion' => [ 'sql' => 'SELECT val FROM opts WHERE opt=\'dbversion\'', ], 'table' => [ 'sql' => 'SELECT name,sql FROM sqlite_master WHERE type=\'table\' ORDER BY name', ], 'index' => [ 'sql' => 'SELECT name,sql FROM sqlite_master WHERE type=\'index\' ORDER BY name', ], 'export' => [ 'cmd' => 'export' ], 'download' => [ 'cmd' => 'download' ], ]; // import form $form = new Form(['action' => $this->selfLink(), 'enctype' => 'multipart/form-data', 'method' => 'post']); $form->addElement( (new InputElement('file', 'importfile')) ->attr('required', 'required') ->attr('accept', '.sql') ); $form->addButton('cmd[import]', $this->getLang('import')); // output as a list echo ''; } /** * Display the saved queries for this database */ public function showSavedQueries() { $queries = $this->querySaver->getQueries(); if (!$queries) return; echo ''; } }