QueryBuilder::prepare() - Code Metrics - kristuff/patabase - Measure and Improve Code Quality continuously with Scrutinizer

QueryBuilder::prepare() A
last analyzed 2022-04-08 21:26 UTC

↳ Parent: QueryBuilder

Complexity

Conditions	3
Paths	3

Size

Total Lines	20
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	3
eloc	9
c	1
b	0
f	0
nc	3
nop	0
dl	0
loc	20
rs	9.9666

<?php declare(strict_types=1);

/** 
 *  ___      _        _
 * | _ \__ _| |_ __ _| |__  __ _ ___ ___
 * |  _/ _` |  _/ _` | '_ \/ _` (_-</ -_)
 * |_| \__,_|\__\__,_|_.__/\__,_/__/\___|
 * 
 * This file is part of Kristuff\Patabase.
 * (c) Kristuff <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @version    1.0.1
 * @copyright  2017-2022 Christophe Buliard
 */

namespace Kristuff\Patabase\Query;

use Kristuff\Patabase\Query;
use Kristuff\Patabase\Query\QueryBase;
use Kristuff\Patabase\Exception;
use Kristuff\Patabase\SqlException;
use Kristuff\Patabase\Driver;
use Kristuff\Patabase\Driver\DatabaseDriver;
use Kristuff\Patabase\Output;
use PDOStatement;


/**
 * Class QueryBuilder
 *
 * Abstract base class for parametized sql queries
 */
abstract class QueryBuilder extends QueryBase
{

    /**
     * Sorting direction
     *
     * @access public
     * @var string
     */
    const SORT_ASC = 'ASC';
    const SORT_DESC = 'DESC';
    
    /**
     * PDO Statement object
     *
     * @access protected
     * @var PDOStatement        $pdoStatement
     */
    protected $pdoStatement = null;

    /**
     * List of parameters passed to PDO statement
     *
     * @access public
     * @var array                $pdoParameters
     */
    protected $pdoParameters = array();

    /**
     * List of parameters passed to query
     *
     * @access public
     * @var array                $parameters
     */
    protected $parameters = array();

    /**
     * Where conditions object
     *
     * @access protected
     * @var Query\Where          $where
     */
    protected $where = null;
    
    /**
     * Having conditions object
     *
     * @access protected
     * @var Query\Having         $having
     */ 
    protected $having = null;

    /**
     * The Driver instance
     *
     * @access protected
     * @var Driver\DatabaseDriver    $driver 
     */
    protected $driver = null;

    /**
     * SQL query string  
     *
     * @access public
     * @return string
     */
    abstract function sql();

    /**
     * Escape a given string with driver escape chars
     * 
     * @access public
     * @param string   $str        The value to escape
     *
     * @return string
     */
    public function escape(string $str): string
    {
       return $this->driver->escape($str);
    }

    /**
     * Escape an array of string with driver escape chars
     *
     * @access public
     * @param array    $values     The array of values
     *
     * @return array
     */
    public function escapeList(array $values): array
    {
        $newList = array();
        foreach ($values as $identifier) {
            $newList[] = $this->escape($identifier);
        }
        return $newList;
    }

    /**
     * Constructor, init the query by define the driver
     *
     * @access public
     * @param  DatabaseDriver    $driver         The driver instance
     *
     * @return string
     */
    public function __construct(DatabaseDriver $driver)
    {
        $this->driver = $driver;
    }

    /**
     * Destructor
     *
     * @access public
     */
    public function __destruct()
    {
        $this->driver = null;
    }

    /**
     * Define a key/value parameter
     *
     * @access public
     * @param string       $columName          The name
     * @param mixed        $value              The value
     * @return void
     */
    public function setSqlParameter(string $name, $value): void
    {
        $this->pdoParameters[$name] = $value;    
    }

    /**
     * Get whether a parameter with given name exists
     *
     * @access protected
     * @param string       $name               the column name
     *
     * @return bool
     */
    public function sqlParameterExists(string $name): bool
    {
        return array_key_exists($name, $this->pdoParameters);
    }

