public function testConditionalMultitableUpdate()
{
$q = new UpdateQuery(array('test', 'test2'));
$q->setValues(array(array(new Field('field1'), 'value1'), array(new Field('field2', 1), 'value2')));
$q->setWhere(new Condition('<', new Field('date', 1), '2004-10-11'));
$this->assertEquals('UPDATE `test` AS `t0`, `test2` AS `t1` SET `t0`.`field1` = :p1, `t1`.`field2` = :p2 WHERE `t1`.`date` < :p3', $q->sql());
$params = $q->parameters();
$this->assertEquals('value1', $params[':p1']);
$this->assertEquals('value2', $params[':p2']);
$this->assertEquals('2004-10-11', $params[':p3']);
}
/**
* Uses the ID passed and updates the database row with
* new data.
* @param integer $dbID The {@link DBHandler} database ID to query.
* @param integer $dataID The ID in the database of the data to be updated.
* @access public
* @return void
*/
function update($dbID, $dataID)
{
if (!$dataID) {
return false;
}
$query = new UpdateQuery();
$query->setTable($this->_table);
$query->setColumns(array("data"));
$query->setWhere("id='" . addslashes($dataID) . "'");
$query->setValues(array("'" . addslashes($this->asString()) . "'"));
$dbHandler = Services::getService("DatabaseManager");
$result = $dbHandler->query($query, $dbID);
if (!$result) {
throwError(new UnknownDBError("StorableString"));
return false;
}
return true;
}
function test_All_Queries()
{
$value = "'Depeche Mode rocks!'";
$this->dbhandler->connect();
// create a new queue of queries to execuete
$queryQueue = new Queue();
$query = new InsertQuery();
$query->setTable("test1");
$query->setColumns(array("value"));
$query->addRowOfValues(array($value));
$queryQueue->add($query);
$query = new InsertQuery();
$query->setTable("test1");
$query->setColumns(array(id, value));
$query->addRowOfValues(array("3000000", $value));
$queryQueue->add($query);
$query = new DeleteQuery();
$query->setTable("test1");
$query->setWhere("id = 3000000");
$queryQueue->add($query);
$query = new UpdateQuery();
$query->setTable("test1");
$query->setColumns(array("value"));
$query->setValues(array($value));
$query->setWhere("id > 1000 AND id < 1006");
$queryQueue->add($query);
$resultQueue = $this->dbhandler->queryQueue($queryQueue);
$this->assertEqual($this->dbhandler->getTotalNumberOfQueries(), 4);
$this->assertEqual($this->dbhandler->getTotalNumberOfSuccessfulQueries(), 4);
$this->assertEqual($this->dbhandler->getTotalNumberOfFailedQueries(), 0);
$result = $resultQueue->next();
$this->assertEqual($result->getNumberOfRows(), 1);
$this->assertNotNull($result->getLastAutoIncrementValue());
$id = $result->getLastAutoIncrementValue();
$result = $resultQueue->next();
$this->assertEqual($result->getNumberOfRows(), 1);
$this->assertNotNull($result->getLastAutoIncrementValue());
$result = $resultQueue->next();
$this->assertEqual($result->getNumberOfRows(), 1);
$result = $resultQueue->next();
$query = new SelectQuery();
$query->setColumns(array("value"));
$query->addTable("test1");
$query->setWhere("id = {$id}");
$result = $this->dbhandler->query($query);
$this->assertEqual($this->dbhandler->getTotalNumberOfQueries(), 5);
$this->assertEqual($this->dbhandler->getTotalNumberOfSuccessfulQueries(), 5);
$this->assertEqual($this->dbhandler->getTotalNumberOfFailedQueries(), 0);
$this->assertEqual("'" . $result->field("value") . "'", $value);
$result->free();
}
/**
* Commits any changes that have been made to the database. If neither update() nor prune() have been
* called, even if changes have been made, they will not be reflected in the database.
* @return bool
* @access public
*/
function commit()
{
$dbHandler = Services::getService("DatabaseManager");
if ($this->_update) {
// let's re-cast our primitive to a storablePrimitive
$this->recastAsStorable();
// first we need to commit the actual Primitive value
// so that we can get its ID
if (!$this->_dataID) {
$this->_dataID = $this->_primitive->insert(DATAMANAGER_DBID);
} else {
$this->_primitive->update(DATAMANAGER_DBID, $this->_dataID);
}
$this->_date = DateAndTime::now();
if ($this->_myID) {
// we're already in the DB. just update the entry
$query = new UpdateQuery();
$query->setWhere("id='" . addslashes($this->_myID) . "'");
$query->setColumns(array("value_index", "active", "modified"));
$query->setValues(array($this->_parent->getIndex(), $this->_active ? 1 : 0, $dbHandler->toDBDate($this->_date, DATAMANAGER_DBID)));
} else {
// we have to insert a new one
$query = new InsertQuery();
$idManager = Services::getService("Id");
$newID = $idManager->createId();
$this->_myID = $newID->getIdString();
$query->setColumns(array("id", "fk_record", "fk_schema_field", "value_index", "fk_data", "active", "modified"));
$schema = $this->_parent->_parent->_parent->getSchema();
$schemaField = $this->_parent->_parent->getSchemaField();
$query->addRowOfValues(array("'" . addslashes($this->_myID) . "'", "'" . addslashes($this->_parent->_parent->_parent->getID()) . "'", "'" . addslashes($schemaField->getID()) . "'", $this->_parent->getIndex(), "'" . addslashes($this->_dataID) . "'", $this->_active ? 1 : 0, $dbHandler->toDBDate($this->_date, DATAMANAGER_DBID)));
}
$query->setTable("dm_record_field");
$result = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$result) {
throwError(new UnknownDBError("DMRecord"));
return false;
}
}
if ($this->_prune && $this->_dataID) {
if ($id = $this->getID()) {
// ok, let's get rid of ourselves... completely!
$query = new DeleteQuery();
$query->setTable("dm_record_field");
$query->setWhere("id='" . addslashes($id) . "'");
$res = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$res) {
throwError(new UnknownDBError("DMRecord"));
}
// now tell the data object to prune itself
$this->recastAsStorable();
$this->_primitive->prune(DATAMANAGER_DBID, $this->_dataID);
// and we have to get rid of any tag mappings where we are included.
$query = new DeleteQuery();
$query->setTable("dm_tag_map");
$query->setWhere("fk_record_field='" . addslashes($id) . "'");
$res = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$res) {
throwError(new UnknownDBError("DMRecord"));
}
}
}
// reset the prune flag
$this->_prune = false;
// reset the update flag
$this->_update = false;
return true;
}
/**
* Uses the ID passed and updates the database row with
* new data.
* @param integer $dbID The {@link DBHandler} database ID to query.
* @param integer $dataID The ID in the database of the data to be updated.
* @access public
* @return void
*/
function update($dbID, $dataID)
{
if (!$dataID) {
return false;
}
$query = new UpdateQuery();
$query->setTable("dm_okitype");
$query->setColumns(array("domain", "authority", "keyword"));
$query->setWhere("id='" . addslashes($dataID) . "'");
$query->setValues(array("'" . addslashes($this->getDomain()) . "'", "'" . addslashes($this->getAuthority()) . "'", "'" . addslashes($this->getKeyword()) . "'"));
$dbHandler = Services::getService("DatabaseManager");
$result = $dbHandler->query($query, $dbID);
if (!$result) {
throwError(new UnknownDBError("StorableOKIType"));
return false;
}
return true;
}
/**
* Saves the style components of a style property
*
* @param object StyleCollection $property
* @return void
* @access public
* @since 4/26/06
*/
function saveStyleComponentsForProperty($property)
{
// get the style components for the property
$dbHandler = Services::getService("DatabaseManager");
$styleComponents = $property->getSCs();
foreach ($styleComponents as $styleComponent) {
$id = $styleComponent->getId();
$idValue = $id->getIdString();
$query = new SelectQuery();
$query->addTable($this->_dbName . ".tm_style_component");
$query->addWhere('component_id = ' . $idValue);
$query->addColumn("component_id");
$queryResult = $dbHandler->query($query, $this->_dbIndex);
if ($queryResult->getNumberOfRows() > 1) {
throwError(new Error("GUIManager", "component id multiplicity"));
} else {
if ($queryResult->getNumberOfRows() == 1) {
$queryResult->free();
$query = new UpdateQuery();
$query->setWhere("component_id = {$idValue}");
} else {
$queryResult->free();
$query = new InsertQuery();
}
}
$query->setTable($this->_dbName . ".tm_style_component");
$query->setColumns(array('component_id', 'component_class_name', 'component_value'));
$query->setValues(array("'" . addslashes($idValue) . "'", "'" . addslashes(get_class($styleComponent)) . "'", "'" . addslashes($styleComponent->getValue()) . "'"));
$dbHandler->query($query, $this->_dbIndex);
// save the style properties for this theme
}
}
/**
* Commits (either inserts or updates) the data for this DMRecord into the database.
* @param boolean optional $ignoreMandatory If true, doesn't fail if mandatory
* fields don't have values.
* @return bool
*/
function commit($ignoreMandatory = false)
{
// Ensure that we have fields for all labels, incase
// the schema has changed since we were loaded.
foreach ($this->_schema->getAllLabels() as $label) {
$this->_checkLabel($label);
}
// Get the DBHandler
$dbHandler = Services::getService("DatabaseManager");
// the first thing we're gonna do is check to make sure that all our required fields
// have at least one value.
if (!$this->_delete) {
foreach ($this->_schema->getAllIDs() as $id) {
$fieldDef = $this->_schema->getField($id);
if ($fieldDef->isRequired() && ($this->_fields[$id]->numValues(true) == 0 || $this->_fields[$id]->numValues() == 0) && !$ignoreMandatory) {
throwError(new Error("Could not commit DMRecord to database because the required field '{$id}' does\n\t\t\t\t\tnot have any values!", "DMRecord", true));
return false;
}
}
if ($this->_myID) {
// we're already in the database
$query = new UpdateQuery();
$query->setTable("dm_record");
$query->setColumns(array("ver_control"));
$query->setValues(array($this->_versionControlled ? 1 : 0));
$query->setWhere("id='" . addslashes($this->_myID) . "'");
} else {
// we'll have to make a new entry
$schemaManager = Services::getService("SchemaManager");
$newID = $this->_idManager->createId();
$this->_myID = $newID->getIdString();
$query = new InsertQuery();
$query->setTable("dm_record");
$query->setColumns(array("id", "fk_schema", "created", "ver_control"));
$query->addRowOfValues(array("'" . addslashes($this->_myID) . "'", "'" . addslashes($this->_schema->getID()) . "'", $dbHandler->toDBDate($this->_creationDate, DATAMANAGER_DBID), $this->_versionControlled ? 1 : 0));
}
// execute the query;
$result = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$result) {
throwError(new UnknownDBError("DMRecord"));
return false;
}
}
// now let's cycle through our FieldValues and commit them
foreach ($this->_schema->getAllIDs() as $id) {
$this->_fields[$id]->commit();
}
if ($this->_prune) {
$constraint = $this->_pruneConstraint;
// check if we have to delete any dataset tags based on our constraints
$constraint->checkTags($this);
$tagMgr = Services::getService("RecordTagManager");
// if we are no good any more, delete ourselves completely
if ($this->_delete) {
// now, remove any tags from the DB that have to do with us, since they will no longer
// be valid.
$tagMgr->pruneTags($this);
$query = new DeleteQuery();
$query->setTable("dm_record");
$query->setWhere("id='" . addslashes($this->getID()) . "'");
$dbHandler->query($query, DATAMANAGER_DBID);
$query = new DeleteQuery();
$query->setTable("dm_record_set");
$query->setWhere("fk_record='" . addslashes($this->getID()) . "'");
$dbHandler->query($query, DATAMANAGER_DBID);
} else {
// if we're pruning but not deleting the whole shebang, let's
// make sure that there are no tags in the database with no
// mappings whatsoever.
$tagMgr->checkForEmptyTags($this);
}
}
return true;
}
/**
* Reflects any changes made locally to the database.
* @param optional int $id The ID in the database to update (if not adding...).
* @return int Returns our ID in the database or NULL upon error.
* @access public
*/
function commit($id = null)
{
if (!$this->_schema->getID() || !$this->_addToDB && !$id) {
// we have no ID, we probably can't commit...unless we're going to be added to the DB.
// we'll also fail if our dataSetTypeDef doesn't have an ID. that meaning it wasn't meant to be
// synchronized into the database.
throwError(new Error("Could not commit() to database because either: 1) we don't have a local ID, \n\t\t\tmeaning we were not meant to be synchronized with the database, or 2) the Schema to which we \n\t\t\tbelong is not linked with the database. (label: " . $this->_label . ", schema name: " . $this->_schema->getDisplayName() . ")", "DataManager", true));
return null;
}
$dbHandler = Services::getService("DatabaseManager");
if ($this->_addToDB) {
$query = new InsertQuery();
$query->setTable("dm_schema_field");
$query->setColumns(array("id", "fk_schema", "name", "mult", "fieldtype", "active", "required", "description"));
$query->addRowOfValues(array("'" . addslashes($id) . "'", "'" . addslashes($this->_schema->getID()) . "'", "'" . addslashes($this->_displayName) . "'", $this->_mult ? 1 : 0, "'" . addslashes($this->_type) . "'", 1, $this->_required ? 1 : 0, "'" . addslashes($this->_description) . "'"));
$this->_addToDB = false;
$result = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$result || $result->getNumberOfRows() != 1) {
throwError(new UnknownDBError("DataManager"));
return false;
}
return $id;
}
if ($this->_update) {
// do some updating
$query = new UpdateQuery();
$query->setTable("dm_schema_field");
$query->setColumns(array("name", "mult", "active", "required", "description"));
$query->setWhere("id='" . addslashes($id) . "'");
$query->setValues(array("'" . addslashes($this->_displayName) . "'", $this->_mult ? 1 : 0, $this->_active ? 1 : 0, $this->_required ? 1 : 0, "'" . addslashes($this->_description) . "'"));
$this->_update = false;
$result = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$result || $result->getNumberOfRows() != 1) {
throwError(new UnknownDBError("DataManager"));
return null;
}
return $id;
}
if ($this->_delete) {
// let's get rid of this bad-boy
$query = new UpdateQuery();
$query->setTable("dm_schema_field");
$query->setWhere("id='" . addslashes($id) . "'");
$query->setColumns(array("active"));
$query->setValues(array(0));
$this->_delete = false;
$result = $dbHandler->query($query, DATAMANAGER_DBID);
// if (!$result || $result->getNumberOfRows() != 1) {
// throwError( new UnknownDBError("DataManager") );
// return false;
// }
return $id;
}
// if we're here... nothing happened... no problems
return $id;
}
/**
* Passed a {@link Schema}, will make sure that the definition stored in the database
* reflects what is stored in the passed object.
* @param ref object A {@link Schema} object.
* @return boolean Success/failure.
* @access public
*/
function synchronize($new)
{
ArgumentValidator::validate($new, ExtendsValidatorRule::getRule("Schema"));
$id = $new->getID();
debug::output("Attempting to synchronize Schema type '" . $id . "' with\n\t\tthe database.", DEBUG_SYS1, "DataManager");
// check if we already have a definition for this type. if we don't, add a new one.
if (!$this->schemaExists($id)) {
$old = $this->_addSchema($id, $new->getDisplayName(), $new->getRevision(), $new->getDescription());
debug::output("Creating new Schema in the database.", DEBUG_SYS3, "DataManager");
} else {
$old = $this->getSchemaByID($id);
// make sure $oldDef has all its data loaded
$old->load();
debug::output("Using database version for synchronization.", DEBUG_SYS3, "DataManger");
}
/*
The synchronization process is not simple.
compare revision numbers, and update them appropriately. do this last.
get all field ids, from both old def (from DB) and new def, store in $ids[]
For each $ids {
if the field doesn't exist, add it to the DB
if the field does exist {
...in database, but not in new, delete it
...in both new def and db {
if mult value has changed ...
... from yes to no, make the change.
... from no to yes, make the change.
if versionControl has changed ...
... shouldn't be a problem -- just make the change
if field type has changed ...
... NOT ALLOWED!
if active flag has changed ...
... from yes to no, delete the old
... from no to yes, re-activate the old
if required flag has changed ...
... from yes to no, ok, update it
... from no to yes, throw an error. we can't validate all datasets
if display name has changed
... change it
if desc has changed
... change it
}
}
}
*/
$allLabels = array_unique(array_merge($new->getAllIDs(true), $old->getAllIDs(true)));
debug::output("Merged ids: " . implode(", ", $allLabels), DEBUG_SYS5, "DataManager");
foreach ($allLabels as $label) {
// now we're going to go through the logic above in the comment
debug::output("Checking id '{$label}' ...", DEBUG_SYS5, "DataManager");
// if the field exists in new and not in old, add it to old, and flag it to add to DB
if (!$old->fieldExists($label) && $new->fieldExists($label)) {
$field = $new->getField($label);
$newField = $field->replicate();
$newField->addToDB();
$old->addField($newField);
debug::output("Label '{$label}' flagged for addition to database.", DEBUG_SYS5, "DataManager");
unset($newField, $field);
continue;
}
// if the field exists in the old but not in the new, flag it for deletion.
if ($old->fieldExists($label) && !$new->fieldExists($label)) {
$field = $old->getField($label);
$field->delete();
debug::output("Label '{$label}' flagged for deletion from database.", DEBUG_SYS5, "DataManager");
unset($field);
continue;
}
// ok, if we're at this point it means the label is defined in both definitions.
$oldField = $old->getField($label);
$newField = $new->getField($label);
// first let's check if the types match. if they don't, we're going to go psycho
$oldType = $oldField->getType();
$newType = $newField->getType();
if ($newType != $oldType) {
// go PSYCHO!!!!
throwError(new Error("While synchronizing Schema '" . $this->_type . "', it appears that the\n\t\t\t\t\tfield type for id '{$label}' is different from what we have stored. It is not possible\n\t\t\t\t\tto synchronize without potential data loss. Aborting.", "DataManager", true));
return false;
}
unset($newType, $oldType);
// let's check the active flag
$oldActive = $oldField->isActive();
$newActive = $newField->isActive();
if ($oldActive != $newActive) {
if ($oldActive && !$newActive) {
$oldField->delete();
debug::output("ID '{$label}' is no longer active.", DEBUG_SYS5, "DataManager");
}
if (!$oldActive && $newActive) {
$oldField->setActiveFlag(true);
$oldField->update();
debug::output("ID '{$label}' is to be reactivated.", DEBUG_SYS5, "DataManager");
}
}
// now let's check the mult
$oldMult = $oldField->getMultFlag();
$newMult = $newField->getMultFlag();
if ($oldMult !== $newMult) {
// boolean-safe compare
// ok, now, if we're changing from true to false, just go ahead, make the change
if (!$oldMult && $newMult) {
$oldField->setMultFlag(true);
$oldField->update();
debug::output("Label '{$label}': activating multiple-values.", DEBUG_SYS5, "DataManager");
}
// otherwise, we'll have to do some smart updating
if ($oldMult && !$newMult) {
// now, make the change
$oldField->setMultFlag(false);
$oldField->update();
debug::output("Label '{$label}': deactivating multiple values, deleted any additional data entries that would conflict with this setting.", DEBUG_SYS5, "DataManager");
}
}
// check the description
$oldDesc = $oldField->getDescription();
$newDesc = $newField->getDescription();
if ($oldDesc != $newDesc) {
$oldField->updateDescription($newDesc);
$oldField->update();
}
// check the display name
$oldName = $oldField->getDisplayName();
$newName = $newField->getDisplayName();
if ($oldName != $newName) {
$oldField->updateDisplayName($newName);
$oldField->update();
}
// now let's check the req
$oldReq = $oldField->isRequired();
$newReq = $newField->isRequired();
if ($oldReq !== $newReq) {
// boolean-safe compare
// ok, now, if we're changing from true to false, just go ahead, make the change
if (!$oldReq && $newReq) {
$oldField->setRequired(true);
$oldField->update();
}
// otherwise, throw an error!
if ($oldReq && !$newReq) {
throwError(new Error("synchronize() can not change a field's required flag from NO to YES!", "DataManager", true));
return false;
}
}
}
// now that we're done syncrhonizing $newDef with $oldDef, let's commit everything to the DB
$old->commitAllFields();
// lastly, update the row in the table for this schema
// change the database.
$query = new UpdateQuery();
$query->setTable("dm_schema");
$query->setWhere("id='" . addslashes($old->getID()) . "'");
$query->setColumns(array("revision", "displayname", "description", "other_params"));
$query->setValues(array($new->getRevision(), "'" . addslashes($old->getDisplayName()) . "'", "'" . addslashes($old->getDescription()) . "'", "'" . addslashes(serialize($old->getOtherParameters())) . "'"));
$dbHandler = Services::getService("DatabaseManager");
$dbHandler->query($query, DATAMANAGER_DBID);
$old->loaded(true);
debug::output("... synchronization finished.", DEBUG_SYS2, "DataManager");
}
/**
* Uses the ID passed and updates the database row with
* new data.
* @param integer $dbID The {@link DBHandler} database ID to query.
* @param integer $dataID The ID in the database of the data to be updated.
* @access public
* @return void
*/
function update($dbID, $dataID)
{
if (!$dataID) {
return false;
}
$query = new UpdateQuery();
$query->setTable("dm_time");
$query->setColumns(array("jdn", "seconds"));
$query->setWhere("id='" . addslashes($dataID) . "'");
// Convert to UTC for storage
$utc = $this->asUTC();
$utcTime = $utc->asTime();
$query->setValues(array("'" . addslashes($utc->julianDayNumber()) . "'", "'" . addslashes($utcTime->asSeconds()) . "'"));
$dbHandler = Services::getService("DatabaseManager");
$result = $dbHandler->query($query, $dbID);
if (!$result) {
throwError(new UnknownDBError("StorableTime"));
return false;
}
return true;
}
