
org.nbnResolving.database.impl.AdminUtils Maven / Gradle / Ivy
/* *********************************************************************
* Class AdminUtils
*
* Copyright (c) 2010-2013, German National Library/Deutsche Nationalbibliothek
* Adickesallee 1, D-60322 Frankfurt am Main, Federal Republic of Germany
*
* This program is free software.
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* Kadir Karaca Kocer -- German National Library
*
**********************************************************************/
/* ********************************************************************
* CHANGELOG:
*
* 2013-02-26 More comments and Checkstyle improvements, Karaca
* 2012-03-15 Commented and ported to Apache Maven, Karaca
* 2012-07-19 Bugfix updateURN() - M. Klein
* 2012-07-16 Refactoring - M. Klein
* 2012-07-13 Updating updateURL() - M. Klein
* Created on 2011-09-02 by Karaca Kocer
********************************************************************/
package org.nbnResolving.database.impl;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.ArrayList;
import java.util.TimeZone;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.nbnResolving.database.model.TableINSTITUTION;
import org.nbnResolving.database.model.TableMIMETYPES;
import org.nbnResolving.database.model.TableNAMESPACE;
import org.nbnResolving.database.model.TableNS2INSTITUTION;
import org.nbnResolving.database.model.TableNS2PERMISSION;
import org.nbnResolving.database.model.TablePERSON;
import org.nbnResolving.database.model.TableURL;
import org.nbnResolving.database.model.TableURN;
/**
* Class implementing some utility functions to operate with the local database.
* This class implements only the write, erase & manipulate fuctionality.
* All searching, counting, converting operations are imlemented in
* DatabaseUtils.java
* Please see the project EPICUR database documentation
* to understand the database design.
*
* @author Kadir Karaca Kocer
*/
public class AdminUtils {
private static final Log LOGGER = LogFactory.getLog(AdminUtils.class);
private static final Date EPOCH = new Date(0);
private static final String URN_0 = "2003-10-01 08:00:00"; //this is the beginning of URN service
/**
* Method to call for all SQL UPDATE and DELETE operations.
*
* @param dbConnection The database connection to use.
* @param query The SQL command (UPDATE and DELETE) as string to execute.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int executeUpdate(final Connection dbConnection, final String query) throws SQLException {
if (dbConnection == null) throw new SQLException("executeQuery(): Database connection can not be NULL!");
//final Statement statement = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
final Statement statement = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
// Execute the SQL Query
if (LOGGER.isDebugEnabled()) {LOGGER.debug("executeUpdate(): Executing the SQL query:\n" + query); }
// statement.execute(query);
final int retValue = statement.executeUpdate(query);
statement.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("executeUpdate(): " + retValue + " records modified. End."); }
return retValue;
}
/**
* Method to call for all SQL INSERT operations.
*
* @param dbConnection The database connection to use.
* @param query The SQL command (INSERT) as string to execute.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static long executeInsert(final Connection dbConnection, final String query) throws SQLException {
if (dbConnection == null) throw new SQLException("executeInsert(): Database connection can not be NULL!");
//final Statement statement = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
final Statement statement = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
// Execute the SQL Query
if (LOGGER.isDebugEnabled()) {LOGGER.debug("executeInsert(): Executing the SQL query:\n" + query); }
// statement.execute(query);
final int effectedRows = statement.executeUpdate(query, java.sql.Statement.RETURN_GENERATED_KEYS);
long generatedKey = -1;
if (effectedRows > 0) {
ResultSet rs = statement.getGeneratedKeys();
if (rs.next()) generatedKey = rs.getLong(1);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("executeInsert(): Insert executed. Generated key is: " + generatedKey + " End.");}
} else {
LOGGER.warn("executeInsert(): Insert executed but no Rows effected. Please check. End.");
}
statement.close();
return generatedKey;
}
/**
* Converts a java.util.Date into DataTime format of SQL Database.
*
* @param date Date to convert
* @param utc True if UTC time zone, false = local
* @return String representation in local or UTC of given Date-Time.
* @see java.util.TimeZone
*/
public static String convertToDateTime(final Date date, final boolean utc) {
final java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (utc) sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
return sdf.format(date);
}
/**
* Writes a MimeType in RDBMS.
*
* @param mt Mimetype to save.
* @param dbConnection RDBMS Connection to use.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int writeMimeTypeIntoDB(final TableMIMETYPES mt, final Connection dbConnection) throws SQLException {
String sql = "INSERT INTO MIMETYPES VALUES (";
if (mt.getMimetype_id() > 0)
sql = sql + mt.getMimetype_id() + ", ";
else
sql = sql + "null, ";
if (mt.getMimetype() != null) {
sql = sql + " '" + mt.getMimetype() + "', ";
} else {
sql = sql + " null, ";
}
if (mt.getCreated() != null) {
sql = sql + " '" + convertToDateTime(mt.getCreated(), false) + "');";
} else {
sql = sql + " null);";
}
return (int) executeInsert(dbConnection, sql);
}
/**
* @param mimetypes
* @param dbConnection RDBMS Connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writeMimeTypesIntoDB(final ArrayList mimetypes, final Connection dbConnection) throws SQLException {
for (TableMIMETYPES mt : mimetypes) {
writeMimeTypeIntoDB(mt, dbConnection);
}
}
// ****** INSTITUTION ******************************************************************
/**
* Writes an Institution in RDBMS.
*
* @param inst
* @param dbConnection RDBMS Connection to use.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int writeInstitutionIntoDB(final TableINSTITUTION inst, final Connection dbConnection) throws SQLException {
String sql = "INSERT INTO INSTITUTION VALUES (";
if (inst.getInstitution_id() > 0)
sql = sql + inst.getInstitution_id() + ", ";
else
sql = sql + "null, ";
if (inst.getName() != null) {
sql = sql + " '" + inst.getName() + "', ";
} else {
sql = sql + " null, ";
}
if (inst.getStreet() != null) {
sql = sql + " '" + inst.getStreet() + "', ";
} else {
sql = sql + " null, ";
}
if (inst.getOffice_box() != null) {
sql = sql + " '" + inst.getOffice_box() + "', ";
} else {
sql = sql + " null, ";
}
if (inst.getZip() != null) {
sql = sql + " '" + inst.getZip() + "', ";
} else {
sql = sql + " null, ";
}
if (inst.getCity() != null) {
sql = sql + " '" + inst.getCity() + "', ";
} else {
sql = sql + " null, ";
}
if (inst.getCountry() != null) {
sql = sql + " '" + inst.getCountry() + "', ";
} else {
sql = sql + " null, ";
}
if (inst.getSign() != null) {
sql = sql + " '" + inst.getSign() + "', ";
} else {
sql = sql + " null, ";
}
sql = sql + inst.isCheck_checksum() + ", ";
if (inst.getChecksum_algorithm() != null) {
sql = sql + " '" + inst.getChecksum_algorithm() + "', ";
} else {
sql = sql + " null, ";
}
if ((inst.getCreated() != null) && (inst.getCreated().after(EPOCH))) {
sql = sql + " '" + convertToDateTime(inst.getCreated(), false) + "', ";
} else {
sql = sql + " '" + URN_0 + "', "; //should not occur. Set it to the beginning
}
if ((inst.getLast_modified() != null) && (inst.getLast_modified().after(EPOCH))) {
sql = sql + " '" + convertToDateTime(inst.getLast_modified(), false) + "', ";
} else {
sql = sql + " '" + convertToDateTime(new Date(), false) + "', ";
}
if (inst.getComment() != null) {
sql = sql + " '" + inst.getComment() + "');";
} else {
sql = sql + " null);";
}
return (int) executeInsert(dbConnection, sql);
}
/**
* @param institutions
* @param dbConnection RDBMS Connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writeInstitutionsIntoDB(final ArrayList institutions, final Connection dbConnection) throws SQLException {
for (TableINSTITUTION inst : institutions) {
writeInstitutionIntoDB(inst, dbConnection);
}
}
// ****** NAMESPACE *****************************************************
/**
* Writes a Namespace in RDBMS.
*
* @param ns Namespace to insert into Database.
* @param dbConnection Database connection to use.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int writeNamespaceIntoDB(final TableNAMESPACE ns, final Connection dbConnection) throws SQLException {
String sql = "INSERT INTO NAMESPACE VALUES (";
if (ns.getNs_id() > 0)
sql = sql + ns.getNs_id() + ", ";
else
sql = sql + "null, ";
if (ns.getName() != null) {
sql = sql + " '" + ns.getName() + "', ";
} else {
sql = sql + " null, ";
}
sql = sql + ns.getNs_status() + ", ";
if (ns.getCountry() != null) {
sql = sql + " '" + ns.getCountry() + "', ";
} else {
sql = sql + " null, ";
}
// if (ns.getResolver_adress() != null) {sql = sql + " '" + ns.getResolver_adress() + "', ";} else {sql = sql + " null, ";}
sql = sql + " 'http://nbn-resolving.org', ";
// sql = sql + ns.getIs_part_of() + ", ";
sql = sql + "1, ";
sql = sql + ns.isActive() + ", ";
// sql = sql + "'" + ns.getLast_seperator() + "', ";
sql = sql + "'-', ";
sql = sql + ns.isDig_signed() + ", ";
if (ns.getPublic_key() != null) {
sql = sql + " '" + ns.getPublic_key() + "', ";
} else {
sql = sql + " null, ";
}
// sql = sql + " 'No Key Defined!', ";
sql = sql + ns.isAppears_in_statistic() + ", ";
// sql = sql + ns.getUrn_generation_counter() + ", ";
sql = sql + "0, ";
sql = sql + " '" + convertToDateTime(new Date(), false) + "', "; // TODO: URN_GENERATION_DATE
// if (ns.get != null) { sql = sql + " '" + convertToDateTime(inst.getCreated(), false) + "', "; } else {sql = sql + " null, ";}
if (ns.getCreated() != null) {
sql = sql + " '" + convertToDateTime(ns.getCreated(), false) + "', ";
} else {
sql = sql + ", '" + convertToDateTime(new Date(), false) + "'"; // should not occur
}
if (ns.getLast_modified() != null) {
sql = sql + " '" + convertToDateTime(ns.getLast_modified(), false) + "', ";
} else {
sql = sql + " '" + convertToDateTime(new Date(), false) + "', ";
}
if (ns.getComment() != null) {
sql = sql + " '" + ns.getComment().replaceAll("'", "''") + "');";
} else {
sql = sql + " null);";
}
return (int) executeInsert(dbConnection, sql);
}
/**
* Write a list of Namespaces in the RDBMS.
*
* @param namespaces
* @param dbConnection RDBMS Connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writeNamespacesIntoDB(final ArrayList namespaces, final Connection dbConnection) throws SQLException {
for (TableNAMESPACE ns : namespaces) {
writeNamespaceIntoDB(ns, dbConnection);
}
}
// ****** PERSON *******************************************************
/**
* Write a Person in RDBMS.
*
* @param person Person to write.
* @param dbConnection RDBMS Connection to use.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int writePersonIntoDB(final TablePERSON person, final Connection dbConnection) throws SQLException {
String sql = "insert into PERSON values (";
if (person.getPerson_id() > 0) {
sql = sql + person.getPerson_id();
} else {
sql = sql + "null";
}
String temp = person.getTitle();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
temp = person.getLast_name();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
temp = person.getFirst_name();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
temp = person.getTelephone();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
temp = person.getFax();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
temp = person.getEmail();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
sql = sql + ", " + person.getInstitution_id();
sql = sql + ", " + person.isPrimary_recipient();
temp = person.getDivision();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
temp = person.getLogin();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
temp = person.getPassword();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "'";
} else {
sql = sql + ", null";
}
sql = sql + ", " + person.isActive();
Date date = person.getCreated();
if ((date != null) && (date.after(EPOCH))) {
sql = sql + ", '" + convertToDateTime(date, false) + "'";
} else {
sql = sql + ", '" + URN_0 + "'"; //should not occur. Set it to the beginning
}
date = person.getLast_modified();
if ((date != null) && (date.after(EPOCH))) {
sql = sql + ", '" + convertToDateTime(date, false) + "'";
} else {
sql = sql + ", '" + convertToDateTime(new Date(), false) + "'";
}
temp = person.getComment();
if ((temp != null) && (!temp.isEmpty())) {
sql = sql + ", '" + temp.replaceAll("'", "''") + "');";
} else {
sql = sql + ", null);";
}
return (int) executeInsert(dbConnection, sql);
}
/**
* Write a list of Person in RDBMS.
*
* @param people List of Person.
* @param dbConnection RDBMS Connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writePeopleIntoDB(final ArrayList people, final Connection dbConnection) throws SQLException {
for (TablePERSON person : people) {
writePersonIntoDB(person, dbConnection);
}
}
// ****** NS2INSTITUTION **************************************
/**
* @param n2i
* @param dbConnection RDBMS Connection to use.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int writeNs2InstitutionIntoDB(final TableNS2INSTITUTION n2i, final Connection dbConnection) throws SQLException {
String sql = "insert into NS2INSTITUTION values (";
sql = sql + n2i.getNs_id() + ", " + n2i.getInstitution_id();
Date date = n2i.getCreated();
if ((date != null) && (date.after(EPOCH))) {
sql = sql + ", '" + convertToDateTime(date, false) + "'";
} else {
sql = sql + ", '" + URN_0 + "'"; //should not occur. Set it to the beginning
}
date = n2i.getLast_modified();
if ((date != null) && (date.after(EPOCH))) {
sql = sql + ", '" + convertToDateTime(date, false) + "');";
} else {
sql = sql + ", '" + convertToDateTime(new Date(), false) + "');";
}
return (int) executeInsert(dbConnection, sql);
}
/**
* @param n2iList
* @param dbConnection RDBMS Connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writeNs2InstitutionListIntoDB(final ArrayList n2iList, final Connection dbConnection)
throws SQLException {
for (TableNS2INSTITUTION n2i : n2iList) {
writeNs2InstitutionIntoDB(n2i, dbConnection);
}
}
// ****** NS2PERMISSION ******************************************************
/**
* @param n2p Namespace To Permission.
* @param dbConnection RDBMS Connection to use.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int writeNs2PermissionIntoDB(final TableNS2PERMISSION n2p, final Connection dbConnection) throws SQLException {
String sql = "insert into NS2PERMISSION values (";
sql = sql + n2p.getNs_id() + ", " + n2p.getPermission_id() + ", " + n2p.getPerson_id();
Date date = n2p.getCreated();
if ((date != null) && (date.after(EPOCH))) {
sql = sql + ", '" + convertToDateTime(date, false) + "'";
} else {
sql = sql + ", '" + URN_0 + "'"; // should not occur
}
date = n2p.getLast_modified();
if ((date != null) && (date.after(EPOCH))) {
sql = sql + ", '" + convertToDateTime(date, false) + "');";
} else {
sql = sql + ", '" + convertToDateTime(new Date(), false) + "');";
}
return (int) executeInsert(dbConnection, sql);
}
/**
* @param n2pList
* @param dbConnection RDBMS Connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writeNs2PermissionListIntoDB(final ArrayList n2pList, final Connection dbConnection) throws SQLException {
for (TableNS2PERMISSION n2p : n2pList) {
writeNs2PermissionIntoDB(n2p, dbConnection);
}
}
// ****** URN **********************************************************
/**
* @param urn URN to insert into Database.
* @param dbConnection Database connection to use.
* @return The results of the SQL Operation as JDBC driver delivers. See JavaDoc for java.sql
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static long writeURNIntoDB(final TableURN urn, final Connection dbConnection) throws SQLException {
String colums = " (URN, ";
String values = " values ('" + urn.getUrn() + "', ";
String sql = "insert into URN ";
if (urn.getUrn_id() > 0) {
colums = colums + "URN_ID, ";
values = values + urn.getUrn_id() + ", ";
}
colums = colums + "NS_ID, ";
values = values + urn.getNs_id() + ", ";
if (urn.getParent() > 0) {
colums = colums + "PARENT, ";
values = values + urn.getParent() + ", ";
}
if (urn.getNewer_version() > 0) {
colums = colums + "NEWER_VERSION, ";
values = values + urn.getNewer_version() + ", ";
}
if (urn.getOlder_version() > 0) {
colums = colums + "OLDER_VERSION, ";
values = values + urn.getOlder_version() + ", ";
}
colums = colums + "ACTIVE, ";
values = values + urn.getActive() + ", ";
if (urn.getIdn() != null) {
colums = colums + "IDN, ";
values = values + "'" + urn.getIdn() + "', ";
}
//obsolete: will always be false
//sql = sql + urn.isIs_retrospective() + ", ";
if ((urn.getCreated() != null) && (urn.getCreated().after(EPOCH))) {
colums = colums + "CREATED, ";
values = values + " '" + convertToDateTime(urn.getCreated(), false) + "', ";
} else {
colums = colums + "CREATED, ";
values = values + " '" + convertToDateTime(new Date(), false) + "', ";
}
colums = colums + "LAST_MODIFIED, ";
values = values + " '" + convertToDateTime(new Date(), false) + "', ";
colums = colums + "COMMENT) ";
String comment = urn.getComment();
if (( comment != null) && (!comment.isEmpty())) {
values = values + " '" + urn.getComment().replaceAll("'", "''") + "')";
} else {
values = values + " null)";
}
sql = sql + colums + values;
if (LOGGER.isDebugEnabled()) LOGGER.debug("Inserting URN record:\n" + sql);
//System.out.println(sql);
return executeInsert(dbConnection, sql);
}
/**
* Update an existing URN.
*
* @param urn URN to update in Database.
* @param dbConnection Database connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void updateURN(final TableURN urn, final Connection dbConnection) throws SQLException {
final String now = convertToDateTime(new Date(), false);
String sql = "update URN set";
// URN & URN_ID can not be updated
sql = sql + " NS_ID=" + urn.getNs_id();
if (urn.getParent() > 0) sql = sql + ", PARENT=" + urn.getParent();
// Note: newer and older versions can be deleted with Admin GUI
if (urn.getNewer_version() > 0) sql = sql + ", NEWER_VERSION=" + urn.getNewer_version();
if (urn.getOlder_version() > 0) sql = sql + ", OLDER_VERSION=" + urn.getOlder_version();
sql = sql + ", ACTIVE=" + urn.getActive();
if ((urn.getIdn() != null) && (!urn.getIdn().isEmpty())) sql = sql + ", IDN=" + urn.getIdn();
//obsolete
//sql = sql + ", IS_RETROSPECTIVE=" + urn.isIs_retrospective();
if ((urn.getCreated() != null) && (urn.getCreated().after(EPOCH))) {
sql = sql + ", CREATED='" + convertToDateTime(urn.getCreated(), false) + "'";
} else {
sql = sql + ", CREATED='" + URN_0 + "'"; // should not occur
}
sql = sql + ", LAST_MODIFIED='" + now + "'";
final String comment = urn.getComment();
if ((comment != null) && (!comment.isEmpty())) {
sql = sql + ", COMMENT='" + comment.replaceAll("'", "''") + "'";
}
sql = sql + " where URN='" + urn.getUrn() + "'";
if (LOGGER.isDebugEnabled()) LOGGER.debug("Updating URN record:\n" + sql);
executeUpdate(dbConnection, sql);
}
// ****** URL **********************************************************
/**
* Write an URL in RDBMS.
*
* @param url URL to insert into Database.
* @param dbConnection Database connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writeURLIntoDB(final TableURL url, final Connection dbConnection) throws SQLException {
String sql = "INSERT INTO URL VALUES ('" + url.getUrl().replaceAll("'", "''") + "', ";
sql = sql + url.getUrn_id() + ", ";
sql = sql + url.getMimetype_id() + ", ";
//transforming all booleans TRUE and FALSE to 1 and 0 because of some Database incompatibility issues
if (url.isIs_primary()) sql = sql + "1, "; else sql = sql + "0, ";
if (url.isN2c()) sql = sql + "1, "; else sql = sql + "0, ";
sql = sql + url.getOrigin() + ", ";
sql = sql + url.getInstitution_id() + ", ";
sql = sql + url.getStatus() + ", ";
if (url.getBlocking_time() != null) {
sql = sql + " '" + convertToDateTime(url.getBlocking_time(), false) + "', ";
} else {
sql = sql + " null, ";
}
if (url.getChecksum() != null) {
sql = sql + " '" + url.getChecksum() + "', ";
} else {
sql = sql + " null, ";
}
if (url.isChecksum_error()) sql = sql + "1, "; else sql = sql + "0, ";
if (url.isChecksum_check()) sql = sql + "1, "; else sql = sql + "0, ";
sql = sql + url.getFilesize() + ", ";
if (url.isUrl_check()) sql = sql + "1, "; else sql = sql + "0, ";
sql = sql + url.getUrl_error() + ", ";
if (url.getLast_url_check() != null) {
sql = sql + " '" + convertToDateTime(url.getLast_url_check(), false) + "', ";
} else {
sql = sql + " null, ";
}
if (url.getLast_checksum_check() != null) {
sql = sql + " '" + convertToDateTime(url.getLast_checksum_check(), false) + "', ";
} else {
sql = sql + " null, ";
}
if ((url.getCreated() != null) && (url.getCreated().after(EPOCH))) {
sql = sql + " '" + convertToDateTime(url.getCreated(), false) + "', ";
} else {
sql = sql + " '" + convertToDateTime(new Date(), false) + "', ";
}
if ((url.getLast_modified() != null) && (url.getLast_modified().after(EPOCH))) {
sql = sql + " '" + convertToDateTime(url.getLast_modified(), false) + "', ";
} else {
sql = sql + " '" + convertToDateTime(new Date(), false) + "', ";
}
if (url.getComment() != null) {
sql = sql + " '" + url.getComment().replaceAll("'", "''") + "')";
} else {
sql = sql + " null)";
}
executeInsert(dbConnection, sql);
}
/**
* Write a list of URLs in RDBMS.
*
* @param urls
* @param conn RDBMS Connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void writeURLsIntoDB(final ArrayList urls, final Connection conn) throws SQLException {
for (TableURL url : urls) {
writeURLIntoDB(url, conn);
}
}
/**
* @param url URL to update in Database.
* @param dbConnection Database connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void updateURL(final TableURL url, final Connection dbConnection) throws SQLException {
updateURL(url, null, dbConnection);
}
/**
* Update an URL in RDBMS.
*
* @param url URL to update in Database.
* @param urlStr New URL as String if the resource is just moved.
* @param dbConnection Database connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void updateURL(final TableURL url, final String urlStr, final Connection dbConnection) throws SQLException {
final String now = convertToDateTime(new Date(), false);
String sql = "update URL set";
sql = sql + " URL='" + url.getUrl().replaceAll("'", "''") + "'";
// URL & URN_ID can not be updated
sql = sql + ", MIMETYPE_ID=" + url.getMimetype_id();
if (url.isIs_primary()) {
sql = sql + ", IS_PRIMARY=1";
} else {
sql = sql + ", IS_PRIMARY=0";
}
if (url.isN2c()) {
sql = sql + ", N2C=1";
} else {
sql = sql + ", N2C=0";
}
sql = sql + ", ORIGIN=" + url.getOrigin();
// institution id can not be updated
sql = sql + ", STATUS=" + url.getStatus();
if (url.getBlocking_time() != null) {
sql = sql + ", BLOCKING_TIME='" + convertToDateTime(url.getBlocking_time(), false) + "'";
}
if (url.getChecksum() != null) {
sql = sql + ", CHECKSUM='" + url.getChecksum() + "'";
}
sql = sql + ", FILESIZE=" + url.getFilesize();
if (url.isUrl_check()) {
sql = sql + ", URL_CHECK=1";
} else {
sql = sql + ", URL_CHECK=0";
}
sql = sql + ", URL_ERROR=" + url.getUrl_error();
if (url.getLast_url_check() != null) {
sql = sql + ", LAST_URL_CHECK='" + convertToDateTime(url.getLast_url_check(), false) + "'";
}
if (url.isChecksum_check()) {
sql = sql + ", CHECKSUM_CHECK=1";
} else {
sql = sql + ", CHECKSUM_CHECK=0";
}
if (url.isChecksum_error()) {
sql = sql + ", CHECKSUM_ERROR=1";
} else {
sql = sql + ", CHECKSUM_ERROR=0";
}
if (url.getLast_checksum_check() != null) {
sql = sql + ", LAST_CHECKSUM_CHECK='" + convertToDateTime(url.getLast_checksum_check(), false) + "'";
}
if ((url.getCreated() != null) && url.getCreated().after(EPOCH)) {
sql = sql + ", CREATED='" + convertToDateTime(url.getCreated(), false) + "'";
} else {
sql = sql + ", CREATED='" + URN_0 + "'"; //should not occur
}
sql = sql + ", LAST_MODIFIED='" + now + "'";
final String comment = url.getComment();
if ((comment != null) && (!comment.isEmpty())) {
sql = sql + ", COMMENT='" + comment.replaceAll("'", "''") + "'";
}
sql = sql + " where URL='";
if (urlStr == null) {
sql = sql + url.getUrl().replaceAll("'", "''") + "'";
} else {
sql = sql + urlStr.replaceAll("'", "''") + "'";
}
executeUpdate(dbConnection, sql);
}
/**
* Delete a URL from RDBMS.
*
* @param urlStr URN to delete from Database as Java String.
* @param dbConnection Database connection to use.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static void deleteURL(final String urlStr, final Connection dbConnection) throws SQLException {
final String sql = "delete from URL where URL='" + urlStr.replaceAll("'", "''") + "'";
executeUpdate(dbConnection, sql);
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy