gvSIG projects pool

org.gvsig.vcsgis.lib.VCSGisLibraryImpl

@begin createRepositoryIndex1

create index if not exists "idx_data_1" on "VCSGISREPO_DATA" ("COD_ENTITY" ASC, "DAT_REVNUMBER" ASC)

@end createRepositoryIndex1

@begin createRepositoryIndex2

create index if not exists "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_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_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_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 dateFormat

yyyy-MM-dd HH:mm:ss

@end dateFormat

@begin createWorkspaceIndex1

create "index" if not exists "idx_wschanges_1" on "VCSGIS_WSCHANGES" ("COD_ENTITY" ASC, "WSCH_OPERATION" DESC, "COD_WSCHANGE" ASC)

@end createWorkspaceIndex1

@begin createWorkspaceIndex2

create index if not exists "idx_wschanges_2" on "VCSGIS_WSCHANGES" ("WSCH_SELECTED" ASC, "WSCH_OPERATION" ASC)

@end createWorkspaceIndex2

@begin createWorkspaceIndex3

create index if not exists "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" LIMIT 1)

@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 defaultCheckoutMode

USESELECT (posible values USEDBFUNCTION, USESELECT, USEAPPFILTER)

@end defaultCheckoutMode

@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

@param revision_number

@param entity_code

@param data_table_name

select * from "%data_table_name$s" where

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" "VCSGISREPO_DATA2" on

	    ("VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" and "VCSGISREPO_DATA1"."COD_ENTITY" = "VCSGISREPO_DATA2"."COD_ENTITY")

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

	where 

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

	  and "VCSGISREPO_DATA1"."DAT_REVNUMBER" > %revision_number$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

@param revision_number

@param entity_code

@param data_table_name

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" "VCSGISREPO_DATA2" on

	    ("VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" and "VCSGISREPO_DATA1"."COD_ENTITY" = "VCSGISREPO_DATA2"."COD_ENTITY")

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

	where 

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

	  and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <= %revision_number$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 getDataOfEntityToRevisionNumberAndRoi

@param revision_number

@param entity_code

@param roi

@param data_table_name

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s'

        and (SDO_RELATE("VCSGISREPO_DATA"."DAT_GEOM",%roi$s,'mask=ANYINTERACT') = 'TRUE')

        and "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" "VCSGISREPO_DATA2" on

	    ("VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" and "VCSGISREPO_DATA1"."COD_ENTITY" = "VCSGISREPO_DATA2"."COD_ENTITY")

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

	where 

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

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

          and (SDO_RELATE("VCSGISREPO_DATA1"."DAT_GEOM",%roi$s,'mask=ANYINTERACT') = 'TRUE') 

	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 getDataOfEntityToRevisionNumberAndRoi

@begin getDataOfEntityFromEfectiveDate

@param entity_code

@param efective_date

