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;
}

After a quick test this seems slightly faster if reading and writing to files directly instead of relying on cat, shell pipes and shell redirection.

$file = fopen('in.sql', 'rb');
$outfile = fopen('out.sql', 'wb');

while(false != $l = fgets($file)){
    if (substr($l, 0, 6) === 'INSERT') fputs($outfile, process_line($l));
    else fputs($outfile, $l);
}

function process_line($line)
{
  // ... this is unchanged 
  return $ret;
}

Edit: can't reproduce this result, seems about the same now.

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