如何在 magento2 中使用 db_schema.xml 为实体添加 UUID（在代码和 mysql 中进行验证）？

问：

我想使用 db_schema.xml 在 Magento 2 中实现 UUID 而不是简单的增量 ID。我的目标是让新的数据库行在添加时自动生成 UUID。同样，当使用存储库以编程方式创建实体并将其保存到数据库时，应自动生成 UUID。此实现还应包括 UUID 验证。

我创建了模块。让我们假设 'Vendor_Module'：

文件：

app/code/Vendor/Module/registration.php:

<?php

declare(strict_types=1);

\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Vendor_Module',
    __DIR__
);

app/code/Vendor/Module/etc/module.xml:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Vendor_Module" setup_version="1.0.0" />
</config>

app/code/Vendor/Module/etc/db_schema.xml:

<?xml version="1.0" ?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="module_example" engine="innodb" resource="default" comment="Vendor Module Example">
        <column name="entity_id" nullable="false" xsi:type="varchar" length="36" default="NULL" comment="Entity ID"/>

        <column name="name" nullable="false" xsi:type="text" comment="Name of an entity"/>

        <column name="created_at" nullable="false" on_update="false" default="CURRENT_TIMESTAMP" xsi:type="timestamp"
            comment="Created At"/>

        <column name="updated_at" nullable="false" on_update="true" default="CURRENT_TIMESTAMP" xsi:type="timestamp"
            comment="Updated At"/>

        <constraint referenceId="PRIMARY" xsi:type="primary">
            <column name="entity_id"/>
        </constraint>
    </table>
</schema>

app/code/Vendor/Module/etc/db_schema_whitelist.json:

