Postgres to Sqlite conversion script

pg_to_sqlite.php

<?php

// Migrate a postgres SQL file to a format that can be ingested by sqlite
// Generated by cursor

if ($argc !== 2) {
    die("Usage: php pg_to_sqlite.php <input_file>\n");
}

$inputFile = $argv[1];
$outputFile = dirname($inputFile) . '/' . basename($inputFile, '.sql') . '_sqlite.sql';

if (!file_exists($inputFile)) {
    die("Input file not found: $inputFile\n");
}

$input = fopen($inputFile, 'r');
$output = fopen($outputFile, 'w');

$currentTable = null;
$columns = [];
$inCopy = false;

while (($line = fgets($input)) !== false) {
    $line = trim($line);

    // Skip empty lines and comments
    if (empty($line) || strpos($line, '--') === 0) {
        continue;
    }

    // Handle COPY statements
    if (preg_match('/^COPY\s+(\w+)\s+\((.*?)\)\s+FROM\s+stdin;$/', $line, $matches)) {
        $currentTable = $matches[1];
        $columns = array_map('trim', explode(',', $matches[2]));
        $inCopy = true;
        continue;
    }

    // Handle end of COPY
    if ($line === '\.') {
        $inCopy = false;
        $currentTable = null;
        continue;
    }

    // Convert COPY data to INSERT statements
    if ($inCopy && $currentTable) {
        $values = str_getcsv($line, "\t");
        // Escape single quotes and wrap values in quotes
        $values = array_map(function ($val) {
            if ($val === '\N') {
                return 'NULL';
            }
            return "'" . str_replace("'", "''", $val) . "'";
        }, $values);

        fwrite($output, "INSERT INTO $currentTable (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ");\n");
        continue;
    }

    // Pass through other SQL statements (after making them SQLite compatible)
    if (!$inCopy) {
        // Convert PostgreSQL-specific syntax to SQLite
        $line = preg_replace('/WITH\s*\(.*?\)/', '', $line); // Remove WITH clauses
        $line = preg_replace('/::[\w\s\[\]]+/', '', $line); // Remove type casts
        $line = str_replace('NOW()', "datetime('now')", $line); // Convert NOW()
        $line = str_replace('true', '1', $line); // Convert boolean true
        $line = str_replace('false', '0', $line); // Convert boolean false

        if (!empty($line)) {
            fwrite($output, $line . "\n");
        }
    }
}

fclose($input);
fclose($output);

echo "Conversion completed. Output written to: $outputFile\n";