/**
* This will run SQL query and return structured data
*
* @param string $sql
* @param mixed $key
* @param array $options
* @return array
*/
public function query($sql, $key = null, $options = [])
{
$result = ['success' => false, 'sql' => $sql, 'error' => [], 'errno' => 0, 'num_rows' => 0, 'affected_rows' => 0, 'rows' => [], 'key' => $key, 'structure' => [], 'time' => null, 'last_insert_id' => 0];
// start time
$result['time'] = debug::get_microtime();
// cache id
$crypt_object = new crypt();
$cache_id = !empty($options['cache_id']) ? $options['cache_id'] : 'db_query_' . $crypt_object->hash($sql . serialize($key));
// if we cache this query
if (!empty($options['cache'])) {
$cache_object = new cache($this->connect_options['cache_link']);
$cached_result = $cache_object->get($cache_id);
if ($cached_result !== false) {
return $cached_result;
}
}
// quering regular query first
if (empty($options['multi_query'])) {
$resource = mysqli_query($this->db_resource, $sql);
if (!$resource) {
$this->error_overrides($result, mysqli_errno($this->db_resource), mysqli_error($this->db_resource));
} else {
$result['affected_rows'] += mysqli_affected_rows($this->db_resource);
if ($resource !== true) {
$result['num_rows'] += mysqli_num_rows($resource);
$result['structure'] = $this->field_structures($resource);
}
if ($result['num_rows'] > 0) {
while ($rows = mysqli_fetch_assoc($resource)) {
// casting types
$data = [];
foreach ($rows as $k => $v) {
$data[$k] = $this->process_value_as_per_type($v, $result['structure'][$k]['type']);
}
// assigning keys
if (!empty($key)) {
array_key_set_by_key_name($result['rows'], $key, $data);
} else {
$result['rows'][] = $data;
}
}
}
if ($resource !== true) {
mysqli_free_result($resource);
}
$result['success'] = true;
}
} else {
// multi query
$resource = mysqli_multi_query($this->db_resource, $sql);
if (!$resource) {
$result['error'][] = 'Db Link ' . $this->db_link . ': ' . mysqli_error($this->db_resource);
$result['errno'] = mysqli_errno($this->db_resource);
// we log this error message
// todo: process log policy here
error_log('Query error: ' . implode(' ', $result['error']) . ' [' . $sql . ']');
} else {
$result['affected_rows'] += mysqli_affected_rows($this->db_resource);
do {
if ($result_multi = mysqli_store_result($this->db_resource)) {
if ($result_multi) {
$result['num_rows'] += $num_rows = mysqli_num_rows($result_multi);
$result['structure'] = $this->field_structures($result_multi);
} else {
$num_rows = 0;
$result['error'][] = 'Db Link ' . $this->db_link . ': Multi query error!';
$result['errno'] = 1;
// we log this error message
// todo: process log policy here
error_log('Query error: ' . implode(' ', $result['error']) . ' [' . $sql . ']');
}
if ($num_rows > 0) {
while ($rows = mysqli_fetch_assoc($result_multi)) {
// casting types
$data = [];
foreach ($rows as $k => $v) {
$data[$k] = $this->process_value_as_per_type($v, $result['structure'][$k]['type']);
}
// assigning keys
if (!empty($key)) {
array_key_set_by_key_name($result['rows'], $key, $data);
} else {
$result['rows'][] = $data;
}
}
}
mysqli_free_result($result_multi);
}
} while (mysqli_more_results($this->db_resource) && mysqli_next_result($this->db_resource));
if (empty($result['error'])) {
$result['success'] = true;
}
}
}
// last insert id for auto increment columns
$result['last_insert_id'] = mysqli_insert_id($this->db_resource);
// caching if no error
if (!empty($options['cache']) && empty($result['error'])) {
$cache_object->set($cache_id, $result, ['tags' => $options['cache_tags'] ?? null]);
}
// end time
$result['time'] = debug::get_microtime() - $result['time'];
// if we are debugging
if (debug::$debug) {
debug::$data['sql'][] = $result;
}
return $result;
}
/**
* Primary key
*
* @param mixed $pk
* @param array $data
*/
function pk($pk, &$data)
{
if (!is_array($pk)) {
$pk = [$pk];
}
$result = [];
if (!is_array($data)) {
$data = [];
}
foreach ($data as $k => $v) {
array_key_set_by_key_name($result, $pk, $v);
}
$data = $result;
}
/**
* This will run SQL query and return structured data
*
* @param string $sql
* @param mixed $key
* @param array $options
* @return array
*/
public function query($sql, $key = null, $options = [])
{
$result = ['success' => false, 'sql' => $sql, 'error' => [], 'errno' => 0, 'num_rows' => 0, 'affected_rows' => 0, 'rows' => [], 'key' => $key, 'structure' => [], 'time' => null];
// start time
$result['time'] = debug::get_microtime();
// cache id
$crypt_object = new crypt();
$cache_id = !empty($options['cache_id']) ? $options['cache_id'] : 'db_query_' . $crypt_object->hash($sql . serialize($key));
// if we cache this query
if (!empty($options['cache'])) {
$cache_object = new cache($this->connect_options['cache_link']);
$cached_result = $cache_object->get($cache_id);
if ($cached_result !== false) {
return $cached_result;
}
}
// quering
$resource = @pg_query($this->db_resource, $sql);
$result['status'] = pg_result_status($resource);
if (!$resource || $result['status'] > 4) {
$last_error = pg_last_error($this->db_resource);
if (empty($last_error)) {
$errno = 1;
$error = 'DB Link ' . $this->db_link . ': ' . 'Unspecified error!';
} else {
preg_match("|ERROR:\\s(.*?):|i", $last_error, $matches);
$errno = !empty($matches[1]) ? $matches[1] : 1;
$error = $last_error;
}
$this->error_overrides($result, $errno, $error);
} else {
$result['affected_rows'] = pg_affected_rows($resource);
$result['num_rows'] = pg_num_rows($resource);
$result['structure'] = $this->field_structures($resource);
if ($result['num_rows'] > 0) {
while ($rows = pg_fetch_assoc($resource)) {
// transforming pg arrays to php arrays and casting types
foreach ($rows as $k => $v) {
if ($result['structure'][$k]['type'][0] == '_') {
$rows[$k] = $this->pg_parse_array($v);
} else {
if (in_array($result['structure'][$k]['type'], ['int2', 'int4', 'int8'])) {
if (!is_null($v)) {
$rows[$k] = (int) $v;
}
} else {
if (in_array($result['structure'][$k]['type'], ['real', 'double precision'])) {
if (!is_null($v)) {
$rows[$k] = (double) $v;
}
} else {
if ($result['structure'][$k]['type'] == 'bytea') {
$rows[$k] = pg_unescape_bytea($v);
} else {
if ($result['structure'][$k]['type'] == 'jsonb') {
// we must get json vallues to PHP format
$rows[$k] = json_encode(json_decode($v, true));
}
}
}
}
}
}
// assigning keys
if (!empty($key)) {
array_key_set_by_key_name($result['rows'], $key, $rows);
} else {
$result['rows'][] = $rows;
}
}
}
pg_free_result($resource);
$result['success'] = true;
}
// caching if no error
if (!empty($options['cache']) && empty($result['error'])) {
$cache_object->set($cache_id, $result, ['tags' => $options['cache_tags'] ?? []]);
}
// end time
$result['time'] = debug::get_microtime() - $result['time'];
// if we are debugging
if (debug::$debug) {
debug::$data['sql'][] = $result;
}
return $result;
}
