System requirements for installation

Supported Versions

• Upsert^® Sudo v2.1.x+

  • 11.1.x
  • 12.x

• Upsert^® Sudo v1.0.x - v2.0.x

  • 9.x
  • 10.x
  • 11.0.x

Supported Editions

• Sell
• Serve
• Enterprise
• Professional

Supported Databases

• MySQL
• MSSQL
• Oracle
• DB2

Supported Languages

• English

Installing the Plug-In

Once you complete your order for the Upsert^® Migrator plug-in, you should receive an email with instructions to download the necessary file. Complete these steps to install the plug-in to your Sugar instance:

1. Log in to your Sugar instance as an admin user

2. Go to Admin > Module Loader

3. Select the Upsert Migrator ZIP file and click ‘Upload’

4. Click the ‘Install’ button

5. Review the license agreement, toggle the option to ‘Accept’, and click ‘Commit’ if you accept.

Uninstalling the Plug-In

Follow the steps below to remove the plug-in from your Sugar instance.

1. Log in to your Sugar instance as an admin user

2. Go to Admin > Module Loader

3. Locate the ‘Upsert^® Migrator for SugarCRM’ package in the list of installed packages and click ‘Uninstall’

4. Select the desired option for handling the related database tables: a. Do Not Remove Tables - You either want to upgrade the plug-in to a newer release or you want to retain the sudo audit log data. b. Remove Tables - You want all data associated with the plug-in to be deleted and you have no intention of upgrading.

5. Click ‘Next’

The uninstall will process and provide a confirmation upon successful completion.

Migrator Overview

The Upsert Migrator tool is a set of utility classes that enable developers to write database migration scripts. There are currently two kinds of migration jobs supported: standalone and instance.

Both migration job types contain the ability to execute SQL and PHP code if needed, however, instance migration jobs load Sugar's libraries, such as BeanFactory and SugarQuery, while standalone jobs do not. Think of standalone as things to do before a Sugar instance is up and instance as things to do once sugar is up and running.

PLEASE READ

When creating migration jobs, it is highly recommended to convert SQL with create table to create table if not exists and insert into to insert ignore into as this will reduce issues when re-running migrations for developers on local stacks.

Note: You can use Query Capture to log any queries executed by Sugar during development.

Specifying Environments

Before you can create or run migrations, you must create custom/src/Upsert/Migrator/Environment.php. An example file has been provided at custom/src/Upsert/Migrator/Environment.example for your reference. This file is designed to help detect the current environment by environment variables but can be altered however you need.

Standalone

Standalone migration jobs do not require a working instance of sugar to use. This is the migration job type that you will need to use for upgrades and structural changes as instance migration jobs will fail if Sugar can't run correctly.

Standalone migrations are best used for

• Creating tables

CREATE TABLE IF NOT EXISTS table_name (id char(36) NOT NULL) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

• Inserting static data

INSERT IGNORE INTO table_name (id, field1, field2) VALUES('8cd3ea66-d9e6-11eb-bfb8-0242ac12000a', 'value1', 'value2');

• Altering tables

ALTER TABLE table_name add COLUMN field1 bool DEFAULT 0 NULL

Creating Standalone Migration Jobs

To create a standalone migration job:

• Run the upsert:migrator:standalone:create Sugar command from /var/www/html:

php bin/sugarcrm upsert:migrator:standalone:create "TICKET-XX Your Job Description"

This will create a new job file under ./sugar/custom/src/Upsert/Migrator/MigrationJobs/Standalone/ for you to customize.

An example of a generated file is shown below:

<?php

namespace Sugarcrm\Sugarcrm\custom\Upsert\Migrator\MigrationJobs\Standalone;

class Migration1589291696 extends \Sugarcrm\Sugarcrm\custom\Upsert\Migrator\Migrations\Standalone
{
    protected $description = 'Standalone :: TICKET-XX Your Job Description';

    /**
     * The sql queries we need grouped by environment.
     *
     * 'env' => [
     *     'sql'
     * ]
     *
     * @var array
     */
    protected $sql = [
        'all' => [
            //sql specific to all environments
        ],
        'prod' => [
            //sql specific to prod
        ],
        'dev' => [
            //sql specific to dev
        ],
        'uat' => [
            //sql specific to uat
        ],
        'training' => [
            //sql specific to training
        ],
        'ci' => [
            //sql specific to ci
        ],
    ];

    /**
     * Executes any programmatic scripts we may need given an environment.
     * - code here should NOT be specific to sugar
     */
    protected function executeCode()
    {
        //code specific to all environments

        if ($this->env == 'prod') {
            //code specific to prod
        } elseif ($this->env == 'dev') {
            //code specific to dev
        } elseif ($this->env == 'uat') {
            //code specific to uat
        } elseif ($this->env == 'training') {
            //code specific to training
        } elseif ($this->env == 'ci') {
            //code specific to ci
        }

        //return true for success
        return true;
    }
}

