Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

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

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

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

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

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Iterator;

import java.util.List;

import org.apache.commons.io.IOUtils;

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

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

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

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

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

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

import org.gvsig.fmap.dal.feature.exception.PerformEditingException;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices;

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

import org.gvsig.fmap.dal.store.db.FeatureTypeHelper;

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.tools.dynobject.DynObject;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * @author jmvivo

 *

 */

public class JDBCStoreProviderWriter extends JDBCStoreProvider {

	final static private Logger logger = LoggerFactory

			.getLogger(JDBCStoreProviderWriter.class);

        protected Appender appender;

	public JDBCStoreProviderWriter(JDBCStoreParameters params,

			DataStoreProviderServices storeServices)

			throws InitializeException {

		super(params, storeServices);

                this.appender = this.createAppender();

	}

	protected JDBCStoreProviderWriter(JDBCStoreParameters params,

			DataStoreProviderServices storeServices, DynObject metadata)

			throws InitializeException {

		super(params, storeServices, metadata);

                this.appender = this.createAppender();

	}

        protected Appender createAppender() {

            return new JDBCAppender(this);

        }

        public boolean supportsAppendMode() {

            return true;

        }

        public void endAppend() throws DataException {

            this.appender.end();

        }

        public void beginAppend() throws DataException {

            this.appender.begin();

        }

        public void append(final FeatureProvider featureProvider) throws DataException {

            this.appender.append(featureProvider);

        }

	protected void addToListFeatureValues(FeatureProvider featureProvider,

			FeatureAttributeDescriptor attrOfList,

			FeatureAttributeDescriptor attr, List<Object> values) throws DataException {

		if (attr == null) {

			if (attrOfList.isPrimaryKey()) {

				throw new RuntimeException("pk attribute '"

						+ attrOfList.getName() + "' not found in feature");

			}

			values.add(helper

					.dalValueToJDBC(attr, attrOfList.getDefaultValue()));

		} else {

			values.add(helper.dalValueToJDBC(attr, featureProvider.get(attr

					.getIndex())));

		}

	}

	protected void addToListFeatureValues(FeatureProvider featureProvider,

			List<FeatureAttributeDescriptor> attributes, List<Object> values) throws DataException {

		FeatureAttributeDescriptor attr, attrOfList;

		FeatureType fType = featureProvider.getType();

		for (int i = 0; i < attributes.size(); i++) {

			attrOfList = (FeatureAttributeDescriptor) attributes.get(i);

			attr = fType.getAttributeDescriptor(attrOfList.getName());

			addToListFeatureValues(featureProvider, attrOfList, attr, values);

		}

	}

	protected void appendToSQLPreparedPkWhereClause(StringBuilder sql,

			List<FeatureAttributeDescriptor> pkAttributes) {

		sql.append(" Where ");

		FeatureAttributeDescriptor attr;

		for (int i = 0; i < pkAttributes.size() - 1; i++) {

			attr = (FeatureAttributeDescriptor) pkAttributes.get(i);

			sql.append(helper.escapeFieldName(attr.getName()));

			sql.append(" = ? AND ");

		}

		attr = (FeatureAttributeDescriptor) pkAttributes.get(pkAttributes

				.size() - 1);

		sql.append(helper.escapeFieldName(attr.getName()));

		sql.append(" = ? ");

	}

	protected void executeRemovePreparedStatement(Connection conn, String sql,

			List<FeatureAttributeDescriptor> attributes, Iterator<FeatureReferenceProviderServices> featureReferences) throws DataException {

				PreparedStatement st;

				try {

					st = conn.prepareStatement(sql);

				} catch (SQLException e) {

					throw new JDBCPreparingSQLException(sql, e);

				}

				try {

					List<Object> values = new ArrayList<Object>();

					FeatureReferenceProviderServices featureRef;

//					FeatureType featureType;

					while (featureReferences.hasNext()) {

						st.clearParameters();

						featureRef = featureReferences.next();

						values.clear();

//						featureType = this.getFeatureStore()

//							.getFeatureType(featureRef.getFeatureTypeId());

						Iterator<FeatureAttributeDescriptor> iter = attributes.iterator();

						FeatureAttributeDescriptor attr;

						while (iter.hasNext()) {

							attr =  iter.next();

							values.add( helper.dalValueToJDBC(attr, featureRef

									.getKeyValue(attr.getName())));

						}

						for (int i = 0; i < values.size(); i++) {

							st.setObject(i + 1, values.get(i));

						}

						try {

							int nAffected =JDBCHelper.executeUpdate(st);

							if (nAffected == 0) {

								throw new JDBCUpdateWithoutChangesException(sql, values);

							}

							if (nAffected > 1){

								logger.warn("Remove statement affectst to {} rows: {}",

										nAffected, sql);

							}

						} catch (SQLException e) {

							throw new JDBCExecutePreparedSQLException(sql, values, e);

						}

					}

				} catch (SQLException e) {

					throw new JDBCSQLException(e);

				} finally {

                                    JDBCUtils.closeQuietly(st);

				}

			}

