gvSIG data provider for Oracle

-- fetchFeatureProviderByReference SQL

SELECT 

    "P1"."TEST"."ID", "P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long", 

    "P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time", 

    "P1"."TEST"."Bool2", "P1"."TEST"."String", "P1"."TEST"."Bool3", "P1"."TEST"."Double", 

    "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal", 

    NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL) 

FROM "P1"."TEST" 

WHERE ( ("P1"."TEST"."ID") = (?) ) AND  ROWNUM <= 1;

-- Count SQL

--SELECT NVL2((SDO_AGGR_MBR("Geometry")),(SDO_AGGR_MBR("Geometry")).Get_WKB(),NULL) 

--FROM "public"."test" 

--WHERE ( ("Geometry") IS NOT NULL );

SELECT NVL2((SDO_AGGR_MBR("P1"."TEST"."Geometry")),(SDO_AGGR_MBR("P1"."TEST"."Geometry")).Get_WKB(),NULL) 

FROM "P1"."TEST" 

WHERE ( ("P1"."TEST"."Geometry") IS NOT NULL );

-- Se usa en TestCreateTable.

-- Se corresponde con el fichero CSV "testCreateSource1.csv".

CREATE TABLE "P1"."TEST" (

    "ID" NUMBER(9,0), 

    "Byte" NUMBER(3,0) DEFAULT NULL, 

    "Bool1" CHAR(1) DEFAULT NULL, 

    "Long" NUMBER(18,0) DEFAULT NULL, 

    "Timestamp" TIMESTAMP DEFAULT NULL, 

    "Date" DATE DEFAULT NULL, 

    "Time" TIMESTAMP DEFAULT NULL, 

    "Bool2" CHAR(1) DEFAULT NULL, 

    "String" NVARCHAR2(30) DEFAULT NULL, 

    "Bool3" CHAR(1) DEFAULT NULL, 

    "Double" BINARY_DOUBLE DEFAULT NULL, 

    "Bool4" CHAR(1) DEFAULT NULL, 

    "Float" BINARY_FLOAT DEFAULT NULL, 

    "Bool5" CHAR(1) DEFAULT NULL, 

    "Decimal" NUMBER(6,3) DEFAULT NULL, 

    "GEOMETRY"  SDO_GEOMETRY DEFAULT NULL 

);

ALTER TABLE "P1"."TEST" ADD PRIMARY KEY ("ID");

DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS 

WHERE F_TABLE_SCHEMA = 'P1' 

    AND F_TABLE_NAME = 'TEST' 

    AND F_GEOMETRY_COLUMN = 'GEOMETRY';

INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS (

    F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) 

    VALUES ('P1', 'TEST', 'GEOMETRY', 1);

-- Create table with CLOB

CREATE TABLE "P1"."TEST" (

    "ID" NUMBER(9,0), 

    "Byte" NUMBER(3,0) DEFAULT NULL, 

    "Bool1" CHAR(1) DEFAULT NULL, 

    "Long" NUMBER(18,0) DEFAULT NULL, 

    "Timestamp" TIMESTAMP DEFAULT NULL, 

    "Date" DATE DEFAULT NULL, 

    "Time" TIMESTAMP DEFAULT NULL, 

    "Bool2" CHAR(1) DEFAULT NULL, 

    "String" NCLOB DEFAULT NULL, 

    "Bool3" CHAR(1) DEFAULT NULL, 

    "Double" BINARY_DOUBLE DEFAULT NULL, 

    "Bool4" CHAR(1) DEFAULT NULL, 

    "Float" BINARY_FLOAT DEFAULT NULL, 

    "Bool5" CHAR(1) DEFAULT NULL, 

    "Decimal" NUMBER(6,3) DEFAULT NULL, 

    "GEOMETRY"  SDO_GEOMETRY DEFAULT NULL 

);

ALTER TABLE "P1"."TEST" ADD PRIMARY KEY ("ID");

DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS 

WHERE F_TABLE_SCHEMA = 'P1' 

    AND F_TABLE_NAME = 'TEST' 

    AND F_GEOMETRY_COLUMN = 'GEOMETRY';

INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS (

    F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) 

    VALUES ('P1', 'TEST', 'GEOMETRY', 1);

-- Create table with large string

CREATE TABLE "P1"."TEST" (

    "ID" NUMBER(9,0), 

    "Byte" NUMBER(3,0) DEFAULT NULL, 

    "Bool1" CHAR(1) DEFAULT NULL, 

    "Long" NUMBER(18,0) DEFAULT NULL, 

    "Timestamp" TIMESTAMP DEFAULT NULL, 

    "Date" DATE DEFAULT NULL, 

    "Time" TIMESTAMP DEFAULT NULL, 

    "Bool2" CHAR(1) DEFAULT NULL, 

    "String" NVARCHAR2(4086) DEFAULT NULL, 

    "Bool3" CHAR(1) DEFAULT NULL, 

    "Double" BINARY_DOUBLE DEFAULT NULL, 

    "Bool4" CHAR(1) DEFAULT NULL, 

    "Float" BINARY_FLOAT DEFAULT NULL, 

    "Bool5" CHAR(1) DEFAULT NULL, 

    "Decimal" NUMBER(6,3) DEFAULT NULL, 

    "GEOMETRY"  SDO_GEOMETRY DEFAULT NULL 

);

ALTER TABLE "P1"."TEST" ADD PRIMARY KEY ("ID");

DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS 

WHERE F_TABLE_SCHEMA = 'P1' 

    AND F_TABLE_NAME = 'TEST' 

    AND F_GEOMETRY_COLUMN = 'GEOMETRY';

INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS (

    F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) 

    VALUES ('P1', 'TEST', 'GEOMETRY', 1);

-- Create table with Index 3D geometry

CREATE TABLE "P1"."TEST" (

    "ID" NUMBER(9,0), 

    "Byte" NUMBER(3,0) DEFAULT NULL, 

    "Bool1" CHAR(1) DEFAULT NULL, 

    "Long" NUMBER(18,0) DEFAULT NULL, 

    "Timestamp" TIMESTAMP DEFAULT NULL, 

    "Date" DATE DEFAULT NULL, 

    "Time" TIMESTAMP DEFAULT NULL, 

    "Bool2" CHAR(1) DEFAULT NULL, 

    "String" NVARCHAR2(30) DEFAULT NULL, 

    "Bool3" CHAR(1) DEFAULT NULL, 

    "Double" BINARY_DOUBLE DEFAULT NULL, 

    "Bool4" CHAR(1) DEFAULT NULL, 

    "Float" BINARY_FLOAT DEFAULT NULL, 

    "Bool5" CHAR(1) DEFAULT NULL, 

    "Decimal" NUMBER(6,3) DEFAULT NULL, 

    "GEOMETRY"  SDO_GEOMETRY DEFAULT NULL 

);

