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