DevGuy

Perl :: Output redirection with indirect filehandles

Author: devguy
Subject: Output redirection with indirect filehandles
Posted: Wed Jan 13, 2010 3:26 am (GMT 0)
Topic Replies: 0

This write the output of "dir" to out.txt

Code:

use strict;
use warnings;
my $f;
open($f, ">&", STDOUT);
close STDOUT;
open(STDOUT, ">", 'out.txt');
system('dir');
close STDOUT;
open(STDOUT, ">&", $f);
print "Done";

Perl :: Install perl modules semi-automatically

Author: devguy
Subject: Install perl modules semi-automatically
Posted: Tue Jan 12, 2010 5:43 pm (GMT 0)
Topic Replies: 0

Installing a large set of Perl modules via PPM and/or CPAN is painful. Here's a script that makes it easier.

In order for CPAN builds to work with ActivePerl on Windows, you need to get nmake.exe and nmake.err. Get them here. Put both in your PATH or in the same directory where this script resides.

Code:

#! /usr/bin/perl
use File::Basename;
use Carp;
use Cwd;
use Getopt::Long;
use strict;
use warnings;
use CPAN;
use File::Temp qw(tempfile);

my $automated = 0;
my $no_prompt = 0;
my $exit_code = 0;
my ( $fh, $in_file );

&Getopt::Long::GetOptions(
'noprompt|P' => \$no_prompt,
'automated|m!' => \$automated,
);

print <<END
When running this script for the first time, if
the CPAN shell has not been configured, please
perform the following. On Linux, you must use root
or sudo.

perl -MCPAN -e shell

Answer the prompts and then type, followed by ENTER:
o conf commit
END
;

# This causes CPAN to take all defaults without input
$ENV{PERL_MM_USE_DEFAULT} = 1;
CPAN::Shell->o( 'conf', 'init', 'auto_commit' );

my $make = 'make';

if ( $^O =~ /Win/ ) {
# Use 'nmake' which should be located in PATH or in the
# directory where this script resides
chdir dirname($0) || croak("chdir failed - $!");

$make = 'nmake';
my $sep = ';';
my $d = cwd;
$d =~ s/\//\\/g;
$ENV{PATH} .= "${sep}$d";
CPAN::Shell->o( 'conf', 'shell', 'cmd' );
}
else {
CPAN::Shell->o( 'conf', 'shell', '/bin/bash' );
}

CPAN::Shell->o( 'conf', 'make_install_make_command', $make );

if ( $^O =~ /cgywin/ ) {
print <<END

Make sure you have read/write permissions in all of your
.../perl/.../lib, .../perl/.../man, and .../perl/.../pod
directories before you attempt the install.
END
;
CPAN::Shell->o( 'conf', 'build_dir', '/cygdrive/c/CPANinst' );
CPAN::Shell->o( 'conf', 'cpan_home', '/cygdrive/c/CPANinst' );
CPAN::Shell->o( 'conf', 'keep_source_where', '/cygdrive/c/CPANinst' );
CPAN::Shell->o( 'conf', 'prefs_dir', '/cygdrive/c/CPANinst' );
}

CPAN::Shell->o( 'conf', 'commit' );

my @bundles = (

# Specifying 'no_activeperl' and 'no_cpan' causes the entry to be ignored.
#
# ActivePerl implies Windows. ppm is the default installation on
# ActivePerl.
#
# There is currently no way to run ppm on cygwin or Linux.
#
# On ActivePerl, 'no_activeperl' causes the entry to be skipped.
# 'cpan' causes cpan to be used instead of ppm.
#
# On non-ActivePerl, specifying either 'activeperl_only' or
# 'no_cpan' cause the entry to be skipped.
#
# 'more' only applies to cpan. Its value contains text that will
# be printed prior to installing the module (e.g., "Press ENTER
# when you are asked for input").
#
# 'manual_only' only applies to cpan.
# It causes the item to be skipped when in automated mode.
#
# 'notest' only applies to cpan.
# It avoids running tests but it also installs prerequisites.
# It is the default.
#
# 'normal' only applies to cpan. It runs a normal CPAN install.
#
# 'manual' only applies to cpan.
# It invokes the shell and runs make etc..
# It avoids installing prerequisites but runs tests.
#
# 'force' only applies to cpan.
# It should be used when the installation fails
# due to failed tests or prerequisites.
# Unfortunately, it causes the module to be installed
# every time this script is run. Try 'notest' before 'force.'

"Bundle::CPAN" => {
'comment' => 'Needed by DateTime',
'no_activeperl' => 1,
},
"Params::Validate" => { 'comment' => 'Needed by DateTime' },
"DateTime" => {},
"DateTime::Format::Duration" => {},
"S/SB/SBECK/Date-Manip-5.54.tar.gz" => { 'no_activeperl' => 1, },
"J/JP/JPEACOCK/version-0.78.tar.gz" => { 'no_activeperl' => 1, },
"version" => { 'activeperl_only' => 1 },
"DateTime::Format::DateManip" => { 'activeperl_only' => 1 },
"DateTime::Format::Builder" => {},
"DateTime::Format::ISO8601" => {},
"Test::Unit" => {},
"DBI" => {},
"DBD::ODBC" => {},
"Class::Std::Utils" => {},
"List::MoreUtils" => {},
"Config::Std" => {},
"Log::Log4perl" => {},
"Log::Dispatch::FileRotate" => {},
"Devel::Trace" => {},
"DateTime::Cron::Simple" => {},
"Smart::Comments" => {},
"Class::InsideOut" => {},
"Perl6::Export::Attrs" => {},
"Pod::Checker" => { 'no_activeperl' => 1 },
"YAML::LibYAML" => { 'activeperl_only' => 1 },
"YAML::XS" => { 'no_activeperl' => 1 },
"Class::Inner" => {},
"Error" => {},
"Devel::Symdump" => {},
"Pod::Simple::HTMLBatch" => { 'no_activeperl' => 1 },
"File::Slurp" => {},
"Tree::Simple" => {},
"File::Path" => {},
"File::Spec" => { 'no_activeperl' => 1 },
"Net::Ping" => {},
"Algorithm::Diff" => {},
"Data::GUID" => {},
"Readonly" => {},
"Perl::Tidy" => {},
"Math::BigInt" => {},
"POE" => {},
"POE::Component::EasyDBI" => { 'cpan' => 1, },
"POE::Component::Child" => {'cpan' => 1,},
"Net::SNMP" => {},
"Contextual::Return" => {},
"Template" => { 'no_activeperl' => 1 },
"Template-Toolkit" => { 'activeperl_only' => 1 },
"Sys::Hostname" => { 'no_activeperl' => 1 },
);

