#!/usr/bin/perl --
#~ 2011-07-15-22:39:54 by Anonymous Monk
#~ perltidy -csc -otr -opr -ce -nibc -i=4     

# use lib grep -d, '/home/bcnagle/perl', ( getpwuid $>  )[7].'/perl/';
use strict;
use warnings;
use XML::Twig;
use DBI;

Main( @ARGV );
exit( 0 );

sub Main {
#~     DoTheDo( connect_to_db() , '/home/bcnagle/file.xml');
    DoTheDo( connect_to_db(), DemoData() );
}

BEGIN {
    our @Atts = (
        'path',         'Product_ID',    'Updated', 'Quality',
        'Supplier_id',  'Prod_ID',       'Catid',   'On_Market',
        'Model_Name',   'Product_View',  'HighPic', 'HighPicSize',
        'HighPicWidth', 'HighPicHeight', 'Date_Added'
    );
    sub DoTheDo {
        my( $dbh, $xmlFile ) = @_;
        
        my $insert = $dbh->prepare(
            sprintf 'INSERT INTO files (%s) VALUES (%s)',
            join( ',', map { $dbh->quote_identifier($_) } @Atts ),
            join( ',', map { '?' } 1 .. @Atts ),
        );
        
        my $callback = sub  {
            my( $twig, $file ) = @_; 
            $insert->execute( map { '' . $file->att($_) } @Atts );  
            $twig->purge;
        };
        
        my $twig = XML::Twig->new(
#~             twig_handlers => {
            start_tag_handlers => { # if you only want <file> without children
                'filesindex/file' => $callback,
            }
        );
        
    #~     $twig->parsefile( $xmlFile );
        $twig->xparse( $xmlFile );
        $dbh->disconnect();
        
    } ## end sub DoTheDo

    sub connect_to_db {
        my $dbh = DBI->connect(
            'dbi:SQLite:dbname=short.test.sqlite',
            undef, undef, { RaiseError => 1, PrintError => 1, },
        );
        
        my $sql = sprintf 'CREATE TABLE files ( %s )',
            join( ",\n", map { $dbh->quote_identifier($_)." TEXT " } @Atts );
        print "\n$sql\n";
        eval { $dbh->do( $sql ) } or warn $@;
        return $dbh;

    } ## end sub connect_to_db

} ## end BEGIN

sub DemoData {
## xml_pp -s record
## http://perlmonks.com/?abspart=1;displaytype=displaycode;node_id=914742;part=2
    my $xml = <<'__XML__';
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ICECAT-interface SYSTEM "http://data.icecat.biz/dtd/files.index.dtd">

<!-- source: ICEcat.biz 2011 -->
<ICECAT-interface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://data.icecat.biz/xsd/files.index.xsd">
  <filesindex Generated="20110712102037">
    <file Catid="978" Date_Added="20050627000000" HighPic="http://images.icecat.biz/img/norm/high/19311-1470.jpg" HighPicHeight="200" HighPicSize="13817" HighPicWidth="320" Model_Name="016166400" On_Market="1" Prod_ID="016166400" Product_ID="19311" Product_View="15591" Quality="ICECAT" Supplier_id="30" Updated="20110711025126" path="export/freexml.int/EN/19311.xml">
      <EAN_UPCS> 
        <EAN_UPC Value="0042215447881"/> 
        <EAN_UPC Value="0422154478816"/> </EAN_UPCS>
      <Country_Markets> 
        <Country_Market Value="FR"/> 
        <Country_Market Value="UK"/> 
        <Country_Market Value="DE"/> 
        <Country_Market Value="DK"/> 
        <Country_Market Value="PL"/> 
        <Country_Market Value="CH"/> 
        <Country_Market Value="CZ"/> 
        <Country_Market Value="AT"/> </Country_Markets>
    </file>
  </filesindex>
</ICECAT-interface>
__XML__

    return $xml
} ## end sub DemoData

__END__

##</code><code>##

CREATE TABLE files ( "path" TEXT ,
"Product_ID" TEXT ,
"Updated" TEXT ,
"Quality" TEXT ,
"Supplier_id" TEXT ,
"Prod_ID" TEXT ,
"Catid" TEXT ,
"On_Market" TEXT ,
"Model_Name" TEXT ,
"Product_View" TEXT ,
"HighPic" TEXT ,
"HighPicSize" TEXT ,
"HighPicWidth" TEXT ,
"HighPicHeight" TEXT ,
"Date_Added" TEXT  )

##</code><code>##

$ dbish dbi:SQLite:dbname=short.test.sqlite
Useless localization of scalar assignment at c:/perl/site/5.12.2/lib/DBI/Format.pm line 377.
DBI::Shell 11.95 using DBI 1.616

WARNING: The DBI::Shell interface and functionality are
=======  very likely to change in subsequent versions!


Connecting to 'dbi:SQLite:dbname=short.test.sqlite' as ''...
@dbi:SQLite:dbname=short.test.sqlite> select * from files;
path,Product_ID,Updated,Quality,Supplier_id,Prod_ID,Catid,On_Market,Model_Name,Product_View,HighPic,HighPicSize,HighPicWidth,HighPicHeight,Date_Added
'export/freexml.int/EN/19311.xml','19311','20110711025126','ICECAT','30','016166400','978','1','016166400','15591','http://images.icecat.biz/img/norm/high/19311-1470.jpg','13817','320','200','20050627000000'
[1 rows of 15 fields returned]
@dbi:SQLite:dbname=short.test.sqlite> ;quit
Disconnecting from dbi:SQLite:dbname=short.test.sqlite.