SQL::getInstance() - Code Metrics - Inspection of "interfaces refactoring, few tests fixed, bug with..." - drmvc/database - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( a3068c...d16122 )

by Mr

created 2018-07-18 10:11 UTC

SQL::getInstance() 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
rs	10
c	0
b	0
f	0
cc	1
eloc	1
nc	1
nop	0

<?php

namespace DrMVC\Database\Drivers;

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

drmvc / database

Push — master ( a3068c...d16122 )

SQL::getInstance() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like