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