| gvSIG desktop 1

/*

 * Created on 15-ene-2007

 *

 * 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

 */

/* CVS MESSAGES:

*

* $Id$

* $Log$

* Revision 1.1  2007-01-15 20:15:35  azabala

* *** empty log message ***

*

*

*/

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

import java.io.IOException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

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

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.fmap.drivers.ITableDefinition;

import com.iver.cit.gvsig.fmap.edition.EditionException;

import com.iver.cit.gvsig.fmap.edition.IFieldManager;

import com.iver.cit.gvsig.fmap.edition.IRowEdited;

import com.iver.cit.gvsig.fmap.edition.ISpatialWriter;

import com.iver.cit.gvsig.fmap.edition.fieldmanagers.JdbcFieldManager;

import com.iver.cit.gvsig.fmap.edition.writers.AbstractWriter;

public class MySqlWriter extends AbstractWriter 

		implements ISpatialWriter, IFieldManager {

	private int numRows;

	private DBLayerDefinition lyrDef;

	private Connection conex;

	private Statement st;

	/**

	 * flag to mark if writer must create the table or it must

	 * add records to an existing one

	 * 

	 */

	private boolean bCreateTable;

	private boolean bWriteAll;

	private MySql mySql = new MySql();

	/*

	 * TODO

	 * Ver si en MySQL los tipos de datos (numeric, etc.)

	 * se nombran as? tambi?n

	 * */

	private JdbcFieldManager fieldManager;

	/**

	 * 

	 * Call setFile before using this

	 * function

	 * 

	 * @param lyrDef

	 * @throws IOException

	 * @throws DriverException

	 */

	public void initialize(ITableDefinition lyrD) throws EditionException {

		super.initialize(lyrD);

		this.lyrDef = (DBLayerDefinition) lyrD;

		conex = lyrDef.getConnection();

		try {

			st = conex.createStatement();

			/*

			 * y en caso de que sea false usar "CREATE TABLE IF NOT EXISTS" en el 

			 * else

			 * (AZO)

			 * 

			 * */

			if (bCreateTable) {

				try {

					st.execute("DROP TABLE " + lyrDef.getTableName() + ";");

				} catch (SQLException e1) {

				}

				//In MySQL you can add geometry column in CREATE TABLE statement

				String sqlCreate = mySql.getSqlCreateSpatialTable(lyrDef,

												lyrDef.getFieldsDesc(), 

												true);

				st.execute(sqlCreate);

				conex.commit();

			}//if

			conex.setAutoCommit(false);

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

		} catch (SQLException e) {

			e.printStackTrace();

			throw new EditionException(e);

		}

	}

	public void preProcess() throws EditionException {

		numRows = 0;

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

		try {

			conex.setAutoCommit(false);

			conex.rollback();			

			alterTable();

		} catch (SQLException e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		}

	}

	public void process(IRowEdited row) throws EditionException {

		String sqlInsert;

		try {

			switch (row.getStatus()) {

			case IRowEdited.STATUS_ADDED:

				IFeature feat = (IFeature) row.getLinkedRow();

				sqlInsert = mySql.getSqlInsertFeature(lyrDef, feat);

				st.execute(sqlInsert);

				break;

			case IRowEdited.STATUS_MODIFIED:

				IFeature featM = (IFeature) row.getLinkedRow();

				if (bWriteAll) {

					sqlInsert = mySql.getSqlInsertFeature(lyrDef, featM);

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

					st.execute(sqlInsert);

				} else {

					String sqlModify = mySql.getSqlModifyFeature(lyrDef, featM);

					st.execute(sqlModify);

				}

				break;

			case IRowEdited.STATUS_ORIGINAL:

				IFeature featO = (IFeature) row.getLinkedRow();

				if (bWriteAll) {

					sqlInsert = mySql.getSqlInsertFeature(lyrDef, featO);

					st.execute(sqlInsert);

				}

				break;

			case IRowEdited.STATUS_DELETED:

				String sqlDelete = mySql.getSqlDeleteFeature(lyrDef, row);

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

				st.execute(sqlDelete);

				break;

			}

			numRows++;

		} catch (SQLException e) {

			e.printStackTrace();

			throw new EditionException(e);

		}

	}

	public void postProcess() throws EditionException {

		try {

			conex.setAutoCommit(true);

		} catch (SQLException e) {

			e.printStackTrace();

			throw new EditionException(e);

		}

	}

	public String getName() {

		return "MySQL Writer";

	}

	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;

	}

	public boolean canWriteAttribute(int sqlType) {

		switch (sqlType) {

		case Types.DOUBLE:

		case Types.FLOAT:

		case Types.INTEGER:

		case Types.BIGINT:

			return true;

		case Types.DATE:

			return true;

		case Types.BIT:

		case Types.BOOLEAN:

			return true;

		case Types.VARCHAR:

		case Types.CHAR:

		case Types.LONGVARCHAR:

			return true; 

		}

		return false;

	}

	/**

	 * @return Returns the bCreateTable.

	 */

	public boolean isCreateTable() {

		return bCreateTable;

	}

	/**

	 * @param createTable

	 *            The bCreateTable to set.

	 */

	public void setCreateTable(boolean createTable) {

		bCreateTable = createTable;

	}

	/**

	 * @return Returns the bWriteAll.

	 */

	public boolean isWriteAll() {

		return bWriteAll;

	}

	/**

	 * @param writeAll

	 *            The bWriteAll to set.

	 */

	public void setWriteAll(boolean writeAll) {

		bWriteAll = writeAll;

	}

