<commit_before>#!/usr/bin/perl

use strict;
use warnings;

my $MIN_QUERY_LENGTH = 50;

my $stashed_query = "";
open (FILE, "postgresql2.log") or die "Couldn't open PostgreSQL logfile: $!";
while (<FILE>) {

    my $line = $_;

    # Handle lines containing 'SELECT'
    if ($line =~ /.*?PDT LOG:.*?(SELECT.*)/) {
        my $select_statement = $1;
        # print "select=$select_statement\n"; # for debugging
        # If this line contains what appear to be replaceable parameters,
        # stash the query
        
        if ($select_statement =~ /.*?(\$\d{1,3}).*/) {
            $stashed_query = $select_statement;
            # print "stashed_query=$stashed_query\n"; # for debugging
        # Otherwise, print the query
        } else {
            $stashed_query = "";
            print "$select_statement\n" if (length($select_statement) > $MIN_QUERY_LENGTH);
        }

    # Handle lines containing parameter values that should be applied to the
    # replaceable parameters in a just-stashed query
    } elsif (($line =~ /.*?PDT DETAIL:  parameters: (.*)/) && ($stashed_query ne "")) {
        my @parameter_pairs = split(',', $1);
        # print "parameter_pairs=@parameter_pairs\n"; # for debugging
        my $count = 0;
        foreach (@parameter_pairs) {
            my ($replaceable_parameter, $parameter_value) = split(" = ", $_);
            $replaceable_parameter =~ s/^\s*.//;
            # print "replaceable_parameter=$replaceable_parameter\n"; # for debugging
            # print "parameter_value=$parameter_value\n"; # for debugging
            # print "count=$count\n";
            $stashed_query =~  s/\$${replaceable_parameter}/${parameter_value}/;
            # print "stashed_query after $count=$stashed_query\n"; # for debugging
        }
        # Print the query after replacing parameters with their values
        # print "after replacement=";
        print "$stashed_query\n" if (length($stashed_query) > $MIN_QUERY_LENGTH);
        
    } else {
        # Do nothing; this line of the logfile isn't of interest
    }
    
}
close FILE;

=begin COMMENTS
foreach (@parameter_pairs) {
            @each_param = split(" = ", $_);
            $parameters{$each_param[0]} = $each_param[1];
             print "$parameters\n";
        } 
if contains PDT LOG:
  Take everything after that
  Take everything from the start of (must be capitalized, as per Nuxeo and reports) SELECT to EOL.
  Discard if doesn't meet length threshold
  Write to disk if doesn't contain replaceable parameters
if contains PDT DETAIL:  parameters:
  Take everything after that
  Split on comma separators
  Split on ' = ' separators
  $1 = '<EMAIL>', $2 = '2'
=end COMMENTS<commit_msg>CSPACE-5939: Script to extract queries now accepts command line options, including logfile name, and creates output directory for SQL queries.<commit_after>#!/usr/bin/perl

# Extracts a set of SQL SELECT queries from a PostgreSQL database logfile.

use strict;
use warnings;
use File::Basename;
use Getopt::Std;
use POSIX qw(strftime);

# Whether to echo printing of SELECT queries to the console.
# Set to '1' when doing interactive debugging.
my $echo_to_console = 1;

my $DEFAULT_MIN_QUERY_LENGTH = 50;
my $SELECT_STATEMENT_REGEX = ".*?PDT LOG:.*?(SELECT.*)";
my $CONTAINS_REPLACEABLE_PARAMS_REGEX = ".*?(\\\$[0-9]{1,3}).*";
my $PARAMETER_VALUES_REGEX = ".*?PDT DETAIL:  parameters: (.*)";

my %options=();
getopt('d:L:', \%options);

# Pathname of PostgreSQL logfile to open
#(assumed to be in local directory if no path provided)
my $logfile_name = $ARGV[0] || 'postgresql.log';

# Output directory to which query files will be written
my $output_dir = $options{d} || 'sql';
my $query_output_dir = create_query_output_dir($output_dir);

# Minimum length for queries of interest; shorter queries will be filtered out
my $min_query_length = $options{L} || $DEFAULT_MIN_QUERY_LENGTH;

my $stashed_query = "";
my $query_number = 0;

open (LOGFILE, $logfile_name) or die "Couldn't open PostgreSQL logfile $logfile_name: $!";
while (<LOGFILE>) {

    my $current_line = $_;

    # Handle lines containing 'SELECT'
    if ($current_line =~ /$SELECT_STATEMENT_REGEX/) {
        my $select_statement = $1;
        # If this line contains what appear to be replaceable parameters,
        # stash away the query, at least until the next line is read and
        # its values can be inserted in place of those parameters
        if ($select_statement =~ /$CONTAINS_REPLACEABLE_PARAMS_REGEX/) {
            $stashed_query = $select_statement;
        # Otherwise, print the query
        } else {
            if (length($select_statement) > $min_query_length) {
                $stashed_query = "";
                $query_number++;
                print_query("$select_statement", $query_number, $query_output_dir);
            }
        }

    # Handle lines containing parameter values that should be applied to the
    # replaceable parameters in a just-stashed query
    } elsif (($current_line =~ /$PARAMETER_VALUES_REGEX/) && ($stashed_query ne "")) {
        my @parameter_pairs = split(',', $1);
        my $count = 0;
        foreach (@parameter_pairs) {
            my ($replaceable_parameter, $parameter_value) = split(" = ", $_);
            # Strip leading whitespace and the leading dollar sign from the string to match
            $replaceable_parameter =~ s/^\s*.//;
            $stashed_query =~  s/\$${replaceable_parameter}/${parameter_value}/;
        }
        # Print the query after replacing parameters with their values
        if (length($stashed_query) > $min_query_length) {
            $query_number++;
            print_query("$stashed_query", $query_number, $query_output_dir);
        }
    } else {
        # Do nothing; this line of the logfile isn't of interest
    }
    
}
close LOGFILE;

sub print_query {
    my $select_statement = shift;
    my $query_number = shift;
    my $query_output_dir = shift;
    if ($echo_to_console) {
        print "$select_statement\n";
    }
}

# Borrowed from db_benchmark.pl script
sub create_query_output_dir {
	my $output_dir = shift;
	my $now_string = strftime('%Y-%m-%d-%H%M%S', localtime());
	my $query_output_dir = "$output_dir/$now_string";
	if (! -d $output_dir) {
	    mkdir $output_dir || die "Directory not found and can't be created: $output_dir $!";
	}
	mkdir $query_output_dir || die "Can't create directory: $query_output_dir $!";
	return $query_output_dir;
}

sub print_usage {
    my $script_name = basename($0);
    print <<"END_USAGE_INSTRUCTIONS";
$script_name:
Extracts a set of SQL SELECT queries from a PostgreSQL database logfile.

Usage:

$script_name [options] logfile
Options (defaults in parens)
-d name of output directory within the current directory (sql)
-L minimum length in chars of any SELECT queries to be exacted (50)
or
--help  prints this set of help instructions, then exits without running script
END_USAGE_INSTRUCTIONS
}

{'fraction_non_alphanumeric': 0.11781692174415247, 'fraction_numerical': 0.004764654923476754, 'mean_word_length': 3.0988165680473374, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 10, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6639538', 'n_tokens_mistral': 2166, 'n_tokens_neox': 2107, 'n_words': 646}