I'm trying to write a script in dart to convert dump of insert statements into copy statements

[insinfo]

I'm trying to write a script in dart to convert postgresql dump of insert statements into copy statements, could the sqlparser lib help me with this?

import 'dart:async';
import 'dart:io';
import 'dart:convert';
// works but uses RegExp
void main(List<String> arguments) async {
  if (arguments.length != 2) {
    print('Usage: dart pg_dump_converter.dart input.sql output.sql');
    return;
  }

  final inputFile = File(arguments[0]);
  final outputFile = File(arguments[1]);

  if (!inputFile.existsSync()) {
    print('Input file does not exist.');
    return;
  }

  final inputLines = inputFile.openRead()
      .transform(utf8.decoder)
      .transform(const LineSplitter());

  final output = outputFile.openWrite();

  final iterator = StreamIterator<String>(inputLines);

  bool collectingInsert = false;
  String currentTable = '';
  List<String> insertStatements = [];

  while (await iterator.moveNext()) {
    String line = iterator.current;
    String trimmedLine = line.trim();

    if (!collectingInsert) {
      if (trimmedLine.startsWith('INSERT INTO')) {
        // Start collecting INSERTs
        collectingInsert = true;
        currentTable = extractTableName(trimmedLine);
        insertStatements = [];
        insertStatements.add(line);

        // Collect the rest of the INSERT statement
        bool endOfInsert = trimmedLine.endsWith(';');
        while (!endOfInsert && await iterator.moveNext()) {
          line = iterator.current;
          insertStatements.add(line);
          trimmedLine = line.trim();
          if (trimmedLine.endsWith(';')) {
            endOfInsert = true;
          }
        }

        // Collect contiguous INSERTs from the same table
        while (true) {
          // Peek at the next line without consuming it
          if (await iterator.moveNext()) {
            String nextLine = iterator.current;
            String nextTrimmedLine = nextLine.trim();

            if (nextTrimmedLine.startsWith('INSERT INTO')) {
              String nextTable = extractTableName(nextTrimmedLine);
              if (nextTable == currentTable) {
                // Consume the next line and collect the INSERT
                insertStatements.add(nextLine);

                // Collect the rest of this INSERT statement
                endOfInsert = nextTrimmedLine.endsWith(';');
                while (!endOfInsert && await iterator.moveNext()) {
                  line = iterator.current;
                  insertStatements.add(line);
                  trimmedLine = line.trim();
                  if (trimmedLine.endsWith(';')) {
                    endOfInsert = true;
                  }
                }
              } else {
                // Different table, process the collected INSERTs
                await processInsertStatements(insertStatements, output);
                collectingInsert = false;
                // Process the current line (new INSERT from a different table)
                collectingInsert = true;
                currentTable = nextTable;
                insertStatements = [];
                insertStatements.add(nextLine);

                // Collect the rest of this INSERT statement
                endOfInsert = nextTrimmedLine.endsWith(';');
                while (!endOfInsert && await iterator.moveNext()) {
                  line = iterator.current;
                  insertStatements.add(line);
                  trimmedLine = line.trim();
                  if (trimmedLine.endsWith(';')) {
                    endOfInsert = true;
                  }
                }
              }
            } else {
              // Line is not an INSERT INTO
              // Process the collected INSERTs
              await processInsertStatements(insertStatements, output);
              collectingInsert = false;
              // Write the current line to the output file
              output.writeln(nextLine);
              break;
            }
          } else {
            // End of file
            await processInsertStatements(insertStatements, output);
            collectingInsert = false;
            break;
          }
        }
      } else {
        // Not an INSERT INTO, write the line to the output file
        output.writeln(line);
      }
    }
  }

  // In case we are still collecting INSERTs at the end of the file
  if (collectingInsert && insertStatements.isNotEmpty) {
    await processInsertStatements(insertStatements, output);
  }

  await output.close();
}

