-- dbConnect IBM DB2 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 `db2 -n -w -t -f ./tables.sql`
--

DROP TABLE TypeTest;
DROP TABLE TransactionTest;


-- The following table lists the types that the API has been tested with
--  IBM DB2 Types Testing Table.
--  LONG types are replaced by LOB types according to the DB2 documentation
--  CLOB types are normally by default 1MB big
CREATE TABLE TypeTest
(
  id                            INTEGER      NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  description                   VARCHAR(100) NOT NULL,
                                                                                                               
  t_SMALLINT                    SMALLINT,
  t_INTEGER                     INTEGER,
  t_BIGINT                      BIGINT,

  t_NUMERIC                     NUMERIC(10,5),
  t_FLOAT                       FLOAT,
  t_REAL                        REAL,
  t_DOUBLE                      DOUBLE,
  t_DECIMAL                     DECIMAL,

  t_CHAR                        CHAR(100),
  t_VARCHAR                     VARCHAR(255),
  t_CLOB                        CLOB,
  t_GRAPHIC                     GRAPHIC(50),
  t_VARGRAPHIC                  VARGRAPHIC(255),

  t_TIMESTAMP                   TIMESTAMP,
  t_DATE                        DATE,
  t_TIME                        TIME,

  t_BLOB                        BLOB(1K),
  t_BINARY                      CHAR FOR BIT DATA,

  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_FLOAT, t_REAL, t_DOUBLE, t_DECIMAL)
VALUES
   ('2: Floating Point Types', 23459.2345, 9999.1234567, 1000.1234444, 100000000.999954325, 55.54);

INSERT INTO TypeTest 
   (description, t_CHAR, t_VARCHAR, t_CLOB, t_GRAPHIC, t_VARGRAPHIC)
VALUES
   ('3: Character (String) Types', 'Fixed CHAR Field', 'This is some varchar text', 'This is data in a CLOB field', 'This is data in a GRAPHIC field', 'This is data in a VARGRAPHIC field');

INSERT INTO TypeTest
   (description, t_TIMESTAMP, t_DATE, t_TIME)
VALUES
   ('4: Date & Time Types', '1999-12-31 23:59:59.0000', '2020-12-31', '18:59:59');

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


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