r19632 MediaWiki - Code Review archive

Repository:	MediaWiki
Revision:	< r19631‎ \| r19632 \| r19633 >
Date:	17:04, 24 January 2007
Author:	brion
Status:	old
Tags:
Comment:	* (bug 8749) Bring MySQL 5 table defs back into sync Patch from siebrand: http://bugzilla.wikimedia.org/attachment.cgi?id=3131
Modified paths:	/trunk/phase3/RELEASE-NOTES (modified) (history) /trunk/phase3/maintenance/mysql5/tables-binary.sql (modified) (history) /trunk/phase3/maintenance/mysql5/tables.sql (modified) (history)

Diff [purge]

Index: trunk/phase3/maintenance/mysql5/tables.sql
—	—	@@ -90,7 +90,7 @@
91	91	-- Timestamp of the last time when a new password was
92	92	-- sent, for throttling purposes
93	93	user_newpass_time char(14) binary,
94		-
	94	+
95	95	-- Note: email should be restricted, not public info.
96	96	-- Same with passwords.
97	97	user_email tinytext NOT NULL,
—	—	@@ -181,6 +181,7 @@
182	182	user_ip varchar(40) NOT NULL default '',
183	183	INDEX user_id (user_id),
184	184	INDEX user_ip (user_ip)
	185	+
185	186	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
186	187
187	188
—	—	@@ -419,7 +420,6 @@
420	421
421	422	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
422	423
423		-
424	424	--
425	425	-- Track links to images used inline
426	426	-- We don't distinguish live from broken links here, so
—	—	@@ -456,7 +456,8 @@
457	457	-- The title of the linking page, or an optional override
458	458	-- to determine sort order. Sorting is by binary order, which
459	459	-- isn't always ideal, but collations seem to be an exciting
460		~~- -- and dangerous new world in MySQL...~~
	460	+ -- and dangerous new world in MySQL... The sortkey is updated
	461	+ -- if no override exists and cl_from is renamed.
461	462	--
462	463	-- For MySQL 4.1+ with charset set to utf8, the sort key index
463	464	-- needs cut to be smaller than 1024 bytes (at 3 bytes per char).
—	—	@@ -599,8 +600,8 @@
600	601	-- Indicates that the IP address was banned because a banned
601	602	-- user accessed a page through it. If this is 1, ipb_address
602	603	-- will be hidden, and the block identified by block ID number.
603		~~- ipb_auto bool NOT NULL default '0',~~
604		-
	604	+ ipb_auto bool NOT NULL default 0,
	605	+
605	606	-- If set to 1, block applies only to logged-out users
606	607	ipb_anon_only bool NOT NULL default 0,
607	608
—	—	@@ -609,15 +610,15 @@
610	611
611	612	-- Block triggers autoblocks
612	613	ipb_enable_autoblock bool NOT NULL default '1',
613		-
	614	+
614	615	-- Time at which the block will expire.
615	616	ipb_expiry char(14) binary NOT NULL default '',
616	617
617	618	-- Start and end of an address range, in hexadecimal
618	619	-- Size chosen to allow IPv6
619		~~- ipb_range_start varchar(32) NOT NULL default '',~~
620		~~- ipb_range_end varchar(32) NOT NULL default '',~~
621		-
	620	+ ipb_range_start tinyblob NOT NULL,
	621	+ ipb_range_end tinyblob NOT NULL,
	622	+
622	623	PRIMARY KEY ipb_id (ipb_id),
623	624
624	625	-- Unique index to support "user already blocked" messages
—	—	@@ -673,7 +674,7 @@
674	675
675	676	-- user_id and user_name of uploader.
676	677	img_user int(5) unsigned NOT NULL default '0',
677		~~- img_user_text varchar(255) binary NOT NULL default '',~~
	678	+ img_user_text varchar(255) binary NOT NULL,
