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());
 }
Exemple #2
0
 /**
  * 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;
 }