Database::query() - Code Metrics - Inspection of "add new class DatabaseCache and improve classes Da..." - tnhfw/tnh-fw - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — 1.0.0-dev ( 459011...73c7ea )

by nguereza

created 2020-02-27 15:51 UTC

Database::query() A

↳ Parent: Project

Complexity

Conditions	3
Paths	3

Size

Total Lines	39
Code Lines	26

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	1	Features	1

Metric	Value
cc	3
eloc	26
nc	3
nop	3
dl	0
loc	39
rs	9.504
c	1
b	1
f	1

<?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 extends BaseClass {
	
        /**
         * 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 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 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 DatabaseQueryBuilder instance
         * @var object
         */
        protected $queryBuilder = null;
    
        /**
         * The DatabaseQueryRunner instance
         * @var object
         */
        protected $queryRunner = null;

        /**
         * The DatabaseCache instance
         * @var object
         */
        protected $cacheInstance = null;


    /**
     * Construct new instance
     * @param array $overwriteConfig the config to overwrite with the config set in database.php
     * @param boolean $autoConnect whether to connect to database automatically
     */
    public function __construct($overwriteConfig = array(), $autoConnect = true) {
        parent::__construct();
		
            //Set DatabaseQueryBuilder instance to use
        $this->setDependencyInstanceFromParamOrCreate('queryBuilder', null, 'DatabaseQueryBuilder', 'classes/database');
       
        //Set DatabaseQueryRunner instance to use
        $this->setDependencyInstanceFromParamOrCreate('queryRunner', null, 'DatabaseQueryRunner', 'classes/database');

        //Set DatabaseCache instance to use
        $this->setDependencyInstanceFromParamOrCreate('cacheInstance', null, 'DatabaseCache', 'classes/database');

        //Set database configuration
        $this->setDatabaseConfiguration($overwriteConfig, true, $autoConnect);
    }

    /**
     * This is used to connect to database
     * @return bool 
     */
    public function connect() {
        $config = $this->getDatabaseConfiguration();
        if (!empty($config)) {
        try {
            $this->pdo = new PDO($this->getDsnValueFromConfig(), $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);
      
            //update of QueryRunner and QueryBuilder properties
            $this->updateQueryBuilderAndRunnerProperties();

            return is_object($this->pdo);
            } 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->queryBuilder->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->queryBuilder->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) && !empty($this->data)) {
        //as when using $this->setData() may be the data already escaped
        $escape = false;
        $data = $this->data;
        }
        $query = $this->queryBuilder->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) && !empty($this->data)) {
        //as when using $this->setData() may be the data already escaped
        $escape = false;
        $data = $this->data;
        }
        $query = $this->queryBuilder->update($data, $escape)->getQuery();
        return $this->query($query);
    }

    /**
     * Delete the record in database
     * @return mixed the delete status
     */
    public function delete() {
            $query = $this->queryBuilder->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 instance
     */
    public function setCache($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 instance
     */
    public function cached($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 no
     * need escaped
     */
    public function escape($data, $escaped = true) {
        $data = trim($data);
        if ($escaped) {
        return $this->pdo->quote($data);
        }
        return $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 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 DatabaseCache instance
     * @return object DatabaseCache
     */
    public function getCacheInstance() {
        return $this->cacheInstance;
    }

    /**
     * Set the DatabaseCache instance
     * @param object DatabaseCache $cacheInstance the DatabaseCache object
     */
    public function setCacheInstance(DatabaseCache $cacheInstance) {
        $this->cacheInstance = $cacheInstance;
        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));
        $this->logger->info('Execute SQL query [' . $this->query . ']');

        $cacheExpire = $this->temporaryCacheTtl;

        //return to the initial cache time
        $this->temporaryCacheTtl = $this->cacheTtl;

        //the database cache content
        $cacheContent = $this->cacheInstance->setQuery($query)
                                            ->setReturnType($returnAsList)
                                            ->setReturnAsArray($returnAsArray)
                                            ->setCacheTtl($cacheExpire)
                                            ->getCacheContent();
        if (!$cacheContent) {
            $this->logger->info('No cache data found for this query or is not a SELECT query, get result from real database');
            //count the number of query execution to server
            $this->queryCount++;
            
            $queryResult = $this->queryRunner->setQuery($query)
                                             ->setReturnType($returnAsList)
                                             ->setReturnAsArray($returnAsArray)
                                             ->execute();

            if (is_object($queryResult)) {
                $this->result  = $queryResult->getResult();
                $this->numRows = $queryResult->getNumRows();
                //save the result into cache
                $this->cacheInstance->saveCacheContent($this->result);
            }
        } else {
            $this->logger->info('The result for query [' . $this->query . '] already cached use it');
            $this->result = $cacheContent;
            $this->numRows = count($this->result);
        }
        
        return $this->result;
    }

    /**
     * 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
     * @param boolean $autoConnect whether to connect to database after set the configuration
     * @return object Database
     */
    public function setDatabaseConfiguration(array $overwriteConfig = array(), $useConfigFile = true, $autoConnect = false) {
        $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);
      
        //get the default configuration
        $config = $this->getDatabaseDefaultConfiguration();
		  
        $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']))
                                                ))
                            );
        if ($autoConnect) {
                //Now connect to the database
                $this->connect();
            }
            return $this;
    }

    /**
     * Return the database configuration
     * @return array
     */
    public  function getDatabaseConfiguration() {
        return $this->config;
    }

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

    /**
     * Return the database default configuration
     * @return array
     */
    protected function getDatabaseDefaultConfiguration() {
        return array(
                'driver' => '',
                'username' => '',
                'password' => '',
                'database' => '',
                'hostname' => 'localhost',
                'charset' => 'utf8',
                'collation' => 'utf8_general_ci',
                'prefix' => '',
                'port' => ''
            );
    }

    /**
     * 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'])
                            ->setPrefix($this->config['prefix'])
                            ->setPdo($this->pdo);
        }

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

    /**
     * This method is used to get the PDO DSN string using the configured driver
     * @return string|null the DSN string or null if can not find it
     */
    protected function getDsnValueFromConfig() {
        $dsn = null;
        $config = $this->getDatabaseConfiguration();
        if (!empty($config)) {
        $driver = $config['driver'];
        $driverDsnMap = array(
                                'mysql'  => $this->getDsnValueForDriver('mysql'),
                                'pgsql'  => $this->getDsnValueForDriver('pgsql'),
                                'sqlite' => $this->getDsnValueForDriver('sqlite'),
                                'oracle' => $this->getDsnValueForDriver('oracle')
                                );
        if (isset($driverDsnMap[$driver])) {
            $dsn = $driverDsnMap[$driver];
        }
        }    
        return $dsn;
    }

    /**
     * Get the DSN value for the given driver
     * @param  string $driver the driver name
     * @return string|null         the dsn name
     */
    protected function getDsnValueForDriver($driver) {
        $dsn = '';
        $config = $this->getDatabaseConfiguration();
        if (empty($config)) {
        return null;
        }
        switch ($driver) {
            case 'mysql':
            case 'pgsql':
              $port = '';
                if (!empty($config['port'])) {
                $port = 'port=' . $config['port'] . ';';
                }
                $dsn = $driver . ':host=' . $config['hostname'] . ';' . $port . 'dbname=' . $config['database'];
                break;
            case 'sqlite':
              $dsn = 'sqlite:' . $config['database'];
                break;
            case 'oracle':
          $port = '';
            if (!empty($config['port'])) {
            $port = ':' . $config['port'];
            }
            $dsn = 'oci:dbname=' . $config['hostname'] . $port . '/' . $config['database'];
            break;
        }
        return $dsn;
    }
	
    /**
     * Reset the database class attributs to the initail values before each query.
     */
    private function reset() {
        //query builder reset
        $this->queryBuilder->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 extends BaseClass {
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 number of executed query for the current request
66			* @var int
67			*/
68			private $queryCount = 0;
69
70			/**
71			* The default data to be used in the statments query INSERT, UPDATE
72			* @var array
73			*/
74			private $data = array();
75
76			/**
77			* The database configuration
78			* @var array
79			*/
80			private $config = array();
81
82			/**
83			* The cache default time to live in second. 0 means no need to use the cache feature
84			* @var int
85			*/
86			private $cacheTtl = 0;
87
88			/**
89			* The cache current time to live. 0 means no need to use the cache feature
90			* @var int
91			*/
92			private $temporaryCacheTtl = 0;
93
94			/**
95			* The DatabaseQueryBuilder instance
96			* @var object
97			*/
98			protected $queryBuilder = null;
99
100			/**
101			* The DatabaseQueryRunner instance
102			* @var object
103			*/
104			protected $queryRunner = null;
105
106			/**
107			* The DatabaseCache instance
108			* @var object
109			*/
110			protected $cacheInstance = null;
111
112
113			/**
114			* Construct new instance
115			* @param array $overwriteConfig the config to overwrite with the config set in database.php
116			* @param boolean $autoConnect whether to connect to database automatically
117			*/
118			public function __construct($overwriteConfig = array(), $autoConnect = true) {
119			parent::__construct();
120
121			//Set DatabaseQueryBuilder instance to use
122			$this->setDependencyInstanceFromParamOrCreate('queryBuilder', null, 'DatabaseQueryBuilder', 'classes/database');
123
124			//Set DatabaseQueryRunner instance to use
125			$this->setDependencyInstanceFromParamOrCreate('queryRunner', null, 'DatabaseQueryRunner', 'classes/database');
126
127			//Set DatabaseCache instance to use
128			$this->setDependencyInstanceFromParamOrCreate('cacheInstance', null, 'DatabaseCache', 'classes/database');
129
130			//Set database configuration
131			$this->setDatabaseConfiguration($overwriteConfig, true, $autoConnect);
132			}
133
134			/**
135			* This is used to connect to database
136			* @return bool
137			*/
138			public function connect() {
139			$config = $this->getDatabaseConfiguration();
140			if (!empty($config)) {
141			try {
142			$this->pdo = new PDO($this->getDsnValueFromConfig(), $config['username'], $config['password']);
143			$this->pdo->exec("SET NAMES '" . $config['charset'] . "' COLLATE '" . $config['collation'] . "'");
144			$this->pdo->exec("SET CHARACTER SET '" . $config['charset'] . "'");
145			$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
146
147			//update of QueryRunner and QueryBuilder properties
148			$this->updateQueryBuilderAndRunnerProperties();
149
150			return is_object($this->pdo);
151			} catch (PDOException $e) {
152			$this->logger->fatal($e->getMessage());
153			show_error('Cannot connect to Database.');
154			return false;
155			}
156			}
157			return false;
158			}
159
160
161			/**
162			* Return the number of rows returned by the current query
163			* @return int
164			*/
165			public function numRows() {
166			return $this->numRows;
167			}
168
169			/**
170			* Return the last insert id value
171			* @return mixed
172			*/
173			public function insertId() {
174			return $this->insertId;
175			}
176
177
178			/**
179			* Get the result of one record rows returned by the current query
180			* @param boolean $returnSQLQueryOrResultType if is boolean and true will return the SQL query string.
181			* If is string will determine the result type "array" or "object"
182			* @return mixed the query SQL string or the record result
183			*/
184			public function get($returnSQLQueryOrResultType = false) {
185			$this->queryBuilder->limit(1);
186			$query = $this->getAll(true);
187			if ($returnSQLQueryOrResultType === true) {
188			return $query;
189			} else {
190			return $this->query($query, false, $returnSQLQueryOrResultType == 'array');
191			}
192			}
193
194			/**
195			* Get the result of record rows list returned by the current query
196			* @param boolean\|string $returnSQLQueryOrResultType if is boolean and true will return the SQL query string.
197			* If is string will determine the result type "array" or "object"
198			* @return mixed the query SQL string or the record result
199			*/
200			public function getAll($returnSQLQueryOrResultType = false) {
201			$query = $this->queryBuilder->getQuery();
202			if ($returnSQLQueryOrResultType === true) {
203			return $query;
204			}
205			return $this->query($query, true, $returnSQLQueryOrResultType == 'array');
206			}
207
208			/**
209			* Insert new record in the database
210			* @param array $data the record data if is empty will use the $this->data array.
211			* @param boolean $escape whether to escape or not the values
212			* @return mixed the insert id of the new record or null
213			*/
214			public function insert($data = array(), $escape = true) {
215			if (empty($data) && !empty($this->data)) {
216			//as when using $this->setData() may be the data already escaped
217			$escape = false;
218			$data = $this->data;
219			}
220			$query = $this->queryBuilder->insert($data, $escape)->getQuery();
221			$result = $this->query($query);
222			if ($result) {
223			$this->insertId = $this->pdo->lastInsertId();
224			//if the table doesn't have the auto increment field or sequence, the value of 0 will be returned
225			return !($this->insertId) ? true : $this->insertId;
226			}
227			return false;
228			}
229
230			/**
231			* Update record in the database
232			* @param array $data the record data if is empty will use the $this->data array.
233			* @param boolean $escape whether to escape or not the values
234			* @return mixed the update status
235			*/
236			public function update($data = array(), $escape = true) {
237			if (empty($data) && !empty($this->data)) {
238			//as when using $this->setData() may be the data already escaped
239			$escape = false;
240			$data = $this->data;
241			}
242			$query = $this->queryBuilder->update($data, $escape)->getQuery();
243			return $this->query($query);
244			}
245
246			/**
247			* Delete the record in database
248			* @return mixed the delete status
249			*/
250			public function delete() {
251			$query = $this->queryBuilder->delete()->getQuery();
252			return $this->query($query);
253			}
254
255			/**
256			* Set database cache time to live
257			* @param integer $ttl the cache time to live in second
258			* @return object the current instance
259			*/
260			public function setCache($ttl = 0) {
261			$this->cacheTtl = $ttl;
262			$this->temporaryCacheTtl = $ttl;
263			return $this;
264			}
265
266			/**
267			* Enabled cache temporary for the current query not globally
268			* @param integer $ttl the cache time to live in second
269			* @return object the current instance
270			*/
271			public function cached($ttl = 0) {
272			$this->temporaryCacheTtl = $ttl;
273			return $this;
274			}
275
276			/**
277			* Escape the data before execute query useful for security.
278			* @param mixed $data the data to be escaped
279			* @param boolean $escaped whether we can do escape of not
280			* @return mixed the data after escaped or the same data if no
281			* need escaped
282			*/
283			public function escape($data, $escaped = true) {
284			$data = trim($data);
285			if ($escaped) {
286			return $this->pdo->quote($data);
287			}
288			return $data;
289			}
290
291			/**
292			* Return the number query executed count for the current request
293			* @return int
294			*/
295			public function queryCount() {
296			return $this->queryCount;
297			}
298
299			/**
300			* Return the current query SQL string
301			* @return string
302			*/
303			public function getQuery() {
304			return $this->query;
305			}
306
307			/**
308			* Return the application database name
309			* @return string
310			*/
311			public function getDatabaseName() {
312			return $this->databaseName;
313			}
314
315			/**
316			* Return the PDO instance
317			* @return object
318			*/
319			public function getPdo() {
320			return $this->pdo;
321			}
322
323			/**
324			* Set the PDO instance
325			* @param object $pdo the pdo object
326			* @return object Database
327			*/
328			public function setPdo(PDO $pdo) {
329			$this->pdo = $pdo;
330			return $this;
331			}
332
333			/**
334			* Return the DatabaseQueryBuilder instance
335			* @return object DatabaseQueryBuilder
336			*/
337			public function getQueryBuilder() {
338			return $this->queryBuilder;
339			}
340
341			/**
342			* Set the DatabaseQueryBuilder instance
343			* @param object DatabaseQueryBuilder $queryBuilder the DatabaseQueryBuilder object
344			*/
345			public function setQueryBuilder(DatabaseQueryBuilder $queryBuilder) {
346			$this->queryBuilder = $queryBuilder;
347			return $this;
348			}
349
350			/**
351			* Return the DatabaseCache instance
352			* @return object DatabaseCache
353			*/
354			public function getCacheInstance() {
355			return $this->cacheInstance;
356			}
357
358			/**
359			* Set the DatabaseCache instance
360			* @param object DatabaseCache $cacheInstance the DatabaseCache object
361			*/
362			public function setCacheInstance(DatabaseCache $cacheInstance) {
363			$this->cacheInstance = $cacheInstance;
364			return $this;
365			}
366
367			/**
368			* Return the DatabaseQueryRunner instance
369			* @return object DatabaseQueryRunner
370			*/
371			public function getQueryRunner() {
372			return $this->queryRunner;
373			}
374
375			/**
376			* Set the DatabaseQueryRunner instance
377			* @param object DatabaseQueryRunner $queryRunner the DatabaseQueryRunner object
378			*/
379			public function setQueryRunner(DatabaseQueryRunner $queryRunner) {
380			$this->queryRunner = $queryRunner;
381			return $this;
382			}
383
384			/**
385			* Return the data to be used for insert, update, etc.
386			* @return array
387			*/
388			public function getData() {
389			return $this->data;
390			}
391
392			/**
393			* Set the data to be used for insert, update, etc.
394			* @param string\|array $key the data key identified
395			* @param mixed $value the data value
396			* @param boolean $escape whether to escape or not the $value
397			* @return object the current Database instance
398			*/
399			public function setData($key, $value = null, $escape = true) {
400			if (is_array($key)) {
401			foreach ($key as $k => $v) {
402			$this->setData($k, $v, $escape);
403			}
404			} else {
405			$this->data[$key] = $this->escape($value, $escape);
406			}
407			return $this;
408			}
409
410			/**
411			* Execute an SQL query
412			* @param string $query the query SQL string
413			* @param boolean $returnAsList indicate whether to return all record or just one row
414			* @param boolean $returnAsArray return the result as array or not
415			* @return mixed the query result
416			*/
417			public function query($query, $returnAsList = true, $returnAsArray = false) {
418			$this->reset();
419			$this->query = preg_replace('/\s\s+\|\t\t+/', ' ', trim($query));
420			$this->logger->info('Execute SQL query [' . $this->query . ']');
421
422			$cacheExpire = $this->temporaryCacheTtl;
423
424			//return to the initial cache time
425			$this->temporaryCacheTtl = $this->cacheTtl;
426
427			//the database cache content
428			$cacheContent = $this->cacheInstance->setQuery($query)
429			->setReturnType($returnAsList)
430			->setReturnAsArray($returnAsArray)
431			->setCacheTtl($cacheExpire)
432			->getCacheContent();
433			if (!$cacheContent) {
434			$this->logger->info('No cache data found for this query or is not a SELECT query, get result from real database');
435			//count the number of query execution to server
436			$this->queryCount++;
437
438			$queryResult = $this->queryRunner->setQuery($query)
439			->setReturnType($returnAsList)
440			->setReturnAsArray($returnAsArray)
441			->execute();
442
443			if (is_object($queryResult)) {
444			$this->result = $queryResult->getResult();
445			$this->numRows = $queryResult->getNumRows();
446			//save the result into cache
447			$this->cacheInstance->saveCacheContent($this->result);
448			}
449			} else {
450			$this->logger->info('The result for query [' . $this->query . '] already cached use it');
451			$this->result = $cacheContent;
452			$this->numRows = count($this->result);
453			}
454
455			return $this->result;
456			}
457
458			/**
459			* Setting the database configuration using the configuration file and additional configuration from param
460			* @param array $overwriteConfig the additional configuration to overwrite with the existing one
461			* @param boolean $useConfigFile whether to use database configuration file
462			* @param boolean $autoConnect whether to connect to database after set the configuration
463			* @return object Database
464			*/
465			public function setDatabaseConfiguration(array $overwriteConfig = array(), $useConfigFile = true, $autoConnect = false) {
466			$db = array();
467			if ($useConfigFile && file_exists(CONFIG_PATH . 'database.php')) {
468			//here don't use require_once because somewhere user can create database instance directly
469			require CONFIG_PATH . 'database.php';
470			}
471
472			//merge with the parameter
473			$db = array_merge($db, $overwriteConfig);
474
475			//get the default configuration
476			$config = $this->getDatabaseDefaultConfiguration();
477
478			$config = array_merge($config, $db);
479			//determine the port using the hostname like localhost:3307
480			//hostname will be "localhost", and port "3307"
481			$p = explode(':', $config['hostname']);
482			if (count($p) >= 2) {
483			$config['hostname'] = $p[0];
484			$config['port'] = $p[1];
485			}
486
487			$this->databaseName = $config['database'];
488			$this->config = $config;
489			$this->logger->info(
490			'The database configuration are listed below: '
491			. stringfy_vars(array_merge(
492			$this->config,
493			array('password' => string_hidden($this->config['password']))
494			))
495			);
496			if ($autoConnect) {
497			//Now connect to the database
498			$this->connect();
499			}
500			return $this;
501			}
502
503			/**
504			* Return the database configuration
505			* @return array
506			*/
507			public function getDatabaseConfiguration() {
508			return $this->config;
509			}
510
511			/**
512			* Close the connexion
513			*/
514			public function close() {
515			$this->pdo = null;
516			}
517
518			/**
519			* Return the database default configuration
520			* @return array
521			*/
522			protected function getDatabaseDefaultConfiguration() {
523			return array(
524			'driver' => '',
525			'username' => '',
526			'password' => '',
527			'database' => '',
528			'hostname' => 'localhost',
529			'charset' => 'utf8',
530			'collation' => 'utf8_general_ci',
531			'prefix' => '',
532			'port' => ''
533			);
534			}
535
536			/**
537			* Update the DatabaseQueryBuilder and DatabaseQueryRunner properties
538			* @return void
539			*/
540			protected function updateQueryBuilderAndRunnerProperties() {
541			//update queryBuilder with some properties needed
542			if (is_object($this->queryBuilder)) {
543			$this->queryBuilder->setDriver($this->config['driver'])
544			->setPrefix($this->config['prefix'])
545			->setPdo($this->pdo);
546			}
547
548			//update queryRunner with some properties needed
549			if (is_object($this->queryRunner)) {
550			$this->queryRunner->setDriver($this->config['driver'])
551			->setPdo($this->pdo);
552			}
553			}
554
555
556			/**
557			* This method is used to get the PDO DSN string using the configured driver
558			* @return string\|null the DSN string or null if can not find it
559			*/
560			protected function getDsnValueFromConfig() {
561			$dsn = null;
562			$config = $this->getDatabaseConfiguration();
563			if (!empty($config)) {
564			$driver = $config['driver'];
565			$driverDsnMap = array(
566			'mysql' => $this->getDsnValueForDriver('mysql'),
567			'pgsql' => $this->getDsnValueForDriver('pgsql'),
568			'sqlite' => $this->getDsnValueForDriver('sqlite'),
569			'oracle' => $this->getDsnValueForDriver('oracle')
570			);
571			if (isset($driverDsnMap[$driver])) {
572			$dsn = $driverDsnMap[$driver];
573			}
574			}
575			return $dsn;
576			}
577
578			/**
579			* Get the DSN value for the given driver
580			* @param string $driver the driver name
581			* @return string\|null the dsn name
582			*/
583			protected function getDsnValueForDriver($driver) {
584			$dsn = '';
585			$config = $this->getDatabaseConfiguration();
586			if (empty($config)) {
587			return null;
588			}
589			switch ($driver) {
590			case 'mysql':
591			case 'pgsql':
592			$port = '';
593			if (!empty($config['port'])) {
594			$port = 'port=' . $config['port'] . ';';
595			}
596			$dsn = $driver . ':host=' . $config['hostname'] . ';' . $port . 'dbname=' . $config['database'];
597			break;
598			case 'sqlite':
599			$dsn = 'sqlite:' . $config['database'];
600			break;
601			case 'oracle':
602			$port = '';
603			if (!empty($config['port'])) {
604			$port = ':' . $config['port'];
605			}
606			$dsn = 'oci:dbname=' . $config['hostname'] . $port . '/' . $config['database'];
607			break;
608			}
609			return $dsn;
610			}
611
612			/**
613			* Reset the database class attributs to the initail values before each query.
614			*/
615			private function reset() {
616			//query builder reset
617			$this->queryBuilder->reset();
618			$this->numRows = 0;
619			$this->insertId = null;
620			$this->query = null;
621			$this->result = array();
622			$this->data = array();
623			}
624
625			/**
626			* The class destructor
627			*/
628			public function __destruct() {
629			$this->pdo = null;
630			}
631
632			}
633

tnhfw / tnh-fw

Push — 1.0.0-dev ( 459011...73c7ea )

Database::query() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like