view release on metacpan or  search on metacpan

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

    my $res     = $sth2->fetchall_arrayref() ;

    if ( scalar @{$res} ) { return $res ; } ;
    return [] ;
}



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 {

    my ${LargeDataSetThreshhold}        = 30 ;

    local $_                            = undef;

    my $dbh                             = shift ;
    my $schema                          = shift ;
    my $table                           = shift ;

    croak "bad arg dbh"                 unless defined $dbh;
    croak "bad arg schema"              unless defined $schema;
    croak "bad arg table"               unless defined $table;

    $schema = substr $schema, 1, -1     if $schema =~ m/\A \[ .+ \] \Z /msix;
    $table  = substr $table,  1, -1     if $table  =~ m/\A \[ .+ \] \Z /msix;
    my $combinedName                    = "${schema}.${table}";
    my $quotedCombinedName              = "[${schema}].[${table}]";
    my $tableVarName                    = "LocalTable_${table}";

    my $quotedSchema                    = "[${schema}]";
    my $quotedTable                     = "[${table}]";

    my $database                        = databaseName($dbh);

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

    my $date                            = strftime "%d/%m/%Y", localtime;

    my $hasId                   = has_idCols($dbh,$schema,$table) ;
    my $idCol                   = undef ;
    if ($hasId) {
        $idCol                  = idCols($dbh,$schema,$table) ;
    }
#warn Dumper $idCol ;
    my $set_IDENTITY_INSERT_ON  = "";
    my $set_IDENTITY_INSERT_OFF = "";
    $set_IDENTITY_INSERT_ON     = "set IDENTITY_INSERT ${quotedCombinedName} ON"  if $hasId;
    $set_IDENTITY_INSERT_OFF    = "set IDENTITY_INSERT ${quotedCombinedName} OFF" if $hasId;


    my $ra_columns              = columns($dbh,$schema,$table);
#warn Dumper $quotedSchema,$quotedTable   ;
    my $ra_pkcolumns            = pkcolumns($dbh,$quotedSchema,$quotedTable);

    croak "${quotedCombinedName} 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{uniqueidentifier|char|text|date}i ? 0 : 1 ;  } @{$ra_columns} ;
#warn Dumper $ra_columns;
#warn Dumper @IsColumnNumeric ;
#exit;

    my $primaryKeyCheckClause   = "";
    my $pk_column               = undef ; #$ra_pkcolumns->[0][0];
    #my @nonKeyColumns = grep { $_->[0][0] ne $pk_column } @{$ra_columns};

    my @nonKeyColumns           = () ;


    my $widest_column_name_len = max ( map { length ($_->[0]); } @{$ra_columns} ) ;
    my $widest_column_name_padding = int($widest_column_name_len/4) + 4;

    my $flatcolumnlist          = "" ;
    my $flatvariablelist        = "" ;
    foreach my $l (@{$ra_columns}) {
        do { local $" = "";   $flatcolumnlist           .= "[$l->[0]]" ; $flatcolumnlist .= ", "} ;
        do { local $" = "";   $flatvariablelist         .= "@"."$l->[0]" ; $flatvariablelist .= ","} ;
    }
    $flatcolumnlist             =~ s{ ,\s? \z }{}msx;
    $flatvariablelist           =~ s{ ,\s? \z }{}msx;


#warn Dumper @{$ra_pkcolumns} ;

    my $reportingPKCols                 = "" ;
    my $recordExistenceCheckSQL         = "" ;
    if ( ! scalar @{$ra_pkcolumns} ) {
        my @pk_ColumnsCheck = () ;
        foreach my $l (@{$ra_columns}) {