schemas/psql-initialize.sql

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