	protected void executeUpdatePreparedStatement(Connection conn, String sql,

			List<FeatureAttributeDescriptor> attributes, Iterator<FeatureProvider> featureProviders) throws DataException {

				PreparedStatement st;

				try {

					st = conn.prepareStatement(sql);

				} catch (SQLException e) {

					throw new JDBCPreparingSQLException(sql, e);

				}

				try {

					List<Object> values = new ArrayList<Object>();

					FeatureProvider featureProvider;

					while (featureProviders.hasNext()) {

						st.clearParameters();

						featureProvider = (FeatureProvider) featureProviders.next();

						values.clear();

						addToListFeatureValues(featureProvider, attributes, values);

						for (int i = 0; i < values.size(); i++) {

							st.setObject(i + 1, values.get(i));

						}

						try {

							if ( JDBCHelper.executeUpdate(st) == 0) {

								throw new JDBCUpdateWithoutChangesException(sql, values);

							}

						} catch (SQLException e) {

							throw new JDBCExecutePreparedSQLException(sql, values, e);

						}

					}

				} catch (SQLException e) {

					throw new JDBCSQLException(e,sql);

				} finally {

                                    JDBCUtils.closeQuietly(st);

				}

			}

	protected void performDeletes(Connection conn, Iterator<FeatureReferenceProviderServices> deleteds, List<FeatureAttributeDescriptor> pkAttributes)

			throws DataException {

				if (pkAttributes.size() < 0) {

					throw new RuntimeException("Operation requires missing pk");

				}

				// ************ Prepare SQL ****************

				StringBuilder sqlb = new StringBuilder();

				sqlb.append("Delete from ");

				sqlb.append(getJDBCParameters().tableID());

				appendToSQLPreparedPkWhereClause(sqlb, pkAttributes);

				String sql = sqlb.toString();

				// ************ Prepare SQL (end) ****************

				executeRemovePreparedStatement(conn, sql, pkAttributes, deleteds);

			}

	protected String getSqlStatementAddField(FeatureAttributeDescriptor attr,

			List<String> additionalStatement) throws DataException {

		StringBuilder strb = new StringBuilder();

		strb.append("ADD ");

		strb.append(this.helper.getSqlFieldDescription(attr));

		return strb.toString();

	}

	protected String getSqlStatementDropField(FeatureAttributeDescriptor attr,List<String> additionalStatement) {

		// DROP [ COLUMN ] column

		return " DROP COLUMN "

				+ this.helper.escapeFieldName(attr.getName());

	}

	protected List<String> getSqlStatementAlterField(

			FeatureAttributeDescriptor attrOrg,

			FeatureAttributeDescriptor attrTrg, List<String> additionalStatement)

			throws DataException {

		//

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

		StringBuilder strb;

		if (attrOrg.getDataType() != attrTrg.getDataType()) {

			// ALTER COLUMN {col} TYPE {type} character varying(35)

			strb = new StringBuilder();

			strb.append("ALTER COLUMN ");

			strb.append(helper.escapeFieldName(attrTrg.getName()));

			strb.append(" ");

			strb.append(helper.getSqlColumnTypeDescription(attrTrg));

			actions.add(strb.toString());

		}

		if (attrOrg.allowNull() != attrTrg.allowNull()) {

			// ALTER [ COLUMN ] column { SET | DROP } NOT NULL

			strb = new StringBuilder();

			strb.append("ALTER COLUMN ");

			strb.append(helper.escapeFieldName(attrTrg.getName()));

			strb.append(' ');

			if (attrTrg.allowNull()) {

				strb.append("SET ");

			} else {

				strb.append("DROP ");

			}

			strb.append("NOT NULL");

			actions.add(strb.toString());

		}

		if (attrOrg.getDefaultValue() != attrTrg.getDefaultValue()) {

			if (attrTrg.getDefaultValue() == null) {

				// ALTER [ COLUMN ] column DROP DEFAULT

				strb = new StringBuilder();

				strb.append("ALTER COLUMN ");

				strb.append(helper.escapeFieldName(attrTrg.getName()));

				strb.append(" DROP DEFAULT");

				actions.add(strb.toString());

			} else if (!attrTrg.getDefaultValue().equals(

					attrOrg.getDefaultValue())) {

				// ALTER [ COLUMN ] column DROP DEFAULT

				strb = new StringBuilder();

				strb.append("ALTER COLUMN ");

				strb.append(helper.escapeFieldName(attrTrg.getName()));

				strb.append(" SET DEFAULT");

				strb.append(helper.dalValueToJDBC(attrTrg, attrTrg

						.getDefaultValue()));

				actions.add(strb.toString());

			}

		}

		return actions;

	}

