/ 
DBIx-PgLink-0.01
River stage zero No dependents
/ DBIx::PgLink::Accessor::Table

NAME

DBIx::PgLink::Accessor::Table - accessor for table/view

DESCRIPTION

Enumerate tables/views in remote database.

Creates set of local database object that supports DML operations on remote table as if it is local table.

Enumerate existing accessors in local database.

List of local objects

  1. Composite type

    Represent row of remote table. Field types are mapped to PostgreSQL types.

  2. Set of functions

    PL/Perl function execute SELECT query on remote table and fetch data to PostgreSQL. One function has no parameters and fetch all data, others accept WHERE clause, optional parameter values list and optional parameter types list.

    Name of function suffixed with "$" to minimize name clash with remote function accessor.

  3. View

    Wraps function that fetch all data.

  4. Insert, update and delete rules on view

    Translate any modification of view to insert into shadow table.

  5. Shadow table

    Proxy object. Contains no real data.

  6. Trigger on shadow table

    Fire PL/Perl function on every row inserted to shadow table. That function transfer data modification to remote table.

For each object build method creates 4 queries - for SELECT, INSERT, UPDATE and DELETE. Queries are stored in dbix_pglink.queries table. You can manually change it or modify dbix_pglink.columns metadata table and call dbix_pglink.rebuild_accessors() function.

Columns marked with searchable attrribute in dbix_pglink.columns used in WHERE condition of UPDATE and DELETE statements to locate changed row. If remote table has primary key, it used as search key. All columns are supposed searchable if no primary key exists or driver can't recognize primary key.

Remote modification statements are executed in transaction, started in statement BEFORE-trigger and commited in statement AFTER-trigger.

METHODS

update_statistics
$accessor->update_statistics(
  execution_cost => 220
);

Counts number of rows for each corresponding remote table and assign row count and estimated execution cost to local PL/Perl function.

Requires local PostgreSQL server version 8.3 or later.

CAVEATS

  • All rows are fetched for any SELECT, UPDATE or DELETE operation

    Currently there is no way to supply dynamic condition to view/function. To limit number of fetched rows you can manually add static condition to function call in view.

    This is a major flaw both in dbi_link project and DBIx::PgLink.

    Do not issue SELECT, UPDATE, DELETE on large tables (i.e. more than 10_000 rows on fast network)

    For SELECT use accessor function with WHERE clause as argument, for UPDATE and DELETE use dbix_pglink.remote_do function.

    INSERT query is relatively fast (but ~2x-times slower than plain DBI).

  • Remote transaction stalls, if error occured at local database

    You need explicitly call SELECT dbix_pglink.rollback() at exception handler in your application code.

SEE ALSO

DBIx::PgLink::Accessor::BaseAccessor

AUTHOR

Alexey Sharafutdinov <alexey.s.v.br@gmail.com>

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.