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
|
-- 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';
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,
user_password_expires TIMESTAMPTZ NULL
);
ALTER SEQUENCE user_user_id_seq OWNED BY mwuser.user_id;
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 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_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_links_updated TIMESTAMPTZ NULL,
page_latest INTEGER NOT NULL, -- FK?
page_len INTEGER NOT NULL,
page_content_model TEXT,
page_lang TEXT DEFAULT NULL
);
ALTER SEQUENCE page_page_id_seq OWNED BY page.page_id;
CREATE UNIQUE INDEX name_title 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 ON page (page_random);
CREATE INDEX page_len ON page (page_len);
CREATE INDEX page_redirect_namespace_len ON page (page_is_redirect, page_namespace, 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_comment_id INTEGER NOT NULL DEFAULT 0,
rev_actor INTEGER NOT NULL DEFAULT 0,
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 ''
);
ALTER SEQUENCE revision_rev_id_seq OWNED BY revision.rev_id;
CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
CREATE INDEX rev_actor_timestamp ON revision (rev_actor,rev_timestamp,rev_id);
CREATE INDEX rev_page_actor_timestamp ON revision (rev_page,rev_actor,rev_timestamp);
-- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
-- 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 text 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 text
FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
CREATE INDEX ts2_page_title ON page USING gin(titlevector);
CREATE INDEX ts2_page_text ON text USING gin(textvector);
|
