Application: gvSIG desktop » gvSIG data provider for PostgreSQL

/**

 * 

 */

package org.gvsig.fmap.dal.store.postgresql;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

/**

 * @author cordin

 * 

 */

public class DatabaseMetadataChecks {

	// Columns as specified by the DatabaseMetadata.getTables javadoc

	private static final String[] COLUMNS = new String[] {

	// Metadata resultset column names

			"TABLE_CAT", // String => table catalog (may be null)

			"TABLE_SCHEM", // String => table schema (may be null)

			"TABLE_NAME", // String => table name

			"TABLE_TYPE", // String => table type. Typical types are "TABLE",

						// "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",

						// "LOCAL TEMPORARY", "ALIAS", "SYNONYM".

			"REMARKS", // String => explanatory comment on the table

			"TYPE_CAT", // String => the types catalog (may be null)

			"TYPE_SCHEM", // String => the types schema (may be null)

			"TYPE_NAME", // String => type name (may be null)

			"SELF_REFERENCING_COL_NAME", // String => name of the designated

										// "identifier" column of a typed

										// table (may be null)

			"REF_GENERATION", // String => specifies how values in

							// SELF_REFERENCING_COL_NAME are created. Values

							// are "SYSTEM", "USER", "DERIVED". (may be

							// null)

	};

	public void showTables() throws Exception {

		Connection conn = DriverManager.getConnection(

				"jdbc:postgresql://localhost/gvsigDB", "gvsig", "gvsig");

		try {

			// Statement st = conn.createStatement();

			// String sql = "select tablename from pg_tables";

			// ResultSet result = st.executeQuery(sql);

			DatabaseMetaData metadata = conn.getMetaData();

			ResultSet result = metadata.getTables(null, null, null,

					new String[] { "TABLE", "VIEW" });

			ResultSetMetaData resultMD = result.getMetaData();

			String[] columns = new String[resultMD.getColumnCount()];

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

				columns[i] = resultMD.getColumnName(i + 1);

			}

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

				System.out.print(columns[i]);

				System.out.print(";");

			}

			System.out.println();

			while (result.next()) {

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

					System.out.print(result.getString(columns[i]));

					System.out.print(";");

				}

				System.out.println();

			}

		} finally {

			try {

				conn.close();

			} catch (SQLException e) {

				e.printStackTrace();

			}

		}

	}

	/**

	 * @param args

	 */

	public static void main(String[] args) {

		try {

			new DatabaseMetadataChecks().showTables();

		} catch (Exception e) {

			e.printStackTrace();

			System.exit(-1);

		}

		System.exit(0);

	}

}

/* gvSIG. Geographic Information System of the Valencian Government

*

* Copyright (C) 2007-2008 Infrastructures and Transports Department

* of the Valencian Government (CIT)

*

* 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., 51 Franklin Street, Fifth Floor, Boston,

* MA  02110-1301, USA.

*

*/

/*

* AUTHORS (In addition to CIT):

* 2009 IVER T.I   {{Task}}

*/

/**

 *

 */

package org.gvsig.fmap.dal.store.postgresql;

import java.util.Iterator;

import org.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.feature.BaseTestEditableFeatureStore;

import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.EditableFeatureType;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureQuery;

import org.gvsig.fmap.dal.feature.FeatureStore;

import org.gvsig.fmap.dal.feature.FeatureStoreTransform;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.NewFeatureStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.operation.towkt.ToWKT;

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.evaluator.EvaluatorData;

import org.gvsig.tools.evaluator.EvaluatorException;

import org.gvsig.tools.evaluator.EvaluatorFieldsInfo;

/**

 * @author jmvivo

 *

 */

// public class TestPostgreSQL extends BaseTestEditableFeatureStore {

public class TestPostgreSQLWrite extends BaseTestEditableFeatureStore {

	private JDBCServerExplorer myExplorer;

	private PostgreSQLNewStoreParameters newParams;

	@Override

	protected void doSetUp() throws Exception {

		// Initialize wkt geom operation

		int wktCode = ToWKT.CODE;

	}

	public DataStoreParameters getDefaultDataStoreParameters()

			throws DataException {

		PostgreSQLStoreParameters pgParameters = null;

		pgParameters = (PostgreSQLStoreParameters) dataManager

				.createStoreParameters(PostgreSQLStoreProvider.NAME);

		pgParameters.setHost("localhost");

		pgParameters.setUser("postgres");

		pgParameters.setPassword("postgres");

		pgParameters.setDBName("gis");

		pgParameters.setTable("muni10000_peq");

		return pgParameters;

	}

	public boolean hasExplorer() {

		return true;

	}