my $i = 0;
while ( $i < $#bundles ) {
my ( $bundle, $info ) = @bundles[ $i, $i + 1 ];
$i += 2;

if ( !$info->{'cpan'} && $^O =~ /Win/ ) { # If Windows..
next if $info->{'no_activeperl'};
my $bdl = $bundle;
$bdl =~ s/::/-/g;
print "\n*** $bundle ***\n";
system( 'ppm', 'install', $bdl );
}
else {
next if $info->{'activeperl_only'};
next if $info->{'no_cpan'};
next if $info->{'manual_only'} && $automated;

print "\n*** $bundle ***\n";
print "\n*** " . $info->{'more'} . " ***\n" if $info->{'more'};

if ( !$info->{'manual'} && !$info->{'force'} && !$info->{'normal'} ) {
$info->{'notest'} = 1;
}

if ( $info->{'force'} ) {
system(
"perl", '-MCPAN',
'-e', "CPAN::Shell->force(q{install}, q{$bundle})"
);
}

if ( $info->{'manual'} ) {
unless ($in_file) {
( $fh, $in_file ) = tempfile();
close $fh;
}

open( $fh, ">", $in_file ) || croak "open $in_file failed - $!";
print $fh "perl Makefile.PL\n$make\n$make install\nexit\n";
close $fh;

no warnings;
open( SAVEIN, "<&STDIN" );
close STDIN;
open( STDIN, $in_file );
system( "perl", '-MCPAN', '-e', "CPAN::Shell->look( q{$bundle} )" );
close STDIN;
open( STDIN, "<&SAVEIN" );
}

if ( $info->{'notest'} ) {
unless ($in_file) {
( $fh, $in_file ) = tempfile();
close $fh;
}

open( $fh, ">", $in_file ) || croak "open $in_file failed - $!";
print $fh "notest install $bundle\n";
close $fh;

no warnings;
open( SAVEIN, "<&STDIN" );
close STDIN;
open( STDIN, $in_file );
system( "perl", "-MCPAN", "-e", "shell" );
close STDIN;
open( STDIN, "<&SAVEIN" );
}

if ( $info->{'normal'} ) {
system( "perl", "-MCPAN", "-e", "install q{$bundle}" );
}
}

if ($?) {
carp "Error installing $bundle - $?";
$exit_code = 1;
}
}

if ($in_file) {
close $fh;
unlink $in_file;
}

unless ( $automated || $no_prompt ) {
print "\nPress ENTER to continue...\n";
<STDIN>;
}

exit $exit_code;

__END__

=pod

=head1 NAME

install_perl_modules.pl

=head1 VERSION

1.0.0

=head1 DESCRIPTION

Installs multiple Perl modules at once. Works with
ActivePerl, cygwin, and Linux/UNIX.

=head1 SWITCHES

=over

=item B<--noprompt|-P> - Do not prompt for user input after completion

=item B<--automated|-m> - Running in automated mode. Do not attempt to install any packages that require user input.

=back

=head1 EXIT CODE

0 indicates success. Any other value indicates an error occurred.

=head1 BUGS

None yet

=head1 AUTHOR

DevGuy.com and Justin Lee. Free to use and distribute with no restrictions.

Perl :: Use IO::Select to implement nonblocking reads

Author: devguy
Subject: Use IO::Select to implement nonblocking reads
Posted: Tue Jan 12, 2010 7:41 am (GMT 0)
Topic Replies: 0

http://www.sdsc.edu/~moreland/courses/IntroPerl/docs/manual/lib/IO/Select.html

Linux and BSD :: cifs mount and smbclient examples

Author: devguy
Subject: cifs mount and smbclient examples
Posted: Sat Jan 09, 2010 10:28 pm (GMT 0)
Topic Replies: 0

mount -t cifs -o umask=000,mask=000,uid=60000,username=td/tl186004 //sdfs601/tl186004 /home/qa

smbclient is cool. It makes any Windows/Samba share look like an FTP server.

smbclient \\\\sdfs601\\tl186004 --user=td/tl186004

Perl :: Unexpected yet correct behavior involving $@ and functions

Author: devguy
Subject: Unexpected yet correct behavior involving $@ and functions
Posted: Sat Jan 09, 2010 5:30 pm (GMT 0)
Topic Replies: 0

This prints 10, 5, and 10:

Code:

sub f {
local $@;
eval{print @_; die 5};
print $@;
}

$@='10';
f($@);
print $@;

But this prints 5 twice:

Code:

sub f {
eval{print @_; die 5};
print $@
}

$@='10';
f($@);
print $@;

Where did the 10 go?

(1) The eval statement clears $@
(2) Argments are passed by reference
(3) $_[0] passed to f() contains a reference to $@. By clearing $@, $_[0] also gets cleared
(4) By using "local $@", the eval clears the "localized version" of $@ instead of the caller's version.

Quote:

You're asking for trouble if you pass $@ to a function. $@ is a potential output for every subroutine, so don't make it an input because it's likely to be overwritten when you least expect.

Nevertheless, well-behaved functions must use "local $global" before they overwrite $global (think $_ with foreach).

For $@, this includes using "eval" either directly or indirectly. It also includes the creation of an Error object which sets $@ as a side effect.

Using "local $@" before invoking eval {} makes things challenging if you want to be able to throw exceptions back to the caller while leaving $@ the way you found it in non-exceptional cases. Here's how to do it.

Code:

sub foo {
my $failed = 1;
my $err;
my $ret;
{
local $@;
eval { $ret = sub_that_can_die(@_); $failed = 0; };
$err = $@;
}
die $err if $failed;
$ret;
}

Perl :: Deduping Arrays

