gvSIG projects pool

@begin createRepositoryIndex1

create index idx_data_1 on VCSGISREPO_DATA (COD_ENTITY ASC, DAT_REVNUMBER ASC)

@end createRepositoryIndex1

@begin createRepositoryIndex2

create index idx_data_2 on VCSGISREPO_DATA (DAT_FEATURERELATEDCODE ASC, DAT_REVNUMBER ASC, DAT_OPERATION ASC)

@end createRepositoryIndex2

@begin getDataOfEntityFromRevisionNumber

select * from "VCSGISREPO_DATA" where

        "VCSGISREPO_DATA"."COD_ENTITY" = '%2$s' AND

        "VCSGISREPO_DATA"."COD_DATA" in (

        select 

          MIN("VCSGISREPO_DATA1"."COD_DATA")

        from "VCSGISREPO_DATA" "VCSGISREPO_DATA1"

          left join "VCSGISREPO_DATA" "VCSGISREPO_DATA2" on

            "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE"

            and "VCSGISREPO_DATA2"."DAT_REVNUMBER" > %1$d 

        where 

          "VCSGISREPO_DATA1"."COD_ENTITY" = '%2$s'

          and "VCSGISREPO_DATA1"."DAT_REVNUMBER" > %1$d   

        group by "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", "VCSGISREPO_DATA1"."DAT_REVNUMBER", "VCSGISREPO_DATA1"."DAT_OPERATION"

        having not (

          ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and MAX("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and (MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0  or max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 ))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") <> 0  and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and (max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1) and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))  )

)

order by "VCSGISREPO_DATA"."DAT_OPERATION" desc

@end getDataOfEntityFromRevisionNumber

@begin getDataOfEntityToRevisionNumber

select * from "VCSGISREPO_DATA" where 

        "VCSGISREPO_DATA"."COD_ENTITY" = '%2$s' AND

        "VCSGISREPO_DATA"."COD_DATA" in (

        select 

          MIN("VCSGISREPO_DATA1"."COD_DATA")

        from "VCSGISREPO_DATA" "VCSGISREPO_DATA1"

          left join "VCSGISREPO_DATA" "VCSGISREPO_DATA2" on

            "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE"

            and "VCSGISREPO_DATA2"."DAT_REVNUMBER" <= %1$d 

        where 

          "VCSGISREPO_DATA1"."COD_ENTITY" = '%2$s'

          and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <= %1$d   

        group by "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", "VCSGISREPO_DATA1"."DAT_REVNUMBER", "VCSGISREPO_DATA1"."DAT_OPERATION"

        having not (

          ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and MAX("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and (MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0  or max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 ))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") <> 0  and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and (max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1) and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))  )

)

order by "VCSGISREPO_DATA"."DAT_OPERATION" desc

@end getDataOfEntityToRevisionNumber

@begin getDataOfEntityFromEfectiveDate

select * from "VCSGISREPO_DATA" where 

        "VCSGISREPO_DATA"."COD_ENTITY" = '%1$s' AND

        "VCSGISREPO_DATA"."COD_DATA" in (

        select 

          MIN("VCSGISREPO_DATA1"."COD_DATA")

        from "VCSGISREPO_DATA" "VCSGISREPO_DATA1"

          left join "VCSGISREPO_DATA" "VCSGISREPO_DATA2" on

            "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE"

            and "VCSGISREPO_DATA2"."DAT_EFECTIVEDATE" <= to_date('%2$s', 'YYYY-MM-DD HH24:MI:SS') 

        where 

          "VCSGISREPO_DATA1"."COD_ENTITY" = '%1$s'

          and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= to_date('%2$s', 'YYYY-MM-DD HH24:MI:SS')   

        group by "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE", "VCSGISREPO_DATA1"."DAT_REVNUMBER", "VCSGISREPO_DATA1"."DAT_OPERATION"

        having not (

          ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and (MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0  or max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 ))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and (max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1) and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= max("VCSGISREPO_DATA2"."DAT_EFECTIVEDATE") and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <> max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= max("VCSGISREPO_DATA2"."DAT_EFECTIVEDATE") and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <> max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))  )

)

@end getDataOfEntityFromEfectiveDate

@begin getDataOfEntityFromEfectiveDateAndRoi

