summaryrefslogtreecommitdiff
path: root/maintenance/postgres/tables.sql
blob: 5ed7de99aef44c3b38ea2ad2b7ed3915d3dc1ed3 (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
-- 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.
-- This is the PostgreSQL version.
-- For information about each table, please see the notes in maintenance/tables.sql
-- Please make sure all dollar-quoting uses $mw$ at the start of the line
-- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code)

BEGIN;
SET client_min_messages = 'ERROR';

DROP SEQUENCE IF EXISTS user_user_id_seq CASCADE;
DROP SEQUENCE IF EXISTS page_page_id_seq CASCADE;
DROP SEQUENCE IF EXISTS revision_rev_id_seq CASCADE;
DROP SEQUENCE IF EXISTS page_restrictions_id_seq CASCADE;
DROP SEQUENCE IF EXISTS ipblocks_ipb_id_seq CASCADE;
DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE;
DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE;
DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE;
DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE;
DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE;
DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE;
DROP FUNCTION IF EXISTS page_deleted() CASCADE;
DROP FUNCTION IF EXISTS ts2_page_title() CASCADE;
DROP FUNCTION IF EXISTS ts2_page_text() CASCADE;
DROP FUNCTION IF EXISTS add_interwiki(TEXT,INT,SMALLINT) CASCADE;

CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
CREATE TABLE mwuser ( -- replace reserved word 'user'
  user_id                   INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
  user_name                 TEXT     NOT NULL  UNIQUE,
  user_real_name            TEXT,
  user_password             TEXT,
  user_newpassword          TEXT,
  user_newpass_time         TIMESTAMPTZ,
  user_token                TEXT,
  user_email                TEXT,
  user_email_token          TEXT,
  user_email_token_expires  TIMESTAMPTZ,
  user_email_authenticated  TIMESTAMPTZ,
  user_touched              TIMESTAMPTZ,
  user_registration         TIMESTAMPTZ,
  user_editcount            INTEGER
);
CREATE INDEX user_email_token_idx ON mwuser (user_email_token);

-- Create a dummy user to satisfy fk contraints especially with revisions
INSERT INTO mwuser
  VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());

CREATE TABLE user_groups (
  ug_user   INTEGER      NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  ug_group  TEXT     NOT NULL
);
CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);

CREATE TABLE user_former_groups (
  ufg_user   INTEGER      NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  ufg_group  TEXT     NOT NULL
);
CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);

CREATE TABLE user_newtalk (
  user_id              INTEGER      NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  user_ip              TEXT             NULL,
  user_last_timestamp  TIMESTAMPTZ
);
CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);


CREATE SEQUENCE page_page_id_seq;
CREATE TABLE page (
  page_id            INTEGER        NOT NULL  PRIMARY KEY DEFAULT nextval('page_page_id_seq'),
  page_namespace     SMALLINT       NOT NULL,
  page_title         TEXT           NOT NULL,
  page_restrictions  TEXT,
  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  DEFAULT RANDOM(),
  page_touched       TIMESTAMPTZ,
  page_latest        INTEGER        NOT NULL, -- FK?
  page_len           INTEGER        NOT NULL,
  page_content_model TEXT
);
CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
CREATE INDEX page_main_title         ON page (page_title text_pattern_ops) WHERE page_namespace = 0;
CREATE INDEX page_talk_title         ON page (page_title text_pattern_ops) WHERE page_namespace = 1;
CREATE INDEX page_user_title         ON page (page_title text_pattern_ops) WHERE page_namespace = 2;
CREATE INDEX page_utalk_title        ON page (page_title text_pattern_ops) WHERE page_namespace = 3;
CREATE INDEX page_project_title      ON page (page_title text_pattern_ops) WHERE page_namespace = 4;
CREATE INDEX page_mediawiki_title    ON page (page_title text_pattern_ops) WHERE page_namespace = 8;
CREATE INDEX page_random_idx         ON page (page_random);
CREATE INDEX page_len_idx            ON page (page_len);

CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
$mw$
BEGIN
DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
RETURN NULL;
END;
$mw$;

CREATE TRIGGER page_deleted AFTER DELETE ON page
  FOR EACH ROW EXECUTE PROCEDURE page_deleted();

