Skip to content

Instantly share code, notes, and snippets.

@divinity76
Forked from lavoiesl/process-mysqldump.c
Last active July 10, 2018 00:12
Show Gist options
  • Select an option

  • Save divinity76/494fef3d2ea1b800bb98e13db9c3ec3a to your computer and use it in GitHub Desktop.

Select an option

Save divinity76/494fef3d2ea1b800bb98e13db9c3ec3a to your computer and use it in GitHub Desktop.
Add newlines before parenthesis for a SQL mysqldump
// 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;
}
<?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;
}
@robehickman

robehickman commented Jan 25, 2018 •

Copy link
Copy Markdown

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.

@colinmollenhour

colinmollenhour commented Jul 9, 2018 •

Copy link
Copy Markdown

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment