Database Handle Attributes

sqlite_version

Returns the version of the SQLite library which DBD::SQLite2 is using, i.e, "2.8.15".

sqlite_encoding

Returns either "UTF-8" or "iso8859" to indicate how the SQLite library was compiled.

sqlite_handle_binary_nulls

Set this attribute to 1 to transparently handle binary nulls in quoted and returned data.

NOTE: This will cause all backslash characters ("\") to be doubled up in all columns regardless of whether or not they contain binary data or not. This may break your database if you use it from another application. This does not use the built in "sqlite_encode_binary" and "sqlite_decode_binary" functions, which may be considered a bug.

$dbh->func('last_insert_rowid')

$dbh->func( $name, $argc, $func_ref, "create_function" )

$name

The name of the function. This is the name of the function as it will be used from SQL.

$argc

The number of arguments taken by the function. If this number is -1, the function can take any number of arguments.

$func_ref

This should be a reference to the function's implementation.

For example, here is how to define a now() function which returns the current number of seconds since the epoch:

    $dbh->func( 'now', 0, sub { return time }, 'create_function' );

After this, it could be use from SQL as:

    INSERT INTO mytable ( now() );

$dbh->func( $name, $argc, $pkg, 'create_aggregate' )

$name

The name of the aggregate function, this is the name under which the function will be available from SQL.

$argc

This is an integer which tells the SQL parser how many arguments the function takes. If that number is -1, the function can take any number of arguments.

$pkg

This is the package which implements the aggregator interface.

The aggregator interface consists of defining three methods:

new()

This method will be called once to create an object which should be used to aggregate the rows in a particular group. The step() and finalize() methods will be called upon the reference return by the method.

step(@_)

This method will be called once for each rows in the aggregate.

finalize()

This method will be called once all rows in the aggregate were processed and it should return the aggregate function's result. When there is no rows in the aggregate, finalize() will be called right after new().

Here is a simple aggregate function which returns the variance (example adapted from pysqlite):

    package variance;

    sub new { bless [], shift; }

    sub step {
        my ( $self, $value ) = @_;

        push @$self, $value;
    }

    sub finalize {
        my $self = $_[0];

        my $n = @$self;

        # Variance is NULL unless there is more than one row
        return undef unless $n || $n == 1;

        my $mu = 0;
        foreach my $v ( @$self ) {
            $mu += $v;
        }
        $mu /= $n;

        my $sigma = 0;
        foreach my $v ( @$self ) {
            $sigma += ($x - $mu)**2;
        }
        $sigma = $sigma / ($n - 1);

        return $sigma;
    }

    $dbh->func( "variance", 1, 'variance', "create_aggregate" );

The aggregate function can then be used as:

    SELECT group_name, variance(score) FROM results
    GROUP BY group_name;