TSQLQueryBuilderBasic::from() - Code Metrics - mosbth/cdatabase - Measure and Improve Code Quality continuously with Scrutinizer

TSQLQueryBuilderBasic::from() A
last analyzed 2019-04-09 20:46 UTC

↳ Parent: TSQLQueryBuilderBasic

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes

Metric	Value
dl	0
loc	6
ccs	3
cts	3
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	1
crap	1

<?php

namespace Mos\Database;

/**
 * Database wrapper, provides a database API for the framework but hides details of implementation.
 *
 */
trait TSQLQueryBuilderBasic
{
    /**
     * Properties
     */
    private $dialect;   // SQL dialect for a certain database
    private $sql;       // The query built
    private $prefix;    // Prefix to attach to all table names

    private $columns;   // Columns to select
    private $from;      // From part
    private $join;      // Join part
    private $where;     // Where part
    private $groupby;   // Group by part
    private $orderby;   // Order by part
    private $limit;     // Limit by part
    private $offset;    // Offset by part




    /**
     * Get SQL.
     *
     * @return string as sql-query
     */
    public function getSQL()
    {
        if ($this->sql) {
            return $this->sql;
        }
        return $this->build();
    }



    /**
     * Build SQL.
     *
     * @return string as SQL query
     */
    protected function build()
    {
        $sql = "SELECT\n\t"
            . $this->columns . "\n"
            . $this->from . "\n"
            . ($this->join    ? $this->join           : null)
            . ($this->where   ? $this->where . "\n"   : null)
            . ($this->groupby ? $this->groupby . "\n" : null)
            . ($this->orderby ? $this->orderby . "\n" : null)
            . ($this->limit   ? $this->limit . "\n"   : null)
            . ($this->offset  ? $this->offset . "\n"  : null)
            . ";";

        return $sql;
    }



    /**
     * Create a inner or outer join.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     * @param string $type      what type of join to create.
     *
     * @return void
     */
    private function createJoin($table, $condition, $type)
    {
        $this->join .= $type . " JOIN " . $this->prefix . $table
            . "\n\tON " . $condition . "\n";

        return $this;
    }



    /**
     * Set database type to consider when generating SQL.
     *
     * @param string $dialect representing database type.
     *
     * @return void
     */
    public function setSQLDialect($dialect)
    {
        $this->dialect = $dialect;
    }



    /**
     * Set a table prefix.
     *
     * @param string $prefix to use in front of all tables.
     *
     * @return void
     */
    public function setTablePrefix($prefix)
    {
        $this->prefix = $prefix;
    }



    /**
     * Utility to check if array is associative array.
     *
     * http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-sequential/4254008#4254008
     *
     * @param array $array input array to check.
     *
     * @return boolean true if array is associative array with at least one key, else false.
     *
     */
    private function isAssoc($array)
    {
        return (bool) count(array_filter(array_keys($array), 'is_string'));
    }



    /**
     * Create a table.
     *
     * @param string $name    the table name.
     * @param array  $columns the columns in the table.
     *
     * @return $this
     */
    public function createTable($name, $columns)
    {
        $cols = null;

        foreach ($columns as $col => $options) {
            $cols .= "\t" . $col . ' ' . implode(' ', $options) . ",\n";
        }
        $cols = substr($cols, 0, -2);

        $this->sql = "CREATE TABLE "
            . $this->prefix
            . $name
            . "\n(\n"
            . $cols
            . "\n);\n";

        if ($this->dialect == 'sqlite') {
            $this->sql = str_replace('auto_increment', '', $this->sql);
        }

        return $this;
    }



    /**
     * Drop a table.
     *
     * @param string $name the table name.
     *
     * @return $this
     */
    public function dropTable($name)
    {
        $this->sql = "DROP TABLE "
            . $this->prefix
            . $name
            . ";\n";

        return $this;
    }



    /**
     * Drop a table if it exists.
     *
     * @param string $name the table name.
     *
     * @return $this
     */
    public function dropTableIfExists($name)
    {
        $this->sql = "DROP TABLE IF EXISTS "
            . $this->prefix
            . $name
            . ";\n";

        return $this;
    }



