TestDriver.java [test] Revision: default Date:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Queue;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.ThreadLocalRandom;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.LogManager;
import java.util.logging.Logger;
import org.testng.Assert;
import org.testng.Reporter;
import org.testng.annotations.AfterClass;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;
/**
*
* @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
*/
public class TestDriver {
private static Connection conn;
//private static ArrayList<RandomMapunit> testMapunits = new ArrayList<>();
private static Queue<RandomMapunit> testMapunits = new ConcurrentLinkedQueue<>();
private static Queue<RandomMapunit> randomMapunits = new ConcurrentLinkedQueue<>();
/**
* Number of random mapunits to test intersections for.
*/
private static final int MAX_RANDOM_MAPUNITS = 10;
/**
* Acre measurement delta for comparisons in intersection tests.
*/
private static final double MAX_AREA_DELTA = 0.01;
public TestDriver() throws ClassNotFoundException, SQLException {
System.out.println("Setting all logging to be only level: WARNING or higher");
Logger log = LogManager.getLogManager().getLogger("");
for (Handler h : log.getHandlers()) {
h.setLevel(Level.WARNING);
}
}
@Test
public void testResultSetIteration() {
System.out.println("Testing that we can iterate over a ResultSet and that it contains what we expect it to.");
String query = "SELECT top 100 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit "
+ " INNER JOIN legend ON mapunit.lkey=legend.lkey "
+ " INNER JOIN muaggatt ON mapunit.mukey=muaggatt.mukey WHERE "
+ " legend.areatypename like 'Non-MLRA%' ORDER BY NEWID();";
try {
ResultSet results;
Statement statement = conn.createStatement();
results = statement.executeQuery(query);
int count = 0;
while (results.next()) {
String areasymbol = results.getString("areasymbol");
String musym = results.getString("musym");
String mukey = results.getString("mukey");
String muname = results.getString("muname");
double muacres = results.getDouble("muacres");
double brockdepmin = results.getDouble("brockdepmin");
count++;
}
Assert.assertTrue(count == 100); //We should have received 100 random mapunits, and been able to read their data.
} catch (SQLException ex) {
Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
Assert.assertTrue(false, "No results were found for this test, which should have generated a random list of mapunit keys. Something may be wrong with the ResultSet implementation of the SDMDriver.");
}
}
@Test
public void testBadColumnName() {
System.out.println("Testing that ResultSet throws appropriate SQL Exception on missing column name");
String query = "SELECT top 1 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit "
+ "INNER JOIN legend ON mapunit.lkey=legend.lkey "
+ "INNER JOIN muaggatt ON mapunit.mukey=muaggatt.mukey WHERE "
+ " legend.areatypename like 'Non-MLRA%' ORDER BY mapunit.mukey;";
try {
ResultSet results;
Statement statement = conn.createStatement();
results = statement.executeQuery(query);
int count = 0;
while (results.next()) {
String areasymbol = results.getString("areasymbol");
String musym = results.getString("musym");
String mukey = results.getString("mukey");
String muname = results.getString("muname");
double muacres = results.getDouble("muacres");
double brockdepmin = results.getDouble("brockdepmin");
double area = results.getDouble("area"); //This one should cause an exception if the ResultSet object is working correctly.
count++;
}
Assert.assertFalse(count > 0, "This SQL statement should have thrown an SQL Exception, but did not."); //We should have recived an SQLException.
} catch (SQLException ex) {
System.out.println("Column, 'area', not found in the ResultSet, as expected. Test PASS");
Assert.assertTrue(true);
}
}
@Test(enabled=true, threadPoolSize = 10, invocationCount = MAX_RANDOM_MAPUNITS, timeOut = 0)
public void testComplexIntersectionQuery() throws InterruptedException {
String bufferWKT;
RandomMapunit tMapunit = testMapunits.poll();
long sleepTime = ThreadLocalRandom.current().nextInt(10, 2000);
//System.out.println(Thread.currentThread().getId() + ": Sleepting for " + sleepTime + " milliseconds.");
//System.out.flush();
Thread.sleep(sleepTime);
if (null != tMapunit) {
bufferWKT = tMapunit.getBufferWKT();
System.out.println(Thread.currentThread().getId() + ": Runing intersection for mupolygonkey: " + tMapunit.mupolygonKey);
System.out.flush();
String query = "SELECT areasymbol, musym, mukey, muname, muacres, brockdepmin, "
+ " geography::STGeomFromText(intersectPoly.STAsText(), 4326).MakeValid().STArea() / 4046.86 AS area "
+ " FROM ("
+ " SELECT m.areasymbol, m.musym, m.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin, "
+ " m.mupolygongeo.STIntersection(geometry::STGeomFromText ('" + bufferWKT + "', 4326)).MakeValid() as intersectPoly "
+ " FROM dbo.mupolygon as m WITH (index(SI_mupolygon_24876)) "
+ " INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey "
+ " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey "
+ " INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey "
+ " WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('" + bufferWKT + "', 4326)) = 1 "
+ " AND m.mupolygongeo.STIsValid()=1 "
+ " AND (geometry:: STGeomFromText ('" + bufferWKT + "', 4326).STIsValid())=1 "
+ " AND legend.areatypename like 'Non-MLRA%' ) as a ORDER BY mukey;";
try {
ResultSet results;
long timeMills = System.currentTimeMillis();
Statement statement = conn.createStatement();
results = statement.executeQuery(query);
double area = 0.0;
System.out.println(Thread.currentThread().getId() + ": Query took " + ((System.currentTimeMillis()) - timeMills) / 1000.0 + " seconds.");
System.out.flush();
while (results.next()) {
String areasymbol = results.getString("areasymbol");
String musym = results.getString("musym");
String mukey = results.getString("mukey");
String muname = results.getString("muname");
double muacres = results.getDouble("muacres");
double brockdepmin = results.getDouble("brockdepmin");
area += results.getDouble("area");
}
Assert.assertEquals(area, tMapunit.getArea(), MAX_AREA_DELTA, "Intersection query based on random data from the remote server resulted in area calculation that did not match expected outcomes.");
} catch (SQLException ex) {
Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
Assert.assertTrue(false);
}
} else {
System.out.println("Queue is empty. Finished.");
}
}
@Test(enabled=true, threadPoolSize = 10, invocationCount = MAX_RANDOM_MAPUNITS, timeOut = 0)
public void testPreparedStatementQuery() throws InterruptedException {
RandomMapunit tMapunit = randomMapunits.poll();
long sleepTime = ThreadLocalRandom.current().nextInt(10, 2000);
//System.out.println(Thread.currentThread().getId() + ": Sleepting for " + sleepTime + " milliseconds.");
//System.out.flush();
Thread.sleep(sleepTime);
if (null != tMapunit) {
ArrayList<Object> values = new ArrayList<>();
values.add(Integer.parseInt(tMapunit.mukey));
values.add(tMapunit.areasymbol);
values.add(Integer.parseInt(tMapunit.mupolygonKey));
System.out.println(Thread.currentThread().getId() + ": Runing prepared statement query for mupolygonkey: " + tMapunit.mupolygonKey);
System.out.flush();
String query = "SELECT mupolygon.mukey FROM mupolygon "
+ " INNER JOIN mapunit ON mupolygon.mukey=mapunit.mukey "
+ " INNER JOIN legend ON mapunit.lkey=legend.lkey "
+ " WHERE mapunit.mukey=? AND legend.areasymbol=? AND mupolygon.mupolygonkey=?;";
try {
ResultSet results;
long timeMills = System.currentTimeMillis();
PreparedStatement statement = conn.prepareStatement(query);
for (int i = 0; i < values.size(); i++) {
statement.setObject(i + 1, values.get(i));
}
results = statement.executeQuery();
System.out.println(Thread.currentThread().getId() + ": Query took " + ((System.currentTimeMillis()) - timeMills) / 1000.0 + " seconds.");
System.out.flush();
if (results.next()) {
Assert.assertEquals(tMapunit.mukey, results.getString("mukey"), "Results did not find the correct mupolygonkey." );
} else {
Assert.assertTrue(false, "Could not retrieve the record for this mukey using a prepared statement: " + tMapunit.mukey);
}
} catch (SQLException ex) {
Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
Assert.assertTrue(false, "Could not use the SDMPreparedStatement: " + ex.getMessage());
}
}else {
System.out.println("Queue is empty. Finished.");
}
}
@BeforeClass
public static void setUpClass() throws Exception {
System.out.println("Loading the SDMDriver, and testing a connection.");
Class.forName("csip.sdm.SDMDriver");
Reporter.log("Driver was loaded for: csip.sdm.SDMDriver");
conn = DriverManager.getConnection("jdbc:sdm:rest://SDMDataAccess.sc.egov.usda.gov/Tabular/post.rest");
Reporter.log("Connection to remote SDM REST Service was successful");
//fill randomMapunits
String query = "SELECT mupolygon.mupolygonkey, mupolygon.mukey, legend.areasymbol, mupolygon.mupolygongeo.STCentroid().STAsText() as centerPoint "
+ "FROM mupolygon, legend, mapunit "
+ " WHERE mupolygon.mupolygonkey in "
+ " ("
+ " SELECT TOP " + MAX_RANDOM_MAPUNITS + " mupolygonkey "
+ " FROM mupolygon, legend, mapunit "
+ " WHERE legend.lkey=mapunit.lkey AND mapunit.mukey=mupolygon.mukey AND legend.areatypename like 'Non-MLRA%' "
+ " ORDER BY newid() "
+ " ) "
+ " AND legend.lkey=mapunit.lkey AND mapunit.mukey=mupolygon.mukey; ";
try {
System.out.println("Building a list of " + MAX_RANDOM_MAPUNITS + " random mapunits to test with.");
ResultSet results;
Statement statement = conn.createStatement();
results = statement.executeQuery(query);
while (results.next()) {
System.out.println("Got mupolygonkey: " + results.getString("mupolygonkey"));
RandomMapunit tMapunit = new RandomMapunit(results.getString("mupolygonkey"), results.getString("mukey"), results.getString("areasymbol"), results.getString("centerPoint"));
testMapunits.add(tMapunit);
randomMapunits.add(tMapunit);
}
} catch (SQLException ex) {
Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
Assert.assertTrue(false, "Could not generate a random set of mapunit buffers for use in upcoming tests: " + ex.getMessage());
}
System.out.println("Creating randomly sized buffer polygons from each mapunit centroid.");
for (RandomMapunit tMapunit : testMapunits) {
tMapunit.getBuffers(conn);
}
}
@AfterClass
public static void tearDownClass() throws Exception {
System.out.println("Closing conneciton.");
conn.close();
}
@BeforeMethod
public void setUpMethod() throws Exception {
}
@AfterMethod
public void tearDownMethod() throws Exception {
}
private static class RandomMapunit {
private String mupolygonKey;
private String mukey;
private String areasymbol;
private String centroidWKT;
private String bufferWKT;
private double area;
private double bufferSize;
public RandomMapunit(String mupolygonKey, String mukey, String areasymbol, String centroidWKT) {
this.mupolygonKey = mupolygonKey;
this.mukey = mukey;
this.areasymbol = areasymbol;
this.centroidWKT = centroidWKT;
bufferSize = ThreadLocalRandom.current().nextDouble(50, 100);
}
public void getBuffers(Connection conn) {
String query = "SELECT geography::STGeomFromText('" + centroidWKT + "', 4326).STBuffer(" + bufferSize + ").STAsText() as bufferWKT, "
+ " geography::STGeomFromText('" + centroidWKT + "', 4326).STBuffer(" + bufferSize + ").STArea() / 4046.86 as area "
+ "FROM mupolygon"
+ " WHERE mupolygonkey=" + mupolygonKey + "; ";
try {
ResultSet results;
Statement statement = conn.createStatement();
results = statement.executeQuery(query);
if (results.next()) {
bufferWKT = results.getString("bufferWKT");
area = results.getDouble("area");
}
} catch (SQLException ex) {
Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex);
Assert.assertTrue(false, "Could not generate a randomly sized buffer within this mapunit: " + ex.getMessage());
}
}
public String getBufferWKT() {
return bufferWKT;
}
public double getArea() {
return area;
}
}
}