OracleGrammar::wrapValue() - Code Metrics - Inspection of "Implement reserved words qouting on schema grammar..." - yajra/laravel-oci8 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 4.0 ( eb4219...df36c7 )

by Arjay

created 2015-12-04 02:56 UTC

OracleGrammar::wrapValue() A

↳ Parent: Project

Complexity

Conditions	4
Paths	4

Size

Total Lines	12
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	1

Metric	Value
c	2
b	0
f	1
dl	0
loc	12
rs	9.2
cc	4
eloc	6
nc	4
nop	1

<?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";
    }
}


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

yajra / laravel-oci8

Push — 4.0 ( eb4219...df36c7 )

OracleGrammar::wrapValue() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like