Author: devguy
Subject: Deduping Arrays
Posted: Thu Jan 07, 2010 7:21 pm (GMT 0)
Topic Replies: 0

Let's remove duplicate entries from the array @files

Code:

my %fn;
map {$fn{$_} = 1} @files;
@files = keys %fn;

Perl :: Package-scoped variables and shutdown

Author: devguy
Subject: Package-scoped variables and shutdown
Posted: Mon Jan 04, 2010 10:49 am (GMT 0)
Topic Replies: 0

Package-scoped variables are unstable as the process shuts down. Application developers have almost no control over the order in which package-scoped variables are garbage-collected.

Any destructor (as well as the functions called by it) which refers to a package-scoped variable is likely to fail. This unfortunately includes all of Log4Perl's loggers. Using a Log4Perl logger in a destructor is destined to fail if the object can live until the application is shut down. Use Smart::Comments to log object destruction.

Perl :: Minimal POE alarm example

Author: devguy
Subject: Minimal POE alarm example
Posted: Mon Jan 04, 2010 9:04 am (GMT 0)
Topic Replies: 0

Code:

sub POE::Kernel::TRACE_REFCNT () { 1 }
use POE;
use Smart::Comments;

my $stopped;

sub start {
my $self = shift;
POE::Session->create(
object_states => [
$self => { '_start' => "process_begin" },
$self => { 'check_stop' => "process_check_stop" },
]
);
return;
}

sub stop {
$stopped = 1;
POE::Kernel->alarm( 'check_stop' => undef );
return;
}

sub process_begin {
$_[KERNEL]->yield('check_stop');
return;
}

sub process_check_stop {
### check_stop
unless ( $stopped ) {
$_[KERNEL]->alarm( 'check_stop' => time+5);
}
return;
}

my $obj = bless {};
$obj->start();

POE::Kernel->run();

Linux and BSD :: Virtual KVM with Synergy

Author: devguy
Subject: Virtual KVM with Synergy
Posted: Sun Jan 03, 2010 5:58 pm (GMT 0)
Topic Replies: 0

If you have two computers with monitors side by side, you can control both with the same mouse & keyboard using synergy. It even works across Windows and Linux machines.

http://synergy2.sourceforge.net/

Perl :: Callbacks using closures

Author: devguy
Subject: Callbacks using closures
Posted: Sat Jan 02, 2010 12:44 am (GMT 0)
Topic Replies: 0

Happy New Year!

Callback mechanisms should allow the user/caller to ...

(1) Implement callbacks as methods or as regular subroutines

(2) Specify their own function and method names that will be invoked by the callback

Closures in Perl are the best way to achieve this objective.

Let's look at a small example where the function foo() invokes a callback. The callback is the method 'bar' on a specific instance ($y). foo() is able to pass arguments to bar().

The following program outputs an array containing 1 and 2.

The nonobvious detail here is that the user must provide a closure that passes @_ to bar(), which is not the @_ at the time of the closure creation but is instead the @_ at the time the closure is invoked.

Code:

use Smart::Comments;

sub bar {
### @_
}

sub foo {
my $cb = $_[0];
&$cb(1,2);
}

my $y = bless {};
foo( sub {$y->bar(@_)} );

Perl :: Interesting use of Devel::Symdump

Author: devguy
Subject: Interesting use of Devel::Symdump
Posted: Tue Dec 29, 2009 3:12 am (GMT 0)
Topic Replies: 0

http://cpansearch.perl.org/src/MCAST/Test-Unit-0.25/lib/Test/Unit/Procedural.pm

Perl :: Send trap using Net::SNMP

Author: devguy
Subject: Send trap using Net::SNMP
Posted: Thu Dec 24, 2009 9:02 pm (GMT 0)
Topic Replies: 0

(1)

/etc/snmp/snmptrapd.conf:

Code:

ignoreauthfailure 1
disableAuthorization yes
logoption f /home/yourname/trap.log

As root, run: snmptrapd

Perl script:

Code:

use Net::SNMP;

$hostname = '127.0.0.1';
$port = '162'; # default port for trapd
$nonblocking = "0";
$version = '1';
$seconds = "30";
$retries = "3";
$community = "fubar";

# $debug bitmask options
# 0x02 - Message or PDU encoding and decoding
# 0x04 - Transport Layer
# 0x08 - Dispatcher
# 0x10 - Message Processing
# 0x20 - Security

#$debug = "0x00";
$debug = "0x3F";

($session, $error) = Net::SNMP->session(
-hostname => $hostname,
-port => $port,
-nonblocking => $nonblocking,
-version => $version,
-timeout => $seconds,
-retries => $retries,
-debug => $debug,
-community => $community,
);
unless ( $session ) { print "Error: $error\n"; exit 1; }

print "sending trap.....";

$oid = '1.3.6.1.6.3.1.1.4.1.0';

my @oid_value;

push @oid_value, ( '1.3.6.1.2.1.1.1.0', OCTET_STRING, "Error occurred" ); # SNMPv2-MIB::sysDescr.0
push @oid_value, ( '1.3.6.1.2.1.1.3.0', TIMETICKS, 10*100 ); # SNMPv2-MIB::sysUpTime.0
push @oid_value, ( '1.3.6.1.2.1.1.4.0', OCTET_STRING, 'test@test.com'); # SNMPv2-MIB::sysContact.0
push @oid_value, ( '1.3.6.1.6.3.1.1.4.1.0', OBJECT_IDENTIFIER, $oid); # SNMPv2-MIB::snmpTrapOID.0
# my enterprise specific oid
push @oid_value, ( '1.3.6.1.4.1.29484.1.2.1', INTEGER, 100); # SNMPv2-SMI::enterprises.28560.1.2.1

$result = $session->trap(
-enterprise => "1.3.6.1.4.1.29484",
-agentaddr => "127.0.0.1",
-generictrap => 6,
-specifictrap => 0,
-timestamp => 5*100,
-varbindlist => \@oid_value);

unless ( $result ) { print "error: $session->error" }
$session->close;

cat /home/yourname/trap.log

Code:

