2 -- for the regression suite
5 \set httpd_user 'wikiuser'
7 \set page_tbl :prefix 'page'
8 \set page_id_seq :prefix 'page_id_seq'
9 \set page_id_idx :prefix 'page_id_idx'
10 \set page_name_idx :prefix 'page_name_idx'
12 \set version_tbl :prefix 'version'
13 \set vers_id_idx :prefix 'vers_id_idx'
14 \set vers_mtime_idx :prefix 'vers_mtime_idx'
16 \set recent_tbl :prefix 'recent'
17 \set recent_id_idx :prefix 'recent_id_idx'
18 \set recent_lv_idx :prefix 'recent_lv_idx'
20 \set nonempty_tbl :prefix 'nonempty'
21 \set nonmt_id_idx :prefix 'nonmt_id_idx'
23 \set link_tbl :prefix 'link'
24 \set link_from_idx :prefix 'link_from_idx'
25 \set link_to_idx :prefix 'link_to_idx'
26 \set relation_idx :prefix 'relation_idx'
28 \set pagedata_tbl :prefix 'pagedata'
29 \set pagedata_id_idx :prefix 'pagedata_id_idx'
30 \set versiondata_tbl :prefix 'versiondata'
31 \set pageperm_tbl :prefix 'pageperm'
32 \set pageperm_id_idx :prefix 'pageperm_id_idx'
33 \set pageperm_access_idx :prefix 'pageperm_access_idx'
34 \set existing_page_view :prefix 'existing_page'
35 \set curr_page_view :prefix 'curr_page'
37 \set session_tbl :prefix 'session'
38 \set sess_id_idx :prefix 'sess_id_idx'
39 \set sess_date_idx :prefix 'sess_date_idx'
40 \set sess_ip_idx :prefix 'sess_ip_idx'
42 \set pref_tbl :prefix 'pref'
43 \set pref_id_idx :prefix 'pref_id_idx'
44 \set pref_pkey :prefix 'pref_pkey'
46 --\set user_tbl :prefix 'users'
47 --\set user_id_idx :prefix 'users_id_idx'
48 \set member_tbl :prefix 'member'
49 \set member_id_idx :prefix 'member_id_idx'
50 \set member_group_idx :prefix 'member_group_idx'
52 \set rating_tbl :prefix 'rating'
53 \set rating_id_idx :prefix 'rating_id_idx'
55 \set accesslog_tbl :prefix 'accesslog'
56 \set accesslog_time_idx :prefix 'log_time_idx'
57 \set accesslog_host_idx :prefix 'log_host_idx'
59 \set update_recent_fn :prefix 'update_recent'
60 \set prepare_rename_fn :prefix 'prepare_rename_page'
62 \echo Dropping all test relations
64 DROP TABLE :version_tbl CASCADE;
65 DROP TABLE :recent_tbl CASCADE;
66 DROP TABLE :nonempty_tbl CASCADE;
67 DROP TABLE :link_tbl CASCADE;
68 DROP TABLE :rating_tbl CASCADE;
70 DROP TABLE :pagedata_tbl CASCADE;
71 DROP TABLE :versiondata_tbl CASCADE;
72 DROP TABLE :pageperm_tbl CASCADE;
74 DROP VIEW :existing_page_view;
75 DROP VIEW :curr_page_view;
77 DROP TABLE :page_tbl CASCADE;
78 --DROP SEQUENCE :page_id_seq;
80 DROP TABLE :member_tbl CASCADE;
81 DROP TABLE :pref_tbl CASCADE;
82 DROP TABLE :session_tbl CASCADE;
83 DROP TABLE :accesslog_tbl CASCADE;
85 DROP FUNCTION :update_recent_fn (INT4, INT4);
86 DROP FUNCTION :prepare_rename_fn (INT4, INT4);
88 ------------------------------------------------------------
90 \echo Creating :page_tbl
91 CREATE TABLE :page_tbl (
92 id SERIAL PRIMARY KEY,
93 pagename VARCHAR(100) NOT NULL UNIQUE CHECK (pagename <> ''),
94 hits INT4 NOT NULL DEFAULT 0,
95 pagedata TEXT NOT NULL DEFAULT '',
96 cached_html bytea DEFAULT ''
98 -- CREATE UNIQUE INDEX :page_id_idx ON :page_tbl (id);
99 -- CREATE UNIQUE INDEX :page_name_idx ON :page_tbl (pagename);
101 -- we use 0 <=> global_data to satisfy the relation = 0 constraint
102 INSERT INTO :page_tbl VALUES (0,'global_data',0,'','');
104 \echo Creating :version_tbl
105 CREATE TABLE :version_tbl (
106 id INT4 REFERENCES :page_tbl,
107 version INT4 NOT NULL,
109 -- FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
110 minor_edit INT2 DEFAULT 0,
111 -- use bytea instead?
112 content TEXT NOT NULL DEFAULT '',
113 versiondata TEXT NOT NULL DEFAULT ''
115 CREATE UNIQUE INDEX :vers_id_idx ON :version_tbl (id, version);
116 CREATE INDEX :vers_mtime_idx ON :version_tbl (mtime);
117 -- deletion order: version, recent, nonempty
119 \echo Creating :recent_tbl
120 CREATE TABLE :recent_tbl (
121 id INT4 REFERENCES :page_tbl,
125 FOREIGN KEY (id, latestversion) REFERENCES :version_tbl (id, version),
126 CHECK (latestminor >= latestmajor)
128 CREATE UNIQUE INDEX :recent_id_idx ON :recent_tbl (id);
129 CREATE INDEX :recent_lv_idx ON :recent_tbl (latestversion);
131 \echo Creating :nonempty_tbl
132 CREATE TABLE :nonempty_tbl (
133 id INT4 NOT NULL REFERENCES :page_tbl
135 CREATE UNIQUE INDEX :nonmt_id_idx ON :nonempty_tbl (id);
137 \echo Creating experimental pagedata (not yet used)
138 CREATE TABLE :pagedata_tbl (
139 id INT4 NOT NULL REFERENCES :page_tbl,
142 rest TEXT NOT NULL DEFAULT ''
144 CREATE INDEX :pagedata_id_idx ON pagedata (id);
146 \echo Creating experimental versiondata (not yet used)
147 CREATE TABLE :versiondata_tbl (
149 version INT4 NOT NULL,
150 markup INT2 DEFAULT 2,
152 author_id VARCHAR(48),
153 pagetype VARCHAR(20) DEFAULT 'wikitext',
154 rest TEXT NOT NULL DEFAULT '',
155 FOREIGN KEY (id, version) REFERENCES :version_tbl (id, version)
157 \echo Creating experimental pageperm (not yet used)
158 CREATE TABLE :pageperm_tbl (
159 id INT4 NOT NULL REFERENCES :page_tbl(id),
160 -- view,edit,create,list,remove,change,dump
161 access CHAR(12) NOT NULL,
162 groupname VARCHAR(48),
165 CREATE INDEX :pageperm_id_idx ON pageperm (id);
166 CREATE INDEX :pageperm_access_idx ON pageperm (access);
168 \echo Creating experimental page views (not yet used)
170 -- nonempty versiondata
171 CREATE VIEW :existing_page_view AS
172 SELECT * FROM :page_tbl P INNER JOIN :nonempty_tbl N USING (id);
174 -- latest page version
175 CREATE VIEW :curr_page_view AS
176 SELECT P.id,P.pagename,P.hits,P.pagedata,P.cached_html,
177 V.version,V.mtime,V.minor_edit,V.content,V.versiondata
179 JOIN :version_tbl V USING (id)
180 JOIN :recent_tbl R ON (V.id=R.id AND V.version=R.latestversion);
182 \echo Creating :link_tbl
183 CREATE TABLE :link_tbl (
184 linkfrom INT4 NOT NULL REFERENCES :page_tbl,
185 linkto INT4 NOT NULL REFERENCES :page_tbl,
188 CREATE INDEX :link_from_idx ON :link_tbl (linkfrom);
189 CREATE INDEX :link_to_idx ON :link_tbl (linkto);
190 CREATE INDEX :relation_idx ON :link_tbl (relation);
192 -- ALTER TABLE link DROP CONSTRAINT link_relation_fkey;
194 -- if you plan to use the wikilens theme
195 \echo Creating :rating_tbl
196 CREATE TABLE :rating_tbl (
197 dimension INTEGER NOT NULL,
198 raterpage INT8 NOT NULL REFERENCES :page_tbl,
199 rateepage INT8 NOT NULL REFERENCES :page_tbl,
200 ratingvalue FLOAT NOT NULL,
201 rateeversion INT8 NOT NULL,
202 tstamp TIMESTAMP NOT NULL
204 CREATE UNIQUE INDEX :rating_id_idx ON :rating_tbl (dimension, raterpage, rateepage);
206 --================================================================
207 -- end of page relations
208 --================================================================
210 \echo Creating :session_tbl
211 CREATE TABLE :session_tbl (
212 sess_id CHAR(32) PRIMARY KEY,
213 sess_data bytea NOT NULL,
215 sess_ip CHAR(40) NOT NULL
217 -- CREATE UNIQUE INDEX :sess_id_idx ON :session_tbl (sess_id);
218 CREATE INDEX :sess_date_idx ON :session_tbl (sess_date);
219 CREATE INDEX :sess_ip_idx ON :session_tbl (sess_ip);
221 -- Optional DB Auth and Prefs
222 -- For these tables below the default table prefix must be used
223 -- in the DBAuthParam SQL statements also.
225 \echo Creating :pref_tbl
226 CREATE TABLE :pref_tbl (
227 userid CHAR(48) PRIMARY KEY,
228 prefs TEXT NULL DEFAULT '',
229 passwd CHAR(48) DEFAULT '',
230 groupname CHAR(48) DEFAULT 'users'
232 -- CREATE UNIQUE INDEX :pref_id_idx ON :pref_tbl (userid);
233 CREATE INDEX pref_group_idx ON :pref_tbl (groupname);
235 -- Use the member table, if you need it for n:m user-group relations,
236 -- and adjust your DBAUTH_AUTH_ SQL statements.
237 CREATE TABLE :member_tbl (
238 userid CHAR(48) NOT NULL REFERENCES :pref_tbl,
239 groupname CHAR(48) NOT NULL DEFAULT 'users'
241 CREATE INDEX :member_id_idx ON :member_tbl (userid);
242 CREATE INDEX :member_group_idx ON :member_tbl (groupname);
244 -- if ACCESS_LOG_SQL > 0
245 -- only if you need fast log-analysis (spam prevention, recent referrers)
246 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
247 \echo Creating :accesslog_tbl
248 CREATE TABLE :accesslog_tbl (
250 remote_host VARCHAR(100),
251 remote_user VARCHAR(50),
252 request_method VARCHAR(10),
253 request_line VARCHAR(255),
254 request_args VARCHAR(255),
255 request_file VARCHAR(255),
256 request_uri VARCHAR(255),
257 request_time CHAR(28),
260 referer VARCHAR(255),
262 request_duration FLOAT
264 CREATE INDEX :accesslog_time_idx ON :accesslog_tbl (time_stamp);
265 CREATE INDEX :accesslog_host_idx ON :accesslog_tbl (remote_host);
266 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
268 --================================================================
270 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
271 -- at first init it for the database:
272 -- $ psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
274 -- example of ISpell dictionary
275 -- 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';
276 -- example of synonym dict
277 -- UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
279 \echo Initializing tsearch2 indices
280 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
281 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
282 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
284 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
286 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
287 FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
289 --================================================================
291 \echo You might want to ignore the following errors or run
292 \echo /usr/sbin/createuser -S -R -d :httpd_user
294 \echo Applying permissions for role :httpd_user
295 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl TO :httpd_user;
296 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl TO :httpd_user;
297 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl TO :httpd_user;
298 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl TO :httpd_user;
299 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl TO :httpd_user;
301 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl TO :httpd_user;
302 -- you may want to fine tune this:
303 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl TO :httpd_user;
304 -- GRANT SELECT ON :user_tbl TO :httpd_user;
305 GRANT SELECT ON :member_tbl TO :httpd_user;
306 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl TO :httpd_user;
307 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl TO :httpd_user;
309 --================================================================
310 -- some stored procedures to put unneccesary syntax into the server
312 \echo Initializing stored procedures
315 CREATE OR REPLACE FUNCTION :update_recent_fn (INT4, INT4)
316 RETURNS integer AS $$
317 DELETE FROM test_recent WHERE id = $1;
318 INSERT INTO test_recent (id, latestversion, latestmajor, latestminor)
319 SELECT id, MAX(version) AS latestversion,
320 MAX(CASE WHEN minor_edit = 0 THEN version END) AS latestmajor,
321 MAX(CASE WHEN minor_edit <> 0 THEN version END) AS latestminor
322 FROM test_version version WHERE id = $2 GROUP BY id;
323 DELETE FROM test_nonempty WHERE id = $1;
324 INSERT INTO test_nonempty (id)
326 FROM test_recent recent, test_version version
327 WHERE recent.id = version.id
328 AND version = latestversion
331 SELECT id FROM test_nonempty WHERE id = $1;
335 CREATE OR REPLACE FUNCTION :prepare_rename_fn (INT4, INT4)
337 DELETE FROM test_page WHERE id = $2;
338 DELETE FROM test_version WHERE id = $2;
339 DELETE FROM test_recent WHERE id = $2;
340 DELETE FROM test_nonempty WHERE id = $2;
341 -- We have to fix all referring tables to the old id
342 UPDATE test_link SET linkfrom = $1 WHERE linkfrom = $2;
343 UPDATE test_link SET linkto = $1 WHERE linkto = $2;