public function testAffectedRows()
{
if (!DB::get_connector() instanceof PDOConnector) {
$this->markTestSkipped('This test requires the current DB connector is PDO');
}
$query = new SQLUpdate('MySQLDatabaseTest_Data');
$query->setAssignments(array('Title' => 'New Title'));
// Test update which affects no rows
$query->setWhere(array('Title' => 'Bob'));
$result = $query->execute();
$this->assertInstanceOf('PDOQuery', $result);
$this->assertEquals(0, DB::affected_rows());
// Test update which affects some rows
$query->setWhere(array('Title' => 'First Item'));
$result = $query->execute();
$this->assertInstanceOf('PDOQuery', $result);
$this->assertEquals(1, DB::affected_rows());
}
/**
* Execute a complex manipulation on the database.
* A manipulation is an array of insert / or update sequences. The keys of the array are table names,
* and the values are map containing 'command' and 'fields'. Command should be 'insert' or 'update',
* and fields should be a map of field names to field values, NOT including quotes.
*
* The field values could also be in paramaterised format, such as
* array('MAX(?,?)' => array(42, 69)), allowing the use of raw SQL values such as
* array('NOW()' => array()).
*
* @see SQLWriteExpression::addAssignments for syntax examples
*
* @param array $manipulation
*/
public function manipulate($manipulation)
{
if (empty($manipulation)) {
return;
}
foreach ($manipulation as $table => $writeInfo) {
if (empty($writeInfo['fields'])) {
continue;
}
// Note: keys of $fieldValues are not escaped
$fieldValues = $writeInfo['fields'];
// Switch command type
switch ($writeInfo['command']) {
case "update":
// Build update
$query = new SQLUpdate("\"{$table}\"", $this->escapeColumnKeys($fieldValues));
// Set best condition to use
if (!empty($writeInfo['where'])) {
$query->addWhere($writeInfo['where']);
} elseif (!empty($writeInfo['id'])) {
$query->addWhere(array('"ID"' => $writeInfo['id']));
}
// Test to see if this update query shouldn't, in fact, be an insert
if ($query->toSelect()->count()) {
$query->execute();
break;
}
// ...if not, we'll skip on to the insert code
// ...if not, we'll skip on to the insert code
case "insert":
// Ensure that the ID clause is given if possible
if (!isset($fieldValues['ID']) && isset($writeInfo['id'])) {
$fieldValues['ID'] = $writeInfo['id'];
}
// Build insert
$query = new SQLInsert("\"{$table}\"", $this->escapeColumnKeys($fieldValues));
$query->execute();
break;
default:
user_error("SS_Database::manipulate() Can't recognise command '{$writeInfo['command']}'", E_USER_ERROR);
}
}
}
public function reset_table($rows, $selectors = array(), $primary_key = "")
{
$current_cols = $this->get_columns();
// The table doesn't exist: make it
if (!$current_cols || empty($current_cols)) {
$datatypes = $this->get_column_datatypes();
$cr = new SQLCreate($this->table, $datatypes, $primary_key);
$cr->debug = $this->debug;
$cr->execute();
}
if (empty($selectors)) {
// No selectors: Always insert the rows
foreach ($rows as $rownum => $row) {
$ins = new SQLInsert($this->table, $row);
$ins->debug = $this->debug;
$ins->execute();
}
} else {
// Selectors exist: update the given rows
foreach ($rows as $rownum => $row) {
$row_selectors = array();
foreach ($selectors as $selkey) {
if (isset($row[$selkey])) {
$row_selectors[$selkey] = $row[$selkey];
}
}
if (!empty($row_selectors)) {
$upd = new SQLUpdate($this->table, $row, $row_selectors, true);
$upd->debug = $this->debug;
$upd->execute();
} else {
$ins = new SQLInsert($this->table, $row);
$ins->debug = $this->debug;
$ins->execute();
}
}
}
}
protected function addMappingToList($URL, $ID)
{
$this->linkMappings[$URL] = $ID;
$query = new SQLUpdate($this->replayTable, array('FullURL' => $URL), "ID = {$ID}");
$query->execute();
}
/**
* Sets the values for multiple rows on a database table by the ID column.
* Useful when fields have been removed from the class' `$db` property,
* and therefore are no longer accessible through the ORM.
* Returns false if the table or any of the rows do not exist.
* Returns true if the SQL query was executed.
*
* @param string $table
* @param array $values Ex: array('FieldName' => value)
* @param int|null $id Note: Null only works here if $insert = true.
* @param bool $insert Allows insertion of a new record if the ID provided is null or doesn't exist.
* NOTE: If an "ID" field is passed, that ID value will be retained.
* @return boolean Will return true if anything was changed, false otherwise.
*/
public static function setRowValuesOnTable($table, array $values, $id = null, $insert = false)
{
// TODO: This should maybe throw an exception instead.
if (!self::tableColumnsExist($table, array_keys($values))) {
return false;
}
// Assume it exists unless we're actually going to allow inserting. Then we'll really check for sure.
$exists = true;
if ($insert) {
// Ensure the ID we're checking now is the same as the one we're inserting to help prevent issues with duplicate keys.
$checkID = $id;
if (isset($values['ID'])) {
$checkID = $values['ID'];
}
$select = new SQLSelect('COUNT(*)', $table, array('ID' => $checkID));
$result = $select->execute();
$exists = (bool) (int) $result->value();
}
// Pull out an ID (if applicable).
if ($id === null && array_key_exists('ID', $values)) {
$id = $values['ID'];
}
if ($exists) {
// Generate an execute an UPDATE query.
$update = new SQLUpdate($table, $values, array('ID' => $id));
$update->execute();
return true;
} elseif ($insert) {
// Generate an INSERT query instead.
$insert = new SQLInsert($table, $values);
$insert->execute();
return true;
}
// Nothing was done.
return false;
}
public function updateOrAddTableRow($idfields)
{
$idvals = $this->findRow($idfields);
//echo "<h2>Update or Add Table ROw</h2>";
if (!$idvals) {
// Did not find row-- add it
$this->addTableRow();
} else {
// Found row -- update it, leaving the idvals selectors alone
$sqlu = new SQLUpdate($this->table, $this->values, $idvals);
$sqlu->international = $this->international;
// $sqlu->build_query();
// echo $sqlu->query;
$sqlu->execute();
}
}
if (isset($_REQUEST[$field])) {
$update->add_value($field, $_REQUEST[$field]);
}
}
if (isset($_REQUEST["password1"]) && $_REQUEST["password1"] != "") {
if (!isset($_REQUEST["password2"]) && $_REQUEST["password2"] != "") {
error_out("Please retype password.");
} else {
if ($_REQUEST["password1"] != $_REQUEST["password2"]) {
error_out("Passwords do not match.");
} else {
$hash = db_hash_password($_REQUEST['password1']);
if ($hash) {
$update->add_value("pass", $hash);
} else {
error_out("Problem hashing password. Please retry.");
}
}
}
} else {
if (isset($_REQUEST["password2"]) && $_REQUEST["password2"] != "") {
error_out("Please type password in both fields.");
}
}
$update->execute();
$user->dbGet();
$rjo->query = $update->query;
$rjo->status = "success";
$rjo->successMessage = "Saved user settings.";
}
echo $rjo->printjson();
$res = $selectidq->execute();
$element_id = $res["ID"][0];
}
if ($element_id) {
// Update the languages
$posslanguages = $records->getPossLanguages();
foreach ($posslanguages as $language) {
$translation = filter_input(INPUT_POST, $language['KEYCODE']);
if ($translation) {
$translation = trim($translation);
if ($translation != "") {
$update_lang = new SQLUpdate('translation_text');
$update_lang->insertIfAbsent = true;
$update_lang->selectors['language_keycode'] = $language['KEYCODE'];
$update_lang->selectors['survey_text_element_id'] = $element_id;
$update_lang->values['text_translation'] = trim($translation);
$update_lang->values = array_merge($update_lang->selectors, $update_lang->values);
//$update_lang->build_query();
//echo "<br>" . $update_lang->query;
$update_lang->execute();
}
}
}
}
}
// Print out the Text Elements
echo $records->panelhtml();
?>
</div>
</body>
</html>
/**
* The process to automatically consolidate existing and configuration defined tag types, executed on project build.
*/
public function requireDefaultRecords()
{
parent::requireDefaultRecords();
// Retrieve existing and configuration defined tag types that have not been consolidated.
$wrapped = array();
foreach ($this->service->getFusionTagTypes() as $type => $field) {
if (($tags = $type::get()->filter('FusionTagID', 0)) && $tags->exists()) {
foreach ($tags as $tag) {
// Determine whether there's an existing fusion tag.
if (!($existing = FusionTag::get()->filter('Title', $tag->{$field})->first())) {
// There is no fusion tag, therefore instantiate one using the current tag.
$fusion = FusionTag::create();
$fusion->Title = $tag->{$field};
$fusion->TagTypes = serialize(array($tag->ClassName => $tag->ClassName));
$fusion->write();
$fusionID = $fusion->ID;
DB::alteration_message("\"{$tag->{$field}}\" Fusion Tag", 'created');
} else {
// There is a fusion tag, therefore append the current tag type.
$types = unserialize($existing->TagTypes);
$types[$tag->ClassName] = $tag->ClassName;
$existing->TagTypes = serialize($types);
$existing->write();
$fusionID = $existing->ID;
}
// Update the current tag to point to this.
$tag->FusionTagID = $fusionID;
$tag->write();
}
}
// The existing and configuration defined tag types need to be wrapped for serialised partial matching.
$wrapped[] = "\"{$type}\"";
}
// Determine whether tag type exclusions have caused any fusion tags to become redundant.
$tags = FusionTag::get()->where('TagTypes IS NOT NULL');
if (count($wrapped)) {
// Determine the fusion tags that only contain tag type exclusions.
$tags = $tags->exclude('TagTypes:PartialMatch', $wrapped);
}
if ($tags->exists()) {
// Determine any data objects with the tagging extension.
$objects = array();
$configuration = Config::inst();
$classes = ClassInfo::subclassesFor('DataObject');
unset($classes['DataObject']);
foreach ($classes as $class) {
// Determine the specific data object extensions.
$extensions = $configuration->get($class, 'extensions', Config::UNINHERITED);
if (is_array($extensions) && in_array('TaggingExtension', $extensions)) {
$objects[] = $class;
}
}
// Determine whether these fusion tags are now redundant.
$mode = Versioned::get_reading_mode();
Versioned::reading_stage('Stage');
$types = array();
$where = array();
foreach ($tags as $tag) {
// Determine whether this fusion tag is being used.
$ID = $tag->ID;
foreach ($objects as $object) {
if ($object::get()->filter('FusionTags.ID', $ID)->exists()) {
// This fusion tag is being used, so keep it.
continue 2;
}
}
// These fusion tags are now redundant.
$types = array_merge($types, unserialize($tag->TagTypes));
$where[] = array('FusionTagID' => $ID);
$tag->delete();
DB::alteration_message("\"{$tag->Title}\" Fusion Tag", 'deleted');
}
Versioned::set_reading_mode($mode);
// The tag type exclusions need updating to reflect this.
foreach ($types as $exclusion) {
$query = new SQLUpdate($exclusion, array('FusionTagID' => 0), $where);
$query->useDisjunction();
$query->execute();
}
}
}
/**
* @param String $identifier Unique identifier for this fixture type
* @param Array $data Map of property names to their values.
* @param Array $fixtures Map of fixture names to an associative array of their in-memory
* identifiers mapped to their database IDs. Used to look up
* existing fixtures which might be referenced in the $data attribute
* via the => notation.
* @return DataObject
*/
public function createObject($identifier, $data = null, $fixtures = null)
{
// We have to disable validation while we import the fixtures, as the order in
// which they are imported doesnt guarantee valid relations until after the import is complete.
$validationenabled = Config::inst()->get('DataObject', 'validation_enabled');
Config::inst()->update('DataObject', 'validation_enabled', false);
$this->invokeCallbacks('beforeCreate', array($identifier, &$data, &$fixtures));
try {
$class = $this->class;
$obj = DataModel::inst()->{$class}->newObject();
// If an ID is explicitly passed, then we'll sort out the initial write straight away
// This is just in case field setters triggered by the population code in the next block
// Call $this->write(). (For example, in FileTest)
if (isset($data['ID'])) {
$obj->ID = $data['ID'];
// The database needs to allow inserting values into the foreign key column (ID in our case)
$conn = DB::get_conn();
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing(ClassInfo::baseDataClass($class), true);
}
$obj->write(false, true);
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing(ClassInfo::baseDataClass($class), false);
}
}
// Populate defaults
if ($this->defaults) {
foreach ($this->defaults as $fieldName => $fieldVal) {
if (isset($data[$fieldName]) && $data[$fieldName] !== false) {
continue;
}
if (is_callable($fieldVal)) {
$obj->{$fieldName} = $fieldVal($obj, $data, $fixtures);
} else {
$obj->{$fieldName} = $fieldVal;
}
}
}
// Populate overrides
if ($data) {
foreach ($data as $fieldName => $fieldVal) {
// Defer relationship processing
if ($obj->many_many($fieldName) || $obj->has_many($fieldName) || $obj->has_one($fieldName)) {
continue;
}
$this->setValue($obj, $fieldName, $fieldVal, $fixtures);
}
}
$obj->write();
// Save to fixture before relationship processing in case of reflexive relationships
if (!isset($fixtures[$class])) {
$fixtures[$class] = array();
}
$fixtures[$class][$identifier] = $obj->ID;
// Populate all relations
if ($data) {
foreach ($data as $fieldName => $fieldVal) {
if ($obj->many_many($fieldName) || $obj->has_many($fieldName)) {
$obj->write();
$parsedItems = array();
if (is_array($fieldVal)) {
// handle lists of many_many relations. Each item can
// specify the many_many_extraFields against each
// related item.
foreach ($fieldVal as $relVal) {
$item = key($relVal);
$id = $this->parseValue($item, $fixtures);
$parsedItems[] = $id;
array_shift($relVal);
$obj->getManyManyComponents($fieldName)->add($id, $relVal);
}
} else {
$items = preg_split('/ *, */', trim($fieldVal));
foreach ($items as $item) {
// Check for correct format: =><relationname>.<identifier>.
// Ignore if the item has already been replaced with a numeric DB identifier
if (!is_numeric($item) && !preg_match('/^=>[^\\.]+\\.[^\\.]+/', $item)) {
throw new InvalidArgumentException(sprintf('Invalid format for relation "%s" on class "%s" ("%s")', $fieldName, $class, $item));
}
$parsedItems[] = $this->parseValue($item, $fixtures);
}
if ($obj->has_many($fieldName)) {
$obj->getComponents($fieldName)->setByIDList($parsedItems);
} elseif ($obj->many_many($fieldName)) {
$obj->getManyManyComponents($fieldName)->setByIDList($parsedItems);
}
}
} else {
$hasOneField = preg_replace('/ID$/', '', $fieldName);
if ($className = $obj->has_one($hasOneField)) {
$obj->{$hasOneField . 'ID'} = $this->parseValue($fieldVal, $fixtures, $fieldClass);
// Inject class for polymorphic relation
if ($className === 'DataObject') {
$obj->{$hasOneField . 'Class'} = $fieldClass;
}
}
}
}
}
$obj->write();
// If LastEdited was set in the fixture, set it here
if ($data && array_key_exists('LastEdited', $data)) {
$edited = $this->parseValue($data['LastEdited'], $fixtures);
$update = new SQLUpdate($class, array('"LastEdited"' => $edited), array('"ID"' => $obj->id));
$update->execute();
}
} catch (Exception $e) {
Config::inst()->update('DataObject', 'validation_enabled', $validationenabled);
throw $e;
}
Config::inst()->update('DataObject', 'validation_enabled', $validationenabled);
$this->invokeCallbacks('afterCreate', array($obj, $identifier, &$data, &$fixtures));
return $obj;
}