schemas/psql-1_3_12.sql

   1 -- $Id: psql-1_3_12.sql,v 1.1 2005-11-14 22:20:21 rurban Exp $
   2
   3 \set QUIET
   4
   5 -- Init the database with:
   6 -- $ /usr/sbin/createdb phpwiki
   7 -- $ /usr/sbin/createuser -S -R -d phpwiki # (see httpd_user below)
   8 -- $ /usr/bin/psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
   9 -- $ /usr/bin/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 page_tbl           :prefix 'page'
  38 \set page_id_seq        :prefix 'page_id_seq'
  39 \set page_id_idx        :prefix 'page_id_idx'
  40 \set page_name_idx      :prefix 'page_name_idx'
  41
  42 \set version_tbl        :prefix 'version'
  43 \set vers_id_idx        :prefix 'vers_id_idx'
  44 \set vers_mtime_idx     :prefix 'vers_mtime_idx'
  45
  46 \set recent_tbl         :prefix 'recent'
  47 \set recent_id_idx      :prefix 'recent_id_idx'
  48
  49 \set nonempty_tbl       :prefix 'nonempty'
  50 \set nonmt_id_idx       :prefix 'nonmt_id_idx'
  51
  52 \set link_tbl           :prefix 'link'
  53 \set link_from_idx      :prefix 'link_from_idx'
  54 \set link_to_idx        :prefix 'link_to_idx'
  55
  56 \set session_tbl        :prefix 'session'
  57 \set sess_id_idx        :prefix 'sess_id_idx'
  58 \set sess_date_idx      :prefix 'sess_date_idx'
  59 \set sess_ip_idx        :prefix 'sess_ip_idx'
  60
  61 \set pref_tbl           :prefix 'pref'
  62 \set pref_id_idx        :prefix 'pref_id_idx'
  63 --\set user_tbl                 :prefix 'users'
  64 --\set user_id_idx      :prefix 'users_id_idx'
  65 \set member_tbl         :prefix 'member'
  66 \set member_id_idx      :prefix 'member_id_idx'
  67 \set member_group_idx   :prefix 'member_group_idx'
  68
  69 \set rating_tbl         :prefix 'rating'
  70 \set rating_id_idx      :prefix 'rating_id_idx'
  71
  72 \set accesslog_tbl      :prefix 'accesslog'
  73 \set accesslog_time_idx :prefix 'log_time_idx'
  74 \set accesslog_host_idx :prefix 'log_host_idx'
  75
  76 --================================================================
  77 \echo schema enhancements
  78
  79 ALTER TABLE :page_tbl
  80         ALTER COLUMN id TYPE SERIAL PRIMARY KEY
  81         ALTER COLUMN pagename TYPE VARCHAR(100)
  82         ALTER COLUMN pagename SET NOT NULL
  83         ALTER COLUMN pagename ADD CONSTRAINT pagename_key UNIQUE CHECK (pagename <> '');
  84 ALTER TABLE :version_tbl  ALTER COLUMN id INT4 REFERENCES :page_tbl ON DELETE CASCADE;
  85 ALTER TABLE :nonempty_tbl ALTER COLUMN id INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE;
  86 ALTER TABLE :link_tbl
  87         ALTER COLUMN linkfrom INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE
  88         ALTER COLUMN linkto   INT4 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE
  89         ADD COLUMN relation   INT4 DEFAULT 0;
  90 CREATE INDEX :relation_idx ON :link_tbl (relation);
  91 ALTER TABLE :rating_tbl
  92         ALTER COLUMN raterpage INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE
  93         ALTER COLUMN rateepage INT8 NOT NULL REFERENCES :page_tbl ON DELETE CASCADE;
  94 ALTER TABLE :member_tbl ALTER COLUMN userid CHAR(48) NOT NULL REFERENCES :pref_tbl ON DELETE CASCADE;
  95
  96 --================================================================
  97
  98 \echo add tsearch2 fulltextsearch extension
  99 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
 100 -- At first init it for the database:
 101
 102 -- example of ISpell dictionary
 103 --   UPDATE pg_ts_dict SET dict_initoption='DictFile="/usr/local/share/ispell/russian.dict",
 104 --     AffFile ="/usr/local/share/ispell/russian.aff", StopFile="/usr/local/share/ispell/russian.stop"'
 105 --     WHERE dict_name='ispell_template';
 106 -- example of synonym dict
 107 --   UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
 108
 109 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
 110 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
 111 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
 112 VACUUM FULL ANALYZE;
 113 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
 114 VACUUM FULL ANALYZE;
 115 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
 116        FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
 117
 118 --================================================================
 119
 120 \echo Initializing stored procedures
 121
 122 CREATE OR REPLACE FUNCTION delete_versiondata (id integer, version integer)
 123         RETURNS void AS '
 124 DELETE FROM version WHERE id=$1 AND version=$2;
 125 DELETE FROM recent WHERE id=$1;
 126 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
 127   SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0  THEN version END),
 128                            MAX(CASE WHEN minor_edit<>0 THEN version END)
 129     FROM version WHERE id=$2 GROUP BY id;
 130 DELETE FROM nonempty WHERE id=$1;
 131 INSERT INTO nonempty (id)
 132   SELECT recent.id
 133     FROM recent, version
 134     WHERE recent.id=version.id
 135           AND version=latestversion
 136           AND content<>''''
 137           AND recent.id=$1;
 138 ' LANGUAGE SQL;
 139
 140 CREATE OR REPLACE FUNCTION
 141         set_versiondata (id integer, version integer, mtime integer, minor_edit integer,
 142                          content TEXT, versiondata TEXT)
 143         RETURNS void AS
 144 '
 145 DELETE FROM version WHERE id=$1 AND version=$2;
 146 INSERT INTO version (id,version,mtime,minor_edit,content,versiondata)
 147        VALUES($1, $2, $3, $4, $5, $6);
 148 DELETE FROM recent WHERE id=$1;
 149 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
 150   SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0  THEN version END),
 151                            MAX(CASE WHEN minor_edit<>0 THEN version END)
 152     FROM version WHERE id=$2 GROUP BY id;
 153 DELETE FROM nonempty WHERE id=$1;
 154 INSERT INTO nonempty (id)
 155   SELECT recent.id
 156     FROM recent, version
 157     WHERE recent.id=version.id
 158           AND version=latestversion
 159           AND content<>''''
 160           AND recent.id=$1;
 161 ' LANGUAGE sql;
 162
 163 CREATE OR REPLACE FUNCTION prepare_rename_page (oldid integer, newid integer)
 164         RETURNS void AS
 165 '
 166 DELETE FROM page     WHERE id=$2;
 167 DELETE FROM version  WHERE id=$2;
 168 DELETE FROM recent   WHERE id=$2;
 169 DELETE FROM nonempty WHERE id=$2;
 170 -- We have to fix all referring tables to the old id
 171 UPDATE link SET linkfrom=$1 WHERE linkfrom=$2;
 172 UPDATE link SET linkto=$1   WHERE linkto=$2;
 173 ' LANGUAGE sql;