예제 #1
0
 /**
  *
  * 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);
     }
 }
예제 #2
0
 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));
 }