SQL

The SQL array property will allow you to define the SQL to import given the environment. If the SQL is not dependent on the environment, you can place it under the all index. Please note that you can populate each environments array index with a SQL string or parameterized array:

SQL String

"UPDATE calls SET name = 'My Call' WHERE (id = 'c80726fa-9425-11uc-9683-0242ac130005') AND (deleted = 0)",

Parameterized Query Array

array (
  //parameterized query
  0 => 'UPDATE calls SET name = ? WHERE (id = ?) AND (deleted = ?)',

  //parameters
  1 => 
  array (
    1 => 'My Call',
    2 => 'c80726fa-9425-11uc-9683-0242ac130005',
    3 => 0,
  ),

  //parameter types - this is optional but recommended
  2 => 
  array (
    1 => 2,
    2 => 2,
    3 => 5,
  ),
)

Code

The executeCode function will allow you to execute non-sugar specific code during the migration. This is useful for querying the database before running updates. If you need the status of the migration to depend upon the result of a query, you should use $this->executeQuery($query) to execute your query.

Database State

To determine your databases migration state, you can use the Sugar-CLI command upsert:migrator:standalone:status:

php bin/sugarcrm upsert:migrator:standalone:status

This command will display the completed and pending jobs.

Resetting the Database State

To reset your databases migration state, you can use the Sugar-CLI command upsert:migrator:standalone:reset:

php bin/sugarcrm upsert:migrator:standalone:reset 

This command will reset the marker in the database and allow you to rerun previously completed jobs.

Executing Jobs

During a deployment, standalone migration jobs are executed by running php -f ./custom/src/Upsert/Migrator/Commands/cli-deployment-standalone.php. For development against a working Sugar instance, a developer may choose to use Sugar-CLI command upsert:migrator:standalone:execute:

php bin/sugarcrm upsert:migrator:standalone:execute

Instance

Instance migration jobs require a working instance of sugar to use. This should be used when you want to use bean objects to create records.

Instance migrations are best used for

• Data corrections for existing data such as calculating column value updates
• Creating or updating data using Sugars BeanFactory
• Inserting dynamic or calculated data

INSERT IGNORE INTO table_name (id, field1, field2) VALUES($id, 'value1', 'value2');

Creating Instance Migration Jobs

To create an instance migration job:

• Run the upsert:migrator:instance:create Sugar command from /var/www/html:

php bin/sugarcrm upsert:migrator:instance:create "TICKET-XX Your Job Description"

This will create a new job file under ./sugar/custom/src/Upsert/Migrator/MigrationJobs/Instance/ for you to customize.

An example of a generated file is shown below:

<?php

namespace Sugarcrm\Sugarcrm\custom\Upsert\Migrator\MigrationJobs\Instance;

class Migration1589478132 extends \Sugarcrm\Sugarcrm\custom\Upsert\Migrator\Migrations\Instance
{
    protected $description = 'Instance :: TICKET-XX Your Job Description';

    /**
     * The sql queries we need grouped by environment.
     *
     * 'env' => [
     *     'sql'
     * ]
     *
     * @var array
     */
    protected $sql = [
        'all' => [
            //sql specific to all environments
        ],
        'prod' => [
            //sql specific to prod
        ],
        'dev' => [
            //sql specific to dev
        ],
        'uat' => [
            //sql specific to uat
        ],
        'training' => [
            //sql specific to training
        ],
        'ci' => [
            //sql specific to ci
        ],
    ];

    /**
     * Executes any programmatic scripts we may need given an environment.
     * - code here SHOULD be specific to sugar
     */
    protected function executeCode()
    {
        //code specific to all environments

        if ($this->env == 'prod') {
            //code specific to prod
        } elseif ($this->env == 'dev') {
            //code specific to dev
        } elseif ($this->env == 'uat') {
            //code specific to uat
        } elseif ($this->env == 'training') {
            //code specific to training
        } elseif ($this->env == 'ci') {
            //code specific to ci
        }

        //return true for success
        return true;
    }
}

SQL

The SQL array property will allow you to define the SQL to import given the environment. If the SQL is not dependent on the environment, you can place it under the all index. Please note that you can populate each environments array index with a SQL string or parameterized array:

SQL String

"UPDATE calls SET name = 'My Call' WHERE (id = 'c80726fa-9425-11uc-9683-0242ac130005') AND (deleted = 0)",

Parameterized Query Array

array (
  //parameterized query
  0 => 'UPDATE calls SET name = ? WHERE (id = ?) AND (deleted = ?)',

  //parameters
  1 => 
  array (
    1 => 'My Call',
    2 => 'c80726fa-9425-11uc-9683-0242ac130005',
    3 => 0,
  ),

  //parameter types - this is optional but recommended
  2 => 
  array (
    1 => 2,
    2 => 2,
    3 => 5,
  ),
),

