github.com/JBoudou/Itero@v0.1.7/sql/update-v0.1.7.mysql (about)

     1  ALTER TABLE Polls
     2    ADD COLUMN
     3      ShortURL          varchar(32),
     4    ADD
     5      CONSTRAINT Polls_ShortURL_unique UNIQUE (ShortURL);
     6  
     7  DELIMITER //
     8  
     9  CREATE OR REPLACE PROCEDURE Polls_checker_before (
    10    Title             tinytext,
    11    State             ENUM('Waiting','Active','Terminated'),
    12    Start             datetime,
    13    Salt              int unsigned,
    14    ShortURL          varchar(32),
    15    NbChoices         tinyint unsigned,
    16    MinNbRounds       smallint unsigned,
    17    MaxNbRounds       smallint unsigned,
    18    Deadline          datetime,
    19    MaxRoundDuration  time,
    20    RoundThreshold    double unsigned ,
    21    CurrentRound      tinyint unsigned,
    22    CurrentRoundStart timestamp
    23  )
    24  BEGIN
    25    IF length(Title) < 3 THEN
    26      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Title field is too short';
    27    END IF;
    28    IF State = '' THEN
    29      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unauthorized State value';
    30    END IF;
    31    IF Start IS NULL AND State = 'Waiting' THEN
    32      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A Start date must be given for Waiting polls';
    33    END IF;
    34    IF Salt >= 4194304 THEN
    35      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salt must be 22 bits long';
    36    END IF;
    37    IF ShortURL IS NOT NULL AND ShortURL NOT RLIKE '^[-_.~a-zA-Z0-9]+$' THEN
    38      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ShortURL must be a valid URI segment';
    39    END IF;
    40    IF NbChoices < 2 THEN
    41      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'NbChoices must be at least 2';
    42    END IF;
    43    IF MaxNbRounds < MinNbRounds THEN
    44      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'MaxNbRounds must be greater than MinNbRounds';
    45    END IF;
    46    IF MaxNbRounds IS NULL AND Deadline IS NULL THEN
    47      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'One amongst MaxNbRounds and Deadline must not be NULL';
    48    END IF;
    49    IF MaxRoundDuration < '00:01:00' THEN
    50      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'MaxRoundDuration must be at least one minute';
    51    END IF;
    52    IF RoundThreshold < 0 OR RoundThreshold > 1 THEN
    53      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'RoundThreshold must be in [0;1]';
    54    END IF;
    55    IF CurrentRound > MaxNbRounds THEN
    56      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CurrentRound must be at most MaxNbRounds';
    57    END IF;
    58  END;
    59  //
    60  
    61  CREATE OR REPLACE TRIGGER Polls_check_before_insert
    62    BEFORE INSERT ON Polls FOR EACH ROW
    63  BEGIN
    64    IF NEW.State != 'Waiting' AND NEW.State != 'Active' THEN
    65      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'State must be Waiting or Active on INSERT';
    66    END IF;
    67    IF NEW.CurrentRound != 0 THEN
    68      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CurrentRound must be zero on INSERT';
    69    END IF;
    70  
    71    SELECT Name
    72      INTO @Name
    73      FROM Users
    74     WHERE Id = NEW.Admin;
    75    IF @Name IS NULL THEN
    76      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin must correspond to a named user';
    77    END IF;
    78  
    79    SET NEW.Created           = CURRENT_TIMESTAMP();
    80    SET NEW.CurrentRoundStart = CURRENT_TIMESTAMP();
    81  
    82    CALL Polls_checker_before(NEW.Title, NEW.State, NEW.Start, NEW.Salt, NEW.ShortURL, NEW.NbChoices,
    83                              NEW.MinNbRounds, NEW.MaxNbRounds, NEW.Deadline, NEW.MaxRoundDuration,
    84                              NEW.RoundThreshold, NEW.CurrentRound, NEW.CurrentRoundStart);
    85  END;
    86  //
    87  
    88  CREATE OR REPLACE TRIGGER Polls_check_before_update
    89    BEFORE UPDATE ON Polls FOR EACH ROW
    90  BEGIN
    91    IF NEW.Created != OLD.Created THEN
    92      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Created cannot be changed';
    93    END IF;
    94    IF NEW.Admin != OLD.Admin THEN
    95      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin cannot be changed';
    96    END IF;
    97  
    98    IF NEW.CurrentRound > OLD.CurrentRound THEN
    99      SET NEW.CurrentRoundStart = CURRENT_TIMESTAMP();
   100    END IF;
   101    IF OLD.State = 'Waiting' AND NEW.State = 'Active' THEN
   102      SET NEW.CurrentRound = 0;
   103      SET NEW.CurrentRoundStart = CURRENT_TIMESTAMP();
   104    END IF;
   105    IF NEW.State = 'Terminated' THEN
   106      SET NEW.ShortURL = NULL;
   107    END IF;
   108  
   109    CALL Polls_checker_before(NEW.Title, NEW.State, NEW.Start, NEW.Salt, NEW.ShortURL, NEW.NbChoices,
   110                              NEW.MinNbRounds, NEW.MaxNbRounds, NEW.Deadline, NEW.MaxRoundDuration,
   111                              NEW.RoundThreshold, NEW.CurrentRound, NEW.CurrentRoundStart);
   112  END;
   113  //
   114  
   115  DELIMITER ;