1 CREATE TABLE plugin_wiki_accesslog (
3 remote_host character varying(100),
4 remote_user character varying(50),
5 request_method character varying(10),
6 request_line character varying(255),
7 request_args character varying(255),
8 request_file character varying(255),
9 request_uri character varying(255),
10 request_time character(28),
13 referer character varying(255),
14 agent character varying(255),
15 request_duration double precision
17 -- ALTER TABLE public.plugin_wiki_accesslog OWNER TO gforge;
19 CREATE TABLE plugin_wiki_page (
21 pagename character varying(100) NOT NULL,
22 hits integer DEFAULT 0 NOT NULL,
23 pagedata text DEFAULT ''::text NOT NULL,
24 cached_html bytea DEFAULT ''::bytea,
25 CONSTRAINT plugin_wiki_page_pagename_check CHECK (((pagename)::text <> ''::text))
27 -- ALTER TABLE public.plugin_wiki_page OWNER TO gforge;
29 CREATE TABLE plugin_wiki_recent (
31 latestversion integer,
35 -- ALTER TABLE public.plugin_wiki_recent OWNER TO gforge;
37 CREATE TABLE plugin_wiki_version (
39 version integer NOT NULL,
40 mtime integer NOT NULL,
41 minor_edit smallint DEFAULT 0,
42 content text DEFAULT ''::text NOT NULL,
43 versiondata text DEFAULT ''::text NOT NULL
45 -- ALTER TABLE public.plugin_wiki_version OWNER TO gforge;
47 CREATE TABLE plugin_wiki_nonempty (
50 -- ALTER TABLE public.plugin_wiki_nonempty OWNER TO gforge;
52 CREATE TABLE plugin_wiki_link (
53 linkfrom integer NOT NULL,
54 linkto integer NOT NULL,
57 -- ALTER TABLE public.plugin_wiki_link OWNER TO gforge;
59 CREATE TABLE plugin_wiki_member (
60 userid character(48) NOT NULL,
61 groupname character(48) DEFAULT 'users'::bpchar NOT NULL
63 -- ALTER TABLE public.plugin_wiki_member OWNER TO gforge;
66 CREATE TABLE plugin_wiki_pagedata (
70 rest text DEFAULT ''::text NOT NULL
72 -- ALTER TABLE public.plugin_wiki_pagedata OWNER TO gforge;
74 CREATE TABLE plugin_wiki_pageperm (
76 "access" character(12) NOT NULL,
77 groupname character varying(48),
80 -- ALTER TABLE public.plugin_wiki_pageperm OWNER TO gforge;
82 CREATE TABLE plugin_wiki_pref (
83 userid character(48) NOT NULL,
84 prefs text DEFAULT ''::text,
85 passwd character(48) DEFAULT ''::bpchar,
86 groupname character(48) DEFAULT 'users'::bpchar
88 -- ALTER TABLE public.plugin_wiki_pref OWNER TO gforge;
90 CREATE TABLE plugin_wiki_rating (
91 dimension integer NOT NULL,
92 raterpage bigint NOT NULL,
93 rateepage bigint NOT NULL,
94 ratingvalue double precision NOT NULL,
95 rateeversion bigint NOT NULL,
96 tstamp timestamp without time zone NOT NULL
98 -- ALTER TABLE public.plugin_wiki_rating OWNER TO gforge;
100 CREATE TABLE plugin_wiki_session (
101 sess_id character(32) NOT NULL,
102 sess_data bytea NOT NULL,
104 sess_ip character(40) NOT NULL
106 -- ALTER TABLE public.plugin_wiki_session OWNER TO gforge;
108 CREATE TABLE plugin_wiki_versiondata (
110 version integer NOT NULL,
111 markup smallint DEFAULT 2,
112 author character varying(48),
113 author_id character varying(48),
114 pagetype character varying(20) DEFAULT 'wikitext'::character varying,
115 rest text DEFAULT ''::text NOT NULL
117 -- ALTER TABLE public.plugin_wiki_versiondata OWNER TO gforge;
120 ALTER TABLE ONLY plugin_wiki_pref
121 ADD CONSTRAINT plugin_wiki_pref_pkey PRIMARY KEY (userid);
122 ALTER TABLE ONLY plugin_wiki_session
123 ADD CONSTRAINT plugin_wiki_session_pkey PRIMARY KEY (sess_id);
125 CREATE UNIQUE INDEX plugin_wiki_page_id_idx ON plugin_wiki_page (id);
127 CREATE INDEX plugin_wiki_link_from_idx ON plugin_wiki_link USING btree (linkfrom);
128 CREATE INDEX plugin_wiki_link_to_idx ON plugin_wiki_link USING btree (linkto);
129 CREATE INDEX plugin_wiki_log_host_idx ON plugin_wiki_accesslog USING btree (remote_host);
130 CREATE INDEX plugin_wiki_log_time_idx ON plugin_wiki_accesslog USING btree (time_stamp);
131 CREATE INDEX plugin_wiki_member_group_idx ON plugin_wiki_member USING btree (groupname);
132 CREATE INDEX plugin_wiki_member_id_idx ON plugin_wiki_member USING btree (userid);
133 CREATE UNIQUE INDEX plugin_wiki_nonmt_id_idx ON plugin_wiki_nonempty USING btree (id);
134 CREATE INDEX plugin_wiki_pagedata_id_idx ON plugin_wiki_pagedata USING btree (id);
135 CREATE INDEX plugin_wiki_pageperm_access_idx ON plugin_wiki_pageperm USING btree ("access");
136 CREATE INDEX plugin_wiki_pageperm_id_idx ON plugin_wiki_pageperm USING btree (id);
137 CREATE UNIQUE INDEX plugin_wiki_rating_id_idx ON plugin_wiki_rating USING btree (dimension, raterpage, rateepage);
138 CREATE UNIQUE INDEX plugin_wiki_recent_id_idx ON plugin_wiki_recent USING btree (id);
139 CREATE INDEX plugin_wiki_recent_lv_idx ON plugin_wiki_recent USING btree (latestversion);
140 CREATE INDEX plugin_wiki_relation_idx ON plugin_wiki_link USING btree (relation);
141 CREATE INDEX plugin_wiki_sess_date_idx ON plugin_wiki_session USING btree (sess_date);
142 CREATE INDEX plugin_wiki_sess_ip_idx ON plugin_wiki_session USING btree (sess_ip);
143 CREATE UNIQUE INDEX plugin_wiki_vers_id_idx ON plugin_wiki_version USING btree (id, version);
144 CREATE INDEX plugin_wiki_vers_mtime_idx ON plugin_wiki_version USING btree (mtime);
147 CREATE INDEX pref_group_idx ON plugin_wiki_pref USING btree (groupname);
150 ALTER TABLE ONLY plugin_wiki_link
151 ADD CONSTRAINT plugin_wiki_link_linkfrom_fkey FOREIGN KEY (linkfrom) REFERENCES plugin_wiki_page(id);
152 ALTER TABLE ONLY plugin_wiki_link
153 ADD CONSTRAINT plugin_wiki_link_linkto_fkey FOREIGN KEY (linkto) REFERENCES plugin_wiki_page(id);
154 ALTER TABLE ONLY plugin_wiki_member
155 ADD CONSTRAINT plugin_wiki_member_userid_fkey FOREIGN KEY (userid) REFERENCES plugin_wiki_pref(userid);
156 ALTER TABLE ONLY plugin_wiki_nonempty
157 ADD CONSTRAINT plugin_wiki_nonempty_id_fkey FOREIGN KEY (id) REFERENCES plugin_wiki_page(id);
158 ALTER TABLE ONLY plugin_wiki_pagedata
159 ADD CONSTRAINT plugin_wiki_pagedata_id_fkey FOREIGN KEY (id) REFERENCES plugin_wiki_page(id);
160 ALTER TABLE ONLY plugin_wiki_pageperm
161 ADD CONSTRAINT plugin_wiki_pageperm_id_fkey FOREIGN KEY (id) REFERENCES plugin_wiki_page(id);
162 ALTER TABLE ONLY plugin_wiki_rating
163 ADD CONSTRAINT plugin_wiki_rating_rateepage_fkey FOREIGN KEY (rateepage) REFERENCES plugin_wiki_page(id);
164 ALTER TABLE ONLY plugin_wiki_rating
165 ADD CONSTRAINT plugin_wiki_rating_raterpage_fkey FOREIGN KEY (raterpage) REFERENCES plugin_wiki_page(id);
166 ALTER TABLE ONLY plugin_wiki_recent
167 ADD CONSTRAINT plugin_wiki_recent_id_fkey FOREIGN KEY (id) REFERENCES plugin_wiki_page(id);
168 ALTER TABLE ONLY plugin_wiki_version
169 ADD CONSTRAINT plugin_wiki_version_id_fkey FOREIGN KEY (id) REFERENCES plugin_wiki_page(id);
170 ALTER TABLE ONLY plugin_wiki_versiondata
171 ADD CONSTRAINT plugin_wiki_versiondata_id_fkey FOREIGN KEY (id, version) REFERENCES plugin_wiki_version(id, version);
174 CREATE VIEW plugin_wiki_curr_page AS
175 SELECT p.id, p.pagename, p.hits, p.pagedata, p.cached_html, v.version, v.mtime, v.minor_edit, v.content, v.versiondata FROM ((plugin_wiki_page p JOIN plugin_wiki_version v USING (id)) JOIN plugin_wiki_recent r ON (((v.id = r.id) AND (v.version = r.latestversion))));
176 -- ALTER TABLE public.plugin_wiki_curr_page OWNER TO gforge;
178 CREATE VIEW plugin_wiki_existing_page AS
179 SELECT p.id, p.pagename, p.hits, p.pagedata, p.cached_html FROM (plugin_wiki_page p JOIN plugin_wiki_nonempty n USING (id));
180 -- ALTER TABLE public.plugin_wiki_existing_page OWNER TO gforge;
183 CREATE FUNCTION plugin_wiki_prepare_rename_page(integer, integer) RETURNS void
185 DELETE FROM plugin_wiki_page WHERE id = $2;
186 DELETE FROM plugin_wiki_version WHERE id = $2;
187 DELETE FROM plugin_wiki_recent WHERE id = $2;
188 DELETE FROM plugin_wiki_nonempty WHERE id = $2;
189 UPDATE plugin_wiki_link SET linkfrom = $1 WHERE linkfrom = $2;
190 UPDATE plugin_wiki_link SET linkto = $1 WHERE linkto = $2;
194 -- ALTER FUNCTION public.plugin_wiki_prepare_rename_page(integer, integer) OWNER TO gforge;
195 CREATE FUNCTION plugin_wiki_update_recent(integer, integer) RETURNS integer
197 DELETE FROM plugin_wiki_recent WHERE id = $1;
198 INSERT INTO plugin_wiki_recent (id, latestversion, latestmajor, latestminor)
199 SELECT id, MAX(version) AS latestversion,
200 MAX(CASE WHEN minor_edit = 0 THEN version END) AS latestmajor,
201 MAX(CASE WHEN minor_edit <> 0 THEN version END) AS latestminor
202 FROM plugin_wiki_version WHERE id = $2 GROUP BY id;
203 DELETE FROM plugin_wiki_nonempty WHERE id = $1;
204 INSERT INTO plugin_wiki_nonempty (id)
205 SELECT plugin_wiki_recent.id
206 FROM plugin_wiki_recent, plugin_wiki_version
207 WHERE plugin_wiki_recent.id = plugin_wiki_version.id
208 AND version = latestversion
210 AND plugin_wiki_recent.id = $1;
211 SELECT id FROM plugin_wiki_nonempty WHERE id = $1;
215 ALTER TABLE plugin_wiki_version ADD COLUMN idxFTI tsvector;
216 UPDATE plugin_wiki_version SET idxFTI=to_tsvector('default', content);
217 CREATE INDEX idxFTI_idx ON plugin_wiki_version USING gist(idxFTI);
218 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON plugin_wiki_version
219 FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
221 -- ALTER FUNCTION public.plugin_wiki_update_recent(integer, integer) OWNER TO gforge;
225 -- REVOKE ALL ON TABLE plugin_wiki_accesslog FROM PUBLIC;
226 -- REVOKE ALL ON TABLE plugin_wiki_accesslog FROM gforge;
227 -- GRANT ALL ON TABLE plugin_wiki_accesslog TO gforge;
230 -- REVOKE ALL ON TABLE plugin_wiki_recent FROM PUBLIC;
231 -- REVOKE ALL ON TABLE plugin_wiki_recent FROM gforge;
232 -- GRANT ALL ON TABLE plugin_wiki_recent TO gforge;
233 -- REVOKE ALL ON TABLE plugin_wiki_version FROM PUBLIC;
234 -- REVOKE ALL ON TABLE plugin_wiki_version FROM gforge;
235 -- GRANT ALL ON TABLE plugin_wiki_version TO gforge;
236 -- REVOKE ALL ON TABLE plugin_wiki_nonempty FROM PUBLIC;
237 -- REVOKE ALL ON TABLE plugin_wiki_nonempty FROM gforge;
238 -- GRANT ALL ON TABLE plugin_wiki_nonempty TO gforge;
239 -- REVOKE ALL ON TABLE plugin_wiki_link FROM PUBLIC;
240 -- REVOKE ALL ON TABLE plugin_wiki_link FROM gforge;
241 -- GRANT ALL ON TABLE plugin_wiki_link TO gforge;
242 -- REVOKE ALL ON TABLE plugin_wiki_member FROM PUBLIC;
243 -- REVOKE ALL ON TABLE plugin_wiki_member FROM gforge;
244 -- GRANT ALL ON TABLE plugin_wiki_member TO gforge;
245 -- REVOKE ALL ON TABLE plugin_wiki_pref FROM PUBLIC;
246 -- REVOKE ALL ON TABLE plugin_wiki_pref FROM gforge;
247 -- GRANT ALL ON TABLE plugin_wiki_pref TO gforge;
248 -- REVOKE ALL ON TABLE plugin_wiki_rating FROM PUBLIC;
249 -- REVOKE ALL ON TABLE plugin_wiki_rating FROM gforge;
250 -- GRANT ALL ON TABLE plugin_wiki_rating TO gforge;
251 -- REVOKE ALL ON TABLE plugin_wiki_session FROM PUBLIC;
252 -- REVOKE ALL ON TABLE plugin_wiki_session FROM gforge;
253 -- GRANT ALL ON TABLE plugin_wiki_session TO gforge;
255 CREATE TABLE plugin_wiki_config
257 group_id integer NOT NULL,
258 config_name character varying(40) NOT NULL,
259 config_value integer NOT NULL DEFAULT 0,
260 CONSTRAINT plugin_wiki_config_pkey PRIMARY KEY (group_id, config_name)
263 ALTER TABLE plugin_wiki_config OWNER TO gforge;
265 -- For existing wikis, we enable wikiwords as before.
266 -- Not doing it could break links.
267 INSERT INTO plugin_wiki_config
268 SELECT group_id AS group_id, 'DISABLE_MARKUP_WIKIWORD' AS config_name, '0' AS config_value
269 FROM group_plugin, plugins
270 WHERE group_plugin.plugin_id = plugins.plugin_id AND plugin_name = 'wiki';
272 -- For existing wikis, we disable spam prevention.
273 -- This is a change, but cannot be a problem.
274 INSERT INTO plugin_wiki_config
275 SELECT group_id AS group_id, 'NUM_SPAM_LINKS' AS config_name, '0' AS config_value
276 FROM group_plugin, plugins
277 WHERE group_plugin.plugin_id = plugins.plugin_id AND plugin_name = 'wiki';