<commit_before>#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use DBD::Pg;

package TableUsageGraphs;

BEGIN
{
	require Exporter;

	our $VERSION = 1.00;

	our @ISA = qw(Exporter);

	our @EXPORT = qw(generate_table_usage_graphs);
}

sub draw_graph
{
	my ($graphdir, $toplevelfunction, $graph) = @_;

	open(my $pipe, "| dot -Tsvg -o $graphdir/tlf$toplevelfunction.svg") or die "could not fork";

	print $pipe "strict digraph $graph->{toplevelfunctionname} {\n";
	print $pipe "rankdir = LR;\nnode [shape=record]\n";

	my $tables = $graph->{tables};
	foreach my $table (keys %{$tables})
	{
		foreach my $referenced_table (@{$tables->{$table}->{confrelids}})
		{
			print $pipe "\"$table\":name -> \"$referenced_table\":name;\n";
		}

		my $tref = $tables->{$table};
		print $pipe "\"$table\" [label=\"<name> $tref->{relname}|" .
					"sequential scans: $tref->{seq_scan}|avg seq tuples: $tref->{seq_tup_read}|" .
					"index scans: $tref->{idx_scan}|avg idx tuples: $tref->{idx_tup_read}|" .
					"inserted rows: $tref->{n_tup_ins}|updated rows: $tref->{n_tup_upd}|" .
					"deleted rows: $tref->{n_tup_del}\"]\n";
	}

	print $pipe "}\n";

	close($pipe);
	die "dot failed for table graph $toplevelfunction" if $? != 0;
}

sub generate_table_usage_graphs
{
	my ($graphdir, $dbh, $system_catalogs) = @_;

	my $table_list_query = 
<<"SQL";
	WITH TopLevelFunctionTableUsage AS (
	    SELECT
	        TopLevelFunction, relid, sum(seq_scan) AS seq_scan, sum(seq_tup_read) AS seq_tup_read,
	                                 sum(idx_scan) AS idx_scan, sum(idx_tup_read) AS idx_tup_read,
									 sum(n_tup_ins) AS n_tup_ins, sum(n_tup_upd) AS n_tup_upd,
									 sum(n_tup_del) AS n_tup_del
	    FROM
	        call_graph.TableUsage tu
	    JOIN
	        call_graph.CallGraphs cg
	            ON (tu.CallGraphID = cg.CallGraphID)
	    GROUP BY
	        TopLevelFunction, relid
	)
	SELECT
	    tu.TopLevelFunction AS TopLevelFunction, tlf.proname AS TopLevelFunctionName,
		tu.relid AS relid, pg_class.relname AS relname,
		pg_constraint.confrelid AS confrelid,
		seq_scan, CASE WHEN seq_scan > 0 THEN round(seq_tup_read / seq_scan::numeric, 2) ELSE 0 END AS seq_tup_read,
		idx_scan, CASE WHEN idx_scan > 0 THEN round(idx_tup_read / idx_scan::numeric, 2) ELSE 0 END AS idx_tup_read,
		n_tup_ins, n_tup_upd, n_tup_del
	FROM
	    TopLevelFunctionTableUsage tu
	JOIN
	    $system_catalogs->{pg_class} pg_class
	        ON (pg_class.oid = tu.relid)
	JOIN
		$system_catalogs->{pg_proc} tlf
			ON (tu.TopLevelFunction = tlf.oid)
	LEFT JOIN
	    $system_catalogs->{pg_constraint} pg_constraint
	        ON (pg_constraint.contype = 'f' AND pg_constraint.conrelid = tu.relid AND
				-- omit self references
				tu.relid <> pg_constraint.confrelid AND
	            EXISTS (SELECT * FROM TopLevelFunctionTableUsage tu2
	            		WHERE tu2.TopLevelFunction = tu.TopLevelFunction AND
							  tu2.relid = pg_constraint.confrelid))
SQL
	;

	my $sth = $dbh->prepare($table_list_query);
	$sth->execute();

	return undef if ($sth->rows <= 0);

	my %graphs;
	while (my $row = $sth->fetchrow_hashref)
	{
		if (!exists $graphs{$row->{toplevelfunction}})
		{
			$graphs{$row->{toplevelfunction}} = { toplevelfunctionname => $row->{toplevelfunctionname},
												  tables => {} };
		}

		my $tables = $graphs{$row->{toplevelfunction}}{tables};

		if (!exists $tables->{$row->{relid}})
		{
			$tables->{$row->{relid}} = { relname => $row->{relname},
										 confrelids => [],
										 seq_scan => $row->{seq_scan},
										 seq_tup_read => $row->{seq_tup_read},
										 idx_scan => $row->{idx_scan},
										 idx_tup_read => $row->{idx_tup_read},
										 n_tup_ins => $row->{n_tup_ins},
										 n_tup_upd => $row->{n_tup_upd},
										 n_tup_del => $row->{n_tup_del}
										 };
		}
		else
		{
			$tables->{$row->{relid}}->{seq_scan} += $row->{seq_scan};
			$tables->{$row->{relid}}->{seq_tup_read} += $row->{seq_tup_read};
			$tables->{$row->{relid}}->{idx_scan} += $row->{idx_scan};
			$tables->{$row->{relid}}->{idx_tup_read} += $row->{idx_tup_read};
			$tables->{$row->{relid}}->{n_tup_ins} += $row->{n_tup_ins};
			$tables->{$row->{relid}}->{n_tup_upd} += $row->{n_tup_upd};
			$tables->{$row->{relid}}->{n_tup_del} += $row->{n_tup_del};
		}

		if (defined $row->{confrelid})
		{
			push @{$tables->{$row->{relid}}{confrelids}}, $row->{confrelid};
		}
	}

	foreach my $graph (keys %graphs)
	{
		draw_graph($graphdir, $graph, $graphs{$graph})
			if (scalar keys %{$graphs{$graph}->{tables}} > 0);
	}

	return \%graphs;
}