ALTER TABLE "P1"."TEST" ADD PRIMARY KEY ("ID");

DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS 

WHERE F_TABLE_SCHEMA = 'P1' 

    AND F_TABLE_NAME = 'TEST' 

    AND F_GEOMETRY_COLUMN = 'GEOMETRY';

INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS (

    F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) 

    VALUES ('P1', 'TEST', 'GEOMETRY', 1001);

CREATE INDEX "SDX_TEST_GEOMETRY" ON "TEST" ("Geometry") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('SDO_INDX_DIMS=3,LAYER_GTYPE=POINT');

ALTER INDEX "SDX_TEST_GEOMETRY" REBUILD PARAMETERS ('SDO_INDX_DIMS=3,LAYER_GTYPE=POINT');

-- Create table with Index 2D geometry

CREATE TABLE "P1"."TEST" (

    "ID" NUMBER(9,0), 

    "Byte" NUMBER(3,0) DEFAULT NULL, 

    "Bool1" CHAR(1) DEFAULT NULL, 

    "Long" NUMBER(18,0) DEFAULT NULL, 

    "Timestamp" TIMESTAMP DEFAULT NULL, 

    "Date" DATE DEFAULT NULL, 

    "Time" TIMESTAMP DEFAULT NULL, 

    "Bool2" CHAR(1) DEFAULT NULL, 

    "String" NVARCHAR2(30) DEFAULT NULL, 

    "Bool3" CHAR(1) DEFAULT NULL, 

    "Double" BINARY_DOUBLE DEFAULT NULL, 

    "Bool4" CHAR(1) DEFAULT NULL, 

    "Float" BINARY_FLOAT DEFAULT NULL, 

    "Bool5" CHAR(1) DEFAULT NULL, 

    "Decimal" NUMBER(6,3) DEFAULT NULL, 

    "GEOMETRY"  SDO_GEOMETRY DEFAULT NULL 

);

ALTER TABLE "P1"."TEST" ADD PRIMARY KEY ("ID");

DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS 

WHERE F_TABLE_SCHEMA = 'P1' 

    AND F_TABLE_NAME = 'TEST' 

    AND F_GEOMETRY_COLUMN = 'GEOMETRY';

INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS (

    F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) 

    VALUES ('P1', 'TEST', 'GEOMETRY', 1);

CREATE INDEX "SDX_TEST_GEOMETRY" ON "TEST" ("Geometry") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('SDO_INDX_DIMS=2,LAYER_GTYPE=POINT');

ALTER INDEX "SDX_TEST_GEOMETRY" REBUILD PARAMETERS ('SDO_INDX_DIMS=2,LAYER_GTYPE=POINT');

-- Drop table SQL

DROP TABLE "P1"."TEST";

DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = 'P1' AND F_TABLE_NAME = 'test';

-- Date SQL

SELECT 

    "P1"."TEST"."ID", "P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long", 

    "P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time", "P1"."TEST"."Bool2", 

    "P1"."TEST"."String", "P1"."TEST"."Bool3", "P1"."TEST"."Double", 

    "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal", 

    NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL) 

FROM "P1"."TEST" 

WHERE ( 

    (( 

        (( ("P1"."TEST"."Time") > (TIMESTAMP '1970-01-01 01:02:03') )) 

        AND 

        (( ("P1"."TEST"."Time") < (TIMESTAMP '1970-01-01 20:52:55') )) 

    )) 

    OR 

    (( 

        (( ("P1"."TEST"."Date") > (DATE '2019-02-17') )) 

        AND 

        (( ("P1"."TEST"."Date") < (DATE '2020-02-23') )) 

    )) 

) ORDER BY "P1"."TEST"."ID" ASC;

-- Table is empty

SELECT 1 FROM "P1"."TEST" WHERE  ROWNUM <= 1;

-- Insert SQL

INSERT INTO "P1"."TEST" ( 

    "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", 

    "Decimal", "Geometry" 

) VALUES ( 

    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NVL2((?),SDO_GEOMETRY((?), (?)),NULL) 

);

-- Delete SQL

DELETE FROM "P1"."TEST" WHERE ( ("ID") = (?) );

-- Update SQL

UPDATE "P1"."TEST" 

    SET 

        "Byte" = ?, 

        "Bool1" = ?, 

        "Long" = ?, 

        "Timestamp" = ?, 

        "Date" = ?, 

        "Time" = ?, 

        "Bool2" = ?, 

        "String" = ?, 

        "Bool3" = ?, 

        "Double" = ?, 

        "Bool4" = ?, 

        "Float" = ?, 

        "Bool5" = ?, 

        "Decimal" = ?, 

        "Geometry" = NVL2((?),SDO_GEOMETRY((?), (?)),NULL) 

    WHERE 

        ( ("ID") = (?) );

-- Update Table SQL

ALTER TABLE "P1"."TEST" ADD "Byte" NUMBER(3,0) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Bool1" CHAR(1) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Long" NUMBER(18,0) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Timestamp" TIMESTAMP DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Date" DATE DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Time" TIMESTAMP DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Bool2" CHAR(1) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "String" NVARCHAR2(30) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Bool3" CHAR(1) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Double" BINARY_DOUBLE DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Bool4" CHAR(1) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Float" BINARY_FLOAT DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Bool5" CHAR(1) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Decimal" NUMBER(6,3) DEFAULT NULL NULL;

ALTER TABLE "P1"."TEST" ADD "Geometry" SDO_GEOMETRY DEFAULT NULL NULL;

DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS 

    WHERE F_TABLE_SCHEMA = 'P1' 

    AND F_TABLE_NAME = 'test' 

    AND F_GEOMETRY_COLUMN = 'Geometry';

INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS (

    F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) 

    VALUES ('P1', 'test', 'Geometry', 1001);

-- UpdateTableStatistics SQL

ANALYZE TABLE "P1"."TEST" COMPUTE STATISTICS;