	public void testLoadMetadata() throws Exception {

		DataStoreParameters params = this.getDefaultDataStoreParameters();

		FeatureStore store = null;

		store = (FeatureStore) dataManager.createStore(params);

		FeatureType fType = store.getDefaultFeatureType();

		FeatureAttributeDescriptor geomAttr;

		if (fType.getDefaultGeometryAttributeIndex() >= 0) {

			assertNotNull(store.getEnvelope());

			geomAttr = fType.getAttributeDescriptor(fType

					.getDefaultGeometryAttributeIndex());

			assertTrue(geomAttr.getGeometryType() == Geometry.TYPES.MULTISURFACE);

			assertTrue(geomAttr.getGeometrySubType() == Geometry.SUBTYPES.GEOM2D);

			assertNotNull(store.getDynValue("CRS"));

		}

	}

	public void testCloserConnection() throws Exception {

		DataStoreParameters params = this.getDefaultDataStoreParameters();

		FeatureStore store = null;

		store = (FeatureStore) dataManager.createStore(params);

		FeatureQuery query = store.createFeatureQuery();

		query.getOrder().add("gid", true);

		query.setFilter(new Evaluator() {

			public Object evaluate(EvaluatorData data)

					throws EvaluatorException {

				// TODO Auto-generated method stub

				return Boolean.TRUE;

			}

			public String getSQL() {

				return "true = true";

			}

			public String getDescription() {

				// TODO Auto-generated method stub

				return null;

			}

			public String getName() {

				return "AlwaysTrue";

			}

			public EvaluatorFieldsInfo getFieldsInfo() {

				// TODO Auto-generated method stub

				return null;

			}

		});

		FeatureStoreTransform transform = new StringsToLowerTransform();

		transform.setFeatureStore(store);

		store.getTransforms().add(transform);

		transform = new StringsToLowerTransform();

		transform.setFeatureStore(store);

		store.getTransforms().add(transform);

		transform = new StringsToLowerTransform();

		transform.setFeatureStore(store);

		store.getTransforms().add(transform);

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

			// this.fullStoreIteratorTest(store);

			this.testIterationFastAndStandart(store, query);

		}

		store.dispose();

	}

	public boolean usesResources() {

		return true;

	}

	public NewFeatureStoreParameters getDefaultNewDataStoreParameters()

			throws Exception {

		PostgreSQLStoreParameters parameters = (PostgreSQLStoreParameters) this

				.getDefaultDataStoreParameters();

		FeatureStore store = null;

		if (this.myExplorer == null) {

			store = (FeatureStore) dataManager.createStore(parameters);

			myExplorer = (JDBCServerExplorer) store.getExplorer();

		}

		if (this.newParams == null) {

			if (store == null){

				store = (FeatureStore) dataManager.createStore(parameters);

			}

			newParams = (PostgreSQLNewStoreParameters) myExplorer

					.getAddParameters();

			newParams.setTable(parameters.getTable() + "_test");

			FeatureType ftOrg = store.getDefaultFeatureType();

			EditableFeatureType ftTrg = (EditableFeatureType) newParams

					.getDefaultFeatureType();

			FeatureAttributeDescriptor org;

			EditableFeatureAttributeDescriptor trg;

			Iterator iter = ftOrg.iterator();

			while (iter.hasNext()) {

				org = (FeatureAttributeDescriptor) iter.next();

				trg = ftTrg.add(org.getName(), org.getType());

				trg.setAllowNull(org.allowNull());

				trg.setDefaultValue(org.getDefaultValue());

				trg.setGeometrySubType(org.getGeometrySubType());

				trg.setGeometryType(org.getGeometryType());

				trg.setIsAutomatic(org.isAutomatic());

				trg.setIsPrimaryKey(org.isPrimaryKey());

				trg.setIsReadOnly(org.isReadOnly());

				trg.setMaximumOccurrences(org.getMaximumOccurrences());

				trg.setMinimumOccurrences(org.getMinimumOccurrences());

				trg.setPrecision(org.getPrecision());

				trg.setSize(org.getSize());

				trg.setSRS(org.getSRS());

			}

			ftTrg.setDefaultGeometryAttributeName(ftOrg

					.getDefaultGeometryAttributeName());

			ftTrg.setHasOID(ftOrg.hasOID());

		}

		if (store != null){

			store.dispose();

		}

		return this.newParams;

	}

	public boolean resourcesNotifyChanges() {

		return false;

	}

}

/* gvSIG. Geographic Information System of the Valencian Government

*

* Copyright (C) 2007-2008 Infrastructures and Transports Department

* of the Valencian Government (CIT)

*

* 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., 51 Franklin Street, Fifth Floor, Boston,

* MA  02110-1301, USA.

*

*/

package org.gvsig.fmap.dal.store.postgresql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.MessageFormat;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.operation.fromwkb.FromWKB;

import org.gvsig.fmap.geom.operation.fromwkb.FromWKBGeometryOperationContext;

import org.gvsig.fmap.geom.operation.towkb.ToWKB;

import org.gvsig.fmap.geom.operation.towkt.ToWKT;

import org.gvsig.fmap.geom.primitive.Point;

import org.gvsig.tools.junit.AbstractLibraryAutoInitTestCase;

public class TestReadAndWriteGeom extends AbstractLibraryAutoInitTestCase {

	private static final int FEATURES_TO_INSERT = 12000;

