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);
}
function SQL_Parser($string = null, $dialect = "MySQL", $useCache = true)
{
$this->dialect = $dialect;
if ($useCache) {
$parser =& SQL_Parser::loadParser($dialect);
$this->cloneDialect($parser);
} else {
$this->setDialect($dialect);
}
if (is_string($string)) {
$this->lexer = new Lexer($string, 1);
$this->lexer->symbols =& $this->symbols;
}
}
/**
* Field definitions of fields that have been grafted onto this table.
* It is possible to provide an '__sql__' parameter to the fields.ini
* file that will provide a custom query to be performed. This is really
* only meant for grafting extra columns onto the table that weren't there
* before. These columns will appear in list and view mode, but you won't
* be able to edit them.
* @returns array Array of field definitions of grafted fields. Each field
* def is of the form:
* array('Field'=>fieldname, 'Type'=>varchar(32), 'widget'=>array('label'=>.., 'description'=>..))
*/
function &graftedFields($includeParent = true)
{
$tsql = $this->sql();
if ($includeParent) {
$includeParent = 1;
} else {
$includeParent = 0;
}
if (!isset($this->_cache[__FUNCTION__][intval($includeParent)])) {
//if ( !isset($this->_grafted_fields) ){
$this->_grafted_fields = array();
if (isset($tsql)) {
$this->_grafted_fields = array();
import('SQL/Parser.php');
$parser = new SQL_Parser(null, 'MySQL');
$data = $parser->parse($tsql);
foreach ($data['columns'] as $col) {
if ($col['type'] != 'glob') {
$alias = @$col['alias'] ? $col['alias'] : $col['value'];
if (isset($this->_fields[$alias])) {
continue;
}
$this->_grafted_fields[$alias] = $this->_newSchema('varchar(32)', $alias);
$this->_grafted_fields[$alias]['grafted'] = 1;
if (isset($this->_atts[$alias]) and is_array($this->_atts[$alias])) {
$this->_parseINISection($this->_atts[$alias], $this->_grafted_fields[$alias]);
}
//array('Field'=>$alias, 'name'=>$alias, 'Type'=>'varchar(32)', 'widget'=>array('label'=>$alias, 'description'=>''));
}
}
}
if ($includeParent) {
// We now want to load the parent table columns as well.
$parent =& $this->getParent();
if (isset($parent)) {
$this->_grafted_fields = array_merge($parent->fields(false, true), $this->_grafted_fields);
}
}
$this->_cache[__FUNCTION__][intval($includeParent)] = $this->_grafted_fields;
}
return $this->_cache[__FUNCTION__][intval($includeParent)];
}
function getTableDependencies($sql, $lang = null)
{
$app =& Dataface_Application::getInstance();
$key = $this->memcache_get_key($sql, $lang);
$key .= '&-action=deps';
$key = md5($key);
if (DATAFACE_EXTENSION_LOADED_APC && !isset($_GET['--clear-cache'])) {
$deps = apc_fetch($key);
if (is_array($deps)) {
return $deps;
}
} else {
if (isset($_SESSION[$key]) && !isset($_GET['--clear-cache'])) {
$deps = $_SESSION[$key];
if (is_array($deps)) {
return $deps;
}
}
}
// We actually need to calculate the dependencies, so we will
// parse the SQL query.
import('SQL/Parser.php');
$parser = new SQL_Parser(null, 'MySQL');
$data =& $parser->parse($sql);
if (PEAR::isError($data)) {
return $data;
}
$tables = array_unique($data['all_tables']);
if (@$app->_conf['cache_queries_log']) {
$fp = fopen('/tmp/querylog.log', 'a');
fwrite($fp, "\n[" . date('Y-m-d H:i:s') . "] Dependencies: " . implode(',', $tables) . " " . $sql);
fclose($fp);
}
//import('SQL/Parser/wrapper.php');
//$wrapper = new SQL_Parser_wrapper($data);
//$tables = $wrapper->getTableNames();
foreach ($tables as $tid => $table) {
if (preg_match('/^dataface__view_(.*)_[a-z0-9]{32}$/', $table, $matches)) {
$tables[$tid] = $table = $matches[1];
}
$tobj =& Dataface_Table::loadTable($table, null, true);
if (is_a($tobj, 'Dataface_Table') and isset($tobj->_atts['__dependencies__'])) {
$deps = array_map('trim', explode(',', $tobj->_atts['__dependencies__']));
$tables = array_merge($tables, $deps);
}
}
if (isset($app->_conf['__dependencies__'])) {
$deps = array_map('trim', explode(',', $app->_conf['__dependencies']));
$tables = array_merge($tables, $deps);
}
$deps = array_unique($tables);
if (DATAFACE_EXTENSION_LOADED_APC) {
apc_store($key, $deps);
} else {
$_SESSION[$key] = $deps;
}
return $deps;
}
/**
* Gets the values of the foreign keys of a particular relationship. This returns an associative
* array with the following structure:
* Array(
* "table1" => Array( "field1"=>"value1", "field2"=>"value2", ... ),
* "table2" => Array( "field1"=>"value1", "field2"=>"value2", ... ),
* ...
* );
* @param relationship_name The name of the relationship
* @param values Supplementary values passed as array with keys = absolute field names, values = serialized values.
* @param sql If provided, this will be used as the select statement that we dissect.
* @parseValues If true we parse out variables. If false, we simply return the variables to be parsed later.
* @throws PEAR_Error if there is insufficient values supplied for the foreign key to work.
*
*/
function getForeignKeyValues($values = null, $sql = null, $record = null)
{
if (is_object($record)) {
$record_id = $record->getId();
}
if (!isset($values) and !isset($sql) and !isset($record) and isset($this->_cache[__FUNCTION__])) {
return $this->_cache[__FUNCTION__];
}
if (!isset($values) and !isset($sql) and is_object($record) and isset($this->_cache[__FUNCTION__ . $record_id])) {
return $this->_cache[__FUNCTION__ . $record_id];
}
// Strategy:
// ----------
// 1. Label all fields involved in the foreign key so that fields that are equal have the
// same label. Eg: In the query:
// select *
// from Students
// inner join Student_Courses
// on Students.id = Student_Courses.studentid
// inner join Courses
// on Student_Courses.courseid = Courses.id
// where
// Students.id = '$id'
//
// In the above query Students.id and Student_Course.studentid would have the same label, and
// Student_Courses.courseid and Courses.id would have the same label.
// ie: Label(Students.id) = Label(Student_Courses.studentid) ^ Label(Student_Courses.courseid) = Label(Courses.id)
//
// 2. Assign values for each label. All fields with a particular label have the same value.
// In the above query, we would have:
// Value(Label(Students.id)) = '$id'
// **Note from above that Label(Students.id)=Label(Student_Courses.studentid) so their values are also equal.
//
// 3. For labels without a value find out if one of the fields assuming that label is an auto-increment field. If so
// we assign the special value '__Tablename__auto_increment__' where "Tablename" is the name of the table whose
// field is to be auto incremented.
//
// 4. Collect the the values in a structure so that we can lookup the values of any particular field in any particular
// table easily. Return this structure.
$relationship =& $this->_schema;
if ($sql !== null) {
// An SQL query was specified as a parameter, we parse this and use the resulting data structure
// for the rest of the computations.
if (is_string($sql)) {
$parser = new SQL_Parser(null, 'MySQL');
$sql = $parser->parse($sql);
}
$select =& $sql;
} else {
// We use the 'parsed_sql' entry in the relationship as the basis for our dissection.
$select =& $relationship['parsed_sql'];
}
// Build equivalence classes for column names.
$labels = array();
$vals = array();
$this->_makeEquivalenceLabels($labels, $vals, $select);
// Fill in some default values
if (is_array($values)) {
foreach ($values as $field_name => $field_value) {
if (!$field_value) {
continue;
}
// we don't want empty and null values to act as defaults because they
// tend to screw things up when we are adding related records.
if (isset($labels[$field_name])) {
$label = $labels[$field_name];
} else {
$label = $field_name;
$labels[$field_name] = $label;
}
$vals[$label] = $field_value;
}
}
// next we need to find 'circular links'. Ie: There may be columns that are only specified to be equal to each other. Most of the
// time this means that one of the fields is an auto increment field that will be automatically filled in. We need to insert
// a special value (in this case), so that we know this is the case.
foreach ($labels as $field_name => $label) {
if (!isset($vals[$label])) {
$field =& Dataface_Table::getTableField($field_name);
$table_auto_increment = null;
foreach ($labels as $auto_field_name => $auto_label) {
if ($auto_label == $label) {
$auto_field =& Dataface_Table::getTableField($auto_field_name);
if ($auto_field['Extra'] == 'auto_increment') {
list($table_auto_increment) = explode('.', $auto_field_name);
unset($auto_field);
break;
}
unset($auto_field);
}
}
if (isset($table_auto_increment)) {
//list($table) = explode('.', $field_name);
$vals[$label] = "__" . $table_auto_increment . "__auto_increment__";
} else {
$vals[$label] = new Dataface_Relationship_ForeignKey($this, $labels, $label);
}
unset($field);
}
}
$table_cols = array();
foreach ($labels as $field_name => $label) {
$fieldArr =& Dataface_Table::getTableField($field_name);
list($table, $field) = explode('.', $field_name);
if (!$table) {
continue;
}
if (!isset($table_cols[$table])) {
$table_cols[$table] = array();
}
$table_cols[$table][$field] = (is_scalar(@$vals[$label]) and $record !== null and !preg_match('/(blob|binary)/', strtolower($fieldArr['Type']))) ? $record->parseString(@$vals[$label]) : @$vals[$label];
unset($fieldArr);
}
// make sure that each table at least sets all of the mandatory fields.
foreach ($table_cols as $table => $cols) {
$tableObject =& Dataface_Table::loadTable($table);
foreach (array_keys($tableObject->mandatoryFields()) as $key) {
if (!isset($cols[$key])) {
$this->errors[] = PEAR::raiseError(DATAFACE_TABLE_RELATED_RECORD_REQUIRED_FIELD_MISSING_ERROR, null, null, null, "Could not generate SQL to add new record to relationship '" . $this->_name . "' because not all of the required fields have values. In particular, the field '{$key}' of table '{$table}' is missing but is a key of the table.");
}
}
unset($tableObject);
}
if (!isset($values) and !isset($sql) and !isset($record)) {
$this->_cache[__FUNCTION__] = $table_cols;
}
if (!isset($values) and !isset($sql) and is_object($record)) {
$this->_cache[__FUNCTION__ . $record_id] = $table_cols;
}
return $table_cols;
}
<?php
/**
* Pipe queries to this program and get returned the table names used,
* comma seperated.
*/
if (!(include_once 'SQL/Parser.php')) {
echo "You need to: \n";
echo " aptitude install php-pear\n";
echo " pear install -f SQL_Parser\n";
exit(1);
}
$stdin = fopen('php://stdin', 'r');
$sql = '';
while (!feof($stdin)) {
$sql .= fgets($stdin);
}
fclose($stdin);
$sql = str_replace('`', '', $sql);
$SQL_Parser = new SQL_Parser($sql);
$parsed = $SQL_Parser->parse();
echo join(', ', $parsed['table_names']) . "\n";
/**
* Returns the parsed version of the sql for this view.
*/
function _parseSQL()
{
if (!isset($this->sql_data)) {
import('SQL/Parser.php');
$parser = new SQL_Parser(null, 'MySQL');
$this->sql_data = $parser->parse($this->sql);
}
return $this->sql_data;
}
function test_make_equivalence_labels()
{
$labels = array();
$values = array();
require_once 'SQL/Parser.php';
$sql = "select * \n\t\t\t\t\tfrom Profiles p \n\t\t\t\t\tinner join Student_Courses sc \n\t\t\t\t\t\ton p.id = sc.studentid\n\t\t\t\t\tinner join Courses c\n\t\t\t\t\t\ton c.id = sc.courseid\n\t\t\t\twhere\n\t\t\t\t\tp.id = '\$id'";
$parser = new SQL_Parser();
$parsed = $parser->parse($sql);
$courses =& $this->table1->getRelationship('courses');
$courses->_makeEquivalenceLabels($labels, $values, $parsed);
$this->assertEquals(array("Profiles.id" => "Profiles.id", "Student_Courses.studentid" => "Profiles.id", "Courses.id" => "Courses.id", "Student_Courses.courseid" => "Courses.id"), $labels);
$this->assertEquals(array("Profiles.id" => '$id'), $values);
}
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);
}
}