	protected void performUpdateTable(Connection conn, FeatureType original,

			FeatureType target) throws DataException {

		/*

		 *

		 * ALTER TABLE [ ONLY ] name [ * ] action [, ... ]

		 */

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

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

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

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

		FeatureAttributeDescriptor attrOrg;

		FeatureAttributeDescriptor attrTrg;

		Iterator<FeatureAttributeDescriptor> attrs = FeatureTypeHelper.iterator(original);

		while (attrs.hasNext()) {

			attrOrg = (FeatureAttributeDescriptor) attrs.next();

			attrTrg = target.getAttributeDescriptor(attrOrg.getName());

			if (attrTrg == null) {

				toDrop.add(getSqlStatementDropField(attrOrg,

						additionalStatement));

			} else {

				toAlter.addAll(getSqlStatementAlterField(attrOrg, attrTrg,additionalStatement));

			}

		}

		attrs = FeatureTypeHelper.iterator(target);

		while (attrs.hasNext()) {

			attrTrg = (FeatureAttributeDescriptor) attrs.next();

			if (original.getAttributeDescriptor(attrTrg.getName()) == null) {

				toAdd.add(getSqlStatementAddField(attrTrg,

								additionalStatement));

			}

		}

		StringBuilder sqlb = new StringBuilder();

		sqlb.append("ALTER TABLE ");

		sqlb.append(getJDBCParameters().tableID());

		sqlb.append(' ');

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

		actions.addAll(toDrop);

		actions.addAll(toAlter);

		actions.addAll(toAdd);

		Iterator<String> it = actions.iterator();

		while (it.hasNext()) {

			if (it.next() == null) {

				it.remove();

			}

		}

		it = additionalStatement.iterator();

		while (it.hasNext()) {

			if (it.next() == null) {

				it.remove();

			}

		}

		if (actions.size() < 1) {

			return;

		}

		helper.stringJoin(actions, ", ", sqlb);

		String sql = sqlb.toString();

		Statement st = null;

		try {

			st = conn.createStatement();

		} catch (SQLException e1) {

			throw new JDBCSQLException(e1,sql);

		}

		try {

			JDBCHelper.execute(st, sql);

			Iterator<String> iter = additionalStatement.iterator();

			while (iter.hasNext()) {

				sql = (String) iter.next();

                                JDBCHelper.execute(st, sql);

			}

		} catch (SQLException e1) {

			throw new JDBCExecuteSQLException(sql, e1);

		} finally {

			try {

				st.close();

			} catch (Exception e) {

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

			}

			;

		}

	}

	public void perfomInsert(Connection conn, PreparedStatement insertSt,

			String sql, FeatureProvider feature, List<FeatureAttributeDescriptor> attributes)

			throws DataException {

		try {

			insertSt.clearParameters();

			List<Object> values = new ArrayList<Object>();

			addToListFeatureValues(feature, attributes, values);

//			FeatureAttributeDescriptor attr;

			int j = 1;

			for (int i = 0; i < values.size(); i++) {

				insertSt.setObject(j, values.get(i));

				j++;

			}

			if (logger.isDebugEnabled()) {

				logger.debug("Executing insert. sql={} value={}", new Object[] {

						sql, values });

			}

			try {

                                JDBCHelper.execute(insertSt, sql);

			} catch (SQLException e) {

				throw new JDBCExecutePreparedSQLException(sql, values, e);

			}

		} catch (Exception e1) {

			throw new JDBCExecuteSQLException(sql, e1);

		}

	}

	protected void prepareAttributeForUpdate(FeatureAttributeDescriptor attr,

			List<String> values) {

		values.add(helper.escapeFieldName(attr.getName()) + " = ?");

	}

	protected void prepareAttributeForInsert(FeatureAttributeDescriptor attr,

			List<String> fields, List<String> values) {

		fields.add(helper.escapeFieldName(attr.getName()));

		values.add("?");

	}

	protected void prepareSQLAndAttributeListForInsert(StringBuilder sqlb,

			List<FeatureAttributeDescriptor> attributes) throws DataException {

		/*

		 * INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES (

		 * { expression | DEFAULT } [, ...] ) [, ...] | query } [ RETURNING * |

		 * output_expression [ AS output_name ] [, ...] ]

		 */

		sqlb.append("INSERT INTO ");

		sqlb.append(getJDBCParameters().tableID());

		sqlb.append(" (");

		FeatureType type = this.getFeatureStore().getDefaultFeatureType();

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

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

		Iterator<FeatureAttributeDescriptor> iter = FeatureTypeHelper.iterator(type);

		FeatureAttributeDescriptor attr;

		while (iter.hasNext()) {

			attr = iter.next();

			if (attr.isAutomatic() || attr.isReadOnly()) {

				continue;

			}

			attributes.add(attr);

			prepareAttributeForInsert(attr, fields, values);

		}

		if (attributes.size() < 1) {

			throw new RuntimeException("no fields to set");

		}

		helper.stringJoin(fields, ", ", sqlb);

		sqlb.append(") VALUES (");

		helper.stringJoin(values, ", ", sqlb);

		sqlb.append(") ");

	}

