/** * 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 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); } }
function test_add_where_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->addWhereClause("c=5"); $sqlout = $compiler->compile($parsed); $this->assertEquals("select a, b, c from Foo where c = 5", $sqlout); $wrapper->addWhereClause("d='five hundred'"); $sqlout = $compiler->compile($parsed); $this->assertEquals("select a, b, c from Foo where c = 5 and d = 'five hundred'", $sqlout); $wrapper->addWhereClause("Foo.c=60"); $this->assertEquals("select a, b, c from Foo where (c = 5 and d = 'five hundred') and Foo.c = 60", $compiler->compile($parsed)); $sql = "SELECT a,b,c from Foo f inner join Bar b where (f.a='5' and b.b='6')"; $parsed = $parser->parse($sql); $wrapper = new SQL_Parser_wrapper($parsed, 'MySQL'); $wrapper->addWhereClause("Foo.c='7' and Bar.d='8'", 'or'); //$wrapper->unresolveWhereClauseColumns($parsed['where_clause']); $this->assertEquals("select a, b, c from Foo as f inner join Bar as b where (f.a = '5' and b.b = '6') or (f.c = '7' and b.d = '8')", $compiler->compile($parsed)); //print_r($parser->parse("SELECT * FROM Foo order by Bar.c")); //print_r($parsed); }