Revision 47606 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/src/test/java/org/gvsig/sqlite/dal/RunSQL.java

View differences:

RunSQL.java
20 20 
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES_NAME;
21 21 
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException;
22 22 
import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;
23 
import org.gvsig.fmap.geom.Geometry;
23 24 
import org.gvsig.sqlite.dal.functions.Functions;
25 
import org.gvsig.sqlite.dal.geopackage.GeopackageGeometryParser;
24 26 
import org.gvsig.sqlite.dal.geopackage.GeopackageUtils;
25 27 
import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;
26 28 
import org.slf4j.Logger;
......
197 199 
//        sqls.add( "SELECT MIN(\"test\".\"ID\") AS \"ID\", MAX(\"test\".\"Byte\") AS \"Byte\", NULL AS \"Bool1\", \"test\".\"Long\", NULL AS \"Timestamp\", NULL AS \"Date\", NULL AS \"Time\", NULL AS \"Bool2\", NULL AS \"String\", NULL AS \"Bool3\", SUM(\"test\".\"Double\") AS \"Double\", NULL AS \"Bool4\", NULL AS \"Float\", NULL AS \"Bool5\", NULL AS \"Decimal\", NULL AS \"Geometry\", (\"test\".\"ID\" * 2) AS \"Compu1\", SUM((\"test\".\"Long\" + 300)) AS \"Compu2\", SUM(1) AS \"Compu3\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)) AS \"Extra1\", SUM(((20 + \"test\".\"Byte\") + (\"test\".\"ID\" * 2))) AS \"Extra2\", NULL AS \"Extra3\" FROM \"test\" GROUP BY \"test\".\"Long\", ((\"test\".\"Long\" + 10) + (\"test\".\"ID\" * 2)), (\"test\".\"ID\" * 2) ORDER BY ((\"Long\" + 10) + (\"ID\" * 2)) ASC NULLS LAST, SUM(((20 + \"Byte\") + (\"ID\" * 2))) ASC NULLS LAST");
198 200 

  		




  199
  201
  
    
//          sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", EXISTS(( SELECT \"countries\".\"ISO_A2\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) AS \"EXISTS62a964cd7bc24f409b97c03b9170408d\" FROM \"test\" WHERE EXISTS(( SELECT \"countries\".\"ISO_A2\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) ORDER BY \"test\".\"ID\" ASC"; //*


  		




  200
  
  
    
          sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" WHERE ( (\"test\".\"ID\") = (4) ) LIMIT 1";


  		




  
  202
  
    
//          sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\" FROM \"test\" WHERE ( (\"test\".\"ID\") = (4) ) LIMIT 1";


  		




  201
  203
  
    
//        String sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", EXISTS(( SELECT \"test\".\"Long\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) AS \"EXISTS62a964cd7bc24f409b97c03b9170408d\" FROM \"test\" WHERE EXISTS(( SELECT \"test\".\"Long\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) ORDER BY \"test\".\"ID\" ASC"; //*


  		




  202
  204
  
    
//        String sql = "SELECT MIN(\"test\".\"ID\") AS \"ID\", MAX(\"test\".\"Byte\") AS \"Byte\", NULL AS \"Bool1\", \"test\".\"Long\", NULL AS \"Timestamp\", NULL AS \"Date\", NULL AS \"Time\", NULL AS \"Bool2\", NULL AS \"String\", NULL AS \"Bool3\", SUM(\"test\".\"Double\") AS \"Double\", NULL AS \"Bool4\", NULL AS \"Float\", NULL AS \"Bool5\", NULL AS \"Decimal\", NULL AS \"Geometry\" FROM \"test\" WHERE EXISTS(( SELECT \"countries\".\"ISO_A2\" FROM \"countries\" WHERE ((\"test\".\"STRING\" = \"countries\".\"CONTINENT\") AND (\"countries\".\"LASTCENSUS\" < 0)) LIMIT 1 )) GROUP BY \"test\".\"Long\" ORDER BY \"test\".\"Long\" ASC NULLS LAST"; //*


  		




  203
  205
  
    
        


  		




  ......




  228
  230
  
    
//"ORDER BY \"test\".\"Long\" ASC NULLS LAST\n" +


  		




  229
  231
  
    
