function testTranslate()
{
//echo $this->DB->translate_query('select PubType, BiblioString from Publications', 'fr');
//exit;
$sql = 'select PubType, BiblioString from Publications limit 5';
$start = microtime_float();
$res = $this->DB->query($sql, $this->db) or die(xf_db_error($this->db));
while ($row = xf_db_fetch_assoc($res)) {
print_r($row);
}
$stop1 = microtime_float() - $start;
$start = microtime_float();
$res3 = $this->DB->query($sql, $this->db) or die(xf_db_error($this->db));
while ($row = xf_db_fetch_assoc($res3)) {
print_r($row);
}
$stop3 = microtime_float() - $start;
$start = microtime_float();
$res2 = xf_db_query($sql, $this->db) or die(xf_db_error($this->db));
while ($row = xf_db_fetch_assoc($res2)) {
print_r($row);
}
$stop2 = microtime_float() - $start;
echo "MySQL: {$stop2} ; Translated: {$stop1} ; Second translated: {$stop3}";
$parser = new SQL_Parser(null, 'MySQL');
//$sql = 'select IFNULL(f.PubType,d.PubType) as PubType, IFNULL(f.BiblioString,d.BiblioString) as BiblioString from Publications d left join Publications_fr f on d.PubID=f.PubID';
$data = $parser->parse($sql);
//print_r($data);
$compiler = new SQL_Compiler();
echo $compiler->compile($data);
}
/**
Functions defined in this class:
function translateQuery($query, $lang=null){}
function translateSelectQuery($query, $lang=null){}
function translateUpdateQuery($query, $lang=null){}
function translateInsertQuery($query, $lang=null){}
function translateIdent($ident, $lang=null){}
function translateFunction($func, $lang=null){}
function translateJoinClause($func, $lang=null){}
function translateWhereClause($func, $lang=null){}
*/
function Dataface_QueryTranslator($lang = null)
{
$this->app =& Dataface_Application::getInstance();
if (!isset($lang)) {
$lang = $this->app->_conf['lang'];
}
$this->_lang = $lang;
//if ( !@$this->app->_conf['default_language_no_fallback'] or $this->app->_conf['default_language'] != $lang ){
// In Dataface 0.6.10 the default behavior of the query translator was
// changed so that default language queries are not changed. This
// behavior can be reversed by adding the default_language_no_fallback=1
// flag to the conf.ini file.
import('SQL/Parser.php');
$this->_parser = new SQL_Parser(null, 'MySQL');
import('SQL/Compiler.php');
$this->_compiler =& SQL_Compiler::newInstance('mysql');
$this->_compiler->version = 2;
//}
}
/**
* Obtains a reference to this object's SQL compiler.
*/
function &_getCompiler()
{
if (!isset($this->_compiler)) {
import('SQL/Compiler.php');
$this->_compiler = SQL_Compiler::newInstance('mysql');
}
return $this->_compiler;
}
/**
* 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'];
}
/**
* Obtains a reference to an SQL compiler for this view.
*/
function &_getCompiler()
{
if (isset($this->_cache[__FUNCTION__])) {
return $this->_cache[__FUNCTION__];
}
import('SQL/Compiler.php');
$compiler = SQL_Compiler::newInstance('mysql');
$compiler->version = 2;
$this->_cache[__FUNCTION__] =& $compiler;
return $compiler;
}
function testMySQLSelect()
{
$this->parser = new SQL_Parser(null, 'MySQL');
$this->compiler =& SQL_Compiler::newInstance('mysql');
$this->compiler->version = 2;
if (PEAR::isError($this->compiler)) {
trigger_error($this->compiler->getMessage());
}
echo "The compiler is a " . get_class($this->compiler);
include 'mysql_select.php';
$this->runTests($tests);
}
function compileSelect()
{
// save the command and set quantifiers
$sql = 'select ';
if (isset($this->tree['set_quantifier'])) {
$sql .= $this->tree['set_quantifier'] . ' ';
}
if ($this->version <= 1) {
// This object uses legacy settings with the old data structures.
// save the column names and set functions
for ($i = 0; $i < sizeof($this->tree['column_names']); $i++) {
$column = $this->compileIdent($this->tree['column_names'][$i]);
// MOD START 051026 shannah@sfu.ca - Fix to get rid of Notice: Undefined index: column_aliases
//if ($this->tree['column_aliases'][$i] != '') {
if (isset($this->tree['column_aliases']) and $this->tree['column_aliases'][$i] != '') {
// MOD END 051026 shannah@sfu.ca
$column .= ' as ' . $this->compileIdent($this->tree['column_aliases'][$i]);
}
$column_names[] = $column;
}
// MOD ADD START PART 1 of 2 - 051026 shannah@sfu.ca - Fix to get rid of "Notice: Undefined index: set_function"
if (isset($this->tree['set_function'])) {
// MOD ADD END PART 1 of 2
for ($i = 0; $i < sizeof($this->tree['set_function']); $i++) {
$column_names[] = $this->compileFunction($this->tree['set_function'][$i]);
}
// MOD ADD START PART 2 of 2 - 051026 shannah@sfu.ca
}
// MOD ADD END PART 2 or 2
} else {
// This object uses the new settings with new data structures.
for ($i = 0; $i < sizeof($this->tree['columns']); $i++) {
switch ($this->tree['columns'][$i]['type']) {
case 'ident':
if ($this->tree['columns'][$i]['table']) {
$column = $this->compileIdent($this->tree['columns'][$i]['table']) . '.' . $this->compileIdent($this->tree['columns'][$i]['value']);
} else {
$column = $this->compileIdent($this->tree['columns'][$i]['value']);
}
if ($this->tree['columns'][$i]['alias']) {
$column .= ' as ' . $this->compileIdent($this->tree['columns'][$i]['alias']);
}
break;
case 'func':
$column = $this->compileFunction($this->tree['columns'][$i]['value']);
break;
case 'compiled_func':
$column = $this->tree['columns'][$i]['value'];
if ($this->tree['columns'][$i]['alias']) {
$column .= ' as ' . $this->compileIdent($this->tree['columns'][$i]['alias']);
}
break;
case 'glob':
if ($this->tree['columns'][$i]['table']) {
$column = $this->compileIdent($this->tree['columns'][$i]['table']) . '.' . $this->tree['columns'][$i]['value'];
} else {
$column = $this->tree['columns'][$i]['value'];
}
break;
case 'subselect':
$subCompiler =& $this->newInstance($this->type);
$subCompiler->version = $this->version;
$column = '(' . $subCompiler->compile($this->tree['columns'][$i]['value']) . ')';
unset($subCompiler);
if ($this->tree['columns'][$i]['alias']) {
$column .= ' as ' . $this->compileIdent($this->tree['columns'][$i]['alias']);
}
break;
case 'expression':
$column = $this->compileExpression('expression', $this->tree['columns'][$i]['value']);
if (@$this->tree['columns'][$i]['alias']) {
$column .= ' as ' . $this->compileIdent($this->tree['columns'][$i]['alias']);
}
break;
// This section added to try handle literals in the select list
// This section added to try handle literals in the select list
case 'real_val':
case 'int_val':
case 'null':
$column = $this->tree['columns'][$i]['value'];
if ($this->tree['columns'][$i]['alias']) {
$column .= ' as ' . $this->compileIdent($this->tree['columns'][$i]['alias']);
}
break;
case 'text_val':
$column = '\'' . $this->tree['columns'][$i]['value'] . '\'';
if ($this->tree['columns'][$i]['alias']) {
$column .= ' as ' . $this->compileIdent($this->tree['columns'][$i]['alias']);
}
break;
default:
return PEAR::raiseError("Unexpected column type '" . $this->tree['columns'][$i]['type']);
}
$column_names[] = $column;
}
}
if (isset($column_names)) {
$sql .= implode(", ", $column_names);
}
// save the tables
$sql .= ' from ';
if ($this->version <= 1) {
for ($i = 0; $i < sizeof($this->tree['table_names']); $i++) {
$sql .= $this->compileIdent($this->tree['table_names'][$i]);
if ($this->tree['table_aliases'][$i] != '') {
$sql .= ' as ' . $this->compileIdent($this->tree['table_aliases'][$i]);
}
if ($this->tree['table_join_clause'][$i] != '') {
$search_string = $this->compileSearchClause($this->tree['table_join_clause'][$i]);
if (PEAR::isError($search_string)) {
return $search_string;
}
$sql .= ' on ' . $search_string;
}
if (isset($this->tree['table_join'][$i])) {
if ($this->tree['table_join'][$i] != ',') {
$sql .= ' ';
}
$sql .= $this->tree['table_join'][$i] . ' ';
}
}
} else {
// This object is working with the new version of the data structure
// that supports subselects. i.e. it uses the 'tables' array instead
// of 'table_names' and 'table_aliases'.
for ($i = 0; $i < sizeof($this->tree['tables']); $i++) {
switch ($this->tree['tables'][$i]['type']) {
case 'ident':
$sql .= $this->compileIdent($this->tree['tables'][$i]['value']);
break;
case 'subselect':
$compiler = SQL_Compiler::newInstance($this->type);
$compiler->version = $this->version;
$temp = $compiler->compile($this->tree['tables'][$i]['value']);
if (PEAR::isError($temp)) {
return $temp;
}
$sql .= '(' . $temp . ')';
break;
case 'compiled_subselect':
$sql .= '(' . $this->tree['tables'][$i]['value'] . ')';
break;
default:
return $this->raiseError("Unexpected type " . $this->tree['tables'][$i]['type'] . " on line " . __LINE__ . " of file " . __FILE__);
}
if ($this->tree['tables'][$i]['alias'] != '') {
$sql .= ' as ' . $this->compileIdent($this->tree['tables'][$i]['alias']);
}
if ($this->tree['table_join_clause'][$i] != '') {
$search_string = $this->compileSearchClause($this->tree['table_join_clause'][$i]);
if (PEAR::isError($search_string)) {
return $search_string;
}
$sql .= ' on ' . $search_string;
}
if (isset($this->tree['table_join'][$i])) {
if ($this->tree['table_join'][$i] != ',') {
$sql .= ' ';
}
$sql .= $this->tree['table_join'][$i] . ' ';
}
}
}
// save the where clause
if (isset($this->tree['where_clause'])) {
$search_string = $this->compileSearchClause($this->tree['where_clause']);
if (PEAR::isError($search_string)) {
return $search_string;
}
$sql .= ' where ' . $search_string;
}
// save the group by clause
if (isset($this->tree['group_by'])) {
$group_by = array();
foreach ($this->tree['group_by'] as $col) {
switch ($col['type']) {
case 'ident':
$group_by[] = $this->compileIdent($col['value']);
break;
case 'function':
$group_by[] = $this->compileFunction($col['value']);
break;
default:
return $this->raiseError("Unexpected type: " . $col['type'] . " in group by clause");
}
}
$sql .= ' group by ' . implode(', ', $group_by);
}
// save the having clause
if (isset($this->tree['having_clause'])) {
$search_string = $this->compileSearchClause($this->tree['having_clause']);
if (PEAR::isError($search_string)) {
return $search_string;
}
$sql .= ' having ' . $search_string;
}
// save the order by clause
if (isset($this->tree['sort_order'])) {
foreach ($this->tree['sort_order'] as $key => $value) {
if ($value['type'] == 'ident') {
$sort_order[] = $this->compileIdent($value['value']) . ' ' . $value['order'];
} else {
if ($value['type'] == 'function') {
$sort_order[] = $this->compileFunction($value['value'], false) . ' ' . $value['order'];
}
}
}
$sql .= ' order by ' . implode(', ', $sort_order);
}
// save the limit clause
if (isset($this->tree['limit_clause'])) {
// $sql .= ' limit '.(isset($this->tree['limit_clause']['start']) && $this->tree['limit_clause']['start'] ? $this->tree['limit_clause']['start'].',' : '').$this->tree['limit_clause']['length'];
$start = $this->tree['limit_clause']['start'] ? $this->tree['limit_clause']['start'] : '0';
$sql .= ' limit ' . ($start ? $start . ',' : '') . $this->tree['limit_clause']['length'];
}
return $sql;
}
function test_misc()
{
$parser = new SQL_Parser(null, 'MySQL');
$compiler = new SQL_Compiler();
print_r($parser->parse("select foo.a from foo"));
print_r($parser->parse("select a, b, c from foo"));
print_r($parser->parse("SELECT F.a as column1, B.b as column2 FROM Foo F inner join Bar B on F.c=B.c where column1 = 'val1' and column2 = 'val2'"));
$res = $parser->parse("SELECT f.a from Foo f where conv('a',16,2) = '2005'");
if (PEAR::isError($res)) {
echo $res->toString() . Dataface_Error::printStackTrace();
} else {
print_r($res);
}
$res = $parser->parse("SELECT * from Publications where Expires > NOW()");
if (PEAR::isError($res)) {
echo $res->toString() . Dataface_Error::printStackTrace();
} else {
print_r($res);
echo $compiler->compile($res);
}
}
