Serialized to JSON data upgrade
Overview
The following tutorial lists the steps needed to create an upgrade script that converts the data stored in the database from the default PHP serialized format to JSON format.
Use this tutorial to create an upgrade script to update your extension to work with Magento 2.2 and above.
Before you begin
Identify the data you need to convert to JSON in the database.
Your extension must convert data in the following cases:
- The extension stores serialized data provided by a core module that now uses the JSON format.
- The extension uses the automatic serializing mechanism provided by the Magento framework (i.e. the extension declares
\Magento\Framework\Model\ResourceModel\Db\AbstractDb::$_serializableFields
).
Your extension will continue working in Magento 2.2 and above in the following cases, but we recommend you switch to using the JSON format for security reasons:
- The extension stores its own serialized data.
- The extension is responsible for serializing and unserializing data stored in core tables.
API Overview
This tutorial uses the following framework API in the following ways:
\Magento\Framework\DB\FieldDataConverter
- This class converts values for a field in a table from one format to another.\Magento\Framework\DB\FieldDataConverterFactory
- This class creates instances of theFieldDataConverter
with the appropriate data converter implementation.\Magento\Framework\DB\AggregatedFieldDataConverter
- This is a service class that allows specifying multiple fields from different tables at once. This class creates instances of theFieldDataConverter
class and accepts a list of\Magento\Framework\DB\FieldToConvert
value objects with field information. A singleconvert()
method call is limited to one DB connection.
\Magento\Framework\DB\DataConverter\DataConverterInterface
- This interface is for classes that convert data between different formats or types of data.\Magento\Framework\DB\FieldDataConverter
- This class accepts query modifiers for updating specific rows. Here is API for the query modifiers part:\Magento\Framework\DB\Select\QueryModifierInterface
- Interface for classes that add a condition to the database query to target specific entries.\Magento\Framework\DB\Select\QueryModifierFactory
- This class creates instances of specific implementations ofQueryModifierInterface
.\Magento\Framework\DB\Select\InQueryModifier
- An implementation of theQueryModifierInterface
that adds an IN condition to a query.\Magento\Framework\DB\Select\LikeQueryModifier
- An implementation of theQueryModifierInterface
that adds a LIKE condition to a query.\Magento\Framework\DB\Select\CompositeQueryModifier
- An implementation of theQueryModifierInterface
that allows the application of multiple query modifiers.
You can create your own query modifier or use any of the ones listed in the app/etc/di.xml
file.
\Magento\Framework\Module\Manager
- This class checks the status of a module.
Step 1: Create the basic upgrade script
The upgrade script is what runs during the upgrade step of your extension’s lifecycle.
Create the UpgradeData.php
file in the Setup
directory inside your extension’s root directory.
Inside the file, create the class UpgradeData
which implements \Magento\Framework\Setup\UpgradeDataInterface
.
Example upgrade script content:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
<?php
/**
* Copyright © Magento, Inc. All rights reserved.
* See COPYING.txt for license details.
*/
namespace Magento\CustomModule\Setup;
use Magento\Framework\DB\FieldDataConverterFactory;
use Magento\Framework\DB\Query\Generator;
use Magento\Framework\DB\Select\QueryModifierFactory;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\UpgradeDataInterface;
class UpgradeData implements UpgradeDataInterface
{
/**
* @var FieldDataConverterFactory
*/
private $fieldDataConverterFactory;
/**
* @var QueryModifierFactory
*/
private $queryModifierFactory;
/**
* @var Generator
*/
private $queryGenerator;
/**
* Constructor
*
* @param FieldDataConverterFactory $fieldDataConverterFactory
* @param QueryModifierFactory $queryModifierFactory
* @param Generator $queryGenerator
*/
public function __construct(
FieldDataConverterFactory $fieldDataConverterFactory,
QueryModifierFactory $queryModifierFactory,
Generator $queryGenerator
) {
$this->fieldDataConverterFactory = $fieldDataConverterFactory;
$this->queryModifierFactory = $queryModifierFactory;
$this->queryGenerator = $queryGenerator;
}
/**
* @inheritDoc
*/
public function upgrade(
ModuleDataSetupInterface $setup,
ModuleContextInterface $context
) {
if (version_compare($context->getVersion(), '2.0.1', '<')) {
$this->convertSerializedDataToJson($setup);
}
}
/**
* Upgrade to version 2.0.1, convert data for the sales_order_item.product_options and quote_item_option.value
* from serialized to JSON format
*
* @param ModuleDataSetupInterface $setup
* @return void
*/
private function convertSerializedDataToJson(ModuleDataSetupInterface $setup)
{
// Upgrade logic here
}
}
Step 2: Check that the module exists
Any module can replace another module in Magento. If your extension stores data in the tables of another module or it serializes or unserializes data stored in core modules, make sure the module exists and is active before executing the upgrade logic.
Use the \Magento\Framework\Module\Manager
class to check the status of the module your extension depends on.
Add this dependency in the constructor of your upgrade script class:
1
2
3
if ($this->moduleManager->isEnabled('Magento_Sales')) {
// Upgrade logic goes here
}
Step 3: Write the conversion logic
The conversion logic in your script depends on how your extension stores the serialized data.
If your extension stores serialized data in different ways, you will need to use different conversion methods.
Step 3a: Convert data in a column for all rows
Use a FieldDataConverterFactory
to create a FieldDataConverter
instance with the appropriate data converter.
Convert data for a column in a table using:
1
2
3
4
5
6
7
8
$fieldDataConverter = $this->fieldDataConverterFactory->create(SerializedToJson::class);
$fieldDataConverter->convert(
$setup->getConnection(),
$setup->getTable('my_table'),
'entity_id',
'field'
);
Step 3b: Convert data in specific rows for a field
option_id | code | value |
---|---|---|
1 | my_option |
a:1:{s:3:"foo";s:3:"bar";} |
2 | another_option |
<non-serialized string> |
3 | my_option |
a:1:{s:3:"foo";s:3:"bar";} |
If you need to convert specific rows in the column, you can use a query modifier to update values using a condition.
The following code sample upgrades the data for options in the value
column in the quote_item_option
table with the code my_option
.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
use Magento\Framework\DB\DataConverter\SerializedToJson;
$fieldDataConverter = $this->fieldDataConverterFactory->create(
SerializedToJson::class
);
// Convert data for the option with static name in quote_item_option.value
$queryModifier = $this->queryModifierFactory->create(
'in',
[
'values' => [
'code' => [
'my_option'
]
]
]
);
$fieldDataConverter->convert(
$setup->getConnection(),
$setup->getTable('quote_item_option'),
'option_id',
'value',
$queryModifier
);
Use values from another table in the condition
The following tables show how the type
and option_id
columns from the catalog_product_option
table form the unique code
value for custom options in the quote_item_option
table.
catalog_product_option
table
option_id | product_id | type |
---|---|---|
1001 | 1 | my_custom_option |
1002 | 2 | my_custom_option |
1003 | 5 | my_custom_option |
quote_item_option
table
option_id | code | value |
---|---|---|
1 | my_custom_option_1001 | a:1:{s:3:"foo";s:3:"bar";} |
2 | another_option | <non-serialized string> |
3 | my_custom_option_1002 | a:1:{s:3:"foo";s:3:"bar";} |
4 | my_custom_option_1003 | a:1:{s:3:"foo";s:3:"bar";} |
To update custom options data in the quote_item_option
table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
use Magento\Framework\DB\DataConverter\SerializedToJson;
$fieldDataConverter = $this->fieldDataConverterFactory->create(
SerializedToJson::class
);
// Convert data for the option with dynamic name in quote_item_option.value
$select = $setup->getConnection()
->select()
->from(
$setup->getTable('catalog_product_option'),
['option_id']
)
->where('type = ?', 'my_custom_option');
$iterator = $this->queryGenerator->generate('option_id', $select);
foreach ($iterator as $selectByRange) {
$codes = $setup->getConnection()->fetchCol($selectByRange);
$codes = array_map(
function ($id) {
return 'my_custom_option_' . $id;
},
$codes
);
$queryModifier = $this->queryModifierFactory->create(
'in',
[
'values' => [
'code' => $codes
]
]
);
$fieldDataConverter->convert(
$setup->getConnection(),
$setup->getTable('quote_item_option'),
'option_id',
'value',
$queryModifier
);
}
Step 3c: Convert nested serialized data
If your module uses nested serialized data in the database, create a custom data converter to hold the logic for converting the data.
The following example is a custom data converter class that converts data in the product_options
column in the sales_order_item
table.
This field contains nested serialized data that needs conversion.
Since you cannot assume the format of the data when initially converted, the following example also checks the format and uses the appropriate methods to unserialize and serialize the data using the original format.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
<?php
/**
* Copyright © Magento, Inc. All rights reserved.
* See COPYING.txt for license details.
*/
namespace Magento\CustomModule\Setup;
use Magento\Framework\DB\DataConverter\DataConverterInterface;
use Magento\Framework\Serialize\Serializer\Json;
use Magento\Framework\Serialize\Serializer\Serialize;
/**
* Serializer used to convert data in product_options field
*/
class SerializedToJsonDataConverter implements DataConverterInterface
{
/**
* @var Serialize
*/
private $serialize;
/**
* @var Json
*/
private $json;
/**
* Constructor
*
* @param Serialize $serialize
* @param Json $json
*/
public function __construct(
Serialize $serialize,
Json $json
) {
$this->serialize = $serialize;
$this->json = $json;
}
/**
* Convert from serialized to JSON format
*
* @param string $value
* @return string
*/
public function convert($value)
{
$isSerialized = $this->isSerialized($value);
$unserialized = $isSerialized
? $this->serialize->unserialize($value)
: $this->json->unserialize($value);
if (isset($unserialized['options'])) {
foreach ($unserialized['options'] as $key => $option) {
if ($option['option_type'] === 'my_custom_option') {
$unserialized['options'][$key]['option_value'] = $this->json->serialize(
$this->serialize->unserialize(
$option['option_value']
)
);
}
}
}
if (isset($unserialized['my_option'])) {
$unserialized['my_option'] = $this->json->serialize(
$this->serialize->unserialize(
$unserialized['my_option']
)
);
}
return $isSerialized
? $this->serialize->serialize($unserialized)
: $this->json->serialize($unserialized);
}
/**
* Check if value is serialized string
*
* @param string $value
* @return boolean
*/
private function isSerialized($value)
{
return (boolean) preg_match('/^((s|i|d|b|a|O|C):|N;)/', $value);
}
}
After creating your custom data converter class, use the FieldDataConverterFactory
to create a FieldDataConverter
instance with your custom converter:
1
2
3
4
5
6
7
8
9
10
11
12
use Magento\CustomModule\Setup\SerializedToJsonDataConverter;
// Convert options in sales_order_item.product_options
$fieldDataConverter = $this->fieldDataConverterFactory->create(
SerializedToJsonDataConverter::class
);
$fieldDataConverter->convert(
$setup->getConnection(),
$setup->getTable('sales_order_item'),
'item_id',
'product_options'
);
Step 3d: Convert data in a multi-database setup
Adobe Commerce supports storing Quote, Sales, and Inventory data in separate databases. Use the specific connections for each of these modules to update your extension’s stored data for the entities of these modules.
The following code sample obtains the Sales module connection and uses it during data update.
1
2
3
4
5
6
7
8
9
10
11
use Magento\Sales\Setup\SalesSetupFactory;
/** SalesSetupFactory $salesSetup */
$salesSetup = $this->salesSetupFactory->create(['setup' => $setup]);
$fieldDataConverter->convert(
$salesSetup->getConnection(),
$salesSetup->getTable('sales_order_item'),
'item_id',
'product_options'
);
Step 3e: Convert data from multiple fields
Use the \Magento\Framework\DB\AggregatedFieldDataConverter
class to update multiple files instead of \Magento\Framework\DB\FieldDataConverter
.
The following code sample updates two fields in different tables taking into account setup version of the module.
It is possible to aggregate fields for the same connection only. If it is necessary to use multiple connections in one setup script, multiple calls to \Magento\Framework\DB\AggregatedFieldDataConverter::convert()
must be made.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
use Magento\Sales\Setup\SalesSetupFactory;
/** SalesSetupFactory $salesSetup */
$salesSetup = $this->salesSetupFactory->create(['setup' => $setup]);
$fieldsToUpdate = [
new FieldToConvert(
SerializedToJson::class,
$salesSetup->getTable('sales_invoice_item'),
'entity_id',
'tax_ratio'
),
];
if (version_compare($setupVersion, '2.0.5', '<')) {
$fieldsToUpdate[] = new FieldToConvert(
SerializedDataConverter::class,
$salesSetup->getTable('sales_order_item'),
'item_id',
'product_options'
);
}
$this->aggregatedFieldConverter->convert($fieldsToUpdate, $salesSetup->getConnection());