001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2002-2006, GeoTools Project Managment Committee (PMC)
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation;
009: * version 2.1 of the License.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: */
016: package org.geotools.data.postgis;
017:
018: import java.io.IOException;
019: import java.sql.Connection;
020: import java.sql.DatabaseMetaData;
021: import java.sql.ResultSet;
022: import java.sql.ResultSetMetaData;
023: import java.sql.SQLException;
024: import java.sql.Statement;
025:
026: import javax.sql.DataSource;
027:
028: import org.geotools.data.DataStore;
029: import org.geotools.data.DataTestCase;
030: import org.geotools.data.jdbc.ConnectionPool;
031: import org.geotools.data.jdbc.datasource.DataSourceUtil;
032: import org.geotools.data.jdbc.datasource.ManageableDataSource;
033: import org.geotools.data.jdbc.fidmapper.BasicFIDMapper;
034: import org.geotools.data.jdbc.fidmapper.TypedFIDMapper;
035: import org.geotools.feature.Feature;
036:
037: import com.vividsolutions.jts.geom.Geometry;
038:
039: public class AbstractPostgisDataTestCase extends DataTestCase {
040:
041: static boolean WKB_ENABLED = true;
042: static boolean CHECK_TYPE = false;
043:
044: PostgisTests.Fixture f;
045: ManageableDataSource pool;
046: DataStore data;
047:
048: public AbstractPostgisDataTestCase(String name) {
049: super (name);
050: }
051:
052: public String getFixtureFile() {
053: return "fixture.properties";
054: }
055:
056: protected void setUp() throws Exception {
057: super .setUp();
058:
059: f = PostgisTests.newFixture(getFixtureFile());
060: pool = PostgisDataStoreFactory.getDefaultDataSource(f.host,
061: f.user, f.password, f.port.intValue(), f.database, 10,
062: 2, false);
063:
064: setupDbTables();
065:
066: if (CHECK_TYPE) {
067: checkTypesInDataBase();
068: CHECK_TYPE = false; // just once
069: }
070:
071: data = newDataStore();
072:
073: }
074:
075: protected void setupDbTables() throws Exception {
076: setUpRoadTable();
077: setUpRiverTable();
078: setUpLakeTable();
079: }
080:
081: protected DataStore newDataStore() throws IOException {
082: PostgisDataStore pg = new PostgisDataStore(pool, f.schema,
083: getName(), PostgisDataStore.OPTIMIZE_SQL);
084: pg.setWKBEnabled(WKB_ENABLED);
085: pg.setEstimatedExtent(true);
086: pg.setFIDMapper("road", new TypedFIDMapper(new BasicFIDMapper(
087: "fid", 255, false), "road"));
088: pg.setFIDMapper("river", new TypedFIDMapper(new BasicFIDMapper(
089: "fid", 255, false), "river"));
090: pg.setFIDMapper("testset", new TypedFIDMapper(
091: new BasicFIDMapper("gid", 255, true), "testset"));
092: return pg;
093: }
094:
095: protected void tearDown() throws Exception {
096: data.dispose();
097: data = null;
098: super .tearDown();
099: }
100:
101: protected void checkTypesInDataBase() throws SQLException {
102: Connection conn = pool.getConnection();
103:
104: try {
105: DatabaseMetaData md = conn.getMetaData();
106: ResultSet rs =
107: //md.getTables( catalog, null, null, null );
108: md
109: .getTables(null, "public", "%",
110: new String[] { "TABLE", });
111: ResultSetMetaData rsmd = rs.getMetaData();
112: int NUM = rsmd.getColumnCount();
113: System.out.print(" ");
114:
115: for (int i = 1; i <= NUM; i++) {
116: System.out.print(rsmd.getColumnName(i));
117: System.out.flush();
118: System.out.print(":");
119: System.out.flush();
120: System.out.print(rsmd.getColumnClassName(i));
121: System.out.flush();
122:
123: if (i < NUM) {
124: System.out.print(",");
125: System.out.flush();
126: }
127: }
128:
129: System.out.println();
130:
131: while (rs.next()) {
132: System.out.print(rs.getRow());
133: System.out.print(":");
134: System.out.flush();
135:
136: for (int i = 1; i <= NUM; i++) {
137: System.out.print(rsmd.getColumnName(i));
138: System.out.flush();
139: System.out.print("=");
140: System.out.flush();
141: System.out.print(rs.getString(i));
142: System.out.flush();
143:
144: if (i < NUM) {
145: System.out.print(",");
146: System.out.flush();
147: }
148: }
149:
150: System.out.println();
151: }
152: } finally {
153: conn.close();
154: }
155: }
156:
157: protected void setUpRoadTable() throws Exception {
158: Connection conn = pool.getConnection();
159: conn.setAutoCommit(true);
160:
161: try {
162: Statement s = conn.createStatement();
163: s.execute("SELECT dropgeometrycolumn( '" + f.schema
164: + "','road','geom')");
165: } catch (Exception ignore) {
166: }
167:
168: try {
169: Statement s = conn.createStatement();
170: s.execute("DROP TABLE " + f.schema + ".road CASCADE");
171: } catch (Exception ignore) {
172: }
173:
174: try {
175: Statement s = conn.createStatement();
176:
177: //postgis = new PostgisDataSource(connection, FEATURE_TABLE);
178: s.execute("CREATE TABLE " + f.schema
179: + ".road (fid varchar PRIMARY KEY, id int )");
180: s.execute("SELECT AddGeometryColumn('" + f.schema
181: + "', 'road', 'geom', 0, 'LINESTRING', 2);");
182: s.execute("ALTER TABLE " + f.schema
183: + ".road add name varchar;");
184:
185: for (int i = 0; i < roadFeatures.length; i++) {
186: Feature feature = roadFeatures[i];
187:
188: //strip out the road.
189: String fid = feature.getID()
190: .substring("road.".length());
191: String ql = "INSERT INTO "
192: + f.schema
193: + ".road (fid,id,geom,name) VALUES ("
194: + "'"
195: + fid
196: + "',"
197: + feature.getAttribute("id")
198: + ","
199: + "GeometryFromText('"
200: + ((Geometry) feature.getAttribute("geom"))
201: .toText() + "', 0 )," + "'"
202: + feature.getAttribute("name") + "')";
203:
204: s.execute(ql);
205: }
206:
207: s.execute("VACUUM ANALYZE " + f.schema + ".road");
208: } finally {
209: conn.close();
210: }
211: }
212:
213: protected void setUpLakeTable() throws Exception {
214: Connection conn = pool.getConnection();
215: conn.setAutoCommit(true);
216:
217: try {
218: Statement s = conn.createStatement();
219: s.execute("SELECT dropgeometrycolumn( '" + f.schema
220: + "','lake','geom')");
221: } catch (Exception ignore) {
222: }
223:
224: try {
225: Statement s = conn.createStatement();
226: s.execute("DROP TABLE " + f.schema + ".lake CASCADE");
227: } catch (Exception ignore) {
228: }
229:
230: try {
231: Statement s = conn.createStatement();
232:
233: //postgis = new PostgisDataSource(connection, FEATURE_TABLE);
234: s.execute("CREATE TABLE " + f.schema
235: + ".lake ( id int ) WITH OIDS");
236: s.execute("SELECT AddGeometryColumn('" + f.schema
237: + "', 'lake', 'geom', 0, 'POLYGON', 2);");
238: s.execute("ALTER TABLE " + f.schema
239: + ".lake add name varchar;");
240:
241: for (int i = 0; i < lakeFeatures.length; i++) {
242: Feature feature = lakeFeatures[i];
243:
244: //strip out the lake.
245: String ql = "INSERT INTO "
246: + f.schema
247: + ".lake (id,geom,name) VALUES ("
248: + feature.getAttribute("id")
249: + ","
250: + "GeometryFromText('"
251: + ((Geometry) feature.getAttribute("geom"))
252: .toText() + "', 0 )," + "'"
253: + feature.getAttribute("name") + "')";
254:
255: s.execute(ql);
256: }
257:
258: s.execute("VACUUM ANALYZE " + f.schema + ".lake");
259: } finally {
260: conn.close();
261: }
262: }
263:
264: protected void killTestTables() throws Exception {
265: Connection conn = pool.getConnection();
266:
267: try {
268: Statement s = conn.createStatement();
269:
270: try {
271: s.execute("SELECT dropgeometrycolumn( '" + f.schema
272: + "','road','geom')");
273: } catch (Exception ignore) {
274: }
275:
276: try {
277: s.execute("SELECT dropgeometrycolumn( '" + f.schema
278: + "','river','geom')");
279: } catch (Exception ignore) {
280: }
281:
282: try {
283: s.execute("SELECT dropgeometrycolumn( '" + f.schema
284: + "','lake','geom')");
285: } catch (Exception ignore) {
286: }
287:
288: try {
289: s.execute("DROP TABLE " + f.schema + ".road");
290: } catch (Exception ignore) {
291: }
292:
293: try {
294: s.execute("DROP TABLE " + f.schema + ".river");
295: } catch (Exception ignore) {
296: }
297:
298: try {
299: s.execute("DROP TABLE " + f.schema + ".lake");
300: } catch (Exception ignore) {
301: }
302:
303: } finally {
304: conn.close();
305: }
306: }
307:
308: protected void setUpRiverTable() throws Exception {
309: Connection conn = pool.getConnection();
310:
311: try {
312: Statement s = conn.createStatement();
313: s.execute("SELECT dropgeometrycolumn( '" + f.schema
314: + "','river','geom')");
315: } catch (Exception ignore) {
316: }
317:
318: try {
319: Statement s = conn.createStatement();
320: s.execute("DROP TABLE " + f.schema + ".river CASCADE");
321: } catch (Exception ignore) {
322: }
323:
324: try {
325: Statement s = conn.createStatement();
326:
327: //postgis = new PostgisDataSource(connection, FEATURE_TABLE);
328: s.execute("CREATE TABLE " + f.schema
329: + ".river(fid varchar PRIMARY KEY, id int)");
330: s.execute("SELECT AddGeometryColumn('" + f.schema
331: + "', 'river', 'geom', 0, 'MULTILINESTRING', 2);");
332: s.execute("ALTER TABLE " + f.schema
333: + ".river add river varchar");
334: s.execute("ALTER TABLE " + f.schema
335: + ".river add flow float8");
336:
337: for (int i = 0; i < riverFeatures.length; i++) {
338: Feature feature = riverFeatures[i];
339: String fid = feature.getID().substring(
340: "river.".length());
341: s
342: .execute("INSERT INTO "
343: + f.schema
344: + ".river (fid, id, geom, river, flow) VALUES ("
345: + "'"
346: + fid
347: + "',"
348: + feature.getAttribute("id")
349: + ","
350: + "GeometryFromText('"
351: + feature.getAttribute("geom")
352: .toString() + "', 0 )," + "'"
353: + feature.getAttribute("river") + "',"
354: + feature.getAttribute("flow") + ")");
355: }
356:
357: s.execute("VACUUM ANALYZE " + f.schema + ".river");
358: } finally {
359: conn.close();
360: }
361: }
362:
363: }
|