	private static final String TABLE_NAME_INSERT = "testReadadnwritegeom_testcase";

	private static final String FIELD_NAME_INSERT = "geom";

	private Connection conn;

	private GeometryManager geoManager;

	private static String SQL_FOR_READ_TEST = "Select {0} from medio_ejes";

	@Override

	protected void doSetUp() throws Exception {

		geoManager = GeometryLocator.getGeometryManager();

		Class klass = Class.forName(PostgreSQLLibrary.DEFAULT_JDCB_DRIVER_NAME);

		if (klass == null) {

			throw new Exception("Driver not found: "

					+ PostgreSQLLibrary.DEFAULT_JDCB_DRIVER_NAME);

		}

		// inicializamos operaciones de geometrias de las que dependemos

		int code = FromWKB.CODE;

		code = ToWKB.CODE;

		code = ToWKT.CODE;

		conn = DriverManager.getConnection(PostgreSQLLibrary.getJdbcUrl(

				"localhost", 5432, "gis"), "postgres", "postgres");

	}

	public void testInsertWKB() throws Exception {

		PreparedStatement pst = null;

		try {

			crearTablaTest();

			Point geom = geoManager.createPoint(0, 0,

					Geometry.SUBTYPES.GEOM2D);

			pst = conn.prepareStatement(

					"Insert into "

					+ TABLE_NAME_INSERT.toLowerCase() +

					"  ("

					+ FIELD_NAME_INSERT + ") Values (GeomFromWKB(?))");

			int i;

			for (i = 1; i <= FEATURES_TO_INSERT; i++) {

				pst.clearParameters();

				geom.setX(i);

				geom.setY(i);

				pst.setBytes(1,

					(byte[]) geom.invokeOperation(

							ToWKB.CODE,

							null)

					);

				pst.executeUpdate();

			}

			System.out

					.println("TestReadAndWriteGeom.testInsertPostgis() Inserteds= i");

		} finally {

			if (pst != null) {

				try {pst.close();} catch (SQLException e) {e.printStackTrace();}

			}

		}

	}

	private void crearTablaTest() throws SQLException {

		execute("DROP TABLE IF EXISTS " + TABLE_NAME_INSERT.toLowerCase());

		execute("Delete from geometry_columns  where  f_table_name = '"

				+ TABLE_NAME_INSERT.toLowerCase() + "'");

		execute("CREATE TABLE " + TABLE_NAME_INSERT.toLowerCase()

				+ " (id serial PRIMARY KEY)");

		execute("Select AddGeometryColumn('" + TABLE_NAME_INSERT.toLowerCase()

				+ "','"

				+ FIELD_NAME_INSERT + "',-1,'GEOMETRY',2)");

	}

	private void execute(String sql) throws SQLException {

		Statement st = null;

		try {

			st = conn.createStatement();

			st.execute(sql);

		} finally {

			if (st != null) {

				try {st.close();} catch (SQLException e) {e.printStackTrace();}

			}

		}

	}

	public String getSQLForRead(String geoColumn) {

		return MessageFormat.format(SQL_FOR_READ_TEST, geoColumn);

	}

	public void testReadBinary() throws Exception {

		// st.execute("declare " + getTableName() + myCursorId +

		// "_wkb_cursor binary scroll cursor with hold for " + sqlAux);

		// rs = st.executeQuery("fetch forward " + FETCH_SIZE+ " in " +

		// getTableName() + myCursorId + "_wkb_cursor");

		Statement st = null;

		ResultSet rs = null;

		long count = 0;

		Geometry geom;

		try {

			st = conn.createStatement();

			String cursorName = "myCursor___xxx";

			st.execute("declare " + cursorName

					+ "_wkb_cursor binary scroll cursor with hold for "

					+ getSQLForRead("the_geom"));

			rs = st.executeQuery("fetch forward all in " + cursorName

					+ "_wkb_cursor");

			byte[] buff;

			FromWKBGeometryOperationContext opContext = new FromWKBGeometryOperationContext();

			while (rs.next()) {

				count++;

				buff = rs.getBytes(1);

			}

			System.out

					.println("TestReadAndWriteGeom.testReadPostgis() Count = "

							+ count);

		} finally {

			if (st != null) {

				try {

					st.close();

				} catch (SQLException e) {

					e.printStackTrace();

				}

			}

			if (rs != null) {

				try {

					rs.close();

				} catch (SQLException e) {

					e.printStackTrace();

				}

			}

		}

	}

