Application: gvSIG desktop

package org.gvsig.data.datastores.vectorial.db.jdbc.postgresql;

import java.io.ByteArrayOutputStream;

import java.io.PrintStream;

import java.io.UnsupportedEncodingException;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Iterator;

import org.gvsig.data.IDataExplorer;

import org.gvsig.data.IDataStoreParameters;

import org.gvsig.data.datastores.vectorial.ISelectiveWriter;

import org.gvsig.data.datastores.vectorial.db.DBAttributeDescriptor;

import org.gvsig.data.datastores.vectorial.db.DBFeatureType;

import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCAttributeDescriptor;

import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCFeature;

import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCFeaturesWriter;

import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCTypes;

import org.gvsig.data.exception.InitializeException;

import org.gvsig.data.exception.InitializeWriterException;

import org.gvsig.data.exception.ReadException;

import org.gvsig.data.exception.WriteException;

import org.gvsig.data.vectorial.IFeature;

import org.gvsig.data.vectorial.IFeatureAttributeDescriptor;

import org.gvsig.data.vectorial.IFeatureStore;

import org.gvsig.data.vectorial.IFeatureType;

import org.gvsig.data.vectorial.INewFeatureStoreParameters;

import com.iver.cit.gvsig.fmap.core.FShape;

import com.iver.cit.gvsig.fmap.core.IGeometry;

import com.vividsolutions.jts.io.WKBWriter;

public class PostgresqlFeaturesWriter extends JDBCFeaturesWriter {

        DBFeatureType featureType;

        boolean bCreateTable=false;

        private String toEncode;

        PostgresqlStoreParameters parameters;

        private PreparedStatement insertSt;

        private PreparedStatement updateSt;

        private static WKBWriter wkbWriter = new WKBWriter();

        PostgresqlFeaturesWriter(){

        }

        public void init(IFeatureStore store) {

                super.init(store);

                PostgresqlStore pgStore = (PostgresqlStore)store;

                this.parameters=pgStore.getParametersPostgresql();

                this.featureType = (DBFeatureType)this.store.getDefaultFeatureType();

        }

        public void preProcess() throws WriteException, ReadException {

                super.preProcess();

                //??????????????????????????

                // ATENTION: We will transform (in PostGIS class; doubleQuote())

        // to UTF-8 strings. Then, we tell the PostgreSQL server

        // that we will use UTF-8, and it can translate

        // to its charset

        // Note: we have to translate to UTF-8 because

        // the server cannot manage UTF-16

                //??????????????????????????

                if (bCreateTable) {

                        IDataExplorer explorer = this.store.getExplorer();

                        IDataStoreParameters params =this.store.getParameters();

                        explorer.remove(params);

                        INewFeatureStoreParameters newParam = (INewFeatureStoreParameters)explorer.createNewDataStoreParameter();

                        newParam.setFeatureType(this.featureType);

                        explorer.add(newParam);

                }

//        ResultSet rsAux;

//                try {

////                                conex.setAutoCommit(false);

////                        alterTable();

//

////                        rsAux = st.executeQuery("SHOW server_encoding;");

////                rsAux.next();

////                String serverEncoding = rsAux.getString(1);

////                System.out.println("Server encoding = " + serverEncoding);

//                // st.execute("SET CLIENT_ENCODING TO 'UNICODE';");

//                // Intentamos convertir nuestras cadenas a ese encode.

////                setEncoding(serverEncoding);

//                } catch (SQLException e) {

//                        throw new InitializeWriterException("H2",e);

//                }

        }

        public void deleteFeature(IFeature feature) throws WriteException {

                Statement st;

                String sqlDelete = getSqlDeleteFeature(featureType, feature);

                System.out.println("sql = " + sqlDelete);

                try {

                        st = this.conex.createStatement();

                        st.execute(sqlDelete);

                } catch (SQLException e) {

                        throw new WriteException(this.store.getName(),e);

                }

        }

