TOracleCommandBuilder::applyLimitOffset() - Code Metrics - Inspection of "Removed internal usage of prado3-namespaced classe..." - pradosoft/prado - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Branch — master (099915)

by Fabio

created 2019-11-23 15:14 UTC

TOracleCommandBuilder::applyLimitOffset() C

↳ Parent: TOracleCommandBuilder

Complexity

Conditions	14
Paths	193

Size

Total Lines	82
Code Lines	53

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	210

Importance

Changes

Metric	Value
cc	14
eloc	53
c	0
b	0
f	0
nc	193
nop	3
dl	0
loc	82
ccs	0
cts	65
cp	0
crap	210
rs	5.4916

How to fix Long Method Complexity

<?php

/**
 * TOracleCommandBuilder class file.
 *
 * @author Marcos Nobre <marconobre[at]gmail[dot]com>
 * @link https://github.com/pradosoft/prado
 * @license https://github.com/pradosoft/prado/blob/master/LICENSE
 * @package Prado\Data\Common\Oracle
 */

namespace Prado\Data\Common\Oracle;

use Prado\Data\Common\TDbCommandBuilder;
use Prado\Prado;

/**
 * TOracleCommandBuilder provides specifics methods to create limit/offset query commands
 * for Oracle database.
 *
 * @author Marcos Nobre <marconobre[at]gmail[dot]com>
 * @package Prado\Data\Common\Oracle
 * @since 3.1
 */
class TOracleCommandBuilder extends TDbCommandBuilder
{

	/**
	 * Overrides parent implementation. Only column of type text or character (and its variants)
	 * accepts the LIKE criteria.
	 * @param array $fields list of column id for potential search condition.
	 * @param string $keywords string of keywords
	 * @return string SQL search condition matching on a set of columns.
	 */
	public function getSearchExpression($fields, $keywords)
	{
		$columns = [];
		foreach ($fields as $field) {
			if ($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) {
				$columns[] = $field;
			}
		}
		return parent :: getSearchExpression($columns, $keywords);
	}
	/**
	 *
	 * @param mixed $column
	 * @return bool true if column can be used for LIKE searching.
	 */
	protected function isSearchableColumn($column)
	{
		$type = strtolower($column->getDbType());
		return $type === 'character varying' || $type === 'varchar2' || $type === 'character' || $type === 'char' || $type === 'text';
	}

	/**
	 * Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive).
	 * @param string $column column name.
	 * @param array $words keywords
	 * @param mixed $sql
	 * @param mixed $limit
	 * @param mixed $offset
	 * @return string search condition for all words in one column.
	 */
	/*
	*
	*	how Oracle don't implements ILIKE, this method won't be overrided
	*
	protected function getSearchCondition($column, $words)
	{
		$conditions=array();
		foreach($words as $word)
			$conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%');
		return '('.implode(' AND ', $conditions).')';
	}
	*/