//	public void setFlatness(double flatness) {

//		this.flatness = flatness;

//	}

	public FieldDescription[] getOriginalFields() {

		return lyrDef.getFieldsDesc();

	}

	public void addField(FieldDescription fieldDesc) {

		fieldManager.addField(fieldDesc);

	}

	public FieldDescription removeField(String fieldName) {

		return fieldManager.removeField(fieldName);

	}

	public void renameField(String antName, String newName) {

		fieldManager.renameField(antName, newName);

	}

	public boolean alterTable() throws EditionException {

		return fieldManager.alterTable();

	}

	public FieldDescription[] getFields() {

		return fieldManager.getFields();

	}

	public boolean canAlterTable() {

		return true;

	}

	public boolean canSaveEdits() {

		// TODO: Revisar los permisos de la tabla en cuesti?n.

		try {

			return !conex.isReadOnly();

		} catch (SQLException e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

			return false;

		}

	}

}

/*

 * Created on 15-ene-2007

 *

 * 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

 */

/* CVS MESSAGES:

*

* $Id$

* $Log$

* Revision 1.1  2007-01-15 20:15:35  azabala

* *** empty log message ***

*

*

*/

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

import java.io.ByteArrayOutputStream;

import java.io.PrintStream;

import java.io.UnsupportedEncodingException;

import java.sql.Types;

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

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.core.IRow;

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

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

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

import com.vividsolutions.jts.io.WKTWriter;

/**

 * It builds sql sintax for Create, Delete, Update operations in MySQL

 * 

 * */

public class MySql {

	/**

	 * Converts a JTS geometry to WKT

	 * */

	private static WKTWriter geometryWriter = new WKTWriter();