	protected void performInserts(Connection conn, Iterator<FeatureProvider> inserteds)

			throws DataException {

		StringBuilder sqlb = new StringBuilder();

		List<FeatureAttributeDescriptor> attrs = new ArrayList<FeatureAttributeDescriptor>();

		prepareSQLAndAttributeListForInsert(sqlb, attrs);

		String sql = sqlb.toString();

		PreparedStatement st;

		try {

			st = conn.prepareStatement(sql);

		} catch (SQLException e) {

			throw new JDBCPreparingSQLException(sql, e);

		}

		try {

			while (inserteds.hasNext()) {

				perfomInsert(conn, st, sql, inserteds.next(),

						attrs);

			}

		} finally {

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

		}

	}

	protected void performUpdates(Connection conn, Iterator<FeatureProvider> updateds,

			List<FeatureAttributeDescriptor> pkAttributes) throws DataException {

		/*

		 * UPDATE [ ONLY ] table [ [ AS ] alias ] SET { column = { expression |

		 * DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] )

		 * } [, ...] [ FROM fromlist ] [ WHERE condition ] [ RETURNING * |

		 * output_expression [ AS output_name ] [, ...] ]

		 */

		if (pkAttributes.size() < 0) {

			throw new RuntimeException("Operation requires missing pk");

		}

		// ************ Prepare SQL ****************

		StringBuilder sqlb = new StringBuilder();

		sqlb.append("UPDATE ");

		sqlb.append(getJDBCParameters().tableID());

		sqlb.append(" SET ");

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

		FeatureType type = this.getFeatureStore().getDefaultFeatureType();

		Iterator<FeatureAttributeDescriptor> iter = FeatureTypeHelper.iterator(type);

		FeatureAttributeDescriptor attr;

		List<FeatureAttributeDescriptor> updateAttrs = new ArrayList<FeatureAttributeDescriptor>();

		while (iter.hasNext()) {

			attr = iter.next();

			if (attr.isPrimaryKey() || attr.isAutomatic() || attr.isReadOnly()) {

				continue;

			}

			updateAttrs.add(attr);

			prepareAttributeForUpdate(attr, values);

		}

		if (updateAttrs.size() < 1) {

			throw new RuntimeException("no fields to set");

		}

		helper.stringJoin(values, ", ", sqlb);

		sqlb.append(' ');

		appendToSQLPreparedPkWhereClause(sqlb, pkAttributes);

		String sql = sqlb.toString();

		// ************ Prepare SQL (end) ****************

		updateAttrs.addAll(pkAttributes);

		executeUpdatePreparedStatement(conn, sql, updateAttrs, updateds);

	}

	protected TransactionalAction getPerformChangesAction(

			final Iterator<FeatureReferenceProviderServices> deleteds, 

			final Iterator<FeatureProvider> inserteds,

			final Iterator<FeatureProvider> updateds, 

			final Iterator<FeatureTypeChanged> featureTypesChanged) {

		TransactionalAction action = new TransactionalAction() {

			public Object action(Connection conn) throws DataException {

				if (featureTypesChanged.hasNext()) {

					FeatureTypeChanged item = featureTypesChanged.next();

					performUpdateTable(conn, item.getSource(), item.getTarget());

				}

				List<FeatureAttributeDescriptor> pkAttributes = null;

				if (deleteds.hasNext() || updateds.hasNext()) {

					pkAttributes = Arrays.asList(getFeatureStore()

							.getDefaultFeatureType()

							.getPrimaryKey());

				}

				if (deleteds.hasNext()) {

					performDeletes(conn, deleteds, pkAttributes);

				}

				if (updateds.hasNext()) {

					performUpdates(conn, updateds, pkAttributes);

				}

				if (inserteds.hasNext()) {

					performInserts(conn, inserteds);

				}

				return null;

			}

			public boolean continueTransactionAllowed() {

				return false;

			}

		};

		return action;

	}

	@SuppressWarnings("unchecked")

	public void performChanges(Iterator deleteds, Iterator inserteds,

			Iterator updateds, Iterator featureTypesChanged)

			throws PerformEditingException {

		boolean countChanged = deleteds.hasNext() || inserteds.hasNext();

		try {

			this.helper.doConnectionAction(getPerformChangesAction(deleteds,

					inserteds, updateds, featureTypesChanged));

			/*

			 * Get rules before initializing feature type

			 */

			FeatureRules old_rules = getFeatureStore().getDefaultFeatureType().getRules();

			/*

			 * This initialization loses the feature type rules

			 */

			this.initFeatureType();

			/*

			 * Get new feature type, clear rules and add

			 * the ones saved previously

			 */

			FeatureType ft = getFeatureStore().getDefaultFeatureType();

			FeatureRules new_rules = ft.getRules();

			new_rules.clear();

			for (int i=0; i<old_rules.size(); i++) {

			    new_rules.add(old_rules.getRule(i));

			}

			// ===================================================

			if (countChanged) {

				resetCount();

			}

		} catch (Exception e) {

			throw new PerformEditingException(this.getSourceId().toString(), e);

		}

	}

