• Home
• au.net.causal.maven.plugins
• boxdb-maven-plugin
• 2.0
• source code
• SqlServerLinuxDatabase.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

au.net.causal.maven.plugins.boxdb.db.SqlServerLinuxDatabase Maven / Gradle / Ivy

package au.net.causal.maven.plugins.boxdb.db;

import au.net.causal.maven.plugins.boxdb.JdbcSqlRunner;
import au.net.causal.maven.plugins.boxdb.ScriptReaderRunner;
import com.google.common.collect.ImmutableMap;
import io.fabric8.maven.docker.access.DockerAccess;
import io.fabric8.maven.docker.config.ImageConfiguration;
import io.fabric8.maven.docker.config.LogConfiguration;
import io.fabric8.maven.docker.config.RunImageConfiguration;
import io.fabric8.maven.docker.config.RunVolumeConfiguration;
import io.fabric8.maven.docker.log.LogOutputSpec;
import org.apache.maven.plugin.MojoExecutionException;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.NoSuchFileException;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.Duration;
import java.util.Arrays;
import java.util.Collections;
import java.util.concurrent.TimeoutException;
import java.util.stream.Collectors;

public class SqlServerLinuxDatabase extends DockerDatabase
{
    private final Path hostBackupDirectory;
    private final Path hostScriptDirectory;

    public SqlServerLinuxDatabase(BoxConfiguration boxConfiguration, ProjectConfiguration projectConfiguration, BoxContext context)
    throws IOException
    {
        super(boxConfiguration, projectConfiguration, context);

        //Generate host script directory that is used to give scripts to SQL Server
        hostScriptDirectory = context.getTempDirectory().resolve(containerName() + "-scripts");
        if (Files.notExists(hostScriptDirectory))
            Files.createDirectories(hostScriptDirectory);
        
        hostBackupDirectory = context.getTempDirectory().resolve(containerName() + "-backups");
        if (Files.notExists(hostBackupDirectory))
            Files.createDirectories(hostBackupDirectory);
    }

    @Override
    protected int containerDatabasePort()
    {
        return 1433;
    }

    @Override
    protected String dockerImageName()
    {
        return "microsoft/mssql-server-linux";
    }

    @Override
    protected void configureRunImage(RunImageConfiguration.Builder builder)
    {
        super.configureRunImage(builder);

        RunVolumeConfiguration backupVolume = new RunVolumeConfiguration.Builder()
                .bind(Arrays.asList(
                        getBackupDirectory().toAbsolutePath().toString() + ":/data/backup",
                        getScriptDirectory().toAbsolutePath().toString() + ":/data/scripts"))
                .build();

        builder.env(ImmutableMap.of("SA_PASSWORD", getBoxConfiguration().getAdminPassword(),
                                    "ACCEPT_EULA", "Y"));
        builder.volumes(backupVolume);
    }

    @Override
    public JdbcConnectionInfo jdbcConnectionInfo(DatabaseTarget target)
    throws BoxDatabaseException
    {
        return driverType().jdbcConnectionInfo(target, getBoxConfiguration(), getContext().getDockerHostAddress());
    }

    protected SqlServerJdbcDriverType driverType()
    {
        return SqlServerJdbcDriverType.fromBoxConfiguration(getBoxConfiguration());
    }

    @Override
    public JdbcDriverInfo jdbcDriverInfo() throws BoxDatabaseException
    {
        return driverType().jdbcDriver();
    }

    protected DataSourceBuilder dataSourceBuilder(DatabaseTarget target)
    throws BoxDatabaseException
    {
        DataSourceBuilder builder = new DataSourceBuilder(getContext());
        driverType().configureDataSourceBuilder(builder, target, getBoxConfiguration(), getContext().getDockerHostAddress());
        return builder;
    }
    
    @Override
    public Connection createJdbcConnection(DatabaseTarget targetDatabase)
    throws SQLException, BoxDatabaseException, IOException
    {
        return dataSourceBuilder(targetDatabase).create().getConnection();
    }

