root
89 lines
2.6 KiB
PHP
89 lines
2.6 KiB
PHP
<?php
|
|
|
|
namespace App\Database\Migrations;
|
|
|
|
use CodeIgniter\Database\Migration;
|
|
use RuntimeException;
|
|
|
|
class BackfillAddedSchoolYearColumns extends Migration
|
|
{
|
|
public function up(): void
|
|
{
|
|
$schoolYear = $this->configuredSchoolYear();
|
|
|
|
foreach ($this->tablesWithSchoolYearColumn() as $table) {
|
|
$this->db->query(
|
|
sprintf(
|
|
'UPDATE %s SET `school_year` = ? WHERE `school_year` IS NULL OR TRIM(`school_year`) = \'\'',
|
|
$this->quoteIdentifier($table)
|
|
),
|
|
[$schoolYear]
|
|
);
|
|
}
|
|
}
|
|
|
|
public function down(): void
|
|
{
|
|
// This is a data backfill. Do not erase school_year values on rollback.
|
|
}
|
|
|
|
private function configuredSchoolYear(): string
|
|
{
|
|
if (! $this->db->tableExists('configuration')) {
|
|
throw new RuntimeException('Cannot backfill school_year columns: configuration table is missing.');
|
|
}
|
|
|
|
$row = $this->db->table('configuration')
|
|
->select('config_value')
|
|
->where('config_key', 'school_year')
|
|
->where('config_value IS NOT NULL', null, false)
|
|
->where('TRIM(config_value) <>', '')
|
|
->orderBy('id', 'DESC')
|
|
->get(1)
|
|
->getRowArray();
|
|
|
|
$schoolYear = trim((string)($row['config_value'] ?? ''));
|
|
if (preg_match('/^\d{4}-\d{4}$/', $schoolYear) !== 1) {
|
|
throw new RuntimeException(
|
|
'Cannot backfill school_year columns: configuration.school_year must be set as YYYY-YYYY.'
|
|
);
|
|
}
|
|
|
|
return $schoolYear;
|
|
}
|
|
|
|
/**
|
|
* @return list<string>
|
|
*/
|
|
private function tablesWithSchoolYearColumn(): array
|
|
{
|
|
$rows = $this->db->query(
|
|
"SELECT c.TABLE_NAME AS table_name
|
|
FROM information_schema.COLUMNS c
|
|
INNER JOIN information_schema.TABLES t
|
|
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
|
|
AND t.TABLE_NAME = c.TABLE_NAME
|
|
WHERE c.TABLE_SCHEMA = DATABASE()
|
|
AND c.COLUMN_NAME = 'school_year'
|
|
AND t.TABLE_TYPE = 'BASE TABLE'
|
|
ORDER BY c.TABLE_NAME"
|
|
)->getResultArray();
|
|
|
|
$tables = [];
|
|
foreach ($rows as $row) {
|
|
$table = trim((string)($row['table_name'] ?? ''));
|
|
if ($table === '' || $table === 'school_years') {
|
|
continue;
|
|
}
|
|
$tables[] = $table;
|
|
}
|
|
|
|
return $tables;
|
|
}
|
|
|
|
private function quoteIdentifier(string $identifier): string
|
|
{
|
|
return '`' . str_replace('`', '``', $identifier) . '`';
|
|
}
|
|
}
|