	/**
	 * Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause.
	 * @param string $sql SQL query string.
	 * @param int $limit maximum number of rows, -1 to ignore limit.
	 * @param int $offset row offset, -1 to ignore offset.
	 * @return string SQL with limit and offset in Oracle way.
	 */
	public function applyLimitOffset($sql, $limit = -1, $offset = -1)
	{
		if ((int) $limit <= 0 && (int) $offset <= 0) {
			return $sql;
		}

		$pradoNUMLIN = 'pradoNUMLIN';
		$fieldsALIAS = 'xyz';

		$nfimDaSQL = strlen($sql);
		$nfimDoWhere = (strpos($sql, 'ORDER') !== false ? strpos($sql, 'ORDER') : $nfimDaSQL);
		$niniDoSelect = strpos($sql, 'SELECT') + 6;
		$nfimDoSelect = (strpos($sql, 'FROM') !== false ? strpos($sql, 'FROM') : $nfimDaSQL);

		$WhereInSubSelect = "";
		if (strpos($sql, 'WHERE') !== false) {
			$WhereInSubSelect = "WHERE " . substr($sql, strpos($sql, 'WHERE') + 5, $nfimDoWhere - $niniDoWhere);

		}

		$sORDERBY = '';
		if (stripos($sql, 'ORDER') !== false) {
			$p = stripos($sql, 'ORDER');
			$sORDERBY = substr($sql, $p + 8);
		}

		$fields = substr($sql, 0, $nfimDoSelect);
		$fields = trim(substr($fields, $niniDoSelect));
		$aliasedFields = ', ';

		if (trim($fields) == '*') {
			$aliasedFields = ", {$fieldsALIAS}.{$fields}";
			$fields = '';
			$arr = $this->getTableInfo()->getColumns();
			foreach ($arr as $field) {
				$fields .= strtolower($field->getColumnName()) . ', ';
			}
			$fields = str_replace('"', '', $fields);
			$fields = trim($fields);
			$fields = substr($fields, 0, strlen($fields) - 1);
		} else {
			if (strpos($fields, ',') !== false) {
				$arr = $this->getTableInfo()->getColumns();
				foreach ($arr as $field) {
					$field = strtolower($field);
					$existAS = str_ireplace(' as ', '-as-', $field);
					if (strpos($existAS, '-as-') === false) {
						$aliasedFields .= "{$fieldsALIAS}." . trim($field) . ", ";
					} else {
						$aliasedFields .= "{$field}, ";
					}
				}
				$aliasedFields = trim($aliasedFields);
				$aliasedFields = substr($aliasedFields, 0, strlen($aliasedFields) - 1);
			}
		}
		if ($aliasedFields == ', ') {
			$aliasedFields = " , $fieldsALIAS.* ";
		}

		/* ************************
		$newSql = " SELECT $fields FROM ".
				  "(					".
				  "		SELECT rownum as {$pradoNUMLIN} {$aliasedFields} FROM ".
				  " ($sql) {$fieldsALIAS} WHERE rownum <= {$limit} ".
				  ") WHERE {$pradoNUMLIN} >= {$offset} ";

		************************* */
		$offset = (int) $offset;
		$toReg = $offset + $limit;
		$fullTableName = $this->getTableInfo()->getTableFullName();
		if (empty($sORDERBY)) {
			$sORDERBY = "ROWNUM";
		}

		$newSql = " SELECT $fields FROM " .
					"(					" .
					"		SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) -1 as {$pradoNUMLIN} {$aliasedFields} " .
					"		FROM {$fullTableName} {$fieldsALIAS} $WhereInSubSelect" .
					") nn					" .
					" WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} < {$toReg} ";
		//echo $newSql."\n<br>\n";
		return $newSql;
	}
}


