Add newlines before parenthesis for a SQL mysqldump

process-mysqldump.c

// gcc -O2 -Wall -pedantic process-mysqldump.c -o process-mysqldump
// Usage: cat dump.sql | process-mysqldump
//   Or : process-mysqldump dump.sql

#include <stdio.h>
#include <stdlib.h>
#include <stdbool.h>
#include <string.h>

#define BUFFER 100000

bool is_escaped(char* string, int offset) {
    if (offset == 0) {
        return false;
    } else if (string[offset - 1] == '\\') {
        return !is_escaped(string, offset - 1);
    } else {
        return false;
    }
}

bool is_commented(char* string) {
    char buffer[4];

    sprintf(buffer, "%.3s", string);

    return strcmp(buffer, "-- ") == 0;
}

int main(int argc, char *argv[])
{
    FILE* file = argc > 1 ? fopen(argv[1], "r") : stdin;

    char buffer[BUFFER];
    char* line;
    int pos;
    int parenthesis = 0;
    bool quote = false;
    bool escape = false;
    bool comment = false;

    while (fgets(buffer, BUFFER, file) != NULL) {
        line = buffer;

        // skip commented
        if (comment || is_commented(line)) {
            comment = line[strlen(line) - 1] != '\n';
            fputs(line, stdout);
        } else {
            pos = 0;

            nullchar:
            while (line[pos] != '\0') {
                // if we are still in escape state, we need to check first char.
                if (!escape) {
                    // find any character in ()'
                    pos = strcspn(line, "()'\\");
                }

                if (pos > 0) {
                    // print before match
                    printf("%.*s", pos, line);
                }

                switch (line[pos]) {
                    case '(':
                        if (!quote) {
                            if (parenthesis == 0) {
                                putchar('\n');
                            }
                            parenthesis++;
                        }
                        if (escape) {
                            escape = false;
                        }
                        break;

                    case ')':
                        if (!quote) {
                            if (parenthesis > 0) {
                                parenthesis--;
                            } else {
                                // whoops
                                puts("\n");
                                fputs(line, stdout);
                                fputs("Found closing parenthesis without opening one.\n", stderr);
                                exit(1);
                            }
                        }
                        if (escape) {
                            escape = false;
                        }
                        break;

                    case '\\':
                        escape = !escape;
                        break;

                    case '\'':
                        if (escape) {
                            escape = false;
                        } else {
                            quote = !quote;
                        }
                        break;

                    case '\0':
                        goto nullchar;

                    default:
                        if (escape) {
                            escape = false;
                        }
                        break;
                }

                // print char then skip it (to make sure we don’t double match)
                putchar(line[pos]);
                line = line + pos + 1;
                pos = 0;
            }
        }
    }

    return 0;
}

process-mysqldump.php

<?php
// Usage: cat dump.sql | php process-mysqldump.php
while (false !== ($line = fgets_big_buffer())) {
    if (substr($line, 0, 6) === 'INSERT') {
        process_line($line);
    } else {
        echo $line;
    }
}
function fgets_big_buffer()
{
    $ret = fgets(STDIN, 1 * 1024 * 1024);
    if ($ret === false || $ret[strlen($ret) - 1] === "\n") {
        return $ret;
    }
    while (1) {
        $tmp = fgets(STDIN, 1 * 1024 * 1024);
        if ($tmp === false) {
            return $ret;
        }
        $ret .= $tmp;
        if ($tmp[strlen($tmp) - 1] === "\n") {
            return $ret;
        }
    }
}
function process_line($line)
{
    $length = strlen($line);
    $pos = strpos($line, ' VALUES ') + 8;
    $ret = substr($line, 0, $pos);
    $parenthesis = false;
    $quote = false;
    $escape = false;
    for ($i = $pos; $i < $length; ++ $i) {
        switch ($line[$i]) {
            case '(':
                if (! $quote) {
                    if ($parenthesis) {
                       throw new \Exception ( 'double open parenthesis' );
                    } else {
                        $ret .= "\n";
                        $parenthesis = true;
                    }
                }
                $escape = false;
                break;
            case ')':
                if (! $quote) {
                    if ($parenthesis) {
                        $parenthesis = false;
                    } else {
                         throw new Exception ( 'closing parenthesis without open' );
                    }
                }
                $escape = false;
                break;
            case '\\':
                $escape = ! $escape;
                break;
            case "'":
                if ($escape) {
                    $escape = false;
                } else {
                    $quote = ! $quote;
                }
                break;
            default:
                $escape = false;
                break;
        }
        $ret .= $line[$i];
        $to = strcspn($line, '()\\\'', $i + 1)-1;
        if ($to > 0) {
            $ret .= substr($line, $i + 1, $to);
            $i += ($to);
        }
    }
    echo $ret;
}

Unfortunately this also affects CREATE TABLE statements and CREATE TRIGGER in some cases breaking the trigger syntax by introducing newlines where they cause errors.

EDIT: Fixed on my fork: https://gist.github.com/colinmollenhour/cf23b0f7e955267ed1107c9edb07f7c2