    /**
     * Prepare the SQL query
     *
     * @access public
     * @return bool
     */
    public function prepare()
    {
         try {
            // prepare is in a try catch block because sqlite for example could raise 
            // an exception when prepareing the statement (with invalid table name for example)
            // when mysql and postres wont. 
            $this->pdoStatement = $this->driver->getConnection()->prepare($this->sql());
            return true;

        } catch (\PDOException $e) {

            // transactions must be in try catch block
            if ($this->driver->getConnection()->inTransaction()) {
                throw new SqlException($e->getMessage(), (int) $e->getCode());
            }

            // register error 
            $this->error['code'] = (int)$e->getCode();
            $this->error['message'] = $e->getMessage();
            return false;
        }
    }   

    /**
     * Bind values
     *
     * @access protected
     * @return void
     */
    protected function bindValues(): void
    {
        // pdo statement may be not set at this stage if prepare failed
        if (isset($this->pdoStatement)) {

            // bind query parameters
            foreach ($this->pdoParameters as $key => $val) {

                // define param type TODO LOB
                $paramType = \PDO::PARAM_STR; // default

                if (!isset($val)) {
                    $paramType =  \PDO::PARAM_NULL;
                
                } elseif (is_int($val)) {
                    $paramType =  \PDO::PARAM_INT;
                
                } elseif (is_bool($val)) {
                    $paramType =  \PDO::PARAM_BOOL;
                } 
                
                // bind value
                $this->pdoStatement->bindValue($key, $val, $paramType);
            }
        }
    }   

    /**
     * Execute the query
     *
     * @access public
     * @return bool     true if the query is executed with success, otherwise false
     */
    public function execute(): bool
    {
        try {
            // prepare bind execute
            if (!isset($this->pdoStatement)){
               if (!$this->prepare()){
                   return false;
               }
            }
            $this->bindValues();
            return $this->pdoStatement ? $this->pdoStatement->execute() : false;

        } catch (\PDOException $e) {

            // transactions must be in try catch block
            if ($this->driver->getConnection()->inTransaction()) {
                throw new SqlException($e->getMessage(), (int) $e->getCode());
            }

            // register error 
            $this->error['code'] = (int)$e->getCode();
            $this->error['message'] = $e->getMessage();
            return false;
        }
    }

    /**
     * Returns the number of rows affected by the last SQL INSERT, UPDATE or DELETE
     *
     * @access public
     * @return int     This function returns -1 if there is no executed query
     */
    public function rowCount()
    {
        return (empty(!$this->pdoStatement)) ? $this->pdoStatement->rowCount() : -1;
    }
    
    /**
     * Returns the sql query output in given format
     *
     * @access public
     * @param bool             $executed           true if the query has been successfully executed
     * @param string           $outputFormat       The output format
     * 
     * @return mixed                     
     */
    protected function fetchOutput(bool $executed, string $outputFormat)
    {
        switch (strtoupper($outputFormat)){

            case Output::ASSOC:    
                return $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_ASSOC) :  array();

            case Output::OBJ:    
                return $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_OBJ) :    array();

            case Output::COLUMN:    
                return $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_COLUMN) :  array();

            case Output::JSON:
                $results = $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_ASSOC) :  array();
                return json_encode($results, JSON_NUMERIC_CHECK);   

            case Output::JSON_PRETTY_PRINT:    
                $results = $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_ASSOC) :  array();
                return json_encode($results, JSON_PRETTY_PRINT | JSON_NUMERIC_CHECK);   
                
            default:
                throw new Exception\InvalidArgException('The specified output format is invalid.');
        }
    }
 
}

