/** * the constructor */ public function __construct() { $this->_db = Tinebase_Core::getDb(); $this->groupsTable = new Tinebase_Db_Table(array('name' => SQL_TABLE_PREFIX . $this->_tableName)); $this->groupMembersTable = new Tinebase_Db_Table(array('name' => SQL_TABLE_PREFIX . 'group_members')); try { // MySQL throws an exception if the table does not exist // PostgreSQL returns an empty array if the table does not exist $adbSchema = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . 'addressbook'); $adbListsSchema = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . 'addressbook_lists'); if (!empty($adbSchema) && !empty($adbListsSchema)) { $this->_addressBookInstalled = TRUE; } } catch (Zend_Db_Statement_Exception $zdse) { // nothing to do } }
/** * the constructor * */ public function __construct(array $_options = array()) { $imapConfig = Tinebase_Config::getInstance()->get(Tinebase_Config::IMAP, new Tinebase_Config_Struct())->toArray(); // merge _config and dbmail imap $this->_config = array_merge($imapConfig['dbmail'], $this->_config); // set domain from imap config $this->_config['domain'] = !empty($imapConfig['domain']) ? $imapConfig['domain'] : null; // _tablename = "dbmail_users" $this->_userTable = $this->_config['prefix'] . $this->_config['userTable']; // connect to DB $this->_getDb($this->_config); $columns = Tinebase_Db_Table::getTableDescriptionFromCache('dbmail_users', $this->_db); if ((isset($columns['tine20_userid']) || array_key_exists('tine20_userid', $columns)) && (isset($columns['tine20_clientid']) || array_key_exists('tine20_clientid', $columns))) { $this->_hasTine20Userid = true; $this->_propertyMapping['emailUserId'] = 'tine20_userid'; $this->_propertyMapping['emailGID'] = 'tine20_clientid'; } $this->_clientId = Tinebase_Application::getInstance()->getApplicationByName('Tinebase')->getId(); $this->_config['emailGID'] = Tinebase_Application::getInstance()->getApplicationByName('Tinebase')->getId(); }
/** * update to 8.1 * * @see 0009152: saving of record fails because of too many relations */ public function update_0() { $valueFields = array('old_value', 'new_value'); foreach ($valueFields as $field) { // check schema, only change if type == text $typeMapping = $this->_backend->getTypeMapping('text'); $schema = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . 'timemachine_modlog', $this->_backend->getDb()); if ($schema[$field]['DATA_TYPE'] === $typeMapping['defaultType']) { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Old column type (' . $schema[$field]['DATA_TYPE'] . ') is going to be altered to clob'); } $declaration = new Setup_Backend_Schema_Field_Xml(' <field> <name>' . $field . '</name> <type>clob</type> </field> '); $this->_backend->alterCol('timemachine_modlog', $declaration); } } $this->setTableVersion('timemachine_modlog', '3'); $this->setApplicationVersion('Tinebase', '8.1'); }
/** * purge tables * * @param $orderedTables * @param $where */ protected function _purgeTables($orderedTables, $where) { foreach ($orderedTables as $table) { try { $schema = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $table); } catch (Zend_Db_Statement_Exception $zdse) { echo "\nCould not get schema (" . $zdse->getMessage() . "). Skipping table {$table}"; continue; } if (!(isset($schema['is_deleted']) || array_key_exists('is_deleted', $schema)) || !(isset($schema['deleted_time']) || array_key_exists('deleted_time', $schema))) { continue; } $deleteCount = 0; try { $deleteCount = Tinebase_Core::getDb()->delete(SQL_TABLE_PREFIX . $table, $where); } catch (Zend_Db_Statement_Exception $zdse) { echo "\nFailed to purge deleted records for table {$table}. " . $zdse->getMessage(); } if ($deleteCount > 0) { echo "\nCleared table {$table} (deleted {$deleteCount} records)."; } else { echo "\nNothing to purge from {$table}"; } } }
/** * Public service for grouping treatment * * @param Zend_Db_Select $select */ public static function traitGroup(Zend_Db_Select $select) { // not needed for MySQL backends if ($select->getAdapter() instanceof Zend_Db_Adapter_Pdo_Mysql) { return; } $group = $select->getPart(Zend_Db_Select::GROUP); if (empty($group)) { return; } $columns = $select->getPart(Zend_Db_Select::COLUMNS); $updatedColumns = array(); //$column is an array where 0 is table, 1 is field and 2 is alias foreach ($columns as $key => $column) { if ($column[1] instanceof Zend_Db_Expr) { if (preg_match('/^\\(.*\\)/', $column[1])) { $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]); } else { $updatedColumns[] = $column; } continue; } if (preg_match('/^\\(.*\\)/', $column[1])) { $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]); continue; } // resolve * to single columns if ($column[1] == '*') { $tableFields = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $column[0], $select->getAdapter()); foreach ($tableFields as $columnName => $schema) { // adds columns into group by clause (table.field) // checks if field has a function (that must be an aggregation) $fieldName = "{$column[0]}.{$columnName}"; if (in_array($fieldName, $group)) { $updatedColumns[] = array($column[0], $fieldName, $columnName); } else { // any selected field which is not in the group by clause must have an aggregate function // we choose MIN() as default. In practice the affected columns will have only one value anyways. $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $columnName); } } continue; } $fieldName = $column[0] . '.' . $column[1]; if (in_array($fieldName, $group)) { $updatedColumns[] = $column; } else { // any selected field which is not in the group by clause must have an aggregate function // we choose MIN() as default. In practice the affected columns will have only one value anyways. $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $column[2] ? $column[2] : $column[1]); } } $select->reset(Zend_Db_Select::COLUMNS); foreach ($updatedColumns as $column) { $select->columns(!empty($column[2]) ? array($column[2] => $column[1]) : $column[1], $column[0]); } // add order by columns to group by $order = $select->getPart(Zend_Db_Select::ORDER); foreach ($order as $column) { $field = $column[0]; if (preg_match('/.*\\..*/', $field) && !in_array($field, $group)) { // adds column into group by clause (table.field) $group[] = $field; } } $select->reset(Zend_Db_Select::GROUP); $select->group($group); }
/** * checks if use table already has modlog fields * * @return bool */ protected function _userTableHasModlogFields() { $schema = Tinebase_Db_Table::getTableDescriptionFromCache($this->_db->table_prefix . $this->_tableName, $this->_db); return isset($schema['creation_time']); }
/** * rename or redefines column/field in database table * * @param string tableName * @param Setup_Backend_Schema_Field declaration * @param string old column/field name */ public function alterCol($_tableName, Setup_Backend_Schema_Field_Abstract $_declaration, $_oldName = NULL) { $columns = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $_tableName); $quotedName = $this->_db->quoteIdentifier($_declaration->name); foreach ($columns as $column) { // first we need to rename column because use some column name if ($column['COLUMN_NAME'] == $_declaration->name) { $tempName = $_declaration->name . Tinebase_Record_Abstract::generateUID(3); $this->_renameCol($_tableName, $_declaration->name, $tempName); // add new column $this->addCol($_tableName, $_declaration); $updatevalue = 'UPDATE ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName) . ' SET ' . $quotedName . ' = ' . $this->_db->quoteIdentifier($tempName); $this->execQueryVoid($updatevalue); $this->dropCol($_tableName, $tempName); return; } } if (isset($_oldName) && $_oldName != $_declaration->name) { $this->_renameCol($_tableName, $_oldName, $_declaration->name); } $statement = "ALTER TABLE " . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName) . " MODIFY "; $oldName = $_oldName; if ($_oldName == NULL) { $oldName = SQL_TABLE_PREFIX . $_declaration->name; } $statement .= $this->getFieldDeclarations($_declaration, $_tableName); $this->execQueryVoid($statement); }
/** * update modlog seq * * @param string $model * @param string $recordTable */ public static function updateModlogSeq($model, $recordTable) { if (!class_exists($model)) { throw new Setup_Exception('Could not find model class'); } if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) { Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Fetching modlog records for ' . $model); } // check if modlog table already has seq col $db = Tinebase_Core::getDb(); $modlogTable = SQL_TABLE_PREFIX . 'timemachine_modlog'; $modlogTableColumns = Tinebase_Db_Table::getTableDescriptionFromCache($modlogTable, $db); if (!(isset($modlogTableColumns['seq']) || array_key_exists('seq', $modlogTableColumns))) { throw new Tinebase_Exception_SystemGeneric('You need to update Tinebase before updating any other application'); } // fetch modlog records for model $sql = "SELECT DISTINCT record_id,modification_time,seq " . "FROM {$modlogTable} WHERE record_type ='{$model}' " . "ORDER BY modification_time ASC "; if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' SQL for fetching modlogs: ' . $sql); } $result = $db->fetchAll($sql); if (empty($result)) { if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) { Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' No modlog records found for ' . $model); } return; } $recordSeqs = array(); $updateSeqs = array(); // collect modlog data foreach ($result as $modification) { if ($modification['seq'] != 0) { $recordSeqs[$modification['record_id']] = $modification['seq']; continue; } if (!isset($recordSeqs[$modification['record_id']])) { $seq = $recordSeqs[$modification['record_id']] = 1; } else { $seq = ++$recordSeqs[$modification['record_id']]; } if (!isset($updateSeqs[$seq])) { $updateSeqs[$seq] = array(); } $updateSeqs[$seq][] = array('record_id' => $modification['record_id'], 'modification_time' => $modification['modification_time']); } if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) { Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Found ' . count($recordSeqs) . ' (different seqs: ' . count($updateSeqs) . ') records for modlog sequence update.'); } // update modlog foreach ($updateSeqs as $seq => $modsBySeq) { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Updating ' . count($modsBySeq) . ' modification(s) to seq ' . $seq); } $updateData = array('seq' => $seq); $i = 0; while ($i < count($modsBySeq)) { $whereArray = array(); // step by 1000 for ($j = 0; $j < 1000 && $i + $j < count($modsBySeq); $j++) { $whereArray[] = '(' . $db->quoteInto('record_id = ?', $modsBySeq[$i + $j]['record_id']) . ' AND ' . $db->quoteInto('modification_time = ?', $modsBySeq[$i + $j]['modification_time']) . ')'; } if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Stepping from ' . $i . ' to ' . ($i + $j) . '(' . count($whereArray) . ' mods)'); } if (count($whereArray) > 0) { $where = implode(' OR ', $whereArray); $db->update($modlogTable, $updateData, $where); } $i += $j; } } // update records $maxSeqs = array(); foreach ($recordSeqs as $recordId => $maxSeq) { $maxSeqs[$maxSeq][] = (string) $recordId; } foreach ($maxSeqs as $maxSeq => $recordIds) { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Setting max seq to ' . $maxSeq . ' for ' . count($recordIds) . ' record(s).'); } $updateData = array('seq' => $maxSeq); $where = $db->quoteInto($db->quoteIdentifier('id') . ' IN (?)', (array) $recordIds); try { $db->update(SQL_TABLE_PREFIX . $recordTable, $updateData, $where); } catch (Zend_Db_Statement_Exception $zdse) { if (Tinebase_Core::isLogLevel(Zend_Log::WARN)) { Tinebase_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' Could not update record seq: ' . $zdse->getMessage()); } if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Data: ' . print_r($updateData, TRUE) . ' Where: ' . substr($where, 0, 256)); } } } if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) { Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Finished modlog sequence update for ' . $model); } }
/** * Inserts a table row with specified data. * Add support for CLOB/BLOB * Oracle does not support anonymous ('?') binds. * * @param mixed $table The table to insert data into. * @param array $bind Column-value pairs. * @return int The number of affected rows. */ public function insert($table, array $bind) { // Get the table metadata $columns = Tinebase_Db_Table::getTableDescriptionFromCache($table); // Check the columns in the array against the database table // to identify BLOB (or CLOB) columns foreach (array_keys($bind) as $column) { if (in_array($columns[$column]['DATA_TYPE'], array('BLOB', 'CLOB'))) { $lobs[] = $column; } } // If there are no blob columns then use the normal insert procedure if (!isset($lobs)) { $i = 0; // extract and quote col names from the array keys $cols = array(); $vals = array(); foreach ($bind as $col => $val) { $cols[] = $this->quoteIdentifier($col, true); if ($val instanceof Zend_Db_Expr) { $vals[] = $val->__toString(); unset($bind[$col]); } else { // MOD: add to_date for date column if ($val === date('Y-m-d H:i:s', strtotime($val))) { $vals[] = "TO_DATE(" . ':' . $col . $i . ",'YYYY-MM-DD hh24:mi:ss')"; } else { $vals[] = ':' . $col . $i; } unset($bind[$col]); $bind[':' . $col . $i] = $val; } $i++; } // build the statement $sql = "INSERT INTO " . $this->quoteIdentifier($table, true) . ' (' . implode(', ', $cols) . ') ' . 'VALUES (' . implode(', ', $vals) . ')'; // execute the statement and return the number of affected rows $stmt = $this->query($sql, $bind); $result = $stmt->rowCount(); } else { // There are blobs in the $bind array so insert them separately $ociTypes = array('BLOB' => OCI_B_BLOB, 'CLOB' => OCI_B_CLOB); // Extract and quote col names from the array keys $i = 0; $cols = array(); $vals = array(); $lobData = array(); $returning = array(); foreach ($bind as $col => $val) { $cols[] = $this->quoteIdentifier($col, true); if (in_array($col, $lobs)) { $lobs[array_search($col, $lobs)] = $this->quoteIdentifier($col, true); $vals[] = 'EMPTY_' . $columns[$col]['DATA_TYPE'] . '()'; $lobData[':' . $col . $i] = array('ociType' => $ociTypes[$columns[$col]['DATA_TYPE']], 'data' => $val); unset($bind[$col]); $lobDescriptors[':' . $col . $i] = oci_new_descriptor($this->_connection, OCI_D_LOB); $returning[] = ':' . $col . $i; $bind[':' . $col . $i] = $lobDescriptors[':' . $col . $i]; } elseif ($val instanceof Zend_Db_Expr) { $vals[] = $val->__toString(); unset($bind[$col]); } else { $vals[] = ':' . $col . $i; unset($bind[$col]); $bind[':' . $col . $i] = $val; } $i++; } // build the statement $sql = "INSERT INTO " . $this->quoteIdentifier($table, true) . ' (' . implode(', ', $cols) . ') ' . 'VALUES (' . implode(', ', $vals) . ') ' . 'RETURNING ' . implode(', ', $lobs) . ' ' . 'INTO ' . implode(', ', $returning); //Tinebase_Core::getLogger()->debug("SQL INSERT\n" . $sql); // Execute the statement $stmt = new Zend_Db_Statement_Oracle($this, $sql); foreach (array_keys($bind) as $name) { if (in_array($name, array_keys($lobData))) { $stmt->bindParam($name, $bind[$name], $lobData[$name]['ociType'], -1); } else { $stmt->bindParam($name, $bind[$name]); } } $this->_setExecuteMode(OCI_DEFAULT); //Execute without committing $stmt->execute(); $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS); $result = $stmt->rowCount(); // Write the LOB data & free the descriptor if (isset($lobDescriptors)) { foreach ($lobDescriptors as $name => $lobDescriptor) { $lobDescriptor->write($lobData[$name]['data']); $lobDescriptor->free(); } } } return $result; }
/** * returns the db schema * * @return array */ public function getSchema() { return Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . 'notes', $this->_db); }
/** * checks if a given column {@param $_columnName} exists in table {@param $_tableName}. * * @param string $_columnName * @param string $_tableName * @return boolean */ public function columnExists($_columnName, $_tableName) { $columns = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $_tableName, $this->_db); return isset($columns[$_columnName]) || array_key_exists($_columnName, $columns); }
/** * fetch creation time of the first/oldest user * * @return Tinebase_DateTime */ public function getFirstUserCreationTime() { $schema = Tinebase_Db_Table::getTableDescriptionFromCache($this->_db->table_prefix . $this->_tableName, $this->_db); $fallback = new Tinebase_DateTime('2014-12-01'); if (!isset($schema['creation_time'])) { 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; }
/** * purge deleted records * * if param date is given (for example: date=2010-09-17), all records before this date are deleted (if the table has a date field) * if table names are given, purge only records from this tables * * @param $_opts * @return boolean success */ public function purgeDeletedRecords(Zend_Console_Getopt $_opts) { if (!$this->_checkAdminRight()) { return FALSE; } $args = $this->_parseArgs($_opts, array(), 'tables'); if (!(isset($args['tables']) || array_key_exists('tables', $args)) || empty($args['tables'])) { echo "No tables given.\nPurging records from all tables!\n"; $args['tables'] = $this->_getAllApplicationTables(); } $db = Tinebase_Core::getDb(); if (isset($args['date']) || array_key_exists('date', $args)) { echo "\nRemoving all deleted entries before {$args['date']} ..."; $where = array($db->quoteInto($db->quoteIdentifier('deleted_time') . ' < ?', $args['date'])); } else { echo "\nRemoving all deleted entries ..."; $where = array(); } $where[] = $db->quoteInto($db->quoteIdentifier('is_deleted') . ' = ?', 1); foreach ($args['tables'] as $table) { try { $schema = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $table); } catch (Zend_Db_Statement_Exception $zdse) { echo "\nCould not get schema (" . $zdse->getMessage() . "). Skipping table {$table}"; continue; } if (!(isset($schema['is_deleted']) || array_key_exists('is_deleted', $schema)) || !(isset($schema['deleted_time']) || array_key_exists('deleted_time', $schema))) { continue; } $deleteCount = 0; try { $deleteCount = $db->delete(SQL_TABLE_PREFIX . $table, $where); } catch (Zend_Db_Statement_Exception $zdse) { echo "\nFailed to purge deleted records for table {$table}. " . $zdse->getMessage(); } if ($deleteCount > 0) { echo "\nCleared table {$table} (deleted {$deleteCount} records)."; } } echo "\n\n"; return TRUE; }