Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 29 |
CRAP | |
0.00% |
0 / 907 |
| CargoSQLQuery | |
0.00% |
0 / 1 |
|
0.00% |
0 / 29 |
109892 | |
0.00% |
0 / 907 |
| __construct | |
0.00% |
0 / 1 |
2 | |
0.00% |
0 / 2 |
|||
| newFromValues | |
0.00% |
0 / 1 |
12 | |
0.00% |
0 / 31 |
|||
| validateValues | |
0.00% |
0 / 1 |
182 | |
0.00% |
0 / 32 |
|||
| getAliasForFieldString | |
0.00% |
0 / 1 |
2 | |
0.00% |
0 / 1 |
|||
| setAliasedFieldNames | |
0.00% |
0 / 1 |
90 | |
0.00% |
0 / 28 |
|||
| setAliasedTableNames | |
0.00% |
0 / 1 |
20 | |
0.00% |
0 / 13 |
|||
| setCargoJoinConds | |
0.00% |
0 / 1 |
702 | |
0.00% |
0 / 79 |
|||
| setMWJoinConds | |
0.00% |
0 / 1 |
110 | |
0.00% |
0 / 29 |
|||
| setOrderBy | |
0.00% |
0 / 1 |
182 | |
0.00% |
0 / 23 |
|||
| setGroupBy | |
0.00% |
0 / 1 |
30 | |
0.00% |
0 / 7 |
|||
| getAndValidateSQLFunctions | |
0.00% |
0 / 1 |
20 | |
0.00% |
0 / 13 |
|||
| getDescriptionAndTableNameForField | |
0.00% |
0 / 1 |
1892 | |
0.00% |
0 / 90 |
|||
| setDescriptionsAndTableNamesForFields | |
0.00% |
0 / 1 |
6 | |
0.00% |
0 / 8 |
|||
| addToCargoJoinConds | |
0.00% |
0 / 1 |
72 | |
0.00% |
0 / 12 |
|||
| addFieldTableToTableNames | |
0.00% |
0 / 1 |
12 | |
0.00% |
0 / 10 |
|||
| fieldTableIsIncluded | |
0.00% |
0 / 1 |
20 | |
0.00% |
0 / 5 |
|||
| substVirtualFieldName | |
0.00% |
0 / 1 |
72 | |
0.00% |
0 / 27 |
|||
| handleVirtualFields | |
0.00% |
0 / 1 |
1980 | |
0.00% |
0 / 160 |
|||
| handleVirtualCoordinateFields | |
0.00% |
0 / 1 |
552 | |
0.00% |
0 / 73 |
|||
| handleHierarchyFields | |
0.00% |
0 / 1 |
380 | |
0.00% |
0 / 71 |
|||
| distanceToDegrees | |
0.00% |
0 / 1 |
42 | |
0.00% |
0 / 19 |
|||
| handleDateFields | |
0.00% |
0 / 1 |
182 | |
0.00% |
0 / 17 |
|||
| handleSearchTextFields | |
0.00% |
0 / 1 |
110 | |
0.00% |
0 / 43 |
|||
| addTablePrefixesToAll | |
0.00% |
0 / 1 |
20 | |
0.00% |
0 / 9 |
|||
| run | |
0.00% |
0 / 1 |
342 | |
0.00% |
0 / 39 |
|||
| addTablePrefixes | |
0.00% |
0 / 1 |
6 | |
0.00% |
0 / 6 |
|||
| addQuotes | |
0.00% |
0 / 1 |
20 | |
0.00% |
0 / 12 |
|||
| determineDateFields | |
0.00% |
0 / 1 |
812 | |
0.00% |
0 / 42 |
|||
| getMainStartAndEndDateFields | |
0.00% |
0 / 1 |
12 | |
0.00% |
0 / 6 |
|||
| <?php | |
| /** | |
| * CargoSQLQuery - a wrapper class around SQL queries, that also handles | |
| * the special Cargo keywords like "HOLDS" and "NEAR". | |
| * | |
| * @author Yaron Koren | |
| * @ingroup Cargo | |
| */ | |
| class CargoSQLQuery { | |
| private $mCargoDB; | |
| public $mTablesStr; | |
| public $mAliasedTableNames; | |
| public $mFieldsStr; | |
| public $mOrigWhereStr; | |
| public $mWhereStr; | |
| public $mJoinOnStr; | |
| public $mCargoJoinConds; | |
| public $mJoinConds; | |
| public $mAliasedFieldNames; | |
| public $mOrigAliasedFieldNames; | |
| public $mFieldStringAliases; | |
| public $mTableSchemas; | |
| public $mFieldDescriptions; | |
| public $mFieldTables; | |
| public $mOrigGroupByStr; | |
| public $mGroupByStr; | |
| public $mOrigHavingStr; | |
| public $mHavingStr; | |
| public $mOrderBy; | |
| public $mQueryLimit; | |
| public $mOffset; | |
| public $mSearchTerms = []; | |
| public $mDateFieldPairs = []; | |
| public function __construct() { | |
| $this->mCargoDB = CargoUtils::getDB(); | |
| } | |
| /** | |
| * This is newFromValues() instead of __construct() so that an | |
| * object can be created without any values. | |
| */ | |
| public static function newFromValues( $tablesStr, $fieldsStr, $whereStr, $joinOnStr, $groupByStr, | |
| $havingStr, $orderByStr, $limitStr, $offsetStr ) { | |
| global $wgCargoDefaultQueryLimit, $wgCargoMaxQueryLimit; | |
| // "table(s)" is the only mandatory value. | |
| if ( $tablesStr == '' ) { | |
| throw new MWException( "At least one table must be specified." ); | |
| } | |
| self::validateValues( $tablesStr, $fieldsStr, $whereStr, $joinOnStr, $groupByStr, | |
| $havingStr, $orderByStr, $limitStr, $offsetStr ); | |
| $sqlQuery = new CargoSQLQuery(); | |
| $sqlQuery->mCargoDB = CargoUtils::getDB(); | |
| $sqlQuery->mTablesStr = $tablesStr; | |
| $sqlQuery->setAliasedTableNames(); | |
| $sqlQuery->mFieldsStr = $fieldsStr; | |
| // This _decode() call is necessary because the "where=" | |
| // clause can (and often does) include a call to {{PAGENAME}}, | |
| // which HTML-encodes certain characters, notably single quotes. | |
| $sqlQuery->mOrigWhereStr = htmlspecialchars_decode( $whereStr, ENT_QUOTES ); | |
| $sqlQuery->mWhereStr = $sqlQuery->mOrigWhereStr; | |
| $sqlQuery->mJoinOnStr = $joinOnStr; | |
| $sqlQuery->setCargoJoinConds( $joinOnStr ); | |
| $sqlQuery->setAliasedFieldNames(); | |
| $sqlQuery->mTableSchemas = CargoUtils::getTableSchemas( $sqlQuery->mAliasedTableNames ); | |
| $sqlQuery->setOrderBy( $orderByStr ); | |
| $sqlQuery->setGroupBy( $groupByStr ); | |
| $sqlQuery->mOrigHavingStr = $havingStr; | |
| $sqlQuery->mHavingStr = $sqlQuery->mOrigHavingStr; | |
| $sqlQuery->setDescriptionsAndTableNamesForFields(); | |
| $sqlQuery->handleHierarchyFields(); | |
| $sqlQuery->handleVirtualFields(); | |
| $sqlQuery->handleVirtualCoordinateFields(); | |
| $sqlQuery->handleDateFields(); | |
| $sqlQuery->handleSearchTextFields(); | |
| $sqlQuery->setMWJoinConds(); | |
| $sqlQuery->mQueryLimit = $wgCargoDefaultQueryLimit; | |
| if ( $limitStr != '' ) { | |
| $sqlQuery->mQueryLimit = min( $limitStr, $wgCargoMaxQueryLimit ); | |
| } | |
| $sqlQuery->mOffset = $offsetStr; | |
| $sqlQuery->addTablePrefixesToAll(); | |
| return $sqlQuery; | |
| } | |
| /** | |
| * Throw an error if there are forbidden values in any of the | |
| * #cargo_query parameters - some or all of them are potential | |
| * security risks. | |
| * | |
| * It could be that, given the way #cargo_query is structured, only | |
| * some of the parameters need to be checked for these strings, | |
| * but we might as well validate all of them. | |
| * | |
| * The function CargoUtils::getTableSchemas() also does specific | |
| * validation of the "tables" parameter, while this class's | |
| * setDescriptionsAndTableNameForFields() does validation of the | |
| * "fields=" parameter. | |
| */ | |
| public static function validateValues( $tablesStr, $fieldsStr, $whereStr, $joinOnStr, $groupByStr, | |
| $havingStr, $orderByStr, $limitStr, $offsetStr ) { | |
| // Remove quoted strings from "where" parameter, to avoid | |
| // unnecessary false positives from words like "from" | |
| // being included in string comparisons. | |
| // However, before we do that, check for certain strings that | |
| // shouldn't be in quote marks either. | |
| $whereStrRegexps = [ | |
| '/\-\-/' => '--', | |
| '/#/' => '#', | |
| ]; | |
| // Replace # with corresponding Unicode value to prevent security leaks. | |
| $whereStr = str_replace( '#', '\u0023', $whereStr ); | |
| // HTML-decode the string - this is necessary if the query | |
| // contains a call to {{PAGENAME}} and the page name has any | |
| // special characters, because {{PAGENAME]] unfortunately | |
| // HTML-encodes the value, which leads to a '#' in the string. | |
| $decodedWhereStr = html_entity_decode( $whereStr, ENT_QUOTES ); | |
| foreach ( $whereStrRegexps as $regexp => $displayString ) { | |
| if ( preg_match( $regexp, $decodedWhereStr ) ) { | |
| throw new MWException( "Error in \"where\" parameter: the string \"$displayString\" cannot be used within #cargo_query." ); | |
| } | |
| } | |
| $noQuotesFieldsStr = CargoUtils::removeQuotedStrings( $fieldsStr ); | |
| $noQuotesWhereStr = CargoUtils::removeQuotedStrings( $whereStr ); | |
| $noQuotesJoinOnStr = CargoUtils::removeQuotedStrings( $joinOnStr ); | |
| $noQuotesGroupByStr = CargoUtils::removeQuotedStrings( $groupByStr ); | |
| $noQuotesHavingStr = CargoUtils::removeQuotedStrings( $havingStr ); | |
| $noQuotesOrderByStr = CargoUtils::removeQuotedStrings( $orderByStr ); | |
| $regexps = [ | |
| '/\bselect\b/i' => 'SELECT', | |
| '/\binto\b/i' => 'INTO', | |
| '/\bfrom\b/i' => 'FROM', | |
| '/\bunion\b/i' => 'UNION', | |
| '/;/' => ';', | |
| '/@/' => '@', | |
| '/\<\?/' => '<?', | |
| '/\-\-/' => '--', | |
| '/\/\*/' => '/*', | |
| '/#/' => '#', | |
| ]; | |
| foreach ( $regexps as $regexp => $displayString ) { | |
| if ( preg_match( $regexp, $tablesStr ) || | |
| preg_match( $regexp, $noQuotesFieldsStr ) || | |
| preg_match( $regexp, $noQuotesWhereStr ) || | |
| preg_match( $regexp, $noQuotesJoinOnStr ) || | |
| preg_match( $regexp, $noQuotesGroupByStr ) || | |
| preg_match( $regexp, $noQuotesHavingStr ) || | |
| preg_match( $regexp, $noQuotesOrderByStr ) || | |
| preg_match( $regexp, $limitStr ) || | |
| preg_match( $regexp, $offsetStr ) ) { | |
| throw new MWException( "Error: the string \"$displayString\" cannot be used within #cargo_query." ); | |
| } | |
| } | |
| self::getAndValidateSQLFunctions( $noQuotesWhereStr ); | |
| self::getAndValidateSQLFunctions( $noQuotesJoinOnStr ); | |
| self::getAndValidateSQLFunctions( $noQuotesGroupByStr ); | |
| self::getAndValidateSQLFunctions( $noQuotesHavingStr ); | |
| self::getAndValidateSQLFunctions( $noQuotesOrderByStr ); | |
| self::getAndValidateSQLFunctions( $limitStr ); | |
| self::getAndValidateSQLFunctions( $offsetStr ); | |
| } | |
| /** | |
| * Gets a mapping of original field name strings to their field name aliases | |
| * as they appear in the query result | |
| */ | |
| public function getAliasForFieldString( $fieldString ) { | |
| return $this->mFieldStringAliases[$fieldString]; | |
| } | |
| /** | |
| * Gets an array of field names and their aliases from the passed-in | |
| * SQL fragment. | |
| */ | |
| private function setAliasedFieldNames() { | |
| $this->mAliasedFieldNames = []; | |
| $fieldStrings = CargoUtils::smartSplit( ',', $this->mFieldsStr ); | |
| // Default is "_pageName". | |
| if ( count( $fieldStrings ) == 0 ) { | |
| $fieldStrings[] = '_pageName'; | |
| } | |
| // Quick error-checking: for now, just disallow "DISTINCT", | |
| // and require "GROUP BY" instead. | |
| foreach ( $fieldStrings as $i => $fieldString ) { | |
| if ( strtolower( substr( $fieldString, 0, 9 ) ) == 'distinct ' ) { | |
| throw new MWException( "Error: The DISTINCT keyword is not allowed by Cargo; " | |
| . "please use \"group by=\" instead." ); | |
| } | |
| } | |
| // Because aliases are used as keys, we can't have more than | |
| // one blank alias - so replace blank aliases with the name | |
| // "Blank value X" - it will get replaced back before being | |
| // displayed. | |
| $blankAliasCount = 0; | |
| foreach ( $fieldStrings as $i => $fieldString ) { | |
| $fieldStringParts = CargoUtils::smartSplit( '=', $fieldString, true ); | |
| if ( count( $fieldStringParts ) == 2 ) { | |
| $fieldName = trim( $fieldStringParts[0] ); | |
| $alias = trim( $fieldStringParts[1] ); | |
| } else { | |
| $fieldName = $fieldString; | |
| // Might as well change underscores to spaces | |
| // by default - but for regular field names, | |
| // not the special ones. | |
| // "Real" field = with the table name removed. | |
| if ( strpos( $fieldName, '.' ) !== false ) { | |
| list( $tableName, $realFieldName ) = explode( '.', $fieldName, 2 ); | |
| } else { | |
| $realFieldName = $fieldName; | |
| } | |
| if ( $realFieldName[0] != '_' ) { | |
| $alias = str_replace( '_', ' ', $realFieldName ); | |
| } else { | |
| $alias = $realFieldName; | |
| } | |
| } | |
| if ( empty( $alias ) ) { | |
| $blankAliasCount++; | |
| $alias = "Blank value $blankAliasCount"; | |
| } | |
| $this->mAliasedFieldNames[$alias] = $fieldName; | |
| $this->mFieldStringAliases[$fieldString] = $alias; | |
| } | |
| $this->mOrigAliasedFieldNames = $this->mAliasedFieldNames; | |
| } | |
| private function setAliasedTableNames() { | |
| $this->mAliasedTableNames = []; | |
| $tableStrings = CargoUtils::smartSplit( ',', $this->mTablesStr ); | |
| foreach ( $tableStrings as $i => $tableString ) { | |
| $tableStringParts = CargoUtils::smartSplit( '=', $tableString ); | |
| if ( count( $tableStringParts ) == 2 ) { | |
| $tableName = trim( $tableStringParts[0] ); | |
| $alias = trim( $tableStringParts[1] ); | |
| } else { | |
| $tableName = $tableString; | |
| $alias = $tableString; | |
| } | |
| if ( empty( $alias ) ) { | |
| throw new MWException( "Error: blank table aliases cannot be set." ); | |
| } | |
| $this->mAliasedTableNames[$alias] = $tableName; | |
| } | |
| } | |
| /** | |
| * This does double duty: it both creates a "join conds" array | |
| * from the string, and validates the set of join conditions | |
| * based on the set of table names - making sure each table is | |
| * joined. | |
| * | |
| * The "join conds" array created is not of the format that | |
| * MediaWiki's database query() method requires - it is more | |
| * structured and does not contain the necessary table prefixes yet. | |
| */ | |
| private function setCargoJoinConds( $joinOnStr ) { | |
| // This string is needed for "deferred" queries. | |
| $this->mJoinOnStr = $joinOnStr; | |
| $this->mCargoJoinConds = []; | |
| if ( trim( $joinOnStr ) == '' ) { | |
| if ( count( $this->mAliasedTableNames ) > 1 ) { | |
| throw new MWException( "Error: join conditions must be set for tables." ); | |
| } | |
| return; | |
| } | |
| $joinStrings = explode( ',', $joinOnStr ); | |
| // 'HOLDS' must be all-caps for now. | |
| $allowedJoinOperators = [ '=', ' HOLDS ', '<=', '>=', '<', '>' ]; | |
| $joinOperator = null; | |
| foreach ( $joinStrings as $joinString ) { | |
| $foundValidOperator = false; | |
| foreach ( $allowedJoinOperators as $allowedOperator ) { | |
| if ( strpos( $joinString, $allowedOperator ) === false ) { | |
| continue; | |
| } | |
| $foundValidOperator = true; | |
| $joinOperator = $allowedOperator; | |
| break; | |
| } | |
| if ( !$foundValidOperator ) { | |
| throw new MWException( "No valid operator found in join condition ($joinString)." ); | |
| } | |
| $joinParts = explode( $joinOperator, $joinString ); | |
| $joinPart1 = trim( $joinParts[0] ); | |
| $tableAndField1 = explode( '.', $joinPart1 ); | |
| if ( count( $tableAndField1 ) != 2 ) { | |
| throw new MWException( "Table and field name must both be specified in '$joinPart1'." ); | |
| } | |
| list( $table1, $field1 ) = $tableAndField1; | |
| $joinPart2 = trim( $joinParts[1] ); | |
| $tableAndField2 = explode( '.', $joinPart2 ); | |
| if ( count( $tableAndField2 ) != 2 ) { | |
| throw new MWException( "Table and field name must both be specified in '$joinPart2'." ); | |
| } | |
| list( $table2, $field2 ) = $tableAndField2; | |
| $tableAliases = array_keys( $this->mAliasedTableNames ); | |
| // Order the tables in the join condition by their relative positions in table names. | |
| $position1 = array_search( $table1, $tableAliases ); | |
| $position2 = array_search( $table2, $tableAliases ); | |
| if ( $position2 < $position1 ) { | |
| // Swap tables and fields if table2 comes before table1 in table names. | |
| [ $table1, $table2 ] = [ $table2, $table1 ]; | |
| [ $field1, $field2 ] = [ $field2, $field1 ]; | |
| } | |
| $joinCond = [ | |
| 'joinType' => 'LEFT OUTER JOIN', | |
| 'table1' => $table1, | |
| 'field1' => $field1, | |
| 'table2' => $table2, | |
| 'field2' => $field2, | |
| 'joinOperator' => $joinOperator | |
| ]; | |
| $this->mCargoJoinConds[] = $joinCond; | |
| } | |
| // Sort the join conditions by the table names. | |
| usort( $this->mCargoJoinConds, static function ( $joinCond1, $joinCond2 ) use( $tableAliases ) { | |
| $index1 = array_search( $joinCond1['table1'], $tableAliases ); | |
| $index2 = array_search( $joinCond2['table1'], $tableAliases ); | |
| if ( $index1 == $index2 ) { return 0; | |
| } | |
| return $index1 < $index2 ? -1 : 1; | |
| } ); | |
| // Now validate, to make sure that all the tables | |
| // are "joined" together. There's probably some more | |
| // efficient network algorithm for this sort of thing, but | |
| // oh well. | |
| $numUnmatchedTables = count( $this->mAliasedTableNames ); | |
| $firstJoinCond = current( $this->mCargoJoinConds ); | |
| $firstTableInJoins = $firstJoinCond['table1']; | |
| $matchedTables = [ $firstTableInJoins ]; | |
| // We will check against aliases, not table names. | |
| $allPossibleTableAliases = []; | |
| foreach ( $this->mAliasedTableNames as $tableAlias => $tableName ) { | |
| $allPossibleTableAliases[] = $tableAlias; | |
| // This is useful for at least PostgreSQL. | |
| $allPossibleTableAliases[] = $this->mCargoDB->addIdentifierQuotes( $tableAlias ); | |
| } | |
| do { | |
| $previousNumUnmatchedTables = $numUnmatchedTables; | |
| foreach ( $this->mCargoJoinConds as $joinCond ) { | |
| $table1 = $joinCond['table1']; | |
| $table2 = $joinCond['table2']; | |
| if ( !in_array( $table1, $allPossibleTableAliases ) ) { | |
| throw new MWException( "Error: table \"$table1\" is not in list of table names or aliases." ); | |
| } | |
| if ( !in_array( $table2, $allPossibleTableAliases ) ) { | |
| throw new MWException( "Error: table \"$table2\" is not in list of table names or aliases." ); | |
| } | |
| if ( in_array( $table1, $matchedTables ) && !in_array( $table2, $matchedTables ) ) { | |
| $matchedTables[] = $table2; | |
| $numUnmatchedTables--; | |
| } | |
| if ( in_array( $table2, $matchedTables ) && !in_array( $table1, $matchedTables ) ) { | |
| $matchedTables[] = $table1; | |
| $numUnmatchedTables--; | |
| } | |
| } | |
| } while ( $numUnmatchedTables > 0 && $numUnmatchedTables > $previousNumUnmatchedTables ); | |
| if ( $numUnmatchedTables > 0 ) { | |
| foreach ( array_keys( $this->mAliasedTableNames ) as $tableAlias ) { | |
| $escapedTableAlias = $this->mCargoDB->addIdentifierQuotes( $tableAlias ); | |
| if ( !in_array( $tableAlias, $matchedTables ) && | |
| !in_array( $escapedTableAlias, $matchedTables ) ) { | |
| throw new MWException( "Error: Table \"$tableAlias\" is not included within the " | |
| . "join conditions." ); | |
| } | |
| } | |
| } | |
| } | |
| /** | |
| * Turn the very structured format that Cargo uses for join | |
| * conditions into the one that MediaWiki uses - this includes | |
| * adding the database prefix to each table name. | |
| */ | |
| private function setMWJoinConds() { | |
| if ( $this->mCargoJoinConds == null ) { | |
| return; | |
| } | |
| $this->mJoinConds = []; | |
| foreach ( $this->mCargoJoinConds as $cargoJoinCond ) { | |
| // Only add the DB prefix to the table names if | |
| // they're true table names and not aliases. | |
| $table1 = $cargoJoinCond['table1']; | |
| if ( !array_key_exists( $table1, $this->mAliasedTableNames ) || $this->mAliasedTableNames[$table1] == $table1 ) { | |
| $cargoTable1 = $this->mCargoDB->tableName( $table1 ); | |
| } else { | |
| $cargoTable1 = $this->mCargoDB->addIdentifierQuotes( $table1 ); | |
| } | |
| $table2 = $cargoJoinCond['table2']; | |
| if ( !array_key_exists( $table2, $this->mAliasedTableNames ) || $this->mAliasedTableNames[$table2] == $table2 ) { | |
| $cargoTable2 = $this->mCargoDB->tableName( $table2 ); | |
| } else { | |
| $cargoTable2 = $this->mCargoDB->addIdentifierQuotes( $table2 ); | |
| } | |
| if ( array_key_exists( 'joinOperator', $cargoJoinCond ) ) { | |
| $joinOperator = $cargoJoinCond['joinOperator']; | |
| } else { | |
| $joinOperator = '='; | |
| } | |
| $field1 = $this->mCargoDB->addIdentifierQuotes( $cargoJoinCond['field1'] ); | |
| $field2 = $this->mCargoDB->addIdentifierQuotes( $cargoJoinCond['field2'] ); | |
| $joinCondConds = [ | |
| $cargoTable1 . '.' . $field1 . $joinOperator . | |
| $cargoTable2 . '.' . $field2 | |
| ]; | |
| if ( array_key_exists( 'extraCond', $cargoJoinCond ) ) { | |
| $joinCondConds[] = $cargoJoinCond['extraCond']; | |
| } | |
| if ( !array_key_exists( $table2, $this->mJoinConds ) ) { | |
| $this->mJoinConds[$table2] = [ | |
| $cargoJoinCond['joinType'], | |
| $joinCondConds | |
| ]; | |
| } else { | |
| $this->mJoinConds[$table2][1] = array_merge( | |
| $this->mJoinConds[$table2][1], | |
| $joinCondConds | |
| ); | |
| } | |
| } | |
| } | |
| public function setOrderBy( $orderByStr = null ) { | |
| $this->mOrderBy = []; | |
| if ( $orderByStr != '' ) { | |
| $orderByElements = CargoUtils::smartSplit( ',', $orderByStr ); | |
| foreach ( $orderByElements as $elem ) { | |
| // Get rid of 'ASC' - it's never needed. | |
| if ( substr( $elem, -4 ) == ' ASC' ) { | |
| $elem = trim( substr( $elem, 0, strlen( $elem ) - 4 ) ); | |
| } | |
| // If it has "DESC" at the end, remove it, then | |
| // add it back in later. | |
| $hasDesc = ( substr( $elem, -5 ) == ' DESC' ); | |
| if ( $hasDesc ) { | |
| $elem = trim( substr( $elem, 0, strlen( $elem ) - 5 ) ); | |
| } | |
| if ( strpos( $elem, '(' ) === false && strpos( $elem, '.' ) === false && !$this->mCargoDB->isQuotedIdentifier( $elem ) ) { | |
| $elem = $this->mCargoDB->addIdentifierQuotes( $elem ); | |
| } | |
| if ( $hasDesc ) { | |
| $elem .= ' DESC'; | |
| } | |
| $this->mOrderBy[] = $elem; | |
| } | |
| } else { | |
| // By default, sort on up to the first five fields, in | |
| // the order in which they're defined. Five seems like | |
| // enough to make sure everything is in the right order, | |
| // no? Or should it always be all the fields? | |
| $fieldNum = 1; | |
| foreach ( $this->mAliasedFieldNames as $fieldName ) { | |
| if ( strpos( $fieldName, '(' ) === false && strpos( $fieldName, '.' ) === false ) { | |
| $this->mOrderBy[] = $this->mCargoDB->addIdentifierQuotes( $fieldName ); | |
| } else { | |
| $this->mOrderBy[] = $fieldName; | |
| } | |
| $fieldNum++; | |
| if ( $fieldNum > 5 ) { | |
| break; | |
| } | |
| } | |
| } | |
| } | |
| public function setGroupBy( $groupByStr ) { | |
| // @TODO - $mGroupByStr should turn into an array named | |
| // $mGroupBy for better handling of mulitple values, as was | |
| // done with $mOrderBy. | |
| $this->mOrigGroupByStr = $groupByStr; | |
| if ( $groupByStr == '' ) { | |
| $this->mGroupByStr = null; | |
| } elseif ( strpos( $groupByStr, '(' ) === false && strpos( $groupByStr, '.' ) === false && strpos( $groupByStr, ',' ) === false ) { | |
| $this->mGroupByStr = $this->mCargoDB->addIdentifierQuotes( $groupByStr ); | |
| } else { | |
| $this->mGroupByStr = $groupByStr; | |
| } | |
| } | |
| private static function getAndValidateSQLFunctions( $str ) { | |
| global $wgCargoAllowedSQLFunctions; | |
| $sqlFunctionMatches = []; | |
| $sqlFunctionRegex = '/(\b|\W)(\w*?)\s*\(/'; | |
| preg_match_all( $sqlFunctionRegex, $str, $sqlFunctionMatches ); | |
| $sqlFunctions = array_map( 'strtoupper', $sqlFunctionMatches[2] ); | |
| $sqlFunctions = array_map( 'trim', $sqlFunctions ); | |
| // Throw an error if any of these functions | |
| // are not in our "whitelist" of SQL functions. | |
| // Also add to this whitelist SQL operators like AND, OR, NOT, | |
| // etc., because the parsing can mistake these for functions. | |
| $logicalOperators = [ 'AND', 'OR', 'NOT', 'IN' ]; | |
| $allowedFunctions = array_merge( $wgCargoAllowedSQLFunctions, $logicalOperators ); | |
| foreach ( $sqlFunctions as $sqlFunction ) { | |
| // @TODO - fix the original regexp to avoid blank | |
| // strings, so that this check is not necessary. | |
| if ( $sqlFunction == '' ) { | |
| continue; | |
| } | |
| if ( !in_array( $sqlFunction, $allowedFunctions ) ) { | |
| throw new MWException( wfMessage( "cargo-query-badsqlfunction", "$sqlFunction()" )->parse() ); | |
| } | |
| } | |
| return $sqlFunctions; | |
| } | |
| /** | |
| * Attempts to get the "field description" (type, etc.), as well as the | |
| * table name, of a single field specified in a SELECT call (via a | |
| * #cargo_query call), using the set of schemas for all data tables. | |
| * | |
| * Also does some validation of table names, field names, and any SQL | |
| * functions contained in this clause. | |
| */ | |
| private function getDescriptionAndTableNameForField( $origFieldName ) { | |
| $tableName = null; | |
| $fieldName = null; | |
| $description = new CargoFieldDescription(); | |
| // We use "\p{L}0-9" instead of \w here in order to | |
| // handle accented and other non-ASCII characters in | |
| // table and field names. | |
| $fieldPattern = '/^([-_\p{L}0-9$]+)([.]([-_\p{L}0-9$]+))?$/u'; | |
| $fieldPatternFound = preg_match( $fieldPattern, $origFieldName, $fieldPatternMatches ); | |
| $stringPatternFound = false; | |
| $hasFunctionCall = false; | |
| if ( $fieldPatternFound ) { | |
| switch ( count( $fieldPatternMatches ) ) { | |
| case 2: | |
| $fieldName = $fieldPatternMatches[1]; | |
| break; | |
| case 4: | |
| $tableName = $fieldPatternMatches[1]; | |
| $fieldName = $fieldPatternMatches[3]; | |
| break; | |
| } | |
| } else { | |
| $stringPattern = '/^(([\'"]).*?\2)(.+)?$/'; | |
| $stringPatternFound = preg_match( $stringPattern, $origFieldName, $stringPatternMatches ); | |
| if ( $stringPatternFound ) { | |
| // If the count is 3 we have a single quoted string | |
| // If the count is 4 we have stuff after it | |
| $stringPatternFound = count( $stringPatternMatches ) == 3; | |
| } | |
| if ( !$stringPatternFound ) { | |
| $noQuotesOrigFieldName = CargoUtils::removeQuotedStrings( $origFieldName ); | |
| $functionCallPattern = '/\p{L}\s*\(/'; | |
| $hasFunctionCall = preg_match( $functionCallPattern, $noQuotesOrigFieldName ); | |
| } | |
| } | |
| // If it's a pre-defined field, we probably know its type. | |
| if ( $fieldName == '_ID' || $fieldName == '_rowID' || $fieldName == '_pageID' || $fieldName == '_pageNamespace' || $fieldName == '_position' ) { | |
| $description->mType = 'Integer'; | |
| } elseif ( $fieldName == '_pageTitle' ) { | |
| // It's a string - do nothing. | |
| } elseif ( $fieldName == '_pageName' ) { | |
| $description->mType = 'Page'; | |
| } elseif ( $stringPatternFound ) { | |
| // It's a quoted, literal string - do nothing. | |
| } elseif ( $hasFunctionCall ) { | |
| $sqlFunctions = self::getAndValidateSQLFunctions( $noQuotesOrigFieldName ); | |
| $firstFunction = $sqlFunctions[0]; | |
| // 'ROUND' is in neither the Integer nor Float | |
| // lists because it sometimes returns an | |
| // integer, sometimes a float - for formatting | |
| // purposes, we'll just treat it as a string. | |
| if ( in_array( $firstFunction, [ 'COUNT', 'FLOOR', 'CEIL' ] ) ) { | |
| $description->mType = 'Integer'; | |
| } elseif ( in_array( $firstFunction, [ 'SUM', 'POWER', 'LN', 'LOG' ] ) ) { | |
| $description->mType = 'Float'; | |
| } elseif ( in_array( $firstFunction, | |
| [ 'DATE', 'DATE_ADD', 'DATE_SUB', 'DATE_DIFF' ] ) ) { | |
| $description->mType = 'Date'; | |
| } elseif ( in_array( $firstFunction, [ 'TRIM' ] ) ) { | |
| // @HACK - allow users one string function | |
| // (TRIM()) that will return a String type, and | |
| // thus won't have its value parsed as wikitext. | |
| // Hopefully this won't cause problems for those | |
| // just wanting to call TRIM(). (In that case, | |
| // they can wrap the call in CONCAT().) | |
| $description->mType = 'String'; | |
| } elseif ( in_array( $firstFunction, [ 'MAX', 'MIN', 'AVG' ] ) ) { | |
| // These are special functions in that the type | |
| // of their output is not fixed, but rather | |
| // matches the type of their input. So we find | |
| // what's inside the function call and call | |
| // *this* function recursively on that. | |
| $startParenPos = strpos( $origFieldName, '(' ); | |
| $lastParenPos = strrpos( $origFieldName, ')' ); | |
| $innerFieldName = substr( $origFieldName, $startParenPos + 1, ( $lastParenPos - $startParenPos - 1 ) ); | |
| list( $innerDesc, $innerTableName ) = $this->getDescriptionAndTableNameForField( $innerFieldName ); | |
| if ( $firstFunction == 'AVG' && $innerDesc->mType == 'Integer' ) { | |
| // In practice, handling of AVG() is here | |
| // so that calling it on a Rating | |
| // field will keep it as Rating. | |
| $description->mType = 'Float'; | |
| } else { | |
| return [ $innerDesc, $innerTableName ]; | |
| } | |
| } | |
| // If it's anything else ('CONCAT', 'SUBSTRING', | |
| // etc. etc.), we don't have to do anything. | |
| } else { | |
| // It's a standard field - though if it's '_value', | |
| // or ends in '__full', it's actually the type of its | |
| // corresponding field. | |
| $useListTable = ( $fieldName == '_value' ); | |
| if ( $useListTable ) { | |
| if ( $tableName != null ) { | |
| if ( strpos( $tableName, '__' ) !== false ) { | |
| list( $tableName, $fieldName ) = explode( '__', $tableName, 2 ); | |
| } else { | |
| // Support directly operating on list table fields | |
| $fieldName = null; | |
| } | |
| } else { | |
| // We'll assume that there's exactly one | |
| // "field table" in the list of tables - | |
| // otherwise a standalone call to | |
| // "_value" will presumably crash the | |
| // SQL call. | |
| foreach ( $this->mAliasedTableNames as $curTable ) { | |
| if ( strpos( $curTable, '__' ) !== false ) { | |
| list( $tableName, $fieldName ) = explode( '__', $curTable ); | |
| break; | |
| } | |
| } | |
| } | |
| } elseif ( strlen( $fieldName ) > 6 && | |
| strpos( $fieldName, '__full', strlen( $fieldName ) - 6 ) !== false ) { | |
| $fieldName = substr( $fieldName, 0, strlen( $fieldName ) - 6 ); | |
| } | |
| if ( $tableName != null && !$useListTable ) { | |
| if ( !array_key_exists( $tableName, $this->mAliasedTableNames ) ) { | |
| throw new MWException( wfMessage( "cargo-query-badalias", $tableName )->parse() ); | |
| } | |
| $actualTableName = $this->mAliasedTableNames[$tableName]; | |
| if ( !array_key_exists( $actualTableName, $this->mTableSchemas ) ) { | |
| throw new MWException( wfMessage( "cargo-query-unknowndbtable", $actualTableName )->parse() ); | |
| } elseif ( !array_key_exists( $fieldName, $this->mTableSchemas[$actualTableName]->mFieldDescriptions ) ) { | |
| throw new MWException( wfMessage( "cargo-query-unknownfieldfortable", $fieldName, $actualTableName )->parse() ); | |
| } else { | |
| $description = $this->mTableSchemas[$actualTableName]->mFieldDescriptions[$fieldName]; | |
| } | |
| } elseif ( substr( $fieldName, -5 ) == '__lat' || substr( $fieldName, -5 ) == '__lon' ) { | |
| // Special handling for lat/lon helper fields. | |
| $description->mType = 'Coordinates part'; | |
| $tableName = ''; | |
| } elseif ( substr( $fieldName, -11 ) == '__precision' ) { | |
| // Special handling for lat/lon helper fields. | |
| // @TODO - we need validation on | |
| // __lat, __lon and __precision fields, | |
| // to make sure that they exist. | |
| $description->mType = 'Date precision'; | |
| $tableName = ''; | |
| } else { | |
| // Go through all the fields, until we find the | |
| // one matching this one. | |
| foreach ( $this->mTableSchemas as $curTableName => $tableSchema ) { | |
| if ( array_key_exists( $fieldName, $tableSchema->mFieldDescriptions ) ) { | |
| $description = $tableSchema->mFieldDescriptions[$fieldName]; | |
| foreach ( $this->mAliasedTableNames as $tableAlias => $tableName1 ) { | |
| if ( $tableName1 == $curTableName ) { | |
| $tableName = $tableAlias; | |
| break; | |
| } | |
| } | |
| break; | |
| } | |
| } | |
| // If we couldn't find a table name, throw an error. | |
| if ( $tableName == '' ) { | |
| // There's a good chance that | |
| // $fieldName is blank too. | |
| if ( $fieldName == '' ) { | |
| $fieldName = $origFieldName; | |
| } | |
| throw new MWException( wfMessage( "cargo-query-unknownfield", $fieldName )->parse() ); | |
| } | |
| } | |
| } | |
| return [ $description, $tableName ]; | |
| } | |
| /** | |
| * Attempts to get the "field description" (type, etc.), as well as | |
| * the table name, of each field specified in a SELECT call (via a | |
| * #cargo_query call), using the set of schemas for all data tables. | |
| */ | |
| public function setDescriptionsAndTableNamesForFields() { | |
| $this->mFieldDescriptions = []; | |
| $this->mFieldTables = []; | |
| foreach ( $this->mAliasedFieldNames as $alias => $origFieldName ) { | |
| list( $description, $tableName ) = $this->getDescriptionAndTableNameForField( $origFieldName ); | |
| // Fix alias. | |
| $alias = trim( $alias ); | |
| $this->mFieldDescriptions[$alias] = $description; | |
| $this->mFieldTables[$alias] = $tableName; | |
| } | |
| } | |
| public function addToCargoJoinConds( $newCargoJoinConds ) { | |
| foreach ( $newCargoJoinConds as $newCargoJoinCond ) { | |
| // Go through to make sure it's not there already. | |
| $foundMatch = false; | |
| foreach ( $this->mCargoJoinConds as $cargoJoinCond ) { | |
| if ( $cargoJoinCond['table1'] == $newCargoJoinCond['table1'] && | |
| $cargoJoinCond['field1'] == $newCargoJoinCond['field1'] && | |
| $cargoJoinCond['table2'] == $newCargoJoinCond['table2'] && | |
| $cargoJoinCond['field2'] == $newCargoJoinCond['field2'] ) { | |
| $foundMatch = true; | |
| continue; | |
| } | |
| } | |
| if ( !$foundMatch ) { | |
| $this->mCargoJoinConds[] = $newCargoJoinCond; | |
| } | |
| } | |
| } | |
| public function addFieldTableToTableNames( $fieldTableName, $fieldTableAlias, $tableAlias ) { | |
| // Add it in in the correct place, if it should be added at all. | |
| if ( array_key_exists( $fieldTableAlias, $this->mAliasedTableNames ) ) { | |
| return; | |
| } | |
| if ( !array_key_exists( $tableAlias, $this->mAliasedTableNames ) ) { | |
| // Show an error message here? | |
| return; | |
| } | |
| // array_splice() for an associative array - copied from | |
| // http://stackoverflow.com/a/1783125 | |
| $indexOfMainTable = array_search( $tableAlias, array_keys( $this->mAliasedTableNames ) ); | |
| $offset = $indexOfMainTable + 1; | |
| $this->mAliasedTableNames = array_slice( $this->mAliasedTableNames, 0, $offset, true ) + | |
| [ $fieldTableAlias => $fieldTableName ] + | |
| array_slice( $this->mAliasedTableNames, $offset, null, true ); | |
| } | |
| /** | |
| * Helper function for handleVirtualFields() - for the query's | |
| * "fields" and "order by" values, the right replacement for "virtual | |
| * fields" depends on whether the separate table for that field has | |
| * been included in the query. | |
| */ | |
| public function fieldTableIsIncluded( $fieldTableAlias ) { | |
| foreach ( $this->mCargoJoinConds as $cargoJoinCond ) { | |
| if ( $cargoJoinCond['table1'] == $fieldTableAlias || | |
| $cargoJoinCond['table2'] == $fieldTableAlias ) { | |
| return true; | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Provides HOLDS functionality to WHERE clause by replacing $pattern | |
| * in $subject with suitable subquery and setting $found to true if | |
| * successful (leaves it untouched otehrwise). Includes modifying | |
| * the regex beginning from a non-valid identifier character to word | |
| * boundary. | |
| */ | |
| public function substVirtualFieldName( &$subject, $rootPattern, $tableAlias, $notOperation, $fieldTableName, $compareOperator, &$found ) { | |
| $notOperator = $notOperation ? 'NOT' : ''; | |
| $patternMatch = []; | |
| // Match HOLDS syntax with values in single quotes | |
| if ( preg_match_all( $rootPattern . '\s*(\'.*?[^\\\\\']\')/i', $subject, $matches ) ) { | |
| $pattern = $rootPattern . '\s*(\'.*?[^\\\\\']\')/i'; | |
| $patternMatch[$pattern] = $matches; | |
| } | |
| // Match HOLDS syntax with values in double quotes | |
| if ( preg_match_all( $rootPattern . '\s*(\".*?[^\\\"]\")/i', $subject, $matches ) ) { | |
| $pattern = $rootPattern . '\s*(\".*?[^\\\"]\")/i'; | |
| $patternMatch[$pattern] = $matches; | |
| } | |
| // Match HOLDS syntax with fieldnames without quotes. | |
| // Fieldnames are expected to be single words without spaces. | |
| if ( preg_match_all( $rootPattern . '\s*([^\'"\s]+\s*)/i', $subject, $matches ) ) { | |
| $pattern = $rootPattern . '\s*([^\'"\s]*\s*)/i'; | |
| $patternMatch[$pattern] = $matches; | |
| } | |
| // If any match is found, replace it with a subquery. | |
| if ( !empty( $patternMatch ) ) { | |
| foreach ( $patternMatch as $pattern => $matches ) { | |
| $pattern = str_replace( '([^\w$,]|^)', '\b', $pattern ); | |
| $pattern = str_replace( '([^\w$.,]|^)', '\b', $pattern ); | |
| foreach ( $matches[2] as $match ) { | |
| // _ID need not be quoted here. | |
| // This being attached with a table name is handled | |
| // in the function addTablePrefixesToAll, like other fields. | |
| $replacement = | |
| $tableAlias . "._ID " . | |
| $notOperator . | |
| " IN (SELECT " . $this->mCargoDB->addIdentifierQuotes( "_rowID" ) . " FROM " . | |
| $this->mCargoDB->tableName( $fieldTableName ) . | |
| " WHERE " . $this->mCargoDB->addIdentifierQuotes( "_value" ) . | |
| $compareOperator . | |
| $match . | |
| ") "; | |
| $subject = preg_replace( $pattern, $replacement, $subject, $limit = 1 ); | |
| } | |
| } | |
| $found = true; | |
| } | |
| } | |
| private function handleVirtualFields() { | |
| // The array-field alias can be found in a number of different | |
| // clauses. Handling depends on which clause it is: | |
| // "where" - make sure that "HOLDS" or "HOLDS LIKE" is | |
| // specified. If it is, "translate" it into required subquery. | |
| // "join on" - make sure that "HOLDS" is specified, If it is, | |
| // "translate" it, and add the values table to "tables". | |
| // "group by" - always "translate" it into the single value. | |
| // "having" - same as "group by". | |
| // "fields" - "translate" it, where the translation (i.e. | |
| // the true field) depends on whether or not the values | |
| // table is included. | |
| // "order by" - same as "fields". | |
| // First, create an array of the virtual fields in the current | |
| // set of tables. | |
| $virtualFields = []; | |
| foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { | |
| foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { | |
| if ( !$fieldDescription->mIsList ) { | |
| continue; | |
| } | |
| foreach ( $this->mAliasedTableNames as $tableAlias => $tableName2 ) { | |
| if ( $tableName == $tableName2 ) { | |
| $virtualFields[] = [ | |
| 'fieldName' => $fieldName, | |
| 'tableAlias' => $tableAlias, | |
| 'tableName' => $tableName, | |
| 'fieldType' => $fieldDescription->mType, | |
| 'isHierarchy' => $fieldDescription->mIsHierarchy | |
| ]; | |
| } | |
| } | |
| } | |
| } | |
| // "where" | |
| $matches = []; | |
| $numHoldsExpressions = 0; | |
| foreach ( $virtualFields as $virtualField ) { | |
| $fieldName = $virtualField['fieldName']; | |
| $tableAlias = $virtualField['tableAlias']; | |
| $tableName = $virtualField['tableName']; | |
| $fieldType = $virtualField['fieldType']; | |
| $isHierarchy = $virtualField['isHierarchy']; | |
| $fieldTableName = $tableName . '__' . $fieldName; | |
| $fieldTableAlias = $tableAlias . '__' . $fieldName; | |
| $fieldReplaced = false; | |
| $throwException = false; | |
| $patternSimple = [ | |
| CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName ), | |
| CargoUtils::getSQLFieldPattern( $fieldName ) | |
| ]; | |
| $patternRoot = [ | |
| CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . '\s+', | |
| CargoUtils::getSQLFieldPattern( $fieldName, false ) . '\s+' | |
| ]; | |
| for ( $i = 0; $i < 2; $i++ ) { | |
| if ( preg_match( $patternSimple[$i], $this->mWhereStr ) ) { | |
| $this->substVirtualFieldName( | |
| $this->mWhereStr, | |
| $patternRoot[$i] . 'HOLDS\s+NOT\s+LIKE', | |
| $tableAlias, | |
| $notOperation = true, | |
| $fieldTableName, | |
| $compareOperation = "LIKE ", | |
| $fieldReplaced | |
| ); | |
| $this->substVirtualFieldName( | |
| $this->mWhereStr, | |
| $patternRoot[$i] . 'HOLDS\s+LIKE', | |
| $tableAlias, | |
| $notOperation = false, | |
| $fieldTableName, | |
| $compareOperation = "LIKE ", | |
| $fieldReplaced | |
| ); | |
| $this->substVirtualFieldName( | |
| $this->mWhereStr, | |
| $patternRoot[$i] . 'HOLDS\s+NOT', | |
| $tableAlias, | |
| $notOperation = true, | |
| $fieldTableName, | |
| $compareOperation = "= ", | |
| $fieldReplaced | |
| ); | |
| $this->substVirtualFieldName( | |
| $this->mWhereStr, | |
| $patternRoot[$i] . 'HOLDS', | |
| $tableAlias, | |
| $notOperation = false, | |
| $fieldTableName, | |
| $compareOperation = "= ", | |
| $fieldReplaced | |
| ); | |
| if ( preg_match( $patternSimple[$i], $this->mWhereStr ) ) { | |
| if ( $isHierarchy ) { | |
| throw new MWException( "Error: operator for the hierarchy field '" . | |
| "$tableName.$fieldName' must be 'HOLDS', 'HOLDS NOT', '" . | |
| "HOLDS WITHIN', 'HOLDS LIKE' or 'HOLDS NOT LIKE'." ); | |
| } else { | |
| throw new MWException( "Error: operator for the virtual field '" . | |
| "$tableName.$fieldName' must be 'HOLDS', 'HOLDS NOT', '" . | |
| "HOLDS LIKE' or 'HOLDS NOT LIKE'." ); | |
| } | |
| } | |
| } | |
| } | |
| // Always use the "field table" if it's a date field, | |
| // and it's being queried. | |
| $isFieldInQuery = in_array( $fieldName, $this->mAliasedFieldNames ) || | |
| in_array( "$tableAlias.$fieldName", $this->mAliasedFieldNames ); | |
| if ( $isFieldInQuery && ( $fieldType == 'Date' || $fieldType == 'Datetime' ) ) { | |
| $fieldReplaced = true; | |
| } | |
| } | |
| // "join on" | |
| $newCargoJoinConds = []; | |
| foreach ( $this->mCargoJoinConds as $i => $joinCond ) { | |
| // We only handle 'HOLDS' here - no joining on | |
| // 'HOLDS LIKE'. | |
| if ( !array_key_exists( 'joinOperator', $joinCond ) || $joinCond['joinOperator'] != ' HOLDS ' ) { | |
| continue; | |
| } | |
| foreach ( $virtualFields as $virtualField ) { | |
| $fieldName = $virtualField['fieldName']; | |
| $tableAlias = $virtualField['tableAlias']; | |
| $tableName = $virtualField['tableName']; | |
| if ( $fieldName != $joinCond['field1'] || $tableAlias != $joinCond['table1'] ) { | |
| continue; | |
| } | |
| $fieldTableName = $tableName . '__' . $fieldName; | |
| $fieldTableAlias = $tableAlias . '__' . $fieldName; | |
| $this->addFieldTableToTableNames( $fieldTableName, $fieldTableAlias, $tableAlias ); | |
| $newJoinCond = [ | |
| 'joinType' => 'LEFT OUTER JOIN', | |
| 'table1' => $tableAlias, | |
| 'field1' => '_ID', | |
| 'table2' => $fieldTableAlias, | |
| 'field2' => '_rowID' | |
| ]; | |
| $newCargoJoinConds[] = $newJoinCond; | |
| $newJoinCond2 = [ | |
| 'joinType' => 'RIGHT OUTER JOIN', | |
| 'table1' => $fieldTableAlias, | |
| 'field1' => '_value', | |
| 'table2' => $this->mCargoJoinConds[$i]['table2'], | |
| 'field2' => $this->mCargoJoinConds[$i]['field2'] | |
| ]; | |
| $newCargoJoinConds[] = $newJoinCond2; | |
| // Is it safe to unset an array value while | |
| // cycling through the array? Hopefully. | |
| unset( $this->mCargoJoinConds[$i] ); | |
| } | |
| } | |
| $this->addToCargoJoinConds( $newCargoJoinConds ); | |
| // "group by" and "having" | |
| // We handle these before "fields" and "order by" because, | |
| // unlike those two, a virtual field here can affect the | |
| // set of tables and fields being included - which will | |
| // affect the other two. | |
| $matches = []; | |
| foreach ( $virtualFields as $virtualField ) { | |
| $fieldName = $virtualField['fieldName']; | |
| $tableAlias = $virtualField['tableAlias']; | |
| $tableName = $virtualField['tableName']; | |
| $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableName, $fieldName ); | |
| $foundMatch1 = preg_match( $pattern1, $this->mGroupByStr, $matches ); | |
| $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName ); | |
| $foundMatch2 = false; | |
| if ( !$foundMatch1 ) { | |
| $foundMatch2 = preg_match( $pattern2, $this->mGroupByStr, $matches ); | |
| } | |
| if ( $foundMatch1 || $foundMatch2 ) { | |
| $fieldTableName = $tableName . '__' . $fieldName; | |
| $fieldTableAlias = $tableAlias . '__' . $fieldName; | |
| if ( !$this->fieldTableIsIncluded( $fieldTableAlias ) ) { | |
| $this->addFieldTableToTableNames( $fieldTableName, $fieldTableAlias, $tableAlias ); | |
| $this->mCargoJoinConds[] = [ | |
| 'joinType' => 'LEFT OUTER JOIN', | |
| 'table1' => $tableAlias, | |
| 'field1' => '_ID', | |
| 'table2' => $fieldTableAlias, | |
| 'field2' => '_rowID' | |
| ]; | |
| } | |
| $replacement = "$fieldTableAlias._value"; | |
| if ( $foundMatch1 ) { | |
| $this->mGroupByStr = preg_replace( $pattern1, $replacement, $this->mGroupByStr ); | |
| $this->mHavingStr = preg_replace( $pattern1, $replacement, $this->mHavingStr ); | |
| } elseif ( $foundMatch2 ) { | |
| $this->mGroupByStr = preg_replace( $pattern2, $replacement, $this->mGroupByStr ); | |
| $this->mHavingStr = preg_replace( $pattern2, $replacement, $this->mHavingStr ); | |
| } | |
| } | |
| } | |
| // "fields" | |
| foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { | |
| $fieldDescription = $this->mFieldDescriptions[$alias]; | |
| if ( strpos( $fieldName, '.' ) !== false ) { | |
| // This could probably be done better with | |
| // regexps. | |
| list( $tableAlias, $fieldName ) = explode( '.', $fieldName, 2 ); | |
| } else { | |
| $tableAlias = $this->mFieldTables[$alias]; | |
| } | |
| // We're only interested in virtual list fields. | |
| $isVirtualField = false; | |
| foreach ( $virtualFields as $virtualField ) { | |
| if ( $fieldName == $virtualField['fieldName'] && $tableAlias == $virtualField['tableAlias'] ) { | |
| $isVirtualField = true; | |
| break; | |
| } | |
| } | |
| if ( !$isVirtualField ) { | |
| continue; | |
| } | |
| // Since the field name is an alias, it should get | |
| // translated, to either the "full" equivalent or to | |
| // the "value" field in the field table - depending on | |
| // whether or not that field has been "joined" on. | |
| $fieldTableAlias = $tableAlias . '__' . $fieldName; | |
| if ( $this->fieldTableIsIncluded( $fieldTableAlias ) ) { | |
| $fieldName = $fieldTableAlias . '._value'; | |
| } else { | |
| $fieldName .= '__full'; | |
| } | |
| $this->mAliasedFieldNames[$alias] = $fieldName; | |
| } | |
| // "order by" | |
| $matches = []; | |
| foreach ( $virtualFields as $virtualField ) { | |
| $fieldName = $virtualField['fieldName']; | |
| $tableAlias = $virtualField['tableAlias']; | |
| $tableName = $virtualField['tableName']; | |
| $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName ); | |
| $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName ); | |
| $foundMatch1 = $foundMatch2 = false; | |
| foreach ( $this->mOrderBy as &$orderByElem ) { | |
| $foundMatch1 = preg_match( $pattern1, $orderByElem, $matches ); | |
| if ( !$foundMatch1 ) { | |
| $foundMatch2 = preg_match( $pattern2, $orderByElem, $matches ); | |
| } | |
| if ( !$foundMatch1 && !$foundMatch2 ) { | |
| continue; | |
| } | |
| $fieldTableAlias = $tableAlias . '__' . $fieldName; | |
| if ( $this->fieldTableIsIncluded( $fieldTableAlias ) ) { | |
| $replacement = "$fieldTableAlias._value"; | |
| } else { | |
| $replacement = $tableAlias . '.' . $fieldName . '__full '; | |
| } | |
| if ( isset( $matches[2] ) && ( $matches[2] == ',' ) ) { | |
| $replacement .= ','; | |
| } | |
| if ( $foundMatch1 ) { | |
| $orderByElem = preg_replace( $pattern1, $replacement, $orderByElem ); | |
| } else { // $foundMatch2 | |
| $orderByElem = preg_replace( $pattern2, $replacement, $orderByElem ); | |
| } | |
| } | |
| } | |
| } | |
| /** | |
| * Similar to handleVirtualFields(), but handles coordinates fields | |
| * instead of fields that hold lists. This handling is much simpler. | |
| */ | |
| private function handleVirtualCoordinateFields() { | |
| // Coordinate fields can be found in the "fields" and "where" | |
| // clauses. The following handling is done: | |
| // "fields" - "translate" it, where the translation (i.e. | |
| // the true field) depends on whether or not the values | |
| // table is included. | |
| // "where" - make sure that "NEAR" is specified. If it is, | |
| // translate the clause accordingly. | |
| // First, create an array of the coordinate fields in the | |
| // current set of tables. | |
| $coordinateFields = []; | |
| foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { | |
| foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { | |
| if ( $fieldDescription->mType == 'Coordinates' ) { | |
| foreach ( $this->mAliasedTableNames as $tableAlias => $tableName2 ) { | |
| if ( $tableName == $tableName2 ) { | |
| $coordinateFields[] = [ | |
| 'fieldName' => $fieldName, | |
| 'tableName' => $tableName, | |
| 'tableAlias' => $tableAlias, | |
| ]; | |
| break; | |
| } | |
| } | |
| } | |
| } | |
| } | |
| // "fields" | |
| foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { | |
| $fieldDescription = $this->mFieldDescriptions[$alias]; | |
| if ( strpos( $fieldName, '.' ) !== false ) { | |
| // This could probably be done better with | |
| // regexps. | |
| list( $tableAlias, $fieldName ) = explode( '.', $fieldName, 2 ); | |
| } else { | |
| $tableAlias = $this->mFieldTables[$alias]; | |
| } | |
| // We have to do this roundabout checking, instead | |
| // of just looking at the type of each field alias, | |
| // because we want to find only the *virtual* | |
| // coordinate fields. | |
| $isCoordinateField = false; | |
| foreach ( $coordinateFields as $coordinateField ) { | |
| if ( $fieldName == $coordinateField['fieldName'] && | |
| $tableAlias == $coordinateField['tableAlias'] ) { | |
| $isCoordinateField = true; | |
| break; | |
| } | |
| } | |
| if ( !$isCoordinateField ) { | |
| continue; | |
| } | |
| // Since the field name is an alias, it should get | |
| // translated to its "full" equivalent. | |
| $fullFieldName = $fieldName . '__full'; | |
| $this->mAliasedFieldNames[$alias] = $fullFieldName; | |
| // Add in the 'lat' and 'lon' fields as well - we'll | |
| // need them, if a map is being displayed. | |
| $this->mAliasedFieldNames[$fieldName . ' lat'] = $fieldName . '__lat'; | |
| $this->mAliasedFieldNames[$fieldName . ' lon'] = $fieldName . '__lon'; | |
| } | |
| // "where" | |
| // @TODO - add handling for "HOLDS POINT NEAR" | |
| $matches = []; | |
| foreach ( $coordinateFields as $coordinateField ) { | |
| $fieldName = $coordinateField['fieldName']; | |
| $tableAlias = $coordinateField['tableAlias']; | |
| $patternSuffix = '(\s+NEAR\s*)\(([^)]*)\)/i'; | |
| $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . $patternSuffix; | |
| $foundMatch1 = preg_match( $pattern1, $this->mWhereStr, $matches ); | |
| if ( !$foundMatch1 ) { | |
| $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName, false ) . $patternSuffix; | |
| $foundMatch2 = preg_match( $pattern2, $this->mWhereStr, $matches ); | |
| } | |
| if ( $foundMatch1 || $foundMatch2 ) { | |
| // If no "NEAR", throw an error. | |
| if ( count( $matches ) != 4 ) { | |
| throw new MWException( "Error: operator for the virtual coordinates field " | |
| . "'$tableAlias.$fieldName' must be 'NEAR'." ); | |
| } | |
| $coordinatesAndDistance = explode( ',', $matches[3] ); | |
| if ( count( $coordinatesAndDistance ) != 3 ) { | |
| throw new MWException( "Error: value for the 'NEAR' operator must be of the form " | |
| . "\"(latitude, longitude, distance)\"." ); | |
| } | |
| list( $latitude, $longitude, $distance ) = $coordinatesAndDistance; | |
| $distanceComponents = explode( ' ', trim( $distance ) ); | |
| if ( count( $distanceComponents ) != 2 ) { | |
| throw new MWException( "Error: the third argument for the 'NEAR' operator, " | |
| . "representing the distance, must be of the form \"number unit\"." ); | |
| } | |
| list( $distanceNumber, $distanceUnit ) = $distanceComponents; | |
| $distanceNumber = trim( $distanceNumber ); | |
| $distanceUnit = trim( $distanceUnit ); | |
| list( $latDistance, $longDistance ) = self::distanceToDegrees( $distanceNumber, $distanceUnit, | |
| $latitude ); | |
| // There are much better ways to do this, but | |
| // for now, just make a "bounding box" instead | |
| // of a bounding circle. | |
| $newWhere = " $tableAlias.{$fieldName}__lat >= " . max( $latitude - $latDistance, -90 ) . | |
| " AND $tableAlias.{$fieldName}__lat <= " . min( $latitude + $latDistance, 90 ) . | |
| " AND $tableAlias.{$fieldName}__lon >= " . max( $longitude - $longDistance, -180 ) . | |
| " AND $tableAlias.{$fieldName}__lon <= " . min( $longitude + $longDistance, 180 ) . ' '; | |
| if ( $foundMatch1 ) { | |
| $this->mWhereStr = preg_replace( $pattern1, $newWhere, $this->mWhereStr ); | |
| } elseif ( $foundMatch2 ) { | |
| $this->mWhereStr = preg_replace( $pattern2, $newWhere, $this->mWhereStr ); | |
| } | |
| } | |
| } | |
| // "order by" | |
| // This one is simpler than the others - just add a "__full" | |
| // to each coordinates field in the "order by" clause. | |
| $matches = []; | |
| foreach ( $coordinateFields as $coordinateField ) { | |
| $fieldName = $coordinateField['fieldName']; | |
| $tableAlias = $coordinateField['tableAlias']; | |
| $pattern1 = CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, true ); | |
| $pattern2 = CargoUtils::getSQLFieldPattern( $fieldName, true ); | |
| foreach ( $this->mOrderBy as &$orderByElem ) { | |
| $orderByElem = preg_replace( $pattern1, '$1' . "$tableAlias.$fieldName" . '__full$2', $orderByElem ); | |
| $orderByElem = preg_replace( $pattern2, '$1' . $fieldName . '__full$2', $orderByElem ); | |
| } | |
| } | |
| } | |
| /** | |
| * Handles Hierarchy fields' "WHERE" operations | |
| */ | |
| private function handleHierarchyFields() { | |
| // "where" - make sure that if | |
| // "WITHIN" (if not list) or "HOLDS WITHIN" (if list) | |
| // is specified, then translate the clause accordingly. | |
| // other translations in case of List fields, | |
| // are handled by handleVirtualFields(). | |
| // First, create an array of the hierarchy fields in the | |
| // current set of tables. | |
| $hierarchyFields = []; | |
| foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { | |
| foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { | |
| if ( !$fieldDescription->mIsHierarchy ) { | |
| continue; | |
| } | |
| foreach ( $this->mAliasedTableNames as $tableAlias => $tableName2 ) { | |
| if ( $tableName == $tableName2 ) { | |
| $hierarchyFields[] = [ | |
| 'fieldName' => $fieldName, | |
| 'tableAlias' => $tableAlias, | |
| 'tableName' => $tableName, | |
| 'isList' => $fieldDescription->mIsList | |
| ]; | |
| } | |
| } | |
| } | |
| } | |
| // "where" | |
| foreach ( $hierarchyFields as $hierarchyField ) { | |
| $fieldName = $hierarchyField['fieldName']; | |
| $tableName = $hierarchyField['tableName']; | |
| $tableAlias = $hierarchyField['tableAlias']; | |
| $fieldIsList = $hierarchyField['isList']; | |
| $patternSimple = [ | |
| CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName ), | |
| CargoUtils::getSQLFieldPattern( $fieldName ) | |
| ]; | |
| $patternRootArray = [ | |
| CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ), | |
| CargoUtils::getSQLFieldPattern( $fieldName, false ) | |
| ]; | |
| $simpleMatch = false; | |
| $completeMatch = false; | |
| $patternRoot = ""; | |
| if ( preg_match( $patternSimple[0], $this->mWhereStr ) ) { | |
| $simpleMatch = true; | |
| $patternRoot = $patternRootArray[0]; | |
| } elseif ( preg_match( $patternSimple[1], $this->mWhereStr ) ) { | |
| $simpleMatch = true; | |
| $patternRoot = $patternRootArray[1]; | |
| } | |
| // else we don't have current field in WHERE clause | |
| if ( !$simpleMatch ) { | |
| continue; | |
| } | |
| $patternSuffix = '([\'"]?[^\'"]*[\'"]?)/i'; // To capture string in quotes or a number | |
| $hierarchyTable = $this->mCargoDB->tableName( $tableName . '__' . $fieldName . '__hierarchy' ); | |
| $fieldTableName = $this->mCargoDB->tableName( $tableName . '__' . $fieldName ); | |
| $completeSearchPattern = ""; | |
| $matches = []; | |
| $newWhere = ""; | |
| $leftFieldName = $this->mCargoDB->addIdentifierQuotes( "_left" ); | |
| $rightFieldName = $this->mCargoDB->addIdentifierQuotes( "_right" ); | |
| $valueFieldName = $this->mCargoDB->addIdentifierQuotes( "_value" ); | |
| if ( preg_match( $patternRoot . '(\s+HOLDS WITHIN\s+)' . $patternSuffix, $this->mWhereStr, $matches ) ) { | |
| if ( !$fieldIsList ) { | |
| throw new MWException( "Error: \"HOLDS WITHIN\" cannot be used for single hierarchy field, use \"WITHIN\" instead." ); | |
| } | |
| $completeMatch = true; | |
| $completeSearchPattern = $patternRoot . '(\s+HOLDS WITHIN\s+)' . $patternSuffix; | |
| if ( count( $matches ) != 4 || $matches[3] === "" ) { | |
| throw new MWException( "Error: Please specify a value for \"HOLDS WITHIN\"" ); | |
| } | |
| $withinValue = $matches[3]; | |
| $subquery = "( SELECT $valueFieldName FROM $hierarchyTable WHERE " . | |
| "$leftFieldName >= ( SELECT $leftFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) AND " . | |
| "$rightFieldName <= ( SELECT $rightFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) " . | |
| ")"; | |
| $subquery = "( SELECT DISTINCT( " . $this->mCargoDB->addIdentifierQuotes( "_rowID" ) . " ) FROM $fieldTableName WHERE $valueFieldName IN " . $subquery . " )"; | |
| $newWhere = " " . $tableName . "._ID" . " IN " . $subquery; | |
| } | |
| if ( preg_match( $patternRoot . '(\s+WITHIN\s+)' . $patternSuffix, $this->mWhereStr, $matches ) ) { | |
| if ( $fieldIsList ) { | |
| throw new MWException( "Error: \"WITHIN\" cannot be used for list hierarchy field, use \"HOLDS WITHIN\" instead." ); | |
| } | |
| $completeMatch = true; | |
| $completeSearchPattern = $patternRoot . '(\s+WITHIN\s+)' . $patternSuffix; | |
| if ( count( $matches ) != 4 || $matches[3] === "" ) { | |
| throw new MWException( "Error: Please specify a value for \"WITHIN\"" ); | |
| } | |
| $withinValue = $matches[3]; | |
| $subquery = "( SELECT $valueFieldName FROM $hierarchyTable WHERE " . | |
| "$leftFieldName >= ( SELECT $leftFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) AND " . | |
| "$rightFieldName <= ( SELECT $rightFieldName FROM $hierarchyTable WHERE $valueFieldName = $withinValue ) " . | |
| ")"; | |
| $newWhere = " " . $fieldName . " IN " . $subquery; | |
| } | |
| if ( $completeMatch ) { | |
| $this->mWhereStr = preg_replace( $completeSearchPattern, $newWhere, $this->mWhereStr ); | |
| } | |
| // In case fieldIsList === true, there is a possibility of more Query commands. | |
| // like "HOLDS" and "HOLDS LIKE", that is handled by handleVirtualFields() | |
| } | |
| } | |
| /** | |
| * Returns the number of degrees of both latitude and longitude that | |
| * correspond to the passed-in distance (in either kilometers or | |
| * miles), based on the passed-in latitude. (Longitude doesn't matter | |
| * when doing this conversion, but latitude does.) | |
| */ | |
| private static function distanceToDegrees( $distanceNumber, $distanceUnit, $latString ) { | |
| if ( in_array( $distanceUnit, [ 'kilometers', 'kilometres', 'km' ] ) ) { | |
| $distanceInKM = $distanceNumber; | |
| } elseif ( in_array( $distanceUnit, [ 'miles', 'mi' ] ) ) { | |
| $distanceInKM = $distanceNumber * 1.60934; | |
| } else { | |
| throw new MWException( "Error: distance for 'NEAR' operator must be in either miles or " | |
| . "kilometers (\"$distanceUnit\" specified)." ); | |
| } | |
| // The calculation of distance to degrees latitude is | |
| // essentially the same wherever you are on the globe, although | |
| // the longitude calculation is more complicated. | |
| $latDistance = $distanceInKM / 111; | |
| // Convert the latitude string to a latitude number - code is | |
| // copied from CargoUtils::parseCoordinatesString(). | |
| $latIsNegative = false; | |
| if ( strpos( $latString, 'S' ) > 0 ) { | |
| $latIsNegative = true; | |
| } | |
| $latString = str_replace( [ 'N', 'S' ], '', $latString ); | |
| if ( is_numeric( $latString ) ) { | |
| $latNum = floatval( $latString ); | |
| } else { | |
| $latNum = CargoUtils::coordinatePartToNumber( $latString ); | |
| } | |
| if ( $latIsNegative ) { | |
| $latNum *= -1; | |
| } | |
| $lengthOfOneDegreeLongitude = cos( deg2rad( $latNum ) ) * 111.321; | |
| $longDistance = $distanceInKM / $lengthOfOneDegreeLongitude; | |
| return [ $latDistance, $longDistance ]; | |
| } | |
| /** | |
| * For each date field, also add its corresponding "precisicon" | |
| * field (which indicates whether the date is year-only, etc.) to | |
| * the query. | |
| */ | |
| public function handleDateFields() { | |
| $dateFields = []; | |
| foreach ( $this->mOrigAliasedFieldNames as $alias => $fieldName ) { | |
| if ( !array_key_exists( $alias, $this->mFieldDescriptions ) ) { | |
| continue; | |
| } | |
| $fieldDescription = $this->mFieldDescriptions[$alias]; | |
| if ( ( $fieldDescription->mType == 'Date' || $fieldDescription->mType == 'Datetime' || | |
| $fieldDescription->mType == 'Start date' || $fieldDescription->mType == 'Start datetime' || | |
| $fieldDescription->mType == 'End date' || $fieldDescription->mType == 'End datetime' ) && | |
| // Make sure this is an actual field and not a call | |
| // to a function, like DATE_FORMAT(), by checking for | |
| // the presence of '(' and ')' - there's probably a | |
| // more elegant way to do this. | |
| ( strpos( $fieldName, '(' ) == false ) && ( strpos( $fieldName, ')' ) == false ) ) { | |
| $dateFields[$alias] = $fieldName; | |
| } | |
| } | |
| foreach ( $dateFields as $alias => $dateField ) { | |
| // Handle fields that are a list of dates. | |
| if ( substr( $dateField, -6 ) == '__full' ) { | |
| $dateField = substr( $dateField, 0, -6 ); | |
| } | |
| $precisionFieldName = $dateField . '__precision'; | |
| $precisionFieldAlias = $alias . '__precision'; | |
| $this->mAliasedFieldNames[$precisionFieldAlias] = $precisionFieldName; | |
| } | |
| } | |
| private function handleSearchTextFields() { | |
| $searchTextFields = []; | |
| foreach ( $this->mTableSchemas as $tableName => $tableSchema ) { | |
| foreach ( $tableSchema->mFieldDescriptions as $fieldName => $fieldDescription ) { | |
| if ( $fieldDescription->mType != 'Searchtext' ) { | |
| continue; | |
| } | |
| $fieldAlias = array_search( $fieldName, $this->mAliasedFieldNames ); | |
| if ( $fieldAlias === false ) { | |
| $tableAlias = array_search( $tableName, $this->mAliasedTableNames ); | |
| $fieldAlias = array_search( "$tableAlias.$fieldName", $this->mAliasedFieldNames ); | |
| } | |
| if ( $fieldAlias === false ) { | |
| $fieldAlias = $fieldName; | |
| } | |
| $searchTextFields[] = [ | |
| 'fieldName' => $fieldName, | |
| 'fieldAlias' => $fieldAlias, | |
| 'tableName' => $tableName | |
| ]; | |
| } | |
| } | |
| $matches = []; | |
| foreach ( $searchTextFields as $searchTextField ) { | |
| $fieldName = $searchTextField['fieldName']; | |
| $fieldAlias = $searchTextField['fieldAlias']; | |
| $tableName = $searchTextField['tableName']; | |
| $tableAlias = array_search( $tableName, $this->mAliasedTableNames ); | |
| $patternSuffix = '(\s+MATCHES\s*)([\'"][^\'"]*[\'"])/i'; | |
| $patternSuffix1 = '(\s+MATCHES\s*)(\'[^\']*\')/i'; | |
| $patternSuffix2 = '(\s+MATCHES\s*)("[^"]*")/i'; | |
| $patterns = [ | |
| CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . $patternSuffix1, | |
| CargoUtils::getSQLFieldPattern( $fieldName, false ) . $patternSuffix1, | |
| CargoUtils::getSQLTableAndFieldPattern( $tableAlias, $fieldName, false ) . $patternSuffix2, | |
| CargoUtils::getSQLFieldPattern( $fieldName, false ) . $patternSuffix2 | |
| ]; | |
| $matchingPattern = null; | |
| foreach ( $patterns as $i => $pattern ) { | |
| $foundMatch = preg_match( $pattern, $this->mWhereStr, $matches ); | |
| if ( $foundMatch ) { | |
| $matchingPattern = $i; | |
| break; | |
| } | |
| } | |
| if ( $foundMatch ) { | |
| $searchString = $matches[3]; | |
| $newWhere = " MATCH($tableAlias.$fieldName) AGAINST ($searchString IN BOOLEAN MODE) "; | |
| $pattern = $patterns[$matchingPattern]; | |
| $this->mWhereStr = preg_replace( $pattern, $newWhere, $this->mWhereStr ); | |
| $searchEngine = new CargoSearchMySQL(); | |
| $searchTerms = $searchEngine->getSearchTerms( $searchString ); | |
| // @TODO - does $tableName need to be in there? | |
| $this->mSearchTerms[$fieldAlias] = $searchTerms; | |
| } | |
| } | |
| } | |
| /** | |
| * Adds the "cargo" table prefix for every element in the SQL query | |
| * except for 'tables' and 'join on' - for 'tables', the prefix is | |
| * prepended automatically by the MediaWiki query, while for | |
| * 'join on' the prefixes are added when the object is created. | |
| */ | |
| private function addTablePrefixesToAll() { | |
| foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { | |
| $this->mAliasedFieldNames[$alias] = $this->addTablePrefixes( $fieldName ); | |
| } | |
| if ( $this->mWhereStr !== null ) { | |
| $this->mWhereStr = $this->addTablePrefixes( $this->mWhereStr ); | |
| } | |
| $this->mGroupByStr = $this->addTablePrefixes( $this->mGroupByStr ); | |
| $this->mHavingStr = $this->addTablePrefixes( $this->mHavingStr ); | |
| foreach ( $this->mOrderBy as &$orderByElem ) { | |
| $orderByElem = $this->addTablePrefixes( $orderByElem ); | |
| } | |
| } | |
| /** | |
| * Calls a database SELECT query given the parts of the query; first | |
| * appending the Cargo prefix onto table names where necessary. | |
| */ | |
| public function run() { | |
| foreach ( $this->mAliasedTableNames as $tableName ) { | |
| if ( !$this->mCargoDB->tableExists( $tableName ) ) { | |
| throw new MWException( "Error: No database table exists named \"$tableName\"." ); | |
| } | |
| } | |
| $selectOptions = []; | |
| if ( $this->mGroupByStr != '' ) { | |
| $selectOptions['GROUP BY'] = $this->mGroupByStr; | |
| } | |
| if ( $this->mHavingStr != '' ) { | |
| $selectOptions['HAVING'] = $this->mHavingStr; | |
| } | |
| $selectOptions['ORDER BY'] = $this->mOrderBy; | |
| $selectOptions['LIMIT'] = $this->mQueryLimit; | |
| $selectOptions['OFFSET'] = $this->mOffset; | |
| // Aliases need to be surrounded by quotes when we actually | |
| // call the DB query. | |
| $realAliasedFieldNames = []; | |
| foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { | |
| // If it's either a field, or a table + field, | |
| // add quotes around the name(s). | |
| if ( strpos( $fieldName, '(' ) === false ) { | |
| if ( strpos( $fieldName, '.' ) === false ) { | |
| if ( !$this->mCargoDB->isQuotedIdentifier( $fieldName ) && !CargoUtils::isSQLStringLiteral( $fieldName ) ) { | |
| $fieldName = $this->mCargoDB->addIdentifierQuotes( $fieldName ); | |
| } | |
| } else { | |
| list( $realTableName, $realFieldName ) = explode( '.', $fieldName, 2 ); | |
| if ( !$this->mCargoDB->isQuotedIdentifier( $realTableName ) && !CargoUtils::isSQLStringLiteral( $realTableName ) ) { | |
| $realTableName = $this->mCargoDB->addIdentifierQuotes( $realTableName ); | |
| } | |
| if ( !$this->mCargoDB->isQuotedIdentifier( $realFieldName ) && !CargoUtils::isSQLStringLiteral( $realFieldName ) ) { | |
| $realFieldName = $this->mCargoDB->addIdentifierQuotes( $realFieldName ); | |
| } | |
| $fieldName = "$realTableName.$realFieldName"; | |
| } | |
| } | |
| $realAliasedFieldNames[$alias] = $fieldName; | |
| } | |
| $res = $this->mCargoDB->select( $this->mAliasedTableNames, $realAliasedFieldNames, $this->mWhereStr, __METHOD__, | |
| $selectOptions, $this->mJoinConds ); | |
| // Is there a more straightforward way of turning query | |
| // results into an array? | |
| $resultArray = []; | |
| foreach ( $res as $row ) { | |
| $resultsRow = []; | |
| foreach ( $this->mAliasedFieldNames as $alias => $fieldName ) { | |
| if ( !isset( $row->$alias ) ) { | |
| $resultsRow[$alias] = null; | |
| continue; | |
| } | |
| $curValue = $row->$alias; | |
| if ( $curValue instanceof DateTime ) { | |
| // @TODO - This code may no longer be necessary. | |
| $resultsRow[$alias] = $curValue->format( DateTime::W3C ); | |
| } else { | |
| // It's a string. | |
| // Escape any HTML, to avoid JavaScript | |
| // injections and the like. | |
| $resultsRow[$alias] = htmlspecialchars( $curValue ); | |
| } | |
| } | |
| $resultArray[] = $resultsRow; | |
| } | |
| return $resultArray; | |
| } | |
| private function addTablePrefixes( $string ) { | |
| // Create arrays for doing replacements of table names within | |
| // the SQL by their "real" equivalents. | |
| $tableNamePatterns = []; | |
| foreach ( $this->mAliasedTableNames as $alias => $tableName ) { | |
| $tableNamePatterns[] = CargoUtils::getSQLTablePattern( $tableName ); | |
| $tableNamePatterns[] = CargoUtils::getSQLTablePattern( $alias ); | |
| } | |
| return preg_replace_callback( $tableNamePatterns, | |
| [ $this, 'addQuotes' ], $string ); | |
| } | |
| private function addQuotes( $matches ) { | |
| $beforeText = $matches[1]; | |
| $tableName = $matches[2]; | |
| $fieldName = $matches[3]; | |
| $isTableAlias = false; | |
| if ( array_key_exists( $tableName, $this->mAliasedTableNames ) ) { | |
| if ( !in_array( $tableName, $this->mAliasedTableNames ) ) { | |
| $isTableAlias = true; | |
| } | |
| } | |
| if ( $isTableAlias ) { | |
| return $beforeText . $this->mCargoDB->addIdentifierQuotes( $tableName ) . "." . | |
| $this->mCargoDB->addIdentifierQuotes( $fieldName ); | |
| } else { | |
| return $beforeText . $this->mCargoDB->tableName( $tableName ) . "." . | |
| $this->mCargoDB->addIdentifierQuotes( $fieldName ); | |
| } | |
| } | |
| /** | |
| * Figure out which fields, if any, in this query are supposed to | |
| * represent start and end dates, based on a combination of field types, | |
| * order (start is expected to be listed before end) and alias. | |
| * @todo - this logic currently allows for any number of start/end | |
| * date pairs, but that may be overly complicated - it may be safe to | |
| * assume that any query contains no more than one start date and end | |
| * date, and any other dates can just be ignored, i.e. treated as | |
| * display fields. | |
| */ | |
| public function determineDateFields() { | |
| foreach ( $this->mFieldDescriptions as $alias => $description ) { | |
| if ( isset( $this->mAliasedFieldNames[$alias] ) ) { | |
| $realFieldName = $this->mAliasedFieldNames[$alias]; | |
| } else { | |
| $realFieldName = $alias; | |
| } | |
| $curNameAndAlias = [ $realFieldName, $alias ]; | |
| if ( $alias == 'start' || $description->mType == 'Start date' || $description->mType == 'Start datetime' ) { | |
| $foundMatch = false; | |
| foreach ( $this->mDateFieldPairs as $i => &$datePair ) { | |
| if ( array_key_exists( 'end', $datePair ) && !array_key_exists( 'start', $datePair ) ) { | |
| $datePair['start'] = $curNameAndAlias; | |
| $foundMatch = true; | |
| break; | |
| } | |
| } | |
| if ( !$foundMatch ) { | |
| $this->mDateFieldPairs[] = [ 'start' => $curNameAndAlias ]; | |
| } | |
| } elseif ( $alias == 'end' || $description->mType == 'End date' || $description->mType == 'End datetime' ) { | |
| $foundMatch = false; | |
| foreach ( $this->mDateFieldPairs as $i => &$datePair ) { | |
| if ( array_key_exists( 'start', $datePair ) && !array_key_exists( 'end', $datePair ) ) { | |
| $datePair['end'] = $curNameAndAlias; | |
| $foundMatch = true; | |
| break; | |
| } | |
| } | |
| if ( !$foundMatch ) { | |
| $this->mDateFieldPairs[] = [ 'end' => $curNameAndAlias ]; | |
| } | |
| } elseif ( $description->mType == 'Date' || $description->mType == 'Datetime' ) { | |
| $foundMatch = false; | |
| foreach ( $this->mDateFieldPairs as $i => &$datePair ) { | |
| if ( array_key_exists( 'end', $datePair ) && !array_key_exists( 'start', $datePair ) ) { | |
| $datePair['start'] = $curNameAndAlias; | |
| $foundMatch = true; | |
| break; | |
| } elseif ( array_key_exists( 'start', $datePair ) && !array_key_exists( 'end', $datePair ) ) { | |
| $datePair['end'] = $curNameAndAlias; | |
| $foundMatch = true; | |
| break; | |
| } | |
| } | |
| if ( !$foundMatch ) { | |
| $this->mDateFieldPairs[] = [ 'start' => $curNameAndAlias ]; | |
| } | |
| } | |
| } | |
| // Error-checking. | |
| if ( count( $this->mDateFieldPairs ) == 0 ) { | |
| throw new MWException( "Error: No date fields were found in this query." ); | |
| } | |
| foreach ( $this->mDateFieldPairs as $datePair ) { | |
| if ( !array_key_exists( 'start', $datePair ) ) { | |
| throw new MWException( "Error: No corresponding start date field was found for the end date field {$datePair['end'][0]}." ); | |
| } | |
| } | |
| } | |
| public function getMainStartAndEndDateFields() { | |
| if ( count( $this->mDateFieldPairs ) == 0 ) { | |
| $this->determineDateFields(); | |
| } | |
| $firstFieldPair = $this->mDateFieldPairs[0]; | |
| $startDateField = $firstFieldPair['start'][1]; | |
| $endDateField = ( array_key_exists( 'end', $firstFieldPair ) ) ? $firstFieldPair['end'][1] : null; | |
| return [ $startDateField, $endDateField ]; | |
| } | |
| } |