    @Override
    public void executeJdbcScript(Reader scriptReader, DatabaseTarget targetDatabase)
    throws IOException, SQLException, BoxDatabaseException
    {
        try (Connection con = createJdbcConnection(targetDatabase);
             JdbcSqlRunner sqlRunner = new JdbcSqlRunner(con, getContext().getLog()))
        {
            sqlRunner.executeSql(new BufferedReader(scriptReader));
        }
    }

    @Override
    public void configureNewDatabase()
    throws IOException, SQLException, BoxDatabaseException
    {
        runFilteredScript("sqlserver-linux-create-database.sql");
    }

    private void runFilteredScript(String scriptResourceName)
    throws IOException, BoxDatabaseException, SQLException
    {
        URL scriptResource = SqlServerLinuxDatabase.class.getResource(scriptResourceName);
        if (scriptResource == null)
            throw new FileNotFoundException("Missing script resource: " + scriptResourceName);

        ScriptReaderRunner scriptRunner = getContext().createScriptReaderRunner(this, getBoxConfiguration(), getProjectConfiguration());
        ScriptReaderExecution execution = new ScriptReaderExecution();
        execution.setFiltering(true);
        execution.setScripts(Arrays.asList(scriptResource));

        try
        {
            scriptRunner.execute(execution, DatabaseTarget.ADMIN, getProjectConfiguration().getScriptTimeout());
        }
        catch (MojoExecutionException e)
        {
            throw new BoxDatabaseException(e);
        }
    }

    @Override
    protected void executeScriptFile(Path scriptFile, DatabaseTarget targetDatabase, Duration timeout)
    throws IOException, SQLException, BoxDatabaseException
    {
        if (!Files.exists(scriptFile))
            throw new NoSuchFileException(scriptFile.toString());

        //Copy the script file to inside our script dir mounted to the container
        Path mountedScriptFile = Files.createTempFile(hostScriptDirectory, "script", ".sql");
        Files.copy(scriptFile, mountedScriptFile, StandardCopyOption.REPLACE_EXISTING);

        executeSqlCmd("-i /data/scripts/" + mountedScriptFile.getFileName().toString(), targetDatabase, timeout);
    }
    
    protected String sqlToolsPath()
    {
        return "/opt/mssql-tools/bin/";
    }

    private void executeSqlCmd(String args, DatabaseTarget targetDatabase, Duration timeout)
    throws IOException, SqlCmdException, BoxDatabaseException
    {
        //Docker plugin provides no easy way to get exit code from exec call so we need to script it and
        //return its value via the file system and a temp file
        Path returnCodeFile = Files.createTempFile(hostScriptDirectory, "return", ".txt");

        executeSqlCmd(args, targetDatabase, timeout, returnCodeFile, false);
    }

