[SOLVED] Fixes for Postgres in News in 1.1.1 bork older MySQL installations

[openmtl]

I did the 1.1 to 1.1.1 upgrade by uploading a new copy and overwriting and updated files. The only problem I found was that my news disappeared.

I enabled debug and found that the SQL syntax error was complaining about the ...LIMIT OFFSET right at the end of the query in action.default.php (line 228 or so) and function.admin_articlestab.php (around line 95),

I changed my copies to...

//$query1 .= " LIMIT $pagelimit OFFSET $startelement";  <<<- POSTGRESQL SYNTAX
$query1 .= " LIMIT $startelement , $pagelimit ";            <<<- Older MYSQL SYNTAX

I must admit my MySQL is hosted and I've been meaning to talk to them to upgrade the bits as it is running MySQL v3.23.58 proto 10 but http://dev.mysql.com/doc/refman/4.1/en/select.html seems to say that LIMIT OFFSET is valid but hey I guess it isn't.

Now what I was thinking was that could I create a config.php option e.g.,

$config['sql_variant'] = 'legacy-mysql';

or some such setting and then use that where we find subtle quirks in SQL and database access but how do
I access config options from within the .php files ?

Would people mind if we have these little switches to support legacy installations ?

[calguy1000]

What needs to happen is that everything with filtering like this needs to use the SelectLimit call.
unfortunately they don't (including most of mine).

So at the moment we're at a canundrum with postgres and mysql support.

[openmtl]

What needs to happen is that everything with filtering like this needs to use the SelectLimit call.
unfortunately they don't (including most of mine).

So at the moment we're at a canundrum with postgres and mysql support.

Ah yes I see the ADOdb....What is the problem with moving to the ADOdb SelectLimit call
across the board ? Of the modules I have I see just Album, Calendar and the action.rss.php
in News are the only things that use SelectLimit() ). I just tested Calendar (Events) and it
works OK.

Is there some quirk with moving to SelectLimit() i.e. is this the solution to supporting
both Postgresql and MySQL transparently ?

[calguy1000]

No, the problem is.... that we have to modify all umpteen modules.  it takes time, and energy....  I wish there could be a 'maintenance' team that goes behind the 'development' team and cleans up such issues.

[openmtl]

No, the problem is.... that we have to modify all umpteen modules.  it takes time, and energy....  I wish there could be a 'maintenance' team that goes behind the 'development' team and cleans up such issues.

Ah, OK - I'll PM. BTW: I copied the action.rss.php and changed my fixes to.....

if( $pagelimit >= 0 )
{
$dbresult =& $db->SelectLimit($query1,$pagelimit,$startelement,$parms);
}
else
{
$dbresult =& $db->Execute($query1,$parms);
}

and similar in action.defaultphp and function.admin_articlestab.php and it all seems to work still. This is good that it's not a catastrophic issue due to feature gaps in MySQL v. Postgresql but just people time to refactor. I really like Postgresql
and though people don't find it much on Web hosts I prefer to use it in-house as it has that extra "polish".