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.Statement;
021:
022: import org.geotools.data.DataTestCase;
023: import org.geotools.data.DataUtilities;
024: import org.geotools.data.jdbc.datasource.DataSourceUtil;
025: import org.geotools.data.jdbc.datasource.ManageableDataSource;
026: import org.geotools.feature.Feature;
027: import org.geotools.feature.FeatureType;
028:
029: import com.vividsolutions.jts.geom.Coordinate;
030: import com.vividsolutions.jts.geom.Envelope;
031: import com.vividsolutions.jts.geom.Geometry;
032:
033: public class AbstractVersionedPostgisDataTestCase extends DataTestCase {
034: PostgisTests.Fixture f;
035:
036: ManageableDataSource pool;
037:
038: VersionedPostgisDataStore store;
039:
040: PostgisConnectionFactory pcFactory;
041:
042: protected FeatureType railType;
043: protected FeatureType treeType;
044:
045: protected Feature[] railFeatures;
046: protected Feature[] treeFeatures;
047:
048: protected Envelope railBounds;
049: protected Envelope treeBounds;
050:
051: public AbstractVersionedPostgisDataTestCase(String name) {
052: super (name);
053: }
054:
055: public String getFixtureFile() {
056: return "versioned.properties";
057: }
058:
059: protected void setUp() throws Exception {
060: super .setUp();
061:
062: f = PostgisTests.newFixture(getFixtureFile());
063:
064: String url = "jdbc:postgresql" + "://" + f.host + ":" + f.port
065: + "/" + f.database;
066: pool = DataSourceUtil.buildDefaultDataSource(url,
067: "org.postgresql.Driver", f.user, f.password, 20, 1,
068: "select now()", false, -1);
069:
070: // make sure versioned metadata is not in the way
071: SqlTestUtils.dropTable(pool,
072: VersionedPostgisDataStore.TBL_TABLESCHANGED, false);
073: SqlTestUtils.dropTable(pool,
074: VersionedPostgisDataStore.TBL_VERSIONEDTABLES, false);
075: SqlTestUtils.dropTable(pool,
076: VersionedPostgisDataStore.TBL_CHANGESETS, true);
077: SqlTestUtils.execute(pool, "DELETE FROM geometry_columns");
078:
079: setUpLakeTable();
080: setUpRiverTable();
081: setUpRoadTable();
082: setUpRailTable();
083: setUpNoPrimaryKeyTable();
084: setUpTreeTable();
085: setUpEmptyTable();
086: setUpPointTable();
087: }
088:
089: protected void dataSetUp() throws Exception {
090: super .dataSetUp();
091:
092: railType = DataUtilities.createType(getName() + ".rail",
093: "geom:LineString:nillable;srid=4326");
094: railFeatures = new Feature[1];
095: // 0,0 +-----------+ 10,10
096: railFeatures[0] = railType.create(
097: new Object[] { line(new int[] { 0, 0, 10, 10 }) },
098: "rail.1");
099: railBounds = new Envelope();
100: railBounds.expandToInclude(railFeatures[0].getBounds());
101:
102: treeType = DataUtilities.createType(getName() + ".tree",
103: "geom:Point:nillable,name:String");
104: treeFeatures = new Feature[1];
105: treeFeatures[0] = treeType.create(new Object[] {
106: gf.createPoint(new Coordinate(5, 5)), "BigPine" },
107: "tree.tr1");
108: treeBounds = new Envelope();
109: treeBounds.expandToInclude(treeFeatures[0].getBounds());
110: }
111:
112: protected VersionedPostgisDataStore getDataStore()
113: throws IOException {
114: if (store == null) {
115: store = buildDataStore();
116: }
117: return store;
118: }
119:
120: /**
121: * Builds a brand new datastore
122: *
123: * @return
124: * @throws IOException
125: */
126: protected VersionedPostgisDataStore buildDataStore()
127: throws IOException {
128: VersionedPostgisDataStore ds = new VersionedPostgisDataStore(
129: pool, f.schema, getName(),
130: PostgisDataStore.OPTIMIZE_SQL);
131: ds.setWKBEnabled(true);
132: return ds;
133: }
134:
135: protected void tearDown() throws Exception {
136: if (store != null)
137: store.dispose();
138: store = null;
139: pool.close();
140: super .tearDown();
141: }
142:
143: protected void setUpTreeTable() throws Exception {
144: Connection conn = pool.getConnection();
145: conn.setAutoCommit(true);
146:
147: try {
148: Statement s = conn.createStatement();
149: s.execute("SELECT dropgeometrycolumn( '" + f.schema
150: + "','tree','geom')");
151: } catch (Exception ignore) {
152: }
153:
154: try {
155: Statement s = conn.createStatement();
156: s.execute("DROP TABLE " + f.schema + ".tree cascade");
157: } catch (Exception ignore) {
158: }
159:
160: try {
161: Statement s = conn.createStatement();
162:
163: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
164: s.execute("CREATE TABLE " + f.schema
165: + ".tree ( id serial primary key)");
166: s.execute("SELECT AddGeometryColumn('" + f.schema
167: + "', 'tree', 'geom', 0, 'POINT', 2);");
168: s.execute("ALTER TABLE " + f.schema
169: + ".tree add name varchar;");
170:
171: for (int i = 0; i < treeFeatures.length; i++) {
172: Feature feature = treeFeatures[i];
173:
174: // strip out the lake.
175: String ql = "INSERT INTO "
176: + f.schema
177: + ".tree (geom,name) VALUES ("
178: + "GeometryFromText('"
179: + ((Geometry) feature.getAttribute("geom"))
180: .toText() + "', 0 )," + "'"
181: + feature.getAttribute("name") + "')";
182:
183: s.execute(ql);
184: }
185: } finally {
186: conn.close();
187: }
188: }
189:
190: protected void setUpRoadTable() throws Exception {
191: Connection conn = pool.getConnection();
192: conn.setAutoCommit(true);
193:
194: try {
195: Statement s = conn.createStatement();
196: s.execute("SELECT dropgeometrycolumn( '" + f.schema
197: + "','road','geom')");
198: } catch (Exception ignore) {
199: }
200:
201: try {
202: Statement s = conn.createStatement();
203: s.execute("DROP TABLE " + f.schema + ".road cascade");
204: } catch (Exception ignore) {
205: }
206:
207: try {
208: Statement s = conn.createStatement();
209:
210: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
211: s
212: .execute("CREATE TABLE "
213: + f.schema
214: + ".road (fid varchar PRIMARY KEY, id int ) WITH OIDS");
215: s.execute("SELECT AddGeometryColumn('" + f.schema
216: + "', 'road', 'geom', 0, 'LINESTRING', 2);");
217: s.execute("ALTER TABLE " + f.schema
218: + ".road add name varchar;");
219:
220: for (int i = 0; i < roadFeatures.length; i++) {
221: Feature feature = roadFeatures[i];
222:
223: // strip out the road.
224: String fid = feature.getID()
225: .substring("road.".length());
226: String ql = "INSERT INTO "
227: + f.schema
228: + ".road (fid,id,geom,name) VALUES ("
229: + "'"
230: + fid
231: + "',"
232: + feature.getAttribute("id")
233: + ","
234: + "GeometryFromText('"
235: + ((Geometry) feature.getAttribute("geom"))
236: .toText() + "', 0 )," + "'"
237: + feature.getAttribute("name") + "')";
238:
239: s.execute(ql);
240: }
241: } finally {
242: conn.close();
243: }
244: }
245:
246: protected void setUpLakeTable() throws Exception {
247: Connection conn = pool.getConnection();
248: conn.setAutoCommit(true);
249:
250: try {
251: Statement s = conn.createStatement();
252: s.execute("SELECT dropgeometrycolumn( '" + f.schema
253: + "','lake','geom')");
254: } catch (Exception ignore) {
255: }
256:
257: try {
258: Statement s = conn.createStatement();
259: s.execute("DROP TABLE " + f.schema + ".lake cascade");
260: } catch (Exception ignore) {
261: }
262:
263: try {
264: Statement s = conn.createStatement();
265:
266: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
267: s.execute("CREATE TABLE " + f.schema
268: + ".lake ( id int ) WITH OIDS");
269: s.execute("SELECT AddGeometryColumn('" + f.schema
270: + "', 'lake', 'geom', 0, 'POLYGON', 2);");
271: s.execute("ALTER TABLE " + f.schema
272: + ".lake add name varchar;");
273:
274: for (int i = 0; i < lakeFeatures.length; i++) {
275: Feature feature = lakeFeatures[i];
276:
277: // strip out the lake.
278: String ql = "INSERT INTO "
279: + f.schema
280: + ".lake (id,geom,name) VALUES ("
281: + feature.getAttribute("id")
282: + ","
283: + "GeometryFromText('"
284: + ((Geometry) feature.getAttribute("geom"))
285: .toText() + "', 0 )," + "'"
286: + feature.getAttribute("name") + "')";
287:
288: s.execute(ql);
289: }
290: } finally {
291: conn.close();
292: }
293: }
294:
295: protected void setUpRailTable() throws Exception {
296: Connection conn = pool.getConnection();
297: conn.setAutoCommit(true);
298:
299: try {
300: Statement s = conn.createStatement();
301: s.execute("SELECT dropgeometrycolumn( '" + f.schema
302: + "','rail','geom')");
303: } catch (Exception ignore) {
304: }
305:
306: try {
307: Statement s = conn.createStatement();
308: s.execute("DROP TABLE " + f.schema + ".rail cascade");
309: } catch (Exception ignore) {
310: }
311:
312: try {
313: Statement s = conn.createStatement();
314:
315: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
316: s.execute("CREATE TABLE " + f.schema
317: + ".rail ( id serial primary key ) WITH OIDS");
318: s.execute("SELECT AddGeometryColumn('" + f.schema
319: + "', 'rail', 'geom', 4326, 'LINESTRING', 2);");
320:
321: for (int i = 0; i < railFeatures.length; i++) {
322: Feature feature = railFeatures[i];
323:
324: // strip out the lake.
325: String ql = "INSERT INTO "
326: + f.schema
327: + ".rail (geom) VALUES ("
328: + "GeometryFromText('"
329: + ((Geometry) feature.getAttribute("geom"))
330: .toText() + "', 4326 ))";
331:
332: s.execute(ql);
333: }
334: } finally {
335: conn.close();
336: }
337: }
338:
339: protected void setUpNoPrimaryKeyTable() throws Exception {
340: Connection conn = pool.getConnection();
341: conn.setAutoCommit(true);
342:
343: try {
344: Statement s = conn.createStatement();
345: s.execute("SELECT dropgeometrycolumn( '" + f.schema
346: + "','nopk','geom')");
347: } catch (Exception ignore) {
348: }
349:
350: try {
351: Statement s = conn.createStatement();
352: s.execute("DROP TABLE " + f.schema + ".nopk cascade");
353: } catch (Exception ignore) {
354: }
355:
356: try {
357: Statement s = conn.createStatement();
358:
359: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
360: s.execute("CREATE TABLE " + f.schema
361: + ".nopk ( id int ) WITHOUT OIDS");
362: s.execute("SELECT AddGeometryColumn('" + f.schema
363: + "', 'nopk', 'geom', 0, 'POLYGON', 2);");
364: s.execute("ALTER TABLE " + f.schema
365: + ".nopk add name varchar;");
366: } finally {
367: conn.close();
368: }
369: }
370:
371: // protected void killTestTables() throws Exception {
372: // Connection conn = pool.getConnection();
373: //
374: // try {
375: // Statement s = conn.createStatement();
376: //
377: // try {
378: // s.execute("SELECT dropgeometrycolumn( '" + f.schema + "','road','geom')");
379: // } catch (Exception ignore) {
380: // }
381: //
382: // try {
383: // s.execute("SELECT dropgeometrycolumn( '" + f.schema + "','river','geom')");
384: // } catch (Exception ignore) {
385: // }
386: //
387: // try {
388: // s.execute("SELECT dropgeometrycolumn( '" + f.schema + "','lake','geom')");
389: // } catch (Exception ignore) {
390: // }
391: //
392: // try {
393: // s.execute("SELECT dropgeometrycolumn( '" + f.schema + "','rail','geom')");
394: // } catch (Exception ignore) {
395: // }
396: //
397: // try {
398: // s.execute("SELECT dropgeometrycolumn( '" + f.schema + "','empty','geom')");
399: // } catch (Exception ignore) {
400: // }
401: //
402: // try {
403: // s.execute("SELECT dropgeometrycolumn( '" + f.schema + "','point','geom')");
404: // } catch (Exception ignore) {
405: // }
406: //
407: // try {
408: // s.execute("DROP TABLE " + f.schema + ".road");
409: // } catch (Exception ignore) {
410: // }
411: //
412: // try {
413: // s.execute("DROP TABLE " + f.schema + ".river");
414: // } catch (Exception ignore) {
415: // }
416: //
417: // try {
418: // s.execute("DROP TABLE " + f.schema + ".lake");
419: // } catch (Exception ignore) {
420: // }
421: //
422: // try {
423: // s.execute("DROP TABLE " + f.schema + ".rail");
424: // } catch (Exception ignore) {
425: // }
426: //
427: // try {
428: // s.execute("DROP TABLE " + f.schema + ".empty");
429: // } catch (Exception ignore) {
430: // }
431: //
432: // try {
433: // s.execute("DROP TABLE " + f.schema + ".point");
434: // } catch (Exception ignore) {
435: // }
436: //
437: // } finally {
438: // conn.close();
439: // }
440: // }
441:
442: protected void setUpRiverTable() throws Exception {
443: Connection conn = pool.getConnection();
444:
445: try {
446: Statement s = conn.createStatement();
447: s.execute("SELECT dropgeometrycolumn( '" + f.schema
448: + "','river','geom')");
449: } catch (Exception ignore) {
450: }
451:
452: try {
453: Statement s = conn.createStatement();
454: s.execute("DROP TABLE " + f.schema + ".river cascade");
455: } catch (Exception ignore) {
456: }
457:
458: try {
459: Statement s = conn.createStatement();
460:
461: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
462: s
463: .execute("CREATE TABLE "
464: + f.schema
465: + ".river(fid varchar PRIMARY KEY, id int) WITH OIDS");
466: s.execute("SELECT AddGeometryColumn('" + f.schema
467: + "', 'river', 'geom', 0, 'MULTILINESTRING', 2);");
468: s.execute("ALTER TABLE " + f.schema
469: + ".river add river varchar");
470: s.execute("ALTER TABLE " + f.schema
471: + ".river add flow float8");
472:
473: for (int i = 0; i < riverFeatures.length; i++) {
474: Feature feature = riverFeatures[i];
475: String fid = feature.getID().substring(
476: "river.".length());
477: s
478: .execute("INSERT INTO "
479: + f.schema
480: + ".river (fid, id, geom, river, flow) VALUES ("
481: + "'"
482: + fid
483: + "',"
484: + feature.getAttribute("id")
485: + ","
486: + "GeometryFromText('"
487: + feature.getAttribute("geom")
488: .toString() + "', 0 )," + "'"
489: + feature.getAttribute("river") + "',"
490: + feature.getAttribute("flow") + ")");
491: }
492: } finally {
493: conn.close();
494: }
495: }
496:
497: protected void setUpEmptyTable() throws Exception {
498: Connection conn = pool.getConnection();
499:
500: try {
501: Statement s = conn.createStatement();
502: s.execute("SELECT dropgeometrycolumn( '" + f.schema
503: + "','empty','geom')");
504: } catch (Exception ignore) {
505: }
506:
507: try {
508: Statement s = conn.createStatement();
509: s.execute("DROP TABLE " + f.schema + ".empty cascade");
510: } catch (Exception ignore) {
511: }
512:
513: try {
514: Statement s = conn.createStatement();
515:
516: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
517: s.execute("CREATE TABLE " + f.schema
518: + ".empty(fid varchar PRIMARY KEY, id int)");
519: s.execute("SELECT AddGeometryColumn('" + f.schema
520: + "', 'empty', 'geom', 0, 'POINT', 2);");
521: } finally {
522: conn.close();
523: }
524: }
525:
526: protected void setUpPointTable() throws Exception {
527: Connection conn = pool.getConnection();
528:
529: try {
530: Statement s = conn.createStatement();
531: s.execute("SELECT dropgeometrycolumn( '" + f.schema
532: + "','point','geom')");
533: } catch (Exception ignore) {
534: }
535:
536: try {
537: Statement s = conn.createStatement();
538: s.execute("DROP TABLE " + f.schema + ".point cascade");
539: } catch (Exception ignore) {
540: }
541:
542: try {
543: Statement s = conn.createStatement();
544:
545: // postgis = new PostgisDataSource(connection, FEATURE_TABLE);
546: s.execute("CREATE TABLE " + f.schema
547: + ".point(fid varchar PRIMARY KEY, id int)");
548: s.execute("SELECT AddGeometryColumn('" + f.schema
549: + "', 'point', 'geom', 4326, 'POINT', 2);");
550:
551: s.execute("INSERT INTO " + f.schema
552: + ".point (fid, id, geom) VALUES (" + "'point1',1,"
553: + "GeometryFromText('POINT (0.0 0.0)',4326))");
554: } finally {
555: conn.close();
556: }
557: }
558: }
|