xrymbos/nztrain-v2 — comment on commit fde356ae57afdb6b8e746bd5a95bfaec04b2f224
File:  line 0
By ronalchn:

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
>