summaryrefslogtreecommitdiff
path: root/maintenance/ora/tables.sql
blob: 6d4b8ed50396cf2867a93684ca7c07a6a4acc71c (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
-- 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 Oracle version (based on PostgreSQL schema).
-- For information about each table, please see the notes in maintenance/tables.sql

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,
  user_name                 VARCHAR(255)     NOT NULL  UNIQUE,
  user_real_name            CLOB,
  user_password             CLOB,
  user_newpassword          CLOB,
  user_newpass_time         TIMESTAMP WITH TIME ZONE,
  user_token                CHAR(32),
  user_email                CLOB,
  user_email_token          CHAR(32),
  user_email_token_expires  TIMESTAMP WITH TIME ZONE,
  user_email_authenticated  TIMESTAMP WITH TIME ZONE,
  user_options              CLOB,
  user_touched              TIMESTAMP WITH TIME ZONE,
  user_registration         TIMESTAMP WITH TIME ZONE,
  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 (user_user_id_seq.nextval,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '', current_timestamp, current_timestamp, 0);

CREATE TABLE user_groups (
  ug_user   INTEGER      NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
  ug_group  CHAR(16)     NOT NULL
);
CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);

CREATE TABLE user_newtalk (
  user_id  INTEGER NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
  user_ip  VARCHAR(40)        NULL
);
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,
  page_namespace     SMALLINT       NOT NULL,
  page_title         VARCHAR(255)           NOT NULL,
  page_restrictions  CLOB,
  page_counter       INTEGER         DEFAULT 0 NOT NULL,
  page_is_redirect   CHAR           DEFAULT 0 NOT NULL,
  page_is_new        CHAR           DEFAULT 0 NOT NULL,
  page_random        NUMERIC(15,14) NOT NULL,
  page_touched       TIMESTAMP WITH TIME ZONE,
  page_latest        INTEGER        NOT NULL, -- FK?
  page_len           INTEGER        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 TRIGGER page_set_random BEFORE INSERT ON page
        FOR EACH ROW WHEN (new.page_random IS NULL)
        BEGIN
                SELECT dbms_random.value INTO :new.page_random FROM dual;
        END;
/

CREATE SEQUENCE rev_rev_id_val;
CREATE TABLE revision (
  rev_id          INTEGER      NOT NULL PRIMARY KEY,
  rev_page        INTEGER          NULL  REFERENCES page (page_id) ON DELETE CASCADE,
  rev_text_id     INTEGER          NULL, -- FK
  rev_comment     CLOB,
  rev_user        INTEGER      NOT NULL  REFERENCES mwuser(user_id),
  rev_user_text   VARCHAR(255)         NOT NULL,
  rev_timestamp   TIMESTAMP WITH TIME ZONE  NOT NULL,
  rev_minor_edit  CHAR         DEFAULT '0' NOT NULL,
  rev_deleted     CHAR         DEFAULT '0' NOT NULL,
  rev_len         INTEGER          NULL,
  rev_parent_id   INTEGER      	   DEFAULT NULL
);
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_val;
CREATE TABLE pagecontent ( -- replaces reserved word 'text'
  old_id     INTEGER  NOT NULL PRIMARY KEY,
  old_text   CLOB,
  old_flags  CLOB
);


CREATE SEQUENCE pr_id_val;
CREATE TABLE page_restrictions (
  pr_id      INTEGER      NOT NULL UNIQUE,
  pr_page    INTEGER          NULL  REFERENCES page (page_id) ON DELETE CASCADE,
  pr_type    VARCHAR(255)         NOT NULL,
  pr_level   VARCHAR(255)         NOT NULL,
  pr_cascade SMALLINT     NOT NULL,
  pr_user    INTEGER          NULL,
  pr_expiry  TIMESTAMP WITH TIME ZONE      NULL
);
ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);