CREATE SEQUENCE revision_rev_id_seq;
CREATE TABLE revision (
  rev_id             INTEGER      NOT NULL  UNIQUE DEFAULT nextval('revision_rev_id_seq'),
  rev_page           INTEGER          NULL  REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  rev_text_id        INTEGER          NULL, -- FK
  rev_comment        TEXT,
  rev_user           INTEGER      NOT NULL  REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  rev_user_text      TEXT         NOT NULL,
  rev_timestamp      TIMESTAMPTZ  NOT NULL,
  rev_minor_edit     SMALLINT     NOT NULL  DEFAULT 0,
  rev_deleted        SMALLINT     NOT NULL  DEFAULT 0,
  rev_len            INTEGER          NULL,
  rev_parent_id      INTEGER          NULL,
  rev_sha1           TEXT         NOT NULL DEFAULT '',
  rev_content_model  TEXT,
  rev_content_format TEXT
);
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 SEQUENCE text_old_id_seq;
CREATE TABLE pagecontent ( -- replaces reserved word 'text'
  old_id     INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('text_old_id_seq'),
  old_text   TEXT,
  old_flags  TEXT
);


CREATE SEQUENCE page_restrictions_pr_id_seq;
CREATE TABLE page_restrictions (
  pr_id      INTEGER      NOT NULL  UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
  pr_page    INTEGER          NULL  REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  pr_type    TEXT         NOT NULL,
  pr_level   TEXT         NOT NULL,
  pr_cascade SMALLINT     NOT NULL,
  pr_user    INTEGER          NULL,
  pr_expiry  TIMESTAMPTZ      NULL
);
ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);

CREATE TABLE page_props (
  pp_page      INTEGER  NOT NULL  REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  pp_propname  TEXT     NOT NULL,
  pp_value     TEXT     NOT NULL
);
ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
CREATE INDEX page_props_propname ON page_props (pp_propname);
CREATE UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page);

CREATE SEQUENCE archive_ar_id_seq;
CREATE TABLE archive (
  ar_id             INTEGER      NOT NULL  PRIMARY KEY DEFAULT nextval('archive_ar_id_seq'),
  ar_namespace      SMALLINT     NOT NULL,
  ar_title          TEXT         NOT NULL,
  ar_text           TEXT, -- technically should be bytea, but not used anymore
  ar_page_id        INTEGER          NULL,
  ar_parent_id      INTEGER          NULL,
  ar_sha1           TEXT         NOT NULL DEFAULT '',
  ar_comment        TEXT,
  ar_user           INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  ar_user_text      TEXT         NOT NULL,
  ar_timestamp      TIMESTAMPTZ  NOT NULL,
  ar_minor_edit     SMALLINT     NOT NULL  DEFAULT 0,
  ar_flags          TEXT,
  ar_rev_id         INTEGER,
  ar_text_id        INTEGER,
  ar_deleted        SMALLINT     NOT NULL  DEFAULT 0,
  ar_len            INTEGER          NULL,
  ar_content_model  TEXT,
  ar_content_format TEXT
);
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       INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  rd_namespace  SMALLINT NOT NULL,
  rd_title      TEXT     NOT NULL,
  rd_interwiki  TEXT     NULL,
  rd_fragment   TEXT     NULL
);
CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);


CREATE TABLE pagelinks (
  pl_from       INTEGER   NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  pl_namespace  SMALLINT  NOT NULL,
  pl_title      TEXT      NOT NULL
);
CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
CREATE INDEX pagelinks_title ON pagelinks (pl_title);

CREATE TABLE templatelinks (
  tl_from       INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  tl_namespace  SMALLINT NOT NULL,
  tl_title      TEXT     NOT NULL
);
CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
CREATE INDEX templatelinks_from          ON templatelinks (tl_from);

CREATE TABLE imagelinks (
  il_from  INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  il_to    TEXT     NOT NULL
);
CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);

CREATE TABLE categorylinks (
  cl_from           INTEGER      NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  cl_to             TEXT         NOT NULL,
  cl_sortkey        TEXT             NULL,
  cl_timestamp      TIMESTAMPTZ  NOT NULL,
  cl_sortkey_prefix TEXT         NOT NULL  DEFAULT '',
  cl_collation      TEXT         NOT NULL  DEFAULT 0,
  cl_type           TEXT         NOT NULL  DEFAULT 'page'
);
CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
CREATE INDEX cl_sortkey     ON categorylinks (cl_to, cl_sortkey, cl_from);

