/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { // prepare value if ($this->_operator === 'equals' && empty($this->_value)) { // @see 0009362: allow to filter for empty datetimes $operator = 'isnull'; $value = array($this->_value); } else { $operator = $this->_operator; $value = $this->_getDateValues($operator, $this->_value); if (!is_array($value)) { // NOTE: (array) null is an empty array $value = array($value); } } // quote field identifier $field = $this->_getQuotedFieldName($_backend); $db = Tinebase_Core::getDb(); $dbCommand = Tinebase_Backend_Sql_Command::factory($db); // append query to select object foreach ((array) $this->_opSqlMap[$operator]['sqlop'] as $num => $operator) { if (isset($value[$num]) || array_key_exists($num, $value)) { if (get_parent_class($this) === 'Tinebase_Model_Filter_Date' || in_array($operator, array('isnull', 'notnull'))) { $_select->where($field . $operator, $value[$num]); } else { $_select->where($dbCommand->setDate($field) . $operator, new Zend_Db_Expr($dbCommand->setDateValue($value[$num]))); } } else { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' No filter value found, skipping operator: ' . $operator); } } } }
/** * constructor */ public function __construct() { $this->_db = Tinebase_Core::getDb(); $this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db); // temporary on the fly creation of table $this->_dbTable = new Tinebase_Db_Table(array('name' => SQL_TABLE_PREFIX . 'relations', 'primary' => 'id')); }
/** * get a new single filter action * * @param string|array $_fieldOrData * @param string $_operator * @param mixed $_value * @param array $_options * * @todo remove legacy code + obsolete params sometimes */ public function __construct($_fieldOrData, $_operator = NULL, $_value = NULL, array $_options = array()) { $this->_db = Tinebase_Core::getDb(); $this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db); if (is_array($_fieldOrData)) { $data = $_fieldOrData; } else { if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Using deprecated constructor syntax. Please pass all filter data in one array (filter field: ' . $_fieldOrData . ').'); } $data = array('field' => $_fieldOrData, 'operator' => $_operator, 'value' => $_value, 'options' => $_options); } foreach (array('field', 'operator', 'value') as $requiredKey) { if (!(isset($data[$requiredKey]) || array_key_exists($requiredKey, $data))) { throw new Tinebase_Exception_InvalidArgument('Filter object needs ' . $requiredKey); } } $this->_setOptions(isset($data['options']) ? $data['options'] : array()); $this->setField($data['field']); $this->setOperator($data['operator']); $this->setValue($data['value']); if (isset($data['id'])) { $this->setId($data['id']); } if (isset($data['label'])) { $this->setLabel($data['label']); } }
/** * the constructor * * @param array $_options */ public function __construct(array $_options = array()) { if ($this instanceof Tinebase_EmailUser_Smtp_Interface) { $this->_configKey = Tinebase_Config::SMTP; } else { if ($this instanceof Tinebase_EmailUser_Imap_Interface) { $this->_configKey = Tinebase_Config::IMAP; } else { throw new Tinebase_Exception_UnexpectedValue('Plugin must be instance of Tinebase_EmailUser_Smtp_Interface or Tinebase_EmailUser_Imap_Interface'); } } // get email user backend config options (host, dbname, username, password, port) $emailConfig = Tinebase_Config::getInstance()->get($this->_configKey, new Tinebase_Config_Struct())->toArray(); // merge _config and email backend config if ($this->_subconfigKey) { // flatten array $emailConfig = array_merge($emailConfig[$this->_subconfigKey], $emailConfig); } // merge _config and email backend config $this->_config = array_merge($this->_config, $emailConfig); // _tablename (for example "dovecot_users") $this->_userTable = $this->_config['prefix'] . $this->_config['userTable']; // connect to DB $this->_getDB(); $this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . print_r($this->_config, TRUE)); } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { if (empty($this->_value)) { $_select->where('1=1/* empty query */'); return; } $db = Tinebase_Core::getDb(); switch ($this->_operator) { case 'contains': case 'equals': case 'startswith': $queries = explode(' ', $this->_value); foreach ($queries as $query) { $whereParts = array(); foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we already have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)')); } else { $whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($_backend->getTableName() . '.' . $qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)')); } } $whereClause = ''; if (!empty($whereParts)) { $whereClause = implode(' OR ', $whereParts); } if (!empty($whereClause)) { if ($this->_operator == 'equals') { $_select->where($db->quoteInto($whereClause, trim($query))); } else { if ($this->_operator == 'startswith') { $_select->where($db->quoteInto($whereClause, trim($query) . '%')); } else { $_select->where($db->quoteInto($whereClause, '%' . trim($query) . '%')); } } } } break; case 'in': foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we allready have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = $db->quoteInto($db->quoteIdentifier($qField) . ' IN (?)', (array) $this->_value); } else { $whereParts[] = $db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ' IN (?)', (array) $this->_value); } } if (!empty($whereParts)) { $whereClause = implode(' OR ', $whereParts); } if (!empty($whereClause)) { $_select->where($whereClause); } break; default: throw new Tinebase_Exception_InvalidArgument('Operator not defined: ' . $this->_operator); } }
/** * the constructor * * allowed options: * - modelName * - tableName * - tablePrefix * - modlogActive * * @param Zend_Db_Adapter_Abstract $_dbAdapter (optional) * @param array $_options (optional) * @throws Tinebase_Exception_Backend_Database */ public function __construct($_dbAdapter = NULL, $_options = array()) { $this->_db = $_dbAdapter instanceof Zend_Db_Adapter_Abstract ? $_dbAdapter : Tinebase_Core::getDb(); $this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db); $this->_modelName = isset($_options['modelName']) || array_key_exists('modelName', $_options) ? $_options['modelName'] : $this->_modelName; $this->_tableName = isset($_options['tableName']) || array_key_exists('tableName', $_options) ? $_options['tableName'] : $this->_tableName; /** @noinspection PhpUndefinedFieldInspection */ $this->_tablePrefix = isset($_options['tablePrefix']) || array_key_exists('tablePrefix', $_options) ? $_options['tablePrefix'] : $this->_db->table_prefix; $this->_modlogActive = isset($_options['modlogActive']) || array_key_exists('modlogActive', $_options) ? $_options['modlogActive'] : $this->_modlogActive; foreach ($this->_additionalColumns as $name => $query) { $this->_additionalColumns[$name] = str_replace("{prefix}", $this->_tablePrefix, $query); } if (!($this->_tableName && $this->_modelName)) { throw new Tinebase_Exception_Backend_Database('modelName and tableName must be configured or given.'); } if (!$this->_db) { throw new Tinebase_Exception_Backend_Database('Database adapter must be configured or given.'); } }
/** * check if user connected with too many user agent during the last hour * * @param Tinebase_Model_FullUser $_user * @param int $numberOfAllowedUserAgents * @return bool */ protected function _tooManyUserAgents($_user, $numberOfAllowedUserAgents = 3) { $result = false; $db = $this->_backend->getAdapter(); $dbCommand = Tinebase_Backend_Sql_Command::factory($db); $select = $db->select()->distinct(true)->from($this->_backend->getTablePrefix() . $this->_backend->getTableName(), 'user_agent')->where($db->quoteIdentifier('account_id') . ' = ?', $_user->getId())->where($db->quoteIdentifier('li') . ' > NOW() - ' . $dbCommand->getInterval('HOUR', '1'))->where($db->quoteIdentifier('result') . ' <> ?', Tinebase_Auth::SUCCESS, Zend_Db::PARAM_INT)->limit(10); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . $select); } $stmt = $db->query($select); if ($stmt->columnCount() > $numberOfAllowedUserAgents) { if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) { Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' More than ' . $numberOfAllowedUserAgents . ' different UserAgents? we don\'t trust you!'); } $result = true; } $stmt->closeCursor(); return $result; }
/** * the constructor * * @param array $options Options used in connecting, binding, etc. */ public function __construct(array $_options = array()) { parent::__construct($_options); $this->_db = Tinebase_Core::getDb(); $this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db); }
/** * Initializes database procedures if they exist */ protected function _initProcedures() { $backend = Setup_Backend_Factory::factory(); $dbCommand = Tinebase_Backend_Sql_Command::factory(Tinebase_Core::getDb()); $dbCommand->initProcedures($backend); }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * @throws Tinebase_Exception_InvalidArgument */ public function appendFilterSql($_select, $_backend) { // quote field identifier, set action and replace wildcards $field = $this->_getQuotedFieldName($_backend); if (!(isset($this->_opSqlMap[$this->_operator]) || array_key_exists($this->_operator, $this->_opSqlMap))) { throw new Tinebase_Exception_InvalidArgument('Operator "' . $this->_operator . '" not defined in sql map of ' . get_class($this)); } $action = $this->_opSqlMap[$this->_operator]; // don't remove wildcards for certain operators // TODO add an option for this? $value = !in_array($this->_operator, array('in', 'notin')) ? $this->_replaceWildcards($this->_value) : $this->_value; // check if group by is operator and return if this is the case if ($this->_operator == 'group') { $_select->group($this->_field); } if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) { $value = explode(' ', $value); } // this is a text filter, so all items in the filter must be of type text (needed in pgsql) if (in_array($this->_operator, array('in', 'notin')) && is_array($value)) { foreach ($value as &$item) { $item = (string) $item; } } $db = Tinebase_Core::getDb(); if (is_array($value) && empty($value)) { $_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */')); return; } if ($this->_operator == 'equalsspecial') { if (is_array($value)) { foreach ($value as $key => $v) { $value[$key] = preg_replace('/(\\s+|\\-)/', '%', $v); } } else { $value = preg_replace('/(\\s+|\\-)/', '%', $value); } } if (!in_array($this->_operator, array('in', 'notin'))) { $where = Tinebase_Core::getDb()->quoteInto(Tinebase_Backend_Sql_Command::factory($db)->prepareForILike($field) . ' ' . $action['sqlop'], $value); } else { $where = Tinebase_Core::getDb()->quoteInto($field . $action['sqlop'], $value); } if (in_array($this->_operator, array('not', 'notin')) && $value !== '') { $where = "( {$where} OR {$field} IS NULL)"; } if (in_array($this->_operator, array('equals', 'equalsspecial', 'contains', 'startswith', 'endswith', 'in')) && $value === '') { $where = "( {$where} OR {$field} IS NULL)"; } // finally append query to select object $_select->where($where); }
/** * the constructor * */ private function __construct() { $this->_db = Tinebase_Core::getDb(); $this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db); }
/** * add to/cc/bcc and flags custom filters * * @param Zend_Db_Select $_select * @param Felamimail_Backend_Cache_Sql_Message $_backend * @param array $_filterData * @return void */ protected function _addRecipientAndFlagsSql($_select, $_backend, $_filterData) { $db = $_backend->getAdapter(); $foreignTables = $_backend->getForeignTables(); // add conditions $tablename = $foreignTables[$_filterData['field']]['table']; if ($_filterData['field'] !== 'flags') { $fieldName = $tablename . '.name'; $fieldEmail = $tablename . '.email'; } // add filter value if (!is_array($_filterData['value'])) { $value = '%' . $_filterData['value'] . '%'; } else { $value = array(); foreach ((array) $_filterData['value'] as $customValue) { $value[] = '%' . $customValue . '%'; } } if ($_filterData['field'] == 'flags') { $havingColumn = $db instanceof Zend_Db_Adapter_Pdo_Pgsql ? Tinebase_Backend_Sql_Command::factory($db)->getAggregate('felamimail_cache_msg_flag.flag') : 'flags'; if ($_filterData['operator'] == 'equals' || $_filterData['operator'] == 'contains') { $_select->having($db->quoteInto($havingColumn . ' LIKE ?', $value)); } else { if ($_filterData['operator'] == 'in' || $_filterData['operator'] == 'notin') { if (empty($value)) { $whereString = 'flags IS NULL'; } else { $value = (array) $value; $where = array(); $op = $_filterData['operator'] == 'in' ? 'LIKE' : 'NOT LIKE'; $opImplode = $_filterData['operator'] == 'in' ? ' OR ' : ' AND '; foreach ($value as $flag) { $where[] = $db->quoteInto('flags ' . $op . ' ?', $flag); } $whereString = implode($opImplode, $where); if ($_filterData['operator'] == 'notin') { $whereString = '(' . $whereString . ') OR flags IS NULL'; } } $_select->having(str_replace('flags', $havingColumn, $whereString)); } else { $_select->having($db->quoteInto($havingColumn . ' NOT LIKE ? OR ' . $havingColumn . ' IS NULL', $value)); } } } else { $_select->where($db->quoteInto($fieldName . ' LIKE ?', $value) . ' OR ' . $db->quoteInto($fieldEmail . ' LIKE ?', $value)); } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * @throws Tinebase_Exception_InvalidArgument */ public function appendFilterSql($_select, $_backend) { if (empty($this->_value)) { $_select->where('1=1/* empty query */'); return; } $db = $_backend->getAdapter(); $sqlCommand = Tinebase_Backend_Sql_Command::factory($db); if (0 === strpos($this->_operator, 'not')) { $not = true; } else { $not = false; } switch ($this->_operator) { case 'contains': case 'notcontains': case 'equals': case 'not': case 'startswith': case 'endswith': $queries = explode(' ', $this->_value); foreach ($queries as $query) { $whereParts = array(); foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we already have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = $sqlCommand->prepareForILike($sqlCommand->getUnaccent($db->quoteIdentifier($qField))) . ' ' . ($not ? 'NOT ' : '') . $sqlCommand->getLike() . $sqlCommand->prepareForILike($sqlCommand->getUnaccent('(?)')); } else { $whereParts[] = $sqlCommand->prepareForILike($sqlCommand->getUnaccent($db->quoteIdentifier($_backend->getTableName() . '.' . $qField))) . ' ' . ($not ? 'NOT ' : '') . $sqlCommand->getLike() . $sqlCommand->prepareForILike($sqlCommand->getUnaccent('(?)')); } } $whereClause = ''; if (!empty($whereParts)) { if ($not) { $whereClause = implode(' AND ', $whereParts); } else { $whereClause = implode(' OR ', $whereParts); } } if (!empty($whereClause)) { $query = trim($query); if ($this->_operator === 'startswith') { $query .= '%'; } else { if ($this->_operator === 'contains' || $this->_operator === 'notcontains') { $query = '%' . $query . '%'; } else { if ($this->_operator === 'endswith') { $query = '%' . $query; } } } $_select->where($db->quoteInto($whereClause, $query)); } } break; case 'notin': case 'in': foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we allready have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = $db->quoteInto($db->quoteIdentifier($qField) . ($not ? ' NOT' : '') . ' IN (?)', (array) $this->_value); } else { $whereParts[] = $db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ($not ? ' NOT' : '') . ' IN (?)', (array) $this->_value); } } if (!empty($whereParts)) { if ($not) { $whereClause = implode(' AND ', $whereParts); } else { $whereClause = implode(' OR ', $whereParts); } } if (!empty($whereClause)) { $_select->where($whereClause); } break; default: throw new Tinebase_Exception_InvalidArgument('Operator not defined: ' . $this->_operator); } // append advanced search filter if configured if (isset($this->_options['relatedModels']) && isset($this->_options['modelName'])) { $relationFilter = $this->_getAdvancedSearchFilter($this->_options['modelName'], $this->_options['relatedModels']); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Got relation filter: ' . ($relationFilter instanceof Tinebase_Model_Filter_Abstract ? print_r($relationFilter->toArray(), true) : '')); } if ($relationFilter) { $relationSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select); $relationFilter->appendFilterSql($relationSelect, $_backend); $relationSelect->appendWhere($not ? Zend_Db_Select::SQL_AND : Zend_Db_Select::SQL_OR); } } }
/** * the constructor * * allowed options: * - modelName * - tableName * - tablePrefix * - modlogActive * * @param Zend_Db_Adapter_Abstract $_db (optional) * @param array $_options (optional) * @throws Tinebase_Exception_Backend_Database */ public function __construct($_dbAdapter = NULL, $_options = array()) { $this->_db = $_dbAdapter instanceof Zend_Db_Adapter_Abstract ? $_dbAdapter : Tinebase_Core::getDb(); $this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db); $this->_modelName = isset($_options['modelName']) || array_key_exists('modelName', $_options) ? $_options['modelName'] : $this->_modelName; $this->_tableName = isset($_options['tableName']) || array_key_exists('tableName', $_options) ? $_options['tableName'] : $this->_tableName; $this->_tablePrefix = isset($_options['tablePrefix']) || array_key_exists('tablePrefix', $_options) ? $_options['tablePrefix'] : $this->_db->table_prefix; $this->_modlogActive = isset($_options['modlogActive']) || array_key_exists('modlogActive', $_options) ? $_options['modlogActive'] : $this->_modlogActive; if (!($this->_tableName && $this->_modelName)) { throw new Tinebase_Exception_Backend_Database('modelName and tableName must be configured or given.'); } if (!$this->_db) { throw new Tinebase_Exception_Backend_Database('Database adapter must be configured or given.'); } }