Prototype: get SilverStripe database (when a beam.json configis available)

_README.md

getdb prototype

This is an experimental database fetching tool for projects using SilverStripe with _ss_environment.php files for database config and Beam. A project's beam.json config is used to find SSH connection details, and the database details for that environment are read from the _ss_environment.php file on the server.

This is essentially a wrapper to build and run a shell command like this:

ssh user@example.com mysqldump -u user -p'password' someremotedatabase \| gzip -c | gunzip -c | mysql somelocaldb

Requirements

This script currently uses pv to show download progress. pv is not optional yet, so you may need to install it:

# OSX with brew
brew install pv

Usage

Download the script below to somewhere on your PATH, and make it executable:

curl https://gist.githubusercontent.com/stecman/21ef4452dc18d30391a5/raw/getdb -o ~/bin/getdb
chmod +x ~/bin/getdb

Then use as:

getdb [beam target] [local database name]

eg. getdb live dev_foo

The local database will be created automatically if it doesn't exist.

getdb

#!/usr/bin/env php
<?php

/**
 * Extract a list of server details from the nearest beam.json
 * @return array
 */
function getBeamServers()
{
    $path = getcwd();

    do {
        if (is_file("$path/beam.json")) {
            $json = json_decode(file_get_contents("$path/beam.json"), true);
            return $json["servers"];
        }
    } while(($path = dirname($path)) != "/");

    die("Couldn't find a beam.json file in current or any parent directory.\n");
}

// If we're running in remote mode, look for the environment config
// This code is run on the server the database is being fetched from
if (in_array('--remote', $argv)) {
    $stderr = fopen('php://stderr', 'w');
    fwrite($stderr, 'Hello from ' . `hostname -f`);

    $dir = __DIR__;
    do {
        $file = $dir . '/_ss_environment.php';

        if (file_exists($file)) {
            require $file;
            break;
        }

        $dir = dirname($dir);
    } while ($dir !== '/');

    if (!defined('SS_DATABASE_NAME') && isset($database)) {
        define('SS_DATABASE_NAME', $database);
    } else {
        fwrite($stderr, "> SS_DATABASE_NAME not defined. Does this server use constants for database config?\n");
        exit(1);
    }

    $dbName = defined('SS_DATABASE_NAME') ? SS_DATABASE_NAME : $database;

    fwrite($stderr, "Dumping database '$dbName'\n");

    // Dump the database to stdout and down the SSH channel
    $cmd = sprintf(
        'mysqldump --user=%s --password=%s -h %s %s | gzip -cf7',
        escapeshellarg(SS_DATABASE_USERNAME),
        escapeshellarg(SS_DATABASE_PASSWORD),
        escapeshellarg(SS_DATABASE_SERVER),
        escapeshellarg($dbName)
    );

    passthru($cmd, $status);

    if ($status !== 0) {
        fwrite($stderr, "> mysqldump didn't exit cleanly :(\n> See above for details\n");
    }

    fclose($stderr);

// We're running locally
// Run this script on the server and pipe the output to mysql
} else {
    if (count($argv) < 3) {
        echo "Not enough arguments. Usage:\n  ./getdb [target [local db]\n";
        exit(1);
    }

    $servers = getBeamServers();
    $target = $argv[1];
    $targetDatabase = $argv[2];

    if (!isset($servers[$target])) {
        die("No such target '$target' configured in beam.json. Available targets are: "
                . implode(', ', array_keys($servers)) . "\n" );
    }

    // Ensure local database exists
    passthru(sprintf(
        'mysql -e "CREATE DATABASE IF NOT EXISTS %s;"',
        $targetDatabase
    ));

    // Build command to run on remote server
    // This base64 encodes the current script so it can be run on the server
    $server = $servers[$target];
    $remoteCommand = sprintf(
        'cd %s && echo %s | base64 -d | php -- --remote',
        escapeshellarg($server['webroot']),
        escapeshellarg(base64_encode(
            // Load this file as a string without the shebang
            preg_replace('/^.+\n/', '', file_get_contents(__FILE__))
        ))
    );

    // Build command to run locally
    $user = !empty($server['user']) ? $server['user'] . '@' : '';
    $localCommand = sprintf(
        'ssh %s%s %s | pv | gunzip -c | mysql -uroot -proot %s',
        escapeshellarg($user),
        escapeshellarg($server['host']),
        escapeshellarg($remoteCommand),
        escapeshellarg($targetDatabase)
    );

    passthru($localCommand);
}