The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

NAME

SQLite::More - Add more SQL functions to SQLite in Perl - some of those found in Oracle and others

SYNOPSIS

 use DBI 1.609;
 use SQLite::More;
 my $file = '/path/to/some/database_file.sqlite';
 my $dbh  = DBI->connect("dbi:SQLite:$file");
 my $sql  = "select median(salary) from employee";         #  <---- median
 sqlite_more($dbh);                                        #  attach more functions
 print "Median salary: ";
 print "".( ($dbh->selectrow_array($sql))[0] )."\n";

DESCRIPTION

SQLite do not have all the SQL functions that Oracle and other RDBMSs have.

Using SQLite::More makes more of those functions available to user SQL statements.

SQLite::More uses the class function sqlite_create_function() of DBD::SQLite, which is available from DBD::SQLite version 1.27 released 23. nov. 2009.

Extra functions added my SQLite::More version 0.02:

Normal row functions:

 nvl(x,y)                              if x is null then y else x
 decode(x, c1,r1, c2,r2, c3,r3, d)     if x=c1 then r1 elsif x=c2 then r2 elsif x=c3 then r3 else d

 upper(string)               returns perls uc() of that string, may not work for chars other than a-z
 lower(string)               returns perls lc() of that string, may not work for chars other than A-Z
 least(n1,n2,n3,...)         returns the minimum number except null values
 greatest(n1,n2,n3,...)      returns the maximum number except null values
 md5(string)                 returns the 128-bit binary MD5-"string", uses Digest::MD5::md5()
 md5_hex(string)             returns the hexadecimal representation of md5 of a string, uses Digest::MD5::md5_hex()
 random(a,b)                 returns a pseudo-random number between a and b inclusive, i.e. random(1,6) "is" a dice
 time()                      returns the number of seconds since 1. jan 1970, uses perls time function
 sprintf(format,x,y,z,...)   returns a string, uses perl sprintf function

 sqrt(x)                     returns the square root of a number
 power(x,p)                  returns x^p or x**p sice either is an operator in SQL
 ln(x)                       returns the natural logarithm of x, based on e = 2.718281828459...
 log(x)                      returns the natural logarithm of x, based on e = 2.718281828459...
 loge(x)                     returns the natural logarithm of x, based on e = 2.718281828459...
 log10(x)                    returns the logarithm of x, based on 10, that is log10(1000) = 3
 log2(x)                     returns the logarithm of x, based on 2, that is log2(1024) = 10
 pi()                        returns the constant 3.14159265358979323846264338327950288419716939937510
 sin(x)                      returns result of x of trigonometric sinus, x in radians, sin(pi/2) = 1
 cos(x)                      returns result of x of trigonometric sinus, x in radians, cos(pi) = -1
 tan(x)                      tan(x) = sin(x) / cos(x)
 atan2(x,y)                  

 distance(lat1,lon1,lat2,lon2)   ca the earth surface distance in meters given two geographical coordinates

 perlhash('hashname',k,v)    returns $package::hashname{k} where package is main or the callers package

Aggregate functions:

 median(value)
 percentile(p,value)         p is a number between 0 and 100
 percentile(50,value)        same as median(value)
 variance(value)             sum(map ($_-avg(@values))**2, @values)
 stddev(value)               standard deviation, sqrt( (n*sum(map$_**2,@values)-sum(@values)**2) / (n*(n-1)) )

perlhash

The perlhash function takes two or three input arguments.

First argument:

Name of a perl hash. The name can be fully qualified with package name (like main::h or MyClass::hash) or just the hashname in which case the package name is deducted by SQLite::More using perls caller().

Remember to declare the hash with our or local, do not use my.

Two arguments:

- the perl hash value is returned

Three arguments:

- the perl hash old value is returned, but sets the value to the third argument.

Example:

 our %hash=(good=>123, bad=>234, ugly=>345);
 $dbh->do("update some_table set score=perlhash('hash',name)       where name in ('good','bad','ugly')");
 $dbh->do("update some_table set score=perlhash('main::hash',name) where name in ('good','bad','ugly')");
 $dbh->do("update some_table set score=perlhash('hash',name,0)     where name in ('good','bad','ugly')");
 print $hash{'good'}; # some_table.score is 123 for 'good', but $hash{'good'} is set to 0 afterwards.

 package notmain;
 our %hash=(good=>111, bad=>222, ugly=>333);
 $dbh->do("update some_table set score=perlhash('hash',name)         "); # this and
 $dbh->do("update some_table set score=perlhash('notmain::hash',name)"); # this is the same

EXPORT

 sqlite_more($dbh)

SQL EXAMPLES

 select
   department,                                 -- which department
   sum(1),                                     -- number of employees in each department
   avg(salary),                                -- average salsry in each department
   median(salary),                             -- median salary in each department
   max(salary),                                -- top earners salary in each department
   stddev(salary),                             -- standard deviatino within department
   percentile(90,salary),                      -- minimum salary of the top 10% earners in each department
   sum(decode(least(100000,salary),100000,1)), -- how many six figure earners (and 7 and 8 and ...) in each department
   sum(salary)                                 -- total salaries in each department
 from employees
 group by department;

 update player set dice = random(1,6);

INSTALLING

 sudo cpan DBI                 # needs >= 1.609 it seems, at least 1.607 isn't ok
 sudo cpan DBD::SQLite         # needs >= 1.27
 sudo cpan SQLite::More

Or:

 sudo /usr/bin/cpan DBI                 # needs >= 1.609 it seems, at least 1.607 isn't ok
 sudo /usr/bin/cpan DBD::SQLite         # needs >= 1.27
 sudo /usr/bin/cpan SQLite::More

Or:

 sudo apt-get install perl-DBI perl-DBD-SQLite    # might be too old
 sudo cpan SQLite::More

Or:

 sudo yum install perl-DBI perl-DBD-SQLite        # might be too old
 sudo cpan SQLite::More

Or even messier:

 sudo bash
 perl -MDBI          -le'print$DBI::VERSION'           # check current version, should be at least 1.609
 perl -MDBD::SQLite  -le'print$DBD::SQLite::VERSION'   # check current version, should be at least 1.27
 perl -MSQLite::More -le'print$SQLite::More::VERSION'  # check current version
 cd /tmp

 VERSION=1.609
 wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-$VERSION.tar.gz
 tar zxf DBI-$VERSION.tar.gz
 cd      DBI-$VERSION
 perl Makefile.PL  # PREFIX=/...   #possibly
 make test && make install
 #make install                     #maybe anyway
 #--Maybe even:
 #cp -p /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/DBI.so   \
 #      /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/DBI.so

 VERSION=1.31
 wget http://search.cpan.org/CPAN/authors/id/A/AD/ADAMK/DBD-SQLite-$VERSION.tar.gz
 tar zxf DBD-SQLite-$VERSION.tar.gz
 cd      DBD-SQLite-$VERSION
 perl Makefile.PL  # PREFIX=/...   #possibly
 make test && make install
 #make install                     #maybe anyway

 VERSION=0.10
 wget http://search.cpan.org/CPAN/authors/id/K/KJ/KJETIL/SQLite-More-$VERSION.tar.gz
 tar zxf SQLite-More-$VERSION.tar.gz
 cd      SQLite-More-$VERSION
 perl Makefile.PL  # PREFIX=/...   #possibly
 make test && make install

SEE ALSO

DBD::SQLite

DBI

HISTORY

Release history

 0.10   Nov 2010

AUTHOR

Kjetil Skotheim, <kjetilskotheim@gmail.com>

COPYRIGHT AND LICENSE

Copyright (C) 2010 by Kjetil Skotheim

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.