r106507 MediaWiki - Code Review archive

Repository:	MediaWiki
Revision:	< r106506‎ \| r106507 \| r106508 >
Date:	15:59, 17 December 2011
Author:	leonsp
Status:	deferred (Comments)
Tags:
Comment:	Fixes to address MaxSem's comment on r106480 regarding DB2 tables.sql syntax, indentation, and capitalization: * Made all table and column names lowercase for consistency * Removed " from around some "identifiers". Not necessary for DB2. * Applied consistent indentation to all table declarations * Applied consistent indentation to all index declarations * Moved all PRIMARY KEY clauses to their own line for consistency and max line length * Moved all ON clauses to their own line for consistency and max line length * Fixed vertical alignment for column names and data types, remove all other vertical alignment * Removed commented out references to SEQUENCE values, as they are used in neither DB2 nor MySQL schemas. * Consistent spacing around parens () and commas , * Capitalized all data types. * Note: tables.sql is approaching DB2's 32k limit. It will need to be split into two files soon.
Modified paths:	/trunk/phase3/maintenance/ibm_db2/tables.sql (modified) (history)

Diff [purge]

Index: trunk/phase3/maintenance/ibm_db2/tables.sql
—	—	@@ -1,16 +1,20 @@
	2	+-- IBM DB2
2	3
3	4	-- SQL to create the initial tables for the MediaWiki database.
4	5	-- This is read and executed by the install script; you should
5	6	-- not have to run it by itself unless doing a manual install.
6	7
	8	+-- Notes:
	9	+-- * DB2 will convert all table and column names to all caps internally.
	10	+-- * DB2 has a 32k limit on SQL filesize, so it may be necessary
	11	+-- to split this into two files soon.
7	12
	13	+
8	14	CREATE TABLE user (
9	15	-- Needs to start with 0
10		~~- user_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),~~
11		~~- user_name VARCHAR(255) NOT NULL UNIQUE,~~
	16	+ user_id BIGINT
	17	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
	18	+ user_name VARCHAR(255) NOT NULL UNIQUE,
12	19	user_real_name VARCHAR(255),
13	20	user_password VARCHAR(1024),
14	21	user_newpassword VARCHAR(1024),
—	—	@@ -26,799 +30,909 @@
27	31	user_registration TIMESTAMP(3),
28	32	user_editcount INTEGER
29	33	);
30		~~-CREATE INDEX user_email_token_idx ON user (user_email_token);~~
	34	+CREATE INDEX user_email_token_idx
	35	+ ON user (user_email_token);
31	36	CREATE UNIQUE INDEX user_include_idx
32		~~- ON user(user_id)~~
33		~~- INCLUDE (user_name, user_real_name, user_password, user_newpassword, user_newpass_time, user_token,~~
34		~~- user_email, user_email_token, user_email_token_expires, user_email_authenticated,~~
35		~~- user_touched, user_registration, user_editcount);~~
36		-
37		~~-CREATE UNIQUE INDEX user_email ON user (user_email);~~
	37	+ ON user (user_id)
	38	+ INCLUDE (user_name, user_real_name, user_password, user_newpassword,
	39	+ user_newpass_time, user_token,
	40	+ user_email, user_email_token, user_email_token_expires,
	41	+ user_email_authenticated,
	42	+ user_touched, user_registration, user_editcount);
	43	+CREATE UNIQUE INDEX user_email
	44	+ ON user (user_email);
38	45
	46	+
	47	+
39	48	-- Create a dummy user to satisfy fk contraints especially with revisions
40	49	INSERT INTO user(
41		~~-user_name, user_real_name, user_password, user_newpassword, user_newpass_time,~~
42		~~-user_email, user_email_authenticated, user_token, user_registration, user_editcount)~~
	50	+ user_name, user_real_name, user_password, user_newpassword, user_newpass_time,
	51	+ user_email, user_email_authenticated, user_token, user_registration, user_editcount
	52	+)
43	53	VALUES (
44		~~-'Anonymous','', NULL, NULL, CURRENT_TIMESTAMP,~~
45		~~-NULL, NULL, NULL, CURRENT_timestamp, 0);~~
	54	+ 'Anonymous', '', NULL, NULL, CURRENT_TIMESTAMP,
	55	+ NULL, NULL, NULL, CURRENT_TIMESTAMP, 0
	56	+);
46	57
47	58
	59	+
48	60	CREATE TABLE user_groups (
49	61	ug_user BIGINT NOT NULL DEFAULT 0,
50	62	-- REFERENCES user(user_id) ON DELETE CASCADE,
51		~~- ug_group VARCHAR(255) NOT NULL~~
	63	+ ug_group VARCHAR(255) NOT NULL
52	64	);
53		~~-CREATE INDEX user_groups_unique ON user_groups (ug_user, ug_group);~~
	65	+CREATE INDEX user_groups_unique
	66	+ ON user_groups (ug_user, ug_group);
54	67
55	68
	69	+
56	70	CREATE TABLE user_newtalk (
57	71	-- registered users key
58		~~- user_id BIGINT NOT NULL DEFAULT 0,~~
	72	+ user_id BIGINT NOT NULL DEFAULT 0,
59	73	-- REFERENCES user(user_id) ON DELETE CASCADE,
60	74	-- anonymous users key
61	75	user_ip VARCHAR(40),
62	76	user_last_timestamp TIMESTAMP(3)
63	77	);
64		~~-CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);~~
65		~~-CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);~~
	78	+CREATE INDEX user_newtalk_id_idx
	79	+ ON user_newtalk (user_id);
	80	+CREATE INDEX user_newtalk_ip_idx
	81	+ ON user_newtalk (user_ip);
66	82	CREATE UNIQUE INDEX user_newtalk_include_idx
67		~~- ON user_newtalk(user_id, user_ip)~~
68		~~- INCLUDE (user_last_timestamp);~~
	83	+ ON user_newtalk (user_id, user_ip)
	84	+ INCLUDE (user_last_timestamp);
69	85
70	86
	87	+
71	88	CREATE TABLE page (
72		~~- page_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
73		~~- page_namespace SMALLINT NOT NULL,~~
74		~~- page_title VARCHAR(255) NOT NULL,~~
	89	+ page_id BIGINT
	90	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	91	+ page_namespace SMALLINT NOT NULL,
	92	+ page_title VARCHAR(255) NOT NULL,
75	93	page_restrictions VARCHAR(1024),
76		~~- page_counter BIGINT NOT NULL DEFAULT 0,~~
77		~~- page_is_redirect SMALLINT NOT NULL DEFAULT 0,~~
78		~~- page_is_new SMALLINT NOT NULL DEFAULT 0,~~
	94	+ page_counter BIGINT NOT NULL DEFAULT 0,
	95	+ page_is_redirect SMALLINT NOT NULL DEFAULT 0,
	96	+ page_is_new SMALLINT NOT NULL DEFAULT 0,
79	97	page_random NUMERIC(15,14) NOT NULL,
80	98	page_touched TIMESTAMP(3),
81		~~- page_latest BIGINT NOT NULL, -- FK?~~
82		~~- page_len BIGINT NOT NULL~~
	99	+ page_latest BIGINT NOT NULL, -- FK?
	100	+ page_len BIGINT NOT NULL
83	101	);
84		~~-CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);~~
85		~~-CREATE INDEX page_random_idx ON page (page_random);~~
86		~~-CREATE INDEX page_len_idx ON page (page_len);~~
	102	+CREATE UNIQUE INDEX page_unique_name
	103	+ ON page (page_namespace, page_title);
	104	+CREATE INDEX page_random_idx
	105	+ ON page (page_random);
	106	+CREATE INDEX page_len_idx
	107	+ ON page (page_len);
87	108	CREATE UNIQUE INDEX page_id_include
88		~~- ON page (page_id)~~
89		~~- INCLUDE (page_namespace, page_title, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);~~
	109	+ ON page (page_id)
	110	+ INCLUDE (page_namespace, page_title, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
90	111	CREATE UNIQUE INDEX page_name_include
91		~~- ON page (page_namespace, page_title)~~
92		~~- INCLUDE (page_id, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);~~
	112	+ ON page (page_namespace, page_title)
	113	+ INCLUDE (page_id, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
93	114
94	115
	116	+
95	117	CREATE TABLE revision (
96		~~- rev_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
97		~~- rev_page BIGINT NOT NULL DEFAULT 0,~~
	118	+ rev_id BIGINT
	119	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	120	+ rev_page BIGINT NOT NULL DEFAULT 0,
98	121	-- REFERENCES page (page_id) ON DELETE CASCADE,
99		~~- rev_text_id BIGINT, -- FK~~
100		~~- rev_comment VARCHAR(1024),~~
101		~~- rev_user BIGINT NOT NULL DEFAULT 0,~~
	122	+ rev_text_id BIGINT, -- FK
	123	+ rev_comment VARCHAR(1024),
	124	+ rev_user BIGINT NOT NULL DEFAULT 0,
102	125	-- REFERENCES user(user_id) ON DELETE RESTRICT,
103		~~- rev_user_text VARCHAR(255) NOT NULL,~~
104		~~- rev_timestamp TIMESTAMP(3) NOT NULL,~~
105		~~- rev_minor_edit SMALLINT NOT NULL DEFAULT 0,~~
106		~~- rev_deleted SMALLINT NOT NULL DEFAULT 0,~~
107		~~- rev_len BIGINT,~~
108		~~- rev_parent_id BIGINT DEFAULT NULL,~~
109		~~- rev_sha1 VARCHAR(255) NOT NULL DEFAULT ''~~
	126	+ rev_user_text VARCHAR(255) NOT NULL,
	127	+ rev_timestamp TIMESTAMP(3) NOT NULL,
	128	+ rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
	129	+ rev_deleted SMALLINT NOT NULL DEFAULT 0,
	130	+ rev_len BIGINT,
	131	+ rev_parent_id BIGINT DEFAULT NULL,
	132	+ rev_sha1 VARCHAR(255) NOT NULL DEFAULT ''
110	133	);
111		~~-CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);~~
112		~~-CREATE INDEX rev_text_id_idx ON revision (rev_text_id);~~
113		~~-CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);~~
114		~~-CREATE INDEX rev_user_idx ON revision (rev_user);~~
115		~~-CREATE INDEX rev_user_text_idx ON revision (rev_user_text);~~
	134	+CREATE UNIQUE INDEX revision_unique
	135	+ ON revision (rev_page, rev_id);
	136	+CREATE INDEX rev_text_id_idx
	137	+ ON revision (rev_text_id);
	138	+CREATE INDEX rev_timestamp_idx
	139	+ ON revision (rev_timestamp);
	140	+CREATE INDEX rev_user_idx
	141	+ ON revision (rev_user);
	142	+CREATE INDEX rev_user_text_idx
	143	+ ON revision (rev_user_text);
116	144
117	145
118	146
119	147	CREATE TABLE text ( -- replaces reserved word 'text'
120		~~- old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
	148	+ old_id INTEGER
	149	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
121	150	old_text CLOB(16M) INLINE LENGTH 4096,
122	151	old_flags VARCHAR(1024)
123	152	);
124	153
125	154
	155	+
126	156	CREATE TABLE page_restrictions (
127		~~- --pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'),~~
128		~~- --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
129		~~- pr_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
130		~~- pr_page INTEGER NOT NULL DEFAULT 0,~~
	157	+ pr_id BIGINT
	158	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	159	+ pr_page INTEGER NOT NULL DEFAULT 0,
131	160	--(used to be nullable)
132	161	-- REFERENCES page (page_id) ON DELETE CASCADE,
133		~~- pr_type VARCHAR(60) NOT NULL,~~
134		~~- pr_level VARCHAR(60) NOT NULL,~~
135		~~- pr_cascade SMALLINT NOT NULL,~~
	162	+ pr_type VARCHAR(60) NOT NULL,
	163	+ pr_level VARCHAR(60) NOT NULL,
	164	+ pr_cascade SMALLINT NOT NULL,
136	165	pr_user INTEGER,
137	166	pr_expiry TIMESTAMP(3)
138	167	--PRIMARY KEY (pr_page, pr_type)
139	168	);
140		~~-CREATE UNIQUE INDEX pr_pagetype ON page_restrictions (pr_page,pr_type);~~
141		~~-CREATE INDEX pr_typelevel ON page_restrictions (pr_type,pr_level);~~
142		~~-CREATE INDEX pr_level ON page_restrictions (pr_level);~~
143		~~-CREATE INDEX pr_cascade ON page_restrictions (pr_cascade);~~
	169	+--ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page, pr_type);
	170	+CREATE UNIQUE INDEX pr_pagetype
	171	+ ON page_restrictions (pr_page, pr_type);
	172	+CREATE INDEX pr_typelevel
	173	+ ON page_restrictions (pr_type, pr_level);
	174	+CREATE INDEX pr_level
	175	+ ON page_restrictions (pr_level);
	176	+CREATE INDEX pr_cascade
	177	+ ON page_restrictions (pr_cascade);
144	178
	179	+
	180	+
145	181	CREATE TABLE page_props (
146		~~- pp_page INTEGER NOT NULL DEFAULT 0,~~
	182	+ pp_page INTEGER NOT NULL DEFAULT 0,
147	183	-- REFERENCES page (page_id) ON DELETE CASCADE,
148		~~- pp_propname VARCHAR(255) NOT NULL,~~
149		~~- pp_value CLOB(64K) INLINE LENGTH 4096 NOT NULL,~~
150		~~- PRIMARY KEY (pp_page,pp_propname)~~
	184	+ pp_propname VARCHAR(255) NOT NULL,
	185	+ pp_value CLOB(64K) INLINE LENGTH 4096 NOT NULL,
	186	+ PRIMARY KEY (pp_page, pp_propname)
151	187	);
152		~~-CREATE INDEX page_props_propname ON page_props (pp_propname);~~
	188	+CREATE INDEX page_props_propname
	189	+ ON page_props (pp_propname);
153	190
154	191
155	192
156	193	CREATE TABLE archive (
157		~~- ar_namespace SMALLINT NOT NULL,~~
158		~~- ar_title VARCHAR(255) NOT NULL,~~
	194	+ ar_namespace SMALLINT NOT NULL,
	195	+ ar_title VARCHAR(255) NOT NULL,
159	196	ar_text CLOB(16M) INLINE LENGTH 4096,
160	197	ar_comment VARCHAR(1024),
161	198	ar_user BIGINT NOT NULL,
162	199	-- no foreign keys in MySQL
163	200	-- REFERENCES user(user_id) ON DELETE SET NULL,
164		~~- ar_user_text VARCHAR(255) NOT NULL,~~
165		~~- ar_timestamp TIMESTAMP(3) NOT NULL,~~
166		~~- ar_minor_edit SMALLINT NOT NULL DEFAULT 0,~~
	201	+ ar_user_text VARCHAR(255) NOT NULL,
	202	+ ar_timestamp TIMESTAMP(3) NOT NULL,
	203	+ ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
167	204	ar_flags VARCHAR(1024),
168	205	ar_rev_id INTEGER,
169	206	ar_text_id INTEGER,
170		~~- ar_deleted SMALLINT NOT NULL DEFAULT 0,~~
	207	+ ar_deleted SMALLINT NOT NULL DEFAULT 0,
171	208	ar_len INTEGER,
172	209	ar_page_id INTEGER,
173	210	ar_parent_id INTEGER,
174		~~- ar_sha1 VARCHAR(255) NOT NULL DEFAULT ''~~
	211	+ ar_sha1 VARCHAR(255) NOT NULL DEFAULT ''
175	212	);
176		~~-CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);~~
177		~~-CREATE INDEX archive_user_text ON archive (ar_user_text);~~
	213	+CREATE INDEX archive_name_title_timestamp
	214	+ ON archive (ar_namespace, ar_title, ar_timestamp);
	215	+CREATE INDEX archive_user_text
	216	+ ON archive (ar_user_text);
178	217
179	218
180	219
181	220	CREATE TABLE redirect (
182		~~- rd_from BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
	221	+ rd_from BIGINT NOT NULL
	222	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
183	223	--REFERENCES page(page_id) ON DELETE CASCADE,
184		~~- rd_namespace SMALLINT NOT NULL DEFAULT 0,~~
185		~~- rd_title VARCHAR(255) NOT NULL DEFAULT '',~~
186		~~- rd_interwiki varchar(32),~~
	224	+ rd_namespace SMALLINT NOT NULL DEFAULT 0,
	225	+ rd_title VARCHAR(255) NOT NULL DEFAULT '',
	226	+ rd_interwiki VARCHAR(32),
187	227	rd_fragment VARCHAR(255)
188	228	);
189		~~-CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);~~
	229	+CREATE INDEX redirect_ns_title
	230	+ ON redirect (rd_namespace, rd_title, rd_from);
190	231
191	232
192	233	CREATE TABLE pagelinks (
193		~~- pl_from BIGINT NOT NULL DEFAULT 0,~~
	234	+ pl_from BIGINT NOT NULL DEFAULT 0,
194	235	-- REFERENCES page(page_id) ON DELETE CASCADE,
195		~~- pl_namespace SMALLINT NOT NULL,~~
196		~~- pl_title VARCHAR(255) NOT NULL~~
	236	+ pl_namespace SMALLINT NOT NULL,
	237	+ pl_title VARCHAR(255) NOT NULL
197	238	);
198		~~-CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);~~
	239	+CREATE UNIQUE INDEX pagelink_unique
	240	+ ON pagelinks (pl_from, pl_namespace, pl_title);
199	241
	242	+
	243	+
