Application: gvSIG desktop

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

import java.io.ByteArrayOutputStream;

import java.io.File;

import java.io.IOException;

import java.io.PrintStream;

import java.io.RandomAccessFile;

import java.io.UnsupportedEncodingException;

import java.nio.channels.WritableByteChannel;

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.sql.Types;

import java.util.Iterator;

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

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 com.iver.cit.gvsig.fmap.core.FShape;

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

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

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

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

import com.vividsolutions.jts.io.WKBWriter;

class PostgresqlFeaturesWriter extends JDBCFeaturesWriter implements ISelectiveWriter {

        DBFeatureType featureType;

        boolean bCreateTable=false;

        private String toEncode;

        PostgresqlStore store;

        PostgresqlStoreParameters parameters;

        private PreparedStatement insertSt;

        private PreparedStatement updateSt;

        private static WKBWriter wkbWriter = new WKBWriter();

        PostgresqlFeaturesWriter(){

        }

        public void init(IFeatureStore store) {

                this.store = (PostgresqlStore)store;

                this.parameters=(PostgresqlStoreParameters)store.getParameters();

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

                conex = this.store.getConnection();

        }

        public void postProcess() throws OpenException, WriteException {

                try {

                        conex.commit();

                } catch (SQLException e) {

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

                }

        }

        public void cancelActions() throws WriteException{

                try {

                        conex.rollback();

                } catch (SQLException e) {

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

                }

        }

        public void preProcess() throws OpenException, InitializeWriterException {

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

                // 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

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

                Statement st;

                try {

                        conex.setAutoCommit(false);

                        st = conex.createStatement();

                        if (bCreateTable) {

                                dropTableIfExist();

                                String sqlCreate = getSqlCreateSpatialTable(featureType);

                                System.out.println("sqlCreate =" + sqlCreate);

                                st.execute(sqlCreate);

//                                conex.commit();

                        }

//                        fieldManager = new JdbcFieldManager(((ConnectionJDBC)conex).getConnection(), lyrDef.getTableName());

                } catch (SQLException e) {

                        e.printStackTrace();

//                        throw new InitializeWriterException(getName(),e);

                }

//        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() || fad.isAutoIncrement())

                                        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() || fad.isAutoIncrement())

                                        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);

                }

        }

        public String getSqlCreateSpatialTable(DBFeatureType featureType) {

                String resul;

                /* boolean bExistGID = false;

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

                        if (dbLayerDef.getFieldNames()[i].equalsIgnoreCase("gid")) {

                                bExistGID = true;

                                break;

                        }

                } */

                /* if (bExistGID) // Usamos el existente y no a?adimos ninguno nosotros

                        resul = "CREATE TABLE " + dbLayerDef.getTableName() + " (";

                else */

                // FJP: NUEVO: NO TOLERAMOS CAMPOS QUE SE LLAMEN GID. Lo reservamos para uso nuestro.

                resul = "CREATE TABLE " + this.parameters.tableID()

                                        + " ( " + featureType.getFieldId() +" serial PRIMARY KEY ";

                int j=0;

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

                        IFeatureAttributeDescriptor fad=(IFeatureAttributeDescriptor)this.featureType.get(i);

                        String fieldType = fad.getDataType();

                        String strType = JDBCTypes.fieldTypeToString(fieldType);

                        /*

                         * if (fieldType == Types.VARCHAR) strType = strType + "(" +

                         * fieldsDescr[i].getFieldLength() + ")";

                         */

                        if (fad.getName().equalsIgnoreCase(featureType.getFieldId()))

                                continue;

                        resul = resul + ", " + fad.getName() + " "        + strType;

                        j++;

                }

                resul = resul + ");";

                return resul;

        }

        /** @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(PostgresqlStoreUtils.getFilterForID(dbFeatureType, feature));

                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 + " = ?";

        }

}