Revision 937

View differences:

org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/test/java/org/gvsig/oracle/dal/operations/sql/TestNotAndOr.java
1 
package org.gvsig.oracle.dal.operations.sql;
2 

  		




  
  3
  
    
import java.util.List;


  		




  
  4
  
    
import junit.framework.TestCase;


  		




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


  		




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


  		




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


  		




  
  8
  
    
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper;


  		




  
  9
  
    
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory;


  		




  
  10
  
    
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference;


  		




  
  11
  
    
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;


  		




  
  12
  
    
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation;


  		




  
  13
  
    
import org.gvsig.oracle.dal.TestUtils;


  		




  
  14
  
    
import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;


  		




  
  15
  
    
import org.gvsig.tools.util.ListBuilder;


  		




  
  16
  
    
import org.slf4j.Logger;


  		




  
  17
  
    
import org.slf4j.LoggerFactory;


  		




  
  18
  
    

  		




  
  19
  
    
public class TestNotAndOr extends TestCase {


  		




  
  20
  
    

  		




  
  21
  
    
    private static final Logger LOGGER = LoggerFactory.getLogger(TestNotAndOr.class);


  		




  
  22
  
    

  		




  
  23
  
    
    public TestNotAndOr(String testName) {


  		




  
  24
  
    
        super(testName);


  		




  
  25
  
    
    }


  		




  
  26
  
    

  		




  
  27
  
    
    @Override


  		




  
  28
  
    
    protected void setUp() throws Exception {


  		




  
  29
  
    
        super.setUp();


  		




  
  30
  
    
        new DefaultLibrariesInitializer().fullInitialize();


  		




  
  31
  
    
    }


  		




  
  32
  
    

  		




  
  33
  
    
    @Override


  		




  
  34
  
    
    protected void tearDown() throws Exception {


  		




  
  35
  
    
        super.tearDown();


  		




  
  36
  
    
    }


  		




  
  37
  
    

  		




  
  38
  
    
    public void testNot1() throws Exception {


  		




  
  39
  
    
        try {


  		




  
  40
  
    
            JDBCHelper helper = TestUtils.getJDBCHelper();


  		




  
  41
  
    
            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();


  		




  
  42
  
    
            OperationsFactory operations = helper.getOperations();


  		




  
  43
  
    

  		




  
  44
  
    
            List<String> expectedSQLs = ListBuilder.create(


  		




  
  45
  
    
                    "SELECT \"P1\".\"TEST\".\"ID\", \"P1\".\"TEST\".\"Byte\", \"P1\".\"TEST\".\"Bool1\", \"P1\".\"TEST\".\"Long\", \"P1\".\"TEST\".\"Timestamp\", \"P1\".\"TEST\".\"Date\", \"P1\".\"TEST\".\"Time\", \"P1\".\"TEST\".\"Bool2\", \"P1\".\"TEST\".\"String\", \"P1\".\"TEST\".\"Bool3\", \"P1\".\"TEST\".\"Double\", \"P1\".\"TEST\".\"Bool4\", \"P1\".\"TEST\".\"Float\", \"P1\".\"TEST\".\"Bool5\", \"P1\".\"TEST\".\"Decimal\", NVL2((\"P1\".\"TEST\".\"Geometry\"),(\"P1\".\"TEST\".\"Geometry\").Get_WKB(),NULL) "


  		




  
  46
  
    
                            + "FROM \"P1\".\"TEST\" "


  		




  
  47
  
    
                            + "WHERE (NOT (\"P1\".\"TEST\".\"String\" = 'T')) "


  		




  
  48
  
    
                            + "ORDER BY \"P1\".\"TEST\".\"ID\" ASC"


  		




  
  49
  
    
            );


  		




  
  50
  
    

  		




  
  51
  
    
            FeatureStore sourceStore = TestUtils.openSourceStore1();


  		




  
  52
  
    

  		




  
  53
  
    
            TableReference table = operations.createTableReference(


  		




  
  54
  
    
                    "dbtest",


  		




  
  55
  
    
                    sqlbuilder.default_schema(),


  		




  
  56
  
    
                    "test",


  		




  
  57
  
    
                    null


  		




  
  58
  
    
            );


  		




  
  59
  
    
            String filter = "NOT \"String\"";


  		




  
  60
  
    
            FeatureType featureType = sourceStore.getDefaultFeatureType();


  		




  
  61
  
    
            FeatureQuery query = sourceStore.createFeatureQuery();


  		




  
  62
  
    
            query.addFilter(filter);


  		




  
  63
  
    
            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(


  		




  
  64
  
    
                    table,


  		




  
  65
  
    
                    null,


  		




  
  66
  
    
                    null,


  		




  
  67
  
    
                    query,


  		




  
  68
  
    
                    featureType,


  		




  
  69
  
    
                    featureType,


  		




  
  70
  
    
                    0,


  		




  
  71
  
    
                    0,


  		




  
  72
  
    
                    0


  		




  
  73
  
    
            );


  		




  
  74
  
    
            String sql = resultSetForSetProvider.getSQL();


  		




  
  75
  
    
            System.out.println("###### SQL:" + sql);


  		




  
  76
  
    
            System.out.println("###### EXP:" + expectedSQLs.get(0));


  		




  
  77
  
    

  		




  
  78
  
    
            assertEquals("NOT SQL", expectedSQLs.get(0), sql);


  		




  
  79
  
    
        } catch (Throwable th) {


  		




  
  80
  
    
            LOGGER.warn("",th);


  		




  
  81
  
    
            throw th;


  		




  
  82
  
    
        }


  		




  
  83
  
    
    }


  		




  
  84
  
    
    


  		




  
  85
  
    
    public void testAnd1() throws Exception {


  		




  
  86
  
    
        try {


  		




  
  87
  
    
            JDBCHelper helper = TestUtils.getJDBCHelper();


  		




  
  88
  
    
            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();


  		




  
  89
  
    
            OperationsFactory operations = helper.getOperations();


  		




  
  90
  
    

  		




  
  91
  
    
            List<String> expectedSQLs = ListBuilder.create(


  		




  
  92
  
    
                    "SELECT \"P1\".\"TEST\".\"ID\", \"P1\".\"TEST\".\"Byte\", \"P1\".\"TEST\".\"Bool1\", \"P1\".\"TEST\".\"Long\", \"P1\".\"TEST\".\"Timestamp\", \"P1\".\"TEST\".\"Date\", \"P1\".\"TEST\".\"Time\", \"P1\".\"TEST\".\"Bool2\", \"P1\".\"TEST\".\"String\", \"P1\".\"TEST\".\"Bool3\", \"P1\".\"TEST\".\"Double\", \"P1\".\"TEST\".\"Bool4\", \"P1\".\"TEST\".\"Float\", \"P1\".\"TEST\".\"Bool5\", \"P1\".\"TEST\".\"Decimal\", NVL2((\"P1\".\"TEST\".\"Geometry\"),(\"P1\".\"TEST\".\"Geometry\").Get_WKB(),NULL) "


  		




  
  93
  
    
                            + "FROM \"P1\".\"TEST\" "


  		




  
  94
  
    
                            + "WHERE ( ((\"P1\".\"TEST\".\"String\" = 'T')) AND (( (\"P1\".\"TEST\".\"ID\") = (2) )) ) "


  		




  
  95
  
    
                            + "ORDER BY \"P1\".\"TEST\".\"ID\" ASC"


  		




  
  96
  
    
            );


  		




  
  97
  
    

  		




  
  98
  
    
            FeatureStore sourceStore = TestUtils.openSourceStore1();


  		




  
  99
  
    

  		




  
  100
  
    
            TableReference table = operations.createTableReference(


  		




  
  101
  
    
                    "dbtest",


  		




  
  102
  
    
                    sqlbuilder.default_schema(),


  		




  
  103
  
    
                    "test",


  		




  
  104
  
    
                    null


  		




  
  105
  
    
            );


  		




  
  106
  
    
            String filter = "\"String\" AND \"ID\" = 2";


  		




  
  107
  
    
            FeatureType featureType = sourceStore.getDefaultFeatureType();


  		




  
  108
  
    
            FeatureQuery query = sourceStore.createFeatureQuery();


  		




  
  109
  
    
            query.addFilter(filter);


  		




  
  110
  
    
            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(


  		




  
  111
  
    
                    table,


  		




  
  112
  
    
                    null,


  		




  
  113
  
    
                    null,


  		




  
  114
  
    
                    query,


  		




  
  115
  
    
                    featureType,


  		




  
  116
  
    
                    featureType,


  		




  
  117
  
    
                    0,


  		




  
  118
  
    
                    0,


  		




  
  119
  
    
                    0


  		




  
  120
  
    
            );


  		




  
  121
  
    
            String sql = resultSetForSetProvider.getSQL();


  		




  
  122
  
    
            System.out.println("###### SQL:" + sql);


  		




  
  123
  
    
            System.out.println("###### EXP:" + expectedSQLs.get(0));


  		




  
  124
  
    

  		




  
  125
  
    
            assertEquals("AND1 SQL", expectedSQLs.get(0), sql);


  		




  
  126
  
    
        } catch (Throwable th) {


  		




  
  127
  
    
            LOGGER.warn("",th);


  		




  
  128
  
    
            throw th;


  		




  
  129
  
    
        }


  		




  
  130
  
    
    }


  		




  
  131
  
    

  		




  
  132
  
    
    public void testAnd2() throws Exception {


  		




  
  133
  
    
        try {


  		




  
  134
  
    
            JDBCHelper helper = TestUtils.getJDBCHelper();


  		




  
  135
  
    
            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();


  		




  
  136
  
    
            OperationsFactory operations = helper.getOperations();


  		




  
  137
  
    

  		




  
  138
  
    
            List<String> expectedSQLs = ListBuilder.create(


  		




  
  139
  
    
                    "SELECT \"P1\".\"TEST\".\"ID\", \"P1\".\"TEST\".\"Byte\", \"P1\".\"TEST\".\"Bool1\", \"P1\".\"TEST\".\"Long\", \"P1\".\"TEST\".\"Timestamp\", \"P1\".\"TEST\".\"Date\", \"P1\".\"TEST\".\"Time\", \"P1\".\"TEST\".\"Bool2\", \"P1\".\"TEST\".\"String\", \"P1\".\"TEST\".\"Bool3\", \"P1\".\"TEST\".\"Double\", \"P1\".\"TEST\".\"Bool4\", \"P1\".\"TEST\".\"Float\", \"P1\".\"TEST\".\"Bool5\", \"P1\".\"TEST\".\"Decimal\", NVL2((\"P1\".\"TEST\".\"Geometry\"),(\"P1\".\"TEST\".\"Geometry\").Get_WKB(),NULL) "


  		




  
  140
  
    
                            + "FROM \"P1\".\"TEST\" "


  		




  
  141
  
    
                            + "WHERE ( (( (\"P1\".\"TEST\".\"ID\") = (2) )) AND ((\"P1\".\"TEST\".\"String\" = 'T')) ) "


  		




  
  142
  
    
                            + "ORDER BY \"P1\".\"TEST\".\"ID\" ASC"


  		




  
  143
  
    
            );


  		




  
  144
  
    

  		




  
  145
  
    
            FeatureStore sourceStore = TestUtils.openSourceStore1();


  		




  
  146
  
    

  		




  
  147
  
    
            TableReference table = operations.createTableReference(


  		




  
  148
  
    
                    "dbtest",


  		




  
  149
  
    
                    sqlbuilder.default_schema(),


  		




  
  150
  
    
                    "test",


  		




  
  151
  
    
                    null


  		




  
  152
  
    
            );


  		




  
  153
  
    
            String filter = "\"ID\" = 2 AND \"String\"";


  		




  
  154
  
    
            FeatureType featureType = sourceStore.getDefaultFeatureType();


  		




  
  155
  
    
            FeatureQuery query = sourceStore.createFeatureQuery();


  		




  
  156
  
    
            query.addFilter(filter);


  		




  
  157
  
    
            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(


  		




  
  158
  
    
                    table,


  		




  
  159
  
    
                    null,


  		




  
  160
  
    
                    null,


  		




  
  161
  
    
                    query,


  		




  
  162
  
    
                    featureType,


  		




  
  163
  
    
                    featureType,


  		




  
  164
  
    
                    0,


  		




  
  165
  
    
                    0,


  		




  
  166
  
    
                    0


  		




  
  167
  
    
            );


  		




  
  168
  
    
            String sql = resultSetForSetProvider.getSQL();


  		




  
  169
  
    
            System.out.println("###### SQL:" + sql);


  		




  
  170
  
    
            System.out.println("###### EXP:" + expectedSQLs.get(0));


  		




  
  171
  
    

  		




  
  172
  
    
            assertEquals("AND2 SQL", expectedSQLs.get(0), sql);


  		




  
  173
  
    
        } catch (Throwable th) {


  		




  
  174
  
    
            LOGGER.warn("",th);


  		




  
  175
  
    
            throw th;


  		




  
  176
  
    
        }


  		




  
  177
  
    
    }


  		




  
  178
  
    

  		




  
  179
  
    
    public void testAnd3() throws Exception {


  		




  
  180
  
    
        try {


  		




  
  181
  
    
            JDBCHelper helper = TestUtils.getJDBCHelper();


  		




  
  182
  
    
            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();


  		




  
  183
  
    
            OperationsFactory operations = helper.getOperations();


  		




  
  184
  
    

  		




  
  185
  
    
            List<String> expectedSQLs = ListBuilder.create(


  		




  
  186
  
    
                    "SELECT \"P1\".\"TEST\".\"ID\", \"P1\".\"TEST\".\"Byte\", \"P1\".\"TEST\".\"Bool1\", \"P1\".\"TEST\".\"Long\", \"P1\".\"TEST\".\"Timestamp\", \"P1\".\"TEST\".\"Date\", \"P1\".\"TEST\".\"Time\", \"P1\".\"TEST\".\"Bool2\", \"P1\".\"TEST\".\"String\", \"P1\".\"TEST\".\"Bool3\", \"P1\".\"TEST\".\"Double\", \"P1\".\"TEST\".\"Bool4\", \"P1\".\"TEST\".\"Float\", \"P1\".\"TEST\".\"Bool5\", \"P1\".\"TEST\".\"Decimal\", NVL2((\"P1\".\"TEST\".\"Geometry\"),(\"P1\".\"TEST\".\"Geometry\").Get_WKB(),NULL) "


  		




  
  187
  
    
                            + "FROM \"P1\".\"TEST\" "


  		




  
  188
  
    
                            + "WHERE ( ((\"P1\".\"TEST\".\"String\" = 'T')) AND ((\"P1\".\"TEST\".\"String\" = 'T')) ) "


  		




  
  189
  
    
                            + "ORDER BY \"P1\".\"TEST\".\"ID\" ASC"


  		




  
  190
  
    
            );


  		




  
  191
  
    

  		




  
  192
  
    
            FeatureStore sourceStore = TestUtils.openSourceStore1();


  		




  
  193
  
    

  		




  
  194
  
    
            TableReference table = operations.createTableReference(


  		




  
  195
  
    
                    "dbtest",


  		




  
  196
  
    
                    sqlbuilder.default_schema(),


  		




  
  197
  
    
                    "test",


  		




  
  198
  
    
                    null


  		




  
  199
  
    
            );


  		




  
  200
  
    
            String filter = "\"String\" AND \"String\"";


  		




  
  201
  
    
            FeatureType featureType = sourceStore.getDefaultFeatureType();


  		




  
  202
  
    
            FeatureQuery query = sourceStore.createFeatureQuery();


  		




  
  203
  
    
            query.addFilter(filter);


  		




  
  204
  
    
            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(


  		




  
  205
  
    
                    table,


  		




  
  206
  
    
                    null,


  		




  
  207
  
    
                    null,


  		




  
  208
  
    
                    query,


  		




  
  209
  
    
                    featureType,


  		




  
  210
  
    
                    featureType,


  		




  
  211
  
    
                    0,


  		




  
  212
  
    
                    0,


  		




  
  213
  
    
                    0


  		




  
  214
  
    
            );


  		




  
  215
  
    
            String sql = resultSetForSetProvider.getSQL();


  		




  
  216
  
    
            System.out.println("###### SQL:" + sql);


  		




  
  217
  
    
            System.out.println("###### EXP:" + expectedSQLs.get(0));


  		




  
  218
  
    

  		




  
  219
  
    
            assertEquals("AND3 SQL", expectedSQLs.get(0), sql);


  		




  
  220
  
    
        } catch (Throwable th) {


  		




  
  221
  
    
            LOGGER.warn("",th);


  		




  
  222
  
    
            throw th;


  		




  
  223
  
    
        }


  		




  
  224
  
    
    }


  		




  
  225
  
    
    


  		




  
  226
  
    
    public void testOr1() throws Exception {


  		




  
  227
  
    
        try {


  		




  
  228
  
    
            JDBCHelper helper = TestUtils.getJDBCHelper();


  		




  
  229
  
    
            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();


  		




  
  230
  
    
            OperationsFactory operations = helper.getOperations();


  		




  
  231
  
    

  		




  
  232
  
    
            List<String> expectedSQLs = ListBuilder.create(


  		




  
  233
  
    
                    "SELECT \"P1\".\"TEST\".\"ID\", \"P1\".\"TEST\".\"Byte\", \"P1\".\"TEST\".\"Bool1\", \"P1\".\"TEST\".\"Long\", \"P1\".\"TEST\".\"Timestamp\", \"P1\".\"TEST\".\"Date\", \"P1\".\"TEST\".\"Time\", \"P1\".\"TEST\".\"Bool2\", \"P1\".\"TEST\".\"String\", \"P1\".\"TEST\".\"Bool3\", \"P1\".\"TEST\".\"Double\", \"P1\".\"TEST\".\"Bool4\", \"P1\".\"TEST\".\"Float\", \"P1\".\"TEST\".\"Bool5\", \"P1\".\"TEST\".\"Decimal\", NVL2((\"P1\".\"TEST\".\"Geometry\"),(\"P1\".\"TEST\".\"Geometry\").Get_WKB(),NULL) "


  		




  
  234
  
    
                            + "FROM \"P1\".\"TEST\" "


  		




  
  235
  
    
                            + "WHERE ( ((\"P1\".\"TEST\".\"String\" = 'T')) OR (( (\"P1\".\"TEST\".\"ID\") = (2) )) ) "


  		




  
  236
  
    
                            + "ORDER BY \"P1\".\"TEST\".\"ID\" ASC"


  		




  
  237
  
    
            );


  		




  
  238
  
    

  		




  
  239
  
    
            FeatureStore sourceStore = TestUtils.openSourceStore1();


  		




  
  240
  
    

  		




  
  241
  
    
            TableReference table = operations.createTableReference(


  		




  
  242
  
    
                    "dbtest",


  		




  
  243
  
    
                    sqlbuilder.default_schema(),


  		




  
  244
  
    
                    "test",


  		




  
  245
  
    
                    null


  		




  
  246
  
    
            );


  		




  
  247
  
    
            String filter = "\"String\" OR \"ID\" = 2";


  		




  
  248
  
    
            FeatureType featureType = sourceStore.getDefaultFeatureType();


  		




  
  249
  
    
            FeatureQuery query = sourceStore.createFeatureQuery();


  		




  
  250
  
    
            query.addFilter(filter);


  		




  
  251
  
    
            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(


  		




  
  252
  
    
                    table,


  		




  
  253
  
    
                    null,


  		




  
  254
  
    
                    null,


  		




  
  255
  
    
                    query,


  		




  
  256
  
    
                    featureType,


  		




  
  257
  
    
                    featureType,


  		




  
  258
  
    
                    0,


  		




  
  259
  
    
                    0,


  		




  
  260
  
    
                    0


  		




  
  261
  
    
            );


  		




  
  262
  
    
            String sql = resultSetForSetProvider.getSQL();


  		




  
  263
  
    
            System.out.println("###### SQL:" + sql);


  		




  
  264
  
    
            System.out.println("###### EXP:" + expectedSQLs.get(0));


  		




  
  265
  
    

  		




  
  266
  
    
            assertEquals("OR1 SQL", expectedSQLs.get(0), sql);


  		




  
  267
  
    
        } catch (Throwable th) {


  		




  
  268
  
    
            LOGGER.warn("",th);


  		




  
  269
  
    
            throw th;


  		




  
  270
  
    
        }


  		




  
  271
  
    
    }


  		




  
  272
  
    

  		




  
  273
  
    
    public void testOr2() throws Exception {


  		




  
  274
  
    
        try {


  		




  
  275
  
    
            JDBCHelper helper = TestUtils.getJDBCHelper();


  		




  
  276
  
    
            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();


  		




  
  277
  
    
            OperationsFactory operations = helper.getOperations();


  		




  
  278
  
    

  		




  
  279
  
    
            List<String> expectedSQLs = ListBuilder.create(


  		




  
  280
  
    
                    "SELECT \"P1\".\"TEST\".\"ID\", \"P1\".\"TEST\".\"Byte\", \"P1\".\"TEST\".\"Bool1\", \"P1\".\"TEST\".\"Long\", \"P1\".\"TEST\".\"Timestamp\", \"P1\".\"TEST\".\"Date\", \"P1\".\"TEST\".\"Time\", \"P1\".\"TEST\".\"Bool2\", \"P1\".\"TEST\".\"String\", \"P1\".\"TEST\".\"Bool3\", \"P1\".\"TEST\".\"Double\", \"P1\".\"TEST\".\"Bool4\", \"P1\".\"TEST\".\"Float\", \"P1\".\"TEST\".\"Bool5\", \"P1\".\"TEST\".\"Decimal\", NVL2((\"P1\".\"TEST\".\"Geometry\"),(\"P1\".\"TEST\".\"Geometry\").Get_WKB(),NULL) "


  		




  
  281
  
    
                            + "FROM \"P1\".\"TEST\" "


  		




  
  282
  
    
                            + "WHERE ( (( (\"P1\".\"TEST\".\"ID\") = (2) )) OR ((\"P1\".\"TEST\".\"String\" = 'T')) ) "


  		




  
  283
  
    
                            + "ORDER BY \"P1\".\"TEST\".\"ID\" ASC"


  		




  
  284
  
    
            );


  		




  
  285
  
    

  		




  
  286
  
    
            FeatureStore sourceStore = TestUtils.openSourceStore1();


  		




  
  287
  
    

  		




  
  288
  
    
            TableReference table = operations.createTableReference(


  		




  
  289
  
    
                    "dbtest",


  		




  
  290
  
    
                    sqlbuilder.default_schema(),


  		




  
  291
  
    
                    "test",


  		




  
  292
  
    
                    null


  		




  
  293
  
    
            );


  		




  
  294
  
    
            String filter = "\"ID\" = 2 OR \"String\"";


  		




  
  295
  
    
            FeatureType featureType = sourceStore.getDefaultFeatureType();


  		




  
  296
  
    
            FeatureQuery query = sourceStore.createFeatureQuery();


  		




  
  297
  
    
            query.addFilter(filter);


  		




  
  298
  
    
            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(


  		




  
  299
  
    
                    table,


  		




  
  300
  
    
                    null,


  		




  
  301
  
    
                    null,


  		




  
  302
  
    
                    query,


  		




  
  303
  
    
                    featureType,


  		




  
  304
  
    
                    featureType,


  		




  
  305
  
    
                    0,


  		




  
  306
  
    
                    0,


  		




  
  307
  
    
                    0


  		




  
  308
  
    
            );


  		




  
  309
  
    
            String sql = resultSetForSetProvider.getSQL();


  		




  
  310
  
    
            System.out.println("###### SQL:" + sql);


  		




  
  311
  
    
            System.out.println("###### EXP:" + expectedSQLs.get(0));


  		




  
  312
  
    

  		




  
  313
  
    
            assertEquals("OR2 SQL", expectedSQLs.get(0), sql);


  		




  
  314
  
    
        } catch (Throwable th) {


  		




  
  315
  
    
            LOGGER.warn("",th);


  		




  
  316
  
    
            throw th;


  		




  
  317
  
    
        }


  		




  
  318
  
    
    }


  		




  
  319
  
    

  		




  
  320
  
    
    public void testOr3() throws Exception {


  		




  
  321
  
    
        try {


  		




  
  322
  
    
            JDBCHelper helper = TestUtils.getJDBCHelper();


  		




  
  323
  
    
            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();


  		




  
  324
  
    
            OperationsFactory operations = helper.getOperations();


  		




  
  325
  
    

  		




  
  326
  
    
            List<String> expectedSQLs = ListBuilder.create(


  		




  
  327
  
    
                    "SELECT \"P1\".\"TEST\".\"ID\", \"P1\".\"TEST\".\"Byte\", \"P1\".\"TEST\".\"Bool1\", \"P1\".\"TEST\".\"Long\", \"P1\".\"TEST\".\"Timestamp\", \"P1\".\"TEST\".\"Date\", \"P1\".\"TEST\".\"Time\", \"P1\".\"TEST\".\"Bool2\", \"P1\".\"TEST\".\"String\", \"P1\".\"TEST\".\"Bool3\", \"P1\".\"TEST\".\"Double\", \"P1\".\"TEST\".\"Bool4\", \"P1\".\"TEST\".\"Float\", \"P1\".\"TEST\".\"Bool5\", \"P1\".\"TEST\".\"Decimal\", NVL2((\"P1\".\"TEST\".\"Geometry\"),(\"P1\".\"TEST\".\"Geometry\").Get_WKB(),NULL) "


  		




  
  328
  
    
                            + "FROM \"P1\".\"TEST\" "


  		




  
  329
  
    
                            + "WHERE ( ((\"P1\".\"TEST\".\"String\" = 'T')) OR ((\"P1\".\"TEST\".\"String\" = 'T')) ) "


  		




  
  330
  
    
                            + "ORDER BY \"P1\".\"TEST\".\"ID\" ASC"


  		




  
  331
  
    
            );


  		




  
  332
  
    

  		




  
  333
  
    
            FeatureStore sourceStore = TestUtils.openSourceStore1();


  		




  
  334
  
    

  		




  
  335
  
    
            TableReference table = operations.createTableReference(


  		




  
  336
  
    
                    "dbtest",


  		




  
  337
  
    
                    sqlbuilder.default_schema(),


  		




  
  338
  
    
                    "test",


  		




  
  339
  
    
                    null


  		




  
  340
  
    
            );


  		




  
  341
  
    
            String filter = "\"String\" OR \"String\"";


  		




  
  342
  
    
            FeatureType featureType = sourceStore.getDefaultFeatureType();


  		




  
  343
  
    
            FeatureQuery query = sourceStore.createFeatureQuery();


  		




  
  344
  
    
            query.addFilter(filter);


  		




  
  345
  
    
            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(


  		




  
  346
  
    
                    table,


  		




  
  347
  
    
                    null,


  		




  
  348
  
    
                    null,


  		




  
  349
  
    
                    query,


  		




  
  350
  
    
                    featureType,


  		




  
  351
  
    
                    featureType,


  		




  
  352
  
    
                    0,


  		




  
  353
  
    
                    0,


  		




  
  354
  
    
                    0


  		




  
  355
  
    
            );


  		




  
  356
  
    
            String sql = resultSetForSetProvider.getSQL();


  		




  
  357
  
    
            System.out.println("###### SQL:" + sql);


  		




  
  358
  
    
            System.out.println("###### EXP:" + expectedSQLs.get(0));


  		




  
  359
  
    

  		




  
  360
  
    
            assertEquals("OR3 SQL", expectedSQLs.get(0), sql);


  		




  
  361
  
    
        } catch (Throwable th) {


  		




  
  362
  
    
            LOGGER.warn("",th);


  		




  
  363
  
    
            throw th;


  		




  
  364
  
    
        }


  		




  
  365
  
    
    }


  		




  
  366
  
    

  		




  
  367
  
    
}


  		












  

    
      org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/main/java/org/gvsig/oracle/dal/expressionbuilderformatter/OracleFormatter.java
    
  





  28
  28
  
    
            new OracleConstant(this.builder, this),


  		




  29
  29
  
    
            new OracleGeometryParameter(this.builder, this),


  		




  30
  30
  
    
            new AndAnd(this.builder, this),


  		




  
  31
  
    
            new Not(this.builder, this),


  		




  31
  32
  
    
            new And(this.builder, this),


  		




  32
  33
  
    
