1 DROP TABLE IF EXISTS newsletter_issues
;
2 DROP TABLE IF EXISTS newsletters
;
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,
15 UNIQUE KEY (group_id
),
16 FOREIGN KEY (group_id
) REFERENCES groups (id)
19 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='Lists all newsletters';
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
25 WHERE groups.diminutif
IN ('Polytechnique.org', 'Ecole', 'AX')
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');
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
;
37 -----------------------
38 -- newsletter_issues --
39 -----------------------
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 '',
54 UNIQUE KEY (nlid
, short_name
),
55 FOREIGN KEY (nlid
) REFERENCES newsletters (id)
58 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='Lists issues of all newsletters';
60 -- Fill with all X.org nls
61 INSERT INTO newsletter_issues (nlid
, date, title
, state, head
, short_name
, mail_title
)
63 SELECT @idnl_xorg
, n.
date, n.titre
, n.bits
, n.head
, n.short_name
, n.titre_mail
67 -- Fill with all AX nls
68 INSERT INTO newsletter_issues (nlid
, date, title
, state, head
, signature
, short_name
, mail_title
)
70 SELECT @idnl_ax
, a.
date, a.title
, a.bits
, CONCAT("<cher> <prenom>,\n\n", a.body
), a.signature
, a.short_name
, a.subject
72 WHERE bits
!= 'invalid'
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
;
82 UPDATE newsletter_cat
SET nlid
= @idnl_xorg
;
84 ALTER TABLE newsletter_cat
ADD FOREIGN KEY (nlid
) REFERENCES newsletters (id)
87 ALTER TABLE newsletter_cat CHANGE titre title
varchar(128) NOT NULL DEFAULT '';
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`),
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
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
)
114 ALTER TABLE newsletter_art
ADD FOREIGN KEY (id) REFERENCES newsletter_issues (id)
122 -- Fix newsletter_ins: add nlid column, fix FK
123 ALTER TABLE newsletter_ins
ADD COLUMN nlid
INT(11) UNSIGNED
NOT NULL AFTER uid
;
125 UPDATE newsletter_ins
SET nlid
= @idnl_xorg
;
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;
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
)
137 ALTER TABLE newsletter_ins
ADD PRIMARY KEY (uid
, nlid
);
138 ALTER TABLE newsletter_ins
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
)
141 ALTER TABLE newsletter_ins
ADD FOREIGN KEY (last) REFERENCES newsletter_issues (id)
144 ALTER TABLE newsletter_ins
ADD FOREIGN KEY (nlid
) REFERENCES newsletters (id)
148 -- Add AXletter subscribers.
149 INSERT INTO newsletter_ins (nlid
, uid
, last, hash
)
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
)
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'