/**
* 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) . ")";
}
}
