/** * @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]); } } } }