//            new Case(this.builder, this),


  		




  33
  34
  
    
            new Concat(this.builder, this),


  		












  

    
      org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/main/java/org/gvsig/oracle/dal/expressionbuilderformatter/Not.java
    
  





  
  1
  
    
package org.gvsig.oracle.dal.expressionbuilderformatter;


  		




  
  2
  
    

  		




  
  3
  
    
import java.util.List;


  		




  
  4
  
    
import org.apache.commons.lang3.StringUtils;


  		




  
  5
  
    
import org.gvsig.expressionevaluator.ExpressionBuilder;


  		




  
  6
  
    
import org.gvsig.expressionevaluator.ExpressionBuilder.Function;


  		




  
  7
  
    
import org.gvsig.expressionevaluator.ExpressionBuilder.Value;


  		




  
  8
  
    
import org.gvsig.expressionevaluator.Formatter;


  		




  
  9
  
    
import org.gvsig.fmap.dal.SQLBuilder;


  		




  
  10
  
    
import static org.gvsig.fmap.dal.SQLBuilder.PROP_FEATURE_TYPE;


  		




  
  11
  
    
import static org.gvsig.fmap.dal.SQLBuilder.PROP_QUERY;


  		




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


  		




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


  		




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


  		




  
  15
  
    
import org.gvsig.tools.dataTypes.DataTypes;


  		




  
  16
  
    

  		




  
  17
  
    
