289 lines
8.4 KiB
PHP
289 lines
8.4 KiB
PHP
#!/usr/bin/env php
|
|
<?php
|
|
/**
|
|
* Geofeed CSV to SQL Import Script
|
|
*
|
|
* Usage: php import_csv.php [csv_file_path] [--dry-run]
|
|
*
|
|
* If no file path provided, will attempt to fetch from default URL
|
|
*/
|
|
|
|
// Configuration
|
|
$config = [
|
|
'db_host' => getenv('DB_HOST') ?: 'localhost',
|
|
'db_name' => getenv('DB_NAME') ?: 'geofeed_manager',
|
|
'db_user' => getenv('DB_USER') ?: 'root',
|
|
'db_pass' => getenv('DB_PASS') ?: '',
|
|
'default_csv_url' => 'https://store.prpl.uk/geofeed.csv'
|
|
];
|
|
|
|
// Parse command line arguments
|
|
$csvPath = $argv[1] ?? null;
|
|
$dryRun = in_array('--dry-run', $argv);
|
|
|
|
echo "╔══════════════════════════════════════════════════════════════╗\n";
|
|
echo "║ Geofeed CSV to SQL Import Utility ║\n";
|
|
echo "╚══════════════════════════════════════════════════════════════╝\n\n";
|
|
|
|
if ($dryRun) {
|
|
echo "🔍 DRY RUN MODE - No changes will be made to the database\n\n";
|
|
}
|
|
|
|
// Fetch or read CSV data
|
|
$csvData = '';
|
|
if ($csvPath && file_exists($csvPath)) {
|
|
echo "📁 Reading from local file: $csvPath\n";
|
|
$csvData = file_get_contents($csvPath);
|
|
} elseif ($csvPath && filter_var($csvPath, FILTER_VALIDATE_URL)) {
|
|
echo "🌐 Fetching from URL: $csvPath\n";
|
|
$csvData = fetchFromUrl($csvPath);
|
|
} else {
|
|
echo "🌐 Fetching from default URL: {$config['default_csv_url']}\n";
|
|
$csvData = fetchFromUrl($config['default_csv_url']);
|
|
}
|
|
|
|
if (empty($csvData)) {
|
|
die("❌ Error: Could not fetch or read CSV data\n");
|
|
}
|
|
|
|
echo "✅ CSV data retrieved successfully\n\n";
|
|
|
|
// Parse CSV
|
|
$lines = explode("\n", $csvData);
|
|
$entries = [];
|
|
$lineNum = 0;
|
|
$errors = [];
|
|
|
|
foreach ($lines as $line) {
|
|
$lineNum++;
|
|
$line = trim($line);
|
|
|
|
// Skip empty lines and comments
|
|
if (empty($line) || strpos($line, '#') === 0) {
|
|
continue;
|
|
}
|
|
|
|
// Parse CSV line
|
|
$parts = str_getcsv($line);
|
|
|
|
// Geofeed format: ip_prefix,country_code,region,city,postal_code
|
|
if (count($parts) < 1) {
|
|
$errors[] = "Line $lineNum: Empty or invalid format";
|
|
continue;
|
|
}
|
|
|
|
$entry = [
|
|
'ip_prefix' => trim($parts[0] ?? ''),
|
|
'country_code' => strtoupper(trim($parts[1] ?? '')),
|
|
'region_code' => strtoupper(trim($parts[2] ?? '')),
|
|
'city' => trim($parts[3] ?? ''),
|
|
'postal_code' => trim($parts[4] ?? '')
|
|
];
|
|
|
|
// Validate IP prefix
|
|
if (!isValidIpPrefix($entry['ip_prefix'])) {
|
|
$errors[] = "Line $lineNum: Invalid IP prefix '{$entry['ip_prefix']}'";
|
|
continue;
|
|
}
|
|
|
|
// Validate country code (if provided)
|
|
if (!empty($entry['country_code']) && !preg_match('/^[A-Z]{2}$/', $entry['country_code'])) {
|
|
$errors[] = "Line $lineNum: Invalid country code '{$entry['country_code']}'";
|
|
continue;
|
|
}
|
|
|
|
// Clean up empty values
|
|
foreach ($entry as $key => $value) {
|
|
if ($value === '') {
|
|
$entry[$key] = null;
|
|
}
|
|
}
|
|
|
|
$entries[] = $entry;
|
|
}
|
|
|
|
echo "📊 Parsing Results:\n";
|
|
echo " ├── Total lines processed: $lineNum\n";
|
|
echo " ├── Valid entries found: " . count($entries) . "\n";
|
|
echo " └── Errors encountered: " . count($errors) . "\n\n";
|
|
|
|
if (count($errors) > 0) {
|
|
echo "⚠️ Validation Errors (showing first 10):\n";
|
|
foreach (array_slice($errors, 0, 10) as $error) {
|
|
echo " └── $error\n";
|
|
}
|
|
if (count($errors) > 10) {
|
|
echo " └── ... and " . (count($errors) - 10) . " more errors\n";
|
|
}
|
|
echo "\n";
|
|
}
|
|
|
|
if (count($entries) === 0) {
|
|
die("❌ No valid entries to import\n");
|
|
}
|
|
|
|
// Display sample entries
|
|
echo "📋 Sample Entries (first 5):\n";
|
|
echo str_repeat("─", 80) . "\n";
|
|
printf("%-25s %-4s %-10s %-20s %-10s\n", "IP Prefix", "CC", "Region", "City", "Postal");
|
|
echo str_repeat("─", 80) . "\n";
|
|
foreach (array_slice($entries, 0, 5) as $entry) {
|
|
printf("%-25s %-4s %-10s %-20s %-10s\n",
|
|
substr($entry['ip_prefix'], 0, 25),
|
|
$entry['country_code'] ?? '-',
|
|
substr($entry['region_code'] ?? '-', 0, 10),
|
|
substr($entry['city'] ?? '-', 0, 20),
|
|
substr($entry['postal_code'] ?? '-', 0, 10)
|
|
);
|
|
}
|
|
echo str_repeat("─", 80) . "\n\n";
|
|
|
|
if ($dryRun) {
|
|
echo "🔍 DRY RUN COMPLETE - Would have imported " . count($entries) . " entries\n";
|
|
exit(0);
|
|
}
|
|
|
|
// Connect to database
|
|
echo "🔗 Connecting to database...\n";
|
|
try {
|
|
$pdo = new PDO(
|
|
"mysql:host={$config['db_host']};dbname={$config['db_name']};charset=utf8mb4",
|
|
$config['db_user'],
|
|
$config['db_pass'],
|
|
[
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
|
|
]
|
|
);
|
|
echo "✅ Database connection established\n\n";
|
|
} catch (PDOException $e) {
|
|
die("❌ Database connection failed: " . $e->getMessage() . "\n");
|
|
}
|
|
|
|
// Import entries
|
|
echo "📥 Importing entries...\n";
|
|
$inserted = 0;
|
|
$updated = 0;
|
|
$failed = 0;
|
|
|
|
$stmt = $pdo->prepare("
|
|
INSERT INTO geofeed_entries (ip_prefix, country_code, region_code, city, postal_code)
|
|
VALUES (:ip_prefix, :country_code, :region_code, :city, :postal_code)
|
|
ON DUPLICATE KEY UPDATE
|
|
country_code = VALUES(country_code),
|
|
region_code = VALUES(region_code),
|
|
city = VALUES(city),
|
|
postal_code = VALUES(postal_code),
|
|
updated_at = CURRENT_TIMESTAMP
|
|
");
|
|
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
foreach ($entries as $entry) {
|
|
try {
|
|
$stmt->execute([
|
|
':ip_prefix' => $entry['ip_prefix'],
|
|
':country_code' => $entry['country_code'],
|
|
':region_code' => $entry['region_code'],
|
|
':city' => $entry['city'],
|
|
':postal_code' => $entry['postal_code']
|
|
]);
|
|
|
|
if ($stmt->rowCount() === 1) {
|
|
$inserted++;
|
|
} elseif ($stmt->rowCount() === 2) {
|
|
$updated++;
|
|
}
|
|
} catch (PDOException $e) {
|
|
$failed++;
|
|
if ($failed <= 5) {
|
|
echo " ⚠️ Failed to import {$entry['ip_prefix']}: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
}
|
|
|
|
$pdo->commit();
|
|
|
|
echo "\n✅ Import Complete:\n";
|
|
echo " ├── Inserted: $inserted\n";
|
|
echo " ├── Updated: $updated\n";
|
|
echo " └── Failed: $failed\n\n";
|
|
|
|
} catch (Exception $e) {
|
|
$pdo->rollBack();
|
|
die("❌ Import failed: " . $e->getMessage() . "\n");
|
|
}
|
|
|
|
// Log the import
|
|
$pdo->prepare("
|
|
INSERT INTO geofeed_audit_log (entry_id, action, new_values, changed_by)
|
|
VALUES (NULL, 'INSERT', :details, 'import_script')
|
|
")->execute([
|
|
':details' => json_encode([
|
|
'type' => 'bulk_import',
|
|
'inserted' => $inserted,
|
|
'updated' => $updated,
|
|
'failed' => $failed,
|
|
'source' => $csvPath ?? $config['default_csv_url']
|
|
])
|
|
]);
|
|
|
|
echo "🎉 Import process completed successfully!\n";
|
|
|
|
// Helper functions
|
|
function fetchFromUrl($url) {
|
|
$ch = curl_init();
|
|
curl_setopt_array($ch, [
|
|
CURLOPT_URL => $url,
|
|
CURLOPT_RETURNTRANSFER => true,
|
|
CURLOPT_FOLLOWLOCATION => true,
|
|
CURLOPT_TIMEOUT => 30,
|
|
CURLOPT_SSL_VERIFYPEER => true,
|
|
CURLOPT_USERAGENT => 'Geofeed-Importer/1.0'
|
|
]);
|
|
|
|
$response = curl_exec($ch);
|
|
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
|
|
$error = curl_error($ch);
|
|
curl_close($ch);
|
|
|
|
if ($httpCode !== 200) {
|
|
echo "⚠️ HTTP $httpCode response received\n";
|
|
}
|
|
|
|
if ($error) {
|
|
echo "⚠️ cURL error: $error\n";
|
|
return false;
|
|
}
|
|
|
|
return $response;
|
|
}
|
|
|
|
function isValidIpPrefix($prefix) {
|
|
// Check if it's a valid IP or CIDR notation
|
|
if (strpos($prefix, '/') !== false) {
|
|
list($ip, $cidr) = explode('/', $prefix);
|
|
|
|
// Validate CIDR
|
|
if (!is_numeric($cidr)) {
|
|
return false;
|
|
}
|
|
|
|
// IPv4
|
|
if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
|
|
return $cidr >= 0 && $cidr <= 32;
|
|
}
|
|
|
|
// IPv6
|
|
if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
|
|
return $cidr >= 0 && $cidr <= 128;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
// Single IP address
|
|
return filter_var($prefix, FILTER_VALIDATE_IP) !== false;
|
|
}
|