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