-- dbConnect Postgresql database example(s) and testing tables
-- Copyright (C) 2003 Johnathan Ingram, jingram@rogueware.org
--
-- This library is free software; you can redistribute it and/or
--   modify it under the terms of the GNU Lesser General Public
--   License as published by the Free Software Foundation; either
--   version 2.1 of the License, or (at your option) any later version.
--
--   This library is distributed in the hope that it will be useful,
--   but WITHOUT ANY WARRANTY; without even the implied warranty of
--   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
--   Lesser General Public License for more details.
--
--   You should have received a copy of the GNU Lesser General Public
--   License along with this library; if not, write to the Free Software
--   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  US
--
--   Note: To create execute `cat tables.sql | psql -d dbconnectdb -U dbconnect`
--         Use the password "letmein"
--
DROP TABLE TypeTest;
DROP TABLE TransactionTest;
DROP FUNCTION Func1Test(INTEGER, INTEGER);
 

 
-- The following table lists the types that the API has been tested with
--  Postgresql Types Testing Table.
CREATE TABLE TypeTest
(
   id                            SERIAL       NOT NULL,
   description                   VARCHAR(100) NOT NULL,
                                                                                                               
   t_SMALLINT                    SMALLINT,
   t_INTEGER                     INTEGER,
   t_BIGINT                      BIGINT,
                                                                                                               
   t_NUMERIC                     NUMERIC(10,5),
   t_REAL                        REAL,
   t_DOUBLE_PRECISION            DOUBLE PRECISION,
                                                                                                               
   t_CHAR                        CHAR(100),
   t_VARCHAR                     VARCHAR(255),
   t_TEXT                        TEXT,
                                                                                                               
   t_TIMESTAMP                   TIMESTAMP,
   t_TIMESTAMPTZ                 TIMESTAMP WITH TIME ZONE, 
   t_INTERVAL                    INTERVAL,
   t_DATE                        DATE,
   t_TIME                        TIME,
                                                                                                               
   t_BOOLEAN                     BOOLEAN,

   t_BINARY                      BYTEA,
  
   PRIMARY KEY(id)
);

INSERT INTO TypeTest 
   (description, t_SMALLINT, t_INTEGER, t_BIGINT)
VALUES
   ('1: Integer Types', -32768, 2147483647, -9223372036854775800);

INSERT INTO TypeTest 
   (description, t_NUMERIC, t_REAL, t_DOUBLE_PRECISION)
VALUES
   ('2: Floating Point Types', 9871.2345, 9999.1234567, 1000.1234567890123456);

INSERT INTO TypeTest 
   (description, t_CHAR, t_VARCHAR, t_TEXT)
VALUES
   ('3: Character (String) Types', 'This is a char', '345 Elm Street', 'Lots of text: dsjlsadlkjsdlkjsdlkasjdlskadjsaljdlskajdlskjdlskadjlskdjlskhsdjhfgjhhsahlsajdlkasjdlskajdlsajd;sajlkfldsfhdshfdfsfsdhfjsdhfksdfksdfksdfdassdaslkdjsakldj THE END OF THE TEXT THINGY');

INSERT INTO TypeTest 
   (description, t_TIMESTAMP, t_TIMESTAMPTZ, t_INTERVAL, t_DATE, t_TIME)
VALUES
   ('4: Date & Time Types', '1999-12-31 23:59:59', '1999-12-31 23:59:59+02:00', '50 year ago', '2020-12-31', '18:59:59');

INSERT INTO TypeTest 
   (description, t_BOOLEAN)
VALUES
   ('5: Boolean Types', TRUE);

INSERT INTO TypeTest 
   (description, t_BINARY)
VALUES
   ('6: Binary Types', 'Test [\\300]ABCD\\000');



-- The following table is used to test transactions.
-- -------------------------------------------------------
CREATE TABLE TransactionTest
(
   id                      INTEGER      NOT NULL,
   description             VARCHAR(100) NOT NULL,
   
   PRIMARY KEY(id)
);



-- The following function returns a value based on two input values
--  PostGresQL Function Testing.
--  Because you cannot give names to the parameters in functions, dbConnect API
--    will assign parameter names as follows: :(
--      First  Parameter: param1   Use bindParam("param1")->
--      Second Parameter: param2   Use bindParam("param2")->
--      .... (and so forth)
--  A result from a function will always be returned in a bind parameter named "result"
CREATE FUNCTION Func1Test(INTEGER, INTEGER)
RETURNS INTEGER
AS
'
   SELECT $1 + ($2  / 2);
'
LANGUAGE SQL;