1 -- $Id: psql-test-initialize.sql,v 1.1 2006-06-05 09:33:22 rurban Exp $
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'
19 \set nonempty_tbl :prefix 'nonempty'
20 \set nonmt_id_idx :prefix 'nonmt_id_idx'
22 \set link_tbl :prefix 'link'
23 \set link_from_idx :prefix 'link_from_idx'
24 \set link_to_idx :prefix 'link_to_idx'
25 \set relation_idx :prefix 'relation_idx'
27 \set session_tbl :prefix 'session'
28 \set sess_id_idx :prefix 'sess_id_idx'
29 \set sess_date_idx :prefix 'sess_date_idx'
30 \set sess_ip_idx :prefix 'sess_ip_idx'
32 \set pref_tbl :prefix 'pref'
33 \set pref_id_idx :prefix 'pref_id_idx'
34 \set pref_pkey :prefix 'pref_pkey'
36 --\set user_tbl :prefix 'users'
37 --\set user_id_idx :prefix 'users_id_idx'
38 \set member_tbl :prefix 'member'
39 \set member_id_idx :prefix 'member_id_idx'
40 \set member_group_idx :prefix 'member_group_idx'
42 \set rating_tbl :prefix 'rating'
43 \set rating_id_idx :prefix 'rating_id_idx'
45 \set accesslog_tbl :prefix 'accesslog'
46 \set accesslog_time_idx :prefix 'log_time_idx'
47 \set accesslog_host_idx :prefix 'log_host_idx'
49 \echo Dropping all test relations
51 DROP TABLE :version_tbl CASCADE;
52 DROP TABLE :recent_tbl CASCADE;
53 DROP TABLE :nonempty_tbl CASCADE;
54 DROP TABLE :link_tbl CASCADE;
55 DROP TABLE :rating_tbl CASCADE;
57 DROP TABLE :page_tbl CASCADE;
58 DROP SEQUENCE :page_id_seq;
60 DROP TABLE :member_tbl CASCADE;
61 DROP TABLE :pref_tbl CASCADE;
62 DROP TABLE :session_tbl CASCADE;
63 DROP TABLE :accesslog_tbl CASCADE;
65 DROP FUNCTION update_recent (INT4, INT4);
66 DROP FUNCTION prepare_rename_page (INT4, INT4);
68 ------------------------------------------------------------
70 \echo Creating :page_tbl
71 CREATE TABLE :page_tbl (
72 id SERIAL PRIMARY KEY,
73 pagename VARCHAR(100) NOT NULL UNIQUE CHECK (pagename <> ''),
74 hits INT4 NOT NULL DEFAULT 0,
75 pagedata TEXT NOT NULL DEFAULT '',
76 cached_html bytea DEFAULT ''
78 -- CREATE UNIQUE INDEX :page_id_idx ON :page_tbl (id);
79 -- CREATE UNIQUE INDEX :page_name_idx ON :page_tbl (pagename);
81 \echo Creating :version_tbl
82 CREATE TABLE :version_tbl (
83 id INT4 REFERENCES :page_tbl ON DELETE CASCADE,
84 version INT4 NOT NULL,
86 -- FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
87 minor_edit INT2 DEFAULT 0,
89 content TEXT NOT NULL DEFAULT '',
90 versiondata TEXT NOT NULL DEFAULT ''
92 CREATE UNIQUE INDEX :vers_id_idx ON :version_tbl (id, version);
93 CREATE INDEX :vers_mtime_idx ON :version_tbl (mtime);
95 \echo Creating :recent_tbl
96 CREATE TABLE :recent_tbl (
97 id INT4 REFERENCES :page_tbl ON DELETE CASCADE,
101 FOREIGN KEY (id, latestversion) REFERENCES :version_tbl (id, version),
102 CHECK (latestminor >= latestmajor)
104 CREATE UNIQUE INDEX :recent_id_idx ON :recent_tbl (id);
105 CREATE INDEX recent_latestversion_idx ON :recent_tbl (latestversion);
107 \echo Creating :nonempty_tbl
108 CREATE TABLE :nonempty_tbl (
109 id INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE
111 CREATE UNIQUE INDEX :nonmt_id_idx ON :nonempty_tbl (id);
113 \echo Creating :link_tbl
114 CREATE TABLE :link_tbl (
115 linkfrom INT4 NOT NULL REFERENCES :page_tbl,
116 linkto INT4 NOT NULL REFERENCES :page_tbl,
117 relation INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE
119 CREATE INDEX :link_from_idx ON :link_tbl (linkfrom);
120 CREATE INDEX :link_to_idx ON :link_tbl (linkto);
121 CREATE INDEX :relation_idx ON :link_tbl (relation);
123 -- if you plan to use the wikilens theme
124 \echo Creating :rating_tbl
125 CREATE TABLE :rating_tbl (
126 dimension INTEGER NOT NULL,
127 raterpage INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE,
128 rateepage INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE,
129 ratingvalue FLOAT NOT NULL,
130 rateeversion INT8 NOT NULL,
131 tstamp TIMESTAMP NOT NULL
133 CREATE UNIQUE INDEX :rating_id_idx ON :rating_tbl (dimension, raterpage, rateepage);
135 --================================================================
136 -- end of page relations
137 --================================================================
139 \echo Creating :session_tbl
140 CREATE TABLE :session_tbl (
141 sess_id CHAR(32) PRIMARY KEY,
142 sess_data bytea NOT NULL,
144 sess_ip CHAR(40) NOT NULL
146 -- CREATE UNIQUE INDEX :sess_id_idx ON :session_tbl (sess_id);
147 CREATE INDEX :sess_date_idx ON :session_tbl (sess_date);
148 CREATE INDEX :sess_ip_idx ON :session_tbl (sess_ip);
150 -- Optional DB Auth and Prefs
151 -- For these tables below the default table prefix must be used
152 -- in the DBAuthParam SQL statements also.
154 \echo Creating :pref_tbl
155 CREATE TABLE :pref_tbl (
156 userid CHAR(48) PRIMARY KEY,
157 prefs TEXT NULL DEFAULT '',
158 passwd CHAR(48) DEFAULT '',
159 groupname CHAR(48) DEFAULT 'users'
161 -- CREATE UNIQUE INDEX :pref_id_idx ON :pref_tbl (userid);
162 CREATE INDEX pref_group_idx ON :pref_tbl (groupname);
164 -- Use the member table, if you need it for n:m user-group relations,
165 -- and adjust your DBAUTH_AUTH_ SQL statements.
166 CREATE TABLE :member_tbl (
167 userid CHAR(48) NOT NULL REFERENCES :pref_tbl ON DELETE CASCADE,
168 groupname CHAR(48) NOT NULL DEFAULT 'users'
170 CREATE INDEX :member_id_idx ON :member_tbl (userid);
171 CREATE INDEX :member_group_idx ON :member_tbl (groupname);
173 -- if ACCESS_LOG_SQL > 0
174 -- only if you need fast log-analysis (spam prevention, recent referrers)
175 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
176 \echo Creating :accesslog_tbl
177 CREATE TABLE :accesslog_tbl (
179 remote_host VARCHAR(50),
180 remote_user VARCHAR(50),
181 request_method VARCHAR(10),
182 request_line VARCHAR(255),
183 request_args VARCHAR(255),
184 request_file VARCHAR(255),
185 request_uri VARCHAR(255),
186 request_time CHAR(28),
189 referer VARCHAR(255),
191 request_duration FLOAT
193 CREATE INDEX :accesslog_time_idx ON :accesslog_tbl (time_stamp);
194 CREATE INDEX :accesslog_host_idx ON :accesslog_tbl (remote_host);
195 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
197 --================================================================
199 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
200 -- at first init it for the database:
201 -- $ psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
203 -- example of ISpell dictionary
204 -- 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';
205 -- example of synonym dict
206 -- UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
208 \echo Initializing tsearch2 indices
209 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
210 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
211 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
213 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
215 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
216 FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
218 --================================================================
220 \echo You might want to ignore the following errors or run
221 \echo /usr/sbin/createuser -S -R -d :httpd_user
223 \echo Applying permissions for role :httpd_user
224 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl TO :httpd_user;
225 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl TO :httpd_user;
226 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl TO :httpd_user;
227 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl TO :httpd_user;
228 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl TO :httpd_user;
230 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl TO :httpd_user;
231 -- you may want to fine tune this:
232 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl TO :httpd_user;
233 -- GRANT SELECT ON :user_tbl TO :httpd_user;
234 GRANT SELECT ON :member_tbl TO :httpd_user;
235 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl TO :httpd_user;
236 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl TO :httpd_user;
238 --================================================================
239 -- some stored procedures to put unneccesary syntax into the server
241 \echo Initializing stored procedures
243 CREATE OR REPLACE FUNCTION update_recent (id INT4, version INT4)
245 DELETE FROM recent WHERE id=$1;
246 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
247 SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0 THEN version END),
248 MAX(CASE WHEN minor_edit<>0 THEN version END)
249 FROM version WHERE id=$2 GROUP BY id;
250 DELETE FROM nonempty WHERE id=$1;
251 INSERT INTO nonempty (id)
254 WHERE recent.id=version.id
255 AND version=latestversion
260 CREATE OR REPLACE FUNCTION prepare_rename_page (oldid INT4, newid INT4)
262 DELETE FROM page WHERE id=$2;
263 DELETE FROM version WHERE id=$2;
264 DELETE FROM recent WHERE id=$2;
265 DELETE FROM nonempty WHERE id=$2;
266 -- We have to fix all referring tables to the old id
267 UPDATE link SET linkfrom=$1 WHERE linkfrom=$2;
268 UPDATE link SET linkto=$1 WHERE linkto=$2;