/**
* Execute a raw SQL query.
*
* @param string $sql The SQL to execute.
* @param array $params Parameters used in the SQL.
*/
public function query($sql, array $params = array())
{
$querytype = mb_strtoupper(mb_substr($sql, 0, 6));
// Ensure UTF-8.
foreach ($params as $k => $v) {
$params[$k] = \pdyn\datatype\Text::force_utf8($v);
}
// Prefix tables.
$sql = preg_replace_callback('#\\{(.+)\\}#msU', function ($matches) {
return $this->transform_tablename($matches[1]);
}, $sql);
// Logging.
$this->log($sql, $params);
if (!is_array($params)) {
throw new \Exception('Bad params argument in $DB->query', static::ERR_DB_BAD_REQUEST);
}
if (empty($this->link)) {
throw new \Exception('No database connection present.', static::ERR_DB_BAD_REQUEST);
}
if (empty($params)) {
$stmt = $this->link->query($sql);
} else {
$stmt = $this->link->prepare($sql, [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY]);
if (!empty($stmt)) {
$stmt->execute($params);
$errinfo = $stmt->errorInfo();
if ($errinfo[0] !== '00000') {
throw new \Exception($errinfo[2], static::ERR_DB_BAD_REQUEST);
}
if (in_array($querytype, ['INSERT', 'UPDATE', 'DELETE'], true)) {
$affected_rows = $stmt->rowCount();
}
}
}
$errinfo = $this->link->errorInfo();
if ($errinfo[0] !== '00000') {
throw new \Exception($errinfo[2], static::ERR_DB_BAD_REQUEST);
}
$this->numqueries++;
$this->laststmt = $stmt;
$lastid = $this->link->lastInsertId();
$ar = ['affected_rows' => isset($affected_rows) ? $affected_rows : -1, 'last_id' => $querytype === 'INSERT' && !empty($lastid) ? $lastid : 0];
return $ar;
}
/**
* Generate a unqiue text-identifier for a given table and column from any input string.
*
* Will generate a text identifier from the string (lowercase, alphanum-only), search for the value in the given table/column,
* and keep incrementing a suffixed counter until the value is unique.
*
* @param \pdyn\database\DbDriverInterface $DB An active database connection.
* @param string $input Any input text.
* @param string $table The table in which to ensure uniqueness.
* @param string $field The column in which to ensure uniqueness.
* @param array $restriction_list Array of values the text-identifier cannot match.
* @return string The generated, unique text identifier.
*/
public static function generate_slug(\pdyn\database\DbDriverInterface $DB, $input, $table, $field, array $restriction_list = array())
{
$i = 0;
while (true) {
$slug = \pdyn\datatype\Text::make_slug($input);
$slug .= $i != 0 ? '_' . $i : '';
// This is so we don't add the increment on the first loop.
if (!empty($restriction_list) && in_array($slug, $restriction_list, true)) {
$i++;
continue;
}
$found = $DB->get_record($table, [$field => $slug]);
if (empty($found)) {
break;
}
$i++;
}
return $slug;
}
/**
* Force a value to UTF-8, then serialize.
*
* This is used before storing serialized values in the database. Since our DbDrivers convert all strings to UTF-8,
* they can damage seralized data. For example, if non-utf8 text is contained in a serialized array, the offset recorded in the
* serialized string may be not reflect the length after conversion to utf8.
*
* @param mixed $input A value to force to UTF-8 then serialize.
* @return string A serialized UTF-8 value.
*/
public static function utf8safe_serialize($input)
{
if (is_array($input)) {
return serialize(\pdyn\datatype\Text::force_utf8_array($input));
} elseif (is_string($input)) {
return serialize(\pdyn\datatype\Text::force_utf8($input));
} else {
return serialize($input);
}
}
/**
* Tests make_slug function.
*
* @dataProvider dataprovider_makeSlug
*/
public function test_makeSlug($text, $expected)
{
$actual = \pdyn\datatype\Text::make_slug($text);
$this->assertEquals($expected, $actual);
}
/**
* Transform a value into a storable representation.
*
* @param mixed $val A value to transform.
* @param string $datatype The datatype of the value.
* @return string|int|float The transformed value.
*/
public function cast_val($val, $datatype)
{
// Translate non-storable values.
if (is_bool($val)) {
$val = (int) $val;
} elseif (is_array($val)) {
$val = \pdyn\datatype\Text::utf8safe_serialize($val);
} elseif (!is_scalar($val)) {
$val = '';
}
// Cast for column type (if possible).
switch ($datatype) {
case 'timestamp':
case 'int':
case 'bigint':
case 'id':
case 'bool':
case 'user_id':
$val = (int) $val;
break;
case 'float':
$val = (double) $val;
break;
default:
$val = (string) $val;
}
return $val;
}