	public boolean allowWrite() {

		if (directSQLMode) {

			return false;

		}

        FeatureType ft = null;

        try {

            ft = this.getFeatureStore().getDefaultFeatureType();

        } catch (DataException e) {

            logger.error("While getting default Feature Type", e);

        }

        String[] storePK = getPK(ft);

        JDBCHelper helper = this.getHelper();

        String[] dbPK = null;

        try {

            Connection conn = helper.getConnection();

            dbPK = helper.getPksFrom(conn, getJDBCParameters());

            conn.close();

        } catch (Exception exc) {

            logger.error("While getting PK from DB", exc);

            return false;

        }

        return sameStrings(storePK, dbPK);

	}

    private boolean sameStrings(String[] a, String[] b) {

        if (a==null || b==null || a.length!=b.length || a.length==0) {

            /*

             * Must not be empty

             */

            return false;

        }

        int len = a.length;

        boolean found = false;

        /*

         * Check same 

         */

        for (int ai=0; ai<len; ai++) {

            found = false;

            for (int bi=0; bi<len; bi++) {

                if (b[bi].compareTo(a[ai]) == 0) {

                    found = true;

                    break;

                }

            }

            if (! found) {

                return false;

            }

        }

        return true;

    }

    private String[] getPK(FeatureType ft) {

        List resp = new ArrayList();

        FeatureAttributeDescriptor attr;

        Iterator<FeatureAttributeDescriptor> iter = FeatureTypeHelper.iterator(ft);

        while (iter.hasNext()) {

            attr = (FeatureAttributeDescriptor) iter.next();

            if (attr.isPrimaryKey()) {

                resp.add(attr.getName());

            }

        }

        return (String[]) resp.toArray(new String[0]);

    }

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

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

 *

 * For any additional information, do not hesitate to contact us at info AT

 * gvsig.com, or visit our website www.gvsig.com.

 */

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

import org.gvsig.fmap.dal.DALLibrary;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.resource.db.DBResourceLibrary;

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

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

import org.gvsig.fmap.dal.store.db.DBHelper;

import org.gvsig.fmap.dal.store.db.DBStoreLibrary;

import org.gvsig.metadata.exceptions.MetadataException;

import org.gvsig.tools.library.AbstractLibrary;

import org.gvsig.tools.library.LibraryException;

/**

 * Initialize JDBC Provider Library

 *

 */

public class JDBCLibrary extends AbstractLibrary {

    public static final String NAME = "JDBC";

    @Override

    public void doRegistration() {

        registerAsServiceOf(DALLibrary.class);

        require(DBStoreLibrary.class);

        require(DBResourceLibrary.class);

    }

    @Override

    protected void doInitialize() throws LibraryException {

    }

    @Override

    protected void doPostInitialize() throws LibraryException {

        LibraryException ex = null;

        DBHelper.registerParametersDefinition(

                NAME + "StoreParameters",

                JDBCStoreParameters.class,

                NAME + "Parameters.xml"

        );

        DBHelper.registerParametersDefinition(

                NAME + "NewStoreParameters",

                JDBCNewStoreParameters.class,

                NAME + "Parameters.xml"

        );

        DBHelper.registerParametersDefinition(

                NAME + "ServerExplorerParameters",

                JDBCServerExplorerParameters.class,

                NAME + "Parameters.xml"

        );

        DBHelper.registerParametersDefinition(

                NAME + "ResourceParameters",

                JDBCResourceParameters.class,

                NAME + "Parameters.xml"

        );

        try {

            DBHelper.registerMetadataDefinition(

                    NAME,

                    JDBCStoreProvider.class,

                    NAME + "Metadata.xml"

            );

        } catch (MetadataException e) {

            ex = new LibraryException(this.getClass(), e);

        }

        ResourceManagerProviderServices resman = (ResourceManagerProviderServices) DALLocator

                .getResourceManager();

        if (!resman.getResourceProviders().contains(NAME)) {

            resman.register(

                    JDBCResource.NAME,

                    JDBCResource.DESCRIPTION,

                    JDBCResource.class,

                    JDBCResourceParameters.class

            );

        }

        DataManagerProviderServices dataman = (DataManagerProviderServices) DALLocator

                .getDataManager();

        if (!dataman.getStoreProviders().contains(NAME)) {

            dataman.registerStoreProviderFactory(new JDBCStoreProviderFactory());

        }

        if (!dataman.getServerExplorerRegister().exits(JDBCStoreProvider.NAME)) {

            dataman.registerServerExplorerFactory(new JDBCServerExplorerFactory());

        }

        if (ex != null) {

            throw ex;

        }

    }

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

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

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

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

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

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Iterator;

import java.util.List;

import java.util.NoSuchElementException;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

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

import org.gvsig.fmap.dal.feature.FeatureQueryOrder.FeatureQueryOrderMember;

import org.gvsig.fmap.dal.feature.spi.AbstractFeatureSetProvider;

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.evaluator.EvaluatorFieldValue;

import org.gvsig.tools.evaluator.EvaluatorFieldsInfo;

import org.gvsig.tools.exception.BaseException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * @author jmvivo

