SQL parser unable to parse the query for use of caching

[kevinwen]

I have a medium complicated query making the SQL Parser (under xataface/lib/SQL/Parser.php) not work at all. the query I define in fields.ini looks like this:

SELECT DISTINCT c.*, c.change_memo_id as change_memo_id_find, s.status_name, u.user_name as submitted_by_name, w.workflow_name, a.category_name, b.subcategory_name, o.country_name, IFNULL(r.region_name, '<blank>') as region_name, IFNULL(l.locality_name, '<blank>') as locality_name, d.domain_name, u2.user_name as last_updated_by_name, IFNULL(c.date_approved, '<blank>') as date_approved_formatted, u3.user_name as approved_by_name, p.priority FROM change_memos c LEFT JOIN statuses s ON c.status_id = s.status_id LEFT JOIN users u ON c.submitted_by_id = u.user_entry_id LEFT JOIN workflow w ON c.workflow_id = w.workflow_id LEFT JOIN categories a ON c.category_id = a.category_id LEFT JOIN subcategories b ON c.subcategory_id = b.subcategory_id LEFT JOIN countries o ON c.country_id = o.country_id LEFT JOIN regions r ON c.region_id = r.region_id LEFT JOIN localities l ON c.locality_id = l.locality_id LEFT JOIN domains d ON o.domain_id = d.domain_id LEFT JOIN users u2 ON c.last_updated_by = u2.user_entry_id LEFT JOIN users u3 ON c.approved_by = u3.user_entry_id LEFT JOIN priorities p ON c.priority_id = p.priority_id

Finnally, the folowing error was generated:

Fatal error: Failed parsing SQL query on select: select source_name_1 from change_memos union select source_name_2 from change_memos union select source_name_3 from change_memos . The Error was Parse error: Unexpected clause on line 1 select source_name_1 from change_memos union select source_name_2 from change_memos union select source_name_3 from change_memos ^ found: "union" in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\xataface\lib\SQL\Parser.php on line 1700

Does somebody know what's going on? Any way to work around while using caching?

[shannah]

Unfortunately the SQL parser doesn't support the union clause yet. For caching to work correctly, the parser must first parse the query to discover which tables are involved so it can compare the cached results with the modification times of the tables.

If you just use the mysql_query() function for this particular query it will bypass the caching mechanism and should work (assuming the query is otherwise ok).

-Steve

[kevinwen]

Would it work for the union in the near future?

[shannah]

The SQL parser was adapted from the PEAR_SQL_Parser project. When I first appropriated it it barely handled any cases. I have since expanded it to handle many more cases, but there always seem to be more that needs to be done. Generally I have expanded it on a need basis, but so far haven't had the need for union. Perhaps sometime in the future i'll pick it up and try to add support for union, but it is not on the list of immediate items..

[kevinwen]

The union query is actually generated by the following code:

In fields.ini, I put something like this:

[source_name_1]
widget:label = "1st Source"
;widget:type = yui_autocomplete
vocabulary = SourceName
visibility:list = hidden
widget:atts:size=80
group = sources
order = 26

In valuelist.ini, I put something like this:

[SourceName]
__sql__ = "select source_name_1 from change_memos union select source_name_2 from change_memos union select source_name_3 from change_memos"

However, the UNION statement is still executed even if I commented out 'vocabulary = SourceName'. Does somebody know what happens?

[shannah]

Yes. it runs the union as part of the valuelist. You would need to comment out the valuelist def to eliminate it.

Workaround for now: Create a view with the query that you want, and then reference this view in your valuelist.

[kevinwen]

OK, I will comment out the def in valuelist.

I'm new to xataface. Can you tell me by example what and how to create a view and how to reference it in the valuelist?

Also, for those UNION queries, can you make some changes so the UNION statement is still acceptable by the caching-enabled xataface without having users do modification on the framework? You can just bypass the UNION statement when using caching because UNION sql statement is un-avoidable for some situation.

[shannah]

I'm new to xataface. Can you tell me by example what and how to create a view and how to reference it in the valuelist?

A view isn't a Xataface feature, it's an SQL feature. Basically a view is like a saved query in mysql so that you can access that query like a regular table. E.g.

Code: Select all

CREATE VIEW my_view as select username from people1 union select username from people2


Then you would be able to use that query moving forward as if it was a separate table:

Code: Select all

select username from my_view


You can read more about it in the MYSQL manual.

Also, for those UNION queries, can you make some changes so the UNION statement is still acceptable by the caching-enabled xataface without having users do modification on the framework? You can just bypass the UNION statement when using caching because UNION sql statement is un-avoidable for some situation.

Good suggestion. This will still require some changes to the Parser to throw an exception when it hits a "union" statement, but it would be an easier change than making it support union completely.

I'll see about making the change when I do my next round of changes.

[shannah]

I have made a small change to DB.php that will allow it to fail gracefully if it can't parse the query. If an unparsable query is encountered, it just doesn't use the cache, but doesn't throw an error anymore.

Here's the diff:

Code: Select all

Index: DB.php
===================================================================
--- DB.php   (revision 1713)
+++ DB.php   (revision 1714)
@@ -485,6 +485,7 @@
      
      
      $tables = $this->getTableDependencies($sql, $lang);
+      if ( PEAR::isError($tables) ) return null;
         // This is a list of the tables that would cause the cache to be invalidated.
      
      $modification_times = Dataface_Table::getTableModificationTimes();
@@ -640,7 +641,7 @@
      $parser = new SQL_Parser( null, 'MySQL');
      $data =& $parser->parse($sql);
      if ( PEAR::isError($data) ){
-         die($data->getMessage());
+         return $data;
      }
      $tables = array_unique($data['all_tables']);



[kevinwen]

Thank you so much.