        public void insertFeature(IFeature feature) throws WriteException {

                DBFeatureType ftype = (DBFeatureType)feature.getType();

                try {

                        PreparedStatement ps=this.getInsertFeatureStatement(ftype);

                        Iterator it = ftype.iterator();

                        int index= 1;

                        while (it.hasNext()){

                                JDBCAttributeDescriptor fad=(JDBCAttributeDescriptor)it.next();

                                if (fad.isReadOnly())

                                        continue;

                                loadValueInPreparedStatement(ps, index, fad, feature);

                                index++;

                        }

//                        ps.setObject(index, feature.get(ftype.getFieldIdIndex()));

                        ps.execute();

                } catch (SQLException e) {

                        throw new WriteException(this.store.getName(),e);

                }

        }

        private PreparedStatement getInsertFeatureStatement(DBFeatureType ftype) throws SQLException {

                if (this.insertSt == null){

                        StringBuffer fields = new StringBuffer();

                        StringBuffer values = new StringBuffer();

                        StringBuffer sql = new StringBuffer();

                        Iterator iter = ftype.iterator();

                        while (iter.hasNext()){

                                DBAttributeDescriptor fad=(DBAttributeDescriptor)iter.next();

                                String name = fad.getName();

                                if (fad.isReadOnly())

                                        continue;

                                fields.append(name+",");

                                values.append("?,");

                        }

                        sql.append("INSERT INTO "+ftype.getTableID()+" (");

                        sql.append(fields.substring(0, fields.length()-1));

                        sql.append(") VALUES (");

                        sql.append(values.substring(0, values.length()-1));

                        sql.append(")");

                        this.insertSt= this.conex.prepareStatement(sql.toString());

                        System.out.println(sql.toString());

                } else{

                        this.insertSt.clearParameters();

                }

                return this.insertSt;

        }

        public void updateFeatureType(IFeatureType featureType) {

                this.featureType=(DBFeatureType)featureType;

        }

        /**

         * @param createTable

         *            The bCreateTable to set.

         */

        public void setCreateTable(boolean createTable) {

                bCreateTable = createTable;

        }

        boolean dropTableIfExist() throws SQLException{

                if (!this.existTable(parameters.getSchema(), parameters.getTableName())){

                        return false;

                }

                Statement st = conex.createStatement();

                st.execute("DROP TABLE " + parameters.tableID() + ";");

                st.close();

                return false;

        }

        private boolean existTable(String schema, String tableName) throws SQLException{

                boolean exists =false;

                DatabaseMetaData metadata = conex.getMetaData();

                ResultSet rs = metadata.getTables(null, schema, tableName, null);

                exists = !rs.isAfterLast();

                rs.close();

                return exists;

        }

        public void updateFeature(IFeature oldFeature, IFeature feature) throws WriteException, ReadException {

                DBFeatureType ftype = (DBFeatureType)feature.getType();

                try {

                        PreparedStatement ps=this.getUpdateFeatureStatement(ftype);

                        Iterator it = ftype.iterator();

                        int index= 1;

                        while (it.hasNext()){

                                JDBCAttributeDescriptor fad=(JDBCAttributeDescriptor)it.next();

                                if (fad.isPrimaryKey())

                                        continue;

                                loadValueInPreparedStatement(ps, index, fad, feature);

                                index++;

                        }

                        loadPkInPreparedStatement(ps, index, ftype, feature);

                        ps.execute();

                } catch (SQLException e) {

                        throw new WriteException(this.store.getName(),e);

                }

        }

        public void deleteAttribute(IFeatureAttributeDescriptor attribute) throws WriteException {

                try {

                        Statement st = conex.createStatement();

                        String sql = "ALTER TABLE " + parameters.tableID() + " DROP COLUMN "

                                + attribute.getName() + ";";

                        st.execute(sql);

                } catch (SQLException e) {

                        throw new WriteException(this.store.getName(),e);

                }

        }

