/** * Method used to update rows in the DB. Rows are selected based * on selectCriteria and updated using values in updateValues. * <p> * Use this method for performing an update of the kind: * <p> * WHERE some_column = some value AND could_have_another_column = * another value AND so on. * * @param $selectCriteria A Criteria object containing values used in where * clause. * @param $updateValues A Criteria object containing values used in set * clause. * @param PropelPDO $con The PropelPDO connection object to use. * @return int The number of rows affected by last update statement. For most * uses there is only one update statement executed, so this number * will correspond to the number of rows affected by the call to this * method. Note that the return value does require that this information * is returned (supported) by the Propel db driver. * @throws PropelException */ public static function doUpdate(Criteria $selectCriteria, Criteria $updateValues, PropelPDO $con) { $db = Propel::getDB($selectCriteria->getDbName()); $dbMap = Propel::getDatabaseMap($selectCriteria->getDbName()); // Get list of required tables, containing all columns $tablesColumns = $selectCriteria->getTablesColumns(); if (empty($tablesColumns)) { $tablesColumns = array($selectCriteria->getPrimaryTableName() => array()); } // we also need the columns for the update SQL $updateTablesColumns = $updateValues->getTablesColumns(); $affectedRows = 0; // initialize this in case the next loop has no iterations. foreach ($tablesColumns as $tableName => $columns) { $whereClause = array(); $params = array(); $stmt = null; try { $sql = 'UPDATE '; if ($queryComment = $selectCriteria->getComment()) { $sql .= '/* ' . $queryComment . ' */ '; } // is it a table alias? if ($tableName2 = $selectCriteria->getTableForAlias($tableName)) { $udpateTable = $tableName2 . ' ' . $tableName; $tableName = $tableName2; } else { $udpateTable = $tableName; } if ($db->useQuoteIdentifier()) { $sql .= $db->quoteIdentifierTable($udpateTable); } else { $sql .= $udpateTable; } $sql .= " SET "; $p = 1; foreach ($updateTablesColumns[$tableName] as $col) { $updateColumnName = substr($col, strrpos($col, '.') + 1); // add identifiers for the actual database? if ($db->useQuoteIdentifier()) { $updateColumnName = $db->quoteIdentifier($updateColumnName); } if ($updateValues->getComparison($col) != Criteria::CUSTOM_EQUAL) { $sql .= $updateColumnName . '=:p' . $p++ . ', '; } else { $param = $updateValues->get($col); $sql .= $updateColumnName . ' = '; if (is_array($param)) { if (isset($param['raw'])) { $raw = $param['raw']; $rawcvt = ''; // parse the $params['raw'] for ? chars for ($r = 0, $len = strlen($raw); $r < $len; $r++) { if ($raw[$r] == '?') { $rawcvt .= ':p' . $p++; } else { $rawcvt .= $raw[$r]; } } $sql .= $rawcvt . ', '; } else { $sql .= ':p' . $p++ . ', '; } if (isset($param['value'])) { $updateValues->put($col, $param['value']); } } else { $updateValues->remove($col); $sql .= $param . ', '; } } } $params = self::buildParams($updateTablesColumns[$tableName], $updateValues); $sql = substr($sql, 0, -2); if (!empty($columns)) { foreach ($columns as $colName) { $sb = ""; $selectCriteria->getCriterion($colName)->appendPsTo($sb, $params); $whereClause[] = $sb; } $sql .= " WHERE " . implode(" AND ", $whereClause); } $stmt = $con->prepare($sql); // Replace ':p?' with the actual values self::populateStmtValues($stmt, $params, $dbMap, $db); $stmt->execute(); $affectedRows = $stmt->rowCount(); $stmt = null; // close } catch (Exception $e) { if ($stmt) { $stmt = null; } // close Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute UPDATE statement [%s]', $sql), $e); } } // foreach table in the criteria return $affectedRows; }
/** * Returns the "DELETE FROM <table> [AS <alias>]" part of DELETE query. * * @param Criteria $criteria * @param string $tableName * * @return string */ public function getDeleteFromClause($criteria, $tableName) { $sql = 'DELETE '; if ($queryComment = $criteria->getComment()) { $sql .= '/* ' . $queryComment . ' */ '; } if ($realTableName = $criteria->getTableForAlias($tableName)) { if ($this->useQuoteIdentifier()) { $realTableName = $this->quoteIdentifierTable($realTableName); } $sql .= $tableName . ' FROM ' . $realTableName . ' AS ' . $tableName; } else { if ($this->useQuoteIdentifier()) { $tableName = $this->quoteIdentifierTable($tableName); } $sql .= 'FROM ' . $tableName; } return $sql; }
public function testComment() { $c = new Criteria(); $this->assertNull($c->getComment(), 'Comment is null by default'); $c2 = $c->setComment('foo'); $this->assertEquals('foo', $c->getComment(), 'Comment is set by setComment()'); $this->assertEquals($c, $c2, 'setComment() returns the current Criteria'); $c->setComment(); $this->assertNull($c->getComment(), 'Comment is reset by setComment(null)'); }