OracleGrammar::compileInsertGetId() - Code Metrics - Inspection of "Add union order by test." - yajra/laravel-oci8 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 4.0 ( 645286...fb08eb )

by Arjay

created 2015-12-03 14:58 UTC

OracleGrammar::compileInsertGetId() A

↳ Parent: OracleGrammar

Complexity

Conditions	2
Paths	2

Size

Total Lines	8
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Metric	Value
dl	0
loc	8
rs	9.4286
cc	2
eloc	4
nc	2
nop	3

<?php

namespace Yajra\Oci8\Query\Grammars;

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

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

yajra / laravel-oci8

Push — 4.0 ( 645286...fb08eb )

OracleGrammar::compileInsertGetId() A

Complexity

Size

Duplication

Duplication Side-by-Side

Filter issues like