database-transportor

数据库数据迁移工具。重构数据库后，将旧数据迁移至新库时使用。 This tool would be useful if you want to carry the history data after rebuild database.

目录

• 安装

• 命令

• 基础迁移

  • 一对一迁移
  • 带默认值的迁移 - default
  • 迁移前的预处理 - function
  • 带查询条件的迁移 - extra_conditions

• 引用迁移

  • 单引用迁移 - refer
  • 单引用迁移-引用多字段 - refer-mulitfield
  • 多引用迁移 - refers

• 多对多迁移
  
  

安装

安装：composer require cherrylu/database-transportor

然后在config\app.php的providers中添加CherryLu\DatabaseTransportor\DatabaseTransportorServiceProvider::class

命令

创建迁移文件命令：php artisan make:transportor 文件名(如UserTransportor)

该命令将会在database\seeders\transportors路径下创建对应文件，在初始使用时也会同时创建Transportor.php文件

执行迁移命令：php artisan transport [--class=]

未指定迁移文件时，将会迁移Transportor中handle方法传入的类

使用

1.基础迁移

1.1一对一迁移

  $maps = [
	// new_users_map 默认的新表名称
    "new_users_map" => [
      // 指定的新表名称，若没有设定target_table的值，会认为该数组的键名'new_users_map'为新表的表名
      "target_table" => "new_users",
      // 旧表名称，即数据来源表的表名
      "original_table" => "old_users",
      // 新旧表字段映射关系 '新表字段' => '旧表字段'
      "columns" => [
        "id" => "id",
        "username" => "name",
      ],
    ]
  ];
	
	// database.php 中设的 connections 设定
  $old_database = "pgsql";
  $new_database = "mysql";
  
  $transportor = new \cherrylu\transportor\DBT($maps, $new_database, $old_database);
  
  $transportor->setChunk(5000);// 设定每次迁移的数据量 若不设置默认为2000
  
  $transportor->doTransport();// 执行迁移

1.2带默认值的迁移

  $maps = [
    "new_users" => [
      "original_table" => "old_users",
      "columns" => [
        "id" => "id",
        "username" => "name",
        "created_at" => ["default" => \Carbon\Carbon::now()]
      ],
    ]
  ];

当旧表字段查询结果为NULL，或处理后结果为NULL时将填入 default设定的值

1.3迁移前的预处理

  $maps = [
    "new_users" => [
      "original_table" => "old_users",
      "columns" => [
        "id" => "id",
        "username" => [
          "original" => "name",
          "function" => function ($data) {
            return $data->name . "-" .$data->id;
          }
        ]
      ],
    ]
  ];

1.4带查询条件的迁移

  $maps = [
    "new_users" => [
      "original_table" => "old_users",
      "extra_conditions" => [
        ["name", "<>", "王五"],
        // or 定义为字符串时，即执行原生的查询
        " `name` <> '王五' ",
      ],
      "columns" => [
      	"id" => "id",
        "username" => "name",
      ],
    ]
  ];

该工具类所有查询操作符如下：

	case "="
	case ">"
	case "<"
	case "<>"
	case "!="
	case "like"
	case "notlike"
	case "notin"
	case "in"
	case "between"
	case "notbetween"

2.引用迁移

2.1 单引用迁移 - refer

新表 new_roles:

  $maps = [
    "new_users" => [
      "original_table" => "old_users",
      "columns" => [
        "id"  => "id",
        "username" => "name",
        "temp_role_name" => [
          "original" => "role_name",
          "delete_after_transport" => true,
          "rebuild" => true,
        ],
        "role_id" => [
          "refer" => [ // 单引用
            "search_source" => "target", // 如果数据源为旧表时，用original即可
            "search_table" => "new_roles",
            "search_column" => "role_name",
            "according_column" => "temp_role_name",
            "wanted_column" => "id",

            // 未定义此项时，直接迁移 id 的原值。$data是固定格式，为 "wanted_column" 定义的对应引用字段，此处为 id
            "pre_format" => function ($data) {
                // 查询前去除前后的无关字符
                return trim($data->role_name);
            },
          ]
        ],
        "default" => 0,
      ],
    ]
  ];

2.2 单引用迁移-引用多字段 - refer-mulitfield

旧表 records:

  $maps = [
    "new_users" => [
      "original_table" => "old_users",
      "columns" => [
        "id"  => "id",
        "temp_total" => [
          "original" => "total",
          "delete_after_transport" => true,
          "rebuild" => true,
        ],
        "temp_create_date" => [
          "original" => "create_date",
          "delete_after_transport" => true,
          "rebuild" => true,
        ],
        "role_id" => [
          "refer" => [ // 单引用
            "according_column" => ["temp_total", "temp_create_date"],
            "search_source" => "original",
            "search_table" => "records",
            "search_column" => ["amount", "created_at"],
            "wanted_column" => "bill_id",
          ]
        ],
        "default" => 0,
      ],
    ]
  ];

2.3 多引用迁移 - refers

旧表 accounts:

$maps = [
  "new_users" => [
    "original_table" => "old_users",
    "columns" => [
      "id"  => "id",
      "username" => "name",
      "amount" => [
        "refers" => [
          "according_column" => "id",
          "search_source" => "original",
          "search_table" => "accounts",
          "search_column" => "user_id",
          "processor" => function ($data) {
            $amount = 0;
            foreach ( $data as $datum ) {
              $amount += $datum->amount;
            }
            return $amount;
          }
        ]
      ],
    ]
  ]
];

3.多对多迁移

新表 types

此处只给出中间表material_types的$maps,new_materials请参照上文

$maps = [
  "material_types"     => [
		"original_table" => null,
		"columns" => [
			"material_id" => null,
			"type_id"     => null,
		],
		"middle"  => [
			"one"  => [
				"refer_table"      => "new_materials",
				"wanted_column"    => "id",
				"fill_column"      => "material_id",
				"according_column" => "type_temp", // 定义 new_materials 的 maps 时，将原来的 type_id 暂存为 type_temp
				"pre_format"       => function ($data) { return explode(',', trim($data)); }
			],
			"many" => [
				"fill_column"      => "type_id",
				"refer_table"      => "types",
				"wanted_column"    => "id",
				"search_column"    => "id",
				"search_method"    => "in",
			],
		],
	]
];