51 lines
2.5 KiB
PHP
51 lines
2.5 KiB
PHP
<?php
|
|
require_once __DIR__ . '/config.php';
|
|
$db = db();
|
|
|
|
try {
|
|
echo "Starting migration to add database relationships...\n";
|
|
|
|
// 1. Ensure columns used as foreign keys have indexes
|
|
$db->exec("ALTER TABLE celestial_object_types MODIFY COLUMN slug VARCHAR(50) NOT NULL;");
|
|
$db->exec("ALTER TABLE celestial_object_statuses MODIFY COLUMN slug VARCHAR(50) NOT NULL;");
|
|
$db->exec("ALTER TABLE settlement_types MODIFY COLUMN slug VARCHAR(50) NOT NULL;");
|
|
|
|
// Ensure UNIQUE indexes exist for slugs being referenced
|
|
$db->exec("ALTER TABLE celestial_object_types ADD UNIQUE INDEX IF NOT EXISTS idx_types_slug (slug);");
|
|
$db->exec("ALTER TABLE celestial_object_statuses ADD UNIQUE INDEX IF NOT EXISTS idx_statuses_slug (slug);");
|
|
$db->exec("ALTER TABLE settlement_types ADD UNIQUE INDEX IF NOT EXISTS idx_settlement_types_slug (slug);");
|
|
|
|
// Ensure factions.id is indexed (it's the PK, so it is)
|
|
|
|
// 2. Add foreign keys to 'planets'
|
|
echo "Adding foreign keys to 'planets' table...\n";
|
|
|
|
// Clean up any rogue data first
|
|
$db->exec("UPDATE planets SET faction_id = NULL WHERE faction_id NOT IN (SELECT id FROM factions)");
|
|
|
|
// status and type cleanup was done in shell, but just in case
|
|
$db->exec("DELETE FROM planets WHERE status NOT IN (SELECT slug FROM celestial_object_statuses)");
|
|
$db->exec("DELETE FROM planets WHERE type NOT IN (SELECT slug FROM celestial_object_types)");
|
|
|
|
// Add Constraints
|
|
$db->exec("ALTER TABLE planets
|
|
ADD CONSTRAINT fk_planets_type FOREIGN KEY (type) REFERENCES celestial_object_types(slug) ON UPDATE CASCADE ON DELETE RESTRICT,
|
|
ADD CONSTRAINT fk_planets_status FOREIGN KEY (status) REFERENCES celestial_object_statuses(slug) ON UPDATE CASCADE ON DELETE RESTRICT,
|
|
ADD CONSTRAINT fk_planets_faction FOREIGN KEY (faction_id) REFERENCES factions(id) ON DELETE SET NULL;");
|
|
|
|
// 3. Add foreign keys to 'cities'
|
|
echo "Adding foreign keys to 'cities' table...\n";
|
|
|
|
$db->exec("UPDATE cities SET settlement_type_id = NULL WHERE settlement_type_id NOT IN (SELECT id FROM settlement_types)");
|
|
|
|
$db->exec("ALTER TABLE cities
|
|
ADD CONSTRAINT fk_cities_settlement_type FOREIGN KEY (settlement_type_id) REFERENCES settlement_types(id) ON DELETE SET NULL;");
|
|
|
|
// 4. Add foreign keys to 'celestial_object_type_modifiers'
|
|
// This table already has them (checked with SHOW CREATE TABLE)
|
|
|
|
echo "Migration completed successfully!\n";
|
|
} catch (PDOException $e) {
|
|
die("Migration failed: " . $e->getMessage() . "\n");
|
|
}
|