64 lines
1.9 KiB
SQL
64 lines
1.9 KiB
SQL
CREATE TABLE ElectionInformation(
|
|
ElectionName varchar(30) not null unique,
|
|
ThreadId bigint unsigned not null,
|
|
CouncilVoteMsgId bigint unsigned,
|
|
HighCouncilVoteMsgId bigint unsigned,
|
|
CouncilNominationMsgId bigint unsigned,
|
|
HighCouncilNominationMsgId bigint unsigned,
|
|
PRIMARY KEY (ElectionName)
|
|
);
|
|
|
|
CREATE TABLE CouncilMembers(
|
|
ElectionName varchar(30) not null,
|
|
MemberId bigint unsigned not null,
|
|
SpeechMsgId bigint unsigned not null,
|
|
Elected boolean DEFAULT false,
|
|
DiscordManager boolean DEFAULT false,
|
|
ClanLeader boolean DEFAULT false,
|
|
DiscordManagerSpeechId bigint unsigned,
|
|
ClanLeaderSpeechId bigint unsigned,
|
|
FOREIGN KEY (ElectionName) REFERENCES ElectionInformation(ElectionName),
|
|
PRIMARY KEY (ElectionName, MemberId)
|
|
);
|
|
|
|
CREATE TABLE Votes(
|
|
ElectionName varchar(30) not null,
|
|
VoterId bigint unsigned not null,
|
|
VotedForId bigint unsigned,
|
|
Type varchar(14) not null DEFAULT 'council' check("Type" in ('council', 'discordmanager', 'clanleader')),
|
|
FOREIGN KEY (ElectionName) REFERENCES ElectionInformation(ElectionName),
|
|
PRIMARY KEY (ElectionName, VoterId, Type)
|
|
);
|
|
|
|
CREATE TABLE UserBirthday (
|
|
UserId bigint unsigned not null,
|
|
Birthday varchar(5) not null,
|
|
PRIMARY KEY (UserId)
|
|
);
|
|
|
|
CREATE TABLE Presences (
|
|
Id integer PRIMARY KEY,
|
|
Presence varchar(100) not null
|
|
);
|
|
|
|
CREATE TABLE Points (
|
|
QuarterName varchar(30) not null,
|
|
UserId varchar(32) not null,
|
|
DiscordMember boolean not null,
|
|
EventPoints int unsigned DEFAULT 0,
|
|
ClanPoints int unsigned DEFAULT 0,
|
|
PRIMARY KEY (QuarterName, UserId)
|
|
);
|
|
|
|
CREATE TABLE Messages (
|
|
Path varchar(100) PRIMARY KEY,
|
|
Id bigint unsigned not null
|
|
);
|
|
|
|
CREATE TABLE PointsLimit (
|
|
UserId varchar(32) not null,
|
|
EventPoints int unsigned DEFAULT 0,
|
|
ClanPoints int unsigned DEFAULT 0,
|
|
Week varchar(7) not null,
|
|
PRIMARY KEY (UserId, Week)
|
|
); |