Java tutorial
/* File: $Id$ * Revision: $Revision$ * Author: $Author$ * Date: $Date$ * * The Netarchive Suite - Software to harvest and preserve websites * Copyright 2004-2012 The Royal Danish Library, the Danish State and * University Library, the National Library of France and the Austrian * National Library. * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA */ package dk.netarkivet.harvester.datamodel; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import dk.netarkivet.common.exceptions.ArgumentNotValid; import dk.netarkivet.common.exceptions.IOFailure; import dk.netarkivet.common.utils.DBUtils; import dk.netarkivet.common.utils.ExceptionUtils; /** * Derby-specific implementation of DB methods. * */ public abstract class DerbySpecifics extends DBSpecifics { Log log = LogFactory.getLog(DerbySpecifics.class); /** Get a temporary table for short-time use. The table should be * disposed of with dropTemporaryTable. The table has two columns * domain_name varchar(Constants.MAX_NAME_SIZE) + config_name varchar(Constants.MAX_NAME_SIZE) * All rows in the table must be deleted at commit or rollback. * * @param c The DB connection to use. * @throws SQLException if there is a problem getting the table. * @return The name of the created table */ public String getJobConfigsTmpTable(Connection c) throws SQLException { ArgumentNotValid.checkNotNull(c, "Connection c"); PreparedStatement s = c.prepareStatement("DECLARE GLOBAL TEMPORARY TABLE " + "jobconfignames " + "( domain_name varchar(" + Constants.MAX_NAME_SIZE + "), " + " config_name varchar(" + Constants.MAX_NAME_SIZE + ") )" + " ON COMMIT DELETE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS"); s.execute(); s.close(); return "session.jobconfignames"; } /** * Dispose of a temporary table gotten with getTemporaryTable. This can be * expected to be called from within a finally clause, so it mustn't throw * exceptions. * * @param c The DB connection to use. * @param tableName The name of the temporary table */ public void dropJobConfigsTmpTable(Connection c, String tableName) { ArgumentNotValid.checkNotNull(c, "Connection c"); ArgumentNotValid.checkNotNullOrEmpty(tableName, "String tableName"); PreparedStatement s = null; try { // Now drop the temporary table s = c.prepareStatement("DROP TABLE " + tableName); s.execute(); } catch (SQLException e) { log.warn("Couldn't drop temporary table " + tableName + "\n" + ExceptionUtils.getSQLExceptionCause(e), e); } } @Override public String getOrderByLimitAndOffsetSubClause(long limit, long offset) { // LIMIT sub-clause supported by Derby 10.5.3 // see http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html return "OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY"; } @Override public boolean supportsClob() { return true; } /** Migrates the 'jobs' table from version 3 to version 4 * consisting of a change of the field forcemaxbytes from int to bigint * and setting its default to -1. * Furthermore the default value for field num_configs is set to 0. * @throws IOFailure in case of problems in interacting with the database */ protected synchronized void migrateJobsv3tov4() { // Change the forcemaxbytes from 'int' to 'bigint'. // Procedure for changing the datatype of a derby table was found here: // https://issues.apache.org/jira/browse/DERBY-1515 String[] sqlStatements = { "ALTER TABLE jobs ADD COLUMN forcemaxbytes_new bigint NOT NULL DEFAULT -1", "UPDATE jobs SET forcemaxbytes_new = forcemaxbytes", "ALTER TABLE jobs DROP COLUMN forcemaxbytes", "RENAME COLUMN jobs.forcemaxbytes_new TO forcemaxbytes", "ALTER TABLE jobs ALTER COLUMN num_configs SET DEFAULT 0" }; HarvestDBConnection.updateTable("jobs", 4, sqlStatements); } /** Migrates the 'jobs' table from version 4 to version 5 * consisting of adding new fields 'resubmitted_as_job' and 'submittedDate'. * @throws IOFailure in case of problems in interacting with the database */ protected synchronized void migrateJobsv4tov5() { // Update jobs table to version 5 String[] sqlStatements = { "ALTER TABLE jobs ADD COLUMN submitteddate timestamp", "ALTER TABLE jobs ADD COLUMN resubmitted_as_job bigint" }; HarvestDBConnection.updateTable("jobs", 5, sqlStatements); } /** Migrates the 'configurations' table from version 3 to version 4. * This consists of altering the default value of field 'maxbytes' to -1. */ protected synchronized void migrateConfigurationsv3ov4() { // Update configurations table to version 4 String[] sqlStatements = { "ALTER TABLE configurations ALTER maxbytes WITH DEFAULT -1" }; HarvestDBConnection.updateTable("configurations", 4, sqlStatements); } /** Migrates the 'fullharvests' table from version 2 to version 3. * This consists of altering the default value of field 'maxbytes' to -1. */ protected synchronized void migrateFullharvestsv2tov3() { // Update fullharvests table to version 3 String[] sqlStatements = { "ALTER TABLE fullharvests ALTER maxbytes WITH DEFAULT -1" }; HarvestDBConnection.updateTable("fullharvests", 3, sqlStatements); } @Override protected void createGlobalCrawlerTrapLists() { String createStatement = "CREATE TABLE global_crawler_trap_lists(\n" + " global_crawler_trap_list_id INT NOT NULL " + "GENERATED ALWAYS AS IDENTITY PRIMARY KEY," + " name VARCHAR(300) NOT NULL UNIQUE, " + " description VARCHAR(30000)," + " isActive INT NOT NULL) "; HarvestDBConnection.updateTable("global_crawler_trap_lists", 1, createStatement); } @Override protected void createGlobalCrawlerTrapExpressions() { String createStatement = "CREATE TABLE global_crawler_trap_expressions(" + " crawler_trap_list_id INT NOT NULL, " + "" + " trap_expression VARCHAR(1000)," + " PRIMARY KEY (crawler_trap_list_id, " + "trap_expression))"; HarvestDBConnection.updateTable("global_crawler_trap_expressions", 1, createStatement); } @Override public void createFrontierReportMonitorTable() { String createStatement = "CREATE TABLE frontierReportMonitor (" + " jobId bigint NOT NULL, " + "filterId varchar(200) NOT NULL," + "tstamp timestamp NOT NULL," + "domainName varchar(300) NOT NULL," + "currentSize bigint NOT NULL," + "totalEnqueues bigint NOT NULL," + "sessionBalance bigint NOT NULL," + "lastCost numeric NOT NULL," + "averageCost numeric NOT NULL," + "lastDequeueTime varchar(100) NOT NULL," + "wakeTime varchar(100) NOT NULL," + "totalSpend bigint NOT NULL," + "totalBudget bigint NOT NULL," + "errorCount bigint NOT NULL," + "lastPeekUri varchar(1000) NOT NULL," + "lastQueuedUri varchar(1000) NOT NULL," + "UNIQUE (jobId, filterId, domainName)" + ")"; HarvestDBConnection.updateTable("frontierreportmonitor", 1, createStatement); } @Override public void createRunningJobsHistoryTable() { String createStatement = "CREATE TABLE runningJobsHistory (" + "jobId bigint NOT NULL, " + "harvestName varchar(300) NOT NULL," + "hostUrl varchar(300) NOT NULL," + "progress numeric NOT NULL," + "queuedFilesCount bigint NOT NULL," + "totalQueuesCount bigint NOT NULL," + "activeQueuesCount bigint NOT NULL," + "exhaustedQueuesCount bigint NOT NULL," + "elapsedSeconds bigint NOT NULL," + "alertsCount bigint NOT NULL," + "downloadedFilesCount bigint NOT NULL," + "currentProcessedKBPerSec int NOT NULL," + "processedKBPerSec int NOT NULL," + "currentProcessedDocsPerSec numeric NOT NULL," + "processedDocsPerSec numeric NOT NULL," + "activeToeCount integer NOT NULL," + "status int NOT NULL," + "tstamp timestamp NOT NULL," + "PRIMARY KEY (jobId, harvestName, elapsedSeconds, tstamp)" + ")"; HarvestDBConnection.updateTable("runningjobshistory", 1, createStatement); Connection c = HarvestDBConnection.get(); try { DBUtils.executeSQL(c, "CREATE INDEX runningJobsHistoryCrawlJobId on runningJobsHistory (jobId)", "CREATE INDEX runningJobsHistoryCrawlTime on runningJobsHistory (elapsedSeconds)", "CREATE INDEX runningJobsHistoryHarvestName on runningJobsHistory (harvestName)"); } finally { HarvestDBConnection.release(c); } } @Override public void createRunningJobsMonitorTable() { String createStatement = "CREATE TABLE runningJobsMonitor (" + "jobId bigint NOT NULL, " + "harvestName varchar(300) NOT NULL," + "hostUrl varchar(300) NOT NULL," + "progress numeric NOT NULL," + "queuedFilesCount bigint NOT NULL," + "totalQueuesCount bigint NOT NULL," + "activeQueuesCount bigint NOT NULL," + "exhaustedQueuesCount bigint NOT NULL," + "elapsedSeconds bigint NOT NULL," + "alertsCount bigint NOT NULL," + "downloadedFilesCount bigint NOT NULL," + "currentProcessedKBPerSec integer NOT NULL," + "processedKBPerSec integer NOT NULL," + "currentProcessedDocsPerSec numeric NOT NULL," + "processedDocsPerSec numeric NOT NULL," + "activeToeCount integer NOT NULL," + "status integer NOT NULL," + "tstamp timestamp NOT NULL, " + "PRIMARY KEY (jobId, harvestName)" + ")"; HarvestDBConnection.updateTable("runningjobsmonitor", 1, createStatement); Connection c = HarvestDBConnection.get(); try { DBUtils.executeSQL(c, "CREATE INDEX runningJobsMonitorJobId on runningJobsMonitor (jobId)", "CREATE INDEX runningJobsMonitorHarvestName on runningJobsMonitor (harvestName)"); } finally { HarvestDBConnection.release(c); } } // Below DB changes introduced with development release 3.15 // with changes to tables 'runningjobshistory', 'runningjobsmonitor', // 'configurations', 'fullharvests', and 'jobs'. /** * Migrates the 'runningjobshistory' table from version 1 to version 2. This * consists of adding the new column 'retiredQueuesCount'. */ @Override protected void migrateRunningJobsHistoryTableV1ToV2() { String[] sqlStatements = { "ALTER TABLE runningjobshistory " + "ADD COLUMN retiredQueuesCount bigint not null DEFAULT 0" }; HarvestDBConnection.updateTable("runningjobshistory", 2, sqlStatements); } /** * Migrates the 'runningjobsmonitor' table from version 1 to version 2. This * consists of adding the new column 'retiredQueuesCount'. */ @Override protected void migrateRunningJobsMonitorTableV1ToV2() { String[] sqlStatements = { "ALTER TABLE runningjobsmonitor " + "ADD COLUMN retiredQueuesCount bigint not null DEFAULT 0" }; HarvestDBConnection.updateTable("runningjobsmonitor", 2, sqlStatements); } @Override protected void migrateConfigurationsv4tov5() { // Change the maxobjects from 'int' to 'bigint'. // Procedure for changing the datatype of a derby table was found here: // https://issues.apache.org/jira/browse/DERBY-1515 String[] sqlStatements = { "ALTER TABLE configurations ADD COLUMN maxobjects_new bigint NOT NULL DEFAULT -1", "UPDATE configurations SET maxobjects_new = maxobjects", "ALTER TABLE configurations DROP COLUMN maxobjects", "RENAME COLUMN configurations.maxobjects_new TO maxobjects" }; HarvestDBConnection.updateTable("configurations", 5, sqlStatements); } @Override protected void migrateFullharvestsv3tov4() { // Add new bigint field maxjobrunningtime with default 0 String[] sqlStatements = { "ALTER TABLE fullharvests ADD COLUMN maxjobrunningtime bigint NOT NULL DEFAULT 0" }; HarvestDBConnection.updateTable("fullharvests", 4, sqlStatements); } @Override protected void migrateJobsv5tov6() { // Add new bigint field with default 0 String[] sqlStatements = { "ALTER TABLE jobs ADD COLUMN forcemaxrunningtime bigint NOT NULL DEFAULT 0" }; HarvestDBConnection.updateTable("jobs", 6, sqlStatements); } @Override protected void migrateFullharvestsv4tov5() { // Add new bigint field isindexready (0 is not ready, 1 is ready). String[] sqlStatements = { "ALTER TABLE fullharvests ADD COLUMN isindexready int NOT NULL DEFAULT 0" }; HarvestDBConnection.updateTable("fullharvests", 5, sqlStatements); } @Override protected void createExtendedFieldTypeTable() { String[] statements = new String[3]; statements[0] = "" + "CREATE TABLE extendedfieldtype " + " ( " + " extendedfieldtype_id BIGINT NOT NULL PRIMARY KEY, " + " name VARCHAR(50) NOT NULL " + " )"; statements[1] = "INSERT INTO extendedfieldtype ( extendedfieldtype_id, name )" + " VALUES ( 1, 'domains')"; statements[2] = "INSERT INTO extendedfieldtype ( extendedfieldtype_id, name ) " + "VALUES ( 2, 'harvestdefinitions')"; HarvestDBConnection.updateTable("extendedfieldtype", 1, statements); } @Override protected void createExtendedFieldTable() { String createStatement = "" + "CREATE TABLE extendedfield " + " ( " + " extendedfield_id BIGINT NOT NULL PRIMARY KEY, " + " extendedfieldtype_id BIGINT NOT NULL, " + " name VARCHAR(50) NOT NULL, " + " format VARCHAR(50) NOT NULL, " + " defaultvalue VARCHAR(50) NOT NULL, " + " options VARCHAR(50) NOT NULL, " + " datatype INT NOT NULL, " + " mandatory INT NOT NULL, " + " sequencenr INT " + " )"; HarvestDBConnection.updateTable("extendedfield", 1, createStatement); } @Override protected void createExtendedFieldValueTable() { String createStatement = "" + "CREATE TABLE extendedfieldvalue " + " ( " + " extendedfieldvalue_id BIGINT NOT NULL PRIMARY KEY, " + " extendedfield_id BIGINT NOT NULL, " + " instance_id BIGINT NOT NULL, " + " content VARCHAR(100) NOT NULL " + " )"; HarvestDBConnection.updateTable("extendedfieldvalue", 1, createStatement); } @Override protected synchronized void migrateJobsv6tov7() { String[] sqlStatements = { "ALTER TABLE jobs ADD COLUMN continuationof BIGINT DEFAULT NULL" }; HarvestDBConnection.updateTable("jobs", 7, sqlStatements); } @Override protected void migrateDomainsv2tov3() { String[] sqlStatements = { "ALTER TABLE domains ADD COLUMN NEW_COLUMN CLOB(64M)", "UPDATE domains SET NEW_COLUMN=crawlertraps", "ALTER TABLE domains DROP COLUMN crawlertraps", "RENAME COLUMN domains.NEW_COLUMN TO crawlertraps" }; HarvestDBConnection.updateTable("domains", 3, sqlStatements); } @Override protected void migrateJobsv7tov8() { String[] sqlStatements = { "ALTER TABLE jobs ADD COLUMN creationdate TIMESTAMP DEFAULT NULL" }; HarvestDBConnection.updateTable("jobs", 8, sqlStatements); } @Override protected void migrateJobsv8tov9() { String[] sqlStatements = { "ALTER TABLE jobs ADD COLUMN harvestname_prefix VARCHAR(100) DEFAULT NULL" }; HarvestDBConnection.updateTable("jobs", 9, sqlStatements); } @Override protected void migrateHarvestdefinitionsv2tov3() { String[] sqlStatements = { "ALTER TABLE harvestdefinitions ADD COLUMN audience VARCHAR(100) DEFAULT NULL" }; HarvestDBConnection.updateTable("harvestdefinitions", 3, sqlStatements); } @Override protected void migrateHarvestdefinitionsv3tov4() { String[] sqlStatements = { "ALTER TABLE harvestdefinitions ADD COLUMN channel_id BIGINT DEFAULT NULL" }; HarvestDBConnection.updateTable("harvestdefinitions", 4, sqlStatements); } @Override protected void migrateJobsv9tov10() { String[] sqlStatements = { "ALTER TABLE jobs ADD COLUMN channel VARCHAR(300) DEFAULT NULL", "ALTER TABLE jobs ADD COLUMN snapshot BOOLEAN", "UPDATE jobs SET channel = 'snapshot' WHERE priority=0", "UPDATE jobs SET channel = 'focused' WHERE priority=1", "UPDATE jobs SET snapshot = true WHERE priority=0", "UPDATE jobs SET snapshot = false WHERE priority=1", "ALTER TABLE jobs DROP COLUMN priority" }; HarvestDBConnection.updateTable("jobs", 10, sqlStatements); } @Override public void createHarvestChannelTable() { String createStatement = "CREATE TABLE harvestchannel (" + "id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " + "name VARCHAR(300) NOT NULL UNIQUE," + "issnapshot BOOLEAN NOT NULL," + "isdefault BOOLEAN NOT NULL," + "comments VARCHAR(30000)" + ")"; String insertStatementOne = "INSERT INTO harvestchannel(name, issnapshot, isdefault, comments) " + "VALUES(\'SNAPSHOT\', true, true, \'Channel for snapshot harvests\')"; String insertStatementTwo = "INSERT INTO harvestchannel(name, issnapshot, isdefault, comments) " + "VALUES(\'FOCUSED\', false, true, \'Channel for focused harvests\')"; HarvestDBConnection.updateTable("harvestchannel", 1, new String[] { createStatement, insertStatementOne, insertStatementTwo }); } /** * Migrates the 'ExtendedFieldTable' from version 1 to version 2 consisting of adding * the maxlen field */ protected void migrateExtendedFieldTableV1toV2() { String[] sqlStatements = { "ALTER TABLE extendedfield ADD COLUMN maxlen INT", "ALTER TABLE extendedfield ALTER options SET DATA TYPE VARCHAR(1000)" }; HarvestDBConnection.updateTable("extendedfield", 2, sqlStatements); } /** * Migrates the 'ExtendedFieldValueTable' from version 1 to version 2 changing the maxlen of content to 30000 */ protected void migrateExtendedFieldTableValueV1toV2() { String[] sqlStatements = { "ALTER TABLE extendedfieldvalue ALTER content SET DATA TYPE VARCHAR(30000)", "ALTER TABLE extendedfieldvalue ALTER content NOT NULL" }; HarvestDBConnection.updateTable("extendedfieldvalue", 2, sqlStatements); } }