468 lines
14 KiB
PHP
468 lines
14 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
/**
|
|
* CelesTrak JSON Import
|
|
* Quelle:
|
|
* https://celestrak.org/NORAD/elements/gp.php?GROUP=active&FORMAT=json
|
|
*
|
|
* Tabellen:
|
|
* - sat_satellites
|
|
* - sat_tle_current
|
|
* - app_import_runs
|
|
*/
|
|
|
|
ini_set('display_errors', '1');
|
|
error_reporting(E_ALL);
|
|
date_default_timezone_set('UTC');
|
|
|
|
/*
|
|
|--------------------------------------------------------------------------
|
|
| Konfiguration laden
|
|
|--------------------------------------------------------------------------
|
|
*/
|
|
$config = require __DIR__ . '/../../config/database.php';
|
|
|
|
$sourceUrl = 'https://celestrak.org/NORAD/elements/gp.php?GROUP=active&FORMAT=json';
|
|
$sourceName = 'celestrak';
|
|
|
|
/*
|
|
|--------------------------------------------------------------------------
|
|
| Datenbankverbindung
|
|
|--------------------------------------------------------------------------
|
|
*/
|
|
$dsn = sprintf(
|
|
'mysql:host=%s;dbname=%s;charset=%s',
|
|
$config['host'],
|
|
$config['dbname'],
|
|
$config['charset'] ?? 'utf8mb4'
|
|
);
|
|
|
|
$pdo = new PDO(
|
|
$dsn,
|
|
$config['user'],
|
|
$config['pass'],
|
|
[
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
]
|
|
);
|
|
|
|
/*
|
|
|--------------------------------------------------------------------------
|
|
| Hilfsfunktionen
|
|
|--------------------------------------------------------------------------
|
|
*/
|
|
function fetchRemoteJson(string $url): array
|
|
{
|
|
$ch = curl_init($url);
|
|
|
|
curl_setopt_array($ch, [
|
|
CURLOPT_RETURNTRANSFER => true,
|
|
CURLOPT_FOLLOWLOCATION => true,
|
|
CURLOPT_CONNECTTIMEOUT => 20,
|
|
CURLOPT_TIMEOUT => 180,
|
|
CURLOPT_USERAGENT => 'Skyview CelesTrak Importer/1.0',
|
|
CURLOPT_HTTPHEADER => [
|
|
'Accept: application/json',
|
|
'Accept-Encoding: gzip',
|
|
],
|
|
]);
|
|
|
|
$response = curl_exec($ch);
|
|
|
|
if ($response === false) {
|
|
throw new RuntimeException('Download fehlgeschlagen: ' . curl_error($ch));
|
|
}
|
|
|
|
$httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
|
|
$contentType = (string)curl_getinfo($ch, CURLINFO_CONTENT_TYPE);
|
|
|
|
curl_close($ch);
|
|
|
|
if ($httpCode !== 200) {
|
|
throw new RuntimeException('HTTP-Fehler beim Abruf: ' . $httpCode);
|
|
}
|
|
|
|
$isGzip = str_ends_with(strtolower($url), '.gz')
|
|
|| str_contains(strtolower($contentType), 'gzip')
|
|
|| str_starts_with($response, "\x1f\x8b");
|
|
|
|
if ($isGzip) {
|
|
$decoded = gzdecode($response);
|
|
if ($decoded === false) {
|
|
throw new RuntimeException('GZip-Datei konnte nicht entpackt werden');
|
|
}
|
|
|
|
$response = $decoded;
|
|
}
|
|
|
|
$data = json_decode($response, true);
|
|
|
|
if (!is_array($data)) {
|
|
throw new RuntimeException('Ungültige JSON-Antwort');
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
function normalizeDateTime(?string $value): ?string
|
|
{
|
|
if ($value === null || trim($value) === '') {
|
|
return null;
|
|
}
|
|
|
|
try {
|
|
$dt = new DateTimeImmutable($value, new DateTimeZone('UTC'));
|
|
return $dt->format('Y-m-d H:i:s');
|
|
} catch (Throwable $e) {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
function normalizeDate(?string $value): ?string
|
|
{
|
|
if ($value === null || trim($value) === '') {
|
|
return null;
|
|
}
|
|
|
|
try {
|
|
$dt = new DateTimeImmutable($value, new DateTimeZone('UTC'));
|
|
return $dt->format('Y-m-d');
|
|
} catch (Throwable $e) {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
function toNullableString(mixed $value): ?string
|
|
{
|
|
if ($value === null) {
|
|
return null;
|
|
}
|
|
|
|
$value = trim((string)$value);
|
|
return $value === '' ? null : $value;
|
|
}
|
|
|
|
function toNullableFloat(mixed $value): ?float
|
|
{
|
|
if ($value === null || $value === '') {
|
|
return null;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
return null;
|
|
}
|
|
|
|
return (float)$value;
|
|
}
|
|
|
|
function toNullableInt(mixed $value): ?int
|
|
{
|
|
if ($value === null || $value === '') {
|
|
return null;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
return null;
|
|
}
|
|
|
|
return (int)$value;
|
|
}
|
|
|
|
/*
|
|
|--------------------------------------------------------------------------
|
|
| Import
|
|
|--------------------------------------------------------------------------
|
|
*/
|
|
$importRunId = null;
|
|
$recordsReceived = 0;
|
|
$recordsInserted = 0;
|
|
$recordsUpdated = 0;
|
|
|
|
try {
|
|
$stmtImportStart = $pdo->prepare("
|
|
INSERT INTO app_import_runs (
|
|
source,
|
|
source_url,
|
|
started_at,
|
|
status,
|
|
message
|
|
) VALUES (
|
|
:source,
|
|
:source_url,
|
|
NOW(),
|
|
'running',
|
|
NULL
|
|
)
|
|
");
|
|
$stmtImportStart->execute([
|
|
':source' => $sourceName,
|
|
':source_url' => $sourceUrl,
|
|
]);
|
|
|
|
$importRunId = (int)$pdo->lastInsertId();
|
|
|
|
$rows = fetchRemoteJson($sourceUrl);
|
|
|
|
if (empty($rows)) {
|
|
throw new RuntimeException('Keine Datensätze empfangen');
|
|
}
|
|
|
|
$stmtFindSatellite = $pdo->prepare("
|
|
SELECT id
|
|
FROM sat_satellites
|
|
WHERE norad_cat_id = :norad_cat_id
|
|
LIMIT 1
|
|
");
|
|
|
|
$stmtUpsertSatellite = $pdo->prepare("
|
|
INSERT INTO sat_satellites (
|
|
norad_cat_id,
|
|
object_name,
|
|
object_id,
|
|
object_type,
|
|
classification_type,
|
|
country_code,
|
|
launch_date,
|
|
decay_date,
|
|
rcs_size,
|
|
active,
|
|
created_at,
|
|
updated_at
|
|
) VALUES (
|
|
:norad_cat_id,
|
|
:object_name,
|
|
:object_id,
|
|
:object_type,
|
|
:classification_type,
|
|
:country_code,
|
|
:launch_date,
|
|
:decay_date,
|
|
:rcs_size,
|
|
1,
|
|
NOW(),
|
|
NOW()
|
|
)
|
|
ON DUPLICATE KEY UPDATE
|
|
object_name = VALUES(object_name),
|
|
object_id = VALUES(object_id),
|
|
object_type = VALUES(object_type),
|
|
classification_type = VALUES(classification_type),
|
|
country_code = VALUES(country_code),
|
|
launch_date = VALUES(launch_date),
|
|
decay_date = VALUES(decay_date),
|
|
rcs_size = VALUES(rcs_size),
|
|
active = 1,
|
|
updated_at = NOW()
|
|
");
|
|
|
|
$stmtCurrentExists = $pdo->prepare("
|
|
SELECT id
|
|
FROM sat_tle_current
|
|
WHERE norad_cat_id = :norad_cat_id
|
|
LIMIT 1
|
|
");
|
|
|
|
$stmtUpsertCurrent = $pdo->prepare("
|
|
INSERT INTO sat_tle_current (
|
|
satellite_id,
|
|
norad_cat_id,
|
|
epoch,
|
|
creation_date,
|
|
mean_motion,
|
|
eccentricity,
|
|
inclination,
|
|
ra_of_asc_node,
|
|
arg_of_pericenter,
|
|
mean_anomaly,
|
|
ephemeris_type,
|
|
element_set_no,
|
|
rev_at_epoch,
|
|
bstar,
|
|
mean_motion_dot,
|
|
mean_motion_ddot,
|
|
center_name,
|
|
ref_frame,
|
|
time_system,
|
|
mean_element_theory,
|
|
raw_json,
|
|
source,
|
|
imported_at,
|
|
updated_at
|
|
) VALUES (
|
|
:satellite_id,
|
|
:norad_cat_id,
|
|
:epoch,
|
|
:creation_date,
|
|
:mean_motion,
|
|
:eccentricity,
|
|
:inclination,
|
|
:ra_of_asc_node,
|
|
:arg_of_pericenter,
|
|
:mean_anomaly,
|
|
:ephemeris_type,
|
|
:element_set_no,
|
|
:rev_at_epoch,
|
|
:bstar,
|
|
:mean_motion_dot,
|
|
:mean_motion_ddot,
|
|
:center_name,
|
|
:ref_frame,
|
|
:time_system,
|
|
:mean_element_theory,
|
|
:raw_json,
|
|
:source,
|
|
NOW(),
|
|
NOW()
|
|
)
|
|
ON DUPLICATE KEY UPDATE
|
|
satellite_id = VALUES(satellite_id),
|
|
epoch = VALUES(epoch),
|
|
creation_date = VALUES(creation_date),
|
|
mean_motion = VALUES(mean_motion),
|
|
eccentricity = VALUES(eccentricity),
|
|
inclination = VALUES(inclination),
|
|
ra_of_asc_node = VALUES(ra_of_asc_node),
|
|
arg_of_pericenter = VALUES(arg_of_pericenter),
|
|
mean_anomaly = VALUES(mean_anomaly),
|
|
ephemeris_type = VALUES(ephemeris_type),
|
|
element_set_no = VALUES(element_set_no),
|
|
rev_at_epoch = VALUES(rev_at_epoch),
|
|
bstar = VALUES(bstar),
|
|
mean_motion_dot = VALUES(mean_motion_dot),
|
|
mean_motion_ddot = VALUES(mean_motion_ddot),
|
|
center_name = VALUES(center_name),
|
|
ref_frame = VALUES(ref_frame),
|
|
time_system = VALUES(time_system),
|
|
mean_element_theory = VALUES(mean_element_theory),
|
|
raw_json = VALUES(raw_json),
|
|
source = VALUES(source),
|
|
imported_at = NOW(),
|
|
updated_at = NOW()
|
|
");
|
|
|
|
$pdo->beginTransaction();
|
|
|
|
foreach ($rows as $row) {
|
|
$noradCatId = toNullableInt($row['NORAD_CAT_ID'] ?? null);
|
|
|
|
if ($noradCatId === null) {
|
|
continue;
|
|
}
|
|
|
|
$recordsReceived++;
|
|
|
|
$stmtCurrentExists->execute([
|
|
':norad_cat_id' => $noradCatId,
|
|
]);
|
|
$currentExists = (bool)$stmtCurrentExists->fetchColumn();
|
|
|
|
$stmtUpsertSatellite->execute([
|
|
':norad_cat_id' => $noradCatId,
|
|
':object_name' => toNullableString($row['OBJECT_NAME'] ?? null),
|
|
':object_id' => toNullableString($row['OBJECT_ID'] ?? null),
|
|
':object_type' => toNullableString($row['OBJECT_TYPE'] ?? null),
|
|
':classification_type' => toNullableString($row['CLASSIFICATION_TYPE'] ?? null),
|
|
':country_code' => toNullableString($row['COUNTRY_CODE'] ?? null),
|
|
':launch_date' => normalizeDate($row['LAUNCH_DATE'] ?? null),
|
|
':decay_date' => normalizeDate($row['DECAY_DATE'] ?? null),
|
|
':rcs_size' => toNullableString($row['RCS_SIZE'] ?? null),
|
|
]);
|
|
|
|
$stmtFindSatellite->execute([
|
|
':norad_cat_id' => $noradCatId,
|
|
]);
|
|
$satelliteId = $stmtFindSatellite->fetchColumn();
|
|
|
|
if (!$satelliteId) {
|
|
throw new RuntimeException('satellite_id konnte nicht ermittelt werden für NORAD ' . $noradCatId);
|
|
}
|
|
|
|
$stmtUpsertCurrent->execute([
|
|
':satellite_id' => (int)$satelliteId,
|
|
':norad_cat_id' => $noradCatId,
|
|
':epoch' => normalizeDateTime($row['EPOCH'] ?? null),
|
|
':creation_date' => normalizeDateTime($row['CREATION_DATE'] ?? null),
|
|
':mean_motion' => toNullableFloat($row['MEAN_MOTION'] ?? null),
|
|
':eccentricity' => toNullableFloat($row['ECCENTRICITY'] ?? null),
|
|
':inclination' => toNullableFloat($row['INCLINATION'] ?? null),
|
|
':ra_of_asc_node' => toNullableFloat($row['RA_OF_ASC_NODE'] ?? null),
|
|
':arg_of_pericenter' => toNullableFloat($row['ARG_OF_PERICENTER'] ?? null),
|
|
':mean_anomaly' => toNullableFloat($row['MEAN_ANOMALY'] ?? null),
|
|
':ephemeris_type' => toNullableInt($row['EPHEMERIS_TYPE'] ?? null),
|
|
':element_set_no' => toNullableInt($row['ELEMENT_SET_NO'] ?? null),
|
|
':rev_at_epoch' => toNullableInt($row['REV_AT_EPOCH'] ?? null),
|
|
':bstar' => toNullableFloat($row['BSTAR'] ?? null),
|
|
':mean_motion_dot' => toNullableFloat($row['MEAN_MOTION_DOT'] ?? null),
|
|
':mean_motion_ddot' => toNullableFloat($row['MEAN_MOTION_DDOT'] ?? null),
|
|
':center_name' => toNullableString($row['CENTER_NAME'] ?? null),
|
|
':ref_frame' => toNullableString($row['REF_FRAME'] ?? null),
|
|
':time_system' => toNullableString($row['TIME_SYSTEM'] ?? null),
|
|
':mean_element_theory' => toNullableString($row['MEAN_ELEMENT_THEORY'] ?? null),
|
|
':raw_json' => json_encode($row, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
|
|
':source' => $sourceName,
|
|
]);
|
|
|
|
if ($currentExists) {
|
|
$recordsUpdated++;
|
|
} else {
|
|
$recordsInserted++;
|
|
}
|
|
}
|
|
|
|
$pdo->commit();
|
|
|
|
$stmtImportFinish = $pdo->prepare("
|
|
UPDATE app_import_runs
|
|
SET
|
|
finished_at = NOW(),
|
|
records_received = :records_received,
|
|
records_inserted = :records_inserted,
|
|
records_updated = :records_updated,
|
|
status = 'success',
|
|
message = NULL
|
|
WHERE id = :id
|
|
");
|
|
$stmtImportFinish->execute([
|
|
':records_received' => $recordsReceived,
|
|
':records_inserted' => $recordsInserted,
|
|
':records_updated' => $recordsUpdated,
|
|
':id' => $importRunId,
|
|
]);
|
|
|
|
echo "Import erfolgreich\n";
|
|
echo "Empfangen: {$recordsReceived}\n";
|
|
echo "Neu: {$recordsInserted}\n";
|
|
echo "Aktualisiert: {$recordsUpdated}\n";
|
|
|
|
} catch (Throwable $e) {
|
|
if ($pdo->inTransaction()) {
|
|
$pdo->rollBack();
|
|
}
|
|
|
|
if ($importRunId !== null) {
|
|
$stmtImportError = $pdo->prepare("
|
|
UPDATE app_import_runs
|
|
SET
|
|
finished_at = NOW(),
|
|
records_received = :records_received,
|
|
records_inserted = :records_inserted,
|
|
records_updated = :records_updated,
|
|
status = 'error',
|
|
message = :message
|
|
WHERE id = :id
|
|
");
|
|
$stmtImportError->execute([
|
|
':records_received' => $recordsReceived,
|
|
':records_inserted' => $recordsInserted,
|
|
':records_updated' => $recordsUpdated,
|
|
':message' => mb_substr($e->getMessage(), 0, 65000),
|
|
':id' => $importRunId,
|
|
]);
|
|
}
|
|
|
|
echo "Fehler: " . $e->getMessage() . "\n";
|
|
exit(1);
|
|
}
|