Passed
Push — master ( 0ddaf0...97ef82 )
by Darko
07:24
created

UpdateNNTmuxDB::handle()   B

Complexity

Conditions 7
Paths 39

Size

Total Lines 48
Code Lines 25

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
eloc 25
dl 0
loc 48
rs 8.5866
c 0
b 0
f 0
cc 7
nc 39
nop 0
1
<?php
2
3
namespace App\Console\Commands;
4
5
use Illuminate\Console\Command;
6
use Illuminate\Support\Facades\DB;
7
use Illuminate\Support\Facades\Schema;
8
9
class UpdateNNTmuxDB extends Command
10
{
11
    /**
12
     * The name and signature of the console command.
13
     */
14
    protected $signature = 'nntmux:db
15
                            {--seed : Run database seeders after migration}
16
                            {--rollback= : Rollback the last N migrations}
17
                            {--check : Check migration status without running}';
18
19
    /**
20
     * The console command description.
21
     */
22
    protected $description = 'Update NNTmux database with enhanced safety and performance';
23
24
    /**
25
     * Execute the console command.
26
     */
27
    public function handle(): int
28
    {
29
        try {
30
            $this->info('🗄️ Starting database update process...');
31
32
            // Check database connection and detect database type
33
            $dbType = $this->checkDatabaseConnection();
34
            if (!$dbType) {
35
                $this->error('Database connection failed');
36
                return Command::FAILURE;
37
            }
38
39
            // Handle rollback if requested
40
            if ($this->option('rollback')) {
41
                return $this->handleRollback();
42
            }
43
44
            // Check migration status if requested
45
            if ($this->option('check')) {
46
                return $this->checkMigrationStatus();
47
            }
48
49
            // Backup database before migration (in production)
50
            if (app()->environment('production')) {
0 ignored issues
show
introduced by
The method environment() does not exist on Illuminate\Container\Container. Are you sure you never get this type here, but always one of the subclasses? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

50
            if (app()->/** @scrutinizer ignore-call */ environment('production')) {
Loading history...
51
                $this->info('📋 Creating database backup...');
52
                $this->createDatabaseBackup($dbType);
53
            }
54
55
            // Run migrations
56
            $this->info('🔄 Running database migrations...');
57
            $this->runMigrations();
58
59
            // Run seeders if requested
60
            if ($this->option('seed')) {
61
                $this->info('🌱 Running database seeders...');
62
                $this->runSeeders();
63
            }
64
65
            // Optimize database
66
            $this->info('⚡ Optimizing database...');
67
            $this->optimizeDatabase($dbType);
68
69
            $this->info('✅ Database update completed successfully');
70
            return Command::SUCCESS;
71
72
        } catch (\Exception $e) {
73
            $this->error('❌ Database update failed: ' . $e->getMessage());
74
            return Command::FAILURE;
75
        }
76
    }
77
78
    /**
79
     * Check database connection and return database type
80
     */
81
    private function checkDatabaseConnection(): ?string
82
    {
83
        try {
84
            DB::connection()->getPdo();
85
86
            $dbConfig = config('database.connections.' . config('database.default'));
87
            $driver = $dbConfig['driver'] ?? null;
88
89
            // Detect actual database type for MySQL-compatible drivers
90
            if (in_array($driver, ['mysql', 'mariadb'])) {
91
                $version = DB::select('SELECT VERSION() as version')[0]->version;
92
93
                if (str_contains(strtolower($version), 'mariadb')) {
94
                    $actualType = 'mariadb';
95
                } else {
96
                    $actualType = 'mysql';
97
                }
98
99
                $this->line("  ✓ Database connection established ($actualType $version)");
100
                return $actualType;
101
            }
102
103
            $this->line("  ✓ Database connection established ($driver)");
104
            return $driver;
105
106
        } catch (\Exception $e) {
107
            $this->error('  ✗ Database connection failed: ' . $e->getMessage());
108
            return null;
109
        }
110
    }
111
112
    /**
113
     * Handle migration rollback
114
     */
115
    private function handleRollback(): int
116
    {
117
        $steps = (int) $this->option('rollback');
118
119
        if ($steps <= 0) {
120
            $this->error('Invalid rollback steps. Must be a positive integer.');
121
            return Command::FAILURE;
122
        }
123
124
        $this->warn("⚠️ Rolling back $steps migrations...");
125
126
        if (!$this->confirm('Are you sure you want to rollback migrations? This may cause data loss.')) {
127
            $this->info('Rollback cancelled');
128
            return Command::SUCCESS;
129
        }
130
131
        $exitCode = $this->call('migrate:rollback', ['--step' => $steps]);
132
133
        if ($exitCode === 0) {
134
            $this->info("✅ Successfully rolled back $steps migrations");
135
        }
136
137
        return $exitCode;
138
    }
139
140
    /**
141
     * Check migration status
142
     */
143
    private function checkMigrationStatus(): int
144
    {
145
        $this->info('📊 Migration Status:');
146
        return $this->call('migrate:status');
147
    }
148
149
    /**
150
     * Create database backup with proper driver detection
151
     */
152
    private function createDatabaseBackup(string $dbType): void
153
    {
154
        try {
155
            $backupPath = storage_path('backups');
156
157
            if (!is_dir($backupPath)) {
158
                mkdir($backupPath, 0755, true);
159
            }
160
161
            $timestamp = now()->format('Y-m-d_H-i-s');
162
            $filename = "nntmux_backup_{$timestamp}.sql";
163
            $fullPath = $backupPath . '/' . $filename;
164
165
            $dbConfig = config('database.connections.' . config('database.default'));
166
167
            if (in_array($dbType, ['mysql', 'mariadb'])) {
168
                $this->createMysqlCompatibleBackup($dbConfig, $fullPath, $dbType);
169
            } elseif ($dbType === 'pgsql') {
170
                $this->createPostgresBackup($dbConfig, $fullPath);
171
            } else {
172
                $this->warn("  ⚠ Backup not supported for database type: $dbType");
173
            }
174
175
        } catch (\Exception $e) {
176
            $this->warn('  ⚠ Backup creation failed: ' . $e->getMessage());
177
        }
178
    }
179
180
    /**
181
     * Create backup for MySQL/MariaDB
182
     */
183
    private function createMysqlCompatibleBackup(array $dbConfig, string $fullPath, string $dbType): void
184
    {
185
        $command = sprintf(
186
            '%s -h%s -P%s -u%s %s %s > %s 2>/dev/null',
187
            $dbType === 'mariadb' ? 'mariadb-dump' : 'mysqldump',
188
            escapeshellarg($dbConfig['host']),
189
            escapeshellarg($dbConfig['port']),
190
            escapeshellarg($dbConfig['username']),
191
            !empty($dbConfig['password']) ? '-p' . escapeshellarg($dbConfig['password']) : '',
192
            escapeshellarg($dbConfig['database']),
193
            escapeshellarg($fullPath)
194
        );
195
196
        exec($command, $output, $returnVar);
197
198
        if ($returnVar === 0 && file_exists($fullPath) && filesize($fullPath) > 0) {
199
            $this->line("  ✓ $dbType backup created: " . basename($fullPath));
200
        } else {
201
            // Fallback to mysqldump if mariadb-dump failed
202
            if ($dbType === 'mariadb') {
203
                $fallbackCommand = str_replace('mariadb-dump', 'mysqldump', $command);
204
                exec($fallbackCommand, $output, $returnVar);
205
206
                if ($returnVar === 0 && file_exists($fullPath) && filesize($fullPath) > 0) {
207
                    $this->line("  ✓ MySQL backup created (fallback): " . basename($fullPath));
208
                } else {
209
                    $this->warn('  ⚠ Backup creation failed');
210
                }
211
            } else {
212
                $this->warn('  ⚠ Backup creation failed');
213
            }
214
        }
215
    }
216
217
    /**
218
     * Create backup for PostgreSQL
219
     */
220
    private function createPostgresBackup(array $dbConfig, string $fullPath): void
221
    {
222
        $command = sprintf(
223
            'PGPASSWORD=%s pg_dump -h %s -p %s -U %s %s > %s 2>/dev/null',
224
            escapeshellarg($dbConfig['password']),
225
            escapeshellarg($dbConfig['host']),
226
            escapeshellarg($dbConfig['port']),
227
            escapeshellarg($dbConfig['username']),
228
            escapeshellarg($dbConfig['database']),
229
            escapeshellarg($fullPath)
230
        );
231
232
        exec($command, $output, $returnVar);
233
234
        if ($returnVar === 0 && file_exists($fullPath) && filesize($fullPath) > 0) {
235
            $this->line("  ✓ PostgreSQL backup created: " . basename($fullPath));
236
        } else {
237
            $this->warn('  ⚠ PostgreSQL backup creation failed');
238
        }
239
    }
240
241
    /**
242
     * Run database migrations
243
     */
244
    private function runMigrations(): void
245
    {
246
        $migrateOptions = [];
247
248
        if (app()->environment('production')) {
249
            $migrateOptions['--force'] = true;
250
        }
251
252
        $exitCode = $this->call('migrate', $migrateOptions);
253
254
        if ($exitCode !== 0) {
255
            throw new \Exception('Migration failed');
256
        }
257
258
        $this->line('  ✓ Migrations completed successfully');
259
    }
260
261
    /**
262
     * Run database seeders
263
     */
264
    private function runSeeders(): void
265
    {
266
        $seedOptions = [];
267
268
        if (app()->environment('production')) {
269
            $seedOptions['--force'] = true;
270
        }
271
272
        $exitCode = $this->call('db:seed', $seedOptions);
273
274
        if ($exitCode !== 0) {
275
            throw new \Exception('Seeding failed');
276
        }
277
278
        $this->line('  ✓ Seeders completed successfully');
279
    }
280
281
    /**
282
     * Optimize database tables with proper driver detection
283
     */
284
    private function optimizeDatabase(string $dbType): void
285
    {
286
        try {
287
            if (in_array($dbType, ['mysql', 'mariadb'])) {
288
                $this->optimizeMysqlCompatible($dbType);
289
            } elseif ($dbType === 'pgsql') {
290
                $this->optimizePostgres();
291
            } else {
292
                $this->line("  ℹ Database optimization skipped (unsupported type: $dbType)");
293
            }
294
        } catch (\Exception $e) {
295
            $this->warn('  ⚠ Database optimization failed: ' . $e->getMessage());
296
        }
297
    }
298
299
    /**
300
     * Optimize MySQL/MariaDB tables
301
     */
302
    private function optimizeMysqlCompatible(string $dbType): void
303
    {
304
        $dbConfig = config('database.connections.' . config('database.default'));
305
        $tables = DB::select('SHOW TABLES');
306
        $tableKey = 'Tables_in_' . $dbConfig['database'];
307
308
        $optimizedCount = 0;
309
        foreach ($tables as $table) {
310
            $tableName = $table->$tableKey;
311
            try {
312
                DB::statement("OPTIMIZE TABLE `$tableName`");
313
                $optimizedCount++;
314
            } catch (\Exception $e) {
315
                $this->warn("    ⚠ Failed to optimize table: $tableName");
316
            }
317
        }
318
319
        $this->line("  ✓ $dbType tables optimized ($optimizedCount/" . count($tables) . ")");
320
    }
321
322
    /**
323
     * Optimize PostgreSQL database
324
     */
325
    private function optimizePostgres(): void
326
    {
327
        try {
328
            DB::statement('VACUUM ANALYZE');
329
            $this->line('  ✓ PostgreSQL database optimized (VACUUM ANALYZE)');
330
        } catch (\Exception $e) {
331
            $this->warn('  ⚠ PostgreSQL optimization failed: ' . $e->getMessage());
332
        }
333
    }
334
}
335