	public void testReadWKB() throws Exception {

		Statement st = null;

		ResultSet rs = null;

		long count = 0;

		Geometry geom;

		byte[] buff;

		FromWKBGeometryOperationContext opContext = new FromWKBGeometryOperationContext();

		try {

			st = conn.createStatement();

			rs = st.executeQuery(getSQLForRead("asBinary(the_geom)"));

			while (rs.next()) {

				count++;

				buff = rs.getBytes(1);

			}

			System.out

					.println("TestReadAndWriteGeom.testReadPostgis() Count = "

							+ count);

		} finally {

			if (st != null) {

				try {

					st.close();

				} catch (SQLException e) {

					e.printStackTrace();

				}

			}

			if (rs != null) {

				try {

					rs.close();

				} catch (SQLException e) {

					e.printStackTrace();

				}

			}

		}

	}

//	public void testReadPostgis() throws Exception {

//		Statement st = null;

//		ResultSet rs = null;

//		long count = 0;

//		Geometry geom;

//		PGgeometry pgGeom;

//		PostGIS2Geometry converter = new PostGIS2Geometry();

//		try {

//			st = conn.createStatement();

//			rs = st.executeQuery(getSQLForRead("the_geom"));

//			while (rs.next()) {

//				count++;

//				pgGeom = (PGgeometry) rs.getObject(1);

//				if (pgGeom != null) {

//					geom = converter.getGeometry(pgGeom);

//					assertNotNull(geom);

//				}

//			}

//

//

//			System.out

//					.println("TestReadAndWriteGeom.testReadPostgis() Count = "

//							+ count);

//

//

//		} finally {

//			if (st != null) {

//				try {

//					st.close();

//				} catch (SQLException e) {

//					e.printStackTrace();

//				}

//			}

//			if (rs != null) {

//				try {

//					rs.close();

//				} catch (SQLException e) {

//					e.printStackTrace();

//				}

//			}

//		}

//	}

	protected void tearDown() throws Exception {

		super.tearDown();

		conn.close();

	}

}

/* gvSIG. Geographic Information System of the Valencian Government

*

* Copyright (C) 2007-2008 Infrastructures and Transports Department

* of the Valencian Government (CIT)

*

* 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., 51 Franklin Street, Fifth Floor, Boston,

* MA  02110-1301, USA.

*

*/

/*

* AUTHORS (In addition to CIT):

* 2009 IVER T.I   {{Task}}

*/

/**

 *

 */

package org.gvsig.fmap.dal.store.postgresql;

import org.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.feature.BaseTestFeatureStore;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureQuery;

import org.gvsig.fmap.dal.feature.FeatureStore;

import org.gvsig.fmap.dal.feature.FeatureStoreTransform;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.NewFeatureStoreParameters;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.evaluator.EvaluatorData;

import org.gvsig.tools.evaluator.EvaluatorException;

import org.gvsig.tools.evaluator.EvaluatorFieldsInfo;

/**

 * @author jmvivo

 *

 */

// public class TestPostgreSQL extends BaseTestEditableFeatureStore {

public class TestPostgreSQL extends BaseTestFeatureStore {

	@Override

	protected void doSetUp() throws Exception {

		super.doSetUp();

	}

	public DataStoreParameters getDefaultDataStoreParameters()

			throws DataException {

		PostgreSQLStoreParameters pgParameters = null;

		pgParameters = (PostgreSQLStoreParameters) dataManager

				.createStoreParameters(PostgreSQLStoreProvider.NAME);

		pgParameters.setHost("localhost");

		pgParameters.setUser("postgres");

		pgParameters.setPassword("postgres");

		pgParameters.setDBName("gis");

		pgParameters.setTable("muni10000_peq");

		return pgParameters;

	}

	public boolean hasExplorer() {

		return true;

	}

	public void testLoadMetadata() throws Exception {

		DataStoreParameters params = this.getDefaultDataStoreParameters();

		FeatureStore store = null;

		store = (FeatureStore) dataManager.createStore(params);

		FeatureType fType = store.getDefaultFeatureType();

		FeatureAttributeDescriptor geomAttr;

		if (fType.getDefaultGeometryAttributeIndex() >= 0) {

			assertNotNull(store.getEnvelope());

			geomAttr = fType.getAttributeDescriptor(fType

					.getDefaultGeometryAttributeIndex());

			assertTrue(geomAttr.getGeometryType() == Geometry.TYPES.MULTISURFACE);

			assertTrue(geomAttr.getGeometrySubType() == Geometry.SUBTYPES.GEOM2D);

			assertNotNull(store.getDynValue("CRS"));

		}

	}

	public void testCloserConnection() throws Exception {

		DataStoreParameters params = this.getDefaultDataStoreParameters();

		FeatureStore store = null;

		store = (FeatureStore) dataManager.createStore(params);

		FeatureQuery query = store.createFeatureQuery();

		query.getOrder().add("gid", true);

		query.setFilter(new Evaluator() {

			public Object evaluate(EvaluatorData data)

					throws EvaluatorException {

				// TODO Auto-generated method stub

				return Boolean.TRUE;

			}

			public String getSQL() {

				return "true = true";

			}

			public String getDescription() {

				// TODO Auto-generated method stub

				return null;

			}

			public String getName() {

				return "AlwaysTrue";

			}

			public EvaluatorFieldsInfo getFieldsInfo() {

				// TODO Auto-generated method stub

				return null;

			}

		});

		FeatureStoreTransform transform = new StringsToLowerTransform();

		transform.setFeatureStore(store);

		store.getTransforms().add(transform);

		transform = new StringsToLowerTransform();

		transform.setFeatureStore(store);

		store.getTransforms().add(transform);

		transform = new StringsToLowerTransform();

		transform.setFeatureStore(store);

		store.getTransforms().add(transform);

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

			// this.fullStoreIteratorTest(store);

			this.testIterationFastAndStandart(store, query);

		}

