Sponsoring The Perl Toolchain Summit 2025: Help make this important event another success Learn more

 / 
SQLite-More-0.10
River stage zero No dependents
/ SQLite::More

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.