SQL::insert() - Code Metrics - Inspection of "insert and update methods simplification" - drmvc/database - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( dbbc4c...80942e )

by Mr

created 2018-03-31 00:44 UTC

SQL::insert() A

↳ Parent: SQL

Complexity

Conditions	3
Paths	4

Size

Total Lines	19
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	19
rs	9.4285
c	0
b	0
f	0
cc	3
eloc	8
nc	4
nop	2

<?php

namespace DrMVC\Database\Drivers;

use DrMVC\Database\SQLException;

abstract class SQL extends Driver
{
    /**
     * Initiate connection to database
     *
     * @return  DriverInterface
     */
    public function connect(): DriverInterface
    {
        try {
            $connection = new \PDO(
                $this->getDsn(),
                $this->getParam('username'),
                $this->getParam('password')
            );
        } catch (SQLException $e) {
            // __construct
        }
        $this->setConnection($connection);

        return $this;
    }

    /**
     * Close database connection
     *
     * @return  DriverInterface
     */
    public function disconnect(): DriverInterface
    {
        $this->setConnection(null);
        return $this;
    }

    /**
     * Run a select statement against the database
     *
     * @param   string $query
     * @param   array $data
     * @return  array|object
     */
    public function select(string $query, array $data = [])
    {
        // Set statement
        $statement = $this->getConnection()->prepare($query);

        // Parse parameters from array
        foreach ($data as $key => $value) {
            $value_type = \is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR;
            $statement->bindValue($key, $value, $value_type);
        }

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

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

    /**
     * Exec query without return, create table for example
     *
     * @param   string $query
     * @return  mixed
     */
    public function exec(string $query)
    {
        return $this->getConnection()->exec($query);
    }

    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->_connection->prepare($query);

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

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

        return (null !== $id)
            // Return ID of inserted element
            ? $this->getConnection()->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');
    }