200	244	CREATE TABLE templatelinks (
201		~~- tl_from BIGINT NOT NULL DEFAULT 0,~~
	245	+ tl_from BIGINT NOT NULL DEFAULT 0,
202	246	-- REFERENCES page(page_id) ON DELETE CASCADE,
203	247	tl_namespace SMALLINT NOT NULL,
204		~~- tl_title VARCHAR(255) NOT NULL~~
	248	+ tl_title VARCHAR(255) NOT NULL
205	249	);
206		~~-CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);~~
207		~~-CREATE UNIQUE INDEX tl_from_idx ON templatelinks (tl_from,tl_namespace,tl_title);~~
	250	+CREATE UNIQUE INDEX templatelinks_unique
	251	+ ON templatelinks (tl_namespace, tl_title, tl_from);
	252	+CREATE UNIQUE INDEX tl_from_idx
	253	+ ON templatelinks (tl_from, tl_namespace, tl_title);
208	254
	255	+
	256	+
209	257	CREATE TABLE imagelinks (
210		~~- il_from BIGINT NOT NULL DEFAULT 0,~~
	258	+ il_from BIGINT NOT NULL DEFAULT 0,
211	259	-- REFERENCES page(page_id) ON DELETE CASCADE,
212		~~- il_to VARCHAR(255) NOT NULL~~
	260	+ il_to VARCHAR(255) NOT NULL
213	261	);
214		~~-CREATE UNIQUE INDEX il_from_idx ON imagelinks (il_to,il_from);~~
215		~~-CREATE UNIQUE INDEX il_to_idx ON imagelinks (il_from,il_to);~~
	262	+CREATE UNIQUE INDEX il_from_idx
	263	+ ON imagelinks (il_to, il_from);
	264	+CREATE UNIQUE INDEX il_to_idx
	265	+ ON imagelinks (il_from, il_to);
216	266
	267	+
	268	+
217	269	CREATE TABLE categorylinks (
218		~~- cl_from BIGINT NOT NULL DEFAULT 0,~~
	270	+ cl_from BIGINT NOT NULL DEFAULT 0,
219	271	-- REFERENCES page(page_id) ON DELETE CASCADE,
220		~~- cl_to VARCHAR(255) NOT NULL,~~
	272	+ cl_to VARCHAR(255) NOT NULL,
221	273	-- cl_sortkey has to be at least 86 wide
222	274	-- in order to be compatible with the old MySQL schema from MW 1.10
223	275	--cl_sortkey VARCHAR(86),
224		~~- cl_sortkey VARCHAR(230) FOR BIT DATA NOT NULL ,~~
225		~~- cl_sortkey_prefix VARCHAR(255) FOR BIT DATA NOT NULL ,~~
226		~~- cl_timestamp TIMESTAMP(3) NOT NULL,~~
227		~~- cl_collation VARCHAR(32) FOR BIT DATA NOT NULL ,~~
228		~~- cl_type VARCHAR(6) FOR BIT DATA NOT NULL~~
	276	+ cl_sortkey VARCHAR(230) FOR BIT DATA NOT NULL,
	277	+ cl_sortkey_prefix VARCHAR(255) FOR BIT DATA NOT NULL,
	278	+ cl_timestamp TIMESTAMP(3) NOT NULL,
	279	+ cl_collation VARCHAR(32) FOR BIT DATA NOT NULL,
	280	+ cl_type VARCHAR(6) FOR BIT DATA NOT NULL
229	281	);
230		~~-CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);~~
231		~~-CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);~~
	282	+CREATE UNIQUE INDEX cl_from
	283	+ ON categorylinks (cl_from, cl_to);
	284	+CREATE INDEX cl_sortkey
	285	+ ON categorylinks (cl_to, cl_sortkey, cl_from);
232	286
233	287
234	288
235	289	CREATE TABLE externallinks (
236		~~- el_from BIGINT NOT NULL DEFAULT 0,~~
	290	+ el_from BIGINT NOT NULL DEFAULT 0,
237	291	-- REFERENCES page(page_id) ON DELETE CASCADE,
238		~~- el_to VARCHAR(1024) NOT NULL,~~
239		~~- el_index VARCHAR(1024) NOT NULL~~
	292	+ el_to VARCHAR(1024) NOT NULL,
	293	+ el_index VARCHAR(1024) NOT NULL
240	294	);
241		~~-CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);~~
242		~~-CREATE INDEX externallinks_index ON externallinks (el_index);~~
	295	+CREATE INDEX externallinks_from_to
	296	+ ON externallinks (el_from, el_to);
	297	+CREATE INDEX externallinks_index
	298	+ ON externallinks (el_index);
243	299
244	300
	301	+
245	302	--
246	303	-- Track external user accounts, if ExternalAuth is used
247	304	--
248	305	CREATE TABLE external_user (
249	306	-- Foreign key to user_id
250		~~- eu_local_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
	307	+ eu_local_id BIGINT NOT NULL
	308	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
251	309
252	310	-- Some opaque identifier provided by the external database
253		~~- eu_external_id VARCHAR(255) NOT NULL~~
	311	+ eu_external_id VARCHAR(255) NOT NULL
254	312	);
255	313	CREATE UNIQUE INDEX eu_external_id_idx
256		~~- ON external_user (eu_external_id)~~
257		~~- INCLUDE (eu_local_id);~~
	314	+ ON external_user (eu_external_id)
	315	+ INCLUDE (eu_local_id);
258	316	CREATE UNIQUE INDEX eu_local_id_idx
259		~~- ON external_user (eu_local_id)~~
260		~~- INCLUDE (eu_external_id);~~
	317	+ ON external_user (eu_local_id)
	318	+ INCLUDE (eu_external_id);
261	319
262	320
263	321
264	322	CREATE TABLE langlinks (
265		~~- ll_from BIGINT NOT NULL DEFAULT 0,~~
	323	+ ll_from BIGINT NOT NULL DEFAULT 0,
266	324	-- REFERENCES page (page_id) ON DELETE CASCADE,
267	325	ll_lang VARCHAR(20),
268	326	ll_title VARCHAR(255)
269	327	);
270		~~-CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);~~
271		~~-CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);~~
	328	+CREATE UNIQUE INDEX langlinks_unique
	329	+ ON langlinks (ll_from, ll_lang);
	330	+CREATE INDEX langlinks_lang_title
	331	+ ON langlinks (ll_lang, ll_title);
272	332
273	333
	334	+
274	335	CREATE TABLE site_stats (
275		~~- ss_row_id BIGINT NOT NULL UNIQUE,~~
276		~~- ss_total_views BIGINT DEFAULT 0,~~
277		~~- ss_total_edits BIGINT DEFAULT 0,~~
278		~~- ss_good_articles BIGINT DEFAULT 0,~~
279		~~- ss_total_pages INTEGER DEFAULT -1,~~
280		~~- ss_users INTEGER DEFAULT -1,~~
281		~~- ss_active_users INTEGER DEFAULT -1,~~
282		~~- ss_admins INTEGER DEFAULT -1,~~
283		~~- ss_images INTEGER DEFAULT 0~~
	336	+ ss_row_id BIGINT NOT NULL UNIQUE,
	337	+ ss_total_views BIGINT DEFAULT 0,
	338	+ ss_total_edits BIGINT DEFAULT 0,
	339	+ ss_good_articles BIGINT DEFAULT 0,
	340	+ ss_total_pages INTEGER DEFAULT -1,
	341	+ ss_users INTEGER DEFAULT -1,
	342	+ ss_active_users INTEGER DEFAULT -1,
	343	+ ss_admins INTEGER DEFAULT -1,
	344	+ ss_images INTEGER DEFAULT 0
284	345	);
285	346
	347	+
	348	+
286	349	CREATE TABLE hitcounter (
287		~~- hc_id BIGINT NOT NULL~~
	350	+ hc_id BIGINT NOT NULL
288	351	);
289	352
	353	+
	354	+
290	355	CREATE TABLE ipblocks (
291		~~- ipb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
292		~~- --DEFAULT nextval('ipblocks_ipb_id_val'),~~
	356	+ ipb_id INTEGER NOT NULL
	357	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
293	358	ipb_address VARCHAR(1024),
294	359	ipb_user BIGINT NOT NULL DEFAULT 0,
295	360	-- REFERENCES user(user_id) ON DELETE SET NULL,
296		~~- ipb_by BIGINT NOT NULL DEFAULT 0,~~
	361	+ ipb_by BIGINT NOT NULL DEFAULT 0,
297	362	-- REFERENCES user(user_id) ON DELETE CASCADE,
298		~~- ipb_by_text VARCHAR(255) NOT NULL DEFAULT '',~~
299		~~- ipb_reason VARCHAR(1024) NOT NULL,~~
300		~~- ipb_timestamp TIMESTAMP(3) NOT NULL,~~
301		~~- ipb_auto SMALLINT NOT NULL DEFAULT 0,~~
302		~~- ipb_anon_only SMALLINT NOT NULL DEFAULT 0,~~
303		~~- ipb_create_account SMALLINT NOT NULL DEFAULT 1,~~
304		~~- ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,~~
305		~~- ipb_expiry TIMESTAMP(3) NOT NULL,~~
	363	+ ipb_by_text VARCHAR(255) NOT NULL DEFAULT '',
	364	+ ipb_reason VARCHAR(1024) NOT NULL,
	365	+ ipb_timestamp TIMESTAMP(3) NOT NULL,
	366	+ ipb_auto SMALLINT NOT NULL DEFAULT 0,
	367	+ ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
	368	+ ipb_create_account SMALLINT NOT NULL DEFAULT 1,
	369	+ ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
	370	+ ipb_expiry TIMESTAMP(3) NOT NULL,
306	371	ipb_range_start VARCHAR(1024),
307	372	ipb_range_end VARCHAR(1024),
308		~~- ipb_deleted SMALLINT NOT NULL DEFAULT 0,~~
309		~~- ipb_block_email SMALLINT NOT NULL DEFAULT 0,~~
310		~~- ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0~~
	373	+ ipb_deleted SMALLINT NOT NULL DEFAULT 0,
	374	+ ipb_block_email SMALLINT NOT NULL DEFAULT 0,
	375	+ ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
311	376
312	377	);
313		~~-CREATE INDEX ipb_address ON ipblocks (ipb_address);~~
314		~~-CREATE INDEX ipb_user ON ipblocks (ipb_user);~~
315		~~-CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);~~
	378	+CREATE INDEX ipb_address
	379	+ ON ipblocks (ipb_address);
	380	+CREATE INDEX ipb_user
	381	+ ON ipblocks (ipb_user);
	382	+CREATE INDEX ipb_range
	383	+ ON ipblocks (ipb_range_start, ipb_range_end);
