org.nbnResolving.database.impl.DatabaseUtils Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of database Show documentation
Show all versions of database Show documentation
Java classes providing database access to URN:NBN database of National Bibliography Numbers system.
See the official URN:NBN Resolver http://nbn-resolving.org or http://persid.org
/* *********************************************************************
* Class DatabaseUtils
*
* 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-03-27 obvsg - Bugfixes
* 2013-02-26 More comments and Checkstyle improvements.
* 2012-03-15 Commented and ported to Apache Maven
* 2012-09-11 Replacing rs.getDate() with rs.getTimestamp() - M. Klein
* 2012-08-30 New function getURLsByInstId() - M. Klein
* 2012-08-29 New functions getNumberOf...ByInstId() - M. Klein
* 2012-08-28 Functions getNumberOf...ByPersId() deleted - M. Klein
* 2012-07-19 New function getNsPermissionsByPersId() - M. Klein
* 2012-07-16 Refactoring - M. Klein
* 2012-07-09 New functions getNumberOf...ByPersId() - M. Klein
* Created on 2010-08-07 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.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.nbnResolving.database.model.JoinNSPERMISSION;
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.TablePERMISSION;
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 relational database management system.
*
* This class implements only the read-only fuctionality.
* All insert|update|delete operations are implemented in
* AdminUtils.java Please see the project EPICUR database
* documentation to understand the database design.
*
* @author Kadir Karaca Kocer
*/
public class DatabaseUtils {
private static final Log LOGGER = LogFactory.getLog(DatabaseUtils.class);
// some constants
/** Only the inactive URNs */
public static final int URN_INACTIVE = 0;
/** Only the active ones */
public static final int URN_ACTIVE = 1;
/** Only the reserved URNs */
public static final int URN_RESERVED = 2;
/** All URNs. No matter which status. */
public static final int URN_ALL = 3;
/** limits the result set size for performance reasons */
public static final int GUI_LIMIT = 100;
/** to get all INSTITUTION & NAMESPACE */
public static final int LIMIT_SMALL = 500;
/** to get all NS2PERMISSION table */
public static final int LIMIT_MID = 2000;
/** to get all URN */
public static final int LIMIT_URN = 3000;
/** see OFFSET parameter of SELECT command in your RDBMS documentation */
// public final static int OFFSET = 100;
/**
* Connect to RDBMS. Not recommended. Use JNDI instead.
*
* @param database Database to connect to.
* @param driver JDBC driver to use
* @param user User name in this database
* @param password Password for this user
* @param readonly TRUE if the connection should be Read-Only (safer and faster).
* FALSE if you must write into database.
* @return A database connection to use
* @throws SQLException Something went wrong during the RDBMS operation.
* @throws ClassNotFoundException
*/
public static Connection connectToDatabase(final String database, final String driver, final String user,
final String password, final boolean readonly) throws SQLException, ClassNotFoundException {
// Class.forName("net.sourceforge.jtds.jdbc.Driver");
if (LOGGER.isDebugEnabled()) {LOGGER.debug("EpicurLocalResolver: Connecting to database " + database + " as user " + user);}
Class.forName(driver);
final Connection connection = java.sql.DriverManager.getConnection(database, user, password);
if (readonly) {
connection.setReadOnly(true);
}
return connection;
}
/**
* Execute the given SQL query.
*
* @param dbConnection The database connection to use.
* @param query The query as string to execute.
* @param limit Result Set limit.
* @return The results of the SQL Query as java.sql.ResultSet
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ResultSet executeQuery(final Connection dbConnection, final String query, final int limit) 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_READ_ONLY);
// check this:
// statement.setFetchSize(LIMIT_URN);
statement.setMaxRows(limit);
// Execute the SQL Query
if (LOGGER.isDebugEnabled()) {LOGGER.debug("executeQuery(): Executing the SQL query:\n" + query);}
// get the results
final ResultSet rs = statement.executeQuery(query);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("executeQuery(): Returning the result set. End.");}
return rs;
}
/**
* @param rs Result Set to process.
* @param extended If you set this true you get more (but not so important) information from the database.
* @return The Java representation of the Database Result Set.
* @see org.nbnResolving.database.model.TableURN
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableURN resultSetToURN(final ResultSet rs, final boolean extended) throws SQLException {
if (rs == null) {throw new SQLException("Result Set can not be null!");}
final TableURN urnInfo = new TableURN();
urnInfo.setUrn(rs.getString("URN"));
urnInfo.setUrn_id(rs.getLong("URN_ID"));
final int ns_id = rs.getInt("NS_ID");
urnInfo.setNs_id(ns_id);
urnInfo.setParent(rs.getLong("PARENT"));
urnInfo.setNewer_version(rs.getLong("NEWER_VERSION"));
urnInfo.setOlder_version(rs.getLong("OLDER_VERSION"));
urnInfo.setActive(rs.getInt("ACTIVE"));
// urnInfo.setCreated(rs.getDate("CREATED"));
urnInfo.setCreated(rs.getTimestamp("CREATED"));
// urnInfo.setLast_modified(rs.getDate("LAST_MODIFIED"));
urnInfo.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
if (extended) {
urnInfo.setIdn(rs.getString("IDN"));
urnInfo.setIs_retrospective(rs.getBoolean("IS_RETROSPECTIVE"));
urnInfo.setComment(rs.getString("COMMENT"));
}
return urnInfo;
}
/**
* @param rs Result set of a previous SQL query.
* @param extended Simple or extended query.
* @return TableURL object for given Database ResultSet.
* @see org.nbnResolving.database.model.TableURL
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableURL resultSetToURL(final ResultSet rs, final boolean extended) throws SQLException {
if (rs == null) {throw new SQLException("Result Set can not be null!");}
final TableURL urlInfo = new TableURL();
urlInfo.setUrl(rs.getString("URL"));
urlInfo.setUrn_id(rs.getLong("URN_ID"));
final int mimetypeId = rs.getInt("MIMETYPE_ID");
urlInfo.setMimetype_id(mimetypeId);
urlInfo.setIs_primary(rs.getBoolean("IS_PRIMARY"));
urlInfo.setN2c(rs.getBoolean("N2C"));
urlInfo.setOrigin(rs.getByte("ORIGIN"));
final int institutionId = rs.getInt("INSTITUTION_ID");
urlInfo.setInstitution_id(institutionId);
urlInfo.setStatus(rs.getByte("STATUS"));
urlInfo.setFilesize(rs.getLong("FILESIZE"));
urlInfo.setUrl_error(rs.getInt("URL_ERROR"));
// urlInfo.setCreated(rs.getDate("CREATED"));
urlInfo.setCreated(rs.getTimestamp("CREATED"));
// urlInfo.setLast_modified(rs.getDate("LAST_MODIFIED"));
urlInfo.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
if (extended) {
// we do not need the others at the moment
// urlInfo.setBlocking_time(rs.getDate("BLOCKING_TIME"));
urlInfo.setBlocking_time(rs.getTimestamp("BLOCKING_TIME"));
urlInfo.setChecksum(rs.getString("CHECKSUM"));
urlInfo.setChecksum_check(rs.getBoolean("CHECKSUM_CHECK"));
urlInfo.setChecksum_error(rs.getBoolean("CHECKSUM_ERROR"));
urlInfo.setUrl_check(rs.getBoolean("URL_CHECK"));
// urlInfo.setLast_url_check(rs.getDate("LAST_URL_CHECK"));
urlInfo.setLast_url_check(rs.getTimestamp("LAST_URL_CHECK"));
// urlInfo.setLast_checksum_check(rs.getDate("LAST_CHECKSUM_CHECK"));
urlInfo.setLast_checksum_check(rs.getTimestamp("LAST_CHECKSUM_CHECK"));
urlInfo.setComment(rs.getString("COMMENT"));
}
return urlInfo;
}
/**
* @param rs Result set of a previous SQL query.
* @param extended Simple or extended query.
* @return List of URLs for given Database ResultSet as Java ArrayList.
* @see org.nbnResolving.database.model.TableURL
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList resultSetToURLarray(ResultSet rs, boolean extended) throws SQLException {
if (rs == null) {throw new SQLException("Result Set can not be null!");}
ArrayList list = new ArrayList();
// loop over the results
while (rs.next()) {
list.add(resultSetToURL(rs, extended));
}
return list;
}
/**
* Converts given Result Set to Namespace bean.
*
* @param rs Result set of a previous SQL query.
* @return Java representation of NAMESPACE table. See the RDBMS model.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableNAMESPACE resultSetToNAMESPACE(final ResultSet rs) throws SQLException {
if (rs == null) {throw new SQLException("Result Set can not be null!");}
final TableNAMESPACE namespace = new TableNAMESPACE();
namespace.setNs_id(rs.getInt("NS_ID"));
namespace.setName(rs.getString("NAME"));
namespace.setNs_status(rs.getByte("NS_STATUS"));
namespace.setCountry(rs.getString("COUNTRY"));
// RESOLVER_ADRESS
// namespace.setIs_part_of(rs.getInt("IS_PART_OF"));
namespace.setActive(rs.getBoolean("ACTIVE"));
// LAST_SEPERATOR
namespace.setDig_signed(rs.getBoolean("DIG_SIGNED"));
namespace.setPublic_key(rs.getString("PUBLIC_KEY"));
// APPEARS_IN_STATISTIC
// URN_GENERATION_COUNTER
// URN_GENERATION_DATE
// namespace.setCreated(rs.getDate("CREATED"));
namespace.setCreated(rs.getTimestamp("CREATED"));
// namespace.setLast_modified(rs.getDate("LAST_MODIFIED"));
namespace.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
// COMMENT
return namespace;
}
/**
* @param rs Result set of a previous SQL query.
* @return Java representation of PERSON table. See the RDBMS model.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TablePERSON resultSetToPERSON(final ResultSet rs) throws SQLException {
if (rs == null) {throw new SQLException("resultSetToPERSON: Result Set can not be null!");}
final TablePERSON person = new TablePERSON();
person.setPerson_id(rs.getInt("PERSON_ID"));
person.setTitle(rs.getString("TITLE"));
person.setLast_name(rs.getString("LAST_NAME"));
person.setFirst_name(rs.getString("FIRST_NAME"));
person.setTelephone(rs.getString("TELEPHONE"));
person.setFax(rs.getString("FAX"));
person.setEmail(rs.getString("EMAIL"));
person.setInstitution_id(rs.getInt("INSTITUTION_ID"));
person.setPrimary_recipient(rs.getBoolean("PRIMARY_RECIPIENT"));
person.setDivision(rs.getString("DIVISION"));
person.setLogin(rs.getString("LOGIN"));
person.setPassword(rs.getString("PASSWORD"));
person.setActive(rs.getBoolean("ACTIVE"));
// person.setCreated(rs.getDate("CREATED"));
person.setCreated(rs.getTimestamp("CREATED"));
// person.setLast_modified(rs.getDate("LAST_MODIFIED"));
person.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
person.setComment(rs.getString("COMMENT"));
return person;
}
/**
* @param rs Result set of a previous SQL query.
* @return Java representation of PERMISSION table. See the RDBMS model.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TablePERMISSION resultSetToPERMISSION(final ResultSet rs) throws SQLException {
if (rs == null) throw new SQLException("resultSetToPERMISSION: Result Set can not be null!");
final TablePERMISSION permission = new TablePERMISSION();
permission.setPermission_id(rs.getInt("PERMISSION_ID"));
permission.setCreate_urn(rs.getBoolean("CREATE_URN"));
permission.setModify_urn(rs.getBoolean("MODIFY_URN"));
permission.setCreate_url(rs.getBoolean("CREATE_URL"));
permission.setDelete_url(rs.getBoolean("DELETE_URL"));
permission.setModify_url(rs.getBoolean("MODIFY_URL"));
permission.setCreate_person(rs.getBoolean("CREATE_PERSON"));
permission.setModify_person(rs.getBoolean("MODIFY_PERSON"));
permission.setDelete_person(rs.getBoolean("DELETE_PERSON"));
permission.setCreate_institution(rs.getBoolean("CREATE_INSTITUTION"));
permission.setModify_institution(rs.getBoolean("MODIFY_INSTITUTION"));
// permission.setCreated(rs.getDate("CREATED"));
permission.setCreated(rs.getTimestamp("CREATED"));
// permission.setLast_modified(rs.getDate("LAST_MODIFIED"));
permission.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
permission.setComment(rs.getString("COMMENT"));
return permission;
}
/**
* @param rs Result set of a previous SQL query.
* @return Java representation of NS2PERMISSION table. See the RDBMS model.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableNS2PERMISSION resultSetToNS2PERMISSION(final ResultSet rs) throws SQLException {
if (rs == null) throw new SQLException("resultSetToNS2PERMISSION: Result Set can not be null!");
final TableNS2PERMISSION ns2p = new TableNS2PERMISSION();
ns2p.setNs_id(rs.getInt("NS_ID"));
ns2p.setPerson_id(rs.getInt("PERSON_ID"));
ns2p.setPermission_id(rs.getInt("PERMISSION_ID"));
// ns2p.setCreated(rs.getDate("CREATED"));
ns2p.setCreated(rs.getTimestamp("CREATED"));
// ns2p.setLast_modified(rs.getDate("LAST_MODIFIED"));
ns2p.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
return ns2p;
}
/* ****** NAMESPACE **********************************/
/**
* Returns the namespace with given unique id.
*
* @param dbConnection RDBMS Connection to use.
* @param id Unique id of the namespace.
* @return URN:NBN namespace with given id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableNAMESPACE getNamespace(final Connection dbConnection, final int id) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespace() - start");}
final String query = "select * from NAMESPACE where NS_ID=" + id;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
TableNAMESPACE namespace = null;
while (rs.next()) {
// convert the database row to Java object
namespace = resultSetToNAMESPACE(rs);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespace() - end.");}
return namespace;
}
/**
* Returns the namespace with given name.
*
* @param dbConnection RDBMS Connection to use.
* @param name Name of the namespace.
* @return URN:NBN namespace with given id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableNAMESPACE getNamespace(final Connection dbConnection, final String name) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespace() - start");}
final String query = "select * from NAMESPACE where NAME='" + name + "'";// obvsg - put parameter name into quota
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
TableNAMESPACE namespace = null;
while (rs.next()) {
// convert the database row to Java object
namespace = resultSetToNAMESPACE(rs);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespace() - end.");}
return namespace;
}
/**
* Returns the list of namespace id that belong to given institution id.
*
* @param dbConnection RDBMS Connection to use.
* @param instId Unique id of the institution.
* @return List of namespace ids.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static List getNamespaceIdsForInstitution(final Connection dbConnection, final int instId) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespaceIdsForInstitution() - start");}
final List list = new ArrayList();
final String query = "select NS_ID from NS2INSTITUTION where INSTITUTION_ID=" + instId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
while (rs.next()) {
// convert the database row to Java object
list.add(Integer.valueOf(rs.getInt(1))); //obvsg change from 0 to 1
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespaceIdsForInstitution() - end.");}
return list;
}
/**
* Returns the list of all in system registered namespaces.
*
* @param dbConnection RDBMS Connection to use.
* @return List of all supported URN:NBN namespaces.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getNamespaceList(final Connection dbConnection) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespaceList() - start");}
final String query = "select * FROM NAMESPACE";
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespaceList(): Got the results. Reading the result set.");}
ArrayList namespaces = new ArrayList();
while (rs.next()) {
// convert the database row to Java object & add to Array
namespaces.add(resultSetToNAMESPACE(rs));
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNamespaceList() - end.");}
return namespaces;
}
/* ****** MIMETYPES **********************************************/
/**
* @param dbConnection The database connection to use.
* @param mimeTypeId Unique Id of MimeType.
* @return Media Type string for the given MimeType id {@link "http://www.iana.org/assignments/media-types/"}
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static String getMimeType(final Connection dbConnection, final int mimeTypeId) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getMimeType() - start");}
String mimeType = "MimeType not registered in database!";
final String query = "select MIMETYPE from MIMETYPES where MIMETYPE_ID=" + mimeTypeId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getMimeType(): Got the results. Reading the result set.");}
while (rs.next()) {
mimeType = rs.getString("MIMETYPE");
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getMimeType() - end.");}
return mimeType;
}
/**
* Returns the list of all in system registered mimetypes.
*
* @param dbConnection The database connection to use.
* @return List of all registered MimeTypes {@link "http://www.iana.org/assignments/media-types/"}
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static List getMimeTypeList(final Connection dbConnection) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getMimeTypeList() - start");}
final List mimeTypeList = new ArrayList();
final String query = "select * from MIMETYPES";
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getMimeTypeList(): Got the results. Reading the result set.");}
while (rs.next()) {
final TableMIMETYPES mt = new TableMIMETYPES();
mt.setMimetype_id(rs.getInt("MIMETYPE_ID"));
mt.setMimetype(rs.getString("MIMETYPE"));
mt.setCreated(rs.getTimestamp("CREATED"));
mimeTypeList.add(mt);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getMimeTypeList() - end.");}
return mimeTypeList;
}
/* ****** INSTITUTION **************************************/
/**
* Search for a given Institution in RDBMS.
*
* @param dbConnection The database connection to use.
* @param institutionId Unique Id of an Institution.
* @param extended If you set this true you get more (but not so important) information from the database.
* @return Java representation of institution which is responsible for given namespace id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableINSTITUTION getInstitution(final Connection dbConnection, final int institutionId,
final boolean extended) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitution() - start");}
final TableINSTITUTION institution = new TableINSTITUTION();
String query = "select NAME, STREET, OFFICE_BOX, ZIP, CITY";
if (extended) query = query + ", COUNTRY, SIGN, CHECK_CHECKSUM, CHECKSUM_ALGORITHM, CREATED, LAST_MODIFIED, COMMENT";
query = query + " from INSTITUTION where INSTITUTION_ID=" + institutionId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitution(): Got the results. Reading the result set.");}
while (rs.next()) {
institution.setInstitution_id(institutionId);
institution.setName(rs.getString("NAME"));
institution.setStreet(rs.getString("STREET"));
institution.setOffice_box(rs.getString("OFFICE_BOX"));
institution.setZip(rs.getString("ZIP"));
institution.setCity(rs.getString("CITY"));
if (extended) {
institution.setCountry(rs.getString("COUNTRY"));
institution.setSign(rs.getString("SIGN"));
institution.setCheck_checksum(rs.getBoolean("CHECK_CHECKSUM"));
institution.setChecksum_algorithm(rs.getString("CHECKSUM_ALGORITHM"));
// institution.setCreated(rs.getDate("CREATED"));
institution.setCreated(rs.getTimestamp("CREATED"));
// institution.setLast_modified(rs.getDate("LAST_MODIFIED"));
institution.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
institution.setComment(rs.getString("COMMENT"));
}
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitution() - end.");}
return institution;
}
/**
* Get a list of all registered Institutions.
*
* @param dbConnection The database connection to use.
* @return Java representation of all registered institutions.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getInstitutionList(final Connection dbConnection) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitutionList() - start");}
final String query = "select * FROM INSTITUTION";
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitutionList(): Got the results. Reading the result set.");}
ArrayList institutions = new ArrayList();
while (rs.next()) {
final TableINSTITUTION institution = new TableINSTITUTION();
institution.setInstitution_id(rs.getInt("INSTITUTION_ID"));
institution.setName(rs.getString("NAME"));
institution.setStreet(rs.getString("STREET"));
institution.setOffice_box(rs.getString("OFFICE_BOX"));
institution.setZip(rs.getString("ZIP"));
institution.setCity(rs.getString("CITY"));
institution.setCountry(rs.getString("COUNTRY"));
institution.setSign(rs.getString("SIGN"));
institution.setCheck_checksum(rs.getBoolean("CHECK_CHECKSUM"));
institution.setChecksum_algorithm(rs.getString("CHECKSUM_ALGORITHM"));
// institution.setCreated(rs.getDate("CREATED"));
institution.setCreated(rs.getTimestamp("CREATED"));
// institution.setLast_modified(rs.getDate("LAST_MODIFIED"));
institution.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
institution.setComment(rs.getString("COMMENT"));
institutions.add(institution);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitutionList() - end.");}
return institutions;
}
/* **** PERSON ****************************************************** */
/**
* Returns the Person object for this given id.
*
* @param dbConnection The database connection to use.
* @param persId Unique database id of person.
* @return The corresponding person for given id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TablePERSON getPersonById(final Connection dbConnection, final int persId) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPersonById() - start");}
TablePERSON person = new TablePERSON();
final String query = "select * from PERSON where PERSON_ID=" + persId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
person = resultSetToPERSON(rs);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPersonById() - end.");}
return person;
}
/**
* Returns the Person object for this given login name.
*
* @param dbConnection The database connection to use.
* @param login Unique login of a person.
* @return The corresponding person for given login string.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TablePERSON getPersonByLogin(final Connection dbConnection, final String login) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPersonByLogin() - start");}
TablePERSON person = new TablePERSON();
final String query = "select * from PERSON where LOGIN='" + login + "'";
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
person = resultSetToPERSON(rs);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPersonByLogin() - end.");}
return person;
}
/**
* Returns list of all in system registered people.
*
* @param dbConnection The database connection to use.
* @return List of all registered people.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getPersonList(final Connection dbConnection) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPersonList() - start");}
final ArrayList people = new ArrayList();
final String query = "select * from PERSON";
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
while (rs.next()) {
people.add(resultSetToPERSON(rs));
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPersonList() - end.");}
return people;
}
/* ***** PERMISSION ****************************************** */
/**
* Returns the Permission object for this given id.
*
* @param dbConnection The database connection to use.
* @param permId Unique database id of permission.
* @return The corresponding permission for given id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TablePERMISSION getPermissionById(final Connection dbConnection, final int permId) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPermissionById() - start");}
TablePERMISSION permission = new TablePERMISSION();
final String query = "select * from PERMISSION where PERMISSION_ID=" + permId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
permission = resultSetToPERMISSION(rs);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPermissionById() - end.");}
return permission;
}
/**
* Returns list of all in system registered permissions.
*
* @param dbConnection The database connection to use.
* @return List of all registered permissions.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getPermissionList(final Connection dbConnection) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPermissionList() - start");}
final ArrayList permissions = new ArrayList();
final String query = "select * from PERMISSION";
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
while (rs.next()) {
permissions.add(resultSetToPERMISSION(rs));
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getPermissionList() - end.");}
return permissions;
}
/* ***** NS2INSTITUTION ******************************************/
/**
* Returns the unique database id of the responsible institution for this given namespace id.
*
* @param dbConnection The database connection to use.
* @param namespaceId Unique database id of namespace.
* @return The corresponding institution id for given namespace id. 0 if not found.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int getInstitutionIdForNamespace(final Connection dbConnection, final int namespaceId) throws SQLException {
//TODO: relation is NOT 1:1! This must deliver a List or Array!
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitutionIdForNamespace() - start");}
int institutionId = 0;
final String query = "select INSTITUTION_ID from NS2INSTITUTION where NS_ID=" + namespaceId;
// get the result
final ResultSet rs = executeQuery(dbConnection, query, 1);
LOGGER.debug("getInstitutionIdForNamespace(): Got the results. Reading the result set.");
while (rs.next()) {
institutionId = rs.getInt("INSTITUTION_ID");
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getInstitutionIdForNamespace() - end.");}
return institutionId;
}
/**
* Get the relationsship information between Institutions and Namespaces.
*
* @param dbConnection RDBMS Connection to use.
* @param nsId Unique identifier of the namespace. 0 for all records.
* @return List of all NS2PERMISSION objects for a given Person Id. All records if Id=0.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getNs2InstitutionListForNamespace(final Connection dbConnection, final int nsId)
throws SQLException {
final ArrayList list = new ArrayList();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNs2InstitutionListForNamespace() - start");}
String query = "select * from NS2INSTITUTION";
// records of a specific person or all records required
if (nsId > 0) query = query + " where NS_ID=" + nsId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
while (rs.next()) {
final TableNS2INSTITUTION row = new TableNS2INSTITUTION();
row.setNs_id(rs.getInt("NS_ID"));
row.setInstitution_id(rs.getInt("INSTITUTION_ID"));
row.setCreated(rs.getTimestamp("CREATED"));
row.setLast_modified(rs.getTimestamp("LAST_MODIFIED"));
list.add(row);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNs2InstitutionListForNamespace() - end");}
return list;
}
/* ***** NS2PERMISSION ************************************************/
/**
* @param dbConnection RDBMS Connection to use.
* @param persId Unique identifier of the person. 0 for all records.
* @return List of all NS2PERMISSION objects for a given Person Id. All records if Id=0.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getNs2PermissionsByPersId(final Connection dbConnection, final int persId)
throws SQLException {
final ArrayList list = new ArrayList();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNs2PermissionsByPersId() - start");}
String query = "select * from NS2PERMISSION";
// records of a specific person or all records required
if (persId > 0) query = query + " where PERSON_ID=" + persId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
TableNS2PERMISSION row = null;
while (rs.next()) {
row = resultSetToNS2PERMISSION(rs);
list.add(row);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNs2PermissionsByPersId() - end");}
return list;
}
/* ***** URN ****************************************/
/**
* @param dbConnection The database connection to use.
* @param urn URN to search for.
* @param extended If you set this true you get more (but not so important) information from the database.
* @return Java representation of the Database Table URN or NULL if there is not such URN in database.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableURN getURNInfo(final Connection dbConnection, final String urn, final boolean extended) throws SQLException {
String query = "select URN.URN, URN.URN_ID, URN.NS_ID, URN.PARENT, URN.NEWER_VERSION," + "URN.OLDER_VERSION, URN.ACTIVE, URN.CREATED, URN.LAST_MODIFIED";
if (extended) query = query + ", URN.IDN, URN.IS_RETROSPECTIVE, URN.COMMENT";
query = query + " from URN where URN.URN='" + urn + "'";
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetURNInfo() - Started. Sending QUERY:\n" + query);}
final ResultSet rs = executeQuery(dbConnection, query, 1);
TableURN urnInfo = null;
if (rs.next()) {
urnInfo = resultSetToURN(rs, extended);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetURNInfo() - end.");}
return urnInfo;
}
/**
* @param dbConnection The database connection to use.
* @param urnId Unique Database Id of URN to search for.
* @return Java representation of the Database Table URN or NULL if there is not such URN in database.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableURN getURNInfo(final Connection dbConnection, final long urnId) throws SQLException {
final String query = "select * from URN where URN.URN_ID=" + urnId;
final ResultSet rs = executeQuery(dbConnection, query, 1);
TableURN urnInfo = null;
if (rs.next()) {
urnInfo = resultSetToURN(rs, true);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetURNInfo() - end.");}
return urnInfo;
}
/**
* @param dbConnection The database connection to use.
* @param urn_id Unique id to search for.
* @return URN with the given URN_ID as String. See the database documentation.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static String getURNasString(final Connection dbConnection, final long urn_id) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNasString() - start");}
String ret = "";
final String query = "select URN.URN from URN where URN.URN_ID=" + urn_id;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 0);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNasString: Got the results. Reading the result set.");}
while (rs.next()) {
ret = rs.getString(1); // you know that is unique!
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNasString - end");}
return ret;
}
/**
* @param dbConnection The database connection to use.
* @param nsId Unique id of the Namespace to search for.
* @param urnType 0: only inactive URNs, 1: only active URNs, 2: only reserved URNs, 3: all URNs.
* @return URN count for given Namespace. See the database documentation.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int getNumberOfURNs(final Connection dbConnection, final int nsId, final int urnType) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURNs() - start");}
int count = 0;
String query = "select count(URN.URN_ID) from URN where URN.NS_ID=" + nsId;
if (urnType == URN_ACTIVE) {
query = query + " AND URN.ACTIVE=1";
} else if (urnType == URN_RESERVED) {
query = query + " AND URN.ACTIVE=2";
} else if (urnType == URN_INACTIVE) {
query = query + " AND URN.ACTIVE=0";
}
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
count = rs.getInt(1);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURNs - end");}
return count;
}
/**
* @param dbConnection The database connection to use.
* @param urnSubStr A substring to count all URNs starting with this one.
* @param urnType 0: only inactive URNs, 1: only active URNs, 2: only reserved URNs, 3: all URNs
* @return URN count for given Namespace. See the database documentation.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int getNumberOfURNs(final Connection dbConnection, final String urnSubStr, final int urnType) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURNs() - start");}
int count = 0;
String query = "SELECT COUNT(URN.URN_ID) FROM URN WHERE URN.URN LIKE '" + urnSubStr + "%'";
if (urnType == URN_ACTIVE) {
query = query + " AND URN.ACTIVE=1";
} else if (urnType == URN_RESERVED) {
query = query + " AND URN.ACTIVE=2";
} else if (urnType == URN_INACTIVE) {
query = query + " AND URN.ACTIVE=0";
}
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
count = rs.getInt(1);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURNs - end");}
return count;
}
/**
* @param dbConnection The database connection to use.
* @param institutionId Unique identifier of the institution.
* @param urnType 0: only inactive URNs, 1: only active URNs, 2: only reserved URNs, 3: all URNs
* @return URN count.
* @throws SQLException Something went wrong during the RDBMS operation.
* @see #getNumberOfURNsByInstId(Connection, int)
*/
public static int getNumberOfURNsByInstId(final Connection dbConnection, final int institutionId, final int urnType) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURNsByInstId() - start");}
int count = 0;
String query = "SELECT COUNT(URN.URN_ID) FROM (URN INNER JOIN NS2INSTITUTION ON NS2INSTITUTION.NS_ID=URN.NS_ID) " +
"WHERE NS2INSTITUTION.INSTITUTION_ID=" + institutionId;
if (urnType != -1) {
if (urnType == URN_ACTIVE) {
query = query + " AND URN.ACTIVE=1";
} else if (urnType == URN_RESERVED) {
query = query + " AND URN.ACTIVE=2";
} else if (urnType == URN_INACTIVE) {
query = query + " AND URN.ACTIVE=0";
}
}
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
count = rs.getInt(1);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURNsByInstId - end");}
return count;
}
/**
* @param dbConnection The database connection to use.
* @param instId Unique identifier of the institution.
* @return Namespace count.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int getNumberOfNamespacesByInstId(final Connection dbConnection, final int instId) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfNamespacesByInstId() - start");}
int count = 0;
final String query = "SELECT COUNT(NS2INSTITUTION.INSTITUTION_ID) FROM NS2INSTITUTION WHERE NS2INSTITUTION.INSTITUTION_ID=" + instId;
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
count = rs.getInt(1);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfNamespacesByInstId - end");}
return count;
}
/**
* @param dbConnection The database connection to use.
* @param institutionId Unique identifier of the institution.
* @return URN count.
* @throws SQLException Something went wrong during the RDBMS operation.
* @see #getNumberOfURNsByInstId(Connection, int, int)
*/
public static int getNumberOfURNsByInstId(final Connection dbConnection, final int institutionId) throws SQLException {
return getNumberOfURNsByInstId(dbConnection, institutionId, -1);
}
/**
* @param dbConnection The database connection to use.
* @param persId Unique identifier of the person.
* @param onlyFaulty If true only as faulty marked URLs. If false all URLs.
* @return Java representation of all the Frontpage URLs in der database that starts with the given person id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getURLsByPersId(final Connection dbConnection, final int persId, final boolean onlyFaulty)
throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsByPersId() - start");}
String query = "SELECT * FROM (NS2PERMISSION INNER JOIN NAMESPACE ON NS2PERMISSION.NS_ID=NAMESPACE.NS_ID" +
" INNER JOIN URN ON NAMESPACE.NS_ID=URN.NS_ID INNER JOIN URL ON URN.URN_ID=URL.URN_ID)" +
" WHERE NS2PERMISSION.PERSON_ID=" + persId;
if (onlyFaulty) {
query = query + " AND URL.URL_ERROR > 0";
}
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), true);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsByPersId - end");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param instId Unique identifier of the institution.
* @param onlyFaulty If true only as faulty marked URLs. If false all URLs.
* @return Java representation of all the Frontpage URLs in der database that starts with the given institution id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getURLsByInstId(final Connection dbConnection, final int instId, final boolean onlyFaulty)
throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURLsByInstId() - start");}
String query = "SELECT * FROM URL WHERE URL.INSTITUTION_ID=" + instId;
if (onlyFaulty) {
query = query + " AND URL.URL_ERROR>0";
}
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), true);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsByInstId - end");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param instId Unique identifier of the institution.
* @param onlyFaulty If true only as faulty marked URLs. If false all URLs.
* @return URL count.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int getNumberOfURLsByInstId(final Connection dbConnection, final int instId, final boolean onlyFaulty)
throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURLsByInstId() - start");}
int count = 0;
String query = "SELECT COUNT(URL.URL) FROM URL WHERE URL.INSTITUTION_ID=" + instId;
if (onlyFaulty) {
query = query + " AND URL.URL_ERROR > 0";
}
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
count = rs.getInt(1);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURLsByInstId - end");}
return count;
}
/**
* @param dbConnection The database connection to use.
* @param urnSubStr A substring to get all URNs starting with this one.
* @param urnType 0: only inactive URNs, 1: only active URNs, 2: only reserved URNs, 3: all URNs
* @return URNs starting with given substring. See the database documentation.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getURNsStartingWith(final Connection dbConnection, final String urnSubStr, final int urnType)
throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNsStartingWith - start");}
final ArrayList urns = new ArrayList();
String query = "SELECT * FROM URN WHERE URN.URN LIKE '" + urnSubStr + "%'";
if (urnType == URN_ACTIVE) {
query = query + " AND URN.ACTIVE=1";
} else if (urnType == URN_RESERVED) {
query = query + " AND URN.ACTIVE=2";
} else if (urnType == URN_INACTIVE) {
query = query + " AND URN.ACTIVE=0";
}
// get the results
final ResultSet rs = executeQuery(dbConnection, query, GUI_LIMIT);
TableURN urnInfo = null;
while (rs.next()) {
urnInfo = resultSetToURN(rs, true);
urns.add(urnInfo);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNsStartingWith - end");}
return urns;
}
/**
* @param dbConnection The database connection to use.
* @param nsId Unique id of the Namespace to search for.
* @param urnType 0: only inactive URNs, 1: only active URNs, 2: only reserved URNs, 3: all URNs
* @return URN count for given Namespace. See the database documentation.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getURNsForNamespace(final Connection dbConnection, final int nsId, final int urnType)
throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNsForNamespace - start");}
final ArrayList urns = new ArrayList();
String query = "SELECT * FROM URN WHERE URN.NS_ID=" + nsId;
if (urnType == URN_ACTIVE) {
query = query + " and URN.ACTIVE=1";
} else if (urnType == URN_RESERVED) {
query = query + " and URN.ACTIVE=2";
} else if (urnType == URN_INACTIVE) {
query = query + " and URN.ACTIVE=0";
}
// get the results
final ResultSet rs = executeQuery(dbConnection, query, LIMIT_URN);
TableURN urnInfo = null;
while (rs.next()) {
urnInfo = resultSetToURN(rs, true);
urns.add(urnInfo);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNsForNamespace - end");}
return urns;
}
/* **** URL ********************************** */
/**
* @param dbConnection RDBMS Connection to use.
* @param urls List of URLs to enrich.
* @param urn_id Unique Id of URN.
* @param institution
* @param mimetype
* @return Enriched list of URLs for given URL list.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList enrichURLinfo(final Connection dbConnection, final ArrayList urls, final long urn_id,
final boolean institution, final boolean mimetype) throws SQLException {
for (TableURL urlInfo : urls) {
urlInfo.setUrn_id(urn_id);
if (institution) {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetURLs(): Getting the institution data");}
// TODO: set extended to false in production system
urlInfo.setInstitution(getInstitution(dbConnection, urlInfo.getInstitution_id(), true));
}
if (mimetype) urlInfo.setMimeType(getMimeType(dbConnection, urlInfo.getMimetype_id()));
}
// return the enriched URL information
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urn_id Unique id of the URN to search for.
* @param extended If you set this true you get more (but not so important) information from the database.
* @param institution If you set this true an TableINSTITUTION Object will also built.
* @return Java representation of all active URLs in der database that have this URN id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getActiveURLs(final Connection dbConnection, final long urn_id, final boolean extended,
final boolean institution) throws SQLException {
LOGGER.debug("GetActiveURLs() - start");
String query = "select URL.URL, URL.URN_ID, URL.MIMETYPE_ID, URL.IS_PRIMARY, URL.N2C, URL.ORIGIN,"
+ " URL.INSTITUTION_ID, URL.STATUS, URL.CHECKSUM, URL.FILESIZE, URL.URL_ERROR,"
+ " URL.LAST_URL_CHECK, URL.CREATED, URL.LAST_MODIFIED";
if (extended) query = query + ", URL.BLOCKING_TIME, URL.CHECKSUM_ERROR, URL.CHECKSUM_CHECK, " +
" URL.URL_CHECK, URL.LAST_CHECKSUM_CHECK, URL.COMMENT";
// only the status=3 -> active! (2=temporary, 1=permanent, 0=blocked)
query = query + " from URL where URL.URN_ID=" + urn_id + " and URL.STATUS=3" + " order by URL.IS_PRIMARY desc, URL.N2C desc, URL.ORIGIN asc";
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), extended);
// if (urls.size() > 0) enrichURLinfo(dbConnection, urls, urn_id, institution);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetActiveURLs() - end.");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urn_id Unique id of the URN to search for.
* @param extended If you set this true you get more (but not so important) information from the database.
* @param institution If you set this true an TableINSTITUTION Object will also built.
* @return Java representation of all the URLs in der database that have this URN id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getPrimary(final Connection dbConnection, final long urn_id, final boolean extended,
final boolean institution) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetPrimary() - start");}
String query = "select URL.URL, URL.URN_ID, URL.MIMETYPE_ID, URL.IS_PRIMARY, URL.N2C, URL.ORIGIN," +
" URL.INSTITUTION_ID, URL.STATUS, URL.CHECKSUM, URL.FILESIZE, URL.URL_ERROR," +
" URL.LAST_URL_CHECK, URL.CREATED, URL.LAST_MODIFIED";
if (extended) query = query + ", URL.BLOCKING_TIME, URL.CHECKSUM_ERROR, URL.CHECKSUM_CHECK, " +
"URL.URL_CHECK, URL.LAST_CHECKSUM_CHECK, URL.COMMENT";
// only the status=3 -> active! (2=temporary, 1=permanent, 0=blocked) +
// URL.IS_PRIMARY=1 means true
query = query + " from URL where URL.URN_ID=" + urn_id + " and URL.IS_PRIMARY=1 and URL.STATUS=3";
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), extended);
// if (urls.size() > 0) enrichURLinfo(dbConnection, urls, urn_id, institution);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetPrimary() - end.");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urn_id Unique id of the URN to search for.
* @param extended If you set this true you get more (but not so important) information from the database.
* @param institution If you set this true an TableINSTITUTION Object will also built.
* @return Java representation of all the Frontpage URLs in der database that have this URN id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getFrontpage(final Connection dbConnection, final long urn_id, final boolean extended,
final boolean institution) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetFrontpage() - start");}
String query = "select URL.URL, URL.URN_ID, URL.MIMETYPE_ID, URL.IS_PRIMARY, URL.N2C, URL.ORIGIN," +
" URL.INSTITUTION_ID, URL.STATUS, URL.CHECKSUM, URL.FILESIZE, URL.URL_ERROR," +
" URL.LAST_URL_CHECK, URL.CREATED, URL.LAST_MODIFIED";
if (extended) query = query + ", URL.BLOCKING_TIME, URL.CHECKSUM_ERROR, URL.CHECKSUM_CHECK, " +
"URL.URL_CHECK, URL.LAST_CHECKSUM_CHECK, URL.COMMENT";
// only the status=3 -> active! (2=temporary, 1=permanent, 0=blocked)
query = query + " from URL where URL.URN_ID=" + urn_id + " and URL.N2C=1 and URL.STATUS=3" +
" order by URL.IS_PRIMARY desc, URL.ORIGIN asc";
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), extended);
// if (urls.size() > 0) enrichURLinfo(dbConnection, urls, urn_id, institution);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("GetFrontpage() - end.");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urn_id Unique id of the URN to search for.
* @param extended If you set this true you get more (but not so important) information from the database.
* @param institution If you set this true an TableINSTITUTION Object will also built.
* @return Java representation of all the URLs in der database that have this URN id and has Archive as origin.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getArchive(final Connection dbConnection, final long urn_id, final boolean extended,
final boolean institution) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getArchive() - start");}
String query = "select URL.URL, URL.URN_ID, URL.MIMETYPE_ID, URL.IS_PRIMARY, URL.N2C, URL.ORIGIN," + " URL.INSTITUTION_ID, URL.STATUS, URL.CHECKSUM, URL.FILESIZE, URL.URL_ERROR,"
+ " URL.LAST_URL_CHECK, URL.CREATED, URL.LAST_MODIFIED";
if (extended) query = query + ", URL.BLOCKING_TIME, URL.CHECKSUM_ERROR, URL.CHECKSUM_CHECK, " + " URL.URL_CHECK, URL.LAST_CHECKSUM_CHECK, URL.COMMENT";
// only the status=3 -> active! (2=temporary, 1=permanent, 0=blocked), ORIGIN=1 means archive
query = query + " from URL where URL.URN_ID=" + urn_id + " and URL.ORIGIN=1 and URL.STATUS=3" + " order by URL.IS_PRIMARY desc, URL.N2C desc";
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), extended);
// if (urls.size() > 0) enrichURLinfo(dbConnection, urls, urn_id, institution);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getArchive() - end.");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urn_id Unique id of the URN to search for.
* @return All the URLs in der database that have this URN id as a Array of Strings.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getURLsAsString(final Connection dbConnection, final long urn_id) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsAsString() - start");}
final String query = "select URL.URL from URL where URL.URN_ID=" + urn_id + " and URL.STATUS=3" +
// first the ones with the smallest error counts (ideally 0)!
" order by URL.URL_ERROR asc, URL.IS_PRIMARY desc, URL.N2C desc, URL.ORIGIN asc";
final ResultSet rs = executeQuery(dbConnection, query, GUI_LIMIT);
final ArrayList urls = new ArrayList();
while (rs.next()) {
final String url = rs.getString("URL");
urls.add(url);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsAsString() - end.");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urlStr URL as string.
* @return Java representation of URL in der database that have this URL as string.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static TableURL getURL(final Connection dbConnection, final String urlStr) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURL() - start");}
final String query = "select * from URL where URL.URL='" + urlStr.replaceAll("'", "''") + "'";
final ResultSet rs = executeQuery(dbConnection, query, 1);
TableURL url = null;
while (rs.next()) {
url = resultSetToURL(rs, true);
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURL() - end.");}
return url;
}
/**
* @param dbConnection The database connection to use.
* @param urlSubStr Substring to search in URL.
* @return Java representation of all the Frontpage URLs in der database that starts with the given substring.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getURLsStartingWith(final Connection dbConnection, final String urlSubStr)
throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsStartingWith() - start");}
final String query = "select * from URL where URL.URL like '" + urlSubStr.replaceAll("'", "''") + "%'";
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), true);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsStartingWith() - end.");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urn_id Unique id of the URN to search for.
* @param onlyFaulty If true only as faulty marked URLs. If false all URLs.
* @return Java representation of all URLs in der database that have this URN id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList getURLsByURNid(Connection dbConnection, long urn_id, boolean onlyFaulty) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsByURNid() - start");}
String query = "select * from URL where URL.URN_ID=" + urn_id;
// filter only as non-functioning marked ones if user wishes
if (onlyFaulty) query = query + " and URL.URL_ERROR>0";
// order the list according to priority
query = query + " order by URL.IS_PRIMARY desc, URL.N2C desc, URL.ORIGIN asc";
final ArrayList urls = resultSetToURLarray(executeQuery(dbConnection, query, GUI_LIMIT), true);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURLsByURNid()() - end.");}
return urls;
}
/**
* @param dbConnection The database connection to use.
* @param urlSubStr Substring to search in URL.
* @return URL count.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int getNumberOfURLsByUrl(final Connection dbConnection, final String urlSubStr) throws SQLException {
int count = -1;
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURLsByUrl() - start");}
final String query = "select count(URL.URL) from URL where URL.URL like '" + urlSubStr.replaceAll("'", "''") + "%'";
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
count = rs.getInt(1); // you know that is unique!
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURLsByUrl() - end.");}
return count;
}
/**
* @param dbConnection The database connection to use.
* @param urnId Unique identifier of this URN.
* @param onlyFaulty If true count only as faulty marked URLs. If false all URLs.
* @return The number of all URLs or just faulty marked ones for a given URN id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static int getNumberOfURLsByUrnId(final Connection dbConnection, final long urnId, final boolean onlyFaulty)
throws SQLException {
int count = -1;
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURLsByUrnId() - start");}
String query = "select count(URL.URL) from URL where URL.URN_ID=" + urnId;
if (onlyFaulty) query = query + " and URL.URL_ERROR>0";
final ResultSet rs = executeQuery(dbConnection, query, 1);
while (rs.next()) {
count = rs.getInt(1); // you know that is unique!
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNumberOfURLsByUrnId() - end.");}
return count;
}
/**
* @param dbConnection The database connection to use.
* @param url URL to search for.
* @return The unique URN_ID for the given String. -1 if URL not registered. See the database documentation.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static long getURNidForURL(final Connection dbConnection, final String url) throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNidForURL() - start");}
long id = -1;
final String query = "select URL.URN_ID from URL where URL.URL='" + url.replaceAll("'", "''") + "'";
// get the results
final ResultSet rs = executeQuery(dbConnection, query, 1);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNidForURL: Got the results. Reading the result set.");}
while (rs.next()) {
id = rs.getLong(1); // you know that is unique!
}
rs.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getURNidForURL - end");}
return id;
}
/**
* @param dbConnection The database connection to use.
* @param persId Unique identifier of the person.
* @return List of namespace and permission fields for the given unique person id.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static List getNsPermissionsByPersId(final Connection dbConnection, final int persId)
throws SQLException {
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNsPermissionsByPersId() - start");}
//IMPORTANT: if you modify this query you shoud check the resultSetToNSPERMISSION() too!
String query = "select NAMESPACE.NS_ID, NAMESPACE.NAME, " +
"PERMISSION.PERMISSION_ID, PERMISSION.CREATE_URN, PERMISSION.MODIFY_URN, PERMISSION.CREATE_URL, PERMISSION.DELETE_URL, PERMISSION.MODIFY_URL, " +
"PERMISSION.CREATE_PERSON, PERMISSION.DELETE_PERSON, PERMISSION.MODIFY_PERSON, " +
"PERMISSION.CREATE_INSTITUTION, PERMISSION.MODIFY_INSTITUTION, PERMISSION.COMMENT, PERMISSION.CREATED, PERMISSION.LAST_MODIFIED ";
query = query + "from (NS2PERMISSION INNER JOIN NAMESPACE ON NS2PERMISSION.NS_ID=NAMESPACE.NS_ID INNER JOIN PERMISSION ON NS2PERMISSION.PERMISSION_ID=PERMISSION.PERMISSION_ID) ";
query = query + " WHERE NS2PERMISSION.PERSON_ID=" + persId;
final Statement statement = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNsPermissionsByPersId(): Executing the SQL query:\n" + query);}
final ResultSet rs = statement.executeQuery(query);
final ArrayList list = resultSetToNSPERMISSIONarray(rs);
statement.close();
if (LOGGER.isDebugEnabled()) {LOGGER.debug("getNsPermissionsByPersId() - end");}
return list;
}
/**
* @param rs Result set of a previous SQL query.
* @return Java representation of NS2PERMISSION table. See the RDBMS model.
* @throws SQLException Something went wrong during the RDBMS operation.
*/
/* this does not works with Sybase!
public static JoinNSPERMISSION resultSetToNSPERMISSION(final ResultSet rs) throws SQLException {
if (rs == null) {throw new SQLException("resultSetToNSPERMISSION: Result Set can not be null!");}
final JoinNSPERMISSION nsp = new JoinNSPERMISSION();
nsp.setNs_id(rs.getInt("NAMESPACE.NS_ID"));
nsp.setNs_name(rs.getString("NAMESPACE.NAME"));
nsp.setPermission_id(rs.getInt("PERMISSION.PERMISSION_ID"));
nsp.setPermission_create_urn(rs.getBoolean("PERMISSION.CREATE_URN"));
nsp.setPermission_modify_urn(rs.getBoolean("PERMISSION.MODIFY_URN"));
nsp.setPermission_create_url(rs.getBoolean("PERMISSION.CREATE_URL"));
nsp.setPermission_delete_url(rs.getBoolean("PERMISSION.DELETE_URL"));
nsp.setPermission_modify_url(rs.getBoolean("PERMISSION.MODIFY_URL"));
nsp.setPermission_create_person(rs.getBoolean("PERMISSION.CREATE_PERSON"));
nsp.setPermission_delete_person(rs.getBoolean("PERMISSION.DELETE_PERSON"));
nsp.setPermission_modify_person(rs.getBoolean("PERMISSION.MODIFY_PERSON"));
nsp.setPermission_create_institution(rs.getBoolean("PERMISSION.CREATE_INSTITUTION"));
nsp.setPermission_modify_institution(rs.getBoolean("PERMISSION.MODIFY_INSTITUTION"));
nsp.setPermission_comment(rs.getString("PERMISSION.COMMENT"));
// nsp.setPermission_created(rs.getDate("PERMISSION.CREATED"));
nsp.setPermission_created(rs.getTimestamp("PERMISSION.CREATED"));
// nsp.setPermission_last_modified(rs.getDate("PERMISSION.LAST_MODIFIED"));
nsp.setPermission_last_modified(rs.getTimestamp("PERMISSION.LAST_MODIFIED"));
return nsp;
} */
public static JoinNSPERMISSION resultSetToNSPERMISSION(final ResultSet rs) throws SQLException {
if (rs == null) {throw new SQLException("resultSetToNSPERMISSION: Result Set can not be null!");}
final JoinNSPERMISSION nsp = new JoinNSPERMISSION();
nsp.setNs_id(rs.getInt(1));
nsp.setNs_name(rs.getString(2));
nsp.setPermission_id(rs.getInt(3));
nsp.setPermission_create_urn(rs.getBoolean(4));
nsp.setPermission_modify_urn(rs.getBoolean(5));
nsp.setPermission_create_url(rs.getBoolean(6));
nsp.setPermission_delete_url(rs.getBoolean(7));
nsp.setPermission_modify_url(rs.getBoolean(8));
nsp.setPermission_create_person(rs.getBoolean(9));
nsp.setPermission_delete_person(rs.getBoolean(10));
nsp.setPermission_modify_person(rs.getBoolean(11));
nsp.setPermission_create_institution(rs.getBoolean(12));
nsp.setPermission_modify_institution(rs.getBoolean(13));
nsp.setPermission_comment(rs.getString(14));
nsp.setPermission_created(rs.getTimestamp(15));
nsp.setPermission_last_modified(rs.getTimestamp(16));
return nsp;
}
/**
* @param rs Result set of a previous SQL query.
* @return List of NSPERMISSION for given Database ResultSet as Java ArrayList.
* @see org.nbnResolving.database.model.JoinNSPERMISSION
* @throws SQLException Something went wrong during the RDBMS operation.
*/
public static ArrayList resultSetToNSPERMISSIONarray(final ResultSet rs) throws SQLException {
final ArrayList list = new ArrayList();
// loop over the results
while (rs.next()) {
list.add(resultSetToNSPERMISSION(rs));
}
return list;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy