| gvSIG desktop 1

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

import java.math.BigDecimal;

import java.nio.ByteBuffer;

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Statement;

import java.sql.Timestamp;

import java.sql.Types;

import java.util.Iterator;

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

import org.gvsig.data.datastores.vectorial.db.jdbc.exception.JDBCDriverNotFoundException;

import org.gvsig.data.datastores.vectorial.db.jdbc.exception.SQLException;

import org.gvsig.data.datastores.vectorial.db.jdbc.postgresql.PostgresqlFeature;

import org.gvsig.data.datastores.vectorial.db.jdbc.postgresql.PostgresqlStore;

import org.gvsig.data.datastores.vectorial.db.jdbc.postgresql.PostgresqlStoreParameters;

import org.gvsig.data.datastores.vectorial.db.jdbc.postgresql.PostgresqlStoreUtils;

import org.gvsig.data.exception.InitializeException;

import org.gvsig.data.exception.ReadException;

import org.gvsig.data.vectorial.DefaultAttributeDescriptor;

import org.gvsig.data.vectorial.IFeature;

import org.gvsig.data.vectorial.IFeatureAttributeDescriptor;

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

import com.iver.cit.gvsig.fmap.drivers.WKBParser2;

import com.iver.cit.gvsig.fmap.drivers.XTypes;

import com.vividsolutions.jts.io.WKBWriter;

public class PostgresqlBinStoreUtils {

        private static WKBParser2 wkbParser = new WKBParser2();

        private static WKBWriter wkbWriter = new WKBWriter();

        private static int cursorCount=0;

        private static String baseCursorName=null;

        public static IFeature createFeatureFromBinaryCursor(JDBCStore store, ResultSet rs, DBFeatureType featureType) throws ReadException {

                PostgresqlFeature feature=null;

                Object[] pk;

                try {

                        pk = getPkFromResulsetBinary(rs, featureType);

                        feature=new PostgresqlFeature(featureType,store,pk);

                        Iterator iter = featureType.iterator();

                        IGeometry geom = null;

                        while (iter.hasNext()) {

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

                                feature.set(fad.getName(), getFieldValueFromBinaryCursor(rs, fad));

                        }

                        return feature;

                } catch (java.sql.SQLException e) {

                        throw new ReadException("CreateFeature",e);

                }

        }

        public static Connection getConnection(String dbUrl, String dbUser, String dbPass) throws InitializeException {

                //TODO: Aqu? habria que implementar la llamada

                //      al Resource Manager para comprobar si ya hay

                //                una connexion a la BD

                String connID = getConnectionResourceID(dbUrl, dbUser);

                Connection conn = null;

//                IResource res = ResourceManager.getResourceManager().getResource(connID);

                try {

                        Class.forName("org.postgresql.Driver");

                } catch (ClassNotFoundException e) {

                        throw new JDBCDriverNotFoundException("org.postgresql.Driver",e);

                }

                try {

                        conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);

                        conn.setAutoCommit(false);

                } catch (java.sql.SQLException e1) {

                        throw new InitializeException(PostgresqlStore.DATASTORE_NAME,e1);

                }

                //TODO: Registrar en el Resource manager

                // ResourceManager.getResourceManager().addResource(res);

