Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
0.00% |
0 / 968 |
|
0.00% |
0 / 30 |
CRAP | |
0.00% |
0 / 1 |
CargoSQLQuery | |
0.00% |
0 / 968 |
|
0.00% |
0 / 30 |
117306 | |
0.00% |
0 / 1 |
__construct | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
newFromValues | |
0.00% |
0 / 35 |
|
0.00% |
0 / 1 |
20 | |||
validateValues | |
0.00% |
0 / 45 |
|
0.00% |
0 / 1 |
210 | |||
getAliasForFieldString | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
setAliasedFieldNames | |
0.00% |
0 / 27 |
|
0.00% |
0 / 1 |
90 | |||
setAliasedTableNames | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
20 | |||
setCargoJoinConds | |
0.00% |
0 / 69 |
|
0.00% |
0 / 1 |
552 | |||
setMWJoinConds | |
0.00% |
0 / 32 |
|
0.00% |
0 / 1 |
110 | |||
setOrderBy | |
0.00% |
0 / 22 |
|
0.00% |
0 / 1 |
182 | |||
setGroupBy | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
30 | |||
getAndValidateSQLFunctions | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
30 | |||
getDescriptionAndTableNameForField | |
0.00% |
0 / 91 |
|
0.00% |
0 / 1 |
1892 | |||
setDescriptionsAndTableNamesForFields | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
6 | |||
addToCargoJoinConds | |
0.00% |
0 / 18 |
|
0.00% |
0 / 1 |
132 | |||
addFieldTableToTableNames | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
12 | |||
fieldTableIsIncluded | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
20 | |||
substVirtualFieldName | |
0.00% |
0 / 27 |
|
0.00% |
0 / 1 |
72 | |||
handleVirtualFields | |
0.00% |
0 / 195 |
|
0.00% |
0 / 1 |
2352 | |||
handleVirtualCoordinateFields | |
0.00% |
0 / 74 |
|
0.00% |
0 / 1 |
552 | |||
handleHierarchyFields | |
0.00% |
0 / 75 |
|
0.00% |
0 / 1 |
380 | |||
distanceToDegrees | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
42 | |||
handleDateFields | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
182 | |||
handleSearchTextFields | |
0.00% |
0 / 45 |
|
0.00% |
0 / 1 |
110 | |||
addTablePrefixesToAll | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
12 | |||
run | |
0.00% |
0 / 39 |
|
0.00% |
0 / 1 |
342 | |||
addTablePrefixes | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
12 | |||
addQuotes | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
20 | |||
determineDateFields | |
0.00% |
0 / 39 |
|
0.00% |
0 / 1 |
756 | |||
getMainStartAndEndDateFields | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
12 | |||
isAggregating | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
30 |
1 | <?php |
2 | /** |
3 | * CargoSQLQuery - a wrapper class around SQL queries, that also handles |
4 | * the special Cargo keywords like "HOLDS" and "NEAR". |
5 | * |
6 | * @author Yaron Koren |
7 | * @ingroup Cargo |
8 | */ |
9 | |
10 | class CargoSQLQuery { |
11 | |
12 | private $mCargoDB; |
13 | public $mTablesStr; |
14 | public $mAliasedTableNames; |
15 | public $mFieldsStr; |
16 | public $mOrigWhereStr; |
17 | public $mWhereStr; |
18 | public $mJoinOnStr; |
19 | public $mCargoJoinConds; |
20 | public $mJoinConds; |
21 | public $mAliasedFieldNames; |
22 | public $mOrigAliasedFieldNames; |
23 | public $mFieldStringAliases; |
24 | public $mTableSchemas; |
25 | public $mFieldDescriptions; |
26 | public $mFieldTables; |
27 | public $mOrigGroupByStr; |
28 | public $mGroupByStr; |
29 | public $mOrigHavingStr; |
30 | public $mHavingStr; |
31 | public $mOrigOrderBy; |
32 | public $mOrderBy; |
33 | public $mQueryLimit; |
34 | public $mOffset; |
35 | public $mSearchTerms = []; |
36 | public $mDateFieldPairs = []; |
37 | |
38 | public function __construct() { |
39 | $this->mCargoDB = CargoUtils::getDB(); |
40 | } |
41 | |
42 | /** |
43 | * This is newFromValues() instead of __construct() so that an |
44 | * object can be created without any values. |
45 | */ |
46 | public static function newFromValues( $tablesStr, $fieldsStr, $whereStr, $joinOnStr, $groupByStr, |
47 | $havingStr, $orderByStr, $limitStr, $offsetStr, $allowFieldEscaping = false ) { |
48 | global $wgCargoDefaultQueryLimit, $wgCargoMaxQueryLimit; |
49 | |
50 | // "table(s)" is the only mandatory value. |
51 | if ( $tablesStr == '' ) { |
52 | throw new MWException( "At least one table must be specified." ); |
53 | } |
54 | |
55 | // Needed to avoid various warnings. |
56 | if ( $whereStr === null ) { |
57 | $whereStr = ''; |
58 | } |
59 | |
60 | self::validateValues( $tablesStr, $fieldsStr, $whereStr, $joinOnStr, $groupByStr, |
61 | $havingStr, $orderByStr, $limitStr, $offsetStr, $allowFieldEscaping ); |
62 | |
63 | $sqlQuery = new CargoSQLQuery(); |
64 | $sqlQuery->mCargoDB = CargoUtils::getDB(); |
65 | $sqlQuery->mTablesStr = $tablesStr; |
66 | $sqlQuery->setAliasedTableNames(); |
67 | $sqlQuery->mFieldsStr = $fieldsStr; |
68 | // This _decode() call is necessary because the "where=" |
69 | // clause can (and often does) include a call to {{PAGENAME}}, |
70 | // which HTML-encodes certain characters, notably single quotes. |
71 | $sqlQuery->mOrigWhereStr = htmlspecialchars_decode( $whereStr, ENT_QUOTES ); |
72 | $sqlQuery->mWhereStr = $sqlQuery->mOrigWhereStr; |
73 | $sqlQuery->mJoinOnStr = $joinOnStr; |
74 | $sqlQuery->setCargoJoinConds( $joinOnStr ); |
75 | $sqlQuery->setAliasedFieldNames(); |
76 | $sqlQuery->mTableSchemas = CargoUtils::getTableSchemas( $sqlQuery->mAliasedTableNames ); |
77 | $sqlQuery->mOrigOrderBy = $orderByStr; |
78 | $sqlQuery->setOrderBy( $orderByStr ); |
79 | $sqlQuery->setGroupBy( $groupByStr ); |
80 | $sqlQuery->mOrigHavingStr = $havingStr; |
81 | $sqlQuery->mHavingStr = $sqlQuery->mOrigHavingStr; |
82 | $sqlQuery->setDescriptionsAndTableNamesForFields(); |
83 | $sqlQuery->handleHierarchyFields(); |
84 | $sqlQuery->handleVirtualFields(); |
85 | $sqlQuery->handleVirtualCoordinateFields(); |
86 | $sqlQuery->handleDateFields(); |
87 | $sqlQuery->handleSearchTextFields(); |
88 | $sqlQuery->setMWJoinConds(); |
89 | $sqlQuery->mQueryLimit = $wgCargoDefaultQueryLimit; |
90 | if ( $limitStr != '' ) { |
91 | $sqlQuery->mQueryLimit = min( $limitStr, $wgCargoMaxQueryLimit ); |
92 | } |
93 | $sqlQuery->mOffset = $offsetStr; |
94 | $sqlQuery->addTablePrefixesToAll(); |
95 | |
96 | return $sqlQuery; |
97 | } |
98 | |
99 | /** |
100 | * Throw an error if there are forbidden values in any of the |
101 | * #cargo_query parameters - some or all of them are potential |
102 | * security risks. |
103 | * |
104 | * It could be that, given the way #cargo_query is structured, only |
105 | * some of the parameters need to be checked for these strings, |
106 | * but we might as well validate all of them. |
107 | * |
108 | * The function CargoUtils::getTableSchemas() also does specific |
109 | * validation of the "tables" parameter, while this class's |
110 | * setDescriptionsAndTableNameForFields() does validation of the |
111 | * "fields=" parameter. |
112 | */ |
113 | public static function validateValues( $tablesStr, $fieldsStr, $whereStr, $joinOnStr, $groupByStr, |
114 | $havingStr, $orderByStr, $limitStr, $offsetStr, $allowFieldEscaping ) { |
115 | // Remove quoted strings from "where" parameter, to avoid |
116 | // unnecessary false positives from words like "from" |
117 | // being included in string comparisons. |
118 | // However, before we do that, check for certain strings that |
119 | // shouldn't be in quote marks either. |
120 | $whereStrRegexps = [ |
121 | '/\-\-/' => '--', |
122 | '/#/' => '#', |
123 | ]; |
124 | |
125 | // HTML-decode the string - this is necessary if the query |
126 | // contains a call to {{PAGENAME}} and the page name has any |
127 | // special characters, because {{PAGENAME]] unfortunately |
128 | // HTML-encodes the value, which leads to a '#' in the string. |
129 | $decodedWhereStr = html_entity_decode( $whereStr, ENT_QUOTES ); |
130 | foreach ( $whereStrRegexps as $regexp => $displayString ) { |
131 | if ( preg_match( $regexp, $decodedWhereStr ) ) { |
132 | throw new MWException( "Error in \"where\" parameter: the string \"$displayString\" cannot be used within #cargo_query." ); |
133 | } |
134 | } |
135 | $noQuotesFieldsStr = CargoUtils::removeQuotedStrings( $fieldsStr ); |
136 | $noQuotesWhereStr = CargoUtils::removeQuotedStrings( $decodedWhereStr ); |
137 | $noQuotesJoinOnStr = CargoUtils::removeQuotedStrings( $joinOnStr ); |
138 | $noQuotesGroupByStr = CargoUtils::removeQuotedStrings( $groupByStr ); |
139 | $noQuotesHavingStr = CargoUtils::removeQuotedStrings( $havingStr ); |
140 | $noQuotesOrderByStr = CargoUtils::removeQuotedStrings( $orderByStr ); |
141 | |
142 | $regexps = [ |
143 | '/\bselect\b/i' => 'SELECT', |
144 | '/\binto\b/i' => 'INTO', |
145 | '/\bfrom\b/i' => 'FROM', |
146 | '/\bunion\b/i' => 'UNION', |
147 | '/;/' => ';', |
148 | '/@/' => '@', |
149 | '/\<\?/' => '<?', |
150 | '/\-\-/' => '--', |
151 | '/\/\*/' => '/*', |
152 | '/#/' => '#', |
153 | ]; |
154 | // Bypass this particular check, for Special:Drilldown and possibly |
155 | // other query locations. |
156 | if ( !$allowFieldEscaping ) { |
157 | // Temporarily removed. |
158 | // $regexps['/`/'] = '`'; |
159 | } |
160 | foreach ( $regexps as $regexp => $displayString ) { |
161 | if ( preg_match( $regexp, $tablesStr ) || |
162 | preg_match( $regexp, $noQuotesFieldsStr ) || |
163 | preg_match( $regexp, $noQuotesWhereStr ) || |
164 | preg_match( $regexp, $noQuotesJoinOnStr ) || |
165 | preg_match( $regexp, $noQuotesGroupByStr ) || |
166 | preg_match( $regexp, $noQuotesHavingStr ) || |
167 | preg_match( $regexp, $noQuotesOrderByStr ) || |
168 | preg_match( $regexp, (string)$limitStr ) || |
169 | preg_match( $regexp, (string)$offsetStr ) ) { |
170 | throw new MWException( "Error: the string \"$displayString\" cannot be used within #cargo_query." ); |
171 | } |
172 | } |
173 | |
174 | self::getAndValidateSQLFunctions( $noQuotesWhereStr ); |
175 | self::getAndValidateSQLFunctions( $noQuotesJoinOnStr ); |
176 | self::getAndValidateSQLFunctions( $noQuotesGroupByStr ); |
177 | self::getAndValidateSQLFunctions( $noQuotesHavingStr ); |
178 | self::getAndValidateSQLFunctions( $noQuotesOrderByStr ); |
179 | self::getAndValidateSQLFunctions( $limitStr ); |
180 | self::getAndValidateSQLFunctions( $offsetStr ); |
181 | } |
182 | |
183 | /** |
184 | * Gets a mapping of original field name strings to their field name aliases |
185 | * as they appear in the query result |
186 | */ |
187 | public function getAliasForFieldString( $fieldString ) { |
188 | return $this->mFieldStringAliases[$fieldString]; |
189 | } |
190 | |
191 | /** |
192 | * Gets an array of field names and their aliases from the passed-in |
193 | * SQL fragment. |
194 | */ |
195 | private function setAliasedFieldNames() { |
196 | $this->mAliasedFieldNames = []; |
197 | $fieldStrings = CargoUtils::smartSplit( ',', $this->mFieldsStr ); |
198 | // Default is "_pageName". |
199 | if ( count( $fieldStrings ) == 0 ) { |
200 | $fieldStrings[] = '_pageName'; |
201 | } |
202 | |
203 | // Quick error-checking: for now, just disallow "DISTINCT", |
204 | // and require "GROUP BY" instead. |
205 | foreach ( $fieldStrings as $fieldString ) { |
206 | if ( strtolower( substr( $fieldString, 0, 9 ) ) == 'distinct ' ) { |
207 | throw new MWException( "Error: The DISTINCT keyword is not allowed by Cargo; " |
208 | . "please use \"group by=\" instead." ); |
209 | } |
210 | } |
211 | |
212 | // Because aliases are used as keys, we can't have more than |
213 | // one blank alias - so replace blank aliases with the name |
214 | // "Blank value X" - it will get replaced back before being |
215 | // displayed. |
216 | $blankAliasCount = 0; |
217 | foreach ( $fieldStrings as $fieldString ) { |
218 | $fieldStringParts = CargoUtils::smartSplit( '=', $fieldString, true ); |
219 | if ( count( $fieldStringParts ) == 2 ) { |
220 | $fieldName = trim( $fieldStringParts[0] ); |
221 | $alias = trim( $fieldStringParts[1] ); |
222 | } else { |
223 | $fieldName = $fieldString; |
224 | // Might as well change underscores to spaces |
225 | // by default - but for regular field names, |
226 | // not the special ones. |
227 | // "Real" field = with the table name removed. |
228 | if ( strpos( $fieldName, '.' ) !== false ) { |
229 | [ $tableName, $realFieldName ] = explode( '.', $fieldName, 2 ); |
230 | } else { |
231 | $realFieldName = $fieldName; |
232 | } |
233 | if ( $realFieldName[0] != '_' ) { |
234 | $alias = str_replace( '_', ' ', $realFieldName ); |
235 | } else { |
236 | $alias = $realFieldName; |
237 | } |
238 | } |
239 | if ( !$alias ) { |
240 | $blankAliasCount++; |
241 | $alias = "Blank value $blankAliasCount"; |
242 | } |
243 | $this->mAliasedFieldNames[$alias] = $fieldName; |
244 | $this->mFieldStringAliases[$fieldString] = $alias; |
245 | } |
246 | $this->mOrigAliasedFieldNames = $this->mAliasedFieldNames; |
247 | } |
248 | |
249 | private function setAliasedTableNames() { |
250 | $this->mAliasedTableNames = []; |
251 | $tableStrings = CargoUtils::smartSplit( ',', $this->mTablesStr ); |
252 | |
253 | foreach ( $tableStrings as $tableString ) { |
254 | $tableStringParts = CargoUtils::smartSplit( '=', $tableString ); |
255 | if ( count( $tableStringParts ) == 2 ) { |
256 | $tableName = trim( $tableStringParts[0] ); |
257 | $alias = trim( $tableStringParts[1] ); |
258 | } else { |
259 | $tableName = $tableString; |
260 | $alias = $tableString; |
261 | } |
262 | if ( !$alias ) { |
263 | throw new MWException( "Error: blank table aliases cannot be set." ); |
264 | } |
265 | $this->mAliasedTableNames[$alias] = $tableName; |
266 | } |
267 | } |
268 | |
269 | /** |
270 | * This does double duty: it both creates a "join conds" array |
271 | * from the string, and validates the set of join conditions |
272 | * based on the set of table names - making sure each table is |
273 | * joined. |
274 | * |
275 | * The "join conds" array created is not of the format that |
276 | * MediaWiki's database query() method requires - it is more |
277 | * structured and does not contain the necessary table prefixes yet. |
278 | */ |
279 | private function setCargoJoinConds( $joinOnStr ) { |
280 | // This string is needed for "deferred" queries. |
281 | $this->mJoinOnStr = $joinOnStr; |
282 | |
283 | $this->mCargoJoinConds = []; |
284 | |
285 | if ( $joinOnStr === null || trim( $joinOnStr ) === '' ) { |
286 | if ( count( $this->mAliasedTableNames ) > 1 ) { |
287 | throw new MWException( "Error: join conditions must be set for tables." ); |
288 | } |
289 | return; |
290 | } |
291 | |
292 | $joinStrings = explode( ',', $joinOnStr ); |
293 | // 'HOLDS' must be all-caps for now. |
294 | $allowedJoinOperators = [ '=', ' HOLDS ', '<=', '>=', '<', '>' ]; |
295 | $joinOperator = null; |
296 | |
297 | foreach ( $joinStrings as $joinString ) { |
298 | $foundValidOperator = false; |
299 | foreach ( $allowedJoinOperators as $allowedOperator ) { |
300 | if ( strpos( $joinString, $allowedOperator ) === false ) { |
301 | continue; |
302 | } |
303 | $foundValidOperator = true; |
304 | $joinOperator = $allowedOperator; |
305 | break; |
306 | } |
307 | |
308 | if ( !$foundValidOperator ) { |
309 | throw new MWException( "No valid operator found in join condition ($joinString)." ); |
310 | } |
311 | |
312 | $joinParts = explode( $joinOperator, $joinString ); |
313 | $joinPart1 = trim( $joinParts[0] ); |
314 | $tableAndField1 = explode( '.', $joinPart1 ); |
315 | if ( count( $tableAndField1 ) != 2 ) { |
316 | throw new MWException( "Table and field name must both be specified in '$joinPart1'." ); |
317 | } |
318 | [ $table1, $field1 ] = $tableAndField1; |
319 | $joinPart2 = trim( $joinParts[1] ); |
320 | $tableAndField2 = explode( '.', $joinPart2 ); |
321 | if ( count( $tableAndField2 ) != 2 ) { |
322 | throw new MWException( "Table and field name must both be specified in '$joinPart2'." ); |
323 | } |
324 | [ $table2, $field2 ] = $tableAndField2; |
325 | |
326 | $joinCond = [ |
327 | 'joinType' => 'LEFT OUTER JOIN', |
328 | 'table1' => $table1, |
329 | 'field1' => $field1, |
330 | 'table2' => $table2, |
331 | 'field2' => $field2, |
332 | 'joinOperator' => $joinOperator |
333 | ]; |
334 | $this->mCargoJoinConds[] = $joinCond; |
335 | } |
336 | |
337 | // Now validate, to make sure that all the tables |
338 | // are "joined" together. There's probably some more |
339 | // efficient network algorithm for this sort of thing, but |
340 | // oh well. |
341 | $numUnmatchedTables = count( $this->mAliasedTableNames ); |
342 | $firstJoinCond = current( $this->mCargoJoinConds ); |
343 | $firstTableInJoins = $firstJoinCond['table1']; |
344 | $matchedTables = [ $firstTableInJoins ]; |
345 | // We will check against aliases, not table names. |
346 | $allPossibleTableAliases = []; |
347 | foreach ( $this->mAliasedTableNames as $tableAlias => $tableName ) { |
348 | $allPossibleTableAliases[] = $tableAlias; |
349 | // This is useful for at least PostgreSQL. |
350 | $allPossibleTableAliases[] = $this->mCargoDB->addIdentifierQuotes( $tableAlias ); |
351 | } |
352 | do { |
353 | $previousNumUnmatchedTables = $numUnmatchedTables; |
354 | foreach ( $this->mCargoJoinConds as $joinCond ) { |
355 | $table1 = $joinCond['table1']; |
356 | $table2 = $joinCond['table2']; |
357 | if ( !in_array( $table1, $allPossibleTableAliases ) ) { |
358 | throw new MWException( "Error: table \"$table1\" is not in list of table names or aliases." ); |
359 | } |
360 | if ( !in_array( $table2, $allPossibleTableAliases ) ) { |
361 | throw new MWException( "Error: table \"$table2\" is not in list of table names or aliases." ); |
362 | } |
363 | |
364 | if ( in_array( $table1, $matchedTables ) && !in_array( $table2, $matchedTables ) ) { |
365 | $matchedTables[] = $table2; |
366 | $numUnmatchedTables--; |
367 | } |
368 | if ( in_array( $table2, $matchedTables ) && !in_array( $table1, $matchedTables ) ) { |
369 | $matchedTables[] = $table1; |
370 | $numUnmatchedTables--; |
371 | } |
372 | } |
373 | } while ( $numUnmatchedTables > 0 && $numUnmatchedTables > $previousNumUnmatchedTables ); |
374 | |
375 | if ( $numUnmatchedTables > 0 ) { |
376 | foreach ( array_keys( $this->mAliasedTableNames ) as $tableAlias ) { |
377 | $escapedTableAlias = $this->mCargoDB->addIdentifierQuotes( $tableAlias ); |
378 | if ( !in_array( $tableAlias, $matchedTables ) && |
379 | !in_array( $escapedTableAlias, $matchedTables ) ) { |
380 | throw new MWException( "Error: Table \"$tableAlias\" is not included within the " |
381 | . "join conditions." ); |
382 | } |
383 | } |
384 | } |
385 | } |
386 | |
387 | /** |
388 | * Turn the very structured format that Cargo uses for join |
389 | * conditions into the one that MediaWiki uses - this includes |
390 | * adding the database prefix to each table name. |
391 | */ |
392 | private function setMWJoinConds() { |
393 | if ( $this->mCargoJoinConds == null ) { |
394 | return; |
395 | } |
396 | |
397 | $this->mJoinConds = []; |
398 | foreach ( $this->mCargoJoinConds as $cargoJoinCond ) { |
399 | // Only add the DB prefix to the table names if |
400 | // they're true table names and not aliases. |
401 | $table1 = $cargoJoinCond['table1']; |
402 | if ( !array_key_exists( $table1, $this->mAliasedTableNames ) || $this->mAliasedTableNames[$table1] == $table1 ) { |
403 | $cargoTable1 = $this->mCargoDB->tableName( $table1 ); |
404 | } else { |
405 | $cargoTable1 = $this->mCargoDB->addIdentifierQuotes( $table1 ); |
406 | } |
407 | $table2 = $cargoJoinCond['table2']; |
408 | if ( !array_key_exists( $table2, $this->mAliasedTableNames ) || $this->mAliasedTableNames[$table2] == $table2 ) { |
409 | $cargoTable2 = $this->mCargoDB->tableName( $table2 ); |
410 | } else { |
411 | $cargoTable2 = $this->mCargoDB->addIdentifierQuotes( $table2 ); |
412 | } |
413 | if ( array_key_exists( 'joinOperator', $cargoJoinCond ) ) { |
414 | $joinOperator = $cargoJoinCond['joinOperator']; |
415 | } else { |
416 | $joinOperator = '='; |
417 | } |
418 | |
419 | $field1 = $this->mCargoDB->addIdentifierQuotes( $cargoJoinCond['field1'] ); |
420 | $field2 = $this->mCargoDB->addIdentifierQuotes( $cargoJoinCond['field2'] ); |
421 | $joinCondConds = [ |
422 | $cargoTable1 . '.' . $field1 . $joinOperator . |
423 | $cargoTable2 . '.' . $field2 |
424 | ]; |
425 | if ( array_key_exists( 'extraCond', $cargoJoinCond ) ) { |
426 | $joinCondConds[] = $cargoJoinCond['extraCond']; |
427 | } |
428 | if ( !array_key_exists( $table2, $this->mJoinConds ) ) { |
429 | $this->mJoinConds[$table2] = [ |
430 | $cargoJoinCond['joinType'], |
431 | $joinCondConds |
432 | ]; |
433 | } else { |
434 | $this->mJoinConds[$table2][1] = array_merge( |
435 | $this->mJoinConds[$table2][1], |
436 | $joinCondConds |
437 | ); |
438 | } |
439 | } |
440 | } |
441 | |
442 | public function setOrderBy( $orderByStr = null ) { |
443 | $this->mOrderBy = []; |
444 | if ( $orderByStr != '' ) { |
445 | $orderByElements = CargoUtils::smartSplit( ',', $orderByStr ); |
446 | foreach ( $orderByElements as $elem ) { |
447 | // Get rid of "ASC" - it's never needed. |
448 | if ( strtolower( substr( $elem, -4 ) ) == ' asc' ) { |
449 | $elem = trim( substr( $elem, 0, strlen( $elem ) - 4 ) ); |
450 | } |
451 | // If it has "DESC" at the end, remove it, then |
452 | // add it back in later. |
453 | $hasDesc = ( strtolower( substr( $elem, -5 ) ) == ' desc' ); |
454 | if ( $hasDesc ) { |
455 | $elem = trim( substr( $elem, 0, strlen( $elem ) - 5 ) ); |
456 | } |
457 | if ( strpos( $elem, '(' ) === false && strpos( $elem, '.' ) === false && !$this->mCargoDB->isQuotedIdentifier( $elem ) ) { |
458 | $elem = $this->mCargoDB->addIdentifierQuotes( $elem ); |
459 | } |
460 | if ( $hasDesc ) { |
461 | $elem .= ' DESC'; |
462 | } |
463 | $this->mOrderBy[] = $elem; |
464 | } |
465 | } else { |
466 | // By default, sort on up to the first five fields, in |
467 | // the order in which they're defined. Five seems like |
468 | // enough to make sure everything is in the right order, |
469 | // no? Or should it always be all the fields? |
470 | $fieldNum = 1; |
471 | foreach ( $this->mAliasedFieldNames as $fieldName ) { |
472 | if ( strpos( $fieldName, '(' ) === false && strpos( $fieldName, '.' ) === false ) { |
473 | $this->mOrderBy[] = $this->mCargoDB->addIdentifierQuotes( $fieldName ); |
474 | } else { |
475 | $this->mOrderBy[] = $fieldName; |
476 | } |
477 | $fieldNum++; |
478 | if ( $fieldNum > 5 ) { |
479 | break; |
480 | } |
481 | } |
482 | } |
483 | } |
484 | |
485 | public function setGroupBy( $groupByStr ) { |
486 | // @TODO - $mGroupByStr should turn into an array named |
487 | // $mGroupBy for better handling of mulitple values, as was |
488 | // done with $mOrderBy. |
489 | $this->mOrigGroupByStr = $groupByStr; |
490 | if ( $groupByStr == '' ) { |
491 | $this->mGroupByStr = ''; |
492 | } elseif ( strpos( $groupByStr, '(' ) === false && strpos( $groupByStr, '.' ) === false && strpos( $groupByStr, ',' ) === false ) { |
493 | $this->mGroupByStr = $this->mCargoDB->addIdentifierQuotes( $groupByStr ); |
494 | } else { |
495 | $this->mGroupByStr = $groupByStr; |
496 | } |
497 | } |
498 | |
499 | private static function getAndValidateSQLFunctions( $str ) { |
500 | global $wgCargoAllowedSQLFunctions; |
501 | |
502 | if ( $str === null ) { |
503 | return []; |
504 | } |
505 | |
506 | $sqlFunctionMatches = []; |
507 | $sqlFunctionRegex = '/(\b|\W|`)(\w*?)(\s*|`)\(/'; |
508 | preg_match_all( $sqlFunctionRegex, $str, $sqlFunctionMatches ); |
509 | $sqlFunctions = array_map( 'strtoupper', $sqlFunctionMatches[2] ); |
510 | $sqlFunctions = array_map( 'trim', $sqlFunctions ); |
511 | // Throw an error if any of these functions |
512 | // are not in our "whitelist" of SQL functions. |
513 | // Also add to this whitelist SQL operators like AND, OR, NOT, |
514 | // etc., because the parsing can mistake these for functions. |
515 | $logicalOperators = [ 'AND', 'OR', 'NOT', 'IN' ]; |
516 | $allowedFunctions = array_merge( $wgCargoAllowedSQLFunctions, $logicalOperators ); |
517 | foreach ( $sqlFunctions as $sqlFunction ) { |
518 | // @TODO - fix the original regexp to avoid blank |
519 | // strings, so that this check is not necessary. |
520 | if ( $sqlFunction == '' ) { |
521 | continue; |
522 | } |
523 | if ( !in_array( $sqlFunction, $allowedFunctions ) ) { |
524 | throw new MWException( wfMessage( "cargo-query-badsqlfunction", "$sqlFunction()" )->parse() ); |
525 | } |
526 | } |
527 | |
528 | return $sqlFunctions; |
529 | } |
530 | |
531 | /** |
532 | * Attempts to get the "field description" (type, etc.), as well as the |
533 | * table name, of a single field specified in a SELECT call (via a |
534 | * #cargo_query call), using the set of schemas for all data tables. |
535 | * |
536 | * Also does some validation of table names, field names, and any SQL |
537 | * functions contained in this clause. |
538 | */ |
539 | private function getDescriptionAndTableNameForField( $origFieldName ) { |
540 | $tableName = null; |
541 | $fieldName = null; |
542 | $description = new CargoFieldDescription(); |
543 | |
544 | // We use "\p{L}0-9" instead of \w here in order to |
545 | // handle accented and other non-ASCII characters in |
546 | // table and field names. |
547 | $fieldPattern = '/^([-_\p{L}0-9$]+)([.]([-_\p{L}0-9$]+))?$/u'; |
548 | $fieldPatternFound = preg_match( $fieldPattern, $origFieldName, $fieldPatternMatches ); |
549 | $stringPatternFound = false; |
550 | $hasFunctionCall = false; |
551 | |
552 | if ( $fieldPatternFound ) { |
553 | switch ( count( $fieldPatternMatches ) ) { |
554 | case 2: |
555 | $fieldName = $fieldPatternMatches[1]; |
556 | break; |
557 | case 4: |
558 | $tableName = $fieldPatternMatches[1]; |
559 | $fieldName = $fieldPatternMatches[3]; |
560 | break; |
561 | } |
562 | } else { |
563 | $stringPattern = '/^(([\'"]).*?\2)(.+)?$/'; |
564 | $stringPatternFound = preg_match( $stringPattern, $origFieldName, $stringPatternMatches ); |
565 | if ( $stringPatternFound ) { |
566 | // If the count is 3 we have a single quoted string |
567 | // If the count is 4 we have stuff after it |
568 | $stringPatternFound = count( $stringPatternMatches ) == 3; |
569 | } |
570 | |
571 | if ( !$stringPatternFound ) { |
572 | $noQuotesOrigFieldName = CargoUtils::removeQuotedStrings( $origFieldName ); |
573 | |
574 | $functionCallPattern = '/\p{L}\s*\(/'; |
575 | $hasFunctionCall = preg_match( $functionCallPattern, $noQuotesOrigFieldName ); |
576 | } |
577 | } |
578 | // If it's a pre-defined field, we probably know its type. |
579 | if ( $fieldName == '_ID' || $fieldName == '_rowID' || $fieldName == '_pageID' || $fieldName == '_pageNamespace' || $fieldName == '_position' ) { |
580 | $description->mType = 'Integer'; |
581 | } elseif ( $fieldName == '_pageTitle' ) { |
582 | // It's a string - do nothing. |
583 | } elseif ( $fieldName == '_pageName' ) { |
584 | $description->mType = 'Page'; |
585 | } elseif ( $stringPatternFound ) { |
586 | // It's a quoted, literal string - do nothing. |
587 | } elseif ( $hasFunctionCall ) { |
588 | $sqlFunctions = self::getAndValidateSQLFunctions( $noQuotesOrigFieldName ); |
589 | $firstFunction = $sqlFunctions[0]; |
590 | // 'ROUND' is in neither the Integer nor Float |
591 | // lists because it sometimes returns an |
592 | // integer, sometimes a float - for formatting |
593 | // purposes, we'll just treat it as a string. |
594 | if ( in_array( $firstFunction, [ 'COUNT', 'FLOOR', 'CEIL' ] ) ) { |
595 | $description->mType = 'Integer'; |
596 | } elseif ( in_array( $firstFunction, [ 'SUM', 'POWER', 'LN', 'LOG' ] ) ) { |
597 | $description->mType = 'Float'; |
598 | } elseif ( in_array( $firstFunction, |
599 | [ 'DATE', 'DATE_ADD', 'DATE_SUB', 'DATE_DIFF' ] ) ) { |
600 | $description->mType = 'Date'; |
601 | } elseif ( in_array( $firstFunction, [ 'TRIM' ] ) ) { |
602 | // @HACK - allow users one string function |
603 | // (TRIM()) that will return a String type, and |
604 | // thus won't have its value parsed as wikitext. |
605 | // Hopefully this won't cause problems for those |
606 | // just wanting to call TRIM(). (In that case, |
607 | // they can wrap the call in CONCAT().) |
608 | $description->mType = 'String'; |
609 | } elseif ( in_array( $firstFunction, [ 'MAX', 'MIN', 'AVG' ] ) ) { |
610 | // These are special functions in that the type |
611 | // of their output is not fixed, but rather |
612 | // matches the type of their input. So we find |
613 | // what's inside the function call and call |
614 | // *this* function recursively on that. |
615 | $startParenPos = strpos( $origFieldName, '(' ); |
616 | $lastParenPos = strrpos( $origFieldName, ')' ); |
617 | $innerFieldName = substr( $origFieldName, $startParenPos + 1, ( $lastParenPos - $startParenPos - 1 ) ); |
618 | [ $innerDesc, $innerTableName ] = $this->getDescriptionAndTableNameForField( $innerFieldName ); |
619 | if ( $firstFunction == 'AVG' && $innerDesc->mType == 'Integer' ) { |
620 | // In practice, handling of AVG() is here |
621 | // so that calling it on a Rating |
622 | // field will keep it as Rating. |
623 | $description->mType = 'Float'; |
624 | } else { |
625 | return [ $innerDesc, $innerTableName ]; |
626 | } |
627 | } |
628 | // If it's anything else ('CONCAT', 'SUBSTRING', |
629 | // etc. etc.), we don't have to do anything. |
630 | } else { |
631 | // It's a standard field - though if it's '_value', |
632 | // or ends in '__full', it's actually the type of its |
633 | // corresponding field. |
634 | $useListTable = ( $fieldName == '_value' ); |
635 | if ( $useListTable ) { |
636 | if ( $tableName != null ) { |
637 | if ( strpos( $tableName, '__' ) !== false ) { |
638 | [ $tableName, $fieldName ] = explode( '__', $tableName, 2 ); |
639 | } else { |
640 | // Support directly operating on list table fields |
641 | $fieldName = null; |
642 | } |
643 | } else { |
644 | // We'll assume that there's exactly one |
645 | // "field table" in the list of tables - |
646 | // otherwise a standalone call to |
647 | // "_value" will presumably crash the |
648 | // SQL call. |
649 | foreach ( $this->mAliasedTableNames as $curTable ) { |
650 | if ( strpos( $curTable, '__' ) !== false ) { |
651 | [ $tableName, $fieldName ] = explode( '__', $curTable ); |
652 | break; |
653 | } |
654 | } |
655 | } |
656 | } elseif ( strlen( $fieldName ) > 6 && |
657 | strpos( $fieldName, '__full', strlen( $fieldName ) - 6 ) !== false ) { |
658 | $fieldName = substr( $fieldName, 0, strlen( $fieldName ) - 6 ); |
659 | } |
660 | if ( $tableName != null && !$useListTable ) { |
661 | if ( !array_key_exists( $tableName, $this->mAliasedTableNames ) ) { |
662 | throw new MWException( wfMessage( "cargo-query-badalias", $tableName )->parse() ); |
663 | } |
664 | $actualTableName = $this->mAliasedTableNames[$tableName]; |
665 | if ( !array_key_exists( $actualTableName, $this->mTableSchemas ) ) { |
666 | throw new MWException( wfMessage( "cargo-query-unknowndbtable", $actualTableName )->parse() ); |
667 | } elseif ( !array_key_exists( $fieldName, $this->mTableSchemas[$actualTableName]->mFieldDescriptions ) ) { |
668 | throw new MWException( wfMessage( "cargo-query-unknownfieldfortable", $fieldName, $actualTableName )->parse() ); |
669 | } else { |
670 | $description = $this->mTableSchemas[$actualTableName]->mFieldDescriptions[$fieldName]; |
671 | } |
672 | } elseif ( substr( $fieldName, -5 ) == '__lat' || substr( $fieldName, -5 ) == '__lon' ) { |
673 | // Special handling for lat/lon helper fields. |
674 | $description->mType = 'Coordinates part'; |
675 | $tableName = ''; |
676 | } elseif ( substr( $fieldName, -11 ) == '__precision' ) { |
677 | // Special handling for lat/lon helper fields. |
678 | // @TODO - we need validation on |
679 | // __lat, __lon and __precision fields, |
680 | // to make sure that they exist. |
681 | $description->mType = 'Date precision'; |
682 | $tableName = ''; |
683 | } else { |
684 | // Go through all the fields, until we find the |
685 | // one matching this one. |
686 | foreach ( $this->mTableSchemas as $curTableName => $tableSchema ) { |
687 | if ( array_key_exists( $fieldName, $tableSchema->mFieldDescriptions ) ) { |
688 | $description = $tableSchema->mFieldDescriptions[$fieldName]; |
689 | foreach ( $this->mAliasedTableNames as $tableAlias => $tableName1 ) { |
690 | if ( $tableName1 == $curTableName ) { |
691 | $tableName = $tableAlias; |
692 | break; |
693 | } |
694 | } |
695 | break; |
696 | } |
697 | } |
698 | |
699 | // If we couldn't find a table name, throw an error. |
700 | if ( $tableName == '' ) { |
701 | // There's a good chance that |
702 | // $fieldName is blank too. |
703 | if ( $fieldName == '' ) { |
704 | $fieldName = $origFieldName; |
705 | } |
706 | throw new MWException( wfMessage( "cargo-query-unknownfield", $fieldName )->parse() ); |
707 | } |
708 | } |
709 | } |
710 | |
711 | return [ $description, $tableName ]; |
712 | } |
713 | |
714 | /** |
715 | * Attempts to get the "field description" (type, etc.), as well as |
716 | * the table name, of each field specified in a SELECT call (via a |
717 | * #cargo_query call), using the set of schemas for all data tables. |
718 | */ |
719 | public function setDescriptionsAndTableNamesForFields() { |
720 | $this->mFieldDescriptions = []; |
721 | $this->mFieldTables = []; |
722 | foreach ( $this->mAliasedFieldNames as $alias => $origFieldName ) { |
723 | [ $description, $tableName ] = $this->getDescriptionAndTableNameForField( $origFieldName ); |
724 | |
725 | // Fix alias. |
726 | $alias = trim( $alias ); |
727 | $this->mFieldDescriptions[$alias] = $description; |
728 | $this->mFieldTables[$alias] = $tableName; |
729 | } |
730 | } |
731 | |
732 | public function addToCargoJoinConds( $newCargoJoinConds ) { |
733 | foreach ( $newCargoJoinConds as $newCargoJoinCond ) { |
734 | // Go through to make sure it's not there already. |
735 | $foundMatch = false; |
736 | foreach ( $this->mCargoJoinConds as $cargoJoinCond ) { |
737 | if ( $cargoJoinCond['table1'] == $newCargoJoinCond['table1'] && |
738 | $cargoJoinCond['field1'] == $newCargoJoinCond['field1'] && |
739 | $cargoJoinCond['table2'] == $newCargoJoinCond['table2'] && |
740 | $cargoJoinCond['field2'] == $newCargoJoinCond['field2'] ) { |
741 | $foundMatch = true; |
742 | continue; |
743 | } |
744 | } |
745 | if ( !$foundMatch ) { |
746 | // If this join references another table, insert |
747 | // this one before the join for that one. |
748 | $inserted = false; |
749 | foreach ( $this->mCargoJoinConds as $i => $curJoinCond ) { |
750 | if ( $newCargoJoinCond['table2'] == $curJoinCond['table1'] ) { |
751 | array_splice( $this->mCargoJoinConds, $i, 0, [ $newCargoJoinCond ] ); |
752 | $inserted = true; |
753 | break; |
754 | } |
755 | } |
756 | if ( !$inserted ) { |
757 | array_unshift( $this->mCargoJoinConds, $newCargoJoinCond ); |
758 | } |
759 | } |
760 | } |
761 | } |
762 | |
763 | public function addFieldTableToTableNames( $fieldTableName, $fieldTableAlias, $tableAlias ) { |
764 | // Add it in in the correct place, if it should be added at all. |
765 | if ( array_key_exists( $fieldTableAlias, $this->mAliasedTableNames ) ) { |
766 | return; |
767 | } |
768 | if ( !array_key_exists( $tableAlias, $this->mAliasedTableNames ) ) { |
769 | // Show an error message here? |
770 | return; |
771 | } |
772 | |
773 | // array_splice() for an associative array - copied from |
774 | // http://stackoverflow.com/a/1783125 |
775 | $indexOfMainTable = array_search( $tableAlias, array_keys( $this->mAliasedTableNames ) ); |
776 | $offset = $indexOfMainTable + 1; |
777 | $this->mAliasedTableNames = array_slice( $this->mAliasedTableNames, 0, $offset, true ) + |
778 | [ $fieldTableAlias => $fieldTableName ] + |
779 | array_slice( $this->mAliasedTableNames, $offset, null, true ); |
780 | } |
781 | |
782 | /** |
783 | * Helper function for handleVirtualFields() - for the query's |
784 | * "fields" and "order by" values, the right replacement for "virtual |
785 | * fields" depends on whether the separate table for that field has |
786 | * been included in the query. |
787 | */ |
788 | public function fieldTableIsIncluded( $fieldTableAlias ) { |
789 | foreach ( $this->mCargoJoinConds as $cargoJoinCond ) { |
790 | if ( $cargoJoinCond['table1'] == $fieldTableAlias || |
791 | $cargoJoinCond['table2'] == $fieldTableAlias ) { |
792 | return true; |
793 | } |
794 | } |
795 | return false; |
796 | } |
797 | |
798 | /** |
799 | * Provides HOLDS functionality to WHERE clause by replacing $pattern |
800 | * in $subject with suitable subquery and setting $found to true if |
801 | * successful (leaves it untouched otehrwise). Includes modifying |
802 | * the regex beginning from a non-valid identifier character to word |
803 | * boundary. |
804 | */ |
805 | public function substVirtualFieldName( &$subject, $rootPattern, $tableAlias, $notOperation, $fieldTableName, $compareOperator, &$found ) { |
806 | $notOperator = $notOperation ? 'NOT' : ''; |
807 | $patternMatch = []; |
808 | // Match HOLDS syntax with values in single quotes |
809 | if ( preg_match_all( $rootPattern . '\s*(\'.*?[^\\\\\']\')/i', $subject, $matches ) ) { |
810 | $pattern = $rootPattern . '\s*(\'.*?[^\\\\\']\')/i'; |
811 | $patternMatch[$pattern] = $matches; |
812 | } |
813 | // Match HOLDS syntax with values in double quotes |
814 | if ( preg_match_all( $rootPattern . '\s*(\".*?[^\\\"]\")/i', $subject, $matches ) ) { |
815 | $pattern = $rootPattern . '\s*(\".*?[^\\\"]\")/i'; |
816 | $patternMatch[$pattern] = $matches; |
817 | } |
818 | // Match HOLDS syntax with fieldnames without quotes. |
819 | // Fieldnames are expected to be single words without spaces. |
820 | if ( preg_match_all( $rootPattern . '\s*([^\'"\s]+\s*)/i', $subject, $matches ) ) { |
821 | $pattern = $rootPattern . '\s*([^\'"\s]*\s*)/i'; |
822 | $patternMatch[$pattern] = $matches; |
823 | } |
824 | // If any match is found, replace it with a subquery. |
825 | if ( $patternMatch ) { |
826 | foreach ( $patternMatch as $pattern => $matches ) { |
827 | $pattern = str_replace( '([^\w$,]|^)', '\b', $pattern ); |
828 | $pattern = str_replace( '([^\w$.,]|^)', '\b', $pattern ); |
829 | foreach ( $matches[2] as $match ) { |
830 | // _ID need not be quoted here. |
831 | // This being attached with a table name is handled |
832 | // in the function addTablePrefixesToAll, like other fields. |
833 | $replacement = |
834 | $tableAlias . "._ID " . |
835 | $notOperator . |
836 | " IN (SELECT " . $this->mCargoDB->addIdentifierQuotes( "_rowID" ) . " FROM " . |
837 | $this->mCargoDB->tableName( $fieldTableName ) . |
838 | " WHERE " . $this->mCargoDB->addIdentifierQuotes( "_value" ) . |
839 | $compareOperator . |
840 | $match . |
841 | ") "; |
842 | $subject = preg_replace( $pattern, $replacement, $subject, $limit = 1 ); |
843 | } |
844 | } |
845 | $found = true; |
846 | } |
847 | } |
848 | |
849 | private function handleVirtualFields() { |
850 | // The array-field alias can be found in a number of different |
851 | // clauses. Handling depends on which clause it is: |
852 | // "where" - make sure that "HOLDS" or "HOLDS LIKE" is |
853 | // specified. If it is, "translate" it into required subquery. |
854 | // "join on" - make sure that "HOLDS" is specified, If it is, |
855 | // "translate" it, and add the values table to "tables". |
856 | // "group by" - always "translate" it into the single value. |
857 | // "having" - same as "group by". |
858 | // "fields" - "translate" it, where the translation (i.e. |
859 | // the true field) depends on whether or not the values |
860 | // table is included. |
861 | // "order by" - same as "fields". |
862 | |
863 | // First, create an array of the virtual fields in the current |
864 | // set of tables. |
865 | $virtualFields = []; |
866 | foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { |
867 | foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { |
868 | if ( !$fieldDescription->mIsList ) { |
869 | continue; |
870 | } |
871 | foreach ( $this->mAliasedTableNames as $tableAlias => $tableName2 ) { |
872 | if ( $tableName == $tableName2 ) { |
873 | $virtualFields[] = [ |
874 | 'fieldName' => $fieldName, |
875 | 'tableAlias' => $tableAlias, |
876 | 'tableName' => $tableName, |
877 | 'fieldType' => $fieldDescription->mType, |
878 | 'isHierarchy' => $fieldDescription->mIsHierarchy |
879 | ]; |
880 | } |
881 | } |
882 | } |
883 | } |
884 | |
885 | // "where" |
886 | $matches = []; |
887 | $numHoldsExpressions = 0; |
888 | foreach ( $virtualFields as $virtualField ) { |
889 | $fieldName = $virtualField['fieldName']; |
890 | $tableAlias = $virtualField['tableAlias']; |
891 | $tableName = $virtualField['tableName']; |
892 | $fieldType = $virtualField['fieldType']; |
893 | $isHierarchy = $virtualField['isHierarchy']; |
894 | |
895 | $fieldTableName = $tableName . '__' . $fieldName; |
896 | $fieldReplaced = false; |
897 | |
898 | $patternSimple = [ |
899 | CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName ), |
900 | CargoUtils::getSQLFieldPattern( $fieldName ) |
901 | ]; |
902 | $patternRoot = [ |
903 | CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . '\s+', |
904 | CargoUtils::getSQLFieldPattern( $fieldName, false ) . '\s+' |
905 | ]; |
906 | |
907 | for ( $i = 0; $i < 2; $i++ ) { |
908 | if ( preg_match( $patternSimple[$i], $this->mWhereStr ) ) { |
909 | |
910 | $this->substVirtualFieldName( |
911 | $this->mWhereStr, |
912 | $patternRoot[$i] . 'HOLDS\s+NOT\s+LIKE', |
913 | $tableAlias, |
914 | $notOperation = true, |
915 | $fieldTableName, |
916 | $compareOperation = "LIKE ", |
917 | $fieldReplaced |
918 | ); |
919 | |
920 | $this->substVirtualFieldName( |
921 | $this->mWhereStr, |
922 | $patternRoot[$i] . 'HOLDS\s+LIKE', |
923 | $tableAlias, |
924 | $notOperation = false, |
925 | $fieldTableName, |
926 | $compareOperation = "LIKE ", |
927 | $fieldReplaced |
928 | ); |
929 | |
930 | $this->substVirtualFieldName( |
931 | $this->mWhereStr, |
932 | $patternRoot[$i] . 'HOLDS\s+NOT', |
933 | $tableAlias, |
934 | $notOperation = true, |
935 | $fieldTableName, |
936 | $compareOperation = "= ", |
937 | $fieldReplaced |
938 | ); |
939 | |
940 | $this->substVirtualFieldName( |
941 | $this->mWhereStr, |
942 | $patternRoot[$i] . 'HOLDS', |
943 | $tableAlias, |
944 | $notOperation = false, |
945 | $fieldTableName, |
946 | $compareOperation = "= ", |
947 | $fieldReplaced |
948 | ); |
949 | |
950 | if ( preg_match( $patternSimple[$i], $this->mWhereStr ) ) { |
951 | if ( $isHierarchy ) { |
952 | throw new MWException( "Error: operator for the hierarchy field '" . |
953 | "$tableName.$fieldName' must be 'HOLDS', 'HOLDS NOT', '" . |
954 | "HOLDS WITHIN', 'HOLDS LIKE' or 'HOLDS NOT LIKE'." ); |
955 | } else { |
956 | throw new MWException( "Error: operator for the virtual field '" . |
957 | "$tableName.$fieldName' must be 'HOLDS', 'HOLDS NOT', '" . |
958 | "HOLDS LIKE' or 'HOLDS NOT LIKE'." ); |
959 | } |
960 | } |
961 | } |
962 | } |
963 | // Always use the "field table" if it's a date field, |
964 | // and it's being queried. |
965 | $isFieldInQuery = in_array( $fieldName, $this->mAliasedFieldNames ) || |
966 | in_array( "$tableAlias.$fieldName", $this->mAliasedFieldNames ); |
967 | if ( $isFieldInQuery && ( $fieldType == 'Date' || $fieldType == 'Datetime' ) ) { |
968 | $fieldReplaced = true; |
969 | } |
970 | } |
971 | // "join on" |
972 | $newCargoJoinConds = []; |
973 | foreach ( $this->mCargoJoinConds as $i => $joinCond ) { |
974 | // We only handle 'HOLDS' here - no joining on |
975 | // 'HOLDS LIKE'. |
976 | if ( !array_key_exists( 'joinOperator', $joinCond ) || $joinCond['joinOperator'] != ' HOLDS ' ) { |
977 | continue; |
978 | } |
979 | |
980 | foreach ( $virtualFields as $virtualField ) { |
981 | $fieldName = $virtualField['fieldName']; |
982 | $tableAlias = $virtualField['tableAlias']; |
983 | $tableName = $virtualField['tableName']; |
984 | if ( $fieldName != $joinCond['field1'] || $tableAlias != $joinCond['table1'] ) { |
985 | continue; |
986 | } |
987 | $fieldTableName = $tableName . '__' . $fieldName; |
988 | $fieldTableAlias = $tableAlias . '__' . $fieldName; |
989 | $this->addFieldTableToTableNames( $fieldTableName, $fieldTableAlias, $tableAlias ); |
990 | $newJoinCond = [ |
991 | 'joinType' => 'LEFT OUTER JOIN', |
992 | 'table1' => $tableAlias, |
993 | 'field1' => '_ID', |
994 | 'table2' => $fieldTableAlias, |
995 | 'field2' => '_rowID' |
996 | ]; |
997 | $newCargoJoinConds[] = $newJoinCond; |
998 | $newJoinCond2 = [ |
999 | 'joinType' => 'RIGHT OUTER JOIN', |
1000 | 'table1' => $fieldTableAlias, |
1001 | 'field1' => '_value', |
1002 | 'table2' => $this->mCargoJoinConds[$i]['table2'], |
1003 | 'field2' => $this->mCargoJoinConds[$i]['field2'] |
1004 | ]; |
1005 | $newCargoJoinConds[] = $newJoinCond2; |
1006 | // Is it safe to unset an array value while |
1007 | // cycling through the array? Hopefully. |
1008 | unset( $this->mCargoJoinConds[$i] ); |
1009 | } |
1010 | } |
1011 | $this->addToCargoJoinConds( $newCargoJoinConds ); |
1012 | |
1013 | // If there's more than one table, there must be one or more |
1014 | // joins - match the order in $this->mAliasedTableNames to the |
1015 | // order of the tables within the joins. |
1016 | if ( count( $this->mAliasedTableNames ) > 1 ) { |
1017 | $orderedTableAliases = []; |
1018 | foreach ( $this->mCargoJoinConds as $joinCond ) { |
1019 | $table1 = $joinCond['table1']; |
1020 | $table2 = $joinCond['table2']; |
1021 | if ( !in_array( $table1, $orderedTableAliases ) ) { |
1022 | $orderedTableAliases[] = $table1; |
1023 | } |
1024 | if ( !in_array( $table2, $orderedTableAliases ) ) { |
1025 | $orderedTableAliases[] = $table2; |
1026 | } |
1027 | } |
1028 | |
1029 | uksort( $this->mAliasedTableNames, static function ( $key1, $key2 ) use ( $orderedTableAliases ) { |
1030 | return ( array_search( $key1, $orderedTableAliases ) - array_search( $key2, $orderedTableAliases ) ); |
1031 | } ); |
1032 | } |
1033 | |
1034 | // "group by" and "having" |
1035 | // We handle these before "fields" and "order by" because, |
1036 | // unlike those two, a virtual field here can affect the |
1037 | // set of tables and fields being included - which will |
1038 | // affect the other two. |
1039 | $matches = []; |
1040 | foreach ( $virtualFields as $virtualField ) { |
1041 | $fieldName = $virtualField['fieldName']; |
1042 | $tableAlias = $virtualField['tableAlias']; |
1043 | $tableName = $virtualField['tableName']; |
1044 | $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableName, $fieldName ); |
1045 | $foundMatch1 = preg_match( $pattern1, $this->mGroupByStr, $matches ); |
1046 | $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName ); |
1047 | $foundMatch2 = false; |
1048 | |
1049 | if ( !$foundMatch1 ) { |
1050 | $foundMatch2 = preg_match( $pattern2, $this->mGroupByStr, $matches ); |
1051 | } |
1052 | if ( $foundMatch1 || $foundMatch2 ) { |
1053 | $fieldTableName = $tableName . '__' . $fieldName; |
1054 | $fieldTableAlias = $tableAlias . '__' . $fieldName; |
1055 | if ( !$this->fieldTableIsIncluded( $fieldTableAlias ) ) { |
1056 | $this->addFieldTableToTableNames( $fieldTableName, $fieldTableAlias, $tableAlias ); |
1057 | $this->mCargoJoinConds[] = [ |
1058 | 'joinType' => 'LEFT OUTER JOIN', |
1059 | 'table1' => $tableAlias, |
1060 | 'field1' => '_ID', |
1061 | 'table2' => $fieldTableAlias, |
1062 | 'field2' => '_rowID' |
1063 | ]; |
1064 | } |
1065 | $replacement = "$fieldTableAlias._value"; |
1066 | |
1067 | if ( $foundMatch1 ) { |
1068 | $this->mGroupByStr = preg_replace( $pattern1, $replacement, $this->mGroupByStr ?? '' ); |
1069 | $this->mHavingStr = preg_replace( $pattern1, $replacement, $this->mHavingStr ?? '' ); |
1070 | } elseif ( $foundMatch2 ) { |
1071 | $this->mGroupByStr = preg_replace( $pattern2, $replacement, $this->mGroupByStr ?? '' ); |
1072 | $this->mHavingStr = preg_replace( $pattern2, $replacement, $this->mHavingStr ?? '' ); |
1073 | } |
1074 | } |
1075 | } |
1076 | |
1077 | // "fields" |
1078 | foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { |
1079 | $fieldDescription = $this->mFieldDescriptions[$alias]; |
1080 | |
1081 | if ( strpos( $fieldName, '.' ) !== false ) { |
1082 | // This could probably be done better with |
1083 | // regexps. |
1084 | [ $tableAlias, $fieldName ] = explode( '.', $fieldName, 2 ); |
1085 | } else { |
1086 | $tableAlias = $this->mFieldTables[$alias]; |
1087 | } |
1088 | |
1089 | // We're only interested in virtual list fields. |
1090 | $isVirtualField = false; |
1091 | foreach ( $virtualFields as $virtualField ) { |
1092 | if ( $fieldName == $virtualField['fieldName'] && $tableAlias == $virtualField['tableAlias'] ) { |
1093 | $isVirtualField = true; |
1094 | break; |
1095 | } |
1096 | } |
1097 | if ( !$isVirtualField ) { |
1098 | continue; |
1099 | } |
1100 | |
1101 | // Since the field name is an alias, it should get |
1102 | // translated, to either the "full" equivalent or to |
1103 | // the "value" field in the field table - depending on |
1104 | // whether or not that field has been "joined" on. |
1105 | $fieldTableAlias = $tableAlias . '__' . $fieldName; |
1106 | if ( $this->fieldTableIsIncluded( $fieldTableAlias ) ) { |
1107 | $fieldName = $fieldTableAlias . '._value'; |
1108 | } else { |
1109 | $fieldName .= '__full'; |
1110 | } |
1111 | $this->mAliasedFieldNames[$alias] = $fieldName; |
1112 | } |
1113 | |
1114 | // "order by" |
1115 | $matches = []; |
1116 | foreach ( $virtualFields as $virtualField ) { |
1117 | $fieldName = $virtualField['fieldName']; |
1118 | $tableAlias = $virtualField['tableAlias']; |
1119 | $tableName = $virtualField['tableName']; |
1120 | $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName ); |
1121 | $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName ); |
1122 | $foundMatch1 = $foundMatch2 = false; |
1123 | foreach ( $this->mOrderBy as &$orderByElem ) { |
1124 | $foundMatch1 = preg_match( $pattern1, $orderByElem, $matches ); |
1125 | |
1126 | if ( !$foundMatch1 ) { |
1127 | $foundMatch2 = preg_match( $pattern2, $orderByElem, $matches ); |
1128 | } |
1129 | if ( !$foundMatch1 && !$foundMatch2 ) { |
1130 | continue; |
1131 | } |
1132 | $fieldTableAlias = $tableAlias . '__' . $fieldName; |
1133 | if ( $this->fieldTableIsIncluded( $fieldTableAlias ) ) { |
1134 | $replacement = "$fieldTableAlias._value"; |
1135 | } else { |
1136 | $replacement = $tableAlias . '.' . $fieldName . '__full '; |
1137 | } |
1138 | if ( isset( $matches[2] ) && ( $matches[2] == ',' ) ) { |
1139 | $replacement .= ','; |
1140 | } |
1141 | if ( $foundMatch1 ) { |
1142 | $orderByElem = preg_replace( $pattern1, $replacement, $orderByElem ); |
1143 | } else { // $foundMatch2 |
1144 | $orderByElem = preg_replace( $pattern2, $replacement, $orderByElem ); |
1145 | } |
1146 | } |
1147 | } |
1148 | } |
1149 | |
1150 | /** |
1151 | * Similar to handleVirtualFields(), but handles coordinates fields |
1152 | * instead of fields that hold lists. This handling is much simpler. |
1153 | */ |
1154 | private function handleVirtualCoordinateFields() { |
1155 | // Coordinate fields can be found in the "fields" and "where" |
1156 | // clauses. The following handling is done: |
1157 | // "fields" - "translate" it, where the translation (i.e. |
1158 | // the true field) depends on whether or not the values |
1159 | // table is included. |
1160 | // "where" - make sure that "NEAR" is specified. If it is, |
1161 | // translate the clause accordingly. |
1162 | |
1163 | // First, create an array of the coordinate fields in the |
1164 | // current set of tables. |
1165 | $coordinateFields = []; |
1166 | foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { |
1167 | foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { |
1168 | if ( $fieldDescription->mType == 'Coordinates' ) { |
1169 | foreach ( $this->mAliasedTableNames as $tableAlias => $tableName2 ) { |
1170 | if ( $tableName == $tableName2 ) { |
1171 | $coordinateFields[] = [ |
1172 | 'fieldName' => $fieldName, |
1173 | 'tableName' => $tableName, |
1174 | 'tableAlias' => $tableAlias, |
1175 | ]; |
1176 | break; |
1177 | } |
1178 | } |
1179 | } |
1180 | } |
1181 | } |
1182 | |
1183 | // "fields" |
1184 | foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { |
1185 | $fieldDescription = $this->mFieldDescriptions[$alias]; |
1186 | |
1187 | if ( strpos( $fieldName, '.' ) !== false ) { |
1188 | // This could probably be done better with |
1189 | // regexps. |
1190 | [ $tableAlias, $fieldName ] = explode( '.', $fieldName, 2 ); |
1191 | } else { |
1192 | $tableAlias = $this->mFieldTables[$alias]; |
1193 | } |
1194 | |
1195 | // We have to do this roundabout checking, instead |
1196 | // of just looking at the type of each field alias, |
1197 | // because we want to find only the *virtual* |
1198 | // coordinate fields. |
1199 | $isCoordinateField = false; |
1200 | foreach ( $coordinateFields as $coordinateField ) { |
1201 | if ( $fieldName == $coordinateField['fieldName'] && |
1202 | $tableAlias == $coordinateField['tableAlias'] ) { |
1203 | $isCoordinateField = true; |
1204 | break; |
1205 | } |
1206 | } |
1207 | if ( !$isCoordinateField ) { |
1208 | continue; |
1209 | } |
1210 | |
1211 | // Since the field name is an alias, it should get |
1212 | // translated to its "full" equivalent. |
1213 | $fullFieldName = $fieldName . '__full'; |
1214 | $this->mAliasedFieldNames[$alias] = $fullFieldName; |
1215 | |
1216 | // Add in the 'lat' and 'lon' fields as well - we'll |
1217 | // need them, if a map is being displayed. |
1218 | $this->mAliasedFieldNames[$fieldName . ' lat'] = $fieldName . '__lat'; |
1219 | $this->mAliasedFieldNames[$fieldName . ' lon'] = $fieldName . '__lon'; |
1220 | } |
1221 | |
1222 | // "where" |
1223 | // @TODO - add handling for "HOLDS POINT NEAR" |
1224 | $matches = []; |
1225 | foreach ( $coordinateFields as $coordinateField ) { |
1226 | $fieldName = $coordinateField['fieldName']; |
1227 | $tableAlias = $coordinateField['tableAlias']; |
1228 | $patternSuffix = '(\s+NEAR\s*)\(([^)]*)\)/i'; |
1229 | |
1230 | $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . $patternSuffix; |
1231 | $foundMatch1 = preg_match( $pattern1, $this->mWhereStr, $matches ); |
1232 | if ( !$foundMatch1 ) { |
1233 | $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName, false ) . $patternSuffix; |
1234 | $foundMatch2 = preg_match( $pattern2, $this->mWhereStr, $matches ); |
1235 | } |
1236 | if ( $foundMatch1 || $foundMatch2 ) { |
1237 | // If no "NEAR", throw an error. |
1238 | if ( count( $matches ) != 4 ) { |
1239 | throw new MWException( "Error: operator for the virtual coordinates field " |
1240 | . "'$tableAlias.$fieldName' must be 'NEAR'." ); |
1241 | } |
1242 | $coordinatesAndDistance = explode( ',', $matches[3] ); |
1243 | if ( count( $coordinatesAndDistance ) != 3 ) { |
1244 | throw new MWException( "Error: value for the 'NEAR' operator must be of the form " |
1245 | . "\"(latitude, longitude, distance)\"." ); |
1246 | } |
1247 | [ $latitude, $longitude, $distance ] = $coordinatesAndDistance; |
1248 | $distanceComponents = explode( ' ', trim( $distance ) ); |
1249 | if ( count( $distanceComponents ) != 2 ) { |
1250 | throw new MWException( "Error: the third argument for the 'NEAR' operator, " |
1251 | . "representing the distance, must be of the form \"number unit\"." ); |
1252 | } |
1253 | [ $distanceNumber, $distanceUnit ] = $distanceComponents; |
1254 | $distanceNumber = trim( $distanceNumber ); |
1255 | $distanceUnit = trim( $distanceUnit ); |
1256 | [ $latDistance, $longDistance ] = self::distanceToDegrees( $distanceNumber, $distanceUnit, |
1257 | $latitude ); |
1258 | // There are much better ways to do this, but |
1259 | // for now, just make a "bounding box" instead |
1260 | // of a bounding circle. |
1261 | $newWhere = " $tableAlias.{$fieldName}__lat >= " . max( $latitude - $latDistance, -90 ) . |
1262 | " AND $tableAlias.{$fieldName}__lat <= " . min( $latitude + $latDistance, 90 ) . |
1263 | " AND $tableAlias.{$fieldName}__lon >= " . max( $longitude - $longDistance, -180 ) . |
1264 | " AND $tableAlias.{$fieldName}__lon <= " . min( $longitude + $longDistance, 180 ) . ' '; |
1265 | |
1266 | if ( $foundMatch1 ) { |
1267 | $this->mWhereStr = preg_replace( $pattern1, $newWhere, $this->mWhereStr ); |
1268 | } elseif ( $foundMatch2 ) { |
1269 | $this->mWhereStr = preg_replace( $pattern2, $newWhere, $this->mWhereStr ); |
1270 | } |
1271 | } |
1272 | } |
1273 | |
1274 | // "order by" |
1275 | // This one is simpler than the others - just add a "__full" |
1276 | // to each coordinates field in the "order by" clause. |
1277 | $matches = []; |
1278 | foreach ( $coordinateFields as $coordinateField ) { |
1279 | $fieldName = $coordinateField['fieldName']; |
1280 | $tableAlias = $coordinateField['tableAlias']; |
1281 | |
1282 | $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, true ); |
1283 | $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName, true ); |
1284 | foreach ( $this->mOrderBy as &$orderByElem ) { |
1285 | $orderByElem = preg_replace( $pattern1, '$1' . "$tableAlias.$fieldName" . '__full$2', $orderByElem ); |
1286 | $orderByElem = preg_replace( $pattern2, '$1' . $fieldName . '__full$2', $orderByElem ); |
1287 | } |
1288 | } |
1289 | } |
1290 | |
1291 | /** |
1292 | * Handles Hierarchy fields' "WHERE" operations |
1293 | */ |
1294 | private function handleHierarchyFields() { |
1295 | // "where" - make sure that if |
1296 | // "WITHIN" (if not list) or "HOLDS WITHIN" (if list) |
1297 | // is specified, then translate the clause accordingly. |
1298 | // other translations in case of List fields, |
1299 | // are handled by handleVirtualFields(). |
1300 | |
1301 | // First, create an array of the hierarchy fields in the |
1302 | // current set of tables. |
1303 | $hierarchyFields = []; |
1304 | foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { |
1305 | foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { |
1306 | if ( !$fieldDescription->mIsHierarchy ) { |
1307 | continue; |
1308 | } |
1309 | foreach ( $this->mAliasedTableNames as $tableAlias => $tableName2 ) { |
1310 | if ( $tableName == $tableName2 ) { |
1311 | $hierarchyFields[] = [ |
1312 | 'fieldName' => $fieldName, |
1313 | 'tableAlias' => $tableAlias, |
1314 | 'tableName' => $tableName, |
1315 | 'isList' => $fieldDescription->mIsList |
1316 | ]; |
1317 | } |
1318 | } |
1319 | } |
1320 | } |
1321 | |
1322 | // "where" |
1323 | foreach ( $hierarchyFields as $hierarchyField ) { |
1324 | $fieldName = $hierarchyField['fieldName']; |
1325 | $tableName = $hierarchyField['tableName']; |
1326 | $tableAlias = $hierarchyField['tableAlias']; |
1327 | $fieldIsList = $hierarchyField['isList']; |
1328 | |
1329 | $patternSimple = [ |
1330 | CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName ), |
1331 | CargoUtils::getSQLFieldPattern( $fieldName ) |
1332 | ]; |
1333 | $patternRootArray = [ |
1334 | CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ), |
1335 | CargoUtils::getSQLFieldPattern( $fieldName, false ) |
1336 | ]; |
1337 | |
1338 | $simpleMatch = false; |
1339 | $completeMatch = false; |
1340 | $patternRoot = ""; |
1341 | |
1342 | if ( preg_match( $patternSimple[0], $this->mWhereStr ) ) { |
1343 | $simpleMatch = true; |
1344 | $patternRoot = $patternRootArray[0]; |
1345 | } elseif ( preg_match( $patternSimple[1], $this->mWhereStr ) ) { |
1346 | $simpleMatch = true; |
1347 | $patternRoot = $patternRootArray[1]; |
1348 | } |
1349 | // else we don't have current field in WHERE clause |
1350 | |
1351 | if ( !$simpleMatch ) { |
1352 | continue; |
1353 | } |
1354 | $patternSuffix = '([\'"]?[^\'"]*[\'"]?)/i'; // To capture string in quotes or a number |
1355 | $hierarchyTable = $this->mCargoDB->tableName( $tableName . '__' . $fieldName . '__hierarchy' ); |
1356 | $fieldTableName = $this->mCargoDB->tableName( $tableName . '__' . $fieldName ); |
1357 | $completeSearchPattern = ""; |
1358 | $matches = []; |
1359 | $newWhere = ""; |
1360 | $leftFieldName = $this->mCargoDB->addIdentifierQuotes( "_left" ); |
1361 | $rightFieldName = $this->mCargoDB->addIdentifierQuotes( "_right" ); |
1362 | $valueFieldName = $this->mCargoDB->addIdentifierQuotes( "_value" ); |
1363 | |
1364 | if ( preg_match( $patternRoot . '(\s+HOLDS WITHIN\s+)' . $patternSuffix, $this->mWhereStr, $matches ) ) { |
1365 | if ( !$fieldIsList ) { |
1366 | throw new MWException( "Error: \"HOLDS WITHIN\" cannot be used for single hierarchy field, use \"WITHIN\" instead." ); |
1367 | } |
1368 | $completeMatch = true; |
1369 | $completeSearchPattern = $patternRoot . '(\s+HOLDS WITHIN\s+)' . $patternSuffix; |
1370 | if ( count( $matches ) != 4 || $matches[3] === "" ) { |
1371 | throw new MWException( "Error: Please specify a value for \"HOLDS WITHIN\"" ); |
1372 | } |
1373 | $withinValue = $matches[3]; |
1374 | $subquery = "( SELECT $valueFieldName FROM $hierarchyTable WHERE " . |
1375 | "$leftFieldName >= ( SELECT $leftFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) AND " . |
1376 | "$rightFieldName <= ( SELECT $rightFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) " . |
1377 | ")"; |
1378 | $subquery = "( SELECT DISTINCT( " . $this->mCargoDB->addIdentifierQuotes( "_rowID" ) . " ) FROM $fieldTableName WHERE $valueFieldName IN " . $subquery . " )"; |
1379 | $newWhere = " " . $tableName . "._ID" . " IN " . $subquery; |
1380 | } |
1381 | |
1382 | if ( preg_match( $patternRoot . '(\s+WITHIN\s+)' . $patternSuffix, $this->mWhereStr, $matches ) ) { |
1383 | if ( $fieldIsList ) { |
1384 | throw new MWException( "Error: \"WITHIN\" cannot be used for list hierarchy field, use \"HOLDS WITHIN\" instead." ); |
1385 | } |
1386 | $completeMatch = true; |
1387 | $completeSearchPattern = $patternRoot . '(\s+WITHIN\s+)' . $patternSuffix; |
1388 | if ( count( $matches ) != 4 || $matches[3] === "" ) { |
1389 | throw new MWException( "Error: Please specify a value for \"WITHIN\"" ); |
1390 | } |
1391 | $withinValue = $matches[3]; |
1392 | $subquery = "( SELECT $valueFieldName FROM $hierarchyTable WHERE " . |
1393 | "$leftFieldName >= ( SELECT $leftFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) AND " . |
1394 | "$rightFieldName <= ( SELECT $rightFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) " . |
1395 | ")"; |
1396 | $newWhere = " " . $fieldName . " IN " . $subquery; |
1397 | } |
1398 | |
1399 | if ( $completeMatch ) { |
1400 | $this->mWhereStr = preg_replace( $completeSearchPattern, $newWhere, $this->mWhereStr ); |
1401 | } |
1402 | |
1403 | // In case fieldIsList === true, there is a possibility of more Query commands. |
1404 | // like "HOLDS" and "HOLDS LIKE", that is handled by handleVirtualFields() |
1405 | } |
1406 | } |
1407 | |
1408 | /** |
1409 | * Returns the number of degrees of both latitude and longitude that |
1410 | * correspond to the passed-in distance (in either kilometers or |
1411 | * miles), based on the passed-in latitude. (Longitude doesn't matter |
1412 | * when doing this conversion, but latitude does.) |
1413 | */ |
1414 | private static function distanceToDegrees( $distanceNumber, $distanceUnit, $latString ) { |
1415 | if ( in_array( $distanceUnit, [ 'kilometers', 'kilometres', 'km' ] ) ) { |
1416 | $distanceInKM = $distanceNumber; |
1417 | } elseif ( in_array( $distanceUnit, [ 'miles', 'mi' ] ) ) { |
1418 | $distanceInKM = $distanceNumber * 1.60934; |
1419 | } else { |
1420 | throw new MWException( "Error: distance for 'NEAR' operator must be in either miles or " |
1421 | . "kilometers (\"$distanceUnit\" specified)." ); |
1422 | } |
1423 | // The calculation of distance to degrees latitude is |
1424 | // essentially the same wherever you are on the globe, although |
1425 | // the longitude calculation is more complicated. |
1426 | $latDistance = $distanceInKM / 111; |
1427 | |
1428 | // Convert the latitude string to a latitude number - code is |
1429 | // copied from CargoUtils::parseCoordinatesString(). |
1430 | $latIsNegative = false; |
1431 | if ( strpos( $latString, 'S' ) > 0 ) { |
1432 | $latIsNegative = true; |
1433 | } |
1434 | $latString = str_replace( [ 'N', 'S' ], '', $latString ); |
1435 | if ( is_numeric( $latString ) ) { |
1436 | $latNum = floatval( $latString ); |
1437 | } else { |
1438 | $latNum = CargoUtils::coordinatePartToNumber( $latString ); |
1439 | } |
1440 | if ( $latIsNegative ) { |
1441 | $latNum *= -1; |
1442 | } |
1443 | |
1444 | $lengthOfOneDegreeLongitude = cos( deg2rad( $latNum ) ) * 111.321; |
1445 | $longDistance = $distanceInKM / $lengthOfOneDegreeLongitude; |
1446 | |
1447 | return [ $latDistance, $longDistance ]; |
1448 | } |
1449 | |
1450 | /** |
1451 | * For each date field, also add its corresponding "precisicon" |
1452 | * field (which indicates whether the date is year-only, etc.) to |
1453 | * the query. |
1454 | */ |
1455 | public function handleDateFields() { |
1456 | $dateFields = []; |
1457 | foreach ( $this->mOrigAliasedFieldNames as $alias => $fieldName ) { |
1458 | if ( !array_key_exists( $alias, $this->mFieldDescriptions ) ) { |
1459 | continue; |
1460 | } |
1461 | $fieldDescription = $this->mFieldDescriptions[$alias]; |
1462 | if ( ( $fieldDescription->mType == 'Date' || $fieldDescription->mType == 'Datetime' || |
1463 | $fieldDescription->mType == 'Start date' || $fieldDescription->mType == 'Start datetime' || |
1464 | $fieldDescription->mType == 'End date' || $fieldDescription->mType == 'End datetime' ) && |
1465 | // Make sure this is an actual field and not a call |
1466 | // to a function, like DATE_FORMAT(), by checking for |
1467 | // the presence of '(' and ')' - there's probably a |
1468 | // more elegant way to do this. |
1469 | ( strpos( $fieldName, '(' ) == false ) && ( strpos( $fieldName, ')' ) == false ) ) { |
1470 | $dateFields[$alias] = $fieldName; |
1471 | } |
1472 | } |
1473 | foreach ( $dateFields as $alias => $dateField ) { |
1474 | // Handle fields that are a list of dates. |
1475 | if ( substr( $dateField, -6 ) == '__full' ) { |
1476 | $dateField = substr( $dateField, 0, -6 ); |
1477 | } |
1478 | $precisionFieldName = $dateField . '__precision'; |
1479 | $precisionFieldAlias = $alias . '__precision'; |
1480 | $this->mAliasedFieldNames[$precisionFieldAlias] = $precisionFieldName; |
1481 | } |
1482 | } |
1483 | |
1484 | private function handleSearchTextFields() { |
1485 | $searchTextFields = []; |
1486 | foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { |
1487 | foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { |
1488 | if ( $fieldDescription->mType != 'Searchtext' ) { |
1489 | continue; |
1490 | } |
1491 | $fieldAlias = array_search( $fieldName, $this->mAliasedFieldNames ); |
1492 | if ( $fieldAlias === false ) { |
1493 | $tableAlias = array_search( $tableName, $this->mAliasedTableNames ); |
1494 | $fieldAlias = array_search( "$tableAlias.$fieldName", $this->mAliasedFieldNames ); |
1495 | } |
1496 | if ( $fieldAlias === false ) { |
1497 | $fieldAlias = $fieldName; |
1498 | } |
1499 | $searchTextFields[] = [ |
1500 | 'fieldName' => $fieldName, |
1501 | 'fieldAlias' => $fieldAlias, |
1502 | 'tableName' => $tableName |
1503 | ]; |
1504 | } |
1505 | } |
1506 | |
1507 | $matches = []; |
1508 | foreach ( $searchTextFields as $searchTextField ) { |
1509 | $fieldName = $searchTextField['fieldName']; |
1510 | $fieldAlias = $searchTextField['fieldAlias']; |
1511 | $tableName = $searchTextField['tableName']; |
1512 | $tableAlias = array_search( $tableName, $this->mAliasedTableNames ); |
1513 | $patternSuffix = '(\s+MATCHES\s*)([\'"][^\'"]*[\'"])/i'; |
1514 | $patternSuffix1 = '(\s+MATCHES\s*)(\'[^\']*\')/i'; |
1515 | $patternSuffix2 = '(\s+MATCHES\s*)("[^"]*")/i'; |
1516 | |
1517 | $patterns = [ |
1518 | CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . $patternSuffix1, |
1519 | CargoUtils::getSQLFieldPattern( $fieldName, false ) . $patternSuffix1, |
1520 | CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . $patternSuffix2, |
1521 | CargoUtils::getSQLFieldPattern( $fieldName, false ) . $patternSuffix2 |
1522 | ]; |
1523 | $matchingPattern = null; |
1524 | foreach ( $patterns as $i => $pattern ) { |
1525 | $foundMatch = preg_match( $pattern, $this->mWhereStr, $matches ); |
1526 | if ( $foundMatch ) { |
1527 | $matchingPattern = $i; |
1528 | break; |
1529 | } |
1530 | } |
1531 | |
1532 | if ( $foundMatch ) { |
1533 | $searchString = $matches[3]; |
1534 | $newWhere = " MATCH($tableAlias.$fieldName) AGAINST ($searchString IN BOOLEAN MODE) "; |
1535 | |
1536 | $pattern = $patterns[$matchingPattern]; |
1537 | $this->mWhereStr = preg_replace( $pattern, $newWhere, $this->mWhereStr ); |
1538 | $searchEngine = new CargoSearchMySQL(); |
1539 | $searchTerms = $searchEngine->getSearchTerms( $searchString ); |
1540 | // @TODO - does $tableName need to be in there? |
1541 | $this->mSearchTerms[$fieldAlias] = $searchTerms; |
1542 | } |
1543 | } |
1544 | } |
1545 | |
1546 | /** |
1547 | * Adds the "cargo" table prefix for every element in the SQL query |
1548 | * except for 'tables' and 'join on' - for 'tables', the prefix is |
1549 | * prepended automatically by the MediaWiki query, while for |
1550 | * 'join on' the prefixes are added when the object is created. |
1551 | */ |
1552 | private function addTablePrefixesToAll() { |
1553 | foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { |
1554 | $this->mAliasedFieldNames[$alias] = $this->addTablePrefixes( $fieldName ); |
1555 | } |
1556 | $this->mWhereStr = $this->addTablePrefixes( $this->mWhereStr ); |
1557 | $this->mGroupByStr = $this->addTablePrefixes( $this->mGroupByStr ); |
1558 | $this->mHavingStr = $this->addTablePrefixes( $this->mHavingStr ); |
1559 | foreach ( $this->mOrderBy as &$orderByElem ) { |
1560 | $orderByElem = $this->addTablePrefixes( $orderByElem ); |
1561 | } |
1562 | } |
1563 | |
1564 | /** |
1565 | * Calls a database SELECT query given the parts of the query; first |
1566 | * appending the Cargo prefix onto table names where necessary. |
1567 | */ |
1568 | public function run() { |
1569 | foreach ( $this->mAliasedTableNames as $tableName ) { |
1570 | if ( !$this->mCargoDB->tableExists( $tableName, __METHOD__ ) ) { |
1571 | throw new MWException( "Error: No database table exists named \"$tableName\"." ); |
1572 | } |
1573 | } |
1574 | |
1575 | $selectOptions = []; |
1576 | |
1577 | if ( $this->mGroupByStr != '' ) { |
1578 | $selectOptions['GROUP BY'] = $this->mGroupByStr; |
1579 | } |
1580 | if ( $this->mHavingStr != '' ) { |
1581 | $selectOptions['HAVING'] = $this->mHavingStr; |
1582 | } |
1583 | |
1584 | $selectOptions['ORDER BY'] = $this->mOrderBy; |
1585 | $selectOptions['LIMIT'] = $this->mQueryLimit; |
1586 | $selectOptions['OFFSET'] = $this->mOffset; |
1587 | |
1588 | // Aliases need to be surrounded by quotes when we actually |
1589 | // call the DB query. |
1590 | $realAliasedFieldNames = []; |
1591 | foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { |
1592 | // If it's either a field, or a table + field, |
1593 | // add quotes around the name(s). |
1594 | if ( strpos( $fieldName, '(' ) === false ) { |
1595 | if ( strpos( $fieldName, '.' ) === false ) { |
1596 | if ( !$this->mCargoDB->isQuotedIdentifier( $fieldName ) && !CargoUtils::isSQLStringLiteral( $fieldName ) ) { |
1597 | $fieldName = $this->mCargoDB->addIdentifierQuotes( $fieldName ); |
1598 | } |
1599 | } else { |
1600 | [ $realTableName, $realFieldName ] = explode( '.', $fieldName, 2 ); |
1601 | if ( !$this->mCargoDB->isQuotedIdentifier( $realTableName ) && !CargoUtils::isSQLStringLiteral( $realTableName ) ) { |
1602 | $realTableName = $this->mCargoDB->addIdentifierQuotes( $realTableName ); |
1603 | } |
1604 | if ( !$this->mCargoDB->isQuotedIdentifier( $realFieldName ) && !CargoUtils::isSQLStringLiteral( $realFieldName ) ) { |
1605 | $realFieldName = $this->mCargoDB->addIdentifierQuotes( $realFieldName ); |
1606 | } |
1607 | $fieldName = "$realTableName.$realFieldName"; |
1608 | } |
1609 | } |
1610 | $realAliasedFieldNames[$alias] = $fieldName; |
1611 | } |
1612 | |
1613 | $res = $this->mCargoDB->select( $this->mAliasedTableNames, $realAliasedFieldNames, $this->mWhereStr, __METHOD__, |
1614 | $selectOptions, $this->mJoinConds ); |
1615 | |
1616 | // Is there a more straightforward way of turning query |
1617 | // results into an array? |
1618 | $resultArray = []; |
1619 | foreach ( $res as $row ) { |
1620 | $resultsRow = []; |
1621 | foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { |
1622 | if ( !isset( $row->$alias ) ) { |
1623 | $resultsRow[$alias] = null; |
1624 | continue; |
1625 | } |
1626 | |
1627 | $curValue = $row->$alias; |
1628 | if ( $curValue instanceof DateTime ) { |
1629 | // @TODO - This code may no longer be necessary. |
1630 | $resultsRow[$alias] = $curValue->format( DateTime::W3C ); |
1631 | } else { |
1632 | // It's a string. |
1633 | // Escape any HTML, to avoid JavaScript |
1634 | // injections and the like. |
1635 | $resultsRow[$alias] = htmlspecialchars( $curValue ); |
1636 | } |
1637 | } |
1638 | $resultArray[] = $resultsRow; |
1639 | } |
1640 | |
1641 | return $resultArray; |
1642 | } |
1643 | |
1644 | private function addTablePrefixes( $string ) { |
1645 | if ( $string === null ) { |
1646 | return null; |
1647 | } |
1648 | |
1649 | // Create arrays for doing replacements of table names within |
1650 | // the SQL by their "real" equivalents. |
1651 | $tableNamePatterns = []; |
1652 | foreach ( $this->mAliasedTableNames as $alias => $tableName ) { |
1653 | $tableNamePatterns[] = CargoUtils::getSQLTablePattern( $tableName ); |
1654 | $tableNamePatterns[] = CargoUtils::getSQLTablePattern( $alias ); |
1655 | } |
1656 | |
1657 | return preg_replace_callback( $tableNamePatterns, |
1658 | [ $this, 'addQuotes' ], $string ); |
1659 | } |
1660 | |
1661 | private function addQuotes( $matches ) { |
1662 | $beforeText = $matches[1]; |
1663 | $tableName = $matches[2]; |
1664 | $fieldName = $matches[3]; |
1665 | $isTableAlias = false; |
1666 | if ( array_key_exists( $tableName, $this->mAliasedTableNames ) ) { |
1667 | if ( !in_array( $tableName, $this->mAliasedTableNames ) ) { |
1668 | $isTableAlias = true; |
1669 | } |
1670 | } |
1671 | if ( $isTableAlias ) { |
1672 | return $beforeText . $this->mCargoDB->addIdentifierQuotes( $tableName ) . "." . |
1673 | $this->mCargoDB->addIdentifierQuotes( $fieldName ); |
1674 | } else { |
1675 | return $beforeText . $this->mCargoDB->tableName( $tableName ) . "." . |
1676 | $this->mCargoDB->addIdentifierQuotes( $fieldName ); |
1677 | } |
1678 | } |
1679 | |
1680 | /** |
1681 | * Figure out which fields, if any, in this query are supposed to |
1682 | * represent start and end dates, based on a combination of field types, |
1683 | * order (start is expected to be listed before end) and alias. |
1684 | * @todo - this logic currently allows for any number of start/end |
1685 | * date pairs, but that may be overly complicated - it may be safe to |
1686 | * assume that any query contains no more than one start date and end |
1687 | * date, and any other dates can just be ignored, i.e. treated as |
1688 | * display fields. |
1689 | */ |
1690 | public function determineDateFields() { |
1691 | foreach ( $this->mFieldDescriptions as $alias => $description ) { |
1692 | $realFieldName = $this->mAliasedFieldNames[$alias] ?? $alias; |
1693 | $curNameAndAlias = [ $realFieldName, $alias ]; |
1694 | if ( $alias == 'start' || $description->mType == 'Start date' || $description->mType == 'Start datetime' ) { |
1695 | $foundMatch = false; |
1696 | foreach ( $this->mDateFieldPairs as &$datePair ) { |
1697 | if ( array_key_exists( 'end', $datePair ) && !array_key_exists( 'start', $datePair ) ) { |
1698 | $datePair['start'] = $curNameAndAlias; |
1699 | $foundMatch = true; |
1700 | break; |
1701 | } |
1702 | } |
1703 | if ( !$foundMatch ) { |
1704 | $this->mDateFieldPairs[] = [ 'start' => $curNameAndAlias ]; |
1705 | } |
1706 | } elseif ( $alias == 'end' || $description->mType == 'End date' || $description->mType == 'End datetime' ) { |
1707 | $foundMatch = false; |
1708 | foreach ( $this->mDateFieldPairs as &$datePair ) { |
1709 | if ( array_key_exists( 'start', $datePair ) && !array_key_exists( 'end', $datePair ) ) { |
1710 | $datePair['end'] = $curNameAndAlias; |
1711 | $foundMatch = true; |
1712 | break; |
1713 | } |
1714 | } |
1715 | if ( !$foundMatch ) { |
1716 | $this->mDateFieldPairs[] = [ 'end' => $curNameAndAlias ]; |
1717 | } |
1718 | } elseif ( $description->mType == 'Date' || $description->mType == 'Datetime' ) { |
1719 | $foundMatch = false; |
1720 | foreach ( $this->mDateFieldPairs as &$datePair ) { |
1721 | if ( array_key_exists( 'end', $datePair ) && !array_key_exists( 'start', $datePair ) ) { |
1722 | $datePair['start'] = $curNameAndAlias; |
1723 | $foundMatch = true; |
1724 | break; |
1725 | } elseif ( array_key_exists( 'start', $datePair ) && !array_key_exists( 'end', $datePair ) ) { |
1726 | $datePair['end'] = $curNameAndAlias; |
1727 | $foundMatch = true; |
1728 | break; |
1729 | } |
1730 | } |
1731 | if ( !$foundMatch ) { |
1732 | $this->mDateFieldPairs[] = [ 'start' => $curNameAndAlias ]; |
1733 | } |
1734 | } |
1735 | } |
1736 | |
1737 | // Error-checking. |
1738 | if ( count( $this->mDateFieldPairs ) == 0 ) { |
1739 | throw new MWException( "Error: No date fields were found in this query." ); |
1740 | } |
1741 | foreach ( $this->mDateFieldPairs as $datePair ) { |
1742 | if ( !array_key_exists( 'start', $datePair ) ) { |
1743 | throw new MWException( "Error: No corresponding start date field was found for the end date field {$datePair['end'][0]}." ); |
1744 | } |
1745 | } |
1746 | } |
1747 | |
1748 | public function getMainStartAndEndDateFields() { |
1749 | if ( count( $this->mDateFieldPairs ) == 0 ) { |
1750 | $this->determineDateFields(); |
1751 | } |
1752 | $firstFieldPair = $this->mDateFieldPairs[0]; |
1753 | $startDateField = $firstFieldPair['start'][1]; |
1754 | $endDateField = ( array_key_exists( 'end', $firstFieldPair ) ) ? $firstFieldPair['end'][1] : null; |
1755 | return [ $startDateField, $endDateField ]; |
1756 | } |
1757 | |
1758 | /** |
1759 | * See if a query does aggregation - used to determine whether a |
1760 | * "backlinks" element should be added to the query. (It doesn't make |
1761 | * sense for aggregating queries.) |
1762 | * This is somewhat redundant code, because all of these fields and |
1763 | * their functions have been parsed already, but it seemed easier to |
1764 | * just do this specific re-parse. |
1765 | */ |
1766 | public function isAggregating() { |
1767 | if ( $this->mGroupByStr != '' ) { |
1768 | return true; |
1769 | } |
1770 | foreach ( $this->mOrigAliasedFieldNames as $alias => $field ) { |
1771 | $sqlFunctionMatch = []; |
1772 | $sqlFunctionRegex = '/(\b|\W)(\w*?)\s*\(/'; |
1773 | preg_match( $sqlFunctionRegex, $field, $sqlFunctionMatch ); |
1774 | if ( count( $sqlFunctionMatch ) > 1 ) { |
1775 | $firstFunction = strtoupper( trim( $sqlFunctionMatch[2] ) ); |
1776 | if ( in_array( $firstFunction, [ 'COUNT', 'AVG', 'MAX', 'MIN' ] ) ) { |
1777 | return true; |
1778 | } |
1779 | } |
1780 | } |
1781 | return false; |
1782 | } |
1783 | |
1784 | } |