Difference between revisions of "HSQLDB support database"
(Created page with " Into {{GVESB}} it is also included the package [http://hsqldb.org/doc/2.0/guide/index.html HSQLDB]. Obviously, you might choose another DB manager. For the {{GVESB}} preconfigu...") |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | Into {{GVESB}} it is also included the package [http://hsqldb.org/doc/2.0/guide/index.html HSQLDB]. | ||
| − | + | Obviously, you might choose another DB manager. For the {{GVESB}} preconfigured Services we preferred HSQLDB because of its "lightness". | |
To open a Client HSQLDB execute the following script: | To open a Client HSQLDB execute the following script: | ||
| Line 14: | Line 15: | ||
| [[File:windows.png]] | | [[File:windows.png]] | ||
| In case of Windows operating system, from $GV_HOME/hsqldb/bin, the script is ''runDBManager.cmd ''. | | In case of Windows operating system, from $GV_HOME/hsqldb/bin, the script is ''runDBManager.cmd ''. | ||
| − | |} | + | |} |
The connection parameters are: | The connection parameters are: | ||
| Line 24: | Line 25: | ||
For HSQLDB we created the following schema: | For HSQLDB we created the following schema: | ||
| − | <syntaxhighlight lang=" | + | <syntaxhighlight lang="SQL"> |
CREATE SCHEMA GV_TEST AUTHORIZATION DBA; | CREATE SCHEMA GV_TEST AUTHORIZATION DBA; | ||
CREATE USER "gv_test" PASSWORD 'gv_test' ADMIN; | CREATE USER "gv_test" PASSWORD 'gv_test' ADMIN; | ||
Latest revision as of 14:20, 24 February 2012
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:
|
In case of *nix operating systems, from $GV_HOME/hsqldb/lib, the script is java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing. |
|
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.
