/**
* @return array
* @param string $type The Schema type to look for.
* Returns an array of DMRecord IDs that are of the Schema type $type.
*/
function getRecordIDsByType($type)
{
// we're going to get all the IDs that match a given type.
$query = new SelectQuery();
$query->addTable("dm_record");
$query->addTable("dm_schema", INNER_JOIN, "dm_schema.id=dm_record.fk_schema");
$query->addColumn("id", "", "dm_record");
$query->setWhere("dm_schema.id='" . addslashes($type) . "'");
$dbHandler = Services::getService("DatabaseManager");
$result = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$result) {
throwError(new UnknownDBError("RecordManager"));
return false;
}
$array = array();
while ($result->hasMoreRows()) {
$array[] = $result->field(0);
$result->advanceRow();
}
$result->free();
return $array;
}
/**
* Loads the definition data from the database, if not already done.
* @return bool FALSE on error.
*/
function load()
{
// load our fields from the database
if ($this->loaded()) {
// throwError( new Error("Already loaded from the database for type ".HarmoniType::typeToString($this->_type).".","DataSetTypeDefinition",true));
return true;
}
// attempt to get our ID from the SchemaManager
if (!$this->_isCreatedByManager) {
throwError(new Error("The Schema object of type '" . HarmoniType::typeToString($this->_type) . "'\n\t\t\t\twas not meant to interface with the database.", "DataManager", true));
return false;
}
$query = new SelectQuery();
$query->addTable("dm_schema_field");
$query->addColumn("id", "", "dm_schema_field");
$query->addColumn("name", "", "dm_schema_field");
$query->addColumn("mult", "", "dm_schema_field");
$query->addColumn("required", "", "dm_schema_field");
$query->addColumn("active", "", "dm_schema_field");
$query->addColumn("fieldtype", "", "dm_schema_field");
$query->addColumn("description", "", "dm_schema_field");
$query->setWhere("fk_schema='" . addslashes($this->_id) . "'");
$dbHandler = Services::getService("DatabaseManager");
$result = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$result) {
throwError(new UnknownDBError("DataManager"));
}
$rows = array();
while ($result->hasMoreRows()) {
$rows[] = $result->getCurrentRow();
$result->advanceRow();
}
$result->free();
$this->populate($rows);
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();
}
/**
* testing nested subquery (query, which uses subquery, which uses subquery) and checking, that conditions are correct (for use in prepared queries)
*/
public function testSubqueryWithConditions()
{
$field1 = new Field('id', 0);
$subq2 = new SelectQuery(array('bans'));
$subq2->setSelect(array($field1));
$subq2->setWhere(new Condition('=', new Field('abc'), 10));
$subq = new SelectQuery(array('users'));
$subq->setSelect(array($field1));
$subq->setWhere(new AndOp(new Condition('>', new Field('age'), 14), new Condition('in', new Field('id'), $subq2), new Condition('=', new Field('regdt'), '2009-01-01')));
$q = new SelectQuery(array('posts'));
$q->setWhere(new AndOp(new Condition('>', new Field('comment'), 1), new Condition('in', new Field('user_id'), $subq), new Condition('=', new Field('funny'), 1)));
$this->assertEquals('SELECT `t0`.* FROM `posts` AS `t0` WHERE (`t0`.`comment` > :p1 AND `t0`.`user_id` IN (SELECT `t0`.`id` FROM `users` AS `t0` WHERE (`t0`.`age` > :p2 AND `t0`.`id` IN (SELECT `t0`.`id` FROM `bans` AS `t0` WHERE `t0`.`abc` = :p3) AND `t0`.`regdt` = :p4)) AND `t0`.`funny` = :p5)', $q->sql());
}
/**
* Loads the tag mapping data from the database, if it hasn't done so already.
* @return bool
* @access public
*/
function load()
{
if ($this->_loaded) {
return true;
}
// load the data
$query = new SelectQuery();
$query->addTable("dm_tag_map");
$query->addTable("dm_record_field", INNER_JOIN, "dm_tag_map.fk_record_field=dm_record_field.id");
$query->addTable("dm_schema_field", INNER_JOIN, "dm_record_field.fk_schema_field=dm_schema_field.id");
$query->addColumn("index", "record_field_index", "dm_record_field");
$query->addColumn("id", "record_field_id", "dm_record_field");
$query->addColumn("id", "schema_field_id", "dm_schema_field");
$query->setWhere("fk_tag='" . addslashes($this->_myID) . "'");
$dbHandler = Services::getService("DatabaseManager");
$result = $dbHandler->query($query, DATAMANAGER_DBID);
if (!$result) {
throwError(new UnknownDBError("RecordTag"));
}
$tagRows = array();
while ($result->hasMoreRows()) {
$a = $result->getCurrentRow();
$result->advanceRow();
$tagRows[] = $a;
}
$result->free();
$this->populate($tagRows);
return true;
}
public function testSqlFunctions()
{
$f = new SqlFunction('substring', array(new Field('name'), 5, 2));
$params = array();
$this->assertEquals('SUBSTRING(`t0`.`name`, 5, 2)', $f->getSql($params));
$this->assertEquals('substring', $f->getName());
try {
$f = new SqlFunction('there_is_no_such_function', new Field('name'));
$this->fail();
} catch (InvalidArgumentException $e) {
}
try {
$f = new SqlFunction('substring', new AndOp(array()));
$this->fail('wrong value accepted');
} catch (InvalidArgumentException $e) {
}
$q = new SelectQuery('test');
$q->setWhere(new Condition('>', new SqlFunction('length', new Field('foo')), 5));
$params = array();
$this->assertEquals('SELECT `t0`.* FROM `test` AS `t0` WHERE LENGTH(`t0`.`foo`) > :p1', $q->sql());
$parameters = $q->parameters();
$this->assertEquals('5', $parameters[':p1']);
}
/**
* Deletes all tags stored in the database for {@link DMRecord} id $id.
* @access public
* @param int $id
* @return void
*/
function deleteRecordTags($id)
{
if (!$id) {
return;
}
// first get a list of RecordTag IDs for this dataset.
$query = new SelectQuery();
$query->addTable("dm_tag");
$query->addColumn("id");
$query->setWhere("fk_record='" . addslashes($id) . "'");
$dbHandler = Services::getService("DatabaseManager");
$res = $dbHandler->query($query, DATAMANAGER_DBID);
$ids = array();
while ($res->hasMoreRows()) {
$ids[] = $res->field(0);
$res->advanceRow();
}
$res->free();
// now delete the datasets
$query = new DeleteQuery();
$query->setTable("dm_tag");
$query->setWhere("fk_record='" . addslashes($id) . "'");
$dbHandler->query($query, DATAMANAGER_DBID);
// and delete the mappings
$wheres = array();
foreach ($ids as $tagID) {
$wheres[] = "fk_tag='" . addslashes($tagID) . "'";
}
$query->reset();
$query->setTable("dm_tag_map");
$query->setWhere(implode(" OR ", $wheres));
$dbHandler->query($query, DATAMANAGER_DBID);
}
/**
* Will load the data structures for multiple {@link Schema}s.
* @param ref object An array containing the list of types IDs to be loaded.
* @return void
* @access public
*/
function loadMultiple($preloadTypes)
{
$ids = $preloadTypes;
if (count($ids)) {
// let's do it
$query = new SelectQuery();
$query->addTable("dm_schema_field");
$query->addColumn("id", "id", "dm_schema_field");
$query->addColumn("name", "label", "dm_schema_field");
$query->addColumn("mult", "mult", "dm_schema_field");
$query->addColumn("required", "required", "dm_schema_field");
$query->addColumn("active", "active", "dm_schema_field");
$query->addColumn("fieldtype", "fieldtype", "dm_schema_field");
$query->addColumn("fk_schema", "fk_schema", "dm_schema_field");
$wheres = array();
foreach ($ids as $id) {
$wheres[] = "fk_schema='" . addslashes($id) . "'";
}
$query->setWhere("(" . implode(" OR ", $wheres) . ")");
// print "<PRE>".MySQL_SQLGenerator::generateSQLQuery($query)."</PRE>";
$dbHandler = Services::getService("DatabaseManager");
$res = $dbHandler->query($query, DATAMANAGER_DBID);
$rows = array();
while ($res->hasMoreRows()) {
$row = $res->getCurrentRow();
$res->advanceRow();
$theID = $row["fk_schema"];
if (!isset($rows[$theID])) {
$rows[$theID] = array();
}
$rows[$theID][] = $row;
}
$res->free();
// printpre($rows);
// now distribute the rows among their respective objects
foreach (array_keys($rows) as $id) {
$obj = $this->getSchema($id);
if (!$obj->loaded()) {
$obj->populate($rows[$id]);
}
}
}
}
