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