316	384
317	385
318	386
319	387	CREATE TABLE image (
320		~~- img_name VARCHAR(255) NOT NULL PRIMARY KEY,~~
321		~~- img_size BIGINT NOT NULL,~~
322		~~- img_width INTEGER NOT NULL,~~
323		~~- img_height INTEGER NOT NULL,~~
324		~~- img_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',~~
	388	+ img_name VARCHAR(255) NOT NULL
	389	+ PRIMARY KEY,
	390	+ img_size BIGINT NOT NULL,
	391	+ img_width INTEGER NOT NULL,
	392	+ img_height INTEGER NOT NULL,
	393	+ img_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
325	394	img_bits SMALLINT,
326	395	img_media_type VARCHAR(255),
327		~~- img_major_mime VARCHAR(255) DEFAULT 'unknown',~~
328		~~- img_minor_mime VARCHAR(32) DEFAULT 'unknown',~~
329		~~- img_description VARCHAR(1024) NOT NULL DEFAULT '',~~
	396	+ img_major_mime VARCHAR(255) DEFAULT 'unknown',
	397	+ img_minor_mime VARCHAR(32) DEFAULT 'unknown',
	398	+ img_description VARCHAR(1024) NOT NULL DEFAULT '',
330	399	img_user BIGINT NOT NULL DEFAULT 0,
331	400	-- REFERENCES user(user_id) ON DELETE SET NULL,
332		~~- img_user_text VARCHAR(255) NOT NULL DEFAULT '',~~
	401	+ img_user_text VARCHAR(255) NOT NULL DEFAULT '',
333	402	img_timestamp TIMESTAMP(3),
334		~~- img_sha1 VARCHAR(255) NOT NULL DEFAULT ''~~
	403	+ img_sha1 VARCHAR(255) NOT NULL DEFAULT ''
335	404	);
336		~~-CREATE INDEX img_size_idx ON image (img_size);~~
337		~~-CREATE INDEX img_timestamp_idx ON image (img_timestamp);~~
338		~~-CREATE INDEX img_sha1 ON image (img_sha1);~~
	405	+CREATE INDEX img_size_idx
	406	+ ON image (img_size);
	407	+CREATE INDEX img_timestamp_idx
	408	+ ON image (img_timestamp);
	409	+CREATE INDEX img_sha1
	410	+ ON image (img_sha1);
339	411
	412	+
340	413	CREATE TABLE oldimage (
341		~~- oi_name VARCHAR(255) NOT NULL DEFAULT '',~~
342		~~- oi_archive_name VARCHAR(255) NOT NULL,~~
343		~~- oi_size BIGINT NOT NULL,~~
344		~~- oi_width INTEGER NOT NULL,~~
345		~~- oi_height INTEGER NOT NULL,~~
346		~~- oi_bits SMALLINT NOT NULL,~~
	414	+ oi_name VARCHAR(255) NOT NULL DEFAULT '',
	415	+ oi_archive_name VARCHAR(255) NOT NULL,
	416	+ oi_size BIGINT NOT NULL,
	417	+ oi_width INTEGER NOT NULL,
	418	+ oi_height INTEGER NOT NULL,
	419	+ oi_bits SMALLINT NOT NULL,
347	420	oi_description VARCHAR(1024),
348	421	oi_user BIGINT NOT NULL DEFAULT 0,
349	422	-- REFERENCES user(user_id) ON DELETE SET NULL,
350		~~- oi_user_text VARCHAR(255) NOT NULL,~~
351		~~- oi_timestamp TIMESTAMP(3) NOT NULL,~~
352		~~- oi_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',~~
353		~~- oi_media_type VARCHAR(255) ,~~
354		~~- oi_major_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',~~
355		~~- oi_minor_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',~~
356		~~- oi_deleted SMALLINT NOT NULL DEFAULT 0,~~
357		~~- oi_sha1 VARCHAR(255) NOT NULL DEFAULT ''~~
	423	+ oi_user_text VARCHAR(255) NOT NULL,
	424	+ oi_timestamp TIMESTAMP(3) NOT NULL,
	425	+ oi_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
	426	+ oi_media_type VARCHAR(255),
	427	+ oi_major_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
	428	+ oi_minor_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
	429	+ oi_deleted SMALLINT NOT NULL DEFAULT 0,
	430	+ oi_sha1 VARCHAR(255) NOT NULL DEFAULT ''
358	431	--FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
359	432	);
360		~~-CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);~~
361		~~-CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);~~
362		~~-CREATE INDEX oi_sha1 ON oldimage (oi_sha1);~~
	433	+CREATE INDEX oi_name_timestamp
	434	+ ON oldimage (oi_name, oi_timestamp);
	435	+CREATE INDEX oi_name_archive_name
	436	+ ON oldimage (oi_name, oi_archive_name);
	437	+CREATE INDEX oi_sha1
	438	+ ON oldimage (oi_sha1);
363	439
364	440
365	441
366	442	CREATE TABLE filearchive (
367		~~- fa_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
368		~~- --PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),~~
369		~~- fa_name VARCHAR(255) NOT NULL,~~
	443	+ fa_id INTEGER NOT NULL
	444	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	445	+ fa_name VARCHAR(255) NOT NULL,
370	446	fa_archive_name VARCHAR(255),
371	447	fa_storage_group VARCHAR(255),
372		~~- fa_storage_key VARCHAR(64) DEFAULT '',~~
373		~~- fa_deleted_user BIGINT NOT NULL DEFAULT 0,~~
	448	+ fa_storage_key VARCHAR(64) DEFAULT '',
	449	+ fa_deleted_user BIGINT NOT NULL DEFAULT 0,
374	450	-- REFERENCES user(user_id) ON DELETE SET NULL,
375		~~- fa_deleted_timestamp TIMESTAMP(3) NOT NULL,~~
	451	+ fa_deleted_timestamp TIMESTAMP(3) NOT NULL,
376	452	fa_deleted_reason VARCHAR(255),
377		~~- fa_size BIGINT NOT NULL,~~
378		~~- fa_width INTEGER NOT NULL,~~
379		~~- fa_height INTEGER NOT NULL,~~
380		~~- fa_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',~~
	453	+ fa_size BIGINT NOT NULL,
	454	+ fa_width INTEGER NOT NULL,
	455	+ fa_height INTEGER NOT NULL,
	456	+ fa_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
381	457	fa_bits SMALLINT,
382	458	fa_media_type VARCHAR(255),
383		~~- fa_major_mime VARCHAR(255) DEFAULT 'unknown',~~
384		~~- fa_minor_mime VARCHAR(255) DEFAULT 'unknown',~~
385		~~- fa_description VARCHAR(1024) NOT NULL,~~
	459	+ fa_major_mime VARCHAR(255) DEFAULT 'unknown',
	460	+ fa_minor_mime VARCHAR(255) DEFAULT 'unknown',
	461	+ fa_description VARCHAR(1024) NOT NULL,
386	462	fa_user BIGINT NOT NULL DEFAULT 0,
387	463	-- REFERENCES user(user_id) ON DELETE SET NULL,
388		~~- fa_user_text VARCHAR(255) NOT NULL,~~
	464	+ fa_user_text VARCHAR(255) NOT NULL,
389	465	fa_timestamp TIMESTAMP(3),
390		~~- fa_deleted SMALLINT NOT NULL DEFAULT 0~~
	466	+ fa_deleted SMALLINT NOT NULL DEFAULT 0
391	467	);
392		~~-CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);~~
393		~~-CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);~~
394		~~-CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);~~
395		~~-CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);~~
	468	+CREATE INDEX fa_name_time
	469	+ ON filearchive (fa_name, fa_timestamp);
	470	+CREATE INDEX fa_dupe
	471	+ ON filearchive (fa_storage_group, fa_storage_key);
	472	+CREATE INDEX fa_notime
	473	+ ON filearchive (fa_deleted_timestamp);
	474	+CREATE INDEX fa_nouser
	475	+ ON filearchive (fa_deleted_user);
