Application: gvSIG desktop

/* gvSIG. Geographic Information System of the Valencian Government

*

* Copyright (C) 2007-2008 Infrastructures and Transports Department

* of the Valencian Government (CIT)

*

* 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.

*

*/

/*

* AUTHORS (In addition to CIT):

* 2009 IVER T.I   {{Task}}

*/

/**

 *

 */

package org.gvsig.fmap.dal.store.jdbc;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataStore;

import org.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.NewDataStoreParameters;

import org.gvsig.fmap.dal.exception.CloseException;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.exception.InitializeException;

import org.gvsig.fmap.dal.exception.OpenException;

import org.gvsig.fmap.dal.exception.ProviderNotRegisteredException;

import org.gvsig.fmap.dal.exception.ReadException;

import org.gvsig.fmap.dal.exception.RemoveException;

import org.gvsig.fmap.dal.exception.ValidateDataParametersException;

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

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

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

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

import org.gvsig.fmap.dal.resource.spi.ResourceProvider;

import org.gvsig.fmap.dal.serverexplorer.db.spi.AbstractDBServerExplorer;

import org.gvsig.fmap.dal.spi.DataManagerProviderServices;

import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.tools.exception.BaseException;

/**

 * @author jmvivo

 *

 */

public class JDBCServerExplorer extends AbstractDBServerExplorer

                implements JDBCHelperUser {

        private static final Logger LOG = LoggerFactory

                        .getLogger(JDBCServerExplorer.class);

        private static final String METADATA_COLUMN_TABLE_CATALOG = "TABLE_CAT";

        private static final String METADATA_COLUMN_TABLE_SCHEMA = "TABLE_SCHEM";

        private static final String METADATA_COLUMN_TABLE_NAME = "TABLE_NAME";

        public static final String NAME = "JDBCServerExplorer";

        protected JDBCHelper helper;

        private Boolean canAdd;

        public JDBCServerExplorer(JDBCServerExplorerParameters parameters,

                        DataServerExplorerProviderServices services)

                        throws InitializeException {

                super(parameters, services);

                this.helper = createHelper();

        }

        protected JDBCServerExplorerParameters getJDBCParameters() {

                return (JDBCServerExplorerParameters) getParameters();

        }

        protected JDBCHelper createHelper() throws InitializeException {

                return new JDBCHelper(this, getJDBCParameters());

        }

        protected JDBCHelper getHelper() {

                return helper;

        }

        public List list() throws DataException {

                return this.list(MODE_ALL);

        }

        public List list(boolean showInformationDBTables) throws DataException {

                return this.list(MODE_ALL, showInformationDBTables);

        }

        public List list(int mode) throws DataException {

                JDBCServerExplorerParameters parameters = getJDBCParameters();

                if (parameters.getShowInformationDBTables() != null) {

                        return this.list(mode, parameters.getShowInformationDBTables()

                                        .booleanValue());

                }

                Boolean show = (Boolean) parameters

                                .getDynClass()

                                .getDynField(

                                                JDBCServerExplorerParameters.SHOWINFORMATIONDBTABLES_PARAMTER_NAME)

                                .getDefaultValue();

                if (show == null){

                        show = Boolean.FALSE;

                }

                return this.list(mode, show.booleanValue());

        }

        protected DataManagerProviderServices getManager() {

                return (DataManagerProviderServices) DALLocator.getDataManager();

        }

        public boolean hasGeometrySupport() {

                return false;

        }

        public boolean closeResourceRequested(ResourceProvider resource) {

                try {

                        this.helper.close();

                } catch (CloseException e) {

                        LOG.error("Exception in close Request", e);

                }

                return !this.helper.isOpen();

        }

        public void resourceChanged(ResourceProvider resource) {

                // Nothing to do

        }

        public void remove(DataStoreParameters dsp) throws RemoveException {

                final JDBCStoreParameters dsParams =(JDBCStoreParameters) dsp;

                TransactionalAction action = new TransactionalAction() {

                        public boolean continueTransactionAllowed() {

                                return false;

                        }

                        public Object action(Connection conn) throws DataException {

                                Statement st;

                                try{

                                        st = conn.createStatement();

                                } catch (SQLException e) {

                                        throw new JDBCSQLException(e);

                                }

                                String sqlDrop = "Drop table "

                                        + dsParams.tableID();

                                try{

                                        try{

                                                st.execute(sqlDrop);

                                        } catch (SQLException e) {

                                                throw new JDBCExecuteSQLException(sqlDrop, e);

                                        }

                                } finally{

                                        try{ st.close(); } catch (SQLException e) {};

                                }

                                return null;

                        }

                };

                try {

                        this.helper.doConnectionAction(action);

                } catch (Exception e) {

                        throw new RemoveException(this.getProviderName(), e);

                }

        }

        public NewDataStoreParameters getAddParameters() throws DataException {

                JDBCServerExplorerParameters parameters = getJDBCParameters();

                JDBCNewStoreParameters params = new JDBCNewStoreParameters();

                params.setHost(parameters.getHost());

                params.setPort(parameters.getPort());

                params.setDBName(parameters.getDBName());

                params.setUser(parameters.getUser());

                params.setPassword(parameters.getPassword());

                params.setCatalog(parameters.getCatalog());

                params.setSchema(parameters.getSchema());

                params.setJDBCDriverClassName(parameters.getJDBCDriverClassName());

                params.setUrl(parameters.getUrl());

                params.setDefaultFeatureType(this.getServerExplorerProviderServices()

                                .createNewFeatureType());

                return params;

        }

        public void closeDone() throws DataException {

                // Nothing to do

        }

        public void opendDone() throws DataException {

                // Nothin to do

        }

        public DataStore open(DataStoreParameters dsp) throws DataException {

                checkIsMine(dsp);

                DataManager dataMan = DALLocator.getDataManager();

                DataStore store;

                try {

                        store = dataMan.openStore(dsp.getDataStoreName(), dsp);

                } catch (ValidateDataParametersException e) {

                        throw new InitializeException(e);

                }

                return store;

        }

        protected void checkIsMine(DataStoreParameters dsp) {

                if (!(dsp instanceof JDBCStoreParameters)) {

                        // FIXME Exception ???

                        throw new IllegalArgumentException(

                                        "not instance of FilesystemStoreParameters");

                }

                // try {

                // dsp.validate();

                // } catch (ValidateDataParametersException e) {

                // throw new IllegalArgumentException("check parameters", e);

                // }

                JDBCServerExplorerParameters parameters = getJDBCParameters();

                JDBCStoreParameters pgp = (JDBCStoreParameters) dsp;

                if (!compare(pgp.getHost(), parameters.getHost())) {

                        throw new IllegalArgumentException("wrong explorer: Host (mine: "

                                        + parameters.getHost() + " other:" + pgp.getHost() + ")");

                }

                if (!compare(pgp.getPort(), parameters.getPort())) {

                        throw new IllegalArgumentException("wrong explorer: Port (mine: "

                                        + parameters.getPort() + " other:" + pgp.getPort() + ")");

                }

                if (!compare(pgp.getDBName(), parameters.getDBName())) {

                        throw new IllegalArgumentException("wrong explorer: DBName (mine: "

                                        + parameters.getDBName() + " other:" + pgp.getDBName()

                                        + ")");

                }

                if (parameters.getCatalog() != null && !parameters.getCatalog().trim().equals("")) {

                        // implicit catalog

                        if (!compare(pgp.getCatalog(), parameters.getCatalog())) {

                                throw new IllegalArgumentException(

                                                "wrong explorer: Catalog (mine: "

                                                                + parameters.getCatalog() + " other:"

                                                                + pgp.getCatalog() + ")");

                        }

                }

                if (parameters.getSchema() != null && !parameters.getSchema().trim().equals("")) {

                        // implicit schema

                        if (!compare(pgp.getSchema(), parameters.getSchema())) {

                                throw new IllegalArgumentException(

                                                "wrong explorer: Schema (mine: "

                                                                + parameters.getSchema() + " other:"

                                                                + pgp.getSchema() + ")");

                        }

                }

        }

        protected boolean compare(Object str1, Object str2) {

                if (str1 == str2){

                        return true;

                }

                if (str1 == null){

                        return false;

                }

                return  str1.equals(str2);

        }

        protected JDBCStoreParameters createStoreParams()

                        throws InitializeException, ProviderNotRegisteredException {

                DataManagerProviderServices manager = this.getManager();

                JDBCServerExplorerParameters parameters = getJDBCParameters();

                JDBCStoreParameters orgParams = (JDBCStoreParameters) manager

                                .createStoreParameters(getStoreName());

                orgParams.setHost(parameters.getHost());

                orgParams.setPort(parameters.getPort());

                orgParams.setDBName(parameters.getDBName());

                orgParams.setUser(parameters.getUser());

                orgParams.setPassword(parameters.getPassword());

        orgParams.setCatalog(parameters.getCatalog());

        orgParams.setJDBCDriverClassName(parameters.getJDBCDriverClassName());

        orgParams.setSchema(parameters.getSchema());

        orgParams.setUrl(parameters.getUrl());

                return orgParams;

        }

        public List list(final int mode, final boolean showInformationDBTables)

                        throws DataException {

                final JDBCStoreParameters orgParams = createStoreParams();

                ConnectionAction action = new ConnectionAction() {

                        public Object action(Connection conn) throws DataException {

                                String[] tableTypes = null;

                                if (!showInformationDBTables) {

                                        tableTypes = new String[] { "TABLE", "VIEW" };

                                }

                                ResultSet result = null;

                                try {

                                        DatabaseMetaData metadata = conn.getMetaData();

                                        result = metadata.getTables(null, null, null,

                                                        tableTypes);

                                        List<JDBCStoreParameters> paramList = new ArrayList<JDBCStoreParameters>();

                                        while (result.next()) {

                                                JDBCStoreParameters params = (JDBCStoreParameters) orgParams

                                                                .getCopy();

                                                params.setCatalog(result

                                                                .getString(METADATA_COLUMN_TABLE_CATALOG));

                                                params.setSchema(result

                                                                .getString(METADATA_COLUMN_TABLE_SCHEMA));

                                                params.setTable(result

                                                                .getString(METADATA_COLUMN_TABLE_NAME));

                                                paramList.add(params);

                                        }

                                        return paramList;

                                } catch (SQLException e) {

                                        throw new JDBCSQLException(e);

                                } finally {

                                        if (result != null) {

                                                try {

                                                        result.close();

                                                } catch (Exception e) {

                                                        LOG.error("Error closing DatabaseMetadata "

                                                                        + "getTables() Resultset", e);

                                                }

                                        }

                                }

                        }

                };

                try {

                        return (List) helper.doConnectionAction(action);

                } catch (Exception e) {

                        throw new ReadException(this.getProviderName(), e);

                }

        }

        public void open() throws OpenException {

                helper.open();

        }

        public void close() throws CloseException {

                helper.close();

        }

        @Override

        protected void doDispose() throws BaseException {

                helper.dispose();

                helper = null;

        }

        public String getProviderName() {

                return NAME;

        }

        protected String getStoreName() {

                return JDBCStoreProvider.NAME;

        }

        public boolean canAdd() {

                if (this.canAdd == null){

                        ConnectionAction action = new ConnectionAction(){

                                public Object action(Connection conn) throws DataException {

                                        try {

                                        DatabaseMetaData metadata = conn.getMetaData();

                                                if (metadata.isReadOnly()) {

                                                        return Boolean.FALSE;

                                                }

                                                return Boolean.TRUE;

                                        } catch (SQLException e) {

                                                throw new JDBCSQLException(e);

                                        }

                                }

                        };

                        try {

                                this.canAdd = (Boolean) helper.doConnectionAction(action);

                        } catch (Exception e) {

                                // FIXME Exception

                                throw new RuntimeException(e);

                        }

                }

                return this.canAdd.booleanValue();

        }

        public FeatureType getFeatureType(DataStoreParameters dsp)

                        throws DataException {

                checkIsMine(dsp);

                // TODO: checks geometry columns and driver geometry supports

                EditableFeatureType edType = this.getServerExplorerProviderServices()

                                .createNewFeatureType();

                helper.loadFeatureType(edType, (JDBCStoreParameters) dsp);

                return edType;

        }

        public boolean add(String providerName, NewDataStoreParameters ndsp, boolean overwrite)

                        throws DataException {

                /**

                 * CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (

                 * { column_name data_type [ DEFAULT default_expr ] [ column_constraint

                 * [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING |

                 * EXCLUDING } DEFAULTS ] } [, ... ] ) [ INHERITS ( parent_table [, ...

                 * ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS |

                 * DELETE ROWS | DROP } ]

                 *

                 * where column_constraint is:

                 *

                 * [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE | PRIMARY

                 * KEY | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [

                 * MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON

                 * UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY

                 * DEFERRED | INITIALLY IMMEDIATE ]

                 *

                 * and table_constraint is:

                 *

                 * [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) |

                 * PRIMARY KEY ( column_name [, ... ] ) | CHECK ( expression ) | FOREIGN

                 * KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...

                 * ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE

                 * action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [

                 * INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

                 */

                if (!(ndsp instanceof NewFeatureStoreParameters)) {

                        // FIXME exception

                        throw new IllegalArgumentException();

                }

                checkIsMine(ndsp);

                NewFeatureStoreParameters nfdsp = (NewFeatureStoreParameters) ndsp;

                StringBuilder sql = new StringBuilder();

                if (!nfdsp.isValid()) {

                        // TODO Exception

                        throw new InitializeException(this.getProviderName(), new Exception(

                                        "Parameters not valid"));

                }

                try {

                        nfdsp.validate();

                } catch (ValidateDataParametersException e1) {

                        throw new InitializeException(this.getProviderName(), e1);

                }

                FeatureType fType = nfdsp.getDefaultFeatureType();

                sql.append("Create table " + ((JDBCStoreParameters) ndsp).tableID()

                                + "(");

                Iterator attrs = fType.iterator();

                String sqlAttr;

                List sqlAttrs = new ArrayList();

                while (attrs.hasNext()) {

                        sqlAttr = helper

                                        .getSqlFieldDescription((FeatureAttributeDescriptor) attrs

                                                        .next());

                        if (sqlAttr != null) {

                                sqlAttrs.add(sqlAttr);

                        }

                }

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

                sql.append(")");

                final String sqlCreate = sql.toString();

                final List sqlAdditional = helper

                                .getAdditionalSqlToCreate(ndsp, fType);

                TransactionalAction action = new TransactionalAction() {

                        public boolean continueTransactionAllowed() {

                                // TODO Auto-generated method stub

                                return false;

                        }

                        public Object action(Connection conn) throws DataException {

                                Statement st = null;

                                try {

                                        st = conn.createStatement();

                                } catch (SQLException e1) {

                                        throw new JDBCSQLException(e1);

                                }

                                String sql = null;

                                try {

                                        sql = sqlCreate;

                                        st.execute(sql);

                                        if (sqlAdditional != null) {

                                                Iterator iter = sqlAdditional.iterator();

                                                while (iter.hasNext()) {

                                                        sql = (String) iter.next();

                                                        st.execute(sql);

                                                }

                                        }

                                } catch (SQLException e) {

                                        throw new JDBCExecuteSQLException(sql, e);

                                } finally {

                                        try {

                                                st.close();

                                        } catch (SQLException e) {

                                                LOG.error("Exception clossing statement", e);

                                        }

                                        ;

                                }

                                return Boolean.TRUE;

                        }

                };

                Boolean result = Boolean.FALSE;

                try {

                        result = (Boolean) helper.doConnectionAction(action);

                } catch (Exception e) {

                        // FIXME Exception

                        throw new RuntimeException(e);

                }

                return result.booleanValue();

        }

        public List getDataStoreProviderNames() {

                List x = new ArrayList(1);

                x.add(JDBCStoreProvider.NAME);

                return x;

        }

}