tests/unit/psql-test-initialize.sql

   1 -- $Id: psql-test-initialize.sql,v 1.1 2006-06-05 09:33:22 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
  19 \set nonempty_tbl       :prefix 'nonempty'
  20 \set nonmt_id_idx       :prefix 'nonmt_id_idx'
  21
  22 \set link_tbl           :prefix 'link'
  23 \set link_from_idx      :prefix 'link_from_idx'
  24 \set link_to_idx        :prefix 'link_to_idx'
  25 \set relation_idx       :prefix 'relation_idx'
  26
  27 \set session_tbl        :prefix 'session'
  28 \set sess_id_idx        :prefix 'sess_id_idx'
  29 \set sess_date_idx      :prefix 'sess_date_idx'
  30 \set sess_ip_idx        :prefix 'sess_ip_idx'
  31
  32 \set pref_tbl           :prefix 'pref'
  33 \set pref_id_idx        :prefix 'pref_id_idx'
  34 \set pref_pkey          :prefix 'pref_pkey'
  35
  36 --\set user_tbl                 :prefix 'users'
  37 --\set user_id_idx      :prefix 'users_id_idx'
  38 \set member_tbl         :prefix 'member'
  39 \set member_id_idx      :prefix 'member_id_idx'
  40 \set member_group_idx   :prefix 'member_group_idx'
  41
  42 \set rating_tbl         :prefix 'rating'
  43 \set rating_id_idx      :prefix 'rating_id_idx'
  44
  45 \set accesslog_tbl      :prefix 'accesslog'
  46 \set accesslog_time_idx :prefix 'log_time_idx'
  47 \set accesslog_host_idx :prefix 'log_host_idx'
  48
  49 \echo Dropping all test relations
  50
  51 DROP TABLE :version_tbl CASCADE;
  52 DROP TABLE :recent_tbl CASCADE;
  53 DROP TABLE :nonempty_tbl CASCADE;
  54 DROP TABLE :link_tbl CASCADE;
  55 DROP TABLE :rating_tbl CASCADE;
  56
  57 DROP TABLE :page_tbl CASCADE;
  58 DROP SEQUENCE :page_id_seq;
  59
  60 DROP TABLE :member_tbl CASCADE;
  61 DROP TABLE :pref_tbl CASCADE;
  62 DROP TABLE :session_tbl CASCADE;
  63 DROP TABLE :accesslog_tbl CASCADE;
  64
  65 DROP FUNCTION update_recent (INT4, INT4);
  66 DROP FUNCTION prepare_rename_page (INT4, INT4);
  67
  68 ------------------------------------------------------------
  69
  70 \echo Creating :page_tbl
  71 CREATE TABLE :page_tbl (
  72         id              SERIAL PRIMARY KEY,
  73         pagename        VARCHAR(100) NOT NULL UNIQUE CHECK (pagename <> ''),
  74         hits            INT4 NOT NULL DEFAULT 0,
  75         pagedata        TEXT NOT NULL DEFAULT '',
  76         cached_html     bytea DEFAULT ''
  77 );
  78 -- CREATE UNIQUE INDEX :page_id_idx ON :page_tbl (id);
  79 -- CREATE UNIQUE INDEX :page_name_idx ON :page_tbl (pagename);
  80
  81 \echo Creating :version_tbl
  82 CREATE TABLE :version_tbl (
  83         id              INT4 REFERENCES :page_tbl ON DELETE CASCADE,
  84         version         INT4 NOT NULL,
  85         mtime           INT4 NOT NULL,
  86 -- FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
  87         minor_edit      INT2 DEFAULT 0,
  88 -- use bytea instead?
  89         content         TEXT NOT NULL DEFAULT '',
  90         versiondata     TEXT NOT NULL DEFAULT ''
  91 );
  92 CREATE UNIQUE INDEX :vers_id_idx ON :version_tbl (id, version);
  93 CREATE INDEX :vers_mtime_idx ON :version_tbl (mtime);
  94
  95 \echo Creating :recent_tbl
  96 CREATE TABLE :recent_tbl (
  97         id              INT4 REFERENCES :page_tbl ON DELETE CASCADE,
  98         latestversion   INT4,
  99         latestmajor     INT4,
 100         latestminor     INT4,
 101         FOREIGN KEY (id, latestversion) REFERENCES :version_tbl (id, version),
 102         CHECK (latestminor >= latestmajor)
 103 );
 104 CREATE UNIQUE INDEX :recent_id_idx ON :recent_tbl (id);
 105 CREATE INDEX recent_latestversion_idx ON :recent_tbl (latestversion);
 106
 107 \echo Creating :nonempty_tbl
 108 CREATE TABLE :nonempty_tbl (
 109         id              INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE
 110 );
 111 CREATE UNIQUE INDEX :nonmt_id_idx ON :nonempty_tbl (id);
 112
 113 \echo Creating :link_tbl
 114 CREATE TABLE :link_tbl (
 115         linkfrom  INT4 NOT NULL REFERENCES :page_tbl,
 116         linkto    INT4 NOT NULL REFERENCES :page_tbl,
 117         relation  INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE
 118 );
 119 CREATE INDEX :link_from_idx ON :link_tbl (linkfrom);
 120 CREATE INDEX :link_to_idx   ON :link_tbl (linkto);
 121 CREATE INDEX :relation_idx  ON :link_tbl (relation);
 122
 123 -- if you plan to use the wikilens theme
 124 \echo Creating :rating_tbl
 125 CREATE TABLE :rating_tbl (
 126         dimension    INTEGER NOT NULL,
 127         raterpage    INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE,
 128         rateepage    INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE,
 129         ratingvalue  FLOAT NOT NULL,
 130         rateeversion INT8 NOT NULL,
 131         tstamp       TIMESTAMP NOT NULL
 132 );
 133 CREATE UNIQUE INDEX :rating_id_idx ON :rating_tbl (dimension, raterpage, rateepage);
 134
 135 --================================================================
 136 -- end of page relations
 137 --================================================================
 138
 139 \echo Creating :session_tbl
 140 CREATE TABLE :session_tbl (
 141         sess_id         CHAR(32) PRIMARY KEY,
 142         sess_data       bytea NOT NULL,
 143         sess_date       INT4,
 144         sess_ip         CHAR(40) NOT NULL
 145 );
 146 -- CREATE UNIQUE INDEX :sess_id_idx ON :session_tbl (sess_id);
 147 CREATE INDEX :sess_date_idx ON :session_tbl (sess_date);
 148 CREATE INDEX :sess_ip_idx   ON :session_tbl (sess_ip);
 149
 150 -- Optional DB Auth and Prefs
 151 -- For these tables below the default table prefix must be used
 152 -- in the DBAuthParam SQL statements also.
 153
 154 \echo Creating :pref_tbl
 155 CREATE TABLE :pref_tbl (
 156         userid    CHAR(48) PRIMARY KEY,
 157         prefs     TEXT NULL DEFAULT '',
 158         passwd    CHAR(48) DEFAULT '',
 159         groupname CHAR(48) DEFAULT 'users'
 160 );
 161 -- CREATE UNIQUE INDEX :pref_id_idx ON :pref_tbl (userid);
 162 CREATE INDEX pref_group_idx ON :pref_tbl (groupname);
 163
 164 -- Use the member table, if you need it for n:m user-group relations,
 165 -- and adjust your DBAUTH_AUTH_ SQL statements.
 166 CREATE TABLE :member_tbl (
 167         userid    CHAR(48) NOT NULL REFERENCES :pref_tbl ON DELETE CASCADE,
 168         groupname CHAR(48) NOT NULL DEFAULT 'users'
 169 );
 170 CREATE INDEX :member_id_idx    ON :member_tbl (userid);
 171 CREATE INDEX :member_group_idx ON :member_tbl (groupname);
 172
 173 -- if ACCESS_LOG_SQL > 0
 174 -- only if you need fast log-analysis (spam prevention, recent referrers)
 175 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
 176 \echo Creating :accesslog_tbl
 177 CREATE TABLE :accesslog_tbl (
 178         time_stamp       INT,
 179         remote_host      VARCHAR(50),
 180         remote_user      VARCHAR(50),
 181         request_method   VARCHAR(10),
 182         request_line     VARCHAR(255),
 183         request_args     VARCHAR(255),
 184         request_file     VARCHAR(255),
 185         request_uri      VARCHAR(255),
 186         request_time     CHAR(28),
 187         status           INT2,
 188         bytes_sent       INT4,
 189         referer          VARCHAR(255),
 190         agent            VARCHAR(255),
 191         request_duration FLOAT
 192 );
 193 CREATE INDEX :accesslog_time_idx ON :accesslog_tbl (time_stamp);
 194 CREATE INDEX :accesslog_host_idx ON :accesslog_tbl (remote_host);
 195 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
 196
 197 --================================================================
 198
 199 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
 200 -- at first init it for the database:
 201 -- $ psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
 202
 203 -- example of ISpell dictionary
 204 --   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';
 205 -- example of synonym dict
 206 --   UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
 207
 208 \echo Initializing tsearch2 indices
 209 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
 210 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
 211 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
 212 VACUUM FULL ANALYZE;
 213 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
 214 VACUUM FULL ANALYZE;
 215 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
 216        FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
 217
 218 --================================================================
 219
 220 \echo You might want to ignore the following errors or run
 221 \echo /usr/sbin/createuser -S -R -d :httpd_user
 222
 223 \echo Applying permissions for role :httpd_user
 224 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl          TO :httpd_user;
 225 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl       TO :httpd_user;
 226 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl        TO :httpd_user;
 227 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl      TO :httpd_user;
 228 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl          TO :httpd_user;
 229
 230 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl       TO :httpd_user;
 231 -- you may want to fine tune this:
 232 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl          TO :httpd_user;
 233 -- GRANT SELECT ON :user_tbl                            TO :httpd_user;
 234 GRANT SELECT ON :member_tbl                             TO :httpd_user;
 235 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl        TO :httpd_user;
 236 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl     TO :httpd_user;
 237
 238 --================================================================
 239 -- some stored procedures to put unneccesary syntax into the server
 240
 241 \echo Initializing stored procedures
 242
 243 CREATE OR REPLACE FUNCTION update_recent (id INT4, version INT4)
 244         RETURNS void AS '
 245 DELETE FROM recent  WHERE id=$1;
 246 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
 247   SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0  THEN version END),
 248                            MAX(CASE WHEN minor_edit<>0 THEN version END)
 249     FROM version WHERE id=$2 GROUP BY id;
 250 DELETE FROM nonempty WHERE id=$1;
 251 INSERT INTO nonempty (id)
 252   SELECT recent.id
 253     FROM recent, version
 254     WHERE recent.id=version.id
 255           AND version=latestversion
 256           AND content<>''''
 257           AND recent.id=$1;
 258 ' LANGUAGE SQL;
 259
 260 CREATE OR REPLACE FUNCTION prepare_rename_page (oldid INT4, newid INT4)
 261         RETURNS void AS '
 262 DELETE FROM page     WHERE id=$2;
 263 DELETE FROM version  WHERE id=$2;
 264 DELETE FROM recent   WHERE id=$2;
 265 DELETE FROM nonempty WHERE id=$2;
 266 -- We have to fix all referring tables to the old id
 267 UPDATE link SET linkfrom=$1 WHERE linkfrom=$2;
 268 UPDATE link SET linkto=$1   WHERE linkto=$2;
 269 ' LANGUAGE sql;