From 97b14f449a7eec61bf097416095bee383b8835c4 Mon Sep 17 00:00:00 2001 From: rurban Date: Thu, 18 May 2006 06:08:33 +0000 Subject: [PATCH] update psql funcs and notes git-svn-id: svn://svn.code.sf.net/p/phpwiki/code/trunk@5073 96ab9672-09ca-45d6-a79d-3d69d39ca109 --- schemas/psql-1_3_12.sql | 44 +++++++++---------------------------- schemas/psql-destroy.sql | 4 ++-- schemas/psql-initialize.sql | 28 +++++++++++++---------- 3 files changed, 29 insertions(+), 47 deletions(-) diff --git a/schemas/psql-1_3_12.sql b/schemas/psql-1_3_12.sql index 9d90f30a5..1b6657a1f 100644 --- a/schemas/psql-1_3_12.sql +++ b/schemas/psql-1_3_12.sql @@ -1,4 +1,4 @@ --- $Id: psql-1_3_12.sql,v 1.4 2005-11-16 07:33:43 rurban Exp $ +-- $Id: psql-1_3_12.sql,v 1.5 2006-05-18 06:08:33 rurban Exp $ \set QUIET @@ -90,7 +90,7 @@ ALTER TABLE :nonempty_tbl ALTER COLUMN id TYPE INT4, ADD FOREIGN KEY (id) REFERENCES :page_tbl ON DELETE CASCADE; -\echo Creating page views +\echo Creating experimental page views (not yet used) -- nonempty versiondata CREATE VIEW existing_page AS @@ -107,15 +107,15 @@ CREATE VIEW curr_page AS ALTER TABLE :link_tbl ALTER COLUMN linkfrom TYPE INT4, ALTER COLUMN linkto TYPE INT4, - ADD COLUMN relation INT4 DEFAULT 0, - ADD FOREIGN KEY (linkfrom) REFERENCES :page_tbl (id), - ADD FOREIGN KEY (linkto) REFERENCES :page_tbl (id); + ADD COLUMN relation INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE, + ADD FOREIGN KEY (linkfrom) REFERENCES :page_tbl (id) ON DELETE CASCADE, + ADD FOREIGN KEY (linkto) REFERENCES :page_tbl (id) ON DELETE CASCADE; CREATE INDEX :relation_idx ON :link_tbl (relation); ALTER TABLE :rating_tbl ALTER COLUMN raterpage TYPE INT8, ALTER COLUMN rateepage TYPE INT8, - ADD FOREIGN KEY (raterpage) REFERENCES :page_tbl (id), - ADD FOREIGN KEY (rateepage) REFERENCES :page_tbl (id); + ADD FOREIGN KEY (raterpage) REFERENCES :page_tbl (id) ON DELETE CASCADE, + ADD FOREIGN KEY (rateepage) REFERENCES :page_tbl (id) ON DELETE CASCADE; ALTER TABLE :member_tbl ALTER COLUMN userid TYPE CHAR(48), ALTER COLUMN userid SET NOT NULL, @@ -147,33 +147,9 @@ CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl \echo Initializing stored procedures -CREATE OR REPLACE FUNCTION delete_versiondata (id integer, version integer) +CREATE OR REPLACE FUNCTION update_recent (id INT4, version INT4) RETURNS void AS ' -DELETE FROM version WHERE id=$1 AND version=$2; DELETE FROM recent WHERE id=$1; -INSERT INTO recent (id, latestversion, latestmajor, latestminor) - SELECT id, MAX(version), - MAX(CASE WHEN minor_edit=0 THEN version END), - MAX(CASE WHEN minor_edit<>0 THEN version END) - FROM version WHERE id=$2 GROUP BY id; -DELETE FROM nonempty WHERE id=$1; -INSERT INTO nonempty (id) - SELECT recent.id - FROM recent, version - WHERE recent.id=version.id - AND version=latestversion - AND content<>'''' - AND recent.id=$1; -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION - set_versiondata (id integer, version integer, mtime integer, minor_edit smallint, - content text, versiondata text) - RETURNS void AS ' -DELETE FROM version WHERE id=$1 AND version=$2; -INSERT INTO version (id,version,mtime,minor_edit,content,versiondata) - VALUES($1, $2, $3, $4, $5, $6); -DELETE FROM recent WHERE id=$1; INSERT INTO recent (id, latestversion, latestmajor, latestminor) SELECT id, MAX(version), MAX(CASE WHEN minor_edit=0 THEN version END), MAX(CASE WHEN minor_edit<>0 THEN version END) @@ -186,9 +162,9 @@ INSERT INTO nonempty (id) AND version=latestversion AND content<>'''' AND recent.id=$1; -' LANGUAGE sql; +' LANGUAGE SQL; -CREATE OR REPLACE FUNCTION prepare_rename_page (oldid integer, newid integer) +CREATE OR REPLACE FUNCTION prepare_rename_page (oldid INT4, newid INT4) RETURNS void AS ' DELETE FROM page WHERE id=$2; DELETE FROM version WHERE id=$2; diff --git a/schemas/psql-destroy.sql b/schemas/psql-destroy.sql index 4c2f9cd2f..e732c4f72 100644 --- a/schemas/psql-destroy.sql +++ b/schemas/psql-destroy.sql @@ -1,4 +1,4 @@ --- $Id: psql-destroy.sql,v 1.9 2005-11-16 07:33:43 rurban Exp $ +-- $Id: psql-destroy.sql,v 1.10 2006-05-18 06:08:33 rurban Exp $ \set QUIET @@ -6,7 +6,7 @@ -- Prefix for table names. -- -- You should set this to the same value you specify for --- $DBParams['prefix'] in index.php. +-- DATABASE_PREFIX in config/config.ini \set prefix '' diff --git a/schemas/psql-initialize.sql b/schemas/psql-initialize.sql index 9e187312d..8e8094941 100644 --- a/schemas/psql-initialize.sql +++ b/schemas/psql-initialize.sql @@ -1,13 +1,7 @@ --- $Id: psql-initialize.sql,v 1.10 2005-11-16 07:33:43 rurban Exp $ +-- $Id: psql-initialize.sql,v 1.11 2006-05-18 06:08:33 rurban Exp $ \set QUIET -\echo At first init the database with: -\echo $ createdb phpwiki -\echo $ createuser -S -R -d phpwiki # (see httpd_user below) -\echo $ psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql -\echo $ psql phpwiki < psql-initialize.sql - --================================================================ -- Prefix for table names. -- @@ -23,10 +17,15 @@ -- user who will be accessing the tables. -- See DATABASE_DSN in config.ini -- +-- NOTE: To be able to vacuum the tables from ordinary page requests +-- :httpd_user must be the table owner. +-- To run autovacuum and disable page requests vacuums edit the +-- pqsql backend optimize method. +-- -- Commonly, connections from php are made under -- the user name of 'nobody', 'apache' or 'www'. -\set httpd_user 'phpwiki' +\set httpd_user 'wikiuser' --================================================================ -- @@ -36,6 +35,13 @@ \set qprefix '\'' :prefix '\'' \set qhttp_user '\'' :httpd_user '\'' + +\echo At first init the database with: +\echo '$ createdb phpwiki' +\echo '$ createuser -S -R -d ' :qhttpd_user +\echo '$ psql -U ' :qhttpd_user ' phpwiki < /usr/share/postgresql/contrib/tsearch2.sql' +\echo '$ psql -U ' :qhttpd_user ' phpwiki < psql-initialize.sql' + \echo Initializing PhpWiki tables with: \echo ' prefix = ' :qprefix \echo ' httpd_user = ' :qhttp_user @@ -172,9 +178,9 @@ CREATE VIEW curr_page AS \echo Creating :link_tbl CREATE TABLE :link_tbl ( - linkfrom INT4 NOT NULL REFERENCES :page_tbl, - linkto INT4 NOT NULL REFERENCES :page_tbl, - relation INT4 DEFAULT 0 + linkfrom INT4 NOT NULL REFERENCES :page_tbl, + linkto INT4 NOT NULL REFERENCES :page_tbl, + relation INT4 REFERENCES :page_tbl (id) ON DELETE CASCADE ); CREATE INDEX :link_from_idx ON :link_tbl (linkfrom); CREATE INDEX :link_to_idx ON :link_tbl (linkto); -- 2.45.0