1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
<?php
/**
* TOracleCommandBuilder class file.
*
* @author Marcos Nobre <marconobre[at]gmail[dot]com>
* @link http://www.pradosoft.com/
* @copyright Copyright © 2005-2014 PradoSoft
* @license http://www.pradosoft.com/license/
* @package Prado\Data\Common\Oracle
*/
namespace Prado\Data\Common\Oracle;
use Prado\Data\Common\TDbCommandBuilder;
use Prado\Prado;
Prado::using('System.Data.Common.TDbCommandBuilder');
/**
* 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 list of column id for potential search condition.
* @param string string of keywords
* @return string SQL search condition matching on a set of columns.
*/
public function getSearchExpression($fields, $keywords) {
$columns = array ();
foreach ($fields as $field) {
if ($this->isSearchableColumn($this->getTableInfo()->getColumn($field)))
$columns[] = $field;
}
return parent :: getSearchExpression($columns, $keywords);
}
/**
*
* @return boolean 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 name.
* @param array keywords
* @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 query string.
* @param integer maximum number of rows, -1 to ignore limit.
* @param integer 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;
}
}
|
