Statistics
| Revision:

svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.compat.cdc / org.gvsig.fmap.dal / org.gvsig.fmap.dal.db / org.gvsig.fmap.dal.db.jdbc / src / main / java / org / gvsig / fmap / dal / store / jdbc / JDBCServerExplorer.java @ 42775

History | View | Annotate | Download (21 KB)

1
/**
2
 * gvSIG. Desktop Geographic Information System.
3
 *
4
 * Copyright (C) 2007-2013 gvSIG Association.
5
 *
6
 * This program is free software; you can redistribute it and/or modify it under
7
 * the terms of the GNU General Public License as published by the Free Software
8
 * Foundation; either version 3 of the License, or (at your option) any later
9
 * version.
10
 *
11
 * This program is distributed in the hope that it will be useful, but WITHOUT
12
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
13
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
14
 * details.
15
 *
16
 * You should have received a copy of the GNU General Public License along with
17
 * this program; if not, write to the Free Software Foundation, Inc., 51
18
 * Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19
 *
20
 * For any additional information, do not hesitate to contact us at info AT
21
 * gvsig.com, or visit our website www.gvsig.com.
22
 */
23
package org.gvsig.fmap.dal.store.jdbc;
24

    
25
import java.sql.Connection;
26
import java.sql.DatabaseMetaData;
27
import java.sql.ResultSet;
28
import java.sql.SQLException;
29
import java.sql.Statement;
30
import java.util.ArrayList;
31
import java.util.Iterator;
32
import java.util.List;
33
import org.apache.commons.lang3.StringUtils;
34

    
35
import org.slf4j.Logger;
36
import org.slf4j.LoggerFactory;
37

    
38
import org.gvsig.fmap.dal.DALLocator;
39
import org.gvsig.fmap.dal.DataManager;
40
import org.gvsig.fmap.dal.DataStore;
41
import org.gvsig.fmap.dal.DataStoreParameters;
42
import org.gvsig.fmap.dal.NewDataStoreParameters;
43
import org.gvsig.fmap.dal.exception.CloseException;
44
import org.gvsig.fmap.dal.exception.DataException;
45
import org.gvsig.fmap.dal.exception.InitializeException;
46
import org.gvsig.fmap.dal.exception.OpenException;
47
import org.gvsig.fmap.dal.exception.ProviderNotRegisteredException;
48
import org.gvsig.fmap.dal.exception.ReadException;
49
import org.gvsig.fmap.dal.exception.RemoveException;
50
import org.gvsig.fmap.dal.exception.ValidateDataParametersException;
51
import org.gvsig.fmap.dal.feature.EditableFeatureType;
52
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;
53
import org.gvsig.fmap.dal.feature.FeatureType;
54
import org.gvsig.fmap.dal.resource.spi.ResourceProvider;
55
import org.gvsig.fmap.dal.serverexplorer.db.spi.AbstractDBServerExplorer;
56
import org.gvsig.fmap.dal.spi.DataManagerProviderServices;
57
import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices;
58
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException;
59
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;
60
import org.gvsig.tools.exception.BaseException;
61

    
62
/**
63
 * @author jmvivo
64
 *
65
 */
