protected final function render($template, $args = [])
{
$args['SERVER'] = $_SERVER;
if (Session::get('flash')) {
$args['flash'] = Session::get('flash');
Session::del('flash');
}
return new Render($template, $args);
}
/**
* @route POST /query
*/
public function query()
{
if (!Session::get('username')) {
return $this->redirect('/login');
}
$commander = new Commander(trim($_POST['command']));
/** @var \Lib\CommandResult $result */
$result = $commander->execute();
return $this->jsonify($result->getValue(), !$result->getIsSuccess() ? 406 : 200);
}
/**
* @route GET /
*/
public function index()
{
$user = Session::get('user');
$installed = json_decode(file_get_contents(ROOT . '/vendor/composer/installed.json'));
$installedModule = [];
foreach ($installed as $i) {
$installedModule[] = ['name' => $i->name, 'version' => $i->version];
}
return $this->render('main/index.twig', ['name' => $user['name'], 'installed_module' => $installedModule, 'app_root' => ROOT, 'web_root' => getcwd(), 'phpversion' => phpversion()]);
}
/**
* @route GET /logout
*/
public function logout()
{
Session::del('user');
return $this->redirect('/login');
}
public function execute()
{
$command = $this->_parseCommand(trim($_POST['command']));
$dbname = Session::get('dbname');
if (is_a($command, 'Lib\\PostgresqlCommand') && $command->command !== 'c' && !$dbname) {
return new CommandResult(false, ['msg' => 'There is no selected database.']);
}
if ($dbname) {
Query::config(['schema' => 'pgsql', 'host' => Session::get('host'), 'user' => Session::get('username'), 'pass' => Session::get('password'), 'dbname' => $dbname]);
}
/** normal query */
if (!is_a($command, 'Lib\\PostgresqlCommand')) {
try {
$result = Query::query($command->query);
if (count($result) === 0) {
return new CommandResult(true, ['table' => null]);
}
$keys = array_keys($result[0]);
$list = [];
foreach ($result as $v) {
foreach ($v as $k2 => $v2) {
if (is_resource($v2)) {
$v[$k2] = '<resource type>';
}
$v[$k2] = htmlspecialchars($v[$k2]);
}
$list[] = array_values($v);
}
return new CommandResult(true, ['table' => ['keys' => $keys, 'values' => $list]]);
} catch (DBException $e) {
header('Content-Type: application/json');
header($_SERVER["SERVER_PROTOCOL"] . " 406 Not Acceptable");
return new CommandResult(false, ['msg' => $e->getMessage()]);
}
}
/** backslash command */
switch ($command->command) {
case 'c':
Query::config(['schema' => 'pgsql', 'host' => Session::get('host'), 'user' => Session::get('username'), 'pass' => Session::get('password'), 'dbname' => $command->arguments]);
$pd = new PgDatabase();
try {
$databases = Query::get([$pd])->select()->all();
} catch (\Exception $e) {
return new CommandResult(false, ['msg' => 'FATAL: database "' . $command->arguments . "\" does not exist\nPrevious connection kept"], 406);
}
Session::set('dbname', $command->arguments);
$dblist = [];
foreach ($databases as $v) {
$dblist[] = array_values($v);
}
return new CommandResult(true, ['msg' => 'You are now connected to database "' . $command->arguments . '" as user "postgres".', 'databases' => $dblist]);
case 'l':
$pd = new PgDatabase();
$list = [];
$databases = Query::get([$pd])->select()->all();
foreach ($databases as $v) {
$list[] = array_values($v);
}
return new CommandResult(true, ['table' => ['keys' => PgDatabase::getColumns(), 'values' => $list, 'total' => count($list)]]);
case 'd':
if ($command->arguments) {
$pc = new PgClass();
$pa = new PgAttribute();
$pad = new PgAttrdef();
$pct = new PgConstraint();
$relations = Query::get([$pa, [$pc, [Query::eq($pc->oid, $pa->attrelid)]], [new PgAttrdef(), [Query::eq($pad->adrelid, $pc->oid), Query::eq($pad->adnum, $pa->attnum)], 'LEFT JOIN']])->select([$pa->attnum->alias('number'), $pa->attname->alias('name'), $pa->attnotnull->alias('notnull'), $pa->atttypid->func('format_type', [$pa->atttypmod])->alias('Type')])->where([Query::gt($pa->attnum, 0), Query::eq($pc->relname, $command->arguments), Query::eq($pc->relkind, 'r')])->table();
if (count($relations) === 0) {
return new CommandResult(false, ['msg' => 'Did not find any relation named "' . $command->arguments . '".']);
}
return new CommandResult(true, ['table' => $relations]);
}
if ($command->arguments) {
$keys = ['number', 'name', 'notnull', 'type', 'primarykey', 'uniquekey', 'default', 'foreignkey', 'foreignkey_fieldnum', 'foreignkey_connnum'];
$relations = Query::query("SELECT\n f.attnum AS number,\n f.attname AS name,\n f.attnotnull AS notnull,\n pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,\n CASE\n WHEN p.contype = 'p' THEN 't'\n ELSE 'f'\n END AS primarykey,\n CASE\n WHEN p.contype = 'u' THEN 't'\n ELSE 'f'\n END AS uniquekey,\n CASE\n WHEN f.atthasdef = 't' THEN d.adsrc\n ELSE ''\n END AS default_value,\n CASE\n WHEN p.contype = 'f' THEN g.relname\n END AS foreignkey,\n CASE\n WHEN p.contype = 'f' THEN p.confkey\n END AS foreignkey_fieldnum,\n CASE\n WHEN p.contype = 'f' THEN p.conkey\n END AS foreignkey_connnum\nFROM pg_attribute f\n JOIN pg_class c ON c.oid = f.attrelid\n JOIN pg_type t ON t.oid = f.atttypid\n LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum\n LEFT JOIN pg_namespace n ON n.oid = c.relnamespace\n LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)\n LEFT JOIN pg_class AS g ON p.confrelid = g.oid\nWHERE c.relkind = 'r'::char\n AND n.nspname = 'public'\n AND c.relname = \$1\n AND f.attnum > 0 ORDER BY number\n;", [$command->arguments]);
} else {
$pc = new PgClass();
$pn = new PgNamespace();
$relations = Query::get([$pc, [$pn, [Query::eq($pn->oid, $pc->relnamespace)]]])->select([$pn->nspname->alias('Schema'), $pc->relname->alias('Name'), $pc->relkind->condition([['r', 'table'], ['v', 'view'], ['m', 'materialized view'], ['i', 'index'], ['S', 'sequence'], ['s', 'special'], ['f', 'foreign table']])->alias('Type'), $pc->relowner->func('pg_get_userbyid')->alias('Owner')])->where([Query::in($pc->relkind, ['r', 'v', 'm', 'S', 'f', '']), Query::neq($pn->nspname, 'pg_catalog'), Query::neq($pn->nspname, 'information_schema'), Query::regexpneq($pn->nspname, '^pg_toast')])->order_by([1, 2])->table();
return new CommandResult(true, ['table' => $relations]);
}
default:
return new CommandResult(true, ['msg' => 'no such command']);
}
}