SQLConnection::prepare() - Code Metrics - caffeina-core/core - Measure and Improve Code Quality continuously with Scrutinizer

SQLConnection::prepare() A
last analyzed 2019-04-16 08:44 UTC

↳ Parent: SQLConnection

Complexity

Conditions	3
Paths	3

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	3
nc	3
nop	2
dl	0
loc	4
rs	10
c	0
b	0
f	0

<?php

/**
 * SQL
 *
 * SQL database access via PDO.
 *
 * @package core
 * @author [email protected]
 * @copyright Caffeina srl - 2015 - http://caffeina.com
 */


class SQL {
  use Module, Events;

  protected static $connections = [],
                   $current     = 'default';

  /**
   * Register a new datasource
   * @param  string $name     The assigned name for the datasource
   * @param  string $dsn      PDO DSN URL
   * @param  string $username User credentials
   * @param  string $password User credentials
   * @param  array  $options  Options to pass to the PDO constructor
   * @return SQLConnection    The datasource resource
   */
  public static function register($name, $dsn, $username=null, $password=null, $options=[]){
    return self::$connections[$name] = new SQLConnection($dsn, $username, $password, $options);
  }

  /**
   * Register the default datasource
   * @param  string $dsn      PDO DSN URL
   * @param  string $username User credentials
   * @param  string $password User credentials
   * @param  array  $options  Options to pass to the PDO constructor
   * @return SQLConnection    The datasource resource
   */
  public static function connect($dsn, $username=null, $password=null, $options=[]){
    return self::register('default', $dsn, $username, $password, $options);
  }

  /**
   * Bind the default datasource to another named connection
   * @param  string $name The datasource name
   * @return bool       `true` if correctly changed
   */
  public static function defaultTo($name){
    if (isset(self::$connections[$name])){
      self::$current = $name;
      return true;
    } else return false;
  }

  /**
   * Close one or all (if no parameter passed) registered datasource connections
   * @param  string $name The datasource name, omit for close all of them
   * @return bool       `true` if one or more datasource where closed
   */
  public static function close($name=null){
    if ($name === null) {
      foreach (self::$connections as $conn) $conn->close();
      return true;
    } else if (isset(self::$connections[$name])){
      self::$connections[$name]->close();
      return true;
    } else return false;
  }

  /**
   * Datasource connection accessor
   * @param  strinf $name The datasource name
   * @return SQLConnect   The datasource connection
   */
  public static function using($name){
    if (empty(self::$connections[$name])) throw new \Exception("[SQL] Unknown connection named '$name'.");
    return self::$connections[$name];
  }

  /**
   * Proxy all direct static calls to the SQL module to the `default` datasource
   * @param  string $method The method name
   * @param  array $args    The method arguments
   * @return mixed          The method return value
   */
  public static function __callStatic($method, $args){
    if (empty(self::$connections[self::$current])) throw new \Exception("[SQL] No default connection defined.");
    return call_user_func_array([self::$connections[self::$current],$method],$args);
  }

}

// Default connection to in-memory ephemeral database
SQL::connect('sqlite::memory:');

class SQLConnection {

  protected $connection        = [],
            $queries           = [],
            $last_exec_success = true;

  public function __construct($dsn, $username=null, $password=null, $options=[]){
    $this->connection = [
      'dsn'        => $dsn,
      'pdo'        => null,
      'username'   => $username,
      'password'   => $password,
      'options'    => array_merge([
        PDO::ATTR_ERRMODE                => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE     => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES       => true,
        PDO::MYSQL_ATTR_MULTI_STATEMENTS => true,
      ], $options),
    ];
    // "The auto-commit mode cannot be changed for this driver" SQLite workaround
    if (strpos($dsn,'sqlite:') === 0) {
      $this->connection['options'] = $options;
    }
  }

  public function close(){
    $this->connection['pdo'] = null;
  }

  public function connection(){
    if(empty($this->connection['pdo'])) {
      try {
        $this->connection['pdo'] = new PDO(
            $this->connection['dsn'],
            $this->connection['username'],
            $this->connection['password'],
            $this->connection['options']

        );
        SQL::trigger('connect',$this);
        Event::trigger('core.sql.connect',$this);
      } catch(Exception $e) {
        $this->connection['pdo'] = null;
      }
    }
    return $this->connection['pdo'];
  }


