upgrade/1.1.0/20_newsletter.sql

   1 DROP TABLE IF EXISTS newsletter_issues;
   2 DROP TABLE IF EXISTS newsletters;
   3
   4 -----------------
   5 -- newsletters --
   6 -----------------
   7
   8 CREATE TABLE newsletters (
   9   id int(11) unsigned NOT NULL auto_increment,
  10   group_id smallint(5) UNSIGNED NOT NULL,
  11   name varchar(255) NOT NULL,
  12   custom_css BOOL NOT NULL DEFAULT FALSE,
  13   criteria SET('axid', 'promo', 'geo') DEFAUL NULL,
  14   PRIMARY KEY (id),
  15   UNIQUE KEY  (group_id),
  16   FOREIGN KEY (group_id) REFERENCES groups (id)
  17                          ON UPDATE CASCADE
  18                          ON DELETE CASCADE
  19 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lists all newsletters';
  20
  21 -- Filling it with default values for X.org / AX / Ecole
  22 INSERT INTO newsletters (group_id, name, custom_css)
  23       ( SELECT  groups.id, CONCAT("Lettre de ", groups.nom), TRUE
  24           FROM  groups
  25          WHERE  groups.diminutif IN ('Polytechnique.org', 'Ecole', 'AX')
  26       );
  27
  28 -- Set variables for simpler queries later on
  29 SET @idnl_xorg = (SELECT nls.id FROM newsletters AS nls LEFT JOIN groups AS g ON (g.id = nls.group_id) WHERE g.diminutif = 'Polytechnique.org');
  30 SET @idnl_ax = (SELECT nls.id FROM newsletters AS nls LEFT JOIN groups AS g ON (g.id = nls.group_id) WHERE g.diminutif = 'AX');
  31 SET @idnl_ecole = (SELECT nls.id FROM newsletters AS nls LEFT JOIN groups AS g ON (g.id = nls.group_id) WHERE g.diminutif = 'Ecole');
  32
  33 UPDATE newsletters SET name = "Lettre de l'AX", criteria = 'promo,axid' WHERE id = @idnl_ax;
  34 UPDATE newsletters SET name = "Lettre mensuelle de Polytechnique.org", criteria = 'promo' WHERE id = @idnl_xorg;
  35 UPDATE newsletters SET name = "DiXit, lettre de l'École polytechnique", criteria = 'promo' WHERE id = @idnl_ecole;
  36
  37 -----------------------
  38 -- newsletter_issues --
  39 -----------------------
  40
  41 CREATE TABLE newsletter_issues (
  42   nlid int(11) unsigned NOT NULL,
  43   id int(11) unsigned NOT NULL auto_increment,
  44   date date NOT NULL default '0000-00-00',
  45   send_before date default NULL,
  46   state enum('sent','new','pending') NOT NULL default 'new',
  47   sufb_json text default NULL,
  48   title varchar(255) NOT NULL default '',
  49   head mediumtext NOT NULL,
  50   signature mediumtext NOT NULL,
  51   short_name varchar(16) default NULL,
  52   mail_title varchar(255) NOT NULL default '',
  53   PRIMARY KEY (id),
  54   UNIQUE KEY  (nlid, short_name),
  55   FOREIGN KEY (nlid) REFERENCES newsletters (id)
  56                      ON UPDATE CASCADE
  57                      ON DELETE CASCADE
  58 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lists issues of all newsletters';
  59
  60 -- Fill with all X.org nls
  61 INSERT INTO newsletter_issues (nlid, date, title, state, head, short_name, mail_title)
  62         (
  63           SELECT  @idnl_xorg, n.date, n.titre, n.bits, n.head, n.short_name, n.titre_mail
  64             FROM  newsletter AS n
  65         );
  66
  67 -- Fill with all AX nls
  68 INSERT INTO newsletter_issues (nlid, date, title, state, head, signature, short_name, mail_title)
  69         (
  70           SELECT  @idnl_ax, a.date, a.title, a.bits, CONCAT("<cher> <prenom>,\n\n", a.body), a.signature, a.short_name, a.subject
  71             FROM  axletter AS a
  72            WHERE  bits != 'invalid'
  73         );
  74
  75 --------------------
  76 -- newsletter_cat --
  77 --------------------
  78
  79 -- Fix newsletter_cat: add nlid, add FK, rename title
  80 ALTER TABLE newsletter_cat ADD COLUMN nlid INT(11) UNSIGNED NOT NULL AFTER cid;
  81
  82 UPDATE newsletter_cat SET nlid = @idnl_xorg;
  83
  84 ALTER TABLE newsletter_cat ADD FOREIGN KEY (nlid) REFERENCES newsletters (id)
  85                                                   ON UPDATE CASCADE
  86                                                   ON DELETE CASCADE;
  87 ALTER TABLE newsletter_cat CHANGE titre title varchar(128) NOT NULL DEFAULT '';
  88
  89 -- Final state:
  90 --
  91 -- CREATE TABLE `newsletter_cat` (
  92 --   `cid` tinyint(3) unsigned NOT NULL auto_increment,
  93 --   `nlid` int(11) unsigned NOT NULL,
  94 --   `pos` tinyint(3) unsigned NOT NULL default '0',
  95 --   `title` varchar(128) NOT NULL default '',
  96 --   PRIMARY KEY  (`cid`),
  97 --   KEY `pos` (`pos`),
  98 --   KEY `nlid` (`nlid`),
  99 --   CONSTRAINT `newsletter_cat_ibfk_1` FOREIGN KEY (`nlid`) REFERENCES `newsletters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 100 -- ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
 101
 102
 103 --------------------
 104 -- newsletter_art --
 105 --------------------
 106
 107 -- Fix newsletter_cat: add nlid, add FK, rename title
 108 ALTER TABLE newsletter_art DROP FOREIGN KEY newsletter_art_ibfk_1;
 109    UPDATE  newsletter_art AS na
 110 LEFT JOIN  newsletter AS n ON na.id = n.id
 111 LEFT JOIN  newsletter_issues AS ni ON (ni.nlid = @idnl_xorg AND ni.short_name = n.short_name)
 112       SET  na.id = ni.id;
 113
 114 ALTER TABLE newsletter_art ADD FOREIGN KEY (id) REFERENCES newsletter_issues (id)
 115                                                 ON UPDATE CASCADE
 116                                                 ON DELETE CASCADE
 117
 118 --------------------
 119 -- newsletter_ins --
 120 --------------------
 121
 122 -- Fix newsletter_ins: add nlid column, fix FK
 123 ALTER TABLE newsletter_ins ADD COLUMN nlid INT(11) UNSIGNED NOT NULL AFTER uid;
 124
 125 UPDATE  newsletter_ins SET nlid = @idnl_xorg;
 126
 127 -- We have to drop all FKs in order to update 'last' indexes.
 128 ALTER TABLE newsletter_ins DROP FOREIGN KEY newsletter_ins_ibfk_1;
 129 ALTER TABLE newsletter_ins DROP FOREIGN KEY newsletter_ins_ibfk_2;
 130 ALTER TABLE newsletter_ins DROP PRIMARY KEY;
 131
 132    UPDATE  newsletter_ins AS ni
 133 LEFT JOIN  newsletter AS n ON (ni.last = n.id)
 134 LEFT JOIN  newsletter_issues AS ns ON (n.short_name = ns.short_name)
 135       SET  ni.last = ns.id;
 136
 137 ALTER TABLE newsletter_ins ADD PRIMARY KEY (uid, nlid);
 138 ALTER TABLE newsletter_ins ADD FOREIGN KEY (uid) REFERENCES accounts (uid)
 139                                                   ON UPDATE CASCADE
 140                                                   ON DELETE CASCADE;
 141 ALTER TABLE newsletter_ins ADD FOREIGN KEY (last) REFERENCES newsletter_issues (id)
 142                                                   ON UPDATE CASCADE
 143                                                   ON DELETE CASCADE;
 144 ALTER TABLE newsletter_ins ADD FOREIGN KEY (nlid) REFERENCES newsletters (id)
 145                                                   ON UPDATE CASCADE
 146                                                   ON DELETE CASCADE;
 147
 148 -- Add AXletter subscribers.
 149 INSERT INTO newsletter_ins  (nlid, uid, last, hash)
 150         (
 151           SELECT  @idnl_ax, ai.uid, MAX(ni.id), ai.hash
 152             FROM  axletter_ins AS ai
 153        LEFT JOIN  axletter AS a ON (ai.last = a.id)
 154        LEFT JOIN  newsletter_issues AS ni ON (ni.nlid = @idnl_ax AND ni.short_name = a.short_name)
 155         GROUP BY  ai.uid
 156     );
 157
 158 -- Final state:
 159 --
 160 -- CREATE TABLE `newsletter_ins` (
 161 --   `uid` int(11) unsigned NOT NULL default '0',
 162 --   `nlid` int(11) unsigned NOT NULL,
 163 --   `last` int(11) unsigned default NULL,
 164 --   `hash` varchar(32) default NULL,
 165 --   PRIMARY KEY  (`uid`,`nlid`),
 166 --   KEY `last` (`last`),
 167 --   KEY `nlid` (`nlid`),
 168 --   CONSTRAINT `newsletter_ins_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `accounts` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 169 --   CONSTRAINT `newsletter_ins_ibfk_2` FOREIGN KEY (`last`) REFERENCES `newsletter_issues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 170 --   CONSTRAINT `newsletter_ins_ibfk_3` FOREIGN KEY (`nlid`) REFERENCES `newsletters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 171 -- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='liste des abonnés à la newsletter'