tests/unit/psql-test-initialize.sql

   1 -- $Id: psql-test-initialize.sql,v 1.2 2006-12-23 13:01:59 rurban Exp $
   2 -- for the regression suite
   3
   4 \set prefix     'test_'
   5 \set httpd_user 'wikiuser'
   6
   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'
  11
  12 \set version_tbl        :prefix 'version'
  13 \set vers_id_idx        :prefix 'vers_id_idx'
  14 \set vers_mtime_idx     :prefix 'vers_mtime_idx'
  15
  16 \set recent_tbl         :prefix 'recent'
  17 \set recent_id_idx      :prefix 'recent_id_idx'
  18 \set recent_lv_idx      :prefix 'recent_lv_idx'
  19
  20 \set nonempty_tbl       :prefix 'nonempty'
  21 \set nonmt_id_idx       :prefix 'nonmt_id_idx'
  22
  23 \set link_tbl           :prefix 'link'
  24 \set link_from_idx      :prefix 'link_from_idx'
  25 \set link_to_idx        :prefix 'link_to_idx'
  26 \set relation_idx       :prefix 'relation_idx'
  27
  28 \set pagedata_tbl       :prefix 'pagedata'
  29 \set pagedata_id_idx    :prefix 'pagedata_id_idx'
  30 \set versiondata_tbl    :prefix 'versiondata'
  31 \set pageperm_tbl       :prefix 'pageperm'
  32 \set pageperm_id_idx    :prefix 'pageperm_id_idx'
  33 \set pageperm_access_idx :prefix 'pageperm_access_idx'
  34 \set existing_page_view :prefix 'existing_page'
  35 \set curr_page_view     :prefix 'curr_page'
  36
  37 \set session_tbl        :prefix 'session'
  38 \set sess_id_idx        :prefix 'sess_id_idx'
  39 \set sess_date_idx      :prefix 'sess_date_idx'
  40 \set sess_ip_idx        :prefix 'sess_ip_idx'
  41
  42 \set pref_tbl           :prefix 'pref'
  43 \set pref_id_idx        :prefix 'pref_id_idx'
  44 \set pref_pkey          :prefix 'pref_pkey'
  45
  46 --\set user_tbl                 :prefix 'users'
  47 --\set user_id_idx      :prefix 'users_id_idx'
  48 \set member_tbl         :prefix 'member'
  49 \set member_id_idx      :prefix 'member_id_idx'
  50 \set member_group_idx   :prefix 'member_group_idx'
  51
  52 \set rating_tbl         :prefix 'rating'
  53 \set rating_id_idx      :prefix 'rating_id_idx'
  54
  55 \set accesslog_tbl      :prefix 'accesslog'
  56 \set accesslog_time_idx :prefix 'log_time_idx'
  57 \set accesslog_host_idx :prefix 'log_host_idx'
  58
  59 \set update_recent_fn   :prefix 'update_recent'
  60 \set prepare_rename_fn  :prefix 'prepare_rename_page'
  61
  62 \echo Dropping all test relations
  63
  64 DROP TABLE :version_tbl CASCADE;
  65 DROP TABLE :recent_tbl CASCADE;
  66 DROP TABLE :nonempty_tbl CASCADE;
  67 DROP TABLE :link_tbl CASCADE;
  68 DROP TABLE :rating_tbl CASCADE;
  69
  70 DROP TABLE :pagedata_tbl CASCADE;
  71 DROP TABLE :versiondata_tbl CASCADE;
  72 DROP TABLE :pageperm_tbl CASCADE;
  73
  74 DROP VIEW :existing_page_view;
  75 DROP VIEW :curr_page_view;
  76
  77 DROP TABLE :page_tbl CASCADE;
  78 --DROP SEQUENCE :page_id_seq;
  79
  80 DROP TABLE :member_tbl CASCADE;
  81 DROP TABLE :pref_tbl CASCADE;
  82 DROP TABLE :session_tbl CASCADE;
  83 DROP TABLE :accesslog_tbl CASCADE;
  84
  85 DROP FUNCTION :update_recent_fn (INT4, INT4);
  86 DROP FUNCTION :prepare_rename_fn (INT4, INT4);
  87
  88 ------------------------------------------------------------
  89
  90 \echo Creating :page_tbl
  91 CREATE TABLE :page_tbl (
  92         id              SERIAL PRIMARY KEY,
  93         pagename        VARCHAR(100) NOT NULL UNIQUE CHECK (pagename <> ''),
  94         hits            INT4 NOT NULL DEFAULT 0,
  95         pagedata        TEXT NOT NULL DEFAULT '',
  96         cached_html     bytea DEFAULT ''
  97 );
  98 -- CREATE UNIQUE INDEX :page_id_idx ON :page_tbl (id);
  99 -- CREATE UNIQUE INDEX :page_name_idx ON :page_tbl (pagename);
 100
 101 -- we use 0 <=> global_data to satisfy the relation = 0 constraint
 102 INSERT INTO :page_tbl VALUES (0,'global_data',0,'','');
 103
 104 \echo Creating :version_tbl
 105 CREATE TABLE :version_tbl (
 106         id              INT4 REFERENCES :page_tbl,
 107         version         INT4 NOT NULL,
 108         mtime           INT4 NOT NULL,
 109 -- FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
 110         minor_edit      INT2 DEFAULT 0,
 111 -- use bytea instead?
 112         content         TEXT NOT NULL DEFAULT '',
 113         versiondata     TEXT NOT NULL DEFAULT ''
 114 );
 115 CREATE UNIQUE INDEX :vers_id_idx ON :version_tbl (id, version);
 116 CREATE INDEX :vers_mtime_idx ON :version_tbl (mtime);
 117 -- deletion order: version, recent, nonempty
 118
 119 \echo Creating :recent_tbl
 120 CREATE TABLE :recent_tbl (
 121         id              INT4 REFERENCES :page_tbl,
 122         latestversion   INT4,
 123         latestmajor     INT4,
 124         latestminor     INT4,
 125         FOREIGN KEY (id, latestversion) REFERENCES :version_tbl (id, version),
 126         CHECK (latestminor >= latestmajor)
 127 );
 128 CREATE UNIQUE INDEX :recent_id_idx ON :recent_tbl (id);
 129 CREATE INDEX :recent_lv_idx ON :recent_tbl (latestversion);
 130
 131 \echo Creating :nonempty_tbl
 132 CREATE TABLE :nonempty_tbl (
 133         id              INT4 NOT NULL REFERENCES :page_tbl
 134 );
 135 CREATE UNIQUE INDEX :nonmt_id_idx ON :nonempty_tbl (id);
 136
 137 \echo Creating experimental pagedata (not yet used)
 138 CREATE TABLE :pagedata_tbl (
 139         id      INT4 NOT NULL REFERENCES :page_tbl,
 140         date    INT4,
 141         locked  BOOLEAN,
 142         rest    TEXT NOT NULL DEFAULT ''
 143 );
 144 CREATE INDEX :pagedata_id_idx ON pagedata (id);
 145
 146 \echo Creating experimental versiondata (not yet used)
 147 CREATE TABLE :versiondata_tbl (
 148         id        INT4 NOT NULL,
 149         version   INT4 NOT NULL,
 150         markup    INT2 DEFAULT 2,
 151         author    VARCHAR(48),
 152         author_id VARCHAR(48),
 153         pagetype  VARCHAR(20) DEFAULT 'wikitext',
 154         rest      TEXT NOT NULL DEFAULT '',
 155         FOREIGN KEY (id, version) REFERENCES :version_tbl (id, version)
 156 );
 157 \echo Creating experimental pageperm (not yet used)
 158 CREATE TABLE :pageperm_tbl (
 159         id       INT4 NOT NULL REFERENCES :page_tbl(id),
 160         -- view,edit,create,list,remove,change,dump
 161         access   CHAR(12) NOT NULL,
 162         groupname VARCHAR(48),
 163         allowed  BOOLEAN
 164 );
 165 CREATE INDEX :pageperm_id_idx ON pageperm (id);
 166 CREATE INDEX :pageperm_access_idx ON pageperm (access);
 167
 168 \echo Creating experimental page views (not yet used)
 169
 170 -- nonempty versiondata
 171 CREATE VIEW :existing_page_view AS
 172   SELECT * FROM :page_tbl P INNER JOIN :nonempty_tbl N USING (id);
 173
 174 -- latest page version
 175 CREATE VIEW :curr_page_view AS
 176   SELECT P.id,P.pagename,P.hits,P.pagedata,P.cached_html,
 177          V.version,V.mtime,V.minor_edit,V.content,V.versiondata
 178   FROM :page_tbl P
 179     JOIN :version_tbl V USING (id)
 180     JOIN :recent_tbl  R ON (V.id=R.id AND V.version=R.latestversion);
 181
 182 \echo Creating :link_tbl
 183 CREATE TABLE :link_tbl (
 184         linkfrom  INT4 NOT NULL REFERENCES :page_tbl,
 185         linkto    INT4 NOT NULL REFERENCES :page_tbl,
 186         relation  INT4
 187 );
 188 CREATE INDEX :link_from_idx ON :link_tbl (linkfrom);
 189 CREATE INDEX :link_to_idx   ON :link_tbl (linkto);
 190 CREATE INDEX :relation_idx  ON :link_tbl (relation);
 191 -- update:
 192 -- ALTER TABLE link DROP CONSTRAINT link_relation_fkey;
 193
 194 -- if you plan to use the wikilens theme
 195 \echo Creating :rating_tbl
 196 CREATE TABLE :rating_tbl (
 197         dimension    INTEGER NOT NULL,
 198         raterpage    INT8 NOT NULL REFERENCES :page_tbl,
 199         rateepage    INT8 NOT NULL REFERENCES :page_tbl,
 200         ratingvalue  FLOAT NOT NULL,
 201         rateeversion INT8 NOT NULL,
 202         tstamp       TIMESTAMP NOT NULL
 203 );
 204 CREATE UNIQUE INDEX :rating_id_idx ON :rating_tbl (dimension, raterpage, rateepage);
 205
 206 --================================================================
 207 -- end of page relations
 208 --================================================================
 209
 210 \echo Creating :session_tbl
 211 CREATE TABLE :session_tbl (
 212         sess_id         CHAR(32) PRIMARY KEY,
 213         sess_data       bytea NOT NULL,
 214         sess_date       INT4,
 215         sess_ip         CHAR(40) NOT NULL
 216 );
 217 -- CREATE UNIQUE INDEX :sess_id_idx ON :session_tbl (sess_id);
 218 CREATE INDEX :sess_date_idx ON :session_tbl (sess_date);
 219 CREATE INDEX :sess_ip_idx   ON :session_tbl (sess_ip);
 220
 221 -- Optional DB Auth and Prefs
 222 -- For these tables below the default table prefix must be used
 223 -- in the DBAuthParam SQL statements also.
 224
 225 \echo Creating :pref_tbl
 226 CREATE TABLE :pref_tbl (
 227         userid    CHAR(48) PRIMARY KEY,
 228         prefs     TEXT NULL DEFAULT '',
 229         passwd    CHAR(48) DEFAULT '',
 230         groupname CHAR(48) DEFAULT 'users'
 231 );
 232 -- CREATE UNIQUE INDEX :pref_id_idx ON :pref_tbl (userid);
 233 CREATE INDEX pref_group_idx ON :pref_tbl (groupname);
 234
 235 -- Use the member table, if you need it for n:m user-group relations,
 236 -- and adjust your DBAUTH_AUTH_ SQL statements.
 237 CREATE TABLE :member_tbl (
 238         userid    CHAR(48) NOT NULL REFERENCES :pref_tbl,
 239         groupname CHAR(48) NOT NULL DEFAULT 'users'
 240 );
 241 CREATE INDEX :member_id_idx    ON :member_tbl (userid);
 242 CREATE INDEX :member_group_idx ON :member_tbl (groupname);
 243
 244 -- if ACCESS_LOG_SQL > 0
 245 -- only if you need fast log-analysis (spam prevention, recent referrers)
 246 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
 247 \echo Creating :accesslog_tbl
 248 CREATE TABLE :accesslog_tbl (
 249         time_stamp       INT,
 250         remote_host      VARCHAR(100),
 251         remote_user      VARCHAR(50),
 252         request_method   VARCHAR(10),
 253         request_line     VARCHAR(255),
 254         request_args     VARCHAR(255),
 255         request_file     VARCHAR(255),
 256         request_uri      VARCHAR(255),
 257         request_time     CHAR(28),
 258         status           INT2,
 259         bytes_sent       INT4,
 260         referer          VARCHAR(255),
 261         agent            VARCHAR(255),
 262         request_duration FLOAT
 263 );
 264 CREATE INDEX :accesslog_time_idx ON :accesslog_tbl (time_stamp);
 265 CREATE INDEX :accesslog_host_idx ON :accesslog_tbl (remote_host);
 266 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
 267
 268 --================================================================
 269
 270 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
 271 -- at first init it for the database:
 272 -- $ psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
 273
 274 -- example of ISpell dictionary
 275 --   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';
 276 -- example of synonym dict
 277 --   UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
 278
 279 \echo Initializing tsearch2 indices
 280 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
 281 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
 282 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
 283 VACUUM FULL ANALYZE;
 284 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
 285 VACUUM FULL ANALYZE;
 286 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
 287        FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
 288
 289 --================================================================
 290
 291 \echo You might want to ignore the following errors or run
 292 \echo /usr/sbin/createuser -S -R -d  :httpd_user
 293
 294 \echo Applying permissions for role :httpd_user
 295 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl          TO :httpd_user;
 296 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl       TO :httpd_user;
 297 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl        TO :httpd_user;
 298 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl      TO :httpd_user;
 299 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl          TO :httpd_user;
 300
 301 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl       TO :httpd_user;
 302 -- you may want to fine tune this:
 303 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl          TO :httpd_user;
 304 -- GRANT SELECT ON :user_tbl                            TO :httpd_user;
 305 GRANT SELECT ON :member_tbl                             TO :httpd_user;
 306 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl        TO :httpd_user;
 307 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl     TO :httpd_user;
 308
 309 --================================================================
 310 -- some stored procedures to put unneccesary syntax into the server
 311
 312 \echo Initializing stored procedures
 313
 314 -- id, version
 315 CREATE OR REPLACE FUNCTION :update_recent_fn (INT4, INT4)
 316         RETURNS integer AS $$
 317 DELETE FROM test_recent WHERE id = $1;
 318 INSERT INTO test_recent (id, latestversion, latestmajor, latestminor)
 319   SELECT id, MAX(version) AS latestversion,
 320              MAX(CASE WHEN minor_edit =  0 THEN version END) AS latestmajor,
 321              MAX(CASE WHEN minor_edit <> 0 THEN version END) AS latestminor
 322     FROM test_version version WHERE id = $2 GROUP BY id;
 323 DELETE FROM test_nonempty WHERE id = $1;
 324 INSERT INTO test_nonempty (id)
 325   SELECT recent.id
 326     FROM test_recent recent, test_version version
 327     WHERE recent.id = version.id
 328           AND version = latestversion
 329           AND content <> ''
 330           AND recent.id = $1;
 331 SELECT id FROM test_nonempty WHERE id = $1;
 332 $$ LANGUAGE SQL;
 333
 334 -- oldid, newid
 335 CREATE OR REPLACE FUNCTION :prepare_rename_fn (INT4, INT4)
 336         RETURNS void AS $$
 337 DELETE FROM test_page     WHERE id = $2;
 338 DELETE FROM test_version  WHERE id = $2;
 339 DELETE FROM test_recent   WHERE id = $2;
 340 DELETE FROM test_nonempty WHERE id = $2;
 341 -- We have to fix all referring tables to the old id
 342 UPDATE test_link SET linkfrom = $1 WHERE linkfrom = $2;
 343 UPDATE test_link SET linkto = $1   WHERE linkto = $2;
 344 $$ LANGUAGE SQL;