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