r57025 MediaWiki - Code Review archive

Repository:	MediaWiki
Revision:	< r57024‎ \| r57025 \| r57026 >
Date:	20:27, 28 September 2009
Author:	leonsp
Status:	deferred
Tags:
Comment:	MediaWiki database schema for IBM DB2 * removed all foreign keys not in MySQL schema * reordered implicit order of columns in archive table to match MySQL * added missing ss_active_users column to site_stats table * added missing ipb_allow_usertalk column to ipblocks table
Modified paths:	/trunk/phase3/maintenance/ibm_db2/tables.sql (modified) (history)

Diff [purge]

Index: trunk/phase3/maintenance/ibm_db2/tables.sql
—	—	@@ -43,7 +43,8 @@
44	44
45	45
46	46	CREATE TABLE user_groups (
47		~~- ug_user INTEGER REFERENCES user(user_id) ON DELETE CASCADE,~~
	47	+ ug_user INTEGER NOT NULL DEFAULT 0,
	48	+ -- REFERENCES user(user_id) ON DELETE CASCADE,
48	49	ug_group VARCHAR(255) NOT NULL
49	50	);
50	51	CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
—	—	@@ -55,7 +56,8 @@
56	57
57	58	CREATE TABLE user_newtalk (
58	59	-- registered users key
59		~~- user_id INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE,~~
	60	+ user_id INTEGER NOT NULL DEFAULT 0,
	61	+ -- REFERENCES user(user_id) ON DELETE CASCADE,
60	62	-- anonymous users key
61	63	user_ip VARCHAR(40),
62	64	user_last_timestamp TIMESTAMP(3)
—	—	@@ -95,10 +97,12 @@
96	98
97	99	CREATE TABLE revision (
98	100	rev_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
99		~~- rev_page INTEGER REFERENCES page (page_id) ON DELETE CASCADE,~~
	101	+ rev_page INTEGER NOT NULL DEFAULT 0,
	102	+ -- REFERENCES page (page_id) ON DELETE CASCADE,
100	103	rev_text_id INTEGER, -- FK
101	104	rev_comment VARCHAR(1024),
102		~~- rev_user INTEGER NOT NULL REFERENCES user(user_id) ON DELETE RESTRICT,~~
	105	+ rev_user INTEGER NOT NULL DEFAULT 0,
	106	+ -- REFERENCES user(user_id) ON DELETE RESTRICT,
103	107	rev_user_text VARCHAR(255) NOT NULL,
104	108	rev_timestamp TIMESTAMP(3) NOT NULL,
105	109	rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
—	—	@@ -127,9 +131,9 @@
128	132	--pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'),
129	133	--pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
130	134	pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
131		~~- pr_page INTEGER NOT NULL~~
	135	+ pr_page INTEGER NOT NULL DEFAULT 0,
132	136	--(used to be nullable)
133		~~- REFERENCES page (page_id) ON DELETE CASCADE,~~
	137	+ -- REFERENCES page (page_id) ON DELETE CASCADE,
134	138	pr_type VARCHAR(60) NOT NULL,
135	139	pr_level VARCHAR(60) NOT NULL,
136	140	pr_cascade SMALLINT NOT NULL,
—	—	@@ -144,7 +148,8 @@
145	149	CREATE INDEX pr_cascade ON page_restrictions (pr_cascade);
146	150
147	151	CREATE TABLE page_props (
148		~~- pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,~~
	152	+ pp_page INTEGER NOT NULL DEFAULT 0,
	153	+ -- REFERENCES page (page_id) ON DELETE CASCADE,
149	154	pp_propname VARCHAR(255) NOT NULL,
150	155	pp_value CLOB(64K) INLINE LENGTH 4096 NOT NULL,
151	156	PRIMARY KEY (pp_page,pp_propname)
—	—	@@ -158,10 +163,10 @@
159	164	ar_namespace SMALLINT NOT NULL,
160	165	ar_title VARCHAR(255) NOT NULL,
161	166	ar_text CLOB(16M) INLINE LENGTH 4096,
162		~~- ar_page_id INTEGER,~~
163		~~- ar_parent_id INTEGER,~~
164	167	ar_comment VARCHAR(1024),
165		~~- ar_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	168	+ ar_user INTEGER NOT NULL,
	169	+ -- no foreign keys in MySQL
	170	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
166	171	ar_user_text VARCHAR(255) NOT NULL,
167	172	ar_timestamp TIMESTAMP(3) NOT NULL,
168	173	ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
—	—	@@ -169,7 +174,9 @@
170	175	ar_rev_id INTEGER,
171	176	ar_text_id INTEGER,
172	177	ar_deleted SMALLINT NOT NULL DEFAULT 0,
173		~~- ar_len INTEGER~~
	178	+ ar_len INTEGER,
	179	+ ar_page_id INTEGER,
	180	+ ar_parent_id INTEGER
174	181	);
175	182	CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
176	183	CREATE INDEX archive_user_text ON archive (ar_user_text);
—	—	@@ -177,7 +184,8 @@
178	185
179	186
180	187	CREATE TABLE redirect (
181		~~- rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,~~
	188	+ rd_from INTEGER NOT NULL PRIMARY KEY,
	189	+ --REFERENCES page(page_id) ON DELETE CASCADE,
182	190	rd_namespace SMALLINT NOT NULL DEFAULT 0,
183	191	rd_title VARCHAR(255) NOT NULL DEFAULT '',
184	192	rd_interwiki varchar(32),
—	—	@@ -187,14 +195,16 @@
188	196
189	197
190	198	CREATE TABLE pagelinks (
191		~~- pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,~~
	199	+ pl_from INTEGER NOT NULL DEFAULT 0,
	200	+ -- REFERENCES page(page_id) ON DELETE CASCADE,
192	201	pl_namespace SMALLINT NOT NULL,
193	202	pl_title VARCHAR(255) NOT NULL
194	203	);
195	204	CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
196	205
197	206	CREATE TABLE templatelinks (
198		~~- tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,~~
	207	+ tl_from INTEGER NOT NULL DEFAULT 0,
	208	+ -- REFERENCES page(page_id) ON DELETE CASCADE,
199	209	tl_namespace SMALLINT NOT NULL,
200	210	tl_title VARCHAR(255) NOT NULL
201	211	);
—	—	@@ -202,14 +212,16 @@
203	213	CREATE UNIQUE INDEX tl_from_idx ON templatelinks (tl_from,tl_namespace,tl_title);
204	214
205	215	CREATE TABLE imagelinks (
206		~~- il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,~~
	216	+ il_from INTEGER NOT NULL DEFAULT 0,
	217	+ -- REFERENCES page(page_id) ON DELETE CASCADE,
207	218	il_to VARCHAR(255) NOT NULL
208	219	);
209	220	CREATE UNIQUE INDEX il_from_idx ON imagelinks (il_to,il_from);
210	221	CREATE UNIQUE INDEX il_to_idx ON imagelinks (il_from,il_to);
211	222
212	223	CREATE TABLE categorylinks (
213		~~- cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,~~
	224	+ cl_from INTEGER NOT NULL DEFAULT 0,
	225	+ -- REFERENCES page(page_id) ON DELETE CASCADE,
214	226	cl_to VARCHAR(255) NOT NULL,
215	227	cl_sortkey VARCHAR(70),
216	228	cl_timestamp TIMESTAMP(3) NOT NULL
—	—	@@ -220,7 +232,8 @@
221	233
222	234
223	235	CREATE TABLE externallinks (
224		~~- el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,~~
	236	+ el_from INTEGER NOT NULL DEFAULT 0,
	237	+ -- REFERENCES page(page_id) ON DELETE CASCADE,
225	238	el_to VARCHAR(1024) NOT NULL,
226	239	el_index VARCHAR(1024) NOT NULL
227	240	);
—	—	@@ -248,7 +261,8 @@
249	262
250	263
251	264	CREATE TABLE langlinks (
252		~~- ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,~~
	265	+ ll_from INTEGER NOT NULL DEFAULT 0,
	266	+ -- REFERENCES page (page_id) ON DELETE CASCADE,
253	267	ll_lang VARCHAR(20),
254	268	ll_title VARCHAR(255)
255	269	);
—	—	@@ -263,6 +277,7 @@
264	278	ss_good_articles INTEGER DEFAULT 0,
265	279	ss_total_pages INTEGER DEFAULT -1,
266	280	ss_users INTEGER DEFAULT -1,
	281	+ ss_active_users INTEGER DEFAULT -1,
267	282	ss_admins INTEGER DEFAULT -1,
268	283	ss_images INTEGER DEFAULT 0
269	284	);
—	—	@@ -275,8 +290,10 @@
276	291	ipb_id INTEGER NOT NULL PRIMARY KEY,
277	292	--DEFAULT nextval('ipblocks_ipb_id_val'),
278	293	ipb_address VARCHAR(1024),
279		~~- ipb_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
280		~~- ipb_by INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE,~~
	294	+ ipb_user INTEGER NOT NULL DEFAULT 0,
	295	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
	296	+ ipb_by INTEGER NOT NULL DEFAULT 0,
	297	+ -- REFERENCES user(user_id) ON DELETE CASCADE,
281	298	ipb_by_text VARCHAR(255) NOT NULL DEFAULT '',
282	299	ipb_reason VARCHAR(1024) NOT NULL,
283	300	ipb_timestamp TIMESTAMP(3) NOT NULL,
—	—	@@ -288,7 +305,8 @@
289	306	ipb_range_start VARCHAR(1024),
290	307	ipb_range_end VARCHAR(1024),
291	308	ipb_deleted SMALLINT NOT NULL DEFAULT 0,
292		~~- ipb_block_email SMALLINT NOT NULL DEFAULT 0~~
	309	+ ipb_block_email SMALLINT NOT NULL DEFAULT 0,
	310	+ ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
293	311
294	312	);
295	313	CREATE INDEX ipb_address ON ipblocks (ipb_address);
—	—	@@ -308,7 +326,8 @@
309	327	img_major_mime VARCHAR(255) DEFAULT 'unknown',
310	328	img_minor_mime VARCHAR(32) DEFAULT 'unknown',
311	329	img_description VARCHAR(1024) NOT NULL DEFAULT '',
312		~~- img_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	330	+ img_user INTEGER NOT NULL DEFAULT 0,
	331	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
313	332	img_user_text VARCHAR(255) NOT NULL DEFAULT '',
314	333	img_timestamp TIMESTAMP(3),
315	334	img_sha1 VARCHAR(255) NOT NULL DEFAULT ''
—	—	@@ -318,14 +337,15 @@
319	338	CREATE INDEX img_sha1 ON image (img_sha1);
320	339
321	340	CREATE TABLE oldimage (
322		~~- oi_name VARCHAR(255) NOT NULL,~~
	341	+ oi_name VARCHAR(255) NOT NULL DEFAULT '',
323	342	oi_archive_name VARCHAR(255) NOT NULL,
324	343	oi_size INTEGER NOT NULL,
325	344	oi_width INTEGER NOT NULL,
326	345	oi_height INTEGER NOT NULL,
327	346	oi_bits SMALLINT NOT NULL,
328	347	oi_description VARCHAR(1024),
329		~~- oi_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	348	+ oi_user INTEGER NOT NULL DEFAULT 0,
	349	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
330	350	oi_user_text VARCHAR(255) NOT NULL,
331	351	oi_timestamp TIMESTAMP(3) NOT NULL,
332	352	oi_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
—	—	@@ -333,8 +353,8 @@
334	354	oi_major_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
335	355	oi_minor_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
336	356	oi_deleted SMALLINT NOT NULL DEFAULT 0,
337		~~- oi_sha1 VARCHAR(255) NOT NULL DEFAULT '',~~
338		~~- FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE~~
	357	+ oi_sha1 VARCHAR(255) NOT NULL DEFAULT ''
	358	+ --FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
339	359	);
340	360	--ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE;
341	361	CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
—	—	@@ -350,7 +370,8 @@
351	371	fa_archive_name VARCHAR(255),
352	372	fa_storage_group VARCHAR(255),
353	373	fa_storage_key VARCHAR(32),
354		~~- fa_deleted_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	374	+ fa_deleted_user INTEGER NOT NULL DEFAULT 0,
	375	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
355	376	fa_deleted_timestamp TIMESTAMP(3) NOT NULL,
356	377	fa_deleted_reason VARCHAR(255),
357	378	fa_size INTEGER NOT NULL,
—	—	@@ -362,7 +383,8 @@
363	384	fa_major_mime VARCHAR(255) DEFAULT 'unknown',
364	385	fa_minor_mime VARCHAR(255) DEFAULT 'unknown',
365	386	fa_description VARCHAR(1024) NOT NULL,
366		~~- fa_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	387	+ fa_user INTEGER NOT NULL DEFAULT 0,
	388	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
367	389	fa_user_text VARCHAR(255) NOT NULL,
368	390	fa_timestamp TIMESTAMP(3),
369	391	fa_deleted SMALLINT NOT NULL DEFAULT 0
—	—	@@ -378,7 +400,8 @@
379	401	--PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
380	402	rc_timestamp TIMESTAMP(3) NOT NULL,
381	403	rc_cur_time TIMESTAMP(3) NOT NULL,
382		~~- rc_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	404	+ rc_user INTEGER NOT NULL DEFAULT 0,
	405	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
383	406	rc_user_text VARCHAR(255) NOT NULL,
384	407	rc_namespace SMALLINT NOT NULL,
385	408	rc_title VARCHAR(255) NOT NULL,
—	—	@@ -386,7 +409,8 @@
387	410	rc_minor SMALLINT NOT NULL DEFAULT 0,
388	411	rc_bot SMALLINT NOT NULL DEFAULT 0,
389	412	rc_new SMALLINT NOT NULL DEFAULT 0,
390		~~- rc_cur_id INTEGER REFERENCES page(page_id) ON DELETE SET NULL,~~
	413	+ rc_cur_id INTEGER NOT NULL DEFAULT 0,
	414	+ -- REFERENCES page(page_id) ON DELETE SET NULL,
391	415	rc_this_oldid INTEGER NOT NULL,
392	416	rc_last_oldid INTEGER NOT NULL,
393	417	rc_type SMALLINT NOT NULL DEFAULT 0,
—	—	@@ -412,7 +436,8 @@
413	437
414	438
415	439	CREATE TABLE watchlist (
416		~~- wl_user INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE,~~
	440	+ wl_user INTEGER NOT NULL DEFAULT 0,
	441	+ -- REFERENCES user(user_id) ON DELETE CASCADE,
417	442	wl_namespace SMALLINT NOT NULL DEFAULT 0,
418	443	wl_title VARCHAR(255) NOT NULL,
419	444	wl_notificationtimestamp TIMESTAMP(3)
—	—	@@ -487,7 +512,8 @@
488	513	log_type VARCHAR(32) NOT NULL,
489	514	log_action VARCHAR(32) NOT NULL,
490	515	log_timestamp TIMESTAMP(3) NOT NULL,
491		~~- log_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	516	+ log_user INTEGER NOT NULL DEFAULT 0,
	517	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
492	518	-- Name of the user who performed this action
493	519	log_user_text VARCHAR(255) NOT NULL default '',
494	520	log_namespace SMALLINT NOT NULL,
—	—	@@ -508,7 +534,8 @@
509	535	CREATE TABLE trackbacks (
510	536	tb_id INTEGER NOT NULL PRIMARY KEY,
511	537	--PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
512		~~- tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,~~
	538	+ -- foreign key also in MySQL
	539	+ tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
513	540	tb_title VARCHAR(255) NOT NULL,
514	541	tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
515	542	tb_ex CLOB(64K) INLINE LENGTH 4096,
—	—	@@ -598,7 +625,8 @@
599	626	CREATE TABLE protected_titles (
600	627	pt_namespace SMALLINT NOT NULL,
601	628	pt_title VARCHAR(255) NOT NULL,
602		~~- pt_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,~~
	629	+ pt_user INTEGER NOT NULL DEFAULT 0,
	630	+ -- REFERENCES user(user_id) ON DELETE SET NULL,
603	631	pt_reason VARCHAR(1024),
604	632	pt_timestamp TIMESTAMP(3) NOT NULL,
605	633	pt_expiry TIMESTAMP(3) ,

Status & tagging log

17:40, 30 September 2009 Brion VIBBER (talk | contribs) changed the status of r57025 [removed: new added: deferred]