SQL::genWhere() - Code Metrics - Inspection of "tunes requiered for correct returns" - drmvc/database - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 55ad28...46082e )

by Mr

created 2018-04-01 21:50 UTC

SQL::genWhere() A

↳ Parent: SQL

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?php

namespace DrMVC\Database\Drivers;

use \DrMVC\Database\SQLException;
use \PDO;

abstract class SQL extends Driver implements SQLInterface
{
    /**
     * Get current connection
     *
     * @return  PDO
     */
    public function getInstance(): PDO
    {
        return $this->_instance;
    }

    /**
     * Check if input value is integer
     *
     * @param   mixed $value
     * @return  int
     */
    private function isPdoInt($value): int
    {
        return \is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR;
    }

    /**
     * Initiate connection to database
     *
     * @return  DriverInterface
     */
    public function connect(): DriverInterface
    {
        try {
            $connection = new PDO(
                $this->getDsn(),
                $this->getParam('username'),
                $this->getParam('password')
            );
            $this->setInstance($connection);

        } catch (SQLException $e) {
            // __construct
        }

        return $this;
    }

    /**
     * Generate basic select query
     *
     * @param   array $where
     * @return  string
     */
    private function getSelect(array $where): string
    {
        // Current table
        $table = $this->getCollection();

        // Generate where line
        $whereDetails = !empty($where)
            ? ' WHERE ' . $this->genWhere($where)
            : '';

        return "SELECT * FROM $table $whereDetails";
    }

    /**
     * Run a select statement against the database
     *
     * @param   array $where array with data for filtering
     * @return  mixed
     */
    public function select(array $where = [])
    {
        // Set statement
        $query = $this->getSelect($where);
        $statement = $this->getInstance()->prepare($query);

        // Bind where values
        foreach ($where as $key => $value) {
            $statement->bindValue(":where_$key", $value, $this->isPdoInt($value));
        }

        // Execute operation
        $statement->execute();

        // Return object
        return $statement->fetchAll(PDO::FETCH_OBJ);
    }

    /**
     * Execute raw query, without generation, useful for some advanced operations
     *
     * @param   array $arguments array of arguments
     * @return  mixed
     */
    public function rawSQL(array $arguments)
    {
        /*
         * @param string $query  pure sql query
         * @param  array $bind   array with values in [':key' => 'value'] format
         * @param   bool $fetch  make fetch and return data?
         */
        @list($query, $bind, $fetch) = $arguments;
        // Notices disabled

        // Set statement
        $statement = $this->getInstance()->prepare($query);

        // Execute operation
        $statement->execute($bind);

        return (true === $fetch)
            // Return object
            ? $statement->fetchAll(PDO::FETCH_OBJ)
            // Return count of affected rows
            : $statement->rowCount();
    }

    /**
     * Generate INSERT query by array
     *
     * @param   array $data
     * @return  string
     */
    private function genInsert(array $data): string
    {
        // Current table
        $table = $this->getCollection();

        // Generate array of fields for insert
        $fieldNames = implode(',', array_keys($data));

        // Generate line with data for update
        $fieldDetails = ':' . implode(', :', array_keys($data));

        return "INSERT INTO $table ($fieldNames) VALUES ($fieldDetails)";
    }

    /**
     * Insert in database and return of inserted element
     *
     * @param   array $data array of columns and values
     * @param   string $id field name of ID which must be returned
     * @return  mixed
     */
    public function insert(array $data, string $id = null)
    {
        // Prepare query
        $query = $this->genInsert($data);
        $statement = $this->getInstance()->prepare($query);

        // Bind values
        foreach ($data as $key => $value) {
            $statement->bindValue(":$key", $value, $this->isPdoInt($value));
        }

        // Execute operation
        $statement->execute();

        return (null !== $id)
            // Return ID of inserted element
            ? $this->getInstance()->lastInsertId($id)
            // Return count of affected rows
            : $statement->rowCount();
    }

    /**
     * Generate the line by provided keys
     *
     * @param   array $array array with data
     * @param   string $glue by this glue need merge items
     * @param   string $name name of field for PDO->bindValue
     * @return  string
     */
    private function genLine(array $array, string $glue, string $name): string
    {
        $line = '';
        $i = 0;
        foreach ($array as $key => $value) {
            $line .= (($i !== 0) ? null : $glue) . "$key = :${name}_$key";
            $i = 1;
        }
        return $line;
    }

    /**
     * Generate set of fields
     *
     * @param   array $array
     * @return  string
     */
    private function genFields(array $array): string
    {
        return $this->genLine($array, ', ', 'field');
    }

    /**
     * Generate WHERE line
     *
     * @param   array $array
     * @return  string
     */
    private function genWhere(array $array): string
    {
        return $this->genLine($array, ' AND ', 'where');
    }

