| gvSIG desktop 1

/* 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.postgresql;

import java.security.InvalidParameterException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.operation.towkb.ToWKB;

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

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dynobject.DelegatedDynObject;

import org.gvsig.tools.dynobject.DynClass;

import org.gvsig.tools.dynobject.DynObjectManager;

import org.gvsig.tools.exception.BaseException;

import org.gvsig.tools.persistence.PersistenceException;

import org.gvsig.tools.persistence.PersistentState;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class PostgreSQLStoreProvider extends JDBCStoreProvider implements

                PostgreSQLHelperUser {

        final static private Logger logger = LoggerFactory

                        .getLogger(PostgreSQLStoreProvider.class);

        public static String NAME = "PostgreSQLStore";

        public static String DESCRIPTION = "PostgreSQL source";

        private static final String DYNCLASS_NAME = "PostgreSQLStore";

        private static DynClass DYNCLASS = null;

        protected PostgreSQLStoreParameters params;

        protected PostgreSQLHelper helper;

        protected boolean directSQLMode;

        private Long totalCount = null;

        protected static void registerDynClass() {

                DynObjectManager dynman = ToolsLocator.getDynObjectManager();

                DynClass dynClass;

                if (DYNCLASS == null) {

                        dynClass = dynman.add(DYNCLASS_NAME, DESCRIPTION);

                        dynClass.extend(dynman.get(FeatureStore.DYNCLASS_NAME));

                        DYNCLASS = dynClass;

                }

        }

        public PostgreSQLStoreProvider() {

                super();

        }

        public PostgreSQLStoreProvider(PostgreSQLStoreParameters params)

                        throws InitializeException {

                super();

                this.init(params);

        }

        protected void init(PostgreSQLStoreParameters params)

                        throws InitializeException {

                this.params = params;

                this.dynObject = (DelegatedDynObject) ToolsLocator

                                .getDynObjectManager().createDynObject(DYNCLASS);

                this.dynObject.setDynValue("DefaultSRS", null);

                this.dynObject.setDynValue("Envelope", null);

                helper = new PostgreSQLHelper(this, params);

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

                        directSQLMode = true;

                }

        }

        protected String compoundCountSelect(String filter) {

                if (this.directSQLMode) {

                        return null;

                }

                // Select

                StringBuilder sql = new StringBuilder();

                sql.append("Select count(");

                String[] pkFields = params.getPkFields();

                if (pkFields != null && pkFields.length == 1) {

                        sql.append(helper.escapeFieldName(pkFields[0]));

                } else {

                        sql.append('*');

                }

                sql.append(") ");

                sql.append("from ");

                sql.append(params.tableID());

                sql.append(' ');

                appendWhere(sql, filter);

                return sql.toString();

        }

        private void appendWhere(StringBuilder sql, String filter) {

                filter = fixFilter(filter);

                String initialFilter = params.getInitialFilter();

                if ((initialFilter != null && initialFilter.length() != 0)

                                || (filter != null && filter.length() != 0)) {

                        sql.append("where (");

                        if (initialFilter != null && initialFilter.length() != 0

                                        && filter != null && filter.length() != 0) {

                                // initialFilter + filter

                                sql.append('(');

                                sql.append(initialFilter);

                                sql.append(") and (");

                                sql.append(filter);

                                sql.append(')');

                        } else if (initialFilter != null && initialFilter.length() != 0) {

                                // initialFilter only

                                sql.append(initialFilter);

                        } else {

                                // filter only

                                sql.append(filter);

                        }

                        sql.append(") ");

                }

        }

        private String fixFilter(String filter) {

                if (filter == null) {

                        return null;

                }

                // Transform SRS to code

                // GeomFromText\s*\(\s*'[^']*'\s*,\s*('[^']*')\s*\)

                Pattern pattern = Pattern

                                .compile("GeomFromText\\s*\\(\\s*'[^']*'\\s*,\\s*'([^']*)'\\s*\\)");

                Matcher matcher = pattern.matcher(filter);

                StringBuilder strb = new StringBuilder();

                int pos = 0;

                String srsCode;

                while (matcher.find(pos)) {

                        strb.append(filter.substring(pos, matcher.start(1)));

                        srsCode = matcher.group(1).trim();

                        if (srsCode.startsWith("'")) {

                                srsCode = srsCode.substring(1);

                        }

                        if (srsCode.endsWith("'")) {

                                srsCode = srsCode.substring(0, srsCode.length() - 1);

                        }

                        strb.append(helper.getPostgisSRID(srsCode));

                        strb.append(filter.substring(matcher.end(1), matcher.end()));

                        pos = matcher.end();

                }

                strb.append(filter.substring(pos));

                return strb.toString();

        }

        public String compoundSelect(FeatureType type,

                        String filter,

                        String order,

                        long limit, long offset) throws DataException {

                StringBuilder sql = new StringBuilder();

                if (directSQLMode) {

                        if (filter != null || order != null) {

                                // FIXME Exception

                                throw new UnsupportedOperationException();

                        }

                        sql.append(params.getSQL());

                        sql.append(' ');

                } else {

                        FeatureAttributeDescriptor[] fields = type

                                        .getAttributeDescriptors();

                        // Select

                        sql.append("Select ");

                        for (int i = 0; i < fields.length - 1; i++) {

                                sql.append(helper.getSqlFieldName(fields[i]));

                                sql.append(", ");

                        }

                        sql.append(helper.getSqlFieldName(fields[fields.length - 1]));

                        sql.append(' ');

                        FeatureAttributeDescriptor[] pkFields = store.getFeatureType(type.getId()).getPrimaryKey();

                        if (pkFields != null && pkFields.length > 0) {

                                // checks for pk fields are in select

                                boolean toAdd;

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

                                        toAdd = true;

                                        for (int j = 0; j < fields.length; j++) {

                                                if (pkFields[i].getName().equals(fields[j].getName())) {

                                                        toAdd = false;

                                                        break;

                                                }

                                                if (toAdd) {

                                                        sql.append(", ");

                                                        sql.append(helper.getSqlFieldName(pkFields[i]));

                                                }

                                        }

                                }

                                sql.append(' ');

                        }

                        // table

                        sql.append("from ");

                        sql.append(params.tableID());

                        sql.append(' ');

                        // Where

                        appendWhere(sql, filter);

                        // Order

                        if ((params.getInitialOrder() != null && params

                                        .getInitialOrder().length() != 0)

                                        || (order != null && order.length() != 0)) {

                                sql.append("order by ");

                                if (order != null && order.length() != 0) {

                                        // order

                                        sql.append(order);

                                } else {

                                        // initial order

                                        sql.append(params.getInitialOrder());

                                }

                                sql.append(' ');

                        }

                }

                // limit

                if (limit >= 1) {

                        sql.append("limit ");

                        sql.append(limit);

                        sql.append(' ');

                }

                // offset

                if (offset >= 1) {

                        sql.append("offset ");

                        sql.append(offset);

                        sql.append(' ');

                }

                return sql.toString();

        }

        public FeatureStoreProvider initialize(FeatureStoreProviderServices store)

                        throws InitializeException {

                super.initialize(store);

                this.initFeatureType();

                return this;

        }

        protected void initFeatureType() throws InitializeException {

                EditableFeatureType edFType = null;

                try {

                        edFType = this.store.createFeatureType();

                        helper.loadFeatureType(edFType, params);

                } catch (DataException e) {

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

                }

                FeatureType defaultType = edFType.getNotEditableCopy();

                List types = new ArrayList(1);

                types.add(defaultType);

                this.store.setFeatureTypes(types, defaultType);

                try {

                        loadMetadata();

                } catch (DataException e) {

                        throw new InitializeException(e);

                }

        }

        public Object createNewOID() {

                return null;

        }

        public FeatureData getFeatureDataByReference(

                        FeatureReferenceProviderServices reference) throws DataException {

                return getFeatureDataByReference(reference, store

                                .getDefaultFeatureType());

        }

        public FeatureData getFeatureDataByReference(

                        FeatureReferenceProviderServices reference, FeatureType featureType)

                        throws DataException {

                open();

                resourceBegin();

                try {

                        StringBuilder filter = new StringBuilder();

                        FeatureAttributeDescriptor[] pk = store.getFeatureType(

                                        featureType.getId()).getPrimaryKey();

                        List values = new ArrayList();

                        int i;

                        for (i = 0; i < pk.length - 1; i++) {

                                values.add(helper.dalValueToJDBC(pk[i], reference

                                                .getKeyValue(pk[i].getName())));

                                filter.append(helper.getSqlFieldName(pk[i]));

                                filter.append(" = ? AND ");

                        }

                        values.add(helper.dalValueToJDBC(pk[i], reference.getKeyValue(pk[i]

                                        .getName())));

                        filter.append(helper.getSqlFieldName(pk[i]));

                        filter.append(" = ? ");

                        String sql = compoundSelect(featureType, filter.toString(), null,

                                        1, 0);

                        FeatureData data;

                        int rsId = createResultSet(sql, values.toArray());

                        try{

                                if (!resulsetNext(rsId)) {

                                        // FIXME Exception

                                        throw new RuntimeException("Reference Not found");

                                }

                                data = createFeatureData(featureType);

                                loadFeatureData(data, rsId);

                        } finally {

                                closeResulset(rsId);

                        }

                        return data;

                } finally {

                        resourceEnd();

                }

        }

        public int getFeatureReferenceOIDType() {

                return DataTypes.UNKNOWN;

        }

        public String getName() {

                return NAME;

        }

        public Iterator getChilds() {

                return null;

        }

        public void open() throws OpenException {

                helper.open();

        }

        public boolean allowWrite() {

                return false;

        }

        public void close() throws CloseException {

                helper.close();

        }

        public Object getSourceId() {

                return this.params.getSourceId();

        }

        protected ResultSet createNewResultSet(String sql, Object[] values)

                        throws DataException {

                this.open();

                Connection conn =null;

                PreparedStatement st=null;

                ResultSet rs=null;

                this.resourceBegin();

                try{

                        conn = this.helper.getConnection();

                        st = conn.prepareStatement(sql);

                        if (values != null) {

                                Object value;

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

                                        value = values[i];

                                        if (value instanceof Geometry) {

                                                byte[] bytes;

                                                try {

                                                        bytes = (byte[]) ((Geometry) value)

                                                                        .invokeOperation(ToWKB.CODE, null);

                                                } catch (BaseException e) {

                                                        // FIXME

                                                        throw new InvalidParameterException();

                                                }

                                                st.setBytes(i + 1, bytes);

                                        }

                                        st.setObject(i + 1, value);

                                }

                        }

                        try {

                                rs = st.executeQuery();

                        } catch (SQLException e1){

                                try {st.close();  } catch (Exception e2) {        };

                                try {conn.close();} catch (Exception e2) {        };

                                throw new JDBCExecuteSQLException(sql,e1);

                        }

                        rs.setFetchSize(5000); // TODO add to params?

                        return rs;

                } catch (SQLException e) {

                        // TODO throw exception ???

                        try {rs.close();  } catch (Exception e1) {        };

                        try {st.close();  } catch (Exception e1) {        };

                        try {conn.close();} catch (Exception e1) {        };

                        throw new JDBCSQLException(e);

                }finally{

                        this.resourceEnd();

                }

        }

        protected long getCount(String filter) throws DataException {

                this.open();

                if (filter == null && totalCount != null) {

                        return totalCount.longValue();

                }

                long count = 0;

                String sql = compoundCountSelect(filter);

                resourceBegin();

                try {

                        ResultSet rs = createNewResultSet(sql, null);

                        try {

                                if (rs.next()) {

                                        count = rs.getLong(1);

                                }

                        } catch (SQLException e) {

                                throw new JDBCSQLException(e);

                        } finally {

                                closeResulset(rs);

                        }

                } finally {

                        resourceEnd();

                }

                if (filter == null) {

                        totalCount = new Long(count);

                }

                return count;

        }

        protected void resourceBegin() throws ResourceBeginException {

                this.helper.begin();

        }

        protected void resourceEnd() {

                this.helper.end();

        }

        protected boolean closeResource(ResourceProvider resource) {

                try {

                        this.helper.close();

                } catch (CloseException e) {

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

                }

                return !this.helper.isOpen();

        }

        protected void loadFeatureDataValue(FeatureData data, ResultSet rs,

                        FeatureAttributeDescriptor attr) throws DataException {

                if (attr.getDataType() == 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);

                        } catch (BaseException e) {

                                throw new ReadException(getName(), e);

                        }

                } else {

                        super.loadFeatureDataValue(data, rs, attr);

                }

        }

        public FeatureSetProvider createSet(FeatureQuery query,

                        FeatureType featureType) throws DataException {

                return new PostgreSQLSetProvider(this, query, featureType);

        }

        public void dispose() throws CloseException {

                this.close();

                this.helper.dispose();

                super.dispose();

        }

        public DataServerExplorer getExplorer() throws ReadException {

                DataManager manager = DALLocator.getDataManager();

                PostgreSQLServerExplorerParameters exParams;

                try {

                        exParams = (PostgreSQLServerExplorerParameters) manager

                                        .createServerExplorerParameters(PostgreSQLServerExplorer.NAME);

                        exParams.setHost(params.getHost());

                        exParams.setPort(params.getPort());

                        exParams.setDBName(params.getDBName());

                        exParams.setUser(params.getUser());

                        exParams.setPassword(params.getPassword());

                        exParams.setCatalog(params.getCatalog());

                        exParams.setSchema(params.getSchema());

                        exParams.setJDBCDriverClassName(params.getJDBCDriverClassName());

                        exParams.setUseSSL(params.getUseSSL());

                        return manager.createServerExplorer(exParams);

                } catch (DataException e) {

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

                } catch (ValidateDataParametersException e) {

                        // TODO Auto-generated catch block

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

                }

        }

        private void loadMetadata() throws DataException {

                IProjection srs = params.getSRS();

                if (srs == null) {

                        srs = store.getDefaultFeatureType().getDefaultSRS();

                }

                this.dynObject.setDynValue("DefaultSRS", srs);

                String defGeomName = this.store.getDefaultFeatureType()

                                .getDefaultGeometryAttributeName();

                Envelope env = null;

                if (defGeomName != null && defGeomName.length() > 0) {

                        env = this.helper

                                        .getFullEnvelopeOfField(this.params,

                                        defGeomName,

                                        this.params.getWorkingArea());

                }

                this.dynObject.setDynValue("Envelope", env);

        }

        private void clearMetadata() {

                this.dynObject.setDynValue("DefaultSRS", null);

                this.dynObject.setDynValue("Envelope", null);

        }

        public void closeDone() throws DataException {

                clearMetadata();

        }

        public void opendDone() throws DataException {

                // Nothing to do

        }

        public Envelope getEnvelope() throws DataException {

                this.open();

                return (Envelope) this.dynObject.getDynValue("Envelope");

        }

        public void resourceChanged(ResourceProvider resource) {

                this.store.notifyChange(DataStoreNotification.RESOURCE_CHANGED,

                                resource);

        }

        public boolean canWriteGeometry(int geometryType) throws DataException {

                return false;

        }

        public boolean allowAutomaticValues() {

                return true;

        }

        public void performEditing(Iterator deleteds, Iterator inserteds,

                        Iterator updateds, Iterator originalFeatureTypesUpdated)

                        throws PerformEditingException {

                // FIXME exception

                throw new UnsupportedOperationException();

        }

        protected PostgreSQLHelper getHelper() {

                return helper;

        }

        // ************************************************************************************//

        // ************************************************************************************//

        public boolean supportsAppendMode() {

                // TODO Auto-generated method stub

                return false;

        }

        public PersistentState getState() throws PersistenceException {

                // TODO Auto-generated method stub

                return null;

        }

        public void loadState(PersistentState state) throws PersistenceException {

                // TODO Auto-generated method stub

        }

        public void setState(PersistentState state) throws PersistenceException {

                // TODO Auto-generated method stub

        }

        public void endAppend() throws DataException {

                // TODO Auto-generated method stub

        }

        public void append(Feature feature) throws DataException {

                // TODO Auto-generated method stub

        }

        public void beginAppend() throws DataException {

                // TODO Auto-generated method stub

        }

        public long getFeatureCount() throws DataException {

                return getCount(null);

        }

        /* (non-Javadoc)

         * @see org.gvsig.tools.persistence.Persistent#saveToState(org.gvsig.tools.persistence.PersistentState)

         */

        public void saveToState(PersistentState state) throws PersistenceException {

                // TODO Auto-generated method stub

        }

}