	/**

	 * Mover esto a IverUtiles

	 * 

	 * @param val

	 * @return

	 */

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

	}

	/**

	 * It builds MySQL "CREATE TABLE" statement.

	 * 

	 * In PostGIS, its usual first create alphanumeric table, and 

	 * after that add a geometry column for geometries.

	 * 

	 * 

	 * @param dbLayerDef data of the new table (name, etc)

	 * @param fieldsDescr schema of the new table

	 * @param bCreateGID @DEPRECATED 

	 * @return SQL statement with MySQL sintax

	 */

	public String getSqlCreateSpatialTable(DBLayerDefinition dbLayerDef,

			FieldDescription[] fieldsDescr, boolean bCreateGID) {

		String result;

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

					+ " (gid serial PRIMARY KEY ";

		int j=0;

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

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

			//TODO ver si XTypes me los devuelve con la sintaxis MySQL

			String strType = XTypes.fieldTypeToString(fieldType);

			//We dont allow GID field. It is a reserved field name

			if (fieldsDescr[i].getFieldName().equalsIgnoreCase("gid"))

				continue;

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

			j++;

		}

		String geometryFieldName = dbLayerDef.getFieldGeometry();

		//TODO Ver si MySQL se traga los tipos geometricos que devuelve XTypes

		String geometryFieldType = "GEOMETRY";

		switch (dbLayerDef.getShapeType()) {

		case FShape.POINT:

			geometryFieldType = XTypes.fieldTypeToString(XTypes.POINT2D);

			break;

		case FShape.LINE:

			geometryFieldType = XTypes.fieldTypeToString(XTypes.LINE2D);

			break;

		case FShape.POLYGON:

			geometryFieldType = XTypes.fieldTypeToString(XTypes.POLYGON2D);

			break;

		case FShape.MULTI:

			geometryFieldType = XTypes.fieldTypeToString(XTypes.MULTI2D);

			break;

		}

		result += "," + geometryFieldName + " " + 

			geometryFieldType + " NOT NULL, SPATIAL INDEX(" + geometryFieldName + "))";

		return result;

	}

	protected String format(Object value) {

		String retString = null;

		if (value != null) {

			if (value instanceof NullValue)

				retString = "null";

			else{

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

			    retString += ("'" + value.toString().trim() + "',");	

			}

		} else {

			retString = "null";

		}

		return retString;

	}

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

//	}

	/**

	 * 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 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.getFieldsDesc()[i].getFieldName();

			if (name.equals(dbLayerDef.getFieldID()))

				continue;

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

		}

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

		sqlBuf.append(" ) VALUES (");

		String insertQueryHead = sqlBuf.toString();

		sqlBuf = new StringBuffer(insertQueryHead);

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

			String name = dbLayerDef.getFieldsDesc()[j].getFieldName();

			if (name.equals(dbLayerDef.getFieldID()))

				continue;

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

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

			else{

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

			}

		}//for	

		sqlBuf.append(" GeometryFromText( '"

				+ geometryWriter.write(feat.getGeometry().toJTSGeometry()) + "', "

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

		sqlBuf.append(" ) ");

		sql = sqlBuf.toString();

		return sql;

	}

//	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 String getSqlModifyFeature(DBLayerDefinition dbLayerDef, IFeature feat) {

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

				if (name.equalsIgnoreCase(dbLayerDef.getFieldID()))

					continue;

				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( '"

				+ geometryWriter.write(feat.getGeometry().toJTSGeometry()) + "', "

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

		}

		sqlBuf.append(" WHERE ");

		//TODO El feature.getID() funciona? (AZO)

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

		sql = sqlBuf.toString();

		return sql;

	}

	/**

	 * It builds MySQL's delete statement

	 * @param dbLayerDef

	 * @param row

	 * @return

	 */

	public String getSqlDeleteFeature(DBLayerDefinition dbLayerDef, IRow row) {

		// TODO: NECESITAMOS OTRO M?TODO PARA BORRAR CORRECTAMENTE.

		// Esto provocar? errores, ya que getID que viene en un row no 

		// nos sirve dentro de un writer para modificar y/o borrar entidades

		// Por ahora, cojo el ID del campo que me indica el dbLayerDev

		StringBuffer sqlBuf = new StringBuffer("DELETE FROM "

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

		String sql = null;

		int indexFieldId = dbLayerDef.getIdFieldID();

		sqlBuf.append(dbLayerDef.getFieldID() + " = " + row.getAttribute(indexFieldId));

		sql = sqlBuf.toString();

		return sql;

	}

}