github.com/JBoudou/Itero@v0.1.7/sql/install.mysql

# Itero - Iterative vote online
# Copyright (C) 2020 Joseph Boudou
# 
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as
# published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
# 
# You should have received a copy of the GNU Affero General Public License
# along with this program.  If not, see <https://www.gnu.org/licenses/>.

# Compatible with 10.3.28-MariaDB MariaDB Server

SET sql_mode='TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';

## Deletion must be in reverse order ##

DROP PROCEDURE IF EXISTS Ballots_checker_before;
DROP TABLE IF EXISTS Ballots;

DROP PROCEDURE IF EXISTS Participants_checker_before;
DROP TABLE IF EXISTS Participants;

DROP PROCEDURE IF EXISTS Alternatives_checker_before;
DROP TABLE IF EXISTS Alternatives;

DROP PROCEDURE IF EXISTS Polls_checker_before;
DROP TABLE IF EXISTS Polls;
DROP TABLE IF EXISTS PollType;
DROP TABLE IF EXISTS PollRule;
DROP TABLE IF EXISTS RoundType;

DROP PROCEDURE  IF EXISTS Users_checker_before;
DROP TABLE      IF EXISTS Users;


######## Users ########


# Deletion of a user is not possible once she participated to a poll.
CREATE TABLE Users (

  # Passwd stores only a hash signature.
  Id        int unsigned  NOT NULL  AUTO_INCREMENT,
  Email     varchar(128)  ,
  Name      varchar(64)   ,
  Passwd    binary(32)    ,
  Hash      binary(3)     ,
  Created   timestamp     NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  Verified  bool          NOT NULL  DEFAULT FALSE,

  CONSTRAINT Users_pk PRIMARY KEY (Id),
  CONSTRAINT Users_Email_unique UNIQUE (Email),
  CONSTRAINT Users_Name_unique  UNIQUE (Name),
  CONSTRAINT Users_Hash_unique UNIQUE (Hash)

) ENGINE = InnoDB;

DELIMITER //

CREATE OR REPLACE PROCEDURE Users_checker_before (
  Email   varchar(128),
  Name    varchar(64),
  Passwd  binary(32),
  Hash    binary(3)
)
BEGIN
  IF Hash IS NULL AND (Email IS NULL OR Name IS NULL OR Passwd IS NULL) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'When Hash is NULL, Email, Name and Passwd must not be NULL';
  END IF;
  IF Hash IS NULL AND Email NOT LIKE '_%@_%.__%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email field is not valid';
  END IF;
  IF Hash IS NULL AND length(Name) < 2 THEN
    SIGNAL SQLSTATE '44999' SET MESSAGE_TEXT = 'Name field is too short';
  END IF;
END;
//

CREATE OR REPLACE TRIGGER Users_check_before_insert
  BEFORE INSERT ON Users FOR EACH ROW
BEGIN
  SET NEW.Created = CURRENT_TIMESTAMP();
  CALL Users_checker_before(NEW.Email, NEW.Name, NEW.Passwd, NEW.Hash);
END;
//

CREATE OR REPLACE TRIGGER Users_check_before_update
  BEFORE UPDATE ON Users FOR EACH ROW
BEGIN
  IF NEW.Created != OLD.Created THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Created cannot be changed';
  END IF;
  CALL Users_checker_before(NEW.Email, NEW.Name, NEW.Passwd, NEW.Hash);
END;
//

DELIMITER ;


######## Confirmations ########

CREATE TABLE Confirmations (

  Id      int unsigned            NOT NULL AUTO_INCREMENT,
  Salt    int unsigned            NOT NULL,
  Type    ENUM('verify','passwd') NOT NULL,
  User    int unsigned            NOT NULL,
  Expires datetime                NOT NULL,

  CONSTRAINT Confirmations_pk PRIMARY KEY (Id),
  CONSTRAINT Confirmations_User_fk FOREIGN KEY (User) REFERENCES Users (Id) ON DELETE CASCADE,
  CONSTRAINT Confirmations_UserType_unique UNIQUE (User, Type)

) ENGINE = InnoDB;

DELIMITER //

