/**
* Output a sql page for database.
*
*/
function db_admin_sql()
{
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$sql = $_POST['sql'];
} else {
$sql = db_sql('table_list');
}
list($micro, $second) = explode(' ', microtime());
$time_start = $micro + $second;
$resource = db_query($sql);
list($micro, $second) = explode(' ', microtime());
$time_end = $micro + $second;
$_view['time'] = ceil(($time_end - $time_start) * 10000) / 10000;
$_view['sql'] = $sql;
if ($sql === db_sql('table_list')) {
$head = '';
$body = '';
$results = db_result($resource);
$head .= '<tr>';
$head .= '<th>name</th>';
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
$head .= '<th>engine</th>';
$head .= '<th>rows</th>';
$head .= '<th>collation</th>';
$head .= '<th>comment</th>';
}
$head .= '<th>create</th>';
$head .= '<th>columns</th>';
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
$head .= '<th>alter</th>';
}
$head .= '<th>drop</th>';
$head .= '<th>insert</th>';
$head .= '<th>delete</th>';
$head .= '<th>select</th>';
$head .= '</tr>';
foreach ($results as $result) {
$table = array_shift($result);
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
$create = 'SHOW CREATE TABLE';
$define = 'SHOW COLUMNS';
} elseif (DATABASE_TYPE === 'pdo_pgsql' || DATABASE_TYPE === 'pgsql') {
$create = 'create';
$define = 'columns';
} elseif (DATABASE_TYPE === 'pdo_sqlite' || DATABASE_TYPE === 'pdo_sqlite2' || DATABASE_TYPE === 'sqlite') {
$create = 'SELECT sql';
$define = 'PRAGMA TABLE_INFO';
}
$create_sql = db_sql('table_create', $table);
$define_sql = db_sql('table_define', $table);
$create_sql = preg_replace('/"/', '"', $create_sql);
$define_sql = preg_replace('/"/', '"', $define_sql);
$define_resource = db_query($define_sql);
$define_results = db_result($define_resource);
$insert_keys = array();
$insert_values = array();
foreach ($define_results as $define_result) {
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
$insert_keys[] = $define_result['Field'];
$insert_values[] = $define_result['Null'] === 'YES' ? 'NULL' : '\\\'\\\'';
} elseif (DATABASE_TYPE === 'pdo_pgsql' || DATABASE_TYPE === 'pgsql') {
$insert_keys[] = $define_result['column_name'];
$insert_values[] = $define_result['is_nullable'] === 'YES' ? 'NULL' : '\\\'\\\'';
} elseif (DATABASE_TYPE === 'pdo_sqlite' || DATABASE_TYPE === 'pdo_sqlite2' || DATABASE_TYPE === 'sqlite') {
$insert_keys[] = $define_result['name'];
$insert_values[] = $define_result['notnull'] === 0 ? 'NULL' : '\\\'\\\'';
}
}
$body .= '<tr>';
$body .= '<td><span style="font-family:monospace;">' . $table . '</span></td>';
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
$body .= '<td><span style="font-family:monospace;">' . $result['Engine'] . '</span></td>';
$body .= '<td><span style="font-family:monospace;">' . $result['Rows'] . '</span></td>';
$body .= '<td><span style="font-family:monospace;">' . $result['Collation'] . '</span></td>';
$body .= '<td><span style="font-family:monospace;">' . $result['Comment'] . '</span></td>';
}
$body .= '<td><a href="javascript:insertSQL(\'' . str_replace('\'', '\\\'', $create_sql) . '\');">' . $create . '</a></td>';
$body .= '<td><a href="javascript:insertSQL(\'' . str_replace('\'', '\\\'', $define_sql) . '\');">' . $define . '</a></td>';
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
$body .= '<td><a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' COMMENT \\\'\\\';\');">ALTER TABLE</a></td>';
}
$body .= '<td><a href="javascript:insertSQL(\'DROP TABLE ' . $table . ';\');">DROP TABLE</a></td>';
$body .= '<td><a href="javascript:insertSQL(\'INSERT INTO ' . $table . '(' . implode(',', $insert_keys) . ') VALUES(' . implode(',', $insert_values) . ');\');">INSERT</a></td>';
$body .= '<td><a href="javascript:insertSQL(\'DELETE FROM ' . $table . ';\');">DELETE</a></td>';
$body .= '<td><a href="javascript:insertSQL(\'SELECT * FROM ' . $table . ' LIMIT 100;\');">SELECT</a></td>';
$body .= '</tr>';
}
$_view['result'] = '<table summary="result">' . $head . $body . '</table>';
$_view['count'] = db_count($resource);
} elseif (regexp_match('^(SELECT|SHOW|EXPLAIN|DESC|PRAGMA)', $sql)) {
$head = '';
$body = '';
$flag = false;
if ($regexp = regexp_match('^SELECT \\* FROM ([_a-zA-Z0-9\\-]+)', $sql)) {
$table = $regexp[1];
$link = true;
} elseif ($regexp = regexp_match('^' . db_sql('table_define', '([_a-zA-Z0-9\\-]+)'), $sql)) {
$table = $regexp[1];
$link = false;
} else {
$table = null;
$link = false;
}
$results = db_result($resource);
foreach ($results as $result) {
$first_key = null;
$first_value = null;
$body .= '<tr>';
foreach ($result as $key => $value) {
if ($first_key === null) {
$first_key = $key;
$first_value = $value;
}
if (is_string($key)) {
if ($value === null) {
$value_sql = 'NULL';
$value_html = '<em>NULL</em>';
} else {
$value_sql = str_replace('\\', '\\\\\\\\', $value);
$value_sql = str_replace("\n", '\\n', $value_sql);
$value_sql = str_replace('"', '"', $value_sql);
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
$value_sql = str_replace('\'', '\\\\\\\'', $value_sql);
} else {
$value_sql = str_replace('\'', '\\\'\\\'', $value_sql);
}
$value_sql = '\\\'' . $value_sql . '\\\'';
$value_html = h($value, true);
}
if ($link === false) {
$value = $value_html;
} else {
$value = '<a href="javascript:insertSQL(\'UPDATE ' . $table . ' SET ' . $key . ' = ' . $value_sql . ' WHERE ' . $first_key . ' = \\\'' . $first_value . '\\\';\');">' . truncate($value_html, 100) . '</a>';
}
$body .= '<td><span style="font-family:monospace;">' . $value . '</span></td>';
if ($flag === false) {
$head .= '<th>' . h($key, true) . '</th>';
}
}
}
if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
if (regexp_match('^' . db_sql('table_define', '([_a-zA-Z0-9\\-]+)'), $sql)) {
$add_value = '<a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' ADD field INT(1) NOT NULL COMMENT \\\'\\\' AFTER ' . $result['Field'] . ';\');">ADD</a>';
$change_value = '<a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' CHANGE ' . $result['Field'] . ' ' . $result['Field'] . ' INT(1) NOT NULL COMMENT \\\'\\\';\');">CHANGE</a>';
$drop_value = '<a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' DROP ' . $result['Field'] . ';\');">DROP</a>';
$body .= '<td><span style="font-family:monospace;">' . $add_value . ' ' . $change_value . ' ' . $drop_value . '</span></td>';
if ($flag === false) {
$head .= '<th>alter</th>';
}
}
}
$body .= '</tr>';
$flag = true;
}
$_view['result'] = '<table summary="result"><tr>' . $head . '</tr>' . $body . '</table>';
$_view['count'] = db_count($resource);
} else {
$_view['result'] = '<p>OK</p>';
$_view['count'] = db_affected_count($resource);
}
echo "<!DOCTYPE html>\n";
echo "<html>\n";
echo "<head>\n";
echo "<meta charset=\"" . t(MAIN_CHARSET, true) . "\" />\n";
echo "<title>DB</title>\n";
style();
echo "<script>\n";
echo "function insertSQL(sql)\n";
echo "{\n";
echo " document.getElementById('exec_form').sql.value = sql;\n";
echo "}";
echo "</script>\n";
echo "</head>\n";
echo "<body>\n";
echo "<h1><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin\">DB</a></h1>\n";
echo "<h2>Menu</h2>\n";
echo "<ul>\n";
echo "<li>SQL</li>\n";
echo "<li><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin&_work=import\">Import</a></li>\n";
echo "<li><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin&_work=export\">Export</a></li>\n";
if (file_exists(DATABASE_BACKUP_PATH)) {
echo "<li><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin&_work=backup\">Backup</a></li>\n";
}
echo "</ul>\n";
echo "<h2>SQL</h2>\n";
echo "<form action=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin\" method=\"post\" id=\"exec_form\">\n";
echo "<fieldset>\n";
echo "<legend>execute</legend>\n";
echo "<dl>\n";
echo "<dt>SQL</dt>\n";
echo "<dd><textarea name=\"sql\" cols=\"50\" rows=\"5\">" . t($_view['sql'], true) . "</textarea></dd>\n";
echo "</dl>\n";
echo "<p><input type=\"submit\" value=\"execute\" /></p>\n";
echo "</fieldset>\n";
echo "</form>\n";
if ($_view['result']) {
echo "<h2>Result</h2>\n";
echo $_view['result'];
}
echo "<pre><code>Rows: " . $_view['count'] . " rows.\n";
echo "Time: " . $_view['time'] . " sec.</code></pre>\n";
echo "</body>\n";
echo "</html>\n";
return;
}
if (!$resource) {
error('指定されたユーザが見つかりません。');
}
// ユーザを取得
$users = select_users(array('select' => 'email', 'where' => array('id = :id', array('id' => $_SESSION['auth']['user']['id']))));
// メール送信内容を作成
$_view['url'] = $GLOBALS['config']['http_url'] . MAIN_FILE . '/user/activate?email=' . rawurlencode($users[0]['email']) . '&token=' . $token;
$to = $users[0]['email'];
$subject = $GLOBALS['config']['mail_subjects']['user/activate'];
$message = view('mail/user/activate.php', true);
$headers = $GLOBALS['config']['mail_headers'];
// メールを送信
if (service_mail_send($to, $subject, $message, $headers) === false) {
error('メールを送信できません。');
}
// トランザクションを終了
db_commit();
// リダイレクト
redirect('/user/home?ok=send');
} else {
// ユーザを編集
$resource = update_users(array('set' => array('email_activated' => 1, 'token' => null, 'token_code' => null, 'token_expire' => null), 'where' => array('email = :email AND token = :token', array('email' => $_GET['email'], 'token' => $_GET['token']))));
if (!$resource) {
error('データを編集できません。');
}
if (db_affected_count($resource) == 0) {
error('メールアドレスを確認できません。');
}
// リダイレクト
redirect('/user/home?ok=activate');
}