414 lines
No EOL
9.2 KiB
PHP
414 lines
No EOL
9.2 KiB
PHP
<?php
|
|
defined('PROJECT_PATH') OR exit('No direct script access allowed');
|
|
|
|
// v3.43 (+ query counter)
|
|
class DB
|
|
{
|
|
private static $_instance = null;
|
|
|
|
private $_PDO;
|
|
private $_query;
|
|
|
|
private $_query_counter;
|
|
|
|
// Handle instances
|
|
public final static function get_instance(){
|
|
if(self::$_instance === null){
|
|
self::$_instance = new static();
|
|
}
|
|
|
|
return self::$_instance;
|
|
}
|
|
|
|
public static function connection() {
|
|
return Config::get_safe('db_connection', 'sqlite');
|
|
}
|
|
|
|
// CONCAT() does not exist in SQLite, using || instead
|
|
// for postgres, ERROR: could not determine data type of parameter $1
|
|
public final static function concat(){
|
|
$values = func_get_args();
|
|
|
|
if(DB::connection() === 'sqlite' || DB::connection() === 'postgres') {
|
|
return implode(" || ", $values);
|
|
} else {
|
|
return 'CONCAT('.implode(", ", $values).')';
|
|
}
|
|
}
|
|
|
|
// Initialise PDO object
|
|
private final function __construct(){
|
|
switch(DB::connection()) {
|
|
case 'mysql':
|
|
$this->mysql_connect();
|
|
break;
|
|
case 'postgres':
|
|
$this->postgres_connect();
|
|
break;
|
|
case 'sqlite':
|
|
$this->sqlite_connect();
|
|
break;
|
|
}
|
|
}
|
|
|
|
private final function mysql_connect(){
|
|
$host = Config::get_safe('mysql_host', false);
|
|
$port = Config::get_safe('mysql_port', false);
|
|
$socket = Config::get_safe('mysql_socket', false);
|
|
|
|
if($socket === false && $host === false){
|
|
throw new DBException("Mysql host or socket must be defined.");
|
|
}
|
|
|
|
// Try to connect
|
|
try {
|
|
$this->_PDO = new \PDO(
|
|
// Server
|
|
'mysql:'.
|
|
($socket !== false
|
|
? 'unix_socket='.$socket
|
|
: 'host='.$host.($port !== false ? ';port='.$port : '')
|
|
).
|
|
// DB
|
|
';dbname='.Config::get('db_name').
|
|
// Charset
|
|
';charset=utf8',
|
|
// Username
|
|
Config::get('mysql_user'),
|
|
// Password
|
|
Config::get_safe('mysql_pass', ''),
|
|
// Set attributes
|
|
[
|
|
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
|
|
\PDO::ATTR_EMULATE_PREPARES => false
|
|
]
|
|
);
|
|
|
|
$this->_PDO->exec(
|
|
// Set charset
|
|
'SET NAMES utf8;'.
|
|
|
|
// Set timezone
|
|
'SET time_zone="'.date('P').'";'
|
|
);
|
|
} catch (PDOException $e) {
|
|
throw new DBException($e->getMessage());
|
|
}
|
|
}
|
|
|
|
private final function postgres_connect(){
|
|
$host = Config::get_safe('postgres_host', false);
|
|
$port = Config::get_safe('postgres_port', false);
|
|
$socket = Config::get_safe('postgres_socket', false);
|
|
|
|
if($socket === false && $host === false){
|
|
throw new DBException("Postgres host or socket must be defined.");
|
|
}
|
|
|
|
// Try to connect
|
|
try {
|
|
$this->_PDO = new \PDO(
|
|
// Server
|
|
'pgsql:'.
|
|
($socket !== false
|
|
? 'unix_socket='.$socket
|
|
: 'host='.$host.($port !== false ? ';port='.$port : '')
|
|
).
|
|
// DB
|
|
';dbname='.Config::get('db_name').
|
|
// Charset
|
|
';options=\'--client_encoding=UTF8\'',
|
|
// Username
|
|
Config::get('postgres_user'),
|
|
// Password
|
|
Config::get_safe('postgres_pass', ''),
|
|
// Set attributes
|
|
[
|
|
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
|
|
\PDO::ATTR_EMULATE_PREPARES => false
|
|
]
|
|
);
|
|
|
|
$this->_PDO->exec(
|
|
// Set timezone
|
|
'SET TIME ZONE "'.date('e').'";'
|
|
);
|
|
} catch (PDOException $e) {
|
|
throw new DBException($e->getMessage());
|
|
}
|
|
}
|
|
|
|
private final function sqlite_connect(){
|
|
$sqlite_db = PROJECT_PATH.Config::get_safe('sqlite_db', "data/sqlite.db");
|
|
|
|
// First run of sqlite
|
|
if(!file_exists($sqlite_db)) {
|
|
if(!is_writable(dirname($sqlite_db))) {
|
|
throw new DBException("Sqlite database directory must me writable.");
|
|
}
|
|
|
|
if(!touch($sqlite_db)) {
|
|
throw new DBException("Cannot create sqlite database file.");
|
|
}
|
|
|
|
// Inilialize SQL schema
|
|
$sql_schema = file_get_contents(APP_PATH."db/sqlite/01_schema.sql");
|
|
|
|
try {
|
|
$this->_PDO = new \PDO("sqlite:".$sqlite_db, null, null, [
|
|
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
|
|
]);
|
|
$this->_PDO->exec($sql_schema);
|
|
} catch (PDOException $e) {
|
|
$this->_PDO = null;
|
|
unlink($sqlite_db);
|
|
|
|
throw new DBException($e->getMessage());
|
|
}
|
|
|
|
return ;
|
|
}
|
|
|
|
// Try to connect
|
|
try {
|
|
$this->_PDO = new \PDO("sqlite:".$sqlite_db, null, null, [
|
|
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
|
|
]);
|
|
} catch (PDOException $e) {
|
|
throw new DBException($e->getMessage());
|
|
}
|
|
}
|
|
|
|
// Just flattern array to be binded : [key1, key2, [key3, [key4]]] => [key1, key2, key3, key4]
|
|
private final function bind_value($key, $value){
|
|
if(is_array($value)){
|
|
foreach($value as $one_value){
|
|
$key = $this->bind_value($key, $one_value);
|
|
}
|
|
|
|
return $key;
|
|
}
|
|
|
|
// BUG: Force strings to be UTF-8
|
|
// remove all 4-bytes characters.
|
|
if(is_string($value)){
|
|
$value = preg_replace('/[\xF0-\xF7].../s', '', $value);
|
|
}
|
|
|
|
$this->_query->bindValue($key, $value);
|
|
return ++$key;
|
|
}
|
|
|
|
// Process Query
|
|
// query ($sql)
|
|
// query ($sql, $bind_param_01, $bind_param_02, ...)
|
|
// query ($sql, [$bind_param_01, $bind_param_02, ...])
|
|
public final function query(){
|
|
// Second parm is binded values
|
|
$params = func_get_args();
|
|
|
|
// First parameter is sql
|
|
$sql = $params[0];
|
|
unset($params[0]);
|
|
|
|
// Replace backticks with " for postgres
|
|
if(DB::connection() === 'postgres') {
|
|
$sql = str_replace("`", '"', $sql);
|
|
}
|
|
|
|
// Debug mode
|
|
if(Config::get_safe('debug', false)){
|
|
echo "<!-- ".$sql." + ".json_encode($params)." -->\n";
|
|
}
|
|
|
|
// Try to prepare MySQL statement
|
|
try {
|
|
// Prepare PDO statement
|
|
$this->_query = $this->_PDO->prepare($sql);
|
|
|
|
// Bind values
|
|
$this->bind_value(1, $params);
|
|
|
|
// Execute
|
|
$this->_query->execute();
|
|
} catch (PDOException $e) {
|
|
throw new DBException($e->getMessage());
|
|
}
|
|
|
|
$this->_query_counter++;
|
|
return $this;
|
|
}
|
|
|
|
// Insert into table
|
|
public final function insert($table_name, $fields = null){
|
|
// If empty line
|
|
if(empty($fields)){
|
|
return $this->query("INSERT INTO `{$table_name}` () VALUES ()");
|
|
}
|
|
|
|
// If multiple
|
|
if(isset($fields[0])){
|
|
// Turn array into PDO prepered statement format
|
|
$keys = array_keys($fields[0]);
|
|
|
|
// Build query
|
|
$query = "INSERT INTO `{$table_name}` (`".implode('`, `', $keys)."`) VALUES ";
|
|
|
|
// Insert values
|
|
$first = true;
|
|
$prepared_data = array();
|
|
foreach($fields as $field){
|
|
if($first){
|
|
$first = false;
|
|
} else {
|
|
$query .= ',';
|
|
}
|
|
|
|
end($field);
|
|
$last_key = key($field);
|
|
|
|
$query .= '(';
|
|
foreach($field as $key => $value){
|
|
if($value === "NOW()"){
|
|
if(DB::connection() === 'sqlite') {
|
|
$query .= "datetime('now', 'localtime')";
|
|
} else {
|
|
$query .= "NOW()";
|
|
}
|
|
} else {
|
|
$query .= '?';
|
|
$prepared_data[] = $value;
|
|
}
|
|
|
|
if($last_key != $key){
|
|
$query .= ',';
|
|
}
|
|
}
|
|
$query .= ')';
|
|
}
|
|
|
|
// Execute query
|
|
return $this->query($query, $prepared_data);
|
|
}
|
|
|
|
// If only single
|
|
return $this->insert($table_name, array($fields));
|
|
}
|
|
|
|
// Update table
|
|
// update ($table_name, $fields)
|
|
// update ($table_name, $fields, $sql)
|
|
// update ($table_name, $fields, $sql, $bind_param_01, $bind_param_02, ...)
|
|
// update ($table_name, $fields, $sql, [$bind_param_01, $bind_param_02, ...])
|
|
public final function update(){
|
|
// Fourt param is binded values
|
|
$params = func_get_args();
|
|
|
|
// First is table_name
|
|
$table_name = $params[0];
|
|
unset($params[0]);
|
|
|
|
// Second is fields
|
|
$fields = $params[1];
|
|
unset($params[1]);
|
|
|
|
// Third is sql
|
|
$sql = $params[2];
|
|
unset($params[2]);
|
|
|
|
// If fields are not array, do nothing
|
|
if(!is_array($fields)){
|
|
return $this;
|
|
}
|
|
|
|
end($fields);
|
|
$last_key = key($fields);
|
|
|
|
// Support for NOW()
|
|
$prepared_data = array();
|
|
$set_data = null;
|
|
foreach($fields as $key => $value){
|
|
if($value === "NOW()"){
|
|
if(DB::connection() === 'sqlite') {
|
|
$set_data .="`{$key}` = datetime('now', 'localtime')";
|
|
} else {
|
|
$set_data .="`{$key}` = NOW()";
|
|
}
|
|
} else {
|
|
$set_data .= "`{$key}` = ?";
|
|
$prepared_data[] = $value;
|
|
}
|
|
|
|
if($last_key != $key){
|
|
$set_data .= ',';
|
|
}
|
|
}
|
|
|
|
// If params are not array, make it
|
|
if(!is_array($params)){
|
|
$params = array($params);
|
|
}
|
|
|
|
// Merge fields array and additional SQL data
|
|
foreach($params as $param){
|
|
$prepared_data[] = $param;
|
|
}
|
|
|
|
// Build query
|
|
$query = "UPDATE `{$table_name}` SET {$set_data} ".$sql;
|
|
|
|
// Execute query
|
|
return $this->query($query, $prepared_data);
|
|
}
|
|
|
|
// Alias for all
|
|
public final function results(){
|
|
trigger_error("Using deprecated method <strong>DB::results();</strong>. Use <strong>DB::all();</strong> instead.");
|
|
return $this->all();
|
|
}
|
|
|
|
// Get all rows
|
|
public final function all($type = \PDO::FETCH_ASSOC){
|
|
return $this->_query->fetchAll($type);
|
|
}
|
|
|
|
// Get all values to one dimensional array
|
|
public final function columns($column = 0){
|
|
return $this->_query->fetchAll(\PDO::FETCH_COLUMN, $column);
|
|
}
|
|
|
|
// Get first row from result
|
|
public final function first($key = null){
|
|
$results = $this->all();
|
|
|
|
if($key !== null){
|
|
return @$results[0][$key];
|
|
}
|
|
|
|
return @$results[0];
|
|
}
|
|
|
|
// Get last inserted ID
|
|
public final function last_id(){
|
|
return $this->_PDO->lastInsertId();
|
|
}
|
|
|
|
// Exec
|
|
public final function exec($sql){
|
|
// Try to execute MySQL
|
|
try {
|
|
$this->_PDO->exec($sql);
|
|
} catch (PDOException $e) {
|
|
throw new DBException($e->getMessage());
|
|
}
|
|
|
|
return $this;
|
|
}
|
|
|
|
public final function total_queries(){
|
|
return $this->_query_counter;
|
|
}
|
|
}
|
|
|
|
// Handle DB errors
|
|
class DBException extends Exception{} |