Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 41
0.00%
0 / 3
 CRAP
0.00%
0 / 1
MigrateDatabase
0.00%
0 / 41
0.00%
0 / 3
 56
0.00%
0 / 1
 handle
0.00%
0 / 7
0.00%
0 / 1
 6
 migrateDatabase
0.00%
0 / 24
0.00%
0 / 1
 12
 dropAllTables
0.00%
0 / 10
0.00%
0 / 1
 6
1<?php
2
3namespace App\Console\Commands;
4
5use Illuminate\Console\Command;
6use Illuminate\Support\Facades\Artisan;
7use Illuminate\Support\Facades\DB;
8use Illuminate\Support\Facades\Log;
9
10class MigrateDatabase extends Command
11{
12    /**
13     * The name and signature of the console command.
14     *
15     * @var string
16     */
17    protected $signature = 'db:migrate {sourceConnection} {targetConnection}';
18
19    /**
20     * The console command description.
21     *
22     * @var string
23     */
24    protected $description = 'Migrate the database from one server to another';
25
26    /**
27     * Execute the console command.
28     */
29    public function handle(): void
30    {
31        ini_set('memory_limit', -1);
32        set_time_limit(0);
33
34        try {
35            // Retrieve the connection names from the command arguments
36            $sourceConnection = $this->argument('sourceConnection');
37            $targetConnection = $this->argument('targetConnection');
38
39            // Call the method to migrate the database
40            $this->migrateDatabase($sourceConnection, $targetConnection);   // code...
41        } catch (\Exception $e) {
42            Log::channel('migration')->error($e->getMessage());
43        }
44
45    }
46
47    private function migrateDatabase($sourceConnection, $targetConnection): void
48    {
49        Log::channel('migration')->info('Get a list of tables from the source connection');
50        $tables = DB::connection($sourceConnection)->select('SHOW TABLES');
51        $databaseName = DB::connection($sourceConnection)->getDatabaseName();
52        $tables = array_map(current(...), $tables);
53
54        Log::channel('migration')->info('Drop all tables in the target connection');
55        $this->dropAllTables($targetConnection);
56
57        Log::channel('migration')->info('Recreate each table and insert data');
58        foreach ($tables as $table) {
59
60            Log::channel('migration')->info("Retrieve the create table statement from the source connection: {$table}");
61            $createTableStmt = DB::connection($sourceConnection)->selectOne("SHOW CREATE TABLE $table")->{'Create Table'};
62
63            Log::channel('migration')->info('Execute the create table statement on the target connection');
64            DB::connection($targetConnection)->statement($createTableStmt);
65
66            Log::channel('migration')->info('Fetch all data from the source table');
67            $data = DB::connection($sourceConnection)->table($table)->get();
68
69            Log::channel('migration')->info('Disable foreign key checks on the target connection');
70            DB::connection($targetConnection)->statement('SET FOREIGN_KEY_CHECKS=0;');
71
72            Log::channel('migration')->info('Insert data into the target table');
73            foreach ($data as $row) {
74                $row = (array) $row;
75                DB::connection($targetConnection)->table($table)->insert($row);
76            }
77
78            Log::channel('migration')->info('Re-enable foreign key checks on the target connection');
79            DB::connection($targetConnection)->statement('SET FOREIGN_KEY_CHECKS=1;');
80        }
81
82        Artisan::call('migrate');
83        Log::channel('migration')->info("Database migration from '$sourceConnection' to '$targetConnection' completed successfully.");
84    }
85
86    private function dropAllTables($connection): void
87    {
88        Log::channel('migration')->info('Retrieve the list of all tables in the target connection');
89        $tables = DB::connection($connection)->select('SHOW TABLES');
90        $tables = array_map(current(...), $tables);
91
92        Log::channel('migration')->info('Disable foreign key checks');
93        DB::connection($connection)->statement('SET FOREIGN_KEY_CHECKS=0;');
94
95        Log::channel('migration')->info('Drop each table');
96        foreach ($tables as $table) {
97            DB::connection($connection)->statement("DROP TABLE IF EXISTS $table");
98        }
99
100        Log::channel('migration')->info('Re-enable foreign key checks');
101        DB::connection($connection)->statement('SET FOREIGN_KEY_CHECKS=1;');
102    }
103}