CREATE TABLE archive (
  ar_namespace   SMALLINT     NOT NULL,
  ar_title       VARCHAR(255)         NOT NULL,
  ar_text        CLOB,
  ar_comment     CLOB,
  ar_user        INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
  ar_user_text   CLOB         NOT NULL,
  ar_timestamp   TIMESTAMP WITH TIME ZONE  NOT NULL,
  ar_minor_edit  CHAR         DEFAULT '0' NOT NULL,
  ar_flags       CLOB,
  ar_rev_id      INTEGER,
  ar_text_id     INTEGER,
  ar_deleted     INTEGER      DEFAULT '0' NOT NULL
);
CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);

CREATE TABLE redirect (
  rd_from       INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE,
  rd_namespace  SMALLINT NOT NULL,
  rd_title      VARCHAR(255)     NOT 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,
  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       INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE,
  tl_namespace  INTEGER     NOT NULL,
  tl_title      VARCHAR(255)     NOT NULL
);
CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);

CREATE TABLE imagelinks (
  il_from  INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE,
  il_to    VARCHAR(255)     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,
  cl_to         VARCHAR(255)         NOT NULL,
  cl_sortkey    VARCHAR(86),
  cl_timestamp  TIMESTAMP WITH TIME ZONE  NOT NULL
);
CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
CREATE INDEX cl_sortkey     ON categorylinks (cl_to, cl_sortkey);

CREATE TABLE externallinks (
  el_from   INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE,
  el_to     VARCHAR(2048) NOT NULL,
  el_index  CLOB     NOT NULL
);
-- XXX CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
-- XXX CREATE INDEX externallinks_index   ON externallinks (el_index);

CREATE TABLE langlinks (
  ll_from    INTEGER  NOT NULL  REFERENCES page (page_id) ON DELETE CASCADE,
  ll_lang    VARCHAR(10),
  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         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_admins         INTEGER            DEFAULT -1,
  ss_images         INTEGER            DEFAULT 0
);

CREATE TABLE hitcounter (
  hc_id  INTEGER  NOT NULL
);


CREATE SEQUENCE ipblocks_ipb_id_val;
CREATE TABLE ipblocks (
  ipb_id                INTEGER      NOT NULL PRIMARY KEY,
  ipb_address           VARCHAR(255)     NULL,
  ipb_user              INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
  ipb_by                INTEGER      NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
  ipb_reason            VARCHAR(255)         NOT NULL,
  ipb_timestamp         TIMESTAMP WITH TIME ZONE  NOT NULL,
  ipb_auto              CHAR         DEFAULT '0' NOT NULL,
  ipb_anon_only         CHAR         DEFAULT '0' NOT NULL,
  ipb_create_account    CHAR         DEFAULT '1' NOT NULL,
  ipb_enable_autoblock  CHAR         DEFAULT '1' NOT NULL,
  ipb_expiry            TIMESTAMP WITH TIME ZONE  NOT NULL,
  ipb_range_start       CHAR(8),
  ipb_range_end         CHAR(8),
  ipb_deleted           INTEGER      DEFAULT '0' NOT 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         INTEGER   NOT NULL,
  img_width        INTEGER   NOT NULL,
  img_height       INTEGER   NOT NULL,
  img_metadata     CLOB,
  img_bits         SMALLINT,
  img_media_type   CLOB,
  img_major_mime   CLOB                DEFAULT 'unknown',
  img_minor_mime   CLOB                DEFAULT 'unknown',
  img_description  CLOB,
  img_user         INTEGER       NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
  img_user_text    CLOB      NOT NULL,
  img_timestamp    TIMESTAMP WITH TIME ZONE
);
CREATE INDEX img_size_idx      ON image (img_size);
CREATE INDEX img_timestamp_idx ON image (img_timestamp);

