[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