 *

 */

public class JDBCSetProvider extends AbstractFeatureSetProvider {

	final static private Logger logger = LoggerFactory

			.getLogger(JDBCSetProvider.class);

	protected String filter;

	protected String order;

	protected Long size = null;

	protected Boolean isEmpty = null;

	protected List resultSetIDReferenced;

	private JDBCHelper helper = null;

	private int defaultFetchSize = 1000;

	private long limit = -1l;

    private boolean directSQLMode;

	public JDBCSetProvider(JDBCStoreProvider store, FeatureQuery query,

			FeatureType featureType) throws DataException {

		super(store, query, featureType);

		this.helper = store.getHelper();

		this.resultSetIDReferenced = new ArrayList();

		this.directSQLMode =  store.isDirectSQLMode();

		if (query.hasFilter() && this.canFilter()) {

			setFilter(query.getFilter());

		} else {

			setFilter(null);

		}

		if (query.hasOrder() && canOrder()) {

			setOrder(query.getOrder());

		} else {

			setOrder(null);

		}

		if (query != null) {

			limit = query.getLimit();

		}

	}

	/**

	 * @return the defaultFetchSize

	 */

	public int getDefaultFetchSize() {

		return defaultFetchSize;

	}

	/**

	 * @param defaultFetchSize

	 *            the defaultFetchSize to set

	 */

	public void setDefaultFetchSize(int defaultFetchSize) {

		this.defaultFetchSize = defaultFetchSize;

	}

	protected String getSqlForEvaluator(Evaluator filter) {

		if (filter == null) {

			return null;

		}

		EvaluatorFieldsInfo info = filter.getFieldsInfo();

		String filterString = filter.getSQL();

		if (info == null) {

			return filterString;

		}

		String[] filterNames = info.getFieldNames();

		String[] finalNames = new String[filterNames.length];

		EvaluatorFieldValue[] fValues;

		List values = new ArrayList();

		FeatureAttributeDescriptor attr;

		for (int i = 0; i < filterNames.length; i++) {

			attr = getFeatureType().getAttributeDescriptor(filterNames[i]);

			if (attr == null) {

				finalNames[i] = filterNames[i];

				continue;

			}

			finalNames[i] = getEscapedFieldName(attr.getName());

		}

		for (int i = 0; i < filterNames.length; i++) {

			if (!filterNames[i].equals(finalNames[i])) {

				filterString = filterString.replaceAll(

						"\\b" + filterNames[i] + "\\b",

						finalNames[i]);

			}

		}

		return filterString;

	}

	protected String getEscapedFieldName(String fieldName) {

		if (helper == null) {

			helper = getJDBCStoreProvider().getHelper();

		}

		return helper.escapeFieldName(fieldName);

	}

	protected void setOrder(FeatureQueryOrder order) {

		if (order == null || order.size() == 0) {

			this.order = null;

			return;

		}

		StringBuilder buffer = new StringBuilder();

		Iterator iter = order.iterator();

		FeatureQueryOrderMember menber;

		while (true) {

			menber = (FeatureQueryOrderMember) iter.next();

			if (menber.hasEvaluator()) {

				buffer.append(getSqlForEvaluator(menber.getEvaluator()));

			} else {

				buffer.append(getEscapedFieldName(menber.getAttributeName()));

			}

			if (menber.getAscending()) {

				buffer.append(" ASC");

			} else {

				buffer.append(" DESC");

			}

			if (iter.hasNext()) {

				buffer.append(", ");

			} else {

				buffer.append(' ');

				break;

			}

		}

		this.order = buffer.toString();

	}

	protected void setFilter(Evaluator filter) {

		this.filter = getSqlForEvaluator(filter);

	}

	/* (non-Javadoc)

	 * @see org.gvsig.fmap.dal.feature.spi.FeatureSetProvider#canFilter()

	 */

	public boolean canFilter() {

		Evaluator filter = getQuery().getFilter();

		if (directSQLMode){

		    return false;

		}

		if (filter != null) {

			if (filter.getSQL() == null || filter.getSQL().length() == 0) {

				return false;

			} else {

				// TODO Check Geom fields if

				EvaluatorFieldsInfo fInfo = filter.getFieldsInfo();

				if (fInfo == null || fInfo.getFieldNames() == null) {

					return true;

				}

				Iterator names = Arrays.asList(fInfo.getFieldNames())

						.iterator();

				String name;

				int type;

				while (names.hasNext()) {

					name = (String) names.next();

					type =

							this.getFeatureType()

									.getAttributeDescriptor(name)

							.getType();

					if (type == DataTypes.GEOMETRY

							&& !this.helper.supportsGeometry()) {

						return false;

					}

				}

				return true;

			}

		} else{

			return false;

		}

	}