CREATE OR REPLACE PROCEDURE Confirmations_checker_before (
  Salt    int unsigned
)
BEGIN
  IF Salt >= 4194304 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salt must be 22 bits long';
  END IF;
END;
//

CREATE OR REPLACE TRIGGER Confirmations_check_before_insert
  BEFORE INSERT ON Confirmations FOR EACH ROW
BEGIN
  CALL Confirmations_checker_before(NEW.Salt);
END;
//

CREATE OR REPLACE TRIGGER Confirmations_check_before_update
  BEFORE UPDATE ON Confirmations FOR EACH ROW
BEGIN
  CALL Confirmations_checker_before(NEW.Salt);
END;
//

DELIMITER ;


######## Polls ########

# Internal type of polls.
# Describes alternatives, outcomes and ballots.
CREATE TABLE PollType (
  Id    tinyint unsigned NOT NULL,
  Label varchar(32) NOT NULL,
  CONSTRAINT PollType_pk PRIMARY KEY (Id),
  CONSTRAINT PollType_Label_unique UNIQUE (Label)
) ENGINE = InnoDB;

INSERT INTO PollType VALUES
# The outcome is a subset of the alternatives such that the sum of the cost of the alternatives in
# the outcome is at most MaxOutcomeCost.
# A ballot is a subset of the alternatives (all with rank 1) such that:
#  - if BallotCostIsCount is true, the cadinality of the ballot is at most MaxBallotCost,
#  - if BallotCostIsCount is false, the sum of the cost of the alternatives in the ballot is at
#    most MaxBallotCost.
  (0, 'Acceptance Set')
;

# How outcome is computed.
# See Go implementation for detail of each method.
CREATE TABLE PollRule (
  Id    tinyint unsigned NOT NULL,
  Label varchar(32) NOT NULL,
  CONSTRAINT PollRule_pk PRIMARY KEY (Id),
  CONSTRAINT PollRule_Label_unique UNIQUE (Label)
) ENGINE = InnoDB;

INSERT INTO PollRule VALUES
  (0, 'Plurality')
;

# How moves are made during each round.
CREATE TABLE RoundType (
  Id    tinyint unsigned NOT NULL,
  Label varchar(32) NOT NULL,
  CONSTRAINT RoundType_pk PRIMARY KEY (Id),
  CONSTRAINT RoundType_Label_unique UNIQUE (Label)
) ENGINE = InnoDB;

INSERT INTO RoundType VALUES
  (0, 'Freely Asynchronous')  # Participants can move at any time, any number of time.
;

# Deletion of a poll is possible (cascade).
CREATE TABLE Polls (

  Id                int unsigned      NOT NULL  AUTO_INCREMENT,
  Title             tinytext          NOT NULL,
  Description       text,
  Admin             int unsigned      NOT NULL,             # FK on Users
  Created           timestamp         NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  State             ENUM('Waiting','Active','Terminated') NOT NULL DEFAULT 'Active',
  Start             datetime,
  ShortURL          varchar(32),

  # Salt is used to make discovery of poll difficult
  Salt              int unsigned      NOT NULL,

  Type              tinyint unsigned  NOT NULL  DEFAULT 0,  # FK on PollType
  Rule              tinyint unsigned  NOT NULL  DEFAULT 0,  # FK on PollRule
  RoundType         tinyint unsigned  NOT NULL  DEFAULT 0,  # FK on RoundType

  Electorate        ENUM('All','Logged','Verified') NOT NULL DEFAULT 'Logged',
  Hidden            bool              NOT NULL  DEFAULT FALSE,

  NbChoices         tinyint unsigned  NOT NULL,
  MaxOutcomeCost    decimal(65,6)     NOT NULL  DEFAULT 1,
  MaxBallotCost     decimal(65,6)     NOT NULL  DEFAULT 1,
  BallotCostIsCount bool              NOT NULL  DEFAULT TRUE,

  # Whether the last vote is used when a participant did not vote for the last round.
  ReportVote        bool              NOT NULL  DEFAULT FALSE,

  # The poll ends as soon as one of the following condition holds:
  #  - CurrentRound >= MaxNbRounds
  #  - Deadline <= CURRENT_TIMESTAMP() AND CurrentRound >= MinNbRounds
  MinNbRounds       tinyint unsigned  NOT NULL  DEFAULT 2,
  MaxNbRounds       tinyint unsigned            DEFAULT 10,
  Deadline          datetime,

  # The round ends as soon as one of the following conditions holds:
  #  - addtime(CurrentRoundStart, MaxRoundDuration) >= CURRENT_TIMESTAMP()
  #  - CurrentRound > 0 AND RoundThreshold = 0 AND one participant moved for this round
  #  - CurrentRound > 0 AND RoundThreshold > 0 AND the proportion of participants who moved for this round >= RoundThreshold
  MaxRoundDuration  time                        DEFAULT '24:00:00',
  RoundThreshold    double unsigned   NOT NULL  DEFAULT 1,

  CurrentRound      tinyint unsigned  NOT NULL  DEFAULT 0,
  CurrentRoundStart timestamp         NOT NULL  DEFAULT '2020-01-01',

  CONSTRAINT Polls_pk PRIMARY KEY (Id),
  
  CONSTRAINT Polls_Admin_fk FOREIGN KEY (Admin) REFERENCES Users (Id),
  CONSTRAINT Polls_Type_fk FOREIGN KEY (Type) REFERENCES PollType (Id),
  CONSTRAINT Polls_Rule_fk FOREIGN KEY (Rule) REFERENCES PollRule (Id),
  CONSTRAINT Polls_RoundType_fk FOREIGN KEY (RoundType) REFERENCES RoundType (Id),

  CONSTRAINT Polls_ShortURL_unique UNIQUE (ShortURL)

) ENGINE = InnoDB;

