InsertR2Blob.java [src/java/crlmod/utils] Revision: Date:
/*
* $Id: 1.0+65 InsertR2Blob.java 402d39c37049 2021-12-29 od $
*
* This file is part of the Cloud Services Integration Platform (CSIP),
* a Model-as-a-Service framework, API, and application suite.
*
* 2012-2024, OMSLab, Colorado State University.
*
* OMSLab licenses this file to you under the MIT license.
* See the LICENSE file in the project root for more information.
*/
package crlmod.utils;
import csip.api.server.ServiceException;
import csip.SessionLogger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;
/**
*
* This class is designed to generate file key pointers for the LMOD database.
* It needs two database connections, one to LMOD (called LMODconnection) and
* one to a Rusle2 database called simply R2connection. it imports r2 blobs for
* the lesser objects (operations, crops, residues).
*
* @author LYaege
*/
@Deprecated
public class InsertR2Blob {
public String gdbPath;
public SessionLogger LOG;
public Connection con;
public void doOps() throws SQLException, ClassNotFoundException, ServiceException {
System.out.println("Begin R2 XML import for Operations");
Class.forName("org.sqlite.JDBC");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Statement LMODSelectStatement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Statement LMODUpdateStatement = con.createStatement();
ResultSet LMODResult;
Connection R2connection = null;
try {
// create a database connection
R2connection = DriverManager.getConnection("jdbc:sqlite:" + gdbPath);
Statement statement = R2connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
ResultSet rs;
try {
rs = statement.executeQuery("Select * from operations");
} catch (Exception ex) {
System.out.println("No operations table in gdb");
return;
}
String query;
while (rs.next()) {
String path = rs.getString("path").trim();
String name = rs.getString("name").trim();
String data = rs.getString("data").trim();
query = "SELECT * \n"
+ "FROM file_metadata \n"
+ "WHERE file_path = '" + path + "'\n"
+ "AND file_name = '" + name + "'";
LMODResult = LMODSelectStatement.executeQuery(query);
if (getRowCount(LMODResult) == 0) {
//For now, skip if the record isn't already in LMOD.
//query = "INSERT INTO lmod.file_metadata(file_path, file_name, r2data, hash_key, hash_data, file_key, object_key) VALUES ($$" +
// path + "$$,$$" +name+ "$$,$$" +data+ "$$, 2, 2, "+rs.getRow()+", $$ope$$)";
//int rows = LMODUpdateStatement.executeUpdate(query);
//System.out.println("rows updated: "+rows);
} else {
while (LMODResult.next()) {
if (LMODResult.getString("r2data") == null || LMODResult.getString("r2data").equals("")) {
System.out.println("new r2Data will be inserted into an existing record");
//System.out.println(rs.getRow());
query = "UPDATE file_metadata SET r2data = '" + data.replaceAll("'", "''") + "' WHERE file_path = '" + path + "' AND file_name = '" + name + "' AND expired_date IS NULL";
//System.out.println(query);
int rows = LMODUpdateStatement.executeUpdate(query);
if (rows > 1) {
System.out.println("More than one row updated: " + query);
}
} else //Strip all whitespaces, and ignore case and compare data strings.
if (data.replaceAll("\\s+", "").equalsIgnoreCase(LMODResult.getString("r2data").replaceAll("\\s+", ""))) {
//System.out.println("Identical data, no action");
} else {
System.out.println("Newer Data Available");
query = "UPDATE file_metadata SET r2data = '" + data.replaceAll("'", "''") + "' WHERE file_path = '" + path + "' AND file_name = '" + name + "' AND expired_date IS NULL";
//System.out.println(query);
int rows = LMODUpdateStatement.executeUpdate(query);
if (rows > 1) {
System.out.println("More than one row updated: " + query);
}
}
}
LMODResult.close();
}
// read the result set
}
R2connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void doVegs() throws SQLException, ClassNotFoundException, ServiceException {
System.out.println("Beginning R2 XML import for vegetations");
Class.forName("org.sqlite.JDBC");
Statement LMODSelectStatement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Statement LMODUpdateStatement = con.createStatement();
ResultSet LMODResult;
Connection R2connection = null;
try {
// create a database connection
R2connection = DriverManager.getConnection("jdbc:sqlite:" + gdbPath);
Statement statement = R2connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
ResultSet rs;
try {
rs = statement.executeQuery("Select * from vegetations");
} catch (Exception ex) {
System.out.println("No vegetations table in gdb");
return;
}
String query;
while (rs.next()) {
String path = rs.getString("path");
String name = rs.getString("name").replace("&", "&").replace("&und;", "_").trim();
String data = rs.getString("data");
//System.out.println(path + "\\" + name);
query = "SELECT * \n"
+ "FROM file_metadata \n"
+ "WHERE file_path = '" + path + "'\n"
+ "AND file_name = '" + name + "'";
LMODResult = LMODSelectStatement.executeQuery(query);
if (getRowCount(LMODResult) == 0) {
//System.out.println(query);
System.out.println("Not found: " + path + "\\" + name);
//query = "INSERT INTO lmod.file_metadata(file_path, file_name, r2data, hash_key, hash_data, file_key, object_key) VALUES ($$" +
// path + "$$,$$" +name+ "$$,$$" +data+ "$$, 2, 2, "+rs.getRow()+", $$veg$$)";
//System.out.println(query);
//int rows = LMODUpdateStatement.executeUpdate(query);
//System.out.println("rows updated: "+rows);
} else {
while (LMODResult.next()) {
if (LMODResult.getString("r2data") == null || LMODResult.getString("r2data").equals("")) {
System.out.println("r2Data will be inserted into an existing record");
//System.out.println(rs.getRow());
query = "UPDATE file_metadata SET r2data = '" + data.replaceAll("'", "''") + "' WHERE file_path = '" + path + "' AND file_name = '" + name + "' AND expired_date IS NULL";
//System.out.println(query);
int rows = LMODUpdateStatement.executeUpdate(query);
if (rows > 1) {
System.out.println("More than one row updated: " + query);
}
} else if (data.replaceAll("\\s+", "").equalsIgnoreCase(LMODResult.getString("r2data").replaceAll("\\s+", ""))) {
//System.out.println("Identical data, no action");
} else {
System.out.println("Newer Data Available");
query = "UPDATE file_metadata SET r2data = '" + data.replaceAll("'", "''") + "' WHERE file_path = '" + path + "' AND file_name = '" + name + "'AND expired_date IS NULL";
//System.out.println(query);
int rows = LMODUpdateStatement.executeUpdate(query);
if (rows > 1) {
System.out.println("More than one row updated: " + query);
}
}
}
LMODResult.close();
}
// read the result set
}
R2connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void doRes() throws SQLException, ClassNotFoundException, ServiceException {
System.out.println("Beginning R2 XML import for Residues");
Class.forName("org.sqlite.JDBC");
Statement LMODSelectStatement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Statement LMODUpdateStatement = con.createStatement();
ResultSet LMODResult;
Connection R2connection = null;
try {
// create a database connection
R2connection = DriverManager.getConnection("jdbc:sqlite:" + gdbPath);
Statement statement = R2connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
ResultSet rs;
try {
rs = statement.executeQuery("Select * from residues");
} catch (Exception ex) {
System.out.println("No residues table in gdb");
return;
}
String query;
while (rs.next()) {
String path = rs.getString("path").replace("&", "&").replace("&und;", "_").trim();
String name = rs.getString("name").replace("&", "&").replace("&und;", "_").trim();
String data = rs.getString("data");
query = "SELECT * \n"
+ "FROM file_metadata \n"
+ "WHERE file_path = '" + path + "'\n"
+ "AND file_name = '" + name + "'";
LMODResult = LMODSelectStatement.executeQuery(query);
if (getRowCount(LMODResult) == 0) {
//System.out.println(query);
System.out.println("Not found: " + path + "\\" + name);
//query = "INSERT INTO lmod.file_metadata(file_path, file_name, r2data, hash_key, hash_data, file_key, object_key) VALUES ($$" +
// path + "$$,$$" +name+ "$$,$$" +data+ "$$, 2, 2, "+rs.getRow()+", $$rsd$$)";
//System.out.println(query);
//int rows = LMODUpdateStatement.executeUpdate(query);
//System.out.println("rows updated: "+rows);
} else {
while (LMODResult.next()) {
if (LMODResult.getString("r2data") == null || LMODResult.getString("r2data").equals("")) {
//System.out.println("r2Data will be inserted into an existing record");
//System.out.println(rs.getRow());
query = "UPDATE file_metadata SET r2data = '" + data.replaceAll("'", "''") + "' WHERE file_path = '" + path + "' AND file_name = '" + name + "'AND expired_date IS NULL";
//System.out.println(query);
int rows = LMODUpdateStatement.executeUpdate(query);
if (rows > 1) {
System.out.println("More than one row updated: " + query);
}
} else if (data.replaceAll("\\s+", "").equalsIgnoreCase(LMODResult.getString("r2data").replaceAll("\\s+", ""))) {
//System.out.println("Identical data, no action");
} else {
System.out.println("Newer Data Available");
query = "UPDATE file_metadata SET r2data = '"
+ data.replaceAll("'", "''") + "' WHERE file_path = '"
+ path + "' AND file_name = '" + name
+ "'AND expired_date IS NULL";
//System.out.println(query);
int rows = LMODUpdateStatement.executeUpdate(query);
if (rows > 1) {
System.out.println("More than one row updated: " + query);
}
}
}
LMODResult.close();
}
// read the result set
}
R2connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static int getRowCount(ResultSet resultSet) {
int size = 0;
try {
resultSet.last();
size = resultSet.getRow();
resultSet.beforeFirst();
} catch (Exception ex) {
return 0;
}
return size;
}
}