function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$prefix = $GLOBALS['_MAX']['CONF']['table']['prefix'];
if ($this->oDbh->dbsyntax == 'pgsql') {
$oTable =& $this->oUpgrade->oDBUpgrader->oTable;
foreach ($oTable->aDefinition['tables'] as $tableName => $aTable) {
foreach ($aTable['fields'] as $fieldName => $aField) {
if (!empty($aField['autoincrement'])) {
// Check actual sequence name
$oldSequenceName = $this->getLinkedSequence($prefix . $tableName, $fieldName);
if ($oldSequenceName) {
$newSequenceName = OA_DB::getSequenceName($this->oDbh, $tableName, $fieldName);
if ($oldSequenceName != $newSequenceName) {
$this->logOnly("Non standard sequence name found: " . $oldSequenceName);
$qTable = $this->oDbh->quoteIdentifier($prefix . $tableName, true);
$qField = $this->oDbh->quoteIdentifier($fieldName, true);
$qOldSequence = $this->oDbh->quoteIdentifier($oldSequenceName, true);
$qNewSequence = $this->oDbh->quoteIdentifier($newSequenceName, true);
OA::disableErrorHandling();
$result = $this->oDbh->exec("ALTER TABLE {$qOldSequence} RENAME TO {$qNewSequence}");
if (PEAR::isError($result)) {
if ($result->getCode() == MDB2_ERROR_ALREADY_EXISTS) {
$result = $this->oDbh->exec("DROP SEQUENCE {$qNewSequence}");
if (PEAR::isError($result)) {
$this->logError("Could not drop existing sequence {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$result = $this->oDbh->exec("ALTER TABLE {$qOldSequence} RENAME TO {$qNewSequence}");
}
}
if (PEAR::isError($result)) {
$this->logError("Could not rename {$oldSequenceName} to {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$result = $this->oDbh->exec("ALTER TABLE {$qTable} ALTER {$qField} SET DEFAULT nextval(" . $this->oDbh->quote($qNewSequence) . ")");
if (PEAR::isError($result)) {
$this->logError("Could not set column default to sequence {$newSequenceName}: " . $result->getUserInfo());
return false;
}
OA::enableErrorHandling();
$result = $oTable->resetSequenceByData($tableName, $fieldName);
if (PEAR::isError($result)) {
$this->logError("Could not reset sequence value for {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$this->logOnly("Successfully renamed {$oldSequenceName} to {$newSequenceName}");
}
} else {
$this->logOnly("No sequence found for {$tableName}.{$fieldName}");
}
}
}
}
}
return true;
}
/**
* Insert the current objects variables into the database
*
* Returns the ID of the inserted element (if auto increment or sequences are used.)
*
* for example
*
* Designed to be extended
*
* $object = new mytable();
* $object->name = "fred";
* echo $object->insert();
*
* @access public
* @return mixed false on failure, int when auto increment or sequence used, otherwise true on success
*/
function insert()
{
global $_DB_DATAOBJECT;
// we need to write to the connection (For nextid) - so us the real
// one not, a copyied on (as ret-by-ref fails with overload!)
if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
$this->_connect();
}
$quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
$DB =& $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
$items = isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table]) ? $_DB_DATAOBJECT['INI'][$this->_database][$this->__table] : $this->table();
if (!$items) {
$this->raiseError("insert:No table definition for {$this->__table}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
return false;
}
$options =& $_DB_DATAOBJECT['CONFIG'];
$datasaved = 1;
$leftq = '';
$rightq = '';
$seqKeys = isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table]) ? $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] : $this->sequenceKey();
$key = isset($seqKeys[0]) ? $seqKeys[0] : false;
$useNative = isset($seqKeys[1]) ? $seqKeys[1] : false;
$seq = isset($seqKeys[2]) ? $seqKeys[2] : false;
$dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn["phptype"];
// nativeSequences or Sequences..
// big check for using sequences
if ($key !== false && !$useNative) {
if (!$seq) {
// Since recent versions OA_DB is capable to generate sequence names for nextId
// with both MySQL and PgSQL compatibility
$seq = OA_DB::getSequenceName($DB, $this->_tableName, $key, false);
$keyvalue = $DB->nextId($seq);
} else {
$f = $DB->getOption('seqname_format');
$DB->setOption('seqname_format', '%s');
$keyvalue = $DB->nextId($seq);
$DB->setOption('seqname_format', $f);
}
if (PEAR::isError($keyvalue)) {
$this->raiseError($keyvalue->toString(), DB_DATAOBJECT_ERROR_INVALIDCONFIG);
return false;
}
$this->{$key} = $keyvalue;
}
foreach ($items as $k => $v) {
// if we are using autoincrement - skip the column...
if ($key && $k == $key && $useNative) {
continue;
}
if (!isset($this->{$k})) {
continue;
}
// dont insert data into mysql timestamps
// use query() if you really want to do this!!!!
if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
continue;
}
if ($leftq) {
$leftq .= ', ';
$rightq .= ', ';
}
$leftq .= $quoteIdentifiers ? $DB->quoteIdentifier($k) . ' ' : "{$k} ";
if (is_a($this->{$k}, 'DB_DataObject_Cast')) {
$value = $this->{$k}->toString($v, $DB);
if (PEAR::isError($value)) {
$this->raiseError($value->toString(), DB_DATAOBJECT_ERROR_INVALIDARGS);
return false;
}
$rightq .= $value;
continue;
}
if (is_string($this->{$k}) && strtolower($this->{$k}) === 'null' && !($v & DB_DATAOBJECT_NOTNULL)) {
$rightq .= " NULL ";
continue;
}
// DATE is empty... on a col. that can be null..
// note: this may be usefull for time as well..
if (!$this->{$k} && ($v & DB_DATAOBJECT_DATE || $v & DB_DATAOBJECT_TIME) && !($v & DB_DATAOBJECT_NOTNULL)) {
$rightq .= " NULL ";
continue;
}
if ($v & DB_DATAOBJECT_STR) {
$rightq .= $this->_quote((string) ($v & DB_DATAOBJECT_BOOL ? $this->{$k} === 'f' ? 0 : (int) (bool) $this->{$k} : $this->{$k})) . " ";
continue;
}
if (is_numeric($this->{$k})) {
$rightq .= " {$this->{$k}} ";
continue;
}
/* flag up string values - only at debug level... !!!??? */
if (is_object($this->{$k}) || is_array($this->{$k})) {
$this->debug('ODD DATA: ' . $k . ' ' . print_r($this->{$k}, true), 'ERROR');
}
// at present we only cast to integers
// - V2 may store additional data about float/int
$rightq .= ' ' . intval($this->{$k}) . ' ';
}
// not sure why we let empty insert here.. - I guess to generate a blank row..
if ($leftq || $useNative) {
$table = $quoteIdentifiers ? $DB->quoteIdentifier($this->__table) : $this->__table;
$r = $this->_query("INSERT INTO {$table} ({$leftq}) VALUES ({$rightq}) ");
if (PEAR::isError($r)) {
$this->raiseError($r);
return false;
}
if ($r < 1) {
return 0;
}
// now do we have an integer key!
if ($key && $useNative) {
switch ($dbtype) {
case 'mysql':
case 'mysqli':
$method = "{$dbtype}_insert_id";
$this->{$key} = $method($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection);
break;
case 'mssql':
// note this is not really thread safe - you should wrapp it with
// transactions = eg.
// $db->query('BEGIN');
// $db->insert();
// $db->query('COMMIT');
$mssql_key = $DB->getOne("SELECT @@IDENTITY");
if (PEAR::isError($mssql_key)) {
$this->raiseError($r);
return false;
}
$this->{$key} = $mssql_key;
break;
case 'pgsql':
if (!$seq) {
$seq = $DB->getSequenceName($this->__table);
}
$pgsql_key = $DB->getOne("SELECT currval('" . $seq . "')");
if (PEAR::isError($pgsql_key)) {
$this->raiseError($r);
return false;
}
$this->{$key} = $pgsql_key;
break;
case 'ifx':
$this->{$key} = array_shift(ifx_fetch_row(ifx_query("select DBINFO('sqlca.sqlerrd1') FROM systables where tabid=1", $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection, IFX_SCROLL), "FIRST"));
break;
}
}
if (isset($_DB_DATAOBJECT['CACHE'][strtolower(get_class($this))])) {
$this->_clear_cache();
}
if ($key) {
return $this->{$key};
}
return true;
}
$this->raiseError("insert: No Data specifed for query", DB_DATAOBJECT_ERROR_NODATA);
return false;
}
function testGetSequenceName()
{
$conf =& $GLOBALS['_MAX']['CONF'];
$prefix = $conf['table']['prefix'] = 'ox_';
$oDbh =& OA_DB::singleton();
if ($oDbh->dbsyntax == 'pgsql') {
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'x', 'a'), 'ox_x_a_seq');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxyy', 'a'), 'ox_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_a_seq');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'x', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabb'), 'ox_x_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa_seq');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'xxxxxxxxxxxxxxxxxxxxxxxxxxy', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaab'), 'ox_xxxxxxxxxxxxxxxxxxxxxxxxxx_aaaaaaaaaaaaaaaaaaaaaaaaaaaaa_seq');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'x', 'a', false), 'ox_x_a');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxyy', 'a', false), 'ox_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_a');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'x', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabb', false), 'ox_x_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'xxxxxxxxxxxxxxxxxxxxxxxxxxy', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaab', false), 'ox_xxxxxxxxxxxxxxxxxxxxxxxxxx_aaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
} else {
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'x', 'a'), 'x');
$this->assertEqual(OA_DB::getSequenceName($oDbh, 'x', 'a', false), 'x');
}
TestEnv::restoreConfig();
}
/**
* Resets the sequence value for a given table and its id field to the
* maximum value currently in the table. This way after upgrade the
* sequence should be ready to use for inserting new campaigns, websites...
* This function have effect only on PostgreSQL. It does nothing when
* called on a different database.
*
* On database error the function logs an error and returns false.
*
* @param string $table Name of the table (without prefix)
* @param string $field Name of the id field (eg. affiliateid, campaignid)
* @return PEAR_Error True on success, PEAR_Error object on failure.
*/
function resetSequenceByData($table, $field)
{
if ($this->oDbh->dbsyntax == 'pgsql') {
$prefix = $this->getPrefix();
$tableName = $prefix . $table;
$sequenceName = OA_DB::getSequenceName($this->oDbh, $table, $field);
$qSeq = $this->oDbh->quoteIdentifier($sequenceName, true);
$qFld = $this->oDbh->quoteIdentifier($field, true);
$qTbl = $this->oDbh->quoteIdentifier($tableName, true);
$sql = "SELECT setval(" . $this->oDbh->quote($qSeq) . ", MAX({$qFld})) FROM {$qTbl}";
$result = $this->oDbh->exec($sql);
if (PEAR::isError($result)) {
return $result;
}
}
return true;
}