@param data_table_name

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" "VCSGISREPO_DATA2" on

	    ("VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" and "VCSGISREPO_DATA1"."COD_ENTITY" = "VCSGISREPO_DATA2"."COD_ENTITY")

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

	where 

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

	  and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= to_date('%efective_date$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

@param entity_code

@param efective_date

@param roi

@param data_table_name

@const DELETE=0

@const UPDATE=1

@const INSERT=2

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s'

        and (SDO_RELATE("VCSGISREPO_DATA"."DAT_GEOM",%roi$s,'mask=ANYINTERACT') = 'TRUE') 

        and "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" "VCSGISREPO_DATA2" on

	    ("VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" and "VCSGISREPO_DATA1"."COD_ENTITY" = "VCSGISREPO_DATA2"."COD_ENTITY")

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

	where 

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

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

          and (SDO_RELATE("VCSGISREPO_DATA1"."DAT_GEOM",%roi$s,'mask=ANYINTERACT') = 'TRUE')

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

	having not (

	  ("VCSGISREPO_DATA1"."DAT_OPERATION" = %DELETE$s )

	  or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = %UPDATE$s and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = %DELETE$s )

	  or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = %INSERT$s and (MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = %DELETE$s  or max("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s or min("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s ))

	  or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = %UPDATE$s and (max("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s or min("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s) 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" = %INSERT$s and max("VCSGISREPO_DATA2"."DAT_OPERATION") = %INSERT$s 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

@param status

update VCSGIS_REMOTECHANGES 

set RCH_STATUS = %status$d 

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

@end updateStateFromLocalChanges

@begin removeLocalChangesRelatedToSelectedRemoteChanges

@param entity_code

delete from VCSGIS_WSCHANGES 

where COD_ENTITY = '%entity_code$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

@param entity_name

@param FeatureIdFieldName

@param RelatedFeatureCode

@param filter

SELECT * FROM VCSGIS_WSCHANGES left join "%entity_name$s" on WSCH_FEATURECODE = "%FeatureIdFieldName$s" WHERE "%FeatureIdFieldName$s" <> '%RelatedFeatureCode$s' AND (%filter$s)

@end getChangesWidthUserData

@begin alterTableAddColumnVCSGISCODE

@param entity_name

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

@end alterTableAddColumnVCSGISCODE

@begin alterTableDropPrimaryKey

@param entity_name

ALTER TABLE "%entity_name$s" DROP PRIMARY KEY

@end alterTableDropPrimaryKey

@begin alterTableAlterColumnVCSGISCODESetNotNull

@param entity_name

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

@end alterTableAlterColumnVCSGISCODESetNotNull

@begin alterTableAddPrimaryKeyVCSGISCODE

@param entity_name

ALTER TABLE "%entity_name$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, PROI SDO_GEOMETRY DEFAULT NULL) RETURN VCSGISREPO_DATA_TYPE PIPELINED;

    function BlobToHex(pBlob in blob) return clob;

    function HexToBlob(pHexClob in clob) return blob;

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, PROI SDO_GEOMETRY DEFAULT NULL)

        RETURN VCSGISREPO_DATA_TYPE

        PIPELINED

    IS

        CURSOR CURSOR_DATA_WITHOUT_ROI IS

            SELECT * 

            FROM VCSGISREPO_DATA

            WHERE 

                COD_ENTITY = PCOD_ENTITY 

                AND DAT_REVNUMBER <= PDAT_REVNUMBER

            ORDER BY DAT_REVNUMBER DESC

        ;

        CURSOR CURSOR_DATA_WITH_ROI IS

            SELECT * 

            FROM VCSGISREPO_DATA

            WHERE 

                COD_ENTITY = PCOD_ENTITY 

                AND DAT_REVNUMBER <= PDAT_REVNUMBER

                AND (DAT_GEOM IS NULL OR SDO_RELATE(DAT_GEOM, PROI,'mask=ANYINTERACT') = 'TRUE')

            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

        IF PROI IS NULL THEN

            FOR record IN CURSOR_DATA_WITHOUT_ROI

            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; 

        ELSE

            FOR record IN CURSOR_DATA_WITH_ROI

            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 IF;

    END;

    function BlobToHex(pBlob in blob) return clob is

    -- https://paulzipblog.wordpress.com/2021/11/28/blob-to-hex-and-hex-to-blob/

    -- Converts a blob to a clob containing the hex representation of the blob

      BUFF_SIZE constant integer := 16383; -- Each raw byte converts to 2 hex digits, so buffer is half of 32766

      vBuffer varchar2(32766 byte);  -- Twice buffer size

      vOffset pls_integer := 1;

      vLength pls_integer := DBMS_LOB.GetLength(pBlob);

      vResult clob;

    begin

      if coalesce(vLength, 0) > 0 then

        DBMS_LOB.CreateTemporary(vResult, True, DBMS_LOB.Call);

        for i in 1..ceil(vLength / BUFF_SIZE)

        loop

          vBuffer := RawToHex(DBMS_LOB.Substr(pBlob, BUFF_SIZE, vOffset));

          DBMS_LOB.WriteAppend(vResult, length(vBuffer), vBuffer);

          vOffset := vOffset + BUFF_SIZE;

        end loop;

      end if;

      return vResult;

    end;

    function HexToBlob(pHexClob in clob) return blob is

    -- https://paulzipblog.wordpress.com/2021/11/28/blob-to-hex-and-hex-to-blob/

    -- Converts a clob containing hex representation of binary data into a blob

      BUFF_SIZE constant integer := 32764; -- Some Oracle versions have a bug which loses trailing bytes when using buffer sizes > 32764,

      vBuffer raw(16382);

      vOffset pls_integer := 1;

      vLength pls_integer := DBMS_LOB.GetLength(pHexClob);

      vResult blob;

    begin

      if coalesce(vLength, 0) > 0 then

        DBMS_LOB.CreateTemporary(vResult, True, DBMS_LOB.Call);

        for i in 1..ceil(vLength / BUFF_SIZE)

        loop

          vBuffer := HexToRaw(DBMS_LOB.Substr(pHexClob, BUFF_SIZE, vOffset));

          DBMS_LOB.WriteAppend(vResult, UTL_RAW.Length(vBuffer), vBuffer);

          vOffset := vOffset + BUFF_SIZE;

        end loop;

      end if;

      return vResult;

    end;  

END;

@end createFunctionsPost1

@begin getDataOfEntityToRevisionNumberUsingDatabaseFunction

@param revision_number

@param entity_code

@param data_table_name

SELECT * FROM TABLE (VCSGISPKG.CHECKOUT('%entity_code$s', %revision_number$s,NULL))

@end getDataOfEntityToRevisionNumberUsingDatabaseFunction

@begin getDataOfEntityToRevisionNumberAndRoiUsingDatabaseFunction

@param revision_number

@param entity_code

@param roi

@param data_table_name

SELECT * FROM TABLE (VCSGISPKG.CHECKOUT('%entity_code$s', %revision_number$s, %roi$s))

@end getDataOfEntityToRevisionNumberAndRoiUsingDatabaseFunction

@begin defaultCheckoutMode

USESELECT (posible values USEDBFUNCTION, USESELECT, USEAPPFILTER)

@end defaultCheckoutMode

@begin createRepositoryIndex1

create index if not exists idx_data_1 on VCSGISREPO_DATA (COD_ENTITY ASC, DAT_REVNUMBER ASC)

@end createRepositoryIndex1

@begin createRepositoryIndex2

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

@end createRepositoryIndex2

@begin getDataOfEntityFromRevisionNumber

@param revision_number

@param entity_code

@param data_table_name

select * from "%data_table_name$s" where

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" AS "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" AS "VCSGISREPO_DATA2" on

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

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

	where 

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

	  and "VCSGISREPO_DATA1"."DAT_REVNUMBER" > %revision_number$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

@param revision_number

@param entity_code

@param data_table_name

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" AS "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" AS "VCSGISREPO_DATA2" on

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

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

	where 

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

	  and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <= %revision_number$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 getDataOfEntityToRevisionNumberAndRoi

@param revision_number

@param entity_code

@param roi

@param data_table_name

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" AS "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" AS "VCSGISREPO_DATA2" on

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

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

	where 

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

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

          and ("VCSGISREPO_DATA1"."DAT_GEOM" is null or (ST_Intersects("VCSGISREPO_DATA1"."DAT_GEOM",%roi$s)) )

	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 getDataOfEntityToRevisionNumberAndRoi

@begin getDataOfEntityFromEfectiveDate

@param entity_code

@param efective_date

@param data_table_name

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" AS "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" AS "VCSGISREPO_DATA2" on

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

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

	where 

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

	  and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= to_date('%efective_date$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

@param entity_code

@param efective_date

@param roi

@param data_table_name

@const DELETE=0

@const UPDATE=1

@const INSERT=2

select * from "%data_table_name$s" where 

        "%data_table_name$s"."COD_ENTITY" = '%entity_code$s' AND

        "%data_table_name$s"."COD_DATA" in (

	select 

	  MIN("VCSGISREPO_DATA1"."COD_DATA")

	from "%data_table_name$s" AS "VCSGISREPO_DATA1"

	  left join "%data_table_name$s" AS "VCSGISREPO_DATA2" on

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

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

	where 

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

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

          and ("VCSGISREPO_DATA1"."DAT_GEOM" is null or (ST_Intersects("VCSGISREPO_DATA1"."DAT_GEOM",%roi$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" = %DELETE$s )

	  or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = %UPDATE$s and MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = %DELETE$s )

	  or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = %INSERT$s and (MIN("VCSGISREPO_DATA2"."DAT_OPERATION") = %DELETE$s  or max("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s or min("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s ))

	  or  ("VCSGISREPO_DATA1"."DAT_OPERATION" = %UPDATE$s and (max("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s or min("VCSGISREPO_DATA2"."DAT_OPERATION") = %UPDATE$s) 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" = %INSERT$s and max("VCSGISREPO_DATA2"."DAT_OPERATION") = %INSERT$s 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 HH24:mm:ss.S

@end dateFormat

@begin createWorkspaceIndex1

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

@end createWorkspaceIndex1

@begin createWorkspaceIndex2

create index if not exists idx_wschanges_2 on VCSGIS_WSCHANGES (WSCH_SELECTED ASC, WSCH_OPERATION ASC)

@end createWorkspaceIndex2

@begin createWorkspaceIndex3

create index if not exists idx_remotechanges_3 on VCSGIS_REMOTECHANGES (COD_ENTITY ASC, RCH_SELECTED ASC)

@end createWorkspaceIndex3

@begin updateStateFromLocalChanges

@param status

update VCSGIS_REMOTECHANGES 

set RCH_STATUS = %status$d 

where exists (select 1 from VCSGIS_WSCHANGES where RCH_DATACODE = WSCH_FEATURECODE LIMIT 1)

@end updateStateFromLocalChanges

@begin removeLocalChangesRelatedToSelectedRemoteChanges

@param entityCode

delete from VCSGIS_WSCHANGES 

where COD_ENTITY = '%entityCode$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

@param EntityName

@param FeatureIdFieldName

@param RelatedFeatureCode

@param filter

SELECT * FROM VCSGIS_WSCHANGES left join "%EntityName$s" on WSCH_FEATURECODE = "%FeatureIdFieldName$s" WHERE "%FeatureIdFieldName$s" <> '%RelatedFeatureCode$s' AND (%filter$s)

@end getChangesWidthUserData

@begin alterTableAddColumnVCSGISCODE

@param EntityName

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

@end alterTableAddColumnVCSGISCODE

@begin alterTableDropPrimaryKey

@param EntityName

ALTER TABLE "%EntityName$s" DROP PRIMARY KEY

@end alterTableDropPrimaryKey

@begin alterTableAlterColumnVCSGISCODESetNotNull

@param EntityName

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

@end alterTableAlterColumnVCSGISCODESetNotNull

@begin alterTableAddPrimaryKeyVCSGISCODE

@param EntityName

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

@end alterTableAddPrimaryKeyVCSGISCODE

@begin defaultCheckoutMode

USESELECT (posible values USEDBFUNCTION, USESELECT, USEAPPFILTER)

@end defaultCheckoutMode

Esta sql podria ser una sustituta de la actual para recuperar datos de una revision

en concreto:

SELECT * FROM VCSGISREPO_DATA D

WHERE COD_ENTITY = 'HENTITY_ENT_161101000000000000000'

AND "DAT_REVNUMBER" <= 1000000000

AND DAT_OPERATION <> 0

AND DAT_REVNUMBER < (

    SELECT MAX(D1.DAT_REVNUMBER) FROM VCSGISREPO_DATA D1 

        WHERE 

            COD_ENTITY =  'HENTITY_ENT_161101000000000000000'

            AND DAT_FEATURERELATEDCODE = D.DAT_FEATURERELATEDCODE 

            AND "DAT_REVNUMBER" <= 1000000000  

)

order by "D"."DAT_OPERATION" desc;

Esta sql comprobaria si hay alguna tabla que tiene actualizaciones o inserciones

despues de un borrado:

SELECT count(d.cod_entity), vcsgisrepo_entities.ent_name, min(vcsgisrepo_entities.ent_label)

-- SELECT d.cod_entity, vcsgisrepo_entities.ent_name, vcsgisrepo_entities.ent_label, D.DAT_FEATURERELATEDCODE

FROM VCSGISREPO_DATA D

  LEFT JOIN vcsgisrepo_entities ON 

    d.cod_entity = vcsgisrepo_entities.cod_entity

WHERE 

    "DAT_REVNUMBER" <= 1000000000

-- AND COD_ENTITY = 'HENTITY_ENT_161101000000000000000'

AND DAT_OPERATION = 0

AND DAT_REVNUMBER < (

    SELECT MAX(D1.DAT_REVNUMBER) FROM VCSGISREPO_DATA D1 

        WHERE 

                "DAT_REVNUMBER" <= 1000000000  

            AND COD_ENTITY =  D.COD_ENTITY

            AND DAT_FEATURERELATEDCODE = D.DAT_FEATURERELATEDCODE 

)

group by vcsgisrepo_entities.ent_name

order by vcsgisrepo_entities.ent_name asc;

/*

 * gvSIG. Desktop Geographic Information System.

 * 

 * Copyright (C) 2007-2020 gvSIG Association.

 * 

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 * 

 * This program 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 General Public License for more details.

 * 

 * You should have received a copy of the GNU General Public License 

 * along with this program. If not, see <https://www.gnu.org/licenses/>. 

 * 

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.vcsgis.lib;

import java.util.Iterator;

import org.gvsig.tools.dispose.DisposableIterable;

import org.gvsig.tools.dispose.DisposableIterator;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 *

 * @author gvSIG Team

 * @param <T>

 */

public class PipedIterator<T> implements DisposableIterable<T>, DisposableIterator<T> {

    private static final Logger LOGGER = LoggerFactory.getLogger(PipedIterator.class);

    private T currentElement;

    private boolean isClosed;

    private final long timeout;

    public PipedIterator(long timeout) {

        this.currentElement = null;

        this.isClosed = false;

        this.timeout = timeout;

    }

    public synchronized void put(T element) {

        if( this.isClosed ) {

            throw new IllegalStateException("The piped iterator is closed");

        }

        if (this.currentElement != null) {

            try {

                LOGGER.debug("===: put wait");

                for (int retry = 0; retry < 10; retry++) {

                    this.wait(timeout);

                    if( this.currentElement==null ) {

                        break;

                    }

                    LOGGER.warn("Timeout waiting to consume elements on piped iterator (timeout="+timeout+", retry="+retry+").");

                }

                if( this.currentElement!=null ) {

                    throw new RuntimeException("Timeout waiting to consume elements on piped iterator (timeout="+timeout+").");

                }

            } catch (InterruptedException ex) {

                throw new RuntimeException("Can't put element on piped iterator.", ex);

            }

        }

        LOGGER.debug("===: put notifyAll (closed "+this.isClosed+") "+ element.toString());

        this.currentElement = element;

        this.notifyAll();

    }

    @Override

    public synchronized boolean hasNext() {

        if (this.currentElement != null) {

            LOGGER.debug("===: hasNext true, has currentElement");

            return true;

        }

        if (this.isClosed) {

            LOGGER.debug("===: hasNext false, is closed");

            return false;

        }

        if (this.currentElement == null) {

            try {

                LOGGER.debug("===: hasNext wait");

                for (int retry = 0; retry < 10; retry++) {

                    this.wait(timeout);

                    if( this.currentElement!=null ) {

                        break;

                    }

                    LOGGER.warn("Timeout waiting to the next element on piped iterator (timeout="+timeout+", retry="+retry+").");

                }

                if( this.currentElement==null ) {

                    throw new RuntimeException("Timeout waiting to the next element on piped iterator (timeout="+timeout+").");

                }

            } catch (InterruptedException ex) {

                throw new RuntimeException("Can't get next element on piped iterator.", ex);

            }

        }

        LOGGER.debug("===: hasNext "+ (this.currentElement != null));

        return this.currentElement != null;

    }

    @Override

    public synchronized T next() {

        if (this.currentElement == null) {

            if( this.isClosed ) {

                throw new IllegalStateException("The piped iterator is closed");

            }

            try {

                LOGGER.debug("===: next wait");

                this.wait(timeout);

                if( this.currentElement==null ) {

                    throw new RuntimeException("Timeout waiting to the next element on piped iterator.");

                }

            } catch (InterruptedException ex) {

                throw new RuntimeException("Can't get next element on piped iterator.", ex);

            }

        }

        T x = this.currentElement;

        this.currentElement = null;

        LOGGER.debug("===: next notifyAll "+ x.toString());

        this.notifyAll();

        return x;

    }

    @Override

    public synchronized void dispose() {

    }

    @Override

    public synchronized Iterator<T> iterator() {

        return this;

    }

    public synchronized void close() {

        LOGGER.debug("===: close notifyAll");

        this.isClosed = true;

        this.notifyAll();

    }

}

/* gvSIG. Desktop Geographic Information System.

 *

 * Copyright (c) 2007-2015 gvSIG Association

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 2

 * of the License, or (at your option) any later version.

 *

 * This program 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 General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.vcsgis.lib;

import java.io.File;

import java.io.IOException;

import java.lang.ref.WeakReference;

import java.net.URL;

import java.nio.file.Files;

import java.nio.file.Path;

import java.util.ArrayList;

import java.util.Collections;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Objects;

import javax.json.JsonObject;

import javax.servlet.http.HttpServlet;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.tuple.Pair;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataStore;

import org.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.DatabaseWorkspaceManager;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.CONFIG_NAME_STORESREPOSITORYID;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_CONFIGURATION;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_CONFIGURATION_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_REPOSITORY;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_REPOSITORY_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES_NAME;

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

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

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

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

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

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

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

import org.gvsig.json.Json;

import org.gvsig.json.JsonManager;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dispose.DisposeUtils;

import org.gvsig.tools.task.SimpleTaskStatus;

import org.gvsig.tools.util.Factory;

import org.gvsig.tools.util.HasAFile;

import org.gvsig.tools.util.ListBuilder;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_OK;

import org.gvsig.vcsgis.lib.repository.VCSGisIdentityController;

import org.gvsig.vcsgis.lib.repository.VCSGisRepository;

import org.gvsig.vcsgis.lib.repository.localdb.VCSGisRepositoryLocaldbImpl;

import org.gvsig.vcsgis.lib.repository.localdb.tables.ConfigRepoTable;

import org.gvsig.vcsgis.lib.repository.localdb.tables.DataRepoTable;

import org.gvsig.vcsgis.lib.repository.localdb.tables.EntitiesRepoTable;

import org.gvsig.vcsgis.lib.repository.localdb.tables.HooksRepoTable;

import org.gvsig.vcsgis.lib.repository.localdb.tables.RevisionsRepoTable;

import org.gvsig.vcsgis.lib.repository.localdb.tables.TopologyplanRepoTable;

import org.gvsig.vcsgis.lib.repository.localdb.tables.UsersRepoTable;

import org.gvsig.vcsgis.lib.repository.remoteclient.VCSGisRepositoryClientImpl;

import org.gvsig.vcsgis.lib.repository.requests.VCSGisAuthenticateRequest;

import org.gvsig.vcsgis.lib.repository.requests.VCSGisListWCRequest;

import org.gvsig.vcsgis.lib.repository.requests.VCSGisPrepareWCRequest;

import org.gvsig.vcsgis.lib.server.VCSGisServerController;

import org.gvsig.vcsgis.lib.server.VCSGisServerControllerImpl;

import org.gvsig.vcsgis.lib.server.servlets.RepoInfoServlet;

import org.gvsig.vcsgis.lib.server.servlets.HookTestServlet;

import org.gvsig.vcsgis.lib.server.servlets.SimpleWFSServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisAuthenticateServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisCheckoutServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisCommitServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisDownloadWCServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisEntitiesServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisHistoryServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisListWCServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisPrepareWCServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisRowCreateServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisRowDeleteServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisRowIsOutofdateServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisRowUpdateServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisTopologyPlansServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisUpdateServlet;

import org.gvsig.vcsgis.lib.server.servlets.VCSGisUsersServlet;

import org.gvsig.vcsgis.lib.workspace.FeatureStoreObserver;

import org.gvsig.vcsgis.lib.workspace.StoreProperties;

import org.gvsig.vcsgis.lib.workspace.VCSGisWorkspace;

import org.gvsig.vcsgis.lib.workspace.VCSGisWorkspaceDescriptor;

import org.gvsig.vcsgis.lib.workspace.VCSGisWorkspaceDescriptorImpl;

import org.gvsig.vcsgis.lib.workspace.VCSGisWorkspaceImpl;

import static org.gvsig.vcsgis.lib.workspace.VCSGisWorkspaceImpl.CONFIG_REPOSITORY_NAME;

import org.gvsig.vcsgis.lib.workspace.tables.EntitiesTable;

import org.gvsig.vcsgis.lib.workspace.tables.EntitiesTable.EntityRow;

import org.gvsig.vcsgis.lib.workspace.tables.LocalRevisionsTable;

import org.gvsig.vcsgis.lib.workspace.tables.RemoteChangesTable;

import org.gvsig.vcsgis.lib.workspace.tables.TopologyplanTable;

import org.gvsig.vcsgis.lib.workspace.tables.UsersTable;

import org.gvsig.vcsgis.lib.workspace.tables.VarsTable;

import org.gvsig.vcsgis.lib.workspace.tables.WorkspaceChangesTable;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * @author gvSIG Team

 *

 */

@SuppressWarnings("UseSpecificCatch")

public class VCSGisManagerImpl implements VCSGisManager {

    private static final Logger LOGGER = LoggerFactory.getLogger(VCSGisManagerImpl.class);

    public static final List<String> INTERNAL_WORKSPACE_TABLES = ListBuilder.create(

            EntitiesTable.TABLE_NAME,

            LocalRevisionsTable.TABLE_NAME,

            RemoteChangesTable.TABLE_NAME,

            VarsTable.TABLE_NAME,

            WorkspaceChangesTable.TABLE_NAME,

            UsersTable.TABLE_NAME,

            TopologyplanTable.TABLE_NAME,

            TABLE_RESOURCES_NAME,

            TABLE_CONFIGURATION_NAME,

            TABLE_REPOSITORY_NAME

    );

    public static final List<String> INTERNAL_REPOSITORY_TABLES = ListBuilder.create(DataRepoTable.TABLE_NAME, 

            EntitiesRepoTable.TABLE_NAME,

            HooksRepoTable.TABLE_NAME,

            RevisionsRepoTable.TABLE_NAME,

            ConfigRepoTable.TABLE_NAME,

            TopologyplanRepoTable.TABLE_NAME,

            UsersRepoTable.TABLE_NAME,

            TABLE_RESOURCES_NAME,

            TABLE_CONFIGURATION_NAME,

            TABLE_REPOSITORY_NAME

    );

    private Map<String,VCSGisWorkspaceDescriptor> workspaces;

    private VCSGisCodeGenerator codeGenerator;

    private VCSGisUserIdentificationRequester userIdentificationRequester;

    private Factory<VCSGisAuthenticateRequest> authenticateRequestFactory;

    private Factory<VCSGisIdentityController> identityControllerFactory;

    public VCSGisManagerImpl() {

        this.workspaces = new HashMap<>();

        this.codeGenerator = new RandomCodeGenerator();

        this.userIdentificationRequester = null;

    }

    @Override

    public void clean() {

        for (VCSGisWorkspaceDescriptor workspace : workspaces.values()) {

            DisposeUtils.disposeQuietly(workspace);

        }

        this.workspaces = new HashMap<>();

        this.codeGenerator = new RandomCodeGenerator();

        this.userIdentificationRequester = null;

    }

    @Override

    public Map<String, VCSGisWorkspaceDescriptor> getWorkspaces() {

        return Collections.unmodifiableMap(this.workspaces);

    }

    @Override

    public void restoreWorkspaces(Map<String, VCSGisWorkspaceDescriptor> descriptors) {

        this.workspaces = new HashMap<>();

        if( descriptors != null ) {

            this.workspaces.putAll(descriptors);

        }

    }

    @Override

    public int initWorkspace(File wsfile, VCSGisRepository repo, String label, SimpleTaskStatus status) {

        int err = ERR_NO_ERROR;

        if (wsfile == null) {

            return ERR_DBFILE_IS_NULL;

        }

        if (status == null) {

            status = ToolsLocator.getTaskStatusManager().createDefaultSimpleTaskStatus("vcsgis init_workingcopy");

            status.setAutoremove(true);

            status.add();

        } else {

            status.push();

        }

        DataManager dataManager = DALLocator.getDataManager();

        JDBCServerExplorer explorer = null;

        try {

            wsfile = removeH2FileExtension(wsfile);

            if (StringUtils.isBlank(label)) {

                label = wsfile.getName();

            }

            err = ERR_CANT_OPEN_WORKSPACE_SERVEREXPLORER;

            JDBCServerExplorerParameters explorerParams = (JDBCServerExplorerParameters) dataManager.createServerExplorerParameters(DataStore.H2SPATIAL_PROVIDER_NAME);

            ((HasAFile) explorerParams).setFile(wsfile);

            explorer = (JDBCServerExplorer) dataManager.openServerExplorer(

                    explorerParams.getProviderName(),

                    explorerParams

            );

            return this.initWorkspace(explorer, repo, label, status);

        } catch (Exception ex) {

            status.abort();

            LOGGER.warn("Can't init workspace in '" + wsfile.getAbsolutePath() + "'.", ex);

            status.message(ex.getMessage());

            return err;

        } finally {

            status.pop();

            DisposeUtils.disposeQuietly(explorer);

        }

    }

    @Override

    public int initWorkspace(JDBCServerExplorer wsexplorer, VCSGisRepository repository, String label, SimpleTaskStatus status) {

        int err = ERR_NO_ERROR;

        if (wsexplorer == null) {

            return ERR_WSEXPLORER_IS_NULL;

        }

        if (StringUtils.isBlank(label)) {

            return ERR_LABEL_IS_NULL;

        }

        if (status == null) {

            status = ToolsLocator.getTaskStatusManager().createDefaultSimpleTaskStatus("vcsgis init_workingcopy");

            status.setAutoremove(true);

            status.add();

        } else {

            status.push();

        }

        LOGGER.info("Initializing workspace "+Objects.toString(wsexplorer.getParameters().toJson()).replace('\n', ' ').replaceAll("\"password\":\"[^\"]*\"", "\"password\":\"****\""));

        DataManager dataManager = DALLocator.getDataManager();

        VCSGisWorkspaceImpl workspace = null;

        try {

            status.setTitle("vcsgis init_workingcopy");

            DatabaseWorkspaceManager databaseWorkspaceManager = dataManager.createDatabaseWorkspaceManager(wsexplorer.getParameters());

            if(!databaseWorkspaceManager.existsTable(TABLE_CONFIGURATION)){

                databaseWorkspaceManager.createTable(TABLE_CONFIGURATION);

            }

            if(!databaseWorkspaceManager.existsTable(TABLE_RESOURCES)){

                databaseWorkspaceManager.createTable(TABLE_RESOURCES);

            }

            if(!databaseWorkspaceManager.existsTable(TABLE_REPOSITORY)){

                databaseWorkspaceManager.createTable(TABLE_REPOSITORY);

                databaseWorkspaceManager.set(CONFIG_NAME_STORESREPOSITORYID, "VCSGIS");

            }

            databaseWorkspaceManager.connect();

            FeatureType[] tables = new FeatureType[]{

                VarsTable.featureType(),

                EntitiesTable.featureType(),

                WorkspaceChangesTable.featureType(),

                RemoteChangesTable.featureType(),

                LocalRevisionsTable.featureType(),

                UsersTable.featureType(),

                TopologyplanTable.featureType()

            };

            status.setRangeOfValues(0, tables.length);

            int step = 1;

            for (FeatureType table : tables) {

                status.message(table.getLabel());

                status.setCurValue(step++);

                err = ERR_CANT_CREATE_TABLE + step;

                JDBCNewStoreParameters table_params = wsexplorer.getAddParameters();

                table_params.setDefaultFeatureType(table.getEditable());

                String tableName = table.getTags().getString("ID");

                table_params.setTable(tableName);

                wsexplorer.add(wsexplorer.getProviderName(), table_params, false);

                JDBCStoreParameters openParams = wsexplorer.get(tableName);

                databaseWorkspaceManager.writeStoresRepositoryEntry(tableName, openParams);

            }

            try {

                wsexplorer.execute(VCSGisUtils.getSqlTemplate(wsexplorer.getProviderName(), "createWorkspaceIndex1"));

                wsexplorer.execute(VCSGisUtils.getSqlTemplate(wsexplorer.getProviderName(), "createWorkspaceIndex2"));

                wsexplorer.execute(VCSGisUtils.getSqlTemplate(wsexplorer.getProviderName(), "createWorkspaceIndex3"));

            } catch (Exception ex) {

                LOGGER.warn("Can't create index in workspace", ex);

            }

            status.setTitle("vcsgis retrieving entities");

            workspace = new VCSGisWorkspaceImpl(wsexplorer, this.codeGenerator, repository, label);

            if (workspace.getUserIdentificationRequester() == null) {

                workspace.setUserIdentificationRequester(this.userIdentificationRequester);

            }

            workspace.initialize();

            LOGGER.info("Workspace initialized "+workspace.getCode());

            this.registerWorkspace(workspace);

            status.terminate();

            return ERR_NO_ERROR;

        } catch (UserCancelledException ex) {

            status.cancel();

            LOGGER.warn("Cancelled by user (" + getMessageLabel(wsexplorer) + ").", ex);

            status.message("Cancelled by user.");

            throw ex;

        } catch (Exception ex) {

            status.abort();

            LOGGER.warn("Can't init workspace in '" + getMessageLabel(wsexplorer) + "'.", ex);

            status.message(ex.getMessage());

            return err;

        } finally {

            status.pop();

            DisposeUtils.disposeQuietly(workspace);

        }

    }

    private String getMessageLabel(JDBCServerExplorer explorer) {

        return explorer.getParameters().getUrl();

    }

    @Override

    public VCSGisWorkspace openWorkspace(File wsfile) {

        if (wsfile == null) {

            return null;

        }

        JDBCServerExplorer explorer = null;

        try {

            DataManager dataManager = DALLocator.getDataManager();

            JDBCServerExplorerParameters explorerParams = (JDBCServerExplorerParameters) dataManager.createServerExplorerParameters(DataStore.H2SPATIAL_PROVIDER_NAME);

            ((HasAFile) explorerParams).setFile(wsfile);

            explorer = (JDBCServerExplorer) dataManager.openServerExplorer(

                    explorerParams.getProviderName(),

                    explorerParams

            );

            VCSGisWorkspace workspace = openWorkspace(explorer);

            return workspace;

        } catch (Exception ex) {

            return null;

        } finally {

            DisposeUtils.disposeQuietly(explorer);

        }

    }

    @Override

    public VCSGisWorkspace openWorkspace(JDBCServerExplorer wsexplorer) {

        if (wsexplorer == null) {

            return null;

        }

        JDBCServerExplorerParameters params = null;

        try {

            VCSGisWorkspace workspace;

            params = wsexplorer.getParameters();

            String url = params.getUrl();

            for (Iterator<VCSGisWorkspaceDescriptor> iterator = this.workspaces.values().iterator(); iterator.hasNext();) {

                VCSGisWorkspaceDescriptorImpl value = (VCSGisWorkspaceDescriptorImpl) iterator.next();

                if (DisposeUtils.getReferences(value)<=0) {

                    iterator.remove();

                    continue;

                }

                if (value.isWorkspaceInitialized() && StringUtils.equals(url, value.getExplorerParameters().getUrl())) {

                    workspace = value.getWorkspace();//FIXME: lo siguiente solo si es null

                    //workspace.getCode(); value.getCode();

                    if (workspace.getUserIdentificationRequester() == null) {

                        workspace.setUserIdentificationRequester(this.userIdentificationRequester);

                    }

                    dropExpiredCaches();

//                    LOGGER.debug("===: OPEN WORKSPACE "+ hexId(workspace)+ " from registry (code='"+workspace.getCode()+"', label='"+workspace.getLabel()+"')");        

                    return workspace;

                }

            }

            workspace = new VCSGisWorkspaceImpl(wsexplorer, this.codeGenerator);

            workspace.setUserIdentificationRequester(this.userIdentificationRequester);

            this.registerWorkspace(workspace);

            dropExpiredCaches();

            return workspace;

        } catch (Exception ex) {

            LOGGER.debug("Can't get workspace from "+Objects.toString(params)+".", ex);

            return null;

        }

    }

    public VCSGisWorkspace openWorkspace(File wsfile, String label) {

        if (wsfile == null) {

            return null;

        }

        JDBCServerExplorer explorer = null;

        try {

            DataManager dataManager = DALLocator.getDataManager();

            JDBCServerExplorerParameters explorerParams = (JDBCServerExplorerParameters) dataManager.createServerExplorerParameters(DataStore.H2SPATIAL_PROVIDER_NAME);

            ((HasAFile) explorerParams).setFile(wsfile);

            explorer = (JDBCServerExplorer) dataManager.openServerExplorer(

                    explorerParams.getProviderName(),

                    explorerParams

            );

            VCSGisWorkspace workspace = openWorkspace(explorer, label);

            return workspace;

        } catch (Exception ex) {

            return null;

        } finally {

            DisposeUtils.disposeQuietly(explorer);

        }

    }

    public VCSGisWorkspace openWorkspace(JDBCServerExplorer wsexplorer, String label) {

        // Abre la copia local cambiando su label y asignando un code nuevo.

        if (wsexplorer == null) {

            return null;

        }

        JDBCServerExplorerParameters params = null;

        try {

            VCSGisWorkspace workspace;

            params = wsexplorer.getParameters();

            workspace = new VCSGisWorkspaceImpl(wsexplorer, this.codeGenerator, label);

            workspace.setUserIdentificationRequester(this.userIdentificationRequester);

            this.registerWorkspace(workspace);

            dropExpiredCaches();

            return workspace;

        } catch (Exception ex) {

            LOGGER.debug("Can't get workspace from "+Objects.toString(params)+".", ex);

            return null;

        }

    }

    @Override

    public void registerWorkspace(VCSGisWorkspace workspace) {

        this.deregisterWorkspace(workspace.getCode());

        VCSGisWorkspaceDescriptorImpl descriptor = new VCSGisWorkspaceDescriptorImpl(workspace);

        this.workspaces.put(descriptor.getCode(), descriptor);

    }

    @Override

    public void deregisterWorkspace(File dbfile) {

        Path dbpath = dbfile.toPath();

        List<String>wscodes = new ArrayList<>();

        for (Iterator<VCSGisWorkspaceDescriptor> iterator = this.workspaces.values().iterator(); iterator.hasNext();) {

            VCSGisWorkspaceDescriptorImpl descriptor = (VCSGisWorkspaceDescriptorImpl) iterator.next();

            JDBCServerExplorerParameters explorerParams = descriptor.getExplorerParameters();

            if( explorerParams instanceof HasAFile ) {

                Path p = ((HasAFile) explorerParams).getFile().toPath();

                try {

                    if( Files.isSameFile(dbpath, p) ) {

                        wscodes.add(descriptor.getCode());

                    }

                } catch (IOException ex) {

                    LOGGER.debug("Can't deregister workspace '"+dbfile.getAbsolutePath()+"'.",ex);

                }

            }

        }        

        for (String wscode : wscodes) {

            deregisterWorkspace(wscode);

        }

    }

    @Override

    public void deregisterWorkspace(VCSGisWorkspace workspace) {

        this.deregisterWorkspace(workspace.getCode());

    }

    @Override

    public void deregisterWorkspace(String workspaceCode) {

        DisposeUtils.disposeQuietly(this.workspaces.get(workspaceCode));

        this.workspaces.remove(workspaceCode);

        dropExpiredCaches();

    }

    private void dropExpiredCaches() {

        for (Iterator<VCSGisWorkspaceDescriptor> iterator = this.workspaces.values().iterator(); iterator.hasNext();) {

            VCSGisWorkspaceDescriptorImpl value = (VCSGisWorkspaceDescriptorImpl) iterator.next();

            if(DisposeUtils.getReferences(value)<=0){

                iterator.remove();

            } else {

                value.dropExpiredCaches();

            }

        }

    }

    @Override

    public VCSGisWorkspace getWorkspace(FeatureStore store) {

        try {

            VCSGisWorkspaceImpl workspace;

            switch(StoreProperties.getVCSMode(store)) {

                case StoreProperties.MODE_NOT_VCS:

                    return null;

                case StoreProperties.MODE_VCS:

                    String workspace_code = StoreProperties.getWorkspaceCode(store);

                    VCSGisWorkspaceDescriptor workspaceDescriptor = this.workspaces.get(workspace_code);

                    if( workspaceDescriptor!=null ) {

                        workspace = (VCSGisWorkspaceImpl) workspaceDescriptor.getWorkspace();

                        if (workspace != null) {

                            if( workspace.isInStoreIgnoreChanges(store) ) {

                                return workspace;

                            }

                            String entity_name = StoreProperties.getEntityName(store);

                            EntityRow entity = (EntityRow) workspace.getWorkspaceEntityByName(entity_name);

                            if (entity == null) {

                                StoreProperties.setNotVCSMode(store);

                                DisposeUtils.dispose(workspace);

                                return null;

                            }

                            if( workspace.isCorrupt(entity,store,0) ) {

//                                return null; 

                            }

//                            LOGGER.debug("===: GET WORKSPACE "+ hexId(workspace)+ " from property (code='"+workspace.getCode()+"', label='"+workspace.getLabel()+"') for store '"+store.getName()+"'");        

                            return workspace;

                        }

                    }

                    // break;

                case StoreProperties.MODE_UNKNOWN:

                default:

                    DataStoreParameters params = store.getParameters();

                    if(!(params instanceof JDBCStoreParameters)) {

                        StoreProperties.setNotVCSMode(store);

                        return null;

                    }

                    String entity_name = ((JDBCStoreParameters) params).getTable();

                    if (INTERNAL_WORKSPACE_TABLES.contains(entity_name)) {

                        StoreProperties.setNotVCSMode(store);

                        return null;

                    }