misc software development snippets

We are using the Perl module, Spreadsheet::ParseExcel->new to parse XLS files and insert the results into a UTF8 MySQL database.

I came across an interesting situation with a Microsoft Excel file, where only 3 fields of the 12 are encoded as UTF-16BE, the rest are latin1.

Parsing a file with mixed encoding returns unexpected results, since the data in the spreadsheet looks correct, but parsing in it one receives incorrect characters.

Here is some code using Encode::Guess which will resolve the mixed encoding issue.

my $enc = guess_encoding($text); # use Encode::Guess;
if (ref $enc) {
    $descr = decode($enc->name, $text);
}
else { # convert to utf8, does not turn on utf8 flag
  from_to($text, "iso-8859-1", "utf8");
}

or, if you are already expecting UTF8 data, or more specifically, data with the utf8 flag turned on (perl 5.8.* - see perldoc Encode)

use Encode qw/decode from_to/;
use Encode::Guess;
if (! utf8::is_utf8($val)) { # ok, its not utf8, what is it?
    my $enc = guess_encoding($val); # use Encode::Guess;
    if (ref $enc) {
        $val = decode($enc->name, $val); #turns on utf8 flag
    }
}

(you would have to use these on a field-by-field/val-by-val basis obviously)

I just ran a sudo yum update on my development CentOS 5.3 box. (upgrade to 5.4)

All went well, except two issues.

1. I had to rollback perl-Apache-DBI.noarch 1.07-1.el5.rf
(luckily, I knew about this one, and had an old package available, and there are no deps issues - whew)

The issue manifests itself as:
[Tue Nov 10 10:43:02 2009] [error] Global $r object is not available. Set:\n\tPerlOptions +GlobalRequest\nin httpd.conf at /usr/lib/perl5/vendor_perl/5.8.8/Apache/DBI.pm line 144.\nCompilation failed in require at (eval 2) line 1.\n
[Tue Nov 10 10:43:02 2009] [error] Can't load Perl file: perl_load.pl for server , exiting...

To rollback:
$ sudo yum remove perl-Apache-DBI
$ sudo rpm -ivh perl-Apache-DBI-1.06-1.el5.rf.noarch.rpm

2. synopsis: "Weak references are not implemented in the version of perl "

The issue manifests itself as:
Weak references are not implemented in the version of perl at /usr/lib/perl5/vendor_perl/5.8.8/SOAP/Lite.pm line 2447\nBEGIN failed--compilation aborted at /usr/lib/perl5/vendor_perl/5.8.8/SOAP/Lite.pm line 2447.\nCompilation failed in require,...
...
...
[Wed Nov 11 16:50:01 2009] [error] Can't load Perl file: perl_load.pl for server, exiting...

