Re: How do I create a sort sub on-the-fly?

[jeroenes]

I would avoid writing such elaborate sorting subs and use DBD::RAM. This is the first timer for me, BTW.

This is yet untested tested now:

use DBI;
use SuperSplit;
 
my $data;
{
  local $/ = undef;
  $data = <DATA>;
}
$data=~tr/\-/,/;
 
my $dbh = DBI->connect('DBI:RAM:','usr','pwd',{RaiseError=>1});
  $dbh->func({ table_name  => 'file',
      col_names   => 'source,year,month,time,sip,sport,dip,dport,hits,
+acl,mlnum',
      data_type   => 'CSV',
      data_source => $data,
      }, 'import' );
my $ar =  $dbh->selectall_arrayref(qq[SELECT * FROM file ORDER by  spo
+rt asc,  time desc ]);
 
print superjoin(',',"\n", $ar );

__DATA__
Monmouth,2000-05-2000:00:09-04,192.35.75.69,138,192.100.255,66,2,105,1
+234
Jackson,2000-04-2100:00:10-05,192.35.12.03,144,192.67.29,134,8,101,148
+7
Meade,2001-01-0500:00:11-04,213.132.32,175,184.57.62.35,151,12,107,153
+2
Yuma,200-03-1100:00:12-05,210.0.0.0,156,192.78.54.21,156,10,105,1578
Monmouth,200-04-1000:00:10-08,63.0.0.0,125,192.45.67.2,159,16,101,1879
Lewis,2000-03-1200:21:45-32,167.54.80.65,144,192.67.29,134,32,107,1487
Hero,2001-01-0500:00:11-04,211.34.78.93,132,184.57.62.35,151,12,101,15
+32
Jackson,2000-01-0500:00:11-04,193.0.0.195,175,184.57.62.35,151,14,105,
+153

Of course you will have to write your own sorts here, but that is fairly easy now, just adjust the SQL ORDER string.

HTH,

Jeroen
"We are not alone"(FZ)

[pope]

Perhaps you mean DBD::AnyData? DBD::RAM is obsoleted by this one.

Anyway, you've showed an interesting way to treat the problem as DBI query! ++ for that! (DBD::CSV should do it, too)

[jeroenes]

I had no idea that DBD:RAM was obsoleted. I remembered some merlyn-WT with it, so I just installed it to try. Will check out DBD::Anydata of course... thanks.

Update I got back at my root 'konsole' and found that anaydata failed the tests.. is that a known bug? ... i tried and tried... AnayData actually hangs on the.... I also tried cpan://Anydata... to no avail.

[pope]

No, it should pass all tests. It requires AnyData. It just behaves a little bit different from DBD::CSV when dealing with CSV data. It seems to expect that all non null fields to be escaped properly, which is not the case with DBD::CSV where a field needs escapes only when it is necessary. I was bitten by this quirk once.

[jeroenes]

I think DBD::RAM uses DBD::CSV internally.

[CharlesClarkson]

I originally used Sort::Fields to create the report, but as I mention below most beginners don't like to use modules outside the standard ones.

#!/usr/bin/perl
use strict;
use diagnostics;
use Sort::Fields;

my (@records, @report);
{
    my $data_file = 'test.dat';
    open my $fh, $data_file or
                    die "Can't open $data_file: $!";
        while (<$fh>) {
            # skip blank and commented lines
            next if /^\s*#/;
            next if /^\s*$/;

            # We'll look for lines that describe a report:
            if (/report:\s+sort\s+(\S*)/ )  {
                @report = split /,/, $1;
                next;
            }
            push @records, $_;
        }
}

my (@columns, @sort_description);
{
    my %field = (   source  => 1,
                    time    => 2,
                    sip     => 3,
                    sport   => 4,
                    dip     => 5,
                    dport   => 6,
                    hits    => 7,
                    acl     => 8,
                    lnum    => 9
                    );
    foreach (@report) {
        my ($name, $order) = split /-/;
        my $suffix = $name =~ /source|time/ ? '' : 'n';
        if ($order eq 'd') {
            push @sort_description, "$name:\t\tdescending\n";
            push @columns, "-$field{$name}$suffix";
        } else {
            push @sort_description, "$name:\t\tascending\n";
            push @columns, "$field{$name}$suffix";
        }
    }
}

{
    ### store report data in file
    my $report_file_name = "out.txt";
    open my $fh, '>', $report_file_name or
                    die "Can't Open $report_file_name: $!";
        my $date    = localtime;
        $date       =~ s/ /-/g;
        print $fh
            qq|\t\tREQUEST FOR SORTING\n\n|,
            @sort_description,
            qq|\n\nFILE WAS GENERATED ON:  $date\n\n|,
            fieldsort ',', \@columns, @records;
}
__END__




Thanks for your reply,
Charles K. Clarkson