                return conn;

        }

        static String getConnectionResourceID(String dbUrl,String dbUser){

                return PostgresqlStore.CONNECTION_STRING+";"+dbUrl+";"+dbUser;

        }

        static Object getFieldValueFromBinaryCursor(ResultSet aRs, JDBCAttributeDescriptor attrDescriptor) throws java.sql.SQLException {

                int fieldId = attrDescriptor.ordinal();

                int sqlType = attrDescriptor.getSqlType();

                byte[] byteBuf = aRs.getBytes(fieldId+1);

                if (byteBuf == null)

                        return null;

                else {

                        ByteBuffer buf = ByteBuffer.wrap(byteBuf);

                        switch (sqlType) {

                        case Types.VARCHAR:

                                //FIXME Error

                                return aRs.getString(fieldId);

//                                return new String(buf.toString());

                        case Types.FLOAT:

                                return new Float(buf.getFloat());

                        case Types.DOUBLE:

                                return new Double(buf.getDouble());

                        case Types.REAL:

                                return new Float(buf.getFloat());

                        case Types.INTEGER:

                                return new Integer(buf.getInt());

                        case Types.BIGINT:

                                return new Long(buf.getLong());

                        case Types.BIT:

                                return new Boolean(byteBuf[0] == 1);

                        case Types.BOOLEAN:

                                return new Boolean(aRs.getBoolean(fieldId));

                        case Types.DATE:

                                long daysAfter2000 = buf.getInt() + 1;

                                long msecs = daysAfter2000*24*60*60*1000;

                                long real_msecs_date1 = (long) (XTypes.NUM_msSecs2000 + msecs);

                                Date realDate1 = new Date(real_msecs_date1);

                                return realDate1;

                        case Types.TIME:

                                // TODO:

                                // throw new RuntimeException("TIME type not implemented yet");

                                return "NOT IMPLEMENTED YET";

                        case Types.TIMESTAMP:

                                double segsReferredTo2000 = buf.getDouble();

                                long real_msecs = (long) (XTypes.NUM_msSecs2000 + segsReferredTo2000*1000);

                                Timestamp valTimeStamp = new Timestamp(real_msecs);

                                return valTimeStamp;

                        case Types.NUMERIC:

                                // System.out.println(metaData.getColumnName(fieldId) + " "

                                // + metaData.getColumnClassName(fieldId));

                                short ndigits = buf.getShort();

                                short weight = buf.getShort();

                                short sign = buf.getShort();

                                short dscale = buf.getShort();

                                String strAux;

                                if (sign == 0)

                                        strAux = "+";

                                else

                                        strAux = "-";

                                for (int iDigit = 0; iDigit < ndigits; iDigit++) {

                                        short digit = buf.getShort();

                                        strAux = strAux + digit;

                                        if (iDigit == weight)

                                                strAux = strAux + ".";

                                }

                                strAux = strAux + "0";

                                BigDecimal dec;

                                dec = new BigDecimal(strAux);

                                // System.out.println(ndigits + "_" + weight + "_" + dscale

                                // + "_" + strAux);

                                // System.out.println(strAux + " Big= " + dec);

                                return new Double(dec.doubleValue());

                        default:

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

                                        if (byteBuf == null)

                                                return null;

                                        return wkbParser.parse(byteBuf);

                                }

                                return null;

                        }

                }

        }

        static Object[] getPkFromResulsetBinary(ResultSet rs, DBFeatureType featureType) throws java.sql.SQLException{

                String[] fieldsId = featureType.getFieldsId();

                Object[] result = new Object[fieldsId.length];

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

                        result[i] = getFieldValueFromBinaryCursor(

                                        rs,

                                        (JDBCAttributeDescriptor)featureType.get(

                                                        featureType.getFieldIndex(fieldsId[i])

                                                        )

                                );

                }

                return result;

        }

        static IFeature createFeature(JDBCStore store,ResultSet rs,DBFeatureType featureType) throws ReadException{

                JDBCFeature feature=null;

                Object[] pk;

                try {

                        pk = getPkFromResulset(rs, featureType);

                        feature=new PostgresqlBinFeature(featureType,store,pk);

                        Iterator iter = featureType.iterator();

                        IGeometry geom = null;

                        while (iter.hasNext()) {

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

                                if (fad.getDataType().equals(IFeatureAttributeDescriptor.TYPE_GEOMETRY)) {

                                        byte[] data = rs.getBytes(fad.getName());

                                        if (data == null) {

                                                geom = null;

                                        } else{

                                                geom = wkbParser.parse(data);

                                        }

                                        feature.setGeometry(fad.getName(),geom);

                                } else {

                                        feature.set(fad.getName(), rs.getObject(fad.getName()));

                                }

                        }

                        return feature;

                } catch (java.sql.SQLException e) {

                        throw new ReadException("CreateFeature",e);

                }

        }

        static String addLimitsToSQL(String aSql,int fetchSize,int page){

                return aSql+ " limit " + fetchSize + " offset " + (fetchSize*page);

        }

        public static String createCursorName() {

                if (baseCursorName == null){

                        baseCursorName = "gv_"+ System.currentTimeMillis();

                }

                cursorCount++;

                return baseCursorName+"_"+cursorCount;

        }

        public static DBFeatureType getFeatureType(Connection connection, PostgresqlStoreParameters params) throws ReadException{

                DBFeatureType featureType = new DBFeatureType();

                loadFieldsToFeatureType(connection, params, featureType);

                featureType.setFieldsId(params.getFieldsId());

                if (params.getDefaultGeometryField() != null && params.getDefaultGeometryField() != ""){

                        if (featureType.getFieldIndex(params.getDefaultGeometryField())< 0){

                                // FIXME: crear una nueva excepcion??

                                throw new InitializeException(

                                                PostgresqlStore.DATASTORE_NAME,

                                                new Exception("Geometry Field '"+ params.getDefaultGeometryField() +"' not Found"));

                        }

                        featureType.setDefaultGeometry(params.getDefaultGeometryField());

                }

                return featureType;

        }

        private static void loadFieldsToFeatureType(Connection conn,PostgresqlStoreParameters params,DBFeatureType featureType) throws ReadException{

                String sql="";

                String columns=params.getFieldsString();

                if (params.getSqlSoure() != null){

                        sql = params.getSqlSoure();

                } else {

                        sql = "Select "+columns+" from " + params.tableID() + " limit 1;";

                }

                try {

                        Statement stAux = conn.createStatement();

                        ResultSet rs = stAux.executeQuery(sql);

                        ResultSetMetaData rsMetadata = rs.getMetaData();

                        int i;

                        featureType.setTableID(params.tableID());

                        DefaultAttributeDescriptor attr;

                        for (i=1;i<=rsMetadata.getColumnCount();i++){

                                attr = getAttributeFromJDBC(conn,rsMetadata,i);

                                featureType.add(attr);

                                attr.setOrdinal(i-1);

                        }

                        rs.close();

                } catch (java.sql.SQLException e) {

                        // TODO Auto-generated catch block

                        throw new SQLException(sql,"getFeatureType",e);

                }

        }

        private static DefaultAttributeDescriptor getAttributeFromJDBC(Connection conn,ResultSetMetaData rsMetadata,int colIndex) throws SQLException{

                JDBCAttributeDescriptor column= new JDBCAttributeDescriptor();

                try {

                        column.setName(rsMetadata.getColumnName(colIndex));

                        column.setSqlType(rsMetadata.getColumnType(colIndex));

                        column.setAllowNull(rsMetadata.isNullable(colIndex) == ResultSetMetaData.columnNullable);

                        column.setAutoIncrement(rsMetadata.isAutoIncrement(colIndex));

                        column.setReadOnly(rsMetadata.isReadOnly(colIndex));

                        switch (rsMetadata.getColumnType(colIndex)) {

                        case java.sql.Types.INTEGER:

                                column.setType(IFeatureAttributeDescriptor.TYPE_INT);

                                break;

                        case java.sql.Types.BIGINT:

                                column.setType(IFeatureAttributeDescriptor.TYPE_LONG);

                                break;

                        case java.sql.Types.REAL:

                                column.setType(IFeatureAttributeDescriptor.TYPE_DOUBLE);

                                column.setPrecision(rsMetadata.getPrecision(colIndex));

                                break;

                        case java.sql.Types.DOUBLE:

                                column.setType(IFeatureAttributeDescriptor.TYPE_DOUBLE);

                                column.setPrecision(rsMetadata.getPrecision(colIndex));

                                break;

                        case java.sql.Types.CHAR:

                                column.setType(IFeatureAttributeDescriptor.TYPE_STRING);

                                column.setSize(rsMetadata.getColumnDisplaySize(colIndex));

                                break;

                        case java.sql.Types.VARCHAR:

                                column.setType(IFeatureAttributeDescriptor.TYPE_STRING);

                                column.setSize(rsMetadata.getColumnDisplaySize(colIndex));

                                break;

                        case java.sql.Types.FLOAT:

                                column.setType(IFeatureAttributeDescriptor.TYPE_FLOAT);

                                column.setSize(rsMetadata.getColumnDisplaySize(colIndex));

                                column.setPrecision(rsMetadata.getPrecision(colIndex));

                                break;

                        case java.sql.Types.DECIMAL:

                                column.setType(IFeatureAttributeDescriptor.TYPE_FLOAT);

                                column.setSize(rsMetadata.getColumnDisplaySize(colIndex));

                                column.setPrecision(rsMetadata.getPrecision(colIndex));

                                break;

                        case java.sql.Types.DATE:

                                column.setType(IFeatureAttributeDescriptor.TYPE_DATE);

                                break;

                        case java.sql.Types.BOOLEAN:

                                column.setType(IFeatureAttributeDescriptor.TYPE_BOOLEAN);

                                break;

                        default:

                                column.setType(IFeatureAttributeDescriptor.TYPE_OBJECT);

                                break;

                        }

                } catch (java.sql.SQLException e){

                        throw new SQLException("","load attribute definition",e);

                }

                return column;

        }

        public static String getFliterForID(DBFeatureType featureType, Object[] featureKey) {

                // TODO Auto-generated method stub

                return null;

        }

        protected static String getFliterForID(DBFeatureType fType, IFeature feature){

                return getFliterForID(fType, getPkFromFeature(feature,fType));

        }

        protected static Object[] getPkFromResulset(ResultSet rs, DBFeatureType featureType) throws java.sql.SQLException{

                String[] fieldsId = featureType.getFieldsId();

                Object[] result = new Object[fieldsId.length];

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

                        result[i] = rs.getObject(fieldsId[i]);

                }

                return result;

        }

        protected static Object[] getPkFromFeature(IFeature feature, DBFeatureType featureType){

                String[] fieldsId = featureType.getFieldsId();

                Object[] result = new Object[fieldsId.length];

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

                        result[i] = feature.get(fieldsId[i]);

                }

                return result;

        }

        public static String getJDBCUrl(String host, String db, String port) {

                String url;

                url = "jdbc:postgresql://"+host+":" + port +"/"+db;

                return url;

        }

}