Randy Stauner > DBIx-TableLoader-1.003 > DBIx::TableLoader

Download:
DBIx-TableLoader-1.003.tar.gz

Dependencies

Annotate this POD

Website

View/Report Bugs
Module Version: 1.003   Source   Latest Release: DBIx-TableLoader-1.100

NAME ^

DBIx::TableLoader - Easily load a database table from a data set

VERSION ^

version 1.003

SYNOPSIS ^

  my $dbh = DBI->connect(@connection_args);

  DBIx::TableLoader->new(dbh => $dbh, data => $data)->load();

  # interact with new database table full of data in $dbh

DESCRIPTION ^

This module tries to provide a fast and simple (but very configurable) interface for taking a set of data and loading it into a database table.

Common uses would be to take data from a file (like a CSV) and load it into a SQLite table. (For that specific case see DBIx::TableLoader::CSV.)

In most cases simply calling load() is sufficient, but all methods are documented below for completeness.

METHODS ^

new

Create a new instance. Accepts a hash or hashref of options.

This module is very configurable but tries to use good defaults in the hopes that you won't need to configure too much in most cases.

Most likely needed options:

See "OPTIONS" for the full list.

base_defaults

Returns a hashref of the options defined by the base class and their default values.

defaults

Returns a hashref of additional options defined by a subclass.

columns

  my $columns = $loader->columns;
  # [ ['column1', 'data type'], ['column two', 'data type'] ]

Returns an arrayref of the columns. Each element is an arrayref of column name and column data type.

column_names

  my $column_names = $loader->column_names;
  # ['column1', 'column two']

Returns an arrayref of the column names.

create

Executes a CREATE TABLE SQL statement on the database handle.

create_prefix

Generates the opening of the CREATE TABLE statement (everything before the column specifications).

Defaults to "CREATE $table_type TABLE $quoted_name (".

create_sql

Generates the SQL for the CREATE TABLE statement by concatenating "create_prefix", the column definitions, and "create_suffix".

Can be overridden in the constructor.

create_suffix

Generates the closing of the CREATE TABLE statement (everything after the column specifications).

Defaults to ")".

default_name

Returns the default (base) name for the table.

This is mostly for subclasses where a useful table name can be determined from the input (like a filename). In this module it defaults to 'data'.

This gets concatenated together with "name_prefix" and "name_suffix" in "name".

default_column_type

Columns that have not been given an explicit data type will be defined using the default_column_type.

You can pass a value explicitly to the constructor, or it will try to determine an appropriate (string) type based on the database driver (using "default_sql_data_type").

If all else fails it will default to text (which works for SQLite, PostgreSQL, MySQL, and some others).

default_sql_data_type

Passed to "type_info" in DBI to query the database driver for an appropriate default column type.

Defaults to DBI::SQL_LONGVARCHAR.

determine_column_types

This method goes through the columns and converts any scalar column name to an arrayref of column name and default_column_type. It modifies itself and returns nothing. It is called automatically from the constructor.

drop

Execute the DROP TABLE statement on the database handle.

drop_prefix

Returns the portion of the SQL statement before the table name.

Defaults to DROP TABLE.

drop_sql

Generates the SQL for the DROP TABLE statement by concatenating "drop_prefix", "quoted_name", and "drop_suffix".

Alternatively drop_sql can be set in the constructor if you need something more complex.

drop_suffix

Returns the portion of the SQL statement after the table name.

Nothing by default.

get_raw_row

Subclasses will override this method according to the input data format.

This is called from "get_row" to retrieve the next row of raw data.

It should return undef when there are no more rows.

get_row

  my $row = $loader->get_row();

Returns a single row of data at a time (as an arrayref). This method will be called repeatedly until it returns undef. The returned arrayref will be flattened and passed to "execute" in DBI.

handle_invalid_row

This is called from "get_row" when a row is determined to be invalid (when "validate_row" throws an error).

If handle_invalid_row was not specified in the constructor this method is a no-op: the original row will be returned (and eventually passed to "execute" in DBI).

Possible values for the handle_invalid_row option:

insert_sql

Generate the INSERT SQL statement that will be passed to "prepare" in DBI.

insert_all

Execute an INSERT statement on the database handle for each row of data. It will call "prepare" in DBI using "insert_sql" and then call "execute" in DBI once for each row returned by "get_row".

load

  my $number_of_rows = $loader->load();

Load data into database table. This is a wrapper that does the most commonly needed things in a single method call. If the transaction setting is true (default) the actions will be wrapped in a transaction.

Returns the number of rows inserted.

name

Returns the full table name (concatenation of name_prefix, name, and name_suffix).

prepare_data

This method is called from "new" after the object is blessed (obviously). Any preparation work specific to the type of data should be done here.

This is mostly a hook for subclasses and does very little in this module.

quoted_name

Returns the full, quoted table name. Passes catalog, schema, and name attributes to "quote_identifier" in DBI.

quoted_column_names

  my $quoted_names = $loader->quoted_column_names();
  # ['"column1"', '"column two"']

Returns an arrayref of column names quoted by the database driver.

validate_row

Called from "get_row" to check that the provided row is valid.

It may die for any error which will be caught in "get_row" and the error will be passed to "handle_invalid_row".

The return value works like that of "handle_invalid_row": On success, the valid row (possibly modified) should be returned. If a false value is returned "get_row" will attempt to get another row.

Currently this only checks that the number of fields in the row matches the number of columns expected, however other checks may be added in the future. Subclasses can overwrite this to define their own validations (though calling the original (superclass method) is recommended).

OPTIONS ^

This module is very [excessively] configurable. In most cases the default values will be sufficient, but you should be able to customize the object to fit your needs.

Frequently Used Options:

Less common options that are available when you desire extra tweaking power:

Options that will seldom be necessary but are available for completeness and/or consistency:

SUBCLASSING ^

This module was designed to be subclassed for use with specific data input formats.

DBIx::TableLoader::CSV is a prime example. It is the entire reason this base module was designed.

Subclasses will likely want to override the following methods:

Be sure to check out the code for DBIx::TableLoader::CSV. Also see a very simple example in t/subclass.t.

RATIONALE ^

It seemed frequent that I would find a data set that was difficult to view/analyze (CSV, log file, etc.) and would prefer to load it into a database for its powerful, familiar processing abilities.

I once chose to use MySQL because its built in LOAD DATA command read the malformed CSV I was given and the .import command in SQLite did not.

I wrote this module so that I'd never have to make such a choice again. I wanted to be able to use the power of Text::CSV to make sure I could take any CSV I ever got and load it into SQLite easily.

I tried to make this module a base class to be able to handle various formats.

TODO ^

This is more of a list of ideas than features that are planned.

SEE ALSO ^

SUPPORT ^

Perldoc

You can find documentation for this module with the perldoc command.

  perldoc DBIx::TableLoader

Websites

The following websites have more information about this module, and may be of help to you. As always, in addition to those websites please use your favorite search engine to discover more resources.

Bugs / Feature Requests

Please report any bugs or feature requests by email to bug-dbix-tableloader at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-TableLoader. You will be automatically notified of any progress on the request by the system.

Source Code

https://github.com/rwstauner/DBIx-TableLoader

  git clone https://github.com/rwstauner/DBIx-TableLoader.git

AUTHOR ^

Randy Stauner <rwstauner@cpan.org>

COPYRIGHT AND LICENSE ^

This software is copyright (c) 2011 by Randy Stauner.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

syntax highlighting: