schemas/psql-initialize.sql

   1 -- $Id: psql-initialize.sql,v 1.6 2005-09-28 19:27:23 rurban Exp $
   2
   3 \set QUIET
   4
   5 --================================================================
   6 -- Prefix for table names.
   7 --
   8 -- You should set this to the same value you specified for
   9 -- DATABASE_PREFIX in config.ini
  10
  11 \set prefix     ''
  12
  13 --================================================================
  14 -- Which postgres user gets access to the tables?
  15 --
  16 -- You should set this to the name of the postgres
  17 -- user who will be accessing the tables.
  18 --
  19 -- Commonly, connections from php are made under
  20 -- the user name of 'nobody', 'apache' or 'www'.
  21
  22 \set httpd_user 'phpwiki'
  23
  24 --================================================================
  25 --
  26 -- Don't modify below this point unless you know what you are doing.
  27 --
  28 --================================================================
  29
  30 \set qprefix '\'' :prefix '\''
  31 \set qhttp_user '\'' :httpd_user '\''
  32 \echo Initializing PhpWiki tables with:
  33 \echo '       prefix = ' :qprefix
  34 \echo '   httpd_user = ' :qhttp_user
  35 \echo
  36 \echo 'Expect some \'ERROR: relation \'*\'  already exists\' errors '
  37 \echo 'preventing from overwriting existing tables,sequences,indices.'
  38
  39 \set page_tbl           :prefix 'page'
  40 \set page_id_seq        :prefix 'page_id_seq'
  41 \set page_id_idx        :prefix 'page_id_idx'
  42 \set page_name_idx      :prefix 'page_name_idx'
  43
  44 \set version_tbl        :prefix 'version'
  45 \set vers_id_idx        :prefix 'vers_id_idx'
  46 \set vers_mtime_idx     :prefix 'vers_mtime_idx'
  47
  48 \set recent_tbl         :prefix 'recent'
  49 \set recent_id_idx      :prefix 'recent_id_idx'
  50
  51 \set nonempty_tbl       :prefix 'nonempty'
  52 \set nonmt_id_idx       :prefix 'nonmt_id_idx'
  53
  54 \set link_tbl           :prefix 'link'
  55 \set link_from_idx      :prefix 'link_from_idx'
  56 \set link_to_idx        :prefix 'link_to_idx'
  57
  58 \set session_tbl        :prefix 'session'
  59 \set sess_id_idx        :prefix 'sess_id_idx'
  60 \set sess_date_idx      :prefix 'sess_date_idx'
  61 \set sess_ip_idx        :prefix 'sess_ip_idx'
  62
  63 \set pref_tbl           :prefix 'pref'
  64 \set pref_id_idx        :prefix 'pref_id_idx'
  65 \set user_tbl           :prefix 'users'
  66 \set user_id_idx        :prefix 'users_id_idx'
  67 \set member_tbl         :prefix 'member'
  68 \set member_id_idx      :prefix 'member_id_idx'
  69 \set member_group_idx   :prefix 'member_group_idx'
  70
  71 \set rating_tbl         :prefix 'rating'
  72 \set rating_id_idx      :prefix 'rating_id_idx'
  73
  74 \set accesslog_tbl      :prefix 'accesslog'
  75 \set accesslog_time_idx :prefix 'log_time_idx'
  76 \set accesslog_host_idx :prefix 'log_host_idx'
  77
  78 \echo Creating :page_tbl
  79 CREATE TABLE :page_tbl (
  80         id              SERIAL PRIMARY KEY,
  81         pagename        VARCHAR(100) NOT NULL UNIQUE CHECK (pagename <> ''),
  82         hits            INT4 NOT NULL DEFAULT 0,
  83         pagedata        TEXT NOT NULL DEFAULT '',
  84         cached_html     bytea DEFAULT ''
  85 );
  86 -- CREATE UNIQUE INDEX :page_id_idx ON :page_tbl (id);
  87 CREATE UNIQUE INDEX :page_name_idx ON :page_tbl (pagename);
  88
  89 \echo Creating :version_tbl
  90 CREATE TABLE :version_tbl (
  91         id              INT4 REFERENCES :page_tbl ON DELETE CASCADE,
  92         version         INT4 NOT NULL,
  93         mtime           INT4 NOT NULL,
  94 --FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
  95         minor_edit      INT2 DEFAULT 0,
  96         content         TEXT NOT NULL DEFAULT '',
  97         versiondata     TEXT NOT NULL DEFAULT ''
  98 );
  99 CREATE UNIQUE INDEX :vers_id_idx ON :version_tbl (id, version);
 100 CREATE INDEX :vers_mtime_idx ON :version_tbl (mtime);
 101
 102 \echo Creating :recent_tbl
 103 CREATE TABLE :recent_tbl (
 104         id              INT4 REFERENCES :page_tbl ON DELETE CASCADE,
 105         latestversion   INT4,
 106         latestmajor     INT4,
 107         latestminor     INT4
 108 );
 109 CREATE UNIQUE INDEX :recent_id_idx ON :recent_tbl (id);
 110
 111 \echo Creating :nonempty_tbl
 112 CREATE TABLE :nonempty_tbl (
 113         INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE
 114 );
 115 CREATE UNIQUE INDEX :nonmt_id_idx ON :nonempty_tbl (id);
 116
 117 \echo Creating :link_tbl
 118 CREATE TABLE :link_tbl (
 119         linkfrom        INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE,
 120         linkto          INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE
 121 );
 122 CREATE INDEX :link_from_idx ON :link_tbl (linkfrom);
 123 CREATE INDEX :link_to_idx   ON :link_tbl (linkto);
 124
 125 -- if you plan to use the wikilens theme
 126 \echo Creating :rating_tbl
 127 CREATE TABLE :rating_tbl (
 128         dimension INTEGER NOT NULL,
 129         raterpage INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE,
 130         rateepage INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE,
 131         ratingvalue FLOAT NOT NULL,
 132         rateeversion INT8 NOT NULL,
 133         tstamp TIMESTAMP NOT NULL
 134 );
 135 CREATE UNIQUE INDEX :rating_id_idx ON :rating_tbl (dimension, raterpage, rateepage);
 136
 137 --================================================================
 138 -- end of page relations
 139 --================================================================
 140
 141 \echo Creating :session_tbl
 142 CREATE TABLE :session_tbl (
 143         sess_id         CHAR(32) PRIMARY KEY,
 144         sess_data       bytea NOT NULL,
 145         sess_date       INT4,
 146         sess_ip         CHAR(40) NOT NULL
 147 );
 148 -- CREATE UNIQUE INDEX :sess_id_idx ON :session_tbl (sess_id);
 149 CREATE INDEX :sess_date_idx ON :session_tbl (sess_date);
 150 CREATE INDEX :sess_ip_idx   ON :session_tbl (sess_ip);
 151
 152 -- Optional DB Auth and Prefs
 153 -- For these tables below the default table prefix must be used
 154 -- in the DBAuthParam SQL statements also.
 155
 156 \echo Creating :pref_tbl
 157 CREATE TABLE :pref_tbl (
 158         userid    CHAR(48) PRIMARY KEY,
 159         prefs     TEXT NULL DEFAULT '',
 160         passwd    CHAR(48) DEFAULT '',
 161         groupname CHAR(48) DEFAULT 'users'
 162 );
 163 -- CREATE UNIQUE INDEX :pref_id_idx ON :pref_tbl (userid);
 164 CREATE INDEX :pref_group_idx ON :pref_tbl (groupname);
 165
 166 -- Use the member table, if you need it for n:m user-group relations,
 167 -- and adjust your DBAUTH_AUTH_ SQL statements.
 168 CREATE TABLE :member_tbl (
 169         userid CHAR(48) NOT NULL REFERENCES :pref_tbl ON DELETE CASCADE,
 170         groupname CHAR(48) NOT NULL DEFAULT 'users'
 171 );
 172 CREATE INDEX :member_id_idx    ON :member_tbl (userid);
 173 CREATE INDEX :member_group_idx ON :member_tbl (groupname);
 174
 175 -- if ACCESS_LOG_SQL > 0
 176 -- only if you need fast log-analysis (spam prevention, recent referrers)
 177 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
 178 \echo Creating :accesslog_tbl
 179 CREATE TABLE :accesslog_tbl (
 180         time_stamp    INT,
 181         remote_host   VARCHAR(50),
 182         remote_user   VARCHAR(50),
 183         request_method VARCHAR(10),
 184         request_line  VARCHAR(255),
 185         request_args  VARCHAR(255),
 186         request_file  VARCHAR(255),
 187         request_uri   VARCHAR(255),
 188         request_time  CHAR(28),
 189         status        INT2,
 190         bytes_sent    INT4,
 191         referer       VARCHAR(255),
 192         agent         VARCHAR(255),
 193         request_duration FLOAT
 194 );
 195 CREATE INDEX :accesslog_time_idx ON :accesslog_tbl (time_stamp);
 196 CREATE INDEX :accesslog_host_idx ON :accesslog_tbl (remote_host);
 197 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
 198
 199 --================================================================
 200
 201 \echo You might want to ignore the following errors or run
 202 \echo /usr/sbin/createuser -S -R -d  :httpd_user
 203
 204 \echo Applying permissions for role :httpd_user
 205 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl          TO :httpd_user;
 206 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl       TO :httpd_user;
 207 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl        TO :httpd_user;
 208 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl      TO :httpd_user;
 209 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl          TO :httpd_user;
 210
 211 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl       TO :httpd_user;
 212 -- you may want to fine tune this:
 213 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl          TO :httpd_user;
 214 -- GRANT SELECT ON :user_tbl    TO :httpd_user;
 215 GRANT SELECT ON :member_tbl     TO :httpd_user;
 216 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl        TO :httpd_user;
 217 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl     TO :httpd_user;