CREATE SEQUENCE externallinks_el_id_seq;
CREATE TABLE externallinks (
  el_id     INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'),
  el_from   INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  el_to     TEXT     NOT NULL,
  el_index  TEXT     NOT NULL
);
CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
CREATE INDEX externallinks_index   ON externallinks (el_index);

CREATE TABLE langlinks (
  ll_from    INTEGER  NOT NULL  REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  ll_lang    TEXT,
  ll_title   TEXT
);
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         INTEGER  NOT NULL  UNIQUE,
  ss_total_views    INTEGER            DEFAULT 0,
  ss_total_edits    INTEGER            DEFAULT 0,
  ss_good_articles  INTEGER            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 SEQUENCE ipblocks_ipb_id_seq;
CREATE TABLE ipblocks (
  ipb_id                INTEGER      NOT NULL  PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'),
  ipb_address           TEXT             NULL,
  ipb_user              INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  ipb_by                INTEGER      NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  ipb_by_text           TEXT         NOT NULL  DEFAULT '',
  ipb_reason            TEXT         NOT NULL,
  ipb_timestamp         TIMESTAMPTZ  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            TIMESTAMPTZ  NOT NULL,
  ipb_range_start       TEXT,
  ipb_range_end         TEXT,
  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          NULL  REFERENCES ipblocks(ipb_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED

);
CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
CREATE INDEX ipb_user    ON ipblocks (ipb_user);
CREATE INDEX ipb_range   ON ipblocks (ipb_range_start,ipb_range_end);
CREATE INDEX ipb_parent_block_id   ON ipblocks (ipb_parent_block_id);


CREATE TABLE image (
  img_name         TEXT      NOT NULL  PRIMARY KEY,
  img_size         INTEGER   NOT NULL,
  img_width        INTEGER   NOT NULL,
  img_height       INTEGER   NOT NULL,
  img_metadata     BYTEA     NOT NULL  DEFAULT '',
  img_bits         SMALLINT,
  img_media_type   TEXT,
  img_major_mime   TEXT                DEFAULT 'unknown',
  img_minor_mime   TEXT                DEFAULT 'unknown',
  img_description  TEXT      NOT NULL,
  img_user         INTEGER       NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  img_user_text    TEXT      NOT NULL,
  img_timestamp    TIMESTAMPTZ,
  img_sha1         TEXT      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          TEXT         NOT NULL,
  oi_archive_name  TEXT         NOT NULL,
  oi_size          INTEGER      NOT NULL,
  oi_width         INTEGER      NOT NULL,
  oi_height        INTEGER      NOT NULL,
  oi_bits          SMALLINT         NULL,
  oi_description   TEXT,
  oi_user          INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  oi_user_text     TEXT         NOT NULL,
  oi_timestamp     TIMESTAMPTZ      NULL,
  oi_metadata      BYTEA        NOT NULL DEFAULT '',
  oi_media_type    TEXT             NULL,
  oi_major_mime    TEXT             NULL DEFAULT 'unknown',
  oi_minor_mime    TEXT             NULL DEFAULT 'unknown',
  oi_deleted       SMALLINT     NOT NULL DEFAULT 0,
  oi_sha1          TEXT         NOT NULL DEFAULT ''
);
ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
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 SEQUENCE filearchive_fa_id_seq;
CREATE TABLE filearchive (
  fa_id                 INTEGER      NOT NULL  PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
  fa_name               TEXT         NOT NULL,
  fa_archive_name       TEXT,
  fa_storage_group      TEXT,
  fa_storage_key        TEXT,
  fa_deleted_user       INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  fa_deleted_timestamp  TIMESTAMPTZ  NOT NULL,
  fa_deleted_reason     TEXT,
  fa_size               INTEGER      NOT NULL,
  fa_width              INTEGER      NOT NULL,
  fa_height             INTEGER      NOT NULL,
  fa_metadata           BYTEA        NOT NULL  DEFAULT '',
  fa_bits               SMALLINT,
  fa_media_type         TEXT,
  fa_major_mime         TEXT                   DEFAULT 'unknown',
  fa_minor_mime         TEXT                   DEFAULT 'unknown',
  fa_description        TEXT         NOT NULL,
  fa_user               INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  fa_user_text          TEXT         NOT NULL,
  fa_timestamp          TIMESTAMPTZ,
  fa_deleted            SMALLINT     NOT NULL DEFAULT 0,
  fa_sha1               TEXT         NOT NULL DEFAULT ''
);
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 INDEX fa_sha1      ON filearchive (fa_sha1);

CREATE SEQUENCE uploadstash_us_id_seq;
CREATE TYPE media_type AS ENUM ('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE');

CREATE TABLE uploadstash (
  us_id           INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('uploadstash_us_id_seq'),
  us_user         INTEGER,
  us_key          TEXT,
  us_orig_path    TEXT,
  us_path         TEXT,
  us_props        BYTEA,
  us_source_type  TEXT,
  us_timestamp    TIMESTAMPTZ,
  us_status       TEXT,
  us_chunk_inx    INTEGER NULL,
  us_size         INTEGER,
  us_sha1         TEXT,
  us_mime         TEXT,
  us_media_type   media_type DEFAULT NULL,
  us_image_width  INTEGER,
  us_image_height INTEGER,
  us_image_bits   SMALLINT
);

CREATE INDEX us_user_idx ON uploadstash (us_user);
CREATE UNIQUE INDEX us_key_idx ON uploadstash (us_key);
CREATE INDEX us_timestamp_idx ON uploadstash (us_timestamp);


CREATE SEQUENCE recentchanges_rc_id_seq;
CREATE TABLE recentchanges (
  rc_id              INTEGER      NOT NULL  PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'),
  rc_timestamp       TIMESTAMPTZ  NOT NULL,
  rc_cur_time        TIMESTAMPTZ  NOT NULL,
  rc_user            INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  rc_user_text       TEXT         NOT NULL,
  rc_namespace       SMALLINT     NOT NULL,
  rc_title           TEXT         NOT NULL,
  rc_comment         TEXT,
  rc_minor           SMALLINT     NOT NULL  DEFAULT 0,
  rc_bot             SMALLINT     NOT NULL  DEFAULT 0,
  rc_new             SMALLINT     NOT NULL  DEFAULT 0,
  rc_cur_id          INTEGER          NULL  REFERENCES page(page_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  rc_this_oldid      INTEGER      NOT NULL,
  rc_last_oldid      INTEGER      NOT NULL,
  rc_type            SMALLINT     NOT NULL  DEFAULT 0,
  rc_patrolled       SMALLINT     NOT NULL  DEFAULT 0,
  rc_ip              CIDR,
  rc_old_len         INTEGER,
  rc_new_len         INTEGER,
  rc_deleted         SMALLINT     NOT NULL  DEFAULT 0,
  rc_logid           INTEGER      NOT NULL  DEFAULT 0,
  rc_log_type        TEXT,
  rc_log_action      TEXT,
  rc_params          TEXT
);
CREATE INDEX rc_timestamp       ON recentchanges (rc_timestamp);
CREATE INDEX rc_timestamp_bot   ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
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                   INTEGER     NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  wl_namespace              SMALLINT    NOT NULL  DEFAULT 0,
  wl_title                  TEXT        NOT NULL,
  wl_notificationtimestamp  TIMESTAMPTZ
);
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
CREATE INDEX wl_user ON watchlist (wl_user);


CREATE TABLE interwiki (
  iw_prefix  TEXT      NOT NULL  UNIQUE,
  iw_url     TEXT      NOT NULL,
  iw_local   SMALLINT  NOT NULL,
  iw_trans   SMALLINT  NOT NULL  DEFAULT 0,
  iw_api     TEXT      NOT NULL  DEFAULT '',
  iw_wikiid  TEXT      NOT NULL  DEFAULT ''
);


CREATE TABLE querycache (
  qc_type       TEXT      NOT NULL,
  qc_value      INTEGER   NOT NULL,
  qc_namespace  SMALLINT  NOT NULL,
  qc_title      TEXT      NOT NULL
);
CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);