Code

The executeCode function will allow you to execute Sugar specific code during the migration. This is useful for creating records through bean objects. If you need the status of the migration to depend upon the result of a query, you should use $this->executeQuery($query) to execute your query.

Database State

To determine your databases migration state, you can use the Sugar-CLI command upsert:migrator:instance:status:

php bin/sugarcrm upsert:migrator:instance:status

This command will display the completed and pending jobs.

Resetting the Database State

To reset your databases migration state, you can use the Sugar-CLI command upsert:migrator:instance:reset:

php bin/sugarcrm upsert:migrator:instance:reset 

This command will reset the marker in the database and allow you to rerun previously completed jobs.

Executing Jobs

During a deployment, instance migration jobs are executed by running Sugar-CLI command upsert:migrator:instance:execute. For development against a working Sugar instance, a developer should continue to use the Sugar-CLI command

php bin/sugarcrm upsert:migrator:instance:execute

Query Capture

The query capture component allows a developer to log all queries executed by Sugar. There are currently two loggers available.

UpsertMigratorQueryCaptureMysqliManager

The UpsertMigratorQueryCaptureMysqliManager logger is used to capture queries for reuse in customizations and migrations. When enabled, all queries by the application will be stored in ./custom/src/Upsert/Migrator/QueryCapture/ within the following files based on its type:

• ./custom/src/Upsert/Migrator/QueryCapture/{type}-array-queries.php - This stores queries in an array format. An example of a logged query is:

array (
  0 => 'SELECT * FROM edge_members WHERE id = ?',
  1 => 
  array (
    0 => 'dc44f3dc-a06d-42b0-8978-9601b1b4b210',
  ),
  2 => 
  array (
  ),
),

• ./custom/src/Upsert/Migrator/QueryCapture/{type}-code-queries.php - This stores queries as PHP code. An example of a logged query is:

$sql = "SELECT * FROM edge_members oauth_tokens WHERE id = ?";
$parameters = array (
  0 => 'dc44f3dc-a06d-42b0-8978-9601b1b4b210',
);
$types = array (
);
$GLOBALS['db']->getConnection()->executeQuery($sql, $params, $types);

• ./custom/src/Upsert/Migrator/QueryCapture/{type}-sql-queries.sql - This stores queries as direct sql code. An example of a logged query is:

SELECT * FROM edge_members WHERE id = 'dc44f3dc-a06d-42b0-8978-9601b1b4b210';

Type All

We capture all queries together in the all files:

• ./custom/src/Upsert/Migrator/QueryCapture/all-array-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/all-code-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/all-sql-queries.sql

Type Get

We capture all the fetching queries, such as DESCRIBE, SELECT, SHOW, and EXPLAIN, together in the get files:

• ./custom/src/Upsert/Migrator/QueryCapture/get-array-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/get-code-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/get-sql-queries.sql

Type Set

We capture all the settings queries, such as CREATE INDEX, CREATE TABLE, SELECT INTO, CREATE PROCEDURE, INSERT, UPDATE, RENAME, SET, ALTER, DELETE,TRUNCATE, and DROP, together in the set files:

• ./custom/src/Upsert/Migrator/QueryCapture/set-array-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/set-code-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/set-sql-queries.sql

Type Unknown

If a query did not match the patterns for get or set types, they will be logged as unknown

• ./custom/src/Upsert/Migrator/QueryCapture/unknown-array-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/unknown-code-queries.php
• ./custom/src/Upsert/Migrator/QueryCapture/unknown-sql-queries.sql

If an unknown file was generated, please contact us at Upsert with the query to update getCRUDAction in ./custom/src/Upsert/Migrator/Libraries/Sugar/Classes/Helpers/Query.php.

Enabling The Logger

To enable the query capture, you must add the following to the ./config_override.php:

$sugar_config['dbconfig']['db_manager'] = 'UpsertMigratorQueryCaptureMysqliManager';

Limiting The Logs

If you would like to limit the output files, you can use the sugar configurations below:

$sugar_config['upsert']['migrator']['query_capture']['types'] = [
    'all',
    'set',
    'get',
    'unknown'
];

$sugar_config['upsert']['migrator']['query_capture']['formats'] = [
    'array',
    'code',
    'sql',
];

UpsertMigratorQueryLoggerMysqliManager

The UpsertMigratorQueryLoggerMysqliManager logger is used for debugging purposes. When enabled, all queries by the application will be stored in the ./sugarcrm.log file as a fatal log.

Enabling The Logger

To enable the query capture, you must add the following to the ./config_override.php:

$sugar_config['dbconfig']['db_manager'] = 'UpsertMigratorQueryLoggerMysqliManager';