view release on metacpan or  search on metacpan

lib/VSGDR/MergeData.pm  view on Meta::CPAN

}



sub dependencySQL {

return <<"EOF" ;
select  distinct
        tc2.TABLE_CATALOG               as to_CATALOG
,       tc2.TABLE_SCHEMA                as to_SCHEMA 
,       tc2.TABLE_NAME                  as to_NAME   
,       tc1.TABLE_CATALOG               as from_CATALOG
,       tc1.TABLE_SCHEMA                as from_SCHEMA
,       tc1.TABLE_NAME                  as from_NAME
,       rc.CONSTRAINT_NAME              as to_CONSTRAINT
from    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
join    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1
on      tc1.CONSTRAINT_SCHEMA           = rc.CONSTRAINT_SCHEMA
and     tc1.CONSTRAINT_CATALOG          = rc.CONSTRAINT_CATALOG
and     tc1.CONSTRAINT_NAME             = rc.CONSTRAINT_NAME
join    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2
on      tc2.CONSTRAINT_SCHEMA           = rc.CONSTRAINT_SCHEMA
and     tc2.CONSTRAINT_CATALOG          = rc.CONSTRAINT_CATALOG
and     tc2.CONSTRAINT_NAME             = rc.UNIQUE_CONSTRAINT_NAME

EOF

}


sub generateScript {

    local $_                            = undef;
            
    my $dbh                             = shift ;
    my $src_schema                      = shift ;
    my $src_table                       = shift ;
    my $targ_schema                     = shift ;
    my $targ_table                      = shift ;
    my $script_type                     = shift ;

    croak "bad arg dbh"                 unless defined $dbh;
    croak "bad arg source schema"       unless defined $src_schema;
    croak "bad arg source table"        unless defined $src_table;
    croak "bad arg target schema"       unless defined $targ_schema;
    croak "bad arg target table"        unless defined $targ_table;

    $src_schema  = substr $src_schema, 1, -1     if $src_schema  =~ m/\A \[ .+ \] \Z /msix;
    $src_table   = substr $src_table,  1, -1     if $src_table   =~ m/\A \[ .+ \] \Z /msix;
    $targ_schema = substr $targ_schema, 1, -1    if $targ_schema =~ m/\A \[ .+ \] \Z /msix;
    $targ_table  = substr $targ_table,  1, -1    if $targ_table  =~ m/\A \[ .+ \] \Z /msix;
                  
    my $combinedSourceName              = "${src_schema}.${src_table}"; 
    my $quotedCombinedSourceName        = "[${src_schema}].[${src_table}]"; 
    my $combinedTargetName              = "${targ_schema}.${targ_table}"; 
    my $quotedCombinedTargetName        = "[${targ_schema}].[${targ_table}]"; 

    my $database                        = databaseName($dbh);

    no warnings;
    my $userName                        = $OSNAME eq 'MSWin32' ? eval('Win32::LoginName') : ${[getpwuid( $< )]}->[6]; $userName =~ s/,.*//;
    use warnings;
    
    use warnings;                      
    my $date                            = strftime "%d/%m/%Y", localtime;



    my $hasId                   = has_idCols($dbh,$targ_schema,$targ_table) ;
    my $idCol                   = undef ;
    if ($hasId) {
        $idCol                  = idCols($dbh,$targ_schema,$targ_table) ;
    }

    my $ra_columns              = columns($dbh,$targ_schema,$targ_table);
    my $ra_pkcolumns            = pkcolumns($dbh,$targ_schema,$targ_table);

    croak "${combinedTargetName} doesn't appear to be a valid table"          unless scalar @{$ra_columns};
    
#warn Dumper $ra_columns ;
#exit ;

#    croak 'No Primary Key defined'          unless scalar @{$ra_pkcolumns};
#    croak 'Unusable Primary Key defined'    unless scalar @{$ra_pkcolumns} == 1;

    my @IsColumnNumeric = map { $_->[1] =~ m{char|text|date}i ? 0 : 1 ;  } @{$ra_columns} ;

    my $primaryKeyCheckClause   = "";
    my @nonKeyColumns ;
    foreach my $col (@{$ra_columns}) {
        push @nonKeyColumns, $col unless grep {$_->[0] eq $col->[0] } @{$ra_pkcolumns} ;
    }
        

    my $onclause                = do {local $" = " and "; "@{[map {\"tgt.$_->[0]  =  src.$_->[0]\"} @$ra_pkcolumns]}" };

    my $insertclause    = "(" . do {local $" = ", "; "@{[map {\"[$_->[0]]\"} @$ra_columns]}"         } . ")";
    my $valuesclause    = "(" . do {local $" = ", "; "@{[map {\"src.[$_->[0]]\"} @$ra_columns]}" } . ") ";

    my $fullUpdateClause = "" ;
    my $exceptClause  = "select "         . do {local $" = ", "; "@{[map {\"tgt.[$_->[0]]\"} @nonKeyColumns]}" };
       $exceptClause .= " except select " . do {local $" = ", "; "@{[map {\"src.[$_->[0]]\"} @nonKeyColumns]}" };
    
    if ( scalar @nonKeyColumns > 0 ) {
        $fullUpdateClause ="when matched and exists (select * from (${exceptClause}) x )\n    then update\n    set     " . do {local $" = "\n    ,       "; "@{[map {\"tgt.[$_->[0]]\t\t=  src.[$_->[0]]\"} @nonKeyColumns]}" };
    }

    my $maxCol;
    
    #warn Dumper @maxWidth ;
    
    

return <<"EOF";

/****************************************************************************************
 * Database:    ${database}
 * Author  :    ${userName}
 * Date    :    ${date}
 * Purpose :    Merge statement usp for ${combinedTargetName}
 *