#!/usr/bin/perl

use strict;
use DBI;

# Make sure you import sr21.sql first
# Don't forget to change the password, etc
my $dbh = DBI->connect('DBI:mysql:usdasr21:localhost:3306', 'usda', 'password');

# The key is the name of the file, the value is the number of fields
my %files = (
    'ABBREV.txt'   => 51,
    'DATA_SRC.txt' => 9,
    'DATSRCLN.txt' => 3,
    'DERIV_CD.txt' => 2,
    'FD_GROUP.txt' => 2,
    'FOOD_DES.txt' => 14,
    'FOOTNOTE.txt' => 5,
    'NUT_DATA.txt' => 17,
    'NUTR_DEF.txt' => 6,
    'SRC_CD.txt'   => 2,
    'WEIGHT.txt'   => 7,
);

for my $file ( keys %files ) {
    my $table = $file;
    $table =~ s/\.txt//;

    # The DBI lets us pass ? instead of the actual value now,
    # and then pass the value later. Not that SQL injection
    # should be an issue here, but it's good practice.
    my $values = join ',', qw/?/ x $files{$file};

    # The files are in CRLF format, but some fields have LF
    # characters in them. This makes sure the fields parse
    # properly.
    local $/ = "\r\n";

    open SRFILE, "<$file";
    while ( my $line = <SRFILE> ) {
        # Tildes denote text fields, but we don't need that here.
        $line =~ s/~//g;

        # Break into fields.
        my @vals = split( /\^/, $line );

        # We don't need to say what the fields are, since the
        # SQL table definitions had them in the right order.
        my $sql = "INSERT INTO $table VALUES ( $values )";
        query( $sql, @vals );
    }
    close SRFILE;
}

exit;

# prepare and execute query, all at once
sub query {
    my ( $query, @values ) = @_; 
    my $sth = $dbh->prepare($query);
    $sth->execute( @values );
    return $sth;
}