CheckDatabaseMigrationIT.java

/**
 * Copyright (C) 2015 Orange
 * 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.
 */
package com.francetelecom.clara.cloud.db.liquibase;

import static org.junit.Assert.assertTrue;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;

import javax.sql.DataSource;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.francetelecom.clara.cloud.commons.TechnicalException;
import com.francetelecom.clara.cloud.test.database.DbaasDatabase;

/**
 * <pre>
 * Test database migration on an existing database
 * This tests create a copy of a reference database into a test database
 * Then it executes a Spring init. of jpa and liquibase beans to verify that liquibase properly migrates the data
 * 
 * The reference database must be pre-loaded with some representative data 
 * Typically it could be a copy of the FUT database that will be migrated when the next release will be deployed.
 * 
 * To export/import fut database into the reference database you can use following commands
 * 
 * Export: pg_dump -h [dbHost] -p [dbPort] -U [dbUser] -W -n public -v -x -O -f [exportFile.sql] [dbName]
 * Import: psql -h [dbHost] -p [dbPort] -d [dbName] -U [dbUser] -W -v ON_ERROR_STOP=ON -f [exportFile.sql]
 * 
 * Note: database host, port and name must be set; password will be prompted
 * 
 * This test is restricted to postgresql. 
 * It uses pg_dump and psql posgresql commands; those commands must be available in PATH environment
 * </pre>
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration()
public class CheckDatabaseMigrationIT {

	private static Logger logger = LoggerFactory.getLogger(CheckDatabaseMigrationIT.class.getName());

	// Following properties are worked-out during set up from db url
	@Value("${test.liquibase.futcopy.host}")
	String referenceDbHost;
	@Value("${test.liquibase.futcopy.port}")
	String referenceDbPort;
	@Value("${test.liquibase.futcopy.dbname}")
	String referenceDbName;
	@Value("${test.liquibase.futcopy.user}")
	String referenceDbUser;
	@Value("${test.liquibase.futcopy.password}")
	String referenceDbPassword;
	
	// Test database
	@Autowired(required = true)
	DbaasDatabase testDatabase;

	// Datasource used to connect on the test database
	@Autowired
	public DataSource datasource;

	// Some temporary files used in test
	File exportFile;
	File deleteTablesScriptFile;
	File deleteSequencesScriptFile;

	@Before
	public void setup() throws IOException {

		// export test database properties so that they can be used during test which initializes a spring context
		System.setProperty("testDatabase.url", testDatabase.getUrl());
		System.setProperty("testDatabase.user", testDatabase.getUser());
		System.setProperty("testDatabase.password", testDatabase.getPassword());

		logger.debug("PATH=" + System.getenv("PATH"));

		// Create temporary files
		exportFile = File.createTempFile("export", ".sql");
		deleteTablesScriptFile = File.createTempFile("delete_tables", ".sql");
		deleteSequencesScriptFile = File.createTempFile("delete_sequences", ".sql");

		String exportFileName = exportFile.getPath();
		logger.info("Setup test database - START");
		exportData(referenceDbHost, referenceDbPort, referenceDbName, referenceDbUser, referenceDbPassword, exportFileName);
		
		importData(testDatabase.getHost(), testDatabase.getPort(), testDatabase.getName(), testDatabase.getUser(), testDatabase.getPassword(), exportFileName);
		logger.info("Setup test database - END");
	}

	@After
	public void cleanup() {
		// no need to purge test database as it will be dropped during spring context tear-down
		// delete temporary files
		exportFile.delete();
		deleteTablesScriptFile.delete();
		deleteSequencesScriptFile.delete();
	}

	@Test
	public void testMigration() {
		// Before running test we verify that test database contains some data (to avoid testing migration on empty database or not relevant data)
		assertTestDataIsRelevant();
		logger.info("Init spring context on test database - START");
		// We use a dedicated spring configuration which initializes liquibase and hibernate using the default production configuration
		ApplicationContext context = new ClassPathXmlApplicationContext(
				"com/francetelecom/clara/cloud/db/liquibase/CheckDatabaseMigrationIT-InitLiquibaseHibernateProduction-context.xml");
		logger.info("Init spring context on test database - END");
	}

	/**
	 * Verify that test data are relevant We simply checks that there is at least 1 environment in database
	 */
	private void assertTestDataIsRelevant() {
		logger.info("Verifying test database contains relevant data");
		JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
		int n = jdbcTemplate.queryForObject("select count(*) from environment where status <> 'REMOVED'", Integer.class);
		logger.debug("Test database contains " + n + " environments");
		assertTrue("Test data may not be relevant: it should includes at least one environemnt", n > 0);
	}

	/**
	 * Export database data into a file udsing pg_dump
	 * 
	 * @param dbHost
	 * @param dbPort
	 * @param dbName
	 * @param dbUser
	 * @param dbPassword
	 * @param exportFile
	 */
	private void exportData(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword, String exportFile) {
		execPgdump(dbHost, dbPort, dbName, dbUser, dbPassword, exportFile);
	}

	/**
	 * Purge database by dropping all tables and sequences using psql scripts
	 * 
	 * @param dbHost
	 * @param dbPort
	 * @param dbName
	 * @param dbUser
	 * @param dbPassword
	 */
	private void purgeDatabase(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword) {

		String deleteTablesScriptName = deleteTablesScriptFile.getPath();
		String deleteSequencesScriptName = deleteSequencesScriptFile.getPath();

		execPsql(dbHost, dbPort, dbName, dbUser, dbPassword, "-t", "-o", deleteTablesScriptName, "-c",
				"select 'drop table '||table_name||' cascade;' from information_schema.tables where table_schema='public' order by table_name;");
		execPsql(dbHost, dbPort, dbName, dbUser, dbPassword, "-q", "-f", deleteTablesScriptName);
		execPsql(dbHost, dbPort, dbName, dbUser, dbPassword, "-t", "-o", deleteSequencesScriptName, "-c",
				"select 'drop sequence '||sequence_name||';' from information_schema.sequences where sequence_schema='public' order by sequence_name;");
		execPsql(dbHost, dbPort, dbName, dbUser, dbPassword, "-q", "-f", deleteSequencesScriptName);
	}

	/**
	 * Import database data using psql
	 * 
	 * @param dbHost
	 * @param dbPort
	 * @param dbName
	 * @param dbUser
	 * @param dbPassword
	 * @param exportFile
	 */
	private void importData(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword, String exportFile) {
		execPsql(dbHost, dbPort, dbName, dbUser, dbPassword, "-f", exportFile);
	}

	/**
	 * Execute psql command
	 * 
	 * @param dbHost
	 * @param dbPort
	 * @param dbName
	 * @param dbUser
	 * @param dbPassword
	 * @param psqlOptions
	 */
	private void execPsql(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword, String... psqlOptions) {
		// variable used to store and display actual command that is executed
		String command = "";
		try {
			String[] psqlCommonOptions = { "psql", "-h", dbHost, // database host
					"-p", dbPort, // database port
					"-d", dbName, // database name
					"-U", dbUser, // database user
					"-w", // do not prompt for password
					"-v", "ON_ERROR_STOP=ON", // stop on first error
			};
			// Build the list of command options
			List<String> psqlCommands = new Vector<String>();
			for (String e : Arrays.asList(psqlCommonOptions))
				psqlCommands.add(e);
			for (String e : Arrays.asList(psqlOptions))
				psqlCommands.add(e);
			// Build and log command
			ProcessBuilder processBuilder = new ProcessBuilder(psqlCommands);
			for (String p : processBuilder.command())
				command = command + p + " ";
			logger.debug("Process command:" + command);
			// redirect error stream into stdout
			processBuilder.redirectErrorStream(true);
			// Password needs to be put in env variables as it can not be set as psql command parameter
			processBuilder.environment().put("PGPASSWORD", dbPassword);
			Process p = processBuilder.start();
			// read and log console messages
			BufferedReader stdInput = new BufferedReader(new InputStreamReader(p.getInputStream()));
			Vector<String> consoleMessages = new Vector<String>();
			String consoleMessage = null;
			while ((consoleMessage = stdInput.readLine()) != null) {
				logger.debug("Process console: " + consoleMessage);
				consoleMessages.add(consoleMessage);
			}
			// wait for process to complete
			int exitCode = p.waitFor();
			logger.debug("Process exit code is:" + exitCode);
			if (exitCode != 0) {
				// add the last 5 messages from console into exception
				int i0 = Math.max(0, consoleMessages.size() - 5);
				String lastMessages = "...";
				for (int i = i0; i < consoleMessages.size(); i++)
					lastMessages += "\n" + consoleMessages.get(i);
				throw new TechnicalException("psql command failed: " + command + " ; exit code = " + exitCode + "\n" + lastMessages);
			}
		} catch (IOException e) {
			logger.error("Exception during psql execution", e);
			throw new TechnicalException("psql command failed: " + command, e);
		} catch (InterruptedException e) {
			logger.error("Exception during psql execution", e);
			throw new TechnicalException("psql command failed: " + command, e);
		}

	}

	/**
	 * Execute pg_dump command
	 * 
	 * @param dbHost
	 * @param dbPort
	 * @param dbName
	 * @param dbUser
	 * @param dbPassword
	 * @param dumpFile
	 */
	private void execPgdump(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword, String dumpFile) {
		// variable used to store and display actual command that is executed
		String command = "";
		try {
			ProcessBuilder processBuilder = new ProcessBuilder("pg_dump", "-h", dbHost, // database host
					"-p", dbPort, // database port
					"-n", "public", // export only public schema
					"-w", // do not prompt for password
					"-U", dbUser, // database user
					"-v", // verbose mode
					"-x", // do not export grants
					"-O", // do not export ownership
					"-f", dumpFile, // export file
					dbName // database name (must be last parameter)
			);
			// Build and log command
			for (String p : processBuilder.command())
				command = command + p + " ";
			logger.debug("Process command:" + command);
			// redirect error stream into stdout
			processBuilder.redirectErrorStream(true);
			// Password needs to be put in env variables as it can not be set as psql command parameter
			processBuilder.environment().put("PGPASSWORD", dbPassword);
			// Start execution
			Process p = processBuilder.start();
			// read and log console messages
			BufferedReader stdInput = new BufferedReader(new InputStreamReader(p.getInputStream()));
			Vector<String> consoleMessages = new Vector<String>();
			String consoleMessage = null;
			while ((consoleMessage = stdInput.readLine()) != null) {
				logger.debug("Process console: " + consoleMessage);
				consoleMessages.add(consoleMessage);
			}
			// wait for process to complete
			int exitCode = p.waitFor();
			logger.debug("Process exit code is:" + exitCode);
			if (exitCode != 0) {
				// add the last 5 messages from console into exception
				int i0 = Math.max(0, consoleMessages.size() - 5);
				String lastMessages = "...";
				for (int i = i0; i < consoleMessages.size(); i++)
					lastMessages += "\n" + consoleMessages.get(i);
				throw new TechnicalException("pg_dump command failed: " + command + " ; exit code = " + exitCode + "\n" + lastMessages);
			}
		} catch (IOException e) {
			logger.error("Exception during pg_dump execution", e);
			throw new TechnicalException("pg_dump command failed: " + command, e);
		} catch (InterruptedException e) {
			logger.error("Exception during pg_dump execution", e);
			throw new TechnicalException("pg_dump command failed: " + command, e);
		}
	}
}