which seemed me to (incorrectly) initially look in SOAP::Lite. (it's a Scalar::Util warning) Luckily I had another box I had just updated and knew that the updated did in fact work.

here is a command line test, to check with:
% perl -e"use Scalar::Util qw( weaken );"
%

should return nothing, on my box it returned:
% perl -e"use Scalar::Util qw( weaken );"
Weak references are not implemented in the version of perl at -e line 1
BEGIN failed--compilation aborted at -e line 1.

I couldn't find Scalar::Util installed!?
$ yum list|grep perl |grep -i scalar
perl-Scalar-Defer.noarch 0.20-1.el5.rf rpmforge
perl-Scalar-List-Utils.x86_64 1.21-1.el5.rf rpmforge
perl-Scalar-Properties.noarch 0.13-1.el5.rf rpmforge
perl-Set-Scalar.noarch 1.24-1.el5.rf rpmforge
perl-Tie-Scalar-Timeout.noarch 1.33-1.el5.rf rpmforge

After some searching, it appears that it's part or perl core now.

Eventually, I stumbled onto:
% locate Util.pm
/usr/lib/perl5/5.8.8/CGI/Util.pm
/usr/lib/perl5/5.8.8/Hash/Util.pm
/usr/lib/perl5/5.8.8/List/Util.pm
/usr/lib/perl5/5.8.8/Scalar/Util.pm
/usr/lib/perl5/vendor_perl/5.8.8/CGI/Simple/Util.pm
/usr/lib/perl5/vendor_perl/5.8.8/Crypt/DSA/Util.pm
/usr/lib/perl5/vendor_perl/5.8.8/HTTP/Headers/Util.pm
/usr/lib/perl5/vendor_perl/5.8.8/Mail/Util.pm
/usr/lib/perl5/vendor_perl/5.8.8/Mail/SpamAssassin/Util.pm
/usr/lib/perl5/vendor_perl/5.8.8/Net/SFTP/Util.pm
/usr/lib/perl5/vendor_perl/5.8.8/Net/SSH/Perl/Util.pm
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/List/Util.pm
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Scalar/Util.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/APR/Util.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Apache/TestUtil.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Apache2/ConnectionUtil.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Apache2/RequestUtil.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Apache2/ServerUtil.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Apache2/Util.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/MapUtil.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/Util.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Params/Util.pm

The only difference in the results of this and the box that works,
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/List/Util.pm
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Scalar/Util.pm are not in the list, of the working box.

(I have no idea how those got there, or where they are from, except for what the obviously look like, rhel5/centos packages,...)

perl INC is loading those libs first too.

$ perl -e 'print join "\n", @INC'
/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.8
/usr/lib/perl5/site_perl
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.8
/usr/lib/perl5/vendor_perl
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/5.8.8

so to 'resolve' the issue, I did this.

% sudo mv /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/List/Util.pm /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/List/Util.pm-1.19-broken-with-5.8.8-27
% sudo mv /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Scalar/Util.pm /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Scalar/Util.pm-1.19-broken-with-5.8.8-27

This worked for me, although is probably not the 'correct way'. I'm still not sure exactly what happened to get into this state, or I'd file a bug somewhere.

ref: (1)http://www.gossamer-threads.com/lists/modperl/modperl/98113

(1)http://rt.cpan.org/Public/Bug/Display.html?id=36346

(2) http://www.perlmonks.org/?node_id=424737 A different approach to the situation. (in general, we try to do everything through yum/rpm packages)
(2)http://bugs.centos.org/view.php?id=3022 an old bug, which made me realize I had to figure it out
(2) http://www.bluequartz.us/phpBB2/viewtopic.php?t=106577&sid=17481305416e016b01a061acb4ac3f45

Task: create a debian package for the CPAN perl module ACME-QuoteDB.

From the Docs:

ACME::QuoteDB − API implements CRUD for a Collection of Quotes (adages/proverbs/sayings/epigrams, etc)

This module provides an easy to use programmitic interface to a database (sqlite3 or mysql) of ’quotes’. (any content really, that can fit into our "defined format")

For simplicty you can think of it as a modern fancy perl version of fortune (with a management interface, remote database connection support, plus additional features and some not (yet) supported)

Supported actions include: (CRUD)

> Create
* Adding quote(s)
* ’Batch’ Loading quotes from a file (stream, other database, etc)

> Read
* Displaying a single quote, random or based on some criteria
* Displaying multiple quotes, based on some criteria
* Displaying a specific number of quotes, based on some search criteria

> Update
* Update an existing quote

> Delete
* Remove an existing quote

yeah, ok, whatever, that may be useful,...
so, let's get on with it.

$ wget http://search.cpan.org/CPAN/authors/id/D/DV/DVWRIGHT/ACME-QuoteDB-0.1.0.tar.gz
$ tar zxvf ACME-QuoteDB-0.1.0.tar.gz
$ tar zcvf libacme-quotedb-perl_0.1.0.orig.tar.gz ACME-QuoteDB
$ mv ACME-QuoteDB libacme-quotedb-perl-0.1.0
$ cd libacme-quotedb-perl-0.1.0
$ dh-make-perl
[ this failed first time becuase it could not find a Makefile.PL
the perl module author used Module::Build, which uses a Build.PL,
in place of a Makefile.PL and they did not include a Makefile.PL.
You can get around this by running:
$ perl Build.PL
$./Build dist (creates a Makefile.PL)
]
$ dh-make-perl (ran successfully now)

now edit files:
debian/changelog
debian/control
debian/copyright

$ debuild -us -uc
dpkg-buildpackage -rfakeroot -D -us -uc
dpkg-buildpackage: set CFLAGS to default value: -g -O2
dpkg-buildpackage: set CPPFLAGS to default value:
dpkg-buildpackage: set LDFLAGS to default value:
dpkg-buildpackage: set FFLAGS to default value: -g -O2
dpkg-buildpackage: set CXXFLAGS to default value: -g -O2
dpkg-buildpackage: source package libacme-quotedb-perl
dpkg-buildpackage: source version 0.1.0-1
dpkg-buildpackage: source changed by david wright
dpkg-buildpackage: host architecture i386
fakeroot debian/rules clean
dh_testdir
dh_testroot
dh_clean build-stamp install-stamp
# Add commands to clean up after the build process here
[ ! -f Build ] || /usr/bin/perl Build --allow_mb_mismatch 1 distclean
No such file: lib/ACME/QuoteDB/DB/quotes.db
Not in MANIFEST: debian/changelog
Not in MANIFEST: debian/compat
Not in MANIFEST: debian/control
Not in MANIFEST: debian/copyright
Not in MANIFEST: debian/rules
Not in MANIFEST: debian/watch
Not in MANIFEST: lib/ACME/QuoteDB/DB/quotes.db-ok
Not in MANIFEST: Makefile.PL
Not in MANIFEST: t/data/simpsons_quotes.tsv.csv-with-rating
MANIFEST appears to be out of sync with the distribution
Deleting blib
Deleting _build
Deleting Build
dpkg-source -b libacme-quotedb-perl-0.1.0
dpkg-source: info: using source format `1.0'
dpkg-source: info: building libacme-quotedb-perl using existing libacme-quotedb-perl_0.1.0.orig.tar.gz
dpkg-source: info: building libacme-quotedb-perl in libacme-quotedb-perl_0.1.0-1.diff.gz
dpkg-source: warning: ignoring deletion of file ACME-QuoteDB.pidaproject
dpkg-source: warning: ignoring deletion of file lib/ACME/QuoteDB/DB/quotes.db
dpkg-source: info: building libacme-quotedb-perl in libacme-quotedb-perl_0.1.0-1.dsc
debian/rules build
dh_testdir
# Add commands to compile the package here
/usr/bin/perl Build.PL installdirs=vendor
Creating custom builder _build/lib/MyModuleBuilder.pm in _build/lib
WARNING: the following files are missing in your kit:
lib/ACME/QuoteDB/DB/quotes.db
Please inform the author.

Checking whether your kit is complete...
Checking prerequisites...
Looks good

Creating new 'Build' script for 'ACME-QuoteDB' version '0.1.0'
/usr/bin/perl Build
Copying lib/ACME/QuoteDB.pm -> blib/lib/ACME/QuoteDB.pm
Copying lib/ACME/QuoteDB/LoadDB.pm -> blib/lib/ACME/QuoteDB/LoadDB.pm
Copying lib/ACME/QuoteDB/DB/Category.pm -> blib/lib/ACME/QuoteDB/DB/Category.pm
Copying lib/ACME/QuoteDB/DB/Attribution.pm -> blib/lib/ACME/QuoteDB/DB/Attribution.pm
Copying lib/ACME/QuoteDB/DB/QuoteCatg.pm -> blib/lib/ACME/QuoteDB/DB/QuoteCatg.pm
Copying lib/ACME/QuoteDB/DB/DBI.pm -> blib/lib/ACME/QuoteDB/DB/DBI.pm
Copying lib/ACME/QuoteDB/DB/Quote.pm -> blib/lib/ACME/QuoteDB/DB/Quote.pm
Manifying blib/lib/ACME/QuoteDB/LoadDB.pm -> blib/libdoc/ACME::QuoteDB::LoadDB.3pm
Manifying blib/lib/ACME/QuoteDB/DB/QuoteCatg.pm -> blib/libdoc/ACME::QuoteDB::DB::QuoteCatg.3pm
Manifying blib/lib/ACME/QuoteDB.pm -> blib/libdoc/ACME::QuoteDB.3pm
Manifying blib/lib/ACME/QuoteDB/DB/Attribution.pm -> blib/libdoc/ACME::QuoteDB::DB::Attribution.3pm
Manifying blib/lib/ACME/QuoteDB/DB/Category.pm -> blib/libdoc/ACME::QuoteDB::DB::Category.3pm
Manifying blib/lib/ACME/QuoteDB/DB/Quote.pm -> blib/libdoc/ACME::QuoteDB::DB::Quote.3pm
Manifying blib/lib/ACME/QuoteDB/DB/DBI.pm -> blib/libdoc/ACME::QuoteDB::DB::DBI.3pm
/usr/bin/perl Build test
t/00-load...................# Testing ACME::QuoteDB 0.1.0, Perl 5.010000, /usr/bin/perl
ok
t/01-load_quotes............ok
t/02-get_quotes.............ok
t/03-load_quotes_env........ok
t/04-get_quotes_more........ok
t/04-load_get_quote_utf8....ok
t/05-load_quotes_remote.....ok
t/boilerplate...............ok
t/pod-coverage..............ok
t/pod.......................ok
All tests successful.
Files=10, Tests=128, 22 wallclock secs ( 9.49 cusr + 0.76 csys = 10.25 CPU)
touch build-stamp
fakeroot debian/rules binary
dh_testdir
dh_testroot
dh_clean -k
# Add commands to install the package into /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl here
/usr/bin/perl Build install destdir=/tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl create_packlist=0
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB.pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB/LoadDB.pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB/DB/Quote.pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB/DB/Category.pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB/DB/DBI.pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB/DB/QuoteCatg.pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB/DB/Attribution.pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/perl5/ACME/QuoteDB/DB/quotes.db
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/man/man3/ACME::QuoteDB::DB::Category.3pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/man/man3/ACME::QuoteDB::LoadDB.3pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/man/man3/ACME::QuoteDB::DB::QuoteCatg.3pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/man/man3/ACME::QuoteDB::DB::Attribution.3pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/man/man3/ACME::QuoteDB::DB::DBI.3pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/man/man3/ACME::QuoteDB::DB::Quote.3pm
Installing /tmp/t/libacme-quotedb-perl-0.1.0/debian/libacme-quotedb-perl/usr/share/man/man3/ACME::QuoteDB.3pm
touch install-stamp
dh_testdir
dh_testroot
dh_installdocs README
dh_installchangelogs Changes
dh_perl
dh_compress
dh_fixperms
dh_installdeb
dh_gencontrol
dpkg-gencontrol: warning: unknown substitution variable ${misc:Depends}
dh_md5sums
dh_builddeb
dpkg-deb: building package `libacme-quotedb-perl' in `../libacme-quotedb-perl_0.1.0-1_all.deb'.
dpkg-genchanges >../libacme-quotedb-perl_0.1.0-1_i386.changes
dpkg-genchanges: including full source code in upload
dpkg-buildpackage: full upload (original source is included)
Now running lintian...
W: libacme-quotedb-perl: manpage-has-errors-from-man usr/share/man/man3/ACME::QuoteDB.3pm.gz 950: warning [p 12, 5.7i]: can't break line
W: libacme-quotedb-perl: old-fsf-address-in-copyright-file
W: libacme-quotedb-perl: copyright-contains-dh-make-perl-boilerplate
E: libacme-quotedb-perl: description-too-long
W: libacme-quotedb-perl: spelling-error-in-description mysql MySQL
W: libacme-quotedb-perl: new-package-should-close-itp-bug
Finished running lintian.
Ok, now let's clean up those 'lintian' errors.

we are mainly concerned with fixing the lintian error 'E'
but we'll clear up all we can.

1. manpage-has-errors-from-man
skiping, cant control manpage now.

2.old-fsf-address-in-copyright-file
http://lintian.debian.org/tags/old-fsf-address-in-copyright-file.html

3. copyright-contains-dh-make-perl-boilerplate
self explanitory.

4. description-too-long
http://lintian.debian.org/tags/description-too-long.html
The first line of the "Description:" must not exceed 80 characters.

5. spelling-error-in-description mysql MySQL
self explanitory.

6. new-package-should-close-itp-bug
http://lintian.debian.org/tags/new-package-should-close-itp-bug.html
isn't relavent in this situation becuase there is no bug request for this package.

ok, cleared what we could up, re-run

$ debuild -us -uc
Now running lintian...
W: libacme-quotedb-perl: manpage-has-errors-from-man usr/share/man/man3/ACME::QuoteDB.3pm.gz 950: warning [p 12, 5.7i]: can't break line
W: libacme-quotedb-perl: new-package-should-close-itp-bug
Finished running lintian.

ok, better

built on
debian stable - lenny
cat /etc/debian_version
5.0.3

$ tar zcvf libacme-quotedb-perl-0.1.0.tar.gz libacme-quotedb-perl_0.1.0-1*
libacme-quotedb-perl_0.1.0-1_all.deb
libacme-quotedb-perl_0.1.0-1.diff.gz
libacme-quotedb-perl_0.1.0-1.dsc
libacme-quotedb-perl_0.1.0-1_i386.build
libacme-quotedb-perl_0.1.0-1_i386.changes

which you can find for download here:
http://www.dwright.us/misc/libacme-quotedb-perl-0.1.0.tar.gz

$ sudo dpkg -i libacme-quotedb-perl_0.1.0-1_all.deb

update Sat Sep 26 01:04:23 PDT 2009: the libacme-quotedb-perl_0.1.0-1_all.deb package was not properly created, when installed, one could not write to the database, that is corrected now with this version: (libacme-quotedb-perl_0.1.2)
http://www.dwright.us/misc/libacme-quotedb-perl-0.1.2.tar.gz


$ sudo dpkg -i libacme-quotedb-perl_0.1.2-1_all.deb
proof of the test db installed by default:
$ perl -MACME::QuoteDB -le 'print quote()'
나는 유리를 먹을 수 있어요. 그래도 아프지 않아요
-- I can eat grass (Korean)

this version is signed and was built on debian: squeeze/sid (which is currently testing; lenny is stable)

references:
http://www.debian-administration.org/articles/78
http://www.debian-administration.org/articles/78#comment_9
http://www.debian.org/doc/packaging-manuals/perl-policy/
http://www.debian.org/doc/maint-guide/