NAME

Spreadsheet::Compare::Reader::DB - Database Adapter for Spreadsheet::Compare

DESCRIPTION

This module provides an init/fetch interface for records returned from a database query

EXAMPLE

    ---
    - title: __GLOBAL__
      type: DB
      dsns :
        - dsn: 'dbi:SQLite:dbname=t/left/db.sqlite'
        - dsn: 'dbi:SQLite:dbname=t/right/db.sqlite'
    #=============================================
    - title     : default config
      sql :
        - select * from table01
      identity: '[ROW_ID]'
    #=============================================
    - title     : construct id upper case column names
      sql :
        - select
            *,
            type || color as 'id'
          from table02
      identity: '[ID]'
      column_case: uc

ATTRIBUTES

Spreadsheet::Compare::Reader::DB implements the following attributes.

column_case

  possible values: <lc|uc|undef>
  default: undef

The DBI method for converting header name case. Default is using the header as is. Use 'uc' to use upper case header names and 'lc' for lower case.

dbh

(readonly) returns the current DBI database handle.

dsns

  possible values: <list of one or two hashes>
  default: []

Example:

  dsns:
    - dsn: 'dbi:SQLite:dbname=./left/db.sqlite'
    - dsn: 'dbi:SQLite:dbname=./right/db.sqlite'

A list of one or two hashes defining a database connection. If only one definition is used, the comparison will be run on the same database and you will need two different sql statements for the "sql" option.

An entry has to be a hash with the keys 'dsn', 'usr' and 'pwd'. Only 'dsn' is mandatory. The dsn can be any valid Perl DBI DSN.

has_header

(readonly) always true. The column names from the SQL statements are used.

sql

Example:

  sql :
    - select * from left_table order by id
    - select * from right_table order by id

A list of one or two sql statements extracting the data to be compared. If only one statement is issued it will be used for both sides of the comparison. In this case two different "dsns" should be used.

It is advisable to construct an identity column in the statement and use this for "identity" in Spreadsheet::Compare::Reader. This is faster than using an identity consisting of multiple columns.

For very large data sets, memory consumption can be limited by sorting the statement results by that column with an 'order by' directive, setting the option "is_sorted" ("is_sorted" in Spreadsheet::Compare::Single) to a true value and use "fetch_size" ("fetch:_size" in Spreadsheet::Compare::Single) to limit the number of records that will be compared in one batch. Also see ("MEMORY USAGE" in Spreadsheet::Compare).

METHODS

Spreadsheet::Compare::Reader::DB inherits or overwrites all methods from Spreadsheet::Compare::Reader.