yousign/safe-migrations

确保您的迁移安全

维护者

详细信息

github.com/Yousign/safe-migrations

来源

问题

安装: 8,165

依赖项: 0

建议者: 0

安全: 0

星标: 8

关注者: 10

分支: 0

公开问题: 0

v1.0.6 2024-03-20 14:18 UTC

Requires

Requires (Dev)

MIT 1e30f3521c5a384d4c9400b72f8a28057a0d4f73

  • Benoit Galati <benoit.galati.woop@yousign.com>
  • Nicolas Dousson <nicolas.dousson.woop@yousign.com>

This package is auto-updated.

Last update: 2024-09-20 15:31:09 UTC

README

确保您的迁移安全

🪄 特点

  • PG 13+
  • PHP 8.1
  • Doctrine 迁移

🤷 为什么？

因为 SQL 迁移可能在数据库上执行重查询，这可能会降低您的应用程序速度。

⚙️ 配置

*对于 Symfony > 6.x

在您的项目中安装

$ composer req yousign/safe-migrations

在您的 services.yaml 中声明中间件

parameters:
  env(ENABLE_RETRY_LOCK_TIMEOUT): false
  
services:
  Yousign\SafeMigrations\Doctrine\DBAL\Driver\Middleware\RetryLockTimeoutMiddleware:
    $isEnabled: '%env(bool:ENABLE_RETRY_LOCK_TIMEOUT)%'

创建迁移模板 migration.php.tpl

<?php

declare(strict_types=1);

namespace <namespace>;

use Doctrine\DBAL\Schema\Schema;
use Yousign\SafeMigrations\Doctrine\Migration;

class <className> extends Migration
{
    public function up(Schema $schema): void
    {
<up>
    }
}

在 doctrine_migrations.yaml 中将此模板设置为迁移的默认值

doctrine_migrations:
  custom_template: "%kernel.project_dir%/migrations/migration.php.tpl"

通过在 .env<.environment> 中的环境变量中启用锁定重试

ENABLE_RETRY_LOCK_TIMEOUT=true

这就完成了 ☕

▶️ 使用方法

迁移类

当您生成新的迁移时，这个类将扩展库中的 Migration 类，该类公开以下安全方法。

这些方法中的每一个都将生成正确的 SQL 请求集，以使请求的查询安全。

创建表 
$this->createTable(table: 'test', columnDefinitions: [
    'id UUID NOT NULL', 
    'PRIMARY KEY(id)',
])
添加外键 
$this->addForeignKey(table: 'address', name: 'fk_address_contact', column: 'contact', referenceTable: 'contact', referenceColumn: 'id', options: 'ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE')
重命名约束 
$this->renameConstraint(table: 'address', from: 'id_pkey', to: 'pkey_id')
对列进行注释 
$this->commentOnColumn(table: 'address', name: 'name', comment: null)
添加索引

注意：在表上添加索引将执行对表中所有列的 "analyze"，以更新统计信息

$this->addIndex(name: 'idx_contact_email', table: 'contact', columns: ['email'], unique: false, usingMethod: 'GIN', where: 'country = "France"')
删除索引 
$this->dropIndex(name: 'idx_contact_email')
重命名索引 
$this->renameIndex(from: 'idx_email_signer', to: 'idx_signer_email')
添加列 
$this->addColumn(table: 'contact', name: 'mobile', type: 'text', defaultValue: null, nullable: true)
删除列 
$this->dropColumn(table: 'contact', name: 'landline')
设置列的默认值 
$this->setDefaultOnColumn(table: 'contact', name: 'email', value: "'noreply@undefined.org'")
删除列的默认值 
$this->dropDefaultOnColumn(table: 'contact', name: 'email')
设置列可空 
$this->setColumnNullable(table: 'contact', name: 'email')
设置列不可空 
$this->setColumnNotNullable(table: 'contact', name: 'email')

迁移执行

如果在执行 Doctrine 迁移时出现锁定，迁移将抛出 DriverException (Doctrine\DBAL\Driver\Exception)。

如果 SQLSTATE 值为 55P03 (lock_not_available)，如果查询不成功，则将重试 3 次，每次间隔 10 秒，然后抛出抛出的异常。

您将获得以下输出

3 次重试后失败 
$ bin/symfony console d:m:m

[notice] Migrating up to DoctrineMigrations\Version20231224200000
09:30:38 WARNING   [app] (1/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
09:30:51 WARNING   [app] (2/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
09:31:04 WARNING   [app] (3/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
[error] Migration DoctrineMigrations\Version20231224200000 failed during Execution. Error: "An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout"
09:31:17 CRITICAL  [console] Error thrown while running command "'d:m:m'". Message: "An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout" - An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout ["exception" => Doctrine\DBAL\Exception\DriverException^ { …},"command" => "'d:m:m'","message" => "An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout"]
1 次重试后成功 
bin/symfony console d:m:m

[notice] Migrating up to DoctrineMigrations\Version20231224200000
09:28:54 WARNING   [app] (1/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
[notice] finished in 15446.1ms, used 38.5M memory, 2 migrations executed, 13 sql queries

[OK] Successfully migrated to version: DoctrineMigrations\Version20231224200000

📋 常见问题解答

它与迁移捆绑包兼容吗？

当然，这个库与 doctrine/doctrine-migrations-bundle 没有兼容性问题。

🔗 参考

🤝 贡献

请阅读CONTRIBUTING.md，了解我们的行为准则以及向我们提交拉取请求的流程。

在编写您的修复/功能之后，您可以运行以下命令以确保一切仍然正常。

# Install dev dependencies
$ make vendor

# Running tests and quality tools locally
$ make tests

作者