/**
* Update database records
* @param string|integer $id
* @param array|object $keysValues
* @param string|array $table Table's name
* @param string $idAttribute **[Optional]** Id attribute
* @return integer Return number of affected records
* @param null|integer $limit **[Optional]**
* Limit clause, when null there is not limit.
* @todo Add $additionalAttributes
*/
public static function update($id, $keysValues, $table, $idAttribute = 'id', $limit = 1)
{
//Work with arrays
if (is_object($keysValues)) {
$keysValues = (array) $keysValues;
}
$queryKeys = implode('" = ?,"', array_keys($keysValues));
$queryValues = array_values($keysValues);
//Push id to the end
$queryValues[] = $id;
$tableName = '';
//Work with array
if (is_object($table)) {
$table = (array) $table;
}
if (is_array($table) && isset($table['schema']) && isset($table['table'])) {
$tableName = sprintf('"%s"."%s"', $table['schema'], $table['table']);
} else {
$tableName = sprintf('"%s"', $table);
}
$query = sprintf('UPDATE %s SET "%s" = ?
WHERE "%s" = ?
%s', $tableName, $queryKeys, $idAttribute, $limit === null ? '' : '');
//Return number of rows affected
$result = Database::execute($query, $queryValues);
return $result;
}
/**
* @covers Phramework\QueryLog\QueryLog::register
*/
public function testRegister2()
{
try {
\Phramework\Database\Database::execute('SELECT * FROM "boomboom"');
} catch (\Exception $e) {
}
\Phramework\Database\Database::execute('SELECT * FROM user');
\Phramework\Database\Database::execute('SELECT * FROM user WHERE "user"."id" = ? LIMIT 1', [1]);
}
/**
* Delete database records
* @param string|integer $id
* Id value
* @param array|object $additionalAttributes
* Additional attributes to use in WHERE $idAttribute
* @param string|array $table Table's name
* @param string $idAttribute **[Optional]**
* Id attribute
* @param null|integer $limit **[Optional]**
* Limit clause, when null there is not limit.
* @return integer Return number of affected records
*/
public static function delete($id, $additionalAttributes, $table, $idAttribute = 'id', $limit = 1)
{
//force disable limit because they wont work with postgreqsql
$limit = null;
$queryValues = [$id];
$additional = [];
foreach ($additionalAttributes as $key => $value) {
//push to additional
$additional[] = sprintf('AND "%s"."%s" = ?', $table, $key);
$queryValues[] = $value;
}
$tableName = '';
if (is_array($table) && isset($table['schema']) && isset($table['table'])) {
$tableName = '"' . $table['schema'] . '"' . '."' . $table['table'] . '"';
} else {
$tableName = '"' . $table . '"';
}
$query = sprintf('DELETE FROM %s
WHERE "%s" = ?
%s
%s', $tableName, $idAttribute, implode("\n", $additional), $limit === null ? '' : 'LIMIT ' . $limit);
return Database::execute($query, $queryValues);
}
/**
* Create a new record in database
* @param array|object $attributes Key-value array or object with records's attributes
* @param string $table Table's name
* @param string|null $schema *[Optional]* Table's schema, default is null for no schema
* @param integer $return Return method type
* - if **`RETURN_ID`** will return the id of last inserted record
* - if **`RETURN_RECORDS`** will return the inserted record
* - if **`RETURN_NUMBER_OF_RECORDS`** will return the number of records affected
* @return integer|array
* @throws ServerException
* @todo Check RETURNING id for another primary key attribute
*/
public static function create($attributes, $table, $schema = null, $return = self::RETURN_ID)
{
if (is_object($attributes)) {
$attributes = (array) $attributes;
}
$driver = Database::getAdapterName();
//prepare query
$query_keys = implode('" , "', array_keys($attributes));
$query_parameter_string = trim(str_repeat('?,', count($attributes)), ',');
$query_values = array_values($attributes);
if ($driver == 'postgresql') {
//Make sure boolean are strings
foreach ($query_values as &$queryValue) {
if (is_bool($queryValue)) {
$queryValue = $queryValue ? 'true' : 'false';
}
}
}
$query = 'INSERT INTO ';
if ($schema !== null) {
$query .= sprintf('"%s"."%s"', $schema, $table);
} else {
$query .= sprintf('"%s"', $table);
}
$query .= sprintf(' ("%s") VALUES (%s)', $query_keys, $query_parameter_string);
if ($return == self::RETURN_ID) {
//Return inserted id
if ($driver == 'postgresql') {
$query .= ' RETURNING id';
$id = Database::executeAndFetch($query, $query_values);
return $id['id'];
}
return Database::executeLastInsertId($query, $query_values);
} elseif ($return == self::RETURN_RECORDS) {
//Return records
if ($driver != 'postgresql') {
throw new ServerExcetion('RETURN_RECORDS works only with postgresql adapter');
}
$query .= 'RETURNING *';
return Database::executeAndFetch($query, $query_values);
} else {
//Return number of records affected
return Database::execute($query, $query_values);
}
}
public static function post($username)
{
return Database::execute('INSERT INTO "user"
("username") VALUES (?)', [$username]);
}