CREATE TABLE querycache_info (
  qci_type       TEXT              UNIQUE,
  qci_timestamp  TIMESTAMPTZ NULL
);

CREATE TABLE querycachetwo (
  qcc_type          TEXT     NOT NULL,
  qcc_value         INTEGER  NOT NULL  DEFAULT 0,
  qcc_namespace     INTEGER  NOT NULL  DEFAULT 0,
  qcc_title         TEXT     NOT NULL  DEFAULT '',
  qcc_namespacetwo  INTEGER  NOT NULL  DEFAULT 0,
  qcc_titletwo      TEXT     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  TEXT                   UNIQUE,
  value    BYTEA        NOT NULL  DEFAULT '',
  exptime  TIMESTAMPTZ  NOT NULL
);
CREATE INDEX objectcacache_exptime ON objectcache (exptime);

CREATE TABLE transcache (
  tc_url       TEXT         NOT NULL  UNIQUE,
  tc_contents  TEXT         NOT NULL,
  tc_time      TIMESTAMPTZ  NOT NULL
);


CREATE SEQUENCE logging_log_id_seq;
CREATE TABLE logging (
  log_id          INTEGER      NOT NULL  PRIMARY KEY DEFAULT nextval('logging_log_id_seq'),
  log_type        TEXT         NOT NULL,
  log_action      TEXT         NOT NULL,
  log_timestamp   TIMESTAMPTZ  NOT NULL,
  log_user        INTEGER                REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  log_namespace   SMALLINT     NOT NULL,
  log_title       TEXT         NOT NULL,
  log_comment     TEXT,
  log_params      TEXT,
  log_deleted     SMALLINT     NOT NULL DEFAULT 0,
  log_user_text   TEXT         NOT NULL DEFAULT '',
  log_page        INTEGER
);
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 logging_times ON logging (log_timestamp);
CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp);
CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp);