127.0.0.1: Enterprise Specific Trap (.0) Uptime: 0:00:05.00, SNMPv2-MIB::sysDescr.0 = STRING: Error occurred, DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (1000) 0:00:10.00, SNMPv2-MIB::sysContact.0 = STRING: test@test.com, SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-MIB::snmpTrapOID.0, SNMPv2-SMI::enterprises.29484.1.2.1 = INTEGER: 100

Professional :: Trying viplugin for Eclipse

Author: devguy
Subject: Trying viplugin for Eclipse
Posted: Sun Dec 20, 2009 8:44 pm (GMT 0)
Topic Replies: 0

Is viplugin for Eclipse better than the free gvim? I decided to try.

I enjoyed using viplugin but the following two problems are annoying. They are Eclipse shortcomings. gvim is still better.

1. Control-w isn't supported for split view. Eclipse has a work-around for split view - basically, there is no split view but you can open multiple windows and tab between them. This debate has been going since at least 2002.

2. When the cursor is over a bracket or paren, there is no visual indication to quickly find its match.

Perl :: Numbered and bulleted lists in POD

Author: devguy
Subject: Numbered and bulleted lists in POD
Posted: Wed Dec 09, 2009 4:05 pm (GMT 0)
Topic Replies: 0

Code:

=head1 Bulleted list

=over 4

=item * I<Development>

A new version of the harness is being developed and tested locally on a developer�s machine

=item * I<Staging>

A new version of the harness is being tested on an integration server

=item * I<Production>

The harness is running in production

=item *I<Local End-User>

The harness has been downloaded by, say, a database developer and everything is running on his or her laptop

=back

=head1 NUMBERED LIST

Note that the first =item has a number by itself and the others don't

=over 4

=item 1

A base directory + filename is provided (e.g., config/harness.ini)

=item 2

The deployment type is inserted into the path e.g., config/production/harness.ini

=item 3

If P2 exists, it is used. Otherwise, P1 is used.

=back

Windows :: Run shell in background on Windows gvim

Author: devguy
Subject: Run shell in background on Windows gvim
Posted: Wed Dec 09, 2009 3:51 pm (GMT 0)
Topic Replies: 0

Create a batch file with the following contents. I name it scmd.bat. Put the file in your vim/vim72 dircectory.

Code:

cmd /c "start cmd&taskkill /f /im vimrun.exe&exit"

Then run it with :!scmd

Perl :: Caller - execution context

Author: devguy
Subject: Caller - execution context
Posted: Sat Dec 05, 2009 4:20 am (GMT 0)
Topic Replies: 0

http://perldoc.perl.org/functions/caller.html

Teradata :: Using Teradata with DbVisualizer

Author: devguy
Subject: Using Teradata with DbVisualizer
Posted: Tue Nov 10, 2009 5:50 pm (GMT 0)
Topic Replies: 0

1. Install DbVisualizer

http://www.dbvis.com/products/dbvis/download/

2. Download Terata JDBC Driver

http://www.teradata.com/DownloadCenter/Group48.aspx

3. Run DbVisualizer

4. Tools / Driver Manager ...

5. Driver / Create Driver ...

6. Fill out the dialog similiar to the following

7. Troubleshooting

1) Did you add both Jars?
2) Is there a colon after teradata in the URL?

Teradata :: Teradata JDBC Driver URL

Author: devguy
Subject: Teradata JDBC Driver URL
Posted: Mon Nov 09, 2009 12:03 am (GMT 0)
Topic Replies: 0

For use with DBVisualizer..

jdbc:teradata://ipaddress_or_server_name

Teradata :: Installing Perl::DBi Connectivity for Teradata

Author: devguy
Subject: Installing Perl::DBi Connectivity for Teradata
Posted: Fri Nov 06, 2009 8:16 pm (GMT 0)
Topic Replies: 0

Initial Author: Dazhi Wang

See also: http://www.presicient.com/tdatdbd/

1. Install TTU CLI
* Versions 7.x and prior require installation of the Teradata WinCLI Developer's Kit
* Therefore, install version 12 or higher

2. Run: perl -MCPAN -e "install DBI"

3. If using Windows and Visual Studio is not installed, run the nmake minimal install

4. Download Teradata::SQL and unzip the package

5. If using Windows:
* Edit Makefile.PL
* Change the file path of coptypes.h from '/usr/include/coptypes.h' to 'C:\Program Files\Teradata\Teradata Client\cli\inc\coptypes.h'

6. If using Windows, open the VS2005 command prompt

7. Run: perl Makefile.PL

8. Change the generated Makefile file by add following libs to LDLOADLIBS

Windows:

"C:\Program Files\Teradata\Teradata Client\cli\lib\wincli32.lib"
"C:\Program Files\Teradata\Teradata Client\cli\lib\tdusr32.lib"

Linux:

TBD

9. Run: nmake

10. Run: nmake install

11. Run The following script to test the installation:

Code:

sub dbc_info_checker {
my ( $db_ip ) = @_;

# Connect and get a database handle.
my $dbh = Teradata::SQL::connect("$db_ip/dbc,dbc")
or die "Could not connect";

# Prepare a request; read the results.
my $rh = $dbh->open("select * from dbcinfo;");
my @dbcinfo_rows;
while ( my @current_row = $rh->fetchrow_list ) {
push @dbcinfo_rows, @current_row;
}
$rh->close;
$dbh->disconnect;
print "dbcinfo data: ",Dumper(\@dbcinfo_rows);

return 0;

}

dbc_info_checker('substitute_teradata_database_server_name_or_ip_address_here');

Windows :: Keeping Remote Desktop Sessions Alive

Author: devguy
Subject: Keeping Remote Desktop Sessions Alive
Posted: Thu Nov 05, 2009 6:28 pm (GMT 0)
Topic Replies: 0

1. Install the AutoIt script engine
2. Save the script located here, giving it a ".au3" extension
3. Connect to the remote server using Remote Desktop
4. Execute the .au3 script from the command line with the server name or IP address as the first parameter
5. A dialog box will appear. You may click OK to dismiss it.
6. Pause/Resume/Exit the script via the AutoIt tray icon
7. Don't minimize the remote desktop window. If you do, this script won't work.

Perl :: JDBC and Perl

Author: devguy
Subject: JDBC and Perl
Posted: Thu Oct 22, 2009 7:14 pm (GMT 0)
Topic Replies: 0