//"";


  		




  230
  232
  
    

  		




  231
  
  
    
        sql = "SELECT 1 FROM \"test\" LIMIT 1";


  		




  
  233
  
    
//        sql = "SELECT IIF(ST_ExtentAggregate(\"Geometry\") IS NULL,NULL, ST_AsBinary(ST_ExtentAggregate(\"Geometry\"))) FROM \"test\" WHERE ( (\"Geometry\") IS NOT NULL )";


  		




  
  234
  
    
//        sql = "SELECT \"test\".\"ID\", \"test\".\"Byte\", \"test\".\"Bool1\", \"test\".\"Long\", \"test\".\"Timestamp\", \"test\".\"Date\", \"test\".\"Time\", \"test\".\"Bool2\", \"test\".\"String\", \"test\".\"Bool3\", \"test\".\"Double\", \"test\".\"Bool4\", \"test\".\"Float\", \"test\".\"Bool5\", \"test\".\"Decimal\", \"test\".\"Geometry\", JSON_VALUE(\"test\".\"String\", '$.acc_cit') AS \"JsonValue1\" FROM \"test\" WHERE (JSON_VALUE(\"test\".\"String\", '$.acc_cit') = 1) ORDER BY \"test\".\"ID\" ASC;";


  		




  
  235
  
    
//        sql = "SELECT IIF(ST_Extent(\"Geometry\") IS NULL,NULL, ST_AsBinary(ST_Extent(\"Geometry\"))) AS \"envelope\" FROM \"test\" WHERE (ST_Intersects((ST_Envelope(\"Geometry\")),((x'010300000001000000050000000000000000000000000000000000000000000000000000000000000000005940000000000000594000000000000059400000000000005940000000000000000000000000000000000000000000000000'))) AND Long = 27)";


  		




  
  236
  
    
//        sql = "SELECT IIF(ST_Extent(\"Geometry\") IS NULL,NULL, ST_AsBinary(ST_Extent(\"Geometry\"))) AS \"envelope\" FROM \"test\" WHERE (( ((\"Long\" IN (SELECT \"rtree_test_Geometry\".\"id\" FROM \"rtree_test_Geometry\" WHERE \"rtree_test_Geometry\".\"minx\" <= (100.0) AND \"rtree_test_Geometry\".\"miny\" <= (100.0) AND \"rtree_test_Geometry\".\"maxx\" >= (0.0) AND \"rtree_test_Geometry\".\"maxy\" >= (0.0)))) AND ST_Intersects((\"Geometry\"),((x'010300000001000000050000000000000000000000000000000000000000000000000000000000000000005940000000000000594000000000000059400000000000005940000000000000000000000000000000000000000000000000')) )) AND Long = 27)";


  		




  
  237
  
    
//        sql = "SELECT ( ST_ExtentAggregate(\"Geometry\") ) AS \"envelope\" FROM \"test\" WHERE (ST_Intersects((ST_Envelope(\"Geometry\")),(ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326)))) AND Long = 27)";


  		




  
  238
  
    
        sql = "SELECT \"PINTURA_VIAL_209000\".\"GEOMETRY\", \"PINTURA_VIAL_209000\".\"MSLINK\" FROM \"PINTURA_VIAL_209000\" WHERE (NOT((\"PINTURA_VIAL_209000\".\"GEOMETRY\" IS NULL)) AND ((\"MSLINK\" IN (SELECT \"rtree_PINTURA_VIAL_209000_GEOMETRY\".\"id\" FROM \"rtree_PINTURA_VIAL_209000_GEOMETRY\" WHERE \"rtree_PINTURA_VIAL_209000_GEOMETRY\".\"minx\" <= (722872.8431989691) AND \"rtree_PINTURA_VIAL_209000_GEOMETRY\".\"miny\" <= (4251413.508219072) AND \"rtree_PINTURA_VIAL_209000_GEOMETRY\".\"maxx\" >= (722486.6794051547) AND \"rtree_PINTURA_VIAL_209000_GEOMETRY\".\"maxy\" >= (4251076.3575221645))))) ORDER BY \"PINTURA_VIAL_209000\".\"MSLINK\" ASC";


  		




  232
  239
  
    
        runSQL(sql);


  		




  233
  240
  
    
    } 


  		




  234
  241
  
    
private static void runSQL(String sql) throws Exception{


  		




  ......




  236
  243
  
    
}


  		




  237
  244
  
    

  		




  238
  245
  
    
private static void runSQL(List<String> sqls) throws Exception{


  		




  239
  
  
    
        String dbPath = "/home/fdiaz/projects/gvSIG/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/target/test-dbs/";


  		




  240
  
  
    
        String dbName = "testCreate-1699425817409-001.gpkg";


  		




  241
  
  
    
        


  		




  
  246
  
    
//        String dbPath = "/home/fdiaz/projects/gvSIG/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/target/test-dbs/";


  		




  
  247
  
    
        String dbPath = "/home/fdiaz/tmp/";


  		




  
  248
  
    
        String dbName = "PINTURA_VIAL_209000.gpkg";


  		




  242
  249
  
    
        System.out.println(dbPath+dbName);


  		




  243
  250
  
    

  		




  244
  251
  
    
        for (String sql : sqls) {


  		




  
  252
  
    
        long t1 = System.currentTimeMillis();


  		




  245
  253
  
    
            


  		




  246
  254
  
    
        


  		




  247
  
  
    
        String table = "testCreateTarget1";


  		




  248
  
  
    
        sql = sql.replaceAll("_test_", "_"+table+"_");


  		




  249
  
  
    
        sql = sql.replaceAll("\"test\"", "\""+table+"\"");


  		




  
  255
  
    
//        String table = "testCreateTarget1";


  		




  
  256
  
    
//        sql = sql.replaceAll("_test_", "_"+table+"_");


  		




  
  257
  
    
//        sql = sql.replaceAll("\"test\"", "\""+table+"\"");


  		




  250
  258
  
    

  		




  251
  259
  
    
        System.out.println("SQL " + sql);


  		




  252
  260
  
    

  		




  ......




  284
  292
  
    
            System.out.print(StringUtils.repeat("-", colsizes[i] - 1) + " ");


  		




  285
  293
  
    
        }


  		




  286
  294
  
    
        System.out.println();


  		




  
  295
  
    
        GeopackageGeometryParser parser = GeopackageUtils.createGeometryParser();


  		




  287
  296
  
    
        while (rs.next()) {


  		




  288
  297
  
    
            for (int i = 1; i <= columnCount; i++) {


  		




  289
  
  
    
                System.out.print(StringUtils.rightPad(Objects.toString(rs.getObject(i)), colsizes[i]));


  		




  
  298
  
    
                if(i==1){


  		




  
  299
  
    
                    byte[] bytes = rs.getBytes(1);


  		




  
  300
  
    
                    if( GeopackageUtils.isGeopackageGeometry(bytes) ) {


  		




  
  301
  
    
                        parser.clean();


  		




  
  302
  
    
                        Geometry geom = parser.parseToGeometry(null, bytes);


  		




  
  303
  
    
                        System.out.print(StringUtils.rightPad(Objects.toString("GEOM"), colsizes[i]));


  		




  
  304
  
    
                    } else {


  		




  
  305
  
    
                        System.out.print(StringUtils.rightPad(Objects.toString("BYTES"), colsizes[i]));


  		




  
  306
  
    
                    }


  		




  
  307
  
    

  		




  
  308
  
    
                } else {


  		




  
  309
  
    
                    System.out.print(StringUtils.rightPad(Objects.toString(rs.getObject(i)), colsizes[i]));


  		




  
  310
  
    
                }


  		




  290
  311
  
    
            }


  		




  291
  312
  
    
            System.out.println();


  		




  292
  313
  
    
        }


  		




  293
  314
  
    
        rs.close();


  		




  294
  315
  
    
        System.out.println();


  		




  295
  316
  
    
        JDBCUtils.close(conn);


  		




  
  317
  
    
            long t2 = System.currentTimeMillis();


  		




  
  318
  
    
            System.out.println("TIME: "+(t2-t1)+"ms");


  		




  296
  319
  
    
        }


  		




  
  320
  
    
        


  		




  297
  321
  
    
    }


  		




  298
  322
  
    

  		




  299
  323
  
    
    public static Connection getConnection(String fName) throws SQLException {


  		












Also available in:  Unified diff