/**
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 the SQL query that can be used to obtain the related records of this
* relationship. Note that the value returned from this method cannot be passed
* directly to xf_db_query(). It may still have unresolved wildcards and must
* be passed through Dataface_Record::parseString() to replace all wildcards.
*
* @param getBlobs If true then Blob columns will also be returned. Default is false.
* @type boolean
*
* @returns SQL Query
* @type string
*/
function getSQL($getBlobs = false, $where = 0, $sort = 0, $preview = 1)
{
$start = microtime_float();
import('SQL/Compiler.php');
import('SQL/Parser/wrapper.php');
$loadParserTime = microtime_float() - $start;
if (isset($this->_sql_generated[$where][$sort][$preview]) and $this->_sql_generated[$where][$sort][$preview]) {
/*
* The SQL has already been generated and stored. We can just return it.
*/
if ($getBlobs) {
// We will be returning blob columns as well
return $this->_schema['sql_with_blobs'][$where][$sort][$preview];
} else {
// We will NOT be returning BLOB columns
return $this->_schema['sql_without_blobs'][$where][$sort][$preview];
}
} else {
/*
* The SQL has not been generated yet. We will generate it.
*/
$this->_sql_generated[$where][$sort][$preview] = true;
if (!isset($this->_schema['sql_without_blobs'])) {
$this->_schema['sql_without_blobs'] = array();
}
if (!isset($this->_schema['sql_with_blobs'])) {
$this->_schema['sql_with_blobs'] = array();
}
if (defined('DATAFACE_USE_CACHE') and DATAFACE_USE_CACHE) {
$cache_key_blobs = 'tables/' . $this->_sourceTable->tablename . '/relationships/' . $this->_name . '/sql/withblobs';
$cache_key_noblobs = 'tables/' . $this->_sourceTable->tablename . '/relationships/' . $this->_name . '/sql/withoutblobs';
// we are using the APC cache
import('Dataface/Cache.php');
$cache =& Dataface_Cache::getInstance();
$this->_schema['sql_with_blobs'] = $cache->get($cache_key_blobs);
$this->_schema['sql_without_blobs'] = $cache->get($cache_key_noblobs);
}
if (!isset($this->_schema['sql_without_blobs'][$where][$sort][$preview]) or !isset($this->_schema['sql_with_blobs'][$where][$sort][$preview])) {
//if ( !$this->_schema['sql_without_blobs'][$where][$sort] ) $this->_schema['sql_without_blobs'] = array();
//if ( !$this->_schema['sql_with_blobs'] ) $this->_schema['sql_with_blobs'] = array();
$parsed = unserialize(serialize($this->_schema['parsed_sql']));
$parsed['column_names'] = array();
$parsed['column_aliases'] = array();
$parsed['columns'] = array();
$wrapper = new SQL_Parser_wrapper($parsed, 'MySQL');
$blobCols = array();
$tableAliases = array();
// For tables that have custom SQL defined we sub in its SQL
// here.
foreach (array_keys($parsed['tables']) as $tkey) {
if ($parsed['tables'][$tkey]['type'] == 'ident') {
$table =& Dataface_Table::loadTable($parsed['tables'][$tkey]['value']);
$proxyView = $table->getProxyView();
$tsql = $table->sql();
if (isset($tsql) and !$proxyView) {
$parsed['tables'][$tkey]['type'] = 'compiled_subselect';
$parsed['tables'][$tkey]['value'] = $tsql;
if (!$parsed['tables'][$tkey]['alias']) {
$parsed['tables'][$tkey]['alias'] = $table->tablename;
}
} else {
if ($proxyView) {
$parsed['tables'][$tkey]['value'] = $proxyView;
if (!$parsed['tables'][$tkey]['alias']) {
$parsed['tables'][$tkey]['alias'] = $table->tablename;
}
}
}
$tableAliases[$table->tablename] = $parsed['tables'][$tkey]['alias'];
unset($table);
unset($tsql);
}
}
$done = array();
$dups = array();
foreach ($this->fields(true) as $colname) {
// We go through each column in the query and add meta columns for length.
//$table =& Dataface_Table::getTableTableForField($colname);
list($tablename, $col) = explode('.', $colname);
if ($tablename != $this->getDomainTable() and Dataface_Table::loadTable($this->getDomainTable())->hasField($col)) {
// If this is a duplicate field we take the domain table value.
$dups[$col] = $this->getDomainTable();
continue;
}
if (isset($done[$col])) {
$dups[$col] = $tablename;
}
$done[$col] = true;
$table =& Dataface_Table::loadTable($tablename);
$alias = $wrapper->getTableAlias($tablename);
if (!$alias) {
$alias = $tablename;
}
$colname = $alias . '.' . $col;
if (isset($field)) {
unset($field);
}
$field =& $table->getField($col);
if (PEAR::isError($field)) {
$field = array();
}
if ($table->isPassword($col)) {
unset($table);
continue;
}
if ($table->isBlob($col)) {
$blobCols[] = $colname;
}
if (@$tableAliases[$tablename]) {
$tableAlias = $tableAliases[$tablename];
} else {
$tableAlias = $tablename;
}
if ($tableAlias) {
$colFull = '`' . $tableAlias . '`.`' . $col . '`';
//echo "Full";
} else {
$colFull = '`' . $col . '`';
}
$rfieldProps = array();
if (isset($this->_schema['field']) and isset($this->_schema['field'][$col])) {
$rfieldProps = $this->_schema['field'][$col];
}
$maxlen = 255;
if (@$rfieldProps['max_length']) {
$maxlen = intval($rfieldProps['max_length']);
}
if (in_array(strtolower($table->getType($col)), array('timestamp', 'datetime'))) {
$parsed['columns'][] = array('type' => 'compiled_func', 'table' => null, 'value' => "ifnull(convert_tz(" . $colFull . ",'SYSTEM','" . addslashes(df_tz_or_offset()) . "'), " . $colFull . ")", 'alias' => $col);
} else {
if ($preview and $table->isText($col) and !@$field['struct'] and !$table->isXML($col)) {
$parsed['columns'][] = array('type' => 'compiled_func', 'table' => null, 'value' => "SUBSTRING({$colFull}, 1, {$maxlen})", 'alias' => $col);
} else {
$parsed['columns'][] = array('type' => 'ident', 'table' => $tableAlias, 'value' => $col, 'alias' => null);
}
}
//$wrapper->addMetaDataColumn($colname);
// Note: Removed *length* metadata columns for now.. not hard to add
// back. Will wait to see if anyone screams!
// Steve Hannah 071229
unset($table);
}
if ($where !== 0) {
$whereClause = $where;
// Avoid ambiguous column error. Any duplicate columns need to be specified.
foreach ($dups as $dcolname => $dtablename) {
$whereClause = preg_replace('/([^.]|^) *`' . preg_quote($dcolname) . '`/', '$1 `' . $dtablename . '`.`' . $dcolname . '`', $whereClause);
}
$wrapper->addWhereClause($whereClause);
}
if ($sort !== 0) {
$sortClause = $sort;
foreach ($dups as $dcolname => $dtablename) {
$sortClause = preg_replace('/([^.]|^) *`' . preg_quote($dcolname) . '`/', '$1 `' . $dtablename . '`.`' . $dcolname . '`', $sortClause);
}
$wrapper->setSortClause($sortClause);
}
//$compiler = new SQL_Compiler(null, 'mysql');
$compiler =& SQL_Compiler::newInstance('mysql');
$compiler->version = 2;
$this->_schema['sql_with_blobs'][$where][$sort][$preview] = $compiler->compile($parsed);
foreach ($blobCols as $blobCol) {
$wrapper->removeColumn($blobCol);
}
$this->_schema['sql_without_blobs'][$where][$sort][$preview] = $compiler->compile($parsed);
if (defined('DATAFACE_USE_CACHE') and DATAFACE_USE_CACHE) {
$cache->set($cache_key_blobs, $this->_schema['sql_with_blobs']);
$cache->set($cache_key_noblobs, $this->_schema['sql_without_blobs']);
}
}
/*
* Now that the SQL is generated we can call ourselves and the first
* case will now be initiated (ie: the generated sql will be returned).
*/
$timeToGenerate = microtime_float() - $start;
if (DATAFACE_DEBUG) {
$this->app->addDebugInfo("Time to generate sql for relationship {$this->name} : {$timeToGenerate}");
}
return $this->getSQL($getBlobs, $where, $sort);
}
}
/**
* 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;
}