  /**
   * Prepares a SQL query string
   *
   * @param      string   $query       The query
   * @param      array    $pdo_params  The extra PDO parameters
   *
   * @return     boolean
   */
  public function prepare($query, $pdo_params=[]){
    if(!$this->connection()) return false;
    return isset($this->queries[$query]) ? $this->queries[$query] : ($this->queries[$query] = $this->connection()->prepare($query, $pdo_params));
  }

  public function exec($query, $params=[], $pdo_params=[]){
    if(!$this->connection()) return false;

    if (false==is_array($params)) $params = (array)$params;
    $query = Filter::with('core.sql.query',$query);

    if($statement = $this->prepare($query, $pdo_params)){
      SQL::trigger('query',$query,$params,(bool)$statement);
      Event::trigger('core.sql.query',$query,$params,(bool)$statement);

      foreach ($params as $key => $val) {
        $type = PDO::PARAM_STR;
        if (is_bool($val)) {
          $type = PDO::PARAM_BOOL;
        } elseif (is_null($val)) {
          $type = PDO::PARAM_NULL;
        } elseif (is_int($val)) {
          $type = PDO::PARAM_INT;
        }

        // bindValue need a 1-based numeric parameter
        $statement->bindValue(is_numeric($key)?$key+1:':'.$key, $val, $type);
      }
    } else {
      $error = $this->connection['pdo']->errorInfo();
      SQL::trigger('error',$error[2], $query, $params, $error);
      Event::trigger('core.sql.error',$error[2], $query, $params, $error);
      return false;
    }

    $this->last_exec_success = $statement && $statement->execute();
    return $statement;
  }

  public function value($query, $params=[], $column=0){
    if(!$this->connection()) return false;

    $res = $this->exec($query,$params);
    return $res ? $res->fetchColumn($column) : null;
  }

  public function column($query, $params=[], $column=0){
    if(!$this->connection()) return false;

    $results = [];
    $res     = $this->exec($query,$params);

    if (is_string($column))
      while ($x = $res->fetch(PDO::FETCH_OBJ)) $results[] = $x->$column;
    else
      while ($x = $res->fetchColumn($column)) $results[] = $x;

    return $results;
  }

  public function reduce($query, $params=[], $looper = null, $initial = null){
    if(!$this->connection()) return false;

    // ($query,$looper,$initial) shorthand
    if (is_callable($params)) { $initial = $looper; $looper = $params; $params = []; }
    if(( $res = $this->exec($query,$params, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]) ) && is_callable($looper) ){
      while ($row = $res->fetchObject()) { $initial = $looper($initial, $row); }
      return $initial;
    } else return false;
  }

  public function each($query, $params=[], callable $looper = null){
    if(!$this->connection()) return false;

    // ($query,$looper) shorthand
    if ($looper===null && is_callable($params)) {$looper = $params; $params = [];}
    if( $res = $this->exec($query,$params, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]) ){
      if(is_callable($looper)) {
        while ($row = $res->fetchObject()) $looper($row);
        return true;
      } else return $res->fetchAll(PDO::FETCH_CLASS);
    } else return false;
  }

  public function single($query, $params=[], callable $handler = null){
    if(!$this->connection()) return false;

    // ($query,$handler) shorthand
    if ($handler===null && is_callable($params)) {$handler = $params; $params = [];}
    if( $res = $this->exec($query,$params, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]) ){
        if (is_callable($handler))
          return $handler($res->fetchObject());
        else
          return $res->fetchObject();
    }
  }

 public function run($script){
    if(!$this->connection()) return false;

    $sql_path = Options::get('database.sql.path',APP_DIR.'/sql');
    $sql_sep  = Options::get('database.sql.separator',';');
    if (is_file($f = "$sql_path/$script.sql")){
        $result = true;
        foreach(explode($sql_sep,file_get_contents($f)) as $statement) {
            $result = $this->exec($statement);
        }
        return $result;
    } else return false;
  }

  public function all($query, $params=[], callable $looper = null){
   if(!$this->connection()) return false;
   return $this->each($query,$params,$looper);
  }

  public function delete($table, $pks=null, $pk='id', $inclusive=true){
    if(!$this->connection()) return false;

    if (null===$pks) {
      return $this->exec("DELETE FROM `$table`");
    } else {
      return $this->exec("DELETE FROM `$table` WHERE `$pk` ".($inclusive ? "" : "NOT " )."IN (" . implode( ',', array_fill_keys( (array)$pks, '?' ) ) . ")",(array)$pks);
    }
  }

  public function insert($table, $data, $pk='id'){
    if(!$this->connection()) return false;

    if (false==is_array($data)) $data = (array)$data;
    $k = array_keys($data);
    asort($k);
    $pk_a = $k;
    array_walk($pk_a,function(&$e){ $e = ':'.$e;});
    $q = "INSERT INTO `$table` (`".implode('`,`',$k)."`) VALUES (".implode(',',$pk_a).")";
    $this->exec($q,$data);
    return $this->last_exec_success ? $this->connection()->lastInsertId($pk) : false;
  }

  public function updateWhere($table, $data, $where, $pk='id'){
    if(!$this->connection()) return false;

    if (false==is_array($data)) $data = (array)$data;

    if (empty($data)) return false;
    $k = array_keys($data);
    asort($k);

    // Remove primary key from SET
    array_walk($k,function(&$e) use ($pk) {
      $e = ($e==$pk) ? null : "`$e`=:$e";
    });

    $q = "UPDATE `$table` SET ".implode(', ',array_filter($k))." WHERE $where";
    $this->exec($q, $data);
    $data = (object)$data;
    return $this->last_exec_success ? $data->$pk : false;
  }

  public function update($table, $data, $pk='id', $extra_where=''){
    return $this->updateWhere($table, $data, "`$pk`=:$pk $extra_where", $pk);
  }

  public function insertOrUpdate($table, $data=[], $pk='id', $extra_where=''){
    if(!$this->connection()) return false;

    if (false==is_array($data)) $data = (array)$data;
    if (empty($data[$pk])) return $this->insert($table, $data);
    if( (string) $this->value("SELECT `$pk` FROM `$table` WHERE `$pk`=? LIMIT 1", [$data[$pk]]) === (string) $data[$pk] ){
        return $this->update($table, $data, $pk, $extra_where);
    } else {
        return $this->insert($table, $data, $pk);
    }
  }
}