END
{
}

1;<commit_msg>Fix statistics calculation bug in table usage graphs.<commit_after>#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use DBD::Pg;

package TableUsageGraphs;

BEGIN
{
	require Exporter;

	our $VERSION = 1.00;

	our @ISA = qw(Exporter);

	our @EXPORT = qw(generate_table_usage_graphs);
}

sub draw_graph
{
	my ($graphdir, $toplevelfunction, $graph) = @_;

	open(my $pipe, "| dot -Tsvg -o $graphdir/tlf$toplevelfunction.svg") or die "could not fork";

	print $pipe "strict digraph $graph->{toplevelfunctionname} {\n";
	print $pipe "rankdir = LR;\nnode [shape=record]\n";

	my $tables = $graph->{tables};
	foreach my $table (keys %{$tables})
	{
		foreach my $referenced_table (@{$tables->{$table}->{confrelids}})
		{
			print $pipe "\"$table\":name -> \"$referenced_table\":name;\n";
		}

		my $tref = $tables->{$table};
		print $pipe "\"$table\" [label=\"<name> $tref->{relname}|" .
					"sequential scans: $tref->{seq_scan}|avg seq tuples: $tref->{seq_tup_read}|" .
					"index scans: $tref->{idx_scan}|avg idx tuples: $tref->{idx_tup_read}|" .
					"inserted rows: $tref->{n_tup_ins}|updated rows: $tref->{n_tup_upd}|" .
					"deleted rows: $tref->{n_tup_del}\"]\n";
	}

	print $pipe "}\n";

	close($pipe);
	die "dot failed for table graph $toplevelfunction" if $? != 0;
}

