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);
}
