RDBTools

Provides for pythonscript accessible functions to perform set operations on zsql method results.

Operations provided

• union_results
• join_results
• difference_results

The algorithms are based off Aaron Watters kjbuckets package and his early 1995 news posting to comp.lang.python.

Example Use Case

You have correlatable data in multiple dbs. Lets say employee contact info is in postgres, and payment info is in oracle. You want to aggregate this info into a single result set for a report display. a contrived example but a useful illustration.

contact table:

      create table corp_employee_contacts (
         employee_id   integer unique not null,
         email         varchar(255),
         first_name    varchar(30),
         last_name     varchar(30),
         middle_name   varchar(30)
      );

payment table:

      create table corp_employee_accounts (
         employee_id   integer unique not null,
         payment_period integer not null,
         payment_amount numeric default 0 not null
      )

zsql method: getEmployeeContacts:

      select * from corp_employee_contacts

zsql method: getEmployeeAccounts:

      select employee_id, sum(payment_amount) as total_pay
      from corp_employee_accounts
      group by employee_id

python script getEmployeeTotalPay:

      from Product.RDBTools import join_results

      contacts = context.getEmployeeContacts()
      accounts = context.getEmployeeAccounts()

      return join_results(accounts, contacts)

the resultant result set contains properly merged information from both results

Advanced Usage Note

Acquisition Contexts

ZSQL Results sets are bound to a context, the set operations exposed here preserve the context of the first result set argument.

Brains

brains are not preserved.

Speed

additional speed can be had by using installing the kjbuckets extension module, if its not installed the python implementation found in zope will be used.

Cross Products

if no common columns are found a cross product is returned.

Author

kapil thangavelu

License

X11, see LICENSE.txt for more details