| 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') DEFAULT 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 datetime 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' |