1 -- $Id: psql-1_3_12.sql,v 1.5 2006-05-18 06:08:33 rurban Exp $
5 -- Init the database with:
6 -- $ /usr/sbin/createdb phpwiki
7 -- $ /usr/sbin/createuser -S -R -d phpwiki # (see httpd_user below)
8 -- $ /usr/bin/psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
9 -- $ /usr/bin/psql phpwiki < psql-initialize.sql
11 --================================================================
12 -- Prefix for table names.
14 -- You should set this to the same value you specified for
15 -- DATABASE_PREFIX in config.ini
19 --================================================================
20 -- Which postgres user gets access to the tables?
22 -- You should set this to the name of the postgres
23 -- user who will be accessing the tables.
24 -- See DATABASE_DSN in config.ini
26 -- Commonly, connections from php are made under
27 -- the user name of 'nobody', 'apache' or 'www'.
29 \set httpd_user 'phpwiki'
31 --================================================================
33 -- Don't modify below this point unless you know what you are doing.
35 --================================================================
37 \set page_tbl :prefix 'page'
38 \set page_id_seq :prefix 'page_id_seq'
39 \set page_id_idx :prefix 'page_id_idx'
40 \set page_name_idx :prefix 'page_name_idx'
42 \set version_tbl :prefix 'version'
43 \set vers_id_idx :prefix 'vers_id_idx'
44 \set vers_mtime_idx :prefix 'vers_mtime_idx'
46 \set recent_tbl :prefix 'recent'
47 \set recent_id_idx :prefix 'recent_id_idx'
49 \set nonempty_tbl :prefix 'nonempty'
50 \set nonmt_id_idx :prefix 'nonmt_id_idx'
52 \set link_tbl :prefix 'link'
53 \set link_from_idx :prefix 'link_from_idx'
54 \set link_to_idx :prefix 'link_to_idx'
55 \set relation_idx :prefix 'relation_idx'
57 \set session_tbl :prefix 'session'
58 \set sess_id_idx :prefix 'sess_id_idx'
59 \set sess_date_idx :prefix 'sess_date_idx'
60 \set sess_ip_idx :prefix 'sess_ip_idx'
62 \set pref_tbl :prefix 'pref'
63 \set pref_id_idx :prefix 'pref_id_idx'
64 --\set user_tbl :prefix 'users'
65 --\set user_id_idx :prefix 'users_id_idx'
66 \set member_tbl :prefix 'member'
67 \set member_id_idx :prefix 'member_id_idx'
68 \set member_group_idx :prefix 'member_group_idx'
70 \set rating_tbl :prefix 'rating'
71 \set rating_id_idx :prefix 'rating_id_idx'
73 \set accesslog_tbl :prefix 'accesslog'
74 \set accesslog_time_idx :prefix 'log_time_idx'
75 \set accesslog_host_idx :prefix 'log_host_idx'
77 --================================================================
78 \echo schema enhancements
81 ALTER COLUMN id TYPE SERIAL /* PRIMARY KEY */,
82 ALTER COLUMN pagename TYPE VARCHAR(100),
83 ALTER COLUMN pagename SET NOT NULL,
85 ADD CHECK (pagename <> '');
86 ALTER TABLE :version_tbl
87 ALTER COLUMN id TYPE INT4,
88 ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
89 ALTER TABLE :nonempty_tbl
90 ALTER COLUMN id TYPE INT4,
91 ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
93 \echo Creating experimental page views (not yet used)
95 -- nonempty versiondata
96 CREATE VIEW existing_page AS
97 SELECT * FROM :page_tbl P INNER JOIN :nonempty_tbl N USING (id);
99 -- latest page version
100 CREATE VIEW curr_page AS
101 SELECT P.id,P.pagename,P.hits,P.pagedata,P.cached_html,
102 V.version,V.mtime,V.minor_edit,V.content,V.versiondata
104 JOIN :version_tbl V USING (id)
105 JOIN :recent_tbl R ON (V.id=R.id AND V.version=R.latestversion);
107 ALTER TABLE :link_tbl
108 ALTER COLUMN linkfrom TYPE INT4,
109 ALTER COLUMN linkto TYPE INT4,
110 ADD COLUMN relation INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE,
111 ADD FOREIGN KEY (linkfrom) REFERENCES :page_tbl (id) ON DELETE CASCADE,
112 ADD FOREIGN KEY (linkto) REFERENCES :page_tbl (id) ON DELETE CASCADE;
113 CREATE INDEX :relation_idx ON :link_tbl (relation);
114 ALTER TABLE :rating_tbl
115 ALTER COLUMN raterpage TYPE INT8,
116 ALTER COLUMN rateepage TYPE INT8,
117 ADD FOREIGN KEY (raterpage) REFERENCES :page_tbl (id) ON DELETE CASCADE,
118 ADD FOREIGN KEY (rateepage) REFERENCES :page_tbl (id) ON DELETE CASCADE;
119 ALTER TABLE :member_tbl
120 ALTER COLUMN userid TYPE CHAR(48),
121 ALTER COLUMN userid SET NOT NULL,
122 ADD FOREIGN KEY (userid) REFERENCES :pref_tbl;
124 --================================================================
126 \echo add tsearch2 fulltextsearch extension
127 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
128 -- At first init it for the database:
130 -- example of ISpell dictionary
131 -- UPDATE pg_ts_dict SET dict_initoption='DictFile="/usr/local/share/ispell/russian.dict",
132 -- AffFile ="/usr/local/share/ispell/russian.aff", StopFile="/usr/local/share/ispell/russian.stop"'
133 -- WHERE dict_name='ispell_template';
134 -- example of synonym dict
135 -- UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
137 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
138 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
139 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
141 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
143 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
144 FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
146 --================================================================
148 \echo Initializing stored procedures
150 CREATE OR REPLACE FUNCTION update_recent (id INT4, version INT4)
152 DELETE FROM recent WHERE id=$1;
153 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
154 SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0 THEN version END),
155 MAX(CASE WHEN minor_edit<>0 THEN version END)
156 FROM version WHERE id=$2 GROUP BY id;
157 DELETE FROM nonempty WHERE id=$1;
158 INSERT INTO nonempty (id)
161 WHERE recent.id=version.id
162 AND version=latestversion
167 CREATE OR REPLACE FUNCTION prepare_rename_page (oldid INT4, newid INT4)
169 DELETE FROM page WHERE id=$2;
170 DELETE FROM version WHERE id=$2;
171 DELETE FROM recent WHERE id=$2;
172 DELETE FROM nonempty WHERE id=$2;
173 -- We have to fix all referring tables to the old id
174 UPDATE link SET linkfrom=$1 WHERE linkfrom=$2;
175 UPDATE link SET linkto=$1 WHERE linkto=$2;