    /**
     * Create a proper column value arrays from incoming $columns and $values.
     *
     * @param array       $columns
     * @param array|null  $values
     *
     * @return array that can be parsed with list($columns, $values)
     */
    public function mapColumnsWithValues($columns, $values)
    {
        // If $values is null, then use $columns to build it up
        if (is_null($values)) {


            if ($this->isAssoc($columns)) {


                // Incoming is associative array, split it up in two
                $values = array_values($columns);
                $columns = array_keys($columns);


            } else {


                // Create an array of '?' to match number of columns
                $max = count($columns);
                for ($i = 0; $i < $max; $i++) {
                    $values[] = '?';
                }
            }
        }

        return [$columns, $values];
    }



    /**
     * Build a insert-query.
     *
     * @param string $table   the table name.
     * @param array  $columns to insert och key=>value with columns and values.
     * @param array  $values  to insert or empty if $columns has both columns and values.
     *
     * @return void
     */
    public function insert($table, $columns, $values = null)
    {
        list($columns, $values) = $this->mapColumnsWithValues($columns, $values);

        if (count($columns) !== count($values)) {
            throw new \Exception("Columns does not match values, not equal items.");
        }

        $cols = null;
        $vals = null;

        $max = count($columns);
        for ($i = 0; $i < $max; $i++) {
            $cols .= $columns[$i] . ', ';

            $val = $values[$i];

            if ($val == '?') {
                $vals .= $val . ', ';
            } else {
                $vals .= (is_string($val)
                    ? "'$val'"
                    : $val)
                    . ', ';
            }
        }

        $cols = substr($cols, 0, -2);
        $vals = substr($vals, 0, -2);

        $this->sql = "INSERT INTO "
            . $this->prefix
            . $table
            . "\n\t("
            . $cols
            . ")\n"
            . "\tVALUES\n\t("
            . $vals
            . ");\n";
    }



    /**
     * Build an update-query.
     *
     * @param string $table   the table name.
     * @param array  $columns to update or key=>value with columns and values.
     * @param array  $values  to update or empty if $columns has bot columns and values.
     * @param array  $where   limit which rows are updated.
     *
     * @return void
     */
    public function update($table, $columns, $values = null, $where = null)
    {
        // If $values is string, then move that to $where
        if (is_string($values)) {
            $where = $values;
            $values = null;
        }

        list($columns, $values) = $this->mapColumnsWithValues($columns, $values);

        if (count($columns) != count($values)) {
            throw new \Exception("Columns does not match values, not equal items.");
        }

        $cols = null;
        $max = count($columns);
        
        for ($i = 0; $i < $max; $i++) {
            $cols .= "\t" . $columns[$i] . ' = ';

            $val = $values[$i];
            if ($val == '?') {
                $cols .= $val . ",\n";
            } else {
                $cols .= (is_string($val)
                    ? "'$val'"
                    : $val)
                    . ",\n";
            }
        }

        $cols = substr($cols, 0, -2);

        $this->sql = "UPDATE "
            . $this->prefix
            . $table
            . "\nSET\n"
            . $cols
            . "\nWHERE "
            . $where
            . "\n;\n";
    }



    /**
     * Build a delete-query.
     *
     * @param string $table the table name.
     * @param array  $where limit which rows are updated.
     *
     * @return void
     */
    public function delete($table, $where = null)
    {
        if (isset($where)) {
            $where = " WHERE " . $where;
        }

        $this->sql = "DELETE FROM "
            . $this->prefix
            . $table
            . $where
            . ";\n";
    }



    /**
     * Clear all previous sql-code.
     *
     * @return void
     */
    protected function clear()
    {
        $this->sql      = null;
        $this->columns  = null;
        $this->from     = null;
        $this->join     = null;
        $this->where    = null;
        $this->groupby  = null;
        $this->orderby  = null;
        $this->limit    = null;
        $this->offset   = null;
    }



