public function upsert($data, $columns = [])
{
if ($columns) {
if (!in_array($this->primaryKey, $columns)) {
throw new \RuntimeException("cannot upset without a primary key in the dataset");
}
} else {
$columns = array_keys($this->columns);
}
foreach ($data as $row) {
if (count($row) != count($columns)) {
throw new \RuntimeException("column count does not match row count");
}
$columnString = implode("`,`", $columns);
$quotedValues = [];
foreach ($row as $val) {
$quotedValues[] = $this->connection->quote($val);
}
$valueString = implode(",", $quotedValues);
for ($c = 0; $c < count($quotedValues); $c++) {
$key = $columns[$c];
if ($key != $this->primaryKey) {
$val = $quotedValues[$c];
$updateStringParts[] = "{$key}={$val}";
}
}
$updateString = implode(", ", $updateStringParts);
$sql = "INSERT INTO `{$this->name}` (`{$columnString}`) VALUES ({$valueString})";
$sql .= " ON DUPLICATE KEY UPDATE {$updateString}";
$this->connection->getPdo()->query($sql);
}
}
/**
* @param array $values
*
* @return array
*/
protected function quoteArray(array $values)
{
$result = [];
foreach ($values as $k => $v) {
$result[$k] = $this->_connection->quote($v);
}
return $result;
}
private function formatValue($value)
{
if (is_string($value)) {
if (strlen($value) > 20) {
$this->remaining[] = $value;
return '?';
} else {
return $this->connection->quote($value);
}
} elseif (is_int($value)) {
return (string) $value;
} elseif (is_float($value)) {
return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
} elseif (is_bool($value)) {
return $this->driver->formatBool($value);
} elseif ($value === NULL) {
return 'NULL';
} elseif ($value instanceof TableRow) {
return $value->getPrimary();
} elseif (is_array($value) || $value instanceof Traversable) {
$vx = $kx = array();
if (isset($value[0])) {
// non-associative; value, value, value
foreach ($value as $v) {
$vx[] = $this->formatValue($v);
}
return implode(', ', $vx);
} elseif ($this->arrayMode === 'values') {
// (key, key, ...) VALUES (value, value, ...)
$this->arrayMode = 'multi';
foreach ($value as $k => $v) {
$kx[] = $this->driver->delimite($k);
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
} elseif ($this->arrayMode === 'assoc') {
// key=value, key=value, ...
foreach ($value as $k => $v) {
$vx[] = $this->driver->delimite($k) . '=' . $this->formatValue($v);
}
return implode(', ', $vx);
} elseif ($this->arrayMode === 'multi') {
// multiple insert (value, value, ...), ...
foreach ($value as $k => $v) {
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $vx) . ')';
}
} elseif ($value instanceof DateTime) {
return $this->driver->formatDateTime($value);
} elseif ($value instanceof SqlLiteral) {
return $value->__toString();
} else {
$this->remaining[] = $value;
return '?';
}
}
/**
* Get values
*
* @return string
*/
public function getSet()
{
$format = function ($value, $field) {
return $this->connection->quoteIdentifier($field) . '=' . $this->connection->quote($value);
};
return implode(',', array_map($format, $this->data, array_keys($this->data)));
}
function login()
{
$authorized = false;
$error = array();
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if (strlen($_POST['userid']) > 0) {
$validation = new Validation();
if ($message = $validation->userid($_POST['userid'], 'ユーザー名')) {
$error[] = $message;
} else {
$userid = $_POST['userid'];
}
$_POST['password'] = trim($_POST['password']);
if ($message = $validation->alphaNumeric($_POST['password'], 'パスワード')) {
$error[] = $message;
} else {
$password = md5($_POST['password']);
}
if (count($error) <= 0) {
$connection = new Connection();
$query = sprintf("SELECT id,userid,password,realname,user_group,authority FROM %suser WHERE userid = '%s'", DB_PREFIX, $connection->quote($userid));
$data = $connection->fetchOne($query);
$connection->close();
if (count($data) > 0 && $data['userid'] === $userid && $data['password'] === $password) {
$authorized = true;
} else {
$error[] = 'ユーザー名もしくはパスワードが<br />異なります。';
}
}
} else {
$error[] = 'ユーザー名を入力してください。';
}
} elseif (isset($_SESSION['status'])) {
if ($_SESSION['status'] == 'idle') {
$error[] = '自動的にログアウトしました。<br />ログインしなおしてください。';
} elseif ($_SESSION['status'] == 'expire') {
$error[] = 'ログインの有効期限が切れました。<br />ログインしなおしてください。';
}
session_unregister('status');
}
if ($authorized === true && count($error) <= 0) {
session_regenerate_id();
$_SESSION['logintime'] = time();
$_SESSION['accesstime'] = $_SESSION['logintime'];
$_SESSION['authorized'] = md5(__FILE__ . $_SESSION['logintime']);
$_SESSION['userid'] = $data['userid'];
$_SESSION['realname'] = $data['realname'];
$_SESSION['group'] = $data['user_group'];
$_SESSION['authority'] = $data['authority'];
if (isset($_SESSION['referer'])) {
header('Location: ' . $_SESSION['referer']);
session_unregister('referer');
} else {
header('Location: index.php');
}
exit;
} else {
return $error;
}
}
/**
* @param array $values
*
* @return array
*/
protected function quoteArray(array $values)
{
$result = [];
// check first key
if (is_numeric(array_keys($values)[0])) {
foreach ($values as $k => $v) {
$result[$k] = $this->_connection->quote($v);
}
return ['?' => '(' . implode(',', $result) . ')'];
} else {
foreach ($values as $k => $v) {
$result[$k] = $this->_connection->quote($v);
}
}
return $result;
}
/**
* @return string
*/
public function prepare()
{
$array = [];
foreach ($this->_data as $key => $value) {
$array[] = $key . '=' . $this->_connection->quote($value);
}
$where = empty($this->_where) ? '' : ' WHERE ' . $this->_where->prepare();
return 'UPDATE ' . $this->_tableName . ' SET ' . implode(', ', $array) . $where;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
/* Not for multi-column foreign keys */
$keys = array();
foreach ($this->connection->query("\n\t\t\tSELECT\n\t\t\t\ttc.constraint_name AS name,\n\t\t\t\tkcu.column_name AS local,\n\t\t\t\tccu.table_name AS table,\n\t\t\t\tccu.column_name AS foreign\n\t\t\tFROM\n\t\t\t\tinformation_schema.table_constraints AS tc\n\t\t\t\tJOIN information_schema.key_column_usage AS kcu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\t\tJOIN information_schema.constraint_column_usage AS ccu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\tWHERE\n\t\t\t\tconstraint_type = 'FOREIGN KEY'\n\t\t\t\tAND\n\t\t\t\ttc.table_name = {$this->connection->quote($table)}\n\t\t\tORDER BY\n\t\t\t\tkcu.ordinal_position\n\t\t") as $row) {
$keys[] = (array) $row;
}
return $keys;
}
/**
* @return string
*/
public function prepare()
{
$keys = [];
$values = [];
foreach ($this->_data as $key => $value) {
$keys[] = $key;
$values[] = $this->_connection->quote($value);
}
$delay = $this->_delay ? ' DELAY ' : '';
$ignore = $this->_ignoreOnDuplicate ? ' IGNORE ' : '';
return 'INSERT ' . $delay . $ignore . ' INTO ' . $this->_intoTable . '(' . implode(', ', $keys) . ') VALUES (' . implode(', ', $values) . ')';
}
/**
* Returns metadata for all columns in a table.
*/
public function getColumns($table)
{
$meta = $this->connection->query("\n\t\t\tSELECT sql FROM sqlite_master WHERE type = 'table' AND name = {$this->connection->quote($table)}\n\t\t\tUNION ALL\n\t\t\tSELECT sql FROM sqlite_temp_master WHERE type = 'table' AND name = {$this->connection->quote($table)}\n\t\t")->fetch();
$columns = array();
foreach ($this->connection->query("PRAGMA table_info({$this->delimite($table)})") as $row) {
$column = $row['name'];
$pattern = "/(\"{$column}\"|\\[{$column}\\]|{$column})\\s+[^,]+\\s+PRIMARY\\s+KEY\\s+AUTOINCREMENT/Ui";
$type = explode('(', $row['type']);
$columns[] = array('name' => $column, 'table' => $table, 'fullname' => "{$table}.{$column}", 'nativetype' => strtoupper($type[0]), 'size' => isset($type[1]) ? (int) $type[1] : NULL, 'nullable' => $row['notnull'] == '0', 'default' => $row['dflt_value'], 'autoincrement' => (bool) preg_match($pattern, $meta['sql']), 'primary' => $row['pk'] == '1', 'vendor' => (array) $row);
}
return $columns;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
$keys = array();
$query = 'SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE ' . 'WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = ' . $this->connection->quote($table);
foreach ($this->connection->query($query) as $id => $row) {
$keys[$id]['name'] = $row['CONSTRAINT_NAME'];
// foreign key name
$keys[$id]['local'] = $row['COLUMN_NAME'];
// local columns
$keys[$id]['table'] = $row['REFERENCED_TABLE_NAME'];
// referenced table
$keys[$id]['foreign'] = $row['REFERENCED_COLUMN_NAME'];
// referenced columns
}
return array_values($keys);
}
}
if ($count > 0) {
$result[] = '管理者権限を持ったユーザーがすでに存在します。<br />新しい管理者は作成できません。';
} else {
if (strlen($_POST['userid']) <= 0) {
$error[] = 'ユーザーIDを入力してください。';
} else {
if ($string = Validation::userid('userid', 'ユーザーID')) {
$error[] = $string;
}
if ($string = Validation::length('userid', 'ユーザーID', 100)) {
$error[] = $string;
}
}
if (count($error) <= 0 && is_array($table) && in_array(DB_PREFIX . 'user', $table)) {
$count = $connection->fetchCount(DB_PREFIX . 'user', "WHERE userid = '" . $connection->quote($_POST['userid']) . "'", 'id');
if ($count > 0) {
$error[] = 'そのユーザーIDはすでに存在します。<br />別のユーザーIDを入力してください。';
}
}
$_POST['password'] = trim($_POST['password']);
if (strlen($_POST['password']) <= 0) {
$error[] = 'パスワードを入力してください。';
} else {
if ($string = Validation::alphaNumeric('password', 'パスワード')) {
$error[] = $string;
}
if ($string = Validation::length('password', 'パスワード', 4, 32)) {
$error[] = $string;
}
}
