OracleGrammar::compileInsertLob() - Code Metrics - Inspection of "Improve exists query as suggested. #107" - yajra/laravel-oci8 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — vendor ( 7b877a...9387bf )

by Arjay

created 2015-12-03 07:49 UTC

OracleGrammar::compileInsertLob() B

↳ Parent: OracleGrammar

Complexity

Conditions	5
Paths	16

Size

Total Lines	31
Code Lines	18

Duplication

Lines	0
Ratio	0 %

Metric	Value
dl	0
loc	31
rs	8.439
cc	5
eloc	18
nc	16
nop	4

<?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)
    {
        $query->selectRaw('1 as "exists"')
              ->whereRaw("rownum = 1");

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

    /**
     * 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 '';
    }

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

yajra / laravel-oci8

Push — vendor ( 7b877a...9387bf )

OracleGrammar::compileInsertLob() B

Complexity

Size

Duplication

Duplication Side-by-Side

Filter issues like