svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.h2spatial / org.gvsig.h2spatial.h2gis132 / org.gvsig.h2spatial.h2gis132.provider / src / test / java / org / gvsig / fmap / dal / store / h2 / operations / sql / TestResultSetForSetProvider.java @ 46543
History | View | Annotate | Download (28.6 KB)
| 1 | 45472 | jjdelcerro | package org.gvsig.fmap.dal.store.h2.operations.sql; |
|---|---|---|---|
| 2 | |||
| 3 | import java.util.List; |
||
| 4 | import junit.framework.TestCase; |
||
| 5 | 46050 | omartinez | import org.gvsig.expressionevaluator.ExpressionUtils; |
| 6 | 46517 | fdiaz | import org.gvsig.fmap.dal.DALLocator; |
| 7 | import org.gvsig.fmap.dal.DataManager; |
||
| 8 | 46050 | omartinez | import org.gvsig.fmap.dal.DataTypes; |
| 9 | import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
||
| 10 | import org.gvsig.fmap.dal.feature.EditableFeatureType; |
||
| 11 | 45472 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureQuery; |
| 12 | import org.gvsig.fmap.dal.feature.FeatureStore; |
||
| 13 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
| 14 | 46050 | omartinez | import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureAttributeEmulatorExpression; |
| 15 | 45472 | jjdelcerro | import org.gvsig.fmap.dal.store.h2.TestUtils; |
| 16 | 46507 | jjdelcerro | import org.gvsig.fmap.dal.store.h2.TestUtilsH2Spatial; |
| 17 | import org.gvsig.fmap.dal.store.jdbc2.AbstractTestUtils; |
||
| 18 | import org.gvsig.fmap.dal.store.jdbc2.AbstractTestUtils.Expecteds; |
||
| 19 | 45472 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
| 20 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
||
| 21 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
||
| 22 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
| 23 | import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation; |
||
| 24 | import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
||
| 25 | import org.slf4j.Logger; |
||
| 26 | import org.slf4j.LoggerFactory; |
||
| 27 | |||
| 28 | 46507 | jjdelcerro | @SuppressWarnings({"UseSpecificCatch", "CallToPrintStackTrace"}) |
| 29 | 45472 | jjdelcerro | public class TestResultSetForSetProvider extends TestCase { |
| 30 | |||
| 31 | 46104 | omartinez | private static final Logger LOGGER = LoggerFactory.getLogger(TestResultSetForSetProvider.class); |
| 32 | 45472 | jjdelcerro | |
| 33 | 46104 | omartinez | public TestResultSetForSetProvider(String testName) { |
| 34 | super(testName);
|
||
| 35 | } |
||
| 36 | 45472 | jjdelcerro | |
| 37 | 46104 | omartinez | @Override
|
| 38 | protected void setUp() throws Exception { |
||
| 39 | super.setUp();
|
||
| 40 | new DefaultLibrariesInitializer().fullInitialize();
|
||
| 41 | } |
||
| 42 | 45472 | jjdelcerro | |
| 43 | 46104 | omartinez | @Override
|
| 44 | protected void tearDown() throws Exception { |
||
| 45 | super.tearDown();
|
||
| 46 | } |
||
| 47 | 46507 | jjdelcerro | |
| 48 | protected AbstractTestUtils utils;
|
||
| 49 | |||
| 50 | public AbstractTestUtils utils() {
|
||
| 51 | if (this.utils == null) { |
||
| 52 | this.utils = this.createUtils(); |
||
| 53 | } |
||
| 54 | return this.utils; |
||
| 55 | } |
||
| 56 | 45472 | jjdelcerro | |
| 57 | 46507 | jjdelcerro | protected AbstractTestUtils createUtils() {
|
| 58 | return new TestUtilsH2Spatial(); |
||
| 59 | } |
||
| 60 | |||
| 61 | 46104 | omartinez | public void testSimple() throws Exception { |
| 62 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 63 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 64 | OperationsFactory operations = helper.getOperations(); |
||
| 65 | 45472 | jjdelcerro | |
| 66 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 67 | 45472 | jjdelcerro | |
| 68 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 69 | 45472 | jjdelcerro | |
| 70 | 46104 | omartinez | TableReference table = operations.createTableReference( |
| 71 | "dbtest",
|
||
| 72 | sqlbuilder.default_schema(), |
||
| 73 | "test",
|
||
| 74 | null
|
||
| 75 | ); |
||
| 76 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 77 | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
||
| 78 | table, |
||
| 79 | null,
|
||
| 80 | null,
|
||
| 81 | null,
|
||
| 82 | featureType, |
||
| 83 | featureType, |
||
| 84 | 0,
|
||
| 85 | 0,
|
||
| 86 | 0
|
||
| 87 | ); |
||
| 88 | String sql = resultSetForSetProvider.getSQL();
|
||
| 89 | 46507 | jjdelcerro | utils().output_results("testSimple",sql,expectedSQLs.get("testSimple")); |
| 90 | utils().runSQLToCheckSyntax("testSimple", sourceStore, table.getTable(), sql);
|
||
| 91 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSimple"), sql); |
||
| 92 | 46104 | omartinez | } |
| 93 | 46050 | omartinez | |
| 94 | 46104 | omartinez | public void testComputedAttribute() throws Exception { |
| 95 | try {
|
||
| 96 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 97 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 98 | OperationsFactory operations = helper.getOperations(); |
||
| 99 | 46050 | omartinez | |
| 100 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 101 | 46050 | omartinez | |
| 102 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 103 | 46050 | omartinez | |
| 104 | 46104 | omartinez | TableReference table = operations.createTableReference( |
| 105 | "dbtest",
|
||
| 106 | sqlbuilder.default_schema(), |
||
| 107 | "test",
|
||
| 108 | null
|
||
| 109 | ); |
||
| 110 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 111 | EditableFeatureType eFeatureType = featureType.getEditable(); |
||
| 112 | eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
||
| 113 | 46050 | omartinez | |
| 114 | 46104 | omartinez | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 115 | table, |
||
| 116 | null,
|
||
| 117 | null,
|
||
| 118 | null,
|
||
| 119 | eFeatureType, |
||
| 120 | eFeatureType, |
||
| 121 | 0,
|
||
| 122 | 0,
|
||
| 123 | 0
|
||
| 124 | ); |
||
| 125 | String sql = resultSetForSetProvider.getSQL();
|
||
| 126 | 46507 | jjdelcerro | utils().output_results("testComputedAttribute",sql,expectedSQLs.get("testComputedAttribute")); |
| 127 | utils().runSQLToCheckSyntax("testComputedAttribute", sourceStore, table.getTable(), sql);
|
||
| 128 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedAttribute"), sql); |
||
| 129 | 46104 | omartinez | } catch (Exception ex) { |
| 130 | ex.printStackTrace(); |
||
| 131 | throw ex;
|
||
| 132 | } |
||
| 133 | } |
||
| 134 | 46050 | omartinez | |
| 135 | 46104 | omartinez | public void testComputedAttribute2() throws Exception { |
| 136 | try {
|
||
| 137 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 138 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 139 | OperationsFactory operations = helper.getOperations(); |
||
| 140 | 46050 | omartinez | |
| 141 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 142 | 46050 | omartinez | |
| 143 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 144 | 46050 | omartinez | |
| 145 | 46104 | omartinez | TableReference table = operations.createTableReference( |
| 146 | "dbtest",
|
||
| 147 | sqlbuilder.default_schema(), |
||
| 148 | "test",
|
||
| 149 | null
|
||
| 150 | ); |
||
| 151 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 152 | EditableFeatureType eFeatureType = featureType.getEditable(); |
||
| 153 | eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
||
| 154 | eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
||
| 155 | 46050 | omartinez | |
| 156 | 46104 | omartinez | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 157 | table, |
||
| 158 | null,
|
||
| 159 | null,
|
||
| 160 | null,
|
||
| 161 | eFeatureType, |
||
| 162 | eFeatureType, |
||
| 163 | 0,
|
||
| 164 | 0,
|
||
| 165 | 0
|
||
| 166 | ); |
||
| 167 | String sql = resultSetForSetProvider.getSQL();
|
||
| 168 | 46507 | jjdelcerro | utils().output_results("testComputedAttribute2", sql, expectedSQLs.get("testComputedAttribute2")); |
| 169 | utils().runSQLToCheckSyntax("testComputedAttribute2", sourceStore, table.getTable(), sql);
|
||
| 170 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedAttribute2"), sql); |
||
| 171 | 46104 | omartinez | } catch (Exception ex) { |
| 172 | ex.printStackTrace(); |
||
| 173 | throw ex;
|
||
| 174 | } |
||
| 175 | } |
||
| 176 | 46050 | omartinez | |
| 177 | 46104 | omartinez | public void testComputedExtraColumn() throws Exception { |
| 178 | try {
|
||
| 179 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 180 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 181 | OperationsFactory operations = helper.getOperations(); |
||
| 182 | 46050 | omartinez | |
| 183 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 184 | 46050 | omartinez | |
| 185 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 186 | 46050 | omartinez | |
| 187 | 46104 | omartinez | TableReference table = operations.createTableReference( |
| 188 | "dbtest",
|
||
| 189 | sqlbuilder.default_schema(), |
||
| 190 | "test",
|
||
| 191 | null
|
||
| 192 | ); |
||
| 193 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 194 | EditableFeatureType eFeatureType = featureType.getEditable(); |
||
| 195 | eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
||
| 196 | 46050 | omartinez | |
| 197 | 46104 | omartinez | FeatureQuery query = sourceStore.createFeatureQuery(); |
| 198 | EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
||
| 199 | extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
||
| 200 | 46050 | omartinez | |
| 201 | 46104 | omartinez | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 202 | table, |
||
| 203 | null,
|
||
| 204 | null,
|
||
| 205 | query, |
||
| 206 | eFeatureType, |
||
| 207 | eFeatureType, |
||
| 208 | 0,
|
||
| 209 | 0,
|
||
| 210 | 0
|
||
| 211 | ); |
||
| 212 | String sql = resultSetForSetProvider.getSQL();
|
||
| 213 | 46507 | jjdelcerro | utils().output_results("testComputedExtraColumn", sql, expectedSQLs.get("testComputedExtraColumn")); |
| 214 | utils().runSQLToCheckSyntax("testComputedExtraColumn", sourceStore, table.getTable(), sql);
|
||
| 215 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedExtraColumn"), sql); |
||
| 216 | 46104 | omartinez | } catch (Exception ex) { |
| 217 | ex.printStackTrace(); |
||
| 218 | throw ex;
|
||
| 219 | } |
||
| 220 | } |
||
| 221 | 46050 | omartinez | |
| 222 | 46104 | omartinez | public void testComputedExtraColumn2() throws Exception { |
| 223 | try {
|
||
| 224 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 225 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 226 | OperationsFactory operations = helper.getOperations(); |
||
| 227 | 46050 | omartinez | |
| 228 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 229 | 46050 | omartinez | |
| 230 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 231 | 46050 | omartinez | |
| 232 | 46104 | omartinez | TableReference table = operations.createTableReference( |
| 233 | "dbtest",
|
||
| 234 | sqlbuilder.default_schema(), |
||
| 235 | "test",
|
||
| 236 | null
|
||
| 237 | ); |
||
| 238 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 239 | EditableFeatureType eFeatureType = featureType.getEditable(); |
||
| 240 | 45472 | jjdelcerro | |
| 241 | 46104 | omartinez | FeatureQuery query = sourceStore.createFeatureQuery(); |
| 242 | EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
||
| 243 | extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
||
| 244 | EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
||
| 245 | extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Extra1"))); |
||
| 246 | 45472 | jjdelcerro | |
| 247 | 46104 | omartinez | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 248 | table, |
||
| 249 | null,
|
||
| 250 | null,
|
||
| 251 | query, |
||
| 252 | eFeatureType, |
||
| 253 | eFeatureType, |
||
| 254 | 0,
|
||
| 255 | 0,
|
||
| 256 | 0
|
||
| 257 | ); |
||
| 258 | String sql = resultSetForSetProvider.getSQL();
|
||
| 259 | 46507 | jjdelcerro | utils().output_results("testComputedExtraColumn2", sql, expectedSQLs.get("testComputedExtraColumn2")); |
| 260 | utils().runSQLToCheckSyntax("testComputedExtraColumn2", sourceStore, table.getTable(), sql);
|
||
| 261 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedExtraColumn2"), sql); |
||
| 262 | 46104 | omartinez | } catch (Exception ex) { |
| 263 | ex.printStackTrace(); |
||
| 264 | throw ex;
|
||
| 265 | } |
||
| 266 | } |
||
| 267 | 45472 | jjdelcerro | |
| 268 | 46104 | omartinez | public void testComputedExtraColumnWithWhere() throws Exception { |
| 269 | try {
|
||
| 270 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 271 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 272 | OperationsFactory operations = helper.getOperations(); |
||
| 273 | 46010 | jjdelcerro | |
| 274 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 275 | 46010 | jjdelcerro | |
| 276 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 277 | 46050 | omartinez | |
| 278 | 46104 | omartinez | TableReference table = operations.createTableReference( |
| 279 | "dbtest",
|
||
| 280 | sqlbuilder.default_schema(), |
||
| 281 | "test",
|
||
| 282 | null
|
||
| 283 | ); |
||
| 284 | 46050 | omartinez | |
| 285 | 46104 | omartinez | StringBuilder filter = new StringBuilder(); |
| 286 | filter.append("Extra1 > 10");
|
||
| 287 | 46050 | omartinez | |
| 288 | 46104 | omartinez | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
| 289 | 46050 | omartinez | |
| 290 | 46104 | omartinez | EditableFeatureType eFeatureType = featureType.getEditable(); |
| 291 | eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
||
| 292 | 45472 | jjdelcerro | |
| 293 | 46104 | omartinez | FeatureQuery query = sourceStore.createFeatureQuery(); |
| 294 | EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
||
| 295 | extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
||
| 296 | query.addFilter(filter.toString()); |
||
| 297 | query.getOrder().add("Extra1");
|
||
| 298 | 45472 | jjdelcerro | |
| 299 | 46104 | omartinez | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 300 | table, |
||
| 301 | null,
|
||
| 302 | null,
|
||
| 303 | query, |
||
| 304 | eFeatureType, |
||
| 305 | eFeatureType, |
||
| 306 | 0,
|
||
| 307 | 0,
|
||
| 308 | 0
|
||
| 309 | ); |
||
| 310 | String sql = resultSetForSetProvider.getSQL();
|
||
| 311 | 46507 | jjdelcerro | utils().output_results("testComputedExtraColumnWithWhere", sql, expectedSQLs.get("testComputedExtraColumnWithWhere")); |
| 312 | utils().runSQLToCheckSyntax("testComputedExtraColumnWithWhere", sourceStore, table.getTable(), sql);
|
||
| 313 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedExtraColumnWithWhere"), sql); |
||
| 314 | 46104 | omartinez | } catch (Exception ex) { |
| 315 | ex.printStackTrace(); |
||
| 316 | throw ex;
|
||
| 317 | } |
||
| 318 | } |
||
| 319 | 45472 | jjdelcerro | |
| 320 | 46104 | omartinez | public void testSimpleGroup() throws Exception { |
| 321 | try {
|
||
| 322 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 323 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 324 | OperationsFactory operations = helper.getOperations(); |
||
| 325 | 45472 | jjdelcerro | |
| 326 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 327 | 45472 | jjdelcerro | |
| 328 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 329 | 45472 | jjdelcerro | |
| 330 | 46104 | omartinez | TableReference table = operations.createTableReference( |
| 331 | "dbtest",
|
||
| 332 | sqlbuilder.default_schema(), |
||
| 333 | "test",
|
||
| 334 | null
|
||
| 335 | ); |
||
| 336 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 337 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 338 | query.getGroupByColumns().add("Long");
|
||
| 339 | query.getAggregateFunctions().put("ID", "MIN"); |
||
| 340 | query.getAggregateFunctions().put("Byte", "MAX"); |
||
| 341 | query.getAggregateFunctions().put("Double", "SUM"); |
||
| 342 | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
||
| 343 | table, |
||
| 344 | null,
|
||
| 345 | null,
|
||
| 346 | query, |
||
| 347 | featureType, |
||
| 348 | featureType, |
||
| 349 | 0,
|
||
| 350 | 0,
|
||
| 351 | 0
|
||
| 352 | ); |
||
| 353 | String sql = resultSetForSetProvider.getSQL();
|
||
| 354 | 46507 | jjdelcerro | utils().output_results("testSimpleGroup", sql, expectedSQLs.get("testSimpleGroup")); |
| 355 | utils().runSQLToCheckSyntax("testSimpleGroup", sourceStore, table.getTable(), sql);
|
||
| 356 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSimpleGroup"), sql); |
||
| 357 | 46104 | omartinez | } catch (Throwable th) { |
| 358 | 46105 | omartinez | LOGGER.warn("",th);
|
| 359 | 46104 | omartinez | throw th;
|
| 360 | } |
||
| 361 | } |
||
| 362 | 45472 | jjdelcerro | |
| 363 | 46505 | fdiaz | public void testSimpleAggregateAndOrder() throws Exception { |
| 364 | try {
|
||
| 365 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 366 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 367 | OperationsFactory operations = helper.getOperations(); |
||
| 368 | |||
| 369 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 370 | 46505 | fdiaz | |
| 371 | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
||
| 372 | |||
| 373 | TableReference table = operations.createTableReference( |
||
| 374 | "dbtest",
|
||
| 375 | sqlbuilder.default_schema(), |
||
| 376 | "test",
|
||
| 377 | null
|
||
| 378 | ); |
||
| 379 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 380 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 381 | |||
| 382 | EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
||
| 383 | extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(featureType, ExpressionUtils.createExpression("Long+10"))); |
||
| 384 | EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
||
| 385 | extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(featureType, ExpressionUtils.createExpression("20+Byte"))); |
||
| 386 | |||
| 387 | query.getAggregateFunctions().put("ID", "MIN"); |
||
| 388 | query.getAggregateFunctions().put("Byte", "MAX"); |
||
| 389 | query.getAggregateFunctions().put("Double", "SUM"); |
||
| 390 | query.getAggregateFunctions().put("Extra1", "SUM"); |
||
| 391 | query.getOrder().add("ID");
|
||
| 392 | 46507 | jjdelcerro | // query.getOrder().add("Long");
|
| 393 | 46505 | fdiaz | query.getOrder().add("Extra1");
|
| 394 | 46507 | jjdelcerro | |
| 395 | // !OJO! No podemos ordenar por Extra2, da el error:
|
||
| 396 | // Column """Byte""" must be in the GROUP BY list
|
||
| 397 | // query.getOrder().add("Extra2");
|
||
| 398 | 46505 | fdiaz | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 399 | table, |
||
| 400 | null,
|
||
| 401 | null,
|
||
| 402 | query, |
||
| 403 | featureType, |
||
| 404 | featureType, |
||
| 405 | 0,
|
||
| 406 | 0,
|
||
| 407 | 0
|
||
| 408 | ); |
||
| 409 | String sql = resultSetForSetProvider.getSQL();
|
||
| 410 | 46507 | jjdelcerro | utils().output_results("testSimpleAggregateAndOrder", sql, expectedSQLs.get("testSimpleAggregateAndOrder")); |
| 411 | utils().runSQLToCheckSyntax("testSimpleAggregateAndOrder", sourceStore, table.getTable(), sql);
|
||
| 412 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSimpleAggregateAndOrder"), sql); |
||
| 413 | 46505 | fdiaz | } catch (Throwable th) { |
| 414 | LOGGER.warn("",th);
|
||
| 415 | throw th;
|
||
| 416 | } |
||
| 417 | } |
||
| 418 | |||
| 419 | 46104 | omartinez | public void testGroupByComputed() throws Exception { |
| 420 | try {
|
||
| 421 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 422 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 423 | OperationsFactory operations = helper.getOperations(); |
||
| 424 | 45472 | jjdelcerro | |
| 425 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 426 | 45472 | jjdelcerro | |
| 427 | 46104 | omartinez | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
| 428 | |||
| 429 | TableReference table = operations.createTableReference( |
||
| 430 | "dbtest",
|
||
| 431 | sqlbuilder.default_schema(), |
||
| 432 | "test",
|
||
| 433 | null
|
||
| 434 | ); |
||
| 435 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 436 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 437 | EditableFeatureType eFeatureType = featureType.getEditable(); |
||
| 438 | eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
||
| 439 | eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+300"))); |
||
| 440 | 46505 | fdiaz | eFeatureType.add("Compu3", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("1"))); |
| 441 | 46104 | omartinez | EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
| 442 | EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
||
| 443 | extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
||
| 444 | extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("20+Byte+Compu1"))); |
||
| 445 | query.getGroupByColumns().add("Long");
|
||
| 446 | query.getGroupByColumns().add("Extra1");
|
||
| 447 | query.getGroupByColumns().add("Compu1");
|
||
| 448 | query.getAggregateFunctions().put("ID", "MIN"); |
||
| 449 | query.getAggregateFunctions().put("Byte", "MAX"); |
||
| 450 | query.getAggregateFunctions().put("Double", "SUM"); |
||
| 451 | query.getAggregateFunctions().put("Extra2", "SUM"); |
||
| 452 | query.getAggregateFunctions().put("Compu2", "SUM"); |
||
| 453 | 46505 | fdiaz | query.getAggregateFunctions().put("Compu3", "SUM"); |
| 454 | 46104 | omartinez | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 455 | table, |
||
| 456 | null,
|
||
| 457 | null,
|
||
| 458 | query, |
||
| 459 | eFeatureType, |
||
| 460 | eFeatureType, |
||
| 461 | 0,
|
||
| 462 | 0,
|
||
| 463 | 0
|
||
| 464 | ); |
||
| 465 | String sql = resultSetForSetProvider.getSQL();
|
||
| 466 | 46507 | jjdelcerro | utils().output_results("testGroupByComputed", sql, expectedSQLs.get("testGroupByComputed")); |
| 467 | utils().runSQLToCheckSyntax("testGroupByComputed", sourceStore, table.getTable(), sql);
|
||
| 468 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testGroupByComputed"), sql); |
||
| 469 | 46104 | omartinez | } catch (Throwable th) { |
| 470 | 46105 | omartinez | LOGGER.warn("", th);
|
| 471 | 46104 | omartinez | throw th;
|
| 472 | } |
||
| 473 | } |
||
| 474 | |||
| 475 | 46505 | fdiaz | public void testGroupByAndOrderByComputed() throws Exception { |
| 476 | try {
|
||
| 477 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 478 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 479 | OperationsFactory operations = helper.getOperations(); |
||
| 480 | |||
| 481 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 482 | 46505 | fdiaz | |
| 483 | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
||
| 484 | |||
| 485 | TableReference table = operations.createTableReference( |
||
| 486 | "dbtest",
|
||
| 487 | sqlbuilder.default_schema(), |
||
| 488 | "test",
|
||
| 489 | null
|
||
| 490 | ); |
||
| 491 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 492 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 493 | EditableFeatureType eFeatureType = featureType.getEditable(); |
||
| 494 | eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
||
| 495 | eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+300"))); |
||
| 496 | eFeatureType.add("Compu3", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("1"))); |
||
| 497 | |||
| 498 | EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
||
| 499 | extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
||
| 500 | |||
| 501 | EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
||
| 502 | extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("20+Byte+Compu1"))); |
||
| 503 | |||
| 504 | EditableFeatureAttributeDescriptor extraColumn3 = query.getExtraColumn().add("Extra3", DataTypes.INTEGER);
|
||
| 505 | extraColumn3.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+33"))); |
||
| 506 | |||
| 507 | query.getGroupByColumns().add("Long");
|
||
| 508 | query.getGroupByColumns().add("Extra1");
|
||
| 509 | query.getGroupByColumns().add("Compu1");
|
||
| 510 | query.getAggregateFunctions().put("ID", "MIN"); |
||
| 511 | query.getAggregateFunctions().put("Byte", "MAX"); |
||
| 512 | query.getAggregateFunctions().put("Double", "SUM"); |
||
| 513 | query.getAggregateFunctions().put("Extra2", "SUM"); |
||
| 514 | query.getAggregateFunctions().put("Compu2", "SUM"); |
||
| 515 | query.getAggregateFunctions().put("Compu3", "SUM"); |
||
| 516 | query.getOrder().add("Extra1");
|
||
| 517 | query.getOrder().add("Extra2");
|
||
| 518 | 46507 | jjdelcerro | // query.getOrder().add("Extra3");
|
| 519 | 46505 | fdiaz | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 520 | table, |
||
| 521 | null,
|
||
| 522 | null,
|
||
| 523 | query, |
||
| 524 | eFeatureType, |
||
| 525 | eFeatureType, |
||
| 526 | 0,
|
||
| 527 | 0,
|
||
| 528 | 0
|
||
| 529 | ); |
||
| 530 | String sql = resultSetForSetProvider.getSQL();
|
||
| 531 | 46507 | jjdelcerro | utils().output_results("testGroupByAndOrderByComputed", sql, expectedSQLs.get("testGroupByAndOrderByComputed")); |
| 532 | utils().runSQLToCheckSyntax("testGroupByAndOrderByComputed", sourceStore, table.getTable(), sql);
|
||
| 533 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testGroupByAndOrderByComputed"), sql); |
||
| 534 | 46505 | fdiaz | } catch (Throwable th) { |
| 535 | LOGGER.warn("", th);
|
||
| 536 | throw th;
|
||
| 537 | } |
||
| 538 | } |
||
| 539 | |||
| 540 | 46104 | omartinez | public void testSubselect() throws Exception { |
| 541 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 542 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 543 | OperationsFactory operations = helper.getOperations(); |
||
| 544 | 46518 | fdiaz | DataManager dataManager = DALLocator.getDataManager(); |
| 545 | 46104 | omartinez | |
| 546 | 46518 | fdiaz | dataManager.getStoresRepository().remove("test");
|
| 547 | dataManager.getStoresRepository().remove("countries");
|
||
| 548 | |||
| 549 | |||
| 550 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 551 | 46104 | omartinez | |
| 552 | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
||
| 553 | |||
| 554 | TableReference table = operations.createTableReference( |
||
| 555 | "dbtest",
|
||
| 556 | sqlbuilder.default_schema(), |
||
| 557 | "test",
|
||
| 558 | null
|
||
| 559 | ); |
||
| 560 | StringBuilder filter = new StringBuilder(); |
||
| 561 | filter.append("EXISTS(");
|
||
| 562 | filter.append(" SELECT \"ISO_A2\" FROM countries");
|
||
| 563 | filter.append(" WHERE ");
|
||
| 564 | 46543 | fdiaz | filter.append(" test.STRING = countries.CONTINENT AND ");
|
| 565 | 46104 | omartinez | filter.append(" countries.LASTCENSUS < 0 ");
|
| 566 | filter.append(" LIMIT 1;, ");
|
||
| 567 | filter.append(" 'EXISTS62a964cd7bc24f409b97c03b9170408d' ");
|
||
| 568 | filter.append(")");
|
||
| 569 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 570 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 571 | query.addFilter(filter.toString()); |
||
| 572 | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
||
| 573 | table, |
||
| 574 | null,
|
||
| 575 | null,
|
||
| 576 | query, |
||
| 577 | featureType, |
||
| 578 | featureType, |
||
| 579 | 0,
|
||
| 580 | 0,
|
||
| 581 | 0
|
||
| 582 | ); |
||
| 583 | String sql = resultSetForSetProvider.getSQL();
|
||
| 584 | 46507 | jjdelcerro | utils().output_results("testSubselect", sql, expectedSQLs.get("testSubselect")); |
| 585 | 46104 | omartinez | |
| 586 | 46507 | jjdelcerro | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSubselect"), sql); |
| 587 | 46104 | omartinez | } |
| 588 | |||
| 589 | 46517 | fdiaz | public void testSubselect2() throws Exception { |
| 590 | 46518 | fdiaz | try {
|
| 591 | 46517 | fdiaz | JDBCHelper helper = TestUtils.createJDBCHelper(); |
| 592 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 593 | OperationsFactory operations = helper.getOperations(); |
||
| 594 | DataManager dataManager = DALLocator.getDataManager(); |
||
| 595 | |||
| 596 | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
||
| 597 | |||
| 598 | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
||
| 599 | dataManager.getStoresRepository().add(sourceStore.getName(), sourceStore); |
||
| 600 | |||
| 601 | FeatureStore countriesStore = TestUtils.openCountriesStore(); |
||
| 602 | dataManager.getStoresRepository().add(countriesStore.getName(), countriesStore); |
||
| 603 | |||
| 604 | TableReference table = operations.createTableReference( |
||
| 605 | "dbtest",
|
||
| 606 | sqlbuilder.default_schema(), |
||
| 607 | "test",
|
||
| 608 | null
|
||
| 609 | ); |
||
| 610 | StringBuilder filter = new StringBuilder(); |
||
| 611 | filter.append("EXISTS(");
|
||
| 612 | filter.append(" SELECT \"Long\" FROM countries");
|
||
| 613 | filter.append(" WHERE ");
|
||
| 614 | 46543 | fdiaz | filter.append(" test.STRING = countries.CONTINENT AND ");
|
| 615 | 46517 | fdiaz | filter.append(" countries.LASTCENSUS < 0 ");
|
| 616 | filter.append(" LIMIT 1;, ");
|
||
| 617 | filter.append(" 'EXISTS62a964cd7bc24f409b97c03b9170408d' ");
|
||
| 618 | filter.append(")");
|
||
| 619 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 620 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 621 | query.addFilter(filter.toString()); |
||
| 622 | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
||
| 623 | table, |
||
| 624 | null,
|
||
| 625 | null,
|
||
| 626 | query, |
||
| 627 | featureType, |
||
| 628 | featureType, |
||
| 629 | 0,
|
||
| 630 | 0,
|
||
| 631 | 0
|
||
| 632 | ); |
||
| 633 | String sql = resultSetForSetProvider.getSQL();
|
||
| 634 | utils().output_results("testSubselect2", sql, expectedSQLs.get("testSubselect2")); |
||
| 635 | |||
| 636 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSubselect2"), sql); |
||
| 637 | 46518 | fdiaz | } catch (Exception ex) { |
| 638 | LOGGER.warn("", ex);
|
||
| 639 | throw ex;
|
||
| 640 | } |
||
| 641 | 46517 | fdiaz | } |
| 642 | |||
| 643 | 46104 | omartinez | public void testGroupAndSubselect() throws Exception { |
| 644 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 645 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 646 | OperationsFactory operations = helper.getOperations(); |
||
| 647 | |||
| 648 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 649 | 46104 | omartinez | |
| 650 | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
||
| 651 | |||
| 652 | TableReference table = operations.createTableReference( |
||
| 653 | "dbtest",
|
||
| 654 | sqlbuilder.default_schema(), |
||
| 655 | "test",
|
||
| 656 | null
|
||
| 657 | ); |
||
| 658 | StringBuilder filter = new StringBuilder(); |
||
| 659 | filter.append("EXISTS(");
|
||
| 660 | filter.append(" SELECT \"ISO_A2\" FROM countries");
|
||
| 661 | filter.append(" WHERE ");
|
||
| 662 | 46543 | fdiaz | filter.append(" test.STRING = countries.CONTINENT AND ");
|
| 663 | 46104 | omartinez | filter.append(" countries.LASTCENSUS < 0 ");
|
| 664 | filter.append(" LIMIT 1;, ");
|
||
| 665 | filter.append(" 'EXISTS62a964cd7bc24f409b97c03b9170408d' ");
|
||
| 666 | filter.append(")");
|
||
| 667 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
| 668 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 669 | query.getGroupByColumns().add("Long");
|
||
| 670 | query.getAggregateFunctions().put("ID", "MIN"); |
||
| 671 | query.getAggregateFunctions().put("Byte", "MAX"); |
||
| 672 | query.getAggregateFunctions().put("Double", "SUM"); |
||
| 673 | query.addFilter(filter.toString()); |
||
| 674 | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
||
| 675 | table, |
||
| 676 | null,
|
||
| 677 | null,
|
||
| 678 | query, |
||
| 679 | featureType, |
||
| 680 | featureType, |
||
| 681 | 0,
|
||
| 682 | 0,
|
||
| 683 | 0
|
||
| 684 | ); |
||
| 685 | String sql = resultSetForSetProvider.getSQL();
|
||
| 686 | 46507 | jjdelcerro | utils().output_results("testGroupAndSubselect", sql, expectedSQLs.get("testGroupAndSubselect")); |
| 687 | 46104 | omartinez | |
| 688 | 46507 | jjdelcerro | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testGroupAndSubselect"), sql); |
| 689 | 46104 | omartinez | } |
| 690 | |||
| 691 | public void testConstantColumnPrimaryKey() throws Exception { |
||
| 692 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
| 693 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
| 694 | OperationsFactory operations = helper.getOperations(); |
||
| 695 | |||
| 696 | 46507 | jjdelcerro | Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
| 697 | 46104 | omartinez | |
| 698 | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
||
| 699 | |||
| 700 | TableReference table = operations.createTableReference( |
||
| 701 | "dbtest",
|
||
| 702 | sqlbuilder.default_schema(), |
||
| 703 | "test",
|
||
| 704 | null
|
||
| 705 | ); |
||
| 706 | 45472 | jjdelcerro | // meterle como constantCOlumn ID
|
| 707 | |||
| 708 | 46104 | omartinez | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
| 709 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
| 710 | 45472 | jjdelcerro | |
| 711 | 46104 | omartinez | query.setConstantsAttributeNames(new String[]{"ID"}); |
| 712 | 45472 | jjdelcerro | |
| 713 | 46104 | omartinez | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 714 | table, |
||
| 715 | null,
|
||
| 716 | null,
|
||
| 717 | query, |
||
| 718 | featureType, |
||
| 719 | featureType, |
||
| 720 | 0,
|
||
| 721 | 0,
|
||
| 722 | 0
|
||
| 723 | ); |
||
| 724 | String sql = resultSetForSetProvider.getSQL();
|
||
| 725 | 46507 | jjdelcerro | utils().output_results("testConstantColumnPrimaryKey", sql, expectedSQLs.get("testConstantColumnPrimaryKey")); |
| 726 | utils().runSQLToCheckSyntax("testConstantColumnPrimaryKey", sourceStore, table.getTable(), sql);
|
||
| 727 | assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testConstantColumnPrimaryKey"), sql); |
||
| 728 | 46104 | omartinez | } |
| 729 | |||
| 730 | // TODO: a?adir un test con where, group y order.
|
||
| 731 | 45472 | jjdelcerro | } |