/**
* Reads a record from the database and returns it as a Dataface_ViewRecord
* object.
* @param $params Associative array of key/value pairs to search.
*
*/
function getRecord($params = array())
{
$data = $this->_parseSQL();
$wrapper = new SQL_Parser_wrapper($data);
$where = array();
foreach ($params as $key => $value) {
$tablename = $this->getTableName($key);
if (isset($tablename)) {
$where[] = '`' . addslashes($tablename) . '`.`' . $key . '`=\'' . addslashes($params[$key]) . '\'';
}
}
$where = implode(' AND ', $where);
$wrapper->addWhereClause($where);
$compiler =& $this->_getCompiler();
$sql = $compiler->compile($data);
$db =& Dataface_DB::getInstance();
$res = $db->query($sql);
if (PEAR::isError($res)) {
return $res;
}
if (!$res) {
return PEAR::raiseError(mysql_error($this->app->_db));
}
if (mysql_num_rows($res) == 0) {
return null;
}
$vals = mysql_fetch_assoc($res);
mysql_free_result($res);
return $this->newRecord($vals);
}
/**
* Returns an SQL query that will obtain the domain of this relationship. The Domain
* is slightly different than the actual relationship, in that it returns all eligible
* rows that can be added to the relationship (and rows already in the relationship).
*/
function getDomainSQL()
{
$relationship =& $this->_schema;
// obtain reference to the relationship in question
// The 'domain_sql' attribute of a relationship defines the SQL select statement that
// is used to obtain the set of candidates for a relationship. This can be specified
// in the ini file using the __domain__ attribute of a relationship, or it can be parsed
// from the existiing 'sql' attribute.
if (!isset($relationship['domain_sql'])) {
import('SQL/Compiler.php');
// compiles SQL tree structure into query strings
import('SQL/Parser/wrapper.php');
// utility methods for dealing with SQL structures
$compiler = new SQL_Compiler();
// the compiler we will use to generate the eventual SQL
$parsed_sql = unserialize(serialize($relationship['parsed_sql']));
// we make a deep copy of the existing 'parsed_sql' structure that was
// created in the "readRelationshipsIniFile" method. We deep copy, because
// some of the methods in SQL_Parser_wrapper work directly on the
// datastructure - but we want to leave it unchanged.
$wrapper = new SQL_Parser_wrapper($parsed_sql);
// create a new wrapper to operate on the sql data structure.
$wrapper->removeWhereClausesWithTable($this->_sourceTable->tablename);
$wrapper->removeJoinClausesWithTable($this->_sourceTable->tablename);
// We remove all Where and Join clauses that use columns from the current table.
// This is because portions of the sql pertaining to the current table
// likely represent specifications within the domain to mark that an
// element of the domain is related to the current table.
$wrapper->removeWhereClausesWithPattern('/\\$\\w+/');
$wrapper->removeJoinClausesWithPattern('/\\$\\w+/');
// Similarly we need to remove any clauses containing variables which
// get filled in by the current table. The rationale is the same as
// for removing clauses pertaining to the current table.
$fkVals = $this->getForeignKeyValues();
// We obtain the foreign key values for this relationship because they
// will help us to decide which columns in the remaining query are
// helpful for obtaining the domain.
$uselessTables = array();
// will hold list of tables that we don't need
$fkTables = array_keys($fkVals);
// list of tables that are involved in foreign key relationships in this
// relationship.
foreach ($fkVals as $fkTable => $fkFields) {
$foundVal = 0;
$foundLink = 0;
// keep track of which tables actually have real values assigned.
foreach ($fkFields as $fieldVal) {
//if ( !preg_match('/^__(\w+)_auto_increment__$/', $fieldVal) ){
// // A field with a value of the form __Tablename__auto_increment__ is a placeholder
// // for an auto generated id. If the only values specified for a table are placeholders
// // then that table is pretty much useless as a domain query... it can be eliminated.
// $foundVal++;
//
//
//}
if (is_scalar($fieldVal) and strpos($fieldVal, '$') === 0) {
// This table is linked directly to the current table... hence it is only a join
// table.
$foundLink++;
}
}
if ($foundLink) {
// no real valus found.. mark table as useless.
$uselessTables[] = $fkTable;
}
}
foreach ($uselessTables as $table_name) {
// Remove all useless tables from the query's where and join clauses.
$wrapper->removeWhereClausesWithTable($table_name);
$wrapper->removeJoinClausesWithTable($table_name);
$wrapper->removeColumnsFromTable($table_name);
}
$domain_tables = array_diff($relationship['selected_tables'], $uselessTables);
if (!$domain_tables) {
$domain_tables = $relationship['selected_tables'];
}
$table_ranks = array();
foreach ($this->_schema['columns'] as $col) {
list($tname) = explode('.', $col);
if (!isset($table_ranks[$tname])) {
$table_ranks[$tname] = 0;
}
$table_ranks[$tname]++;
}
$high = null;
$high_score = 0;
foreach ($domain_tables as $dt) {
if ($table_ranks[$dt] > $high_score) {
$high = $dt;
$high_score = $table_ranks[$dt];
}
}
$domain_tables = array($high);
if (count($domain_tables) !== 1) {
return PEAR::raiseError("Error calculating domain tables for relationship '" . $this->_name . "'. Selected tables are {" . implode(',', $relationship['selected_tables']) . "} and Useless tables are {" . implode(',', $uselessTables) . "}.", null, null, null, 1);
}
$relationship['domain_table'] = array_pop($domain_tables);
$wrapper->packTables();
// Previous steps have only eliminated useless tables with respect to query
// parameters. There may still be some tables listed in the query that don't
// offer anything. Notice that we pass the list of selected tables to this
// method to indicate that tables whose columns are selected need to be there
// and should be left intact.
$relationship['domain_sql'] = $compiler->compile($parsed_sql);
}
return $relationship['domain_sql'];
}
function test_set_sort_clause()
{
$parser = new SQL_Parser(null, 'MySQL');
$compiler = new SQL_Compiler();
$sql = "SELECT a,b,c from Foo";
$parsed = $parser->parse($sql);
$wrapper = new SQL_Parser_wrapper($parsed, 'MySQL');
$wrapper->setSortClause("c");
$this->assertEquals("select a, b, c from Foo order by c asc", $compiler->compile($parsed));
$wrapper->setSortClause("b");
$this->assertEquals("select a, b, c from Foo order by b asc", $compiler->compile($parsed));
$wrapper->setSortClause("b desc");
$this->assertEquals("select a, b, c from Foo order by b desc", $compiler->compile($parsed));
$wrapper->setSortClause("b desc, c");
$this->assertEquals("select a, b, c from Foo order by b desc, c asc", $compiler->compile($parsed));
$wrapper->addSortClause("d");
$this->assertEquals("select a, b, c from Foo order by b desc, c asc, d asc", $compiler->compile($parsed));
}
