mirror of
https://github.com/swarm-game/swarm.git
synced 2024-10-05 20:19:09 +03:00
82e8ac95ad
Closes #1847. ## Demo ### Production https://swarmgame.net/list-games.html ### Local testing ``` tournament/scripts/demo/server-native.sh ``` and ``` scripts/test/run-tests.sh swarm:test:tournament-host ``` ## Authentication flow 1. Users are represented by a GitHub username (primary key) and an "authentication cookie" in the SQLite database. 2. Site prompts user to login when the client's cookie is nonexistent or does not match any user in the database. 3. GitHub flow: 1. Clicking the "Login" link redirects user to the GitHub login page. 2. GitHub sends a `code` to our callback URL. 3. use that `code` to get an "access token" 4. use the "access token" to look up the username of the person who is logging in. 5. generate and store a new cookie in the database row for that username 6. set the cookie value on the user's client. 4. As long as the client keeps sending the cookie value known to the server, all uploads/activity will be attributed to their GitHub username. ## New features * Login/Logout * All uploaded content is attributed to an authenticated GitHub user * Separate pages for scenario lists and solution lists * Download a solution file
88 lines
2.7 KiB
SQL
88 lines
2.7 KiB
SQL
BEGIN TRANSACTION;
|
|
|
|
CREATE TABLE IF NOT EXISTS "users" (
|
|
"alias" TEXT NOT NULL,
|
|
"cookie" TEXT NOT NULL UNIQUE DEFAULT (lower(hex(randomblob(16)))),
|
|
PRIMARY KEY("alias")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "github_tokens" (
|
|
"alias" TEXT NOT NULL,
|
|
"github_access_token" TEXT NOT NULL,
|
|
"github_access_token_expires_at" DATETIME NOT NULL,
|
|
"github_refresh_token" TEXT NOT NULL,
|
|
"github_refresh_token_expires_at" DATETIME NOT NULL,
|
|
PRIMARY KEY("alias"),
|
|
FOREIGN KEY(alias) REFERENCES users(alias)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "scenarios" (
|
|
"content_sha1" TEXT NOT NULL UNIQUE,
|
|
"uploader" TEXT NOT NULL,
|
|
"original_filename" TEXT,
|
|
"title" TEXT,
|
|
"swarm_git_sha1" TEXT,
|
|
"uploaded_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"content" TEXT NOT NULL,
|
|
PRIMARY KEY("content_sha1"),
|
|
FOREIGN KEY(uploader) REFERENCES users(alias)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "evaluated_solution" (
|
|
"id" INTEGER NOT NULL UNIQUE,
|
|
"evaluated_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"scenario" TEXT NOT NULL,
|
|
"seed" INTEGER NOT NULL,
|
|
"wall_time_seconds" REAL NOT NULL,
|
|
"ticks" INTEGER,
|
|
"char_count" INTEGER,
|
|
"ast_size" INTEGER,
|
|
"builtin" BOOLEAN,
|
|
PRIMARY KEY("id" AUTOINCREMENT),
|
|
FOREIGN KEY(scenario) REFERENCES scenarios(content_sha1)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "solution_submission" (
|
|
"content_sha1" TEXT NOT NULL,
|
|
"uploader" TEXT NOT NULL,
|
|
"uploaded_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"solution_evaluation" INTEGER,
|
|
"content" TEXT NOT NULL,
|
|
PRIMARY KEY("content_sha1"),
|
|
FOREIGN KEY(uploader) REFERENCES users(alias),
|
|
FOREIGN KEY(solution_evaluation) REFERENCES evaluated_solution(id)
|
|
);
|
|
|
|
CREATE VIEW agg_scenario_submissions AS
|
|
SELECT scenarios.original_filename,
|
|
scenarios.content_sha1 AS scenario,
|
|
scenarios.uploaded_at AS scenario_uploaded_at,
|
|
COALESCE(foo.submission_count, 0) AS submission_count,
|
|
scenarios.uploader AS scenario_uploader,
|
|
scenarios.swarm_git_sha1,
|
|
scenarios.title
|
|
FROM ((scenarios
|
|
LEFT JOIN ( SELECT evaluated_solution.scenario,
|
|
count(*) AS submission_count
|
|
FROM evaluated_solution
|
|
WHERE (NOT evaluated_solution.builtin)
|
|
GROUP BY evaluated_solution.scenario) foo ON (scenarios.content_sha1 = foo.scenario))
|
|
);
|
|
|
|
CREATE VIEW all_solution_submissions AS
|
|
SELECT
|
|
evaluated_solution.scenario,
|
|
solution_submission.uploaded_at,
|
|
evaluated_solution.seed,
|
|
evaluated_solution.wall_time_seconds,
|
|
evaluated_solution.ticks,
|
|
evaluated_solution.char_count,
|
|
evaluated_solution.ast_size,
|
|
solution_submission.uploader AS solution_submitter,
|
|
solution_submission.content_sha1 AS solution_sha1
|
|
FROM solution_submission
|
|
JOIN evaluated_solution ON evaluated_solution.id = solution_submission.solution_evaluation
|
|
WHERE NOT evaluated_solution.builtin;
|
|
|
|
COMMIT;
|