OracleGrammar::compileInsert() - Code Metrics - Inspection of "Merge branch 'release/v4.0.3'" - yajra/laravel-oci8 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 4.0 ( 05ba58...a84d33 )

by Arjay

created 2015-12-22 04:42 UTC

OracleGrammar::compileInsert() B

↳ Parent: OracleGrammar

Complexity

Conditions	4
Paths	6

Size

Total Lines	35
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Metric	Value
dl	0
loc	35
rs	8.5806
cc	4
eloc	16
nc	6
nop	2

<?php

namespace Yajra\Oci8\Query\Grammars;

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\Grammar;
use Yajra\Oci8\OracleReservedWords;
use Yajra\Pdo\Oci8\Exceptions\Oci8Exception;

class OracleGrammar extends Grammar
{
    use OracleReservedWords;

    /**
     * The keyword identifier wrapper format.
     *
     * @var string
     */
    protected $wrapper = '%s';

    /**
     * Compile an exists statement into SQL.
     *
     * @param \Illuminate\Database\Query\Builder $query
     * @return string
     */
    public function compileExists(Builder $query)
    {
        $q          = clone $query;
        $q->columns = [];
        $q->selectRaw('1 as "exists"')
          ->whereRaw("rownum = 1");

        return $this->compileSelect($q);
    }

    /**
     * Compile a select query into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder
     * @return string
     */
    public function compileSelect(Builder $query)
    {
        if (is_null($query->columns)) {
            $query->columns = ['*'];
        }

        $components = $this->compileComponents($query);

        // If an offset is present on the query, we will need to wrap the query in
        // a big "ANSI" offset syntax block. This is very nasty compared to the
        // other database systems but is necessary for implementing features.
        if ($this->isPaginationable($query, $components)) {
            return $this->compileAnsiOffset($query, $components);
        }

        return trim($this->concatenate($components));
    }

    /**
     * @param Builder $query
     * @param array $components
     * @return bool
     */
    protected function isPaginationable(Builder $query, array $components)
    {
        return ($query->limit > 0 || $query->offset > 0) && ! array_key_exists('lock', $components);
    }

    /**
     * Create a full ANSI offset clause for the query.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  array $components
     * @return string
     */
    protected function compileAnsiOffset(Builder $query, $components)
    {
        $constraint = $this->compileRowConstraint($query);

        $sql = $this->concatenate($components);

        // We are now ready to build the final SQL query so we'll create a common table
        // expression from the query and get the records with row numbers within our
        // given limit and offset value that we just put on as a query constraint.
        $temp = $this->compileTableExpression($sql, $constraint, $query);

        return $temp;
    }

    /**
     * Compile the limit / offset row constraint for a query.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @return string
     */
    protected function compileRowConstraint($query)
    {
        $start = $query->offset + 1;

        if ($query->limit == 1) {
            return "= 1";
        }

        if ($query->limit > 1) {
            $finish = $query->offset + $query->limit;

            return "between {$start} and {$finish}";
        }

        return ">= {$start}";
    }

    /**
     * Compile a common table expression for a query.
     *
     * @param  string $sql
     * @param  string $constraint
     * @param Builder $query
     * @return string
     */
    protected function compileTableExpression($sql, $constraint, $query)
    {
        if ($query->limit > 1) {
            return "select t2.* from ( select rownum AS \"rn\", t1.* from ({$sql}) t1 ) t2 where t2.\"rn\" {$constraint}";
        } else {
            return "select * from ({$sql}) where rownum {$constraint}";
        }
    }

    /**
     * Compile a truncate table statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @return array
     */
    public function compileTruncate(Builder $query)
    {
        return ['truncate table ' . $this->wrapTable($query->from) => []];
    }

    /**
     * Compile an insert and get ID statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  array $values
     * @param  string $sequence
     * @return string
     */
    public function compileInsertGetId(Builder $query, $values, $sequence = 'id')
    {
        if (empty($sequence)) {
            $sequence = 'id';
        }

        return $this->compileInsert($query, $values) . ' returning ' . $this->wrap($sequence) . ' into ?';
    }

