schemas/psql-1_3_12.sql

   1 \set QUIET
   2
   3 -- Init the database with:
   4 -- $ /usr/sbin/createdb phpwiki
   5 -- $ /usr/sbin/createuser -S -R -d phpwiki # (see httpd_user below)
   6 -- $ /usr/bin/psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
   7 -- $ /usr/bin/psql phpwiki < psql-initialize.sql
   8
   9 -- ================================================================
  10 -- Prefix for table names.
  11 --
  12 -- You should set this to the same value you specified for
  13 -- DATABASE_PREFIX in config.ini
  14
  15 \set prefix     ''
  16
  17 -- ================================================================
  18 -- Which postgres user gets access to the tables?
  19 --
  20 -- You should set this to the name of the postgres
  21 -- user who will be accessing the tables.
  22 -- See DATABASE_DSN in config.ini
  23 --
  24 -- Commonly, connections from php are made under
  25 -- the user name of 'nobody', 'apache' or 'www'.
  26
  27 \set httpd_user 'phpwiki'
  28
  29 -- ================================================================
  30 --
  31 -- Don't modify below this point unless you know what you are doing.
  32 --
  33 -- ================================================================
  34
  35 \set page_tbl           :prefix 'page'
  36 \set page_id_seq        :prefix 'page_id_seq'
  37 \set page_id_idx        :prefix 'page_id_idx'
  38 \set page_name_idx      :prefix 'page_name_idx'
  39
  40 \set version_tbl        :prefix 'version'
  41 \set vers_id_idx        :prefix 'vers_id_idx'
  42 \set vers_mtime_idx     :prefix 'vers_mtime_idx'
  43
  44 \set recent_tbl         :prefix 'recent'
  45 \set recent_id_idx      :prefix 'recent_id_idx'
  46
  47 \set nonempty_tbl       :prefix 'nonempty'
  48 \set nonmt_id_idx       :prefix 'nonmt_id_idx'
  49
  50 \set link_tbl           :prefix 'link'
  51 \set link_from_idx      :prefix 'link_from_idx'
  52 \set link_to_idx        :prefix 'link_to_idx'
  53 \set relation_idx       :prefix 'relation_idx'
  54
  55 \set session_tbl        :prefix 'session'
  56 \set sess_id_idx        :prefix 'sess_id_idx'
  57 \set sess_date_idx      :prefix 'sess_date_idx'
  58 \set sess_ip_idx        :prefix 'sess_ip_idx'
  59
  60 \set pref_tbl           :prefix 'pref'
  61 \set pref_id_idx        :prefix 'pref_id_idx'
  62 -- \set user_tbl                :prefix 'users'
  63 -- \set user_id_idx     :prefix 'users_id_idx'
  64 \set member_tbl         :prefix 'member'
  65 \set member_id_idx      :prefix 'member_id_idx'
  66 \set member_group_idx   :prefix 'member_group_idx'
  67
  68 \set rating_tbl         :prefix 'rating'
  69 \set rating_id_idx      :prefix 'rating_id_idx'
  70
  71 \set accesslog_tbl      :prefix 'accesslog'
  72 \set accesslog_time_idx :prefix 'log_time_idx'
  73 \set accesslog_host_idx :prefix 'log_host_idx'
  74
  75 -- ================================================================
  76 \echo schema enhancements
  77
  78 ALTER TABLE :page_tbl
  79         ALTER COLUMN id TYPE SERIAL /* PRIMARY KEY */,
  80         ALTER COLUMN pagename TYPE VARCHAR(100),
  81         ALTER COLUMN pagename SET NOT NULL,
  82         ADD UNIQUE(pagename),
  83         ADD CHECK (pagename <> '');
  84 ALTER TABLE :version_tbl
  85         ALTER COLUMN id TYPE INT4,
  86         ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
  87 ALTER TABLE :nonempty_tbl
  88         ALTER COLUMN id TYPE INT4,
  89         ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE;
  90
  91 \echo Creating experimental page views (not yet used)
  92
  93 -- nonempty versiondata
  94 CREATE VIEW existing_page AS
  95   SELECT * FROM :page_tbl P INNER JOIN :nonempty_tbl N USING (id);
  96
  97 -- latest page version
  98 CREATE VIEW curr_page AS
  99   SELECT P.id,P.pagename,P.hits,P.pagedata,P.cached_html,
 100          V.version,V.mtime,V.minor_edit,V.content,V.versiondata
 101   FROM :page_tbl P
 102     JOIN :version_tbl V USING (id)
 103     JOIN :recent_tbl  R ON (V.id=R.id AND V.version=R.latestversion);
 104
 105 ALTER TABLE :link_tbl
 106         ALTER COLUMN linkfrom TYPE INT4,
 107         ALTER COLUMN linkto   TYPE INT4,
 108         ADD COLUMN   relation INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE,
 109         ADD FOREIGN KEY (linkfrom) REFERENCES :page_tbl (id) ON DELETE CASCADE,
 110         ADD FOREIGN KEY (linkto)   REFERENCES :page_tbl (id) ON DELETE CASCADE;
 111 CREATE INDEX :relation_idx ON :link_tbl (relation);
 112 ALTER TABLE :rating_tbl
 113         ALTER COLUMN raterpage TYPE INT8,
 114         ALTER COLUMN rateepage TYPE INT8,
 115         ADD FOREIGN KEY (raterpage) REFERENCES :page_tbl (id) ON DELETE CASCADE,
 116         ADD FOREIGN KEY (rateepage) REFERENCES :page_tbl (id) ON DELETE CASCADE;
 117 ALTER TABLE :member_tbl
 118         ALTER COLUMN userid TYPE CHAR(48),
 119         ALTER COLUMN userid SET NOT NULL,
 120         ADD FOREIGN KEY (userid) REFERENCES :pref_tbl;
 121
 122 -- ================================================================
 123
 124 \echo add tsearch2 fulltextsearch extension
 125 -- Use the tsearch2 fulltextsearch extension: (recommended) 7.4, 8.0, 8.1
 126 -- At first init it for the database:
 127
 128 -- example of ISpell dictionary
 129 --   UPDATE pg_ts_dict SET dict_initoption='DictFile="/usr/local/share/ispell/russian.dict",
 130 --     AffFile ="/usr/local/share/ispell/russian.aff", StopFile="/usr/local/share/ispell/russian.stop"'
 131 --     WHERE dict_name='ispell_template';
 132 -- example of synonym dict
 133 --   UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
 134
 135 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
 136 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
 137 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
 138 VACUUM FULL ANALYZE;
 139 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
 140 VACUUM FULL ANALYZE;
 141 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
 142        FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);
 143
 144 -- ================================================================
 145
 146 \echo Initializing stored procedures
 147
 148 CREATE OR REPLACE FUNCTION update_recent (id INT4, version INT4)
 149         RETURNS void AS '
 150 DELETE FROM recent  WHERE id=$1;
 151 INSERT INTO recent (id, latestversion, latestmajor, latestminor)
 152   SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0  THEN version END),
 153                            MAX(CASE WHEN minor_edit<>0 THEN version END)
 154     FROM version WHERE id=$2 GROUP BY id;
 155 DELETE FROM nonempty WHERE id=$1;
 156 INSERT INTO nonempty (id)
 157   SELECT recent.id
 158     FROM recent, version
 159     WHERE recent.id=version.id
 160           AND version=latestversion
 161           AND content<>''''
 162           AND recent.id=$1;
 163 ' LANGUAGE SQL;
 164
 165 CREATE OR REPLACE FUNCTION prepare_rename_page (oldid INT4, newid INT4)
 166         RETURNS void AS '
 167 DELETE FROM page     WHERE id=$2;
 168 DELETE FROM version  WHERE id=$2;
 169 DELETE FROM recent   WHERE id=$2;
 170 DELETE FROM nonempty WHERE id=$2;
 171 -- We have to fix all referring tables to the old id
 172 UPDATE link SET linkfrom=$1 WHERE linkfrom=$2;
 173 UPDATE link SET linkto=$1   WHERE linkto=$2;
 174 ' LANGUAGE sql;