	/* (non-Javadoc)

	 * @see org.gvsig.fmap.dal.feature.spi.FeatureSetProvider#canIterateFromIndex()

	 */

	public boolean canIterateFromIndex() {

		return helper.supportOffset();

	}

	/* (non-Javadoc)

	 * @see org.gvsig.fmap.dal.feature.spi.FeatureSetProvider#canOrder()

	 */

	public boolean canOrder() {

	    if (directSQLMode){

	        return false;

	    }

		// TODO Check Geom fields if postgis not are available

		FeatureQuery query = getQuery();

		if (query.hasOrder()) {

			Iterator iter = query.getOrder().iterator();

			FeatureQueryOrderMember menber;

			String sql;

			while (iter.hasNext()){

				menber = (FeatureQueryOrderMember) iter.next();

				if (menber.hasEvaluator()){

					sql = menber.getEvaluator().getSQL();

					if (sql == null || sql.length() == 0) {

						return false;

					}

				}

			}

		}

		return true;

	}

	private JDBCStoreProvider getJDBCStoreProvider() {

		return (JDBCStoreProvider) getStore();

	}

	@Override

	protected void doDispose() throws BaseException {

		if (resultSetIDReferenced != null) {

			Iterator iter = resultSetIDReferenced.iterator();

			Integer resID;

			while (iter.hasNext()) {

				resID = (Integer) iter.next();

				if (resID != null) {

					logger.warn(

						"ResultSet (ID {}) not closed on dispose, will close",

						resID);

					try {

						getJDBCStoreProvider().closeResulset(resID.intValue());

					} catch (DataException e) {

						logger.error("Close resulset Exception", e);

					}

				}

				iter.remove();

			}

		}

		resultSetIDReferenced = null;

		filter = null;

		order = null;

		size = null;

		isEmpty = null;

	}

	protected String getSQL(long fromIndex) throws DataException {

		return getJDBCStoreProvider().compoundSelect(getFeatureType(), filter,

				order, limit, fromIndex);

	}

	/* (non-Javadoc)

	 * @see org.gvsig.fmap.dal.feature.spi.FeatureSetProvider#getSize()

	 */

	public long getSize() throws DataException {

		if (size == null) {

			size = new Long(getJDBCStoreProvider().getCount(filter));

		}

		return size.longValue();

	}

	/* (non-Javadoc)

	 * @see org.gvsig.fmap.dal.feature.spi.FeatureSetProvider#isEmpty()

	 */

	public boolean isEmpty() throws DataException {

		JDBCStoreProvider store = getJDBCStoreProvider();

		if (isEmpty == null) {

			if (size == null) {

				String sql =

						store.compoundSelect(getFeatureType(), filter, null, 1,

								0);

				int rsID = store.createResultSet(sql, getFetchSize());

				isEmpty = new Boolean(!store.resulsetNext(rsID));

				store.closeResulset(rsID);

			} else {

				isEmpty = new Boolean(size.longValue() < 1);

			}

		}

		return isEmpty.booleanValue();

	}

	protected int getFetchSize() {

		long pageSize = -1;

		if (getQuery() != null) {

			pageSize = getQuery().getPageSize();

			pageSize =

					pageSize > Integer.MAX_VALUE ? Integer.MAX_VALUE : pageSize;

		}

		return (pageSize > 0 ? (int) pageSize : defaultFetchSize);

	}

	protected JDBCIterator createFastIterator(long index) throws DataException {

		if (isEmpty != null && isEmpty.booleanValue()) {

			return new EmptyJDBCIterator(getJDBCStoreProvider());

		}

		int rsID =

				getJDBCStoreProvider().createResultSet(getSQL(index),

						getFetchSize());

		return createDefaultFastIterator(rsID);

	}

	protected JDBCIterator createDefaultFastIterator(int resultSetID)

			throws DataException {

		return new JDBCFastIterator(getJDBCStoreProvider(), this,

				getFeatureType(), resultSetID);

	}

	protected JDBCIterator createIterator(long index) throws DataException {

        if (isEmpty != null && isEmpty.booleanValue()) {

            return new EmptyJDBCIterator(getJDBCStoreProvider());

        }

        int rsID =

                getJDBCStoreProvider().createResultSet(getSQL(index),

                        getFetchSize());

        return createDefaultIterator(rsID);

	}

	protected JDBCIterator createDefaultIterator(int resultSetID)

			throws DataException {

		return new JDBCIterator(getJDBCStoreProvider(), this, getFeatureType(),

				resultSetID);

	}

	public void addResulsetReference(int resulsetID) {

		this.resultSetIDReferenced.add(new Integer(resulsetID));

	}

	public void removeResulsetReference(int resulsetID) {

		this.resultSetIDReferenced.remove(new Integer(resulsetID));

	}

	private class EmptyJDBCIterator extends JDBCIterator {