{
    "module_example": {
        "column": {
            "entity_id": true,
            "name": true,
            "created_at": true,
            "updated_at": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

app/code/Vendor/Module/Api/Data/ModuleExampleInterface.php:

<?php

declare(strict_types=1);

namespace Vendor\Module\Api\Data;


use Magento\Framework\Api\ExtensibleDataInterface;
use Magento\Framework\Exception\LocalizedException;

interface ModuleExampleInterface extends ExtensibleDataInterface
{
    public const ENTITY_ID = 'entity_id';

    public const NAME = 'name';

    public const CREATED_AT = 'created_at';

    public const UPDATED_AT = 'updated_at';

    /**
     * @return string|null
     * @throws LocalizedException
     */
    public function getEntityId();

    /**
     * @param string|null $entityId
     * @return ModuleExampleInterface
     * @throws LocalizedException
     */
    public function setEntityId($entityId): self;

    /**
     * @return string
     */
    public function getName(): string;

    /**
     * @return ModuleExampleInterface
     */
    public function setName(string $name): self;

    /**
     * @return string|null
     */
    public function getCreatedAt(): ?string;

    /**
     * @return ModuleExampleInterface
     */
    public function setCreatedAt(?string $createdAt): self;

    /**
     * @return string|null
     */
    public function getUpdatedAt(): ?string;

    /**
     * @return ModuleExampleInterface
     */
    public function setUpdatedAt(?string $updatedAt): self;

    /**
     * @return \Vendor\Module\Api\Data\ModuleExampleExtensionInterface|null
     */
    public function getExtensionAttributes();

    /**
     * @param \Vendor\Module\Api\Data\ModuleExampleExtensionInterface $extensionAttributes
     * @return $this
     */
    public function setExtensionAttributes(
        \Vendor\Module\Api\Data\ModuleExampleExtensionInterface $extensionAttributes
    );
}

app/code/Vendor/Module/Model/Data/ModuleExample.php:

<?php

declare(strict_types=1);

namespace Vendor\Module\Model\Data;


use Magento\Framework\Api\AbstractExtensibleObject;
use Vendor\Module\Api\Data\ModuleExampleInterface;
use Vendor\Module\Helper\Data;

class ModuleExample extends AbstractExtensibleObject implements ModuleExampleInterface
{
    /**
     * @inheritDoc
     */
    public function getEntityId()
    {
        $entityId = $this->_get(self::ENTITY_ID);
        if (!empty($entityId)) {
            return Data::checkUUID((string)$entityId);
        }
        return null;
    }

    /**
     * @inheritDoc
     */
    public function setEntityId($entityId): ModuleExampleInterface
    {
        if (!empty($entityId)) {
            $entityId = Data::checkUUID((string)$entityId);
        }
        return $this->setData(self::ENTITY_ID, $entityId);
    }

    /**
     * @inheritDoc
     */
    public function getName(): string
    {
        return (string)$this->_get(self::NAME);
    }

    /**
     * @inheritDoc
     */
    public function setName(string $name): ModuleExampleInterface
    {
        return $this->setData(self::NAME, $name);
    }

    /**
     * @inheritDoc
     */
    public function getCreatedAt(): ?string
    {
        return $this->_get(self::CREATED_AT);
    }

    /**
     * @inheritDoc
     */
    public function setCreatedAt(?string $createdAt): ModuleExampleInterface
    {
        return $this->setData(self::CREATED_AT, $createdAt);
    }

    /**
     * @inheritDoc
     */
    public function getUpdatedAt(): ?string
    {
        return $this->_get(self::UPDATED_AT);
    }

    /**
     * @inheritDoc
     */
    public function setUpdatedAt(?string $updatedAt): ModuleExampleInterface
    {
        return $this->setData(self::UPDATED_AT, $updatedAt);
    }

    /**
     * @inheritDoc
     */
    public function getExtensionAttributes()
    {
        return $this->_getExtensionAttributes();
    }

    /**
     * @inheritDoc
     */
    public function setExtensionAttributes(
        \Vendor\Module\Api\Data\ModuleExampleExtensionInterface $extensionAttributes
    ) {
        return $this->_setExtensionAttributes($extensionAttributes);
    }
}

app/code/Vendor/Module/etc/di.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <!-- CRUD preferences -->
    <preference for="Vendor\Module\Api\Data\ModuleExampleInterface"
        type="Vendor\Module\Model\Data\ModuleExample"/>
</config>

app/code/Vendor/Module/Helper/Data.php:

<?php

declare(strict_types=1);

namespace Vendor\Module\Helper;


use Magento\Framework\App\Helper\AbstractHelper;
use Magento\Framework\Exception\LocalizedException;
use Ramsey\Uuid\Uuid;
use Ramsey\Uuid\Validator\ValidatorInterface;

class Data extends AbstractHelper
{
    /**
     * @throws LocalizedException
     */
    public static function checkUUID(string $uuid): string
    {
        /** @var ValidatorInterface $uuidValidator */
        $uuidValidator = Uuid::getFactory()->getValidator();
        if (!$uuidValidator->validate($uuid)) {
            throw new LocalizedException(__('It is not a valid uuid (\'%1\').', $uuid));
        }
        return $uuid;
    }
}

app/code/Vendor/Module/Model/ModuleExample.php

<?php

declare(strict_types=1);

namespace Vendor\Module\Model;


use Magento\Framework\Api\DataObjectHelper;
use Magento\Framework\Model\AbstractModel;
use Magento\Framework\Model\Context;
use Magento\Framework\Registry;
use Vendor\Module\Api\Data\ModuleExampleInterface;
use Vendor\Module\Api\Data\ModuleExampleInterfaceFactory;
use Vendor\Module\Model\ResourceModel\ModuleExample\Collection;

class ModuleExample extends AbstractModel
{
    /**
     * @var ModuleExampleInterfaceFactory
     */
    protected $moduleExampleDataFactory;

    /**
     * @var DataObjectHelper
     */
    protected $dataObjectHelper;

    /**
     * @inheritDoc
     */
    protected $_eventPrefix = 'module_example';

    /**
     * @inheritDoc
     */
    public function __construct(
        ModuleExmpleInterfaceFactory $moduleExampleDataFactory,
        DataObjectHelper $dataObjectHelper,
        Context $context,
        Registry $registry,
        ResourceModel\ModuleExample $resource,
        Collection $resourceCollection,
        array $data = []
    ) {
        $this->moduleExampleDataFactory = $moduleExampleDataFactory;
        $this->dataObjectHelper = $dataObjectHelper;
        parent::__construct($context, $registry, $resource, $resourceCollection, $data);
    }

    public function getDataModel(): ModuleExampleInterface
    {
        $moduleExampleData = $this->getData();
        $moduleExampleDataObject = $this->moduleExampleDataFactory->create();
        $this->dataObjectHelper->populateWithArray(
            $moduleExampleDataObject,
            $moduleExampleData,
            ModuleExample::class
        );
        return $moduleExampleDataObject;
    }
}

app/code/Vendor/Module/Model/ResourceModel/ModuleExample/Collection.php

<?php

declare(strict_types=1);

namespace Vendor\Module\Model\ResourceModel\ModuleExample;


use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use Vendor\Module\Model\ModuleExample;
use Vendor\Module\Model\ResourceModel;

class Collection extends AbstractCollection
{
    /**
     * @var string
     */
    protected $_idFieldName = 'entity_id';

    /**
     * @inheritDoc
     */
    protected function _construct()
    {
        $this->_init(
            ModuleExample::class,
            ResourceModel\ModuleExample::class
        );
    }
}

app/code/Vendor/Module/Model/ResourceModel/ModuleExample.php

<?php

declare(strict_types=1);

namespace Vendor\Module\Model\ResourceModel;


use Magento\Framework\Model\AbstractModel;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Ramsey\Uuid\Uuid;
use Vendor\Module\Api\Data\ModuleExampleInterface;
use Vendor\Module\Model\Data\ModuleExample as ModuleExampleDataModel;

class ModuleExample extends AbstractDb
{
    /**
     * @inheritDoc
     */
    protected function _construct()
    {
        $this->_init('module_example', ModuleExampleInterface::ENTITY_ID);
        $this->_isPkAutoIncrement = false;
    }

    /**
     * @inheritDoc
     */
    protected function _prepareDataForSave(AbstractModel $object)
    {
        if ($object->isObjectNew()) {
            $newEntityId = Uuid::uuid4()->toString();
            /** @var ModuleExampleDataModel $object */
            $object->setEntityId($newEntityId);
        }
        return parent::_prepareDataForSave($object);
    }
}

app/code/Vendor/Module/Setup/UpgradeSchema.php

<?php

declare(strict_types=1);

namespace Vendor\Module\Setup;

use Magento\Framework\DB\Ddl\Trigger;
use Magento\Framework\DB\Ddl\TriggerFactory;
use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Vendor\Module\Api\Data\ModuleExampleInterface;

class UpgradeSchema implements UpgradeSchemaInterface
{
    /**
     * @var TriggerFactory
     */
    private $triggerFactory;

    /**
     */
    public function __construct(TriggerFactory $triggerFactory)
    {
        $this->triggerFactory = $triggerFactory;
    }

    /**
     * {@inheritDoc}
     * @see https://magento.stackexchange.com/questions/152753/how-to-create-mysql-triggers-through-setup-script#answer-241716
     * @see https://myway-sql.com/mysql_functions/IS_UUID
     * @throws \Zend_Db_Exception
     */
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) {
        $installer = $setup;
        $installer->startSetup();
        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            $setup->getConnection()->query('DROP FUNCTION IF EXISTS IS_UUID;');
            $setup->getConnection()->multiQuery("CREATE FUNCTION IS_UUID(id varchar(128))
    RETURNS integer

BEGIN

    IF id is null THEN
        RETURN null;
    ELSE
        IF char_length(id)=38 THEN
            IF substr(id, 1, 1)='{' AND substr(id, 38, 1)='}' THEN
                SET id=substr(id, 2, 36);
            END IF;
        END IF;

        IF char_length(id)=36 THEN
            IF substr(id, 9, 1)='-' AND substr(id, 14, 1)='-' AND
               substr(id, 19, 1)='-' AND substr(id, 24, 1)='-' THEN
                SET id=REPLACE(id,'-','');
            END IF;
        END IF;
        IF char_length(id)=32 THEN
            IF unhex(id) is null THEN RETURN 0; ELSE RETURN 1; END IF;
        ELSE
            RETURN 0;
        END IF;
    END IF;

END;");
        }
        if (version_compare($context->getVersion(), '1.0.2', '<')) {
            $id = ModuleExampleInterface::ENTITY_ID;
            $table = $setup->getTable('module_example');
            $trigger1 = $this->triggerFactory->create()
                ->setName('validate_uuid')
                ->setTime(Trigger::TIME_BEFORE)
                ->setEvent(\Magento\Framework\DB\Ddl\Trigger::EVENT_UPDATE)
                ->setTable($table);
            $trigger1->addStatement(<<<EOT
IF IS_UUID(NEW.{$id}) = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The value is not UUID.' ; END IF ;
EOT);
            $setup->getConnection()->dropTrigger($trigger1->getName());
            $setup->getConnection()->createTrigger($trigger1);

            $trigger2 = $this->triggerFactory->create()
                ->setName('set_default_uuid')
                ->setTime(Trigger::TIME_BEFORE)
                ->setEvent(\Magento\Framework\DB\Ddl\Trigger::EVENT_INSERT)
                ->setTable($table);
            $trigger2->addStatement("SET NEW.{$id} = UUID();");
            $setup->getConnection()->dropTrigger($trigger2->getName());
            $setup->getConnection()->createTrigger($trigger2);
        }
        $installer->endSetup();
    }
}

您知道更简单或更好的任务解决方案吗？先谢谢你。

错误或者我会说不好的做法是使用已弃用的方法。$setup->getConnection()->multiQuery