NAME

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

DESCRIPTION

This module provides a fetch interface for records returned from a database query. It uses DBI to connect to the databases and retrieve data. DBI and the DBD Drivers for the database have to be installed separately.

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.