Database

All UWALC related information is stored under the uwaulc database. Currently there are no other databases, but there is a large room for scalability for other servers/clubs.

The database tables are as shown below. As the field names are intuitive, I will only briefly outline each fields.

users
student_id	Primary Key
name	Full name
discord	Discord discriminator (e.g. Bruce#1234)
discord_id	Discord ID (e.g. 1234567890123456789)
summoner	IGN (e.g. Bruce)
summoner_id	Summoner ID (e.g. dhgRDWjwi132131)
join_ts	Join timestamp
end_ts	Membership expiry timestamp

points
student_id	Foreign Key
points	Rift Champions Points
wins	Wins for W/L calculation
losses	Losses for W/L calculation
mvp	MVP count
ace	Ace count

profiles
student_id	Foreign Key
position	Club positions
badges	Profile Badges Comma Separated Enum

tftpoints	depreciated

Profile Enums

The profile table has a position attribute which takes in any varchar(100).

It is technically not an enum and can take any value, and will be displayed when typing the .profile command on the UI. However, here are some common values we have used previously for consistency.

Position Value	Description
Committee Member	Ordinary Committee Member
Vice President	Club Vice President
President	Club President
Treasurer	Club Treasurer
null	Club Member

The profile table also has a badges attribute which takes in any varchar(300).

This requires a comma-separated list of badge enum values. These badges are extracted from another discord server so it is important that the names match a badge (otherwise that individual's profile will not work). Here are the currently available badge enums.

Badge	Description
Empty	No badge (Must be used on its own without any other badges)
RiftChampionsParticipant	Participated in Rift Champions
RiftChampions$place	Previous Rift Champions rank (Top 10 players only e.g. RiftChampions1st or RiftChampions10th)
OUCParticipant	Participated in OUC
GoldAndUnderParticipant	Participated in OUC
CommitteeMember	Current Club Committee Member
ULCPresident	President of the Club
ULCVicePresident	Vice President of the Club
MinionDeveloper	Initial Developer for Minion

These badges are applied manually by executing the appropriate SQL query and should be carefully executed.

Queries

Here are some useful queries for profile updates.

View the list of users who do not have the RiftChampionsParticipant badge and require it

select p.student_id from profiles p, points pt 
where badges not like "%RiftChampionsParticipant%" 
and p.student_id = pt.student_id 
and pt.points != 0;

Updating them to have the participant badge

update profiles set badges = "RiftChampionsParticipant" 
where badges not like "%RiftChampionsParticipant%" 
and student_id in (select pt.student_id from (select * from points) pt where pt.points != 0);

Get top ten points

select * from points order by points desc limit 10;

Use to update individual badge, need to change concat value and student id

update profiles set badges=concat(badges, ",RiftChampions$place") where student_id = $studentId

# Where $place and $studentId are replaced accordingly