diff --git a/server/SHServ/Migrations/Migration.php b/server/SHServ/Migrations/Migration.php new file mode 100644 index 0000000..6fad52a --- /dev/null +++ b/server/SHServ/Migrations/Migration.php @@ -0,0 +1,64 @@ + thin_builder; + } + + /** + * Execute a raw SQL query. + * + * @param string $sql + * @return \PDOStatement + */ + protected function query(string $sql): \PDOStatement { + return $this -> tb() -> query($sql); + } + + /** + * Check if a table exists. + * + * @param string $tablename + * @return bool + */ + protected function table_exists(string $tablename): bool { + $result = $this -> tb() -> query( + "SHOW TABLES LIKE '{$tablename}'", + 'fetch', + \PDO::FETCH_NUM + ); + return (bool) $result; + } + + /** + * Drop a table if it exists. + * + * @param string $tablename + * @return void + */ + protected function drop_table_if_exists(string $tablename): void { + $this -> query("DROP TABLE IF EXISTS `{$tablename}`"); + } +} diff --git a/server/SHServ/Migrations/MigrationsManager.php b/server/SHServ/Migrations/MigrationsManager.php new file mode 100644 index 0000000..c1cb00e --- /dev/null +++ b/server/SHServ/Migrations/MigrationsManager.php @@ -0,0 +1,308 @@ + migrations_dir = dirname(__DIR__, 2) . '/migrations'; + $this -> tb = app() -> thin_builder; + $this -> ensure_migrations_table(); + } + + /** + * Create the migrations tracking table if it doesn't exist. + */ + protected function ensure_migrations_table(): void { + if ($this -> table_exists($this -> table_name)) { + return; + } + + $this -> tb -> create_table($this -> table_name, [ + 'id' => [ + 'type' => 'INT', + 'length' => 11, + 'auto_increment' => true, + 'can_be_null' => false, + ], + 'migration' => [ + 'type' => 'VARCHAR', + 'length' => 255, + 'can_be_null' => false, + ], + 'batch' => [ + 'type' => 'INT', + 'length' => 11, + 'default' => 0, + 'can_be_null' => false, + ], + 'applied_at' => [ + 'type' => 'TIMESTAMP', + 'default' => 'CURRENT_TIMESTAMP', + 'can_be_null' => false, + ], + ], 'id', 'InnoDB'); + } + + /** + * Run all pending migrations. + * + * @return array Applied migration filenames + */ + public function migrate(): array { + $pending = $this -> get_pending_migrations(); + if (empty($pending)) { + return []; + } + + $batch = $this -> get_next_batch_number(); + $applied = []; + + foreach ($pending as $file) { + $migration = $this -> load_migration($file); + $migration -> up(); + + $this -> tb -> insert($this -> table_name, [ + 'migration' => $file, + 'batch' => $batch, + 'applied_at' => date('Y-m-d H:i:s'), + ]); + + $applied[] = $file; + } + + return $applied; + } + + /** + * Rollback the last batch of migrations. + * + * @param int $steps Number of batches to rollback (default 1) + * @return array Rolled back migration filenames + */ + public function rollback(int $steps = 1): array { + $batches = $this -> tb -> select( + $this -> table_name, + ['batch'], + [], + ['batch'], + 'DESC', + [0, $steps] + ); + + if (empty($batches)) { + return []; + } + + $batch_numbers = array_map(fn($b) => (int) $b['batch'], $batches); + $batch_numbers = array_unique($batch_numbers); + + $migrations_to_rollback = $this -> tb -> select( + $this -> table_name, + [], + ['batch', 'IN', $batch_numbers], + ['id'], + 'DESC', + [] + ); + + $rolled_back = []; + foreach ($migrations_to_rollback as $row) { + $file = $row['migration']; + $migration = $this -> load_migration($file); + $migration -> down(); + + $this -> tb -> delete($this -> table_name, ['id', '=', $row['id']]); + $rolled_back[] = $file; + } + + return $rolled_back; + } + + /** + * Show migration status: applied and pending. + * + * @return array ['applied' => [...], 'pending' => [...]] + */ + public function status(): array { + $all_files = $this -> get_migration_files(); + $applied_rows = $this -> tb -> select($this -> table_name, [], [], ['id'], 'ASC', []); + $applied_map = array_column($applied_rows, 'batch', 'migration'); + + $applied = []; + $pending = []; + + foreach ($all_files as $file) { + if (isset($applied_map[$file])) { + $applied[] = ['file' => $file, 'batch' => $applied_map[$file]]; + } else { + $pending[] = $file; + } + } + + return compact('applied', 'pending'); + } + + /** + * Generate a new migration file template. + * + * @param string $name Snake_case migration name + * @return string Generated filename + */ + public function create(string $name): string { + $timestamp = date('Ymd_His'); + $filename = "{$timestamp}_{$name}.php"; + $filepath = $this -> migrations_dir . '/' . $filename; + + $class_name = $this -> migration_class_name($name); + + $template = << migrations_dir)) { + return []; + } + + $files = array_filter(scandir($this -> migrations_dir), function ($f) { + return substr($f, -4) === '.php'; + }); + + sort($files); + return array_values($files); + } + + /** + * Get migrations that have not been applied yet. + * + * @return array + */ + protected function get_pending_migrations(): array { + $all = $this -> get_migration_files(); + $applied = $this -> tb -> select($this -> table_name, ['migration'], [], ['id'], 'ASC', []); + $applied_names = array_column($applied, 'migration'); + + return array_values(array_diff($all, $applied_names)); + } + + /** + * Determine the next batch number. + * + * @return int + */ + protected function get_next_batch_number(): int { + $result = $this -> tb -> select( + $this -> table_name, + ['batch'], + [], + ['batch'], + 'DESC', + [0, 1] + ); + + if (empty($result)) { + return 1; + } + return (int) $result[0]['batch'] + 1; + } + + /** + * Load and instantiate a migration class from file. + * + * @param string $filename + * @return Migration + */ + protected function load_migration(string $filename): Migration { + $filepath = $this -> migrations_dir . '/' . $filename; + if (!file_exists($filepath)) { + throw new \Exception("Migration file not found: {$filepath}"); + } + + require_once $filepath; + + // Extract class name from filename: Ymd_His_name.php => MigrationName + $base = substr($filename, 0, -4); + $parts = explode('_', $base, 3); + if (count($parts) < 3) { + throw new \Exception("Invalid migration filename format: {$filename}"); + } + + $class_name = $this -> migration_class_name($parts[2]); + + if (!class_exists($class_name)) { + throw new \Exception("Migration class {$class_name} not found in {$filename}"); + } + + $instance = new $class_name(); + if (!($instance instanceof Migration)) { + throw new \Exception("Class {$class_name} must extend SHServ\\Migrations\\Migration"); + } + + return $instance; + } + + /** + * Convert snake_case migration name to CamelCase class name. + * + * @param string $name + * @return string + */ + protected function migration_class_name(string $name): string { + return str_replace(' ', '', ucwords(str_replace('_', ' ', $name))); + } + + /** + * Check if a table exists. + * + * @param string $tablename + * @return bool + */ + protected function table_exists(string $tablename): bool { + $result = $this -> tb -> query( + "SHOW TABLES LIKE '{$tablename}'", + 'fetch', + \PDO::FETCH_NUM + ); + return (bool) $result; + } +} diff --git a/server/console.php b/server/console.php index b45f38f..2ed7a97 100644 --- a/server/console.php +++ b/server/console.php @@ -7,13 +7,29 @@ function console() { global $argv; - switch($argv[1]) { + if(!isset($argv[1])) { + echo "Usage: php console.php [args]\n"; + echo "\nCommands:\n"; + echo " get.config Show server config (without DB credentials)\n"; + echo " run-regular-script Run a regular control script by alias\n"; + echo " migrate Run all pending migrations\n"; + echo " migrate:rollback [steps] Rollback last batch of migrations\n"; + echo " migrate:status Show applied and pending migrations\n"; + echo " migrate:create Generate a new migration file\n"; + echo "\n"; + return; + } + + $command = $argv[1]; + + switch($command) { case "get.config": $config = FCONF; unset($config['db']['password']); unset($config['db']['user']); echo json_encode($config); break; + case "run-regular-script": if(!isset($argv[2])) { echo "No alias provided\n"; @@ -25,7 +41,70 @@ exit(1); } break; - default: echo "\nNo command"; + + case "migrate": + $manager = new \SHServ\Migrations\MigrationsManager(); + $applied = $manager -> migrate(); + if(empty($applied)) { + echo "Nothing to migrate.\n"; + } else { + echo "Applied " . count($applied) . " migration(s):\n"; + foreach($applied as $file) { + echo " ✓ {$file}\n"; + } + } + break; + + case "migrate:rollback": + $steps = isset($argv[2]) ? (int) $argv[2] : 1; + $manager = new \SHServ\Migrations\MigrationsManager(); + $rolled = $manager -> rollback($steps); + if(empty($rolled)) { + echo "Nothing to rollback.\n"; + } else { + echo "Rolled back " . count($rolled) . " migration(s):\n"; + foreach($rolled as $file) { + echo " ↶ {$file}\n"; + } + } + break; + + case "migrate:status": + $manager = new \SHServ\Migrations\MigrationsManager(); + $status = $manager -> status(); + echo "Applied migrations:\n"; + if(empty($status['applied'])) { + echo " (none)\n"; + } else { + foreach($status['applied'] as $row) { + echo " [batch {$row['batch']}] {$row['file']}\n"; + } + } + echo "\nPending migrations:\n"; + if(empty($status['pending'])) { + echo " (none)\n"; + } else { + foreach($status['pending'] as $file) { + echo " … {$file}\n"; + } + } + break; + + case "migrate:create": + if(!isset($argv[2])) { + echo "Migration name required.\n"; + echo "Usage: php console.php migrate:create \n"; + exit(1); + } + $name = $argv[2]; + $manager = new \SHServ\Migrations\MigrationsManager(); + $filename = $manager -> create($name); + echo "Created migration: {$filename}\n"; + break; + + default: + echo "Unknown command: {$command}\n"; + exit(1); } echo "\n"; diff --git a/server/migrations/20250602_000000_create_sessions_table.php b/server/migrations/20250602_000000_create_sessions_table.php new file mode 100644 index 0000000..b537a45 --- /dev/null +++ b/server/migrations/20250602_000000_create_sessions_table.php @@ -0,0 +1,56 @@ +tb()->create_table('sessions', [ + 'id' => [ + 'type' => 'INT', + 'length' => 11, + 'auto_increment' => true, + 'can_be_null' => false, + ], + 'uid' => [ + 'type' => 'INT', + 'length' => 11, + 'default' => 0, + 'can_be_null' => false, + ], + 'status' => [ + 'type' => 'VARCHAR', + 'length' => 50, + 'default' => 'active', + 'can_be_null' => false, + ], + 'token' => [ + 'type' => 'VARCHAR', + 'length' => 255, + 'can_be_null' => false, + ], + 'last_using_at' => [ + 'type' => 'DATETIME', + 'default' => 'NULL', + 'can_be_null' => true, + ], + 'update_at' => [ + 'type' => 'DATETIME', + 'default' => 'NULL', + 'can_be_null' => true, + ], + 'create_at' => [ + 'type' => 'DATETIME', + 'default' => 'CURRENT_TIMESTAMP', + 'can_be_null' => false, + ], + ], 'id', 'InnoDB'); + } + + public function down(): void { + $this->drop_table_if_exists('sessions'); + } +}