DELIMITER //

CREATE FUNCTION RoundDeadline(
  CurrentRoundStart datetime,
  MaxRoundDuration time,
  Deadline datetime,
  CurrentRound tinyint,
  MinNbRounds tinyint
)
  RETURNS datetime DETERMINISTIC
BEGIN
  DECLARE one datetime;
  SET one = ADDTIME(CurrentRoundStart, MaxRoundDuration);

  IF CurrentRound + 1 < MinNbRounds OR Deadline < one THEN
    RETURN one;
  END IF;

  IF ADDTIME(one, MaxRoundDuration) > Deadline THEN
    RETURN Deadline;
  END IF;

  RETURN one;
END;
//

CREATE OR REPLACE PROCEDURE Polls_checker_before (
  Title             tinytext,
  State             ENUM('Waiting','Active','Terminated'),
  Start             datetime,
  Salt              int unsigned,
  ShortURL          varchar(32),
  NbChoices         tinyint unsigned,
  MinNbRounds       smallint unsigned,
  MaxNbRounds       smallint unsigned,
  Deadline          datetime,
  MaxRoundDuration  time,
  RoundThreshold    double unsigned ,
  CurrentRound      tinyint unsigned,
  CurrentRoundStart timestamp
)
BEGIN
  IF length(Title) < 3 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Title field is too short';
  END IF;
  IF State = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unauthorized State value';
  END IF;
  IF Start IS NULL AND State = 'Waiting' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A Start date must be given for Waiting polls';
  END IF;
  IF Salt >= 4194304 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salt must be 22 bits long';
  END IF;
  IF ShortURL IS NOT NULL AND ShortURL NOT RLIKE '^[-_.~a-zA-Z0-9]+$' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ShortURL must be a valid URI segment';
  END IF;
  IF NbChoices < 2 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'NbChoices must be at least 2';
  END IF;
  IF MaxNbRounds < MinNbRounds THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'MaxNbRounds must be greater than MinNbRounds';
  END IF;
  IF MaxNbRounds IS NULL AND Deadline IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'One amongst MaxNbRounds and Deadline must not be NULL';
  END IF;
  IF MaxRoundDuration < '00:01:00' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'MaxRoundDuration must be at least one minute';
  END IF;
  IF RoundThreshold < 0 OR RoundThreshold > 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'RoundThreshold must be in [0;1]';
  END IF;
  IF CurrentRound > MaxNbRounds THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CurrentRound must be at most MaxNbRounds';
  END IF;
END;
//

CREATE OR REPLACE TRIGGER Polls_check_before_insert
  BEFORE INSERT ON Polls FOR EACH ROW