		store.dispose();

	}

	public boolean usesResources() {

		return true;

	}

	public NewFeatureStoreParameters getDefaultNewDataStoreParameters()

			throws Exception {

		// TODO Auto-generated method stub

		return null;

	}

}

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="PostgreSQL" namespace="Metadata">

      <extends>

      	<class name="JDBC" namespace="Metadata"/>

      </extends>

      <description>Metadata of a PostgreSQL store</description>

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="PostgreSQLResourceParameters">

      <extends>

        <class>JDBCResourceParameters</class>

      </extends>

      <fields>

        <field name="JDBCDriverClass" type="string" mandatory="true"

          defaultValue="org.postgresql.Driver" group="Advanced">

          <description>JDBC Driver class</description>

        </field>

		<field name="port" type="integer" mandatory="false"

          defaultValue="5432" group="Connection">

          <description></description>

        </field>

        <field name="UseSSL" type="boolean" mandatory="false"

          defaultValue="false" group="Basic">

          <description>Use SSL connetion</description>

        </field>

      </fields>

    </class>

    <class name="PostgreSQLStoreParameters">

      <extends>

        <class>JDBCStoreParameters</class>

        <class>PostgreSQLResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="PostgreSQLNewStoreParameters">

      <extends>

        <class>JDBCNewStoreParameters</class>

        <class>PostgreSQLResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="PostgreSQLServerExplorerParameters">

      <extends>

        <class>PostgreSQLResourceParameters</class>

        <class>JDBCServerExplorerParameters</class>

      </extends>

      <fields/>

    </class>

  </classes>

</definitions>  

org.gvsig.fmap.dal.store.postgresql.PostgreSQLLibrary

/* gvSIG. Geographic Information System of the Valencian Government

*

* Copyright (C) 2007-2008 Infrastructures and Transports Department

* of the Valencian Government (CIT)

*

* 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., 51 Franklin Street, Fifth Floor, Boston,

* MA  02110-1301, USA.

*

*/

/*

* AUTHORS (In addition to CIT):

* 2009 IVER T.I   {{Task}}

*/

/**

 *

 */

package org.gvsig.fmap.dal.store.postgresql;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Comparator;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Properties;

import java.util.TreeMap;

import java.util.TreeSet;

import org.cresques.cts.IProjection;

import org.postgresql.PGResultSetMetaData;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.NewDataStoreParameters;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.exception.InitializeException;

import org.gvsig.fmap.dal.exception.ReadException;

import org.gvsig.fmap.dal.exception.WriteException;

import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.EditableFeatureType;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.exception.CreateGeometryException;

import org.gvsig.fmap.dal.feature.exception.UnsupportedDataTypeException;

import org.gvsig.fmap.dal.resource.ResourceAction;

import org.gvsig.fmap.dal.resource.spi.ResourceManagerProviderServices;

import org.gvsig.fmap.dal.store.jdbc.ConnectionAction;

import org.gvsig.fmap.dal.store.jdbc.JDBCHelper;

import org.gvsig.fmap.dal.store.jdbc.JDBCHelperUser;

import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecutePreparedSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCPreparingSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.primitive.Envelope;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.exception.BaseException;

/**

 * @author jmvivo

 *

 */

public class PostgreSQLHelper extends JDBCHelper {

	private static Logger logger = LoggerFactory

			.getLogger(PostgreSQLHelper.class);

	private Map pgSR2SRSID = new TreeMap();

	private Map srsID2pgSR = new TreeMap();

	private static Properties beforePostgis13 = null;

    private int[] postGISVersion = { 0,0,0 };

    private boolean versionSet = false;

	PostgreSQLHelper(JDBCHelperUser consumer,

			PostgreSQLConnectionParameters params)

			throws InitializeException {

		super(consumer, params);

	}

	protected void initializeResource() throws InitializeException {

		ResourceManagerProviderServices manager = (ResourceManagerProviderServices) DALLocator

		.getResourceManager();

		PostgreSQLResource resource = (PostgreSQLResource) manager

		.createAddResource(

				PostgreSQLResource.NAME, new Object[] {

						params.getUrl(), params.getHost(),

						params.getPort(), params.getDBName(), params.getUser(),

						params.getPassword(),

						params.getJDBCDriverClassName(),

						((PostgreSQLConnectionParameters) params).getUseSSL() });

		this.setResource(resource);

	}

	protected String getDefaultSchema(Connection conn)

