| gvSIG desktop 1

/* gvSIG. Sistema de Informaci?n Geogr?fica de la Generalitat Valenciana

 *

 * Copyright (C) 2004 IVER T.I. and Generalitat Valenciana.

 *

 * 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., 59 Temple Place - Suite 330, Boston, MA  02111-1307,USA.

 *

 * For more information, contact:

 *

 *  Generalitat Valenciana

 *   Conselleria d'Infraestructures i Transport

 *   Av. Blasco Ib??ez, 50

 *   46010 VALENCIA

 *   SPAIN

 *

 *      +34 963862235

 *   gvsig@gva.es

 *      www.gvsig.gva.es

 *

 *    or

 *

 *   IVER T.I. S.A

 *   Salamanca 50

 *   46005 Valencia

 *   Spain

 *

 *   +34 963163400

 *   dac@iver.es

 */

package com.iver.cit.gvsig.fmap.drivers;

import java.awt.geom.Rectangle2D;

import java.io.IOException;

import java.net.NoRouteToHostException;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import java.util.Hashtable;

import java.util.StringTokenizer;

import javax.swing.JDialog;

import org.apache.log4j.Logger;

import com.hardcode.gdbms.engine.data.DataSourceFactory;

import com.hardcode.gdbms.engine.data.driver.ObjectDriver;

import com.hardcode.gdbms.engine.values.IntValue;

import com.hardcode.gdbms.engine.values.Value;

import com.hardcode.gdbms.engine.values.ValueFactory;

import com.iver.cit.gvsig.fmap.DriverException;

import com.iver.cit.gvsig.fmap.Messages;

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

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

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

import com.iver.cit.gvsig.fmap.layers.SelectableDataSource;

import com.iver.cit.gvsig.fmap.layers.XMLException;

import com.iver.utiles.XMLEntity;

import com.iver.utiles.swing.JPasswordDlg;

/**

 * Clase abstracta qu

 */

public abstract class DefaultDBDriver implements VectorialJDBCDriver, ObjectDriver {    

    private static Logger logger = Logger.getLogger(SelectableDataSource.class.getName());

    private static Hashtable poolPassw = new Hashtable();

    protected Connection conn;

    // protected String tableName;

    // protected String whereClause;

    // protected String fields;

    // protected String sqlOrig;

    private DBLayerDefinition lyrDef = null;

    protected ResultSet rs;

    protected boolean bCursorActivo = false;

    protected Statement st;

    protected int numReg=-1;

    private Rectangle2D fullExtent = null;

    // protected String strFID_FieldName;

    // protected String idFID_FieldName;

    protected Hashtable hashRelate;

    protected ResultSetMetaData metaData = null;

    protected Rectangle2D workingArea;

        private String driverClass;

        private String userName;

        private String dbUrl;

        private String className;

        private String catalogName;

        private String tableName;

        private String[] fields;

        private String FIDfield;

        private String geometryField;

        private String whereClause;

        private String strSRID;

    abstract public void setData(Connection conn, DBLayerDefinition lyrDef);

        /**

         * @return devuelve la Conexi?n a la base de datos, para que 

         * el usuario pueda hacer la consulta que quiera, si lo desea.

         * Por ejemplo, esto puede ser ?til para abrir un cuadro de dialogo

         * avanazado y lanzar peticiones del tipo "Devuelveme un buffer

         * a las autopistas", y con el resultset que te venga, escribir

         * un shape, o cosas as?.

         */

        public Connection getConnection()

        {

            return conn;

        }

        public String[] getFields()

        {

        /* StringTokenizer tokenizer = new StringTokenizer(fields, ",");

        String[] arrayFields = new String[tokenizer.countTokens()];

        int i=0;

        while (tokenizer.hasMoreTokens())

        {

            arrayFields[i] = tokenizer.nextToken();

            i++;

        }

            return arrayFields; */

        return lyrDef.getFieldNames();

        }

    /**

     * First, the geometry field. After, the rest of fields

     * @return

     */

    public String getTotalFields()

    {

        String strAux = getGeometryField(getLyrDef().getFieldGeometry());

        String[] fieldNames = getLyrDef().getFieldNames();

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

        {

            strAux = strAux + ", " + fieldNames[i];

        }

        return strAux;

    }

        public String getWhereClause()

        {

            return lyrDef.getWhereClause();

        }

        public String getTableName()

        {

            return lyrDef.getTableName();

        }

        /**

         * @throws DriverIOException

         * @throws DriverException

         * @see com.iver.cit.gvsig.fmap.layers.ReadableVectorial#getShapeCount()

         */