CREATE TABLE log_search (
  ls_field   TEXT     NOT NULL,
  ls_value   TEXT     NOT NULL,
  ls_log_id  INTEGER  NOT NULL DEFAULT 0,
  PRIMARY KEY (ls_field,ls_value,ls_log_id)
);
CREATE INDEX ls_log_id ON log_search (ls_log_id);


CREATE SEQUENCE job_job_id_seq;
CREATE TABLE job (
  job_id              INTEGER   NOT NULL  PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
  job_cmd             TEXT      NOT NULL,
  job_namespace       SMALLINT  NOT NULL,
  job_title           TEXT      NOT NULL,
  job_timestamp       TIMESTAMPTZ,
  job_params          TEXT      NOT NULL,
  job_random          INTEGER   NOT NULL DEFAULT 0,
  job_attempts        INTEGER   NOT NULL DEFAULT 0,
  job_token           TEXT      NOT NULL DEFAULT '',
  job_token_timestamp TIMESTAMPTZ,
  job_sha1            TEXT NOT NULL DEFAULT ''
);
CREATE INDEX job_sha1 ON job (job_sha1);
CREATE INDEX job_cmd_token ON job (job_cmd, job_token, job_random);
CREATE INDEX job_cmd_token_id ON job (job_cmd, job_token, job_id);
CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
CREATE INDEX job_timestamp_idx ON job (job_timestamp);

-- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
-- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector.
-- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.

ALTER TABLE page ADD titlevector tsvector;
CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
  NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' '));
ELSIF NEW.page_title != OLD.page_title THEN
  NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' '));
END IF;
RETURN NEW;
END;
$mw$;

CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
  FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();


ALTER TABLE pagecontent ADD textvector tsvector;
CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
  NEW.textvector = to_tsvector(NEW.old_text);
ELSIF NEW.old_text != OLD.old_text THEN
  NEW.textvector := to_tsvector(NEW.old_text);
END IF;
RETURN NEW;
END;
$mw$;

CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
  FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();

-- These are added by the setup script due to version compatibility issues
-- If using 8.1, we switch from "gin" to "gist"

CREATE INDEX ts2_page_title ON page USING gin(titlevector);
CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);

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$;

-- This table is not used unless profiling is turned on
CREATE TABLE profiling (
  pf_count   INTEGER         NOT NULL DEFAULT 0,
  pf_time    FLOAT           NOT NULL DEFAULT 0,
  pf_memory  FLOAT           NOT NULL DEFAULT 0,
  pf_name    TEXT            NOT NULL,
  pf_server  TEXT            NULL
);
CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);

