/** * This test was needed because of the wierd type casting issues with 0 and 1 and '1' and '0' as keys in an array. * '0' and '1' turn into integers which they shouldn't and this messes up the oneOf sql query builder. Additionally * on some versions of MySQL, 0,1 in a NOT IN, will evaluate true to 'abc' which it shouldn't. As a result * the 0/1 boolean values have been removed from the BooleanSanitizerUtil::getAcceptableValues(). */ public function testBooleanAcceptableValuesMappingAndSqlOneOfString() { $string = SQLOperatorUtil::resolveOperatorAndValueForOneOf('oneOf', BooleanSanitizerUtil::getAcceptableValues()); $compareString = "IN('false','true','y','n','yes','no','0','1','')"; // Not Coding Standard $this->assertEquals($compareString, $string); }
public function validateDynamicStructure($attribute, $params) { if (count($this->dynamicClauses) > 0) { if (null != ($errorMessage = SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage($this->{$attribute}, count($this->dynamicClauses)))) { $this->addError('dynamicStructure', $errorMessage); } } }
/** * @see DataAnalyzerInterface::runAndMakeMessages() */ public function runAndMakeMessages(AnalyzerSupportedDataProvider $dataProvider, $columnName) { $acceptableValues = BooleanSanitizerUtil::getAcceptableValues(); $inPart = SQLOperatorUtil::resolveOperatorAndValueForOneOf('oneOf', $acceptableValues); $where = DatabaseCompatibilityUtil::lower($columnName) . ' NOT ' . $inPart; $count = $dataProvider->getCountByWhere($where); if ($count > 0) { $label = '{count} value(s) have invalid check box values. '; $label .= 'These values will be set to false upon import.'; $this->addMessage(Zurmo::t('ImportModule', $label, array('{count}' => $count))); } }
protected function addReadOptimizationWhereClause(&$where, $whereKey, $tableAliasName) { assert('is_array($where)'); assert('is_int($whereKey)'); assert('is_string($tableAliasName)'); $q = DatabaseCompatibilityUtil::getQuote(); $columnWithTableAlias = self::makeColumnNameWithTableAlias($tableAliasName, $this->modelAttributeToDataProviderAdapter->getColumnName()); $mungeTableName = ReadPermissionsOptimizationUtil::getMungeTableName($this->modelAttributeToDataProviderAdapter->getModelClassName()); $mungeIds = ReadPermissionsOptimizationUtil::getMungeIdsByUser(Yii::app()->user->userModel); $whereContent = $columnWithTableAlias . " " . SQLOperatorUtil::getOperatorByType('equals') . " "; $whereContent .= "(select securableitem_id from {$q}{$mungeTableName}{$q} " . "where {$q}securableitem_id{$q} = {$columnWithTableAlias} and {$q}munge_id{$q}" . " in ('" . join("', '", $mungeIds) . "') limit 1)"; $where[$whereKey] = $whereContent; }
public function validateDynamicStructure($attribute, $params) { if (count($this->dynamicClauses) > 0) { if (null != ($errorMessage = SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage($this->{$attribute}, count($this->dynamicClauses)))) { $this->addError('dynamicStructure', $errorMessage); } else { $formula = strtolower($this->dynamicStructure); $formula = str_replace("(", "", $formula); $formula = str_replace(")", "", $formula); $arguments = preg_split("/or|and/", $formula); foreach ($arguments as $argument) { $argument = trim($argument); if (!is_numeric($argument) || !(intval($argument) <= count($this->dynamicClauses)) || !(intval($argument) > 0) || !(preg_match("/\\./", $argument) === 0)) { $errorContent = Zurmo::t('Core', 'Please use only integers lesser than {max}.', array('{max}' => count($this->dynamicClauses))); } } } if (isset($errorContent)) { $this->addError('dynamicStructure', Zurmo::t('Core', 'The structure is invalid. {error}', array('{error}' => $errorContent))); } } }
/** * Validates if the filter structure is valid. */ public function validateFiltersStructure() { if (count($this->filters) > 0) { if (null != ($errorMessage = SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage($this->filtersStructure, count($this->filters)))) { $this->addError('filtersStructure', $errorMessage); } } }
/** * @return string */ public static function resolveValueRightSideLikePartByOperatorType($operatorType) { assert('is_string($operatorType)'); $validOperator = true; if (YII_DEBUG) { $validOperator = SQLOperatorUtil::isValidOperatorType($operatorType); } if ($validOperator && in_array($operatorType, array('startsWith', 'doesNotStartsWith', 'contains', 'doesNotContains'))) { return '%'; } }
public static function userAddedToRole(User $user) { assert('$user->role->id > 0'); foreach (self::getMungableModelClassNames() as $modelClassName) { $mungeTableName = self::getMungeTableName($modelClassName); $userId = $user->id; $sql = "select securableitem_id\n from ownedsecurableitem\n where owner__user_id = {$userId}"; $securableItemIds = R::getCol($sql); //Increment the parent roles for securableItems that the user is the owner on. self::bulkIncrementParentRolesCounts($mungeTableName, $securableItemIds, $user->role); //Get all downstream groups the user is in including any groups that are in those groups recursively. //Then for each group found, add weight for the user's upstream roles. $groupMungeIds = array(); foreach ($user->groups as $group) { $groupMungeIds[] = 'G' . $group->id; self::getAllUpstreamGroupsRecursively($group, $groupMungeIds); } if (count($groupMungeIds) > 0) { $inSqlPart = SQLOperatorUtil::resolveOperatorAndValueForOneOf('oneOf', $groupMungeIds, true); $sql = "select distinct {$mungeTableName}.securableitem_id\n from {$mungeTableName}\n where {$mungeTableName}.munge_id {$inSqlPart}"; $securableItemIds = R::getCol($sql); self::bulkIncrementParentRolesCounts($mungeTableName, $securableItemIds, $user->role); } } }
/** * For now use the same method that SQL structure checks use since it works the same way * @param $structure * @param $clauseCount * @return null | string if error message */ public static function resolveValidationForATemplateSqlStatementAndReturnErrorMessage($structure, $clauseCount) { return SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage($structure, $clauseCount); }
public function testDoesOperatorTypeAllowNullValues() { $this->assertTrue(SQLOperatorUtil::doesOperatorTypeAllowNullValues('isNull')); $this->assertTrue(SQLOperatorUtil::doesOperatorTypeAllowNullValues('isEmpty')); $this->assertTrue(SQLOperatorUtil::doesOperatorTypeAllowNullValues('isNotNull')); $this->assertTrue(SQLOperatorUtil::doesOperatorTypeAllowNullValues('isNotEmpty')); $this->assertFalse(SQLOperatorUtil::doesOperatorTypeAllowNullValues('startsWith')); }
public static function resolveToLowerForStringComparison($operatorType, $value) { assert('is_string($operatorType)'); assert('is_string($value)'); if (RedBeanDatabase::getDatabaseType() != 'mysql') { //todo: for pgsql, need to use lower or ILIKE to make sure evaluation is not case sensitive throw new NotSupportedException(); } return SQLOperatorUtil::getOperatorByType($operatorType) . " '" . SQLOperatorUtil::resolveValueLeftSideLikePartByOperatorType($operatorType) . $value . SQLOperatorUtil::resolveValueRightSideLikePartByOperatorType($operatorType) . "'"; }
protected static function bypassReadPermissionsOptimizationToSqlQueryBasedOnWhere($where) { $q = DatabaseCompatibilityUtil::getQuote(); $builtTemplateType = static::BUILT_TYPE_BUILDER_TEMPLATE; $isNull = SQLOperatorUtil::resolveOperatorAndValueForNullOrEmpty('isNull'); $expectedWhere = "({$q}emailtemplate{$q}.{$q}builttype{$q} = {$builtTemplateType}) and " . "({$q}emailtemplate{$q}.{$q}modelclassname{$q} {$isNull})"; if ($where == $expectedWhere) { return true; } return parent::bypassReadPermissionsOptimizationToSqlQueryBasedOnWhere($where); }
/** * Add a sql string to the where array base on the $operatorType, $value, $tableAliasName, and $columnName * parameters. How the sql string is built depends on if the value is a string or not. * @see RedBeanModelDataProvider::makeWhere * @see buildJoinAndWhereForNonRelatedAttribute * @see buildJoinAndWhereForRelatedAttribute */ protected static function addWherePartByClauseInformation($operatorType, $value, &$where, $whereKey, $tableAliasName, $columnName) { assert('is_string($operatorType)'); assert('is_array($where)'); assert('is_int($whereKey)'); assert('is_string($tableAliasName)'); assert('is_string($columnName)'); $quote = DatabaseCompatibilityUtil::getQuote(); if (is_string($value) || is_array($value) && count($value) > 0 || $value !== null || $value === null && SQLOperatorUtil::doesOperatorTypeAllowNullValues($operatorType)) { $where[$whereKey] = "({$quote}{$tableAliasName}{$quote}.{$quote}{$columnName}{$quote} " . DatabaseCompatibilityUtil::getOperatorAndValueWherePart($operatorType, $value) . ")"; } }
public function testResolveValueRightSideLikePartByOperatorType() { $this->assertEquals('%', SQLOperatorUtil::resolveValueRightSideLikePartByOperatorType('doesNotStartsWith')); $this->assertEquals(null, SQLOperatorUtil::resolveValueRightSideLikePartByOperatorType('doesNotEndsWith')); $this->assertEquals('%', SQLOperatorUtil::resolveValueRightSideLikePartByOperatorType('doesNotContains')); $this->assertEquals('%', SQLOperatorUtil::resolveValueRightSideLikePartByOperatorType('contains')); }
public function testResolveValidationForATemplateSqlStatementAndReturnErrorMessage() { $this->assertEquals('The structure is invalid. Please fix conditions.', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('', 1)); $this->assertEquals('The structure is invalid. Please fix conditions.', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('dumb structure', 1)); $this->assertEquals('The structure is invalid. Please use only integers less than 2.', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('4', 1)); $this->assertEquals('The structure is invalid. Please fix your parenthesis.', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('()))', 1)); $this->assertEquals('The structure is invalid. Please fix your parenthesis around the not operator.', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('(not) 2 and 1', 2)); $this->assertEquals('The structure is invalid. Please fix conditions.', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('or 2 and 1', 2)); $this->assertEquals('The structure is invalid. Please, only use one of the operators: not, and, or.', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('1 e 2 ou 1', 2)); $this->assertEquals('', SQLOperatorUtil::resolveValidationForATemplateSqlStatementAndReturnErrorMessage('not (not (2 and 4) or 1 and not 4)', 4)); }
/** * Add a sql string to the where array. How the sql string is built depends on if the value is a string or not. * @param $operatorType * @param $value * @param $where * @param $whereKey * @param $tableAliasName * @param $columnName */ protected function addWherePartByClauseInformation($operatorType, $value, &$where, $whereKey, $tableAliasName, $columnName) { assert('is_string($operatorType)'); assert('is_array($where)'); assert('is_int($whereKey)'); assert('is_string($tableAliasName)'); assert('is_string($columnName)'); if (is_string($value) || is_array($value) && count($value) > 0 || $value !== null || $value === null && SQLOperatorUtil::doesOperatorTypeAllowNullValues($operatorType)) { $where[$whereKey] = "(" . self::resolveWhereColumnContentForModifier($tableAliasName, $columnName) . " " . DatabaseCompatibilityUtil::getOperatorAndValueWherePart($operatorType, $value) . ")"; } }