/**


  		




  
  18
  
    
 *


  		




  
  19
  
    
 * @author jjdelcerro


  		




  
  20
  
    
 */


  		




  
  21
  
    
class Not implements Formatter<Value> {


  		




  
  22
  
    

  		




  
  23
  
    
    private final Formatter<Value> formatter;


  		




  
  24
  
    
    private final SQLBuilder builder;


  		




  
  25
  
    

  		




  
  26
  
    
    public Not(SQLBuilder builder, Formatter<Value> formatter) {


  		




  
  27
  
    
        this.builder = builder;


  		




  
  28
  
    
        this.formatter = formatter;


  		




  
  29
  
    
    }


  		




  
  30
  
    

  		




  
  31
  
    
    @Override


  		




  
  32
  
    
    public boolean canApply(Value value) {


  		




  
  33
  
    
        if (value instanceof Function) {


  		




  
  34
  
    
            if (value instanceof ExpressionBuilder.Function) {


  		




  
  35
  
    
                return StringUtils.equalsIgnoreCase(ExpressionBuilder.OPERATOR_NOT, ((Function) value).name());


  		




  
  36
  
    
            }


  		




  
  37
  
    
        }


  		




  
  38
  
    
        return false;


  		




  
  39
  
    
    }


  		




  
  40
  
    

  		




  
  41
  
    
    @Override


  		




  
  42
  
    
    public String format(Value function) {


  		




  
  43
  
    
        List<Value> parameters = ((Function) function).parameters();


  		




  
  44
  
    
        Value p1 = parameters.get(0);


  		




  
  45
  
    

  		




  
  46
  
    
        if( isString(p1) ) {


  		




  
  47
  
    
            return "(NOT ("+p1.toString(formatter)+" = 'T'))";


  		




  
  48
  
    
//            return "("+p1.toString(formatter)+" <> 'T')";


  		




  
  49
  
    
        }


  		




  
  50
  
    
        return "(NOT "+p1.toString(formatter)+")";


  		




  
  51
  
    
    }


  		




  
  52
  
    

  		




  
  53
  
    
    /*friend*/ static boolean isString(Value value) {


  		




  
  54
  
    
        FeatureAttributeDescriptor attr = getAttributeDescriptor(value);


  		




  
  55
  
    
        if( attr!=null && attr.getType() == DataTypes.STRING ) {


  		




  
  56
  
    
            return true;


  		




  
  57
  
    
        }


  		




  
  58
  
    
        if( value instanceof ExpressionBuilder.Constant ) {


  		




  
  59
  
    
            ExpressionBuilder.Constant c = (ExpressionBuilder.Constant) value;


  		




  
  60
  
    
            if( c.value() instanceof CharSequence ) {


  		




  
  61
  
    
                return true;


  		




  
  62
  
    
            }


  		




  
  63
  
    
        }


  		




  
  64
  
    
        return false;


  		




  
  65
  
    
    }


  		




  
  66
  
    
    


  		




  
  67
  
    
    /*friend*/ static FeatureAttributeDescriptor getAttributeDescriptor(Value value) {


  		




  
  68
  
    
        FeatureAttributeDescriptor attr = null;


  		




  
  69
  
    
        FeatureType featureType = (FeatureType) value.getProperty(PROP_FEATURE_TYPE);


  		




  
  70
  
    
        if (value instanceof ExpressionBuilder.Variable) {


  		




  
  71
  
    
            ExpressionBuilder.Variable variable = (ExpressionBuilder.Variable) value;


  		




  
  72
  
    
            attr = featureType.getAttributeDescriptor(variable.name());


  		




  
  73
  
    
            if (attr == null) {


  		




  
  74
  
    
                FeatureQuery query = (FeatureQuery) value.getProperty(PROP_QUERY);


  		




  
  75
  
    
                attr = query.getExtraColumn().get(variable.name());


  		




  
  76
  
    
            }


  		




  
  77
  
    
        }


  		




  
  78
  
    
        return attr;


  		




  
  79
  
    
    }


  		




  
  80
  
    
}


  		












  

    
      org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/main/java/org/gvsig/oracle/dal/expressionbuilderformatter/And.java
    
  





  35
  35
  
    
    @Override


  		




  36
  36
  
    
    public String format(Value function) {


  		




  37
  37
  
    
        BinaryOperator operator = (BinaryOperator) function;


  		




  38
  
  
    
        String p1 = operator.left().toString(formatter);


  		




  39
  
  
    
        String p2 = operator.right().toString(formatter);


  		




  40
  
  
    
        String r = MessageFormat.format(OracleSQLBuilder.FORMAT_OPERATOR_AND, p1, p2);


  		




  
  38
  
    
        Value p1 = operator.left();


  		




  
  39
  
    
        Value p2 = operator.right();


  		




  
  40
  
    
        String s1 = p1.toString(formatter);


  		




  
  41
  
    
        String s2 = p2.toString(formatter);


  		




  
  42
  
    
        if( Not.isString(p1) ) {


  		




  
  43
  
    
            s1 = "(" + s1 + " = 'T')";


  		




  
  44
  
    
        }


  		




  
  45
  
    
        if( Not.isString(p2) ) {


  		




  
  46
  
    
            s2 = "(" + s2 + " = 'T')";


  		




  
  47
  
    
        }


  		




  
  48
  
    
        String r = MessageFormat.format(OracleSQLBuilder.FORMAT_OPERATOR_AND, s1, s2);


  		




  41
  49
  
    
        return r;


  		




  42
  50
  
    
    }


  		




  43
  51
  
    

  		












  

    
      org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/main/java/org/gvsig/oracle/dal/expressionbuilderformatter/Or.java
    
  





  35
  35
  
    
    @Override


  		




  36
  36
  
    
    public String format(Value function) {


  		




  37
  37
  
    
        BinaryOperator operator = (BinaryOperator) function;


  		




  38
  
  
    
        String p1 = operator.left().toString(formatter);


  		




  39
  
  
    
        String p2 = operator.right().toString(formatter);


  		




  40
  
  
    
        String r = MessageFormat.format(OracleSQLBuilder.FORMAT_OPERATOR_OR, p1, p2);


  		




  
  38
  
    
        Value p1 = operator.left();


  		




  
  39
  
    
        Value p2 = operator.right();


  		




  
  40
  
    
        String s1 = p1.toString(formatter);


  		




  
  41
  
    
        String s2 = p2.toString(formatter);


  		




  
  42
  
    
        if( Not.isString(p1) ) {


  		




  
  43
  
    
            s1 = "(" + s1 + " = 'T')";


  		




  
  44
  
    
        }


  		




  
  45
  
    
        if( Not.isString(p2) ) {


  		




  
  46
  
    
            s2 = "(" + s2 + " = 'T')";


  		




  
  47
  
    
        }


  		




  
  48
  
    
        String r = MessageFormat.format(OracleSQLBuilder.FORMAT_OPERATOR_OR, s1, s2);


  		




  41
  49
  
    
        return r;


  		




  42
  50
  
    
    }


  		




  43
  51
  
    

  		












  

    
      org.gvsig.oracle/trunk/org.gvsig.oracle/pom.xml
    
  





  15
  15
  
    
  <parent>


  		




  16
  16
  
    
      <groupId>org.gvsig</groupId>


  		




  17
  17
  
    
      <artifactId>org.gvsig.desktop</artifactId>


  		




  18
  
  
    
      <version>2.0.446</version>


  		




  
  18
  
    
      <version>2.0.447-SNAPSHOT</version>


  		




  19
  19
  
    
  </parent>


  		




  20
  20
  
    

  		




  21
  21
  
    
  <url>https://devel.gvsig.org/redmine/projects/gvsig-oracle</url>


  		












Also available in:  Unified diff