Query translation issue with View's default Archive page

Ok, I attached a views.patch to implement case/switch for oracle driver in views. Perhaps someone could review and tell me if I'm way off or something. I think much of Postgre was similar to Oracle so I was able to use it.

Yes, there was an issue with the module supporting Oracle, but the prepared statement being passed to the driver won't work in its current form. Let's compare MySQL/Oracle queries shall we:

MySQL =>

SELECT COUNT(*) AS expression FROM (SELECT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL -18000 SECOND), '%Y%m') AS created_year_month, 1 AS expression FROM node node WHERE (( (node.status = :db_condition_placeholder_0) )) GROUP BY created_year_month)

Oracle => (From Debug log)

SELECT COUNT(*) AS expression#012FROM #012(SELECT TO_CHAR(unixts_to_date(node.created), 'YYYYMM') AS created_year_month, 1 AS expression#012FROM #012"NODE" node#012WHERE (( (node.status = :db_condition_placeholder_0) ))#012GROUP BY created_year_month) subquery args: Array#012(#012  [:db_condition_placeholder_0] => 1#012)

BTW - the unixts_to_date function is simply (if something in the driver handles dates please tell me):

SQL> SELECT TEXT FROM USER_SOURCE WHERE NAME = 'UNIXTS_TO_DATE';

TEXT
--------------------------------------------------------------------------------
function unixts_to_date(p_timestamp number) return date
 as
 begin
 return to_date('1970-01-01','YYYY-MM-DD') +
 numtodsinterval(p_timestamp,'SECOND');end unixts_to_date;

When the oracle query above runs it still throws the General error: 904 OCIStmtExecute: ORA-00904: "CREATED_YEAR_MONTH": invalid identifier since we are not explicitly selecting the 'group by' alias.

I guess the real question is whether this should be considered a driver bug or a defect with the module?

Thoughts?

Missed the part about oracle_exception_queries in the first response. I will try to get that working in D7.

Well, I got things working but I'm not at all proud at how I did it. Its a pretty nasty hack..but it works for what I want to do. Just in case anyone runs into the issue I'll give a brief overview.

I did try to use the oracle_exception_queries as discussed here http://drupal.org/node/860342#comment-3330524 but I couldn't get it to work. So I moved my focus to directly overriding the query in Views.

Where you do this is views/plugins/views_plugin_query_default.inc - You basically have to match a couple of conditions and then override. Where I modified (hacked) everything was in the function execute(&$view) - right after the queries begin building

    $query = $view->build_info['query'];
    $count_query = $view->build_info['count_query'];

I override with this:

    if($view->name == "archive" && preg_match('/GROUP BY created_year_month/',$query) > 0):
    $query = "SELECT created_year_month, COUNT(num_records) AS num_records FROM (select TO_CHAR(drupal_timestamp.todate(node.created), 'YYYYMM') as created_year_month, node.nid AS num_records FROM  node WHERE (( (node.status = 1) )) )GROUP BY created_year_month ORDER BY created_year_month DESC";
    $count_query = "SELECT created_year_month, COUNT(num_records) AS num_records FROM (select TO_CHAR(drupal_timestamp.todate(node.created), 'YYYYMM') as created_year_month, node.nid AS num_records FROM node WHERE ( (node.status = 1) )) GROUP BY created_year_month )";
    endif;

I had to add in the preg_match after I got the basic count query stuff working. Why? Because if you use the default View code the base "archive" page doesn't work but the (for example) "archive/201108" does work (because it isn't counting anything) . So you have to force it to only override where the GROUP BY on the alias takes place. For these queries you also have to bypass {addMetaData, addTag, module_invoke_all, preExecute} with something like:

    if($view->name != "archive"):
    $query->addMetaData('view', $view);
    $count_query->addMetaData('view', $view);
    endif;

And finally, the default "$query->execute()" won't work (think because its not a PreparedStatement) with the override query so you have to execute with "db_query()". I did the following:

        if($view->name == "archive" && preg_match('/GROUP BY created_year_month/',$query) > 0){
        $result = db_query($query);
        } else {
        $result = $query->execute();
        }

Not looking forward to any upgrades with the Views module! IMO, I don't think the driver should be expected to translate this type of query. I do wish Views had a better way to manage this stuff.

We can go ahead and close this issue.