select * from "VCSGISREPO_DATA" where 

        "VCSGISREPO_DATA"."COD_ENTITY" = '%1$s' AND

        "VCSGISREPO_DATA"."COD_DATA" in (

        select 

          MIN("VCSGISREPO_DATA1"."COD_DATA")

        from "VCSGISREPO_DATA" "VCSGISREPO_DATA1"

          left join "VCSGISREPO_DATA" "VCSGISREPO_DATA2" on

            "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE"

            and "VCSGISREPO_DATA2"."DAT_EFECTIVEDATE" <= to_date('%2$s', 'YYYY-MM-DD HH24:MI:SS') 

        where 

          "VCSGISREPO_DATA1"."COD_ENTITY" = '%1$s'

          and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= to_date('%2$s', 'YYYY-MM-DD HH24:MI:SS')   

          and ("VCSGISREPO_DATA1"."DAT_GEOM" is null or (ST_Intersects("VCSGISREPO_DATA1"."DAT_GEOM",SDO_GEOMETRY(HEXTORAW('%3$s'), %4$s) )) )

        group by "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE", "VCSGISREPO_DATA1"."DAT_REVNUMBER", "VCSGISREPO_DATA1"."DAT_OPERATION"

        having not (

          ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0 )

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and (MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = 0  or max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 ))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and (max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1) and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= max("VCSGISREPO_DATA2"."DAT_EFECTIVEDATE") and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <> max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))

          or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= max("VCSGISREPO_DATA2"."DAT_EFECTIVEDATE") and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <> max("VCSGISREPO_DATA2"."DAT_REVNUMBER"))  )

)

@end getDataOfEntityFromEfectiveDateAndRoi

@begin dateFormat

yyyy-MM-dd HH:mm:ss

@end dateFormat

@begin createWorkspaceIndex1

create index idx_wschanges_1 on VCSGIS_WSCHANGES (COD_ENTITY ASC, WSCH_OPERATION DESC, COD_WSCHANGE ASC)

@end createWorkspaceIndex1

@begin createWorkspaceIndex2

create index idx_wschanges_2 on VCSGIS_WSCHANGES (WSCH_SELECTED ASC, WSCH_OPERATION ASC)

@end createWorkspaceIndex2

@begin createWorkspaceIndex3

create index idx_remotechanges_3 on VCSGIS_REMOTECHANGES (COD_ENTITY ASC, RCH_SELECTED ASC)

@end createWorkspaceIndex3

@begin updateStateFromLocalChanges

-- status

update VCSGIS_REMOTECHANGES 

set RCH_STATUS = %1$d 

where exists (select 1 from VCSGIS_WSCHANGES where RCH_DATACODE = WSCH_FEATURECODE FETCH FIRST 1 ROWS ONLY;)

@end updateStateFromLocalChanges

@begin removeLocalChangesRelatedToSelectedRemoteChanges

-- entityCode

delete from VCSGIS_WSCHANGES 

where COD_ENTITY = '%1$s' and WSCH_FEATURECODE in (

    select RCH_DATACODE from VCSGIS_REMOTECHANGES where RCH_DATACODE = WSCH_FEATURECODE and CAST(RCH_SELECTED as INT)=1)

@end removeLocalChangesRelatedToSelectedRemoteChanges

@begin getChangesWidthUserData

-- EntityName

-- FeatureIdFieldName (VCSGISCODE)

-- RelatedFeatureCode

-- filter

SELECT * FROM VCSGIS_WSCHANGES left join "%1$s" on WSCH_FEATURECODE = "%2$s" WHERE "%2$s" <> '%3$s' AND (%4$s)

@end getChangesWidthUserData

@begin alterTableAddColumnVCSGISCODE

-- EntityName

ALTER TABLE "%1$s" ADD COLUMN "VCSGISCODE" VARCHAR(40)

@end alterTableAddColumnVCSGISCODE

@begin alterTableDropPrimaryKey

-- EntityName

ALTER TABLE "%1$s" DROP PRIMARY KEY

@end alterTableDropPrimaryKey

@begin alterTableAlterColumnVCSGISCODESetNotNull

-- EntityName

ALTER TABLE "%1$s" ALTER COLUMN "VCSGISCODE" SET NOT NULL

