NAME

DBIx::Class::Helper::TableSample - Add support for tablesample clauses

VERSION

version v0.3.2

SYNOPSIS

In a resultset:

package MyApp::Schema::ResultSet::Wobbles;

use base qw/DBIx::Class::ResultSet/;

__PACKAGE__->load_components( qw/
    Helper::TableSample
/);

Using the resultset:

my $rs = $schema->resultset('Wobbles')->search_rs(
  undef,
  {
    columns     => [qw/ id name /],
    tablesample => {
      method   => 'system',
      fraction => 0.5,
    },
  }
);

This generates the SQL

SELECT me.id, me.name FROM table me TABLESAMPLE SYSTEM (0.5)

DESCRIPTION

This helper adds rudimentary support for tablesample queries to DBIx::Class resultsets.

The tablesample key supports the following options as a hash reference:

• fraction

  This is the percentage or fraction of the table to sample, between 0 and 100, or a numeric expression that returns such a value.

  (Some databases may restrict this to an integer.)

  The value is not checked by this helper, so you can use database-specific extensions, e.g. 1000 ROWS or 15 PERCENT.

  Scalar references are dereferenced, and expressions or database-specific extensions should be specified has scalar references, e.g.

  my $rs = $schema->resultset('Wobbles')->search_rs(
    undef,
    {
      columns     => [qw/ id name /],
      tablesample => {
        fraction => \ "1000 ROWS",
      },
    }
  );
  

• method

  By default, there is no sampling method, e.g. you can simply use:

  my $rs = $schema->resultset('Wobbles')->search_rs(
    undef,
    {
      columns     => [qw/ id name /],
      tablesample => 5,
    }
  );
  

  as an equivalent of

  my $rs = $schema->resultset('Wobbles')->search_rs(
    undef,
    {
      columns     => [qw/ id name /],
      tablesample => { fraction => 5 },
    }
  );
  

  to generate

  SELECT me.id FROM table me TABLESAMPLE (5)
  

  If your database supports or requires a sampling method, you can specify it, e.g. system or bernoulli.

  my $rs = $schema->resultset('Wobbles')->search_rs(
    undef,
    {
      columns     => [qw/ id name /],
      tablesample => {
         fraction => 5,
         method   => 'system',
      },
    }
  );
  

  will generate

  SELECT me.id FROM table me TABLESAMPLE SYSTEM (5)
  

  See your database documentation for the allowable methods. Note that some databases require it.

  Prior to version 0.3.0, this was called type. It is supported for backwards compatability.

• repeatable

  If this key is specified, then it will add a REPEATABLE clause, e.g.

  my $rs = $schema->resultset('Wobbles')->search_rs(
    undef,
    {
      columns     => [qw/ id name /],
      tablesample => {
        fraction   => 5,
        repeatable => 123456,
      },
    }
  );
  

  to generate

  SELECT me.id FROM table me TABLESAMPLE (5) REPEATABLE (123456)
  

  Scalar references are dereferenced, and expressions or database-specific extensions should be specified has scalar references.

KNOWN ISSUES

Resultsets with joins or inner queries are not supported.

Delete and update queries are not supported.

Oracle has a non-standard table sampling syntax, so is not yet supported.

Not all databases support table sampling, and those that do may have different restrictions. You should consult your database documentation.

SEE ALSO

DBIx::Class

SOURCE

The development version is on github at https://github.com/robrwo/-DBIX-Class-Helper-TableSample and may be cloned from git://github.com/robrwo/-DBIX-Class-Helper-TableSample.git

BUGS

Please report any bugs or feature requests on the bugtracker website https://github.com/robrwo/-DBIX-Class-Helper-TableSample/issues

When submitting a bug or request, please include a test-file or a patch to an existing test-file that illustrates the bug or desired feature.

AUTHOR

Robert Rothenberg rrwo@cpan.org

The initial development of this module was sponsored by Science Photo Library https://www.sciencephoto.com.

COPYRIGHT AND LICENSE

This software is Copyright (c) 2019-2022 by Robert Rothenberg.

This is free software, licensed under:

The Artistic License 2.0 (GPL Compatible)