66
public class JDBCServerExplorer extends AbstractDBServerExplorer
67
        implements JDBCHelperUser {
68

    
69
    private static final Logger LOG = LoggerFactory
70
            .getLogger(JDBCServerExplorer.class);
71

    
72
    private static final String METADATA_COLUMN_TABLE_CATALOG = "TABLE_CAT";
73
    private static final String METADATA_COLUMN_TABLE_SCHEMA = "TABLE_SCHEM";
74
    private static final String METADATA_COLUMN_TABLE_NAME = "TABLE_NAME";
75

    
76
    public static final String NAME = "JDBCServerExplorer";
77
    protected JDBCHelper helper;
78

    
79
    private Boolean canAdd;
80

    
81
    public JDBCServerExplorer(JDBCServerExplorerParameters parameters,
82
            DataServerExplorerProviderServices services)
83
            throws InitializeException {
84
        super(parameters, services);
85
        this.helper = createHelper();
86
    }
87

    
88
    protected JDBCServerExplorerParameters getJDBCParameters() {
89
        return (JDBCServerExplorerParameters) getParameters();
90
    }
91

    
92
    protected JDBCHelper createHelper() throws InitializeException {
93
        return new JDBCHelper(this, getJDBCParameters());
94
    }
95

    
96
    protected JDBCHelper getHelper() {
97
        return helper;
98
    }
99

    
100
    public List list() throws DataException {
101
        return this.list(MODE_ALL);
102
    }
103

    
104
    public List list(boolean showInformationDBTables) throws DataException {
105
        return this.list(MODE_ALL, showInformationDBTables);
106
    }
107

    
108
    public List list(int mode) throws DataException {
109
        JDBCServerExplorerParameters parameters = getJDBCParameters();
110
        if (parameters.getShowInformationDBTables() != null) {
111
            return this.list(mode, parameters.getShowInformationDBTables()
112
                    .booleanValue());
113
        }
114
        Boolean show = (Boolean) parameters
115
                .getDynClass()
116
                .getDynField(
117
                        JDBCServerExplorerParameters.SHOWINFORMATIONDBTABLES_PARAMTER_NAME)
118
                .getDefaultValue();
119
        if (show == null) {
120
            show = Boolean.FALSE;
121
        }
122

    
123
        return this.list(mode, show.booleanValue());
124
    }
125

    
126
    protected DataManagerProviderServices getManager() {
127
        return (DataManagerProviderServices) DALLocator.getDataManager();
128
    }
129

    
130
    public boolean hasGeometrySupport() {
131
        return false;
132
    }
133

    
134
    public boolean closeResourceRequested(ResourceProvider resource) {
135
        try {
136
            this.helper.close();
137
        } catch (CloseException e) {
138
            LOG.error("Exception in close Request", e);
139
        }
140
        return !this.helper.isOpen();
141
    }
142

    
143
    public void resourceChanged(ResourceProvider resource) {
144
        // Nothing to do
145
    }
146

    
147
    public void remove(DataStoreParameters dsp) throws RemoveException {
148
        final JDBCStoreParameters dsParams = (JDBCStoreParameters) dsp;
149

    
150
        TransactionalAction action = new TransactionalAction() {
151
            public boolean continueTransactionAllowed() {
152
                return false;
153
            }
154

    
155
            public Object action(Connection conn) throws DataException {
156
                Statement st;
157
                try {
158
                    st = conn.createStatement();
159
                } catch (SQLException e) {
160
                    throw new JDBCSQLException(e);
161
                }
162

    
163
                String sqlDrop = "Drop table "
164
                        + dsParams.tableID();
165

    
166
                try {
167
                    try {
168
                        JDBCHelper.execute(st, sqlDrop);
169
                    } catch (SQLException e) {
170
                        throw new JDBCExecuteSQLException(sqlDrop, e);
171
                    }
172

    
173
                } finally {
174
                    try {
175
                        st.close();
176
                    } catch (SQLException e) {
177
                    };
178
                }
179
                return null;
180
            }
181
        };
182
        try {
183
            this.helper.doConnectionAction(action);
184
        } catch (Exception e) {
185
            throw new RemoveException(this.getProviderName(), e);
186
        }
187
    }
188

    
189
    public DataStoreParameters getOpenParameters() throws DataException {
190
        JDBCServerExplorerParameters parameters = getJDBCParameters();
191
        JDBCStoreParameters params = new JDBCStoreParameters();
192
        params.setHost(parameters.getHost());
193
        params.setPort(parameters.getPort());
194
        params.setDBName(parameters.getDBName());
195
        params.setUser(parameters.getUser());
196
        params.setPassword(parameters.getPassword());
197
        params.setCatalog(parameters.getCatalog());
198
        params.setSchema(parameters.getSchema());
199
        params.setJDBCDriverClassName(parameters.getJDBCDriverClassName());
200
        params.setUrl(parameters.getUrl());
201
        return params;
202
    }
203

    
204
    public NewDataStoreParameters getAddParameters() throws DataException {
205
        JDBCServerExplorerParameters parameters = getJDBCParameters();
206
        JDBCNewStoreParameters params = new JDBCNewStoreParameters();
207
        params.setHost(parameters.getHost());
208
        params.setPort(parameters.getPort());
209
        params.setDBName(parameters.getDBName());
210
        params.setUser(parameters.getUser());
211
        params.setPassword(parameters.getPassword());
212
        params.setCatalog(parameters.getCatalog());
213
        params.setSchema(parameters.getSchema());
214
        params.setJDBCDriverClassName(parameters.getJDBCDriverClassName());
215
        params.setUrl(parameters.getUrl());
216

    
217
        params.setDefaultFeatureType(this.getServerExplorerProviderServices()
218
                .createNewFeatureType());
219

    
220
        return params;
221
    }
222

    
223
    public void closeDone() throws DataException {
224
        // Nothing to do
225
    }
226

    
227
    public void opendDone() throws DataException {
228
        // Nothin to do
229

    
230
    }
231

    
232
    public DataStore open(DataStoreParameters dsp) throws DataException {
233
        checkIsMine(dsp);
234
        DataManager dataMan = DALLocator.getDataManager();
235
        DataStore store;
236
        try {
237
            store = dataMan.openStore(dsp.getDataStoreName(), dsp);
238
        } catch (ValidateDataParametersException e) {
239
            throw new InitializeException(e);
240
        }
241

    
242
        return store;
243
    }
244

    
245
    protected void checkIsMine(DataStoreParameters dsp) {
246
        if (!(dsp instanceof JDBCConnectionParameters)) {
247
            // FIXME Exception ???
248
            throw new IllegalArgumentException(
249
                    "not instance of FilesystemStoreParameters");
250
        }
251

    
252
                // try {
253
        // dsp.validate();
254
        // } catch (ValidateDataParametersException e) {
255
        // throw new IllegalArgumentException("check parameters", e);
256
        // }
257
        JDBCServerExplorerParameters parameters = getJDBCParameters();
258

    
259
        JDBCConnectionParameters pgp = (JDBCConnectionParameters) dsp;
260
        if (!compare(pgp.getHost(), parameters.getHost())) {
261
            throw new IllegalArgumentException("wrong explorer: Host (mine: "
262
                    + parameters.getHost() + " other:" + pgp.getHost() + ")");
263
        }
264
        if (!compare(pgp.getPort(), parameters.getPort())) {
265
            throw new IllegalArgumentException("wrong explorer: Port (mine: "
266
                    + parameters.getPort() + " other:" + pgp.getPort() + ")");
267
        }
268
        if (!compare(pgp.getDBName(), parameters.getDBName())) {
269
            throw new IllegalArgumentException("wrong explorer: DBName (mine: "
270
                    + parameters.getDBName() + " other:" + pgp.getDBName()
271
                    + ")");
272
        }
273
        if (parameters.getCatalog() != null && !parameters.getCatalog().trim().equals("")) {
274
            // implicit catalog
275
            if (!compare(pgp.getCatalog(), parameters.getCatalog())) {
276
                throw new IllegalArgumentException(
277
                        "wrong explorer: Catalog (mine: "
278
                        + parameters.getCatalog() + " other:"
279
                        + pgp.getCatalog() + ")");
280
            }
281
        }
282
        if (parameters.getSchema() != null && !parameters.getSchema().trim().equals("")) {
283
            // implicit schema
284
            if (!compare(pgp.getSchema(), parameters.getSchema())) {
285
                throw new IllegalArgumentException(
286
                        "wrong explorer: Schema (mine: "
287
                        + parameters.getSchema() + " other:"
288
                        + pgp.getSchema() + ")");
289
            }
290
        }
291
    }
292

    
293
    protected boolean compare(Object str1, Object str2) {
294
        if (str1 == str2) {
295
            return true;
296
        }
297
        if (str1 == null) {
298
            return false;
299
        }
300
        return str1.equals(str2);
301
    }
302

    
303
    protected JDBCStoreParameters createStoreParams()
304
            throws InitializeException, ProviderNotRegisteredException {
305
        DataManagerProviderServices manager = this.getManager();
306
        JDBCServerExplorerParameters parameters = getJDBCParameters();
307
        JDBCStoreParameters orgParams = (JDBCStoreParameters) manager
308
                .createStoreParameters(getStoreName());
309
        orgParams.setHost(parameters.getHost());
310
        orgParams.setPort(parameters.getPort());
311
        orgParams.setDBName(parameters.getDBName());
312
        orgParams.setUser(parameters.getUser());
313
        orgParams.setPassword(parameters.getPassword());
314
        orgParams.setCatalog(parameters.getCatalog());
315
        orgParams.setJDBCDriverClassName(parameters.getJDBCDriverClassName());
316
        orgParams.setSchema(parameters.getSchema());
317
        orgParams.setUrl(parameters.getUrl());
318
        return orgParams;
319
    }
320

    
321
    public List list(final int mode, final boolean showInformationDBTables)
322
            throws DataException {
323

    
324
        final JDBCStoreParameters orgParams = createStoreParams();
325

    
326
        ConnectionAction action = new ConnectionAction() {
327

    
328
            public Object action(Connection conn) throws DataException {
329

    
330
                String[] tableTypes = null;
331
                if (!showInformationDBTables) {
332
                    tableTypes = new String[]{"TABLE", "VIEW"};
333
                }
334

    
335
                ResultSet result = null;
336
                try {
337
                    DatabaseMetaData metadata = conn.getMetaData();
338
                    result = metadata.getTables(null, null, null,
339
                            tableTypes);
340
                    List<JDBCStoreParameters> paramList = new ArrayList<JDBCStoreParameters>();
341
                    while (result.next()) {
342
                        JDBCStoreParameters params = (JDBCStoreParameters) orgParams
343
                                .getCopy();
344
                        params.setCatalog(result
345
                                .getString(METADATA_COLUMN_TABLE_CATALOG));
346
                        params.setSchema(result
347
                                .getString(METADATA_COLUMN_TABLE_SCHEMA));
348
                        params.setTable(result
349
                                .getString(METADATA_COLUMN_TABLE_NAME));
350
                        paramList.add(params);
351
                    }
352

    
353
                    return paramList;
354
                } catch (SQLException e) {
355
                    throw new JDBCSQLException(e);
356
                } finally {
357
                    if (result != null) {
358
                        try {
359
                            result.close();
360
                        } catch (Exception e) {
361
                            LOG.error("Error closing DatabaseMetadata "
362
                                    + "getTables() Resultset", e);
363
                        }
364
                    }
365
                }
366
            }
367

    
368
        };
369

    
370
        try {
371
            return (List) helper.doConnectionAction(action);
372
        } catch (Exception e) {
373
            throw new ReadException(this.getProviderName(), e);
374
        }
375
    }
376

    
377
    public void open() throws OpenException {
378
        helper.open();
379
    }
380

    
381
    public void close() throws CloseException {
382
        helper.close();
383
    }
384

    
385
    @Override
386
    protected void doDispose() throws BaseException {
387
        helper.dispose();
388
        helper = null;
389
    }
390

    
391
    public String getProviderName() {
392
        return NAME;
393
    }
394

    
395
    public String getStoreName() {
396
        return JDBCStoreProvider.NAME;
397
    }
398

    
399
    public boolean canAdd() {
400
        if (this.canAdd == null) {
401
            ConnectionAction action = new ConnectionAction() {
402

    
403
                public Object action(Connection conn) throws DataException {
404
                    try {
405
                        DatabaseMetaData metadata = conn.getMetaData();
406
                        if (metadata.isReadOnly()) {
407
                            return Boolean.FALSE;
408
                        }
409
                        return Boolean.TRUE;
410
                    } catch (SQLException e) {
411
                        throw new JDBCSQLException(e);
412
                    }
413
                }
414

    
415
            };
416

    
417
            try {
418
                this.canAdd = (Boolean) helper.doConnectionAction(action);
419
            } catch (Exception e) {
420
                // FIXME Exception
421
                throw new RuntimeException(e);
422
            }
423
        }
424
        return this.canAdd.booleanValue();
425
    }
426

    
427
    public FeatureType getFeatureType(DataStoreParameters dsp)
428
            throws DataException {
429
        checkIsMine(dsp);
430

    
431
        // TODO: checks geometry columns and driver geometry supports
432
        EditableFeatureType edType = this.getServerExplorerProviderServices()
433
                .createNewFeatureType();
434
        helper.loadFeatureType(edType, (JDBCStoreParameters) dsp);
435

    
436
        return edType;
437

    
438
    }
439

    
440
    public boolean add(String providerName, NewDataStoreParameters ndsp, boolean overwrite)
441
            throws DataException {
442

    
443
        /**
444
         * CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
445
         * { column_name data_type [ DEFAULT default_expr ] [ column_constraint
446
         * [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING |
447
         * EXCLUDING } DEFAULTS ] } [, ... ] ) [ INHERITS ( parent_table [, ...
448
         * ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS |
449
         * DELETE ROWS | DROP } ]
450
         *
451
         * where column_constraint is:
452
         *
453
         * [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE | PRIMARY
454
         * KEY | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [
455
         * MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON
456
         * UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY
457
         * DEFERRED | INITIALLY IMMEDIATE ]
458
         *
459
         * and table_constraint is:
460
         *
461
         * [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) |
462
         * PRIMARY KEY ( column_name [, ... ] ) | CHECK ( expression ) | FOREIGN
463
         * KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
464
         * ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE
465
         * action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [
466
         * INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
467
         */
468
        if (!(ndsp instanceof JDBCNewStoreParameters)) {
469
            // FIXME exception
470
            throw new IllegalArgumentException();
471
        }
472
        checkIsMine(ndsp);
473

    
474
        JDBCNewStoreParameters jdbcnsp = (JDBCNewStoreParameters) ndsp;
475

    
476
        StringBuilder sql = new StringBuilder();
477

    
478
        if (!jdbcnsp.isValid()) {
479
            // TODO Exception
480
            throw new InitializeException(this.getProviderName(), new Exception(
481
                    "Parameters not valid"));
482
        }
483
        try {
484
            jdbcnsp.validate();
485
        } catch (ValidateDataParametersException e1) {
486
            throw new InitializeException(this.getProviderName(), e1);
487
        }
488

    
489
        FeatureType fType = jdbcnsp.getDefaultFeatureType();
490

    
491
        sql.append("Create table " + jdbcnsp.tableID()
492
                + "(");
493
        Iterator attrs = fType.iterator();
494
        String sqlAttr;
495
        List sqlAttrs = new ArrayList();
496

    
497
        while (attrs.hasNext()) {
498
            sqlAttr = helper
499
                    .getSqlFieldDescription((FeatureAttributeDescriptor) attrs
500
                            .next());
501
            if (sqlAttr != null) {
502
                sqlAttrs.add(sqlAttr);
503
            }
504
        }
505

    
506
        helper.stringJoin(sqlAttrs, ", ", sql);
507

    
508
        sql.append(")");
509

    
510
        final String sqlCreate = sql.toString();
511
        final List sqlAdditional = helper.getAdditionalSqlToCreate(ndsp, fType);
512

    
513
        List permissions = this.getHelper().createGrantStatements((JDBCNewStoreParameters) ndsp);
514
        if (permissions != null) {
515
            sqlAdditional.addAll(permissions);
516
        }
517

    
518
        if (((JDBCNewStoreParameters) ndsp).getPostCreatingStatement() != null) {
519
            sqlAdditional.add(((JDBCNewStoreParameters) ndsp).getPostCreatingStatement());
520
        }
521

    
522
        TransactionalAction action = new TransactionalAction() {
523

    
524
            public boolean continueTransactionAllowed() {
525
                // TODO Auto-generated method stub
526
                return false;
527
            }
528

    
529
            public Object action(Connection conn) throws DataException {
530
                Statement st = null;
531

    
532
                try {
533
                    st = conn.createStatement();
534
                } catch (SQLException e1) {
535
                    throw new JDBCSQLException(e1);
536
                }
537
                String sql = null;
538

    
539
                try {
540
                    sql = sqlCreate;
541
                    JDBCHelper.execute(st, sql);
542
                    if (sqlAdditional != null) {
543
                        Iterator iter = sqlAdditional.iterator();
544
                        while (iter.hasNext()) {
545
                            sql = (String) iter.next();
546
                            JDBCHelper.execute(st, sql);
547
                        }
548
                    }
549

    
550
                } catch (SQLException e) {
551
                    throw new JDBCExecuteSQLException(sql, e);
552
                } finally {
553
                    try {
554
                        st.close();
555
                    } catch (SQLException e) {
556
                        LOG.error("Exception clossing statement", e);
557
                    }
558
                    ;
559
                }
560

    
561
                return Boolean.TRUE;
562
            }
563

    
564
        };
565

    
566
        Boolean result = Boolean.FALSE;
567

    
568
        try {
569
            result = (Boolean) helper.doConnectionAction(action);
570
        } catch (DataException e) {
571
            throw e;
572
        } catch (Exception e) {
573
            // FIXME Exception
574
            throw new RuntimeException(e);
575
        }
576

    
577
        return result.booleanValue();
578
    }
579

    
580
    public List getDataStoreProviderNames() {
581
        List x = new ArrayList(1);
582
        x.add(JDBCStoreProvider.NAME);
583
        return x;
584
    }
585

    
586
    public void updateTableStatistics(String tableName) throws JDBCExecuteSQLException {
587

    
588
    }
589

    
590
    @Override
591
    public DataStoreParameters get(String name) throws DataException {
592
        JDBCStoreParameters params = this.createStoreParams();
593
        params.setTable(name);
594
        return params;
595
    }
596
}