schemas/psql-initialize.sql

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