NAME

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

PROJECT STATUS

| OS | Build status | |:-------:|--------------:| | Linux | |

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