Setup SQLite databases

[dominictdavies]

SQLite database should store:

• Users
• Lobbies
• TBA

Tasks:

• [ ] Finalise the SQLite database schema
• [ ] Fill the database with some dummy data
• [ ] Set up some simple querying of the database from the project code

[FAljanobi]

lets try our best not to create a sql injection vulnerability lol

One database with many tables Table Users has columns:

• UID- autogen primary key unique int NOT NULL
• Username - text not null
• password- text not null
• email- text not null- test for @ symbol
• authority-text not null-to flag admins

Table Game has columns:

• UID- autogen primary key unique int NOT NULL
• Name - text not null
• potentially a game desc?

lobby

• a lobby has multiple players -Users.uid foreign key -means new table
• a lobby has only one game-Games.uid foreign key
• a lobby has a unique id-Lobby.uid primary key
• a lobby has one desc
• a lobby has multiple tags foreign keys - means new table
• lobby expiry date - should we track creation date?
• user who created lobby? is this info we should track separately from who is current admin?
• lobby type= private/public

lobbytags - rows need to be made unique ;-; easy for dupe rows to spread

• sole purpose is to connect lobby.uid foreign key with tag.uid foreign key

lobbyplayers - rows need to be made unique ;-; easy for dupe rows to spread

• lobby.uid foreign primary key
• users.uid foreign key
• lobby authority level- admin, normal player, we could make a level for requests and track people wanting to join a lobby this way, could make a rejected and banned level for lobbies as well

tags

• uid
• desc/name/string

tell me if i forgot something

[FAljanobi]

forgot to add lobbytimes rip would need uid of lobby, the timeblock, and how often they play that timeblock there would be a possibilty of multiple time blocks(example is every weekend and every other thursday) so cant have lobby.uid unique ;-;

[FAljanobi]

to clarify on how this schema stores time: a lobby wants to play 2-5pm monday and 8-12pm saturday every other week they would have 2 entries on lobbytime and they would look like this: lobbyid,2-5pm startdate, weekly lobbyid,2-5pm startdate, every 2 weeks something along those lines

[FAljanobi]

also someone please double check my work on the db