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
|
-- $Id: psql-1_3_12.sql,v 1.5 2006/05/18 06:08:33 rurban Exp $
\set QUIET
-- Init the database with:
-- $ /usr/sbin/createdb phpwiki
-- $ /usr/sbin/createuser -S -R -d phpwiki # (see httpd_user below)
-- $ /usr/bin/psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
-- $ /usr/bin/psql phpwiki < psql-initialize.sql
--================================================================
-- Prefix for table names.
--
-- You should set this to the same value you specified for
-- DATABASE_PREFIX in config.ini
\set prefix ''
--================================================================
-- Which postgres user gets access to the tables?
--
-- You should set this to the name of the postgres
-- user who will be accessing the tables.
-- See DATABASE_DSN in config.ini
--
-- Commonly, connections from php are made under
-- the user name of 'nobody', 'apache' or 'www'.
\set httpd_user 'phpwiki'
--================================================================
--
-- Don't modify below this point unless you know what you are doing.
--
--================================================================
\set page_tbl :prefix 'page'
\set page_id_seq :prefix 'page_id_seq'
\set page_id_idx :prefix 'page_id_idx'
\set page_name_idx :prefix 'page_name_idx'
\set version_tbl :prefix 'version'
\set vers_id_idx :prefix 'vers_id_idx'
\set vers_mtime_idx :prefix 'vers_mtime_idx'
\set recent_tbl :prefix 'recent'
\set recent_id_idx :prefix 'recent_id_idx'
\set nonempty_tbl :prefix 'nonempty'
\set nonmt_id_idx :prefix 'nonmt_id_idx'
\set link_tbl :prefix 'link'
\set link_from_idx :prefix 'link_from_idx'
\set link_to_idx :prefix 'link_to_idx'
\set relation_idx :prefix 'relation_idx'
\set session_tbl :prefix 'session'
\set sess_id_idx :prefix 'sess_id_idx'
\set sess_date_idx :prefix 'sess_date_idx'
\set sess_ip_idx :prefix 'sess_ip_idx'
\set pref_tbl :prefix 'pref'
\set pref_id_idx :prefix 'pref_id_idx'
--\set user_tbl :prefix 'users'
--\set user_id_idx :prefix 'users_id_idx'
\set member_tbl :prefix 'member'
\set member_id_idx :prefix 'member_id_idx'
\set member_group_idx :prefix 'member_group_idx'
\set rating_tbl :prefix 'rating'
\set rating_id_idx :prefix 'rating_id_idx'
\set accesslog_tbl :prefix 'accesslog'
\set accesslog_time_idx :prefix 'log_time_idx'
\set accesslog_host_idx :prefix 'log_host_idx'
--================================================================
\echo schema enhancements
ALTER TABLE :page_tbl
ALTER COLUMN id TYPE SERIAL /* PRIMARY KEY */,
ALTER COLUMN pagename TYPE VARCHAR(100),
ALTER COLUMN pagename SET NOT NULL,
ADD UNIQUE(pagename),
ADD CHECK (pagename <> '');
ALTER TABLE :version_tbl
ALTER COLUMN id TYPE INT4,
ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
ALTER TABLE :nonempty_tbl
ALTER COLUMN id TYPE INT4,
ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
\echo Creating experimental page views (not yet used)
-- nonempty versiondata
CREATE VIEW existing_page AS
SELECT * FROM :page_tbl P INNER JOIN :nonempty_tbl N USING (id);
-- latest page version
CREATE VIEW curr_page AS
SELECT P.id,P.pagename,P.hits,P.pagedata,P.cached_html,
V.version,V.mtime,V.minor_edit,V.content,V.versiondata
FROM :page_tbl P
JOIN :version_tbl V USING (id)
JOIN :recent_tbl R ON (V.id=R.id AND V.version=R.latestversion);
ALTER TABLE :link_tbl
ALTER COLUMN linkfrom TYPE INT4,
ALTER COLUMN linkto TYPE INT4,
ADD COLUMN relation INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE,
ADD FOREIGN KEY (linkfrom) REFERENCES :page_tbl (id) ON DELETE CASCADE,
ADD FOREIGN KEY (linkto) REFERENCES :page_tbl (id) ON DELETE CASCADE;
CREATE INDEX :relation_idx ON :link_tbl (relation);
ALTER TABLE :rating_tbl
ALTER COLUMN raterpage TYPE INT8,
ALTER COLUMN rateepage TYPE INT8,
ADD FOREIGN KEY (raterpage) REFERENCES :page_tbl (id) ON DELETE CASCADE,
ADD FOREIGN KEY (rateepage) REFERENCES :page_tbl (id) ON DELETE CASCADE;
ALTER TABLE :member_tbl
ALTER COLUMN userid TYPE CHAR(48),
ALTER COLUMN userid SET NOT NULL,
ADD FOREIGN KEY (userid) REFERENCES :pref_tbl;
--================================================================
\echo add tsearch2 fulltextsearch extension
-- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
-- At first init it for the database:
-- example of ISpell dictionary
-- UPDATE pg_ts_dict SET dict_initoption='DictFile="/usr/local/share/ispell/russian.dict",
-- AffFile ="/usr/local/share/ispell/russian.aff", StopFile="/usr/local/share/ispell/russian.stop"'
-- WHERE dict_name='ispell_template';
-- example of synonym dict
-- UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
VACUUM FULL ANALYZE;
CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
VACUUM FULL ANALYZE;
CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
--================================================================
\echo Initializing stored procedures
CREATE OR REPLACE FUNCTION update_recent (id INT4, version INT4)
RETURNS void AS '
DELETE FROM recent WHERE id=$1;
INSERT INTO recent (id, latestversion, latestmajor, latestminor)
SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0 THEN version END),
MAX(CASE WHEN minor_edit<>0 THEN version END)
FROM version WHERE id=$2 GROUP BY id;
DELETE FROM nonempty WHERE id=$1;
INSERT INTO nonempty (id)
SELECT recent.id
FROM recent, version
WHERE recent.id=version.id
AND version=latestversion
AND content<>''''
AND recent.id=$1;
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION prepare_rename_page (oldid INT4, newid INT4)
RETURNS void AS '
DELETE FROM page WHERE id=$2;
DELETE FROM version WHERE id=$2;
DELETE FROM recent WHERE id=$2;
DELETE FROM nonempty WHERE id=$2;
-- We have to fix all referring tables to the old id
UPDATE link SET linkfrom=$1 WHERE linkfrom=$2;
UPDATE link SET linkto=$1 WHERE linkto=$2;
' LANGUAGE sql;
|
