siggame / colisee-db Goto Github PK

story: #18

• Globally auto-incrementing ID - Not Null
• Foreign Team ID (number) - Not Null
• Version (number) - Not Null
• Status: queueing, building, finished, failed; defaults to queueing
• Output URL (string)
• Submission URL (string)
• Docker Image (string)

It should ignore

• .git/
• .gitignore
• .gitattributes
• Dockerfile
• .dockerignore

We need a general purpose way to verify our SQL schema is actually valid in a PostgreSQL environment.

Ideally, a script is developed to test this that works locally and in a Travis CI environment

What information would we want to see in a team's database?

• name
• last submission URL
• last submission time
• last submission build log
• last successful submission URL
• last successful submission time
• last successful submission build log
• is eligible for prizes

If we use team-logins rather than user-logins

• email
• password

The only use I can imagine for it now that colisee-lib is a thing is if we specifically want to test that creating a database is valid; however I feel like that should be a test for colisee-lib to do

story: #18

Games Submissions represents the relation between games and submissions.

• id (global auto incrementing)
• Game ID (integer) - Not Null, References Games.id
• Submission ID (integer) - Not Null, References Submissions.id
• Output URL (string)

story: #18

• ID (global auto incrementing)
• Status (enum [queued, playing, finished, failed]) - Not Null
• Win Reason (string)
• Lose Reason (string)
• Winner ID (integer) - References Teams.id
• Log URL (string)
• Created At (timestamp) - Not Null
• Updated At (timestamp) - Not Null

Add the test script developed in #4 to the Travis CI file.

The id of the database should match the ID given in gitlab, no reason to have 2 IDs

Do we even need this?

I personally prefer the idea of having a single team login.
It reduces the need for a user table and a team-member table.

There isnt much information we would gain from individual users.
The only thing this would add is pre-payment, but it seems there might be an easier way to do this without relying on this database of users.

story: #18

• Globally auto-incrementing ID - Not Null
• Name (string) - Not Null, Unique
• Contact Email (string) - Not Null, Unique
• Password (string) - Not Null
• Eligibility (boolean) - Not Null
• Created At (timestamp) - Not Null
• Updated At (timestamp) - Not Null

Stable is currently not representative of how the db will operate going forward and needs an update. Any schema changes or other fixes/updates to perform before pulling up stable again?

Story: siggame/colisee#10

Entities:

• Teams #19

• Submissions #14

• Games #20

Relations:

• Teams have Submissions #19

• Games have Submissions #21

Storing gamelogs on the database will significantly increase its size, and database space is a lot more expensive than general purpose space.

• User.name

• User.full_name

• User.email

• User.password

• Team.name

• Team.eligible

• latest_submission

• latest_submission_build_output

• latest_valid_submission

• latest_valid_submission_build_output