396	476
397	477
	478	+
398	479	CREATE TABLE recentchanges (
399		~~- rc_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
400		~~- --PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),~~
401		~~- rc_timestamp TIMESTAMP(3) NOT NULL,~~
402		~~- rc_cur_time TIMESTAMP(3) NOT NULL,~~
	480	+ rc_id INTEGER NOT NULL
	481	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	482	+ rc_timestamp TIMESTAMP(3) NOT NULL,
	483	+ rc_cur_time TIMESTAMP(3) NOT NULL,
403	484	rc_user BIGINT NOT NULL DEFAULT 0,
404	485	-- REFERENCES user(user_id) ON DELETE SET NULL,
405		~~- rc_user_text VARCHAR(255) NOT NULL,~~
406		~~- rc_namespace SMALLINT NOT NULL,~~
407		~~- rc_title VARCHAR(255) NOT NULL,~~
	486	+ rc_user_text VARCHAR(255) NOT NULL,
	487	+ rc_namespace SMALLINT NOT NULL,
	488	+ rc_title VARCHAR(255) NOT NULL,
408	489	rc_comment VARCHAR(255),
409		~~- rc_minor SMALLINT NOT NULL DEFAULT 0,~~
410		~~- rc_bot SMALLINT NOT NULL DEFAULT 0,~~
411		~~- rc_new SMALLINT NOT NULL DEFAULT 0,~~
	490	+ rc_minor SMALLINT NOT NULL DEFAULT 0,
	491	+ rc_bot SMALLINT NOT NULL DEFAULT 0,
	492	+ rc_new SMALLINT NOT NULL DEFAULT 0,
412	493	rc_cur_id BIGINT NOT NULL DEFAULT 0,
413	494	-- REFERENCES page(page_id) ON DELETE SET NULL,
414		~~- rc_this_oldid BIGINT NOT NULL,~~
415		~~- rc_last_oldid BIGINT NOT NULL,~~
416		~~- rc_type SMALLINT NOT NULL DEFAULT 0,~~
	495	+ rc_this_oldid BIGINT NOT NULL,
	496	+ rc_last_oldid BIGINT NOT NULL,
	497	+ rc_type SMALLINT NOT NULL DEFAULT 0,
417	498	rc_moved_to_ns SMALLINT,
418	499	rc_moved_to_title VARCHAR(255),
419		~~- rc_patrolled SMALLINT NOT NULL DEFAULT 0,~~
420		~~- rc_ip VARCHAR(40), -- was CIDR type~~
	500	+ rc_patrolled SMALLINT NOT NULL DEFAULT 0,
	501	+ rc_ip VARCHAR(40), -- was CIDR type
421	502	rc_old_len INTEGER,
422	503	rc_new_len INTEGER,
423		~~- rc_deleted SMALLINT NOT NULL DEFAULT 0,~~
424		~~- rc_logid BIGINT NOT NULL DEFAULT 0,~~
	504	+ rc_deleted SMALLINT NOT NULL DEFAULT 0,
	505	+ rc_logid BIGINT NOT NULL DEFAULT 0,
425	506	rc_log_type VARCHAR(255),
426	507	rc_log_action VARCHAR(255),
427	508	rc_params CLOB(64K) INLINE LENGTH 4096
428	509
429	510	);
430		~~-CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);~~
431		~~-CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);~~
432		~~-CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);~~
433		~~-CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);~~
434		~~-CREATE INDEX rc_ip ON recentchanges (rc_ip);~~
	511	+CREATE INDEX rc_timestamp
	512	+ ON recentchanges (rc_timestamp);
	513	+CREATE INDEX rc_namespace_title
	514	+ ON recentchanges (rc_namespace, rc_title);
	515	+CREATE INDEX rc_cur_id
	516	+ ON recentchanges (rc_cur_id);
	517	+CREATE INDEX new_name_timestamp
	518	+ ON recentchanges (rc_new, rc_namespace, rc_timestamp);
	519	+CREATE INDEX rc_ip
	520	+ ON recentchanges (rc_ip);
435	521
436	522
437	523
438	524	CREATE TABLE watchlist (
439		~~- wl_user BIGINT NOT NULL DEFAULT 0,~~
	525	+ wl_user BIGINT NOT NULL DEFAULT 0,
440	526	-- REFERENCES user(user_id) ON DELETE CASCADE,
441		~~- wl_namespace SMALLINT NOT NULL DEFAULT 0,~~
442		~~- wl_title VARCHAR(255) NOT NULL,~~
	527	+ wl_namespace SMALLINT NOT NULL DEFAULT 0,
	528	+ wl_title VARCHAR(255) NOT NULL,
443	529	wl_notificationtimestamp TIMESTAMP(3)
444	530	);
445		~~-CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);~~
	531	+CREATE UNIQUE INDEX wl_user_namespace_title
	532	+ ON watchlist (wl_namespace, wl_title, wl_user);
446	533
447	534
	535	+
448	536	CREATE TABLE interwiki (
449		~~- iw_prefix VARCHAR(32) NOT NULL UNIQUE,~~
450		~~- iw_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,~~
451		~~- iw_api CLOB(64K) INLINE LENGTH 4096 NOT NULL,~~
452		~~- iw_wikiid varchar(64) NOT NULL,~~
453		~~- iw_local SMALLINT NOT NULL,~~
454		~~- iw_trans SMALLINT NOT NULL DEFAULT 0~~
	537	+ iw_prefix VARCHAR(32) NOT NULL UNIQUE,
	538	+ iw_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
	539	+ iw_api CLOB(64K) INLINE LENGTH 4096 NOT NULL,
	540	+ iw_wikiid VARCHAR(64) NOT NULL,
	541	+ iw_local SMALLINT NOT NULL,
	542	+ iw_trans SMALLINT NOT NULL DEFAULT 0
455	543	);
456	544
457	545
	546	+
458	547	CREATE TABLE querycache (
459		~~- qc_type VARCHAR(255) NOT NULL,~~
460		~~- qc_value BIGINT NOT NULL,~~
461		~~- qc_namespace INTEGER NOT NULL,~~
462		~~- qc_title VARCHAR(255) NOT NULL~~
	548	+ qc_type VARCHAR(255) NOT NULL,
	549	+ qc_value BIGINT NOT NULL,
	550	+ qc_namespace INTEGER NOT NULL,
	551	+ qc_title VARCHAR(255) NOT NULL
463	552	);
464		~~-CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);~~
	553	+CREATE INDEX querycache_type_value
	554	+ ON querycache (qc_type, qc_value);
465	555
466	556
467	557
468		~~-CREATE TABLE querycache_info (~~
469		~~- qci_type VARCHAR(255) UNIQUE NOT NULL,~~
470		~~- qci_timestamp TIMESTAMP(3)~~
	558	+CREATE TABLE querycache_info (
	559	+ qci_type VARCHAR(255) UNIQUE NOT NULL,
	560	+ qci_timestamp TIMESTAMP(3)
471	561	);
472	562
473	563
	564	+
474	565	CREATE TABLE querycachetwo (
475		~~- qcc_type VARCHAR(255) NOT NULL,~~
476		~~- qcc_value BIGINT NOT NULL DEFAULT 0,~~
477		~~- qcc_namespace INTEGER NOT NULL DEFAULT 0,~~
478		~~- qcc_title VARCHAR(255) NOT NULL DEFAULT '',~~
479		~~- qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,~~
480		~~- qcc_titletwo VARCHAR(255) NOT NULL DEFAULT ''~~
	566	+ qcc_type VARCHAR(255) NOT NULL,
	567	+ qcc_value BIGINT NOT NULL DEFAULT 0,
	568	+ qcc_namespace INTEGER NOT NULL DEFAULT 0,
	569	+ qcc_title VARCHAR(255) NOT NULL DEFAULT '',
	570	+ qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
	571	+ qcc_titletwo VARCHAR(255) NOT NULL DEFAULT ''
481	572	);
482		~~-CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);~~
483		~~-CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);~~
484		~~-CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);~~
	573	+CREATE INDEX querycachetwo_type_value
	574	+ ON querycachetwo (qcc_type, qcc_value);
	575	+CREATE INDEX querycachetwo_title
	576	+ ON querycachetwo (qcc_type, qcc_namespace, qcc_title);
	577	+CREATE INDEX querycachetwo_titletwo
	578	+ ON querycachetwo (qcc_type, qcc_namespacetwo, qcc_titletwo);
485	579
	580	+
	581	+
486	582	CREATE TABLE objectcache (
487		~~- keyname VARCHAR(255) NOT NULL UNIQUE, -- was nullable~~
488		~~- value CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',~~
489		~~- exptime TIMESTAMP(3) NOT NULL~~
	583	+ keyname VARCHAR(255) NOT NULL UNIQUE, -- was nullable
	584	+ value CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
	585	+ exptime TIMESTAMP(3) NOT NULL
490	586	);
491		~~-CREATE INDEX objectcacache_exptime ON objectcache (exptime);~~
	587	+CREATE INDEX objectcacache_exptime
	588	+ ON objectcache (exptime);