BEGIN
  IF NEW.State != 'Waiting' AND NEW.State != 'Active' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'State must be Waiting or Active on INSERT';
  END IF;
  IF NEW.CurrentRound != 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CurrentRound must be zero on INSERT';
  END IF;

  SELECT Name
    INTO @Name
    FROM Users
   WHERE Id = NEW.Admin;
  IF @Name IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin must correspond to a named user';
  END IF;

  SET NEW.Created           = CURRENT_TIMESTAMP();
  SET NEW.CurrentRoundStart = CURRENT_TIMESTAMP();

  CALL Polls_checker_before(NEW.Title, NEW.State, NEW.Start, NEW.Salt, NEW.ShortURL, NEW.NbChoices,
                            NEW.MinNbRounds, NEW.MaxNbRounds, NEW.Deadline, NEW.MaxRoundDuration,
                            NEW.RoundThreshold, NEW.CurrentRound, NEW.CurrentRoundStart);
END;
//

CREATE OR REPLACE TRIGGER Polls_check_before_update
  BEFORE UPDATE ON Polls FOR EACH ROW
BEGIN
  IF NEW.Created != OLD.Created THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Created cannot be changed';
  END IF;
  IF NEW.Admin != OLD.Admin THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin cannot be changed';
  END IF;

  IF NEW.CurrentRound > OLD.CurrentRound THEN
    SET NEW.CurrentRoundStart = CURRENT_TIMESTAMP();
  END IF;
  IF OLD.State = 'Waiting' AND NEW.State = 'Active' THEN
    SET NEW.CurrentRound = 0;
    SET NEW.CurrentRoundStart = CURRENT_TIMESTAMP();
  END IF;
  IF NEW.State = 'Terminated' THEN
    SET NEW.ShortURL = NULL;
  END IF;

  CALL Polls_checker_before(NEW.Title, NEW.State, NEW.Start, NEW.Salt, NEW.ShortURL, NEW.NbChoices,
                            NEW.MinNbRounds, NEW.MaxNbRounds, NEW.Deadline, NEW.MaxRoundDuration,
                            NEW.RoundThreshold, NEW.CurrentRound, NEW.CurrentRoundStart);
END;
//

# Manual ON DELETE CASCADE because we want Ballots to be automatically deleted when the poll is
# deleted, but we don't want to allow deletion of an alternatives when someone voted for it.
CREATE TRIGGER Polls_cascade_delete_before
  BEFORE DELETE ON Polls FOR EACH ROW
BEGIN
  DELETE FROM Participants wHERE Poll = OLD.id;
END;
//

DELIMITER ;


######## Alternatives ########

CREATE TABLE Alternatives (

  # Beware that the primary key is (Poll, Id), not Id alone.
  Poll    int unsigned      NOT NULL,   # FK on Polls
  Id      tinyint unsigned  NOT NULL,
  Name    varchar(128)      NOT NULL,
  Cost    decimal(65,6)     NOT NULL  DEFAULT 1,

  CONSTRAINT Alternatives_pk PRIMARY KEY (Poll, Id),
  CONSTRAINT Alternatives_PollName_unique UNIQUE (Poll, Name),

  CONSTRAINT Alternatives_Poll_fk FOREIGN KEY (Poll) REFERENCES Polls (Id) ON DELETE CASCADE

) ENGINE = InnoDB;

DELIMITER //

CREATE PROCEDURE Alternatives_checker_before (
  Poll    int unsigned    ,
  Id      tinyint unsigned,
  Name    varchar(128)    ,
  Cost    decimal(65,6)
)
BEGIN

  IF length(Name) < 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Name cannot be empty';
  END IF;

  SELECT p.NbChoices, p.MaxOutcomeCost, p.MaxBallotCost, p.BallotCostIsCount
    INTO @NbChoices, @MaxBallotCost, @MaxBallotCost, @BallotCostIsCount
    FROM Polls AS p
   WHERE p.Id = Poll;

  IF Id >= @NbChoices THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Id must be less than NbChoices';
  END IF;
  IF Cost > @MaxOutcomeCost THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cost must be at most MaxOutcomeCost';
  END IF;
  IF NOT @BallotCostIsCount AND Cost > @MaxBallotCost THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cost must be at most MaxBallotCost';
  END IF;