Install Perl and JRE (java runtime).

Linux:

Code:

perl -MCPAN -e 'install DBI'
perl -MCPAN -e 'force install Bundle::DBD::JDBC'

On Windows:

Code:

You must be using Perl 5.8. 5.10 will not work.
ppm install http://trouchelle.com/ppm/DBD-JDBC.ppd

Start the server using http://search.cpan.org/~vizdom/DBD-JDBC/JDBC.pod

Any DBMS :: Schema and Data Migration Done Right

Author: devguy
Subject: Schema and Data Migration Done Right
Posted: Wed Oct 07, 2009 4:36 pm (GMT 0)
Topic Replies: 0

Although many libraries in Ruby, Python, Java, Scala, etc. provide feature-rich 'automatic' schema and data migrations, none of them provide 100% coverage, unfortunately.

Let's investigate the worst issues with out-of-the-box solutions:

(1) Support for multiple DBMS vendors. Generic libraries typically generate SQL statements using vendor-neutral descriptions of the schema. These types of packages can not support every vendor-specific feature, such as constraint and stored procedure dialects. They even frequently choose the wrong datatypes, for example, when a specific precision needs to be provided.

Quote:

Point #1: Exact vendor-specific SQL statements must be executed in the correct order

(2) A complex migration can involve hundreds of SQL statements. What happens if one fails?
(3) If a migration is executed against the same database twice by accident, data and schema will likely be altered incorrectly. Do mechanisms exist to prevent this?
(4) Processes that execute outside the database (for example, in Java) generally perform badly against large tables. The main contributors are the large number of transactions that each operate on a small number of rows. Stored procedures are usually the best way to perform medium to high complexity tasks.

Quote:

Point #2: The best migration implementation uses plain old SQL statements combined with stored procedures and functions

What constitutes a complex transformation worthy of a stored procedure? Although set operations generally perform better, sometimes a task is too complex to perform in a single SQL statement. Stored procedures typically use cursors. For example: For each row in A, create some number of rows in B depending on how A's rows relate to C's rows.
(5) Migrations often involve integration with external data sources. Therefore, migration libraries only solve part of the problem.

Most importantly, any application that depends on to-be-migrated data must be shut down before the migration process begins. This bit of trapeze artistry is rarely designed for, resulting in a lot of Nagios etc. alerts until the wee hours of the morning. And worse.

Care must be taken to avoid blowing out transaction logs during the migration process. It is often necessary to disable logging (as can be done with, for example, mysql), assuming a backup is performed beforehand. Disabling the transaction log, however, doesn't work with many forms of replication.

Speaking of replication, some RDBMSs such as mysql don't correctly replicate "truncate table" statements, so your migration process might need to avoid using them.

The whole migration process involves:
-- Stopping systems gracefully, including replicas
-- Making backups
-- Altering DBMS configuration (e.g., disable transactions)
-- Transforming the existing schema and the data within it
-- Loading data from external systems
-- Bringing systems online, including replicas, with the correct configurations

With the following additional requirements:
-- Do not allow the system to start before the migration is complete
-- Avoid double-migrating the same database
-- When a failure occurs, notify operators and wait
-- Restart the process from the last point of failure

A robust schema and data migration implementation follows these guidelines:

(1) Use ALTER where possible to add and remove columns and constraints to/from existing tables

(2) Because many types of table alterations can not be performed using ALTER (this is vendor-specific), many table transformations require the following template:

Code:

create table foo_temp as select * from foo;
drop table foo;
-- This can be problematic per relational constraints. mysql for example allows constraint checking to be temporarily disabled
create table foo ..;
insert into foo (select * from foo_temp);

(3) Drop tables, views, stored procedures, etc. that are no longer needed
(4) Create new tables, views, stored procedures, etc.
(5) Execute migration-specific SQL statements, stored procedures, and functions
(6) Remove migration-specific stored procedures and functions

Any DBMS :: Modeling Enums

Author: devguy
Subject: Modeling Enums
Posted: Wed Oct 07, 2009 4:24 pm (GMT 0)
Topic Replies: 0

An "enum" is a variable that can have at most one value from a list of values. Enums can be modeled in RDBMS in two main ways:

(1) A table of values
**Con: Join overhead (however, table would fit completely in cache)
**Pro: Repetitive values compress to 4-byte integer
**Pro: Easy to add values on the fly
**Pro: Can associate other information with the value
**Pro: Reusable across multiple tables

(2) A varchar with a value constraint plus �compression� to avoid storing the same string over and over again
**Can use UDT to reuse across tables ?
**Pro: No join overhead
**Con: Requires DDL modification to add a value
**Con: Retrieving complete list of values requires parsing DDL (but I18N issues are problematic in either case)
**Con: Database specific although most popular RDBMSs have value constraints

Any DBMS :: Migrate your database schemas and data with Scala Migrations

Author: devguy
Subject: Migrate your database schemas and data with Scala Migrations
Posted: Tue Sep 08, 2009 4:59 pm (GMT 0)
Topic Replies: 0

http://code.google.com/p/scala-migrations/

Scala Migrations is a library to manage upgrades and rollbacks to database schemas

VMWare :: RE: Extending a primary VMWARE partition in Windows

Author: devguy
Subject: A few notes on the subject..
Posted: Sat Aug 08, 2009 6:26 pm (GMT 0)
Topic Replies: 1

1. If you already have another VMWare instance, you only need to copy the .vmdk file for your primary disk - not all the other files. If you're feeling lucky, you can modify the file in-place without copying it. Copying the file is peace of mind since it can serve as a backup if anything goes wrong.

2. If the .vmdk file to be extended resides on an external (e.g., USB) drive and you have problems extending it, copy it to your local disk. Sometimes the issues are related to file permissions and sometimes they are related to amount of free disk space.

3. Add the new disk to the "other" VMWare instance as an IDE disk - usually IDE1 to be exact. That is, unless it's really a SCSI disk, which is rare.

4. When extending the disk, the -x option should specify the final disk size, not the additional space to allocate. The "invalid parameter" error usually indicates that the -x parameter specified a size that is smaller than the disk.