492	589
493	590
494	591
495	592	CREATE TABLE transcache (
496		~~- tc_url VARCHAR(255) NOT NULL UNIQUE,~~
497		~~- tc_contents CLOB(64K) INLINE LENGTH 4096 NOT NULL,~~
498		~~- tc_time TIMESTAMP(3) NOT NULL~~
	593	+ tc_url VARCHAR(255) NOT NULL UNIQUE,
	594	+ tc_contents CLOB(64K) INLINE LENGTH 4096 NOT NULL,
	595	+ tc_time TIMESTAMP(3) NOT NULL
499	596	);
500	597
501	598
	599	+
502	600	CREATE TABLE logging (
503		~~- log_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
504		~~- --PRIMARY KEY DEFAULT nextval('log_log_id_seq'),~~
505		~~- log_type VARCHAR(32) NOT NULL,~~
506		~~- log_action VARCHAR(32) NOT NULL,~~
507		~~- log_timestamp TIMESTAMP(3) NOT NULL,~~
508		~~- log_user BIGINT NOT NULL DEFAULT 0,~~
	601	+ log_id BIGINT NOT NULL
	602	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	603	+ log_type VARCHAR(32) NOT NULL,
	604	+ log_action VARCHAR(32) NOT NULL,
	605	+ log_timestamp TIMESTAMP(3) NOT NULL,
	606	+ log_user BIGINT NOT NULL DEFAULT 0,
509	607	-- REFERENCES user(user_id) ON DELETE SET NULL,
510	608	-- Name of the user who performed this action
511		~~- log_user_text VARCHAR(255) NOT NULL default '',~~
512		~~- log_namespace SMALLINT NOT NULL,~~
513		~~- log_title VARCHAR(255) NOT NULL,~~
514		~~- log_page BIGINT,~~
515		~~- log_comment VARCHAR(255),~~
516		~~- log_params CLOB(64K) INLINE LENGTH 4096,~~
517		~~- log_deleted SMALLINT NOT NULL DEFAULT 0~~
	609	+ log_user_text VARCHAR(255) NOT NULL DEFAULT '',
	610	+ log_namespace SMALLINT NOT NULL,
	611	+ log_title VARCHAR(255) NOT NULL,
	612	+ log_page BIGINT,
	613	+ log_comment VARCHAR(255),
	614	+ log_params CLOB(64K) INLINE LENGTH 4096,
	615	+ log_deleted SMALLINT NOT NULL DEFAULT 0
518	616	);
519		~~-CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);~~
520		~~-CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);~~
521		~~-CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);~~
522		~~-CREATE INDEX log_user_type_time ON logging (log_user, log_type, log_timestamp);~~
523		~~-CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp);~~
	617	+CREATE INDEX logging_type_name
	618	+ ON logging (log_type, log_timestamp);
	619	+CREATE INDEX logging_user_time
	620	+ ON logging (log_timestamp, log_user);
	621	+CREATE INDEX logging_page_time
	622	+ ON logging (log_namespace, log_title, log_timestamp);
	623	+CREATE INDEX log_user_type_time
	624	+ ON logging (log_user, log_type, log_timestamp);
	625	+CREATE INDEX log_page_id_time
	626	+ ON logging (log_page, log_timestamp);
	627	+CREATE UNIQUE INDEX type_action
	628	+ ON logging (log_type, log_action, log_timestamp);
524	629
525		~~-CREATE UNIQUE INDEX "TYPE_ACTION" ON "LOGGING"~~
526		-(
527		~~-"LOG_TYPE",~~
528		~~-"LOG_ACTION",~~
529		~~-"LOG_TIMESTAMP"~~
530		-)
531		-;
532	630
533	631
534		-
535	632	CREATE TABLE trackbacks (
536		~~- tb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
537		~~- --PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),~~
	633	+ tb_id INTEGER NOT NULL
	634	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
538	635	-- foreign key also in MySQL
539	636	tb_page INTEGER,
540	637	-- REFERENCES page(page_id) ON DELETE CASCADE,
541		~~- tb_title VARCHAR(255) NOT NULL,~~
542		~~- tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,~~
	638	+ tb_title VARCHAR(255) NOT NULL,
	639	+ tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
543	640	tb_ex CLOB(64K) INLINE LENGTH 4096,
544	641	tb_name VARCHAR(255)
545	642	);
546		~~-CREATE INDEX trackback_page ON trackbacks (tb_page);~~
	643	+CREATE INDEX trackback_page
	644	+ ON trackbacks (tb_page);
547	645
548	646
549	647
550	648	CREATE TABLE job (
551		~~- job_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
552		~~- --PRIMARY KEY DEFAULT nextval('job_job_id_seq'),~~
553		~~- job_cmd VARCHAR(255) NOT NULL,~~
554		~~- job_namespace SMALLINT NOT NULL,~~
555		~~- job_title VARCHAR(255) NOT NULL,~~
556		~~- job_params CLOB(64K) INLINE LENGTH 4096 NOT NULL~~
	649	+ job_id BIGINT NOT NULL
	650	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	651	+ job_cmd VARCHAR(255) NOT NULL,
	652	+ job_namespace SMALLINT NOT NULL,
	653	+ job_title VARCHAR(255) NOT NULL,
	654	+ job_params CLOB(64K) INLINE LENGTH 4096 NOT NULL
557	655	);
558		~~-CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);~~
	656	+CREATE INDEX job_cmd_namespace_title
	657	+ ON job (job_cmd, job_namespace, job_title);
559	658
560	659
	660	+
561	661	--TODO
	662	+--CREATE FUNCTION add_interwiki (TEXT, INT, SMALLINT) RETURNS INT LANGUAGE SQL AS
562	663	--$mw$
563	664	-- INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
564	665	-- SELECT 1;
565	666	--$mw$;
566	667
	668	+
	669	+
567	670	-- hack implementation
568	671	-- should be replaced with OmniFind, Contains(), etc
569	672	CREATE TABLE searchindex (
570		~~- si_page BIGINT NOT NULL,~~
571		~~- si_title varchar(255) NOT NULL default '',~~
572		~~- si_text clob NOT NULL~~
	673	+ si_page BIGINT NOT NULL,
	674	+ si_title VARCHAR(255) NOT NULL DEFAULT '',
	675	+ si_text CLOB NOT NULL
573	676	);
574	677
	678	+
	679	+
575	680	-- This table is not used unless profiling is turned on
576	681	CREATE TABLE profiling (
577		~~- pf_count INTEGER NOT NULL DEFAULT 0,~~
578		~~- pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,~~
579		~~- pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,~~
580		~~- pf_name VARCHAR(255) NOT NULL,~~
581		~~- pf_server VARCHAR(255)~~
	682	+ pf_count INTEGER NOT NULL DEFAULT 0,
	683	+ pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
	684	+ pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
	685	+ pf_name VARCHAR(255) NOT NULL,
	686	+ pf_server VARCHAR(255)
582	687	);
583		~~-CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);~~
	688	+CREATE UNIQUE INDEX pf_name_server
	689	+ ON profiling (pf_name, pf_server);
584	690
	691	+
	692	+
585	693	CREATE TABLE protected_titles (
586		~~- pt_namespace INTEGER NOT NULL,~~
587		~~- pt_title VARCHAR(255) NOT NULL,~~
	694	+ pt_namespace INTEGER NOT NULL,
	695	+ pt_title VARCHAR(255) NOT NULL,
588	696	pt_user BIGINT NOT NULL DEFAULT 0,
589	697	-- REFERENCES user(user_id) ON DELETE SET NULL,
590	698	pt_reason VARCHAR(1024),
591	699	pt_timestamp TIMESTAMP(3) NOT NULL,
592		~~- pt_expiry TIMESTAMP(3) ,~~
593		~~- pt_create_perm VARCHAR(60) NOT NULL DEFAULT ''~~
	700	+ pt_expiry TIMESTAMP(3),
	701	+ pt_create_perm VARCHAR(60) NOT NULL DEFAULT ''
594	702	);
595		~~-CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);~~
	703	+CREATE UNIQUE INDEX protected_titles_unique
	704	+ ON protected_titles (pt_namespace, pt_title);
596	705
597	706
598	707
599	708	CREATE TABLE updatelog (
600		~~- ul_key VARCHAR(255) NOT NULL PRIMARY KEY~~
	709	+ ul_key VARCHAR(255) NOT NULL
	710	+ PRIMARY KEY
601	711	);
602	712
603	713
	714	+
604	715	CREATE TABLE category (
605		~~- cat_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),~~
606		~~- --PRIMARY KEY DEFAULT nextval('category_id_seq'),~~
607		~~- cat_title VARCHAR(255) NOT NULL,~~
608		~~- cat_pages INTEGER NOT NULL DEFAULT 0,~~
609		~~- cat_subcats INTEGER NOT NULL DEFAULT 0,~~
610		~~- cat_files INTEGER NOT NULL DEFAULT 0,~~
611		~~- cat_hidden SMALLINT NOT NULL DEFAULT 0~~
	716	+ cat_id INTEGER NOT NULL
	717	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	718	+ cat_title VARCHAR(255) NOT NULL,
	719	+ cat_pages INTEGER NOT NULL DEFAULT 0,
	720	+ cat_subcats INTEGER NOT NULL DEFAULT 0,
	721	+ cat_files INTEGER NOT NULL DEFAULT 0,
	722	+ cat_hidden SMALLINT NOT NULL DEFAULT 0
