root / branches / v2_0_0_prep / libraries / org.gvsig.fmap.dal.db.h2spatial / src / main / java / org / gvsig / fmap / dal / db / h2spatial / impl / H2SpatialServerExplorer.java @ 32679
History | View | Annotate | Download (15.3 KB)
1 |
package org.gvsig.fmap.dal.db.h2spatial.impl; |
---|---|
2 |
|
3 |
import java.sql.Connection; |
4 |
import java.sql.ResultSet; |
5 |
import java.sql.SQLException; |
6 |
import java.sql.Statement; |
7 |
import java.util.ArrayList; |
8 |
import java.util.HashSet; |
9 |
import java.util.Iterator; |
10 |
import java.util.List; |
11 |
import java.util.Set; |
12 |
|
13 |
import net.sourceforge.hatbox.jts.Proc; |
14 |
|
15 |
import org.gvsig.fmap.dal.DataStoreParameters; |
16 |
import org.gvsig.fmap.dal.NewDataStoreParameters; |
17 |
import org.gvsig.fmap.dal.exception.DataException; |
18 |
import org.gvsig.fmap.dal.exception.InitializeException; |
19 |
import org.gvsig.fmap.dal.exception.ProviderNotRegisteredException; |
20 |
import org.gvsig.fmap.dal.exception.RemoveException; |
21 |
import org.gvsig.fmap.dal.exception.ValidateDataParametersException; |
22 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
23 |
import org.gvsig.fmap.dal.feature.FeatureType; |
24 |
import org.gvsig.fmap.dal.feature.NewFeatureStoreParameters; |
25 |
import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices; |
26 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelper; |
27 |
import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer; |
28 |
import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters; |
29 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
30 |
import org.gvsig.fmap.dal.store.jdbc.TransactionalAction; |
31 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
32 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
33 |
import org.slf4j.Logger; |
34 |
import org.slf4j.LoggerFactory; |
35 |
|
36 |
public class H2SpatialServerExplorer extends JDBCServerExplorer { |
37 |
|
38 |
final static private Logger logger = LoggerFactory |
39 |
.getLogger(H2SpatialServerExplorer.class); |
40 |
|
41 |
public static final String NAME = "H2SpatialExplorer"; |
42 |
|
43 |
private static final String[] h2Alias = new String[] { |
44 |
"HATBOX_BUILD_INDEX",
|
45 |
"HATBOX_DE_SPATIALIZE",
|
46 |
"HATBOX_INTERSECTS_WKB",
|
47 |
"HATBOX_INTERSECTS_WKT",
|
48 |
"HATBOX_MBR_INTERSECTS_ENV",
|
49 |
"HATBOX_QUERY_WITH_PREDICATE_WKB",
|
50 |
"HATBOX_QUERY_WITH_PREDICATE_WKT",
|
51 |
"HATBOX_SPATIALIZE"
|
52 |
}; |
53 |
|
54 |
private static final String[] h2AliasMethod = new String[] { |
55 |
"net.sourceforge.hatbox.jts.Proc.buildIndex",
|
56 |
"net.sourceforge.hatbox.jts.Proc.deSpatialize",
|
57 |
"net.sourceforge.hatbox.jts.Proc.queryIntersectsWkb",
|
58 |
"net.sourceforge.hatbox.jts.Proc.queryIntersectsWkt",
|
59 |
"net.sourceforge.hatbox.jts.Proc.mbrIntersectsEnv",
|
60 |
"net.sourceforge.hatbox.jts.Proc.queryWithPredicateWkb",
|
61 |
"net.sourceforge.hatbox.jts.Proc.queryWithPredicateWkt",
|
62 |
"net.sourceforge.hatbox.jts.Proc.spatialize"
|
63 |
}; |
64 |
|
65 |
public H2SpatialServerExplorer(JDBCServerExplorerParameters parameters,
|
66 |
DataServerExplorerProviderServices services) |
67 |
throws InitializeException {
|
68 |
super(parameters, services);
|
69 |
} |
70 |
|
71 |
private H2SpatialServerExplorerParameters getH2Spatialparameters() {
|
72 |
return (H2SpatialServerExplorerParameters) super.getParameters(); |
73 |
} |
74 |
|
75 |
|
76 |
protected JDBCHelper createHelper() throws InitializeException { |
77 |
return new H2SpatialHelper(this, getH2Spatialparameters()); |
78 |
} |
79 |
|
80 |
|
81 |
protected String getStoreName() { |
82 |
return H2SpatialStoreProvider.NAME;
|
83 |
} |
84 |
|
85 |
public String getName() { |
86 |
return NAME;
|
87 |
} |
88 |
|
89 |
protected JDBCStoreParameters createStoreParams()
|
90 |
throws InitializeException, ProviderNotRegisteredException {
|
91 |
H2SpatialStoreParameters orgParams = (H2SpatialStoreParameters) super
|
92 |
.createStoreParams(); |
93 |
|
94 |
orgParams.setUseSSL(getH2Spatialparameters().getUseSSL()); |
95 |
|
96 |
return orgParams;
|
97 |
} |
98 |
|
99 |
|
100 |
// ****************************
|
101 |
|
102 |
public boolean canAdd() { |
103 |
return true; |
104 |
} |
105 |
|
106 |
protected void checkIsMine(DataStoreParameters dsp) { |
107 |
if (!(dsp instanceof H2SpatialStoreParameters)) { |
108 |
// FIXME Excpetion ???
|
109 |
throw new IllegalArgumentException( |
110 |
"not instance of H2SpatialStoreParameters");
|
111 |
} |
112 |
super.checkIsMine(dsp);
|
113 |
|
114 |
H2SpatialServerExplorerParameters myParameters = getH2Spatialparameters(); |
115 |
H2SpatialStoreParameters myp = (H2SpatialStoreParameters) dsp; |
116 |
if (myp.getUseSSL().booleanValue() != myParameters.getUseSSL()) {
|
117 |
throw new IllegalArgumentException("worng explorer: useSSL (mine:" |
118 |
+ myParameters.getUseSSL() + " other:" + myp.getUseSSL()
|
119 |
+ ")");
|
120 |
} |
121 |
} |
122 |
|
123 |
public void remove(DataStoreParameters dsp) throws RemoveException { |
124 |
final H2SpatialStoreParameters slParams =(H2SpatialStoreParameters) dsp;
|
125 |
|
126 |
TransactionalAction action = new TransactionalAction() {
|
127 |
public boolean continueTransactionAllowed() { |
128 |
return false; |
129 |
} |
130 |
public Object action(Connection conn) throws DataException { |
131 |
|
132 |
|
133 |
Statement st;
|
134 |
try{
|
135 |
st = conn.createStatement(); |
136 |
} catch (SQLException e) { |
137 |
throw new JDBCSQLException(e); |
138 |
} |
139 |
|
140 |
String sqlDrop = "Drop table " |
141 |
+ slParams.tableID(); |
142 |
|
143 |
|
144 |
StringBuilder strb = new StringBuilder(); |
145 |
strb.append("Delete from geometry_columns where f_table_name = ");
|
146 |
strb.append('\'');
|
147 |
strb.append(slParams.getTable()); |
148 |
strb.append('\'');
|
149 |
|
150 |
StringBuilder strb2 = new StringBuilder(); |
151 |
strb2.append("Delete from geom_cols_ref_sys where f_table_name = ");
|
152 |
strb2.append('\'');
|
153 |
strb2.append(slParams.getTable()); |
154 |
strb2.append('\'');
|
155 |
|
156 |
StringBuilder strb3 = new StringBuilder(); |
157 |
strb3.append("DROP TABLE IF EXISTS");
|
158 |
strb3.append('\'');
|
159 |
strb3.append("idx_");
|
160 |
strb3.append(slParams.getTable()); |
161 |
strb3.append("_the_geom");
|
162 |
strb3.append('\'');
|
163 |
|
164 |
StringBuilder strb4 = new StringBuilder(); |
165 |
strb4.append("DROP TABLE IF EXISTS");
|
166 |
strb4.append('\'');
|
167 |
strb4.append("idx_");
|
168 |
strb4.append(slParams.getTable()); |
169 |
strb4.append("_the_geom_node");
|
170 |
strb4.append('\'');
|
171 |
|
172 |
StringBuilder strb5 = new StringBuilder(); |
173 |
strb5.append("DROP TABLE IF EXISTS");
|
174 |
strb5.append('\'');
|
175 |
strb5.append("idx_");
|
176 |
strb5.append(slParams.getTable()); |
177 |
strb5.append("_the_geom_parent");
|
178 |
strb5.append('\'');
|
179 |
|
180 |
StringBuilder strb6 = new StringBuilder(); |
181 |
strb6.append("DROP TABLE IF EXISTS");
|
182 |
strb6.append('\'');
|
183 |
strb6.append("idx_");
|
184 |
strb6.append(slParams.getTable()); |
185 |
strb6.append("_the_geom_rowid");
|
186 |
strb6.append('\'');
|
187 |
|
188 |
StringBuilder strb7 = new StringBuilder(); |
189 |
strb6.append("DROP TABLE IF EXISTS");
|
190 |
strb6.append('\'');
|
191 |
strb6.append("cache_");
|
192 |
strb6.append(slParams.getTable()); |
193 |
strb6.append("_the_geom");
|
194 |
strb6.append('\'');
|
195 |
|
196 |
String sqlDeleteFromGeometry_column = strb.toString();
|
197 |
String sqlDeleteFromGeom_cols_ref = strb2.toString();
|
198 |
String sqlDeleteIdx = strb3.toString();
|
199 |
String sqlDeleteIdx_node = strb4.toString();
|
200 |
String sqlDeleteIdx_parent = strb5.toString();
|
201 |
String sqlDeleteIdx_rowid = strb6.toString();
|
202 |
String sqlDeleteCache = strb7.toString();
|
203 |
|
204 |
try{
|
205 |
try{
|
206 |
st.execute(sqlDrop); |
207 |
} catch (SQLException e) { |
208 |
throw new JDBCExecuteSQLException(sqlDrop, e); |
209 |
} |
210 |
|
211 |
try {
|
212 |
st.execute(sqlDeleteFromGeometry_column); |
213 |
} catch (SQLException e) { |
214 |
throw new JDBCExecuteSQLException( |
215 |
sqlDeleteFromGeometry_column, e); |
216 |
} |
217 |
|
218 |
try {
|
219 |
st.execute(sqlDeleteFromGeom_cols_ref); |
220 |
} catch (SQLException e) { |
221 |
throw new JDBCExecuteSQLException( |
222 |
sqlDeleteFromGeom_cols_ref, e); |
223 |
} |
224 |
|
225 |
try {
|
226 |
st.execute(sqlDeleteIdx); |
227 |
} catch (SQLException e) { |
228 |
throw new JDBCExecuteSQLException( |
229 |
sqlDeleteIdx, e); |
230 |
} |
231 |
|
232 |
try {
|
233 |
st.execute(sqlDeleteIdx_node); |
234 |
} catch (SQLException e) { |
235 |
throw new JDBCExecuteSQLException( |
236 |
sqlDeleteIdx_node, e); |
237 |
} |
238 |
|
239 |
try {
|
240 |
st.execute(sqlDeleteIdx_parent); |
241 |
} catch (SQLException e) { |
242 |
throw new JDBCExecuteSQLException( |
243 |
sqlDeleteIdx_parent, e); |
244 |
} |
245 |
|
246 |
try {
|
247 |
st.execute(sqlDeleteIdx_rowid); |
248 |
} catch (SQLException e) { |
249 |
throw new JDBCExecuteSQLException( |
250 |
sqlDeleteIdx_rowid, e); |
251 |
} |
252 |
|
253 |
try {
|
254 |
st.execute(sqlDeleteCache); |
255 |
} catch (SQLException e) { |
256 |
throw new JDBCExecuteSQLException( |
257 |
sqlDeleteCache, e); |
258 |
} |
259 |
|
260 |
} finally{
|
261 |
try{ st.close(); } catch (SQLException e) {}; |
262 |
} |
263 |
return null; |
264 |
} |
265 |
}; |
266 |
try {
|
267 |
this.helper.doConnectionAction(action);
|
268 |
} catch (Exception e) { |
269 |
throw new RemoveException(this.getName(), e); |
270 |
} |
271 |
} |
272 |
|
273 |
public NewDataStoreParameters getAddParameters() throws DataException { |
274 |
H2SpatialServerExplorerParameters parameters = getH2Spatialparameters(); |
275 |
H2SpatialNewStoreParameters params = new H2SpatialNewStoreParameters();
|
276 |
params.setHost(parameters.getHost()); |
277 |
params.setPort(parameters.getPort()); |
278 |
params.setDBName(parameters.getDBName()); |
279 |
params.setUser(parameters.getUser()); |
280 |
params.setPassword(parameters.getPassword()); |
281 |
params.setCatalog(parameters.getCatalog()); |
282 |
params.setSchema(parameters.getSchema()); |
283 |
params.setJDBCDriverClassName(parameters.getJDBCDriverClassName()); |
284 |
params.setUrl(parameters.getUrl()); |
285 |
params.setUseSSL(parameters.getUseSSL()); |
286 |
|
287 |
|
288 |
params.setDefaultFeatureType(this.getServerExplorerProviderServices()
|
289 |
.createNewFeatureType()); |
290 |
|
291 |
|
292 |
return params;
|
293 |
} |
294 |
|
295 |
|
296 |
|
297 |
// ***********************
|
298 |
// ***********************
|
299 |
|
300 |
protected List getSQLForList(int mode, boolean showInformationDBTables) { |
301 |
List list = new ArrayList(1); |
302 |
String sql="SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA<>'INFORMATION_SCHEMA' AND TABLE_NAME NOT LIKE '%_HATBOX' AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_HATBOX')"; |
303 |
list.add(sql); |
304 |
return list;
|
305 |
|
306 |
} |
307 |
public boolean add(NewDataStoreParameters ndsp, boolean overwrite) |
308 |
throws DataException {
|
309 |
|
310 |
//crea el fichero de la bbdd y la hace espacial
|
311 |
this.initializedb();
|
312 |
|
313 |
/**
|
314 |
* CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
|
315 |
* { column_name data_type [ DEFAULT default_expr ] [ column_constraint
|
316 |
* [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING |
|
317 |
* EXCLUDING } DEFAULTS ] } [, ... ] ) [ INHERITS ( parent_table [, ...
|
318 |
* ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS |
|
319 |
* DELETE ROWS | DROP } ]
|
320 |
*
|
321 |
* where column_constraint is:
|
322 |
*
|
323 |
* [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE | PRIMARY
|
324 |
* KEY | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [
|
325 |
* MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON
|
326 |
* UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY
|
327 |
* DEFERRED | INITIALLY IMMEDIATE ]
|
328 |
*
|
329 |
* and table_constraint is:
|
330 |
*
|
331 |
* [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) |
|
332 |
* PRIMARY KEY ( column_name [, ... ] ) | CHECK ( expression ) | FOREIGN
|
333 |
* KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
|
334 |
* ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE
|
335 |
* action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [
|
336 |
* INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
|
337 |
*/
|
338 |
|
339 |
if (!(ndsp instanceof NewFeatureStoreParameters)) { |
340 |
// FIXME exception
|
341 |
throw new IllegalArgumentException(); |
342 |
} |
343 |
checkIsMine(ndsp); |
344 |
|
345 |
NewFeatureStoreParameters nfdsp = (NewFeatureStoreParameters) ndsp; |
346 |
|
347 |
StringBuilder sql = new StringBuilder(); |
348 |
|
349 |
if (!nfdsp.isValid()) {
|
350 |
// TODO Exception
|
351 |
throw new InitializeException(this.getName(), new Exception( |
352 |
"Parameters not valid"));
|
353 |
} |
354 |
try {
|
355 |
nfdsp.validate(); |
356 |
} catch (ValidateDataParametersException e1) {
|
357 |
throw new InitializeException(this.getName(), e1); |
358 |
} |
359 |
|
360 |
FeatureType fType = nfdsp.getDefaultFeatureType(); |
361 |
|
362 |
|
363 |
|
364 |
sql.append("Create table " + ((JDBCStoreParameters) ndsp).tableID().toUpperCase()
|
365 |
+ "(");
|
366 |
Iterator attrs = fType.iterator();
|
367 |
String sqlAttr;
|
368 |
List sqlAttrs = new ArrayList(); |
369 |
|
370 |
while (attrs.hasNext()) {
|
371 |
sqlAttr = helper.getSqlFieldDescription((FeatureAttributeDescriptor) attrs |
372 |
.next()); |
373 |
if (sqlAttr != null) { |
374 |
sqlAttrs.add(sqlAttr.toUpperCase()); |
375 |
} |
376 |
} |
377 |
sqlAttrs.add("THE_GEOM"+ " "+ "BLOB"); |
378 |
|
379 |
helper.stringJoin(sqlAttrs, ", ", sql);
|
380 |
sql.append(")");
|
381 |
final String sqlDrop="DROP TABLE IF EXISTS " + ((JDBCStoreParameters) ndsp).tableID().toUpperCase(); |
382 |
final String sqlDropHatBox="DROP TABLE IF EXISTS " + ((JDBCStoreParameters) ndsp).tableID().toUpperCase()+"_HATBOX"; |
383 |
final String sqlCreate = sql.toString(); |
384 |
final List sqlAdditional = helper |
385 |
.getAdditionalSqlToCreate(ndsp, fType); |
386 |
final String catalog=((JDBCStoreParameters) ndsp).getCatalog(); |
387 |
final String schema=((JDBCStoreParameters) ndsp).getSchema(); |
388 |
final String table=((JDBCStoreParameters) ndsp).getTable().toUpperCase(); |
389 |
final String geomCol=fType.getDefaultGeometryAttributeName().toUpperCase(); |
390 |
final String geomType="GEOMETRY"; |
391 |
String epsgAux = null; |
392 |
String abrev= fType.getDefaultGeometryAttribute().getSRS().getAbrev();
|
393 |
try{
|
394 |
epsgAux =abrev.substring(abrev.indexOf(":")+1, abrev.length()); |
395 |
}catch (Exception e) { |
396 |
e.printStackTrace(); |
397 |
} |
398 |
final String epsg=epsgAux; |
399 |
TransactionalAction action = new TransactionalAction() {
|
400 |
|
401 |
public boolean continueTransactionAllowed() { |
402 |
// TODO Auto-generated method stub
|
403 |
return false; |
404 |
} |
405 |
|
406 |
public Object action(Connection conn) throws DataException { |
407 |
Statement st = null; |
408 |
try {
|
409 |
st = conn.createStatement(); |
410 |
} catch (SQLException e1) { |
411 |
throw new JDBCSQLException(e1); |
412 |
} |
413 |
String sql = null; |
414 |
|
415 |
try {
|
416 |
sql = sqlCreate; |
417 |
st.execute(sqlDrop); |
418 |
st.execute(sqlDropHatBox); |
419 |
st.execute(sql); |
420 |
if (sqlAdditional != null) { |
421 |
Iterator iter = sqlAdditional.iterator();
|
422 |
while (iter.hasNext()) {
|
423 |
sql = (String) iter.next();
|
424 |
st.execute(sql); |
425 |
} |
426 |
} |
427 |
} catch (SQLException e) { |
428 |
throw new JDBCExecuteSQLException(sql, e); |
429 |
} finally {
|
430 |
try {
|
431 |
st.close(); |
432 |
|
433 |
} catch (SQLException e) { |
434 |
logger.error("Exception clossing statement", e);
|
435 |
} |
436 |
} |
437 |
|
438 |
return Boolean.TRUE; |
439 |
} |
440 |
|
441 |
}; |
442 |
|
443 |
Boolean result = Boolean.FALSE; |
444 |
|
445 |
try {
|
446 |
result = (Boolean) helper.doConnectionAction(action);
|
447 |
Proc.spatialize(helper.getConnection(), schema, table, geomCol, geomType, epsg, "false", "49"); |
448 |
Proc.buildIndex(helper.getConnection(), schema, table, 100, null); |
449 |
} catch (Exception e) { |
450 |
// FIXME Exception
|
451 |
throw new RuntimeException(e); |
452 |
} |
453 |
|
454 |
return result.booleanValue();
|
455 |
} |
456 |
private void initializedb(){ |
457 |
Connection conn = null; |
458 |
try {
|
459 |
Class.forName("org.h2.Driver"); |
460 |
conn = getHelper().getConnection();//DriverManager.getConnection("jdbc:h2:"+System.getProperty("user.home")+File.separator+"gvSIG"+File.separator+"cache"+File.separator+"cache", null, null);
|
461 |
} catch (Exception e) { |
462 |
e.printStackTrace(); |
463 |
} |
464 |
//si esta no esta la bd espacializada se espacializa
|
465 |
if(!determineDbStatus(conn)){
|
466 |
spatializedb(conn); |
467 |
} |
468 |
} |
469 |
|
470 |
private void spatializedb(Connection conn) { |
471 |
Statement stmt = null; |
472 |
try {
|
473 |
stmt = conn.createStatement(); |
474 |
for (int i = 0; i < h2Alias.length; i++) { |
475 |
stmt.executeUpdate("create alias " + h2Alias[i] + " for \"" |
476 |
+ h2AliasMethod[i] + '"');
|
477 |
} |
478 |
stmt.close(); |
479 |
} catch (SQLException e) { |
480 |
e.printStackTrace(); |
481 |
} |
482 |
|
483 |
} |
484 |
|
485 |
private boolean determineDbStatus(Connection conn) { |
486 |
boolean spatialized = false; |
487 |
Statement stmt = null; |
488 |
ResultSet rs = null; |
489 |
try {
|
490 |
stmt = conn.createStatement(); |
491 |
rs = stmt.executeQuery("select ALIAS_NAME from INFORMATION_SCHEMA.FUNCTION_ALIASES "
|
492 |
+ "where ALIAS_SCHEMA = 'PUBLIC' and ALIAS_NAME like 'HATBOX%'");
|
493 |
Set<String> aliasSet = new HashSet<String>(); |
494 |
for (String alias : h2Alias) { |
495 |
aliasSet.add(alias); |
496 |
} |
497 |
while (rs.next()) {
|
498 |
aliasSet.remove(rs.getString(1));
|
499 |
} |
500 |
spatialized = (aliasSet.size() == 0);
|
501 |
} catch (SQLException e) { |
502 |
e.printStackTrace(); |
503 |
} |
504 |
return spatialized;
|
505 |
} |
506 |
} |