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.oracle;

import java.awt.geom.Rectangle2D;

import java.sql.Connection;

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.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.NewDataStoreParameters;

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

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

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.FeatureAttributeDescriptor;

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

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

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

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

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

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

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

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

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

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

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * ORACLE SERVER EXPLORER

 * 

 * @author vsanjaime

 * 

 */

public class OracleServerExplorer extends JDBCServerExplorer {

	final static private Logger logger = LoggerFactory

			.getLogger(OracleServerExplorer.class);

	public static final String NAME = "OracleServerExplorer";

	/**

	 * Constructor

	 * 

	 * @param parameters

	 * @param services

	 * @throws InitializeException

	 */

	public OracleServerExplorer(OracleServerExplorerParameters parameters,

			DataServerExplorerProviderServices services)

			throws InitializeException {

		super(parameters, services);

	}

	/**

	 * get explorer name

	 */

	public String getName() {

		return NAME;

	}

	/**

	 * can add

	 */

	public boolean canAdd() {

		return true;

	}

	/**

	 * remove

	 */

	public void removeMetadata(DataStoreParameters dsp) {

		final OracleStoreParameters oraParams = (OracleStoreParameters) 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);

				}

				// SQL DELETE METADATA

				String tname = oraParams.tableID();

				String sqlDeleteMetadata = "";

				int ind = tname.lastIndexOf(".");

				if (ind != -1) {

					String schema = tname.substring(0, ind);

					tname = tname.substring(ind + 1, tname.length());

					sqlDeleteMetadata = "DELETE FROM "

							+ OracleValues.USER_ORACLE_GEOMETADATA_VIEW

							+ " WHERE TABLE_NAME = '" + tname + "'";

				} else {

					sqlDeleteMetadata = "DELETE FROM "

							+ OracleValues.USER_ORACLE_GEOMETADATA_VIEW

							+ " WHERE TABLE_NAME = '" + tname + "'";

				}

				try {

					st.execute(sqlDeleteMetadata);

				} catch (SQLException e) {

					logger.error("While deleting metadata: " + e.getMessage());

				}

				if (st != null) {

					try {

						st.close();

					} catch (SQLException e) {

						logger.error("While closing statement: " + e.getMessage());

					}

				}

				return null;

			}

		};

		try {

			this.helper.doConnectionAction(action);

		} catch (Exception e) {

			logger.error("While removing metadatat: " + e.getMessage());

		}

	}

	/**

	 * remove

	 */

	public void remove(DataStoreParameters dsp) throws RemoveException {

		final OracleStoreParameters oraParams = (OracleStoreParameters) 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);

				}

				// SQL REMOVE TABLE

				String sqlDropTable = "DROP TABLE " + oraParams.tableID()

						+ " CASCADE CONSTRAINTS";

				// SQL DELETE METADATA

				String tname = oraParams.tableID();

				String sqlDeleteMetadata = "";

				int ind = tname.lastIndexOf(".");

				if (ind != -1) {

					String schema = tname.substring(0, ind);

					tname = tname.substring(ind + 1, tname.length());

					sqlDeleteMetadata = "DELETE FROM "

							+ OracleValues.USER_ORACLE_GEOMETADATA_VIEW

							+ " WHERE TABLE_NAME = '" + tname + "'";

				} else {

					sqlDeleteMetadata = "DELETE FROM "

							+ OracleValues.USER_ORACLE_GEOMETADATA_VIEW

							+ " WHERE TABLE_NAME = '" + tname + "'";

				}

				try {

					// DROP TABLE

					try {

						st.execute(sqlDropTable);

					} catch (SQLException e) {

						throw new JDBCExecuteSQLException(sqlDropTable, e);

					}

					// DELETE METADATA

					try {

						st.execute(sqlDeleteMetadata);

					} catch (SQLException e) {

						throw new JDBCExecuteSQLException(sqlDeleteMetadata, e);

					}

				} finally {

					try {

						st.close();

					} catch (SQLException e) {

					}

					;

				}

				return null;

			}

		};

		try {

			this.helper.doConnectionAction(action);

		} catch (Exception e) {

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

		}

	}

	/**

	 * get parameters

	 */

	public NewDataStoreParameters getAddParameters() throws DataException {

		OracleServerExplorerParameters parameters = getOracleServerExplorerParameters();

		OracleNewStoreParameters params = new OracleNewStoreParameters();

		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.setUseSSL(parameters.getUseSSL());

		params.setOraDriverType(parameters.getOraDriverType());

		params.setDefaultFeatureType(this.getServerExplorerProviderServices()

				.createNewFeatureType());

		return params;

	}

	/**

	 * Geometry support

	 */

	public boolean hasGeometrySupport() {

		return true;

	}

	/**

	 * Get list of table availables in Oracle schema registered in geometry

	 * metadata view

	 */

	public List<JDBCStoreParameters> list(final int mode,

			final boolean showInformationDBTables) throws DataException {

		final JDBCStoreParameters orgParams = createStoreParams();

		ConnectionAction action = new ConnectionAction() {

			public Object action(Connection conn) throws DataException {

				ResultSet rs = null;

				Statement st = null;

				List<String> sqls = getSQLForList(mode, showInformationDBTables);

				try {

					JDBCStoreParameters params = null;

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

					conn = helper.getConnection();

					st = conn.createStatement();

					String sql;

					Iterator<String> sqlIter = sqls.iterator();

					while (sqlIter.hasNext()) {

						sql = sqlIter.next();

						rs = st.executeQuery(sql);

						while (rs.next()) {

							params = (JDBCStoreParameters) orgParams.getCopy();

							params.setTable(rs.getString(1));

							paramList.add(params);

						}

					}

					return paramList;

				} catch (SQLException e) {

					throw new JDBCSQLException(e);

				} finally {

					try {

						rs.close();

					} catch (Exception e) {

					}

					;

					try {

						st.close();

					} catch (Exception e) {

					}

					;

				}

			}

		};

		try {

			return (List) helper.doConnectionAction(action);

		} catch (Exception e) {

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

		}

	}

	public boolean dataStoreExists(DataStoreParameters dsp) {

		final String select_str = "SELECT * FROM " + ((JDBCStoreParameters) dsp).tableID();

		TransactionalAction action = new TransactionalAction() {

			public boolean continueTransactionAllowed() {

				return false;

			}

			public Object action(Connection conn) throws DataException {

				Statement st = null;

				try {

					st = conn.createStatement();

				} catch (SQLException e1) {

					throw new JDBCSQLException(e1);

				}

				boolean resp = true;

				// try select

				try {

					st.execute(select_str);

					logger.warn("Table exists: " + select_str);

				} catch (SQLException e) {

					logger.warn("Table does not exist: " + select_str);

					resp = false;

				}

				try {

					st.close();

				} catch (Exception ex) {

					logger.error("Exception closing statement", ex);

				}

				return resp;

			}

		};

		Boolean result = Boolean.FALSE;

		try {

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

		} catch (Exception e) {

			throw new RuntimeException(e);

		}

		return result.booleanValue();

	}

	public void addWithEnvelopeAndDims(NewFeatureStoreParameters params, boolean b,

			Rectangle2D envelope, int thedims) throws DataException {

		String sql_create = add_SqlCreate(params, b);

		String sql_index = add_SqlIndex(params, b);

		String sql_meta = add_SqlMeta(params, b, envelope, new Integer(thedims));

		doAdd(sql_create, sql_index, sql_meta);

	}

	private boolean doAdd(final String sql_create, final String sql_index, final String sql_meta) {

		TransactionalAction action = new TransactionalAction() {

			public boolean continueTransactionAllowed() {

				return false;

			}

			public Object action(Connection conn) throws DataException {

				Statement st = null;

				try {

					st = conn.createStatement();

				} catch (SQLException e1) {

					throw new JDBCSQLException(e1);

				}

				String sqlnew = null;

				String sqlspatialindex = null;

				String sqlmetadata = null;

				// new table

				try {

					sqlnew = sql_create;

					st.execute(sqlnew);

				} catch (SQLException e) {

					try { st.close(); } catch (SQLException se) { logger.error("Exception closing statement", se); }

					throw new JDBCExecuteSQLException(sqlnew, e);

				}

				// new metadata

				try {

					sqlmetadata = sql_meta;

					st.execute(sqlmetadata);

				} catch (SQLException e) {

					try { st.close(); } catch (SQLException se) { logger.error("Exception closing statement", se); }

					throw new JDBCExecuteSQLException(sqlspatialindex, e);

				}

				// new spatial index

				try {

					sqlspatialindex = sql_index;

					st.execute(sqlspatialindex);

				} catch (SQLException e) {

					try { st.close(); } catch (SQLException se) { logger.error("Exception closing statement", se); }

					throw new JDBCExecuteSQLException(sqlspatialindex, e);

				}

				return Boolean.TRUE;

			}

		};

		Boolean result = Boolean.FALSE;

		try {

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

		} catch (Exception e) {

			throw new RuntimeException(e);

		}

		return result.booleanValue();

	}

	private String add_SqlMeta(NewFeatureStoreParameters params, boolean b,

			Rectangle2D bbox, Integer thedims) {

		// SQL CREATE TABLE METADATA

		Rectangle2D _bbox = bbox;

		if (_bbox == null) {

			logger.warn("Envelope not found in parameters: set (0,0) - (1,1)");

			_bbox = OracleUtils.DEFAULT_BBOX;

		}

		int _dims = 2;

		if (thedims == null) {

			logger.warn("Dimensions not found in parameters: assumed 2");

		} else {

			_dims = thedims.intValue();

		}

		String sqlmeta = ((OracleHelper) helper).getSqlUpdateMetadata(

				(OracleStoreParameters) params, null, _bbox, _dims, true);

		// TODO Auto-generated method stub

		return sqlmeta;

	}

	private String add_SqlIndex(NewFeatureStoreParameters params, boolean b) {

		// SQL CREATE SPATIAL INDEX

		String sqlindex = "CREATE INDEX "

				+ OracleUtils.getDerivedName(((JDBCStoreParameters) params)

						.tableID(), "SX") + " ON "

				+ ((JDBCStoreParameters) params).tableID() + " (\""

				+ OracleValues.DEFAULT_GEO_FIELD

				+ "\") INDEXTYPE IS \"MDSYS\".\"SPATIAL_INDEX\" ";

		return sqlindex;

	}

	private String add_SqlCreate(NewFeatureStoreParameters nfdsp, boolean b) throws DataException {

		// SQL CREATE NEW TABLE

		StringBuilder sqlnewtable = new StringBuilder();

		FeatureType fType = nfdsp.getDefaultFeatureType();

		sqlnewtable.append("CREATE TABLE "

				+ ((JDBCStoreParameters) nfdsp).tableID() + "(");

		Iterator<FeatureAttributeDescriptor> attrs = fType.iterator();

		String sqlAttr;

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

		while (attrs.hasNext()) {

			sqlAttr = helper

					.getSqlFieldDescription((FeatureAttributeDescriptor) attrs

							.next());

			if (sqlAttr != null) {

				sqlAttrs.add(sqlAttr);

			}

		}

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

		String pk = "CONSTRAINT "

				+ OracleUtils.getDerivedName(((JDBCStoreParameters) nfdsp)

						.tableID(), "PK") + " PRIMARY KEY (\""

				+ OracleValues.DEFAULT_ID_FIELD_CASE_SENSITIVE + "\") ENABLE";

		sqlnewtable.append(", ");

		sqlnewtable.append(pk);

		sqlnewtable.append(")");

		return sqlnewtable.toString();		

	}

	/**

	 * create new table

	 * 

	 * @params ndsp

	 * @params overwrite

	 * @return

	 */

	public boolean add(NewDataStoreParameters ndsp, boolean overwrite)

			throws DataException {

		if (!(ndsp instanceof NewFeatureStoreParameters)) {

			throw new IllegalArgumentException("Expected: NewFeatureStoreParameters");

		}

		checkIsMine(ndsp);

		NewFeatureStoreParameters nfdsp = (NewFeatureStoreParameters) ndsp;

		if (!nfdsp.isValid()) {

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

					"Parameters not valid"));

		}

		try {

			nfdsp.validate();

		} catch (ValidateDataParametersException e1) {

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

		}

		String sql_create = add_SqlCreate(nfdsp, overwrite);

		String sql_index = add_SqlIndex(nfdsp, overwrite);

		String sql_meta = add_SqlMeta(nfdsp, overwrite, null, null);

		return doAdd(sql_create, sql_index, sql_meta);

	}

	/**

	 * create helper

	 */

	protected JDBCHelper createHelper() throws InitializeException {

		return new OracleHelper(this, this.getOracleServerExplorerParameters());

	}

	/**

	 * Get store name

	 * 

	 * @return

	 */

	protected String getStoreName() {

		return OracleStoreProvider.NAME;

	}

	/**

	 * get Oracle helper

	 * 

	 * @return

	 */

	protected OracleHelper getOracleHelper() {

		return (OracleHelper) getHelper();

	}

	/**

	 * Get list sql sentences for list available tables

	 * 

	 * @param mode

	 * @param showInformationDBtables

	 * @return

	 */

	protected List<String> getSQLForList(int mode,

			boolean showInformationDBTables) {

		List<String> list = new ArrayList<String>(1);

		list.add("SELECT TABLE_NAME FROM TABS");

		//USER_SDO_GEOM_METADATA means tables witg geometry

		//list.add("SELECT TABLE_NAME FROM USER_SDO_GEOM_METADATA");

		return list;

	}

	/**

	 * check oracle data store parameters, validate SSL and type driver (THIN or

	 * OCI)

	 * 

	 * @param dsp

	 */

	protected void checkIsMine(DataStoreParameters dsp) {

		if (!(dsp instanceof OracleStoreParameters)) {

			throw new IllegalArgumentException(

					"not instance of OracleStoreParameters");

		}

		super.checkIsMine(dsp);

		OracleStoreParameters orap = (OracleStoreParameters) dsp;

		if (orap.getUseSSL().booleanValue() != this.getOracleServerExplorerParameters()

				.getUseSSL()) {

			throw new IllegalArgumentException("worng explorer: SSL");

		}

		if (orap.getOraDriverType().compareToIgnoreCase(

				this.getOracleServerExplorerParameters().getOraDriverType()) != 0) {

			throw new IllegalArgumentException(

					"worng explorer: Oracle type driver: THIN or OCI");

		}

	}

	/**

	 * Create store parameters

	 */

	protected JDBCStoreParameters createStoreParams()

			throws InitializeException, ProviderNotRegisteredException {

		OracleStoreParameters params = (OracleStoreParameters) super.createStoreParams();

		// add SSL and type driver (THIN or OCI)

		params.setSchema(params.getUser().toUpperCase());

		params.setUseSSL(this.getOracleServerExplorerParameters().getUseSSL());

		params.setOraDriverType(this.getOracleServerExplorerParameters()

				.getOraDriverType());

		return params;

	}

	/**

	 * Get Oracle server explorer parameters

	 * 

	 * @return

	 */

	private OracleServerExplorerParameters getOracleServerExplorerParameters() {

		return (OracleServerExplorerParameters) getParameters();

	}

}