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