public function testBasicUpdate()
{
$query = SQLUpdate::create()->setTable('"SQLUpdateTestBase"')->assign('"Description"', 'Description 1a')->addWhere(array('"Title" = ?' => 'Object 1'));
$sql = $query->sql($parameters);
// Check SQL
$this->assertSQLEquals('UPDATE "SQLUpdateTestBase" SET "Description" = ? WHERE ("Title" = ?)', $sql);
$this->assertEquals(array('Description 1a', 'Object 1'), $parameters);
// Check affected rows
$query->execute();
$this->assertEquals(1, DB::affected_rows());
// Check item updated
$item = DataObject::get_one('SQLUpdateTestBase', array('"Title"' => 'Object 1'));
$this->assertEquals('Description 1a', $item->Description);
}
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();
}
}
}
}
/**
* Return the UPDATE clause ready for inserting into a query.
*
* @param SQLExpression $query The expression object to build from
* @param array $parameters Out parameter for the resulting query parameters
* @return string Completed from part of statement
*/
public function buildUpdateFragment(SQLUpdate $query, array &$parameters)
{
$table = $query->getTable();
$text = "UPDATE {$table}";
// Join SET components together, considering parameters
$parts = array();
foreach ($query->getAssignments() as $column => $assignment) {
// Assigment is a single item array, expand with a loop here
foreach ($assignment as $assignmentSQL => $assignmentParameters) {
$parts[] = "{$column} = {$assignmentSQL}";
$parameters = array_merge($parameters, $assignmentParameters);
break;
}
}
$nl = $this->getSeparator();
$text .= "{$nl}SET " . implode(', ', $parts);
return $text;
}
/**
* 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;
}
protected function addMappingToList($URL, $ID)
{
$this->linkMappings[$URL] = $ID;
$query = new SQLUpdate($this->replayTable, array('FullURL' => $URL), "ID = {$ID}");
$query->execute();
}
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();
}
}
function cps4wp_query($sql)
{
global $wpdb;
// echo $sql;
$logto = 'queries';
// The end of the query may be protected against changes
$end = '';
// Remove unusefull spaces
$initial = $sql = trim($sql);
if (0 === strpos($sql, 'SELECT')) {
// clutserpoint doesnot support @ in queries
if (false !== strpos($sql, "@")) {
return false;
}
$logto = 'SELECT';
$s = new SQLSelect($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'UPDATE')) {
$logto = 'UPDATE';
$s = new SQLUpdate($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'INSERT')) {
$logto = 'INSERT';
$s = new SQLInsert($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'DELETE')) {
$logto = 'DELETE';
$s = new SQLDelete($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'SHOW TABLES')) {
$logto = 'SHOWTABLES';
$o = new SQLAbstract();
return $o->execute('SELECT DISTINCT __table FROM ' . DB_NAME);
} elseif (0 === strpos($sql, 'OPTIMIZE TABLE')) {
$logto = 'OPTIMIZE';
$sql = str_replace('OPTIMIZE TABLE', 'VACUUM', $sql);
} elseif (0 === strpos($sql, 'SET NAMES') && false !== strpos($sql, 'COLLATE')) {
$logto = 'SETNAMES';
$sql = "SET NAMES 'utf8'";
$sql = false;
//cps don't need this now
}
// Load up upgrade and install functions as required
$begin = substr($sql, 0, 3);
$search = array('SHO', 'ALT', 'DES', 'CRE', 'DRO');
if (in_array($begin, $search)) {
require_once CPS4WP_ROOT . '/driver_pgsql_install.php';
$sql = cps4wp_installing($sql, $logto);
}
// WP 2.9.1 uses a comparison where text data is not quoted
$pattern = '/AND meta_value = (-?\\d+)/';
$sql = preg_replace($pattern, 'AND meta_value = \'$1\'', $sql);
// Generic "INTERVAL xx YEAR|MONTH|DAY|HOUR|MINUTE|SECOND" handler
$pattern = '/INTERVAL[ ]+(\\d+)[ ]+(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND)/';
$sql = preg_replace($pattern, "'\$1 \$2'::interval", $sql);
$pattern = '/DATE_SUB[ ]*\\(([^,]+),([^\\)]+)\\)/';
$sql = preg_replace($pattern, '($1::timestamp - $2)', $sql);
// Remove illegal characters
$sql = str_replace('`', '', $sql);
// Field names with CAPITALS need special handling
if (false !== strpos($sql, 'ID')) {
$pattern = '/ID([^ ])/';
$sql = preg_replace($pattern, 'ID $1', $sql);
$pattern = '/ID$/';
$sql = preg_replace($pattern, 'ID ', $sql);
$pattern = '/\\(ID/';
$sql = preg_replace($pattern, '( ID', $sql);
$pattern = '/,ID/';
$sql = preg_replace($pattern, ', ID', $sql);
$pattern = '/[0-9a-zA-Z_]+ID/';
$sql = preg_replace($pattern, '"$0"', $sql);
$pattern = '/\\.ID/';
$sql = preg_replace($pattern, '."ID"', $sql);
$pattern = '/[\\s]ID /';
$sql = preg_replace($pattern, ' "ID" ', $sql);
$pattern = '/"ID "/';
$sql = preg_replace($pattern, ' "ID" ', $sql);
}
// CAPITALS
// Empty "IN" statements are erroneous
$sql = str_replace('IN (\'\')', 'IN (NULL)', $sql);
$sql = str_replace('IN ( \'\' )', 'IN (NULL)', $sql);
$sql = str_replace('IN ()', 'IN (NULL)', $sql);
// Put back the end of the query if it was separated
$sql .= $end;
// For insert ID catching
if ($logto == 'INSERT') {
$pattern = '/INSERT INTO (\\w+)\\s+\\([ a-zA-Z_"]+/';
preg_match($pattern, $sql, $matches);
$GLOBALS['cps4wp_ins_table'] = $matches[1];
$match_list = split(' ', $matches[0]);
if ($GLOBALS['cps4wp_ins_table']) {
$GLOBALS['cps4wp_ins_field'] = trim($match_list[3], ' () ');
if (!$GLOBALS['cps4wp_ins_field']) {
$GLOBALS['cps4wp_ins_field'] = trim($match_list[4], ' () ');
}
}
$GLOBALS['cps4wp_last_insert'] = $sql;
} elseif (isset($GLOBALS['cps4wp_queued_query'])) {
pg_query($GLOBALS['cps4wp_queued_query']);
unset($GLOBALS['cps4wp_queued_query']);
}
// Correct quoting for PostgreSQL 9.1+ compatibility
$sql = str_replace("\\'", "''", $sql);
$sql = str_replace('\\"', '"', $sql);
if (CPS4WP_DEBUG) {
if ($initial != $sql) {
error_log('[' . microtime(true) . "] Converting :\n{$initial}\n---- to ----\n{$sql}\n---------------------\n", 3, CPS4WP_LOG . 'cps4wp_' . $logto . '.log');
} else {
error_log('[' . microtime(true) . "] {$sql}\n---------------------\n", 3, CPS4WP_LOG . 'cps4wp_unmodified.log');
}
}
return $sql;
}
<?php
$root = realpath($_SERVER["DOCUMENT_ROOT"]);
require_once "{$root}/shared/defaults.php";
require_once 'class/LoginUser.class.php';
// Initialize the json object
$rjo = new API();
// Save user settings
if (isset($_REQUEST["action"]) && $_REQUEST["action"] == "updateUserSettings") {
$updates = array();
if (!isset($_REQUEST["userid"])) {
error_out("No user id set.");
}
$user = new LoginUser();
$user->id = intval(trim($_REQUEST["userid"]));
$update = new SQLUpdate("login_user");
$update->selectors = array("id" => $_REQUEST["userid"]);
foreach (array("firstname", "lastname", "email") as $field) {
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) {
$add_ste->execute();
$selectidq = new SQLSelect('survey_text_element');
$selectidq->selectfields['id'] = 'id';
$selectidq->wherearray = $values;
$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();
/**
* 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;
}
