gvsig-projects-pool / org.gvsig.vcsgis / trunk / org.gvsig.vcsgis / org.gvsig.vcsgis.lib / org.gvsig.vcsgis.lib.impl / src / main / resources / org / gvsig / vcsgis / lib / H2Spatial.sql @ 3446
History | View | Annotate | Download (8.51 KB)
| 1 |
@begin createRepositoryIndex1
|
|---|---|
| 2 |
create index if not exists idx_data_1 on VCSGISREPO_DATA (COD_ENTITY ASC, DAT_REVNUMBER ASC) |
| 3 |
@end createRepositoryIndex1
|
| 4 |
|
| 5 |
@begin createRepositoryIndex2
|
| 6 |
create index if not exists idx_data_2 on VCSGISREPO_DATA (DAT_FEATURERELATEDCODE ASC, DAT_REVNUMBER ASC, DAT_OPERATION ASC) |
| 7 |
@end createRepositoryIndex2
|
| 8 |
|
| 9 |
@begin getDataOfEntityFromRevisionNumber
|
| 10 |
select
|
| 11 |
"VCSGISREPO_DATA1"."COD_DATA", |
| 12 |
"VCSGISREPO_DATA1"."COD_REVISION", |
| 13 |
"VCSGISREPO_DATA1"."COD_ENTITY", |
| 14 |
case when "VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 then 2 else "VCSGISREPO_DATA1"."DAT_OPERATION" end as "DAT_OPERATION", |
| 15 |
"VCSGISREPO_DATA1"."DAT_REVNUMBER", |
| 16 |
"VCSGISREPO_DATA1"."DAT_EFECTIVEDATE", |
| 17 |
"VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", |
| 18 |
"VCSGISREPO_DATA1"."DAT_GEOM", |
| 19 |
"VCSGISREPO_DATA1"."DAT_DATA", |
| 20 |
(case when min("VCSGISREPO_DATA2"."DAT_OPERATION") = 0 then 1 else 0 end) as "DEL", |
| 21 |
(case when max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 then 1 else 0 end) as "UPD", |
| 22 |
(case when max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 then 1 else 0 end) as "INS", |
| 23 |
max("VCSGISREPO_DATA2"."DAT_REVNUMBER") as MAXREV |
| 24 |
from "VCSGISREPO_DATA" as "VCSGISREPO_DATA1" |
| 25 |
left join "VCSGISREPO_DATA" as "VCSGISREPO_DATA2" on |
| 26 |
"VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" |
| 27 |
and "VCSGISREPO_DATA2"."DAT_REVNUMBER" > %1$d |
| 28 |
where
|
| 29 |
"VCSGISREPO_DATA1"."COD_ENTITY" = '%2$s' |
| 30 |
and "VCSGISREPO_DATA1"."DAT_REVNUMBER" > %1$d |
| 31 |
group by "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", "VCSGISREPO_DATA1"."DAT_REVNUMBER", "VCSGISREPO_DATA1"."DAT_OPERATION", "VCSGISREPO_DATA1"."DAT_DATA" |
| 32 |
having not ( |
| 33 |
("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and ( "INS" = 1 )) |
| 34 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and "DEL" = 1) |
| 35 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and ("DEL" = 1 or "UPD" = 1)) |
| 36 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and "DEL" = 0 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < MAXREV) |
| 37 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and "UPD" = 1 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < MAXREV) |
| 38 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and "INS" = 1 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < MAXREV) ) |
| 39 |
order by "VCSGISREPO_DATA1"."DAT_OPERATION" desc |
| 40 |
@end getDataOfEntityFromRevisionNumber
|
| 41 |
|
| 42 |
@begin getDataOfEntityToRevisionNumber
|
| 43 |
select
|
| 44 |
"VCSGISREPO_DATA1"."COD_DATA", |
| 45 |
"VCSGISREPO_DATA1"."COD_REVISION", |
| 46 |
"VCSGISREPO_DATA1"."COD_ENTITY", |
| 47 |
case when "VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 then 2 else "VCSGISREPO_DATA1"."DAT_OPERATION" end as "DAT_OPERATION", |
| 48 |
"VCSGISREPO_DATA1"."DAT_REVNUMBER", |
| 49 |
"VCSGISREPO_DATA1"."DAT_EFECTIVEDATE", |
| 50 |
"VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", |
| 51 |
"VCSGISREPO_DATA1"."DAT_GEOM", |
| 52 |
"VCSGISREPO_DATA1"."DAT_DATA", |
| 53 |
(case when min("VCSGISREPO_DATA2"."DAT_OPERATION") = 0 then 1 else 0 end) as "DEL", |
| 54 |
(case when max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 then 1 else 0 end) as "UPD", |
| 55 |
(case when max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 then 1 else 0 end) as "INS", |
| 56 |
max("VCSGISREPO_DATA2"."DAT_REVNUMBER") as MAXREV |
| 57 |
from "VCSGISREPO_DATA" as "VCSGISREPO_DATA1" |
| 58 |
left join "VCSGISREPO_DATA" as "VCSGISREPO_DATA2" on |
| 59 |
"VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" |
| 60 |
and "VCSGISREPO_DATA2"."DAT_REVNUMBER" <= %1$d |
| 61 |
where
|
| 62 |
"VCSGISREPO_DATA1"."COD_ENTITY" = '%2$s' |
| 63 |
and "VCSGISREPO_DATA1"."DAT_REVNUMBER" <= %1$d |
| 64 |
group by "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", "VCSGISREPO_DATA1"."DAT_REVNUMBER", "VCSGISREPO_DATA1"."DAT_OPERATION", "VCSGISREPO_DATA1"."DAT_DATA" |
| 65 |
having not ( |
| 66 |
("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and ( "INS" = 1 )) |
| 67 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and "DEL" = 1) |
| 68 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and ("DEL" = 1 or "UPD" = 1)) |
| 69 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and "DEL" = 0 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < MAXREV) |
| 70 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and "UPD" = 1 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < MAXREV) |
| 71 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and "INS" = 1 and "VCSGISREPO_DATA1"."DAT_REVNUMBER" < MAXREV) ) |
| 72 |
order by "VCSGISREPO_DATA1"."DAT_OPERATION" desc |
| 73 |
@end getDataOfEntityToRevisionNumber
|
| 74 |
|
| 75 |
@begin getDataOfEntityFromEfectiveDate
|
| 76 |
-- TABLE_NAME,
|
| 77 |
-- efectiveDate,
|
| 78 |
select
|
| 79 |
"VCSGISREPO_DATA1"."COD_DATA", |
| 80 |
"VCSGISREPO_DATA1"."COD_REVISION", |
| 81 |
"VCSGISREPO_DATA1"."COD_ENTITY", |
| 82 |
case when "VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 then 2 else "VCSGISREPO_DATA1"."DAT_OPERATION" end as "DAT_OPERATION", |
| 83 |
"VCSGISREPO_DATA1"."DAT_REVNUMBER", |
| 84 |
"VCSGISREPO_DATA1"."DAT_EFECTIVEDATE", |
| 85 |
"VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", |
| 86 |
"VCSGISREPO_DATA1"."DAT_GEOM", |
| 87 |
"VCSGISREPO_DATA1"."DAT_DATA", |
| 88 |
(case when min("VCSGISREPO_DATA2"."DAT_OPERATION") = 0 then 1 else 0 end) as "DEL", |
| 89 |
(case when max("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 or min("VCSGISREPO_DATA2"."DAT_OPERATION") = 1 then 1 else 0 end) as "UPD", |
| 90 |
(case when max("VCSGISREPO_DATA2"."DAT_OPERATION") = 2 then 1 else 0 end) as "INS", |
| 91 |
max("VCSGISREPO_DATA2"."DAT_EFECTIVEDATE") as MAXEFFECTIVEDATE, |
| 92 |
max("VCSGISREPO_DATA2"."DAT_REVNUMBER") as MAXREV |
| 93 |
from "VCSGISREPO_DATA" as "VCSGISREPO_DATA1" |
| 94 |
left join "VCSGISREPO_DATA" as "VCSGISREPO_DATA2" on |
| 95 |
"VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE" = "VCSGISREPO_DATA2"."DAT_FEATURERELATEDCODE" |
| 96 |
and "VCSGISREPO_DATA2"."DAT_EFECTIVEDATE" <= '%2$s' |
| 97 |
and "VCSGISREPO_DATA2"."DAT_REVNUMBER" != "VCSGISREPO_DATA1"."DAT_REVNUMBER" |
| 98 |
where
|
| 99 |
"VCSGISREPO_DATA1"."COD_ENTITY" = '%1$s' |
| 100 |
and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= '%2$s' |
| 101 |
group by "VCSGISREPO_DATA1"."DAT_FEATURERELATEDCODE", "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE", "VCSGISREPO_DATA1"."DAT_REVNUMBER", "VCSGISREPO_DATA1"."DAT_OPERATION", "VCSGISREPO_DATA1"."DAT_DATA" |
| 102 |
having not ( |
| 103 |
("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and ( "INS" = 1 )) |
| 104 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and "DEL" = 1) |
| 105 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and ("DEL" = 1 or "UPD" = 1)) |
| 106 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 0 and "DEL" = 0 and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= MAXEFFECTIVEDATE and "VCSGISREPO_DATA1"."DAT_REVNUMBER" != MAXREV) |
| 107 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 1 and "UPD" = 1 and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= MAXEFFECTIVEDATE and "VCSGISREPO_DATA1"."DAT_REVNUMBER" != MAXREV) |
| 108 |
or ("VCSGISREPO_DATA1"."DAT_OPERATION" = 2 and "INS" = 1 and "VCSGISREPO_DATA1"."DAT_EFECTIVEDATE" <= MAXEFFECTIVEDATE and "VCSGISREPO_DATA1"."DAT_REVNUMBER" != MAXREV) ) |
| 109 |
order by "VCSGISREPO_DATA1"."DAT_OPERATION" desc |
| 110 |
|
| 111 |
@end getDataOfEntityFromEfectiveDate
|
| 112 |
|
| 113 |
@begin dateFormat
|
| 114 |
yyyy-MM-dd HH:mm:ss.S |
| 115 |
@end dateFormat
|
| 116 |
|
| 117 |
@begin createWorkspaceIndex1
|
| 118 |
create index if not exists idx_wschanges_1 on VCSGIS_WSCHANGES (COD_ENTITY ASC, WSCH_OPERATION DESC, COD_WSCHANGE ASC) |
| 119 |
@end createWorkspaceIndex1
|
| 120 |
|
| 121 |
@begin createWorkspaceIndex2
|
| 122 |
create index if not exists idx_wschanges_2 on VCSGIS_WSCHANGES (WSCH_SELECTED ASC, WSCH_OPERATION ASC) |
| 123 |
@end createWorkspaceIndex2
|
| 124 |
|
| 125 |
@begin createWorkspaceIndex3
|
| 126 |
create index if not exists idx_remotechanges_3 on VCSGIS_REMOTECHANGES (COD_ENTITY ASC, RCH_SELECTED ASC) |
| 127 |
@end createWorkspaceIndex3
|
| 128 |
|
| 129 |
@begin updateStateFromLocalChanges
|
| 130 |
-- status
|
| 131 |
update VCSGIS_REMOTECHANGES
|
| 132 |
set RCH_STATUS = %1$d |
| 133 |
where exists (select 1 from VCSGIS_WSCHANGES where RCH_DATACODE = WSCH_FEATURECODE LIMIT 1) |
| 134 |
@end updateStateFromLocalChanges
|
| 135 |
|
| 136 |
@begin removeLocalChangesRelatedToSelectedRemoteChanges
|
| 137 |
-- entityCode
|
| 138 |
delete from VCSGIS_WSCHANGES |
| 139 |
where COD_ENTITY = '%1$s' and WSCH_FEATURECODE in ( |
| 140 |
select RCH_DATACODE from VCSGIS_REMOTECHANGES where RCH_DATACODE = WSCH_FEATURECODE and CAST(RCH_SELECTED as INT)=1) |
| 141 |
@end removeLocalChangesRelatedToSelectedRemoteChanges
|
| 142 |
|
| 143 |
@begin getChangesWidthUserData
|
| 144 |
-- EntityName
|
| 145 |
-- FeatureIdFieldName (VCSGISCODE)
|
| 146 |
-- RelatedFeatureCode
|
| 147 |
-- filter
|
| 148 |
SELECT * FROM VCSGIS_WSCHANGES left join "%1$s" on WSCH_FEATURECODE = "%2$s" WHERE "%2$s" <> '%3$s' AND (%4$s) |
| 149 |
@end getChangesWidthUserData
|
| 150 |
|
| 151 |
|
| 152 |
|
| 153 |
@begin alterTableAddColumnVCSGISCODE
|
| 154 |
-- EntityName
|
| 155 |
ALTER TABLE "%1$s" ADD COLUMN "VCSGISCODE" VARCHAR(40) |
| 156 |
@end alterTableAddColumnVCSGISCODE
|
| 157 |
|
| 158 |
@begin alterTableDropPrimaryKey
|
| 159 |
-- EntityName
|
| 160 |
ALTER TABLE "%1$s" DROP PRIMARY KEY |
| 161 |
@end alterTableDropPrimaryKey
|
| 162 |
|
| 163 |
@begin alterTableAlterColumnVCSGISCODESetNotNull
|
| 164 |
-- EntityName
|
| 165 |
ALTER TABLE "%1$s" ALTER COLUMN "VCSGISCODE" SET NOT NULL |
| 166 |
@end alterTableAlterColumnVCSGISCODESetNotNull
|
| 167 |
|
| 168 |
@begin alterTableAddPrimaryKeyVCSGISCODE
|
| 169 |
-- EntityName
|
| 170 |
ALTER TABLE "%1$s" ADD PRIMARY KEY ("VCSGISCODE") |
| 171 |
@end alterTableAddPrimaryKeyVCSGISCODE
|