1		<?php
2
3		/**
4		* SQL
5		*
6		* SQL database access via PDO.
7		*
8		* @package core
9		* @author [email protected]
10		* @copyright Caffeina srl - 2015 - http://caffeina.com
11		*/
12
13
14		class SQL {
15		use Module, Events;
16
17		protected static $connections = [],
18		$current = 'default';
19
20		/**
21		* Register a new datasource
22		* @param string $name The assigned name for the datasource
23		* @param string $dsn PDO DSN URL
24		* @param string $username User credentials
25		* @param string $password User credentials
26		* @param array $options Options to pass to the PDO constructor
27		* @return SQLConnection The datasource resource
28		*/
29		public static function register($name, $dsn, $username=null, $password=null, $options=[]){
30		return self::$connections[$name] = new SQLConnection($dsn, $username, $password, $options);
31		}
32
33		/**
34		* Register the default datasource
35		* @param string $dsn PDO DSN URL
36		* @param string $username User credentials
37		* @param string $password User credentials
38		* @param array $options Options to pass to the PDO constructor
39		* @return SQLConnection The datasource resource
40		*/
41		public static function connect($dsn, $username=null, $password=null, $options=[]){
42		return self::register('default', $dsn, $username, $password, $options);
43		}
44
45		/**
46		* Bind the default datasource to another named connection
47		* @param string $name The datasource name
48		* @return bool `true` if correctly changed
49		*/
50		public static function defaultTo($name){
51	View Code Duplication	if (isset(self::$connections[$name])){
52		self::$current = $name;
53		return true;
54		} else return false;
55		}
56
57		/**
58		* Close one or all (if no parameter passed) registered datasource connections
59		* @param string $name The datasource name, omit for close all of them
60		* @return bool `true` if one or more datasource where closed
61		*/
62		public static function close($name=null){
63		if ($name === null) {
64		foreach (self::$connections as $conn) $conn->close();
65		return true;
66	View Code Duplication	} else if (isset(self::$connections[$name])){
67		self::$connections[$name]->close();
68		return true;
69		} else return false;
70		}
71
72		/**
73		* Datasource connection accessor
74		* @param strinf $name The datasource name
75		* @return SQLConnect The datasource connection
76		*/
77		public static function using($name){
78		if (empty(self::$connections[$name])) throw new \Exception("[SQL] Unknown connection named '$name'.");
79		return self::$connections[$name];
80		}
81
82		/**
83		* Proxy all direct static calls to the SQL module to the `default` datasource
84		* @param string $method The method name
85		* @param array $args The method arguments
86		* @return mixed The method return value
87		*/
88		public static function __callStatic($method, $args){
89		if (empty(self::$connections[self::$current])) throw new \Exception("[SQL] No default connection defined.");
90		return call_user_func_array([self::$connections[self::$current],$method],$args);
91		}
92
93		}
94
95		// Default connection to in-memory ephemeral database
96		SQL::connect('sqlite::memory:');
97
98		class SQLConnection {
99
100		protected $connection = [],
101		$queries = [],
102		$last_exec_success = true;
103
104		public function __construct($dsn, $username=null, $password=null, $options=[]){
105		$this->connection = [
106		'dsn' => $dsn,
107		'pdo' => null,
108		'username' => $username,
109		'password' => $password,
110		'options' => array_merge([
111		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
112		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
113		PDO::ATTR_EMULATE_PREPARES => true,
114		PDO::MYSQL_ATTR_MULTI_STATEMENTS => true,
115		], $options),
116		];
117		// "The auto-commit mode cannot be changed for this driver" SQLite workaround
118		if (strpos($dsn,'sqlite:') === 0) {
119		$this->connection['options'] = $options;
120		}
121		}
122
123		public function close(){
124		$this->connection['pdo'] = null;
125		}
126
127		public function connection(){
128		if(empty($this->connection['pdo'])) {
129		try {
130		$this->connection['pdo'] = new PDO(
131		$this->connection['dsn'],
132		$this->connection['username'],
133		$this->connection['password'],
134		$this->connection['options']
135
136		);
137		SQL::trigger('connect',$this);
138		Event::trigger('core.sql.connect',$this);
139		} catch(Exception $e) {
140		$this->connection['pdo'] = null;
141		}
142		}
143		return $this->connection['pdo'];
144		}
145
146
147		/**
148		* Prepares a SQL query string
149		*
150		* @param string $query The query
151		* @param array $pdo_params The extra PDO parameters
152		*
153		* @return boolean
154		*/
155		public function prepare($query, $pdo_params=[]){
156		if(!$this->connection()) return false;
157		return isset($this->queries[$query]) ? $this->queries[$query] : ($this->queries[$query] = $this->connection()->prepare($query, $pdo_params));
158		}
159
160		public function exec($query, $params=[], $pdo_params=[]){
161		if(!$this->connection()) return false;
162
163		if (false==is_array($params)) $params = (array)$params;
164		$query = Filter::with('core.sql.query',$query);
165
166		if($statement = $this->prepare($query, $pdo_params)){
167		SQL::trigger('query',$query,$params,(bool)$statement);
168		Event::trigger('core.sql.query',$query,$params,(bool)$statement);
169
170		foreach ($params as $key => $val) {
171		$type = PDO::PARAM_STR;
172		if (is_bool($val)) {
173		$type = PDO::PARAM_BOOL;
174		} elseif (is_null($val)) {
175		$type = PDO::PARAM_NULL;
176		} elseif (is_int($val)) {
177		$type = PDO::PARAM_INT;
178		}
179
180		// bindValue need a 1-based numeric parameter
181		$statement->bindValue(is_numeric($key)?$key+1:':'.$key, $val, $type);
182		}
183		} else {
184		$error = $this->connection['pdo']->errorInfo();
185		SQL::trigger('error',$error[2], $query, $params, $error);
186		Event::trigger('core.sql.error',$error[2], $query, $params, $error);
187		return false;
188		}
189
190		$this->last_exec_success = $statement && $statement->execute();
191		return $statement;
192		}
193
194		public function value($query, $params=[], $column=0){
195		if(!$this->connection()) return false;
196
197		$res = $this->exec($query,$params);
198		return $res ? $res->fetchColumn($column) : null;
199		}
200
201		public function column($query, $params=[], $column=0){
202		if(!$this->connection()) return false;
203
204		$results = [];
205		$res = $this->exec($query,$params);
206
207		if (is_string($column))
208		while ($x = $res->fetch(PDO::FETCH_OBJ)) $results[] = $x->$column;
209		else
210		while ($x = $res->fetchColumn($column)) $results[] = $x;
211
212		return $results;
213		}
214
215		public function reduce($query, $params=[], $looper = null, $initial = null){
216		if(!$this->connection()) return false;
217
218		// ($query,$looper,$initial) shorthand
219		if (is_callable($params)) { $initial = $looper; $looper = $params; $params = []; }
220		if(( $res = $this->exec($query,$params, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]) ) && is_callable($looper) ){
221		while ($row = $res->fetchObject()) { $initial = $looper($initial, $row); }
222		return $initial;
223		} else return false;
224		}
225
226		public function each($query, $params=[], callable $looper = null){
227		if(!$this->connection()) return false;
228
229		// ($query,$looper) shorthand
230		if ($looper===null && is_callable($params)) {$looper = $params; $params = [];}
231		if( $res = $this->exec($query,$params, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]) ){
232		if(is_callable($looper)) {
233		while ($row = $res->fetchObject()) $looper($row);
234		return true;
235		} else return $res->fetchAll(PDO::FETCH_CLASS);
236		} else return false;
237		}
238
239		public function single($query, $params=[], callable $handler = null){
240		if(!$this->connection()) return false;
241
242		// ($query,$handler) shorthand
243		if ($handler===null && is_callable($params)) {$handler = $params; $params = [];}
244		if( $res = $this->exec($query,$params, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]) ){
245		if (is_callable($handler))
246		return $handler($res->fetchObject());
247		else
248		return $res->fetchObject();
249		}
250		}
251
252		public function run($script){
253		if(!$this->connection()) return false;
254
255		$sql_path = Options::get('database.sql.path',APP_DIR.'/sql');
256		$sql_sep = Options::get('database.sql.separator',';');
257		if (is_file($f = "$sql_path/$script.sql")){
258		$result = true;
259		foreach(explode($sql_sep,file_get_contents($f)) as $statement) {
260		$result = $this->exec($statement);
261		}
262		return $result;
263		} else return false;
264		}
265
266		public function all($query, $params=[], callable $looper = null){
267		if(!$this->connection()) return false;
268		return $this->each($query,$params,$looper);
269		}
270
271		public function delete($table, $pks=null, $pk='id', $inclusive=true){
272		if(!$this->connection()) return false;
273
274		if (null===$pks) {
275		return $this->exec("DELETE FROM `$table`");
276		} else {
277		return $this->exec("DELETE FROM `$table` WHERE `$pk` ".($inclusive ? "" : "NOT " )."IN (" . implode( ',', array_fill_keys( (array)$pks, '?' ) ) . ")",(array)$pks);
278		}
279		}
280
281		public function insert($table, $data, $pk='id'){
282		if(!$this->connection()) return false;
283
284		if (false==is_array($data)) $data = (array)$data;
285		$k = array_keys($data);
286		asort($k);
287		$pk_a = $k;
288		array_walk($pk_a,function(&$e){ $e = ':'.$e;});
289		$q = "INSERT INTO `$table` (`".implode('`,`',$k)."`) VALUES (".implode(',',$pk_a).")";
290		$this->exec($q,$data);
291		return $this->last_exec_success ? $this->connection()->lastInsertId($pk) : false;
292		}
293
294		public function updateWhere($table, $data, $where, $pk='id'){
295		if(!$this->connection()) return false;
296
297		if (false==is_array($data)) $data = (array)$data;
		0 ignored issues – show Coding Style Best Practice introduced 2016-07-11 16:24 UTC by Report Bug Copy Issue Report It seems like you are loosely comparing two booleans. Considering using the strict comparison `===` instead. When comparing two booleans, it is generally considered safer to use the strict comparison operator. Loading history...
298		if (empty($data)) return false;
299		$k = array_keys($data);
300		asort($k);
301
302		// Remove primary key from SET
303		array_walk($k,function(&$e) use ($pk) {
304		$e = ($e==$pk) ? null : "`$e`=:$e";
305		});
306
307		$q = "UPDATE `$table` SET ".implode(', ',array_filter($k))." WHERE $where";
308		$this->exec($q, $data);
309		$data = (object)$data;
310		return $this->last_exec_success ? $data->$pk : false;
311		}
312
313		public function update($table, $data, $pk='id', $extra_where=''){
314		return $this->updateWhere($table, $data, "`$pk`=:$pk $extra_where", $pk);
315		}
316
317		public function insertOrUpdate($table, $data=[], $pk='id', $extra_where=''){
318		if(!$this->connection()) return false;
319
320		if (false==is_array($data)) $data = (array)$data;
321		if (empty($data[$pk])) return $this->insert($table, $data);
322		if( (string) $this->value("SELECT `$pk` FROM `$table` WHERE `$pk`=? LIMIT 1", [$data[$pk]]) === (string) $data[$pk] ){
323		return $this->update($table, $data, $pk, $extra_where);
324		} else {
325		return $this->insert($table, $data, $pk);
326		}
327		}
328		}
329
330

caffeina-core / core

SQLConnection::prepare() A last analyzed 2019-04-16 08:44 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SQLConnection::prepare() A
last analyzed 2019-04-16 08:44 UTC