/// Function to process collected INSERTs and write the COPY command
Future<void> processInsertStatements(List<String> insertStatements, IOSink output) async {
  
  // Combine the INSERT statements into a single string
  String insertContent = insertStatements.join('\n');

  // Parse the INSERT statements
  var result = parseInsertStatements(insertContent);

  if (result != null) {
    String tableName = result['tableName'] as String;
    List<String> columns = result['columns'] as List<String>;
    List<List<String>> valuesList = result['values'] as List<List<String>>;

    // Write the COPY command
    output.writeln('COPY $tableName (${columns.join(', ')}) FROM stdin;');

    // Write the data rows
    for (var values in valuesList) {
      final dataLine = values.join('\t');
      output.writeln(dataLine);
    }

    // Write the end of the COPY command
    output.writeln('\\.');
  } else {
    // If parsing fails, write the original INSERTs
    for (var line in insertStatements) {
      output.writeln(line);
    }
  }
}

// Function to extract the table name from an INSERT statement
String extractTableName(String line) {
  final regex = RegExp(
    r'^INSERT\s+INTO\s+(\S+)\s*\(',
    caseSensitive: false,
  );

  final match = regex.firstMatch(line);
  if (match != null) {
    return match.group(1)!;
  }
  return '';
}

// Function to parse multiple INSERT statements
Map<String, dynamic>? parseInsertStatements(String insertContent) {
  // Use regular expression to match multiple INSERT statements
  final regex = RegExp(
    r'INSERT\s+INTO\s+(\S+)\s*\(([^)]+)\)\s+VALUES\s*(.+?);',
    caseSensitive: false,
    dotAll: true,
  );

  final matches = regex.allMatches(insertContent);

  if (matches.isEmpty) {
    print('Failed to parse INSERT statements.');
    return null;
  }

  String tableName = '';
  List<String> columns = [];
  List<List<String>> allValues = [];

  for (var match in matches) {
    String thisTableName = match.group(1)!;
    if (tableName.isEmpty) {
      tableName = thisTableName;
    } else if (tableName != thisTableName) {
      // Different table names in the collected INSERTs, cannot process
      print('Different table names in collected INSERTs.');
      return null;
    }

    String columnsPart = match.group(2)!;
    String valuesPart = match.group(3)!;

    // Extract columns
    if (columns.isEmpty) {
      columns = columnsPart.split(',').map((col) => col.trim().replaceAll('"', '')).toList();
    }

    // Parse the values
    List<List<String>> valuesList = parseValues(valuesPart);
    allValues.addAll(valuesList);
  }

  return {
    'tableName': tableName,
    'columns': columns,
    'values': allValues,
  };
}

// Function to parse the VALUES part of an INSERT statement
List<List<String>> parseValues(String valuesPart) {
  List<List<String>> valuesList = [];
  int pos = 0;
  int length = valuesPart.length;

  while (pos < length) {
    // Skip whitespace
    while (pos < length && isWhitespace(valuesPart[pos])) {
      pos++;
    }

    // Expect '('
    if (pos >= length || valuesPart[pos] != '(') {
      // No more tuples
      break;
    }
    pos++; // Skip '('

    List<String> values = [];
    StringBuffer valueBuffer = StringBuffer();
    bool inString = false;

    while (pos < length) {
      String c = valuesPart[pos];

      if (inString) {
        if (c == "'") {
          if (pos + 1 < length && valuesPart[pos + 1] == "'") {
            // Escaped single quotes
            valueBuffer.write("''");
            pos += 2;
          } else {
            // End of string
            valueBuffer.write(c);
            pos++;
            inString = false;
          }
        } else {
          valueBuffer.write(c);
          pos++;
        }
      } else {
        if (c == "'") {
          // Start of string
          inString = true;
          valueBuffer.write(c);
          pos++;
        } else if (c == ',') {
          // End of value
          values.add(processValue(valueBuffer.toString()));
          valueBuffer.clear();
          pos++;
        } else if (c == ')') {
          // End of tuple
          values.add(processValue(valueBuffer.toString()));
          valueBuffer.clear();
          pos++;
          break;
        } else {
          valueBuffer.write(c);
          pos++;
        }
      }
    }

    valuesList.add(values);

    // Skip whitespace
    while (pos < length && isWhitespace(valuesPart[pos])) {
      pos++;
    }

    // Check for a comma between tuples
    if (pos < length && valuesPart[pos] == ',') {
      pos++;
    } else {
      break;
    }
  }

  return valuesList;
}

