[aur-dev] some notes from converting the aur from mysql to postgresql

[Dan McGee]

On Mon, Apr 25, 2011 at 10:38 PM, elij <elij.mx at gmail.com> wrote:
> On Mon, Apr 25, 2011 at 7:49 PM, Dan McGee <dpmcgee at gmail.com> wrote:
>> On Mon, Apr 25, 2011 at 9:35 PM, elij <elij.mx at gmail.com> wrote:
>>> * using the limit offset format of `"LIMIT x,y"`
>>>  postgresql doesn't support this format, so I had to track each
>>> occurrence down and change it to `"Limit x OFFSET y"`
>> This is SQL standard, so you could use this form anyway, correct? Of
>> course the MySQL docs claim this:
>> For compatibility with PostgreSQL, MySQL also supports the LIMIT
>> row_count OFFSET offset syntax.
>
> Yeah. LIMIT w/OFFSET is sql standard. Not sure why mysql had to create
> their own limit/offset
> standard, but well.. yeah.
>
>>> * A few instances of `"LIMIT 0,10"`. A `"LIMIT 10"` would have been
>>> sufficient. Not sure why offset was specified in these few instances.
>> Sure it wasn't auto-generated, in which case it is easier for the DB
>> query analyzer to just throw it away anyway?
>
> It looks like there were just two instances.
>
> web/html/rss.php:
>    $q = "SELECT * FROM Packages ";
>    $q.= "WHERE DummyPkg != 1 ";
>    $q.= "ORDER BY SubmittedTS DESC ";
>    $q.= "LIMIT 0 , 20";
>
> web/lib/stats.inc:
>    $q = 'SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY
> GREATEST(SubmittedTS,ModifiedTS) DESC LIMIT 0 , 10';

If you still have DummyPkg references, you might need to update your
codebase- this was killed a while ago by me.

With that said, the first one still exists; the second one was fixed
in the aforementioned update.

-Dan