678	679
679	680	-- Time of the upload.
680	681	img_timestamp char(14) binary NOT NULL default '',
—	—	@@ -708,14 +709,13 @@
709	710	oi_bits int(3) NOT NULL default 0,
710	711	oi_description tinyblob NOT NULL,
711	712	oi_user int(5) unsigned NOT NULL default '0',
712		~~- oi_user_text varchar(255) binary NOT NULL default '',~~
	713	+ oi_user_text varchar(255) binary NOT NULL,
713	714	oi_timestamp char(14) binary NOT NULL default '',
714	715
715	716	INDEX oi_name (oi_name(10))
716	717
717	718	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
718	719
719		-
720	720	--
721	721	-- Record of deleted file data
722	722	--
—	—	@@ -757,7 +757,7 @@
758	758	fa_minor_mime varchar(32) default "unknown",
759	759	fa_description tinyblob,
760	760	fa_user int(5) unsigned default '0',
761		~~- fa_user_text varchar(255) binary default '',~~
	761	+ fa_user_text varchar(255) binary,
762	762	fa_timestamp char(14) binary default '',
763	763
764	764	PRIMARY KEY (fa_id),
—	—	@@ -780,7 +780,7 @@
781	781
782	782	-- As in revision
783	783	rc_user int(10) unsigned NOT NULL default '0',
784		~~- rc_user_text varchar(255) binary NOT NULL default '',~~
	784	+ rc_user_text varchar(255) binary NOT NULL,
785	785
786	786	-- When pages are renamed, their RC entries do _not_ change.
787	787	rc_namespace int NOT NULL default '0',
—	—	@@ -825,16 +825,17 @@
826	826
827	827	-- Text length in characters before
828	828	-- and after the edit
829		~~- rc_old_len int(10) default '0',~~
830		~~- rc_new_len int(10) default '0',~~
831		-
	829	+ rc_old_len int(10),
	830	+ rc_new_len int(10),
	831	+
832	832	PRIMARY KEY rc_id (rc_id),
833	833	INDEX rc_timestamp (rc_timestamp),
834	834	INDEX rc_namespace_title (rc_namespace, rc_title),
835	835	INDEX rc_cur_id (rc_cur_id),
836	836	INDEX new_name_timestamp (rc_new,rc_namespace,rc_timestamp),
837	837	INDEX rc_ip (rc_ip),
838		~~- INDEX rc_ns_usertext ( rc_namespace, rc_user_text )~~
	838	+ INDEX rc_ns_usertext (rc_namespace, rc_user_text),
	839	+ INDEX rc_user_text (rc_user_text, rc_timestamp)
839	840
840	841	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
841	842
—	—	@@ -853,7 +854,7 @@
854	855	wl_notificationtimestamp varchar(14) binary,
855	856
856	857	UNIQUE KEY (wl_user, wl_namespace, wl_title),
857		~~- KEY namespace_title (wl_namespace,wl_title)~~
	858	+ KEY namespace_title (wl_namespace, wl_title)
858	859
859	860	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
860	861
—	—	@@ -996,7 +997,8 @@
997	998
998	999	KEY type_time (log_type, log_timestamp),
999	1000	KEY user_time (log_user, log_timestamp),
1000		~~- KEY page_time (log_namespace, log_title, log_timestamp)~~
	1001	+ KEY page_time (log_namespace, log_title, log_timestamp),
	1002	+ KEY times (log_timestamp)
1001	1003
1002	1004	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
1003	1005
—	—	@@ -1012,6 +1014,7 @@
1013	1015	INDEX (tb_page)
1014	1016	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
1015	1017
	1018	+
