Search
Contact Us

How to use SQL queries in OroCRM migrations

How to Use Sql Queries in Orocrm Migrations
Denis Saltanakhmedov Avatar
Denis Saltanakhmedov

As you may know, to add new data to Symfony bundles we can use migration scripts or it is also possible to use a fixture for this. However, we do not recommend to use the fixture if you need to change some configurations, alter table or insert any specific variable. Fortunately, OroCRM team has provided the solution for SQL queries in the migration scripts – we can use Insert, Update, Delete and other queries there.

So, if we want to work with SQL query, in the beginning we should create a new migration script in own bundle. In the previous article, we have created the custom test bundle and the example of the migration script. And in this article we will use the migration script with version number v1_1.

We should point out that the migration scripts cannot handle a direct SQL query by default, therefore we will use the helper class. For instance, we create a folder with the mentioned version number in our AtwixTestBundle:

 path:src/Atwix/Bundle/TestBundle/Migrations/Schema/v1_1/AtwixTestBundle.php

The code of the migration will have the following look:

<?php

namespace Atwix\Bundle\TestBundle\Migrations\Schema\v1_1;

use Doctrine\DBAL\Schema\Schema;
use Atwix\Bundle\TestBundle\Migrations\Schema\AtwixTestMigrationsAbstract;
use Oro\Bundle\MigrationBundle\Migration\QueryBag;

class AtwixTestBundle extends AtwixTestMigrationsAbstract
{
    /**
     * {@inheritdoc}
     */
    public function up(Schema $schema, QueryBag $queries)
    {
        $queries->addQuery(new HelperClass());
    }
}

As you can see, there we have the call of our helper object: HelperClass. So, create a class in the same directory, using the code below:

<?php

namespace Atwix\Bundle\TestBundle\Migrations\Schema\v1_1;

use Psr\Log\LoggerInterface;

use Doctrine\DBAL\Types\Type;

use Oro\Bundle\EntityConfigBundle\Config\ConfigModelManager;
use Oro\Bundle\MigrationBundle\Migration\ArrayLogger;
use Oro\Bundle\MigrationBundle\Migration\ParametrizedMigrationQuery;

class HelperClass extends ParametrizedMigrationQuery
{
    /**
     * {@inheritdoc}
     */
    public function getDescription()
    {
        $logger = new ArrayLogger();
        $this->runQuery($logger, true);

        return $logger->getMessages();
    }

    /**
     * {@inheritdoc}
     */
    public function execute(LoggerInterface $logger)
    {
        $this->runQuery($logger);
    }

    /**
     * @param LoggerInterface $logger
     * @param bool            $dryRun
     */
    protected function runQuery(LoggerInterface $logger, $dryRun = false)
    {
        
    }
}

For the time being, this class is not doing anything. It is just a concept and below we are adding the further logic.

Pay attention that HelperClass extends ParametrizedMigrationQuery class. We need the ParametrizedMigrationQuery class to access the “connection” variable and other useful methods. Moreover, we must declare the following methods: getDescription() and execute() because they are abstract methods of the ParametrizedMigrationQuery class. And the method runQuery() will contain routine for processing our query.

For example, we have “OroCRM/Bundle/TaskBundle/Entity/Task” entity. There are few properties in this entity, look at the “orocrm_task” table:

Task-full

So, there are five items, and they all have a different priority. For example, we need to change tasks with high priority to normal and remove tasks with low priority.

In this case, the SQL query that will update the tasks with high priority is the following:

UPDATE "orocrm_task" SET "task_priority_name" = 'normal' WHERE "task_priority_name" = 'high';

And the SQL query for deleting the tasks with low priority is:

DELETE FROM "orocrm_task" WHERE (("task_priority_name" = 'low'));

Now, we can create the runQuery function:

protected function runQuery(LoggerInterface $logger, $dryRun = false)
    {
        $sqlQuery[0] = array(
            'query' => 'UPDATE "orocrm_task" '
                .'SET task_priority_name = :normal '
                .'WHERE task_priority_name = :high;',
            'param' => [
                'normal' => 'normal',
                'high' => 'high'
            ],
            'type' => [
                'normal' => Type::STRING,
                'high' => Type::STRING,
            ]

        );

        $sqlQuery[1] = array(
            'query' => 'DELETE FROM "orocrm_task" '
                .'WHERE (("task_priority_name" = :low));',
            'param' => [
                'low' => 'low'
            ],
            'type' => [
                'low' => Type::STRING
            ]
        );

        foreach ($sqlQuery as $sQuery) {
            var_dump($this->connection->executeUpdate($sQuery['query'],$sQuery['param'],$sQuery['type']));
        }

    }

As a result, our function calls the native OroCRM function “executeUpdate”. This function executes the SQL INSERT/UPDATE/DELETE query with the specified parameters and returns the number of the affected rows. There are three parameters: $query, array $params = array(), array $types = array(). In our migration, we use var_dump() – it is necessary for showing how queries were executed.

Then, we can run:

php app/console oro:migration:load --force --timeout=0

Note, if you run:

php app/console oro:migration:load --dry-run --show-queries

the queries will be executed as well. You must be very careful if you do not want to lose your data.

So, you already know how to use SQL queries in OroCRM migrations. We will be happy to see your feedback or other ideas in the comments below.

Share post: