A D6.x + PGSQL system doesn't like this module as it uses a MySQL-specific bit of SQL and PostgreSQL returns all the column names downcased, even if you specify "AS UpperCaseWords".

Attached patch fixes the issues for PGSQL, should (untested) still work with MySQL.

CommentFileSizeAuthor
#4 uc_who_bought_what-psql-2.diff6.05 KBjohn franklin
uc_who_bought_what-psql.diff5.17 KBjohn franklin

Comments

cayenne’s picture

cayenne commented
Assigned: Unassigned » cayenne

So, if I understand correctly, PGsql does not like field names that have uppercase, or it automatically downcases them?

Okay, if so, that should be a simple change, with the primary issue being preserving column LABELS, which should have been stored separately anyway. Have you tested your solution on attribute names as well?

john franklin’s picture

john franklin commented

[dup deleted.]

john franklin’s picture

john franklin commented

I'm not sure what you mean by "attribute names."

The patch attached is all I tested. It contains all necessary and sufficient changes to get my site to work.

The patch preserves mixed-case column header labels in the HTML output while being able to pull the data out of the hash with all lower-case keys returned from the SQL call. PgSQL seems to automatically downcase them. (I saw them downcased using the CLI psql tool as well.)

john franklin’s picture

john franklin commented
Version: 6.x-2.10 » 6.x-2.11
StatusFileSize
new uc_who_bought_what-psql-2.diff6.05 KB

Re-rolled patch for 2.11.

cayenne’s picture

cayenne commented

Great!

Following the weekend I will roll all these in.

cayenne’s picture

cayenne commented
Priority: Major » Normal

Errr, the patch breaks mySQL version. More work to do.
Basically, we want to take field names to lower case, right?

What is the deal with the date reformatting?

john franklin’s picture

john franklin commented

Yes, downcase all the fields. PostgreSQL will return an array with all downcased keys even if you ask for field names in mixed case. that is "SELECT Foo AS BarBaz" will return $result['barbaz'], not $result['BarBaz']

Regarding the time formatting, there is no such thing as FROM_UNIXTIME in PostgreSQL. It shouldn't be reformatting the time, just the SQL to select it based on DB.

cayenne’s picture

cayenne commented
Status: Active » Needs review

Got it. I believe I got them all in the latest dev. Try it in about ten minutes. Also, time formatting is now done after the fact in PHP rather than in SQL. I suspect that this will make it not care about mySQL vs Postgre

john franklin’s picture

john franklin commented

Yes, doing the date formatting in PHP does remove the issue.

Functionally, the patch committed mostly works. However, there is some code cleanup that can be done:

  • $dateformat is set twice, but never used
  • Line 104 still has "checkOutNode" mixed case. It's not clear where (if) that is used, either.
  • Line 70 has "AS GrandTotal" which results in "( sold in 4 orders)" instead of "($400.00 sold in 4 orders)"
  • The old FROM_UNIXTIME line is there, but commented out. It can be safely removed.
john franklin’s picture

john franklin commented

I'd also suggest the date field use the Drupal function format_date() instead of strftime().

cayenne’s picture

cayenne commented

Ah did not know about format_date(). I'll review your notes, commit, tag and update rev.
Thanks so much, John.

Next step: convert to D7!

cayenne’s picture

cayenne commented

Okay, I found the camelcase problems with the field titles. Old habits die hard.

As to the strftime vs format_date question, I put that in the way I did to get around the problem that the report was not sorting correctly by date per another bug report. Source code and my testing shows that using date_format reintroduces the problem.

I fixed the case on checkOutNode, but will need to figure out why I had it in there.

cayenne’s picture

cayenne commented
Status: Needs review » Closed (fixed)

Posted as .12

john franklin’s picture

john franklin commented

Looks good! Thanks.

liam morland’s picture

liam morland
Primary language English
Location Ontario, CA 🇨🇦
commented
Issue tags: +PostgreSQL

Tagging