    /**
     * Build a select-query.
     *
     * @param string $columns which columns to select.
     *
     * @return $this
     */
    public function select($columns = '*')
    {
        $this->clear();
        $this->columns = $columns;

        return $this;
    }



    /**
     * Build the from part.
     *
     * @param string $table name of table.
     *
     * @return $this
     */
    public function from($table)
    {
        $this->from = "FROM " . $this->prefix . $table;

        return $this;
    }



    /**
     * Build the inner join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @return $this
     */
    public function join($table, $condition)
    {

        return $this->createJoin($table, $condition, 'INNER');
    }



    /**
     * Build the right join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @return $this
     */
    public function rightJoin($table, $condition)
    {
        return $this->createJoin($table, $condition, 'RIGHT OUTER');
    }



    /**
     * Build the left join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @return $this
     */
    public function leftJoin($table, $condition)
    {
        return $this->createJoin($table, $condition, 'LEFT OUTER');
    }



    /**
     * Build the where part.
     *
     * @param string $condition for building the where part of the query.
     *
     * @return $this
     */
    public function where($condition)
    {
        $this->where = "WHERE \n\t(" . $condition . ")";

        return $this;
    }



    /**
     * Build the where part with conditions.
     *
     * @param string $condition for building the where part of the query.
     *
     * @return $this
     */
    public function andWhere($condition)
    {
        $this->where .= "\n\tAND (" . $condition . ")";

        return $this;
    }



    /**
    * Build the group by part.
    *
    * @param string $condition for building the group by part of the query.
    *
    * @return $this
    */
    public function groupBy($condition)
    {
        $this->groupby = "GROUP BY " . $condition;

        return $this;
    }



    /**
    * Build the order by part.
    *
    * @param string $condition for building the where part of the query.
    *
    * @return $this
    */
    public function orderBy($condition)
    {
        $this->orderby = "ORDER BY " . $condition;

        return $this;
    }



    /**
     * Build the LIMIT by part.
     *
     * @param string $condition for building the LIMIT part of the query.
     *
     * @return $this
     */
    public function limit($condition)
    {
        $this->limit = "LIMIT \n\t" . intval($condition);

        return $this;
    }



    /**
     * Build the OFFSET by part.
     *
     * @param string $condition for building the OFFSET part of the query.
     *
     * @return $this
     */
    public function offset($condition)
    {
        $this->offset = "OFFSET \n\t" . intval($condition);

        return $this;
    }
}