612	723	);
613		~~-CREATE UNIQUE INDEX category_title ON category(cat_title);~~
614		~~-CREATE INDEX category_pages ON category(cat_pages);~~
	724	+CREATE UNIQUE INDEX category_title
	725	+ ON category (cat_title);
	726	+CREATE INDEX category_pages
	727	+ ON category (cat_pages);
615	728
616	729
	730	+
617	731	-- A table to track tags for revisions, logs and recent changes.
618	732	CREATE TABLE change_tag (
619		~~- ct_rc_id INTEGER,~~
620		~~- ct_log_id INTEGER,~~
621		~~- ct_rev_id INTEGER,~~
622		~~- ct_tag varchar(255) NOT NULL,~~
623		~~- ct_params CLOB(64K) INLINE LENGTH 4096~~
	733	+ ct_rc_id INTEGER,
	734	+ ct_log_id INTEGER,
	735	+ ct_rev_id INTEGER,
	736	+ ct_tag VARCHAR(255) NOT NULL,
	737	+ ct_params CLOB(64K) INLINE LENGTH 4096
624	738	);
625		~~-CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag (ct_rc_id,ct_tag);~~
626		~~-CREATE UNIQUE INDEX change_tag_log_tag ON change_tag (ct_log_id,ct_tag);~~
627		~~-CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag (ct_rev_id,ct_tag);~~
	739	+CREATE UNIQUE INDEX change_tag_rc_tag
	740	+ ON change_tag (ct_rc_id, ct_tag);
	741	+CREATE UNIQUE INDEX change_tag_log_tag
	742	+ ON change_tag (ct_log_id, ct_tag);
	743	+CREATE UNIQUE INDEX change_tag_rev_tag
	744	+ ON change_tag (ct_rev_id, ct_tag);
628	745	-- Covering index, so we can pull all the info only out of the index.
629		~~-CREATE INDEX change_tag_tag_id ON change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id);~~
	746	+CREATE INDEX change_tag_tag_id
	747	+ ON change_tag (ct_tag, ct_rc_id, ct_rev_id, ct_log_id);
630	748
631	749
	750	+
632	751	-- Rollup table to pull a LIST of tags simply
633	752	CREATE TABLE tag_summary (
634		~~- ts_rc_id INTEGER,~~
	753	+ ts_rc_id INTEGER,
635	754	ts_log_id INTEGER,
636	755	ts_rev_id INTEGER,
637		~~- ts_tags CLOB(64K) INLINE LENGTH 4096 NOT NULL~~
	756	+ ts_tags CLOB(64K) INLINE LENGTH 4096 NOT NULL
638	757	);
639		~~-CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary (ts_rc_id);~~
640		~~-CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary (ts_log_id);~~
641		~~-CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary (ts_rev_id);~~
	758	+CREATE UNIQUE INDEX tag_summary_rc_id
	759	+ ON tag_summary (ts_rc_id);
	760	+CREATE UNIQUE INDEX tag_summary_log_id
	761	+ ON tag_summary (ts_log_id);
	762	+CREATE UNIQUE INDEX tag_summary_rev_id
	763	+ ON tag_summary (ts_rev_id);
642	764
643	765
	766	+
644	767	CREATE TABLE valid_tag (
645		~~- vt_tag varchar(255) NOT NULL PRIMARY KEY~~
	768	+ vt_tag VARCHAR(255) NOT NULL
	769	+ PRIMARY KEY
646	770	);
647	771
	772	+
	773	+
648	774	--
649	775	-- User preferences and perhaps other fun stuff. :)
650	776	-- Replaces the old user.user_options blob, with a couple nice properties:
651	777	--
	778	+-- 1) We only store non-default settings, so changes to the DEFAULTs
652	779	-- are now reflected for everybody, not just new accounts.
653	780	-- 2) We can more easily do bulk lookups, statistics, or modifications of
654	781	-- saved options since it's a sane table structure.
655	782	--
656	783	CREATE TABLE user_properties (
657	784	-- Foreign key to user.user_id
658		~~- up_user BIGINT NOT NULL,~~
659		-
	785	+ up_user BIGINT NOT NULL,
660	786	-- Name of the option being saved. This is indexed for bulk lookup.
661		~~- up_property VARCHAR(255) FOR BIT DATA NOT NULL,~~
662		-
	787	+ up_property VARCHAR(255) FOR BIT DATA NOT NULL,
663	788	-- Property value as a string.
664		~~- up_value CLOB(64K) INLINE LENGTH 4096~~
	789	+ up_value CLOB(64K) INLINE LENGTH 4096
665	790	);
666		~~-CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);~~
667		~~-CREATE INDEX user_properties_property ON user_properties (up_property);~~
	791	+CREATE UNIQUE INDEX user_properties_user_property
	792	+ ON user_properties (up_user, up_property);
	793	+CREATE INDEX user_properties_property
	794	+ ON user_properties (up_property);
668	795
669	796	CREATE TABLE log_search (
670	797	-- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username)
671		~~- ls_field VARCHAR(32) FOR BIT DATA NOT NULL,~~
	798	+ ls_field VARCHAR(32) FOR BIT DATA NOT NULL,
672	799	-- The value of the ID
673		~~- ls_value varchar(255) NOT NULL,~~
	800	+ ls_value VARCHAR(255) NOT NULL,
674	801	-- Key to log_id
675		~~- ls_log_id BIGINT NOT NULL default 0~~
	802	+ ls_log_id BIGINT NOT NULL DEFAULT 0
676	803	);
677		~~-CREATE UNIQUE INDEX ls_field_val ON log_search (ls_field,ls_value,ls_log_id);~~
678		~~-CREATE INDEX ls_log_id ON log_search (ls_log_id);~~
	804	+CREATE UNIQUE INDEX ls_field_val
	805	+ ON log_search (ls_field, ls_value, ls_log_id);
	806	+CREATE INDEX ls_log_id
	807	+ ON log_search (ls_log_id);
679	808
	809	+
	810	+
680	811	-- Table for storing localisation data
681	812	CREATE TABLE l10n_cache (
682	813	-- Language code
683		~~- lc_lang VARCHAR(32) NOT NULL,~~
	814	+ lc_lang VARCHAR(32) NOT NULL,
684	815	-- Cache key
685		~~- lc_key VARCHAR(255) NOT NULL,~~
	816	+ lc_key VARCHAR(255) NOT NULL,
686	817	-- Value
687		~~- lc_value CLOB(16M) INLINE LENGTH 4096 NOT NULL~~
	818	+ lc_value CLOB(16M) INLINE LENGTH 4096 NOT NULL
688	819	);
689		~~-CREATE INDEX lc_lang_key ON l10n_cache (lc_lang, lc_key);~~
	820	+CREATE INDEX lc_lang_key
	821	+ ON l10n_cache (lc_lang, lc_key);
690	822
691	823
692		~~-CREATE TABLE "MSG_RESOURCE_LINKS"~~
693		-(
694		~~-"MRL_RESOURCE" VARCHAR(255) FOR BIT DATA NOT NULL ,~~
695		~~-"MRL_MESSAGE" VARCHAR(255) FOR BIT DATA NOT NULL~~
696		-)
697		-;
698	824
699		~~-CREATE UNIQUE INDEX "UQ61_MSG_RESOURCE_LINKS" ON "MSG_RESOURCE_LINKS"~~
	825	+CREATE TABLE msg_resource_links
700	826	(
701		~~-"MRL_MESSAGE",~~
702		~~-"MRL_RESOURCE"~~
703		-)
704		~~-ALLOW REVERSE SCANS~~
705		-;
	827	+ mrl_resource VARCHAR(255) FOR BIT DATA NOT NULL,
	828	+ mrl_message VARCHAR(255) FOR BIT DATA NOT NULL
	829	+);
	830	+CREATE UNIQUE INDEX uq61_msg_resource_links
	831	+ ON msg_resource_links (mrl_message, mrl_resource);
	832	+-- All DB2 indexes DEFAULT to allowing reverse scans
706	833
707		~~-CREATE TABLE "MSG_RESOURCE"~~
708		-(
709		~~-"MR_RESOURCE" VARCHAR(255) FOR BIT DATA NOT NULL ,~~
710		~~-"MR_LANG" VARCHAR(32) FOR BIT DATA NOT NULL ,~~
711		~~-"MR_BLOB" BLOB NOT NULL ,~~
712		~~-"MR_TIMESTAMP" TIMESTAMP(3) NOT NULL~~
713		-)
714		-;
715	834
716		~~-CREATE UNIQUE INDEX "UQ81_MSG_RESOURCE" ON "MSG_RESOURCE"~~
	835	+
	836	+CREATE TABLE msg_resource
