Commit | Line | Data |
---|---|---|
84163d58 RB |
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, | |
b18348d8 | 13 | criteria SET('axid', 'promo', 'geo') DEFAULT NULL, |
84163d58 RB |
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', | |
21ff78c9 | 45 | send_before datetime default NULL, |
84163d58 RB |
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 | |
b18348d8 | 116 | ON DELETE CASCADE; |
84163d58 RB |
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' |