Database::query() - Code Metrics - Inspection of "Move DatabaseQueryBuilder.php to new folder" - tnhfw/tnh-fw - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — 1.0.0-dev ( ace4b6...d422fd )

by nguereza

created 2020-02-22 19:01 UTC

Database::query() B

↳ Parent: Project

Complexity

Conditions	10
Paths	24

Size

Total Lines	52
Code Lines	30

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	1	Features	1

Metric	Value
cc	10
eloc	30
c	1
b	1
f	1
nc	24
nop	3
dl	0
loc	52
rs	7.6666

How to fix Long Method Complexity

<?php
    defined('ROOT_PATH') || exit('Access denied');
  /**
   * TNH Framework
   *
   * A simple PHP framework using HMVC architecture
   *
   * This content is released under the GNU GPL License (GPL)
   *
   * Copyright (C) 2017 Tony NGUEREZA
   *
   * This program is free software; you can redistribute it and/or
   * modify it under the terms of the GNU General Public License
   * as published by the Free Software Foundation; either version 3
   * of the License, or (at your option) any later version.
   *
   * This program is distributed in the hope that it will be useful,
   * but WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   * GNU General Public License for more details.
   *
   * You should have received a copy of the GNU General Public License
   * along with this program; if not, write to the Free Software
   * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  */
  class Database{
	
  	/**
  	 * The PDO instance
  	 * @var object
  	*/
    private $pdo                 = null;
    
  	/**
  	 * The database name used for the application
  	 * @var string
  	*/
	  private $databaseName        = null;
	
  	/**
  	 * The number of rows returned by the last query
  	 * @var int
  	*/
    private $numRows             = 0;
	
  	/**
  	 * The last insert id for the primary key column that have auto increment or sequence
  	 * @var mixed
  	*/
    private $insertId            = null;
	
  	/**
  	 * The full SQL query statment after build for each command
  	 * @var string
  	*/
    private $query               = null;
	
  	/**
  	 * The result returned for the last query
  	 * @var mixed
  	*/
    private $result              = array();
	
  	/**
  	 * The cache default time to live in second. 0 means no need to use the cache feature
  	 * @var int
  	*/
  	private $cacheTtl             = 0;
	
  	/**
  	 * The cache current time to live. 0 means no need to use the cache feature
  	 * @var int
  	*/
    private $temporaryCacheTtl   = 0;
	
  	/**
  	 * The number of executed query for the current request
  	 * @var int
  	*/
    private $queryCount          = 0;
	
  	/**
  	 * The default data to be used in the statments query INSERT, UPDATE
  	 * @var array
  	*/
    private $data                = array();
	
  	/**
  	 * The database configuration
  	 * @var array
  	*/
    private $config              = array();
	
  	/**
  	 * The logger instance
  	 * @var object
  	 */
    private $logger              = null;

    /**
    * The cache instance
    * @var object
    */
    private $cacheInstance       = null;

    
  	/**
    * The DatabaseQueryBuilder instance
    * @var object
    */
    private $queryBuilder        = null;
    
    /**
    * The DatabaseQueryRunner instance
    * @var object
    */
    private $queryRunner         = null;


    /**
     * Construct new database
     * @param array $overwriteConfig the config to overwrite with the config set in database.php
     */
    public function __construct($overwriteConfig = array()){
        //Set Log instance to use
        $this->setLoggerFromParamOrCreateNewInstance(null);
		
    		//Set DatabaseQueryBuilder instance to use
    		$this->setQueryBuilderFromParamOrCreateNewInstance(null);

        //Set DatabaseQueryRunner instance to use
        $this->setQueryRunnerFromParamOrCreateNewInstance(null);

        //Set database configuration
        $this->setDatabaseConfiguration($overwriteConfig);
        
        //cache time to live
        $this->temporaryCacheTtl = $this->cacheTtl;
    }

    /**
     * This is used to connect to database
     * @return bool 
     */
    public function connect(){
      $config = $this->getDatabaseConfiguration();
      if (! empty($config)){
        try{
            $this->pdo = new PDO($this->getDsnFromDriver(), $config['username'], $config['password']);
            $this->pdo->exec("SET NAMES '" . $config['charset'] . "' COLLATE '" . $config['collation'] . "'");
            $this->pdo->exec("SET CHARACTER SET '" . $config['charset'] . "'");
            $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
            return true;
          }
          catch (PDOException $e){
            $this->logger->fatal($e->getMessage());
            show_error('Cannot connect to Database.');
            return false;
          }
      }
      return false;
    }


    /**
     * Return the number of rows returned by the current query
     * @return int
     */
    public function numRows(){
      return $this->numRows;
    }

    /**
     * Return the last insert id value
     * @return mixed
     */
    public function insertId(){
      return $this->insertId;
    }


    /**
     * Get the result of one record rows returned by the current query
     * @param  boolean $returnSQLQueryOrResultType if is boolean and true will return the SQL query string.
     * If is string will determine the result type "array" or "object"
     * @return mixed       the query SQL string or the record result
     */
    public function get($returnSQLQueryOrResultType = false){
      $this->getQueryBuilder()->limit(1);
      $query = $this->getAll(true);
      if ($returnSQLQueryOrResultType === true){
        return $query;
      } else {
        return $this->query($query, false, $returnSQLQueryOrResultType == 'array');
      }
    }

    /**
     * Get the result of record rows list returned by the current query
     * @param  boolean|string $returnSQLQueryOrResultType if is boolean and true will return the SQL query string.
     * If is string will determine the result type "array" or "object"
     * @return mixed       the query SQL string or the record result
     */
    public function getAll($returnSQLQueryOrResultType = false){
	   $query = $this->getQueryBuilder()->getQuery();
	   if ($returnSQLQueryOrResultType === true){
      	return $query;
      }
      return $this->query($query, true, $returnSQLQueryOrResultType == 'array');
    }

    /**
     * Insert new record in the database
     * @param  array   $data   the record data if is empty will use the $this->data array.
     * @param  boolean $escape  whether to escape or not the values
     * @return mixed          the insert id of the new record or null
     */
    public function insert($data = array(), $escape = true){
      if (empty($data) && $this->getData()){
        //as when using $this->setData() may be the data already escaped
        $escape = false;
        $data = $this->getData();
      }
      $query = $this->getQueryBuilder()->insert($data, $escape)->getQuery();
      $result = $this->query($query);
      if ($result){
        $this->insertId = $this->pdo->lastInsertId();
		    //if the table doesn't have the auto increment field or sequence, the value of 0 will be returned 
        return ! $this->insertId() ? true : $this->insertId();
      }
      return false;
    }

    /**
     * Update record in the database
     * @param  array   $data   the record data if is empty will use the $this->data array.
     * @param  boolean $escape  whether to escape or not the values
     * @return mixed          the update status
     */
    public function update($data = array(), $escape = true){
      if (empty($data) && $this->getData()){
        //as when using $this->setData() may be the data already escaped
        $escape = false;
        $data = $this->getData();
      }
      $query = $this->getQueryBuilder()->update($data, $escape)->getQuery();
      return $this->query($query);
    }

    /**
     * Delete the record in database
     * @return mixed the delete status
     */
    public function delete(){
		$query = $this->getQueryBuilder()->delete()->getQuery();
    	return $this->query($query);
    }

    /**
     * Set database cache time to live
     * @param integer $ttl the cache time to live in second
     * @return object        the current Database instance
     */
    public function setCache($ttl = 0){
      if ($ttl > 0){
        $this->cacheTtl = $ttl;
        $this->temporaryCacheTtl = $ttl;
      }
      return $this;
    }
	
	/**
	 * Enabled cache temporary for the current query not globally	
	 * @param  integer $ttl the cache time to live in second
	 * @return object        the current Database instance
	 */
  	public function cached($ttl = 0){
        if ($ttl > 0){
          $this->temporaryCacheTtl = $ttl;
        }
        return $this;
    }

    /**
     * Escape the data before execute query useful for security.
     * @param  mixed $data the data to be escaped
     * @param boolean $escaped whether we can do escape of not 
     * @return mixed       the data after escaped or the same data if not
     */
    public function escape($data, $escaped = true){
      return $escaped ? 
                      $this->pdo->quote(trim($data)) 
                      : $data; 
    }

    /**
     * Return the number query executed count for the current request
     * @return int
     */
    public function queryCount(){
      return $this->queryCount;
    }

    /**
     * Return the current query SQL string
     * @return string
     */
    public function getQuery(){
      return $this->query;
    }

    /**
     * Return the application database name
     * @return string
     */
    public function getDatabaseName(){
      return $this->databaseName;
    }

    /**
     * Return the PDO instance
     * @return object
     */
    public function getPdo(){
      return $this->pdo;
    }

    /**
     * Set the PDO instance
     * @param object $pdo the pdo object
	 * @return object Database
     */
    public function setPdo(PDO $pdo){
      $this->pdo = $pdo;
      return $this;
    }


    /**
     * Return the Log instance
     * @return Log
     */
    public function getLogger(){
      return $this->logger;
    }

    /**
     * Set the log instance
     * @param Log $logger the log object
	 * @return object Database
     */
    public function setLogger($logger){
      $this->logger = $logger;
      return $this;
    }

     /**
     * Return the cache instance
     * @return CacheInterface
     */
    public function getCacheInstance(){
      return $this->cacheInstance;
    }

    /**
     * Set the cache instance
     * @param CacheInterface $cache the cache object
	 * @return object Database
     */
    public function setCacheInstance($cache){
      $this->cacheInstance = $cache;
      return $this;
    }
	
	
	   /**
     * Return the DatabaseQueryBuilder instance
     * @return object DatabaseQueryBuilder
     */
    public function getQueryBuilder(){
      return $this->queryBuilder;
    }

    /**
     * Set the DatabaseQueryBuilder instance
     * @param object DatabaseQueryBuilder $queryBuilder the DatabaseQueryBuilder object
     */
    public function setQueryBuilder(DatabaseQueryBuilder $queryBuilder){
      $this->queryBuilder = $queryBuilder;
      return $this;
    }
    
    /**
     * Return the DatabaseQueryRunner instance
     * @return object DatabaseQueryRunner
     */
    public function getQueryRunner(){
      return $this->queryRunner;
    }

    /**
     * Set the DatabaseQueryRunner instance
     * @param object DatabaseQueryRunner $queryRunner the DatabaseQueryRunner object
     */
    public function setQueryRunner(DatabaseQueryRunner $queryRunner){
      $this->queryRunner = $queryRunner;
      return $this;
    }

    /**
     * Return the data to be used for insert, update, etc.
     * @return array
     */
    public function getData(){
      return $this->data;
    }

    /**
     * Set the data to be used for insert, update, etc.
     * @param string|array $key the data key identified
     * @param mixed $value the data value
     * @param boolean $escape whether to escape or not the $value
     * @return object        the current Database instance
     */
    public function setData($key, $value = null, $escape = true){
  	  if(is_array($key)){
    		foreach($key as $k => $v){
    			$this->setData($k, $v, $escape);
    		}	
  	  } else {
        $this->data[$key] = $this->escape($value, $escape);
  	  }
      return $this;
    }

     /**
     * Execute an SQL query
     * @param  string  $query the query SQL string
     * @param  boolean $returnAsList  indicate whether to return all record or just one row 
     * @param  boolean $returnAsArray return the result as array or not
     * @return mixed         the query result
     */
    public function query($query, $returnAsList = true, $returnAsArray = false){
      $this->reset();
      $this->query = preg_replace('/\s\s+|\t\t+/', ' ', trim($query));
      //If is the SELECT query
      $isSqlSELECTQuery = stristr($this->query, 'SELECT') !== false;

      //cache expire time
      $cacheExpire = $this->temporaryCacheTtl;
      
      //return to the initial cache time
      $this->temporaryCacheTtl = $this->cacheTtl;
      
      //config for cache
      $cacheEnable = get_config('cache_enable');
      
      //the database cache content
      $cacheContent = null;

      //if can use cache feature for this query
      $dbCacheStatus = $cacheEnable && $cacheExpire > 0;
    
      if ($dbCacheStatus && $isSqlSELECTQuery){
          $this->logger->info('The cache is enabled for this query, try to get result from cache'); 
          $cacheContent = $this->getCacheContentForQuery($query, $returnAsList, $returnAsArray);  
      }
      
      if ( !$cacheContent){
  	   	//count the number of query execution to server
        $this->queryCount++;
        
        $this->queryRunner->setQuery($query);
        $this->queryRunner->setReturnType($returnAsList);
        $this->queryRunner->setReturnAsArray($returnAsArray);
        
        $queryResult = $this->queryRunner->execute();
        if (is_object($queryResult)){
            $this->result  = $queryResult->getResult();
            $this->numRows = $queryResult->getNumRows();
            if ($isSqlSELECTQuery && $dbCacheStatus){
                $key = $this->getCacheKeyForQuery($this->query, $returnAsList, $returnAsArray);
                $this->setCacheContentForQuery($this->query, $key, $this->result, $cacheExpire);
            if (! $this->result){
              $this->logger->info('No result where found for the query [' . $query . ']');
            }
          }
        }
      } else if ($isSqlSELECTQuery){
          $this->logger->info('The result for query [' .$this->query. '] already cached use it');
          $this->result = $cacheContent;
          $this->numRows = count($this->result);
      }
      return $this->result;
    }
	
	
	 /**
	 * Return the database configuration
	 * @return array
	 */
  	public  function getDatabaseConfiguration(){
  	  return $this->config;
  	}

   /**
    * Setting the database configuration using the configuration file and additional configuration from param
    * @param array $overwriteConfig the additional configuration to overwrite with the existing one
    * @param boolean $useConfigFile whether to use database configuration file
	  * @return object Database
    */
    public function setDatabaseConfiguration(array $overwriteConfig = array(), $useConfigFile = true){
        $db = array();
        if ($useConfigFile && file_exists(CONFIG_PATH . 'database.php')){
            //here don't use require_once because somewhere user can create database instance directly
            require CONFIG_PATH . 'database.php';
        }
        
        //merge with the parameter  
        $db = array_merge($db, $overwriteConfig);
        
        //default configuration
        $config = array(
          'driver' => 'mysql',
          'username' => 'root',
          'password' => '',
          'database' => '',
          'hostname' => 'localhost',
          'charset' => 'utf8',
          'collation' => 'utf8_general_ci',
          'prefix' => '',
          'port' => ''
        );
		
    	$config = array_merge($config, $db);
    	//determine the port using the hostname like localhost:3307
      //hostname will be "localhost", and port "3307"
      $p = explode(':', $config['hostname']);
  	  if (count($p) >= 2){
  		  $config['hostname'] = $p[0];
  		  $config['port'] = $p[1];
  		}
		
		 $this->databaseName = $config['database'];
		 $this->config = $config;
		 $this->logger->info(
								'The database configuration are listed below: ' 
								. stringfy_vars(array_merge(
															$this->config, 
															array('password' => string_hidden($this->config['password']))
												))
							);
	  
		 //Now connect to the database
		 $this->connect();
		 
     //do update of QueryRunner and Builder
     $this->updateQueryBuilderAndRunnerProperties();

		 return $this;
    }

    /**
     * Close the connexion
     */
    public function close(){
      $this->pdo = null;
    }

    /**
     * Update the DatabaseQueryBuilder and DatabaseQueryRunner properties
     * @return void
     */
    protected function updateQueryBuilderAndRunnerProperties(){
       //update queryBuilder with some properties needed
     if(is_object($this->queryBuilder)){
        $this->queryBuilder->setDriver($this->config['driver']);
        $this->queryBuilder->setPrefix($this->config['prefix']);
        $this->queryBuilder->setPdo($this->pdo);
     }

      //update queryRunner with some properties needed
     if(is_object($this->queryRunner)){
        $this->queryRunner->setDriver($this->config['driver']);
        $this->queryRunner->setPdo($this->pdo);
     }
    }
	

    /**
     * This method is used to get the PDO DSN string using the configured driver
     * @return string the DSN string
     */
    protected function getDsnFromDriver(){
      $config = $this->getDatabaseConfiguration();
      if (! empty($config)){
        $driver = $config['driver'];
        $driverDsnMap = array(
                              'mysql' => 'mysql:host=' . $config['hostname'] . ';' 
                                          . (($config['port']) != '' ? 'port=' . $config['port'] . ';' : '') 
                                          . 'dbname=' . $config['database'],
                              'pgsql' => 'pgsql:host=' . $config['hostname'] . ';' 
                                          . (($config['port']) != '' ? 'port=' . $config['port'] . ';' : '')
                                          . 'dbname=' . $config['database'],
                              'sqlite' => 'sqlite:' . $config['database'],
                              'oracle' => 'oci:dbname=' . $config['hostname'] 
                                            . (($config['port']) != '' ? ':' . $config['port'] : '')
                                            . '/' . $config['database']
                              );
        return isset($driverDsnMap[$driver]) ? $driverDsnMap[$driver] : '';
      }                   
      return null;
    }

    /**
     * Get the cache content for this query
     * @see Database::query
     *      
     * @return mixed
     */
    protected function getCacheContentForQuery($query, $returnAsList, $returnAsArray){
        $cacheKey = $this->getCacheKeyForQuery($query, $returnAsList, $returnAsArray);
        if (! is_object($this->cacheInstance)){
    			//can not call method with reference in argument
    			//like $this->setCacheInstance(& get_instance()->cache);
    			//use temporary variable
    			$instance = & get_instance()->cache;
    			$this->cacheInstance = $instance;
        }
        return $this->cacheInstance->get($cacheKey);
    }

    /**
     * Save the result of query into cache
     * @param string $query  the SQL query
     * @param string $key    the cache key
     * @param mixed $result the query result to save
     * @param int $expire the cache TTL
     */
     protected function setCacheContentForQuery($query, $key, $result, $expire){
        $this->logger->info('Save the result for query [' .$query. '] into cache for future use');
        if (! is_object($this->cacheInstance)){
  				//can not call method with reference in argument
  				//like $this->setCacheInstance(& get_instance()->cache);
  				//use temporary variable
  				$instance = & get_instance()->cache;
  				$this->cacheInstance = $instance;
  			}
        $this->cacheInstance->set($key, $result, $expire);
     }

    
	 /**
     * Return the cache key for the given query
     * @see Database::query
     * 
     *  @return string
     */
    protected function getCacheKeyForQuery($query, $returnAsList, $returnAsArray){
      return md5($query . $returnAsList . $returnAsArray);
    }
    
	   /**
     * Set the Log instance using argument or create new instance
     * @param object $logger the Log instance if not null
     */
    protected function setLoggerFromParamOrCreateNewInstance(Log $logger = null){
      if ($logger !== null){
        $this->logger = $logger;
      }
      else{
          $this->logger =& class_loader('Log', 'classes');
          $this->logger->setLogger('Library::Database');
      }
    }
	
   /**
   * Set the DatabaseQueryBuilder instance using argument or create new instance
   * @param object $queryBuilder the DatabaseQueryBuilder instance if not null
   */
	protected function setQueryBuilderFromParamOrCreateNewInstance(DatabaseQueryBuilder $queryBuilder = null){
	  if ($queryBuilder !== null){
        $this->queryBuilder = $queryBuilder;
	  }
	  else{
		  $this->queryBuilder =& class_loader('DatabaseQueryBuilder', 'classes/database');
	  }
	}

  /**
   * Set the DatabaseQueryRunner instance using argument or create new instance
   * @param object $queryRunner the DatabaseQueryRunner instance if not null
   */
  protected function setQueryRunnerFromParamOrCreateNewInstance(DatabaseQueryRunner $queryRunner = null){
    if ($queryRunner !== null){
        $this->queryRunner = $queryRunner;
    }
    else{
      $this->queryRunner =& class_loader('DatabaseQueryRunner', 'classes/database');
    }
  }

    /**
     * Reset the database class attributs to the initail values before each query.
     */
    private function reset(){
	   //query builder reset
      $this->getQueryBuilder()->reset();
      $this->numRows  = 0;
      $this->insertId = null;
      $this->query    = null;
      $this->result   = array();
      $this->data     = array();
    }

    /**
     * The class destructor
     */
    public function __destruct(){
      $this->pdo = null;
    }

}


