[ABE.pm] Automatic generation of database tables

Ted Fiedler fiedlert at gmail.com
Fri Mar 10 09:27:53 PST 2006


You'd have to generate the code to write out to SQL.  Here is a script
that takes a cvs file and creates a mysql table using DBI. Its from a
set of PC inventory scripts that I wrote. You may be able to reuse
some code to do what you need. I also got a lot of ideas from
http://perlmonks.org/?node_id=284436.

#!Perl
# buildinventorydb.pl
# BUilds the necessary db tables
# to extract pc inventory data
#
# Ted Fiedler
#
#use warnings;
use strict;
#use diagnostics;
use DBI;

###############################
# Hardcode the dir for now... #
###############################
my $DIR   = "C:\\Documents and
Settings\\TFIEDLER\\Desktop\\Code\\inventory\\invs";

#################
# Connect to db #
#################
my $dbh   = DBI->connect('DBI:mysql:Inventory:mysqlserver.mydom.com',
                         'username, 'pass',
                         { RaiseError => 1, AutoCommit => 1 });

my $productQuery            = qq{select ProductID from software};
my $sth                     = $dbh->prepare($productQuery);
$sth->execute;
my @PRODUCTIDs=();

while ( my ($id)            = $sth->fetchrow_array )
{
    push @PRODUCTIDs, $id;
}

my $pclistQuery             = qq{select pcname from pclist};
$sth                        = $dbh->prepare($pclistQuery);
$sth->execute;

my @PCs                     = ();

while ( my ($pc)            = $sth->fetchrow_array )
{
    push @PCs, $pc;
}

my %pclist                 = ();

my $drop_query             = qq{ DROP TABLE IF EXISTS pc_inventory };
$dbh->do($drop_query);

my $create_query           = qq{
    CREATE TABLE pc_inventory (id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
                               DisplayName varchar(75),
                               version varchar(25),
                               ProductID varchar(75),
                               pc varchar(20),
                               InstallDate varchar(20) ) };
$dbh->do($create_query);

my @fields                 = (qw(DisplayName version ProductID pc InstallDate));
my $fieldlist              = join ",", @fields;
my $field_placeholders     = join ", ", map {'?'} @fields;

my $insert_query           = qq{
    INSERT INTO pc_inventory ( $fieldlist )
    VALUES               ( $field_placeholders)};

my $new_sth                = $dbh->prepare( $insert_query );

my %known                  = ();

for my $pcname (@PCs)
{
    my $dir;
    open $dir, "$DIR/$pcname\.csv" or
        die "unable to open $DIR/$pcname\.csv:$!\n";

    for my $line (<$dir>)
    {
        chomp($line);

        next if ( $line    =~ /^Publisher.*/ );

        # Added to delete all instances of "+"
        $line              =~ s/\++//g;

        my ( $Publisher,
             $version,
             $InstallDate,
             $DisplayName,
             $ProductID )  = split /,/, $line;

        ###################################################
        # One of ProductID or DisplayName must be defined #
        # or we'll be sorting through excess garbage      #
        ###################################################
        next if ( defined ($ProductID) eq "" and
                  defined ($DisplayName) eq "" );

        for (@PRODUCTIDs)
        {
             $new_sth->execute($DisplayName, $version, $ProductID,
$pcname, $InstallDate) if
                              ( grep /$ProductID/, $_ );
        } ## End of "for (@PRODUCTIDs)"     ##

    }     ## End of "for my $line (<$dir>)" ##

}         ## End of "for my $pcname (@PCs)" ##

# __EOF__




On 3/10/06, Faber Fedor <faber at linuxnj.com> wrote:
> On Friday 10 March 2006 11:50 am, Ted Fiedler wrote:
> > Its been a while, but Ive used
> > http://search.cpan.org/~ezdb/Data-Table-1.43/Table.pm in the past for
> > things like this.
>
> That will easily(?) read the file into a table object, but I don't see how to
> generate an SQL table from it.  Am I missing something or do I do that?
>
> --
>
> Regards,
>
> Faber Fedor
> President
> Linux New Jersey, Inc.
> 908-320-0357
> 800-706-0701
>
> http://www.linuxnj.com
>
>
>
> _______________________________________________
> ABE-pm mailing list
> ABE-pm at pm.org
> http://mail.pm.org/mailman/listinfo/abe-pm
>


--
One of my most productive days was throwing away 1000 lines of code.

-- Ken Thompson


More information about the ABE-pm mailing list