initial commit

swizzard · Mar 26, 2020 · e061e73 · e061e73
commit e061e73
Show file tree

Hide file tree

Showing 23 changed files with 11,589 additions and 0 deletions.
diff --git a/.gitignore b/.gitignore
@@ -0,0 +1,3 @@
+app/node_modules
+app/yarn-error.log
+app/public
diff --git a/app/.dockerignore b/app/.dockerignore
@@ -0,0 +1 @@
+node_modules
diff --git a/app/Dockerfile b/app/Dockerfile
@@ -0,0 +1,11 @@
+FROM node:latest
+
+WORKDIR /app
+
+ENV PATH /app/node_modules/.bin:$PATH
+
+COPY package.json /app/package.json
+RUN npm install
+RUN npm install react-scripts@3.4.1 -g
+
+CMD ["yarn", "start"]
diff --git a/app/README.md b/app/README.md
@@ -0,0 +1,68 @@
+This project was bootstrapped with [Create React App](https://github.com/facebook/create-react-app).
+
+## Available Scripts
+
+In the project directory, you can run:
+
+### `yarn start`
+
+Runs the app in the development mode.<br />
+Open [http://localhost:3000](http://localhost:3000) to view it in the browser.
+
+The page will reload if you make edits.<br />
+You will also see any lint errors in the console.
+
+### `yarn test`
+
+Launches the test runner in the interactive watch mode.<br />
+See the section about [running tests](https://facebook.github.io/create-react-app/docs/running-tests) for more information.
+
+### `yarn build`
+
+Builds the app for production to the `build` folder.<br />
+It correctly bundles React in production mode and optimizes the build for the best performance.
+
+The build is minified and the filenames include the hashes.<br />
+Your app is ready to be deployed!
+
+See the section about [deployment](https://facebook.github.io/create-react-app/docs/deployment) for more information.
+
+### `yarn eject`
+
+**Note: this is a one-way operation. Once you `eject`, you can’t go back!**
+
+If you aren’t satisfied with the build tool and configuration choices, you can `eject` at any time. This command will remove the single build dependency from your project.
+
+Instead, it will copy all the configuration files and the transitive dependencies (webpack, Babel, ESLint, etc) right into your project so you have full control over them. All of the commands except `eject` will still work, but they will point to the copied scripts so you can tweak them. At this point you’re on your own.
+
+You don’t have to ever use `eject`. The curated feature set is suitable for small and middle deployments, and you shouldn’t feel obligated to use this feature. However we understand that this tool wouldn’t be useful if you couldn’t customize it when you are ready for it.
+
+## Learn More
+
+You can learn more in the [Create React App documentation](https://facebook.github.io/create-react-app/docs/getting-started).
+
+To learn React, check out the [React documentation](https://reactjs.org/).
+
+### Code Splitting
+
+This section has moved here: https://facebook.github.io/create-react-app/docs/code-splitting
+
+### Analyzing the Bundle Size
+
+This section has moved here: https://facebook.github.io/create-react-app/docs/analyzing-the-bundle-size
+
+### Making a Progressive Web App
+
+This section has moved here: https://facebook.github.io/create-react-app/docs/making-a-progressive-web-app
+
+### Advanced Configuration
+
+This section has moved here: https://facebook.github.io/create-react-app/docs/advanced-configuration
+
+### Deployment
+
+This section has moved here: https://facebook.github.io/create-react-app/docs/deployment
+
+### `yarn build` fails to minify
+
+This section has moved here: https://facebook.github.io/create-react-app/docs/troubleshooting#npm-run-build-fails-to-minify
diff --git a/app/migrate.js b/app/migrate.js
@@ -0,0 +1,19 @@
+const { createDb, migrate } = require('postgres-migrations');
+
+const config = {
+  database: process.env.POSTGRES_DB,
+  user: process.env.POSTGRES_USER,
+  password: process.env.POSTGRES_PASSWORD,
+  host: process.env.POSTGRES_HOST,
+  port: parseInt(process.env.POSTGRES_PORT),
+};
+
+async function doMigration() {
+  await createDb(process.env.POSTGRES_DB, {
+    ...config,
+    defaultDatabase: process.env.POSTGRES_DB,
+  });
+  await migrate(config, '/app/migrations');
+}
+
+doMigration();
diff --git a/app/migrations/0001-initial-tables.sql b/app/migrations/0001-initial-tables.sql
@@ -0,0 +1,42 @@
+CREATE TABLE IF NOT EXISTS player (
+  id SERIAL PRIMARY KEY,
+  email VARCHAR(255) UNIQUE NOT NULL,
+  ipaddr inet UNIQUE NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS quiz (
+  id SERIAL PRIMARY KEY,
+  creator INTEGER REFERENCES player(id)
+);
+
+CREATE TABLE IF NOT EXISTS quiz_round (
+  id SERIAL PRIMARY KEY,
+  quiz_id INTEGER REFERENCES quiz(id),
+  round_no SMALLINT,
+  UNIQUE(quiz_id, round_no)
+);
+
+CREATE TABLE IF NOT EXISTS question (
+  id SERIAL PRIMARY KEY,
+  round_id INTEGER REFERENCES quiz_round(id),
+  question TEXT
+);
+
+CREATE TABLE IF NOT EXISTS answer (
+  id SERIAL PRIMARY KEY,
+  question_id INTEGER REFERENCES question(id),
+  answer TEXT,
+  points SMALLINT
+);
+
+CREATE TABLE IF NOT EXISTS game (
+  id SERIAL PRIMARY KEY,
+  quiz_id INTEGER REFERENCES quiz(id)
+);
+
+CREATE TABLE IF NOT EXISTS game_participant (
+  id SERIAL PRIMARY KEY,
+  game_id INTEGER REFERENCES game(id),
+  player_id INTEGER REFERENCES player(id),
+  score INTEGER
+);
diff --git a/app/migrations/0002-user-stuff.sql b/app/migrations/0002-user-stuff.sql
@@ -0,0 +1,54 @@
+CREATE EXTENSION IF NOT EXISTS pgcrypto;
+CREATE TYPE role AS ENUM ('creator', 'participant', 'banned');
+CREATE TYPE player_result AS (id INTEGER, email VARCHAR(255), display_name VARCHAR(32));
+
+ALTER TABLE player ADD COLUMN IF NOT EXISTS password TEXT NOT NULL;
+ALTER TABLE player ADD COLUMN IF NOT EXISTS display_name VARCHAR(32) NOT NULL;
+ALTER TABLE player ADD COLUMN IF NOT EXISTS role role NOT NULL DEFAULT 'participant';
+
+CREATE OR REPLACE FUNCTION ip_banned(u_ipaddr inet) RETURNS boolean AS $$
+  SELECT EXISTS(SELECT 1 FROM player p WHERE p.ipaddr = u_ipaddr AND p.role = 'banned');
+$$ LANGUAGE SQL STABLE;
+
+CREATE OR REPLACE FUNCTION new_player(
+  u_email VARCHAR(255), u_password TEXT, u_ipaddr inet, u_display_name VARCHAR(32)
+) RETURNS player_result AS $$
+DECLARE
+  is_banned boolean;
+  banned_user player_result = (-1, '', '');
+  u RECORD;
+  new_user player_result;
+BEGIN
+  SELECT ip_banned(u_ipaddr) INTO is_banned;
+  IF is_banned THEN
+    RETURN banned_user;
+  ELSE
+    SELECT p.* INTO u FROM player p WHERE p.email = u_email;
+    IF NOT FOUND THEN
+      INSERT INTO player (email, ipaddr, password, display_name)
+      VALUES (u_email, u_ipaddr, crypt(u_password, gen_salt('md5')), u_display_name)
+      RETURNING player.id, player.email, player.display_name INTO new_user;
+      RETURN new_user;
+    ELSIF u.role <> 'banned' AND u.password = (SELECT crypt(u_password, u.password)) THEN
+      RETURN (u.id, u.email, u.display_name);
+    ELSE RETURN banned_user;
+    END IF;
+  END IF;
+END;
+$$ LANGUAGE plpgsql VOLATILE;
+
+CREATE OR REPLACE FUNCTION delete_user(email VARCHAR(255), u_ipaddr inet) RETURNS boolean AS $$
+DECLARE u record;
+BEGIN
+  IF SELECT ip_banned(u_ipaddr) THEN
+    RETURN 'f';
+  ELSE
+    SELECT p.* INTO u FROM player p WHERE p.email = email;
+    IF NOT FOUND THEN
+      RETURN 'f';
+    ELSIF u.role <> 'banned' THEN
+      DELETE FROM player p WHERE p.id = u.id;
+      RETURN 't';
+    END IF;
+  END;
+$$ LANGUAGE plpgsql VOLATILE;
diff --git a/app/migrations/0003-player-answers.sql b/app/migrations/0003-player-answers.sql
@@ -0,0 +1,5 @@
+CREATE TABLE IF NOT EXISTS participant_response (
+  id SERIAL PRIMARY KEY,
+  question_id INTEGER REFERENCES question(id),
+  participant_id INTEGER REFERENCES game_participant(id)
+);
diff --git a/app/migrations/0004-game-uuid.sql b/app/migrations/0004-game-uuid.sql
@@ -0,0 +1 @@
+ALTER TABLE quiz ADD COLUMN IF NOT EXISTS quiz_code uuid UNIQUE NOT NULL DEFAULT gen_random_uuid();
diff --git a/app/package.json b/app/package.json
@@ -0,0 +1,37 @@
+{
+  "name": "quiz",
+  "version": "0.1.0",
+  "private": true,
+  "dependencies": {
+    "@testing-library/jest-dom": "^4.2.4",
+    "@testing-library/react": "^9.3.2",
+    "@testing-library/user-event": "^7.1.2",
+    "postgres-migrations": "^4.0.2",
+    "query-string": "^6.11.1",
+    "react": "^16.13.1",
+    "react-dom": "^16.13.1",
+    "react-scripts": "3.4.1"
+  },
+  "scripts": {
+    "start": "npm run migrate && react-scripts start",
+    "build": "react-scripts build",
+    "test": "react-scripts test",
+    "eject": "react-scripts eject",
+    "migrate": "node migrate.js"
+  },
+  "eslintConfig": {
+    "extends": "react-app"
+  },
+  "browserslist": {
+    "production": [
+      ">0.2%",
+      "not dead",
+      "not op_mini all"
+    ],
+    "development": [
+      "last 1 chrome version",
+      "last 1 firefox version",
+      "last 1 safari version"
+    ]
+  }
+}
diff --git a/app/src/App.css b/app/src/App.css
@@ -0,0 +1,38 @@
+.App {
+  text-align: center;
+}
+
+.App-logo {
+  height: 40vmin;
+  pointer-events: none;
+}
+
+@media (prefers-reduced-motion: no-preference) {
+  .App-logo {
+    animation: App-logo-spin infinite 20s linear;
+  }
+}
+
+.App-header {
+  background-color: #282c34;
+  min-height: 100vh;
+  display: flex;
+  flex-direction: column;
+  align-items: center;
+  justify-content: center;
+  font-size: calc(10px + 2vmin);
+  color: white;
+}
+
+.App-link {
+  color: #61dafb;
+}
+
+@keyframes App-logo-spin {
+  from {
+    transform: rotate(0deg);
+  }
+  to {
+    transform: rotate(360deg);
+  }
+}
diff --git a/app/src/App.js b/app/src/App.js
@@ -0,0 +1,18 @@
+import React, { useState } from 'react';
+import SignIn from './SignIn';
+
+function App() {
+  const [user, setUser] = useState(null);
+  if (user) {
+    return (
+      <div>
+        <p>{user.email}</p>
+        <p>{user.display_name}</p>
+      </div>
+    );
+  } else {
+    return <SignIn setUser={setUser} />;
+  }
+}
+
+export default App;
diff --git a/app/src/App.test.js b/app/src/App.test.js
@@ -0,0 +1,9 @@
+import React from 'react';
+import { render } from '@testing-library/react';
+import App from './App';
+
+test('renders learn react link', () => {
+  const { getByText } = render(<App />);
+  const linkElement = getByText(/learn react/i);
+  expect(linkElement).toBeInTheDocument();
+});
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		ALTER TABLE quiz ADD COLUMN IF NOT EXISTS quiz_code uuid UNIQUE NOT NULL DEFAULT gen_random_uuid();