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