5. After running diskpart and shutting down, remove the primary disk from the "other" VMWare instance. Back up the 'old" .vmdk fille. Finally, replace the "old" .vmdk file with the "new" one.

MySQL :: Processing cursors correctly

Author: devguy
Subject: Processing cursors correctly
Posted: Mon Aug 03, 2009 9:34 pm (GMT 0)
Topic Replies: 0

Code:

drop PROCEDURE if exists foo_p;

delimiter //
CREATE PROCEDURE foo_p(
)
BEGIN

DECLARE done INT;
DECLARE v_foo varchar(128);

DECLARE cur1 CURSOR FOR

SELECT foo FROM bar;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

main: LOOP
set done = 0;

FETCH cur1 INTO v_foo;

IF done THEN leave main; end if;

/* Do Stuff Here */

END LOOP;

CLOSE cur1;
END
//

Java :: RE: Use Wagon to create a SVN-backed Maven repository

Author: devguy
Posted: Wed Jul 29, 2009 6:34 pm (GMT 0)
Topic Replies: 1

From master Java craftsman Bo Stone

All you do is create a top-level SVN project and add this to your pom.xml

Code:

<build>
...
<extensions>
<extension>
<groupId>org.jvnet.wagon-svn</groupId>
<artifactId>wagon-svn</artifactId>
<version>1.8</version>
</extension>
</extensions>
</build>

<repositories>
...
<repository>
<id>maven2-repository.dev.java.net</id>
<name>Java.net Repository for Maven</name>
<url>http://download.java.net/maven/2/</url>
</repository>
</repositories>

Java :: Use Wagon to create a SVN-backed Maven repository

Author: devguy
Subject: Use Wagon to create a SVN-backed Maven repository
Posted: Wed Jul 29, 2009 6:32 pm (GMT 0)
Topic Replies: 1

https://wagon-svn.dev.java.net/

Windows :: Pause for number of seconds at Windows command line

Author: devguy
Subject: Pause for number of seconds at Windows command line
Posted: Wed Jul 29, 2009 1:34 am (GMT 0)
Topic Replies: 0

Wait 5 seconds..

choice /T:5 /D N /N > Nul

Java :: Enterprise Java Manifesto Point #1 - Data Access Objects

Author: devguy
Subject: Enterprise Java Manifesto Point #1 - Data Access Objects
Posted: Thu Jul 23, 2009 4:39 pm (GMT 0)
Topic Replies: 0

Following the MVC pattern does not guarantee a robust, scalable application. The M (model) is the most overlooked aspect of 3-tier enterprise application development.

When data is stored in an RDBMS, the model should be implemented as Data Access Objects (DAOs) with the following properties:

a. Defined as interfaces (in a JAR that is separate from its implementations) that have minimal dependencies on other jars. The interface approach makes it possible to:

add caching as needed
Implement 'dummy' objects in the early phase of development before the DAOs have been fully implemented

b. Interfaces are implemented as POJOs
c. One RDBMS POJO implementation has the following properties:

Uses iBatis (or hibernate or jdbc) that invokes stored procedures. Stored procedures can be manipulated by less expensive database developers and don't require JAR rebuilding in order to roll out hot patches that affect only SQL. This however means that the build process needs to install stored procs before tests execute.
Logs begin/end of each stored proc call using for example JCL - this provides minimal performance analysis that will be useful later on
Leverages industry-grade connection pool that times-out unused connections
Intelligently retries database connections that fail because the server is momentarily unavailable.

"Intelligently" means that this can not be done if transactions are controlled by the caller. If the caller decides to control transactions, it must also handle retry logic. This complexity is often undesirable.
Intelligently retries statements that fail due to deadlock
Ideally these classes should be OSGi packaged. However, OSGi complexity can be addressed at a later date.

Generating DAOs directly from RDBMS
Reliance on RDBMS-to-Java code generation is a recipe for failure. The tables in an RDBMS represent but one implementation of a conceptual object model. This assertion is true regardless of whether views or tables are used.

Long-lived DAOs should be designed independently of the RDBMS implementation, so both can evolve independently while minimally impacting the other. These goals are facilitated by the use of stored procedures.

Referential integrity is good
Referential integrity checks locate bugs early in the development cycle. They also prevent orphans from stealing your resources. Declarative cascading deletes can eliminate substantial amounts of hand-written code. Furthermore, RI is an invaluable documentation tool.

However, referential integrity must be addressed at schema development time. Cascading deletes - whether declarative or not - can cause a single delete operation to take multiple minutes (or worse), timing out the browser. Lazy delete must be designed in the schema and in the data access interfaces up front. Lazy delete can result in primary key violations.

Transactions are not for large amounts of data
Many implementations fail because the right tool is not used for the job at hand. Insert statements are no match for vendor-specific bulk load tools. ELT is generally the best way to load data in bulk quickly. The downsides are:

Running ELT jobs simultaneously in parallel is often impossible, so how do you avoid it and how do you scale such a solution? First, you need a "critical section." Secondly, the RDBMS and the ELT tool must team together to parallelize as much of the load as possible. This implies an MPP database (e.g., Greenplum, Teradata). Unfortunately the state of the art of ELT tools remains inadequate to automate parallel task execution intelligently because this requires understanding the semantics of your data model.
Additional complexity
If at all possible, use streaming to avoid landing data into a flat file before bulk loading
Controlling batch sizes can be tricky

VMWare :: VMWare Server 2.x Suspend Script Example [Windows]

Author: devguy
Subject: VMWare Server 2.x Suspend Script Example [Windows]
Posted: Tue Jun 23, 2009 9:35 pm (GMT 0)
Topic Replies: 0

Here's an example of a script that suspends two VMs and then lists all running VMs. Wouldn't it be better to write a script that iterated over the list and suspended them all? But you'd probably use Perl for that, right?