717	837	(
718		~~-"MR_RESOURCE"~~
719		~~-,"MR_LANG"~~
720		-)
721		~~-ALLOW REVERSE SCANS~~
722		-;
	838	+ mr_resource VARCHAR(255) FOR BIT DATA NOT NULL,
	839	+ mr_lang VARCHAR(32) FOR BIT DATA NOT NULL,
	840	+ mr_blob CLOB(64K) INLINE LENGTH 4096 NOT NULL,
	841	+ mr_timestamp TIMESTAMP(3) NOT NULL
	842	+);
	843	+CREATE UNIQUE INDEX uq81_msg_resource
	844	+ ON msg_resource (mr_resource, mr_lang);
	845	+-- All DB2 indexes DEFAULT to allowing reverse scans
723	846
724		~~-CREATE TABLE "MODULE_DEPS" (~~
725		~~-"MD_MODULE" VARCHAR(255) FOR BIT DATA NOT NULL ,~~
726		~~-"MD_SKIN" VARCHAR(32) FOR BIT DATA NOT NULL ,~~
727		~~-"MD_DEPS" CLOB(16M) INLINE LENGTH 4096 NOT NULL~~
728		-)
729		-;
730	847
731		~~-CREATE UNIQUE INDEX "UQ96_MODULE_DEPS" ON "MODULE_DEPS"~~
732		-(
733		~~-"MD_MODULE"~~
734		~~-,"MD_SKIN"~~
735		-)
736		~~-ALLOW REVERSE SCANS~~
737		-;
738	848
739		~~-CREATE TABLE "IWLINKS"~~
	849	+CREATE TABLE module_deps (
	850	+ md_module VARCHAR(255) FOR BIT DATA NOT NULL,
	851	+ md_skin VARCHAR(32) FOR BIT DATA NOT NULL,
	852	+ md_deps CLOB(16M) INLINE LENGTH 4096 NOT NULL
	853	+);
	854	+CREATE UNIQUE INDEX uq96_module_deps
	855	+ ON module_deps (md_module, md_skin);
	856	+-- All DB2 indexes DEFAULT to allowing reverse scans
	857	+
	858	+
	859	+
	860	+CREATE TABLE iwlinks
740	861	(
741		~~-"IWL_FROM" INT NOT NULL ,~~
742		~~-"IWL_PREFIX" VARCHAR(20) FOR BIT DATA NOT NULL ,~~
743		~~-"IWL_TITLE" VARCHAR(255) FOR BIT DATA NOT NULL~~
744		-)
745		-;
	862	+ iwl_from INTEGER NOT NULL,
	863	+ iwl_prefix VARCHAR(20) FOR BIT DATA NOT NULL,
	864	+ iwl_title VARCHAR(255) FOR BIT DATA NOT NULL
	865	+);
746	866
747	867
	868	+
748	869	--
749	870	-- Store information about newly uploaded files before they're
750	871	-- moved into the actual filestore
751	872	--
752		~~-CREATE TABLE /_/uploadstash (~~
753		~~- us_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,~~
754		-
755		~~- -- the user who uploaded the file.~~
756		~~- us_user BIGINT NOT NULL,~~
757		-
758		~~- -- file key. this is how applications actually search for the file.~~
759		~~- -- this might go away, or become the primary key.~~
760		~~- us_key varchar(255) NOT NULL,~~
761		-
762		~~- -- the original path~~
763		~~- us_orig_path varchar(255) NOT NULL,~~
764		-
765		~~- -- the temporary path at which the file is actually stored~~
766		~~- us_path varchar(255) NOT NULL,~~
767		-
768		~~- -- which type of upload the file came from (sometimes)~~
769		~~- us_source_type varchar(50),~~
770		-
771		~~- -- the date/time on which the file was added~~
772		~~- us_timestamp TIMESTAMP(3) not null,~~
773		-
774		~~- us_status varchar(50) not null,~~
775		-
776		~~- -- file properties from File::getPropsFromPath. these may prove unnecessary.~~
777		~~- --~~
778		~~- us_size BIGINT NOT NULL,~~
779		~~- -- this hash comes from File::sha1Base36(), and is 31 characters~~
780		~~- us_sha1 varchar(31) NOT NULL,~~
781		~~- us_mime varchar(255),~~
782		~~- -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table~~
783		- us_media_type VARCHAR(30) CONSTRAINT my_constraint CHECK (us_media_type in ('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE')) default NULL,
784		~~- -- image-specific properties~~
785		~~- us_image_width BIGINT,~~
786		~~- us_image_height BIGINT,~~
787		~~- us_image_bits integer~~
788		-
789		~~-) /$wgDBTableOptions/;~~
790		-
	873	+CREATE TABLE uploadstash (
	874	+ us_id BIGINT NOT NULL
	875	+ PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	876	+ -- the user who uploaded the file.
	877	+ us_user BIGINT NOT NULL,
	878	+ -- file key. this is how applications actually search for the file.
	879	+ -- this might go away, or become the primary key.
	880	+ us_key VARCHAR(255) NOT NULL,
	881	+ -- the original path
	882	+ us_orig_path VARCHAR(255) NOT NULL,
	883	+ -- the temporary path at which the file is actually stored
	884	+ us_path VARCHAR(255) NOT NULL,
	885	+ -- which type of upload the file came from (sometimes)
	886	+ us_source_type VARCHAR(50),
	887	+ -- the date/time on which the file was added
	888	+ us_timestamp TIMESTAMP(3) NOT NULL,
	889	+ us_status VARCHAR(50) NOT NULL,
	890	+ -- file properties from File::getPropsFromPath. these may prove unnecessary.
	891	+ --
	892	+ us_size BIGINT NOT NULL,
	893	+ -- this hash comes from File::sha1Base36(), and is 31 characters
	894	+ us_sha1 VARCHAR(31) NOT NULL,
	895	+ us_mime VARCHAR(255),
	896	+ -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
	897	+ us_media_type VARCHAR(30)
	898	+ CONSTRAINT my_constraint
	899	+ CHECK (
	900	+ us_media_type in (
	901	+ 'UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA',
	902	+ 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'
	903	+ )
	904	+ ) DEFAULT NULL,
	905	+ -- image-specific properties
	906	+ us_image_width BIGINT,
	907	+ us_image_height BIGINT,
	908	+ us_image_bits INTEGER
	909	+);
791	910	-- sometimes there's a delete for all of a user's stuff.
792		~~-CREATE INDEX /i/us_user ON /_/uploadstash (us_user);~~
793		~~-CREATE UNIQUE INDEX /i/us_key ON /_/uploadstash (us_key);~~
	911	+CREATE INDEX us_user
	912	+ ON uploadstash (us_user);
	913	+-- pick out files by key, enforce key UNIQUEness
	914	+CREATE UNIQUE INDEX us_key
	915	+ ON uploadstash (us_key);
794	916	-- the abandoned upload cleanup script needs this
795		~~-CREATE INDEX /i/us_timestamp ON /_/uploadstash (us_timestamp);~~
	917	+CREATE INDEX us_timestamp
	918	+ ON uploadstash (us_timestamp);
796	919
797	920
798	921
799	922	-- Stores the groups the user has once belonged to.
800	923	-- The user may still belong these groups. Check user_groups.
801		-
802	924	CREATE TABLE user_former_groups (
803		~~- ufg_user BIGINT NOT NULL DEFAULT 0,~~
804		~~- ufg_group VARCHAR(16) FOR BIT DATA NOT NULL~~
	925	+ ufg_user BIGINT NOT NULL DEFAULT 0,
	926	+ ufg_group VARCHAR(16) FOR BIT DATA NOT NULL
805	927	);
806		~~-CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);~~
	928	+CREATE UNIQUE INDEX ufg_user_group
	929	+ ON user_former_groups (ufg_user, ufg_group);
807	930
808	931
	932	+
809	933	-- Table for holding configuration changes
810		~~-CREATE TABLE "CONFIG"~~
811		-(
812		~~-"CF_NAME" VARCHAR(255) NOT NULL PRIMARY KEY,~~
813		~~-"CF_VALUE" BLOB NOT NULL~~
814		-)
815		-;
	934	+CREATE TABLE config (
	935	+ cf_name VARCHAR(255) NOT NULL
	936	+ PRIMARY KEY,
	937	+ cf_value CLOB(64K) INLINE LENGTH 4096 NOT NULL
	938	+);
816	939

Past revisions this follows-up on

Revision	Commit summary	Author	Date
r106480	Fixes to DB2 support:...	leonsp	21:17, 16 December 2011

Comments

#Comment by MaxSem (talk | contribs) 16:02, 17 December 2011

Thanks:) Note that the 32K limit is irrelevant here because sourceFile() feeds the SQL statement-by-statement instead of whole file at once.

#Comment by Leonsp (talk | contribs) 16:04, 17 December 2011

Awesome, good to know.:-)

Status & tagging log

21:31, 20 December 2011 Brion VIBBER (talk | contribs) changed the status of r106507 [removed: new added: deferred]