1		<?php
2
3		namespace Mos\Database;
4
5		/**
6		* Database wrapper, provides a database API for the framework but hides details of implementation.
7		*
8		*/
9		trait TSQLQueryBuilderBasic
10		{
11		/**
12		* Properties
13		*/
14		private $dialect; // SQL dialect for a certain database
15		private $sql; // The query built
16		private $prefix; // Prefix to attach to all table names
17
18		private $columns; // Columns to select
19		private $from; // From part
20		private $join; // Join part
21		private $where; // Where part
22		private $groupby; // Group by part
23		private $orderby; // Order by part
24		private $limit; // Limit by part
25		private $offset; // Offset by part
26
27
28
29
30		/**
31		* Get SQL.
32		*
33		* @return string as sql-query
34		*/
35	25	public function getSQL()
36		{
37	25	if ($this->sql) {
38	14	return $this->sql;
39		}
40	16	return $this->build();
41		}
42
43
44
45		/**
46		* Build SQL.
47		*
48		* @return string as SQL query
49		*/
50	16	protected function build()
51		{
52		$sql = "SELECT\n\t"
53	16	. $this->columns . "\n"
54	16	. $this->from . "\n"
55	16	. ($this->join ? $this->join : null)
56	16	. ($this->where ? $this->where . "\n" : null)
57	16	. ($this->groupby ? $this->groupby . "\n" : null)
58	16	. ($this->orderby ? $this->orderby . "\n" : null)
59	16	. ($this->limit ? $this->limit . "\n" : null)
60	16	. ($this->offset ? $this->offset . "\n" : null)
61	16	. ";";
62
63	16	return $sql;
64		}
65
66
67
68		/**
69		* Create a inner or outer join.
70		*
71		* @param string $table name of table.
72		* @param string $condition to join.
73		* @param string $type what type of join to create.
74		*
75		* @return void
76		*/
77	3	private function createJoin($table, $condition, $type)
78		{
79	3	$this->join .= $type . " JOIN " . $this->prefix . $table
80	3	. "\n\tON " . $condition . "\n";
81
82	3	return $this;
83		}
84
85
86
87		/**
88		* Set database type to consider when generating SQL.
89		*
90		* @param string $dialect representing database type.
91		*
92		* @return void
93		*/
94	9	public function setSQLDialect($dialect)
95		{
96	9	$this->dialect = $dialect;
97	9	}
98
99
100
101		/**
102		* Set a table prefix.
103		*
104		* @param string $prefix to use in front of all tables.
105		*
106		* @return void
107		*/
108	18	public function setTablePrefix($prefix)
109		{
110	18	$this->prefix = $prefix;
111	18	}
112
113
114
115		/**
116		* Utility to check if array is associative array.
117		*
118		* http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-sequential/4254008#4254008
119		*
120		* @param array $array input array to check.
121		*
122		* @return boolean true if array is associative array with at least one key, else false.
123		*
124		*/
125	4	private function isAssoc($array)
126		{
127	4	return (bool) count(array_filter(array_keys($array), 'is_string'));
128		}
129
130
131
132		/**
133		* Create a table.
134		*
135		* @param string $name the table name.
136		* @param array $columns the columns in the table.
137		*
138		* @return $this
139		*/
140	2	public function createTable($name, $columns)
141		{
142	2	$cols = null;
143
144	2	foreach ($columns as $col => $options) {
145	2	$cols .= "\t" . $col . ' ' . implode(' ', $options) . ",\n";
146	2	}
147	2	$cols = substr($cols, 0, -2);
148
149	2	$this->sql = "CREATE TABLE "
150	2	. $this->prefix
151	2	. $name
152	2	. "\n(\n"
153	2	. $cols
154	2	. "\n);\n";
155
156	2	if ($this->dialect == 'sqlite') {
157	1	$this->sql = str_replace('auto_increment', '', $this->sql);
158	1	}
159
160	2	return $this;
161		}
162
163
164
165		/**
166		* Drop a table.
167		*
168		* @param string $name the table name.
169		*
170		* @return $this
171		*/
172	2	public function dropTable($name)
173		{
174	2	$this->sql = "DROP TABLE "
175	2	. $this->prefix
176	2	. $name
177	2	. ";\n";
178
179	2	return $this;
180		}
181
182
183
184		/**
185		* Drop a table if it exists.
186		*
187		* @param string $name the table name.
188		*
189		* @return $this
190		*/
191	1	public function dropTableIfExists($name)
192		{
193	1	$this->sql = "DROP TABLE IF EXISTS "
194	1	. $this->prefix
195	1	. $name
196	1	. ";\n";
197
198	1	return $this;
199		}
200
201
202
203		/**
204		* Create a proper column value arrays from incoming $columns and $values.
205		*
206		* @param array $columns
207		* @param array\|null $values
208		*
209		* @return array that can be parsed with list($columns, $values)
210		*/
211	9	public function mapColumnsWithValues($columns, $values)
212		{
213		// If $values is null, then use $columns to build it up
214	9	if (is_null($values)) {
		0 ignored issues – show Coding Style introduced 2019-04-09 20:46 UTC by Report Bug Copy Issue Report Blank line found at start of control structure Loading history...
215
216	4	if ($this->isAssoc($columns)) {
		0 ignored issues – show Coding Style introduced 2019-04-09 20:46 UTC by Report Bug Copy Issue Report Blank line found at start of control structure Loading history...
217
218		// Incoming is associative array, split it up in two
219	3	$values = array_values($columns);
220	3	$columns = array_keys($columns);
221
		0 ignored issues – show Coding Style introduced 2019-04-09 20:46 UTC by Report Bug Copy Issue Report Blank line found at end of control structure Loading history...
222	3	} else {
		0 ignored issues – show Coding Style introduced 2019-04-09 20:46 UTC by Report Bug Copy Issue Report Blank line found at start of control structure Loading history...
223
224		// Create an array of '?' to match number of columns
225	1	$max = count($columns);
226	1	for ($i = 0; $i < $max; $i++) {
227	1	$values[] = '?';
228	1	}
229		}
230	4	}
231
232	9	return [$columns, $values];
233		}
234
235
236
237		/**
238		* Build a insert-query.
239		*
240		* @param string $table the table name.
241		* @param array $columns to insert och key=>value with columns and values.
242		* @param array $values to insert or empty if $columns has both columns and values.
243		*
244		* @return void
245		*/
246	6	public function insert($table, $columns, $values = null)
247		{
248	6	list($columns, $values) = $this->mapColumnsWithValues($columns, $values);
249
250	6	if (count($columns) !== count($values)) {
251	1	throw new \Exception("Columns does not match values, not equal items.");
252		}
253
254	5	$cols = null;
255	5	$vals = null;
256
257	5	$max = count($columns);
258	5	for ($i = 0; $i < $max; $i++) {
259	5	$cols .= $columns[$i] . ', ';
260
261	5	$val = $values[$i];
262
263	5	if ($val == '?') {
264	1	$vals .= $val . ', ';
265	1	} else {
266	4	$vals .= (is_string($val)
267	4	? "'$val'"
268	4	: $val)
269	4	. ', ';
270		}
271	5	}
272
273	5	$cols = substr($cols, 0, -2);
274	5	$vals = substr($vals, 0, -2);
275
276	5	$this->sql = "INSERT INTO "
277	5	. $this->prefix
278	5	. $table
279	5	. "\n\t("
280	5	. $cols
281	5	. ")\n"
282	5	. "\tVALUES\n\t("
283	5	. $vals
284	5	. ");\n";
285	5	}
286
287
288
289		/**
290		* Build an update-query.
291		*
292		* @param string $table the table name.
293		* @param array $columns to update or key=>value with columns and values.
294		* @param array $values to update or empty if $columns has bot columns and values.
295		* @param array $where limit which rows are updated.
296		*
297		* @return void
298		*/
299	3	public function update($table, $columns, $values = null, $where = null)
300		{
301		// If $values is string, then move that to $where
302	3	if (is_string($values)) {
303	1	$where = $values;
304	1	$values = null;
305	1	}
306
307	3	list($columns, $values) = $this->mapColumnsWithValues($columns, $values);
308
309	3	if (count($columns) != count($values)) {
310	1	throw new \Exception("Columns does not match values, not equal items.");
311		}
312
313	2	$cols = null;
314	2	$max = count($columns);
315
316	2	for ($i = 0; $i < $max; $i++) {
317	2	$cols .= "\t" . $columns[$i] . ' = ';
318
319	2	$val = $values[$i];
320	2	if ($val == '?') {
321	1	$cols .= $val . ",\n";
322	1	} else {
323	1	$cols .= (is_string($val)
324	1	? "'$val'"
325	1	: $val)
326	1	. ",\n";
327		}
328	2	}
329
330	2	$cols = substr($cols, 0, -2);
331
332	2	$this->sql = "UPDATE "
333	2	. $this->prefix
334	2	. $table
335	2	. "\nSET\n"
336	2	. $cols
337	2	. "\nWHERE "
338	2	. $where
339	2	. "\n;\n";
340	2	}
341
342
343
344		/**
345		* Build a delete-query.
346		*
347		* @param string $table the table name.
348		* @param array $where limit which rows are updated.
349		*
350		* @return void
351		*/
352	2	public function delete($table, $where = null)
353		{
354	2	if (isset($where)) {
355	1	$where = " WHERE " . $where;
356	1	}
357
358	2	$this->sql = "DELETE FROM "
359	2	. $this->prefix
360	2	. $table
361	2	. $where
362	2	. ";\n";
363	2	}
364
365
366
367		/**
368		* Clear all previous sql-code.
369		*
370		* @return void
371		*/
372	16	protected function clear()
373		{
374	16	$this->sql = null;
375	16	$this->columns = null;
376	16	$this->from = null;
377	16	$this->join = null;
378	16	$this->where = null;
379	16	$this->groupby = null;
380	16	$this->orderby = null;
381	16	$this->limit = null;
382	16	$this->offset = null;
383	16	}
384
385
386
387		/**
388		* Build a select-query.
389		*
390		* @param string $columns which columns to select.
391		*
392		* @return $this
393		*/
394	16	public function select($columns = '*')
395		{
396	16	$this->clear();
397	16	$this->columns = $columns;
398
399	16	return $this;
400		}
401
402
403
404		/**
405		* Build the from part.
406		*
407		* @param string $table name of table.
408		*
409		* @return $this
410		*/
411	16	public function from($table)
412		{
413	16	$this->from = "FROM " . $this->prefix . $table;
414
415	16	return $this;
416		}
417
418
419
420		/**
421		* Build the inner join part.
422		*
423		* @param string $table name of table.
424		* @param string $condition to join.
425		*
426		* @return $this
427		*/
428	1	public function join($table, $condition)
429		{
430
431	1	return $this->createJoin($table, $condition, 'INNER');
432		}
433
434
435
436		/**
437		* Build the right join part.
438		*
439		* @param string $table name of table.
440		* @param string $condition to join.
441		*
442		* @return $this
443		*/
444	1	public function rightJoin($table, $condition)
445		{
446	1	return $this->createJoin($table, $condition, 'RIGHT OUTER');
447		}
448
449
450
451		/**
452		* Build the left join part.
453		*
454		* @param string $table name of table.
455		* @param string $condition to join.
456		*
457		* @return $this
458		*/
459	1	public function leftJoin($table, $condition)
460		{
461	1	return $this->createJoin($table, $condition, 'LEFT OUTER');
462		}
463
464
465
466		/**
467		* Build the where part.
468		*
469		* @param string $condition for building the where part of the query.
470		*
471		* @return $this
472		*/
473	1	public function where($condition)
474		{
475	1	$this->where = "WHERE \n\t(" . $condition . ")";
476
477	1	return $this;
478		}
479
480
481
482		/**
483		* Build the where part with conditions.
484		*
485		* @param string $condition for building the where part of the query.
486		*
487		* @return $this
488		*/
489	1	public function andWhere($condition)
490		{
491	1	$this->where .= "\n\tAND (" . $condition . ")";
492
493	1	return $this;
494		}
495
496
497
498		/**
499		* Build the group by part.
500		*
501		* @param string $condition for building the group by part of the query.
502		*
503		* @return $this
504		*/
505	1	public function groupBy($condition)
506		{
507	1	$this->groupby = "GROUP BY " . $condition;
508
509	1	return $this;
510		}
511
512
513
514		/**
515		* Build the order by part.
516		*
517		* @param string $condition for building the where part of the query.
518		*
519		* @return $this
520		*/
521	1	public function orderBy($condition)
522		{
523	1	$this->orderby = "ORDER BY " . $condition;
524
525	1	return $this;
526		}
527
528
529
530		/**
531		* Build the LIMIT by part.
532		*
533		* @param string $condition for building the LIMIT part of the query.
534		*
535		* @return $this
536		*/
537	1	public function limit($condition)
538		{
539	1	$this->limit = "LIMIT \n\t" . intval($condition);
540
541	1	return $this;
542		}
543
544
545
546		/**
547		* Build the OFFSET by part.
548		*
549		* @param string $condition for building the OFFSET part of the query.
550		*
551		* @return $this
552		*/
553	1	public function offset($condition)
554		{
555	1	$this->offset = "OFFSET \n\t" . intval($condition);
556
557	1	return $this;
558		}
559		}
560

mosbth / cdatabase

TSQLQueryBuilderBasic::from() A last analyzed 2019-04-09 20:46 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

TSQLQueryBuilderBasic::from() A
last analyzed 2019-04-09 20:46 UTC