summaryrefslogtreecommitdiff
path: root/maintenance/ibm_db2/tables.sql
blob: caad9251a64887961fa049daedc9c079292eeb67 (plain)
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);