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