/*============================================================================ project: mod_libpq file: example-modlibpq.sql version: 0.0a3 author: Andrew Smith date: 2006-06-03 language: PostgreSQL 8.1 NOTES Create the tables and functions for an example mod_libpq application. This simple web application has a form which contains the name, address, and phone number of each contact record. Records can be searched, inserted, updated and deleted using buttons on the form. Buttons for basic navigation are also included on the form. The form uses the hashed timestamp of a record in a hidden field to guard against two users trying to update or delete the same record at the same time. It also tracks records and requests by embedding the cookie in another hidden field of the form, and so will work correctly even if cookies are disabled in the user agent. Request details and outcomes are recorded in the tRequest table during the header processing phase of a request. The user data for this example is stored in tContact which is used to build an html page during the content creation phase. Copyright (c) 2005,2006, Andrew Smith All rights reserved. ------------------------------------------------------------------------------ Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. * Neither the name of Andrew Smith nor the names of his contributors may be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. ============================================================================*/ ------------------------------------------------------------------------------ \set ON_ERROR_STOP yes CREATE DATABASE example; \connect example CREATE SCHEMA cgi; GRANT USAGE ON SCHEMA cgi TO "www-data"; SET SEARCH_PATH = cgi; ------------------------------------------------------------------------------ -- TABLE DEFINITIONS --------------------------------------------------------- -- this table stores the parameters of all received http requests CREATE TABLE tRequest ( kRequest SERIAL PRIMARY KEY, mTimeStamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, fToken TEXT DEFAULT MD5(TEXT(CURRENT_TIMESTAMP)) NOT NULL UNIQUE, fAddress INET NOT NULL, fCookie TEXT, fQuery TEXT, fInput TEXT, fBrowser TEXT, fReferer TEXT, pContact INTEGER, fMessage TEXT ); GRANT SELECT,INSERT ON tRequest TO "www-data"; GRANT SELECT,UPDATE ON tRequest_kRequest_SEQ TO "www-data"; ------------------------------------------------------------------------------ -- this table stores the user data CREATE TABLE tContact ( kContact SERIAL PRIMARY KEY, mTimeStamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, fName TEXT, fAddress TEXT, fPhone TEXT ); GRANT SELECT,INSERT,UPDATE,DELETE ON tContact TO "www-data"; GRANT SELECT,UPDATE ON tContact_kContact_SEQ TO "www-data"; ------------------------------------------------------------------------------ -- FUNCTION DEFINITIONS ------------------------------------------------------ -- perform the requested action -- return a token to track the request CREATE or replace FUNCTION Headers ( aSystem TEXT, -- request string aAddress TEXT, -- client IP address aCookie TEXT, -- client cookie data aQuery TEXT, -- request arguments aInput TEXT, -- submitted user data aBrowser TEXT, -- browser identification string aReferer TEXT -- referring URL ) RETURNS TEXT AS $$ DECLARE vAddress TEXT; -- contact address field vButton TEXT; -- form button that was pressed vContact INTEGER; -- key of contact record vCookie TEXT; -- cookie embedded in the form vLimit INTEGER; -- first or last record vMessage TEXT; -- status message to display vName TEXT; -- contact name field vPhone TEXT; -- contact phone field vRequest INTEGER; -- key of request record vRowCount INTEGER; -- diagnostic row count vToken TEXT; -- token to be returned vVerify TEXT; -- hashed timestamp of the record BEGIN SET SEARCH_PATH = cgi; vRequest := NEXTVAL(PG_GET_SERIAL_SEQUENCE('trequest','krequest')); -- opera and safari browsers need a variable name vToken := 'token='||MD5(TEXT(CURRENT_TIMESTAMP)); -- parse the input string DECLARE vInputs TEXT[]; vField TEXT; vValue TEXT; BEGIN vInputs := STRING_TO_ARRAY(aInput,'&'); IF vInputs NOTNULL THEN FOR i IN 1 .. ARRAY_UPPER(vInputs,1) LOOP vField := dHttpText(SPLIT_PART(vInputs[i],'=',1)); vValue := dHttpText(SPLIT_PART(vInputs[i],'=',2)); IF vField = 'iButton' THEN vButton := vValue; ELSIF vField = 'iVerify' THEN vVerify := vValue; ELSIF vField = 'iCookie' THEN vCookie := vValue; ELSIF vField = 'iName' THEN vName := vValue; ELSIF vField = 'iAddress' THEN vAddress := vValue; ELSIF vField = 'iPhone' THEN vPhone := vValue; END IF; END LOOP; END IF; END; -- retrieve the current record key using the 'cookie' embedded in the form SELECT pContact INTO vContact FROM tRequest WHERE fToken = vCookie; ---- perform search ------------------------------------------------------ IF vButton = 'Search' THEN SELECT kContact INTO vContact FROM tContact WHERE POSITION(LOWER(vName) IN LOWER(fName)) > 0 AND POSITION(LOWER(vAddress) IN LOWER(fAddress)) > 0 AND POSITION(LOWER(vPhone) IN LOWER(fPhone)) > 0; GET DIAGNOSTICS vRowCount = ROW_COUNT; vMessage := CASE WHEN vRowCount = 0 THEN 'No records found.' ELSE 'Search completed.' END; ---- perform insert record ----------------------------------------------- ELSIF vButton = 'Insert' THEN INSERT INTO tContact(fName,fAddress,fPhone) VALUES(vName,vAddress,vPhone); GET DIAGNOSTICS vRowCount = ROW_COUNT; vContact := CURRVAL('tContact_kContact_SEQ'); vMessage := CASE WHEN vRowCount = 1 THEN 'Record created successfully.' ELSE 'Insert failed. Please try again.' END; ---- perform update record ----------------------------------------------- ELSIF vButton = 'Update' THEN UPDATE tContact SET mTimeStamp = CURRENT_TIMESTAMP, fName = vName, fAddress = vAddress, fPhone = vPhone WHERE kContact = vContact AND MD5(TEXT(mTimeStamp)) = vVerify; GET DIAGNOSTICS vRowCount = ROW_COUNT; vMessage := CASE WHEN vRowCount = 1 THEN 'Record updated successfully.' ELSE 'Record already updated. Please try again.' END; ---- perform delete record ----------------------------------------------- ELSIF vButton = 'Delete' THEN DELETE FROM tContact WHERE kContact = vContact AND MD5(TEXT(mTimeStamp)) = vVerify; GET DIAGNOSTICS vRowCount = ROW_COUNT; IF vRowCount = 1 THEN SELECT MIN(kContact) FROM tContact WHERE kContact > vContact UNION SELECT MAX(kContact) FROM tContact WHERE kContact < vContact INTO vContact; vMessage := 'Record deleted successfully.'; ELSE SELECT MIN(kContact) FROM tContact WHERE kContact >= vContact UNION SELECT MAX(kContact) FROM tContact WHERE kContact < vContact INTO vContact; vMessage := 'Record already deleted or updated. Please try again.'; END IF; ---- go to first record -------------------------------------------------- ELSIF vButton = 'First' THEN SELECT MIN(kContact) FROM tContact INTO vContact; vMessage := 'At first record.'; ---- go to previous record ----------------------------------------------- ELSIF vButton = 'Previous' THEN SELECT MAX(kContact) INTO vContact FROM tContact WHERE kContact < vContact; SELECT MIN(kContact) INTO vLimit FROM tContact; vContact := COALESCE(vContact,vLimit); vMessage := CASE WHEN vContact = vLimit THEN 'At first record.' END; ---- go to next record --------------------------------------------------- ELSIF vButton = 'Next' THEN SELECT MIN(kContact) INTO vContact FROM tContact WHERE kContact > vContact; SELECT MAX(kContact) INTO vLimit FROM tContact; vContact := COALESCE(vContact,vLimit); vMessage := CASE WHEN vContact = vLimit THEN 'At last record.' END; ---- go to last record --------------------------------------------------- ELSIF vButton = 'Last' THEN SELECT MAX(kContact) FROM tContact INTO vContact; vMessage := 'At last record.'; ---- default action goes to first record --------------------------------- ELSE SELECT MIN(kContact) FROM tContact INTO vContact; vMessage := 'At first record.'; -------------------------------------------------------------------------- END IF; -- record the outcome of the request INSERT INTO tRequest ( kRequest, fToken, fAddress, fCookie, fQuery, fInput, fBrowser, fReferer, pContact, fMessage ) VALUES ( vRequest, vToken, CAST(aAddress AS INET), aCookie, aQuery, aInput, aBrowser, aReferer, vContact, vMessage ); -- return the tracking token (cookie) RETURN vToken; END; $$ LANGUAGE plpgsql VOLATILE; ------------------------------------------------------------------------------ -- obtain the document content for the request identified by a token CREATE or replace FUNCTION Content ( aToken TEXT ) RETURNS TEXT AS $$ DECLARE vContact INTEGER; vMessage TEXT; vDisable TEXT; vPage TEXT; BEGIN SET SEARCH_PATH = cgi; -- retrieve the results of processing the request SELECT pContact, COALESCE(fMessage,CASE WHEN pContact ISNULL THEN 'no data' END,' '), CASE WHEN pContact ISNULL THEN ' DISABLED' ELSE '' END INTO vContact, vMessage, vDisable FROM tRequest WHERE fToken = aToken; -- compose the page to be returned SELECT ' mod_libpq example application

