Java tutorial
/** * The contents of this file are subject to the license and copyright * detailed in the LICENSE and NOTICE files at the root of the source * tree and available online at * * http://www.dspace.org/license/ */ package org.dspace.storage.rdbms; import java.io.BufferedReader; import java.io.File; import java.io.IOException; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Locale; import java.util.regex.Matcher; import java.util.regex.Pattern; import javax.sql.DataSource; import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.dspace.core.Context; import org.dspace.discovery.IndexingService; import org.dspace.discovery.SearchServiceException; import org.dspace.services.ConfigurationService; import org.dspace.services.factory.DSpaceServicesFactory; import org.dspace.workflow.factory.WorkflowServiceFactory; import org.flywaydb.core.Flyway; import org.flywaydb.core.api.FlywayException; import org.flywaydb.core.api.MigrationInfo; import org.flywaydb.core.api.callback.FlywayCallback; import org.flywaydb.core.internal.dbsupport.DbSupport; import org.flywaydb.core.internal.dbsupport.DbSupportFactory; import org.flywaydb.core.internal.dbsupport.SqlScript; import org.flywaydb.core.internal.info.MigrationInfoDumper; /** * Utility class used to manage the Database. This class is used by the * DatabaseManager to initialize/upgrade/migrate the Database. It can also * be called via the commandline as necessary to get information about * the database. * <p> * Currently, we use Flyway DB (http://flywaydb.org/) for database management. * * @see org.dspace.storage.rdbms.DatabaseUtils * @author Tim Donohue */ public class DatabaseUtils { /** log4j category */ private static final Logger log = Logger.getLogger(DatabaseUtils.class); // Our Flyway DB object (initialized by setupFlyway()) private static Flyway flywaydb; // When this temp file exists, the "checkReindexDiscovery()" method will auto-reindex Discovery // Reindex flag file is at [dspace]/solr/search/conf/reindex.flag // See also setReindexDiscovery()/getReindexDiscover() private static final String reindexDiscoveryFilePath = DSpaceServicesFactory.getInstance() .getConfigurationService().getProperty("dspace.dir") + File.separator + "solr" + File.separator + "search" + File.separator + "conf" + File.separator + "reindex.flag"; // Types of databases supported by DSpace. See getDbType() public static final String DBMS_POSTGRES = "postgres"; public static final String DBMS_ORACLE = "oracle"; public static final String DBMS_H2 = "h2"; /** * Commandline tools for managing database changes, etc. * @param argv the command line arguments given */ public static void main(String[] argv) { // Usage checks if (argv.length < 1) { System.out.println("\nDatabase action argument is missing."); System.out.println("Valid actions: 'test', 'info', 'migrate', 'repair', 'validate' or 'clean'"); System.out.println("\nOr, type 'database help' for more information.\n"); System.exit(1); } try { // Get a reference to our configured DataSource DataSource dataSource = getDataSource(); // Point Flyway API to our database Flyway flyway = setupFlyway(dataSource); // "test" = Test Database Connection if (argv[0].equalsIgnoreCase("test")) { // Try to connect to the database System.out.println("\nAttempting to connect to database"); try (Connection connection = dataSource.getConnection()) { System.out.println("Connected successfully!"); // Print basic database connection information printDBInfo(connection); // Print any database warnings/errors found (if any) boolean issueFound = printDBIssues(connection); // If issues found, exit with an error status (even if connection succeeded). if (issueFound) System.exit(1); else System.exit(0); } catch (SQLException sqle) { System.err.println("\nError running 'test': "); System.err.println(" - " + sqle); System.err.println("\nPlease see the DSpace documentation for assistance.\n"); sqle.printStackTrace(); System.exit(1); } } else if (argv[0].equalsIgnoreCase("info") || argv[0].equalsIgnoreCase("status")) { try (Connection connection = dataSource.getConnection()) { // Print basic Database info printDBInfo(connection); // Get info table from Flyway System.out.println("\n" + MigrationInfoDumper.dumpToAsciiTable(flyway.info().all())); // If Flyway is NOT yet initialized, also print the determined version information // NOTE: search is case sensitive, as flyway table name is ALWAYS lowercase, // See: http://flywaydb.org/documentation/faq.html#case-sensitive if (!tableExists(connection, flyway.getTable(), true)) { System.out.println( "\nNOTE: This database is NOT yet initialized for auto-migrations (via Flyway)."); // Determine which version of DSpace this looks like String dbVersion = determineDBVersion(connection); if (dbVersion != null) { System.out.println( "\nYour database looks to be compatible with DSpace version " + dbVersion); System.out.println("All upgrades *after* version " + dbVersion + " will be run during the next migration."); System.out.println( "\nIf you'd like to upgrade now, simply run 'dspace database migrate'."); } } // Print any database warnings/errors found (if any) boolean issueFound = printDBIssues(connection); // If issues found, exit with an error status if (issueFound) System.exit(1); else System.exit(0); } catch (SQLException e) { System.err.println("Info exception:"); e.printStackTrace(); System.exit(1); } } else if (argv[0].equalsIgnoreCase("migrate")) { try (Connection connection = dataSource.getConnection()) { System.out.println("\nDatabase URL: " + connection.getMetaData().getURL()); // "migrate" allows for an OPTIONAL second argument: // - "ignored" = Also run any previously "ignored" migrations during the migration // - [version] = ONLY run migrations up to a specific DSpace version (ONLY FOR TESTING) if (argv.length == 2) { if (argv[1].equalsIgnoreCase("ignored")) { System.out.println( "Migrating database to latest version AND running previously \"Ignored\" migrations... (Check logs for details)"); // Update the database to latest version, but set "outOfOrder=true" // This will ensure any old migrations in the "ignored" state are now run updateDatabase(dataSource, connection, null, true); } else { // Otherwise, we assume "argv[1]" is a valid migration version number // This is only for testing! Never specify for Production! String migrationVersion = argv[1]; BufferedReader input = new BufferedReader(new InputStreamReader(System.in)); System.out.println("You've specified to migrate your database ONLY to version " + migrationVersion + " ..."); System.out.println( "\nWARNING: It is highly likely you will see errors in your logs when the Metadata"); System.out.println( "or Bitstream Format Registry auto-update. This is because you are attempting to"); System.out.println("use an OLD version " + migrationVersion + " Database with a newer DSpace API. NEVER do this in a"); System.out.println( "PRODUCTION scenario. The resulting old DB is only useful for migration testing.\n"); System.out.print("Are you SURE you only want to migrate your database to version " + migrationVersion + "? [y/n]: "); String choiceString = input.readLine(); input.close(); if (choiceString.equalsIgnoreCase("y")) { System.out.println("Migrating database ONLY to version " + migrationVersion + " ... (Check logs for details)"); // Update the database, to the version specified. updateDatabase(dataSource, connection, migrationVersion, false); } else { System.out.println("No action performed."); } } } else { System.out .println("Migrating database to latest version... (Check dspace logs for details)"); updateDatabase(dataSource, connection); } System.out.println("Done."); System.exit(0); } catch (SQLException e) { System.err.println("Migration exception:"); e.printStackTrace(); System.exit(1); } } // "repair" = Run Flyway repair script else if (argv[0].equalsIgnoreCase("repair")) { try (Connection connection = dataSource.getConnection();) { System.out.println("\nDatabase URL: " + connection.getMetaData().getURL()); System.out.println( "Attempting to repair any previously failed migrations (or mismatched checksums) via FlywayDB... (Check dspace logs for details)"); flyway.repair(); System.out.println("Done."); System.exit(0); } catch (SQLException | FlywayException e) { System.err.println("Repair exception:"); e.printStackTrace(); System.exit(1); } } // "validate" = Run Flyway validation to check for database errors/issues else if (argv[0].equalsIgnoreCase("validate")) { try (Connection connection = dataSource.getConnection();) { System.out.println("\nDatabase URL: " + connection.getMetaData().getURL()); System.out.println( "Attempting to validate database status (and migration checksums) via FlywayDB..."); flyway.validate(); System.out.println( "No errors thrown. Validation succeeded. (Check dspace logs for more details)"); System.exit(0); } catch (SQLException | FlywayException e) { System.err.println("Validation exception:"); e.printStackTrace(); System.exit(1); } } // "clean" = Run Flyway clean script else if (argv[0].equalsIgnoreCase("clean")) { // If clean is disabled, return immediately if (flyway.isCleanDisabled()) { System.out.println( "\nWARNING: 'clean' command is currently disabled, as it is dangerous to run in Production scenarios!"); System.out.println( "\nIn order to run a 'clean' you first must enable it in your DSpace config by specifying 'db.cleanDisabled=false'.\n"); System.exit(1); } try (Connection connection = dataSource.getConnection()) { String dbType = getDbType(connection); // Not all Postgres user accounts will be able to run a 'clean', // as only 'superuser' accounts can remove the 'pgcrypto' extension. if (dbType.equals(DBMS_POSTGRES)) { // Check if database user has permissions suitable to run a clean if (!PostgresUtils.checkCleanPermissions(connection)) { String username = connection.getMetaData().getUserName(); // Exit immediately, providing a descriptive error message System.out.println("\nERROR: The database user '" + username + "' does not have sufficient privileges to run a 'database clean' (via Flyway)."); System.out.println( "\nIn order to run a 'clean', the database user MUST have 'superuser' privileges"); System.out.println("OR the '" + PostgresUtils.PGCRYPTO + "' extension must be installed in a separate schema (see documentation)."); System.out.println("\nOptionally, you could also manually remove the '" + PostgresUtils.PGCRYPTO + "' extension first (DROP EXTENSION " + PostgresUtils.PGCRYPTO + " CASCADE;), then rerun the 'clean'"); System.exit(1); } } BufferedReader input = new BufferedReader(new InputStreamReader(System.in)); System.out.println("\nDatabase URL: " + connection.getMetaData().getURL()); System.out.println( "\nWARNING: ALL DATA AND TABLES IN YOUR DATABASE WILL BE PERMANENTLY DELETED.\n"); System.out.println( "There is NO turning back from this action. Backup your DB before continuing."); if (dbType.equals(DBMS_ORACLE)) { System.out.println("\nORACLE WARNING: your RECYCLEBIN will also be PURGED.\n"); } else if (dbType.equals(DBMS_POSTGRES)) { System.out.println("\nPOSTGRES WARNING: the '" + PostgresUtils.PGCRYPTO + "' extension will be dropped if it is in the same schema as the DSpace database.\n"); } System.out.print("Do you want to PERMANENTLY DELETE everything from your database? [y/n]: "); String choiceString = input.readLine(); input.close(); if (choiceString.equalsIgnoreCase("y")) { System.out.println("Scrubbing database clean... (Check dspace logs for details)"); cleanDatabase(flyway, dataSource); System.out.println("Done."); System.exit(0); } else { System.out.println("No action performed."); } } catch (SQLException e) { System.err.println("Clean exception:"); e.printStackTrace(); System.exit(1); } } else { System.out.println("\nUsage: database [action]"); System.out.println("Valid actions: 'test', 'info', 'migrate', 'repair' or 'clean'"); System.out.println( " - test = Performs a test connection to database to validate connection settings"); System.out.println( " - info / status = Describe basic info/status about database, including validating the compatibility of this database"); System.out.println(" - migrate = Migrate the database to the latest version"); System.out.println( " - repair = Attempt to repair any previously failed database migrations or checksum mismatches (via Flyway repair)"); System.out.println( " - validate = Validate current database's migration status (via Flyway validate), validating all migration checksums."); System.out.println( " - clean = DESTROY all data and tables in database (WARNING there is no going back!). Requires 'db.cleanDisabled=false' setting in config."); System.out.println(""); System.exit(0); } } catch (Exception e) { System.err.println("Caught exception:"); e.printStackTrace(); System.exit(1); } } /** * Print basic information about the current database to System.out. * This is utilized by both the 'test' and 'info' commandline options. * @param connection current database connection * @throws SQLException if database error occurs */ private static void printDBInfo(Connection connection) throws SQLException { // Get basic Database info from connection DatabaseMetaData meta = connection.getMetaData(); String dbType = getDbType(connection); System.out.println("\nDatabase Type: " + dbType); System.out.println("Database URL: " + meta.getURL()); System.out.println("Database Schema: " + getSchemaName(connection)); System.out.println("Database Username: " + meta.getUserName()); System.out.println("Database Software: " + meta.getDatabaseProductName() + " version " + meta.getDatabaseProductVersion()); System.out.println("Database Driver: " + meta.getDriverName() + " version " + meta.getDriverVersion()); // For Postgres, report whether pgcrypto is installed // (If it isn't, we'll also write out warnings...see below) if (dbType.equals(DBMS_POSTGRES)) { boolean pgcryptoUpToDate = PostgresUtils.isPgcryptoUpToDate(); Double pgcryptoVersion = PostgresUtils.getPgcryptoInstalledVersion(connection); System.out.println("PostgreSQL '" + PostgresUtils.PGCRYPTO + "' extension installed/up-to-date? " + pgcryptoUpToDate + " " + ((pgcryptoVersion != null) ? "(version=" + pgcryptoVersion + ")" : "(not installed)")); } } /** * Print any warnings about current database setup to System.err (if any). * This is utilized by both the 'test' and 'info' commandline options. * @param connection current database connection * @return boolean true if database issues found, false otherwise * @throws SQLException if database error occurs */ private static boolean printDBIssues(Connection connection) throws SQLException { boolean issueFound = false; // Get the DB Type String dbType = getDbType(connection); // For PostgreSQL databases, we need to check for the 'pgcrypto' extension. // If it is NOT properly installed, we'll need to warn the user, as DSpace will be unable to proceed. if (dbType.equals(DBMS_POSTGRES)) { // Get version of pgcrypto available in this postgres instance Double pgcryptoAvailable = PostgresUtils.getPgcryptoAvailableVersion(connection); // Generic requirements message String requirementsMsg = "\n** DSpace REQUIRES PostgreSQL >= " + PostgresUtils.POSTGRES_VERSION + " AND " + PostgresUtils.PGCRYPTO + " extension >= " + PostgresUtils.PGCRYPTO_VERSION + " **\n"; // Check if installed in PostgreSQL & a supported version if (pgcryptoAvailable != null && pgcryptoAvailable.compareTo(PostgresUtils.PGCRYPTO_VERSION) >= 0) { // We now know it's available in this Postgres. Let's see if it is installed in this database. Double pgcryptoInstalled = PostgresUtils.getPgcryptoInstalledVersion(connection); // Check if installed in database, but outdated version if (pgcryptoInstalled != null && pgcryptoInstalled.compareTo(PostgresUtils.PGCRYPTO_VERSION) < 0) { System.out.println("\nWARNING: Required PostgreSQL '" + PostgresUtils.PGCRYPTO + "' extension is OUTDATED (installed version=" + pgcryptoInstalled + ", available version = " + pgcryptoAvailable + ")."); System.out.println(requirementsMsg); System.out.println( "To update it, please connect to your DSpace database as a 'superuser' and manually run the following command: "); System.out.println("\n ALTER EXTENSION " + PostgresUtils.PGCRYPTO + " UPDATE TO '" + pgcryptoAvailable + "';\n"); issueFound = true; } else if (pgcryptoInstalled == null) // If it's not installed in database { System.out.println("\nWARNING: Required PostgreSQL '" + PostgresUtils.PGCRYPTO + "' extension is NOT INSTALLED on this database."); System.out.println(requirementsMsg); System.out.println( "To install it, please connect to your DSpace database as a 'superuser' and manually run the following command: "); System.out.println("\n CREATE EXTENSION " + PostgresUtils.PGCRYPTO + ";\n"); issueFound = true; } } // Check if installed in Postgres, but an unsupported version else if (pgcryptoAvailable != null && pgcryptoAvailable.compareTo(PostgresUtils.PGCRYPTO_VERSION) < 0) { System.out.println("\nWARNING: UNSUPPORTED version of PostgreSQL '" + PostgresUtils.PGCRYPTO + "' extension found (version=" + pgcryptoAvailable + ")."); System.out.println(requirementsMsg); System.out.println("Make sure you are running a supported version of PostgreSQL, and then install " + PostgresUtils.PGCRYPTO + " version >= " + PostgresUtils.PGCRYPTO_VERSION); System.out.println("The '" + PostgresUtils.PGCRYPTO + "' extension is often provided in the 'postgresql-contrib' package for your operating system."); issueFound = true; } else if (pgcryptoAvailable == null) // If it's not installed in Postgres { System.out.println("\nWARNING: PostgreSQL '" + PostgresUtils.PGCRYPTO + "' extension is NOT AVAILABLE. Please install it into this PostgreSQL instance."); System.out.println(requirementsMsg); System.out.println("The '" + PostgresUtils.PGCRYPTO + "' extension is often provided in the 'postgresql-contrib' package for your operating system."); System.out.println( "Once the extension is installed globally, please connect to your DSpace database as a 'superuser' and manually run the following command: "); System.out.println("\n CREATE EXTENSION " + PostgresUtils.PGCRYPTO + ";\n"); issueFound = true; } } return issueFound; } /** * Setup/Initialize the Flyway API to run against our DSpace database * and point at our migration scripts. * * @param datasource * DataSource object initialized by DatabaseManager * @return initialized Flyway object */ private synchronized static Flyway setupFlyway(DataSource datasource) { ConfigurationService config = DSpaceServicesFactory.getInstance().getConfigurationService(); if (flywaydb == null) { try (Connection connection = datasource.getConnection()) { // Initialize Flyway DB API (http://flywaydb.org/), used to perform DB migrations flywaydb = new Flyway(); flywaydb.setDataSource(datasource); flywaydb.setEncoding("UTF-8"); // Default cleanDisabled to "true" (which disallows the ability to run 'database clean') flywaydb.setCleanDisabled(config.getBooleanProperty("db.cleanDisabled", true)); // Migration scripts are based on DBMS Keyword (see full path below) String dbType = getDbType(connection); connection.close(); // Determine location(s) where Flyway will load all DB migrations ArrayList<String> scriptLocations = new ArrayList<String>(); // First, add location for custom SQL migrations, if any (based on DB Type) // e.g. [dspace.dir]/etc/[dbtype]/ // (We skip this for H2 as it's only used for unit testing) if (!dbType.equals(DBMS_H2)) { scriptLocations.add("filesystem:" + config.getProperty("dspace.dir") + "/etc/" + dbType); } // Also add the Java package where Flyway will load SQL migrations from (based on DB Type) scriptLocations.add("classpath:org.dspace.storage.rdbms.sqlmigration." + dbType); // Also add the Java package where Flyway will load Java migrations from // NOTE: this also loads migrations from any sub-package scriptLocations.add("classpath:org.dspace.storage.rdbms.migration"); //Add all potential workflow migration paths List<String> workflowFlywayMigrationLocations = WorkflowServiceFactory.getInstance() .getWorkflowService().getFlywayMigrationLocations(); scriptLocations.addAll(workflowFlywayMigrationLocations); // Now tell Flyway which locations to load SQL / Java migrations from log.info("Loading Flyway DB migrations from: " + StringUtils.join(scriptLocations, ", ")); flywaydb.setLocations(scriptLocations.toArray(new String[scriptLocations.size()])); // Set flyway callbacks (i.e. classes which are called post-DB migration and similar) // In this situation, we have a Registry Updater that runs PRE-migration // NOTE: DatabaseLegacyReindexer only indexes in Legacy Lucene & RDBMS indexes. It can be removed once those are obsolete. List<FlywayCallback> flywayCallbacks = DSpaceServicesFactory.getInstance().getServiceManager() .getServicesByType(FlywayCallback.class); flywaydb.setCallbacks(flywayCallbacks.toArray(new FlywayCallback[flywayCallbacks.size()])); } catch (SQLException e) { log.error("Unable to setup Flyway against DSpace database", e); } } return flywaydb; } /** * Ensures the current database is up-to-date with regards * to the latest DSpace DB schema. If the scheme is not up-to-date, * then any necessary database migrations are performed. * <P> * FlywayDB (http://flywaydb.org/) is used to perform database migrations. * If a Flyway DB migration fails it will be rolled back to the last * successful migration, and any errors will be logged. * * @throws SQLException if database error * If database cannot be upgraded. */ public static synchronized void updateDatabase() throws SQLException { // Get our configured dataSource DataSource dataSource = getDataSource(); try (Connection connection = dataSource.getConnection()) { // Upgrade database to the latest version of our schema updateDatabase(dataSource, connection); } } /** * Ensures the current database is up-to-date with regards * to the latest DSpace DB schema. If the scheme is not up-to-date, * then any necessary database migrations are performed. * <P> * FlywayDB (http://flywaydb.org/) is used to perform database migrations. * If a Flyway DB migration fails it will be rolled back to the last * successful migration, and any errors will be logged. * * @param datasource * DataSource object (retrieved from DatabaseManager()) * @param connection * Database connection * @throws SQLException if database error * If database cannot be upgraded. */ protected static synchronized void updateDatabase(DataSource datasource, Connection connection) throws SQLException { // By default, upgrade to the *latest* version and never run migrations out-of-order updateDatabase(datasource, connection, null, false); } /** * Ensures the current database is up-to-date with regards * to the latest DSpace DB schema. If the scheme is not up-to-date, * then any necessary database migrations are performed. * <P> * FlywayDB (http://flywaydb.org/) is used to perform database migrations. * If a Flyway DB migration fails it will be rolled back to the last * successful migration, and any errors will be logged. * * @param datasource * DataSource object (retrieved from DatabaseManager()) * @param connection * Database connection * @param targetVersion * If specified, only migrate the database to a particular *version* of DSpace. This is mostly just useful for testing. * If null, the database is migrated to the latest version. * @param outOfOrder * If true, Flyway will run any lower version migrations that were previously "ignored". * If false, Flyway will only run new migrations with a higher version number. * @throws SQLException if database error * If database cannot be upgraded. */ protected static synchronized void updateDatabase(DataSource datasource, Connection connection, String targetVersion, boolean outOfOrder) throws SQLException { try { // Setup Flyway API against our database Flyway flyway = setupFlyway(datasource); // Set whethe Flyway will run migrations "out of order". By default, this is false, // and Flyway ONLY runs migrations that have a higher version number. flyway.setOutOfOrder(outOfOrder); // If a target version was specified, tell Flyway to ONLY migrate to that version // (i.e. all later migrations are left as "pending"). By default we always migrate to latest version. if (!StringUtils.isBlank(targetVersion)) { flyway.setTargetAsString(targetVersion); } // Does the necessary Flyway table ("schema_version") exist in this database? // If not, then this is the first time Flyway has run, and we need to initialize // NOTE: search is case sensitive, as flyway table name is ALWAYS lowercase, // See: http://flywaydb.org/documentation/faq.html#case-sensitive if (!tableExists(connection, flyway.getTable(), true)) { // Try to determine our DSpace database version, so we know what to tell Flyway to do String dbVersion = determineDBVersion(connection); // If this is a fresh install, dbVersion will be null if (dbVersion == null) { // Initialize the Flyway database table with defaults (version=1) flyway.baseline(); } else { // Otherwise, pass our determined DB version to Flyway to initialize database table flyway.setBaselineVersionAsString(dbVersion); flyway.setBaselineDescription("Initializing from DSpace " + dbVersion + " database schema"); flyway.baseline(); } } // Determine pending Database migrations MigrationInfo[] pending = flyway.info().pending(); // As long as there are pending migrations, log them and run migrate() if (pending != null && pending.length > 0) { log.info("Pending DSpace database schema migrations:"); for (MigrationInfo info : pending) { log.info("\t" + info.getVersion() + " " + info.getDescription() + " " + info.getType() + " " + info.getState()); } // Run all pending Flyway migrations to ensure the DSpace Database is up to date flyway.migrate(); // Flag that Discovery will need reindexing, since database was updated setReindexDiscovery(true); } else log.info("DSpace database schema is up to date"); } catch (FlywayException fe) { // If any FlywayException (Runtime) is thrown, change it to a SQLException throw new SQLException("Flyway migration error occurred", fe); } } /** * Clean the existing database, permanently removing all data and tables * <P> * FlywayDB (http://flywaydb.org/) is used to clean the database * * @param flyway * Initialized Flyway object * @param dataSource * Initialized DataSource * @throws SQLException if database error * If database cannot be cleaned. */ private static synchronized void cleanDatabase(Flyway flyway, DataSource dataSource) throws SQLException { try { // First, run Flyway's clean command on database. // For MOST database types, this takes care of everything flyway.clean(); try (Connection connection = dataSource.getConnection()) { // Get info about which database type we are using String dbType = getDbType(connection); // If this is Oracle, the only way to entirely clean the database // is to also purge the "Recyclebin". See: // http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9018.htm if (dbType.equals(DBMS_ORACLE)) { PreparedStatement statement = null; try { statement = connection.prepareStatement("PURGE RECYCLEBIN"); statement.executeQuery(); } finally { if (statement != null && !statement.isClosed()) statement.close(); } } } } catch (FlywayException fe) { // If any FlywayException (Runtime) is thrown, change it to a SQLException throw new SQLException("Flyway clean error occurred", fe); } } /** * Attempt to determine the version of our DSpace database, * so that we are able to properly migrate it to the latest schema * via Flyway * <P> * This determination is performed by checking which table(s) exist in * your database and matching them up with known tables that existed in * different versions of DSpace. * * @param connection * Current Database Connection * @throws SQLException if DB status cannot be determined * @return DSpace version as a String (e.g. "4.0"), or null if database is empty */ private static String determineDBVersion(Connection connection) throws SQLException { // First, is this a "fresh_install"? Check for an "item" table. if (!tableExists(connection, "Item")) { // Item table doesn't exist. This database must be a fresh install return null; } // We will now check prior versions in reverse chronological order, looking // for specific tables or columns that were newly created in each version. // Is this pre-DSpace 5.0 (with Metadata 4 All changes)? Look for the "resource_id" column in the "metadatavalue" table if (tableColumnExists(connection, "metadatavalue", "resource_id")) { return "5.0.2014.09.26"; // This version matches the version in the SQL migration for this feature } // Is this pre-DSpace 5.0 (with Helpdesk plugin)? Look for the "request_message" column in the "requestitem" table if (tableColumnExists(connection, "requestitem", "request_message")) { return "5.0.2014.08.08"; // This version matches the version in the SQL migration for this feature } // Is this DSpace 4.x? Look for the "Webapp" table created in that version. if (tableExists(connection, "Webapp")) { return "4.0"; } // Is this DSpace 3.x? Look for the "versionitem" table created in that version. if (tableExists(connection, "versionitem")) { return "3.0"; } // Is this DSpace 1.8.x? Look for the "bitstream_order" column in the "bundle2bitstream" table if (tableColumnExists(connection, "bundle2bitstream", "bitstream_order")) { return "1.8"; } // Is this DSpace 1.7.x? Look for the "dctyperegistry_seq" to NOT exist (it was deleted in 1.7) // NOTE: DSPACE 1.7.x only differs from 1.6 in a deleted sequence. if (!sequenceExists(connection, "dctyperegistry_seq")) { return "1.7"; } // Is this DSpace 1.6.x? Look for the "harvested_collection" table created in that version. if (tableExists(connection, "harvested_collection")) { return "1.6"; } // Is this DSpace 1.5.x? Look for the "collection_item_count" table created in that version. if (tableExists(connection, "collection_item_count")) { return "1.5"; } // Is this DSpace 1.4.x? Look for the "Group2Group" table created in that version. if (tableExists(connection, "Group2Group")) { return "1.4"; } // Is this DSpace 1.3.x? Look for the "epersongroup2workspaceitem" table created in that version. if (tableExists(connection, "epersongroup2workspaceitem")) { return "1.3"; } // Is this DSpace 1.2.x? Look for the "Community2Community" table created in that version. if (tableExists(connection, "Community2Community")) { return "1.2"; } // Is this DSpace 1.1.x? Look for the "Community" table created in that version. if (tableExists(connection, "Community")) { return "1.1"; } // IF we get here, something went wrong! This database is missing a LOT of DSpace tables throw new SQLException( "CANNOT AUTOUPGRADE DSPACE DATABASE, AS IT DOES NOT LOOK TO BE A VALID DSPACE DATABASE."); } /** * Determine if a particular database table exists in our database * * @param connection * Current Database Connection * @param tableName * The name of the table * @return true if table of that name exists, false otherwise */ public static boolean tableExists(Connection connection, String tableName) { //By default, do a case-insensitive search return tableExists(connection, tableName, false); } /** * Determine if a particular database table exists in our database * * @param connection * Current Database Connection * @param tableName * The name of the table * @param caseSensitive * When "true", the case of the tableName will not be changed. * When "false, the name may be uppercased or lowercased based on DB type. * @return true if table of that name exists, false otherwise */ public static boolean tableExists(Connection connection, String tableName, boolean caseSensitive) { boolean exists = false; ResultSet results = null; try { // Get the name of the Schema that the DSpace Database is using // (That way we can search the right schema) String schema = getSchemaName(connection); // Get information about our database. DatabaseMetaData meta = connection.getMetaData(); // If this is not a case sensitive search if (!caseSensitive) { // Canonicalize everything to the proper case based on DB type schema = canonicalize(connection, schema); tableName = canonicalize(connection, tableName); } // Search for a table of the given name in our current schema results = meta.getTables(null, schema, tableName, null); if (results != null && results.next()) { exists = true; } } catch (SQLException e) { log.error("Error attempting to determine if table " + tableName + " exists", e); } finally { try { // ensure the ResultSet gets closed if (results != null && !results.isClosed()) results.close(); } catch (SQLException e) { // ignore it } } return exists; } /** * Determine if a particular database column exists in our database * * @param connection * Current Database Connection * @param tableName * The name of the table * @param columnName * The name of the column in the table * @return true if column of that name exists, false otherwise */ public static boolean tableColumnExists(Connection connection, String tableName, String columnName) { boolean exists = false; ResultSet results = null; try { // Get the name of the Schema that the DSpace Database is using // (That way we can search the right schema) String schema = getSchemaName(connection); // Canonicalize everything to the proper case based on DB type schema = canonicalize(connection, schema); tableName = canonicalize(connection, tableName); columnName = canonicalize(connection, columnName); // Get information about our database. DatabaseMetaData meta = connection.getMetaData(); // Search for a column of that name in the specified table & schema results = meta.getColumns(null, schema, tableName, columnName); if (results != null && results.next()) { exists = true; } } catch (SQLException e) { log.error("Error attempting to determine if column " + columnName + " exists", e); } finally { try { // ensure the ResultSet gets closed if (results != null && !results.isClosed()) results.close(); } catch (SQLException e) { // ignore it } } return exists; } /* * Determine if a particular database sequence exists in our database * * @param connection * Current Database Connection * @param sequenceName * The name of the table * @return true if sequence of that name exists, false otherwise */ public static boolean sequenceExists(Connection connection, String sequenceName) { boolean exists = false; PreparedStatement statement = null; ResultSet results = null; // Whether or not to filter query based on schema (this is DB Type specific) boolean schemaFilter = false; try { // Get the name of the Schema that the DSpace Database is using // (That way we can search the right schema) String schema = getSchemaName(connection); // Canonicalize everything to the proper case based on DB type schema = canonicalize(connection, schema); sequenceName = canonicalize(connection, sequenceName); // Different database types store sequence information in different tables String dbtype = getDbType(connection); String sequenceSQL = null; switch (dbtype) { case DBMS_POSTGRES: // Default schema in PostgreSQL is "public" if (schema == null) { schema = "public"; } // PostgreSQL specific query for a sequence in a particular schema sequenceSQL = "SELECT COUNT(1) FROM pg_class, pg_namespace " + "WHERE pg_class.relnamespace=pg_namespace.oid " + "AND pg_class.relkind='S' " + "AND pg_class.relname=? " + "AND pg_namespace.nspname=?"; // We need to filter by schema in PostgreSQL schemaFilter = true; break; case DBMS_ORACLE: // Oracle specific query for a sequence owned by our current DSpace user // NOTE: No need to filter by schema for Oracle, as Schema = User sequenceSQL = "SELECT COUNT(1) FROM user_sequences WHERE sequence_name=?"; break; case DBMS_H2: // In H2, sequences are listed in the "information_schema.sequences" table // SEE: http://www.h2database.com/html/grammar.html#information_schema sequenceSQL = "SELECT COUNT(1) " + "FROM INFORMATION_SCHEMA.SEQUENCES " + "WHERE SEQUENCE_NAME = ?"; break; default: throw new SQLException("DBMS " + dbtype + " is unsupported."); } // If we have a SQL query to run for the sequence, then run it if (sequenceSQL != null) { // Run the query, passing it our parameters statement = connection.prepareStatement(sequenceSQL); statement.setString(1, sequenceName); if (schemaFilter) { statement.setString(2, schema); } results = statement.executeQuery(); // If results are non-zero, then this sequence exists! if (results != null && results.next() && results.getInt(1) > 0) { exists = true; } } } catch (SQLException e) { log.error("Error attempting to determine if sequence " + sequenceName + " exists", e); } finally { try { // Ensure statement gets closed if (statement != null && !statement.isClosed()) statement.close(); // Ensure ResultSet gets closed if (results != null && !results.isClosed()) results.close(); } catch (SQLException e) { // ignore it } } return exists; } /** * Execute a block of SQL against the current database connection. * <P> * The SQL is executed using the Flyway SQL parser. * * @param connection * Current Database Connection * @param sqlToExecute * The actual SQL to execute as a String * @throws SQLException if database error * If a database error occurs */ public static void executeSql(Connection connection, String sqlToExecute) throws SQLException { try { // Create a Flyway DbSupport object (based on our connection) // This is how Flyway determines the database *type* (e.g. Postgres vs Oracle) DbSupport dbSupport = DbSupportFactory.createDbSupport(connection, false); // Load our SQL string & execute via Flyway's SQL parser SqlScript script = new SqlScript(sqlToExecute, dbSupport); script.execute(dbSupport.getJdbcTemplate()); } catch (FlywayException fe) { // If any FlywayException (Runtime) is thrown, change it to a SQLException throw new SQLException("Flyway executeSql() error occurred", fe); } } /** * Get the Database Schema Name in use by this Connection, so that it can * be used to limit queries in other methods (e.g. tableExists()). * * @param connection * Current Database Connection * @return Schema name as a string, or "null" if cannot be determined or unspecified * @throws SQLException * An exception that provides information on a database access error or other errors. */ public static String getSchemaName(Connection connection) throws SQLException { String schema = null; // Try to get the schema from the DB connection itself. // As long as the Database driver supports JDBC4.1, there should be a getSchema() method // If this method is unimplemented or doesn't exist, it will throw an exception (likely an AbstractMethodError) try { schema = connection.getSchema(); } catch (Exception | AbstractMethodError e) { } // If we don't know our schema, let's try the schema in the DSpace configuration if (StringUtils.isBlank(schema)) { schema = canonicalize(connection, DSpaceServicesFactory.getInstance().getConfigurationService().getProperty("db.schema")); } // Still blank? Ok, we'll find a "sane" default based on the DB type if (StringUtils.isBlank(schema)) { String dbType = getDbType(connection); if (dbType.equals(DBMS_POSTGRES)) { // For PostgreSQL, the default schema is named "public" // See: http://www.postgresql.org/docs/9.0/static/ddl-schemas.html schema = "public"; } else if (dbType.equals(DBMS_ORACLE)) { // For Oracle, default schema is actually the user account // See: http://stackoverflow.com/a/13341390 DatabaseMetaData meta = connection.getMetaData(); schema = meta.getUserName(); } else // For H2 (in memory), there is no such thing as a schema schema = null; } return schema; } /** * Return the canonical name for a database identifier based on whether this * database defaults to storing identifiers in uppercase or lowercase. * * @param connection * Current Database Connection * @param dbIdentifier * Identifier to canonicalize (may be a table name, column name, etc) * @return The canonical name of the identifier. * @throws SQLException * An exception that provides information on a database access error or other errors. */ public static String canonicalize(Connection connection, String dbIdentifier) throws SQLException { // Avoid any null pointers if (dbIdentifier == null) return null; DatabaseMetaData meta = connection.getMetaData(); // Check how this database stores its identifiers, etc. // i.e. lowercase vs uppercase (by default we assume mixed case) if (meta.storesLowerCaseIdentifiers()) { return StringUtils.lowerCase(dbIdentifier); } else if (meta.storesUpperCaseIdentifiers()) { return StringUtils.upperCase(dbIdentifier); } else // Otherwise DB doesn't care about case { return dbIdentifier; } } /** * Whether or not to tell Discovery to reindex itself based on the updated * database. * <P> * Whenever a DB migration occurs this is set to "true" to ensure the * Discovery index is updated. When Discovery initializes it calls * checkReindexDiscovery() to reindex if this flag is true. * <P> * Because the DB migration may be initialized by commandline or any one of * the many DSpace webapps, setting this to "true" actually writes a temporary * file which lets Solr know when reindex is needed. * @param reindex true or false */ public static synchronized void setReindexDiscovery(boolean reindex) { File reindexFlag = new File(reindexDiscoveryFilePath); // If we need to flag Discovery to reindex, we'll create a temporary file to do so. if (reindex) { try { //If our flag file doesn't exist, create it as writeable to all if (!reindexFlag.exists()) { reindexFlag.createNewFile(); reindexFlag.setWritable(true, false); } } catch (IOException io) { log.error("Unable to create Discovery reindex flag file " + reindexFlag.getAbsolutePath() + ". You may need to reindex manually.", io); } } else // Otherwise, Discovery doesn't need to reindex. Delete the temporary file if it exists { //If our flag file exists, delete it if (reindexFlag.exists()) { boolean deleted = reindexFlag.delete(); if (!deleted) log.error("Unable to delete Discovery reindex flag file " + reindexFlag.getAbsolutePath() + ". You may need to delete it manually."); } } } /** * Whether or not reindexing is required in Discovery. * <P> * Because the DB migration may be initialized by commandline or any one of * the many DSpace webapps, this checks for the existence of a temporary * file to know when Discovery/Solr needs reindexing. * @return whether reindex flag is true/false */ public static boolean getReindexDiscovery() { // Simply check if the flag file exists File reindexFlag = new File(reindexDiscoveryFilePath); return reindexFlag.exists(); } /** * Method to check whether we need to reindex in Discovery (i.e. Solr). If * reindexing is necessary, it is performed. If not, nothing happens. * <P> * This method is called by Discovery whenever it initializes a connection * to Solr. * * @param indexer * The actual indexer to use to reindex Discovery, if needed * @see org.dspace.discovery.SolrServiceImpl */ public static synchronized void checkReindexDiscovery(IndexingService indexer) { // We only do something if the reindexDiscovery flag has been triggered if (getReindexDiscovery()) { // Kick off a custom thread to perform the reindexing in Discovery // (See ReindexerThread nested class below) ReindexerThread go = new ReindexerThread(indexer); go.start(); } } /** * Internal class to actually perform re-indexing in a separate thread. * (See checkReindexDiscovery() method)> */ private static class ReindexerThread extends Thread { private final IndexingService indexer; /** * Constructor. Pass it an existing IndexingService * @param is */ ReindexerThread(IndexingService is) { this.indexer = is; } /** * Actually perform Reindexing in Discovery/Solr. * This is synchronized so that only one thread can get in at a time. */ @Override public void run() { synchronized (this.indexer) { // Make sure reindexDiscovery flag is still true // If multiple threads get here we only want to reindex ONCE if (DatabaseUtils.getReindexDiscovery()) { Context context = null; try { context = new Context(); context.turnOffAuthorisationSystem(); log.info( "Post database migration, reindexing all content in Discovery search and browse engine"); // Reindex Discovery completely // Force clean all content this.indexer.cleanIndex(true); // Recreate the entire index (overwriting existing one) this.indexer.createIndex(context); // Rebuild spell checker (which is based on index) this.indexer.buildSpellCheck(); log.info("Reindexing is complete"); } catch (SearchServiceException sse) { log.warn( "Unable to reindex content in Discovery search and browse engine. You may need to reindex manually.", sse); } catch (SQLException | IOException e) { log.error("Error attempting to reindex all contents for search/browse", e); } finally { // Reset our indexing flag. Indexing is done or it threw an error, // Either way, we shouldn't try again. DatabaseUtils.setReindexDiscovery(false); // Clean up our context, if it still exists if (context != null && context.isValid()) context.abort(); } } } } } /** * Determine the type of Database, based on the DB connection. * * @param connection current DB Connection * @return a DB keyword/type (see DatabaseUtils.DBMS_* constants) * @throws SQLException if database error */ public static String getDbType(Connection connection) throws SQLException { DatabaseMetaData meta = connection.getMetaData(); String prodName = meta.getDatabaseProductName(); String dbms_lc = prodName.toLowerCase(Locale.ROOT); if (dbms_lc.contains("postgresql")) { return DBMS_POSTGRES; } else if (dbms_lc.contains("oracle")) { return DBMS_ORACLE; } else if (dbms_lc.contains("h2")) // Used for unit testing only { return DBMS_H2; } else { return dbms_lc; } } /** * Get a reference to the configured DataSource (which can be used to * initialize the database using Flyway). * <P> * This is NOT public, as we discourage direct connections to the database * which bypass Hibernate. Only Flyway should be allowed a direct connection. * @return DataSource */ protected static DataSource getDataSource() { // DataSource is configured via our ServiceManager (i.e. via Spring). return DSpaceServicesFactory.getInstance().getServiceManager().getServiceByName("dataSource", BasicDataSource.class); } /** * In case of a unit test the flyway db is cached to long leading to exceptions, we need to clear the object */ public static void clearFlywayDBCache() { flywaydb = null; } /** * Returns the current Flyway schema_version being used by the given database. * (i.e. the version of the highest numbered migration that this database has run) * @param connection current DB Connection * @return version as string * @throws SQLException if database error occurs */ public static String getCurrentFlywayState(Connection connection) throws SQLException { PreparedStatement statement = connection .prepareStatement("SELECT \"version\" FROM \"schema_version\" ORDER BY \"version\" desc"); ResultSet resultSet = statement.executeQuery(); resultSet.next(); return resultSet.getString("version"); } /** * Return the DSpace version that this Flyway-enabled database reports to be compatible with. * The version is retrieved from Flyway, and parsed into a Double to represent an actual * DSpace version number (e.g. 5.0, 6.0, etc) * @param connection current DB Connection * @return reported DSpace version as a Double * @throws SQLException if database error occurs */ public static Double getCurrentFlywayDSpaceState(Connection connection) throws SQLException { String flywayState = getCurrentFlywayState(connection); Matcher matcher = Pattern.compile("^([0-9]*\\.[0-9]*)(\\.)?.*").matcher(flywayState); if (matcher.matches()) { return Double.parseDouble(matcher.group(1)); } return null; } }