public String generatePreparedFlatCalculationSQL(HashMap<String, String> valueMap) {
String tableName = loadingConfig.getSchemaTableNames().get("featureTable");
String[][] table = loadingConfig.getTableMatrix("flatCalculationTable");
String nameKey = "Calculation/@name";
String nameStr = valueMap.get(nameKey);
// Note that calculation can be null, thus nameStr can be null
String[] names = {"area"};
if (nameStr != null) names = nameStr.split(DELIM);
StringBuffer queryBuf = new StringBuffer("INSERT INTO " + tableName + "(");
for (int i = 0; i < table.length; i++) {
String[] map = table[i];
String attribute = map[1];
if (i != 0) queryBuf.append(", ");
queryBuf.append(attribute);
}
for (int i = 0; i < names.length; i++) queryBuf.append(", " + names[i]);
queryBuf.append(") VALUES(");
for (int i = 0; i < table.length; i++) {
String value = "?";
if (i != 0) queryBuf.append(", ");
queryBuf.append(value);
}
for (int i = 0; i < names.length; i++) queryBuf.append(",?");
queryBuf.append(")");
return queryBuf.toString();
}
public boolean addBatchRegionQuery(PreparedStatement pstmt, HashMap<String, String> valueMap) {
return setQueryParams(
pstmt,
loadingConfig.getSchemaTableNames().get("regionTable"),
valueMap,
loadingConfig.getTableMatrix("regionTable"));
}
public List<String> generateCreateTableStatements() {
List<String> result = new ArrayList<String>();
Map<String, String> tablesNames = loadingConfig.getSchemaTableNames();
/*
* An entry in tablesNames maps a name for the table which is not important in this method
* to the real name of the table in the database. A table can have many definitions, this
* means, the real table name in the database appears as a value for many keys. The
* final query contains the aggregation of all the columns in all definitions.
*/
Map<String, String> processedDefinitions = new HashMap<String, String>();
for (Iterator<Map.Entry<String, String>> tablesNamesIterator =
tablesNames.entrySet().iterator();
tablesNamesIterator.hasNext(); ) {
Map.Entry<String, String> oneDefinition = tablesNamesIterator.next();
// Remove the definition to find all other definitions for the same table
tablesNamesIterator.remove();
// Process this definition
if (!oneDefinition.getValue().equals("")
&& !processedDefinitions.containsKey(oneDefinition.getKey())) {
// Find all other definitions for this table
Map<String, String> allDefinitions = new HashMap<String, String>();
allDefinitions.put(oneDefinition.getKey(), oneDefinition.getValue());
// restOfTheDefinitionsIterator = tablesNamesIterator without the definition being processed
for (Iterator<Map.Entry<String, String>> restOfTheDefinitionsIterator =
tablesNames.entrySet().iterator();
restOfTheDefinitionsIterator.hasNext(); ) {
Map.Entry<String, String> table = restOfTheDefinitionsIterator.next();
if (table.getValue().equals(oneDefinition.getValue())) {
allDefinitions.put(table.getKey(), table.getValue());
processedDefinitions.put(table.getKey(), table.getValue());
}
}
String query = "";
query += "CREATE TABLE " + oneDefinition.getValue() + "(";
for (Iterator<Map.Entry<String, String>> allDefinitionsIterator =
allDefinitions.entrySet().iterator();
allDefinitionsIterator.hasNext(); ) {
Map.Entry<String, String> definition = allDefinitionsIterator.next();
// Process the columns
String[][] columns = loadingConfig.getTableMatrix(definition.getKey());
for (int i = 0; i < columns.length; i++) {
query += "\n\t" + columns[i][1] + "\t\t\t" + columns[i][2];
if (i < (columns.length - 1) || allDefinitions.size() > 1) query += ",";
}
allDefinitionsIterator.remove();
}
query += "\n)";
result.add(query);
}
}
return result;
}
public List<String> generateDropTableStatements() {
List<String> result = new ArrayList<String>();
Map<String, String> tablesNames = loadingConfig.getSchemaTableNames();
for (String tableName : tablesNames.values()) {
if (!tableName.equals("")) result.add("DROP TABLE " + tableName);
}
return result;
}
public boolean addBatchMarkupQuery(PreparedStatement pstmt, HashMap<String, String> valueMap) {
String markupType = valueMap.get("GeometricShape/@xsitype");
if ("Polygon".equals(markupType)) {
return setQueryParams(
pstmt,
markupPolygonTableName,
valueMap,
loadingConfig.getTableMatrix("markupPolygonTable"));
}
return false;
}
public boolean addBatchFlatCalculationQuery(
PreparedStatement pstmt, HashMap<String, String> valueMap) {
String[][] table = loadingConfig.getTableMatrix("flatCalculationTable");
String nameKey = "Calculation/@name";
String valueKey = "CalculationResult/@value";
String nameStr = valueMap.get(nameKey);
if (nameStr == null) return true;
String valueStr = valueMap.get(valueKey);
String[] names = nameStr.split(DELIM);
String[] values = valueStr.split(DELIM);
if (names.length != values.length) return false;
for (int i = 0; i < table.length; i++) {
String[] map = table[i];
String key = map[0];
if (key.contains("partitionKey")) {
String tileName = valueMap.get("Region/@name");
try {
pstmt.setInt(i + 1, partitioningKeyGenerator.getPartitionKeyByTilename(tileName));
} catch (SQLException e) {
e.printStackTrace();
return false;
}
} else {
String value = valueMap.get(key);
try {
pstmt.setString(i + 1, value);
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}
for (int i = 0; i < values.length; i++)
try {
String value = values[i];
if (value.equals("NaN")) value = "0";
pstmt.setString(i + table.length + 1, value);
} catch (SQLException e) {
e.printStackTrace();
}
try {
pstmt.addBatch();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
public String generatePareparedObservationSQL(String quantificationType) {
StringBuffer queryBuf =
new StringBuffer(
"INSERT INTO "
+ loadingConfig.getSchemaTableNames().get("observationOrdinalMultiTable")
+ "(");
;
String[][] table = loadingConfig.getTableMatrix("observationOrdinalMultiTable");
if ("Nominal".equals(quantificationType)) {
queryBuf =
new StringBuffer(
"INSERT INTO "
+ loadingConfig.getSchemaTableNames().get("observationNominalMultiTable")
+ "(");
table = loadingConfig.getTableMatrix("observationNominalMultiTable");
}
for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) {
String attribute = loadingConfig.getTableMatrix("observationSingleTable")[i][1];
if (i != 0) queryBuf.append(", ");
queryBuf.append(attribute);
}
queryBuf.append(",");
for (int i = 0; i < table.length; i++) {
String attribute = table[i][1];
if (i != 0) queryBuf.append(", ");
queryBuf.append(attribute);
}
queryBuf.append(") VALUES(");
// Add single value attributes
for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) {
if (i != 0) queryBuf.append(", ");
queryBuf.append("?");
}
queryBuf.append(",");
// Add multi-value attributes
for (int i = 0; i < table.length; i++) {
if (i != 0) queryBuf.append(", ");
queryBuf.append("?");
}
queryBuf.append(")");
return queryBuf.toString();
}
public String generatePreparedAnnotationMarkupSQL() {
return generatePreparedSQL(
loadingConfig.getSchemaTableNames().get("annotationMarkupTable"),
loadingConfig.getTableMatrix("annotationMarkupTable"))
.toString();
}
public String generatePreparedMarkupSQL() {
return generatePreparedSQL(
markupPolygonTableName, loadingConfig.getTableMatrix("markupPolygonTable"))
.toString();
}
public String generatePreparedRegionSQL() {
return generatePreparedSQL(
loadingConfig.getSchemaTableNames().get("regionTable"),
loadingConfig.getTableMatrix("regionTable"))
.toString();
}
public boolean addBatchObservationQueries(
PreparedStatement opstmt, PreparedStatement npstmt, HashMap<String, String> annMap) {
PreparedStatement pstmt = null;
String quantificationTypeStr = annMap.get("Quantification/@xsitype");
if (quantificationTypeStr == null) return false;
String[] quantificationTypes = quantificationTypeStr.split(DELIM);
String[] singleValues =
new String[loadingConfig.getTableMatrix("observationSingleTable").length];
String[][] multiOrdinalValues =
new String[loadingConfig.getTableMatrix("observationOrdinalMultiTable").length]
[quantificationTypes.length];
String[][] multiNominalValues =
new String[loadingConfig.getTableMatrix("observationNominalMultiTable").length]
[quantificationTypes.length];
String[][] multiValues = null;
for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) {
String key = loadingConfig.getTableMatrix("observationSingleTable")[i][0];
singleValues[i] = annMap.get(key);
}
for (int i = 0; i < loadingConfig.getTableMatrix("observationOrdinalMultiTable").length; i++) {
String[] table = loadingConfig.getTableMatrix("observationOrdinalMultiTable")[i];
String key = table[0];
String valueStr = annMap.get(key);
if (valueStr == null) multiOrdinalValues[i] = null;
else multiOrdinalValues[i] = valueStr.split(DELIM);
}
for (int i = 0; i < loadingConfig.getTableMatrix("observationNominalMultiTable").length; i++) {
String[] table = loadingConfig.getTableMatrix("observationNominalMultiTable")[i];
String key = table[0];
String valueStr = annMap.get(key);
if (valueStr == null) multiNominalValues[i] = null;
else multiNominalValues[i] = valueStr.split(DELIM);
}
for (int count = 0;
count < quantificationTypes.length;
count++) { // For each observation instance <Observation>, generate a new query
String quantificationType = quantificationTypes[count];
String[][] table = loadingConfig.getTableMatrix("observationOrdinalMultiTable");
multiValues = multiOrdinalValues;
if ("Nominal".equals(quantificationType)) {
pstmt = npstmt;
table = loadingConfig.getTableMatrix("observationNominalMultiTable");
multiValues = multiNominalValues;
} else pstmt = opstmt;
// Add single value attributes
for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) {
String[] map = loadingConfig.getTableMatrix("observationSingleTable")[i];
String key = map[0];
String value = annMap.get(key);
try {
pstmt.setString(i + 1, value);
} catch (SQLException e) {
e.printStackTrace();
}
}
// Add multi-value attributes
for (int i = 0; i < table.length; i++) {
if (table[i][0].contains("partitionKey")) {
String tileName = annMap.get("Region/@name");
try {
pstmt.setInt(
i + 1 + loadingConfig.getTableMatrix("observationSingleTable").length,
partitioningKeyGenerator.getPartitionKeyByTilename(tileName));
} catch (SQLException e) {
e.printStackTrace();
return false;
}
} else {
String value = null;
if (multiValues[i] != null) value = multiValues[i][count];
try {
pstmt.setString(
i + 1 + loadingConfig.getTableMatrix("observationSingleTable").length, value);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
try {
pstmt.addBatch();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
return true;
}
private QueryGenerator(String file) {
loadingConfig = new LoadingConfig(file);
markupPolygonTableName = loadingConfig.getSchemaTableNames().get("markupPolygonTable");
}