    /**
     * Compile an insert statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  array $values
     * @return string
     */
    public function compileInsert(Builder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (! is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we can just go off the first list of values in this array.
        $parameters = $this->parameterize(reset($values));

        $value = array_fill(0, count($values), "($parameters)");

        if (count($value) > 1) {
            $insertQueries = [];
            foreach ($value as $parameter) {
                $parameter       = (str_replace(['(', ')'], '', $parameter));
                $insertQueries[] = "select " . $parameter . " from dual ";
            }
            $parameters = implode('union all ', $insertQueries);


            return "insert into $table ($columns) $parameters";
        }
        $parameters = implode(', ', $value);

        return "insert into $table ($columns) values $parameters";
    }

    /**
     * Compile an insert with blob field statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  array $values
     * @param  array $binaries
     * @param  string $sequence
     * @return string
     */
    public function compileInsertLob(Builder $query, $values, $binaries, $sequence = 'id')
    {
        if (empty($sequence)) {
            $sequence = 'id';
        }

        $table = $this->wrapTable($query->from);

        if (! is_array(reset($values))) {
            $values = [$values];
        }

        if (! is_array(reset($binaries))) {
            $binaries = [$binaries];
        }

        $columns       = $this->columnize(array_keys(reset($values)));
        $binaryColumns = $this->columnize(array_keys(reset($binaries)));
        $columns .= (empty($columns) ? '' : ', ') . $binaryColumns;

        $parameters       = $this->parameterize(reset($values));
        $binaryParameters = $this->parameterize(reset($binaries));

        $value       = array_fill(0, count($values), "$parameters");
        $binaryValue = array_fill(0, count($binaries), str_replace('?', 'EMPTY_BLOB()', $binaryParameters));

        $value      = array_merge($value, $binaryValue);
        $parameters = implode(', ', array_filter($value));

        return "insert into $table ($columns) values ($parameters) returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
    }

    /**
     * Compile an update statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  array $values
     * @param  array $binaries
     * @param  string $sequence
     * @return string
     */
    public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
    {
        $table = $this->wrapTable($query->from);

        // Each one of the columns in the update statements needs to be wrapped in the
        // keyword identifiers, also a place-holder needs to be created for each of
        // the values in the list of bindings so we can make the sets statements.
        $columns = [];

        foreach ($values as $key => $value) {
            $columns[] = $this->wrap($key) . ' = ' . $this->parameter($value);
        }

        $columns = implode(', ', $columns);

        // set blob variables
        if (! is_array(reset($binaries))) {
            $binaries = [$binaries];
        }
        $binaryColumns    = $this->columnize(array_keys(reset($binaries)));
        $binaryParameters = $this->parameterize(reset($binaries));

        // create EMPTY_BLOB sql for each binary
        $binarySql = [];
        foreach ((array) $binaryColumns as $binary) {
            $binarySql[] = "$binary = EMPTY_BLOB()";
        }

        // prepare binary SQLs
        if (count($binarySql)) {
            $binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
        }

        // If the query has any "join" clauses, we will setup the joins on the builder
        // and compile them so we can attach them to this update, as update queries
        // can get join statements to attach to other tables when they're needed.
        if (isset($query->joins)) {
            $joins = ' ' . $this->compileJoins($query, $query->joins);
        } else {
            $joins = '';
        }

        // Of course, update queries may also be constrained by where clauses so we'll
        // need to compile the where clauses and attach it to the query so only the
        // intended records are updated by the SQL statements we generate to run.
        $where = $this->compileWheres($query);

        return "update {$table}{$joins} set $columns$binarySql $where returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
    }

    /**
     * Compile the lock into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  bool|string $value
     * @return string
     * @throws Oci8Exception
     */
    protected function compileLock(Builder $query, $value)
    {
        if (is_string($value)) {
            return $value;
        }

        if ($value) {
            return 'for update';
        }

        throw new Oci8Exception('Lock in share mode not yet supported!');
    }

    /**
     * Compile the "limit" portions of the query.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  int $limit
     * @return string
     */
    protected function compileLimit(Builder $query, $limit)
    {
        return '';
    }

    /**
     * Compile the "offset" portions of the query.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  int $offset
     * @return string
     */
    protected function compileOffset(Builder $query, $offset)
    {
        return '';
    }

    /**
     * Compile a "where date" clause.
     *
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  array $where
     * @return string
     */
    protected function whereDate(Builder $query, $where)
    {
        $value = $this->parameter($where['value']);

        return "trunc({$this->wrap($where['column'])}) {$where['operator']} $value";
    }

    /**
     * Compile a date based where clause.
     *
     * @param  string $type
     * @param  \Illuminate\Database\Query\Builder $query
     * @param  array $where
     * @return string
     */
    protected function dateBasedWhere($type, Builder $query, $where)
    {
        $value = $this->parameter($where['value']);

        return "extract ($type from {$this->wrap($where['column'])}) {$where['operator']} $value";
    }

    /**
     * Wrap a single string in keyword identifiers.
     *
     * @param  string $value
     * @return string
     */
    protected function wrapValue($value)

    {
        if ($this->isReserved($value)) {
            return parent::wrapValue($value);
        }

        return $value !== '*' ? sprintf($this->wrapper, $value) : $value;
    }
}


1		<?php
2
3		namespace Yajra\Oci8\Query\Grammars;
4
5		use Illuminate\Database\Query\Builder;
6		use Illuminate\Database\Query\Grammars\Grammar;
7		use Yajra\Oci8\OracleReservedWords;
8		use Yajra\Pdo\Oci8\Exceptions\Oci8Exception;
9
10		class OracleGrammar extends Grammar
11		{
12		use OracleReservedWords;
13
14		/**
15		* The keyword identifier wrapper format.
16		*
17		* @var string
18		*/
19		protected $wrapper = '%s';
20
21		/**
22		* Compile an exists statement into SQL.
23		*
24		* @param \Illuminate\Database\Query\Builder $query
25		* @return string
26		*/
27		public function compileExists(Builder $query)
28		{
29		$q = clone $query;
30		$q->columns = [];
31		$q->selectRaw('1 as "exists"')
32		->whereRaw("rownum = 1");
33
34		return $this->compileSelect($q);
35		}
36
37		/**
38		* Compile a select query into SQL.
39		*
40		* @param \Illuminate\Database\Query\Builder
41		* @return string
42		*/
43		public function compileSelect(Builder $query)
44		{
45		if (is_null($query->columns)) {
46		$query->columns = ['*'];
47		}
48
49		$components = $this->compileComponents($query);
50
51		// If an offset is present on the query, we will need to wrap the query in
52		// a big "ANSI" offset syntax block. This is very nasty compared to the
53		// other database systems but is necessary for implementing features.
54		if ($this->isPaginationable($query, $components)) {
55		return $this->compileAnsiOffset($query, $components);
56		}
57
58		return trim($this->concatenate($components));
59		}
60
61		/**
62		* @param Builder $query
63		* @param array $components
64		* @return bool
65		*/
66		protected function isPaginationable(Builder $query, array $components)
67		{
68		return ($query->limit > 0 \|\| $query->offset > 0) && ! array_key_exists('lock', $components);
69		}
70
71		/**
72		* Create a full ANSI offset clause for the query.
73		*
74		* @param \Illuminate\Database\Query\Builder $query
75		* @param array $components
76		* @return string
77		*/
78		protected function compileAnsiOffset(Builder $query, $components)
79		{
80		$constraint = $this->compileRowConstraint($query);
81
82		$sql = $this->concatenate($components);
83
84		// We are now ready to build the final SQL query so we'll create a common table
85		// expression from the query and get the records with row numbers within our
86		// given limit and offset value that we just put on as a query constraint.
87		$temp = $this->compileTableExpression($sql, $constraint, $query);
88
89		return $temp;
90		}
91
92		/**
93		* Compile the limit / offset row constraint for a query.
94		*
95		* @param \Illuminate\Database\Query\Builder $query
96		* @return string
97		*/
98		protected function compileRowConstraint($query)
99		{
100		$start = $query->offset + 1;
101
102		if ($query->limit == 1) {
103		return "= 1";
104		}
105
106		if ($query->limit > 1) {
107		$finish = $query->offset + $query->limit;
108
109		return "between {$start} and {$finish}";
110		}
111
112		return ">= {$start}";
113		}
114
115		/**
116		* Compile a common table expression for a query.
117		*
118		* @param string $sql
119		* @param string $constraint
120		* @param Builder $query
121		* @return string
122		*/
123		protected function compileTableExpression($sql, $constraint, $query)
124		{
125		if ($query->limit > 1) {
126		return "select t2.* from ( select rownum AS \"rn\", t1.* from ({$sql}) t1 ) t2 where t2.\"rn\" {$constraint}";
127		} else {
128		return "select * from ({$sql}) where rownum {$constraint}";
129		}
130		}
131
132		/**
133		* Compile a truncate table statement into SQL.
134		*
135		* @param \Illuminate\Database\Query\Builder $query
136		* @return array
137		*/
138		public function compileTruncate(Builder $query)
139		{
140		return ['truncate table ' . $this->wrapTable($query->from) => []];
141		}
142
143		/**
144		* Compile an insert and get ID statement into SQL.
145		*
146		* @param \Illuminate\Database\Query\Builder $query
147		* @param array $values
148		* @param string $sequence
149		* @return string
150		*/
151		public function compileInsertGetId(Builder $query, $values, $sequence = 'id')
152		{
153		if (empty($sequence)) {
154		$sequence = 'id';
155		}
156
157		return $this->compileInsert($query, $values) . ' returning ' . $this->wrap($sequence) . ' into ?';
158		}
159
160		/**
161		* Compile an insert statement into SQL.
162		*
163		* @param \Illuminate\Database\Query\Builder $query
164		* @param array $values
165		* @return string
166		*/
167		public function compileInsert(Builder $query, array $values)
168		{
169		// Essentially we will force every insert to be treated as a batch insert which
170		// simply makes creating the SQL easier for us since we can utilize the same
171		// basic routine regardless of an amount of records given to us to insert.
172		$table = $this->wrapTable($query->from);
173
174		if (! is_array(reset($values))) {
175		$values = [$values];
176		}
177
178		$columns = $this->columnize(array_keys(reset($values)));
179
180		// We need to build a list of parameter place-holders of values that are bound
181		// to the query. Each insert should have the exact same amount of parameter
182		// bindings so we can just go off the first list of values in this array.
183		$parameters = $this->parameterize(reset($values));
184
185		$value = array_fill(0, count($values), "($parameters)");
186
187		if (count($value) > 1) {
188		$insertQueries = [];
189		foreach ($value as $parameter) {
190		$parameter = (str_replace(['(', ')'], '', $parameter));
191		$insertQueries[] = "select " . $parameter . " from dual ";
192		}
193		$parameters = implode('union all ', $insertQueries);
194
195
196		return "insert into $table ($columns) $parameters";
197		}
198		$parameters = implode(', ', $value);
199
200		return "insert into $table ($columns) values $parameters";
201		}
202
203		/**
204		* Compile an insert with blob field statement into SQL.
205		*
206		* @param \Illuminate\Database\Query\Builder $query
207		* @param array $values
208		* @param array $binaries
209		* @param string $sequence
210		* @return string
211		*/
212		public function compileInsertLob(Builder $query, $values, $binaries, $sequence = 'id')
213		{
214		if (empty($sequence)) {
215		$sequence = 'id';
216		}
217
218		$table = $this->wrapTable($query->from);
219
220		if (! is_array(reset($values))) {
221		$values = [$values];
222		}
223
224		if (! is_array(reset($binaries))) {
225		$binaries = [$binaries];
226		}
227
228		$columns = $this->columnize(array_keys(reset($values)));
229		$binaryColumns = $this->columnize(array_keys(reset($binaries)));
230		$columns .= (empty($columns) ? '' : ', ') . $binaryColumns;
231
232		$parameters = $this->parameterize(reset($values));
233		$binaryParameters = $this->parameterize(reset($binaries));
234
235		$value = array_fill(0, count($values), "$parameters");
236		$binaryValue = array_fill(0, count($binaries), str_replace('?', 'EMPTY_BLOB()', $binaryParameters));
237
238		$value = array_merge($value, $binaryValue);
239		$parameters = implode(', ', array_filter($value));
240
241		return "insert into $table ($columns) values ($parameters) returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
242		}
243
244		/**
245		* Compile an update statement into SQL.
246		*
247		* @param \Illuminate\Database\Query\Builder $query
248		* @param array $values
249		* @param array $binaries
250		* @param string $sequence
251		* @return string
252		*/
253		public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
254		{
255		$table = $this->wrapTable($query->from);
256
257		// Each one of the columns in the update statements needs to be wrapped in the
258		// keyword identifiers, also a place-holder needs to be created for each of
259		// the values in the list of bindings so we can make the sets statements.
260		$columns = [];
261
262		foreach ($values as $key => $value) {
263		$columns[] = $this->wrap($key) . ' = ' . $this->parameter($value);
264		}
265
266		$columns = implode(', ', $columns);
267
268		// set blob variables
269		if (! is_array(reset($binaries))) {
270		$binaries = [$binaries];
271		}
272		$binaryColumns = $this->columnize(array_keys(reset($binaries)));
273		$binaryParameters = $this->parameterize(reset($binaries));
274
275		// create EMPTY_BLOB sql for each binary
276		$binarySql = [];
277		foreach ((array) $binaryColumns as $binary) {
278		$binarySql[] = "$binary = EMPTY_BLOB()";
279		}
280
281		// prepare binary SQLs
282		if (count($binarySql)) {
283		$binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
284		}
285
286		// If the query has any "join" clauses, we will setup the joins on the builder
287		// and compile them so we can attach them to this update, as update queries
288		// can get join statements to attach to other tables when they're needed.
289		if (isset($query->joins)) {
290		$joins = ' ' . $this->compileJoins($query, $query->joins);
291		} else {
292		$joins = '';
293		}
294
295		// Of course, update queries may also be constrained by where clauses so we'll
296		// need to compile the where clauses and attach it to the query so only the
297		// intended records are updated by the SQL statements we generate to run.
298		$where = $this->compileWheres($query);
299
300		return "update {$table}{$joins} set $columns$binarySql $where returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
301		}
302
303		/**
304		* Compile the lock into SQL.
305		*
306		* @param \Illuminate\Database\Query\Builder $query
307		* @param bool\|string $value
308		* @return string
309		* @throws Oci8Exception
310		*/
311		protected function compileLock(Builder $query, $value)
312		{
313		if (is_string($value)) {
314		return $value;
315		}
316
317		if ($value) {
318		return 'for update';
319		}
320
321		throw new Oci8Exception('Lock in share mode not yet supported!');
322		}
323
324		/**
325		* Compile the "limit" portions of the query.
326		*
327		* @param \Illuminate\Database\Query\Builder $query
328		* @param int $limit
329		* @return string
330		*/
331		protected function compileLimit(Builder $query, $limit)
332		{
333		return '';
334		}
335
336		/**
337		* Compile the "offset" portions of the query.
338		*
339		* @param \Illuminate\Database\Query\Builder $query
340		* @param int $offset
341		* @return string
342		*/
343		protected function compileOffset(Builder $query, $offset)
344		{
345		return '';
346		}
347
348		/**
349		* Compile a "where date" clause.
350		*
351		* @param \Illuminate\Database\Query\Builder $query
352		* @param array $where
353		* @return string
354		*/
355		protected function whereDate(Builder $query, $where)
356		{
357		$value = $this->parameter($where['value']);
358
359		return "trunc({$this->wrap($where['column'])}) {$where['operator']} $value";
360		}
361
362		/**
363		* Compile a date based where clause.
364		*
365		* @param string $type
366		* @param \Illuminate\Database\Query\Builder $query
367		* @param array $where
368		* @return string
369		*/
370		protected function dateBasedWhere($type, Builder $query, $where)
371		{
372		$value = $this->parameter($where['value']);
373
374		return "extract ($type from {$this->wrap($where['column'])}) {$where['operator']} $value";
375		}
376
377		/**
378		* Wrap a single string in keyword identifiers.
379		*
380		* @param string $value
381		* @return string
382		*/
383	View Code Duplication	protected function wrapValue($value)
		0 ignored issues – show Duplication introduced 2015-12-22 04:09 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
384		{
385		if ($this->isReserved($value)) {
386		return parent::wrapValue($value);
387		}
388
389		return $value !== '*' ? sprintf($this->wrapper, $value) : $value;
390		}
391		}
392

yajra / laravel-oci8

Push — 4.0 ( 05ba58...a84d33 )

OracleGrammar::compileInsert() B

Complexity

Size

Duplication

Duplication Side-by-Side

Filter issues like