1			<?php
2			defined('ROOT_PATH') \|\| exit('Access denied');
3			/**
4			* TNH Framework
5			*
6			* A simple PHP framework using HMVC architecture
7			*
8			* This content is released under the GNU GPL License (GPL)
9			*
10			* Copyright (C) 2017 Tony NGUEREZA
11			*
12			* This program is free software; you can redistribute it and/or
13			* modify it under the terms of the GNU General Public License
14			* as published by the Free Software Foundation; either version 3
15			* of the License, or (at your option) any later version.
16			*
17			* This program is distributed in the hope that it will be useful,
18			* but WITHOUT ANY WARRANTY; without even the implied warranty of
19			* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20			* GNU General Public License for more details.
21			*
22			* You should have received a copy of the GNU General Public License
23			* along with this program; if not, write to the Free Software
24			* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
25			*/
26			class Database{
27
28			/**
29			* The PDO instance
30			* @var object
31			*/
32			private $pdo = null;
33
34			/**
35			* The database name used for the application
36			* @var string
37			*/
38			private $databaseName = null;
39
40			/**
41			* The number of rows returned by the last query
42			* @var int
43			*/
44			private $numRows = 0;
45
46			/**
47			* The last insert id for the primary key column that have auto increment or sequence
48			* @var mixed
49			*/
50			private $insertId = null;
51
52			/**
53			* The full SQL query statment after build for each command
54			* @var string
55			*/
56			private $query = null;
57
58			/**
59			* The result returned for the last query
60			* @var mixed
61			*/
62			private $result = array();
63
64			/**
65			* The cache default time to live in second. 0 means no need to use the cache feature
66			* @var int
67			*/
68			private $cacheTtl = 0;
69
70			/**
71			* The cache current time to live. 0 means no need to use the cache feature
72			* @var int
73			*/
74			private $temporaryCacheTtl = 0;
75
76			/**
77			* The number of executed query for the current request
78			* @var int
79			*/
80			private $queryCount = 0;
81
82			/**
83			* The default data to be used in the statments query INSERT, UPDATE
84			* @var array
85			*/
86			private $data = array();
87
88			/**
89			* The database configuration
90			* @var array
91			*/
92			private $config = array();
93
94			/**
95			* The logger instance
96			* @var object
97			*/
98			private $logger = null;
99
100			/**
101			* The cache instance
102			* @var object
103			*/
104			private $cacheInstance = null;
105
106
107			/**
108			* The DatabaseQueryBuilder instance
109			* @var object
110			*/
111			private $queryBuilder = null;
112
113			/**
114			* The DatabaseQueryRunner instance
115			* @var object
116			*/
117			private $queryRunner = null;
118
119
120			/**
121			* Construct new database
122			* @param array $overwriteConfig the config to overwrite with the config set in database.php
123			*/
124			public function __construct($overwriteConfig = array()){
125			//Set Log instance to use
126			$this->setLoggerFromParamOrCreateNewInstance(null);
127
128			//Set DatabaseQueryBuilder instance to use
129			$this->setQueryBuilderFromParamOrCreateNewInstance(null);
130
131			//Set DatabaseQueryRunner instance to use
132			$this->setQueryRunnerFromParamOrCreateNewInstance(null);
133
134			//Set database configuration
135			$this->setDatabaseConfiguration($overwriteConfig);
136
137			//cache time to live
138			$this->temporaryCacheTtl = $this->cacheTtl;
139			}
140
141			/**
142			* This is used to connect to database
143			* @return bool
144			*/
145			public function connect(){
146			$config = $this->getDatabaseConfiguration();
147			if (! empty($config)){
148			try{
149			$this->pdo = new PDO($this->getDsnFromDriver(), $config['username'], $config['password']);
150			$this->pdo->exec("SET NAMES '" . $config['charset'] . "' COLLATE '" . $config['collation'] . "'");
151			$this->pdo->exec("SET CHARACTER SET '" . $config['charset'] . "'");
152			$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
153			return true;
154			}
155			catch (PDOException $e){
156			$this->logger->fatal($e->getMessage());
157			show_error('Cannot connect to Database.');
158			return false;
159			}
160			}
161			return false;
162			}
163
164
165			/**
166			* Return the number of rows returned by the current query
167			* @return int
168			*/
169			public function numRows(){
170			return $this->numRows;
171			}
172
173			/**
174			* Return the last insert id value
175			* @return mixed
176			*/
177			public function insertId(){
178			return $this->insertId;
179			}
180
181
182			/**
183			* Get the result of one record rows returned by the current query
184			* @param boolean $returnSQLQueryOrResultType if is boolean and true will return the SQL query string.
185			* If is string will determine the result type "array" or "object"
186			* @return mixed the query SQL string or the record result
187			*/
188			public function get($returnSQLQueryOrResultType = false){
189			$this->getQueryBuilder()->limit(1);
190			$query = $this->getAll(true);
191			if ($returnSQLQueryOrResultType === true){
192			return $query;
193			} else {
194			return $this->query($query, false, $returnSQLQueryOrResultType == 'array');
195			}
196			}
197
198			/**
199			* Get the result of record rows list returned by the current query
200			* @param boolean\|string $returnSQLQueryOrResultType if is boolean and true will return the SQL query string.
201			* If is string will determine the result type "array" or "object"
202			* @return mixed the query SQL string or the record result
203			*/
204			public function getAll($returnSQLQueryOrResultType = false){
205			$query = $this->getQueryBuilder()->getQuery();
206			if ($returnSQLQueryOrResultType === true){
207			return $query;
208			}
209			return $this->query($query, true, $returnSQLQueryOrResultType == 'array');
210			}
211
212			/**
213			* Insert new record in the database
214			* @param array $data the record data if is empty will use the $this->data array.
215			* @param boolean $escape whether to escape or not the values
216			* @return mixed the insert id of the new record or null
217			*/
218			public function insert($data = array(), $escape = true){
219			if (empty($data) && $this->getData()){
220			//as when using $this->setData() may be the data already escaped
221			$escape = false;
222			$data = $this->getData();
223			}
224			$query = $this->getQueryBuilder()->insert($data, $escape)->getQuery();
225			$result = $this->query($query);
226			if ($result){
227			$this->insertId = $this->pdo->lastInsertId();
228			//if the table doesn't have the auto increment field or sequence, the value of 0 will be returned
229			return ! $this->insertId() ? true : $this->insertId();
230			}
231			return false;
232			}
233
234			/**
235			* Update record in the database
236			* @param array $data the record data if is empty will use the $this->data array.
237			* @param boolean $escape whether to escape or not the values
238			* @return mixed the update status
239			*/
240			public function update($data = array(), $escape = true){
241			if (empty($data) && $this->getData()){
242			//as when using $this->setData() may be the data already escaped
243			$escape = false;
244			$data = $this->getData();
245			}
246			$query = $this->getQueryBuilder()->update($data, $escape)->getQuery();
247			return $this->query($query);
248			}
249
250			/**
251			* Delete the record in database
252			* @return mixed the delete status
253			*/
254			public function delete(){
255			$query = $this->getQueryBuilder()->delete()->getQuery();
256			return $this->query($query);
257			}
258
259			/**
260			* Set database cache time to live
261			* @param integer $ttl the cache time to live in second
262			* @return object the current Database instance
263			*/
264			public function setCache($ttl = 0){
265			if ($ttl > 0){
266			$this->cacheTtl = $ttl;
267			$this->temporaryCacheTtl = $ttl;
268			}
269			return $this;
270			}
271
272			/**
273			* Enabled cache temporary for the current query not globally
274			* @param integer $ttl the cache time to live in second
275			* @return object the current Database instance
276			*/
277			public function cached($ttl = 0){
278			if ($ttl > 0){
279			$this->temporaryCacheTtl = $ttl;
280			}
281			return $this;
282			}
283
284			/**
285			* Escape the data before execute query useful for security.
286			* @param mixed $data the data to be escaped
287			* @param boolean $escaped whether we can do escape of not
288			* @return mixed the data after escaped or the same data if not
289			*/
290			public function escape($data, $escaped = true){
291			return $escaped ?
292			$this->pdo->quote(trim($data))
293			: $data;
294			}
295
296			/**
297			* Return the number query executed count for the current request
298			* @return int
299			*/
300			public function queryCount(){
301			return $this->queryCount;
302			}
303
304			/**
305			* Return the current query SQL string
306			* @return string
307			*/
308			public function getQuery(){
309			return $this->query;
310			}
311
312			/**
313			* Return the application database name
314			* @return string
315			*/
316			public function getDatabaseName(){
317			return $this->databaseName;
318			}
319
320			/**
321			* Return the PDO instance
322			* @return object
323			*/
324			public function getPdo(){
325			return $this->pdo;
326			}
327
328			/**
329			* Set the PDO instance
330			* @param object $pdo the pdo object
331			* @return object Database
332			*/
333			public function setPdo(PDO $pdo){
334			$this->pdo = $pdo;
335			return $this;
336			}
337
338
339			/**
340			* Return the Log instance
341			* @return Log
342			*/
343			public function getLogger(){
344			return $this->logger;
345			}
346
347			/**
348			* Set the log instance
349			* @param Log $logger the log object
350			* @return object Database
351			*/
352			public function setLogger($logger){
353			$this->logger = $logger;
354			return $this;
355			}
356
357			/**
358			* Return the cache instance
359			* @return CacheInterface
360			*/
361			public function getCacheInstance(){
362			return $this->cacheInstance;
363			}
364
365			/**
366			* Set the cache instance
367			* @param CacheInterface $cache the cache object
368			* @return object Database
369			*/
370			public function setCacheInstance($cache){
371			$this->cacheInstance = $cache;
372			return $this;
373			}
374
375
376			/**
377			* Return the DatabaseQueryBuilder instance
378			* @return object DatabaseQueryBuilder
379			*/
380			public function getQueryBuilder(){
381			return $this->queryBuilder;
382			}
383
384			/**
385			* Set the DatabaseQueryBuilder instance
386			* @param object DatabaseQueryBuilder $queryBuilder the DatabaseQueryBuilder object
387			*/
388			public function setQueryBuilder(DatabaseQueryBuilder $queryBuilder){
389			$this->queryBuilder = $queryBuilder;
390			return $this;
391			}
392
393			/**
394			* Return the DatabaseQueryRunner instance
395			* @return object DatabaseQueryRunner
396			*/
397			public function getQueryRunner(){
398			return $this->queryRunner;
399			}
400
401			/**
402			* Set the DatabaseQueryRunner instance
403			* @param object DatabaseQueryRunner $queryRunner the DatabaseQueryRunner object
404			*/
405			public function setQueryRunner(DatabaseQueryRunner $queryRunner){
406			$this->queryRunner = $queryRunner;
407			return $this;
408			}
409
410			/**
411			* Return the data to be used for insert, update, etc.
412			* @return array
413			*/
414			public function getData(){
415			return $this->data;
416			}
417
418			/**
419			* Set the data to be used for insert, update, etc.
420			* @param string\|array $key the data key identified
421			* @param mixed $value the data value
422			* @param boolean $escape whether to escape or not the $value
423			* @return object the current Database instance
424			*/
425			public function setData($key, $value = null, $escape = true){
426			if(is_array($key)){
427			foreach($key as $k => $v){
428			$this->setData($k, $v, $escape);
429			}
430			} else {
431			$this->data[$key] = $this->escape($value, $escape);
432			}
433			return $this;
434			}
435
436			/**
437			* Execute an SQL query
438			* @param string $query the query SQL string
439			* @param boolean $returnAsList indicate whether to return all record or just one row
440			* @param boolean $returnAsArray return the result as array or not
441			* @return mixed the query result
442			*/
443			public function query($query, $returnAsList = true, $returnAsArray = false){
444			$this->reset();
445			$this->query = preg_replace('/\s\s+\|\t\t+/', ' ', trim($query));
446			//If is the SELECT query
447			$isSqlSELECTQuery = stristr($this->query, 'SELECT') !== false;
448
449			//cache expire time
450			$cacheExpire = $this->temporaryCacheTtl;
451
452			//return to the initial cache time
453			$this->temporaryCacheTtl = $this->cacheTtl;
454
455			//config for cache
456			$cacheEnable = get_config('cache_enable');
457
458			//the database cache content
459			$cacheContent = null;
460
461			//if can use cache feature for this query
462			$dbCacheStatus = $cacheEnable && $cacheExpire > 0;
463
464			if ($dbCacheStatus && $isSqlSELECTQuery){
465			$this->logger->info('The cache is enabled for this query, try to get result from cache');
466			$cacheContent = $this->getCacheContentForQuery($query, $returnAsList, $returnAsArray);
467			}
468
469			if ( !$cacheContent){
470			//count the number of query execution to server
471			$this->queryCount++;
472
473			$this->queryRunner->setQuery($query);
474			$this->queryRunner->setReturnType($returnAsList);
475			$this->queryRunner->setReturnAsArray($returnAsArray);
476
477			$queryResult = $this->queryRunner->execute();
478			if (is_object($queryResult)){
479			$this->result = $queryResult->getResult();
480			$this->numRows = $queryResult->getNumRows();
481			if ($isSqlSELECTQuery && $dbCacheStatus){
482			$key = $this->getCacheKeyForQuery($this->query, $returnAsList, $returnAsArray);
483			$this->setCacheContentForQuery($this->query, $key, $this->result, $cacheExpire);
484			if (! $this->result){
485			$this->logger->info('No result where found for the query [' . $query . ']');
486			}
487			}
488			}
489			} else if ($isSqlSELECTQuery){
490			$this->logger->info('The result for query [' .$this->query. '] already cached use it');
491			$this->result = $cacheContent;
492			$this->numRows = count($this->result);
493			}
494			return $this->result;
495			}
496
497
498			/**
499			* Return the database configuration
500			* @return array
501			*/
502			public function getDatabaseConfiguration(){
503			return $this->config;
504			}
505
506			/**
507			* Setting the database configuration using the configuration file and additional configuration from param
508			* @param array $overwriteConfig the additional configuration to overwrite with the existing one
509			* @param boolean $useConfigFile whether to use database configuration file
510			* @return object Database
511			*/
512			public function setDatabaseConfiguration(array $overwriteConfig = array(), $useConfigFile = true){
513			$db = array();
514			if ($useConfigFile && file_exists(CONFIG_PATH . 'database.php')){
515			//here don't use require_once because somewhere user can create database instance directly
516			require CONFIG_PATH . 'database.php';
517			}
518
519			//merge with the parameter
520			$db = array_merge($db, $overwriteConfig);
521
522			//default configuration
523			$config = array(
524			'driver' => 'mysql',
525			'username' => 'root',
526			'password' => '',
527			'database' => '',
528			'hostname' => 'localhost',
529			'charset' => 'utf8',
530			'collation' => 'utf8_general_ci',
531			'prefix' => '',
532			'port' => ''
533			);
534
535			$config = array_merge($config, $db);
536			//determine the port using the hostname like localhost:3307
537			//hostname will be "localhost", and port "3307"
538			$p = explode(':', $config['hostname']);
539			if (count($p) >= 2){
540			$config['hostname'] = $p[0];
541			$config['port'] = $p[1];
542			}
543
544			$this->databaseName = $config['database'];
545			$this->config = $config;
546			$this->logger->info(
547			'The database configuration are listed below: '
548			. stringfy_vars(array_merge(
549			$this->config,
550			array('password' => string_hidden($this->config['password']))
551			))
552			);
553
554			//Now connect to the database
555			$this->connect();
556
557			//do update of QueryRunner and Builder
558			$this->updateQueryBuilderAndRunnerProperties();
559
560			return $this;
561			}
562
563			/**
564			* Close the connexion
565			*/
566			public function close(){
567			$this->pdo = null;
568			}
569
570			/**
571			* Update the DatabaseQueryBuilder and DatabaseQueryRunner properties
572			* @return void
573			*/
574			protected function updateQueryBuilderAndRunnerProperties(){
575			//update queryBuilder with some properties needed
576			if(is_object($this->queryBuilder)){
577			$this->queryBuilder->setDriver($this->config['driver']);
578			$this->queryBuilder->setPrefix($this->config['prefix']);
579			$this->queryBuilder->setPdo($this->pdo);
580			}
581
582			//update queryRunner with some properties needed
583			if(is_object($this->queryRunner)){
584			$this->queryRunner->setDriver($this->config['driver']);
585			$this->queryRunner->setPdo($this->pdo);
586			}
587			}
588
589
590			/**
591			* This method is used to get the PDO DSN string using the configured driver
592			* @return string the DSN string
593			*/
594			protected function getDsnFromDriver(){
595			$config = $this->getDatabaseConfiguration();
596			if (! empty($config)){
597			$driver = $config['driver'];
598			$driverDsnMap = array(
599			'mysql' => 'mysql:host=' . $config['hostname'] . ';'
600			. (($config['port']) != '' ? 'port=' . $config['port'] . ';' : '')
601			. 'dbname=' . $config['database'],
602			'pgsql' => 'pgsql:host=' . $config['hostname'] . ';'
603			. (($config['port']) != '' ? 'port=' . $config['port'] . ';' : '')
604			. 'dbname=' . $config['database'],
605			'sqlite' => 'sqlite:' . $config['database'],
606			'oracle' => 'oci:dbname=' . $config['hostname']
607			. (($config['port']) != '' ? ':' . $config['port'] : '')
608			. '/' . $config['database']
609			);
610			return isset($driverDsnMap[$driver]) ? $driverDsnMap[$driver] : '';
611			}
612			return null;
613			}
614
615			/**
616			* Get the cache content for this query
617			* @see Database::query
618			*
619			* @return mixed
620			*/
621			protected function getCacheContentForQuery($query, $returnAsList, $returnAsArray){
622			$cacheKey = $this->getCacheKeyForQuery($query, $returnAsList, $returnAsArray);
623			if (! is_object($this->cacheInstance)){
624			//can not call method with reference in argument
625			//like $this->setCacheInstance(& get_instance()->cache);
626			//use temporary variable
627			$instance = & get_instance()->cache;
628			$this->cacheInstance = $instance;
629			}
630			return $this->cacheInstance->get($cacheKey);
631			}
632
633			/**
634			* Save the result of query into cache
635			* @param string $query the SQL query
636			* @param string $key the cache key
637			* @param mixed $result the query result to save
638			* @param int $expire the cache TTL
639			*/
640			protected function setCacheContentForQuery($query, $key, $result, $expire){
641			$this->logger->info('Save the result for query [' .$query. '] into cache for future use');
642			if (! is_object($this->cacheInstance)){
643			//can not call method with reference in argument
644			//like $this->setCacheInstance(& get_instance()->cache);
645			//use temporary variable
646			$instance = & get_instance()->cache;
647			$this->cacheInstance = $instance;
648			}
649			$this->cacheInstance->set($key, $result, $expire);
650			}
651
652
653			/**
654			* Return the cache key for the given query
655			* @see Database::query
656			*
657			* @return string
658			*/
659			protected function getCacheKeyForQuery($query, $returnAsList, $returnAsArray){
660			return md5($query . $returnAsList . $returnAsArray);
661			}
662
663			/**
664			* Set the Log instance using argument or create new instance
665			* @param object $logger the Log instance if not null
666			*/
667			protected function setLoggerFromParamOrCreateNewInstance(Log $logger = null){
668			if ($logger !== null){
669			$this->logger = $logger;
670			}
671			else{
672			$this->logger =& class_loader('Log', 'classes');
673			$this->logger->setLogger('Library::Database');
674			}
675			}
676
677			/**
678			* Set the DatabaseQueryBuilder instance using argument or create new instance
679			* @param object $queryBuilder the DatabaseQueryBuilder instance if not null
680			*/
681			protected function setQueryBuilderFromParamOrCreateNewInstance(DatabaseQueryBuilder $queryBuilder = null){
682			if ($queryBuilder !== null){
683			$this->queryBuilder = $queryBuilder;
684			}
685			else{
686			$this->queryBuilder =& class_loader('DatabaseQueryBuilder', 'classes/database');
687			}
688			}
689
690			/**
691			* Set the DatabaseQueryRunner instance using argument or create new instance
692			* @param object $queryRunner the DatabaseQueryRunner instance if not null
693			*/
694			protected function setQueryRunnerFromParamOrCreateNewInstance(DatabaseQueryRunner $queryRunner = null){
695			if ($queryRunner !== null){
696			$this->queryRunner = $queryRunner;
697			}
698			else{
699			$this->queryRunner =& class_loader('DatabaseQueryRunner', 'classes/database');
700			}
701			}
702
703			/**
704			* Reset the database class attributs to the initail values before each query.
705			*/
706			private function reset(){
707			//query builder reset
708			$this->getQueryBuilder()->reset();
709			$this->numRows = 0;
710			$this->insertId = null;
711			$this->query = null;
712			$this->result = array();
713			$this->data = array();
714			}
715
716			/**
717			* The class destructor
718			*/
719			public function __destruct(){
720			$this->pdo = null;
721			}
722
723			}
724

tnhfw / tnh-fw

Push — 1.0.0-dev ( ace4b6...d422fd )

Database::query() B

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like