1			<?php
2
3			/**
4			* TOracleCommandBuilder class file.
5			*
6			* @author Marcos Nobre <marconobre[at]gmail[dot]com>
7			* @link https://github.com/pradosoft/prado
8			* @license https://github.com/pradosoft/prado/blob/master/LICENSE
9			* @package Prado\Data\Common\Oracle
10			*/
11
12			namespace Prado\Data\Common\Oracle;
13
14			use Prado\Data\Common\TDbCommandBuilder;
15			use Prado\Prado;
16
17			/**
18			* TOracleCommandBuilder provides specifics methods to create limit/offset query commands
19			* for Oracle database.
20			*
21			* @author Marcos Nobre <marconobre[at]gmail[dot]com>
22			* @package Prado\Data\Common\Oracle
23			* @since 3.1
24			*/
25			class TOracleCommandBuilder extends TDbCommandBuilder
26			{
27
28			/**
29			* Overrides parent implementation. Only column of type text or character (and its variants)
30			* accepts the LIKE criteria.
31			* @param array $fields list of column id for potential search condition.
32			* @param string $keywords string of keywords
33			* @return string SQL search condition matching on a set of columns.
34			*/
35			public function getSearchExpression($fields, $keywords)
36			{
37			$columns = [];
38			foreach ($fields as $field) {
39			if ($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) {
40			$columns[] = $field;
41			}
42			}
43			return parent :: getSearchExpression($columns, $keywords);
44			}
45			/**
46			*
47			* @param mixed $column
48			* @return bool true if column can be used for LIKE searching.
49			*/
50			protected function isSearchableColumn($column)
51			{
52			$type = strtolower($column->getDbType());
53			return $type === 'character varying' \|\| $type === 'varchar2' \|\| $type === 'character' \|\| $type === 'char' \|\| $type === 'text';
54			}
55
56			/**
57			* Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive).
58			* @param string $column column name.
59			* @param array $words keywords
60			* @param mixed $sql
61			* @param mixed $limit
62			* @param mixed $offset
63			* @return string search condition for all words in one column.
64			*/
65			/*
66			*
67			* how Oracle don't implements ILIKE, this method won't be overrided
68			*
69			protected function getSearchCondition($column, $words)
70			{
71			$conditions=array();
72			foreach($words as $word)
73			$conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%');
74			return '('.implode(' AND ', $conditions).')';
75			}
76			*/
77
78			/**
79			* Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause.
80			* @param string $sql SQL query string.
81			* @param int $limit maximum number of rows, -1 to ignore limit.
82			* @param int $offset row offset, -1 to ignore offset.
83			* @return string SQL with limit and offset in Oracle way.
84			*/
85			public function applyLimitOffset($sql, $limit = -1, $offset = -1)
86			{
87			if ((int) $limit <= 0 && (int) $offset <= 0) {
88			return $sql;
89			}
90
91			$pradoNUMLIN = 'pradoNUMLIN';
92			$fieldsALIAS = 'xyz';
93
94			$nfimDaSQL = strlen($sql);
95			$nfimDoWhere = (strpos($sql, 'ORDER') !== false ? strpos($sql, 'ORDER') : $nfimDaSQL);
96			$niniDoSelect = strpos($sql, 'SELECT') + 6;
97			$nfimDoSelect = (strpos($sql, 'FROM') !== false ? strpos($sql, 'FROM') : $nfimDaSQL);
98
99			$WhereInSubSelect = "";
100			if (strpos($sql, 'WHERE') !== false) {
101			$WhereInSubSelect = "WHERE " . substr($sql, strpos($sql, 'WHERE') + 5, $nfimDoWhere - $niniDoWhere);
			0 ignored issues – show Comprehensibility Best Practice introduced 2018-02-15 21:32 UTC by Report Bug Copy Issue Report The variable `$niniDoWhere` seems to be never defined. Loading history...
102			}
103
104			$sORDERBY = '';
105			if (stripos($sql, 'ORDER') !== false) {
106			$p = stripos($sql, 'ORDER');
107			$sORDERBY = substr($sql, $p + 8);
108			}
109
110			$fields = substr($sql, 0, $nfimDoSelect);
111			$fields = trim(substr($fields, $niniDoSelect));
112			$aliasedFields = ', ';
113
114			if (trim($fields) == '*') {
115			$aliasedFields = ", {$fieldsALIAS}.{$fields}";
116			$fields = '';
117			$arr = $this->getTableInfo()->getColumns();
118			foreach ($arr as $field) {
119			$fields .= strtolower($field->getColumnName()) . ', ';
120			}
121			$fields = str_replace('"', '', $fields);
122			$fields = trim($fields);
123			$fields = substr($fields, 0, strlen($fields) - 1);
124			} else {
125			if (strpos($fields, ',') !== false) {
126			$arr = $this->getTableInfo()->getColumns();
127			foreach ($arr as $field) {
128			$field = strtolower($field);
129			$existAS = str_ireplace(' as ', '-as-', $field);
130			if (strpos($existAS, '-as-') === false) {
131			$aliasedFields .= "{$fieldsALIAS}." . trim($field) . ", ";
132			} else {
133			$aliasedFields .= "{$field}, ";
134			}
135			}
136			$aliasedFields = trim($aliasedFields);
137			$aliasedFields = substr($aliasedFields, 0, strlen($aliasedFields) - 1);
138			}
139			}
140			if ($aliasedFields == ', ') {
141			$aliasedFields = " , $fieldsALIAS.* ";
142			}
143
144			/* ************************
145			$newSql = " SELECT $fields FROM ".
146			"( ".
147			" SELECT rownum as {$pradoNUMLIN} {$aliasedFields} FROM ".
148			" ($sql) {$fieldsALIAS} WHERE rownum <= {$limit} ".
149			") WHERE {$pradoNUMLIN} >= {$offset} ";
150
151			************************* */
152			$offset = (int) $offset;
153			$toReg = $offset + $limit;
154			$fullTableName = $this->getTableInfo()->getTableFullName();
155			if (empty($sORDERBY)) {
156			$sORDERBY = "ROWNUM";
157			}
158
159			$newSql = " SELECT $fields FROM " .
160			"( " .
161			" SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) -1 as {$pradoNUMLIN} {$aliasedFields} " .
162			" FROM {$fullTableName} {$fieldsALIAS} $WhereInSubSelect" .
163			") nn " .
164			" WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} < {$toReg} ";
165			//echo $newSql."\n<br>\n";
166			return $newSql;
167			}
168			}
169

pradosoft / prado

Branch — master (099915)

TOracleCommandBuilder::applyLimitOffset() C

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like