/*============================================================================

    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 <http://asmith.id.au>
    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,'&nbsp;'),
        CASE WHEN pContact ISNULL THEN ' DISABLED' ELSE '' END
    INTO
        vContact,
        vMessage,
        vDisable
    FROM
        tRequest
    WHERE
        fToken = aToken;

    -- compose the page to be returned

    SELECT

'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
 <HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">
  <TITLE>mod_libpq example application</TITLE>
 </HEAD>
 <BODY><FORM METHOD="POST">
  <H1>mod_libpq example</H1>
  <P>Warning: all activity on this site is logged.</P>
  <INPUT TYPE="hidden" NAME="iCookie" VALUE="'||aToken||'">
  <INPUT TYPE="hidden" NAME="iVerify" VALUE="'||mTimeStamp||'">
  <TABLE>
   <TR>
    <TD>Record:</TD>
    <TD COLSPAN="4">
     <INPUT TYPE="text" NAME="" VALUE="'||kContact||'" DISABLED>
    </TD>
   </TR>
   <TR>
    <TD>Name:</TD>
    <TD COLSPAN="4">
     <INPUT TYPE="text" NAME="iName" VALUE="'||fName||'">
    </TD>
   </TR>
   <TR>
    <TD>Address:</TD>
    <TD COLSPAN="4">
     <INPUT TYPE="text" NAME="iAddress" VALUE="'||fAddress||'">
    </TD>
   </TR>
   <TR>
    <TD>Phone:</TD>
    <TD COLSPAN="4">
     <INPUT TYPE="text" NAME="iPhone" VALUE="'||fPhone||'">
    </TD>
   </TR>
   <TR>
    <TD>&nbsp;</TD>
    <TD COLSPAN="4"><B>'||vMessage||'</B></TD>
   </TR>
   <TR>
    <TD>&nbsp;</TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="Search"></TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="Insert"></TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="Update"'||vDisable||'></TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="Delete"'||vDisable||'></TD>
   </TR>
   <TR>
    <TD>&nbsp;</TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="First"'||vDisable||'></TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="Previous"'||vDisable||'></TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="Next"'||vDisable||'></TD>
    <TD><INPUT TYPE="submit" NAME="iButton" VALUE="Last"'||vDisable||'></TD>
   </TR>
  </TABLE>
  </FORM></BODY>
</HTML>'

    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,'&','&amp;');
    vText := REPLACE(vText,'<','&lt;');
    vText := REPLACE(vText,'"','&#34;');
    vText := REPLACE(vText,'[b]','<b>');
    vText := REPLACE(vText,'[/b]','</b>');
    vText := REPLACE(vText,'[nbsp]','&nbsp;');
    RETURN vText;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


------------------------------------------------------------------------------


/*==========================================================================*/
