NAME

SQL::Abstract::Prefetch - implement "prefetch" for DBI RDBMS

SYNOPSIS

  my $queryspec = {
    table => 'blog',
    fields => [
      'html',
      'id',
      'is_published',
      'markdown',
      'slug',
      'title',
      'user_id',
    ],
    keys => [ 'id' ],
    multi => {
      comments => {
        table => 'comment',
        fields => [ 'blog_id', 'html', 'id', 'markdown', 'user_id' ],
        keys => [ 'id' ],
      },
    },
    single => {
      user => {
        table => 'user',
        fields => [ 'access', 'age', 'email', 'id', 'password', 'username' ],
        keys => [ 'id' ],
      },
    },
  };
  my $abstract = SQL::Abstract::Pg->new( name_sep => '.', quote_char => '"' );
  my $dbh = DBI->connect( "dbi:SQLite:dbname=filename.db", '', '' );
  my $prefetch = SQL::Abstract::Prefetch->new(
    abstract => $abstract,
    dbhgetter => sub { $dbh },
    dbcatalog => undef, # for SQLite
    dbschema => undef,
    filter_table => sub { $_[0] !~ /^sqlite_/ },
  );
  my ( $sql, @bind ) = $prefetch->select_from_queryspec(
    $queryspec,
    { id => $items{blog}[0]{id} },
  );
  my ( $extractspec ) = $prefetch->extractspec_from_queryspec( $queryspec );
  my $sth = $dbh->prepare( $sql );
  $sth->execute( @bind );
  my ( $got ) = $prefetch->extract_from_query( $extractspec, $sth );

DESCRIPTION

This class implements "prefetch" in the style of DBIx::Class. Stages of operation:

• Generate a "query spec" that describes what you want back from the database - which fields from which tables, and what relations to join.

• Generate SQL (and bind parameters) from that "query spec".

• Pass the SQL and parameters to a DBI $dbh to prepare and execute.

• Pass the $sth when ready (this allows for asynchronous operation) to the extractor method to turn the returned rows into the hash-refs represented, including array-ref values for any "has many" relationships.

ATTRIBUTES

abstract

Currently, must be a SQL::Abstract::Pg object.

dbhgetter

A code-ref that returns a DBI $dbh.

dbcatalog

The DBI "catalog" argument for e.g. "column_info" in DBI.

dbschema

The DBI "schema" argument for e.g. "column_info" in DBI.

filter_table

Coderef called with a table name, returns a boolean of true to keep, false to discard - typically for a system table.

multi_namer

Coderef called with a table name, returns a suitable name for the relation to that table. Defaults to "to_PL" in Lingua::EN::Inflect::Number.

dbspec

By default, will be calculated from the supplied $dbh, using the supplied dbhgetter, dbcatalog, dbschema, filter_table, and multi_namer. May however be supplied, in which case those other attributes are not needed.

A "database spec"; a hash-ref mapping tables to maps of the relation-name (a string) to a further hash-ref with keys:

type

either single or multi

fromkey

the column name in the "from" table

fromtable

the name of the "from" table

tokey

the column name in the "to" table

totable

the name of the "to" table

The relation-name for "multi" will be calculated using the multi_namer on the remote table name.

METHODS

select_from_queryspec

Parameters:

• a "query spec"; a hash-ref with these keys:

  table

  keys

  array-ref of fields that are primary keys on this table

  fields

  array-ref of fields that are primitive types to show in result, including PKs if wanted. If not wanted, the joins still function.

  single

  hash-ref mapping relation-names to "query specs" - a recursive data structure; the relation is "has one"

  multi

  hash-ref mapping relation-names to "relate specs" as above; the relation is "has many"

• an SQL::Abstract "where" specification

• an SQL::Abstract "options" specification, including order_by, limit, and offset

Returns the generated SQL, then a list of parameters to bind.

extractspec_from_queryspec

Parameters: a "query spec" as above.

Returns an opaque "extract spec": data to be used by "extract_from_query" to interpret results generated from the "select_from_queryspec" query.

extract_from_query

Parameters: an opaque "extract spec" created by "extractspec_from_queryspec", and a DBI $sth.

Returns a list of hash-refs of items as reconstructed according to the spec.

SEE ALSO

Yancy::Backend, DBI, DBIx::Class

cpanm SQL::Abstract::Prefetch

perl -MCPAN -e shell
install SQL::Abstract::Prefetch