3 -- ================================================================
4 -- Prefix for table names.
6 -- You should set this to the same value you specify for
7 -- DATABASE_PREFIX in config/config.ini
11 -- ================================================================
12 -- Which postgres user gets access to the tables?
14 -- You should set this to the name of the postgres
15 -- user who will be accessing the tables.
16 -- See DATABASE_DSN in config.ini
18 -- NOTE: To be able to vacuum the tables from ordinary page requests
19 -- :httpd_user must be the table owner.
20 -- To run autovacuum and disable page requests vacuums edit the
21 -- pqsql backend optimize method in lib/WikiDB/backend/*_psql.php
23 -- Commonly, connections from php are made under
24 -- the user name of 'nobody', 'apache' or 'www'.
26 \set httpd_user 'wikiuser'
28 -- ================================================================
30 -- Don't modify below this point unless you know what you are doing.
32 -- ================================================================
34 \set qprefix '\'' :prefix '\''
35 \set qhttp_user '\'' :httpd_user '\''
37 \echo At first init the database with:
38 \echo '$ createdb phpwiki'
39 \echo '$ createuser -S -R -d ' :qhttp_user
40 \echo '$ psql -U ' :qhttp_user ' phpwiki < /usr/share/pgsql/contrib/tsearch2.sql'
41 \echo '$ psql -U ' :qhttp_user ' phpwiki < psql-initialize.sql'
43 \echo Initializing PhpWiki tables with:
44 \echo ' prefix = ' :qprefix
45 \echo ' httpd_user = ' :qhttp_user
47 \echo 'Expect some \'NOTICE: CREATE ... will create implicit sequence/index ...\' messages '
49 \set page_tbl :prefix 'page'
50 \set page_id_seq :prefix 'page_id_seq'
51 \set page_id_idx :prefix 'page_id_idx'
52 \set page_name_idx :prefix 'page_name_idx'
54 \set version_tbl :prefix 'version'
55 \set vers_id_idx :prefix 'vers_id_idx'
56 \set vers_mtime_idx :prefix 'vers_mtime_idx'
58 \set recent_tbl :prefix 'recent'
59 \set recent_id_idx :prefix 'recent_id_idx'
60 \set recent_lv_idx :prefix 'recent_lv_idx'
62 \set nonempty_tbl :prefix 'nonempty'
63 \set nonmt_id_idx :prefix 'nonmt_id_idx'
65 \set link_tbl :prefix 'link'
66 \set link_from_idx :prefix 'link_from_idx'
67 \set link_to_idx :prefix 'link_to_idx'
68 \set relation_idx :prefix 'relation_idx'
70 \set pagedata_tbl :prefix 'pagedata'
71 \set pagedata_id_idx :prefix 'pagedata_id_idx'
72 \set versiondata_tbl :prefix 'versiondata'
73 \set pageperm_tbl :prefix 'pageperm'
74 \set pageperm_id_idx :prefix 'pageperm_id_idx'
75 \set pageperm_access_idx :prefix 'pageperm_access_idx'
76 -- \set existing_page_view :prefix 'existing_page'
77 -- \set curr_page_view :prefix 'curr_page'
79 \set session_tbl :prefix 'session'
80 \set sess_id_idx :prefix 'sess_id_idx'
81 \set sess_date_idx :prefix 'sess_date_idx'
82 \set sess_ip_idx :prefix 'sess_ip_idx'
84 \set pref_tbl :prefix 'pref'
85 \set pref_id_idx :prefix 'pref_id_idx'
86 -- \set user_tbl :prefix 'users'
87 -- \set user_id_idx :prefix 'users_id_idx'
88 \set member_tbl :prefix 'member'
89 \set member_id_idx :prefix 'member_id_idx'
90 \set member_group_idx :prefix 'member_group_idx'
92 \set rating_tbl :prefix 'rating'
93 \set rating_id_idx :prefix 'rating_id_idx'
95 \set accesslog_tbl :prefix 'accesslog'
96 \set accesslog_time_idx :prefix 'log_time_idx'
97 \set accesslog_host_idx :prefix 'log_host_idx'
99 \set update_recent_fn :prefix 'update_recent'
100 \set prepare_rename_fn :prefix 'prepare_rename_page'
102 \echo Creating :page_tbl
103 CREATE TABLE :page_tbl (
104 id SERIAL PRIMARY KEY,
105 pagename VARCHAR(100) NOT NULL UNIQUE CHECK (pagename <> ''),
106 hits INT4 NOT NULL DEFAULT 0,
107 pagedata TEXT NOT NULL DEFAULT '',
108 -- cached_html bytea DEFAULT ''
109 cached_html TEXT DEFAULT ''
111 -- CREATE UNIQUE INDEX :page_id_idx ON :page_tbl (id);
112 -- CREATE UNIQUE INDEX :page_name_idx ON :page_tbl (pagename);
114 -- we use 0 <=> global_data to satisfy the relation = 0 constraint
115 INSERT INTO :page_tbl VALUES (0,'global_data',0,'','');
117 \echo Creating :version_tbl
118 CREATE TABLE :version_tbl (
119 id INT4 REFERENCES :page_tbl,
120 version INT4 NOT NULL,
122 -- FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
123 minor_edit INT2 DEFAULT 0,
124 -- use bytea instead?
125 content TEXT NOT NULL DEFAULT '',
126 versiondata TEXT NOT NULL DEFAULT ''
128 CREATE UNIQUE INDEX :vers_id_idx ON :version_tbl (id, version);
129 CREATE INDEX :vers_mtime_idx ON :version_tbl (mtime);
130 -- deletion order: version, recent, nonempty
132 \echo Creating :recent_tbl
133 CREATE TABLE :recent_tbl (
134 id INT4 REFERENCES :page_tbl,
138 FOREIGN KEY (id, latestversion) REFERENCES :version_tbl (id, version),
139 CHECK (latestminor >= latestmajor)
141 CREATE UNIQUE INDEX :recent_id_idx ON :recent_tbl (id);
142 CREATE INDEX :recent_lv_idx ON :recent_tbl (latestversion);
144 \echo Creating :nonempty_tbl
145 CREATE TABLE :nonempty_tbl (
146 id INT4 NOT NULL REFERENCES :page_tbl
148 CREATE UNIQUE INDEX :nonmt_id_idx ON :nonempty_tbl (id);
150 \echo Creating experimental pagedata (not yet used)
151 CREATE TABLE :pagedata_tbl (
152 id INT4 NOT NULL REFERENCES :page_tbl,
155 rest TEXT NOT NULL DEFAULT ''
157 CREATE INDEX :pagedata_id_idx ON pagedata (id);
159 \echo Creating experimental versiondata (not yet used)
160 CREATE TABLE :versiondata_tbl (
162 version INT4 NOT NULL,
163 markup INT2 DEFAULT 2,
165 author_id VARCHAR(48),
166 pagetype VARCHAR(20) DEFAULT 'wikitext',
167 rest TEXT NOT NULL DEFAULT '',
168 FOREIGN KEY (id, version) REFERENCES :version_tbl (id, version)
170 \echo Creating experimental pageperm (not yet used)
171 CREATE TABLE :pageperm_tbl (
172 id INT4 NOT NULL REFERENCES :page_tbl(id),
173 -- view,edit,create,list,remove,change,dump
174 access CHAR(12) NOT NULL,
175 groupname VARCHAR(48),
178 CREATE INDEX :pageperm_id_idx ON pageperm (id);
179 CREATE INDEX :pageperm_access_idx ON pageperm (access);
181 -- \echo Creating experimental page views (not yet used)
183 -- nonempty versiondata
184 -- CREATE VIEW :existing_page_view AS
185 -- SELECT * FROM :page_tbl P INNER JOIN :nonempty_tbl N USING (id);
187 -- latest page version
188 -- CREATE VIEW :curr_page_view AS
189 -- SELECT P.id,P.pagename,P.hits,P.pagedata,P.cached_html,
190 -- V.version,V.mtime,V.minor_edit,V.content,V.versiondata
192 -- JOIN :version_tbl V USING (id)
193 -- JOIN :recent_tbl R ON (V.id=R.id AND V.version=R.latestversion);
195 \echo Creating :link_tbl
196 CREATE TABLE :link_tbl (
197 linkfrom INT4 NOT NULL REFERENCES :page_tbl,
198 linkto INT4 NOT NULL REFERENCES :page_tbl,
201 CREATE INDEX :link_from_idx ON :link_tbl (linkfrom);
202 CREATE INDEX :link_to_idx ON :link_tbl (linkto);
203 CREATE INDEX :relation_idx ON :link_tbl (relation);
205 -- ALTER TABLE link DROP CONSTRAINT link_relation_fkey;
207 -- if you plan to use the wikilens theme
208 \echo Creating :rating_tbl
209 CREATE TABLE :rating_tbl (
210 dimension INTEGER NOT NULL,
211 raterpage INT8 NOT NULL REFERENCES :page_tbl,
212 rateepage INT8 NOT NULL REFERENCES :page_tbl,
213 ratingvalue FLOAT NOT NULL,
214 rateeversion INT8 NOT NULL,
215 tstamp TIMESTAMP NOT NULL
217 CREATE UNIQUE INDEX :rating_id_idx ON :rating_tbl (dimension, raterpage, rateepage);
219 -- ================================================================
220 -- end of page relations
221 -- ================================================================
223 \echo Creating :session_tbl
224 CREATE TABLE :session_tbl (
225 sess_id CHAR(32) PRIMARY KEY,
226 sess_data bytea NOT NULL,
228 sess_ip CHAR(40) NOT NULL
230 -- CREATE UNIQUE INDEX :sess_id_idx ON :session_tbl (sess_id);
231 CREATE INDEX :sess_date_idx ON :session_tbl (sess_date);
232 CREATE INDEX :sess_ip_idx ON :session_tbl (sess_ip);
234 -- Optional DB Auth and Prefs
235 -- For these tables below the default table prefix must be used
236 -- in the DBAuthParam SQL statements also.
238 \echo Creating :pref_tbl
239 CREATE TABLE :pref_tbl (
240 userid CHAR(48) PRIMARY KEY,
241 prefs TEXT NULL DEFAULT '',
242 passwd CHAR(48) DEFAULT '',
243 groupname CHAR(48) DEFAULT 'users'
245 -- CREATE UNIQUE INDEX :pref_id_idx ON :pref_tbl (userid);
246 CREATE INDEX pref_group_idx ON :pref_tbl (groupname);
248 -- Use the member table, if you need it for n:m user-group relations,
249 -- and adjust your DBAUTH_AUTH_ SQL statements.
250 CREATE TABLE :member_tbl (
251 userid CHAR(48) NOT NULL REFERENCES :pref_tbl,
252 groupname CHAR(48) NOT NULL DEFAULT 'users'
254 CREATE INDEX :member_id_idx ON :member_tbl (userid);
255 CREATE INDEX :member_group_idx ON :member_tbl (groupname);
257 -- if ACCESS_LOG_SQL > 0
258 -- only if you need fast log-analysis (spam prevention, recent referrers)
259 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
260 \echo Creating :accesslog_tbl
261 CREATE TABLE :accesslog_tbl (
263 remote_host VARCHAR(100),
264 remote_user VARCHAR(50),
265 request_method VARCHAR(10),
266 request_line VARCHAR(255),
267 request_args VARCHAR(255),
268 request_file VARCHAR(255),
269 request_uri VARCHAR(255),
270 request_time CHAR(28),
273 referer VARCHAR(255),
275 request_duration FLOAT
277 CREATE INDEX :accesslog_time_idx ON :accesslog_tbl (time_stamp);
278 CREATE INDEX :accesslog_host_idx ON :accesslog_tbl (remote_host);
279 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
281 -- ================================================================
283 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
284 -- at first init it for the database:
285 -- $ psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
287 -- example of ISpell dictionary
288 -- 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';
289 -- example of synonym dict
290 -- UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
292 \echo Initializing tsearch2 indices
293 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
294 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
295 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
297 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
299 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
300 FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
302 -- this might be needed:
303 -- see http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
304 -- update pg_ts_cfg set locale='en_US.UTF-8' where ts_name='default';
306 -- ================================================================
308 \echo You might want to ignore the following errors or run
309 \echo /usr/sbin/createuser -S -R -d :httpd_user
311 \echo Applying permissions for role :httpd_user
312 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl TO :httpd_user;
313 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl TO :httpd_user;
314 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl TO :httpd_user;
315 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl TO :httpd_user;
316 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl TO :httpd_user;
318 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl TO :httpd_user;
319 -- you may want to fine tune this:
320 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl TO :httpd_user;
321 -- GRANT SELECT ON :user_tbl TO :httpd_user;
322 GRANT SELECT ON :member_tbl TO :httpd_user;
323 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl TO :httpd_user;
324 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl TO :httpd_user;
326 -- ================================================================
327 -- some stored procedures to put unneccesary syntax into the server
329 \echo Initializing stored procedures
332 CREATE OR REPLACE FUNCTION :update_recent_fn (INT4, INT4)
333 RETURNS integer AS $$
334 DELETE FROM recent WHERE id = $1;
335 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
336 SELECT id, MAX(version) AS latestversion,
337 MAX(CASE WHEN minor_edit = 0 THEN version END) AS latestmajor,
338 MAX(CASE WHEN minor_edit <> 0 THEN version END) AS latestminor
339 FROM version WHERE id = $2 GROUP BY id;
340 DELETE FROM nonempty WHERE id = $1;
341 INSERT INTO nonempty (id)
344 WHERE recent.id = version.id
345 AND version = latestversion
348 SELECT id FROM nonempty WHERE id = $1;
352 CREATE OR REPLACE FUNCTION :prepare_rename_fn (INT4, INT4)
354 DELETE FROM page WHERE id = $2;
355 DELETE FROM version WHERE id = $2;
356 DELETE FROM recent WHERE id = $2;
357 DELETE FROM nonempty WHERE id = $2;
358 -- We have to fix all referring tables to the old id
359 UPDATE link SET linkfrom = $1 WHERE linkfrom = $2;
360 UPDATE link SET linkto = $1 WHERE linkto = $2;