public function getTableDataDump($tableName, $step = 100)
{
$sql = '';
if ($this->_read) {
$quotedTableName = $this->_read->quoteIdentifier($tableName);
$colunms = $this->_read->fetchRow('SELECT * FROM ' . $quotedTableName . ' LIMIT 1');
if ($colunms) {
$arrSql = array();
$colunms = array_keys($colunms);
$quote = $this->_read->getQuoteIdentifierSymbol();
$sql = 'INSERT INTO ' . $quotedTableName . ' (' . $quote . implode($quote . ', ' . $quote, $colunms) . $quote . ')';
$sql .= ' VALUES ';
$startRow = 0;
$select = $this->_read->select();
$select->from($tableName)->limit($step, $startRow);
while ($data = $this->_read->fetchAll($select)) {
$dataSql = array();
foreach ($data as $row) {
$dataSql[] = $this->_read->quoteInto('(?)', $row);
}
$arrSql[] = $sql . implode(', ', $dataSql) . ';';
$startRow += $step;
$select->limit($step, $startRow);
}
$sql = implode("\n", $arrSql) . "\n";
}
}
return $sql;
}
/**
* Generates an array of SQL insert statements that
* will save the current
*
* @param array $resources
* @access public
* @return string
*/
public function generateInserts(array $resources)
{
$quotedName = $this->_db->quoteIdentifier('name');
$quotedDescription = $this->_db->quoteIdentifier('description');
$quotedFlagsTable = $this->_db->quoteIdentifier('flags');
$insertResourceTemplate = sprintf('INSERT IGNORE INTO %s (%s, %s) VALUES (?, ?);', $quotedFlagsTable, $quotedName, $quotedDescription);
$selectResourceTemplate = sprintf('SET @flag_id := (SELECT id FROM %s WHERE %s = ?);', $quotedFlagsTable, $quotedName);
$insertPrivilegeTemplate = '(@flag_id, %s, %s)';
$inserts = array();
foreach ($resources as $resource) {
// ready the insert resource query
$insertResourceSql = $this->_db->quoteInto($insertResourceTemplate, $resource['name'], NULL, 1);
$insertResourceSql = $this->_db->quoteInto($insertResourceSql, $resource['description'], NULL, 1);
// ready the select resource query
$selectResourceSql = $this->_db->quoteInto($selectResourceTemplate, $resource['name']);
// ready the insert privilege query
$insertPrivilegeSql = sprintf('INSERT IGNORE INTO %s (%s, %s, %s) VALUES ', $this->_db->quoteIdentifier('privileges'), $this->_db->quoteIdentifier('flag_id'), $quotedName, $quotedDescription);
$insertPrivilegeSqlParts = array();
foreach ($resource['methods'] as $method) {
$insertPrivilegeSqlParts[] = sprintf($insertPrivilegeTemplate, $this->_db->quote($method['name']), $this->_db->quote($method['description']));
}
$inserts[] = $insertResourceSql . PHP_EOL . $selectResourceSql . PHP_EOL . $insertPrivilegeSql . PHP_EOL . "\t" . implode(',' . PHP_EOL . "\t", $insertPrivilegeSqlParts) . ';' . PHP_EOL;
}
return $inserts;
}
/**
* get switch case expression with multiple cases
*
* @param string $field
* @param array $cases
*
* @return Zend_Db_Expr
*/
public function getSwitch($field, $cases)
{
$case = 'CASE ' . $this->_adapter->quoteIdentifier($field) . ' ';
foreach ($cases as $when => $then) {
$case .= $this->_adapter->quoteInto(' WHEN ' . $when . ' THEN ?', $then);
}
$case .= ' END';
return new Zend_Db_Expr($case);
}
/**
* @param $shadowPath
* @param $newPath
* @param $oldPath
* @param $newShadowPath
* @param $oldShadowPath
*/
public function copyTreeByShadowPath($shadowPath, $newPath, $oldPath, $newShadowPath, $oldShadowPath)
{
$select = $this->_db->select()->from($this->_tablePrefix . $this->_tableName, array('path' => new Zend_Db_Expr($this->_db->quoteInto($this->_db->quoteInto('REPLACE(path, ?', $oldPath) . ', ?)', $newPath)), 'shadow_path' => new Zend_Db_Expr($this->_db->quoteInto($this->_db->quoteInto('REPLACE(shadow_path, ?', $oldShadowPath) . ', ?)', $newShadowPath)), 'record_id' => 'record_id', 'creation_time' => new Zend_Db_Expr('NOW()')))->where($this->_db->quoteInto($this->_db->quoteIdentifier('shadow_path') . ' like ?', $shadowPath . '/%'));
$stmt = $this->_db->query($select);
$entries = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
foreach ($entries as $entry) {
$entry['id'] = Tinebase_Record_Abstract::generateUID();
$this->_db->insert($this->_tablePrefix . $this->_tableName, $entry);
}
}
function extractAndQuoteCols($bind, &$cols, &$vals)
{
// extract and quote col names from the array keys
$cols = array();
$vals = array();
foreach ($bind as $col => $val) {
$cols[] = $this->wrappedAdapter->quoteIdentifier($col, true);
if ($val instanceof Zend_Db_Expr) {
$vals[] = $val->__toString();
unset($bind[$col]);
} else {
$vals[] = '?';
}
}
}
/**
* _authenticateCreateSelect() - This method creates a Zend_Db_Select object that
* is completely configured to be queried against the database.
*
* @return Zend_Db_Select
*/
protected function _authenticateCreateSelect()
{
// build credential expression
if (empty($this->_credentialTreatment) || (strpos($this->_credentialTreatment, '?') === false)) {
$this->_credentialTreatment = '?';
}
$credentialExpression = new Zend_Db_Expr(
'(CASE WHEN ' .
$this->_zendDb->quoteInto(
$this->_zendDb->quoteIdentifier($this->_credentialColumn, true)
. ' = ' . $this->_credentialTreatment, $this->_credential
)
. ' THEN 1 ELSE 0 END) AS '
. $this->_zendDb->quoteIdentifier(
$this->_zendDb->foldCase('zend_auth_credential_match')
)
);
// get select
$dbSelect = clone $this->getDbSelect();
$dbSelect->from($this->_tableName, array('*', $credentialExpression))
->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $this->_identity);
return $dbSelect;
}
/**
* get array of ids which got send to the client for a given class
*
* @param Syncope_Model_IDevice|string $_deviceId
* @param Syncope_Model_IFolder|string $_folderId
* @return Syncope_Model_SyncState
*/
public function validate($_deviceId, $_folderId, $_syncKey)
{
$deviceId = $_deviceId instanceof Syncope_Model_IDevice ? $_deviceId->id : $_deviceId;
$folderId = $_folderId instanceof Syncope_Model_IFolder ? $_folderId->id : $_folderId;
$select = $this->_db->select()->from($this->_tablePrefix . 'synckey')->where($this->_db->quoteIdentifier('device_id') . ' = ?', $deviceId)->where($this->_db->quoteIdentifier('counter') . ' = ?', $_syncKey)->where($this->_db->quoteIdentifier('type') . ' = ?', $folderId);
$stmt = $this->_db->query($select);
$state = $stmt->fetchObject('Syncope_Model_SyncState');
$stmt = null;
# see https://bugs.php.net/bug.php?id=44081
if (!$state instanceof Syncope_Model_ISyncState) {
return false;
}
$this->_convertFields($state);
// check if this was the latest syncKey
$select = $this->_db->select()->from($this->_tablePrefix . 'synckey')->where($this->_db->quoteIdentifier('device_id') . ' = ?', $deviceId)->where($this->_db->quoteIdentifier('counter') . ' = ?', $_syncKey + 1)->where($this->_db->quoteIdentifier('type') . ' = ?', $folderId);
$stmt = $this->_db->query($select);
$moreRecentState = $stmt->fetchObject('Syncope_Model_SyncState');
$stmt = null;
# see https://bugs.php.net/bug.php?id=44081
// found more recent synckey => the last sync repsone got not received by the client
if ($moreRecentState instanceof Syncope_Model_ISyncState) {
// undelete entries marked as deleted in syncope_content table
$this->_db->update($this->_tablePrefix . 'content', array('is_deleted' => 0), array('device_id = ?' => $deviceId, 'folder_id = ?' => $folderId, 'creation_synckey = ?' => $state->counter, 'is_deleted = ?' => 1));
// remove entries added during latest sync in syncope_content table
$this->_db->delete($this->_tablePrefix . 'content', array('device_id = ?' => $deviceId, 'folder_id = ?' => $folderId, 'creation_synckey > ?' => $state->counter));
} else {
// finaly delete all entries marked for removal in syncope_content table
$this->_db->delete($this->_tablePrefix . 'content', array('device_id = ?' => $deviceId, 'folder_id = ?' => $folderId, 'is_deleted = ?' => 1));
}
// remove all other synckeys
$this->_deleteOtherStates($state);
return $state;
}
/**
* Apply filter
* @param Zend_Db_Adapter_Abstract $db
* @param Db_Select | Zend_Db_Select $sql
* @throws Exception
*/
public function applyTo(Zend_Db_Adapter_Abstract $db, $sql)
{
if (!$sql instanceof Db_Select && !$sql instanceof Zend_Db_Select) {
throw new Exception('Db_Select_Filter::applyTo $sql must be instance of Db_Select/Zend_Db_Select');
}
$quotedField = $db->quoteIdentifier($this->field);
switch ($this->type) {
case self::LT:
case self::GT:
case self::EQ:
case self::GT_EQ:
case self::LT_EQ:
case self::LIKE:
case self::NOT:
case self::NOT_LIKE:
$sql->where($quotedField . ' ' . $this->type . ' ?', $this->value);
break;
case self::IN:
case self::NOT_IN:
$sql->where($quotedField . ' ' . $this->type . ' (?)', $this->value);
break;
case self::NOT_NULL:
case self::IS_NULL:
$sql->where($quotedField . ' ' . $this->type);
break;
case self::BETWEEN:
case self::NOT_BETWEEN:
$sql->where($quotedField . ' ' . $this->type . ' ' . $db->quote($this->value[0]) . ' AND ' . $db->quote($this->value[1]));
break;
}
}
/**
* delete containers, configs and other data of an application
*
* NOTE: if a table with foreign key constraints to applications is added, we need to make sure that the data is deleted here
*
* @param Tinebase_Model_Application $_applicationName
* @return void
*/
public function removeApplicationData(Tinebase_Model_Application $_application)
{
$dataToDelete = array('container' => array('tablename' => ''), 'config' => array('tablename' => ''), 'customfield' => array('tablename' => ''), 'rights' => array('tablename' => 'role_rights'), 'definitions' => array('tablename' => 'importexport_definition'), 'filter' => array('tablename' => 'filter'));
$countMessage = ' Deleted';
$where = array($this->_db->quoteInto($this->_db->quoteIdentifier('application_id') . '= ?', $_application->getId()));
foreach ($dataToDelete as $dataType => $info) {
switch ($dataType) {
case 'container':
$count = Tinebase_Container::getInstance()->deleteContainerByApplicationId($_application->getId());
break;
case 'config':
$count = Tinebase_Config::getInstance()->deleteConfigByApplicationId($_application->getId());
break;
case 'customfield':
$count = Tinebase_CustomField::getInstance()->deleteCustomFieldsForApplication($_application->getId());
break;
default:
if (array_key_exists('tablename', $info) && !empty($info['tablename'])) {
$count = $this->_db->delete(SQL_TABLE_PREFIX . $info['tablename'], $where);
} else {
Tinebase_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' No tablename defined for ' . $dataType);
$count = 0;
}
}
$countMessage .= ' ' . $count . ' ' . $dataType . '(s) /';
}
$countMessage .= ' for application ' . $_application->name;
Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . $countMessage);
}
/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
protected function _stripQuoted($sql)
{
// get the character for value quoting
// this should be '
$q = $this->_adapter->quote('a');
$q = $q[0];
// get the value used as an escaped quote,
// e.g. \' or ''
$qe = $this->_adapter->quote($q);
$qe = substr($qe, 1, 2);
$qe = preg_quote($qe);
$escapeChar = substr($qe, 0, 1);
// remove 'foo\'bar'
if (!empty($q)) {
$escapeChar = preg_quote($escapeChar);
// this segfaults only after 65,000 characters instead of 9,000
$sql = preg_replace("/{$q}([^{$q}{$escapeChar}]*|({$qe})*)*{$q}/s", '', $sql);
}
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove "foo\"bar"
$sql = preg_replace("/\"(\\\\\"|[^\"])*\"/Us", '', $sql);
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->_adapter->quoteIdentifier('a');
$d = $d[0];
// get the value used as an escaped delimited id quote,
// e.g. \" or "" or \`
$de = $this->_adapter->quoteIdentifier($d);
$de = substr($de, 1, 2);
$de = preg_quote($de);
// Note: $de and $d where never used..., now they are:
$sql = preg_replace("/{$d}({$de}|\\\\{2}|[^{$d}])*{$d}/Us", '', $sql);
return $sql;
}
/**
* converts category to tag
*
* @param int $catId
* @return string tagid
*/
public function getTag($catId)
{
if (!(isset($this->_tagMapCache[$catId]) || array_key_exists($catId, $this->_tagMapCache))) {
$select = $this->_egwDb->select()->from(array('cats' => 'egw_categories'))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('cat_id') . ' = ?', $catId));
$cat = $this->_egwDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC);
$cat = count($cat) === 1 ? $cat[0] : NULL;
if (!$cat) {
$this->_log->DEBUG(__METHOD__ . '::' . __LINE__ . " category {$catId} not found in egw, skipping tag");
return $this->_tagMapCache[$catId] = NULL;
}
$tineDb = Tinebase_Core::getDb();
$select = $tineDb->select()->from(array('tags' => $tineDb->table_prefix . 'tags'))->where($tineDb->quoteInto($tineDb->quoteIdentifier('name') . ' LIKE ?', $cat['cat_name']));
$tag = $tineDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC);
$tag = count($tag) > 0 ? $tag[0] : NULL;
if ($tag) {
return $this->_tagMapCache[$catId] = $tag['id'];
}
// create tag
$catData = unserialize($cat['cat_data']);
$tagId = Tinebase_Record_Abstract::generateUID();
$tagType = $cat['cat_access'] == 'public' ? Tinebase_Model_Tag::TYPE_SHARED : Tinebase_Model_Tag::TYPE_PERSONAL;
$tagOwner = $tagType == Tinebase_Model_Tag::TYPE_SHARED ? 0 : $this->mapAccountIdEgw2Tine($cat['cat_owner']);
$this->_log->NOTICE(__METHOD__ . '::' . __LINE__ . " creating new {$tagType} tag '{$cat['cat_name']}'");
$tineDb->insert($tineDb->table_prefix . 'tags', array('id' => $tagId, 'type' => $tagType, 'owner' => $tagOwner, 'name' => $cat['cat_name'], 'description' => $cat['cat_description'], 'color' => $catData['color'], 'created_by' => $tagOwner ? $tagOwner : Tinebase_Core::getUser()->getId(), 'creation_time' => $cat['last_mod'] ? $this->convertDate($cat['last_mod']) : Tinebase_DateTime::now()));
$right = new Tinebase_Model_TagRight(array('tag_id' => $tagId, 'account_type' => $tagType == Tinebase_Model_Tag::TYPE_SHARED ? Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE : Tinebase_Acl_Rights::ACCOUNT_TYPE_USER, 'account_id' => $tagOwner, 'view_right' => true, 'use_right' => true));
Tinebase_Tags::getInstance()->setRights($right);
Tinebase_Tags::getInstance()->setContexts(array(0), $tagId);
$this->_tagMapCache[$catId] = $tagId;
}
return $this->_tagMapCache[$catId];
}
/**
* Resolve credentials
*
* Only the first matching username/realm combination in the file is
* returned. If the file contains credentials for Digest authentication,
* the returned string is the password hash, or h(a1) from RFC 2617. The
* returned string is the plain-text password for Basic authentication.
*
* The expected format of the file is:
* username:realm:sharedSecret
*
* That is, each line consists of the user's username, the applicable
* authentication realm, and the password or hash, each delimited by
* colons.
*
* @param string $username Username
* @param string $realm Authentication Realm
* @throws Zend_Auth_Adapter_Http_Resolver_Exception
* @return string|false User's shared secret, if the user is found in the
* realm, false otherwise.
*/
public function resolve($username, $realm)
{
$exception = null;
if ($this->_tableName == '') {
$exception = 'A table must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.';
} elseif ($this->_identityColumn == '') {
$exception = 'An identity column must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.';
} elseif ($this->_credentialColumn == '') {
$exception = 'A credential column must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.';
}
if (null !== $exception) {
throw new Zend_Auth_Adapter_Http_Resolver_Exception($exception);
}
// create result array
$authResult = array('code' => Zend_Auth_Result::FAILURE, 'identity' => $username, 'messages' => array());
// get select
$select = $this->_zendDb->select();
$select->from($this->_tableName, array('credential' => $this->_credentialColumn))->where($this->_zendDb->quoteIdentifier($this->_identityColumn) . ' = ?', $username);
// query for the identity
try {
$resultIdentities = $this->_zendDb->fetchAll($select->__toString());
} catch (Exception $e) {
/**
* @see Zend_Auth_Adapter_Exception
*/
require_once 'Zend/Auth/Adapter/Exception.php';
throw new Zend_Auth_Adapter_Exception('The supplied parameters to Zend_Auth_Adapter_DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.');
}
if (count($resultIdentities) != 1) {
return false;
}
$resultIdentity = $resultIdentities[0];
return $resultIdentity['credential'];
}
/**
* Truncate a given table.
*
* @param Zend_Db_Adapter_Abstract $db
* @param string $tableName
* @return void
*/
protected function _truncate(Zend_Db_Adapter_Abstract $db, $tableName)
{
$tableName = $db->quoteIdentifier($tableName, true);
if ($db instanceof Zend_Db_Adapter_Pdo_Sqlite) {
$db->query('DELETE FROM ' . $tableName);
} else {
if ($db instanceof Zend_Db_Adapter_Db2) {
/*if(strstr(PHP_OS, "WIN")) {
$file = tempnam(sys_get_temp_dir(), "zendtestdbibm_");
file_put_contents($file, "");
$db->query('IMPORT FROM '.$file.' OF DEL REPLACE INTO '.$tableName);
unlink($file);
} else {
$db->query('IMPORT FROM /dev/null OF DEL REPLACE INTO '.$tableName);
}*/
require_once "Zend/Exception.php";
throw Zend_Exception("IBM Db2 TRUNCATE not supported.");
} else {
if ($this->_isMssqlOrOracle($db)) {
$db->query('TRUNCATE TABLE ' . $tableName);
} else {
if ($db instanceof Zend_Db_Adapter_Pdo_Pgsql) {
$db->query('TRUNCATE ' . $tableName . ' CASCADE');
} else {
$db->query('TRUNCATE ' . $tableName);
}
}
}
}
}
/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
protected function _stripQuoted($sql)
{
// XF CUSTOM: this function has problems. The regex isn't accurate and the
// accurate regex "{$q}([^\\\\{$q}]+|{$q}{$q}|\\\\.)*{$q}" has issues with
// extremely limited stack sizes.
return '';
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->_adapter->quoteIdentifier('a');
$d = $d[0];
// get the character for value quoting
// this should be '
$q = $this->_adapter->quote('a');
$q = $q[0];
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove quoted identifiers
if (!empty($d)) {
$rx = "{$d}{$d}|{$d}.*?(?<!(((?<![{$d}\\\\]){$d})|((?<!\\\\)\\\\))){$d}(?!{$d})";
$sql = preg_replace("/{$rx}/s", '', $sql);
}
// remove quoted values
if (!empty($q)) {
$rx = "{$q}{$q}|{$q}.*?(?<!(((?<![{$q}\\\\]){$q})|((?<!\\\\)\\\\))){$q}(?!{$q})";
$sql = preg_replace("/{$rx}/s", '', $sql);
}
return $sql;
}
/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
protected function _stripQuoted($sql)
{
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->_adapter->quoteIdentifier('a');
$d = $d[0];
// get the value used as an escaped delimited id quote,
// e.g. \" or "" or \`
$de = $this->_adapter->quoteIdentifier($d);
$de = substr($de, 1, 2);
$de = str_replace('\\', '\\\\', $de);
// get the character for value quoting
// this should be '
$q = $this->_adapter->quote('a');
$q = $q[0];
// get the value used as an escaped quote,
// e.g. \' or ''
$qe = $this->_adapter->quote($q);
$qe = substr($qe, 1, 2);
$qe = str_replace('\\', '\\\\', $qe);
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove "foo\"bar"
$sql = preg_replace("/{$q}({$qe}|\\\\{2}|[^{$q}])*{$q}/", '', $sql);
// remove 'foo\'bar'
if (!empty($q)) {
$sql = preg_replace("/{$q}({$qe}|[^{$q}])*{$q}/", '', $sql);
}
return $sql;
}
protected function _authenticateCreateSelect()
{
// get select
$dbSelect = clone $this->getDbSelect();
$dbSelect->from($this->_tableName, array('*'))->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $this->_apiKey);
return $dbSelect;
}
/**
* reset list of stored id
*
* @param Syncope_Model_IDevice|string $_deviceId
* @param Syncope_Model_IFolder|string $_folderId
*/
public function resetState($_deviceId, $_folderId)
{
$deviceId = $_deviceId instanceof Syncope_Model_IDevice ? $_deviceId->id : $_deviceId;
$folderId = $_folderId instanceof Syncope_Model_IFolder ? $_folderId->id : $_folderId;
$where = array($this->_db->quoteInto($this->_db->quoteIdentifier('device_id') . ' = ?', $deviceId), $this->_db->quoteInto($this->_db->quoteIdentifier('folder_id') . ' = ?', $folderId));
$this->_db->delete($this->_tablePrefix . 'content', $where);
}
/**
* rename table in applications table
*
* @param string $_oldTableName
* @param string $_newTableName
*/
public function renameTable($_oldTableName, $_newTableName)
{
$this->_backend->renameTable($_oldTableName, $_newTableName);
$applicationsTables = new Tinebase_Db_Table(array('name' => SQL_TABLE_PREFIX . 'application_tables'));
$where = array($this->_db->quoteInto($this->_db->quoteIdentifier('name') . ' = ?', $_oldTableName));
$result = $applicationsTables->update(array('name' => $_newTableName), $where);
}
/**
* Handles creating or replacing the view for this survey
*
* @param \Gems_Tracker_Survey $viewName
* @param \MUtil_Model_ModelAbstract $answerModel
*/
protected function replaceCreateView(\Gems_Tracker_Survey $survey, \MUtil_Model_ModelAbstract $answerModel)
{
$viewName = $this->getViewName($survey);
$responseDb = $this->project->getResponseDatabase();
$fieldSql = '';
foreach ($answerModel->getItemsOrdered() as $name) {
if (true === $answerModel->get($name, 'survey_question') && !in_array($name, array('submitdate', 'startdate', 'datestamp')) && !$answerModel->is($name, 'type', \MUtil_Model::TYPE_NOVALUE)) {
// Only real answers
$fieldSql .= ',MAX(IF(gdr_answer_id = ' . $responseDb->quote($name) . ', gdr_response, NULL)) AS ' . $responseDb->quoteIdentifier($name);
}
}
if ($fieldSql > '') {
$dbConfig = $this->db->getConfig();
$tokenTable = $this->db->quoteIdentifier($dbConfig['dbname'] . '.gems__tokens');
$createViewSql = 'CREATE OR REPLACE VIEW ' . $responseDb->quoteIdentifier($viewName) . ' AS SELECT gdr_id_token';
$createViewSql .= $fieldSql;
$createViewSql .= "FROM gemsdata__responses join " . $tokenTable . " on (gto_id_token=gdr_id_token and gto_id_survey=" . $survey->getSurveyId() . ") GROUP BY gdr_id_token;";
try {
$responseDb->query($createViewSql)->execute();
} catch (Exception $exc) {
$responseConfig = $responseDb->getConfig();
$dbUser = $this->db->quoteIdentifier($responseConfig['username']) . '@' . $this->db->quoteIdentifier($responseConfig['host']);
$statement = "GRANT SELECT ON " . $tokenTable . " TO " . $dbUser;
$this->getBatch()->addMessage(sprintf($this->_("Creating view failed, try adding rights using the following statement: %s"), $statement));
}
}
}
/**
* returns grants by owner
*
* eGW has owner based grants whereas Tine 2.0 has container based grants.
* this class reads the egw owner grants and converts them into Tine 2.0 grants
* attacheable to a tine 2.0 container
*
* @param string $_application
* @param string $_accountId
* @return Tinebase_Record_RecordSet of Tinebase_Model_Grant
* @throws Tinebase_Exception_NotFound
*/
public function getGrantsByOwner($_application, $_accountId)
{
$egwAccountId = $this->mapAccountIdTine2Egw($_accountId);
$acl_account = array($egwAccountId);
if ($egwAccountId > 0) {
$user = Tinebase_User::getInstance()->getUserById($_accountId);
$groupIds = $user->getGroupMemberships();
foreach ($groupIds as $groupId) {
$acl_account[] = '-' . $this->mapAccountIdTine2Egw($groupId, 'Group');
}
}
$select = $this->_egwDb->select()->from(array('grants' => 'egw_acl'))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('acl_appname') . ' = ?', $_application))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('acl_account') . ' IN (?)', $acl_account));
$egwGrantDatas = $this->_egwDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC);
//print_r($egwGrantDatas);
// in a first run we merge grants from different sources
$effectiveGrants = array();
if ($egwAccountId > 0) {
// owner has implicitly all grants in egw
$effectiveGrants[$egwAccountId] = 15;
}
foreach ($egwGrantDatas as $egwGrantData) {
// grants are int != 0
if ((int) $egwGrantData['acl_location'] == 0) {
continue;
}
// NOTE: The grant source is not resolveable in Tine 2.0!
// In Tine 2.0 grants are directly given to a container
$grantsSource = $egwGrantData['acl_account'];
$grantsDestination = $egwGrantData['acl_location'];
$grantsGiven = $egwGrantData['acl_rights'];
if (!array_key_exists($grantsDestination, $effectiveGrants)) {
$effectiveGrants[$grantsDestination] = 0;
}
$effectiveGrants[$grantsDestination] |= $grantsGiven;
}
//print_r($effectiveGrants);
// convert to tine grants
$tineGrants = new Tinebase_Record_RecordSet('Tinebase_Model_Grants');
foreach ($effectiveGrants as $grantAccount => $egwGrants) {
$tineGrant = new Tinebase_Model_Grants(array('account_id' => $this->mapAccountIdEgw2Tine($grantAccount), 'account_type' => (int) $grantAccount > 0 ? Tinebase_Acl_Rights::ACCOUNT_TYPE_USER : Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP));
foreach ($this->_grantMap as $egwGrant => $tineGrantString) {
$tineGrant->{$tineGrantString} = (bool) ($egwGrants & $egwGrant);
}
// the owner also gets admin grants
if ($egwAccountId > 0 && $grantAccount == $egwAccountId) {
$tineGrant->{Tinebase_Model_Grants::GRANT_ADMIN} = TRUE;
}
$tineGrants->addRecord($tineGrant);
}
//print_r($tineGrants->toArray());
// for group owners (e.g. group addressbooks) we need an container admin
if ($egwAccountId < 0) {
$adminGroup = Tinebase_Group::getInstance()->getDefaultAdminGroup();
$tineGrant = new Tinebase_Model_Grants(array('account_id' => $this->mapAccountIdEgw2Tine($_accountId), 'account_type' => Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP));
$tineGrant->{Tinebase_Model_Grants::GRANT_ADMIN} = TRUE;
$tineGrants->addRecord($tineGrant);
}
return $tineGrants;
}
protected function _getSqlDropSequence(Zend_Db_Adapter_Abstract $db, $sequenceName)
{
$seqList = $db->fetchCol('SELECT sequence_name FROM ALL_SEQUENCES');
if (in_array($sequenceName, $seqList)) {
return 'DROP SEQUENCE ' . $db->quoteIdentifier($sequenceName);
}
return null;
}
/**
* returns where statement for fulltext search index
*
* @param $fields
* @param $searchstring
*/
public function buildFulltextSearchWhere($fields, $searchstring)
{
$columnNames = array();
foreach ($fields as $c) {
$columnNames[] = $this->db->quoteIdentifier($c);
}
return 'MATCH (' . implode(",", $columnNames) . ') AGAINST (' . $this->db->quote($searchstring) . ' IN BOOLEAN MODE)';
}
/**
* Defined by Zend_Validate_Interface
*
* Returns true if and only if $value is an available username
*
* @param string $value
* @throws Zend_Validate_Exception if there is a fatal error
* @return boolean
*/
public function isValid($value)
{
$this->_setValue($value);
$dbSelect = $this->_zendDb->select();
$dbSelect->from($this->_tableName);
$dbSelect->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $value);
$users = $this->_zendDb->fetchAll($dbSelect->__toString());
if (count($users)) {
$this->_error();
return false;
}
if (in_array($value, array('storytlr', 'mail', 'search', 'support', 'feedback', 'admin', 'info', 'host', 'contact', 'root', 'webmaster', 'dns', 'f**k', 'suck', 'forum', 'wiki', 'bugs', 'beta', 'user', 'users', 'username'))) {
$this->_error();
return false;
}
return true;
}
/**
* authenticate() - defined by Zend_Auth_Adapter_Interface.
*
* @throws Zend_Auth_Adapter_Exception if answering the authentication query is impossible
* @return Zend_Auth_Result
*/
public function authenticate()
{
$exception = null;
if ($this->_tableName == '') {
$exception = 'A table must be supplied authentication adapter.';
} elseif ($this->_identityColumn == '') {
$exception = 'A table column must be supplied for the identity.';
} elseif ($this->_identity == '') {
$exception = 'A value for the identity must be provided to authenticate.';
} elseif ($this->_credentialColumn == '') {
$exception = 'A credential column must be supplied to autheticate against.';
} elseif ($this->_credential === null) {
$exception = 'A credential value must be provided to authenticate.';
}
if (null !== $exception) {
/**
* @see Zend_Auth_Adapter_Exception
*/
require_once 'Zend/Auth/Adapter/Exception.php';
throw new Zend_Auth_Adapter_Exception($exception);
}
// create result array
$authResult = array('isValid' => false, 'identity' => $this->_identity, 'messages' => array());
// build credential expression
if (empty($this->_credentialTreatment) || strpos($this->_credentialTreatment, "?") === false) {
$this->_credentialTreatment = '?';
}
$credentialExpression = new Zend_Db_Expr($this->_zendDb->quoteInto($this->_zendDb->quoteIdentifier($this->_credentialColumn) . ' = ' . $this->_credentialTreatment, $this->_credential) . ' AS zend_auth_credential_match');
// get select
$dbSelect = $this->_zendDb->select();
$dbSelect->from($this->_tableName, array('*', $credentialExpression))->where($this->_zendDb->quoteIdentifier($this->_identityColumn) . ' = ?', $this->_identity);
// query for the identity
try {
$resultIdentities = $this->_zendDb->fetchAll($dbSelect->__toString());
} catch (Exception $e) {
/**
* @see Zend_Auth_Adapter_Exception
*/
require_once 'Zend/Auth/Adapter/Exception.php';
throw new Zend_Auth_Adapter_Exception($e->getMessage());
}
if (count($resultIdentities) < 1) {
$authResult['messages'][] = 'A record with the supplied identity could not be found.';
return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']);
} elseif (count($resultIdentities) > 1) {
$authResult['messages'][] = 'More than one record matches the supplied identity.';
return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']);
}
$resultIdentity = $resultIdentities[0];
if ($resultIdentity['zend_auth_credential_match'] != '1') {
$authResult['messages'][] = 'Supplied credential is invalid.';
return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']);
}
unset($resultIdentity['zend_auth_credential_match']);
$this->_resultRow = $resultIdentity;
$authResult['isValid'] = true;
return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']);
}
/**
* remove all relations for application
*
* @param string $applicationName
*
* @return void
*/
public function removeApplication($applicationName)
{
$tableName = SQL_TABLE_PREFIX . 'relations';
$select = $this->_db->select()->from($tableName)->columns('rel_id')->where($this->_db->quoteIdentifier('own_model') . ' LIKE ?', $applicationName . '_%');
$relation_ids = $this->_db->fetchCol($select);
if (is_array($relation_ids) && count($relation_ids) > 0) {
$this->_db->delete($tableName, $this->_db->quoteInto($this->_db->quoteIdentifier('rel_id') . ' IN (?)', $relation_ids));
}
}
/**
* get the basic select object to fetch records from the database
*
* NOTE: container_id is joined from addressbook lists table
*
* @param array|string|Zend_Db_Expr $_cols columns to get, * per default
* @param boolean $_getDeleted get deleted records (if modlog is active)
* @return Zend_Db_Select
*/
protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
{
$select = $this->_db->select();
$select->from(array($this->_tableName => SQL_TABLE_PREFIX . $this->_tableName), $_cols);
if ($this->_addressBookInstalled === true) {
$select->joinLeft(array('addressbook_lists' => SQL_TABLE_PREFIX . 'addressbook_lists'), $this->_db->quoteIdentifier($this->_tableName . '.list_id') . ' = ' . $this->_db->quoteIdentifier('addressbook_lists.id'), array('container_id'));
}
return $select;
}
public function insertOrUpdate($obj)
{
if ($obj instanceof My_Model_Domain) {
if ($obj->getClientIdUnsetFromData()) {
$obj->unsetField($obj->getClientIdKey());
}
$data = $obj->getData();
} elseif (is_array($obj)) {
$data = $obj;
} else {
throw new Exception("Unsupported datatype used in insert", -1001);
}
// extract and quote col names from the array keys
$cols = array();
$vals = array();
$i = 0;
foreach ($data as $col => $val) {
$cols[] = $this->_connection->quoteIdentifier($col, true);
if ($val instanceof Zend_Db_Expr) {
$vals[] = $val->__toString();
unset($data[$col]);
} else {
if ($this->_connection->supportsParameters('positional')) {
$vals[] = '?';
} else {
if ($this->_connection->supportsParameters('named')) {
unset($data[$col]);
$data[':col' . $i] = $val;
$vals[] = ':col' . $i;
$i++;
} else {
/** @see Zend_Db_Adapter_Exception */
require_once 'Zend/Db/Adapter/Exception.php';
throw new Zend_Db_Adapter_Exception(get_class($this->_connection) . " doesn't support positional or named binding");
}
}
}
}
// build the statement
$sql = "INSERT INTO " . $this->_connection->quoteIdentifier($this->_tablename, true) . ' (' . implode(', ', $cols) . ') ' . 'VALUES (' . implode(', ', $vals) . ')';
$duplicate = " ON DUPLICATE KEY UPDATE ";
foreach ($cols as $index => $col) {
$duplicate .= $col . " = " . $vals[$index] . ",";
}
$duplicate = rtrim($duplicate, ",");
$sql .= $duplicate;
// execute the statement and return the number of affected rows
if ($this->_connection->supportsParameters('positional')) {
$data = array_values($data);
}
//because we have two
$data = array_merge($data, $data);
$stmt = $this->_connection->query($sql, $data);
$result = $stmt->rowCount();
return $result;
}
/**
* Test automatic conversion of SQL functions to
* Zend_Db_Expr, e.g. order('LOWER(title)')
* should give the same result as
* order(new Zend_Db_Expr('LOWER(title)')).
*/
public function testSelectOrderByClauseAutoExpr()
{
$idKey = $this->getResultSetKey('id');
$table = $this->getIdentifier(self::TABLE_NAME);
$id = $this->getIdentifier('id');
$select = $this->_db->select()->from($table)->order("ABS(" . $this->_db->quoteIdentifier($table) . '.' . $this->_db->quoteIdentifier($id) . ")");
$stmt = $this->_db->query($select);
$result = $stmt->fetchAll();
$this->assertEquals(1, $result[0][$idKey]);
}
/**
* check if relation already exists but is_deleted
*
* @param Tinebase_Model_Relation $_relation
* @return string relation id
*/
protected function _checkExistance($_relation)
{
$where = array($this->_db->quoteInto($this->_db->quoteIdentifier('own_model') . ' = ?', $_relation->own_model), $this->_db->quoteInto($this->_db->quoteIdentifier('own_backend') . ' = ?', $_relation->own_backend), $this->_db->quoteInto($this->_db->quoteIdentifier('own_id') . ' = ?', $_relation->own_id), $this->_db->quoteInto($this->_db->quoteIdentifier('related_id') . ' = ?', $_relation->related_id), $this->_db->quoteIdentifier('is_deleted') . ' = 1');
$relationRow = $this->_dbTable->fetchRow($where);
if ($relationRow) {
return $relationRow->rel_id;
} else {
return FALSE;
}
}
/**
* fetch creation time of the first/oldest user
*
* @return Tinebase_DateTime
*/
public function getFirstUserCreationTime()
{
if (!$this->_userTableHasModlogFields()) {
$fallback = new Tinebase_DateTime('2014-12-01');
return $fallback;
}
$select = $select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', 'creation_time')->where($this->_db->quoteIdentifier('login_name') . " not in ('cronuser', 'calendarscheduling')")->where($this->_db->quoteIdentifier('creation_time') . " is not null")->order('creation_time ASC')->limit(1);
$creationTime = $this->_db->fetchOne($select);
$result = !empty($creationTime) ? new Tinebase_DateTime($creationTime) : $fallback;
return $result;
}