CREATE TABLE protected_titles (
  pt_namespace   SMALLINT    NOT NULL,
  pt_title       TEXT        NOT NULL,
  pt_user        INTEGER         NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  pt_reason      TEXT            NULL,
  pt_timestamp   TIMESTAMPTZ NOT NULL,
  pt_expiry      TIMESTAMPTZ     NULL,
  pt_create_perm TEXT        NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);


CREATE TABLE updatelog (
  ul_key TEXT NOT NULL PRIMARY KEY,
  ul_value TEXT
);


CREATE SEQUENCE category_cat_id_seq;
CREATE TABLE category (
  cat_id       INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('category_cat_id_seq'),
  cat_title    TEXT     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);

CREATE TABLE change_tag (
  ct_rc_id   INTEGER      NULL,
  ct_log_id  INTEGER      NULL,
  ct_rev_id  INTEGER      NULL,
  ct_tag     TEXT     NOT NULL,
  ct_params  TEXT         NULL
);
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);
CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);

CREATE TABLE tag_summary (
  ts_rc_id   INTEGER     NULL,
  ts_log_id  INTEGER     NULL,
  ts_rev_id  INTEGER     NULL,
  ts_tags    TEXT    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 TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE user_properties (
  up_user     INTEGER      NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  up_property TEXT     NOT NULL,
  up_value    TEXT
);
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 l10n_cache (
  lc_lang   TEXT  NOT NULL,
  lc_key    TEXT  NOT NULL,
  lc_value  TEXT  NOT NULL
);
CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);

CREATE TABLE iwlinks (
  iwl_from    INTEGER  NOT NULL DEFAULT 0,
  iwl_prefix  TEXT     NOT NULL DEFAULT '',
  iwl_title   TEXT     NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title);
CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from);
CREATE UNIQUE INDEX iwl_prefix_from_title ON iwlinks (iwl_prefix, iwl_from, iwl_title);

CREATE TABLE msg_resource (
  mr_resource   TEXT         NOT NULL,
  mr_lang       TEXT         NOT NULL,
  mr_blob       TEXT         NOT NULL,
  mr_timestamp  TIMESTAMPTZ  NOT NULL
);
CREATE UNIQUE INDEX mr_resource_lang ON msg_resource (mr_resource, mr_lang);

CREATE TABLE msg_resource_links (
  mrl_resource  TEXT  NOT NULL,
  mrl_message   TEXT  NOT NULL
);
CREATE UNIQUE INDEX mrl_message_resource ON msg_resource_links (mrl_message, mrl_resource);

CREATE TABLE module_deps (
  md_module  TEXT  NOT NULL,
  md_skin    TEXT  NOT NULL,
  md_deps    TEXT  NOT NULL
);
CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);

CREATE SEQUENCE sites_site_id_seq;
CREATE TABLE sites (
  site_id           INTEGER     NOT NULL    PRIMARY KEY DEFAULT nextval('sites_site_id_seq'),
  site_global_key   TEXT        NOT NULL,
  site_type         TEXT        NOT NULL,
  site_group        TEXT        NOT NULL,
  site_source       TEXT        NOT NULL,
  site_language     TEXT        NOT NULL,
  site_protocol     TEXT        NOT NULL,
  site_domain       TEXT        NOT NULL,
  site_data         TEXT        NOT NULL,
  site_forward      SMALLINT    NOT NULL,
  site_config       TEXT        NOT NULL
);
CREATE UNIQUE INDEX site_global_key ON sites (site_global_key);
CREATE INDEX site_type ON sites (site_type);
CREATE INDEX site_group ON sites (site_group);
CREATE INDEX site_source ON sites (site_source);
CREATE INDEX site_language ON sites (site_language);
CREATE INDEX site_protocol ON sites (site_protocol);
CREATE INDEX site_domain ON sites (site_domain);
CREATE INDEX site_forward ON sites (site_forward);

CREATE TABLE site_identifiers (
  si_site   INTEGER NOT NULL,
  si_type   TEXT    NOT NULL,
  si_key    TEXT    NOT NULL
);
CREATE UNIQUE INDEX si_type_key ON site_identifiers (si_type, si_key);
CREATE INDEX si_site ON site_identifiers (si_site);
CREATE INDEX si_key ON site_identifiers (si_key);