001: /*
002: * Geotools2 - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2002, 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: */
017: package org.geotools.arcsde.data;
018:
019: import java.io.IOException;
020: import java.util.Arrays;
021: import java.util.Iterator;
022: import java.util.List;
023: import java.util.logging.Logger;
024:
025: import junit.extensions.TestSetup;
026: import junit.framework.Test;
027: import junit.framework.TestCase;
028: import junit.framework.TestSuite;
029: import net.sf.jsqlparser.statement.select.PlainSelect;
030: import net.sf.jsqlparser.statement.select.SelectBody;
031:
032: import org.geotools.arcsde.pool.ArcSDEPooledConnection;
033: import org.geotools.data.DefaultQuery;
034: import org.geotools.data.FeatureSource;
035: import org.geotools.data.Query;
036: import org.geotools.feature.AttributeType;
037: import org.geotools.feature.Feature;
038: import org.geotools.feature.FeatureCollection;
039: import org.geotools.feature.FeatureType;
040: import org.geotools.filter.text.cql2.CQL;
041: import org.opengis.filter.Filter;
042: import org.opengis.referencing.FactoryException;
043: import org.opengis.referencing.NoSuchAuthorityCodeException;
044:
045: import com.esri.sde.sdk.client.SeException;
046: import com.esri.sde.sdk.client.SeQuery;
047: import com.esri.sde.sdk.client.SeQueryInfo;
048: import com.esri.sde.sdk.client.SeRow;
049: import com.esri.sde.sdk.client.SeShape;
050: import com.esri.sde.sdk.client.SeSqlConstruct;
051: import com.vividsolutions.jts.geom.Envelope;
052: import com.vividsolutions.jts.geom.Point;
053:
054: /**
055: * ArcSDEDAtaStore test case for a master-child joining
056: * <p>
057: * This test will create an sde layer (table + spatial table) as master and a
058: * business table as child:
059: *
060: * <pre><code>
061: * -----------------------------------------------
062: * | GT_SDE_TEST_MASTER |
063: * -----------------------------------------------
064: * | ID(int) | NAME (string) | SHAPE (Point) |
065: * -----------------------------------------------
066: * | 1 | name1 | POINT(1, 1) |
067: * -----------------------------------------------
068: * | 2 | name2 | POINT(2, 2) |
069: * -----------------------------------------------
070: * | 3 | name3 | POINT(3, 3) |
071: * -----------------------------------------------
072: *
073: * ---------------------------------------------------------------------
074: * | GT_SDE_TEST_CHILD |
075: * ---------------------------------------------------------------------
076: * | ID(int) | MASTER_ID | NAME (string) | DESCRIPTION(string |
077: * ---------------------------------------------------------------------
078: * | 1 | 1 | child1 | description1 |
079: * ---------------------------------------------------------------------
080: * | 2 | 2 | child2 | description2 |
081: * ---------------------------------------------------------------------
082: * | 3 | 2 | child3 | description3 |
083: * ---------------------------------------------------------------------
084: * | 4 | 3 | child4 | description4 |
085: * ---------------------------------------------------------------------
086: * | 5 | 3 | child5 | description5 |
087: * ---------------------------------------------------------------------
088: * | 6 | 3 | child6 | description6 |
089: * ---------------------------------------------------------------------
090: * </code>
091: * </re>
092: * </p>
093: * <p>
094: * The following are rules that may help you in correctly specifying an SQL
095: * query that will work with the ArcSDE Java API. This rules was collected
096: * empirically based on some of the tests of this test suite. Be aware that
097: * ArcSDE Java API only supports "queries" of the following form:
098: * <code>
099: * SELECT <list of qualified column names>
100: * FROM <list of qualified table names>
101: * WHERE <any where clause supported by the RDBMS>
102: * [ORDER BY <qualified column names>]
103: * </code>
104: * Rules to create SQL QUERIES:
105: * <ul>
106: * <li>
107: * Use full qualified table names. Queries that usually would work against the
108: * underlying RDBMS will not work through the ArcSDE Java API if you do not fully
109: * qualify table names.
110: * <li>
111: * Do not use table aliases, or SHAPE field is fetched as int instead of as geometry.
112: * <li>
113: * Specifying a GROUP BY clause seems incompatible with using the SHAPE field. If
114: * you specify a GROUP BY clause, ArcSDE will return the plain SHAPE field (int) instead
115: * of a geometry.
116: * <li>
117: * And the <strong>most important</strong> one: <strong>SET THE SPATIAL COLUMN AS
118: * THE LAST ONE</strong>. This is most likely a bug in the ArcSDE Java API, since if you
119: * do not set the shape field as the last one in the select items list an IndexOutOfBoundsException
120: * is thrown by
121: * <code>
122: * SeRow.fetch()
123: * </code>
124: * </ul>
125: * </p>
126: *
127: * @author Gabriel Roldan, Axios Engineering
128: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/unsupported/arcsde/datastore/src/test/java/org/geotools/arcsde/data/SDEJavaApiJoinTest.java $
129: * @version $Id: SDEJavaApiJoinTest.java 27863 2007-11-12 20:34:34Z desruisseaux $
130: * @since 2.3.x
131: *
132: */
133: public class SDEJavaApiJoinTest extends TestCase {
134: /** package logger */
135: private static Logger LOGGER = org.geotools.util.logging.Logging
136: .getLogger(SDEJavaApiJoinTest.class.getPackage().getName());
137:
138: /** Helper class that provides config loading and test data for unit tests */
139: private static TestData testData;
140:
141: /** an ArcSDEDataStore created on setUp() to run tests against */
142: private ArcSDEDataStore store;
143:
144: /**
145: * Builds a test suite for all this class' tests with per suite
146: * initialization directed to {@link #oneTimeSetUp()} and per suite clean up
147: * directed to {@link #oneTimeTearDown()}
148: *
149: * @return
150: */
151: public static Test suite() {
152: TestSuite suite = new TestSuite();
153: suite.addTestSuite(SDEJavaApiJoinTest.class);
154:
155: TestSetup wrapper = new TestSetup(suite) {
156: protected void setUp() throws IOException, SeException,
157: NoSuchAuthorityCodeException, FactoryException {
158: oneTimeSetUp();
159: }
160:
161: protected void tearDown() {
162: oneTimeTearDown();
163: }
164: };
165: return wrapper;
166: }
167:
168: /**
169: * Initialization code for the whole test suite
170: *
171: * @throws IOException
172: * @throws SeException
173: * @throws FactoryException
174: * @throws NoSuchAuthorityCodeException
175: */
176: public static void oneTimeSetUp() throws IOException, SeException,
177: NoSuchAuthorityCodeException, FactoryException {
178: testData = new TestData();
179: testData.setUp();
180:
181: ArcSDEPooledConnection conn = testData.getConnectionPool()
182: .getConnection();
183: try {
184: InProcessViewSupportTestData.setUp(conn);
185: } finally {
186: conn.close();
187: }
188: }
189:
190: /**
191: * Tear down code for the whole suite
192: */
193: public static void oneTimeTearDown() {
194: final boolean cleanTestTable = true;
195: final boolean cleanPool = true;
196: testData.tearDown(cleanTestTable, cleanPool);
197: testData = null;
198: }
199:
200: /**
201: * loads {@code testData/testparams.properties} into a Properties object,
202: * wich is used to obtain test tables names and is used as parameter to find
203: * the DataStore
204: *
205: * @throws Exception
206: * DOCUMENT ME!
207: */
208: protected void setUp() throws Exception {
209: super .setUp();
210: this .store = testData.getDataStore();
211: }
212:
213: /**
214: * DOCUMENT ME!
215: *
216: * @throws Exception
217: * DOCUMENT ME!
218: */
219: protected void tearDown() throws Exception {
220: super .tearDown();
221: }
222:
223: /*
224: * public void testBorehole() throws Exception { final String typeName =
225: * "JoinedBoreholes"; final String definitionQuery = "SELECT " + " B.QS,
226: * B.NUMB, B.BSUFF, B.RT, B.BGS_ID, B.NAME, B.ORIGINAL_N, B.CONFIDENTI,
227: * B.LENGTHC," + " G.LITHOSTRAT, G.LITHOLOGY_, G.BASE_BED_C, G.DRILLED_DE,
228: * G.DRILLED__1, B.SHAPE" + " FROM SCO.LOUGHBOROUGH_BORES B,
229: * SCO.LOUGHBOROUGH_BORE_GEOL G" + " WHERE (B.QS = G.QS AND B.NUMB = G.NUMB
230: * AND B.BSUFF = G.BSUFF AND B.RT = G.RT)" + " ORDER BY B.QS, B.RT, B.NUMB,
231: * B.BSUFF";
232: *
233: * try { store.registerView(typeName, definitionQuery); } catch (Exception
234: * e) { e.printStackTrace(); throw e; }
235: *
236: * SimpleFeatureType type = (SimpleFeatureType) store.getSchema(typeName);
237: * assertNotNull(type);
238: *
239: * FeatureSource fs = store.getFeatureSource(typeName); assertNotNull(fs);
240: * int count = fs.getCount(Query.ALL); final int expected = 16479;
241: * assertEquals(expected, count); }
242: */
243:
244: /**
245: * Assert that the datastore complains on views with non supported features
246: */
247: public void testRegisterIllegalView() throws IOException {
248: final String typeName = "badQuery";
249: String plainSql;
250: plainSql = "(SELECT * FROM mytable) UNION (SELECT * FROM mytable2 WHERE mytable2.col = 9)";
251: SelectBody select;
252: try {
253: select = ViewRegisteringFactoryHelper
254: .parseSqlQuery(plainSql);
255: fail("should complain on union");
256: } catch (UnsupportedOperationException e) {
257: // OK
258: }
259: plainSql = "SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.parent_id";
260: try {
261: select = ViewRegisteringFactoryHelper
262: .parseSqlQuery(plainSql);
263: store.registerView(typeName, (PlainSelect) select);
264: fail("should complain on join");
265: } catch (UnsupportedOperationException e) {
266: // OK
267: }
268: plainSql = "SELECT f1,f2,f3 FROM t1 GROUP BY f1,f2";
269: try {
270: select = ViewRegisteringFactoryHelper
271: .parseSqlQuery(plainSql);
272: store.registerView(typeName, (PlainSelect) select);
273: fail("should complain on group by");
274: } catch (UnsupportedOperationException e) {
275: // OK
276: }
277: /*
278: * Looks like jsqlparser is not parsing the INTO directive plainSql =
279: * "SELECT f1,f2 INTO TEMP FROM t1"; try{ store.registerView(typeName,
280: * plainSql); fail("should complain on into");
281: * }catch(UnsupportedOperationException e){ //OK }
282: */
283: plainSql = "SELECT f1,f2,f3 FROM t1 LIMIT 10";
284: try {
285: select = ViewRegisteringFactoryHelper
286: .parseSqlQuery(plainSql);
287: store.registerView(typeName, (PlainSelect) select);
288: fail("should complain on limit");
289: } catch (UnsupportedOperationException e) {
290: // OK
291: }
292: }
293:
294: /**
295: * Fail if tried to register the same view name more than once
296: */
297: public void testRegisterDuplicateViewName() throws IOException {
298: final String plainSQL = InProcessViewSupportTestData.masterChildSql;
299:
300: SelectBody select = ViewRegisteringFactoryHelper
301: .parseSqlQuery(plainSQL);
302: store.registerView(InProcessViewSupportTestData.typeName,
303: (PlainSelect) select);
304: try {
305: store.registerView(InProcessViewSupportTestData.typeName,
306: (PlainSelect) select);
307: fail("Expected IAE on duplicate view name");
308: } catch (IllegalArgumentException e) {
309: assertTrue(true);
310: }
311: }
312:
313: public void testRegisterViewListedInGetTypeNames()
314: throws IOException {
315: final String plainSQL = InProcessViewSupportTestData.masterChildSql;
316:
317: SelectBody select = ViewRegisteringFactoryHelper
318: .parseSqlQuery(plainSQL);
319: store.registerView(InProcessViewSupportTestData.typeName,
320: (PlainSelect) select);
321:
322: List publishedTypeNames = Arrays.asList(store.getTypeNames());
323: assertTrue(publishedTypeNames
324: .contains(InProcessViewSupportTestData.typeName));
325: }
326:
327: public void testRegisterViewBuildsCorrectFeatureType()
328: throws IOException {
329: final String plainSQL = "SELECT "
330: + InProcessViewSupportTestData.MASTER_UNQUALIFIED
331: + ".*, "
332: + InProcessViewSupportTestData.CHILD_UNQUALIFIED
333: + ".DESCRIPTION FROM "
334: + InProcessViewSupportTestData.MASTER_UNQUALIFIED
335: + ", " + InProcessViewSupportTestData.CHILD_UNQUALIFIED
336: + " WHERE "
337: + InProcessViewSupportTestData.CHILD_UNQUALIFIED
338: + ".MASTER_ID = "
339: + InProcessViewSupportTestData.MASTER_UNQUALIFIED
340: + ".ID";
341:
342: SelectBody select = ViewRegisteringFactoryHelper
343: .parseSqlQuery(plainSQL);
344: store.registerView(InProcessViewSupportTestData.typeName,
345: (PlainSelect) select);
346:
347: FeatureType type = store
348: .getSchema(InProcessViewSupportTestData.typeName);
349: assertNotNull(type);
350:
351: assertEquals(InProcessViewSupportTestData.typeName, type
352: .getTypeName());
353:
354: assertEquals(4, type.getAttributeCount());
355: List atts = Arrays.asList(type.getAttributeTypes());
356: assertEquals(4, atts.size());
357: AttributeType att1 = (AttributeType) atts.get(0);
358: AttributeType att2 = (AttributeType) atts.get(1);
359: AttributeType att3 = (AttributeType) atts.get(2);
360: AttributeType att4 = (AttributeType) atts.get(3);
361:
362: assertEquals("ID", att1.getName());
363: assertEquals("NAME", att2.getName());
364: assertEquals("SHAPE", att3.getName());
365: assertEquals("DESCRIPTION", att4.getName());
366:
367: assertEquals(Integer.class, att1.getType());
368: assertEquals(String.class, att2.getType());
369: assertEquals(Point.class, att3.getType());
370: assertEquals(String.class, att4.getType());
371: }
372:
373: public void testViewBounds() throws IOException {
374: SelectBody select = ViewRegisteringFactoryHelper
375: .parseSqlQuery(InProcessViewSupportTestData.masterChildSql);
376: store.registerView(InProcessViewSupportTestData.typeName,
377: (PlainSelect) select);
378:
379: FeatureSource fs = store
380: .getFeatureSource(InProcessViewSupportTestData.typeName);
381: assertNotNull(fs);
382: Envelope bounds = fs.getBounds();
383: assertNotNull(bounds);
384: assertEquals(1D, bounds.getMinX(), 0);
385: assertEquals(1D, bounds.getMinY(), 0);
386: assertEquals(3D, bounds.getMaxX(), 0);
387: assertEquals(3D, bounds.getMaxY(), 0);
388: }
389:
390: public void testViewBoundsQuery() throws Exception {
391: SelectBody select = ViewRegisteringFactoryHelper
392: .parseSqlQuery(InProcessViewSupportTestData.masterChildSql);
393: store.registerView(InProcessViewSupportTestData.typeName,
394: (PlainSelect) select);
395:
396: FeatureSource fs = store
397: .getFeatureSource(InProcessViewSupportTestData.typeName);
398: assertNotNull(fs);
399:
400: String cqlQuery = "NAME='name2' OR DESCRIPTION='description4'";
401: Filter filter = (Filter) CQL.toFilter(cqlQuery);
402: DefaultQuery query = new DefaultQuery(
403: InProcessViewSupportTestData.typeName, filter);
404:
405: Envelope bounds = fs.getBounds(query);
406:
407: assertNotNull(bounds);
408: assertEquals(2D, bounds.getMinX(), 0);
409: assertEquals(2D, bounds.getMinY(), 0);
410: assertEquals(3D, bounds.getMaxX(), 0);
411: assertEquals(3D, bounds.getMaxY(), 0);
412: }
413:
414: public void testViewCount() throws Exception {
415: SelectBody select = ViewRegisteringFactoryHelper
416: .parseSqlQuery(InProcessViewSupportTestData.masterChildSql);
417: store.registerView(InProcessViewSupportTestData.typeName,
418: (PlainSelect) select);
419:
420: FeatureSource fs = store
421: .getFeatureSource(InProcessViewSupportTestData.typeName);
422: assertNotNull(fs);
423: int count = fs.getCount(Query.ALL);
424: final int expected = 7;
425: assertEquals(expected, count);
426: }
427:
428: public void testViewCountQuery() throws Exception {
429: SelectBody select = ViewRegisteringFactoryHelper
430: .parseSqlQuery(InProcessViewSupportTestData.masterChildSql);
431: store.registerView(InProcessViewSupportTestData.typeName,
432: (PlainSelect) select);
433:
434: FeatureSource fs = store
435: .getFeatureSource(InProcessViewSupportTestData.typeName);
436: assertNotNull(fs);
437:
438: String cqlQuery = "NAME='name2' OR DESCRIPTION='description4'";
439: Filter filter = (Filter) CQL.toFilter(cqlQuery);
440: DefaultQuery query = new DefaultQuery(
441: InProcessViewSupportTestData.typeName, filter);
442:
443: int count = fs.getCount(query);
444: final int expected = 3;
445: assertEquals(expected, count);
446: }
447:
448: public void testReadView() throws Exception {
449: SelectBody select = ViewRegisteringFactoryHelper
450: .parseSqlQuery(InProcessViewSupportTestData.masterChildSql);
451: store.registerView(InProcessViewSupportTestData.typeName,
452: (PlainSelect) select);
453:
454: FeatureSource fs = store
455: .getFeatureSource(InProcessViewSupportTestData.typeName);
456:
457: DefaultQuery query = new DefaultQuery(
458: InProcessViewSupportTestData.typeName, Filter.INCLUDE,
459: null);
460: FeatureCollection fc = fs.getFeatures(query);
461: int fcCount = fc.size();
462: int itCount = 0;
463: final int expectedCount = 7;
464: Iterator it = fc.iterator();
465: while (it.hasNext()) {
466: Feature f = (Feature) it.next();
467: assertNotNull(f);
468: itCount++;
469: }
470: fc.close(it);
471: assertEquals(expectedCount, fcCount);
472: assertEquals(expectedCount, itCount);
473: }
474:
475: public void testQueryView() throws Exception {
476: SelectBody select = ViewRegisteringFactoryHelper
477: .parseSqlQuery(InProcessViewSupportTestData.masterChildSql);
478: store.registerView(InProcessViewSupportTestData.typeName,
479: (PlainSelect) select);
480:
481: String cqlQuery = "NAME='name2' OR DESCRIPTION='description6'";
482: Filter filter = (Filter) CQL.toFilter(cqlQuery);
483: DefaultQuery query = new DefaultQuery(
484: InProcessViewSupportTestData.typeName, filter);
485:
486: FeatureSource fs = store
487: .getFeatureSource(InProcessViewSupportTestData.typeName);
488: FeatureCollection fc = fs.getFeatures(query);
489: int fcCount = fc.size();
490: int itCount = 0;
491: final int expectedCount = 3;
492: Iterator it = fc.iterator();
493: while (it.hasNext()) {
494: Feature f = (Feature) it.next();
495: assertNotNull(f);
496: itCount++;
497: }
498: assertEquals(expectedCount, fcCount);
499: assertEquals(expectedCount, itCount);
500: }
501:
502: /**
503: * Meant as example to be sure we're using the ArcSDE java api correctly
504: *
505: * @throws Exception
506: */
507: public void testApiOrderBy() throws Exception {
508: ArcSDEPooledConnection conn = store.getConnectionPool()
509: .getConnection();
510:
511: SeSqlConstruct sqlConstruct = new SeSqlConstruct();
512: String[] tables = { InProcessViewSupportTestData.MASTER,
513: InProcessViewSupportTestData.CHILD };
514: sqlConstruct.setTables(tables);
515: String where = InProcessViewSupportTestData.CHILD
516: + ".MASTER_ID = " + InProcessViewSupportTestData.MASTER
517: + ".ID";
518: sqlConstruct.setWhere(where);
519:
520: // tricky part is that SHAPE column must always be the last one
521: String[] propertyNames = {
522: "(SELECT AVG(ID) AS myid2 FROM "
523: + InProcessViewSupportTestData.CHILD
524: + ") AS AVG",
525: InProcessViewSupportTestData.MASTER + ".NAME AS MNAME",
526: InProcessViewSupportTestData.CHILD + ".ID",
527: InProcessViewSupportTestData.CHILD + ".NAME",
528: InProcessViewSupportTestData.CHILD + ".DESCRIPTION",
529: InProcessViewSupportTestData.MASTER + ".SHAPE" };
530: final int shapeIndex = 5;
531: final int expectedCount = 7;
532:
533: SeQuery query = new SeQuery(conn);
534:
535: SeQueryInfo queryInfo = new SeQueryInfo();
536: queryInfo.setConstruct(sqlConstruct);
537: queryInfo.setColumns(propertyNames);
538: queryInfo.setByClause(" ORDER BY "
539: + InProcessViewSupportTestData.CHILD + ".ID DESC");
540: final int[] expectedShapeIndicators = {
541: SeRow.SE_IS_NOT_NULL_VALUE, // child7
542: SeRow.SE_IS_REPEATED_FEATURE, // child6
543: SeRow.SE_IS_REPEATED_FEATURE, // child5
544: SeRow.SE_IS_REPEATED_FEATURE, // child4
545: SeRow.SE_IS_NOT_NULL_VALUE, // child3
546: SeRow.SE_IS_REPEATED_FEATURE, // child2
547: SeRow.SE_IS_NOT_NULL_VALUE // child1
548: };
549: try {
550: query.prepareQueryInfo(queryInfo);
551: query.execute();
552: SeRow row = query.fetch();
553: int count = 0;
554: while (row != null) {
555: // duplicate shapes are not returned by arcsde.
556: // in that case indicator has the value
557: // SeRow.SE_IS_REPEATED_FEATURE
558: int indicator = row.getIndicator(shapeIndex);
559:
560: assertEquals("at index " + count,
561: expectedShapeIndicators[count], indicator);
562:
563: if (SeRow.SE_IS_NOT_NULL_VALUE == indicator) {
564: Object shape = row.getObject(shapeIndex);
565: assertTrue(shape.getClass().getName(),
566: shape instanceof SeShape);
567: }
568:
569: count++;
570: row = query.fetch();
571: }
572: assertEquals(expectedCount, count);
573: } catch (Exception e) {
574: e.printStackTrace();
575: throw e;
576: } finally {
577: conn.close();
578: }
579: }
580:
581: /**
582: * Using table alias leads to ArcSDE returning SHAPE id instead of SHAPE
583: * geometry.
584: *
585: * @throws Exception
586: */
587: public void testApiAlias() throws Exception {
588: ArcSDEPooledConnection conn = store.getConnectionPool()
589: .getConnection();
590:
591: SeSqlConstruct sqlConstruct = new SeSqlConstruct();
592: String[] tables = {
593: InProcessViewSupportTestData.MASTER + " AS MASTER",
594: InProcessViewSupportTestData.CHILD + " AS CHILD" };
595: sqlConstruct.setTables(tables);
596: String where = "CHILD.MASTER_ID = MASTER.ID";
597: sqlConstruct.setWhere(where);
598:
599: // tricky part is that SHAPE column must always be the last one
600: String[] propertyNames = { "MASTER.ID", "CHILD.NAME",
601: "MASTER.SHAPE" };
602:
603: final int shapeIndex = 2;
604: final int expectedCount = 7;
605:
606: SeQuery query = new SeQuery(conn);
607:
608: SeQueryInfo queryInfo = new SeQueryInfo();
609: queryInfo.setConstruct(sqlConstruct);
610: queryInfo.setColumns(propertyNames);
611:
612: try {
613: query.prepareQueryInfo(queryInfo);
614: query.execute();
615: SeRow row = query.fetch();
616: int count = 0;
617: while (row != null) {
618: // we would expect SeShape being returned from shapeIndex, but
619: // ArcSDE returns shape id
620: if (SeRow.SE_IS_NOT_NULL_VALUE == row
621: .getIndicator(shapeIndex)) {
622: Object shape = row.getObject(shapeIndex);
623: // assertTrue(shape.getClass().getName(), shape instanceof
624: // SeShape);
625: assertFalse(shape.getClass().getName(),
626: shape instanceof SeShape);
627: }
628: count++;
629: row = query.fetch();
630: }
631: assertEquals(expectedCount, count);
632: } catch (Exception e) {
633: e.printStackTrace();
634: throw e;
635: } finally {
636: conn.close();
637: }
638: }
639:
640: /**
641: * Meant as example to be sure we're using the ArcSDE java api correctly
642: * Nasty thing about group by is that is seems that we cannot include/use
643: * the geometry column :(
644: *
645: * @throws Exception
646: */
647: public void testApiGroupBy() throws Exception {
648: ArcSDEPooledConnection conn = store.getConnectionPool()
649: .getConnection();
650:
651: SeSqlConstruct sqlConstruct = new SeSqlConstruct();
652: String[] tables = { InProcessViewSupportTestData.MASTER,
653: InProcessViewSupportTestData.CHILD };
654: sqlConstruct.setTables(tables);
655: String where = InProcessViewSupportTestData.CHILD
656: + ".MASTER_ID = " + InProcessViewSupportTestData.MASTER
657: + ".ID";
658: sqlConstruct.setWhere(where);
659:
660: // tricky part is that SHAPE column must always be the last one
661: String[] propertyNames = {
662: InProcessViewSupportTestData.MASTER + ".ID",
663: InProcessViewSupportTestData.CHILD + ".NAME" /*
664: * , MASTER +
665: * ".SHAPE"
666: */
667: };
668:
669: final int shapeIndex = 5;
670: final int expectedCount = 6;
671:
672: SeQuery query = new SeQuery(conn);
673:
674: SeQueryInfo queryInfo = new SeQueryInfo();
675: queryInfo.setConstruct(sqlConstruct);
676: queryInfo.setColumns(propertyNames);
677:
678: String groupBy = InProcessViewSupportTestData.MASTER + ".ID, "
679: + InProcessViewSupportTestData.CHILD + ".NAME, "
680: + InProcessViewSupportTestData.MASTER + ".SHAPE";
681:
682: queryInfo.setByClause(" GROUP BY " + groupBy + " ORDER BY "
683: + InProcessViewSupportTestData.CHILD + ".NAME DESC");
684:
685: final int[] expectedShapeIndicators = {
686: SeRow.SE_IS_NOT_NULL_VALUE, // child6
687: // (&&
688: // child7)
689: SeRow.SE_IS_REPEATED_FEATURE, // child5
690: SeRow.SE_IS_REPEATED_FEATURE, // child4
691: SeRow.SE_IS_NOT_NULL_VALUE, // child3
692: SeRow.SE_IS_REPEATED_FEATURE, // child2
693: SeRow.SE_IS_NOT_NULL_VALUE // child1
694: };
695: try {
696: query.prepareQueryInfo(queryInfo);
697: query.execute();
698: SeRow row = query.fetch();
699: int count = 0;
700: while (row != null) {
701: // duplicate shapes are not returned by arcsde.
702: // in that case indicator has the value
703: // SeRow.SE_IS_REPEATED_FEATURE
704: // int indicator = row.getIndicator(shapeIndex);
705:
706: // assertEquals("at index " + count,
707: // expectedShapeIndicators[count], indicator);
708:
709: count++;
710: row = query.fetch();
711: }
712: assertEquals(expectedCount, count);
713: } catch (Exception e) {
714: e.printStackTrace();
715: throw e;
716: } finally {
717: conn.close();
718: }
719: }
720:
721: /**
722: * Meant as example to be sure we're using the ArcSDE java api correctly. We
723: * can execute a plain sql query, but shapes are not returned by ArcSDE.
724: * Instead, the SHAPE field contains the SHAPE id, just like in the real
725: * business table.
726: *
727: * @throws Exception
728: */
729: public void testApiPlainSql() throws Exception {
730: ArcSDEPooledConnection conn = store.getConnectionPool()
731: .getConnection();
732:
733: final SeQuery query = new SeQuery(conn);
734: final String plainQuery = "SELECT "
735: + InProcessViewSupportTestData.MASTER + ".ID, "
736: + InProcessViewSupportTestData.MASTER + ".SHAPE, "
737: + InProcessViewSupportTestData.CHILD + ".NAME FROM "
738: + InProcessViewSupportTestData.MASTER + " INNER JOIN "
739: + InProcessViewSupportTestData.CHILD + " ON "
740: + InProcessViewSupportTestData.CHILD + ".MASTER_ID = "
741: + InProcessViewSupportTestData.MASTER + ".ID";
742:
743: final int shapeIndex = 1;
744: final int expectedCount = 7;
745: try {
746: query.prepareSql(plainQuery);
747: query.execute();
748: SeRow row = query.fetch();
749: int count = 0;
750: while (row != null) {
751: Object shape = row.getObject(shapeIndex);
752: assertTrue(shape instanceof Integer); // returns int instead
753: // of shape
754: count++;
755: row = query.fetch();
756: }
757: assertEquals(expectedCount, count);
758: } catch (Exception e) {
759: e.printStackTrace();
760: throw e;
761: } finally {
762: conn.close();
763: }
764: }
765:
766: }
|