Support for PostgreSQL
| Project: | Planet |
| Version: | HEAD |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Hi swe3tdave,
I installed ubuntu-drupal yesterday
with a pgsql system to add some spice
but I have problems with some queries
The one I think I have fixed appears in two places:
near lines 202 and 288.
SELECT COUNT(f.fid) cnt, f.*, (UNIX_TIMESTAMP(NOW()) - checked) _checked
FROM {planet_feeds} f LEFT OUTER JOIN {planet_items} i
ON i.fid = f.fid WHERE f.uid = %d GROUP BY f.fid;3 issues with that:
1. "AS" is needed for column names
causes pgsql to fail if missing.
SELECT COUNT(f.fid) AS cnt, f.*, (UNIX_TIMESTAMP(NOW()) - checked) AS _checked
FROM {planet_feeds} f LEFT OUTER JOIN {planet_items} i
ON i.fid = f.fid WHERE f.uid = %d GROUP BY f.fid;2. UNIX_TIMESTAMP() is undefined in pgsql
and most likely in sqlite and other DB backends.
For me, the easiest is to let PHP compute it, unix timestamp is
returned natively by time()
3. f.* causes GROUP BY clause to fail
It should include all listed fields.
Only 'f._checked', 'f.title', 'f.cnt', 'f.fid' used. No field in planet_items, just count.
But what is the intent here ? Listing all feeds associated with current user, along with items count.
I'd suggest to write " separates queries:
* a loop on user feeds
* for each feed, a "scalar query" to get items count
I attach the patch for the above fixes.
I'm not sure the first block is reachable. If it is, why not refactoring the duplicated code to a single function ?
Best regards,
Michelle
| Attachment | Size |
|---|---|
| queries1.patch | 4.91 KB |
#1
Here's the global patch of my work so far.
There's still a lot of work to do on it.
I can now add feeds, but if I do from admin/settings/planet, the 'Original author' list being empty, no uid is associated with. Thus the feeds don't appear in list ("WHERE uid=%d" clause not satisfied).
It would also be grand if you could add some documentation to the code, especially in big blocks.
Thanks