schemas/psql-1_3_12.sql

   1 -- $Id: psql-1_3_12.sql,v 1.5 2006-05-18 06:08:33 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 \set relation_idx       :prefix 'relation_idx'
  56
  57 \set session_tbl        :prefix 'session'
  58 \set sess_id_idx        :prefix 'sess_id_idx'
  59 \set sess_date_idx      :prefix 'sess_date_idx'
  60 \set sess_ip_idx        :prefix 'sess_ip_idx'
  61
  62 \set pref_tbl           :prefix 'pref'
  63 \set pref_id_idx        :prefix 'pref_id_idx'
  64 --\set user_tbl                 :prefix 'users'
  65 --\set user_id_idx      :prefix 'users_id_idx'
  66 \set member_tbl         :prefix 'member'
  67 \set member_id_idx      :prefix 'member_id_idx'
  68 \set member_group_idx   :prefix 'member_group_idx'
  69
  70 \set rating_tbl         :prefix 'rating'
  71 \set rating_id_idx      :prefix 'rating_id_idx'
  72
  73 \set accesslog_tbl      :prefix 'accesslog'
  74 \set accesslog_time_idx :prefix 'log_time_idx'
  75 \set accesslog_host_idx :prefix 'log_host_idx'
  76
  77 --================================================================
  78 \echo schema enhancements
  79
  80 ALTER TABLE :page_tbl
  81         ALTER COLUMN id TYPE SERIAL /* PRIMARY KEY */,
  82         ALTER COLUMN pagename TYPE VARCHAR(100),
  83         ALTER COLUMN pagename SET NOT NULL,
  84         ADD UNIQUE(pagename),
  85         ADD CHECK (pagename <> '');
  86 ALTER TABLE :version_tbl
  87         ALTER COLUMN id TYPE INT4,
  88         ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
  89 ALTER TABLE :nonempty_tbl
  90         ALTER COLUMN id TYPE INT4,
  91         ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
  92
  93 \echo Creating experimental page views (not yet used)
  94
  95 -- nonempty versiondata
  96 CREATE VIEW existing_page AS
  97   SELECT * FROM :page_tbl P INNER JOIN :nonempty_tbl N USING (id);
  98
  99 -- latest page version
 100 CREATE VIEW curr_page AS
 101   SELECT P.id,P.pagename,P.hits,P.pagedata,P.cached_html,
 102          V.version,V.mtime,V.minor_edit,V.content,V.versiondata
 103   FROM :page_tbl P
 104     JOIN :version_tbl V USING (id)
 105     JOIN :recent_tbl  R ON (V.id=R.id AND V.version=R.latestversion);
 106
 107 ALTER TABLE :link_tbl
 108         ALTER COLUMN linkfrom TYPE INT4,
 109         ALTER COLUMN linkto   TYPE INT4,
 110         ADD COLUMN   relation INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE,
 111         ADD FOREIGN KEY (linkfrom) REFERENCES :page_tbl (id) ON DELETE CASCADE,
 112         ADD FOREIGN KEY (linkto)   REFERENCES :page_tbl (id) ON DELETE CASCADE;
 113 CREATE INDEX :relation_idx ON :link_tbl (relation);
 114 ALTER TABLE :rating_tbl
 115         ALTER COLUMN raterpage TYPE INT8,
 116         ALTER COLUMN rateepage TYPE INT8,
 117         ADD FOREIGN KEY (raterpage) REFERENCES :page_tbl (id) ON DELETE CASCADE,
 118         ADD FOREIGN KEY (rateepage) REFERENCES :page_tbl (id) ON DELETE CASCADE;
 119 ALTER TABLE :member_tbl
 120         ALTER COLUMN userid TYPE CHAR(48),
 121         ALTER COLUMN userid SET NOT NULL,
 122         ADD FOREIGN KEY (userid) REFERENCES :pref_tbl;
 123
 124 --================================================================
 125
 126 \echo add tsearch2 fulltextsearch extension
 127 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
 128 -- At first init it for the database:
 129
 130 -- example of ISpell dictionary
 131 --   UPDATE pg_ts_dict SET dict_initoption='DictFile="/usr/local/share/ispell/russian.dict",
 132 --     AffFile ="/usr/local/share/ispell/russian.aff", StopFile="/usr/local/share/ispell/russian.stop"'
 133 --     WHERE dict_name='ispell_template';
 134 -- example of synonym dict
 135 --   UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
 136
 137 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
 138 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
 139 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
 140 VACUUM FULL ANALYZE;
 141 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
 142 VACUUM FULL ANALYZE;
 143 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
 144        FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
 145
 146 --================================================================
 147
 148 \echo Initializing stored procedures
 149
 150 CREATE OR REPLACE FUNCTION update_recent (id INT4, version INT4)
 151         RETURNS void AS '
 152 DELETE FROM recent  WHERE id=$1;
 153 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
 154   SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0  THEN version END),
 155                            MAX(CASE WHEN minor_edit<>0 THEN version END)
 156     FROM version WHERE id=$2 GROUP BY id;
 157 DELETE FROM nonempty WHERE id=$1;
 158 INSERT INTO nonempty (id)
 159   SELECT recent.id
 160     FROM recent, version
 161     WHERE recent.id=version.id
 162           AND version=latestversion
 163           AND content<>''''
 164           AND recent.id=$1;
 165 ' LANGUAGE SQL;
 166
 167 CREATE OR REPLACE FUNCTION prepare_rename_page (oldid INT4, newid INT4)
 168         RETURNS void AS '
 169 DELETE FROM page     WHERE id=$2;
 170 DELETE FROM version  WHERE id=$2;
 171 DELETE FROM recent   WHERE id=$2;
 172 DELETE FROM nonempty WHERE id=$2;
 173 -- We have to fix all referring tables to the old id
 174 UPDATE link SET linkfrom=$1 WHERE linkfrom=$2;
 175 UPDATE link SET linkto=$1   WHERE linkto=$2;
 176 ' LANGUAGE sql;