I think we should try to keep this stuff abstract so we aren't bound
to SQLite. Maybe look up to see if there's a way to do join queries
unobtrusively with rails? Then instead of a view just make he call, or
use a helper or whatever. Makes it easier to change of we ever
want/need to move away from SQLite (which will happen)

On 17/01/2012, at 7:09 PM, ronalchn
<reply@reply.github.com>
wrote:

> Time taken column on contests page also doesn't appear to work on SQLite
> (just appears as 0). I think the database isn't calculating time
> differences properly.
>
> Ronald
>
> On Tue, Jan 17, 2012 at 9:03 PM, Ronald Chan <ronalchn@gmail.com> wrote:
>
>> Made changes to it. SQLite has different queries. Also fixed another bug
>> for SQLite on users page. However, ranking calculation for SQLite on
>> contests page remains incorrect. I tried to put special ruby code to
>> calculate it for SQLite, but it's not working.
>>
>> PostgreSQL calculates ranking numbers automatically.
>>
>>
>> On Tue, Jan 17, 2012 at 7:27 PM, Simon Welsh <
>> reply@reply.github.com
>>> wrote:
>>
>>> @ronalchn The migrations do not work with SQLite. Can you please rectify?
>>>
>>>
>>> Yogurt:nztrain-v2 simon$ bundle exec rake db:migrate
>>> ==  CreateContestsMaxScoreSubmissionsView: migrating
>>> ==========================
>>> -- execute("CREATE VIEW contests_max_score_submissions AS SELECT DISTINCT
>>> ON (contests.id, submissions.user_id, submissions.problem_id)
>>> submissions.id, submissions.score as score, submissions.user_id,
>>> submissions.problem_id, submissions.created_at, submissions.updated_at,
>>> contest_relations.contest_id FROM contests JOIN contest_relations ON
>>> contests.id = contest_relations.contest_id JOIN contests_problems ON
>>> contests_problems.contest_id = contests.id JOIN submissions ON
>>> contest_relations.user_id = submissions.user_id AND submissions.problem_id
>>> = contests_problems.problem_id WHERE submissions.created_at BETWEEN
>>> contests.start_time AND contests.end_time ORDER BY contests.id,
>>> submissions.user_id, submissions.problem_id, score DESC,
>>> submissions.created_at ASC;")
>>> rake aborted!
>>> An error has occurred, this and all later migrations canceled:
>>>
>>> SQLite3::SQLException: near "ON": syntax error: CREATE VIEW
>>> contests_max_score_submissions AS SELECT DISTINCT ON (contests.id,
>>> submissions.user_id, submissions.problem_id) submissions.id,
>>> submissions.score as score, submissions.user_id, submissions.problem_id,
>>> submissions.created_at, submissions.updated_at,
>>> contest_relations.contest_id FROM contests JOIN contest_relations ON
>>> contests.id = contest_relations.contest_id JOIN contests_problems ON
>>> contests_problems.contest_id = contests.id JOIN submissions ON
>>> contest_relations.user_id = submissions.user_id AND submissions.problem_id
>>> = contests_problems.problem_id WHERE submissions.created_at BETWEEN
>>> contests.start_time AND contests.end_time ORDER BY contests.id,
>>> submissions.user_id, submissions.problem_id, score DESC,
>>> submissions.created_at ASC;
>>>
>>> Tasks: TOP => db:migrate
>>>
>>> ---
>>> Reply to this email directly or view it on GitHub:
>>>
>>> https://github.com/xrymbos/nztrain-v2/commit/fde356ae57afdb6b8e746bd5a95bfaec04b2f224#commitcomment-873167
>>>
>>
>>
>
> ---
> Reply to this email directly or view it on GitHub:
> https://github.com/xrymbos/nztrain-v2/commit/fde356ae57afdb6b8e746bd5a95bfaec04b2f224#commitcomment-873357