001/* 002 * #%L 003 * Netarchivesuite - harvester 004 * %% 005 * Copyright (C) 2005 - 2014 The Royal Danish Library, the Danish State and University Library, 006 * the National Library of France and the Austrian National Library. 007 * %% 008 * This program is free software: you can redistribute it and/or modify 009 * it under the terms of the GNU Lesser General Public License as 010 * published by the Free Software Foundation, either version 2.1 of the 011 * License, or (at your option) any later version. 012 * 013 * This program is distributed in the hope that it will be useful, 014 * but WITHOUT ANY WARRANTY; without even the implied warranty of 015 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 016 * GNU General Lesser Public License for more details. 017 * 018 * You should have received a copy of the GNU General Lesser Public 019 * License along with this program. If not, see 020 * <http://www.gnu.org/licenses/lgpl-2.1.html>. 021 * #L% 022 */ 023 024package dk.netarkivet.harvester.datamodel; 025 026import java.sql.Connection; 027import java.sql.PreparedStatement; 028import java.sql.SQLException; 029 030import org.slf4j.Logger; 031import org.slf4j.LoggerFactory; 032 033import dk.netarkivet.common.exceptions.ArgumentNotValid; 034import dk.netarkivet.common.exceptions.IOFailure; 035 036/** 037 * PostgreSQL-specific implementation of DB methods. Intended for PostgreSQL 8.3 and above. 038 * <p> 039 * PostgreSQL does not support the CLOB datatype but instead provides a "text" data type. See 040 * http://www.postgresql.org/docs/current/static/datatype-character.html. 041 */ 042public class PostgreSQLSpecifics extends DBSpecifics { 043 044 /** The log. */ 045 private static final Logger log = LoggerFactory.getLogger(PostgreSQLSpecifics.class); 046 047 /** 048 * Get an instance of the PostgreSQL specifics class. 049 * 050 * @return Instance of the PostgreSQL specifics class. 051 */ 052 public static DBSpecifics getInstance() { 053 return new PostgreSQLSpecifics(); 054 } 055 056 /** 057 * Get a temporary table for short-time use. The table should be disposed of with dropTemporaryTable. The table has 058 * two columns domain_name varchar(Constants.MAX_NAME_SIZE) config_name varchar(Constants.MAX_NAME_SIZE) 059 * 060 * @param c The DB connection to use. 061 * @return The name of the created table 062 * @throws SQLException if there is a problem getting the table. 063 */ 064 public String getJobConfigsTmpTable(Connection c) throws SQLException { 065 ArgumentNotValid.checkNotNull(c, "Connection c"); 066 PreparedStatement s = c.prepareStatement("CREATE TEMPORARY TABLE jobconfignames " + "( domain_name varchar(" 067 + Constants.MAX_NAME_SIZE + "), " + " config_name varchar(" + Constants.MAX_NAME_SIZE + ") )" 068 + " ON COMMIT DROP"); 069 s.execute(); 070 s.close(); 071 return "jobconfignames"; 072 } 073 074 /** 075 * Dispose of a temporary table created with getTemporaryTable. This can be expected to be called from within a 076 * finally clause, so it mustn't throw exceptions. 077 * 078 * @param c The DB connection to use. 079 * @param tableName The name of the temporary table 080 */ 081 public void dropJobConfigsTmpTable(Connection c, String tableName) { 082 } 083 084 /** 085 * Get the name of the JDBC driver class that handles interfacing to this server. 086 * 087 * @return The name of a JDBC driver class 088 */ 089 public String getDriverClassName() { 090 return "org.postgresql.Driver"; 091 } 092 093 @Override 094 public String getOrderByLimitAndOffsetSubClause(long limit, long offset) { 095 return "LIMIT " + limit + " OFFSET " + offset; 096 } 097 098 @Override 099 public boolean supportsClob() { 100 return false; 101 } 102 103 /** 104 * Migrates the 'jobs' table from version 3 to version 4 consisting of a change of the field forcemaxbytes from int 105 * to bigint and setting its default to -1. Furthermore the default value for field num_configs is set to 0. 106 * 107 * @throws IOFailure in case of problems in interacting with the database 108 */ 109 protected synchronized void migrateJobsv3tov4() { 110 String[] sqlStatements = {"ALTER TABLE jobs DROP COLUMN forcemaxbytes", 111 "ALTER TABLE jobs ADD COLUMN forcemaxbytes bigint not null default -1", 112 "ALTER TABLE jobs DROP COLUMN num_configs", 113 "ALTER TABLE jobs ADD COLUMN num_configs int not null default 0"}; 114 HarvestDBConnection.updateTable("jobs", 4, sqlStatements); 115 } 116 117 /** 118 * Migrates the 'jobs' table from version 4 to version 5 consisting of adding new fields 'resubmitted_as_job' and 119 * 'submittedDate'. 120 * 121 * @throws IOFailure in case of problems in interacting with the database 122 */ 123 protected synchronized void migrateJobsv4tov5() { 124 String[] sqlStatements = {"ALTER TABLE jobs ADD COLUMN submitteddate datetime AFTER enddate", 125 "ALTER TABLE jobs ADD COLUMN resubmitted_as_job bigint"}; 126 HarvestDBConnection.updateTable("jobs", 5, sqlStatements); 127 } 128 129 /** 130 * Migrates the 'configurations' table from version 3 to version 4. This consists of altering the default value of 131 * field 'maxbytes' to -1. 132 */ 133 protected synchronized void migrateConfigurationsv3ov4() { 134 // Update configurations table to version 4 135 String[] sqlStatements = {"ALTER TABLE configurations ALTER maxbytes SET DEFAULT -1"}; 136 HarvestDBConnection.updateTable("configurations", 4, sqlStatements); 137 } 138 139 /** 140 * Migrates the 'fullharvests' table from version 2 to version 3. This consists of altering the default value of 141 * field 'maxbytes' to -1 142 */ 143 protected synchronized void migrateFullharvestsv2tov3() { 144 // Update fullharvests table to version 3 145 String[] sqlStatements = {"ALTER TABLE fullharvests ALTER maxbytes SET DEFAULT -1"}; 146 HarvestDBConnection.updateTable("fullharvests", 3, sqlStatements); 147 } 148 149 @Override 150 protected void createGlobalCrawlerTrapExpressions() { 151 log.warn("Please use the provided SQL scripts to update the DB schema"); 152 HarvestDBConnection.updateTable("global_crawler_trap_expressions", 1); 153 } 154 155 @Override 156 protected void createGlobalCrawlerTrapLists() { 157 log.warn("Please use the provided SQL scripts to update the DB schema"); 158 HarvestDBConnection.updateTable("global_crawler_trap_lists", 1); 159 } 160 161 @Override 162 public void createFrontierReportMonitorTable() { 163 log.warn("Please use the provided SQL scripts to update the DB schema"); 164 HarvestDBConnection.updateTable("frontierreportmonitor", 1); 165 } 166 167 @Override 168 public void createRunningJobsHistoryTable() { 169 log.warn("Please use the provided SQL scripts to update the DB schema"); 170 HarvestDBConnection.updateTable("runningjobshistory", 1); 171 } 172 173 @Override 174 public void createRunningJobsMonitorTable() { 175 log.warn("Please use the provided SQL scripts to update the DB schema"); 176 HarvestDBConnection.updateTable("runningjobsmonitor", 1); 177 } 178 179 // Below DB changes introduced with development release 3.15 180 // with changes to tables 'runningjobshistory', 'runningjobsmonitor', 181 // 'configurations', 'fullharvests', and 'jobs'. 182 183 /** 184 * Migrates the 'runningjobshistory' table from version 1 to version 2. This consists of adding the new column 185 * 'retiredQueuesCount'. 186 */ 187 @Override 188 protected void migrateRunningJobsHistoryTableV1ToV2() { 189 String[] sqlStatements = {"ALTER TABLE runningjobshistory ADD COLUMN retiredQueuesCount bigint not null"}; 190 HarvestDBConnection.updateTable("runningjobshistory", 2, sqlStatements); 191 } 192 193 /** 194 * Migrates the 'runningjobsmonitor' table from version 1 to version 2. This consists of adding the new column 195 * 'retiredQueuesCount'. 196 */ 197 @Override 198 protected void migrateRunningJobsMonitorTableV1ToV2() { 199 String[] sqlStatements = {"ALTER TABLE runningjobsmonitor ADD COLUMN retiredQueuesCount bigint not null"}; 200 HarvestDBConnection.updateTable("runningjobsmonitor", 2, sqlStatements); 201 } 202 203 @Override 204 protected void migrateDomainsv2tov3() { 205 String[] sqlStatements = {"ALTER TABLE domains ALTER COLUMN crawlertraps type text"}; 206 HarvestDBConnection.updateTable("domains", 3, sqlStatements); 207 } 208 209 @Override 210 protected void migrateConfigurationsv4tov5() { 211 // Update configurations table to version 5 212 String[] sqlStatements = {"ALTER TABLE configurations ALTER COLUMN maxobjects TYPE bigint"}; 213 HarvestDBConnection.updateTable("configurations", 5, sqlStatements); 214 } 215 216 @Override 217 protected void migrateFullharvestsv3tov4() { 218 // Update fullharvests table to version 4 219 String[] sqlStatements = {"ALTER TABLE fullharvests ADD COLUMN maxjobrunningtime bigint NOT NULL DEFAULT 0"}; 220 HarvestDBConnection.updateTable("fullharvests", 4, sqlStatements); 221 } 222 223 @Override 224 protected void migrateJobsv5tov6() { 225 String[] sqlStatements = {"ALTER TABLE jobs ADD COLUMN forcemaxrunningtime bigint NOT NULL DEFAULT 0"}; 226 HarvestDBConnection.updateTable("jobs", 6, sqlStatements); 227 } 228 229 @Override 230 protected void migrateFullharvestsv4tov5() { 231 // Update fullharvests table to version 5 232 String[] sqlStatements = {"ALTER TABLE fullharvests ADD COLUMN isindexready bool NOT NULL DEFAULT false"}; 233 HarvestDBConnection.updateTable("fullharvests", 5, sqlStatements); 234 235 } 236 237 @Override 238 protected void createExtendedFieldTypeTable() { 239 String[] statements = new String[3]; 240 statements[0] = "CREATE TABLE extendedfieldtype " + " ( " 241 + " extendedfieldtype_id BIGINT NOT NULL PRIMARY KEY, " 242 + " name VARCHAR(50) NOT NULL " + " )"; 243 244 statements[1] = "INSERT INTO extendedfieldtype ( extendedfieldtype_id, name )" + "VALUES ( 1, 'domains')"; 245 statements[2] = "INSERT INTO extendedfieldtype ( extendedfieldtype_id, name )" 246 + " VALUES ( 2, 'harvestdefinitions')"; 247 248 HarvestDBConnection.updateTable("extendedfieldtype", 1, statements); 249 } 250 251 @Override 252 protected void createExtendedFieldTable() { 253 String createStatement = "" + "CREATE TABLE extendedfield " + " ( " 254 + " extendedfield_id BIGINT NOT NULL PRIMARY KEY, " + " extendedfieldtype_id BIGINT NOT NULL, " 255 + " name VARCHAR(50) NOT NULL, " + " format VARCHAR(50) NOT NULL, " 256 + " defaultvalue VARCHAR(50) NOT NULL, " + " options VARCHAR(50) NOT NULL, " 257 + " datatype INT NOT NULL, " + " mandatory INT NOT NULL, " 258 + " sequencenr INT " + " )"; 259 260 HarvestDBConnection.updateTable("extendedfield", 1, createStatement); 261 } 262 263 @Override 264 protected void createExtendedFieldValueTable() { 265 String createStatement = "" + "CREATE TABLE extendedfieldvalue " + " ( " 266 + " extendedfieldvalue_id BIGINT NOT NULL PRIMARY KEY, " 267 + " extendedfield_id BIGINT NOT NULL, " + " instance_id BIGINT NOT NULL, " 268 + " content VARCHAR(100) NOT NULL " + " )"; 269 270 HarvestDBConnection.updateTable("extendedfieldvalue", 1, createStatement); 271 } 272 273 @Override 274 protected synchronized void migrateJobsv6tov7() { 275 String[] sqlStatements = {"ALTER TABLE jobs ADD COLUMN continuationof BIGINT DEFAULT NULL"}; 276 HarvestDBConnection.updateTable("jobs", 7, sqlStatements); 277 } 278 279 @Override 280 protected void migrateJobsv7tov8() { 281 String[] sqlStatements = {"ALTER TABLE jobs ADD COLUMN creationdate TIMESTAMP DEFAULT NULL"}; 282 HarvestDBConnection.updateTable("jobs", 8, sqlStatements); 283 } 284 285 @Override 286 protected void migrateJobsv8tov9() { 287 String[] sqlStatements = {"ALTER TABLE jobs ADD COLUMN harvestname_prefix VARCHAR(100) DEFAULT NULL"}; 288 HarvestDBConnection.updateTable("jobs", 9, sqlStatements); 289 } 290 291 @Override 292 protected void migrateHarvestdefinitionsv2tov3() { 293 String[] sqlStatements = {"ALTER TABLE harvestdefinitions ADD COLUMN audience VARCHAR(100) DEFAULT NULL"}; 294 HarvestDBConnection.updateTable("harvestdefinitions", 3, sqlStatements); 295 } 296 297 @Override 298 protected void migrateHarvestdefinitionsv3tov4() { 299 String[] sqlStatements = {"ALTER TABLE harvestdefinitions ADD COLUMN channel_id BIGINT DEFAULT NULL"}; 300 HarvestDBConnection.updateTable("harvestdefinitions", 4, sqlStatements); 301 } 302 303 @Override 304 protected void migrateJobsv9tov10() { 305 String[] sqlStatements = {"ALTER TABLE jobs ADD COLUMN channel VARCHAR(300) DEFAULT NULL", 306 "ALTER TABLE jobs ADD COLUMN snapshot BOOL", 307 "UPDATE jobs SET channel = 'LOWPRIORITY' WHERE priority=0;", 308 "UPDATE jobs SET channel = 'HIGHPRIORITY' WHERE priority=1", 309 "UPDATE jobs SET snapshot = true WHERE priority=0", 310 "UPDATE jobs SET snapshot = false WHERE priority=1", "ALTER TABLE jobs DROP COLUMN priority"}; 311 HarvestDBConnection.updateTable("jobs", 10, sqlStatements); 312 } 313 314 @Override 315 protected void createHarvestChannelTable() { 316 String createStatement = "CREATE TABLE harvestchannel (" + "id BIGINT NOT NULL PRIMARY KEY, " 317 + "name VARCHAR(300) NOT NULL UNIQUE," + "issnapshot BOOL NOT NULL," + "isdefault BOOL NOT NULL," 318 + "comments VARCHAR(30000)" + ")"; 319 String[] sqlStatements = { 320 createStatement, 321 "CREATE SEQUENCE harvestchannel_id_seq OWNED BY harvestchannel.id", 322 "ALTER TABLE harvestchannel ALTER COLUMN id SET DEFAULT NEXTVAL('harvestchannel_id_seq')", 323 "CREATE INDEX harvestchannelnameid on harvestchannel(name) TABLESPACE tsindex", 324 "INSERT INTO harvestchannel(name, issnapshot, isdefault, comments) " 325 + " VALUES ('LOWPRIORITY', true, true, 'Channel for snapshot harvests')", 326 "INSERT INTO harvestchannel(name, issnapshot, isdefault, comments) " 327 + " VALUES ('HIGHPRIORITY', false, true, 'Channel for selective harvests')"}; 328 HarvestDBConnection.updateTable("harvestchannel", 1, sqlStatements); 329 } 330 331 /** 332 * Migrates the 'ExtendedFieldTable' from version 1 to version 2 consisting of adding the maxlen field 333 */ 334 protected void migrateExtendedFieldTableV1toV2() { 335 String[] sqlStatements = {"ALTER TABLE extendedfield ADD COLUMN maxlen INT", 336 "ALTER TABLE extendedfield ALTER COLUMN options TYPE VARCHAR(1000)"}; 337 HarvestDBConnection.updateTable("extendedfield", 2, sqlStatements); 338 } 339 340 /** 341 * Migrates the 'ExtendedFieldValueTable' from version 1 to version 2 changing the maxlen of content to 30000 342 */ 343 protected void migrateExtendedFieldTableValueV1toV2() { 344 String[] sqlStatements = {"ALTER TABLE extendedfieldvalue ALTER COLUMN content TYPE VARCHAR(30000), ALTER COLUMN content SET NOT NULL"}; 345 HarvestDBConnection.updateTable("extendedfieldvalue", 2, sqlStatements); 346 } 347 348}