		protected EmptyJDBCIterator(JDBCStoreProvider store) throws DataException {

			super(store, null, null, -1);

		}

		@Override

		protected boolean internalHasNext() {

			return false;

		}

		@Override

		protected Object internalNext() {

			throw new NoSuchElementException();

		}

		@Override

		protected void doDispose() throws BaseException {

			// nothing to do

		}

	}

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

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

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

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

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

import java.security.InvalidParameterException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Collections;

import java.util.Iterator;

import java.util.List;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataStore;

import org.gvsig.fmap.dal.DataStoreNotification;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

import org.gvsig.fmap.dal.feature.spi.AbstractFeatureStoreProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices;

import org.gvsig.fmap.dal.feature.spi.FeatureSetProvider;

import org.gvsig.fmap.dal.resource.ResourceAction;

import org.gvsig.fmap.dal.resource.exception.ResourceExecuteException;

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

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

import org.gvsig.fmap.dal.store.db.DBHelper;

import org.gvsig.fmap.dal.store.db.FeatureTypeHelper;

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

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

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

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.primitive.Envelope;

import org.gvsig.tools.dynobject.DynObject;

import org.gvsig.tools.dynobject.exception.DynFieldNotFoundException;

import org.gvsig.tools.exception.BaseException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * @author jmvivo

 *

 */

public class JDBCStoreProvider extends AbstractFeatureStoreProvider

		implements JDBCHelperUser {

	final static private Logger logger = LoggerFactory

			.getLogger(JDBCStoreProvider.class);

	private List<ResultSetInfo> resulsetList;

	public static String NAME = "JDBC";

	public static String DESCRIPTION = "JDBC generic store";

	public static final String METADATA_DEFINITION_NAME = NAME;

	private long mlsecondsToZombie = 1000 * 60 * 10; // 10 Min

	protected JDBCHelper helper;

	protected boolean directSQLMode;

	private Long totalCount = null;

	private GeometryManager geomManager = null;

	public JDBCStoreProvider(JDBCStoreParameters params,

			DataStoreProviderServices storeServices) throws InitializeException {

		this(

				params, 

				storeServices,

				DBHelper.newMetadataContainer(METADATA_DEFINITION_NAME)

		);

	}

	protected JDBCStoreProvider(JDBCStoreParameters params,

			DataStoreProviderServices storeServices, DynObject metadata)

			throws InitializeException {

		super(params, storeServices, metadata);

		geomManager = GeometryLocator.getGeometryManager();

		resulsetList = new ArrayList<ResultSetInfo>(10);

		helper = createHelper();

		if (params.getSQL() != null && (params.getSQL()).trim().length() > 0) {

			directSQLMode = true;

		}

		this.initFeatureType();

	}

	public Object getDynValue(String name) throws DynFieldNotFoundException {

		try {

			if( DataStore.METADATA_ENVELOPE.equalsIgnoreCase(name) ) {

				Envelope env = this.getEnvelope();

				if( env != null ) {

					return env;

				}

			} else if( DataStore.METADATA_CRS.equalsIgnoreCase(name) ) {

				IProjection proj;

				proj = this.getFeatureStore().getDefaultFeatureType().getDefaultSRS();

				if( proj != null ) {

					return proj;

				}

			}

		} catch (DataException e) {

			throw new RuntimeException(e);

		}

		return super.getDynValue(name);

	}

	protected JDBCStoreParameters getJDBCParameters() {

		return (JDBCStoreParameters) this.getParameters();

	}

	/**

	 * Load data form a resulset.<br>

	 *

	 * <strong>Note:</strong><br>

	 * this method have to perform <code>resouceBegin</code> at the begining and

	 * <code>resourceEnd</code> at the end of execution.

	 *

	 *

	 * @param data

	 * @param resulsetID

	 *

	 * @return

	 * @throws DataException

	 */

	public void loadFeatureProvider(final FeatureProvider data, final int resultsetID)

			throws DataException {

		getResource().execute(new ResourceAction() {

			public Object run() throws Exception {

				ResultSet rs = getResultSet(resultsetID);

				FeatureAttributeDescriptor attr;

				Iterator<FeatureAttributeDescriptor> iter = FeatureTypeHelper.iterator(data.getType());

				while (iter.hasNext()) {

					attr = iter.next();

					loadFeatureProviderValue(data, rs, attr);

				}

				return null;

			}

                        public String toString() {

                            return "loadFeature";

                        }

		});

	}

	protected void loadFeatureProviderValue(FeatureProvider data, ResultSet rs,

			FeatureAttributeDescriptor attr) throws DataException {

		if (attr.getType() == DataTypes.GEOMETRY) {

			byte[] buffer;

			try {

				buffer = rs.getBytes(attr.getIndex() + 1);

				if (buffer == null) {

					data.set(attr.getIndex(), null);

				} else {

					data.set(attr.getIndex(), this.helper.getGeometry(buffer));

				}

			} catch (SQLException e) {

				throw new JDBCSQLException(e);