function alterDatabaseActionTable() { $this->oSchema = MDB2_Schema::factory(OA_DB::singleton(OA_DB::getDsn())); $prefix = $GLOBALS['_MAX']['CONF']['table']['prefix']; OA_DB::setCaseSensitive(); $aPrev = $this->oSchema->getDefinitionFromDatabase(array($prefix . 'database_action')); OA_DB::disableCaseSensitive(); $aCurr = $this->_getLatestDatabaseActionSchema($prefix); $aChanges = $this->oSchema->compareDefinitions($aCurr, $aPrev); if (is_array($aChanges) && count($aChanges) > 0) { if (isset($aChanges['tables']['change'][$prefix . 'database_action'])) { if (isset($aChanges['tables']['change'][$prefix . 'database_action']['indexes']['add']['database_action_pkey'])) { unset($aChanges['tables']['change'][$prefix . 'database_action']['indexes']['add']['database_action_pkey']); unset($aChanges['tables']['change'][$prefix . 'database_action']['indexes']['change']); } if (isset($aChanges['tables']['change'][$prefix . 'database_action']['add']['database_action_id'])) { $result = $this->oSchema->alterDatabase($aCurr, $aPrev, $aChanges); if (PEAR::isError($result)) { $this->oUpgrade->oLogger->logError($result->getUserInfo()); return false; } $this->oUpgrade->oLogger->log('database_action table schema successfully upgraded'); return true; } } } $this->oUpgrade->oLogger->log('database_action table schema upgrade unnecessary'); return true; }
function removeDashboardColumns() { $this->oSchema = MDB2_Schema::factory(OA_DB::singleton(OA_DB::getDsn())); $prefix = $GLOBALS['_MAX']['CONF']['table']['prefix']; $table = 'preference'; $aColumns = array('ad_clicks_sum', 'ad_views_sum', 'ad_clicks_per_second', 'ad_views_per_second', 'ad_cs_data_last_sent', 'ad_cs_data_last_sent', 'ad_cs_data_last_received'); OA_DB::setCaseSensitive(); $aDef = $this->oSchema->getDefinitionFromDatabase(array($prefix . $table)); OA_DB::disableCaseSensitive(); if (is_array($aDef) && count($aDef) > 0) { $aTask['remove'] = array(); if (isset($aDef['tables'][$prefix . $table])) { foreach ($aColumns as $column) { if (isset($aDef['tables'][$prefix . $table]['fields'][$column])) { $aTask['remove'][$column] = array(); $this->oUpgrade->oLogger->logOnly("preference.{$column} found"); } } } if (count($aTask['remove'] > 0)) { $result = $this->oSchema->db->manager->alterTable($prefix . $table, $aTask, false); } } $this->oUpgrade->oLogger->logOnly('preference table schema upgrade for dashboard unnecessary'); return true; }
/** * Resets a (postgresql) sequence to 1 * similar to OA_DB_Table::resetSequence() * DOESN'T SEEM TO WORK THO * * @param string $sequence the name of the sequence to reset * @return boolean true on success, false otherwise */ function resetSequence($tableName) { $aConf = $GLOBALS['_MAX']['CONF']; $oDbh = OA_DB::singleton(); if ($aConf['database']['type'] == 'pgsql') { OA_DB::setCaseSensitive(); $aSequences = $oDbh->manager->listSequences(); OA_DB::disableCaseSensitive(); if (is_array($aSequences)) { OA::debug('Resetting sequence ' . $sequence, PEAR_LOG_DEBUG); OA::disableErrorHandling(null); $tableName = substr($aConf['table']['prefix'] . $tableName, 0, 29) . '_'; foreach ($aSequences as $k => $sequence) { if (strpos($sequence, $tableName) === 0) { $sequence = $oDbh->quoteIdentifier($sequence . '_seq', true); $result = $oDbh->exec("SELECT setval('{$sequence}', 1, false)"); break; } } OA::enableErrorHandling(); if (PEAR::isError($result)) { OA::debug('Unable to reset sequence on table ' . $tableName, PEAR_LOG_ERR); return false; } } } else { if ($aConf['database']['type'] == 'mysql') { $tableName = $aConf['table']['prefix'] . $tableName; OA::disableErrorHandling(); $result = $oDbh->exec("ALTER TABLE {$tableName} AUTO_INCREMENT = 1"); OA::enableErrorHandling(); if (PEAR::isError($result)) { OA::debug('Unable to reset sequence on table ' . $tableName, PEAR_LOG_ERR); return false; } } } return true; }
function _dropTestTables($oDbh) { $conf =& $GLOBALS['_MAX']['CONF']; $conf['table']['split'] = false; $oTable = new OA_DB_Table(); $oTable->init($this->path . 'schema_test_original.xml'); $aExistingTables = OA_DB_Table::listOATablesCaseSensitive(); OA_DB::setCaseSensitive(); if ($this->_tableExists('table1', $aExistingTables)) { $this->assertTrue($oTable->dropTable($this->prefix . 'table1'), 'error dropping test table1'); } if ($this->_tableExists('table2', $aExistingTables)) { $this->assertTrue($oTable->dropTable($this->prefix . 'table2'), 'error dropping test table2'); } OA_DB::disableCaseSensitive(); $aExistingTables = OA_DB_Table::listOATablesCaseSensitive(); $this->assertFalse($this->_tableExists('table1', $aExistingTables), '_dropTestTables'); $this->assertFalse($this->_tableExists('table2', $aExistingTables), '_dropTestTables'); }
/** * @todo make this useful * * perform a database integrity check * perform a dataobject integrity check * * @param string $name * @return boolean */ public function checkDatabase($name, &$aGroup) { $aResult = array(); require_once MAX_PATH . '/lib/OA/Upgrade/DB_Upgrade.php'; $oDBUpgrader = $this->_instantiateClass('OA_DB_Upgrade'); $schema = $aGroup['schema_name']; if ($schema) { $oDBUpgrader->schema = $schema; $oDBUpgrader->file_schema = $this->getFilePathToMDB2Schema($name, $schema); $enabled = $this->isEnabled($name); if (!$enabled) { $this->enableComponentGroup($name, $aGroup['extends']); } $oDBUpgrader->buildSchemaDefinition(); foreach ($oDBUpgrader->oTable->aDefinition['tables'] as $table => &$aDef) { $aParams = array($oDBUpgrader->prefix . $table); OA_DB::setCaseSensitive(); $aObjects[$name]['def'] = $oDBUpgrader->oSchema->getDefinitionFromDatabase($aParams); OA_DB::disableCaseSensitive(); $aObjects[$name]['dif'] = $oDBUpgrader->oSchema->compareDefinitions(array('tables' => array($table => $aDef)), $aObjects[$name]['def']); $aObjects[$name]['dbo'] = OA_Dal::factoryDO($table); if (count($aObjects[$name]['dif']['tables'])) { $aResult[$table]['schema'] = 'ERROR: schema differences found, details in debug.log'; $this->_logError(print_r($aObjects[$name]['dif']['tables'], true), PEAR_LOG_ERR); } else { $aResult[$table]['schema'] = 'OK'; } if (!is_a($aObjects[$name]['dbo'], 'DataObjects_' . ucfirst($table)) || !is_a($aObjects[$name]['dbo'], 'DB_DataObjectCommon')) { $aResult[$table]['dataobject'] = 'ERROR: dataobject problems found, details in debug.log '; if (!is_a($aObjects[$name]['dbo'], 'DataObjects_' . ucfirst($table))) { $this->_logError('Dataobject classname mismatch ' . get_class($aObjects[$name]['dbo']) . ' should be DataObjects_' . ucfirst($table), PEAR_LOG_ERR); } if (!is_a($aObjects[$name]['dbo'], 'DB_DataObjectCommon')) { $this->_logError('Dataobject classtype mismatch ' . get_class($aObjects[$name]['dbo']) . ' is not a DataObjectCommon', PEAR_LOG_ERR); } } else { $aResult[$table]['dataobject'] = 'OK'; } foreach ($aObjects[$name]['def']['tables'][$table]['fields'] as $field => &$aField) { if (!property_exists($aObjects[$name]['dbo'], $field)) { $aResult[$table]['dataobject'] = 'ERROR: dataobject problems found, details in debug.log '; $this->_logError('DataObject class definition mismatch ' . get_class($aObjects[$name]['dbo']) . '::' . $field . ' not found', PEAR_LOG_ERR); $this->_logError(print_r($aObjects[$name]['dbo'], true), PEAR_LOG_ERR); $this->_logError(print_r($aField, true), PEAR_LOG_ERR); } } } if (!$enabled) { $this->disableComponentGroup($name, $aGroup['extends']); } } return $aResult; }
/** * test reseting of all sequences * * @return boolean true on success, false otherwise */ function test_resetAllSequences() { $oDbh =& OA_DB::singleton(); // if ($oDbh->dbsyntax == 'pgsql') // { // $sequence = 'test_table1_test_id1_seq'; // } // else if ($oDbh->dbsyntax == 'mysql') // { // $sequence = 'test_table1'; // } $conf =& $GLOBALS['_MAX']['CONF']; $conf['table']['prefix'] = ''; $oTable = new OA_DB_Table(); $this->_writeSequenceTestDatabaseSchema(); $oTable->init(MAX_PATH . '/var/test.xml'); $oTable->createAllTables(); $aExistingTables = OA_DB_Table::listOATablesCaseSensitive(); $this->assertEqual($aExistingTables[0], 'test_table1'); $this->assertEqual($aExistingTables[1], 'test_table2'); if ($oDbh->dbsyntax == 'pgsql') { OA_DB::setCaseSensitive(); $aSequences = $oDbh->manager->listSequences(); OA_DB::disableCaseSensitive(); $this->assertEqual($aSequences[0], 'test_table1_test_id1'); $this->assertEqual($aSequences[1], 'test_table2_test_id2'); } // table1 for ($i = 1; $i < 11; $i++) { $query = "INSERT INTO " . $oDbh->quoteIdentifier('test_table1', true) . " (test_desc1) VALUES ('{$i}')"; $oDbh->query($query); } $query = "SELECT * FROM " . $oDbh->quoteIdentifier('test_table1', true); $aRows = $oDbh->queryAll($query); $this->assertEqual(count($aRows), 10, 'incorrect number of rows in test_table1'); reset($aRows); foreach ($aRows as $k => $v) { $this->assertTrue($v['test_id1'] == $v['test_desc1'], 'sequence problem with new table'); } $query = "DELETE FROM " . $oDbh->quoteIdentifier('test_table1', true); $oDbh->query($query); $query = "SELECT * FROM " . $oDbh->quoteIdentifier('test_table1', true); $aRows = $oDbh->queryAll($query); $this->assertEqual(count($aRows), 0, 'failed to delete rows from test_table1'); // table2 for ($i = 1; $i < 11; $i++) { $query = "INSERT INTO " . $oDbh->quoteIdentifier('test_table2', true) . " (test_desc2) VALUES ('{$i}')"; $oDbh->query($query); } $query = "SELECT * FROM " . $oDbh->quoteIdentifier('test_table2', true); $aRows = $oDbh->queryAll($query); $this->assertEqual(count($aRows), 10, 'incorrect number of rows in test_table2'); reset($aRows); foreach ($aRows as $k => $v) { $this->assertTrue($v['test_id2'] == $v['test_desc2'], 'sequence problem with new table'); } $query = "DELETE FROM " . $oDbh->quoteIdentifier('test_table2', true); $oDbh->query($query); $query = "SELECT * FROM " . $oDbh->quoteIdentifier('test_table2', true); $aRows = $oDbh->queryAll($query); $this->assertEqual(count($aRows), 0, 'failed to delete rows from test_table2'); $this->assertTrue($oTable->resetAllSequences(), 'failed to reset all sequences'); // table1 for ($i = 1; $i < 11; $i++) { $query = "INSERT INTO " . $oDbh->quoteIdentifier('test_table1', true) . " (test_desc1) VALUES ('{$i}')"; $oDbh->query($query); } $query = "SELECT * FROM " . $oDbh->quoteIdentifier('test_table1', true); $aRows = $oDbh->queryAll($query); $this->assertEqual(count($aRows), 10, 'incorrect number of rows in test_table1'); reset($aRows); foreach ($aRows as $k => $v) { $this->assertTrue($v['test_id1'] == $v['test_desc1'], 'sequence problem after reset: ' . $v['test_id1'] . '=>' . $v['test_desc1']); } $oTable->dropTable('test_table1'); // table2 for ($i = 1; $i < 11; $i++) { $query = "INSERT INTO " . $oDbh->quoteIdentifier('test_table2', true) . " (test_desc2) VALUES ('{$i}')"; $oDbh->query($query); } $query = "SELECT * FROM " . $oDbh->quoteIdentifier('test_table2', true); $aRows = $oDbh->queryAll($query); $this->assertEqual(count($aRows), 10, 'incorrect number of rows in test_table2'); reset($aRows); foreach ($aRows as $k => $v) { $this->assertTrue($v['test_id2'] == $v['test_desc2'], 'sequence problem after reset: ' . $v['test_id2'] . '=>' . $v['test_desc2']); } $oTable->dropTable('test_table2'); @unlink(MAX_PATH . '/var/test.xml'); }
/** * retrieve an array of table names from currently connected database * * @return array */ function _listBackups() { $aResult = array(); $prefix = $this->prefix . 'z_'; OA_DB::setCaseSensitive(); $aBakTables = OA_DB_Table::listOATablesCaseSensitive(); OA_DB::disableCaseSensitive(); $prelen = strlen($prefix); krsort($aBakTables); foreach ($aBakTables as $k => &$name) { // workaround for mdb2 problem "show table like" if (substr($name, 0, $prelen) == $prefix) { $name = str_replace($this->prefix, '', $name); $aInfo = $this->queryAuditForABackup($name); $aResult[$k]['backup_table'] = $name; $aResult[$k]['copied_table'] = $aInfo[0]['tablename']; $aResult[$k]['copied_date'] = $aInfo[0]['updated']; $aStatus = $this->getTableStatus($name); $aResult[$k]['data_length'] = $aStatus[0]['data_length'] / 1024; $aResult[$k]['rows'] = $aStatus[0]['rows']; } } return $aResult; }
/** * Resets all sequences * * @return boolean true on success, false otherwise */ function resetAllSequences() { $aConf = $GLOBALS['_MAX']['CONF']; if (!$this->_checkInit()) { return false; } $allSequencesReset = true; OA_DB::setCaseSensitive(); $aSequences = $this->oDbh->manager->listSequences(); OA_DB::disableCaseSensitive(); if (is_array($aSequences)) { $aTables = $this->aDefinition['tables']; if ($this->oDbh->dbsyntax == 'pgsql') { foreach ($aSequences as $sequence) { $match = false; foreach (array_keys($this->aDefinition['tables']) as $tableName) { $tableName = substr($aConf['table']['prefix'] . $tableName, 0, 29) . '_'; if (strpos($sequence, $tableName) === 0) { $match = true; break; } } if (!$match) { continue; } // listSequences returns sequence names without trailing '_seq' $sequence .= '_seq'; OA::debug('Resetting the ' . $sequence . ' sequence', PEAR_LOG_DEBUG); if (!$this->resetSequence($sequence)) { OA::debug('Unable to reset the sequence ' . $sequence, PEAR_LOG_ERR); $allSequencesReset = false; } } } else { if ($this->oDbh->dbsyntax == 'mysql') { foreach (array_keys($this->aDefinition['tables']) as $tableName) { if (!$this->resetSequence($tableName)) { OA::debug('Unable to reset the auto-increment for ' . $tableName, PEAR_LOG_ERR); $allSequencesReset = false; } } } } } return $allSequencesReset; }
/** * retrieve a schema definition * from one table as specified in parameter * or from all tables with openads prefix * the definitions then have the openads prefix stripped from them * to make the definition generic and suitable for schema comparison * * @param string $table * @return boolean */ function _getDefinitionFromDatabase($table = null) { $aParams = null; if ($table) { $aParams = array($this->prefix . $table); } else { $aParams = $this->_listTables(); } OA_DB::setCaseSensitive(); $aDef = $this->oSchema->getDefinitionFromDatabase($aParams); OA_DB::disableCaseSensitive(); if ($this->_isPearError($aDef, 'error getting database definition')) { return array(); } $aDef['prefixedTblNames'] = true; // we got this from the db so it has table prefixes $aDef['prefixedIdxNames'] = true; // we got this from the db so it has index prefixes $aDef['expandedIdxNames'] = true; // we got this from the db so it has long index names ( + tablename + indexname) return $this->_stripPrefixesFromDatabaseDefinition($aDef); }