Search a tree, find strings in Perl code, check if there is SQL in them, extract tables.

chubby-wings.pl

#!/usr/bin/env perl

use 5.014;
use strict;
use warnings;

use File::Find;
use PPI;
use SQL::Statement;

my $SQLParser = SQL::Parser->new;

$SQLParser->{RaiseError} = 1;
$SQLParser->{PrintError} = 0;

my %TABLES;

run(\@ARGV);

sub run {
    my $argv = shift;
    my $top = $argv->[0];

    $top // die "Need top directory\n";

    find(
        {
            wanted => \&find_sql,
            no_chidr => 1,
        },
        $top
    );

    use YAML;
    print Dump \%TABLES;
}

sub find_sql {
    my $file = $File::Find::name;
    $file =~ / [.] (?: js | rb | t (?:pl)? ) | yml \z/x and return;
    my $doc = PPI::Document->new($file);
    return unless $doc;

    my $strings = $doc->find('PPI::Token::Quote');
    return unless $strings;

    my @strings = map $_->string, @$strings;

    for my $sql ( @$strings ) {
        $sql =~ /delete|insert|select/i or next;
        # printf("%s: --->%s<---\n", $file, $sql) and next;
        $sql =~ /\A(?:q|'|")/ and eval { $sql = eval $sql } or next;
        my $stmt = eval { SQL::Statement->new($sql, $SQLParser) };
        next unless ref $stmt;

        my $rel = $file;
        # $rel =~ s{\A ROOT}{}x; # string the initial path segment

        my @tables = map $_->name, $stmt->tables;
        push @{ $TABLES{$_} }, $rel for @tables;
    }

    return;
}