END;
//

CREATE TRIGGER Alternatives_check_before_insert
  BEFORE INSERT ON Alternatives FOR EACH ROW
BEGIN
  CALL Alternatives_checker_before(NEW.Poll, NEW.Id, NEW.Name, NEW.Cost);
END;
//

CREATE TRIGGER Alternatives_check_before_update
  BEFORE UPDATE ON Alternatives FOR EACH ROW
BEGIN
  CALL Alternatives_checker_before(NEW.Poll, NEW.Id, NEW.Name, NEW.Cost);
END;
//

DELIMITER ;


######## Participants ########

CREATE TABLE Participants (

  User      int unsigned      NOT NULL,
  Poll      int unsigned      NOT NULL,
  Round     tinyint unsigned  NOT NULL,

  CONSTRAINT Participants_pk PRIMARY KEY (User, Poll, Round),

  CONSTRAINT Participants_User_fk FOREIGN KEY (User) REFERENCES Users (Id),
  CONSTRAINT Participants_Poll_fk FOREIGN KEY (Poll) REFERENCES Polls (Id) ON DELETE CASCADE

) ENGINE = InnoDB;

DELIMITER //

CREATE PROCEDURE Participants_checker_before (
  Poll  int unsigned,
  Round tinyint unsigned
)
BEGIN
  SELECT p.CurrentRound
    INTO @CurrentRound
    FROM Polls AS p
   WHERE p.id = Poll;

  IF Round > @CurrentRound THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Round must be at most CurrentRound';
  END IF;
END;
//

CREATE TRIGGER Participants_check_before_insert
  BEFORE INSERT ON Participants FOR EACH ROW
BEGIN
  CALL Participants_checker_before(NEW.Poll, NEW.Round);
END;
//

CREATE TRIGGER Participants_check_before_update
  BEFORE UPDATE ON Participants FOR EACH ROW
BEGIN
  CALL Participants_checker_before(NEW.Poll, NEW.Round);
END;
//

DELIMITER ;


# MariaDB 5.5 does not allow subquery in view's queries (sic!).
# Therefore we need an intermediate view...
CREATE SQL SECURITY INVOKER VIEW Participants_workaround AS
  SELECT Poll
    FROM Participants
   GROUP By Poll, User;


CREATE SQL SECURITY INVOKER VIEW Participants_Poll_Count AS
  SELECT Poll, COUNT(*) AS Count
    FROM Participants_workaround
   GROUP BY Poll;

CREATE SQL SECURITY INVOKER VIEW Participants_Round_Count AS
  SELECT Poll, Round, COUNT(*) AS Count
    FROM Participants
   GROUP BY Poll, Round;



######## Ballots ########

# Simple ranked ballot.
# Other tables for ballots may be added in the future.
CREATE TABLE Ballots (

  User        int unsigned      NOT NULL,
  Poll        int unsigned      NOT NULL,
  Alternative tinyint unsigned      NULL,
  Round       tinyint unsigned  NOT NULL,
  Rank        tinyint           NOT NULL  DEFAULT 1,
  Modified    timestamp         NOT NULL  DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT Ballots_pk PRIMARY KEY (User, Poll, Alternative, Round),

  CONSTRAINT Ballots_Participant_fk FOREIGN KEY (User, Poll, Round) REFERENCES Participants (User, Poll, Round) ON DELETE CASCADE,
  CONSTRAINT Ballots_Alternative_fk FOREIGN KEY (Poll, Alternative) REFERENCES Alternatives (Poll, Id)

) ENGINE = InnoDB;

DELIMITER //

CREATE TRIGGER Ballots_check_before_insert
  BEFORE INSERT ON Ballots FOR EACH ROW
BEGIN
  SET NEW.Modified = CURRENT_TIMESTAMP();
END;
//

CREATE TRIGGER Ballots_check_before_update
  BEFORE UPDATE ON Ballots FOR EACH ROW
BEGIN
  SET NEW.Modified = CURRENT_TIMESTAMP();
END;
//

DELIMITER ;