149 lines
3.4 KiB
Bash
149 lines
3.4 KiB
Bash
#! /bin/env bash
|
|
|
|
FILE=db.sqlite
|
|
DB=sq://$FILE
|
|
DEST=./models
|
|
|
|
SCHEMA=0
|
|
OPTIND=1
|
|
while getopts "s" opt; do
|
|
case "$opt" in
|
|
s) SCHEMA=1
|
|
esac
|
|
done
|
|
|
|
popd &> /dev/null
|
|
|
|
mkdir -p $DEST
|
|
rm -rf $DEST/*.xo.go
|
|
|
|
if [[ "$SCHEMA" = "1" ]]; then
|
|
|
|
echo "Regenerating Schema..."
|
|
(set -ex;
|
|
rm $FILE
|
|
usql -f sql/schema.sql $DB
|
|
)
|
|
fi
|
|
|
|
set -ex
|
|
|
|
xo schema $DB -o $DEST
|
|
xo schema $DB --template yaml -o $DEST
|
|
|
|
# get all users
|
|
xo query $DB -M -B -T User -2 -a -F Users -o $DEST << ENDSQL
|
|
SELECT u.* FROM users u
|
|
ENDSQL
|
|
|
|
# get user by token
|
|
xo query $DB -M -B -T User -1 -2 -a -F UserByToken -o $DEST << ENDSQL
|
|
SELECT
|
|
u.*
|
|
FROM tokens t
|
|
JOIN users u ON t.user_id = u.id
|
|
WHERE t.token = %%token string%%
|
|
ENDSQL
|
|
|
|
# delete a specific token
|
|
xo query $DB -M -B -X -2 -a -F DeleteToken -o $DEST -S token.xo.go << ENDSQL
|
|
DELETE FROM tokens
|
|
WHERE token = %%token string%%
|
|
ENDSQL
|
|
|
|
# delete all tokens for a user
|
|
xo query $DB -M -B -X -2 -a -F DeleteTokens -o $DEST -S token.xo.go << ENDSQL
|
|
DELETE FROM tokens
|
|
WHERE user_id = %%user_id string%%
|
|
ENDSQL
|
|
|
|
|
|
FIELDS='QuestionID string,P1Awarded sql.NullInt64,P2Awarded sql.NullInt64'
|
|
xo query $DB -M -B -2 -Z "$FIELDS" -T AllPartsData -F AllQuestionPartsData -o $DEST << ENDSQL
|
|
WITH attempts AS (
|
|
SELECT *
|
|
FROM question_attempt
|
|
WHERE correct = true
|
|
)
|
|
SELECT DISTINCT
|
|
a.question_id as question_id,
|
|
MAX(p1.points_awarded) AS p1_awarded,
|
|
MAX(p2.points_awarded) AS p2_awarded
|
|
FROM attempts a
|
|
|
|
LEFT JOIN attempts AS p1
|
|
ON p1.id = a.id
|
|
AND p1.question_part = 1
|
|
|
|
LEFT JOIN attempts AS p2
|
|
ON p2.id = a.id
|
|
AND p2.question_part = 2
|
|
|
|
WHERE a.user_id = %%user_id string%%
|
|
|
|
GROUP BY a.user_id, a.question_id
|
|
ENDSQL
|
|
|
|
FIELDS='P1Awarded int, P1Answer string, P2Awarded int, P2Answer string'
|
|
xo query $DB -M -B -2 -1 -Z "$FIELDS" -T PartsData -F QuestionPartsData -o $DEST << ENDSQL
|
|
WITH attempts AS (
|
|
SELECT *
|
|
FROM question_attempt
|
|
WHERE question_id = %%question_id string%%
|
|
AND correct = true
|
|
)
|
|
SELECT
|
|
COALESCE(p1.points_awarded, 0) AS p1_awarded,
|
|
COALESCE(p1.answer, '') AS p1_answer,
|
|
COALESCE(p2.points_awarded, 0) AS p2_awarded,
|
|
COALESCE(p2.answer, '') AS p2_answer
|
|
FROM users u
|
|
|
|
LEFT JOIN attempts AS p1
|
|
ON p1.user_id = u.id
|
|
AND p1.question_part = 1
|
|
|
|
LEFT JOIN attempts AS p2
|
|
ON p2.user_id = u.id
|
|
AND p2.question_part = 2
|
|
|
|
WHERE
|
|
u.id = %%user_id string%%
|
|
ENDSQL
|
|
|
|
FIELDS='ByUser int,Correct bool'
|
|
xo query $DB -M -B -2 -1 -Z "$FIELDS" -T Submissions -F QuestionSubmissions -o $DEST << ENDSQL
|
|
WITH attempts AS (
|
|
SELECT * FROM question_attempt
|
|
WHERE user_id = %%user_id string%%
|
|
AND question_id = %%question_id string%%
|
|
AND question_part = %%question_part int%%
|
|
)
|
|
SELECT
|
|
c.count AS by_user,
|
|
COALESCE(a.correct, FALSE) AS correct
|
|
FROM (
|
|
SELECT COUNT(*) as count from attempts
|
|
) c
|
|
LEFT JOIN attempts a ON a.correct
|
|
ENDSQL
|
|
|
|
FIELDS='Points int'
|
|
xo query $DB -M -B -2 -1 -Z "$FIELDS" -T Points -F UserPoints -o $DEST -S userpoints.xo.go << ENDSQL
|
|
SELECT COALESCE(SUM(points_awarded), 0) FROM question_attempt
|
|
WHERE user_id=%%user_id string%%
|
|
ENDSQL
|
|
|
|
|
|
FIELDS='Name string, Points int,LastValid Time'
|
|
xo query $DB -M -B -2 -Z "$FIELDS" -T LeaderboardEntry -F Leaderboard -o $DEST -S leaderboard.xo.go << ENDSQL
|
|
SELECT u.name,
|
|
COALESCE(SUM(qa.points_awarded), 0) AS points,
|
|
COALESCE(MAX(qa.submitted_at), u.created_at) as last_valid_submission
|
|
FROM users u
|
|
LEFT JOIN question_attempt qa ON u.id = qa.user_id AND qa.correct
|
|
GROUP BY u.id
|
|
ORDER BY COALESCE(SUM(qa.points_awarded), 0) DESC,
|
|
MAX(qa.submitted_at) ASC
|
|
ENDSQL
|