1			<?php declare(strict_types=1);
2
3			/**
4			* ___ _ _
5			* \| _ \__ _\| \|_ __ _\| \|__ __ _ ___ ___
6			* \| _/ _` \| _/ _` \| '_ \/ _` (_-</ -_)
7			* \|_\| \__,_\|\__\__,_\|_.__/\__,_/__/\___\|
8			*
9			* This file is part of Kristuff\Patabase.
10			* (c) Kristuff <[email protected]>
11			*
12			* For the full copyright and license information, please view the LICENSE
13			* file that was distributed with this source code.
14			*
15			* @version 1.0.1
16			* @copyright 2017-2022 Christophe Buliard
17			*/
18
19			namespace Kristuff\Patabase\Query;
20
21			use Kristuff\Patabase\Query;
22			use Kristuff\Patabase\Query\QueryBase;
23			use Kristuff\Patabase\Exception;
24			use Kristuff\Patabase\SqlException;
25			use Kristuff\Patabase\Driver;
26			use Kristuff\Patabase\Driver\DatabaseDriver;
27			use Kristuff\Patabase\Output;
28			use PDOStatement;
29
30
31			/**
32			* Class QueryBuilder
33			*
34			* Abstract base class for parametized sql queries
35			*/
36			abstract class QueryBuilder extends QueryBase
37			{
38
39			/**
40			* Sorting direction
41			*
42			* @access public
43			* @var string
44			*/
45			const SORT_ASC = 'ASC';
46			const SORT_DESC = 'DESC';
47
48			/**
49			* PDO Statement object
50			*
51			* @access protected
52			* @var PDOStatement $pdoStatement
53			*/
54			protected $pdoStatement = null;
55
56			/**
57			* List of parameters passed to PDO statement
58			*
59			* @access public
60			* @var array $pdoParameters
61			*/
62			protected $pdoParameters = array();
63
64			/**
65			* List of parameters passed to query
66			*
67			* @access public
68			* @var array $parameters
69			*/
70			protected $parameters = array();
71
72			/**
73			* Where conditions object
74			*
75			* @access protected
76			* @var Query\Where $where
77			*/
78			protected $where = null;
79
80			/**
81			* Having conditions object
82			*
83			* @access protected
84			* @var Query\Having $having
85			*/
86			protected $having = null;
87
88			/**
89			* The Driver instance
90			*
91			* @access protected
92			* @var Driver\DatabaseDriver $driver
93			*/
94			protected $driver = null;
95
96			/**
97			* SQL query string
98			*
99			* @access public
100			* @return string
101			*/
102			abstract function sql();
103
104			/**
105			* Escape a given string with driver escape chars
106			*
107			* @access public
108			* @param string $str The value to escape
109			*
110			* @return string
111			*/
112			public function escape(string $str): string
113			{
114			return $this->driver->escape($str);
115			}
116
117			/**
118			* Escape an array of string with driver escape chars
119			*
120			* @access public
121			* @param array $values The array of values
122			*
123			* @return array
124			*/
125			public function escapeList(array $values): array
126			{
127			$newList = array();
128			foreach ($values as $identifier) {
129			$newList[] = $this->escape($identifier);
130			}
131			return $newList;
132			}
133
134			/**
135			* Constructor, init the query by define the driver
136			*
137			* @access public
138			* @param DatabaseDriver $driver The driver instance
139			*
140			* @return string
141			*/
142			public function __construct(DatabaseDriver $driver)
143			{
144			$this->driver = $driver;
145			}
146
147			/**
148			* Destructor
149			*
150			* @access public
151			*/
152			public function __destruct()
153			{
154			$this->driver = null;
155			}
156
157			/**
158			* Define a key/value parameter
159			*
160			* @access public
161			* @param string $columName The name
162			* @param mixed $value The value
163			* @return void
164			*/
165			public function setSqlParameter(string $name, $value): void
166			{
167			$this->pdoParameters[$name] = $value;
168			}
169
170			/**
171			* Get whether a parameter with given name exists
172			*
173			* @access protected
174			* @param string $name the column name
175			*
176			* @return bool
177			*/
178			public function sqlParameterExists(string $name): bool
179			{
180			return array_key_exists($name, $this->pdoParameters);
181			}
182
183			/**
184			* Prepare the SQL query
185			*
186			* @access public
187			* @return bool
188			*/
189			public function prepare()
190			{
191			try {
192			// prepare is in a try catch block because sqlite for example could raise
193			// an exception when prepareing the statement (with invalid table name for example)
194			// when mysql and postres wont.
195			$this->pdoStatement = $this->driver->getConnection()->prepare($this->sql());
196			return true;
197
198			} catch (\PDOException $e) {
199
200			// transactions must be in try catch block
201			if ($this->driver->getConnection()->inTransaction()) {
202			throw new SqlException($e->getMessage(), (int) $e->getCode());
203			}
204
205			// register error
206			$this->error['code'] = (int)$e->getCode();
207			$this->error['message'] = $e->getMessage();
208			return false;
209			}
210			}
211
212			/**
213			* Bind values
214			*
215			* @access protected
216			* @return void
217			*/
218			protected function bindValues(): void
219			{
220			// pdo statement may be not set at this stage if prepare failed
221			if (isset($this->pdoStatement)) {
222
223			// bind query parameters
224			foreach ($this->pdoParameters as $key => $val) {
225
226			// define param type TODO LOB
227			$paramType = \PDO::PARAM_STR; // default
228
229			if (!isset($val)) {
230			$paramType = \PDO::PARAM_NULL;
231
232			} elseif (is_int($val)) {
233			$paramType = \PDO::PARAM_INT;
234
235			} elseif (is_bool($val)) {
236			$paramType = \PDO::PARAM_BOOL;
237			}
238
239			// bind value
240			$this->pdoStatement->bindValue($key, $val, $paramType);
241			}
242			}
243			}
244
245			/**
246			* Execute the query
247			*
248			* @access public
249			* @return bool true if the query is executed with success, otherwise false
250			*/
251			public function execute(): bool
252			{
253			try {
254			// prepare bind execute
255			if (!isset($this->pdoStatement)){
256			if (!$this->prepare()){
257			return false;
258			}
259			}
260			$this->bindValues();
261			return $this->pdoStatement ? $this->pdoStatement->execute() : false;
262
263			} catch (\PDOException $e) {
264
265			// transactions must be in try catch block
266			if ($this->driver->getConnection()->inTransaction()) {
267			throw new SqlException($e->getMessage(), (int) $e->getCode());
268			}
269
270			// register error
271			$this->error['code'] = (int)$e->getCode();
272			$this->error['message'] = $e->getMessage();
273			return false;
274			}
275			}
276
277			/**
278			* Returns the number of rows affected by the last SQL INSERT, UPDATE or DELETE
279			*
280			* @access public
281			* @return int This function returns -1 if there is no executed query
282			*/
283			public function rowCount()
284			{
285			return (empty(!$this->pdoStatement)) ? $this->pdoStatement->rowCount() : -1;
286			}
287
288			/**
289			* Returns the sql query output in given format
290			*
291			* @access public
292			* @param bool $executed true if the query has been successfully executed
293			* @param string $outputFormat The output format
294			*
295			* @return mixed
296			*/
297			protected function fetchOutput(bool $executed, string $outputFormat)
298			{
299			switch (strtoupper($outputFormat)){
300
301			case Output::ASSOC:
302			return $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_ASSOC) : array();
303
304			case Output::OBJ:
305			return $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_OBJ) : array();
306
307			case Output::COLUMN:
308			return $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_COLUMN) : array();
309
310			case Output::JSON:
311			$results = $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_ASSOC) : array();
312			return json_encode($results, JSON_NUMERIC_CHECK);
313
314			case Output::JSON_PRETTY_PRINT:
315			$results = $executed ? $this->pdoStatement->fetchAll(\PDO::FETCH_ASSOC) : array();
316			return json_encode($results, JSON_PRETTY_PRINT \| JSON_NUMERIC_CHECK);
317
318			default:
319			throw new Exception\InvalidArgException('The specified output format is invalid.');
320			}
321			}
322
323			}

kristuff / patabase

QueryBuilder::prepare() A last analyzed 2022-04-08 21:26 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

QueryBuilder::prepare() A
last analyzed 2022-04-08 21:26 UTC