Alien-DBD-SQLite-BundledExtensions

 view release on metacpan or  search on metacpan

lib/Alien/DBD/SQLite/BundledExtensions.pm  view on Meta::CPAN

package Alien::DBD::SQLite::BundledExtensions;

use Config;
use File::ShareDir 'dist_dir';

our $VERSION=0.001;
our @extensions = qw/spellfix csv ieee754 nextchar percentile series totype wholenumber eval/;

my $dbd_dist = dist_dir('DBD-SQLite');
my $cc = $Config{cc};

# TODO this probably doesn't work on windows anyway.  Need to look into how to make that work correctly anyway.
my $lib_ext = $^O =~ /mswin/ ? 'dll' : 
              $^O =~ /darwin/ ? 'dylib' : 'so';

# TODO this needs to support mswin32

lib/DBD/SQLite/BundledExtensions.pm  view on Meta::CPAN

package DBD::SQLite::BundledExtensions;

use strict;

use File::Find;
use File::Spec;

for my $ext (qw/spellfix csv ieee754 nextchar percentile series totype wholenumber eval/) {
    eval "sub load_${ext} {my (\$self, \$dbh)=\@_; \$self->_load_extension(\$dbh, '${ext}')}";
}

sub _load_extension {
    my ($self, $dbh, $extension_name) = @_;

    my $file = $self->_locate_extension_library($extension_name);

    $dbh->sqlite_enable_load_extension(1);
    $dbh->do("select load_extension(?)", {}, $file)

lib/DBD/SQLite/BundledExtensions.pm  view on Meta::CPAN

=head2 load_eval

    Loads the eval extension that gives you the C<eval()> SQL function.  Works pretty much like eval in perl but for SQL.  Probably dangerous.

=head2 load_ieee754 

    Gives you some functions for dealing with ieee754 specific issues.

=head2 load_nextchar

=head2 load_percentile 

=head2 load_series 

=head2 load_spellfix 

=head2 load_totype 

=head2 load_wholenumber 

=head1 KNOWN ISSUES

lib/DBD/SQLite/BundledExtensions.pm  view on Meta::CPAN

use of the table name and column name as part of a query.  If either
the table name or column name are keywords or contain special characters,
then they should be escaped.  For example:

    SELECT next_char('cha','[dictionary]','[word]');

This also means that the table name can be a subquery:

    SELECT next_char('cha','(SELECT word AS w FROM dictionary)','w');

=item percentile 

This file contains code to implement the percentile(Y,P) SQL function
as described below:

  (1)  The percentile(Y,P) function is an aggregate function taking
       exactly two arguments.
  (2)  If the P argument to percentile(Y,P) is not the same for every
       row in the aggregate then an error is thrown.  The word "same"
       in the previous sentence means that the value differ by less
       than 0.001.
  (3)  If the P argument to percentile(Y,P) evaluates to anything other
       than a number in the range of 0.0 to 100.0 inclusive then an
       error is thrown.
  (4)  If any Y argument to percentile(Y,P) evaluates to a value that
       is not NULL and is not numeric then an error is thrown.
  (5)  If any Y argument to percentile(Y,P) evaluates to plus or minus
       infinity then an error is thrown.  (SQLite always interprets NaN
       values as NULL.)
  (6)  Both Y and P in percentile(Y,P) can be arbitrary expressions,
       including CASE WHEN expressions.
  (7)  The percentile(Y,P) aggregate is able to handle inputs of at least
       one million (1,000,000) rows.
  (8)  If there are no non-NULL values for Y, then percentile(Y,P)
       returns NULL.
  (9)  If there is exactly one non-NULL value for Y, the percentile(Y,P)
       returns the one Y value.
 (10)  If there N non-NULL values of Y where N is two or more and
       the Y values are ordered from least to greatest and a graph is
       drawn from 0 to N-1 such that the height of the graph at J is
       the J-th Y value and such that straight lines are drawn between
       adjacent Y values, then the percentile(Y,P) function returns
       the height of the graph at P*(N-1)/100.
 (11)  The percentile(Y,P) function always returns either a floating
       point number or NULL.
 (12)  The percentile(Y,P) is implemented as a single C99 source-code
       file that compiles into a shared-library or DLL that can be loaded
       into SQLite using the sqlite3_load_extension() interface.

=item series 

This extension implements the generate_series() function
which gives similar results to the eponymous function in PostgreSQL.

Examples: