SQL::genFields() - Code Metrics - Inspection of "tunes of rawsql for dbunit tests" - drmvc/database - Measure and Improve Code Quality continuously with Scrutinizer

Test Setup Failed

Push — master ( d30f81...8e593b )

by Mr

created 2018-04-12 07:13 UTC

SQL::genFields() 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')
            );

            // We allow to print the errors whenever there is one
            $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $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)
    {
        if (!isset($arguments[1])) {
            $arguments[1] = [];
        }
        if (!isset($arguments[2])) {
            $arguments[2] = false;
        }
        /*
         * @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;

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

drmvc / database

Push — master ( d30f81...8e593b )

SQL::genFields() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like