/** * add foreign table joins * * @param Zend_Db_Select $_select * @param array|string $_cols columns to get, * per default * * @todo joining the same table twice with same name but different "on"'s is not possible currently */ protected function _addForeignTableJoins(Zend_Db_Select $_select, $_cols, $_groupBy = NULL) { if (!empty($this->_foreignTables)) { $groupBy = $_groupBy !== NULL ? $_groupBy : $this->_tableName . '.' . $this->_identifier; $_select->group($groupBy); $cols = (array) $_cols; foreach ($this->_foreignTables as $foreignColumn => $join) { // only join if field is in cols if (in_array('*', $cols) || (isset($cols[$foreignColumn]) || array_key_exists($foreignColumn, $cols))) { if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' foreign column: ' . $foreignColumn); } $selectArray = isset($join['select']) || array_key_exists('select', $join) ? $join['select'] : ((isset($join['field']) || array_key_exists('field', $join)) && (!(isset($join['singleValue']) || array_key_exists('singleValue', $join)) || !$join['singleValue']) ? array($foreignColumn => $this->_dbCommand->getAggregate($join['table'] . '.' . $join['field'])) : array($foreignColumn => $join['table'] . '.id')); $joinId = isset($join['joinId']) ? $join['joinId'] : $this->_identifier; // avoid duplicate columns => will be added again in the next few lines of code $this->_removeColFromSelect($_select, $foreignColumn); $from = $_select->getPart(Zend_Db_Select::FROM); if (!isset($from[$join['table']])) { $_select->joinLeft(array($join['table'] => $this->_tablePrefix . $join['table']), $this->_db->quoteIdentifier($this->_tableName . '.' . $joinId) . ' = ' . $this->_db->quoteIdentifier($join['table'] . '.' . $join['joinOn']), $selectArray); } else { // join is defined already => just add the column $_select->columns($selectArray, $join['table']); } } } } }
/** * replaces wildcards of a single value * * @param string $value * @return string */ protected function _replaceWildcardsSingleValue($value) { $action = $this->_opSqlMap[$this->_operator]; // replace wildcards from user () $returnValue = str_replace(array('*', '_'), $this->_dbCommand->setDatabaseJokerCharacters(), $value); // add wildcard to value according to operator $returnValue = str_replace('?', $returnValue, $action['wildcards']); return $returnValue; }
/** * get user select * * @return Zend_Db_Select */ protected function _getUserSelectObject() { $interval = $this->_dbCommand->getDynamicInterval('SECOND', '1', 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ' > 5 THEN 60 ELSE POWER(2, ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ') END'); $statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (' . 'CASE WHEN ' . $this->_dbCommand->setDate('NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' WHEN ( ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ' > 0 AND ' . $this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure']) . ' + ' . $interval . ' > NOW()) THEN ' . $this->_db->quote('blocked') . ' ELSE ' . $this->_db->quote('enabled') . ' END)' . ' WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('expired') . ' THEN ' . $this->_db->quote('expired') . ' ELSE ' . $this->_db->quote('disabled') . ' END'; $fields = array('accountId' => $this->rowNameMapping['accountId'], 'accountLoginName' => $this->rowNameMapping['accountLoginName'], 'accountLastLogin' => $this->rowNameMapping['accountLastLogin'], 'accountLastLoginfrom' => $this->rowNameMapping['accountLastLoginfrom'], 'accountLastPasswordChange' => $this->rowNameMapping['accountLastPasswordChange'], 'accountStatus' => $statusSQL, 'accountExpires' => $this->rowNameMapping['accountExpires'], 'accountPrimaryGroup' => $this->rowNameMapping['accountPrimaryGroup'], 'accountHomeDirectory' => $this->rowNameMapping['accountHomeDirectory'], 'accountLoginShell' => $this->rowNameMapping['accountLoginShell'], 'accountDisplayName' => $this->rowNameMapping['accountDisplayName'], 'accountFullName' => $this->rowNameMapping['accountFullName'], 'accountFirstName' => $this->rowNameMapping['accountFirstName'], 'accountLastName' => $this->rowNameMapping['accountLastName'], 'accountEmailAddress' => $this->rowNameMapping['accountEmailAddress'], 'lastLoginFailure' => $this->rowNameMapping['lastLoginFailure'], 'loginFailures' => $this->rowNameMapping['loginFailures'], 'contact_id', 'openid', 'visibility', 'NOW()'); // modlog fields have been added later if ($this->_userTableHasModlogFields()) { $fields = array_merge($fields, array('created_by', 'creation_time', 'last_modified_by', 'last_modified_time', 'is_deleted', 'deleted_time', 'deleted_by', 'seq')); } $select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', $fields)->joinLeft(SQL_TABLE_PREFIX . 'addressbook', $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'accounts.contact_id') . ' = ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'addressbook.id'), array('container_id' => 'container_id')); return $select; }
/** * returns all contexts of a given tag * * @param string $_tagId * @return array array of application ids */ public function getContexts($_tagId) { $select = $this->_db->select()->from(array('tags_context' => SQL_TABLE_PREFIX . 'tags_context'), array('application_id' => $this->_dbCommand->getAggregate('application_id')))->where($this->_db->quoteInto($this->_db->quoteIdentifier('tag_id') . ' = ?', $_tagId))->group('tag_id'); Tinebase_Backend_Sql_Abstract::traitGroup($select); $apps = $this->_db->fetchOne($select); if ($apps === '0') { $apps = 'any'; } if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' got tag contexts: ' . $apps); } return explode(',', $apps); }
/** * get user select * * @return Zend_Db_Select */ protected function _getUserSelectObject() { /* * CASE WHEN `status` = 'enabled' THEN (CASE WHEN DATE(NOW()) > `expires_at` THEN 'expired' * WHEN ( `login_failures` > 5 AND DATE(`last_login_failure_at`) + INTERVAL '15' MINUTE > DATE(NOW())) THEN 'blocked' * ELSE 'enabled' END) WHEN `status` = 'expired' THEN 'expired' ELSE 'disabled' END */ $maxLoginFailures = Tinebase_Config::getInstance()->get(Tinebase_Config::MAX_LOGIN_FAILURES, 5); if ($maxLoginFailures > 0) { $loginFailuresCondition = 'WHEN ( ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . " > {$maxLoginFailures} AND " . $this->_dbCommand->setDate($this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure'])) . " + INTERVAL '{$this->_blockTime}' MINUTE > " . $this->_dbCommand->setDate('NOW()') . ") THEN 'blocked'"; } else { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' User blocking disabled.'); } $loginFailuresCondition = ''; } $statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (' . 'CASE WHEN ' . $this->_dbCommand->setDate('NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' ' . $loginFailuresCondition . ' ELSE ' . $this->_db->quote('enabled') . ' END)' . ' WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('expired') . ' THEN ' . $this->_db->quote('expired') . ' ELSE ' . $this->_db->quote('disabled') . ' END'; $select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', array('accountId' => $this->rowNameMapping['accountId'], 'accountLoginName' => $this->rowNameMapping['accountLoginName'], 'accountLastLogin' => $this->rowNameMapping['accountLastLogin'], 'accountLastLoginfrom' => $this->rowNameMapping['accountLastLoginfrom'], 'accountLastPasswordChange' => $this->rowNameMapping['accountLastPasswordChange'], 'accountStatus' => $statusSQL, 'accountExpires' => $this->rowNameMapping['accountExpires'], 'accountPrimaryGroup' => $this->rowNameMapping['accountPrimaryGroup'], 'accountHomeDirectory' => $this->rowNameMapping['accountHomeDirectory'], 'accountLoginShell' => $this->rowNameMapping['accountLoginShell'], 'accountDisplayName' => $this->rowNameMapping['accountDisplayName'], 'accountFullName' => $this->rowNameMapping['accountFullName'], 'accountFirstName' => $this->rowNameMapping['accountFirstName'], 'accountLastName' => $this->rowNameMapping['accountLastName'], 'accountEmailAddress' => $this->rowNameMapping['accountEmailAddress'], 'lastLoginFailure' => $this->rowNameMapping['lastLoginFailure'], 'loginFailures' => $this->rowNameMapping['loginFailures'], 'contact_id', 'openid', 'visibility', 'created_by', 'creation_time', 'last_modified_by', 'last_modified_time', 'is_deleted', 'deleted_time', 'deleted_by', 'seq'))->joinLeft(SQL_TABLE_PREFIX . 'addressbook', $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'accounts.contact_id') . ' = ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'addressbook.id'), array('container_id' => 'container_id')); return $select; }