        public void updateAttribute(IFeatureAttributeDescriptor oldAttribute, IFeatureAttributeDescriptor attribute) throws WriteException, ReadException {

                try {

                        Statement st = conex.createStatement();

                        String sql = "ALTER TABLE " + parameters.tableID() + " RENAME COLUMN "

                        + oldAttribute.getName() + " TO " + attribute.getName() + ";";

                        st.execute(sql);

                } catch (SQLException e) {

                        throw new WriteException(this.store.getName(),e);

                }

        }

        public void insertAttribute(IFeatureAttributeDescriptor attribute) throws WriteException {

                try {

                        Statement st = conex.createStatement();

                        String sql = "ALTER TABLE "

                                + parameters.tableID()

                                + " ADD COLUMN "

                                + attribute.getName()

                                + " "

                                + JDBCTypes.fieldTypeToString(attribute.getDataType())

                                + " "

                                + "DEFAULT " + attribute.getDefaultValue()

                                + ";";

                        st.execute(sql);

                } catch (SQLException e) {

                        throw new WriteException(this.store.getName(),e);

                }

        }

        /** @deprecated ???

        *

        */

        public void setEncoding(String toEncode){

                if (toEncode.compareToIgnoreCase("SQL_ASCII") == 0){

                   this.toEncode = "ASCII";

                  } else {

                          this.toEncode = toEncode;

                  }

        }

        /** @deprecated ???

        *

        */

        public String getEncoding() {

                return toEncode;

        }

        /** @deprecated ???

        *

        */

        public boolean canWriteGeometry(int gvSIGgeometryType) {

                switch (gvSIGgeometryType) {

                case FShape.POINT:

                        return true;

                case FShape.LINE:

                        return true;

                case FShape.POLYGON:

                        return true;

                case FShape.ARC:

                        return false;

                case FShape.ELLIPSE:

                        return false;

                case FShape.MULTIPOINT:

                        return true;

                case FShape.TEXT:

                        return false;

                }

                return false;

        }

        /** @deprecated ???

        *

        */

        protected String addQuotes(Object value) {

                String retString;

                if (value != null) {

                        retString = "'" + doubleQuote(value) + "'";

                } else {

                        retString = "null";

                }

                return retString;

        }

        /** @deprecated ???

        *

        */

        private String doubleQuote(Object obj) {

                String aux = obj.toString().replaceAll("'", "''");

                StringBuffer strBuf = new StringBuffer(aux);

                ByteArrayOutputStream out = new ByteArrayOutputStream(strBuf.length());

                PrintStream printStream = new PrintStream(out);

                printStream.print(aux);

                String aux2 = "ERROR";

                try {

                        aux2 = out.toString(toEncode);

                        System.out.println(aux + " " + aux2);

                } catch (UnsupportedEncodingException e) {

                        // TODO Auto-generated catch block

                        e.printStackTrace();

                }

                return aux2;

        }

        private PreparedStatement getUpdateFeatureStatement(DBFeatureType dbFeatureType) throws SQLException{

                if (this.updateSt == null){

                        StringBuffer sqlBuf = new StringBuffer("UPDATE "

                                        + this.parameters.tableID() + " SET");

                        String sql = null;

                        Iterator iter = dbFeatureType.iterator();

                        while (iter.hasNext()){

                                IFeatureAttributeDescriptor fad=(IFeatureAttributeDescriptor)iter.next();

                                String name = fad.getName();

                                // El campo gid no lo actualizamos.

                                if (fad.isPrimaryKey())

                                        continue;

                                sqlBuf.append(" " + name + " = ? ,");

                        }

                        sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));

                        sqlBuf.append(" WHERE ");

                        sqlBuf.append(getFliterForIDForPStatement(dbFeatureType));

                        sql = sqlBuf.toString();

