From 326705eb376c6731d9b7ce701dbdfe28bf68d46e Mon Sep 17 00:00:00 2001
From: Abigail <abigail@abigail.be>
Date: Thu, 5 Nov 2020 18:53:56 +0100
Subject: Allow testing of SQL solutions.

Testing SQL requires to lot more work from the test program than testing
other languages. For other languages, they just read the input, and
calculate what ever needs to be calculated.

For SQL, things are a bit different. We need to create a database
(SQLite, in memory), create some tables (from a file .tables),
and, for each test, clear the tables, and fill it with data.
And since we have a habit to have actual multiple tests per file,
we need some machinery to deal with that setup.
---
 challenge-085/abigail/test.pl | 127 +++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 126 insertions(+), 1 deletion(-)

diff --git a/challenge-085/abigail/test.pl b/challenge-085/abigail/test.pl
index 6f26ad7159..74cf691f6e 100755
--- a/challenge-085/abigail/test.pl
+++ b/challenge-085/abigail/test.pl
@@ -16,6 +16,7 @@ chdir ".." if -f "../test.pl";
 use experimental 'signatures';
 
 use Test::More;
+use DBI;
 
 
 my %languages = (
@@ -42,12 +43,17 @@ my %languages = (
         exe     =>   "/usr/bin/cc",
         ext     =>   "c",
         dir     =>   "c",
-    }
+    },
+    SQL         =>   {
+        ext     =>   "sql",
+    },
 );
 
 my $perl_exe = $languages {Perl} {exe};
 
 foreach my $challenge (1, 2) {
+    my ($dbh, $query, $tables_info);   # Only for SQL tests.
+
     my @inputs = <input-$challenge-*> or next;
     subtest "Challenge $challenge" => sub {
         foreach my $language (sort keys %languages) {
@@ -70,6 +76,17 @@ foreach my $challenge (1, 2) {
                 system $exe, "-o", $compiled, $source;
             }
 
+            #
+            # SQL requires requires creating an in-memory database,
+            # and loading some tables. For that, we need a .tables
+            # file. We also read the actual query at this time.
+            #
+            if ($language eq "SQL") {
+                my $tables = $source =~ s/\.\Q$ext\E$/.table/r;
+                next unless -r $tables;
+               ($dbh, $query, $tables_info) = init_sql ($source, $tables);
+            }
+
             subtest $language => sub {
                 foreach my $input (@inputs) {
                     my $output_exp = ($input =~ s/input/output/r) . ".exp";
@@ -84,6 +101,9 @@ foreach my $challenge (1, 2) {
                     if ($compiled) {
                         $got = `$perl_exe -ple '$filter' $input | ./$compiled`;
                     }
+                    elsif ($language eq "SQL") {
+                        $got = test_sql ($dbh, $query, $tables_info, $input);
+                    }
                     else {
                         $got = `$perl_exe -ple '$filter' $input |\
                                 $exe @args ./$source`;
@@ -101,5 +121,110 @@ foreach my $challenge (1, 2) {
 
 done_testing;
 
+#
+# Parse the tables SQL, extract the table names, and the column names,
+# *EXCLUDING* any primary key of the form "integer PRIMARY KEY"
+# We're assuming some sane formatting (one column per line).
+#
+# Returns an array of arrays. Each (inner) array consists of a table
+# name, followed by the name of the columns of that table.
+#
+# We will also create the database handle, use it to create the tables,
+# and return the database handle as a second value.
+#
+sub init_sql ($query_file, $tables_file) {
+    my $query  = `cat $query_file`;
+    my $tables = `cat $tables_file`;
+
+    my $in_table = 0;
+    my @info;
+    foreach (split /\n/ => $tables) {
+        if (!$in_table) {
+            if (/^\s* CREATE \s+ TABLE \s+ (\w+)/xi) {
+                $in_table = 1;
+                push @info => [$1];
+            }
+            next;
+        }
+        else {
+            if (/^\s* \)/x) {
+                $in_table = 0;
+                next;
+            }
+            # Any other line is a column definition
+            next if /^ \s* \w+ \s+ integer \s+ PRIMARY \s+ KEY \s*,/xi;
+            if (/^ \s* (\w+)/x) {
+                push @{$info [-1]} => $1;
+            }
+        }
+    }
+
+    my $dbh = DBI:: -> connect ("dbi:SQLite:dbname=:memory:", "", "",
+                                {RaiseError   =>  1,
+                                 PrintError   =>  1,
+                                 AutoCommit   =>  1});
+    $dbh -> do ($tables);
+
+    return ($dbh, $query, \@info);
+}
+
+
+sub test_sql ($dbh, $query, $tables_info, $input) {
+    #
+    # For now, assume we each set of N lines, where N is the number of tables
+    # is a test. We also assume that if a line has P items (space separated),
+    # and the corresponing table has Q columns (not counting any integer primary
+    # keys, as SQLite fills them automatically), we have to fill int (P/Q) rows.
+    #
+
+    #
+    # Read the input
+    #
+    open my $i_fh, "<", $input or die "Failed to open $input: $!";
+    my @input = <$i_fh>;
+
+    my $output = "";
+
+  TEST:
+    while (@input >= @$tables_info) {
+        foreach my $table_info (@$tables_info) {
+            my ($table, @fields) = @$table_info;
+            my $input            = shift @input;
+            my @values           = split ' ' => $input;
+            last TEST if @values < @fields;
+            #
+            # Clear the table
+            #
+            $dbh -> do ("DELETE FROM $table");
+
+            #
+            # Construct an input query
+            #
+            my $place  = "(" . join (", " => ("?") x @fields) . ")";
+            my $insert = do {local $" = ", "; <<~ "--"};
+                INSERT
+                  INTO  $table
+                       (@fields)
+                VALUES  @{[($place) x (@values / @fields)]}
+            --
+
+            $dbh -> do ($insert, undef, @values);
+
+            #
+            # Run the query. If we have multiple results, join columns
+            # by spaces, and rows by newlines.
+            #
+            my $result = $dbh -> selectall_arrayref ($query);
+
+            $output .= join "\n" => map {join " " => @$_} @$result;
+            $output .= "\n";
+        }
+    }
+
+    $output;
+}
+
+
+
 
 __END__
-- 
cgit