			throws JDBCException {

		if (defaultSchema == null) {

			String sql = "Select current_schema()";

			ResultSet rs = null;

			Statement st = null;

			String schema = null;

			try {

				st = conn.createStatement();

				try {

					rs = JDBCHelper.executeQuery(st, sql); 

				} catch (java.sql.SQLException e) {

					throw new JDBCExecuteSQLException(sql, e);

				}

				rs.next();

				schema = rs.getString(1);

			} catch (java.sql.SQLException e) {

				throw new JDBCSQLException(e);

			} finally {

				try {rs.close();} catch (Exception e) {logger.error("Exception clossing resulset", e);};

				try {st.close();} catch (Exception e) {logger.error("Exception clossing statement", e);};

				rs = null;

				st = null;

			}

			defaultSchema = schema;

		}

		return defaultSchema;

	}

    public Envelope getFullEnvelopeOfField(

            JDBCStoreParameters storeParams,

            String geometryAttrName, Envelope limit)

            throws DataException {

        StringBuilder strb = new StringBuilder();

        strb.append("Select " + getFunctionName("ST_AsBinary") + "("

                + getFunctionName("ST_Extent") + "(");

        strb.append(escapeFieldName(geometryAttrName));

        strb.append(")) from ");

        if (storeParams.getSQL() != null

                && storeParams.getSQL().trim().length() > 0) {

            strb.append('(');

            strb.append(storeParams.getSQL());

            strb.append(") as _subquery_alias_ ");

        } else {

            strb.append(storeParams.tableID());

        }

        if (limit != null || (storeParams.getBaseFilter() != null

                && storeParams.getBaseFilter().trim().length() > 0)) {

            strb.append(" where  ");

            if (limit != null) {

                strb.append(" ( " + getFunctionName("ST_Intersects") + "("

                        + getFunctionName("ST_GeomFromText") + "('");

                String workAreaWkt = null;

                try {

                    workAreaWkt = limit.getGeometry().convertToWKT();

                } catch (Exception e) {

                    throw new CreateGeometryException(e);

                }

                strb.append(workAreaWkt);

                strb.append("', ");

                IProjection proj = storeParams.getCRS();

                int sridInt = this.getProviderSRID(proj);

                if (sridInt == -1) {

                    throw new CreateGeometryException(

                            new Exception("CRS is null or unknown."));

                } else {

                    strb.append(Integer.toString(sridInt));

                }

                strb.append("), " + getFunctionName("ST_Envelope") + "(");

                strb.append(escapeFieldName(geometryAttrName));

                strb.append(")) ) ");

            }

            if (storeParams.getBaseFilter() != null && storeParams.getBaseFilter().trim().length() > 0) {

                if (limit != null) {

                    strb.append(" and ");

                }

                strb.append(" ( ");

                strb.append(storeParams.getBaseFilter());

                strb.append(" ) ");

            }

        }

        final String sql = strb.toString();

        this.open();

        return (Envelope) getResource().execute(new ResourceAction() {

            public String toString() {

                return "getEnvelope";

            }

            public Object run() throws Exception {

                ResultSet rs = null;

                Statement st = null;

                Connection conn = null;

                Envelope fullEnvelope = null;

                Envelope emptyEnv

                        = geomManager.createEnvelope(Geometry.SUBTYPES.GEOM2D);

                try {

                    conn = getConnection();

                    st = conn.createStatement();

                    try {

                        rs = JDBCHelper.executeQuery(st, sql);

                    } catch (java.sql.SQLException e) {

                        throw new JDBCExecuteSQLException(sql, e);

                    }

                    if (!rs.next()) {

                        return emptyEnv;

                    }

                    byte[] data = rs.getBytes(1);

                    if (data == null) {

                        return emptyEnv;

                    }

                    Geometry geom = geomManager.createFrom(data);

                    fullEnvelope = geom.getEnvelope();

                    return fullEnvelope;

                } catch (java.sql.SQLException e) {

                    throw new JDBCSQLException(e);

                } catch (BaseException e) {

                    throw new ReadException(user.getProviderName(), e);

                } finally {

                    try {

                        rs.close();

                    } catch (Exception e) {

                    }

                    try {

                        st.close();

                    } catch (Exception e) {

                    }

                    try {

                        conn.close();

                    } catch (Exception e) {

                    }

                    rs = null;

                    st = null;

                    conn = null;

                }

            }

        });

    }

	@Override

	protected boolean supportsGeometry() {

		return true;

	}

	/**

	 * Fill <code>featureType</code> geometry attributes with SRS and ShapeType

	 * information stored in the table GEOMETRY_COLUMNS

	 *

	 * @param conn

	 * @param rsMetadata

	 * @param featureType

	 * @throws ReadException

	 */

	protected void loadSRS_and_shapeType(Connection conn,

			ResultSetMetaData rsMetadata, EditableFeatureType featureType,

			String baseSchema, String baseTable)

