FW: William Blunn’s DBIX::MULTIROW – UPDATING MULTIPLE DATABASE ROWS QUICKLY AND EASILY

William Blunn wrote a good article on DBIx::MultiRow.

A requirement arises in many systems to update multiple SQL database rows. For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update. But if there are a large number of rows that require an update, then the overhead of issuing large numbers of UPDATE statements can result in the operation as a whole taking a long time to complete.

Go to William Blunn’s blog to read the blog post 🙂

He will be adding the Perl module to CPAN soon.

FW: Timm Murray’s Underappreciated Perl Modules Series: File::ShareDir

Problem: you have some wumpus-cavekind of data that needs to be distributed with your Perl module. Where do you put it in a cross-platform way?

Solution #1: Put it in a giant datastructure inside some module. This ends up with a big .pm file that chews up memory.

Solution #2: Put it in a __DATA__ section. But you only get one of those per module, and binary data might get hairy.

Best solution: File::ShareDir.

Read more on Timm Murray’s blog

Howto Perl: Crypt::CBC module with the blowfish encryption cipher

I came up with the following example a few years back. Crypt::CBCperl is quite easy to use but can be confusing to new users of it. I prefer to use the subroutines encrypt_hex and decrypt as the encoded string is hexidecimal not in binary format. This allows me to work with it as if it was a normal string, such as sending it in a tweet or email or possibly embed it in an image.

use warnings;
use strict;
use Crypt::CBC;

our $cipher = Crypt::CBC->new(
  -key => 'g0oB3r__g0oB3r',
  -cipher => 'Blowfish'
);

# Encrypts a string and returns it.
sub encrypt {
  return( $cipher->encrypt_hex($_) );
}

# Decrypts an encrypted string and returns it.
sub decrypt {
  return( $cipher->decrypt( pack("H*", $_) ) );
}

#########

my $encrypted_string = encrypt('happy')
my $decrypted_string = decrypt($encrypted_string);

printf "'happy' -> encrypted as '%s' -> decrypted to '%s'\n", $encrypted_string, $decrypted_string;

“Why the perl community is no boy’s club” by tinita

If you haven’t read tinita‘s blog post, please do so.  As males we often don’t see the harm we do when we are “just joking around” or “teasing” or whatever our justification is for making crude statements.  I know I am not innocent either, most commonly because I wasn’t aware that <insert some comment> was offensive to another person.  We deserve to treat each other with respect.

First, the reason for this post: There was this answer in the recent survey:
“None – I refuse to acknowledge the term man hours, you patriarchical pig. But I have many person-hours. And let me tell you…”

You can discuss if this is discriminating feminists or not. It’s a matter of perception, if you know the author or not. It seems that there are people who find this offensive. And I also think that it shouldn’t be on a perlmonks poll, while in a group of friends it might be funny.

Read the rest of her post on tinita’s blog

HOWTO: Building Perl module DBD::Sybase 1.14 on Windows (32bit or 64bit) with ActiveState Perl 5.16, Microsoft Visual Studio and Sybase OpenClient 15.7

Compiling the DBD::Sybase Perl module really requires Microsoft Visual C++ 2005 or higher. To get started open the “Visual Studio 2005 Command Prompt”.Visual Studio 2005 Command Prompt

You will need to fix the Makefile.PL file:

