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