			throws JDBCException {

		Statement st = null;

		ResultSet rs = null;

		try {

			// Sacamos la lista de los attributos geometricos

			EditableFeatureAttributeDescriptor attr;

			List geoAttrs = new ArrayList();

			Iterator iter = featureType.iterator();

			while (iter.hasNext()) {

				attr = (EditableFeatureAttributeDescriptor) iter.next();

				if (attr.getType() == DataTypes.GEOMETRY) {

					geoAttrs.add(attr);

				}

			}

			if (geoAttrs.size() < 1) {

				return;

			}

			// preparamos un set con las lista de tablas de origen

			// de los campos

			class TableId {

				public String schema=null;

				public String table=null;

				public String field = null;

				public void appendToSQL(StringBuilder strb) {

					if (schema == null || schema.length() == 0) {

						strb

								.append("( F_TABLE_SCHEMA = current_schema() AND F_TABLE_NAME = '");

					} else {

						strb.append("( F_TABLE_SCHEMA = '");

						strb.append(schema);

						strb.append("' AND F_TABLE_NAME = '");

					}

					strb.append(table);

					strb.append("' AND F_GEOMETRY_COLUMN = '");

					strb.append(field);

					strb.append("' )");

				}

			}

			Comparator cmp = new Comparator(){

				public int compare(Object arg0, Object arg1) {

					TableId a0 = (TableId) arg0;

					TableId a1 = (TableId) arg1;

					int aux = a0.field.compareTo(a1.field);

					if (aux != 0) {

					    return aux;

					}

					aux = a0.table.compareTo(a1.table);

                    if (aux != 0) {

                        return aux;

                    }

                    if (a0.schema == null) {

                        if (a1.schema == null) {

                            aux = 0;

                        } else {

                            aux = -1;

                        }

                    } else {

                        if (a1.schema == null) {

                            aux = -1;

                        } else {

                            aux = a0.schema.compareTo(a1.schema);

                        }

                    }

					return aux;

				}

			};

			TreeSet set = new TreeSet(cmp);

			TableId tableId;

			iter = geoAttrs.iterator();

			int rsIndex;

			while (iter.hasNext()) {

				attr = (EditableFeatureAttributeDescriptor) iter.next();

				tableId = new TableId();

				rsIndex = attr.getIndex() + 1;

				if (baseSchema == null && baseTable == null) {

					if (rsMetadata instanceof PGResultSetMetaData) {

						tableId.schema = ((PGResultSetMetaData) rsMetadata)

								.getBaseSchemaName(rsIndex);

						tableId.table = ((PGResultSetMetaData) rsMetadata)

								.getBaseTableName(rsIndex);

						tableId.field = ((PGResultSetMetaData) rsMetadata)

								.getBaseColumnName(rsIndex);

					} else {

						tableId.schema = rsMetadata.getSchemaName(rsIndex);

						tableId.table = rsMetadata.getTableName(rsIndex);

						tableId.field = rsMetadata.getColumnName(rsIndex);

					}

				} else {

					tableId.schema = baseSchema;

					tableId.table = baseTable;

					tableId.field = rsMetadata.getColumnName(rsIndex);

				}

				if (tableId.table == null || tableId.table.length() == 0) {

					// Si no tiene tabla origen (viene de algun calculo por ej.)

					// lo saltamos ya que no estara en la tabla GEOMETRY_COLUMNS

					continue;

				}

				set.add(tableId);

			}

			if (set.size() == 0) {

				return;

			}

			// Preparamos una sql para que nos saque el resultado

			StringBuilder strb = new StringBuilder();

			strb.append("Select geometry_columns.*,auth_name || ':' || auth_srid as SRSID ");

			strb.append("from geometry_columns left join spatial_ref_sys on ");

			strb.append("geometry_columns.srid = spatial_ref_sys.srid WHERE ");

			iter = set.iterator();

			for (int i=0;i<set.size()-1;i++) {

				tableId = (TableId) iter.next();

				tableId.appendToSQL(strb);

				strb.append(" OR ");

			}

			tableId = (TableId) iter.next();

			tableId.appendToSQL(strb);

			String sql = strb.toString();

			st = conn.createStatement();

			try {

				rs = JDBCHelper.executeQuery(st,sql);

			} catch (SQLException e) {

				throw new JDBCExecuteSQLException(sql, e);

			}

			String srsID;

			int pgSrid;

			int geometryType;

			int geometrySubtype;

			String geomTypeStr;

			int dimensions;

			IProjection srs;

			while (rs.next()){

				srsID = rs.getString("SRSID");

				pgSrid = rs.getInt("SRID");

				geomTypeStr = rs.getString("TYPE").toUpperCase();

				geometryType = Geometry.TYPES.GEOMETRY;

				if (geomTypeStr.startsWith("POINT")) {

					geometryType = Geometry.TYPES.POINT;

				} else if (geomTypeStr.startsWith("LINESTRING")) {

					geometryType = Geometry.TYPES.CURVE;

				} else if (geomTypeStr.startsWith("POLYGON")) {

					geometryType = Geometry.TYPES.SURFACE;

				} else if (geomTypeStr.startsWith("MULTIPOINT")) {

					geometryType = Geometry.TYPES.MULTIPOINT;

				} else if (geomTypeStr.startsWith("MULTILINESTRING")) {

					geometryType = Geometry.TYPES.MULTICURVE;

				} else if (geomTypeStr.startsWith("MULTIPOLYGON")) {

					geometryType = Geometry.TYPES.MULTISURFACE;

				}

				dimensions = rs.getInt("coord_dimension");

				geometrySubtype = Geometry.SUBTYPES.GEOM2D;

				if (dimensions > 2) {

					if (dimensions == 3) {

						if (geomTypeStr.endsWith("M")) {

							geometrySubtype = Geometry.SUBTYPES.GEOM2DM;

						} else {

							geometrySubtype = Geometry.SUBTYPES.GEOM3D;

						}

					} else {

						geometrySubtype = Geometry.SUBTYPES.GEOM3DM;

					}

				}

				addToPgSRToSRSID(pgSrid, srsID);

				iter = geoAttrs.iterator();

				while (iter.hasNext()) {

					attr = (EditableFeatureAttributeDescriptor) iter.next();

					rsIndex = attr.getIndex() + 1;

					if (!rsMetadata.getColumnName(rsIndex).equals(

							rs.getString("f_geometry_column"))) {

						continue;

					}

					if (baseSchema == null && baseTable == null) {

						if (rsMetadata instanceof PGResultSetMetaData) {

							if (!((PGResultSetMetaData) rsMetadata)

									.getBaseTableName(rsIndex).equals(

											rs.getString("f_table_name"))) {

								continue;

							}

							String curSchema = rs.getString("f_table_schema");

							String metaSchema = ((PGResultSetMetaData) rsMetadata)

									.getBaseSchemaName(rsIndex);

							if (!metaSchema.equals(curSchema)) {

								if (metaSchema.length() == 0

										&& metaSchema == getDefaultSchema(conn)) {

								} else {

									continue;

								}

							}

						} else {

							if (!rsMetadata.getTableName(rsIndex).equals(

									rs.getString("f_table_name"))) {

								continue;

							}

							String curSchema = rs.getString("f_table_schema");

							String metaSchema = rsMetadata

									.getSchemaName(rsIndex);

							if (!metaSchema.equals(curSchema)) {

								if (metaSchema.length() == 0

										&& metaSchema == getDefaultSchema(conn)) {

								} else {

									continue;

								}

							}

						}

					}

					attr.setGeometryType(geometryType);

					attr.setGeometrySubType(geometrySubtype);

					if (srsID != null && srsID.length() > 0) {

						attr.setSRS(CRSFactory.getCRS(srsID));

					}

					iter.remove();

				}

				iter = geoAttrs.iterator();

				while (iter.hasNext()) {

					attr = (EditableFeatureAttributeDescriptor) iter.next();

					attr.setSRS(null);

					attr.setGeometryType(Geometry.TYPES.GEOMETRY);

				}

			}

		} catch (java.sql.SQLException e) {

			throw new JDBCSQLException(e);

		} finally {

			try {rs.close();} catch (Exception e) {	};

			try {st.close();} catch (Exception e) {	};

		}

	}