    private function getUpdate(array $data, array $where): string
    {
        // Current table
        $table = $this->getConnection();

        // 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 of columns and values
     * @return mixed
     */
    public function update(array $data, array $where)
    {
        // Prepare query
        $query = $this->getUpdate($data, $where);
        $statement = $this->getConnection()->prepare($query);

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

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

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

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

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

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

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

        // Bind where values
        foreach ($where as $key => $value) {
            $statement->bindValue(":where_$key", $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->getConnection();

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


1			<?php
2
3			namespace DrMVC\Database\Drivers;
4
5			use DrMVC\Database\SQLException;
6
7			abstract class SQL extends Driver
8			{
9			/**
10			* Initiate connection to database
11			*
12			* @return DriverInterface
13			*/
14			public function connect(): DriverInterface
15			{
16			try {
17			$connection = new \PDO(
18			$this->getDsn(),
19			$this->getParam('username'),
20			$this->getParam('password')
21			);
22			} catch (SQLException $e) {
23			// __construct
24			}
25			$this->setConnection($connection);
26
27			return $this;
28			}
29
30			/**
31			* Close database connection
32			*
33			* @return DriverInterface
34			*/
35			public function disconnect(): DriverInterface
36			{
37			$this->setConnection(null);
38			return $this;
39			}
40
41			/**
42			* Run a select statement against the database
43			*
44			* @param string $query
45			* @param array $data
46			* @return array\|object
47			*/
48			public function select(string $query, array $data = [])
49			{
50			// Set statement
51			$statement = $this->getConnection()->prepare($query);
52
53			// Parse parameters from array
54			foreach ($data as $key => $value) {
55			$value_type = \is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR;
56			$statement->bindValue($key, $value, $value_type);
57			}
58
59			// Execute the statement
60			$statement->execute();
61
62			// Return result
63			return $statement->fetchAll(\PDO::FETCH_OBJ);
64			}
65
66			/**
67			* Exec query without return, create table for example
68			*
69			* @param string $query
70			* @return mixed
71			*/
72			public function exec(string $query)
73			{
74			return $this->getConnection()->exec($query);
75			}
76
77			private function genInsert(array $data): string
78			{
79			// Current table
80			$table = $this->getCollection();
81
82			// Generate array of fields for insert
83			$fieldNames = implode(',', array_keys($data));
84
85			// Generate line with data for update
86			$fieldDetails = ':' . implode(', :', array_keys($data));
87
88			return "INSERT INTO $table ($fieldNames) VALUES ($fieldDetails)";
89			}
90
91			/**
92			* Insert in database and return of inserted element
93			*
94			* @param array $data array of columns and values
95			* @param string $id field name of ID which must be returned
96			* @return mixed
97			*/
98			public function insert(array $data, string $id = null)
99			{
100			// Prepare query
101			$query = $this->genInsert($data);
102			$statement = $this->_connection->prepare($query);
103
104			// Bind values
105			foreach ($data as $key => $value) {
106			$statement->bindValue(":$key", $value);
107			}
108
109			// Execute operation
110			$statement->execute();
111
112			return (null !== $id)
113			// Return ID of inserted element
114			? $this->getConnection()->lastInsertId($id)
115			// Return count of affected rows
116			: $statement->rowCount();
117			}
118
119			/**
120			* Generate the line by provided keys
121			*
122			* @param array $array array with data
123			* @param string $glue by this glue need merge items
124			* @param string $name name of field for PDO->bindValue
125			* @return string
126			*/
127			private function genLine(array $array, string $glue, string $name): string
128			{
129			$line = '';
130			$i = 0;
131			foreach ($array as $key => $value) {
132			$line .= (($i !== 0) ? null : $glue) . "$key = :${name}_$key";
133			$i = 1;
134			}
135			return $line;
136			}
137
138			/**
139			* Generate set of fields
140			*
141			* @param array $array
142			* @return string
143			*/
144			private function genFields(array $array): string
145			{
146			return $this->genLine($array, ', ', 'field');
147			}
148
149			/**
150			* Generate WHERE line
151			*
152			* @param array $array
153			* @return string
154			*/
155			private function genWhere(array $array): string
156			{
157			return $this->genLine($array, ' AND ', 'where');
158			}
159
160			private function getUpdate(array $data, array $where): string
161			{
162			// Current table
163			$table = $this->getConnection();
164
165			// Generate line with data for update
166			$fieldDetails = !empty($data)
167			? $this->genFields($data)
168			: '';
169
170			// Generate where line
171			$whereDetails = !empty($where)
172			? ' WHERE ' . $this->genWhere($where)
173			: '';
174
175			return "UPDATE $table SET $fieldDetails $whereDetails";
176			}
177
178			/**
179			* Update method
180			*
181			* @param array $data array of columns and values
182			* @param array $where array of columns and values
183			* @return mixed
184			*/
185			public function update(array $data, array $where)
186			{
187			// Prepare query
188			$query = $this->getUpdate($data, $where);
189			$statement = $this->getConnection()->prepare($query);
190
191			// Bind field values
192			foreach ($data as $key => $value) {
193			$statement->bindValue(":field_$key", $value);
194			}
195
196			// Bind where values
197			foreach ($where as $key => $value) {
198			$statement->bindValue(":where_$key", $value);
199			}
200
201			// Execute operation
202			$statement->execute();
203
204			// Return count of affected rows
205			return $statement->rowCount();
206			}
207
208			/**
209			* Delete rows from database
210			*
211			* @param array $where
212			* @return mixed
213			*/
214			public function delete(array $where)
215			{
216			// Current table
217			$table = $this->getConnection();
218
219			// Generate where line
220			$whereDetails = !empty($where)
221			? ' WHERE ' . $this->genWhere($where)
222			: '';
223
224			// Prepare query
225			$statement = $this->getConnection()->prepare("DELETE FROM $table $whereDetails");
226
227			// Bind where values
228			foreach ($where as $key => $value) {
229			$statement->bindValue(":where_$key", $value);
230			}
231
232			// Execute operation
233			$statement->execute();
234
235			// Return count of affected rows
236			return $statement->rowCount();
237			}
238
239			/**
240			* Clean table function
241			*
242			* @return mixed
243			*/
244			public function truncate()
245			{
246			// Current table
247			$table = $this->getConnection();
248
249			// Exec the truncate command
250			return $this->exec("TRUNCATE TABLE $table");
251			}
252			}
253

drmvc / database

Push — master ( dbbc4c...80942e )

SQL::insert() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like