CREATE TABLE oldimage (
  oi_name          VARCHAR(255)         NOT NULL  REFERENCES image(img_name),
  oi_archive_name  VARCHAR(255),
  oi_size          INTEGER      NOT NULL,
  oi_width         INTEGER      NOT NULL,
  oi_height        INTEGER      NOT NULL,
  oi_bits          SMALLINT     NOT NULL,
  oi_description   CLOB,
  oi_user          INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
  oi_user_text     CLOB         NOT NULL,
  oi_timestamp     TIMESTAMP WITH TIME ZONE  NOT NULL,
  oi_metadata      CLOB, 	 
  oi_media_type    VARCHAR(10) DEFAULT NULL,
  oi_major_mime    VARCHAR(11) DEFAULT 'unknown',
  oi_minor_mime    VARCHAR(32) DEFAULT 'unknown',
  oi_deleted       INTEGER DEFAULT 0 NOT NULL
);
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 SEQUENCE filearchive_fa_id_seq;
CREATE TABLE filearchive (
  fa_id                 INTEGER       NOT NULL  PRIMARY KEY,
  fa_name               VARCHAR(255)         NOT NULL,
  fa_archive_name       VARCHAR(255),
  fa_storage_group      VARCHAR(16),
  fa_storage_key        CHAR(64),
  fa_deleted_user       INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
  fa_deleted_timestamp  TIMESTAMP WITH TIME ZONE  NOT NULL,
  fa_deleted_reason     CLOB,
  fa_size               SMALLINT     NOT NULL,
  fa_width              SMALLINT     NOT NULL,
  fa_height             SMALLINT     NOT NULL,
  fa_metadata           CLOB,
  fa_bits               SMALLINT,
  fa_media_type         CLOB,
  fa_major_mime         CLOB                   DEFAULT 'unknown',
  fa_minor_mime         CLOB                   DEFAULT 'unknown',
  fa_description        CLOB         NOT NULL,
  fa_user               INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
  fa_user_text          CLOB         NOT NULL,
  fa_timestamp          TIMESTAMP WITH TIME ZONE,
  fa_deleted            INTEGER      DEFAULT '0' NOT NULL
);
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 SEQUENCE rc_rc_id_seq;
CREATE TABLE recentchanges (
  rc_id              INTEGER      NOT NULL PRIMARY KEY,
  rc_timestamp       TIMESTAMP WITH TIME ZONE  NOT NULL,
  rc_cur_time        TIMESTAMP WITH TIME ZONE  NOT NULL,
  rc_user            INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
  rc_user_text       CLOB         NOT NULL,
  rc_namespace       SMALLINT     NOT NULL,
  rc_title           VARCHAR(255)         NOT NULL,
  rc_comment         VARCHAR(255),
  rc_minor           CHAR         DEFAULT '0' NOT NULL,
  rc_bot             CHAR         DEFAULT '0' NOT NULL,
  rc_new             CHAR         DEFAULT '0' NOT NULL,
  rc_cur_id          INTEGER          NULL  REFERENCES page(page_id) ON DELETE SET NULL,
  rc_this_oldid      INTEGER      NOT NULL,
  rc_last_oldid      INTEGER      NOT NULL,
  rc_type            CHAR         DEFAULT '0' NOT NULL,
  rc_moved_to_ns     SMALLINT,
  rc_moved_to_title  CLOB,
  rc_patrolled       CHAR         DEFAULT '0' NOT NULL,
  rc_ip              VARCHAR(15),
  rc_old_len         INTEGER,
  rc_new_len         INTEGER,
  rc_deleted         INTEGER      DEFAULT '0' NOT NULL,
  rc_logid           INTEGER      DEFAULT '0' NOT NULL,
  rc_log_type      	 CLOB,
  rc_log_action      CLOB,
  rc_params      	 CLOB
);
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                   INTEGER     NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
  wl_namespace              SMALLINT    DEFAULT 0 NOT NULL,
  wl_title                  VARCHAR(255)        NOT NULL,
  wl_notificationtimestamp  TIMESTAMP WITH TIME ZONE
);
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);


CREATE TABLE math (
  math_inputhash              VARCHAR(16)      NOT NULL  UNIQUE,
  math_outputhash             VARCHAR(16)      NOT NULL,
  math_html_conservativeness  SMALLINT  NOT NULL,
  math_html                   CLOB,
  math_mathml                 CLOB
);


