All Downloads are FREE. Search and download functionalities are using the official Maven repository.

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