if ( $^O eq 'MSWin32' ) {
  $lib_string = "-L$SYBASE/lib -llibct.lib -llibcs.lib -llibtcl.lib -llibcomn.lib -llibintl.lib -llibblk.lib $attr{EXTRA_LIBS} -lm";

to

if ( $^O eq 'MSWin32' ) {
  $lib_string = "-L$SYBASE/lib -llibsybct.lib -llibsybcs.lib -llibsybblk.lib $attr{EXTRA_LIBS}";

If you don’t, nmake won’t be able to link against the Sybase libraries. Note that we’re adding “syb” after “lib”.

Warning (mostly harmless): No library found for -llibct.lib
Warning (mostly harmless): No library found for -llibcs.lib
Warning (mostly harmless): No library found for -llibtcl.lib
Warning (mostly harmless): No library found for -llibcomn.lib
Warning (mostly harmless): No library found for -llibintl.lib
Warning (mostly harmless): No library found for -llibblk.lib
Warning (mostly harmless): No library found for -lm

When you run perl Makefile.PL, choose the defaults because the nmake test will NOT work with Visual Studio.
Next we need to change lines 3915 and 3916 in dbdimp.c because C89 requires that declarations of variables must occur at the beginning of a code block. This is part of the C89 specification.

for (i = 0; i < foundOutput; i++) { phs = params[i].phs; CS_DATAFMT datafmt;[/c] to [c num=1 highlight_lines = "2,3"]for (i = 0; i < foundOutput; i++) { CS_DATAFMT datafmt; phs = params[i].phs;[/c] If you don't we will get the following errors: [text]dbdimp.c(3916) : error C2275: 'CS_DATAFMT' : illegal use of this type as an expression C:\Sybase\OCS-15_0\include\cstypes.h(864) : see declaration of 'CS_DATAFMT' dbdimp.c(3916) : error C2146: syntax error : missing ';' before identifier 'datafmt' dbdimp.c(3916) : error C2065: 'datafmt' : undeclared identifier dbdimp.c(3918) : warning C4133: 'function' : incompatible types - from 'int *' to 'CS_DATAFMT *' dbdimp.c(3921) : error C2224: left of '.maxlength' must have struct/union type dbdimp.c(3926) : warning C4018: '< ' : signed/unsigned mismatch dbdimp.c(4146) : warning C4244: 'function' : conversion from 'CS_BIGINT' to 'const NV', possible loss of data dbdimp.c(4151) : warning C4244: 'function' : conversion from 'CS_UBIGINT' to 'const NV', possible loss of data dbdimp.c(5124) : warning C4244: '=' : conversion from 'long' to 'CS_BINARY', possible loss of data NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio 8\VC\BIN\cl.EXE"' : return code '0x2' Stop.[/text] The nmake will now complete with many warnings. I've started on working up a patch for the DBD::Sybase maintainer, Michael Peppler. Tar and gzip the blib directory and call it DBD-Sybase-1.14.tar.gz. Put it in a directory like so: "MSWin32-x64-multi-thread-5.16\DBD-Sybase-1.14.tar.gz" [text]nmake ppd[/text] Now, you will have a file called DBD-Sybase.ppd consisting of: [xml num=1]
DBI driver for Sybase datasources
Michael Peppler (mpeppler@peppler.org)




[/xml]

If you want to build multiple architectures, you will need to build the Module on the appropriate platform. e.g. Windows 7 64bit. I haven’t had much luck with cross-compilers with ActiveState Perl. YMMV. Once you have the second tar ball, simply add it to your PPD file:

<softpkg NAME="DBD-Sybase" VERSION="1.14">
    <abstract>DBI driver for Sybase datasources</abstract>
    <author>Michael Peppler (mpeppler@peppler.org)</author>
    <implementation>
        <architecture NAME="MSWin32-x64-multi-thread-5.16"></architecture>
        <codebase HREF="MSWin32-x64-multi-thread-5.16\DBD-Sybase-1.14.tar.gz"></codebase>
    </implementation>
    <implementation>
        <architecture NAME="MSWin32-x86-multi-thread-5.16"></architecture>
        <codebase HREF="MSWin32-x86-multi-thread-5.16\DBD-Sybase-1.14.tar.gz"></codebase>
    </implementation>
</softpkg>

I typically zip up the PPD file and the two directories listed in the PPD and distribute that. How you do it is entirely up to you.

Oh, if you want DBD::Sybase on Windows to connect to Microsoft SQL Server, build with FreeTDS.

Get it here! Perl DBD::Sybase 1.14.01 for Active State Perl 5.16 Windows XP/Vista/7/8 32bit AND 64bit

Assuming that you installed Sybase SDK OpenClient 15.7.

Sybase Openclient is included in the Sybase Developer’s Kit, and ASE PC Client. If you don’t have a license, you can download the ASE 15.7 Developer Edition for Windows which will include it.

Install ActiveState Perl from http://www.activestate.com (free) and install DBI if it isn’t already installed.  It should be but you never know…

  1. Start -> ActiveState Perl -> Perl Package Manager
  2. install DBI
  3. exit

Now, the easy part.  Install the DBD-Sybase-1.14 1.14 PPM:

  1. download DBD-Sybase-1.14
  2. extract zip file to temporary directory (e.g. c:\test)
  3. Start -> Run -> cmd.exe (as Administrator if Vista or Windows 7)
  4. cd \test
  5. ppm install DBD-Sybase.ppd
  6. exit

That’s it :)

It should automatically install the DBD::Sybase for Perl 5.16 32bit or 64bit depending on which version of Active State Perl you have installed.

UPDATE (March 6th, 2013):

The PPM is fixed now. Please let me know of any issues.

Perl Script to Convert an Audiobook (m4b) to mp3 files splitting on the chapters

I have an Nissan Altima with a BOSE radio that allows me to hook up an USB thumbdrive containing mp3 files. The problem is most of my audiobooks are in m4b format. Previously I’ve used tools like mp3splt and tried to split on ‘silence’ or timed increments (say 15 min) but I was getting mp3 files that would be split in midsentence and sometimes midword. It became very annoying after awhile.

So, I came up with a really simple script to convert an audiobook (m4b) into mp3 files splitting on the chapters. We are dependent on FFmpeg::Command and a modified FFprobe Perl module.

In the following example, we are converting a Ben Bova audiobook but we are going to specify to start the track numbering at “10” because the 2nd file ended with track “9”.

jason@jason-Inspiron-1545 ~/bin $ ./test_mp4_info.pl -i "/home/jason/Audiobooks/Ben Bova/Mars/Mars 3.m4b" -o mp3 -a "Mars" -t 10
Converting "Mars 3.m4b" to "mp3/010 Mars.mp3"...
	album: Mars
	artist: Ben Bova
	title: 010 - Mars
	genre: Audiobook
	track: 10
	... COMPLETE
Converting "Mars 3.m4b" to "mp3/011 Mars.mp3"...
	album: Mars
	artist: Ben Bova
	title: 011 - Mars
	genre: Audiobook
	track: 11
	... COMPLETE
Converting "Mars 3.m4b" to "mp3/012 Mars.mp3"...
	album: Mars
	artist: Ben Bova
	title: 012 - Mars
	genre: Audiobook
	track: 12
	... COMPLETE
Converting "Mars 3.m4b" to "mp3/013 Mars.mp3"...
	album: Mars
	artist: Ben Bova
	title: 013 - Mars
	genre: Audiobook
	track: 13
	... COMPLETE
Converting "Mars 3.m4b" to "mp3/014 Mars.mp3"...
	album: Mars
	artist: Ben Bova
	title: 014 - Mars
	genre: Audiobook
	track: 14
	... COMPLETE

Source code:

#!/usr/bin/perl

use strict;
use warnings;

use lib qw(/home/jason/bin);

use Getopt::Std;
use File::Basename;
use FFmpeg::Command;
use FFprobe;

$|++;

###############################
sub _encode_mp3 {
  my ($input_file, $output_dir, $album, $starting_track) = @_;

  my %tags = ();
  my $track_number;
  my $mp4 = FFprobe->probe_file($input_file);
  my $base_output_file = basename($input_file);
  $base_output_file =~ s/\.\w+$//;

  if (exists $mp4->{format}->{'TAG:comment'}) {
    $tags{genre} = $mp4->{format}->{'TAG:comment'};
    $tags{genre} =~ s/("')//g;
  }

  if (exists $mp4->{format}->{'TAG:genre'}) {
    $tags{genre} = $mp4->{format}->{'TAG:genre'};
    $tags{genre} =~ s/("')//g;
  }

  if (exists $mp4->{format}->{'TAG:artist'}) {
    $tags{artist} = $mp4->{format}->{'TAG:artist'};
    $tags{artist} =~ s/("')//g;
  }

  if ($album) {
    $tags{album} = $album;
  } elsif (exists $mp4->{format}->{'TAG:album'}) {
    $tags{album} = $mp4->{format}->{'TAG:album'};
  }

  $tags{album} =~ s/("')//g;
  $track_number = $starting_track if $starting_track;

  foreach my $chapter (sort keys %{$mp4->{chapters}}) {
    unless ($starting_track) {
      $track_number = $chapter;
    }

    my $output_file = sprintf "%s/%03d %s.mp3", $output_dir, $track_number, $tags{album};
    my $start = $mp4->{chapters}->{$chapter}->{start};
    my $duration = $mp4->{chapters}->{$chapter}->{end} - $start;
    my @options = ();

    if ($album) {
      $tags{title} = sprintf "%03d - %s", $track_number, $album;
    } else {
      if (exists $mp4->{format}->{'TAG:title'}) {
        $tags{title} = sprintf "%03d - %s", $track_number, $mp4->{format}->{'TAG:title'};
      } else {
        $tags{title} = sprintf "%03d - %s", $track_number, $base_output_file;
      }
    }

    $tags{title} =~ s/("')//g;

    my $ffmpeg = FFmpeg::Command->new;

    $ffmpeg->input_options({
        file => $input_file,
     });

    $ffmpeg->output_options({
     'file' => $output_file,
     'audio_codec' => 'libmp3lame',
     'audio_bit_rate' => 64,
     });

    printf "Converting \"%s\" to \"%s\"...\n", basename($input_file), $output_file;