use strict; use warnings;

use DBI;
use DBD::SQLite;
use IO::File;
use Text::CSV_XS;

my $db_file = 'ref_compare.db';
my $csv = Text::CSV_XS->new({sep_char=>"\t"});

## remove the db file if it exists
unlink $db_file if -f $db_file;

my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file",'','');

## create two tables.
## 1: For brd_sym_pn
$dbh->do(q'
   CREATE TABLE brd_sym_pn ( refdes TEXT, pnum TEXT, pkgtype TEXT )
');
## 2: For sym_text_latest
$dbh->do(q'
   CREATE TABLE sym_text_latest ( 
      logpnpkg TEXT, logpnum TEXT, logpkgtype TEXT 
   )
');

## ok, now load brd_sym_pn
my $sth = $dbh->prepare(q'
   INSERT INTO brd_sym_pn (refdes,pnum,pkgtype)
   VALUES (?,?,?)
');

my $brd_sym_pn_io = IO::File->new('brd_sym_pn.txt');

## use $brd_sym_pn_io->getline to skip any "header" rows

until ( $brd_sym_pn_io->eof ) {
   my $values = $csv->getline( $brd_sym_pn_io ); # parse data line
   for ( @$values ) { s/^\s+|\s+$/ } # trim lead/trail whitespace
   $sth->execute( @$values );  # inserts row into DB table
}


## ok, now load sym_text_latest
$sth = $dbh->prepare(q'
   INSERT INTO sym_text_latest (logpnpkg,logpnum,logpkgtype)
   VALUES (?,?,?)
');

my $sym_text_latest = IO::File->new('sym_text_latest.txt');

## use $sym_text_latest->getline to skip any "header" rows

until ( $sym_text_latest->eof ) {
   my $values = $csv->getline( $sym_text_latest ); # parse data line
   for ( @$values ) { s/^\s+|\s+$/ } # trim lead/trail whitespace
   $sth->execute( @$values );  # inserts row into DB table
}

## now you can use any query you want, even in other scripts
## let's find everything where pnums match, but pkgtypes don't:
$sth = $dbh->prepare(q'
   SELECT refdes, pnum, pkgtype, logpnum, logpkgtype
   FROM   brd_sym_pn, sym_text_latest
   WHERE  brd_sym_pn.pnum = sym_text_latest.logpnum
   AND    brd_sym_pn.pkgtype != sym_text_latest.logpkgtype
');
$sth->execute();

# print the results out.
print join "\t", qw/refdes pnum pkgtype logpnum logpkgtype/;
while ( my @row = $sth->fetchrow_array ) {
   print join "\t", @row;
}
[download]