        public int getShapeCount() throws IOException {

                    if (numReg == -1)

                    {

                        try

                    {

                            Statement s = conn.createStatement();                    

                            ResultSet r = s.executeQuery("SELECT COUNT(*) AS NUMREG FROM " + lyrDef.getTableName() + " " + getCompleteWhere());

                            r.next();

                            numReg = r.getInt(1);

                            System.err.println("numReg = " + numReg);

                    }

                        catch (SQLException e)

                        {

                            throw new IOException(e.getMessage());

                        }

                    }

            return numReg;

        }

    /**

     * @see com.iver.cit.gvsig.fmap.layers.ReadableVectorial#getFullExtent()

     */

    public Rectangle2D getFullExtent(){

        // Por defecto recorremos todas las geometrias.

        // Las bases de datos como PostGIS pueden y deben

        // sobreescribir este m?todo.

        if (fullExtent == null)

        {

            try

            {

                IFeatureIterator itGeom = getFeatureIterator("SELECT " +  

                        getGeometryField(getLyrDef().getFieldGeometry()) + " FROM " + 

                        getLyrDef().getTableName() +  " " + getCompleteWhere());

                IGeometry geom;

                int cont = 0;

                while (itGeom.hasNext())

                {

                    geom = itGeom.next().getGeometry();

                    if (cont==0)

                        fullExtent = geom.getBounds2D();

                    else

                        fullExtent.add(geom.getBounds2D());

                    cont++;

                }

            }

            catch (SQLException e)

            {

                System.err.println(e.getMessage());

            } catch (DriverException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

        }

        return fullExtent;

    }

        /**

         * @see com.iver.cit.gvsig.fmap.layers.ReadableVectorial#getShapeType()

         */

        public int getShapeType() {

        IGeometry geom;

        int result = FShape.MULTI;

        try {

            geom = getShape(0);            

            if (geom != null)

                result = geom.getGeometryType();             

        } catch (IOException e) {

            e.printStackTrace();

        }

        return result;

        }

        public int getFieldType(int idField) throws com.hardcode.gdbms.engine.data.driver.DriverException

        {

            String str = "";

            try {

                int i = idField + 2; // idField viene basado en 1, y

                                        // adem?s nos saltamos el campo de geometry

                str = metaData.getColumnClassName(i);

            if (metaData.getColumnType(i) == Types.VARCHAR)

                return Types.VARCHAR;

                    if (metaData.getColumnType(i) == Types.FLOAT)

                        return Types.FLOAT;

                    if (metaData.getColumnType(i) == Types.DOUBLE)

                        return Types.DOUBLE;

                    if (metaData.getColumnType(i) == Types.INTEGER)

                        return Types.INTEGER;

                    if (metaData.getColumnType(i) == Types.BIGINT)

                        return Types.BIGINT;

                    if (metaData.getColumnType(i) == Types.BIT)

                        return Types.BIT;

                    if (metaData.getColumnType(i) == Types.DATE)

                        return Types.DATE;

            if (metaData.getColumnType(i) == Types.DECIMAL)

                return Types.DOUBLE;

            if (metaData.getColumnType(i) == Types.NUMERIC)

                return Types.DOUBLE;

            } catch (SQLException e) {

                    throw new com.hardcode.gdbms.engine.data.driver.DriverException(e);

            }

        throw new com.hardcode.gdbms.engine.data.driver.DriverException("Tipo no soportado: " + str);

        }

    /**

     * Obtiene el valor que se encuentra en la fila y columna indicada

     * Esta es la implementaci?n por defecto. Si lo del absolute

     * no va bien, como es el caso del PostGis, el driver lo

     * tiene que reimplementar

     *

     * @param rowIndex fila

     * @param fieldId columna

     *

     * @return subclase de Value con el valor del origen de datos

     *

     * @throws DriverException Si se produce un error accediendo al DataSource

     */

    public Value getFieldValue(long rowIndex, int idField)

        throws com.hardcode.gdbms.engine.data.driver.DriverException

        {

                int i = (int) (rowIndex + 1);

                int fieldId = idField+2;

                try {

                    rs.absolute(i);

                if (metaData.getColumnType(fieldId) == Types.VARCHAR)

                {

                    String strAux = rs.getString(fieldId);

                    if (strAux == null) strAux = "";

                    return ValueFactory.createValue(strAux);

                }

                        if (metaData.getColumnType(fieldId) == Types.FLOAT)

                            return ValueFactory.createValue(rs.getFloat(fieldId));

                        if (metaData.getColumnType(fieldId) == Types.DOUBLE)

                            return ValueFactory.createValue(rs.getDouble(fieldId));

                        if (metaData.getColumnType(fieldId) == Types.INTEGER)

                            return ValueFactory.createValue(rs.getInt(fieldId));

                        if (metaData.getColumnType(fieldId) == Types.BIGINT)

                            return ValueFactory.createValue(rs.getLong(fieldId));

                        if (metaData.getColumnType(fieldId) == Types.BIT)

                            return ValueFactory.createValue(rs.getBoolean(fieldId));

                        if (metaData.getColumnType(fieldId) == Types.DATE)

                            return ValueFactory.createValue(rs.getDate(fieldId));

                } catch (SQLException e) {

                throw new com.hardcode.gdbms.engine.data.driver.DriverException("Tipo no soportado: columna " + fieldId );

                }

                return null;

        }

    /**

     * Obtiene el n?mero de campos del DataSource

     *

     * @return

     *

     * @throws DriverException Si se produce alg?n error accediendo al

     *         DataSource

     */

    public int getFieldCount() throws com.hardcode.gdbms.engine.data.driver.DriverException

    {

        try {

            // Suponemos que el primer campo es el de las geometries, y no lo

            // contamos

            return rs.getMetaData().getColumnCount()-1;

        } catch (SQLException e) {

            throw new com.hardcode.gdbms.engine.data.driver.DriverException(e);

        }

    }

    /**

     * Devuelve el nombre del campo fieldId-?simo

     *

     * @param fieldId ?ndice del campo cuyo nombre se quiere obtener

     *

     * @return

     * @throws com.hardcode.gdbms.engine.data.driver.DriverException

     *

     * @throws DriverException Si se produce alg?n error accediendo al

     *         DataSource

     */

    public String getFieldName(int fieldId) throws com.hardcode.gdbms.engine.data.driver.DriverException

    {

        try {

            return rs.getMetaData().getColumnName(fieldId+2);

        } catch (SQLException e) {

            throw new com.hardcode.gdbms.engine.data.driver.DriverException(e);

        }

    }

    /**

     * Obtiene el n?mero de registros del DataSource

     *

     * @return

     *

     * @throws DriverException Si se produce alg?n error accediendo al

     *         DataSource

     */

    public long getRowCount()

    {

        try {

            return getShapeCount();

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return -1;

    }

    public void close()

    {

    }

    /**

     * Recorre el recordset creando una tabla Hash que usaremos para 

     * relacionar el n?mero de un registro con su identificador ?nico.

     * Debe ser llamado en el setData justo despu?s de crear el recorset

     * principal

     * @throws SQLException 

     */

    protected void doRelateID_FID() throws SQLException

    {

        hashRelate = new Hashtable();

        String strSQL = "SELECT " + getLyrDef().getFieldID() + " FROM " + getLyrDef().getTableName()

        + " " + getCompleteWhere() + " ORDER BY " + getLyrDef().getFieldID();

        Statement s = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        ResultSet r = s.executeQuery(strSQL);

        int id=0;

        int gid;            

        int index = 0;

        while (r.next())

        {

            String aux = r.getString(1);

            Value val = ValueFactory.createValue(aux);

            hashRelate.put(val, new Integer(index));

            // System.out.println("ASOCIANDO CLAVE " + aux + " CON VALOR " + index);

            index++;

        }

        numReg = index;

        r.close();

        // rs.beforeFirst();

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#getRowIndexByFID(java.lang.Object)

     */

    public int getRowIndexByFID(IFeature FID)

    {

        int resul;

        // Object obj = FID.getAttribute(lyrDef.getIdFieldID());

        String theId = FID.getID();

        Value aux = ValueFactory.createValue(theId);

        // System.err.println("Mirando si existe " + obj.toString());

        Integer rowIndex = (Integer) hashRelate.get(aux);

        resul = rowIndex.intValue();

        // System.err.println("Row asociada a " + obj.toString() + ":" + resul);

        return resul;

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#setXMLEntity(com.iver.utiles.XMLEntity)

     */

    public void setXMLEntity(XMLEntity xml) throws XMLException

    {

        className = xml.getStringProperty("className");

        dbUrl = xml.getStringProperty("dbURL");

        catalogName = xml.getStringProperty("catalog");

        userName =xml.getStringProperty("username");

        driverClass =xml.getStringProperty("driverclass");     

        tableName = xml.getStringProperty("tablename");

        fields = xml.getStringArrayProperty("fields");

        FIDfield = xml.getStringProperty("FID");

        geometryField = xml.getStringProperty("THE_GEOM");        

        whereClause = xml.getStringProperty("whereclause");

        strSRID = xml.getStringProperty("SRID");

        if (xml.contains("minXworkArea"))

        {

            double x = xml.getDoubleProperty("minXworkArea");

            double y = xml.getDoubleProperty("minYworkArea");

            double H = xml.getDoubleProperty("HworkArea");

            double W = xml.getDoubleProperty("WworkArea");

            workingArea = new Rectangle2D.Double(x,y,W,H);

        }

    }

    public void load() throws DriverException{

            try {            

            Class.forName(driverClass);

            String keyPool = dbUrl + "_" + userName;

            Connection newConn = null;

            String clave = null;

            if (!poolPassw.containsKey(keyPool))

            {

                JPasswordDlg dlg = new JPasswordDlg();

                String strMessage = Messages.getString("conectar_jdbc");

                String strPassword = Messages.getString("password");

                dlg.setMessage(strMessage + " " + dbUrl + ". " + strPassword + "?");

                dlg.show();

                clave = dlg.getPassword();

                if (clave == null)

                    return;

                poolPassw.put(keyPool, clave);                    

            }

            else

            {

                clave = (String) poolPassw.get(keyPool);

            }

            newConn = DriverManager.getConnection(dbUrl, userName, clave);

            newConn.setAutoCommit(false);

            DBLayerDefinition lyrDef = new DBLayerDefinition();

            lyrDef.setCatalogName(catalogName);

            lyrDef.setTableName(tableName);

            lyrDef.setFieldNames(fields);

            lyrDef.setFieldID(FIDfield);

            lyrDef.setFieldGeometry(geometryField);

            lyrDef.setWhereClause(whereClause);

            // lyrDef.setClassToInstantiate(driverClass);

            if (workingArea != null)

                lyrDef.setWorkingArea(workingArea);

            lyrDef.setSRID_EPSG(strSRID);

            setData(newConn, lyrDef);

        } catch (ClassNotFoundException e) {

            logger.debug(e);

            throw new DriverException(e); 

        } catch (SQLException e) {

            logger.debug(e);

            throw new DriverException(e);

        } 

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#getXMLEntity()

     */

    public XMLEntity getXMLEntity()

    {       

        XMLEntity xml = new XMLEntity();

        xml.putProperty("className",this.getClass().getName());

        try {

            DatabaseMetaData metadata = getConnection().getMetaData();

            xml.putProperty("dbURL", metadata.getURL());

            xml.putProperty("catalog", getLyrDef().getCatalogName());

            // TODO: NO DEBEMOS GUARDAR EL NOMBRE DE USUARIO Y CONTRASE?A

            // AQUI. Hay que utilizar un pool de conexiones

            // y pedir al usuario que conecte a la base de datos

            // en la primera capa. En el resto, usar la conexi?n

            // creada con anterioridad.

            String userName = metadata.getUserName();

            int aux = userName.indexOf("@");

            if (aux != -1)

                userName = userName.substring(0,aux);

            xml.putProperty("username", userName);

            Driver drv = DriverManager.getDriver(metadata.getURL());

            // System.out.println(drv.getClass().getName());

            xml.putProperty("driverclass", drv.getClass().getName());

            xml.putProperty("tablename", getTableName());

            xml.putProperty("fields", lyrDef.getFieldNames());

            xml.putProperty("FID", lyrDef.getFieldID());

            xml.putProperty("THE_GEOM", lyrDef.getFieldGeometry());            

            xml.putProperty("whereclause", getWhereClause());

            xml.putProperty("SRID", lyrDef.getSRID_EPSG());

            if (getWorkingArea() != null)

            {

                xml.putProperty("minXworkArea", getWorkingArea().getMinX());

                xml.putProperty("minYworkArea", getWorkingArea().getMinY());

                xml.putProperty("HworkArea", getWorkingArea().getHeight());

                xml.putProperty("WworkArea", getWorkingArea().getWidth());

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return xml;

    }

    /**

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialJDBCDriver#setWorkingArea(java.awt.geom.Rectangle2D)

     */

    public void setWorkingArea(Rectangle2D rect) {

        this.workingArea = rect;

    }

    /**

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialJDBCDriver#getWorkingArea()

     */

    public Rectangle2D getWorkingArea() {

        return workingArea;

    }

    /* (non-Javadoc)

     * @see com.hardcode.gdbms.engine.data.driver.GDBMSDriver#setDataSourceFactory(com.hardcode.gdbms.engine.data.DataSourceFactory)

     */

    public void setDataSourceFactory(DataSourceFactory arg0) {

        // TODO Auto-generated method stub

    }

    /**

     * @return Returns the lyrDef.

     */

    public DBLayerDefinition getLyrDef() {

        return lyrDef;

    }

    /**

     * @param lyrDef The lyrDef to set.

     */

    public void setLyrDef(DBLayerDefinition lyrDef) {

        this.lyrDef = lyrDef;

    }

    abstract public String getSqlTotal();

    /**

     * @return Returns the completeWhere. WITHOUT order by clause!!

     */

    abstract public String getCompleteWhere();

}