    private void executeSqlCmd(String args, DatabaseTarget targetDatabase, Duration timeout, Path returnCodeFile, boolean silent)
    throws IOException, SqlCmdException, BoxDatabaseException
    {
        String dbName = (targetDatabase == DatabaseTarget.ADMIN ? "master" : getBoxConfiguration().getDatabaseName());
        args = "-H localhost -b -e -U " + targetDatabase.user(getBoxConfiguration()) + " -P " + targetDatabase.password(getBoxConfiguration()) +
               " -d " + dbName + " " + args;
        
        DockerAccess docker = getContext().getDockerServiceHub().getDockerAccess();

        LogConfiguration logConfig = new LogConfiguration.Builder()
                .enabled(!silent)
                .prefix("sqlcmd")
                .build();
        RunImageConfiguration sqlCmdRunConfig = new RunImageConfiguration.Builder()
                .links(Collections.singletonList(containerName() + ":sqlcmd"))
                .namingStrategy("alias")
                .cmd("unused") //not used but must be non-null - is replaced later
                .log(logConfig)
                .build();
        String sqlCmdExecutablePath = sqlToolsPath() + "sqlcmd";
        sqlCmdRunConfig.getCmd().setExec(Arrays.asList("bash", "-i", "-c", sqlCmdExecutablePath + " " + args + " ; echo $? > " + "/data/scripts/" + returnCodeFile.getFileName().toString()));
        getContext().getLog().debug("Executing command: " + sqlCmdRunConfig.getCmd().getExec());
        sqlCmdRunConfig.getCmd().setShell(null);
        ImageConfiguration sqlPlusConfig = new ImageConfiguration.Builder()
                .runConfig(sqlCmdRunConfig)
                .name(dockerImageName())
                .alias(containerName() + "-sqlcmd")
                .build();

        String containerId = findDockerContainer().getId();
        String execContainerId = docker.createExecContainer(containerId, sqlCmdRunConfig.getCmd());

        LogOutputSpec execLog;
        Path logFile = null;
        if (silent)
        {
            logFile = Files.createTempFile(returnCodeFile.getParent(), "wait", ".log");
            execLog = new LogOutputSpec.Builder().logStdout(false).file(logFile.toAbsolutePath().toString()).build();
        }
        else
            execLog = getContext().getLogSpecFactory().createSpec(execContainerId, sqlPlusConfig);

        getContext().getLog().debug("Beginning exec container " + execContainerId);
        docker.startExecContainer(execContainerId, execLog);
        getContext().getLog().debug("Exec container done " + execContainerId);

        String exitCode = new String(Files.readAllBytes(returnCodeFile), StandardCharsets.UTF_8).trim();

        getContext().getLog().debug("All done: " + exitCode);

        //If we wanted silent run we won't want to keep the output so just delete these files
        //otherwise we'll have a huge amount of them, especially for the waiter script
        if (logFile != null)
        {
            if (getContext().getLog().isDebugEnabled())
                getContext().getLog().debug(Files.lines(logFile).collect(Collectors.joining(System.lineSeparator())));

            try
            {
                Files.deleteIfExists(logFile);
            }
            catch (IOException e)
            {
                //Not a huge deal if we can't delete
                getContext().getLog().debug("Failed to delete temporary log file " + logFile + ": " + e, e);
            }
        }

        if (!"0".equals(exitCode))
            throw new SqlCmdException("sqlcmd exit code: " + exitCode, exitCode);
    }

    @Override
    public void executeSql(String sql, DatabaseTarget targetDatabase, Duration timeout) 
    throws IOException, SQLException, BoxDatabaseException
    {
        //TODO could do this directly instead of writing to file first
        try (StringReader reader = new StringReader(sql))
        {
            executeScript(reader, targetDatabase, timeout);
        }
    }

    private Path getBackupDirectory()
    {
        return hostBackupDirectory;
    }
    
    private Path getScriptDirectory()
    {
        return hostScriptDirectory;
    }

    @Override
    public void backup(Path backupFile, BackupFileTypeHint backupFileTypeHint)
    throws BoxDatabaseException, IOException, SQLException
    {
        Path targetFile = getBackupDirectory().resolve("backup.bak");

        //Backup to backup.bak file
        String sql = "backup database " + getBoxConfiguration().getDatabaseName() + " to disk = '/data/backup/backup.bak' with format;";
        executeJdbcSqlCommand(sql, DatabaseTarget.ADMIN);

        //Move file to destination
        Files.move(targetFile, backupFile, StandardCopyOption.REPLACE_EXISTING);
    }

    @Override
    public void restore(Path backupFile) throws BoxDatabaseException, IOException, SQLException
    {
        Path targetFile = getBackupDirectory().resolve("backup.bak");

        //Copy backup file to vagrant directory
        Files.copy(backupFile, targetFile, StandardCopyOption.REPLACE_EXISTING);

        executeRestoreSqlCmd(targetFile);
    }

