Pgsql error

OK. I set up a MySQL box for testing and I've taken care of the first error above by modifying the query in lines 653-658 of uc_affiliate2.module:
// define the query
$sql = "SELECT u.uid, u.name, u.status, u.created, u.access
FROM {users} u
INNER JOIN {users_roles} ur ON u.uid = ur.uid
WHERE ur.rid IN (SELECT r.rid from {role} r INNER JOIN {permission} p ON r.rid = p.rid WHERE p.perm LIKE '%act as affiliate%')
GROUP BY u.uid, u.name, u.status, u.created, u.access";

This syntax is valid in both Postresql and MySQL.

I'll work on the others tomorrow and will post my changes once I have those solved.

I've looked over the code. The main issue with Postgres is the use of the MySQL unix_timestamp function. I didn't see any other queries that looked like they would cause any issues. In order to make this database agnostic this function will have to be performed by PHP instead of the DBMS.

Looking further at the PHP it appears that the time information for events like clicks etc. are put through the PHP mktime function before being stored in the database. Am I reading that right? Are all dates being stored that way?

The reason this seems relevant to me is twofold. (A) Since the PHP mktime function converts the date/time into a unix timestamp, that conversion would mean that the result of a query like "SELECT 'time' FROM 'uc_affiliate2_clicks'" would return a unix timestamp directly since that is the result of the PHP mktime function. The time calculations could be performed directly on the stored value, thus obviating the need for the MySQL unix_timestamp function or any similar function in PHP prior to performing the calculations. (B) According to the MySQL documentation the conversion to unix timestamp and then back using the unix_timestamp and from_unixtime functions is lossy. The example on the MySQL website shows a difference of about an hour or so. Not huge and for this application may not be of concern at all.

If I am correct in the above assessment then all of the queries using the unix_timestamp function can be simplified to simple select statements using standard SQL that will work with any SQL database engine.

While awaiting a reply I'm going to run some tests along these lines and see what happens...

OK. Here's what I did:

Edited uc_affiliate2.module so all queries involving the MySQL unix_timestamp function were changed from "SELECT unix_timestamp(from_unixtime(time, '%%Y-%%m-%%d')) as ..." to "SELECT time as..."

This eliminated all remaining pgsql errors.

I compared the results to those in a default MySQL installation on a test box and they matched. All affiliate pages displayed properly with no errors and the Postgres machine matched the MySQL machine using identical data.

I then copied the revised module file over to the MySQL test machine and checked the results again. They were the same as with the original file. It would be beneficial to double check the results on different sets of data to ensure that my tests were not too limited in scope.

Chris

I just found another one. Partially easy to fix and partially not. I'm going to have to dig into the php documentation for the solution to this one.

Line 250 of uc_affiliate2.module has the following query:

db_query('INSERT {uc_affiliate2_product_commission} (nid, commission_structure) VALUES (%d, "%s")', $nid, serialize($commission_structure));

for compatibility with both Mysql and Postgresql, the query needs to read:

db_query('INSERT INTO{uc_affiliate2_product_commission} (nid, commission_structure) VALUES (%d, "%s")', $nid, serialize($commission_structure));

That change revealed another problem with this query and pgsql as seen in the resulting error message after the above change:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "50" LINE 1: ...mmission (nid, commission_structure) VALUES (1, "s:2:"50";") ^ in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: INSERT INTO main_uc_affiliate2_product_commission (nid, commission_structure) VALUES (1, "s:2:"50";") in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 250.

I ran the query directly against the database and it succeeded when I changed the quotes around the serialized string value from double quotes to single quotes. As I was reviewing the documentation for the serialize function on php.net I noticed that some people were giving example output for string values as follows:

 String values are always in double quotes
 Array keys are always integers or strings
    "null => 'value'" equates to 's:0:"";s:5:"value";',
    "true => 'value'" equates to 'i:1;s:5:"value";',
    "false => 'value'" equates to 'i:0;s:5:"value";'

There were also examples showing double quoted output from the serialize function.

Taking it a step further I checked to ensure the single quoted query would work on MySQL. It did. The only problem left to solve is hot to coerce a single quoted result out of the PHP serialize function. The query will definitely work in both databases if serialize($commission_structure) returns 's:2:"50";' instead of "s:2:"50";" as in the error message above.

I will keep searching around for an answer ...

Downloaded the CVS and upon hitting the manage affiliates tab I got the following errors:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "u.name" must appear in the GROUP BY clause or be used in an aggregate function in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT u.* FROM shared_users u INNER JOIN main_users_roles ur ON u.uid = ur.uid WHERE ur.rid IN (SELECT r.rid from shared_role r INNER JOIN main_permission p ON r.rid = p.rid WHERE p.perm LIKE '%act as affiliate%') GROUP BY u.uid ORDER BY u.created DESC in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 673.

Here's the relevant section from the PostgrSQL documentation:

In general, if a table is grouped, columns that are not listed in GROUP BY cannot be referenced except in aggregate expressions. An example with aggregate expressions is:

=> SELECT x, sum(y) FROM test1 GROUP BY x;
x | sum
---+-----
a | 4
b | 5
c | 2
(3 rows)

Here sum is an aggregate function that computes a single value over the entire group. More information about the available aggregate functions can be found in Section 9.18.

Including both u.uid and u.name in the group by statement should never cause a problem because uid 1 will always be "John Smith" while uid 2 will always be "Mary Jones", etc. The other columns I'm not as sure about (not yet familiar enough with the module) .

Also with the CVS, logging in as a test affiliate and clicking on the affiliate center tab produced the following errors:

    * warning: Missing argument 1 for uc_affiliate2_dashboard() in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 959.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "AND" LINE 1: ...rs uco on ac.order_id = uco.order_id WHERE aid = AND uco.cr... ^ in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(ac.commission_id) as cnt FROM main_uc_affiliate2_commission ac INNER JOIN main_uc_orders uco on ac.order_id = uco.order_id WHERE aid = AND uco.created between 1245567599 AND 1245653999 in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 967.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "AND" LINE 1: ...rs uco on ac.order_id = uco.order_id WHERE aid = AND uco.cr... ^ in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(ac.commission_id) as cnt FROM main_uc_affiliate2_commission ac INNER JOIN main_uc_orders uco on ac.order_id = uco.order_id WHERE aid = AND uco.created BETWEEN 1245049199 AND 1245653999 in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 977.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "AND" LINE 1: ...rs uco on ac.order_id = uco.order_id WHERE aid = AND uco.cr... ^ in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(ac.commission_id) as cnt FROM main_uc_affiliate2_commission ac INNER JOIN main_uc_orders uco on ac.order_id = uco.order_id WHERE aid = AND uco.created BETWEEN 1214117999 AND 1245653999 in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 987.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "AND" LINE 1: ...licks from main_uc_affiliate2_clicks WHERE aid = AND time b... ^ in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT count(click_id) as clicks from main_uc_affiliate2_clicks WHERE aid = AND time between 1245567599 AND 1245653999 GROUP BY aid in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 995.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "AND" LINE 1: ...licks from main_uc_affiliate2_clicks WHERE aid = AND time b... ^ in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT count(click_id) as clicks from main_uc_affiliate2_clicks WHERE aid = AND time between 1245049199 AND 1245653999 GROUP BY aid in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 1003.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "AND" LINE 1: ...licks from main_uc_affiliate2_clicks WHERE aid = AND time b... ^ in /drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT count(click_id) as clicks from main_uc_affiliate2_clicks WHERE aid = AND time between 1214117999 AND 1245653999 GROUP BY aid in /drupal/sites/all/modules/uc_affiliate2/uc_affiliate2.module on line 1011.

Interestingly the CVS version eliminated the most recent error I found so I combined the CVS change to the product commission structure functions with the changes I'd made as outlined above. With this combination I am no longer getting any SQL errors.

The attached file includes the current release version of the module renamed uc_affiliate2.original, the current CVS version renamed uc_affiliate2.CVS and the above changes are incorporated in the enclosed uc_affiliate2.module file. I've updated the changelog.txt file enclosed with the changes I've made which are also described above.

As you've noted already some of my changes may introduce unanticipated logic errors. I am going to do several days of testing with different data sets to try to determine if this is the case or not. If we're lucky the attached file works well. At the very least it clears the hurdle of PostreSQL errors and allows further testing to determine whether or not additional changes are needed.

I see. One question is how critical is the time for the functionality? Since the reports are breaking clicks down by day, week, etc, does the time actually need to be stored?

Looks good so far. No SQL errors. Thanks!