CREATE TABLE interwiki (
  iw_prefix  VARCHAR(32)   NOT NULL  UNIQUE,
  iw_url     VARCHAR(127)  NOT NULL,
  iw_local   CHAR  NOT NULL,
  iw_trans   CHAR  DEFAULT '0' NOT NULL
);

CREATE TABLE querycache (
  qc_type       CHAR(32)      NOT NULL,
  qc_value      SMALLINT  NOT NULL,
  qc_namespace  SMALLINT  NOT NULL,
  qc_title      CHAR(255)      NOT NULL
);
CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);

CREATE TABLE querycache_info (
  qci_type       VARCHAR(32)              UNIQUE,
  qci_timestamp  TIMESTAMP WITH TIME ZONE NULL
);

CREATE TABLE querycachetwo (
  qcc_type          CHAR(32)     NOT NULL,
  qcc_value         SMALLINT DEFAULT 0 NOT NULL,
  qcc_namespace     INTEGER  DEFAULT 0 NOT NULL,
  qcc_title         CHAR(255)     DEFAULT '' NOT NULL,
  qcc_namespacetwo  INTEGER  DEFAULT 0 NOT NULL,
  qcc_titletwo      CHAR(255)     DEFAULT '' NOT NULL
);
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  CHAR(255)              UNIQUE,
  value    BLOB,
  exptime  TIMESTAMP WITH TIME ZONE  NOT NULL
);
CREATE INDEX objectcacache_exptime ON objectcache (exptime);

CREATE TABLE transcache (
  tc_url       VARCHAR(255)         NOT NULL  UNIQUE,
  tc_contents  CLOB         NOT NULL,
  tc_time      TIMESTAMP WITH TIME ZONE  NOT NULL
);


CREATE SEQUENCE log_log_id_seq;
CREATE TABLE logging (
  log_type        VARCHAR(10)         NOT NULL,
  log_action      VARCHAR(10)         NOT NULL,
  log_timestamp   TIMESTAMP WITH TIME ZONE  NOT NULL,
  log_user        INTEGER                REFERENCES mwuser(user_id) ON DELETE SET NULL,
  log_namespace   SMALLINT     NOT NULL,
  log_title       VARCHAR(255)         NOT NULL,
  log_comment     VARCHAR(255),
  log_params      CLOB,
  log_deleted     INTEGER      DEFAULT '0' NOT NULL,
  log_id          INTEGER      NOT NULL PRIMARY KEY
);
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 SEQUENCE trackbacks_tb_id_seq;
CREATE TABLE trackbacks (
  tb_id     INTEGER   NOT NULL PRIMARY KEY,
  tb_page   INTEGER            REFERENCES page(page_id) ON DELETE CASCADE,
  tb_title  VARCHAR(255)     NOT NULL,
  tb_url    VARCHAR(255)     NOT NULL,
  tb_ex     CLOB,
  tb_name   VARCHAR(255) 
);
CREATE INDEX trackback_page ON trackbacks (tb_page);

CREATE SEQUENCE job_job_id_seq;
CREATE TABLE job (
  job_id         INTEGER   NOT NULL PRIMARY KEY,
  job_cmd        VARCHAR(255)      NOT NULL,
  job_namespace  SMALLINT  NOT NULL,
  job_title      VARCHAR(255)      NOT NULL,
  job_params     CLOB      NOT NULL
);
CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);

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

CREATE TABLE searchindex (
  si_page	INTEGER UNIQUE NOT NULL,
  si_title	VARCHAR(255) DEFAULT '' NOT NULL,
  si_text	CLOB NOT NULL
);


CREATE INDEX si_title_idx ON searchindex(si_title) INDEXTYPE IS ctxsys.context;
CREATE INDEX si_text_idx ON searchindex(si_text) INDEXTYPE IS ctxsys.context;