mod_libpq example

Warning: all activity on this site is logged.

Record:
Name:
Address:
Phone:
  '||vMessage||'
 
 
' FROM ( SELECT MD5(TEXT(mTimeStamp)), TEXT(kContact), dSafeText(fName), dSafeText(fAddress), dSafeText(fPhone) FROM tContact WHERE kContact = vContact UNION SELECT '','','','','' ORDER BY 1 DESC LIMIT 1 ) AS yContact ( mTimeStamp, kContact, fName, fAddress, fPhone ) INTO vPage; RETURN vPage; END; $$ LANGUAGE plpgsql VOLATILE; ------------------------------------------------------------------------------ -- dHttpText(HttpEncodedText) -- convert '+' to space and '%hh' from hex to character CREATE or replace FUNCTION dHttpText(TEXT) RETURNS TEXT AS $$ DECLARE aText ALIAS FOR $1; vBeforeText TEXT; vAfterText TEXT; vPosition INTEGER; vEncoded TEXT; BEGIN vBeforeText := ''; vAfterText := REPLACE(aText,'+',' '); vPosition := POSITION('%' IN vAfterText); WHILE vPosition > 0 LOOP vBeforeText := vBeforeText || SUBSTRING(vAfterText FOR vPosition-1); vEncoded := UPPER(SUBSTRING(vAfterText FROM vPosition+1 FOR 2)); IF TRIM(LEADING '0123456789ABCDEF' FROM vEncoded) = '' THEN vBeforeText := vBeforeText || ENCODE(DECODE(vEncoded,'hex'),'escape'); vAfterText := SUBSTRING(vAfterText FROM vPosition+3); ELSE vBeforeText := vBeforeText || '%'; vAfterText := SUBSTRING(vAfterText FROM vPosition+1); END IF; vPosition := POSITION('%' IN vAfterText); END LOOP; vBeforeText := vBeforeText || vAfterText; RETURN vBeforeText; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; ------------------------------------------------------------------------------ -- dSafeText(Text) -- transcode special characters to make text safe for inclusion in html CREATE FUNCTION dSafeText ( aText TEXT ) RETURNS TEXT AS $$ DECLARE vText TEXT; BEGIN vText := COALESCE(aText,''); vText := REPLACE(vText,'&','&'); vText := REPLACE(vText,'<','<'); vText := REPLACE(vText,'"','"'); vText := REPLACE(vText,'[b]',''); vText := REPLACE(vText,'[/b]',''); vText := REPLACE(vText,'[nbsp]',' '); RETURN vText; END; $$ LANGUAGE plpgsql IMMUTABLE; ------------------------------------------------------------------------------ /*==========================================================================*/