Application: gvSIG desktop

/*

 * Created on 26-oct-2005

 *

 * 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.jdbc_spatial.util;

import java.io.ByteArrayOutputStream;

import java.io.PrintStream;

import java.io.UnsupportedEncodingException;

import java.sql.Types;

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

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

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

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

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

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

import com.iver.cit.gvsig.jdbc_spatial.XTypes;

public class PostGIS {

        public static String toEncode;

        /**

         * Mover esto a IverUtiles

         * 

         * @param val

         * @return

         */

        public static boolean isNumeric(Value val) {

                switch (val.getSQLType()) {

                case Types.DOUBLE:

                case Types.FLOAT:

                case Types.INTEGER:

                case Types.SMALLINT:

                case Types.BIGINT:

                case Types.NUMERIC:

                case Types.REAL:

                case Types.TINYINT:

                        return true;

                }

                return false;

        }

        /**

         * @param dbLayerDef

         * @param fieldTypes

         *            (see java.sql.Types)

         */

        public static String getSqlCreateSpatialTable(DBLayerDefinition dbLayerDef,

                        FieldDescription[] fieldsDescr) {

                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

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

                                        + " (gid serial PRIMARY KEY, ";

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

                        int fieldType = fieldsDescr[i].getFieldType();

                        String strType = XTypes.fieldTypeToString(fieldType);

                        /*

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

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

                         */

                        if (i == 0)

                                resul = resul + dbLayerDef.getFieldNames()[i] + " " + strType;

                        else

                                resul = resul + ", " + dbLayerDef.getFieldNames()[i] + " "

                                                + strType;

                }

                resul = resul + ");";

                return resul;

        }

        public static String getSqlAlterTable(DBLayerDefinition dbLayerDef) {

                String strGeometryFieldType;

                strGeometryFieldType = "GEOMETRY";

                switch (dbLayerDef.getShapeType()) {

                case FShape.POINT:

                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POINT2D);

                        break;

                case FShape.LINE:

                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.LINE2D);

                        break;

                case FShape.POLYGON:

                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POLYGON2D);

                        break;

                case FShape.MULTI:

                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.MULTI2D);

                        break;

                }

                String result = "SELECT AddGeometryColumn('"

                                + dbLayerDef.getCatalogName() + "', '"

                                + dbLayerDef.getTableName() + "', '"

                                + dbLayerDef.getFieldGeometry() + "', "

                                + dbLayerDef.getSRID_EPSG() + ", '" + strGeometryFieldType + "', "

                                + dbLayerDef.getDimension() + ");";

                return result;

        }

        /**

         * From geotools Adds quotes to an object for storage in postgis. The object

         * should be a string or a number. To perform an insert strings need quotes

         * around them, and numbers work fine with quotes, so this method can be

         * called on unknown objects.

         * 

         * @param value

         *            The object to add quotes to.

         * 

         * @return a string representation of the object with quotes.

         */

        protected static String addQuotes(Object value) {

                String retString;

                if (value != null) {

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

                } else {

                        retString = "null";

                }

                return retString;

        }

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

        }

        /**

         * Based in code from JUMP (VividSolutions) and Geotools Things to be aware:

         * We always will use Spatial Tables with Unique ID. IFeature has the same

         * field order than dbLayerDef.getFieldNames()

         * 

         * @param dbLayerDef

         * @param feat

         * @return

         */

        public static String getSqlInsertFeature(DBLayerDefinition dbLayerDef,

                        IFeature feat) {

                StringBuffer sqlBuf = new StringBuffer("INSERT INTO "

                                + dbLayerDef.getTableName() + " (");

                String sql = null;

                int numAlphanumericFields = dbLayerDef.getFieldNames().length;

                for (int i = 0; i < numAlphanumericFields; i++) {

                        String name = dbLayerDef.getFieldNames()[i];

                        // if (cols.contains(name) && (!name.equals(uniqueCol) ||

                        // existsUnique)) {

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

                        // }

                }

                sqlBuf.append(" " + dbLayerDef.getFieldGeometry());

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

                sqlBuf.append(" ) VALUES (");

                String insertQueryHead = sqlBuf.toString();

                sqlBuf = new StringBuffer(insertQueryHead);

                for (int j = 0; j < numAlphanumericFields; j++) {

                        if (isNumeric(feat.getAttribute(j)))

                                sqlBuf.append(feat.getAttribute(j) + ", ");

                        else

                                sqlBuf.append(addQuotes(feat.getAttribute(j)) + ", ");

                }

                sqlBuf.append(" GeometryFromText( '"

                                + feat.getGeometry().toJTSGeometry().toText() + "', "

                                + dbLayerDef.getSRID_EPSG() + ")");

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

                sqlBuf.append(" ) ");

                sql = sqlBuf.toString();

                return sql;

        }

        static public String getSqlCreateIndex(DBLayerDefinition lyrDef) {

                String indexName = lyrDef.getTableName() + "_"

                                + lyrDef.getFieldGeometry() + "_gist";

                String sql = "CREATE INDEX \"" + indexName + "\" ON \""

                                + lyrDef.getTableName() + "\" USING GIST (\""

                                + lyrDef.getFieldGeometry() + "\" GIST_GEOMETRY_OPS)";

                return sql;

        }

        public static String getSqlModifyFeature(DBLayerDefinition dbLayerDef, IFeature feat) {

                /* 

                         UPDATE weather

                         SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2

                         WHERE date > '1994-11-28';

                 */

                StringBuffer sqlBuf = new StringBuffer("UPDATE "

                                + dbLayerDef.getTableName() + " SET");

                String sql = null;

                int numAlphanumericFields = dbLayerDef.getFieldsDesc().length;

                for (int i = 0; i < numAlphanumericFields; i++) {

                        FieldDescription fldDesc = dbLayerDef.getFieldsDesc()[i];

                        if (fldDesc != null)

                        {

                                String name = fldDesc.getFieldName();

                                Value val = feat.getAttribute(i);

                                if (val != null)

                                {

                                        String strAux = val.getStringValue(ValueWriter.internalValueWriter);

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

                                }

                        }

                }

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

                if (feat.getGeometry() != null)

                {

                        sqlBuf.append(", " + dbLayerDef.getFieldGeometry());                

                        sqlBuf.append(" = ");

                        sqlBuf.append(" GeometryFromText( '"

                                + feat.getGeometry().toJTSGeometry().toText() + "', "

                                + dbLayerDef.getSRID_EPSG() + ")");

                }

                sqlBuf.append(" WHERE ");

                sqlBuf.append(dbLayerDef.getFieldID() + " = " + feat.getID());

                sql = sqlBuf.toString();

                return sql;

        }

        public static String getSqlDeleteFeature(DBLayerDefinition dbLayerDef, IFeature feat) {

                // DELETE FROM weather WHERE city = 'Hayward';

                StringBuffer sqlBuf = new StringBuffer("DELETE "

                                + dbLayerDef.getTableName() + " WHERE ");

                String sql = null;

                sqlBuf.append(dbLayerDef.getFieldID() + " = " + feat.getID());

                sql = sqlBuf.toString();

                return sql;

        }

}