INSERT INTO "P1"."TEST" ( 

    "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", 

    "Decimal", "Geometry" 

) VALUES ( 

    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NVL2((?),SDO_GEOMETRY((?), (?)),NULL) );

-- FetchFeatureType first row SQL

SELECT * FROM "P1"."TEST" WHERE  ROWNUM <= 0;

SELECT "USER_TAB_COLS"."COLUMN_NAME", "ALL_CONSTRAINTS"."CONSTRAINT_TYPE" 

FROM USER_TAB_COLS 

JOIN ALL_CONS_COLUMNS on (ALL_CONS_COLUMNS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME and ALL_CONS_COLUMNS.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME) 

LEFT JOIN ALL_CONSTRAINTS on ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME 

WHERE ( 

    (( ("USER_TAB_COLS"."TABLE_NAME") LIKE ('TEST') )) 

    AND 

    (( ("ALL_CONSTRAINTS"."CONSTRAINT_TYPE") = ('P') )) 

);

-- normalize-spaces true

-- strip-start true

-- remove-nl true

-- replace-nl-by-space true

-- trim-end true

-- rem Ojo!, deberia ser "dbtest"."test" y no solo "test" ???

-- begin count

SELECT COUNT(*) FROM "P1"."TEST"

-- end count

-- begin testAggregatesAndGroup

-- rem Count with aggregates and group SQL

SELECT COUNT(*) 

FROM ( 

    SELECT MAX("P1"."TEST"."Byte") "Byte", 

        MIN("P1"."TEST"."ID") "ID", 

        SUM("P1"."TEST"."Double") "Double" 

    FROM "P1"."TEST" 

    GROUP BY "P1"."TEST"."Long") "_subquery_alias_" 

-- end testAggregatesAndGroup

-- begin testGroup

-- rem Count with group SQL

SELECT COUNT(*) 

FROM ( SELECT "P1"."TEST"."Long" 

      FROM "P1"."TEST" 

      GROUP BY "P1"."TEST"."Long") "_subquery_alias_" 

-- end testGroup

-- begin testAggregates

-- rem Count with aggregates

SELECT COUNT(*) 

FROM ( SELECT MAX("P1"."TEST"."Byte") "Byte", 

             MIN("P1"."TEST"."ID") "ID", 

             SUM("P1"."TEST"."Double") "Double" 

      FROM "P1"."TEST") "_subquery_alias_" 

-- end testAggregates

-- ResultSetForSetProvider SQL

-- Simple

SELECT 

    "P1"."TEST"."ID", "P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long", 

    "P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time", 

    "P1"."TEST"."Bool2", "P1"."TEST"."String", "P1"."TEST"."Bool3", 

    "P1"."TEST"."Double", "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal", 

    NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL) 

FROM "P1"."TEST" ORDER BY "P1"."TEST"."ID" ASC;

-- Simple Group by

SELECT MIN("P1"."TEST"."ID") "ID", MAX("P1"."TEST"."Byte") "Byte", NULL "Bool1", "P1"."TEST"."Long", 

    NULL "Timestamp", NULL "Date", NULL "Time", NULL "Bool2", 

    NULL "String", NULL "Bool3", SUM("P1"."TEST"."Double") "Double", 

    NULL "Bool4", NULL "Float", NULL "Bool5", NULL "Decimal", 

    NULL "Geometry" 

FROM "P1"."TEST" 

GROUP BY "P1"."TEST"."Long" 

ORDER BY "P1"."TEST"."Long" ASC NULLS LAST;

-- Subselect