	public String getSqlColumnTypeDescription(FeatureAttributeDescriptor attr) {

		if (attr.getType() == DataTypes.GEOMETRY) {

			return "geometry";

		}

		return super.getSqlColumnTypeDescription(attr);

	}

	public int getPostgisGeomDimensions(int geometrySubType) {

		switch (geometrySubType) {

		case Geometry.SUBTYPES.GEOM2D:

			return 2;

		case Geometry.SUBTYPES.GEOM2DM:

		case Geometry.SUBTYPES.GEOM3D:

			return 3;

		case Geometry.SUBTYPES.GEOM3DM:

			return 4;

		default:

			throw new UnsupportedDataTypeException(

					ToolsLocator.getDataTypesManager().getTypeName(DataTypes.GEOMETRY),

					DataTypes.GEOMETRY);

		}

	}

	public String getPostgisGeomType(int geometryType, int geometrySubType) {

		String pgGeomType;

		switch (geometryType) {

		case Geometry.TYPES.GEOMETRY:

			pgGeomType = "GEOMETRY";

			break;

		case Geometry.TYPES.POINT:

			pgGeomType = "POINT";

			break;

		case Geometry.TYPES.CURVE:

			pgGeomType = "LINESTRING";

			break;

		case Geometry.TYPES.SURFACE:

			pgGeomType = "POLYGON";

			break;

		case Geometry.TYPES.MULTIPOINT:

			pgGeomType = "MULTIPOINT";

			break;

		case Geometry.TYPES.MULTICURVE:

			pgGeomType = "MULTILINESTRING";

			break;

		case Geometry.TYPES.MULTISURFACE:

			pgGeomType = "MULTIPOLYGON";

			break;

		default:

                    logger.warn("Can't determine PostGIS geometry type, use GEOMETRY.");

                    pgGeomType = "GEOMETRY";

		}

		if (geometrySubType == Geometry.SUBTYPES.GEOM2DM