namespace Ice\Db\Driver;
use Ice\Arr;
use Ice\Exception;
use Ice\Db\DbInterface;
/**
* Pdo driver.
*
* @package Ice/Db
* @category Component
* @author Ice Team
* @copyright (c) 2014-2023 Ice Team
* @license http://iceframework.org/license
*/
class Pdo implements DbInterface
{
protected id = "id" { get };
protected type = "SQL" { get };
protected error;
protected client { get };
protected driverName { get };
protected identifier = "\"%s\"";
/**
* Instantiate pdo connection.
*
* @param string dsn
* @param string user
* @param string password
* @param array options
*/
public function __construct(string dsn, string user = NULL, string password = NULL, array options = [])
{
let this->driverName = explode(":", dsn, 2)[0];
switch this->driverName {
case "mysql":
let this->identifier = "`%s`";
let dsn = dsn . ";charset=utf8";
break;
case "sqlsrv":
let this->identifier = "[%s]";
break;
case "sqlite":
let this->identifier = "[%s]";
break;
case "oci":
let this->identifier = "\"%s\"";
break;
case "pgsql":
let this->identifier = "\"%s\"";
break;
}
let this->client = new \PDO(dsn, user, password, options);
}
/**
* Get the id value.
*
* @param string id
* @return int
*/
public function getIdValue(id) -> int
{
return (int) id;
}
/**
* Get a date time object.
*
* @param mixed value
* @param boolean model
* @return object
*/
public function getDateTime(value = null, model = false)
{
var date;
if typeof value == "integer" {
let value = "@" . value;
}
let date = new \DateTime(value);
if model {
return date->format("Y-m-d H:i:s.u");
}
return date;
}
/**
* Find one row that match criteria.
*
*
* //SELECT x, y FROM users WHERE a=1 or b=2 ORDER BY a desc,b asc Limit 1
* $db->findOne("users", ["OR" => [["a" => 1], ["b" => 2]]], ["order" => ["a desc", "b asc"]], ["x","y"]);
*
*
* @param string from Table name
* @param mixed filters Filters to create WHERE conditions
* @param array options Options to limit/group results
* @param array fields Fields to retrieve, if not specified get all
* @return Arr|false
*/
public function findOne(string! from, var filters = [], array options = [], array fields = [])
{
var result, fetched;
let options["limit"] = 1,
result = this->select(from, filters, options, fields),
fetched = result->$fetch(\Pdo::FETCH_ASSOC);
return fetched ? new Arr(fetched) : false;
}
/**
* Find all records that match criteria.
*
*
* //SELECT * FROM users WHERE a=1 and b="q"
* $db->find("users", ["a" => 1, "b" => "q"]);
*
* //SELECT * FROM users WHERE age>33
* $db->find("users", ["age" => [">" => 33]]);
*
* //SELECT x, y FROM users WHERE a=1 or b=2 ORDER BY a desc,b asc
* $db->find("users", ["OR" => [["a" => 1], ["b" => 2]]], ["order" => ["a desc", "b asc"]], ["x","y"]);
*
*
* @param string from Table name
* @param mixed filters Filters to create WHERE conditions
* @param array options Options to limit[top]/group results
* @param array fields Fields to retrieve, if not specified get all
* @return Arr
*/
public function find(string! from, var filters = [], array options = [], array fields = [])
{
var result;
let result = this->select(from, filters, options, fields);
return new Arr(result->fetchAll(\Pdo::FETCH_ASSOC));
}
/**
* Count rows that match criteria.
*
*
* //SELECT count(*) as total FROM users WHERE a=1
* $db->count("users", ["a" => 1]);
*
*
* @param string from Table name
* @param mixed filters Filters to create WHERE conditions
* @return int
*/
public function count(string! from, var filters = []) -> int
{
var result;
let result = this->findOne(from, filters, [], ["count(*) AS total"]);
return result ? (int) result->total : 0;
}
/**
* Prepare SQL WHERE condition.
*
* @params mixed filters
* @params array values
* @param array options
*/
protected function where(var filters = [], array values = [], array options = []) -> array
{
var and, data, operator, key, item, value, or, is, index, i, sql, condition, column;
let and = [],
sql = "",
i = "";
if is_numeric(filters) {
let filters = (int) filters;
}
switch typeof filters {
case "array":
for operator, data in filters {
switch operator {
case "OR":
case "or":
case "$or":
let or = [],
operator = "OR";
break;
case "AND":
case "and":
case "$and":
let operator = "AND";
break;
default: // short "AND"
var tmp = data;
let data = ["AND": [operator: tmp]],
operator = "AND";
break;
}
for item in data {
let key = key(item),
value = current(item),
column = sprintf(this->identifier, key);
if typeof value == "array" {
let is = key(value),
value = current(value);
} else {
let is = "=";
}
do {
let index = ":" . key . i,
i = i ? (int) i + 1 : 1;
} while isset values[index];
switch is {
case "IN":
case "in":
case "$in":
if typeof value == "array" {
var j, id, ids = [];
// Bind all values
for j, id in value {
let ids[] = index . j,
values[index . j] = id;
}
let value = "(" . implode(", ", ids) . ")";
}
let condition = column . " " . is . " " . value;
break;
case "IS":
case "is":
case "IS NOT":
case "is not":
// Don't bind value
let condition = column . " " . is . " " . value;
break;
default:
if isset options["insensitive"] {
let condition = sprintf("LOWER(%s) %s LOWER(%s)", column, is, index);
} else {
let condition = column . " " . is . " " . index;
}
// Bind value
let values[index] = value;
break;
}
if operator == "AND" {
let and[] = condition;
} else {
let or[] = condition;
}
}
if operator == "OR" {
let and[] = "(" . implode(" OR ", or) . ")";
}
}
let sql .= implode(" AND ", and);
break;
case "integer":
let sql .= sprintf(this->identifier, this->id) . "=" . filters;
break;
case "string":
let sql .= filters;
break;
default:
let sql .= "TRUE";
break;
}
return [sql, values];
}
/**
* SELECT record(s) that match criteria.
*
*
* // MySQL: SELECT * FROM users WHERE a=1 or b=2 ORDER BY a desc,b asc LIMIT 10
* // MSSQL: SELECT TOP 10 * FROM users WHERE a=1 or b=2 ORDER BY a desc,b asc
* $db->select("users", ["OR" => [["a" => 1], ["b" => 2]]], ["order" => ["a desc", "b asc"], "limit" => 10]);
*
* // MySQL: SELECT x, y FROM users WHERE a=1 or b=2 ORDER BY a desc,b asc LIMIT 10 offset 50
* // MSSQL: SELECT x, y FROM users WHERE a=1 or b=2 ORDER BY a desc,b asc offset 50 ROWS FETCH NEXT 10 ROWS ONLY
* $db->select("users", ["OR" => [["a" => 1], ["b" => 2]]], ["order" => ["a desc", "b asc"], "limit" => 10, "offset" => 50], ["x","y"]);
*
*
* @param string from Table name
* @param mixed filters Filters to create WHERE conditions
* @param array options Options to limit/offset/group results
* @param array fields Fields to retrieve, if not specified get all
*/
public function select(string! from, var filters = [], array options = [], array fields = [])
{
var columns, filtered, values, query, sql = "SELECT ";
if count(fields) {
let columns = implode(", ", fields);
} else {
let columns = "*";
}
if isset options["limit"] {
// ms sql server only
if this->driverName == "sqlsrv" {
if isset options["offset"] {
// SQL Server 2012+ ONLY
let options["offset"] .= " ROWS FETCH NEXT ". options["limit"] . " ROWS ONLY";
} else {
let sql .= "TOP " . options["limit"] . " ";
}
unset options["limit"];
} elseif this->driverName == "oci" {
if isset options["offset"] {
let options["offset"] .= " ROWS FETCH NEXT ". options["limit"] . " ROWS ONLY";
} else {
if empty filters {
let filters = "rownum = " . options["limit"];
} else {
let filters = ["AND": [filters, ["rownum", options["limit"]]]];
}
}
unset options["limit"];
}
}
let filtered = this->where(filters, [], options),
sql .= columns . " FROM " . sprintf(this->identifier, from),
values = filtered[1];
if !empty filtered[0] {
let sql .= " WHERE " . filtered[0];
}
if isset options["group"] {
if typeof options["group"] == "array" {
let sql .= " GROUP BY " . implode(", ", options["group"]);
} else {
let sql .= " GROUP BY " . options["group"];
}
}
if isset options["order"] {
if typeof options["order"] == "array" {
// Check if order is associative or sequential
if count(array_filter(array_keys(options["order"]), "is_string")) {
var key, value, tmp = [];
for key, value in options["order"] {
let tmp[] = sprintf(this->identifier, key) . " " . value;
}
let sql .= " ORDER BY " . implode(", ", tmp);
} else {
let sql .= " ORDER BY " . implode(", ", options["order"]);
}
} else {
let sql .= " ORDER BY " . options["order"];
}
}
if isset options["limit"] {
let sql .= " LIMIT " . options["limit"];
}
if isset options["offset"] {
let sql .= " OFFSET " . options["offset"];
}
let query = this->client->prepare(sql);
let this->error = query->execute(values) ? null : query->errorInfo();
return query;
}
/**
* INSERT record into table.
*
*
* //INSERT INTO users (a,b) VALUES (1, 2)
* $db->insert("users", [["a" => 1], ["b" => 2]]);
*
*
* @param string from Table name
* @param array fields Fields to insert, keys are the column names
*/
public function insert(string! from, array fields = [])
{
var key, value, columns, values, sql, query, status;
let columns = [],
values = [];
for key, value in fields {
let columns[] = sprintf(this->identifier, key),
values[":" . key] = value;
}
let sql = "INSERT INTO " . sprintf(this->identifier, from) . " (" . implode(", ", columns) . ") VALUES (" . implode(", ", array_keys(values)) . ")",
query = this->client->prepare(sql),
status = query->execute(values),
this->error = status ? null : query->errorInfo();
return status;
}
/**
* UPDATE records in the table.
*
*
* //UPDATE users SET a=1, b=2 WHERE id=10 OR foo="bar"
* $db->update("users", ["OR" => ["id" => 10, "foo" => "bar"]], [["a" => 1], ["b" => 2]]);
*
*
* @param string from Table name
* @param mixed filters Filters to create WHERE conditions
* @param array fields Fields to update, keys are the column names
*/
public function update(string! from, var filters = [], array fields = [])
{
var key, value, columns, values, filtered, sql, query, status;
let columns = [],
values = [];
for key, value in fields {
let values[":" . key] = value,
columns[] = sprintf(this->identifier, key) . " = :" . key;
}
let filtered = this->where(filters, values),
sql = "UPDATE " . sprintf(this->identifier, from) . " SET " . implode(", ", columns) . " WHERE " . filtered[0],
values = array_merge(values, filtered[1]),
query = this->client->prepare(sql),
status = query->execute(values),
this->error = status ? null : query->errorInfo();
return status;
}
/**
* Remove records from the table.
*
*
* //DELETE FROM users WHERE id=10 OR foo="bar"
* $db->delete("users", ["OR" => ["id" => 10, "foo" => "bar"]]);
*
*
* @param string from Table name
* @param mixed filters Filters to create WHERE conditions
*/
public function delete(string! from, var filters = [])
{
var filtered, sql, values, query, status;
let filtered = this->where(filters),
sql = "DELETE FROM " . sprintf(this->identifier, from) . " WHERE " . filtered[0],
values = filtered[1],
query = this->client->prepare(sql),
status = query->execute(values),
this->error = status ? null : query->errorInfo();
return status;
}
/**
* Query sql statement. execute the statement and populate into Model object:
*
*
* //select * from t where id=1
* $m = $this->db->query('select * from t where id=:id', [':id' => 1], new stdClass);
*
* //select * from t where id=1 OR foo='bar'
* $m = $this->db->query('select * from t where id=? OR foo=?', [1, "bar"], '\Ice\Arr');
*
*
* @param string sql SQL with kinda of placeholders
* @param array values Replace placeholders in the sql
* @param mixed obj The classname or arr object will be populated from query result
* @return PDOStatement|object|null If fail return null
*/
public function query(string! sql, array values = [], var obj = null)
{
var query, result, status;
let query = this->client->prepare(sql),
status = query->execute(values),
this->error = status ? null : query->errorInfo();
if status && obj {
if typeof obj == "string" {
return query->fetchObject(obj);
}
let result = query->$fetch(\Pdo::FETCH_ASSOC);
if result {
if obj instanceof Arr {
obj->merge(result);
} else {
let obj = new Arr(result);
}
return obj;
} else {
return false;
}
}
return status ? query : null;
}
/**
* Get last inserted ID.
*
* @return int
*/
public function getLastInsertId() -> int
{
return (int) this->client->lastInsertId();
}
/**
* Get an error message.
*
* @return mixed
*/
public function getError()
{
var error;
fetch error, this->error[0];
return error;
}
}
ice doc v1.10.1