SQLConnection::updateWhere() - Code Metrics - Inspection of "SQL::update no longer auto-set primary key" - caffeina-core/core - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d28f2e...f183bf )

by Stefano

created 2016-03-16 11:36 UTC

SQLConnection::updateWhere() B

↳ Parent: SQLConnection

Complexity

Conditions	5
Paths	5

Size

Total Lines	17
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
c	2
b	0
f	0
dl	0
loc	17
rs	8.8571
cc	5
eloc	11
nc	5
nop	4

<?php

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


class SQL {
  use Module;
  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,
      ],$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']

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

  public function prepare($query){
    if(!$this->connection()) return false;
    return isset($this->queries[$query]) ? $this->queries[$query] : ($this->queries[$query] = $this->connection()->prepare($query));
  }

  public function exec($query, $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)){
      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 {
      Event::trigger('core.sql.error',$query,$params);
      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 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) ){
      if(is_callable($looper))
        while ($row = $res->fetchObject()) $looper($row);
      else
        return $res->fetchAll(PDO::FETCH_CLASS);
    }
  }

  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) ){
        if (is_callable($handler))
          $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=[]){
    if(!$this->connection()) return false;

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

  protected 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);
    return $this->last_exec_success;
  }

  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);
    }
  }
}



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.it
11		*/
12
13
14		class SQL {
15		use Module;
16		protected static $connections = [],
17		$current = 'default';
18
19		/**
20		* Register a new datasource
21		* @param string $name The assigned name for the datasource
22		* @param string $dsn PDO DSN URL
23		* @param string $username User credentials
24		* @param string $password User credentials
25		* @param array $options Options to pass to the PDO constructor
26		* @return SQLConnection The datasource resource
27		*/
28		public static function register($name, $dsn, $username=null, $password=null, $options=[]){
29		return self::$connections[$name] = new SQLConnection($dsn, $username, $password, $options);
30		}
31
32		/**
33		* Register the default datasource
34		* @param string $dsn PDO DSN URL
35		* @param string $username User credentials
36		* @param string $password User credentials
37		* @param array $options Options to pass to the PDO constructor
38		* @return SQLConnection The datasource resource
39		*/
40		public static function connect($dsn, $username=null, $password=null, $options=[]){
41		return self::register('default', $dsn, $username, $password, $options);
42		}
43
44		/**
45		* Bind the default datasource to another named connection
46		* @param string $name The datasource name
47		* @return bool `true` if correctly changed
48		*/
49		public static function defaultTo($name){
50	View Code Duplication	if (isset(self::$connections[$name])){
51		self::$current = $name;
52		return true;
53		} else return false;
54		}
55
56		/**
57		* Close one or all (if no parameter passed) registered datasource connections
58		* @param string $name The datasource name, omit for close all of them
59		* @return bool `true` if one or more datasource where closed
60		*/
61		public static function close($name=null){
62		if ($name === null) {
63		foreach (self::$connections as $conn) $conn->close();
64		return true;
65	View Code Duplication	} else if (isset(self::$connections[$name])){
66		self::$connections[$name]->close();
67		return true;
68		} else return false;
69		}
70
71		/**
72		* Datasource connection accessor
73		* @param strinf $name The datasource name
74		* @return SQLConnect The datasource connection
75		*/
76		public static function using($name){
77		if (empty(self::$connections[$name])) throw new \Exception("[SQL] Unknown connection named '$name'.");
78		return self::$connections[$name];
79		}
80
81		/**
82		* Proxy all direct static calls to the SQL module to the `default` datasource
83		* @param string $method The method name
84		* @param array $args The method arguments
85		* @return mixed The method return value
86		*/
87		public static function __callStatic($method, $args){
88		if (empty(self::$connections[self::$current])) throw new \Exception("[SQL] No default connection defined.");
89		return call_user_func_array([self::$connections[self::$current],$method],$args);
90		}
91
92		}
93
94		// Default connection to in-memory ephemeral database
95		SQL::connect('sqlite::memory:');
96
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		],$options),
115		];
116		// "The auto-commit mode cannot be changed for this driver" SQLite workaround
117		if (strpos($dsn,'sqlite:') === 0) {
118		$this->connection['options'] = $options;
119		}
120		}
121
122		public function close(){
123		$this->connection['pdo'] = null;
124		}
125
126		public function connection(){
127		if(empty($this->connection['pdo'])) {
128		try {
129		$this->connection['pdo'] = new PDO(
130		$this->connection['dsn'],
131		$this->connection['username'],
132		$this->connection['password'],
133		$this->connection['options']
134
135		);
136		Event::trigger('core.sql.connect',$this);
137		} catch(Exception $e) {
138		$this->connection['pdo'] = null;
139		}
140		}
141		return $this->connection['pdo'];
142		}
143
144		public function prepare($query){
145		if(!$this->connection()) return false;
146		return isset($this->queries[$query]) ? $this->queries[$query] : ($this->queries[$query] = $this->connection()->prepare($query));
147		}
148
149		public function exec($query, $params=[]){
150		if(!$this->connection()) return false;
151
152		if (false==is_array($params)) $params = (array)$params;
153		$query = Filter::with('core.sql.query',$query);
154		if($statement = $this->prepare($query)){
155		Event::trigger('core.sql.query',$query,$params,(bool)$statement);
156
157		foreach ($params as $key => $val) {
158		$type = PDO::PARAM_STR;
159		if (is_bool($val)) {
160		$type = PDO::PARAM_BOOL;
161		} elseif (is_null($val)) {
162		$type = PDO::PARAM_NULL;
163		} elseif (is_int($val)) {
164		$type = PDO::PARAM_INT;
165		}
166		// bindValue need a 1-based numeric parameter
167		$statement->bindValue(is_numeric($key)?$key+1:':'.$key, $val, $type);
168		}
169		} else {
170		Event::trigger('core.sql.error',$query,$params);
171		return false;
172		}
173
174		$this->last_exec_success = $statement && $statement->execute();
175		return $statement;
176		}
177
178		public function value($query, $params=[], $column=0){
179		if(!$this->connection()) return false;
180
181		$res = $this->exec($query,$params);
182		return $res ? $res->fetchColumn($column) : null;
183		}
184
185	View Code Duplication	public function each($query, $params=[], callable $looper = null){
		0 ignored issues – show Duplication introduced 2016-02-26 12:20 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
186		if(!$this->connection()) return false;
187
188		// ($query,$looper) shorthand
189		if ($looper===null && is_callable($params)) {$looper = $params; $params = [];}
190		if( $res = $this->exec($query,$params) ){
191		if(is_callable($looper))
192		while ($row = $res->fetchObject()) $looper($row);
193		else
194		return $res->fetchAll(PDO::FETCH_CLASS);
195		}
196		}
197
198	View Code Duplication	public function single($query, $params=[], callable $handler = null){
		0 ignored issues – show Duplication introduced 2016-02-26 12:20 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
199		if(!$this->connection()) return false;
200
201		// ($query,$handler) shorthand
202		if ($handler===null && is_callable($params)) {$handler = $params; $params = [];}
203		if( $res = $this->exec($query,$params) ){
204		if (is_callable($handler))
205		$handler($res->fetchObject());
206		else
207		return $res->fetchObject();
208		}
209		}
210
211		public function run($script){
212		if(!$this->connection()) return false;
213
214		$sql_path = Options::get('database.sql.path',APP_DIR.'/sql');
215		$sql_sep = Options::get('database.sql.separator',';');
216		if (is_file($f = "$sql_path/$script.sql")){
217		$result = true;
218		foreach(explode($sql_sep,file_get_contents($f)) as $statement) {
219		$result = $this->exec($statement);
220		}
221		return $result;
222		} else return false;
223		}
224
225		public function all($query, $params=[], callable $looper = null){
226		if(!$this->connection()) return false;
227		return $this->each($query,$params,$looper);
228		}
229
230		public function delete($table, $pks=null, $pk='id', $inclusive=true){
231		if(!$this->connection()) return false;
232
233		if (null===$pks) {
234		return $this->exec("DELETE FROM `$table`");
235		} else {
236		return $this->exec("DELETE FROM `$table` WHERE `$pk` ".($inclusive ? "" : "NOT " )."IN (" . implode( ',', array_fill_keys( (array)$pks, '?' ) ) . ")",(array)$pks);
237		}
238		}
239
240		public function insert($table, $data=[]){
241		if(!$this->connection()) return false;
242
243		if (false==is_array($data)) $data = (array)$data;
244		$k = array_keys($data);
245		asort($k);
246		$pk = $k;
247		array_walk($pk,function(&$e){ $e = ':'.$e;});
248		$q = "INSERT INTO `$table` (`".implode('`,`',$k)."`) VALUES (".implode(',',$pk).")";
249		$this->exec($q,$data);
250		return $this->last_exec_success ? $this->connection()->lastInsertId() : false;
251		}
252
253		protected function updateWhere($table, $data=[], $where, $pk='id'){
254		if(!$this->connection()) return false;
255
256		if (false==is_array($data)) $data = (array)$data;
257		if (empty($data)) return false;
258		$k = array_keys($data);
259		asort($k);
260
261		// Remove primary key from SET
262		array_walk($k,function(&$e) use ($pk) {
263		$e = ($e==$pk) ? null : "`$e`=:$e";
264		});
265
266		$q = "UPDATE `$table` SET ".implode(', ',array_filter($k))." WHERE $where";
267		$this->exec($q,$data);
268		return $this->last_exec_success;
269		}
270
271		public function update($table, $data=[], $pk='id', $extra_where=''){
272		return $this->updateWhere($table, $data, "`$pk`=:$pk $extra_where", $pk);
273		}
274
275		public function insertOrUpdate($table, $data=[], $pk='id', $extra_where=''){
276		if(!$this->connection()) return false;
277
278		if (false==is_array($data)) $data = (array)$data;
279		if (empty($data[$pk])) return $this->insert($table, $data);
280		if( (string) $this->value("SELECT `$pk` FROM `$table` WHERE `$pk`=? LIMIT 1", [$data[$pk]]) === (string) $data[$pk] ){
281		return $this->update($table, $data, $pk, $extra_where);
282		} else {
283		return $this->insert($table, $data);
284		}
285		}
286		}
287
288

caffeina-core / core

Push — master ( d28f2e...f183bf )

SQLConnection::updateWhere() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like