sub generate_table_usage_graphs
{
	my ($graphdir, $dbh, $system_catalogs) = @_;

	my $table_list_query = 
<<"SQL";
	WITH TopLevelFunctionTableUsage AS (
	    SELECT
	        TopLevelFunction, relid, sum(seq_scan) AS seq_scan, sum(seq_tup_read) AS seq_tup_read,
	                                 sum(idx_scan) AS idx_scan, sum(idx_tup_read) AS idx_tup_read,
									 sum(n_tup_ins) AS n_tup_ins, sum(n_tup_upd) AS n_tup_upd,
									 sum(n_tup_del) AS n_tup_del
	    FROM
	        call_graph.TableUsage tu
	    JOIN
	        call_graph.CallGraphs cg
	            ON (tu.CallGraphID = cg.CallGraphID)
	    GROUP BY
	        TopLevelFunction, relid
	)
	SELECT
	    tu.TopLevelFunction AS TopLevelFunction, tlf.proname AS TopLevelFunctionName,
		tu.relid AS relid, pg_class.relname AS relname,
		pg_constraint.confrelid AS confrelid,
		seq_scan, CASE WHEN seq_scan > 0 THEN round(seq_tup_read / seq_scan::numeric, 2) ELSE 0 END AS seq_tup_read,
		idx_scan, CASE WHEN idx_scan > 0 THEN round(idx_tup_read / idx_scan::numeric, 2) ELSE 0 END AS idx_tup_read,
		n_tup_ins, n_tup_upd, n_tup_del
	FROM
	    TopLevelFunctionTableUsage tu
	JOIN
	    $system_catalogs->{pg_class} pg_class
	        ON (pg_class.oid = tu.relid)
	JOIN
		$system_catalogs->{pg_proc} tlf
			ON (tu.TopLevelFunction = tlf.oid)
	LEFT JOIN
	    $system_catalogs->{pg_constraint} pg_constraint
	        ON (pg_constraint.contype = 'f' AND pg_constraint.conrelid = tu.relid AND
				-- omit self references
				tu.relid <> pg_constraint.confrelid AND
	            EXISTS (SELECT * FROM TopLevelFunctionTableUsage tu2
	            		WHERE tu2.TopLevelFunction = tu.TopLevelFunction AND
							  tu2.relid = pg_constraint.confrelid))
SQL
	;

	my $sth = $dbh->prepare($table_list_query);
	$sth->execute();

	return undef if ($sth->rows <= 0);

	my %graphs;
	while (my $row = $sth->fetchrow_hashref)
	{
		if (!exists $graphs{$row->{toplevelfunction}})
		{
			$graphs{$row->{toplevelfunction}} = { toplevelfunctionname => $row->{toplevelfunctionname},
												  tables => {} };
		}

		my $tables = $graphs{$row->{toplevelfunction}}{tables};

		if (!exists $tables->{$row->{relid}})
		{
			$tables->{$row->{relid}} = { relname => $row->{relname},
										 confrelids => [],
										 seq_scan => $row->{seq_scan},
										 seq_tup_read => $row->{seq_tup_read},
										 idx_scan => $row->{idx_scan},
										 idx_tup_read => $row->{idx_tup_read},
										 n_tup_ins => $row->{n_tup_ins},
										 n_tup_upd => $row->{n_tup_upd},
										 n_tup_del => $row->{n_tup_del}
										 };
		}
		
		# Note that if the row exists, we don't want to do anything to the statistics;
		# those are duplicated for each confrelid and summing them would be wrong.

		if (defined $row->{confrelid})
		{
			push @{$tables->{$row->{relid}}{confrelids}}, $row->{confrelid};
		}
	}

	foreach my $graph (keys %graphs)
	{
		draw_graph($graphdir, $graph, $graphs{$graph})
			if (scalar keys %{$graphs{$graph}->{tables}} > 0);
	}

	return \%graphs;
}

END
{
}

1;
{'fraction_non_alphanumeric': 0.16076479239733002, 'fraction_numerical': 0.003620319040615454, 'mean_word_length': 3.3397152675503192, 'pattern_counts': {'":': 4, '<': 13, '<?xml version=': 0, '>': 147, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2728933', 'n_tokens_mistral': 3944, 'n_tokens_neox': 3383, 'n_words': 682}