SQLConnection::insertOrUpdate() - Code Metrics - Inspection of "New: SQL::column can extract a single column from..." - caffeina-core/core - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 023ff5...b79f79 )

by Stefano

created 2016-05-24 15:28 UTC

SQLConnection::insertOrUpdate() B

↳ Parent: SQLConnection

Complexity

Conditions	5
Paths	7

Size

Total Lines	11
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	11
rs	8.8571
cc	5
eloc	8
nc	7
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 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 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;
  }

  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);
    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		public function column($query, $params=[], $column=0){
186		if(!$this->connection()) return false;
187
188		$results = [];
189		$res = $this->exec($query,$params);
190
191		if (is_string($column))
192		while ($x = $res->fetch(PDO::FETCH_OBJ)) $results[] = $x->$column;
193		else
194		while ($x = $res->fetchColumn($column)) $results[] = $x;
195
196		return $results;
197		}
198
199	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...
200		if(!$this->connection()) return false;
201
202		// ($query,$looper) shorthand
203		if ($looper===null && is_callable($params)) {$looper = $params; $params = [];}
204		if( $res = $this->exec($query,$params) ){
205		if(is_callable($looper))
206		while ($row = $res->fetchObject()) $looper($row);
207		else
208		return $res->fetchAll(PDO::FETCH_CLASS);
209		}
210		}
211
212	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...
213		if(!$this->connection()) return false;
214
215		// ($query,$handler) shorthand
216		if ($handler===null && is_callable($params)) {$handler = $params; $params = [];}
217		if( $res = $this->exec($query,$params) ){
218		if (is_callable($handler))
219		$handler($res->fetchObject());
220		else
221		return $res->fetchObject();
222		}
223		}
224
225		public function run($script){
226		if(!$this->connection()) return false;
227
228		$sql_path = Options::get('database.sql.path',APP_DIR.'/sql');
229		$sql_sep = Options::get('database.sql.separator',';');
230		if (is_file($f = "$sql_path/$script.sql")){
231		$result = true;
232		foreach(explode($sql_sep,file_get_contents($f)) as $statement) {
233		$result = $this->exec($statement);
234		}
235		return $result;
236		} else return false;
237		}
238
239		public function all($query, $params=[], callable $looper = null){
240		if(!$this->connection()) return false;
241		return $this->each($query,$params,$looper);
242		}
243
244		public function delete($table, $pks=null, $pk='id', $inclusive=true){
245		if(!$this->connection()) return false;
246
247		if (null===$pks) {
248		return $this->exec("DELETE FROM `$table`");
249		} else {
250		return $this->exec("DELETE FROM `$table` WHERE `$pk` ".($inclusive ? "" : "NOT " )."IN (" . implode( ',', array_fill_keys( (array)$pks, '?' ) ) . ")",(array)$pks);
251		}
252		}
253
254		public function insert($table, $data){
255		if(!$this->connection()) return false;
256
257		if (false==is_array($data)) $data = (array)$data;
258		$k = array_keys($data);
259		asort($k);
260		$pk = $k;
261		array_walk($pk,function(&$e){ $e = ':'.$e;});
262		$q = "INSERT INTO `$table` (`".implode('`,`',$k)."`) VALUES (".implode(',',$pk).")";
263		$this->exec($q,$data);
264		return $this->last_exec_success ? $this->connection()->lastInsertId() : false;
265		}
266
267		public function updateWhere($table, $data, $where, $pk='id'){
268		if(!$this->connection()) return false;
269
270		if (false==is_array($data)) $data = (array)$data;
271		if (empty($data)) return false;
272		$k = array_keys($data);
273		asort($k);
274
275		// Remove primary key from SET
276		array_walk($k,function(&$e) use ($pk) {
277		$e = ($e==$pk) ? null : "`$e`=:$e";
278		});
279
280		$q = "UPDATE `$table` SET ".implode(', ',array_filter($k))." WHERE $where";
281		$this->exec($q,$data);
282		return $this->last_exec_success;
283		}
284
285		public function update($table, $data, $pk='id', $extra_where=''){
286		return $this->updateWhere($table, $data, "`$pk`=:$pk $extra_where", $pk);
287		}
288
289		public function insertOrUpdate($table, $data=[], $pk='id', $extra_where=''){
290		if(!$this->connection()) return false;
291
292		if (false==is_array($data)) $data = (array)$data;
293		if (empty($data[$pk])) return $this->insert($table, $data);
294		if( (string) $this->value("SELECT `$pk` FROM `$table` WHERE `$pk`=? LIMIT 1", [$data[$pk]]) === (string) $data[$pk] ){
295		return $this->update($table, $data, $pk, $extra_where);
296		} else {
297		return $this->insert($table, $data);
298		}
299		}
300		}
301
302

caffeina-core / core

Push — master ( 023ff5...b79f79 )

SQLConnection::insertOrUpdate() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like