    /**
     * Generate update query
     *
     * @param   array $data
     * @param   array $where
     * @return  string
     */
    private function genUpdate(array $data, array $where): string
    {
        // Current table
        $table = $this->getCollection();

        // Generate line with data for update
        $fieldDetails = !empty($data)
            ? $this->genFields($data)
            : '';

        // Generate where line
        $whereDetails = !empty($where)
            ? ' WHERE ' . $this->genWhere($where)
            : '';

        return "UPDATE $table SET $fieldDetails $whereDetails";
    }

    /**
     * Update method
     *
     * @param   array $data array of columns and values
     * @param   array $where array with data for filtering
     * @return  mixed
     */
    public function update(array $data, array $where = [])
    {
        // Prepare query
        $query = $this->genUpdate($data, $where);
        $statement = $this->getInstance()->prepare($query);

        // Bind field values
        foreach ($data as $key => $value) {
            $statement->bindValue(":field_$key", $value, $this->isPdoInt($value));
        }

        // Bind where values
        foreach ($where as $key => $value) {
            $statement->bindValue(":where_$key", $value, $this->isPdoInt($value));
        }

        // Execute operation
        $statement->execute();

        // Return count of affected rows
        return $statement->rowCount();
    }

    /**
     * Delete rows from database
     *
     * @param   array $where array with data for filtering
     * @return  mixed
     */
    public function delete(array $where)
    {
        // Current table
        $table = $this->getCollection();

        // Generate where line
        $whereDetails = !empty($where)
            ? ' WHERE ' . $this->genWhere($where)
            : '';

        // Prepare query
        $statement = $this->getInstance()->prepare("DELETE FROM $table $whereDetails");

        // Bind where values
        foreach ($where as $key => $value) {
            $statement->bindValue(":where_$key", $value, $this->isPdoInt($value));
        }

        // Execute operation
        $statement->execute();

        // Return count of affected rows
        return $statement->rowCount();
    }

    /**
     * Clean table function
     *
     * @return mixed
     */
    public function truncate()
    {
        // Current table
        $table = $this->getInstance();

        // Exec the truncate command
        return $this->rawSQL(["TRUNCATE TABLE $table"]);
    }
}


