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

[elij]

On Mon, Apr 25, 2011 at 8:50 PM, Dan McGee <dpmcgee at gmail.com> wrote:
> 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.

Ah. Yeah, I probably do need to do a pull and merge (been a while).
Thanks for the heads up.