neverything · February 25, 2025 06:13 · Feb 25, 2025 · Feb 25, 2025
diff --git a/MigrateDatabaseCommand.php b/MigrateDatabaseCommand.php
@@ -0,0 +1,151 @@
+<?php
+
+namespace App\Console\Commands;
+
+use Illuminate\Console\Command;
+use Illuminate\Support\Arr;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Str;
+use Symfony\Component\Console\Helper\ProgressBar;
+
+class MigrateDatabaseCommand extends Command
+{
+    protected $signature = 'app:migrate-database {source} {destination} {table} {--chunk=50} {--from=}';
+
+    private ProgressBar $bar;
+
+    public function handle(): void
+    {
+        $source = $this->argument('source');
+        $destination = $this->argument('destination');
+        $table = $this->argument('table');
+        $chunkSize = $this->option('chunk');
+
+        $this->info("Importing table {$table}");
+
+        // Get the primary key of the table
+        $primaryKey = $this->getPrimaryKey($source, $table);
+
+        $query = DB::connection($source)->table($table);
+
+        if ($primaryKey && $this->option('from')) {
+            $query->where($primaryKey, '>', $this->option('from'));
+        }
+
+        $total = $query->count();
+        $this->bar = $this->output->createProgressBar($total);
+
+        $this->bar->start();
+
+        if ($primaryKey) {
+            // Use chunkById if table has a primary key
+            $this->migrateWithPrimaryKey($source, $destination, $table, $primaryKey, $chunkSize);
+        } else {
+            // Use regular chunk if no primary key
+            $this->migrateWithoutPrimaryKey($source, $destination, $table, chunkSize: -1);
+        }
+
+        // Update sequence if it's a PostgreSQL destination and table has an integer id
+        if ($this->shouldUpdateSequence($destination, $table)) {
+            $this->newLine()->info("Updating sequence for {$table}");
+            DB::connection($destination)->statement("SELECT setval('{$table}_id_seq', (SELECT MAX(id) FROM {$table}))");
+        }
+
+        $this->bar->finish();
+    }
+
+    protected function getPrimaryKey(string $connection, string $table): ?string
+    {
+        $schema = DB::connection($connection)->getSchemaBuilder();
+        $columns = $schema->getColumnListing($table);
+
+        // Check if 'id' exists as it's Laravel's default primary key
+        if (in_array('id', $columns)) {
+            return 'id';
+        }
+
+        // For MySQL/PostgreSQL, we can query the information schema
+        $driver = DB::connection($connection)->getDriverName();
+
+        if ($driver === 'mysql') {
+            $result = DB::connection($connection)
+                ->select("SHOW KEYS FROM {$table} WHERE Key_name = 'PRIMARY'");
+
+            return $result[0]->Column_name ?? null;
+        }
+
+        if ($driver === 'pgsql') {
+            $result = DB::connection($connection)->select("
+                SELECT a.attname
+                FROM pg_index i
+                JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
+                WHERE i.indrelid = '{$table}'::regclass
+                AND i.indisprimary
+            ");
+
+            return $result[0]->attname ?? null;
+        }
+
+        return null;
+    }
+
+    protected function migrateWithPrimaryKey(string $source, string $destination, string $table, string $primaryKey, int $chunkSize): void
+    {
+        DB::connection($source)
+            ->table($table)
+            ->when($this->option('from'), fn ($query) => $query->where($primaryKey, '>', $this->option('from')))
+            ->orderBy($primaryKey)
+            ->chunk($chunkSize, function ($sourceRows) use ($table, $source, $destination, $primaryKey) {
+                $sourceIds = Arr::pluck($sourceRows, $primaryKey);
+                $destinationRows = DB::connection($destination)
+                    ->table($table)
+                    ->whereIn($primaryKey, $sourceIds)
+                    ->get()
+                    ->pluck($primaryKey)
+                    ->toArray();
+
+                $missingIds = array_diff($sourceIds, $destinationRows);
+
+                if (count($missingIds)) {
+                    $missing = DB::connection($source)
+                        ->table($table)
+                        ->whereIn($primaryKey, $missingIds)
+                        ->get();
+
+                    DB::connection($destination)
+                        ->table($table)
+                        ->insert($missing->map(fn ($row) => (array) $row)->toArray());
+                }
+
+                $this->bar->advance(count($sourceRows));
+            });
+    }
+
+    protected function migrateWithoutPrimaryKey(string $source, string $destination, string $table, int $chunkSize): void
+    {
+        // Get first column to use as ordering, because chunk needs to be ordered
+        $firstColumn = DB::connection($source)
+            ->getSchemaBuilder()
+            ->getColumnListing($table)[0];
+
+        DB::connection($source)
+            ->table($table)
+            ->orderBy($firstColumn)
+            ->chunk($chunkSize, function ($sourceRows) use ($table, $destination) {
+                DB::connection($destination)
+                    ->table($table)
+                    ->insert($sourceRows->map(fn ($row) => (array) $row)->toArray());
+
+                $this->bar->advance(count($sourceRows));
+            });
+    }
+
+    protected function shouldUpdateSequence(string $connection, string $table): bool
+    {
+        $schema = DB::connection($connection)->getSchemaBuilder();
+
+        return DB::connection($connection)->getDriverName() === 'pgsql'
+            && $schema->hasColumn($table, 'id')
+            && Str::of($schema->getColumnType($table, 'id'))->contains('int');
+    }
+}
No results found