SQLite::More - Add more SQL functions to SQLite in Perl - some of those found in Oracle and others
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";
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
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.
sqlite_create_function()
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)) )
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().
main::h
MyClass::hash
hashname
Remember to declare the hash with our or local, do not use my.
our
local
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
sqlite_more($dbh)
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);
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
sudo apt-get install perl-DBI perl-DBD-SQLite # might be too old sudo cpan SQLite::More
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
DBD::SQLite
DBI
Release history
0.10 Nov 2010
Kjetil Skotheim, <kjetilskotheim@gmail.com>
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.
To install SQLite::More, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQLite::More
CPAN shell
perl -MCPAN -e shell install SQLite::More
For more information on module installation, please visit the detailed CPAN module installation guide.