schemas/psql-initialize.sql

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