Note the plus signs for spaces. The 'suspend' command requires them although they are not output by the 'list' command. (As an aside, the 'start' command doesn't need the plus signs.) Also, note that Windows batch scripts require doubling percent signs.

Code:

pushd "C:\Program Files (x86)\VMware\VMware VIX"

vmrun -h https://localhost:8333/sdk -u user -p %%perc suspend "[o] Windows+2003+Server+VS 2005/winNetStandard.vmx"

vmrun -h https://localhost:8333/sdk -u user -p %%perc suspend "[o] mmdw2/winNetStandard.vmx"

vmrun -h https://localhost:8333/sdk -u user -p %%perc list

Linux and BSD :: Use tar with Scp to transfer multiple files at once

Author: devguy
Subject: Use tar with Scp to transfer multiple files at once
Posted: Mon Jun 08, 2009 11:00 pm (GMT 0)
Topic Replies: 0

http://www.thingy-ma-jig.co.uk/blog/03-09-2008/using-tar-and-ssh-improve-scp-speeds

Perl :: Current date/time in yyyy-mm-dd hh:mm:ss

Author: devguy
Subject: Current date/time in yyyy-mm-dd hh:mm:ss
Posted: Thu May 21, 2009 5:26 pm (GMT 0)
Topic Replies: 0

Code:

use DateTime;

$dt = DateTime->now; # same as ( epoch => time() )

print $dt->year . '-' . sprintf('%02d', $dt->month) . '-' . sprintf('%02d',$dt->day) . ' ' .
sprintf('%02d',$dt->hour) . ':' . sprintf('%02d',$dt->minute) . ':' . sprintf('%02d',$dt->second);

SQL Server :: SQL Server, Hibernate, and Metadata Case Sensitivity

Author: devguy
Subject: SQL Server, Hibernate, and Metadata Case Sensitivity
Posted: Wed May 13, 2009 2:49 am (GMT 0)
Topic Replies: 0

Context: Loading data from PostgreSQL into Microsoft SQL Server.

PostgreSQL uses case-insensitive string comparison with metadata (involving table, column, etc. names). However, data comparisons are case-sensitive. This affects unique indexes. Let's say there's a unique index on column A, a varchar. Two rows may be inserted such that column A are 'foo' and 'FoO' without violating the unique index.

Therefore, when inserting some data into SQL Server from PostgreSQL, unique indexes can be violated with strings that are identical except for case.

The way around the unique index issue is to change the particular SQL Server database's default collation to be case-sensitive. However, this also causes SQL Server's column and table names to be case-sensitive. SQL Server has only one default collation per database that applies to both metadata and to string columnar data.

Unfortunately, this breaks Hibernate and possibly hand-written queries. Hibernate uppercases table and column names because SQL Server's JDBC driver returns true for

DatabaseMetaData.storesUpperCaseIdentifiers()

This is a bug in Microsoft's JDBC driver. There is apparently no way to tell Hibernate that the database is case sensitive.

SQL Server :: Drop all tables in a database - even with FK constraints

Author: devguy
Subject: Drop all tables in a database - even with FK constraints
Posted: Mon May 11, 2009 10:02 pm (GMT 0)
Topic Replies: 0

Code:

Delete me to run me - this is for your safety

/**
* This SQL drops all tables. It loops 10000 times in order to
* deal with drop errors related to FK constraints.
*/

set xact_abort off
declare @counter int
set @counter = 0
while @counter < 10000
begin
set @counter = @counter + 1
exec sp_MSforeachtable 'set xact_abort off;drop table ?'
end

VMWare :: VMWare Server 2.x Stop and Start Scripts [Windows]

Author: devguy
Subject: VMWare Server 2.x Stop and Start Scripts [Windows]
Posted: Fri May 08, 2009 4:41 pm (GMT 0)
Topic Replies: 0

I believe it was the second Buddha (depends on who's counting) who said that if you're using an external USB (or firewire, esata, etc..) disk to hold VMWare Server 2.x guests, you should stop VMWare before hibernating. First of course you must either shut down or suspend all guests that happen to be powered on.

Rebooting the host (e.g., Vista) without your external disk connected is also problematic resulting in all guests in the corresponding "data stores" becoming unsuable. You must refresh the data store, remove all guests from it, and then add the guests back - not dire but not fun either.

After rebooting, connect your external disk and start VMware Server 2.x manually.

The following scripts stop and start VMware Server 2.x.

Use at your own risk.

In order to defend yourself, first copy the directory C:\ProgramData\VMware somewhere safe (e.g., a USB drive). This directory contains VMWare Server 2.x configuration information such as your data sources, and whenever you change your VMWare Server 2.x configuration you should update your backup.

If you're unable to start the VMWare web application, it's probably because an XML file in C:\ProgramData\VMware has become corrupted. Stop the VMWare Server 2.x services, overwrite the files in C:\ProgramData\VMware with those in your copied directory, and restart the services.

If that doesn't work, uninstall VMWare Server 2.x manually, delete C:\ProgramData\VMware, and install VMWare Server 2.x to a different location that your previous installation (e.g., c:\program files\vmware2).

Here's the script to make VMware Servers 2.x services start manually. Only run this script once. Two of the commands fail because "sc config" apparently fails on service names that contain spaces -- you must change their settings yourself via Control Panel -> Administrative Tools -> Services.

Code:

rem VMWare Server 2.x services start manually
sc config "VMware NAT Service" start= demand
sc config "VMware Host Agent" start= demand
sc config VMnetDHCP start= demand
sc config VMAuthdService start= demand
sc config VMnetBridge start= demand
sc config VMnetuserif start= demand
sc config vmx86 start= demand
sc config "VMware Server Web Access" start= demand
sc config "VMware Registration Service" start= demand

Stop Script:

Code:

rem stop all VMware Server 2.x services
net stop "VMware Server Web Access"
net stop "VMware NAT Service"
net stop "VMware Host Agent"
net stop VMnetDHCP
net stop VMauthdService
net stop VMnetBridge
net stop VMnetuserif
net stop vmx86
rem net stop "VMware Registration Service"

Start Script:

Code:

rem start all VMware Server 2.x services
net start vmx86
net start VMnetuserif
net start VMnetBridge
net start VMauthdService
net start VMnetDHCP
net start "VMware NAT Service"
net start "VMware Host Agent"
net start "VMware Server Web Access"
rem net start "VMware Registration Service"

Perl :: Using Calendar::Simple

Author: devguy
Subject: Using Calendar::Simple
Posted: Wed May 06, 2009 10:22 pm (GMT 0)
Topic Replies: 0

Output the starting and ending dates for each month, sort of..

Code:

## Windows Installation:
##
## ppm install Calendar-Simple
use Calendar::Simple;

my $year = 1995;
my $month = 1;
my $next;

while ($year <= 2200)
{
my @days = calendar($month, $year);
   my $mon = sprintf("%02d", $month);
   print "$year-${mon},$year,$month,${year}${mon}01 00:00:00,${year}$mon";

   my $last;
   foreach (@{$days[$#days]})
   {
      $last = $_ if $_;
   }

   print "$last 23:59:59.999\n";

$month++;
if ($month > 12)
{
$year++;
$month = 1;
}
}

SQL Server :: RE: SQL Server DBA Basic Questions

Author: devguy
Posted: Fri May 01, 2009 4:39 pm (GMT 0)
Topic Replies: 1

Related post:

Junior DBA Questions

PostgreSQL :: Out of memory error with PostgreSQL JDBC driver

Author: devguy
Subject: Out of memory error with PostgreSQL JDBC driver
Posted: Thu Apr 16, 2009 3:52 pm (GMT 0)
Topic Replies: 0

PostgreSQL's JDBC driver will attempt to load every row in a result set into memory. This will result in out of memory errors for large select statements. Here's the fix.

Why disable autoCommit?

Code:

Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
connection.setAutoCommit(false);

stmt.setFetchSize(500);

SQL Server :: RE: Do stuff to all tables in a database

Author: devguy
Posted: Wed Apr 15, 2009 7:47 pm (GMT 0)
Topic Replies: 1

Here's a fun one.

Code:

exec sp_MSforeachtable 'select ''?'', count(*) from ?'

Linux and BSD :: awk example

Author: devguy
Subject: awk example
Posted: Wed Apr 15, 2009 5:54 pm (GMT 0)
Topic Replies: 0

Column delimiter is tab. Ignore the first line. Output the 20th column.

Code:

awk -F'\t' 'NR > 1 {print $20}'

SQL Server :: SQL Server DBA Basic Questions

Author: devguy
Subject: SQL Server DBA Basic Questions
Posted: Tue Apr 14, 2009 3:54 pm (GMT 0)
Topic Replies: 1

1. Which websites do you visit reguarly for SQL Server information?
2. Explain how you approach optimizing a query
3. How do you assign files to databases?
4. Why would you assign multiple data files to a database?
5. What is the difference between the 'simple' and 'full' database recovery options?
6. How do you perform count* on all user tables in a database in a single statement?
7. What is "update statistics" for?
8. Explain why you would rebuild an index
9. What is the difference between a clusted index and a regular index?
10. What is a materialized view?
11. How do you fix corrupted database files?
12. Explain how you would restore from a full backup with incremental backups
13. What is the difference between 'where' and 'having' ?

Java :: Maven and Jetty using cygwin SSH on Windows

Author: devguy
Subject: Maven and Jetty using cygwin SSH on Windows
Posted: Wed Apr 01, 2009 10:28 pm (GMT 0)
Topic Replies: 0

cmd.exe sucks as a logging target. Words don't wrap making "copy" useless - they're cut in half via a newline character (\r\n no less).

What to do?

One way is to use a decent terminal such as Putty and run your java application over SSH using cygwin. But - confound it! - "mvn jetty:run" yields the following error:

BUILD ERROR - The plugin 'org.apache.maven.plugins:maven-jetty-plugin' does not exist or no valid version could be found

No problem! Create the file:

c:\documents and settings\userName\.m2\settings.xml

Code:

<settings>
<pluginGroups>
<pluginGroup>org.mortbay.jetty</pluginGroup>
</pluginGroups>
</settings>

MySQL :: View recently run queries

Author: devguy
Subject: View recently run queries
Posted: Tue Mar 31, 2009 4:31 pm (GMT 0)
Topic Replies: 0

show engine innodb status

SQL Server :: Files and file groups

Author: devguy
Subject: Files and file groups
Posted: Mon Mar 30, 2009 11:38 pm (GMT 0)
Topic Replies: 0

http://decipherinfosys.wordpress.com/2007/01/24/files-and-filegroups-implementation-in-sql-server/

SQL Server :: Planning disk usage in SQL Server 2005 and beyond

Author: devguy
Subject: Planning disk usage in SQL Server 2005 and beyond
Posted: Mon Mar 30, 2009 10:06 pm (GMT 0)
Topic Replies: 0

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122_mem1,00.html

SQL Server :: Do stuff to all tables in a database

Author: devguy
Subject: Do stuff to all tables in a database
Posted: Mon Mar 23, 2009 9:05 pm (GMT 0)
Topic Replies: 1

Example:

Code:

exec sp_MSforeachtable '&set xact_abort off;drop table ?'

I intentionally added a syntax error for obvious reasons. You will need to remove the & yourself.

The purpose of set xact_abort off is to deal with errors related to foreign key constraints. Run this command multiple times until there are no more fk-related errors.

Windows :: Towards Hibernate Bliss

Author: devguy
Subject: Towards Hibernate Bliss
Posted: Fri Mar 20, 2009 4:33 pm (GMT 0)
Topic Replies: 0

If you follow these simple rules you can Hibernate Windows XP or Vista (even with VMWare instances running on an external USB disk!) to your heart's content. Just remember to connect your USB drives to the same USB ports prior to restart.

1. Disconnect all VPN connections
2. Disconnect WAN (e.g., 3G) modem
3. Turn off bluetooth antenna

Data Warehousing :: Big Data on Not-So Big Budgets [Presentation]

Author: devguy
Subject: Big Data on Not-So Big Budgets [Presentation]
Posted: Thu Mar 19, 2009 7:38 pm (GMT 0)
Topic Replies: 0

http://docs.google.com/Present?docid=dhnx36jx_37gfts9chg

Data Warehousing :: One fact referencing multiple values from the same dimension

Author: devguy
Subject: One fact referencing multiple values from the same dimension
Posted: Thu Mar 19, 2009 7:14 pm (GMT 0)
Topic Replies: 0

http://www.dbmsmag.com/9808d05.html

http://www.intelligententerprise.com/010810/412warehouse1_1.jhtml