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