@end alterTableAlterColumnVCSGISCODESetNotNull

@begin alterTableAddPrimaryKeyVCSGISCODE

-- EntityName

ALTER TABLE "%1$s" ADD PRIMARY KEY ("VCSGISCODE")

@end alterTableAddPrimaryKeyVCSGISCODE

@begin createFunctionsPre1

create or replace PACKAGE VCSGISPKG AS

    TYPE VCSGISREPO_DATA_ROWTYPE IS RECORD (

          COD_DATA NVARCHAR2(60)

        , COD_REVISION NVARCHAR2(60)

        , COD_ENTITY NVARCHAR2(60)

        , DAT_OPERATION NUMBER(9, 0)

        , DAT_REVNUMBER NUMBER(18, 0)

        , DAT_EFECTIVEDATE TIMESTAMP(6)

        , DAT_FEATURERELATEDCODE NVARCHAR2(60)

        , DAT_GEOM SDO_GEOMETRY

        , DAT_DATA NCLOB

    );

    TYPE VCSGISREPO_DATA_TYPE IS TABLE OF VCSGISREPO_DATA_ROWTYPE;

    FUNCTION CHECKOUT(PCOD_ENTITY IN VARCHAR2, PDAT_REVNUMBER IN LONG) RETURN VCSGISREPO_DATA_TYPE PIPELINED;

END VCSGISPKG;

@end createFunctionsPre1

@begin createFunctionsPost1

create or replace PACKAGE BODY VCSGISPKG AS

    TYPE MAP_VARCHAR_NUMBER IS TABLE OF NUMBER  INDEX BY VARCHAR2(64);  

    FUNCTION CHECKOUT(PCOD_ENTITY IN VARCHAR2, PDAT_REVNUMBER IN LONG)

        RETURN VCSGISREPO_DATA_TYPE

        PIPELINED

    IS

        CURSOR C1 IS

            SELECT * 

            FROM VCSGISREPO_DATA

            WHERE COD_ENTITY = PCOD_ENTITY AND DAT_REVNUMBER <= PDAT_REVNUMBER

            ORDER BY DAT_REVNUMBER DESC

        ;

        DELETEDS MAP_VARCHAR_NUMBER;

        UPDATEDS_AND_INSERTEDS MAP_VARCHAR_NUMBER;

        VCOD_DATA VCSGISREPO_DATA.COD_DATA%TYPE;

        VDAT_FEATURERELATEDCODE VCSGISREPO_DATA.DAT_FEATURERELATEDCODE%TYPE;

        VDAT_OPERATION VCSGISREPO_DATA.DAT_OPERATION%TYPE;

    BEGIN

        FOR record IN C1

        LOOP

            -- VCOD_DATA := record.COD_DATA;

            VDAT_FEATURERELATEDCODE := record.DAT_FEATURERELATEDCODE;

            VDAT_OPERATION := record.DAT_OPERATION;

            IF DELETEDS.EXISTS(VDAT_FEATURERELATEDCODE) THEN

                CONTINUE;

            END IF;

            IF UPDATEDS_AND_INSERTEDS.EXISTS(VDAT_FEATURERELATEDCODE) THEN

                CONTINUE;

            END IF;  

            IF VDAT_OPERATION = 0 THEN -- DEL

                    DELETEDS(VDAT_FEATURERELATEDCODE) := 1;

                    CONTINUE;

            ELSIF VDAT_OPERATION = 1 THEN -- UPD

                    UPDATEDS_AND_INSERTEDS(VDAT_FEATURERELATEDCODE) := 1;

            ELSIF VDAT_OPERATION = 2 THEN -- INS

                    UPDATEDS_AND_INSERTEDS(VDAT_FEATURERELATEDCODE) := 1;

            END IF;

            PIPE ROW (record);

        END LOOP; 

    END;

END;

@end createFunctionsPost1

@begin getDataOfEntityToRevisionNumberUsingDatabaseFunction

SELECT * FROM TABLE (VCSGISPKG.CHECKOUT('%2$s', %1$s))

@end getDataOfEntityToRevisionNumberUsingDatabaseFunction

@begin defaultCheckoutMode

USEDBFUNCTION (posible values USEDBFUNCTION, USESELECT, USEAPPFILTER)

@end defaultCheckoutMode