Thursday 30 June 2011

DBI::SQLite and dbish

Today I needed to have a SQLite db with foreign keys and realise that my server's SQLite3 is very old (3.3.6). My perl DBD::SQLite is using version 3.7.6. I read in the DBD::SQLite that you can access to the SQLite db using dbish (a shell wrapper to DBI::Shell).

I wanted to use the same SQLite version in shell and scripts. Before installing a new version of sqlite3 I tried dbish.

dbish is part of DBI::Shell. It will be installed in your local-lib/bin when you install DBI::Shell.

First encounter was disappointing: following the POD it was not working at all

$   dbish dbi:SQLite:test.db

     DBI::Shell 11.95 using DBI 1.611

     WARNING: The DBI::Shell interface and functionality are
     =======  very likely to change in subsequent versions!


     Connecting to 'dbi:SQLite:test.db' as ''...
     @dbi:SQLite:test.db> table_info
     @dbi:SQLite:test.db> quit
     @dbi:SQLite:test.db> exit
     @dbi:SQLite:test.db> help
     @dbi:SQLite:test.db> type_info
     @dbi:SQLite:test.db> help
     @dbi:SQLite:test.db>

None of the comands worked :-(.


Googling around I found that the book 'Programming the Perl DBI' By Alligator Descartes, Tim Bunce has a chapter about it and discovered that the commnads must be preceded with a '/'.

@dbi:SQLite:srf2cram.db> /help
Defined commands, in alphabetical order: 
  [/;]chistory   display command history 
  [/;]clear      erase the current statement 
  [/;]col_info   display columns that exist in current database 
  [/;]commit     commit changes to the database 
  [/;]connect    connect to another data source/DSN 
  [/;]count      execute 'select count(*) from table' (on each table listed). 
  [/;]current    display current statement 
  [/;]describe   display information about a table (columns, data types). 
  [/;]do         execute the current (non-select) statement 
  [/;]drivers    display available DBI drivers 
  [/;]edit       edit current statement in an external editor 
  [/;]exit       exit 
  [/;]format     set display format for selected data (Neat|Box) 
  [/;]get        make a previous statement current again 
  [/;]go         execute the current statement 
  [/;]help       display this list of commands 
  [/;]history    display combined command and result history 
  [/;]load       load a file from disk to the current buffer. 
  [/;]option     display or set an option value 
  [/;]perl       evaluate the current statement as perl code 
  [/;]ping       ping the current connection 
  [/;]primary_key_info display primary keys that exist in current database 
  [/;]prompt     change the displayed prompt 
  [/;]quit       exit 
  [/;]redo       re-execute the previously executed statement 
  [/;]rhistory   display result history 
  [/;]rollback   rollback changes to the database 
  [/;]run        load a file from disk to current buffer, then executes. 
  [/;]save       save the current buffer to a disk file. 
  [/;]spool      send all output to a disk file. usage: spool file name or spool off. 
  [/;]table_info display tables that exist in current database 
  [/;]trace      set DBI trace level for current database 
  [/;]type_info  display data types supported by current server 
Commands can be abbreviated. 

dbish is interesting because I can interact whith the databases and test the DBI commands interactively, but I think that with SQLite3 I have more control and is better documented. So I ended installing the latest version of SQLite3.

[update]

I downloaded the latest precompiled sqlite3 and it was not working properly in my CentOS release 5.4: the cursor got detached from the line and was able to move through all the screen (not allowing up for previous history). Also was not quiting.

$  sqlite3 test.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .q
   ...> ;
   ...>
   ...>
   ...>
   ...>
I killed manually and compiled from the 'amalgamation' download and now works fine.


[edit2]

After reading this question in StackOverflow about how to set by default the foreign keys for a database, I am having second thoughts about using foreign keys with SQLite:

No, not even with compile-time options.

The only way, so far, is to use pragma foreign_keys=on at run time. The particular danger is that every application that touches the database has to do that.

If a specific application doesn't run that pragma statement, it can insert data that will violate foreign key constraints, and other applications won't know it. That is, turning on foreign keys doesn't warn you of existing data that violates the constraint.

Monday 27 June 2011

qrcode generator with perl

There are some good pages for genereating QRcode and a lot of not so good ones. There are probably to many to decide which one to use so I decided to create yet another one:
pmg-qrcode




I have used
HTML::QRCode
Imager::QRCode



[...]
use Imager::QRCode;
use HTML::QRCode;

[...]

sub get_html_qr {
  my $text = shift;
  my $qrcode = HTML::QRCode->new->plot($text);
  return $qrcode;
}

sub print_qrimage {
  my $text = shift;
  my $imga_type = shift || 'png';
  my $qr = Imager::QRCode->new(
      size  =>  5,
      level => 'M',
    );

  $qr->plot($text)->write( fh => \*STDOUT, type => $img_type);
}


This is very simple, but .....
the difficult part is to have them working because the prerequisites not very well explained.

If you read the README for HTML::QRCode it says that it works out of the box with an standard cpan install. Well that is true if you have already Text::QRCode, that usually is not the case. Don't panic, lets install T::QRC and all will be fine. Humm... Marvin still depressed. Despite T::QRC also telling you about doing a standard install and all will go OK, again, that will only be true if you have all prerequisites already installed and in the default place. T::QRC needs libqrencode headers and libs and look for them at the root installed paths. The README does not say anything about this but the description of T::QRC gives a hint
DESCRIPTION ^
This module allows you to generate QR Code using ' ' and '*'. 
This module use libqrencode '2.0.0' and above.  

To make the long story short, I tried to install HTML::QRCode in my local-lib environment and failed because Text::QRCode missing. I tried to install T::QRC and failed because no lib-qrencode. Installed locally lib-qrencode and not able to install T::QRC because not knowing how to pass a 'prefix' option to Makefile.pl for the ld_lib and includes. Solved editing the Makefile.PL manually, adding an env-var for LD_RUN_PATH and install manually T::QRC and then cpan install of HTML::QRCode.

Long story:

HTML::QRCode needs Text::QRCode and Text::QRCode needs lib-qrencode:

HTML::QRCode:
t/00-load.t .. 1/1
        #   Failed test 'use Text::QRCode;'
        #   at t/00-load.t line 6.
        #     Tried to use 'Text::QRCode'.
        #     Error:  Can't load '/homes/pmg/.cpan/build/Text-QRCode-0.01-n4g2CG/blib/arch/auto/Text/QRCode/QRCode.so' for module Text::QRCode: libqrencode.so.3: cannot open shared object \
file: No such file or directory at /homes/pmg/pmg-soft/local-perl/lib/5.12.1/x86_64-linux/DynaLoader.pm line 200

I searched for libqrencode and download it:

# download
wget http://fukuchi.org/works/qrencode/qrencode-3.1.1.tar.gz
# extract
# install
[~/pmg-soft/src/qrencode-3.1.1]
$  ./configure --prefix=$HOME/pmg-soft
$ make
$ make install

And obtained a helpful message that I save for later:

----------------------------------------------------------------------
Libraries have been installed in:
/homes/pmg/pmg-soft/lib

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
- add LIBDIR to the `LD_LIBRARY_PATH' environment variable
during execution
- add LIBDIR to the `LD_RUN_PATH' environment variable
during linking
- use the `-Wl,-rpath -Wl,LIBDIR' linker flag
- have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------

Now I have a T::QRC build extracted somewhere (I can see the path in the error message) [ Error: Can't load '/homes/pmg/.cpan/build/Text-QRCode-0.01-n4g2CG/blib/arch/auto/Text/QRCode/QRCode.so'].

I went there and read the Makefile.PL. It has two places (includes and ld-lib) where I need to add the path for my local lib-qrencode files:

a) the directives for the Makefile

sub MY::post_constants {
  [...]
  return <<"POST_CONST";
  CCFLAGS += $define -I/homes/pmg/pmg-soft/include
  LDDLFLAGS += -L/homes/pmg/pmg-soft/lib -lqrencode
  LDFLAGS += -L/homes/pmg/pmg-soft/lib -lqrencode
  POST_CONST
}
b) the comand for testing that the lib-qrencode exist (testing existence by compilation success)
sub test_libqrencode {
  my $compile_cmd
        = 'cc -I/homes/pmg/pmg-soft/include -I/usr/local/include -I/usr/include -L/homes/pmg/pmg-soft/lib -L/usr/lib -L/usr/local/lib -lqrencode';
  [..]
}
Then exported the environmental variable 'LD_RUN_PATH':
export LD_RUN_PATH=/homes/pmg/pmg-soft/lib
$ make clean
$ perl Makefile.PL 
| Cannot determine perl version info from lib/Text/QRCode.pm
| Checking if your kit is complete...
| Looks good 
| Writing Makefile for Text::QRCode
$ make          
$ make test
| PERL_DL_NONLAZY=1 /homes/pmg/pmg-soft//local-perl/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'inc', 'blib/lib', 'blib/arch')" t/00-load.t t/01-plot.t
| t/00-load.t .. 1/1 # Testing Text::QRCode 0.01, Perl 5.012001, /homes/pmg/pmg-soft//local-perl/bin/perl
| t/00-load.t .. ok
| t/01-plot.t .. ok
| All tests successful.
| Files=2, Tests=3,  0 wallclock secs ( 0.02 usr  0.03 sys +  0.06 cusr  0.04 csys =  0.15 CPU)
| Result: PASS
$ make install
|Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
|Installing /homes/pmg/pmg-soft/local-perl/local-lib/lib/perl5/x86_64-linux/auto/Text/QRCode/QRCode.so
|Installing /homes/pmg/pmg-soft/local-perl/local-lib/lib/perl5/x86_64-linux/auto/Text/QRCode/QRCode.bs  
|Installing /homes/pmg/pmg-soft/local-perl/local-lib/lib/perl5/x86_64-linux/Text/QRCode.pm
|Installing /homes/pmg/pmg-soft/local-perl/local-lib/man/man3/Text::QRCode.3
|Appending installation info to /homes/pmg/pmg-soft/local-perl/local-lib/lib/perl5/x86_64-linux/perllocal.pod

Then go to cpan and intall HTML::QRCode
cpan[2]> install HTML::QRCode 

That's all, happy hacking.


Pablo