SELECT "P1"."TEST"."ID", 

       "P1"."TEST"."Byte", 

       "P1"."TEST"."Bool1", 

       "P1"."TEST"."Long", 

       "P1"."TEST"."Timestamp", 

       "P1"."TEST"."Date", 

       "P1"."TEST"."Time", 

       "P1"."TEST"."Bool2", 

       "P1"."TEST"."String", 

       "P1"."TEST"."Bool3", 

       "P1"."TEST"."Double", 

       "P1"."TEST"."Bool4", 

       "P1"."TEST"."Float", 

       "P1"."TEST"."Bool5", 

       "P1"."TEST"."Decimal", 

       NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL), 

       CASE 

         WHEN (EXISTS(( SELECT "P1"."COUNTRIES"."ISO_A2" 

                       FROM "P1"."COUNTRIES" 

                       WHERE ( (( ("P1"."TEST"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND  ROWNUM <= 1 ))) THEN (1) 

         ELSE (0) 

       END "EXISTS62a964cd7bc24f409b97c03b9170408d" 

FROM "P1"."TEST" 

WHERE EXISTS(( SELECT "P1"."COUNTRIES"."ISO_A2" 

                       FROM "P1"."COUNTRIES" 

                       WHERE ( (( ("P1"."TEST"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND  ROWNUM <= 1 )) 

ORDER BY "P1"."TEST"."ID" ASC;

-- Group and Subselect

SELECT MIN("P1"."TEST"."ID") "ID", MAX("P1"."TEST"."Byte") "Byte", NULL "Bool1", "P1"."TEST"."Long", NULL "Timestamp", NULL "Date", NULL "Time", NULL "Bool2", NULL "String", NULL "Bool3", SUM("P1"."TEST"."Double") "Double", NULL "Bool4", NULL "Float", NULL "Bool5", NULL "Decimal", NULL "Geometry" FROM "P1"."TEST" WHERE EXISTS(( SELECT "P1"."COUNTRIES"."ISO_A2" FROM "P1"."COUNTRIES" WHERE ( (( ("P1"."TEST"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND  ROWNUM <= 1 )) GROUP BY "P1"."TEST"."Long" ORDER BY "P1"."TEST"."Long" ASC NULLS LAST;

-- Constant Column Primary Key

SELECT 

    "P1"."TEST"."ID", 

    "P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long", 

    "P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time", 

    "P1"."TEST"."Bool2", "P1"."TEST"."String", "P1"."TEST"."Bool3", 

    "P1"."TEST"."Double", "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal", 

    NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL) 

FROM "P1"."TEST" ORDER BY "P1"."TEST"."ID" ASC;

-- CLOB

SELECT 

    "P1"."TEST"."ID", "P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long", 

    "P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time", 

    "P1"."TEST"."Bool2", "P1"."TEST"."String", "P1"."TEST"."Bool3", 

    "P1"."TEST"."Double", "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal", 

    NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL) 

FROM "P1"."TEST" 

WHERE dbms_lob.compare(("P1"."TEST"."String"),('hola')) = 0 ORDER BY "P1"."TEST"."ID" ASC;

ID__Integer__set__size=0__set__precision=10__set__scale=0__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=true__set__automatic=false__set__isTime=false__set__locale=es__set__order=0__set__roundMode=4;Byte__Byte__set__size=0__set__precision=3__set__scale=0__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=10__set__roundMode=4;Bool1__Boolean__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=20__set__roundMode=4;Long__Long__set__size=0__set__precision=19__set__scale=0__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=30__set__roundMode=4;Timestamp__Timestamp__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=40__set__roundMode=4;Date__Date__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=50__set__roundMode=4;Time__Time__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=60__set__roundMode=4;Bool2__Boolean__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=70__set__roundMode=4;String__String__set__size=30__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=80__set__roundMode=4;Bool3__Boolean__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=90__set__roundMode=4;Double__Double__set__size=0__set__precision=16__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=100__set__roundMode=4;Bool4__Boolean__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=110__set__roundMode=4;Float__Float__set__size=0__set__precision=8__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=120__set__roundMode=4;Bool5__Boolean__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=130__set__roundMode=4;Decimal__Decimal__set__size=0__set__precision=6__set__scale=3__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=140__set__roundMode=4;Geometry__Geometry__set__size=0__set__precision=-1__set__scale=-1__set__hidden=false__set__readOnly=false__set__allowNull=true__set__pk=false__set__automatic=false__set__isTime=false__set__locale=es__set__order=150__set__roundMode=4__set__srs=EPSG:25830__set__geomtype=Point@2D

0;;;;;;;;      ;;;;;true;;

1;10;true;1000;29-dic-2019 12:13:14;29 de diciembre de 2019;12:13:14 CET;true;Yo yo1;false;1234554321;true;12321;true;456,123;POINT (-3.8945156972987958 42.01053743584765)

2;20;true;2000;29-nov-2019 12:13:14;29 de noviembre de 2019;13:13:14 CET;true;Yo yo2;false;1210054321;true;10021;true;456,123;POINT (-2.1079618220646115 41.983079082675474)

3;30;true;3000;29-oct-2019 12:13:14;29 de octubre de 2019;14:13:14 CET;true;Yo yo3;false;1210154321;true;10121;true;456,123;POINT (-2.57249737803327 41.35372113353277)

4;40;true;4000;29-sep-2019 12:13:14;29 de septiembre de 2019;15:13:14 CET;true;Yo yo4;false;1210254321;true;10221;true;456,123;POINT (-4.061822048036304 41.35877680235475)

5;50;true;5000;29-ago-2019 12:13:14;29 de agosto de 2019;16:13:14 CET;true;Yo yo5;false;1210354321;true;10321;true;456,123;POINT (-3.974317066986988 40.78701209315094)

6;60;true;6000;29-jul-2019 12:13:14;29 de julio de 2019;17:13:14 CET;true;Yo yo6;false;1210454321;true;10421;true;456,123;POINT (-2.510509736717547 40.69847453392384)

7;70;true;7000;29-jun-2019 12:13:14;29 de junio de 2019;18:13:14 CET;true;Yo yo7;false;1210554321;true;10521;true;456,123;POINT (-0.5317736981843011 40.66396082637622)

8;80;true;8000;29-may-2019 12:13:14;29 de mayo de 2019;19:13:14 CET;true;Yo yo8;false;1210654321;true;10621;true;456,123;POINT (-0.3626917259170671 41.13652386601604)

9;90;true;9000;29-abr-2019 12:13:14;29 de abril de 2019;20:13:14 CET;true;Yo yo9;false;1210754321;true;10721;true;456,123;POINT (-1.2461823078608523 41.84950010180092)

10;;true;1001;29-dic-2019 12:13:14;29 de diciembre de 2019;12:13:14 CET;true;Yo yo1;false;1234554321;true;12321;true;456,123;POINT (-1.2145405488596532 41.22158511004416)

11;22;;2003;29-nov-2019 12:13:14;29 de noviembre de 2019;13:13:14 CET;true;Yo yo 2;false;1210054321;true;10021;true;456,123;POINT (-0.7699089544899235 41.630581204431756)

12;33;true;;29-sep-2019 12:13:14;29 de octubre de 2019;14:13:14 CET;true;Yo yo3;false;1210154321;true;10121;true;456,123;POINT (0.4821915816701051 41.75970939133133)

13;41;true;4001;;29 de septiembre de 2019;15:13:14 CET;true;Yo yo4;false;1210254321;true;10221;true;456,123;POINT (0.7912661147227479 41.919324620992036)

14;52;true;5002;29-ago-2019 12:13:14;;16:13:14 CET;true;Yo yo5;false;1210354321;true;10321;true;456,123;POINT (1.052534629531243 41.493736996249545)

15;63;true;6003;29-jul-2019 12:13:14;29 de julio de 2019;;true;Yo yo6;false;1210454321;true;10421;true;456,123;POINT (0.8097002367335026 41.0899480235613)

16;74;true;7004;29-jun-2019 12:13:14;29 de junio de 2019;18:13:14 CET;true; ;false;1210554321;true;10521;true;456,123;POINT (-0.4883960310112362 41.17597288081971)

17;85;true;8005;29-may-2019 12:13:14;29 de mayo de 2019;19:13:14 CET;true;Yo yo8;false;;true;10621;true;456,123;POINT (-0.6439030698437881 40.89530766155764)

18;96;true;9006;29-abr-2019 12:13:14;29 de abril de 2019;20:13:14 CET;true;Yo yo9;false;1210754321;true;;true;;POINT (-1.3061826868199504 40.72372835570524)

Byte/Byte

10

20

30

40

50

60

70

80

90

22

33

41

52

63

74

85

96

ID/Integer/set/pk=true;Byte/Byte;Bool1/Boolean;Long/Long;Timestamp/TimeStamp;Date/Date;Time/Time;Bool2/Boolean;String/String/set/size=30;Bool3/Boolean;Double/Double;Bool4/Boolean;Float/Float;Bool5/Boolean;Decimal/Decimal/set/precision=6/set/scale=3;Geometry/Geometry/set/geomtype=Point:2D/set/srs=EPSG:4326

0;  ; ;    ;              ;        ;      ; ;      ; ;           ; ;      ;T;       ;

1;10;T;1000;20191229121314;20191229;121314;T;Yo yo1;F;12345.54321;T;123.21;T;456.123;POINT (-3.8945156972987958 42.01053743584765)

2;20;T;2000;20191129121314;20191129;131314;T;Yo yo2;F;12100.54321;T;100.21;T;456.123;POINT (-2.1079618220646115 41.983079082675474)

3;30;T;3000;20191029121314;20191029;141314;T;Yo yo3;F;12101.54321;T;101.21;T;456.123;POINT (-2.57249737803327 41.35372113353277)

4;40;T;4000;20190929121314;20190929;151314;T;Yo yo4;F;12102.54321;T;102.21;T;456.123;POINT (-4.061822048036304 41.35877680235475)

5;50;T;5000;20190829121314;20190829;161314;T;Yo yo5;F;12103.54321;T;103.21;T;456.123;POINT (-3.974317066986988 40.78701209315094)

6;60;T;6000;20190729121314;20190729;171314;T;Yo yo6;F;12104.54321;T;104.21;T;456.123;POINT (-2.510509736717547 40.69847453392384)

7;70;T;7000;20190629121314;20190629;181314;T;Yo yo7;F;12105.54321;T;105.21;T;456.123;POINT (-0.5317736981843011 40.66396082637622)

8;80;T;8000;20190529121314;20190529;191314;T;Yo yo8;F;12106.54321;T;106.21;T;456.123;POINT (-0.3626917259170671 41.13652386601604)

9;90;T;9000;20190429121314;20190429;201314;T;Yo yo9;F;12107.54321;T;107.21;T;456.123;POINT (-1.2461823078608523 41.84950010180092)

10;;T;1001;20191229121314;20191229;121314;T;Yo yo1;F;12345.54321;T;123.21;T;456.123;POINT (-1.2145405488596532 41.22158511004416)

11;22; ;2002;20191129121314;20191129;131314;T;Yo yo2;F;12100.54321;T;100.21;T;456.123;POINT (-0.7699089544899235 41.630581204431756)

12;33;T; ;20190929121314;20191029;141314;T;Yo yo3;F;12101.54321;T;101.21;T;456.123;POINT (0.4821915816701051 41.75970939133133)

13;41;T;4001; ;20190929;151314;T;Yo yo4;F;12102.54321;T;102.21;T;456.123;POINT (0.7912661147227479 41.919324620992036)

14;52;T;5002;20190829121314; ;161314;T;Yo yo5;F;12103.54321;T;103.21;T;456.123;POINT (1.052534629531243 41.493736996249545)

15;63;T;6003;20190729121314;20190729; ;T;Yo yo6;F;12104.54321;T;104.21;T;456.123;POINT (0.8097002367335026 41.0899480235613)

16;74;T;7004;20190629121314;20190629;181314;T; ;F;12105.54321;T;105.21;T;456.123;POINT (-0.4883960310112362 41.17597288081971)

17;85;T;8005;20190529121314;20190529;191314;T;Yo yo8;F; ;T;106.21;T;456.123;POINT (-0.6439030698437881 40.89530766155764)

18;96;T;9006;20190429121314;20190429;201314;T;Yo yo9;F;12107.54321;T; ;T; ;POINT (-1.3061826868199504 40.72372835570524)

log4j.rootLogger=INFO, stdout

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

# Pattern to output the caller's file name and line number.

log4j.appender.stdout.layout.ConversionPattern=%5p %r [%t] (%F:%L) - %m%n

log4j.logger.org.gvsig.andami.plugins=INFO

log4j.logger.org.gvsig.tools=INFO

log4j.logger.org.gvsig.tools.dataTypes=INFO

log4j.logger.org.gvsig.tools.library=INFO

log4j.logger.org.gvsig.tools.persistence=INFO

log4j.logger.org.gvsig.i18n.Messages=INFO

log4j.logger.org.gvsig.installer.lib.impl.DefaultDependenciesCalculator=INFO

#

# JCRS

log4j.logger.org.gvsig.crs=INFO

log4j.logger.es.idr.teledeteccion.connection=INFO

log4j.logger.org.geotools.referencing.operation.projection=INFO

#

# JDBC/BBDD

# Core

log4j.logger.org.gvsig.fmap.dal.store.jdbc=INFO

log4j.logger.org.gvsig.fmap.dal.store.jdbc2=INFO

log4j.logger.org.gvsig.fmap.dal.feature.spi=INFO

# Data providers

log4j.logger.org.gvsig.fmap.dal.store.h2=INFO

log4j.logger.org.gvsig.postgresql.dal=INFO

log4j.logger.org.gvsig.mssqlserver.dal=INFO

log4j.logger.org.gvsig.mysql.dal=INFO

log4j.logger.org.gvsig.spatialite.dal=INFO

log4j.logger.org.gvsig.oracle.dal=INFO

# Exoport providers

log4j.logger.org.gvsig.exportto.swing.prov.jdbc=INFO

log4j.logger.org.gvsig.exportto.swing.prov.h2=INFO

#

# shp/dbf driver

log4j.logger.org.gvsig.fmap.dal.store.shp=INFO

log4j.logger.org.gvsig.fmap.dal.store.dbf=INFO

#

package org.gvsig.oracle.dal;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.feature.FeatureStore;

import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters;

import org.gvsig.fmap.dal.store.jdbc2.AbstractTestUtils;

import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper;

import org.gvsig.fmap.dal.store.jdbc2.spi.FakeConnectionProvider;

/**

 *

 * @author jjdelcerro

 */

public class TestUtilsOracle extends AbstractTestUtils {

    @Override

    public String getProviderName() {

        return OracleLibrary.NAME;

    }

    @Override

    public String getExpectedsPath() {

        return "/org/gvsig/oracle/dal/operations/sql";

    }

    @Override

    public JDBCHelper createJDBCHelper() throws Exception {

      OracleConnectionParameters params = (OracleConnectionParameters) this.getServerExplorerParameters("fake");

      OracleHelper helper = new OracleHelper(params, new FakeConnectionProvider());

      return helper;

    }

    @Override

    public JDBCServerExplorerParameters getServerExplorerParameters(String dbname) throws Exception {

        return this.getOracleServerExplorerParameters(dbname);

    }

    public JDBCServerExplorerParameters getOracleServerExplorerParameters(String dbname) throws Exception {

        OracleExplorerParameters conn = TestUtils.buildDBConnection();

        return conn;

    }

    @Override

    public void runSQLToCheckSyntax(String testName, FeatureStore datacsv, String testTableName, String sql) throws Exception {

        // Do nothing

    }

}

package org.gvsig.oracle.dal;

import junit.framework.TestCase;

import org.apache.commons.lang3.ArrayUtils;

import org.cresques.cts.IProjection;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.fmap.dal.SQLBuilder.Privilege;

import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase;

import org.gvsig.fmap.geom.DataTypes;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.primitive.Polygon;

import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;

public class OracleSQLBuilderTest extends TestCase {

    public OracleSQLBuilderTest(String testName) {

        super(testName);

    }

    @Override

    protected void setUp() throws Exception {

        super.setUp();

        new DefaultLibrariesInitializer().fullInitialize();

    }

    @Override

    protected void tearDown() throws Exception {

        super.tearDown();

    }

    private SQLBuilder createSQLBuilder() {

        return new OracleSQLBuilder(TestUtils.getJDBCHelper());

    }

    public void testCalculateEnvelopeOfColumn() throws Exception {

        TableReference table = new OperationsFactoryBase.DefaultTableReference("master","dbo","test1", null);

        String columnName = "geom";

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().value(

            expbuilder.as_geometry(

                expbuilder.ST_ExtentAggregate(

                        expbuilder.column(columnName)

                )

            )

        );

        //sqlbuilder.select().group_by(expbuilder.column(columnName));

        sqlbuilder.select().from().table()

                .database(table.getDatabase())

                .schema(table.getSchema())

                .name(table.getTable());

        sqlbuilder.select().from().subquery(table.getSubquery());

        sqlbuilder.select().where().set(        

            expbuilder.not_is_null(expbuilder.column(columnName))

        );

        System.out.println("# Test:: testCalculateEnvelopeOfColumn");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "SELECT NVL2((SDO_AGGR_MBR(\"geom\")),(SDO_AGGR_MBR(\"geom\")).Get_WKB(),NULL) FROM \"dbo\".\"TEST1\" WHERE ( (\"geom\") IS NOT NULL )", 

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testCalculateEnvelope() throws Exception {

        GeometryManager geometryManager = GeometryLocator.getGeometryManager();

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);

        limit.addVertex(0, 0);

        limit.addVertex(0, 100);

        limit.addVertex(100, 100);

        limit.addVertex(100, 0);

        limit.addVertex(0, 0);

        limit.setProjection(proj);

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().value(

            expbuilder.as_geometry(

              expbuilder.ST_ExtentAggregate(

                expbuilder.column("the_geom")

              )

            )

        ).as("envelope");

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        sqlbuilder.select().where().set(

            expbuilder.ST_Intersects(

                expbuilder.ST_Envelope(

                    expbuilder.column("the_geom")

                ),

                expbuilder.geometry(limit, proj)

            )

        );

        sqlbuilder.select().where().and(

                expbuilder.custom("x = 27")

        );

        System.out.println("# Test:: testCalculateEnvelope");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "SELECT NVL2((SDO_AGGR_MBR(\"the_geom\")),(SDO_AGGR_MBR(\"the_geom\")).Get_WKB(),NULL) \"envelope\" FROM \"dbo\".\"TEST1\" WHERE ( ((SDO_RELATE(SDO_GEOM.SDO_MBR(\"the_geom\"), SDO_GEOMETRY((TO_BLOB(HEXTORAW('000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'))), (4326)), 'mask=ANYINTERACT') = 'TRUE')) AND (x = 27) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[the_geom]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testCount() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().value(sqlbuilder.count().all());

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        sqlbuilder.select().from().subquery(null);

        sqlbuilder.select().where().set( expbuilder.custom("pp = 200"));

        System.out.println("# Test:: testCount");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testCount

        //# SQL:: SELECT COUNT(*) FROM "test1" WHERE pp = 200

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "SELECT COUNT(*) FROM \"dbo\".\"TEST1\" WHERE pp = 200",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testCreateTable() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.create_table().table().database("master").schema("dbo").name("test1");

        sqlbuilder.create_table().add_column(

                "name",

                DataTypes.STRING,

                45,

                0,

                0,

                false,

                false,

                true,

                false,

                null

        );

        sqlbuilder.create_table().add_column(

                "id",

                DataTypes.INT,

                0,

                0,

                0,

                true,

                false,

                false,

                true,

                0

        );

        sqlbuilder.create_table().add_column(

                "geom",

                DataTypes.GEOMETRY,

                0,

                0,

                0,

                false,

                false,

                true,

                false,

                null

        );

        // CREATE TABLE "test1" ("name" VARCHAR(45) DEFAULT NULL, "id" INTEGER PRIMARY KEY AUTO_INCREMENT DEFAULT '0' NOT NULL, "geom" GEOMETRY ); ALTER TABLE "test1" ADD CONSTRAINT IF NOT EXISTS "constraint_test1_geom_dim" CHECK ST_CoordDim("geom") = 2

        System.out.println("# Test:: testCreateTable");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "CREATE TABLE \"dbo\".\"TEST1\" (\"name\" NVARCHAR2(45) DEFAULT NULL, \"id\" NUMBER(9,0), \"geom\"  SDO_GEOMETRY DEFAULT NULL ); ALTER TABLE \"dbo\".\"TEST1\" ADD PRIMARY KEY (\"id\"); CREATE SEQUENCE \"GVSEQ_TEST1_ID\"; CREATE OR REPLACE TRIGGER \"GVSER_TEST1_ID\" BEFORE INSERT ON \"dbo\".\"TEST1\" FOR EACH ROW BEGIN SELECT \"GVSEQ_TEST1_ID\".NEXTVAL INTO :new.\"id\" FROM dual; END;; DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = 'dbo' AND F_TABLE_NAME = 'TEST1' AND F_GEOMETRY_COLUMN = 'geom'; INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS (F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) VALUES ('dbo', 'TEST1', 'geom', 0)",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testDropTable() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.drop_table().table().database("master").schema("dbo").name("test1");

        // DROP TABLE "test1"

        System.out.println("# Test:: testDropTable");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "DROP TABLE \"dbo\".\"TEST1\"",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testFetchFeatureProviderByReference() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        String value = "yoyo";

        sqlbuilder.select().column().name("name");

        sqlbuilder.select().column().name("id");

        sqlbuilder.select().column().name("geom").as_geometry();

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        sqlbuilder.select().where().set(

            expbuilder.eq(

                expbuilder.column("name"),

                expbuilder.parameter(value).as_constant()

            )

        );

        sqlbuilder.select().limit(1);

        // SELECT "name", "id", ST_AsBinary("geom") FROM "master"."dbo"."TEST1" WHERE ( ("name") = (?) ) LIMIT 1

        System.out.println("# Test:: testFetchFeatureProviderByReference");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "SELECT \"name\", \"id\", NVL2((\"geom\"),(\"geom\").Get_WKB(),NULL) FROM \"dbo\".\"TEST1\" WHERE ( (\"name\") = (?) ) AND  ROWNUM <= 1",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "['yoyo']",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testFetchFeatureType() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().all();

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        sqlbuilder.select().limit(1);

        System.out.println("# Test:: testFetchFeatureType");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testFetchFeatureType

        //# SQL:: SELECT * FROM "test1" LIMIT 1

        //# Variables:: []

        //# Parametros:: []        

        assertEquals(

                "SELECT * FROM \"dbo\".\"TEST1\" WHERE  ROWNUM <= 1",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformDeletes() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.delete().table().database("master").schema("dbo").name("test1");

        sqlbuilder.delete().where().set(

            expbuilder.eq( 

                expbuilder.column("id1"),

                expbuilder.parameter("id1").as_variable()

            )

        );

        sqlbuilder.delete().where().and(

            expbuilder.eq( 

                expbuilder.column("id2"),

                expbuilder.parameter("id2").as_variable()

            )

        );

        System.out.println("# Test:: testPerformDeletes");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "DELETE FROM \"dbo\".\"TEST1\" WHERE ( (( (\"id1\") = (?) )) AND (( (\"id2\") = (?) )) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[id1, id2]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"id1\", \"id2\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformInserts1() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.insert().table().database("master").schema("dbo").name("test1");

        sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id"));

        sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));

        sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));

        System.out.println("# Test:: testPerformInserts1");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "INSERT INTO \"dbo\".\"TEST1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, NVL2((?),SDO_GEOMETRY((?), (4326)),NULL) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"id\", \"name\", \"geom\", \"geom\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformInserts2() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.insert().table().database("master").schema("dbo").name("test1");

        sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id"));

        sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));

        sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));

        System.out.println("# Test:: testPerformInserts2");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "INSERT INTO \"dbo\".\"TEST1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, NVL2((?),SDO_GEOMETRY((?), (4326)),NULL) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"id\", \"name\", \"geom\", \"geom\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformUpdates1() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.update().table().database("master").schema("dbo").name("test1");

        sqlbuilder.update().where().set(

            expbuilder.eq(

                expbuilder.column("id"), 

                expbuilder.parameter("id").as_variable()

            )

        );

        sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name"));

        sqlbuilder.update().column().name("geom").with_value(

                expbuilder.parameter("geom").as_geometry_variable().srs(proj) 

        );

        System.out.println("# Test:: testPerformUpdates");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "UPDATE \"dbo\".\"TEST1\" SET \"name\" = ?, \"geom\" = NVL2((?),SDO_GEOMETRY((?), (4326)),NULL) WHERE ( (\"id\") = (?) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"name\", \"geom\", \"geom\", \"id\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformUpdates2() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.update().table().database("master").schema("dbo").name("test1");

        sqlbuilder.update().where().set(

            expbuilder.eq(

                expbuilder.column("id"), 

                expbuilder.parameter("id").as_variable()

            )

        );

        sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name"));

        sqlbuilder.update().column().name("geom").with_value(

                expbuilder.parameter("geom").as_geometry_variable()

                        .srs(expbuilder.parameter().value(proj)) 

        );

        System.out.println("# Test:: testPerformUpdates");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "UPDATE \"dbo\".\"TEST1\" SET \"name\" = ?, \"geom\" = NVL2((?),SDO_GEOMETRY((?), (?)),NULL) WHERE ( (\"id\") = (?) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"name\", \"geom\", \"geom\", 4326, \"id\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testGrant1() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.grant().table().database("master").schema("dbo").name("test1");

        sqlbuilder.grant().role("prueba").select().insert().update();

        sqlbuilder.grant().role("gis").all();

        System.out.println("# Test:: testGrant1");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testGrant2() throws Exception {

        SQLBuilder sqlbuilder = new SQLBuilderBase();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.grant().table().database("master").schema("dbo").name("test1");

        sqlbuilder.grant().role("prueba").privilege(Privilege.SELECT)

                .privilege(Privilege.INSERT)

                .privilege(Privilege.UPDATE);

        sqlbuilder.grant().role("gis").privilege(Privilege.ALL);

        System.out.println("# Test:: testGrant2");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testSelect() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        GeometryManager geometryManager = GeometryLocator.getGeometryManager();

        Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);

        limit.addVertex(0, 0);

        limit.addVertex(0, 100);

        limit.addVertex(100, 100);

        limit.addVertex(100, 0);

        limit.addVertex(0, 0);

        limit.setProjection(proj);

        sqlbuilder.select().column().value(expbuilder.parameter("Geometry").as_geometry_variable().srs(proj));

        sqlbuilder.select().column().value(expbuilder.parameter("ID").as_variable());

        sqlbuilder.select().column().value(expbuilder.constant(limit));

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        System.out.println("# Test:: testSelect");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testCount

        //# SQL:: SELECT NVL2((?),SDO_GEOMETRY((?).Get_WKB(), (?)),NULL), "ID", SDO_GEOMETRY((TO_BLOB(HEXTORAW('000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'))), (4326)) FROM "dbo"."TEST1" 

        //# Variables:: []

        //# Parametros:: ["Geometry", "Geometry", "ID"]

        assertEquals(

                "SELECT NVL2((?),SDO_GEOMETRY((?), (4326)),NULL), ?, SDO_GEOMETRY((TO_BLOB(HEXTORAW('000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'))), (4326)) FROM \"dbo\".\"TEST1\"",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"Geometry\", \"Geometry\", \"ID\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testSelectIntersect() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        GeometryManager geometryManager = GeometryLocator.getGeometryManager();

        Polygon envelope = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);

        envelope.addVertex(0, 0);

        envelope.addVertex(0, 100);

        envelope.addVertex(100, 100);

        envelope.addVertex(100, 0);

        envelope.addVertex(0, 0);

        envelope.setProjection(proj);

        GeometryExpressionBuilderHelper.GeometryParameter column = expbuilder.parameter("Geometry").as_geometry_variable().srs(proj);

        sqlbuilder.select().column().value(column);

        sqlbuilder.select().column().value(expbuilder.parameter("ID").as_variable());

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        sqlbuilder.select().where().set(

                expbuilder.not_is_null(column)).and(

                expbuilder.ST_Intersects(

                        column,

                        expbuilder.geometry(envelope, proj)

                )

        );

        System.out.println("# Test:: testSelect");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testCount

        //# SQL:: SELECT NVL2((?),SDO_GEOMETRY((?).Get_WKB(), (?)),NULL), "ID", SDO_GEOMETRY((TO_BLOB(HEXTORAW('000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'))), (4326)) FROM "dbo"."TEST1" 

        //# Variables:: []

        //# Parametros:: ["Geometry", "Geometry", "ID"]

        assertEquals(

                "SELECT NVL2((?),SDO_GEOMETRY((?), (4326)),NULL), ? FROM \"dbo\".\"TEST1\" WHERE ( (( (NVL2((?),SDO_GEOMETRY((?), (4326)),NULL)) IS NOT NULL )) AND ((SDO_RELATE(NVL2((?),SDO_GEOMETRY((?), (4326)),NULL), SDO_GEOMETRY((TO_BLOB(HEXTORAW('000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'))), (4326)), 'mask=ANYINTERACT') = 'TRUE')) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"Geometry\", \"Geometry\", \"ID\", \"Geometry\", \"Geometry\", \"Geometry\", \"Geometry\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testWhereTRUE() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.update().table().database("master").schema("dbo").name("test1");

        sqlbuilder.update().where().set(expbuilder.constant(true));

        sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name"));

        System.out.println("# Test:: testTRUE");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        assertEquals(

                "UPDATE \"dbo\".\"TEST1\" SET \"name\" = ? WHERE (1=1)",

                sqlbuilder.toString()

        );

        assertEquals(

                "[name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"name\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

}

package org.gvsig.oracle.dal;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.net.URL;

import java.util.ArrayList;

import java.util.List;

import java.util.Properties;

import org.apache.commons.io.FileUtils;

import org.apache.commons.io.FilenameUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataStore;

import org.gvsig.fmap.dal.feature.Feature;

import org.gvsig.fmap.dal.feature.FeatureStore;

import org.gvsig.fmap.dal.feature.impl.DefaultFeature;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper;

import org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer;

import org.gvsig.fmap.dal.store.jdbc2.spi.FakeConnectionProvider;

import org.gvsig.tools.resourcesstorage.ResourcesStorage;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class TestUtils  {

    public static final Logger LOGGER = LoggerFactory.getLogger(TestUtils.class);

    public static final String PROVIDER_NAME = OracleLibrary.NAME;

    public static final String TEST_USER = "P1";

    public static final String TEST_SCHEMA = "";

    public static final String TEST_DB_NAME = "orcl";

    public static OracleExplorerParameters buildDBConnection() throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        OracleExplorerParameters conn = (OracleExplorerParameters) 

                dataManager.createServerExplorerParameters(OracleLibrary.NAME);

        Properties p = new Properties();

        File userDirectory = FileUtils.getUserDirectory();

        String properties = FilenameUtils.concat(

                        userDirectory.getCanonicalPath(), 

                        ".gvSIG_Oracle_testDBConnection.properties");

        File fileProperties = new File(properties);

        if(!fileProperties.exists()) {

            p.setProperty("host", "127.0.0.1");

            p.setProperty("port", "1521");

            p.setProperty("dbuser", TEST_USER);

            p.setProperty("schema", TEST_SCHEMA);

            p.setProperty("password", "PASSWD");

            p.setProperty("dbname", TEST_DB_NAME);

            p.setProperty("mode", "service");

            p.store(new FileOutputStream(properties), "Oracle test DB connection");

        } else {

            FileInputStream fis = new FileInputStream(properties);

            p.load(fis);

        }

        conn.setDynValue("host", p.getProperty("host", null));

        conn.setDynValue("port", p.getProperty("port", null));

        conn.setDynValue("dbuser", p.getProperty("dbuser", null));

        conn.setDynValue("schema", p.getProperty("schema", null));

        conn.setDynValue("password", p.getProperty("password", null));

        conn.setDynValue("dbname", p.getProperty("dbname", null));

        conn.setDynValue("mode", p.getProperty("mode", null));

        return conn;

    }

    public static JDBCServerExplorer openServerExplorer() throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        OracleConnectionParameters conn = buildDBConnection();

        JDBCServerExplorer explorer = (JDBCServerExplorer) dataManager.openServerExplorer(

                PROVIDER_NAME, conn

        );

        return explorer;

    }

    public static File getTargetFolder() throws Exception {

        URL url = TestUtils.class.getResource("/");

        File x = new File(url.toURI());

        File target = x.getParentFile();

        return target;

    }

    public static File getResource(String name) throws Exception {

        File x = new File(getTargetFolder(), name);

        return x;

    }

    public static File getResourceAsFile(String pathname) throws Exception {

        URL url = TestUtils.class.getResource(pathname);

        File x = new File(url.toURI());

        return x;

    }

    public static void removeDALResource(String tableName) throws Exception {

//        OracleConnectionParameters connection = buildDBConnection(dbname);

//        DatabaseWorkspaceManager workspace = DALLocator.getDataManager().createDatabaseWorkspaceManager(

//                (DataServerExplorerParameters) connection

//        );

        JDBCServerExplorer explorer = openServerExplorer();

        JDBCStoreParameters params = explorer.get(tableName);

        ResourcesStorage resources = explorer.getResourcesStorage(params);

        resources.remove("dal");

    }

    public static FeatureStore openSourceStore1() throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        File f = getResourceAsFile("/org/gvsig/oracle/dal/testCreateSource1.csv");

        FeatureStore store = (FeatureStore) dataManager.openStore(

                DataStore.CSV_PROVIDER_NAME, 

                "file=",f,

                "automaticTypesDetection=", false,

                "locale=","en"

        );

        return store;

    }

    public static FeatureStore openSourceStoreShort() throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        File f = getResourceAsFile("/org/gvsig/oracle/dal/testCreateSource_short.csv");

        FeatureStore store = (FeatureStore) dataManager.openStore(

                DataStore.CSV_PROVIDER_NAME, 

                "file=",f,

                "automaticTypesDetection=", false,

                "locale=","en"

        );

        return store;