SQL::rawSQL() - Code Metrics - Inspection of "docs updated" - drmvc/database - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d08e85...16992a )

by Mr

created 2018-04-12 10:28 UTC

SQL::rawSQL() A

↳ Parent: SQL

Complexity

Conditions	2
Paths	2

Size

Total Lines	23
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	23
rs	9.0856
c	0
b	0
f	0
cc	2
eloc	7
nc	2
nop	1

<?php

namespace DrMVC\Database\Drivers;

use DrMVC\Database\SQLException;
use PDO;

/**
 * Abstract class for work with SQL-based databases (via PDO)
 *
 * @package DrMVC\Database\Drivers
 * @since   3.0
 */
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);
    }

    private function setRawDefauls(array &$arguments)
    {
        // Values for bind
        if (!isset($arguments[1])) {
            $arguments[1] = [];
        }
        // Enable fetchAll
        if (!isset($arguments[2])) {
            $arguments[2] = false;
        }
        return $arguments;
    }

    /**
     * Execute raw query, without generation, useful for some advanced operations
     *
     * @param   array $arguments array of arguments
     * @return  mixed
     */
    public function rawSQL(array $arguments)
    {
        // Small fixes if only query provided
        $this->setRawDefauls($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;

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

drmvc / database

Push — master ( d08e85...16992a )

SQL::rawSQL() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like