// Helper function to check for whitespace
bool isWhitespace(String c) {
  return c == ' ' || c == '\t' || c == '\n' || c == '\r';
}

// Function to process an individual value
String processValue(String value) {
  value = value.trim();
  if (value.startsWith("'") && value.endsWith("'")) {
    // Remove the surrounding quotes
    value = value.substring(1, value.length - 1);
    // Replace escaped single quotes
    value = value.replaceAll("''", "'");
    // Escape backslashes
    value = value.replaceAll('\\', '\\\\');
    // Escape tabs
    value = value.replaceAll('\t', '\\t');
    // Escape newlines and carriage returns
    value = value.replaceAll('\r\n', '\\r\\n');
    value = value.replaceAll('\n', '\\n');
    value = value.replaceAll('\r', '\\r');
    // Remove any actual newlines
    value = value.replaceAll(RegExp(r'[\n\r]'), '');
  } else if (value.toUpperCase() == 'NULL') {
    value = '\\N';
  } else {
    value = value.trim();
  }
  return value;
}

input

INSERT INTO public.pessoas (id, nome, codigo) VALUES (1, 'Isaque', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (2, 'Cintia', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (3, 'Leo', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (5, NULL, NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (7, '', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (8, 'ASa insert quebra de linha 
insert
dentro de value', NULL);

output

COPY public.pessoas (id, nome, codigo) FROM stdin;
1	Isaque	\N
2	Cintia	\N
3	Leo	\N
5	\N	\N
7		\N
8	ASa insert quebra de linha \ninsert\ndentro de value	\N
\.

[simolus3]

I should mention that sqlparser was designed with SQLite in mind only, but it's probably resilient enough to give you decent ASTs for other dialects using a slightly different syntax as well. In your case, where the insert statements are directly matching SQLite syntax, it works perfectly fine of course:

import 'package:sqlparser/sqlparser.dart';

void main(List<String> arguments) async {
  final parser = SqlEngine();
  final stmts = parser.parseMultiple('''
INSERT INTO public.pessoas (id, nome, codigo) VALUES (1, 'Isaque', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (2, 'Cintia', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (3, 'Leo', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (5, NULL, NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (7, '', NULL);
INSERT INTO public.pessoas (id, nome, codigo) VALUES (8, 'ASa insert quebra de linha 
insert
dentro de value', NULL);
''');

  for (final stmt in stmts.rootNode.childNodes) {
    if (stmt is InsertStatement) {
      final table = stmt.table.span!.text;
      final source = stmt.source;

      if (source is! ValuesSource) {
        print('Unknown insert statement: $source');
        continue;
      }

      for (final tuple in source.values) {
        final entries = tuple.expressions.map((e) {
          return switch (e) {
            StringLiteral(:final value) => value,
            NullLiteral() => null,
            NumericLiteral(:final value) => value,
            _ => throw 'Unsupported expression $e',
          };
        });

        // This is where you'd record the insert to transform it into the copy - all the values are here
        print('Found insert to $table with entries ${entries.toList()}');
      }
    } else {
      print('Unknown statement! $stmt');
    }
  }
}

If your statements aren't necessarily semicolon-separated, you can also keep using your LineSplitter-based solution and use engine.parse() to parse a single statement that doesn't have to end in a semicolon.