HSQLDB support database

From GreenVulcano Wiki
Revision as of 14:20, 24 February 2012 by Anonymous (talk)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Into GreenVulcano® ESB it is also included the package HSQLDB.

Obviously, you might choose another DB manager. For the GreenVulcano® ESB preconfigured Services we preferred HSQLDB because of its "lightness".

To open a Client HSQLDB execute the following script:

Linux.png In case of *nix operating systems, from $GV_HOME/hsqldb/lib, the script is java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing.
Windows.png In case of Windows operating system, from $GV_HOME/hsqldb/bin, the script is runDBManager.cmd .

The connection parameters are:

Type: HSQL Database Engine Server
URL: jdbc:hsqldb:hsql://localhost:9001/gvesb
Setting Name: GVESB

Scripts to create the Test schema for the various databases, are present in the directory $GV_HOME/TEST/config.

For HSQLDB we created the following schema:

CREATE SCHEMA GV_TEST AUTHORIZATION DBA;
CREATE USER "gv_test" PASSWORD 'gv_test' ADMIN;
ALTER  USER "gv_test" SET INITIAL SCHEMA GV_TEST;

CREATE TABLE "GV_TEST"."CITY" (
    "ID"    NUMBER(10,0) NOT NULL PRIMARY KEY,
    "NAME"  VARCHAR2(50) NOT NULL
);
CREATE TABLE "GV_TEST"."CREDIT_CARD" (
    "ID"        NUMBER(10,0) NOT NULL PRIMARY KEY,
    "ID_OWNER"  NUMBER(10,0) NOT NULL,
    "CNUMBER"   VARCHAR2(20) NOT NULL,
    "CREDIT"    NUMBER(15,5) NOT NULL,
    "ENABLED"   VARCHAR2(1) NOT NULL
);
CREATE TABLE "GV_TEST"."EXTERNAL_CREDIT_CARD" (
    "ID"        NUMBER(10,0) NOT NULL PRIMARY KEY,
    "CNUMBER"   VARCHAR2(20) NOT NULL,
    "CREDIT"    NUMBER(15,5) NOT NULL,
    "ENABLED"   VARCHAR2(1) NOT NULL
);
CREATE TABLE "GV_TEST"."PERSON" (
    "ID"        NUMBER(10,0) NOT NULL PRIMARY KEY,
    "NAME"      VARCHAR2(50) NOT NULL,
    "BIRTHDATE" DATE NOT NULL,
    "ID_CITY"   NUMBER(10,0) NOT NULL
);
CREATE TABLE "GV_TEST"."XML_FILES" (
    "FILE_ID"       NUMBER(10,0) NOT NULL PRIMARY KEY,
    "CREATION_DATE" DATE DEFAULT sysdate NULL,
    "FILE_NAME"     VARCHAR2(200) NOT NULL,
    "PAYLOAD"       CLOB NOT NULL
);

-- Sequences
CREATE SEQUENCE "GV_TEST"."SEQ_FILES"
    INCREMENT BY 1
    START WITH 14;
CREATE SEQUENCE "GV_TEST"."SEQ_PERSON_ID"
    INCREMENT BY 1
    START WITH 91;

-- Procedures
--CREATE PROCEDURE "GV_TEST"."LOAD_XML_FILE"(v_file_name in varchar2, v_file in CLOB, v_file_id out number) is
--        fileid number;
--    begin
--        select SEQ_FILES.nextval into fileid from dual;
--
--        insert into xml_files(file_id, creation_date, file_name, payload)
--        values(fileid, sysdate, v_file_name, v_file);
--
--        v_file_id := fileid;
--
--    exception
--        when others then
--            raise_application_error(-20000,'AN ERROR OCCURRED. CODE: '||SQLCODE||' MESSAGE: '||SQLERRM);
--    end;

-- Data
INSERT INTO "GV_TEST"."CITY"("ID", "NAME") VALUES(1, 'ROMA');
INSERT INTO "GV_TEST"."CITY"("ID", "NAME") VALUES(2, 'NAPOLI');
INSERT INTO "GV_TEST"."CITY"("ID", "NAME") VALUES(3, 'GENOVA');
INSERT INTO "GV_TEST"."CITY"("ID", "NAME") VALUES(4, 'TORINO');
INSERT INTO "GV_TEST"."CITY"("ID", "NAME") VALUES(5, 'MILANO');
INSERT INTO "GV_TEST"."CITY"("ID", "NAME") VALUES(6, 'TOPOLINIA');

INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(36, 'PIPPO', TO_DATE('2000-10-10','YYYY-MM-DD'), 6);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(37, 'TOPOLINO', TO_DATE('2000-03-17','YYYY-MM-DD'), 6);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(24, 'ANCO MARZIO', TO_DATE('1979-10-02','YYYY-MM-DD'), 1);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(33, 'CIRO ROSSI', TO_DATE('1984-07-10','YYYY-MM-DD'), 2);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(6, 'NUMA POMPILIO', TO_DATE('1970-10-02','YYYY-MM-DD'), 1);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(8, 'TULLIO OSTILIO', TO_DATE('1975-05-05','YYYY-MM-DD'), 1);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(20, 'CIRO VERDI', TO_DATE('1974-09-15','YYYY-MM-DD'), 2);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(3, 'MARCO VERDI', TO_DATE('1975-02-20','YYYY-MM-DD'), 4);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(34, 'ANTONIO ROSSI', TO_DATE('1980-02-05','YYYY-MM-DD'), 5);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(19, 'PAOLO VERDI', TO_DATE('1974-09-15','YYYY-MM-DD'), 5);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(1, 'MARIO ROSSI', TO_DATE('1960-10-10','YYYY-MM-DD'), 5);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(2, 'MARIO BIANCHI', TO_DATE('1970-08-15','YYYY-MM-DD'), 5);
INSERT INTO "GV_TEST"."PERSON"("ID", "NAME", "BIRTHDATE", "ID_CITY")
  VALUES(12, 'PAOLO ROSSI', TO_DATE('1984-07-25','YYYY-MM-DD'), 5);

INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(1, 37, '1111222233334444', 370, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(2, 24, '1111222233335555', 250, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(3, 33, '1111222233336666', 100, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(4, 33, '1111222233337777', 10, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(5, 34, '1111222233338888', 48.32, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(6, 19, '1111222233330000', 0, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(7, 1, '0000111122223333', 200, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(8, 2, '1234123412341234', 100, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(9, 2, '1234123415555666', 300, 'Y');
INSERT INTO "GV_TEST"."CREDIT_CARD"("ID", "ID_OWNER", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(10, 34, '1234123414444789', 600, 'Y');
INSERT INTO "GV_TEST"."EXTERNAL_CREDIT_CARD"("ID", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(1, '8888222233334444', 90, 'Y');
INSERT INTO "GV_TEST"."EXTERNAL_CREDIT_CARD"("ID", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(2, '8888222233335555', 0, 'N');
INSERT INTO "GV_TEST"."EXTERNAL_CREDIT_CARD"("ID", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(3, '8888222233336666', 610, 'Y');
INSERT INTO "GV_TEST"."EXTERNAL_CREDIT_CARD"("ID", "CNUMBER", "CREDIT", "ENABLED")
  VALUES(4, '8888222233337777', 0, 'N');
commit;

This information have been used in preconfigured Services as ProcessSVCEmails and ProcessSingleSVCEmail.