WeMigrane™

The WeMigrane™ (Formerly Migratron 9000™) is a purpose built laravel application for migrating data from databases / files to a single database (A light version of the ETL concept). It will be used primarily for migrating clients from the v9 to v10 versions of SGI (although it is generic enough that it will work for other cases as well).

Process explanation

The whole process goes as follows, the programer creates a sequence (a container of jobs), then appends a number of jobs to that sequence (the order in which they are appended is important). Each job can have one extractor (extractors retrieve information from the source database / files), one or more transformers (transformers modify the information provided by extractors / previous tranformers) and one or more loaders (loaders load the transformed information into the destination ).

As you can see in the chart above, all the programmer needs to do is to create one sequence (extending the class WeJobSequencer) and as many jobs as are needed to complete the migration (extending the class WeJobSequenceMember). Note that the Jobs created by extending the class WeJobSequenceMember will have the variable $this->we_job available for adding operations to.

To recap, a sequence contains many jobs and a job contains many operations. An operation refers to an ETL operation (a loader, extractor, or transformer).

Note both the GenericSequence and GenericJob have a define function that will be called when the sequence is executed. This is where, for the sequence you define the jobs to be executed, and for the job, you define the operations that constitute it.

Update 2018-06-01:

It is now possible to add sequences as sub sequence of a sequence (wow). The jobs will execute first and them sub sequences will execute second (regardless of their actual position in the define function).

A Sequence can define any number of parameters that may be entered (via artisan) at the start of the sequence execution. If any parameters are set as required, then the sequence will not start until all required parameters are filled.

/**
 * Example of a GenericSequence - Note you must rename the class to someting more pertinent
 */
class V9V10Sequence extends WeJobSequencer {

    function __construct() {
       parent::__construct();
    }

    protected function define() {
        $this->name = 'V9 to V10 base sequence';

        // Adding a parameter
        // parameters are name, required and default value
        $this->addParameter('fromdate', true, null);

        // Adding a sub sequence (any sequence can be added as a sub sequence)
        $this->addSequence(new Subsequence);

        // Administration Jobs
        $this->addJob(new UsersTable);
        $this->addJob(new CorporateRolesTable);
        $this->addJob(new CorporateRoleUsersTable);
        $this->addJob(new GroupsTable);
        $this->addJob(new GroupUsersTable);
        $this->addJob(new CompanyOrganizationsTable);
        $this->addJob(new CompanyOrganizationUsersTable);
        $this->addJob(new TablesTable);
        $this->addJob(new TableItemsTable);
        $this->addJob(new AbbreviationsTable);

        // Documentation Jobs
        $this->addJob(new DocumentationTreeTable);
        $this->addJob(new FamilyTypesTable);
        $this->addJob(new FamiliesTable);
        $this->addJob(new FamiliesWorkflowToWorkflowsTable);
    }
}

A single job may execute multiple operations (extract, transform, load) and the load operations may return items for further transformation and loading. Each transformer or loader operation may change the items list that is being passed from one to another and pass it, in turn, to the next operation in line. You must be aware of what these changes could be by consulting the documentation on them. This is crucial for designing a good sequence of jobs (it helps to be familiar with ETL concepts).

/**
 * Example of a GenericJob
 */
class GroupsTable extends WeJobSequenceMember
{
    function __construct() {
       parent::__construct('Groups Table + Translations');
    }

    protected function define()
    {
        $this->we_job
            ->extract('App\Etl\Extractors\LaravelTableExtractor', 't03w043', [
                'connection' => 'source_db'
            ])
            ->transform('App\Etl\Transformers\ColumnRenameTransformer', [
                'columns' => [
                    'C22484' => 'id',
                    'C22485' => 'name',
                ]
            ])
            ->transform('App\Etl\Transformers\IncludeOnlyColumnsTransformer', [
                'columns' => [
                    'id',
                    'name'
                ]
            ])
            ->transform('App\Etl\Transformers\AddColumnsStaticValueTransformer', [
                'new_columns'   => [
                    'status_id' => 1, 
                    'locale'    => 'pt'
                ]
            ])
            ->load('App\Etl\Loaders\LaravelTableLoader', 't00_groups', [
                'connection'      => 'destination_db',
                'include_columns' => [
                    'id',
                    'status_id'
                ],
                'wipe_table'      => true
            ])
            ->transform('App\Etl\Transformers\ColumnRenameTransformer', [
                'columns' => [
                    'id' => 'group_id'
                ]
            ])
            ->load('App\Etl\Loaders\LaravelTableLoader', 't00_group_translations', [
                'connection'      => 'destination_db',
                'include_columns' => [
                    'group_id',
                    'name',
                    'locale'
                ],
                'wipe_table'      => true
            ]);
    }
}

If the job fails at any stage after the first loading stage, all loading stages that have already succeeded will be rolled back.

Artisan commands available

All wemigrane commands are container within

Execute sequence

php artisan wemigrane:execute_sequence <sequence_name> [session_name] [*p=<param>:<value>]

This command executes the sequence named in the first parameter (Sequences are defined in a cammel case folder and refered in the command by snake case). The second (optional) parameter is used to name the session of the migration. Knowing this session name is important if you wish to repeat a migration without repeating successull jobs. If a session name is not entered, one will be generated, and must be remembered on repeat executions of the sequence.

A given sequence may define parameters which can be entered in the format of -p=<param>:<value>. If the sequence contains parameters set as required, it will not proceed untill all required parameters have a corresponding value.

At the end of executing the command, WeMigrane will generate a report of anomalous ocurrences in the folder WeMigrane/storage/app/session_name_timestamp.html

Introduction video

See the introductory video bellow