    @Override
    public void restore(URL backupResource)
    throws BoxDatabaseException, IOException, SQLException
    {
        Path targetFile = getBackupDirectory().resolve("backup.bak");

        //Copy backup file to vagrant directory
        try (InputStream is = backupResource.openStream())
        {
            Files.copy(is, targetFile, StandardCopyOption.REPLACE_EXISTING);
        }

        executeRestoreSqlCmd(targetFile);
    }

    private void executeJdbcSqlCommand(String sql, DatabaseTarget target)
    throws SQLException, BoxDatabaseException, IOException
    {
        try (StringReader reader = new StringReader(sql))
        {
            executeJdbcScript(reader, target);
        }
    }

    private void executeRestoreSqlCmd(Path backupFile)
    throws SQLException, BoxDatabaseException, IOException
    {
        String dropSql = "drop database if exists " + getBoxConfiguration().getDatabaseName() + ";";
        executeJdbcSqlCommand(dropSql, DatabaseTarget.ADMIN);
        
        String sql = "restore database " + getBoxConfiguration().getDatabaseName() + " from disk = '/data/backup/" + backupFile.getFileName().toString() + "' with replace;";
        executeJdbcSqlCommand(sql, DatabaseTarget.ADMIN);

        runFilteredScript("sqlserver-linux-post-restore-users.sql");
    }

    @Override
    public void waitUntilStarted(Duration maxTimeToWait) throws TimeoutException, BoxDatabaseException 
    {
        long startTime = System.currentTimeMillis();
        long maxEndTime = startTime + maxTimeToWait.toMillis();
        
        super.waitUntilStarted(maxTimeToWait);
        
        //Use JDBC waiting to fully wait until database is up
        //Sadly it's our only option for SQL Server for Linux at the moment
        //since there is a window where docker returns and the database is not fully started and will refuse login
        boolean connectionSucceeded = false;
        while (!connectionSucceeded && System.currentTimeMillis() < maxEndTime) 
        {
            try (Connection con = createJdbcConnection(DatabaseTarget.ADMIN)) 
            {
                connectionSucceeded = true;
            } 
            catch (SQLException e) 
            {
                //A subclass might override this for some reason so let's support it anyway - by default returns false
                if (databaseIsReady(e)) 
                {
                    connectionSucceeded = true;
                    getContext().getLog().debug("Failed to connect, but database deemed it is ready: " + e, e);
                } 
                else if (isLoginFailureBeforeDatabaseFullyStarted(e))
                {
                    //Happened because database not fully started
                    getContext().getLog().debug("Loop waiting for database connectivity failed to get connection", e);
                    
                    try 
                    {
                        Thread.sleep(getProjectConfiguration().getPollTime().toMillis());
                    }
                    catch (InterruptedException ex)
                    {
                        throw new BoxDatabaseException("Interrupted waiting for database to start up: " + ex, ex);
                    }
                }
                else
                    throw new BoxDatabaseException("Error occurred connecting to database to verify startup: " + e, e);
            }
            catch (IOException e)
            {
                throw new BoxDatabaseException("I/O error occured waiting for database to start up: " + e, e);
            }
        }
        
        if (!connectionSucceeded)
            throw new TimeoutException("Timed out waiting for database to come up.");
    }
    
    private boolean isLoginFailureBeforeDatabaseFullyStarted(SQLException ex)
    {
        switch (driverType())
        {
            case MICROSOFT:
                return "S0001".equals(ex.getSQLState()) && ex.getErrorCode() == 18456;
            case JTDS:
                return "28000".equals(ex.getSQLState()) && ex.getErrorCode() == 18456;
            default:
                throw new Error("Unknown driver type: " + driverType());
        }
    }

    public static class SqlCmdException extends SQLException
    {
        private final String exitCode;

        public SqlCmdException(String reason, String exitCode)
        {
            super(reason);
            this.exitCode = exitCode;
        }

        public String getExitCode()
        {
            return exitCode;
        }
    }
}