1			<?php
2
3			namespace DrMVC\Database\Drivers;
4
5			use \DrMVC\Database\SQLException;
6			use \PDO;
7
8			abstract class SQL extends Driver implements SQLInterface
9			{
10			/**
11			* Get current connection
12			*
13			* @return PDO
14			*/
15			public function getInstance(): PDO
16			{
17			return $this->_instance;
18			}
19
20			/**
21			* Check if input value is integer
22			*
23			* @param mixed $value
24			* @return int
25			*/
26			private function isPdoInt($value): int
27			{
28			return \is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR;
29			}
30
31			/**
32			* Initiate connection to database
33			*
34			* @return DriverInterface
35			*/
36			public function connect(): DriverInterface
37			{
38			try {
39			$connection = new PDO(
40			$this->getDsn(),
41			$this->getParam('username'),
42			$this->getParam('password')
43			);
44			$this->setInstance($connection);
45
46			} catch (SQLException $e) {
47			// __construct
48			}
49
50			return $this;
51			}
52
53			/**
54			* Generate basic select query
55			*
56			* @param array $where
57			* @return string
58			*/
59			private function getSelect(array $where): string
60			{
61			// Current table
62			$table = $this->getCollection();
63
64			// Generate where line
65			$whereDetails = !empty($where)
66			? ' WHERE ' . $this->genWhere($where)
67			: '';
68
69			return "SELECT * FROM $table $whereDetails";
70			}
71
72			/**
73			* Run a select statement against the database
74			*
75			* @param array $where array with data for filtering
76			* @return mixed
77			*/
78			public function select(array $where = [])
79			{
80			// Set statement
81			$query = $this->getSelect($where);
82			$statement = $this->getInstance()->prepare($query);
83
84			// Bind where values
85			foreach ($where as $key => $value) {
86			$statement->bindValue(":where_$key", $value, $this->isPdoInt($value));
87			}
88
89			// Execute operation
90			$statement->execute();
91
92			// Return object
93			return $statement->fetchAll(PDO::FETCH_OBJ);
94			}
95
96			/**
97			* Execute raw query, without generation, useful for some advanced operations
98			*
99			* @param array $arguments array of arguments
100			* @return mixed
101			*/
102			public function rawSQL(array $arguments)
103			{
104			/*
105			* @param string $query pure sql query
106			* @param array $bind array with values in [':key' => 'value'] format
107			* @param bool $fetch make fetch and return data?
108			*/
109			@list($query, $bind, $fetch) = $arguments;
110			// Notices disabled
111
112			// Set statement
113			$statement = $this->getInstance()->prepare($query);
114
115			// Execute operation
116			$statement->execute($bind);
117
118			return (true === $fetch)
119			// Return object
120			? $statement->fetchAll(PDO::FETCH_OBJ)
121			// Return count of affected rows
122			: $statement->rowCount();
123			}
124
125			/**
126			* Generate INSERT query by array
127			*
128			* @param array $data
129			* @return string
130			*/
131			private function genInsert(array $data): string
132			{
133			// Current table
134			$table = $this->getCollection();
135
136			// Generate array of fields for insert
137			$fieldNames = implode(',', array_keys($data));
138
139			// Generate line with data for update
140			$fieldDetails = ':' . implode(', :', array_keys($data));
141
142			return "INSERT INTO $table ($fieldNames) VALUES ($fieldDetails)";
143			}
144
145			/**
146			* Insert in database and return of inserted element
147			*
148			* @param array $data array of columns and values
149			* @param string $id field name of ID which must be returned
150			* @return mixed
151			*/
152			public function insert(array $data, string $id = null)
153			{
154			// Prepare query
155			$query = $this->genInsert($data);
156			$statement = $this->getInstance()->prepare($query);
157
158			// Bind values
159			foreach ($data as $key => $value) {
160			$statement->bindValue(":$key", $value, $this->isPdoInt($value));
161			}
162
163			// Execute operation
164			$statement->execute();
165
166			return (null !== $id)
167			// Return ID of inserted element
168			? $this->getInstance()->lastInsertId($id)
169			// Return count of affected rows
170			: $statement->rowCount();
171			}
172
173			/**
174			* Generate the line by provided keys
175			*
176			* @param array $array array with data
177			* @param string $glue by this glue need merge items
178			* @param string $name name of field for PDO->bindValue
179			* @return string
180			*/
181			private function genLine(array $array, string $glue, string $name): string
182			{
183			$line = '';
184			$i = 0;
185			foreach ($array as $key => $value) {
186			$line .= (($i !== 0) ? null : $glue) . "$key = :${name}_$key";
187			$i = 1;
188			}
189			return $line;
190			}
191
192			/**
193			* Generate set of fields
194			*
195			* @param array $array
196			* @return string
197			*/
198			private function genFields(array $array): string
199			{
200			return $this->genLine($array, ', ', 'field');
201			}
202
203			/**
204			* Generate WHERE line
205			*
206			* @param array $array
207			* @return string
208			*/
209			private function genWhere(array $array): string
210			{
211			return $this->genLine($array, ' AND ', 'where');
212			}
213
214			/**
215			* Generate update query
216			*
217			* @param array $data
218			* @param array $where
219			* @return string
220			*/
221			private function genUpdate(array $data, array $where): string
222			{
223			// Current table
224			$table = $this->getCollection();
225
226			// Generate line with data for update
227			$fieldDetails = !empty($data)
228			? $this->genFields($data)
229			: '';
230
231			// Generate where line
232			$whereDetails = !empty($where)
233			? ' WHERE ' . $this->genWhere($where)
234			: '';
235
236			return "UPDATE $table SET $fieldDetails $whereDetails";
237			}
238
239			/**
240			* Update method
241			*
242			* @param array $data array of columns and values
243			* @param array $where array with data for filtering
244			* @return mixed
245			*/
246			public function update(array $data, array $where = [])
247			{
248			// Prepare query
249			$query = $this->genUpdate($data, $where);
250			$statement = $this->getInstance()->prepare($query);
251
252			// Bind field values
253			foreach ($data as $key => $value) {
254			$statement->bindValue(":field_$key", $value, $this->isPdoInt($value));
255			}
256
257			// Bind where values
258			foreach ($where as $key => $value) {
259			$statement->bindValue(":where_$key", $value, $this->isPdoInt($value));
260			}
261
262			// Execute operation
263			$statement->execute();
264
265			// Return count of affected rows
266			return $statement->rowCount();
267			}
268
269			/**
270			* Delete rows from database
271			*
272			* @param array $where array with data for filtering
273			* @return mixed
274			*/
275			public function delete(array $where)
276			{
277			// Current table
278			$table = $this->getCollection();
279
280			// Generate where line
281			$whereDetails = !empty($where)
282			? ' WHERE ' . $this->genWhere($where)
283			: '';
284
285			// Prepare query
286			$statement = $this->getInstance()->prepare("DELETE FROM $table $whereDetails");
287
288			// Bind where values
289			foreach ($where as $key => $value) {
290			$statement->bindValue(":where_$key", $value, $this->isPdoInt($value));
291			}
292
293			// Execute operation
294			$statement->execute();
295
296			// Return count of affected rows
297			return $statement->rowCount();
298			}
299
300			/**
301			* Clean table function
302			*
303			* @return mixed
304			*/
305			public function truncate()
306			{
307			// Current table
308			$table = $this->getInstance();
309
310			// Exec the truncate command
311			return $this->rawSQL(["TRUNCATE TABLE $table"]);
312			}
313			}
314

drmvc / database

Push — master ( 55ad28...46082e )

SQL::genWhere() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like