                        this.updateSt= this.conex.prepareStatement(sql);

                } else{

                        this.updateSt.clearParameters();

                }

                return this.updateSt;

        }

        public String getSqlDeleteFeature(DBFeatureType dbFeatureType, IFeature feature) {

                StringBuffer sqlBuf = new StringBuffer("DELETE FROM "

                                + this.parameters.tableID() + " WHERE ");

                String sql = null;

                sqlBuf.append(((JDBCFeature)feature).getFilterForID());

                sql = sqlBuf.toString();

                return sql;

        }

        public static void create(PostgresqlStoreParameters parameters, IFeatureType featureType)throws InitializeWriterException, InitializeException {

                Connection con = PostgresqlStoreUtils.getConnection(parameters.getUrl(), parameters.getUser(), parameters.getPassw());

                StringBuffer sb=new StringBuffer();

                sb.append("CREATE TABLE ");

                sb.append(parameters.tableID());

                sb.append(" (");

                sb.append("id int, ");

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

                        IFeatureAttributeDescriptor descriptor=(IFeatureAttributeDescriptor)featureType.get(i);

                        String type = descriptor.getDataType();

                        if (type.equals(IFeatureAttributeDescriptor.TYPE_BOOLEAN)){

                                sb.append(descriptor.getName());

                                sb.append(" bit, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_BYTE)){

                                sb.append(descriptor.getName());

                                sb.append(" byte, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_DATE)){

                                sb.append(descriptor.getName());

                                sb.append(" date, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_DOUBLE)){

                                sb.append(descriptor.getName());

                                sb.append(" double, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_FLOAT)){

                                sb.append(descriptor.getName());

                                sb.append(" float, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_INT)){

                                sb.append(descriptor.getName());

                                sb.append(" int, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_LONG)){

                                sb.append(descriptor.getName());

                                sb.append(" bigint, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_STRING)){

                                sb.append(descriptor.getName());

                                sb.append(" varchar, ");

                        }else if (type.equals(IFeatureAttributeDescriptor.TYPE_GEOMETRY)){

                                sb.append(descriptor.getName());

                                sb.append(" other, ");

                        }else {

                                System.out.print(" ---- " + "TYPE UNKNOWN");

                        }

                }

                String createTable=sb.toString();

                createTable=createTable.substring(0, createTable.length()-2);

                createTable+=")";

                try{

                        Statement st=con.createStatement();

                        st.execute(createTable);

                        st.close();

                }

                catch(SQLException except){

                        throw new InitializeWriterException(parameters.getDataStoreName(),except);

                }

        }

        protected void loadPkInPreparedStatement(PreparedStatement ps,int paramIndex,DBFeatureType fType,IFeature feature) throws java.sql.SQLException{

                if (fType.getFieldsId().length != 1)

                        throw new UnsupportedOperationException("ID fields > 1");

                String id =fType.getFieldsId()[0];

                loadValueInPreparedStatement(ps, paramIndex, (JDBCAttributeDescriptor)fType.get(fType.getFieldIndex(id)), feature);

        }

        protected void loadValueInPreparedStatement(PreparedStatement ps,int paramIndex,JDBCAttributeDescriptor attr,IFeature feature) throws java.sql.SQLException{

                Object value = feature.get(attr.ordinal());

                if (value == null){

                        ps.setNull(paramIndex, attr.getSqlType());

                        return;

                }

                if (attr.getDataType() == IFeatureAttributeDescriptor.TYPE_GEOMETRY){

                        IGeometry geom =(IGeometry)feature.get(attr.ordinal());

                        ps.setBytes(

                                paramIndex,        wkbWriter.write(geom.toJTSGeometry())

                        );

                        return;

                }

                ps.setObject(paramIndex, feature.get(attr.ordinal()));

        }

        protected static String getFliterForIDForPStatement(DBFeatureType fType) {

                if (fType.getFieldsId().length != 1)

                        throw new UnsupportedOperationException("ID fields > 1");

                String id =fType.getFieldsId()[0];

                return id + " = ?";

        }

}