1016	1019	-- Jobs performed by parallel apache threads or a command-line daemon
1017	1020	CREATE TABLE /$wgDBprefix/job (
1018	1021	job_id int(9) unsigned NOT NULL auto_increment,
—	—	@@ -1032,6 +1035,7 @@
1033	1036	KEY (job_cmd, job_namespace, job_title)
1034	1037	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
1035	1038
	1039	+
1036	1040	-- Details of updates to cached special pages
1037	1041	CREATE TABLE /$wgDBprefix/querycache_info (
1038	1042
—	—	@@ -1063,7 +1067,6 @@
1064	1068	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
1065	1069
1066	1070	-- Used for caching expensive grouped queries that need two links (for example double-redirects)
1067		-
1068	1071	CREATE TABLE /$wgDBprefix/querycachetwo (
1069	1072	-- A key name, generally the base name of of the special page.
1070	1073	qcc_type char(32) NOT NULL,
—	—	@@ -1084,3 +1087,26 @@
1085	1088	KEY qcc_titletwo (qcc_type,qcc_namespacetwo,qcc_titletwo)
1086	1089
1087	1090	) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
	1091	+
	1092	+--- Used for storing page restrictions (i.e. protection levels)
	1093	+CREATE TABLE /$wgDBprefix/page_restrictions (
	1094	+ -- Page to apply restrictions to (Foreign Key to page).
	1095	+ pr_page int(8) NOT NULL,
	1096	+ -- The protection type (edit, move, etc)
	1097	+ pr_type varchar(255) NOT NULL,
	1098	+ -- The protection level (Sysop, autoconfirmed, etc)
	1099	+ pr_level varchar(255) NOT NULL,
	1100	+ -- Whether or not to cascade the protection down to pages transcluded.
	1101	+ pr_cascade tinyint(4) NOT NULL,
	1102	+ -- Field for future support of per-user restriction.
	1103	+ pr_user int(8) NULL,
	1104	+ -- Field for time-limited protection.
	1105	+ pr_expiry char(14) binary NULL,
	1106	+
	1107	+ PRIMARY KEY (pr_page,pr_type),
	1108	+
	1109	+ KEY pr_page (pr_page),
	1110	+ KEY pr_typelevel (pr_type,pr_level),
	1111	+ KEY pr_level (pr_level),
	1112	+ KEY pr_cascade (pr_cascade)
	1113	+) ENGINE=InnoDB, DEFAULT CHARSET=utf8;
Index: trunk/phase3/maintenance/mysql5/tables-binary.sql
—	—	@@ -99,7 +99,7 @@
100	100	-- Timestamp of the last time when a new password was
101	101	-- sent, for throttling purposes
102	102	user_newpass_time char(14) binary,
103		-
	103	+
104	104	-- Note: email should be restricted, not public info.
105	105	-- Same with passwords.
106	106	user_email tinytext NOT NULL,
—	—	@@ -190,6 +190,7 @@
191	191	user_ip varchar(40) NOT NULL default '',
192	192	INDEX user_id (user_id),
193	193	INDEX user_ip (user_ip)
	194	+
194	195	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
195	196
196	197
—	—	@@ -428,7 +429,6 @@
429	430
430	431	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
431	432
432		-
433	433	--
434	434	-- Track links to images used inline
435	435	-- We don't distinguish live from broken links here, so
—	—	@@ -465,7 +465,8 @@
466	466	-- The title of the linking page, or an optional override
467	467	-- to determine sort order. Sorting is by binary order, which
468	468	-- isn't always ideal, but collations seem to be an exciting
469		~~- -- and dangerous new world in MySQL...~~
	469	+ -- and dangerous new world in MySQL... The sortkey is updated
	470	+ -- if no override exists and cl_from is renamed.
470	471	--
471	472	-- For MySQL 4.1+ with charset set to utf8, the sort key index
472	473	-- needs cut to be smaller than 1024 bytes (at 3 bytes per char).
—	—	@@ -608,8 +609,8 @@
609	610	-- Indicates that the IP address was banned because a banned
610	611	-- user accessed a page through it. If this is 1, ipb_address
611	612	-- will be hidden, and the block identified by block ID number.
612		~~- ipb_auto bool NOT NULL default '0',~~
613		-
	613	+ ipb_auto bool NOT NULL default 0,
	614	+
614	615	-- If set to 1, block applies only to logged-out users
615	616	ipb_anon_only bool NOT NULL default 0,
616	617
—	—	@@ -618,14 +619,14 @@
619	620
620	621	-- Block triggers autoblocks
621	622	ipb_enable_autoblock bool NOT NULL default '1',
622		-
	623	+
623	624	-- Time at which the block will expire.
624	625	ipb_expiry char(14) binary NOT NULL default '',
625	626
626	627	-- Start and end of an address range, in hexadecimal
627	628	-- Size chosen to allow IPv6
628		~~- ipb_range_start varchar(32) NOT NULL default '',~~
629		~~- ipb_range_end varchar(32) NOT NULL default '',~~
	629	+ ipb_range_start tinyblob NOT NULL,
	630	+ ipb_range_end tinyblob NOT NULL,
630	631
631	632	PRIMARY KEY ipb_id (ipb_id),
632	633
—	—	@@ -682,7 +683,7 @@
683	684
684	685	-- user_id and user_name of uploader.
685	686	img_user int(5) unsigned NOT NULL default '0',
686		~~- img_user_text varchar(255) binary NOT NULL default '',~~
	687	+ img_user_text varchar(255) binary NOT NULL,
687	688
688	689	-- Time of the upload.
689	690	img_timestamp char(14) binary NOT NULL default '',
—	—	@@ -717,14 +718,13 @@
718	719	oi_bits int(3) NOT NULL default 0,
719	720	oi_description tinyblob NOT NULL,
720	721	oi_user int(5) unsigned NOT NULL default '0',
721		~~- oi_user_text varchar(255) binary NOT NULL default '',~~
	722	+ oi_user_text varchar(255) binary NOT NULL,
722	723	oi_timestamp char(14) binary NOT NULL default '',
723	724
724	725	INDEX oi_name (oi_name(10))
725	726
726	727	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
727	728
728		-
729	729	--
730	730	-- Record of deleted file data
731	731	--
—	—	@@ -766,7 +766,7 @@
767	767	fa_minor_mime varchar(32) default "unknown",
768	768	fa_description tinyblob,
769	769	fa_user int(5) unsigned default '0',
770		~~- fa_user_text varchar(255) binary default '',~~
	770	+ fa_user_text varchar(255) binary,
771	771	fa_timestamp char(14) binary default '',
772	772
773	773	PRIMARY KEY (fa_id),
—	—	@@ -789,7 +789,7 @@
790	790
791	791	-- As in revision
792	792	rc_user int(10) unsigned NOT NULL default '0',
793		~~- rc_user_text varchar(255) binary NOT NULL default '',~~
	793	+ rc_user_text varchar(255) binary NOT NULL,
794	794
795	795	-- When pages are renamed, their RC entries do _not_ change.
796	796	rc_namespace int NOT NULL default '0',
—	—	@@ -834,16 +834,17 @@
835	835
836	836	-- Text length in characters before
837	837	-- and after the edit
838		~~- rc_old_len int(10) default '0',~~
839		~~- rc_new_len int(10) default '0',~~
840		-
	838	+ rc_old_len int(10),
	839	+ rc_new_len int(10),
	840	+
841	841	PRIMARY KEY rc_id (rc_id),
842	842	INDEX rc_timestamp (rc_timestamp),
843	843	INDEX rc_namespace_title (rc_namespace, rc_title),
844	844	INDEX rc_cur_id (rc_cur_id),
845	845	INDEX new_name_timestamp (rc_new,rc_namespace,rc_timestamp),
846	846	INDEX rc_ip (rc_ip),
847		~~- INDEX rc_ns_usertext ( rc_namespace, rc_user_text )~~
	847	+ INDEX rc_ns_usertext (rc_namespace, rc_user_text),
	848	+ INDEX rc_user_text (rc_user_text, rc_timestamp)
848	849
849	850	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
850	851
—	—	@@ -862,7 +863,7 @@
863	864	wl_notificationtimestamp varchar(14) binary,
864	865
865	866	UNIQUE KEY (wl_user, wl_namespace, wl_title),
866		~~- KEY namespace_title (wl_namespace,wl_title)~~
	867	+ KEY namespace_title (wl_namespace, wl_title)
867	868
868	869	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
869	870
—	—	@@ -1005,7 +1006,8 @@
1006	1007
1007	1008	KEY type_time (log_type, log_timestamp),
1008	1009	KEY user_time (log_user, log_timestamp),
1009		~~- KEY page_time (log_namespace, log_title, log_timestamp)~~
	1010	+ KEY page_time (log_namespace, log_title, log_timestamp),
	1011	+ KEY times (log_timestamp)
1010	1012
1011	1013	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
1012	1014
—	—	@@ -1021,6 +1023,7 @@
1022	1024	INDEX (tb_page)
1023	1025	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
1024	1026
	1027	+
1025	1028	-- Jobs performed by parallel apache threads or a command-line daemon
1026	1029	CREATE TABLE /$wgDBprefix/job (
1027	1030	job_id int(9) unsigned NOT NULL auto_increment,
—	—	@@ -1041,6 +1044,7 @@
1042	1045	KEY (job_cmd, job_namespace, job_title)
1043	1046	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
1044	1047
	1048	+
1045	1049	-- Details of updates to cached special pages
1046	1050	CREATE TABLE /$wgDBprefix/querycache_info (
1047	1051
—	—	@@ -1072,7 +1076,6 @@
1073	1077	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
1074	1078
1075	1079	-- Used for caching expensive grouped queries that need two links (for example double-redirects)
1076		-
1077	1080	CREATE TABLE /$wgDBprefix/querycachetwo (
1078	1081	-- A key name, generally the base name of of the special page.
1079	1082	qcc_type char(32) NOT NULL,
—	—	@@ -1093,3 +1096,26 @@
1094	1097	KEY qcc_titletwo (qcc_type,qcc_namespacetwo,qcc_titletwo)
1095	1098
1096	1099	) ENGINE=InnoDB, DEFAULT CHARSET=binary;
	1100	+
	1101	+--- Used for storing page restrictions (i.e. protection levels)
	1102	+CREATE TABLE /$wgDBprefix/page_restrictions (
	1103	+ -- Page to apply restrictions to (Foreign Key to page).
	1104	+ pr_page int(8) NOT NULL,
	1105	+ -- The protection type (edit, move, etc)
	1106	+ pr_type varchar(255) NOT NULL,
	1107	+ -- The protection level (Sysop, autoconfirmed, etc)
	1108	+ pr_level varchar(255) NOT NULL,
	1109	+ -- Whether or not to cascade the protection down to pages transcluded.
	1110	+ pr_cascade tinyint(4) NOT NULL,
	1111	+ -- Field for future support of per-user restriction.
	1112	+ pr_user int(8) NULL,
	1113	+ -- Field for time-limited protection.
	1114	+ pr_expiry char(14) binary NULL,
	1115	+
	1116	+ PRIMARY KEY (pr_page,pr_type),
	1117	+
	1118	+ KEY pr_page (pr_page),
	1119	+ KEY pr_typelevel (pr_type,pr_level),
	1120	+ KEY pr_level (pr_level),
	1121	+ KEY pr_cascade (pr_cascade)
	1122	+) ENGINE=InnoDB, DEFAULT CHARSET=binary;
Index: trunk/phase3/RELEASE-NOTES
—	—	@@ -144,6 +144,7 @@
145	145	* Added an option to make Linker::userToolLinks() show the contribs link
146	146	red when the user has no edits. Linker::userToolLinksRedContribs() is an
147	147	alias to that which should be used to make it more self documentating.
	148	+* (bug 8749) Bring MySQL 5 table defs back into sync
148	149
149	150
150	151	== Languages updated ==