The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
OpenInteract-1.50
River stage zero No dependents
1 ++
/ OpenInteract::SQLInstall

NAME

OpenInteract::SQLInstall -- Dispatcher for installing various SQL data from packages to database

SYNOPSIS

 # Define a SQLInstaller for your package

 package OpenInteract::SQLInstall::MyPackage;

 use strict;
 use vars qw( %HANDLERS );
 %HANDLERS = (
   'create_structure' => { Sybase => \&structure_sybase,
                           Oracle => \&structure_oracle,
                           mysql  => \&structure_mysql },
   ...
 );

 sub structure_sybase { ...do stuff...}
 sub structure_oracle { ...do stuff...}
 sub structure_mysql  { ...do stuff...}

 # Use this class in a separate program
 use OpenInteract::SQLInstall;
 use OpenInteract::PackageRepository;
 use OpenInteract::Startup;
 use OpenInteract::DBI;

 my $C   = OpenInteract::Startup->create_config({
                base_config_file => "$WEBSITE_DIR/conf/base.conf"
           });
 my $dbh = eval { OpenInteract::DBI->connect( $C->{db_info} ) };
 die "Cannot open database handle: $@"  if ( $@ );

 my $repository = OpenInteract::PackageRepository->fetch(
                                   undef, { directory => $WEBSITE_DIR } );
 my $pkg_info = $repository->fetch_package_by_name( { name => 'my_package' } );
 OpenInteract::SQLInstall->require_package_installer( $pkg_info );
 my %args = ( package => $pkg_info, config => $C, db => $dbh );
 OpenInteract::SQLInstall->apply( { %args, action => 'create_structure' } );
 OpenInteract::SQLInstall->apply( { %args, action => 'install_data' } );
 OpenInteract::SQLInstall->apply( { %args, action => 'install_security' } );

DESCRIPTION

One of the difficulties with developing an application that can potentially work with so many different databases is that it needs to work with so many different databases. Many of the differences among databases are dealt with by the amazing DBI module, but enough remain to warrant some thought.

This module serves two audiences:

  1. The user of OpenInteract who wants to get packages, run a few commands and have them simply work.

  2. The developer of OpenInteract packages who wants to develop for as many databases as possible without too much of a hassle.

This module provides tools for both. The first group (users) does not need to concern itself with how this module works -- running the various oi_manage commands should be sufficient.

However, OpenInteract developers need a keen understanding of how things work. This whole endeavor is a work-in-progress -- things work, but there will certainly be new challenges brought on by the wide variety of applications for which OpenInteract can be used.

USER OVERVIEW

Every package has a module that has a handful of procedures specified in such a way that OpenInteract knows what to call and for which database. Generally, all you need to deal with is the wrapper provided by the oi_manage program. For instance:

 oi_manage install_sql --website_dir=/home/httpd/myOI --package=mypackage

This will install the SQL for the package 'mypackage' installed to your website. As long as you have specified your database properly in your conf/server.perl file, everything should flow smooth as silk.

DEVELOPER OVERVIEW

The SQL installation program of OpenInteract is a kind of mini framework -- you have the freedom to do anything you like in the handlers for your package. But OpenInteract provides a number of tools for you as well.

METHODS

Note that subclasses do not need to define any of these methods. They simply need to define a package variable %HANDLERS which describes the work done for particular actions and particular databases and the routines to accomplish this work.

require_package_installer( $package_object )

Given a package object, finds and does a require on the library used to install SQL for this package.

apply( \%params )

Performs a particular SQL action for a particular package.

Returns: 'ok' if no errors were returned, or the text of the error if an error occurred.

Parameters:

  • action ($)

    String with name of action to perform.

  • db (obj)

    DBI database handle

  • config (obj or \%)

    OpenInteract configuration object (or just a hashref), which should have the key 'db_info' with information about the database in it, and the key 'website_name' with the name of the website.

  • package (\%)

    Package object to perform action on

read_db_handlers( $driver_name )

Finds the handlers to execute for a particular database ($driver_name). If your handler does not implement a set of handlers for $driver_name, you can create a generic one (and hope that it works) using the key '_default_'.

Of course, your class may implement this however it needs. But the method implemented in the parent allows you to define a package variable like so:

 package MyClass::SQLInstall;

 use strict;
 use vars ( %HANDLERS );

 %HANDLERS = (
     create_structure => { that_db  => \&that_db_structure,
                           other_db => \&other_db_structure },
     install_data     => { '_default_' => \&generic_data },
     install_security => {},
 );

Returns: hashref with the keys as the actions that can be performed and the values as code references to apply for a database.

sql_class_to_website( $website_name, $text, [ $text, ... ] )

Does a simple substitution of 'OpenInteract' for the website on each string you pass to it.

Returns the strings with the substitution done in the same order.

Example:

 my @classes = sql_class_to_website( 'MyWebsite',
                                 qw( OpenInteract::News::Handler
                                     OpenInteract::News ) );
 # @classes is now: MyWebsite::News::Handler MyWebsite::News

sql_class_to_oi( $website_name, $text, [ $text, ... ] )

Does a simple substitution of your website name to 'OpenInteract' for each string you pass to it.

Returns the strings with the substitution done in the same order.

sql_modify_increment( $driver_name, $sql, [ $sql, ... ] )

Modify each SQL statement to use a particular database style for representing auto-incrementing values.

Returns the strings with the substitution done, in the same order.

process_data_file( \%params )

Processes the datafile described by \%params. This is a fairly hefty method, and its workings are described in "PROCESSING DATA FILES".

transform_data( \%action, \@data, \%params )

Take the first item from a data file (the action) and perform any indicated tasks necessary.

Currently, the tasks implemented by this parent class are:

  • transform_class_to_website (\@)

    Specify a list of fields that should have the value transformed to replace 'OpenInteract' with the website name

  • transform_class_to_oi (\@)

    Specify a list of fields that should have the value transformed to replace the current website namespace with 'OpenInteract'.

  • transform_default_to_id (\@)

    Specify a list of fields for which we need to use the value specified lookup as a lookup in the 'default_objects' server configuration key. This is often used when specifying security -- if you are using LDAP, then your group ID is soemething like 'site admin' rather than '3', so you want to ensure that the security is set appropriately.

  • transform_to_now (\@)

    Specify a list of fields for which we need to set the value to a date string representing right now.

read_perl_file( $filename )

Reads in the file $filename which describes a perl data structure. Returns the structure.

read_file( $filename )

Reads in the file $filename and returns a scalar with all the text of the file.

format_status( \%info, \@status )

PROCESSING DATA FILES

We need to be able to pass data from one database to another and be very flexible as to how we do it. The various data file formats have taken care of everything I could think of -- hopefully you will think up some more.

To begin, there are two elements to a data file. The first element tells the installer what type of data follows -- should we create objects from them? Should we just plug the values into a SQL statement and execute it against a particular table?

The second element is the actual data, which is in an order determined by the first element.

There are several different ways to process a data file. Both are described in detail below:

Object Processing

Object processing allows you to just specify the field order and the class, then let SPOPS do the dirty work. This is the preferred way of transferring data, but it is not always feasible. An example where it is not feasible include linking tables that SPOPS uses but does not model.

SQL Processing

SQL processing allows you to present elements of a SQL statement and plug in values as many times as necessary. This can be used most anywhere and for anything.

Object Processing

The first item in the list describes the class you want to use to create objects and the order the fields that follow are in. Here is a simple example of the data file used to install initial groups:

  $data_group = [ { spops_class => 'OpenInteract::Group',
                    field_order => [ qw/ group_id name / ] },
                  [ 1, 'admin' ],
                  [ 2, 'public' ],
                  [ 3, 'site admin' ],
  ];

Important note: when installing to a website, this module will substitute the name of the website for 'OpenInteract' in 'spops_class'. So in the above example the class for the group objects created in 'MyWebsite' will be MyWebsite::Group -- not OpenInteract::Group.

Here is a slightly abbreviated form of what steps would look like if they were done in code:

 my $website_name = 'MyWebsite';
 my $object_class = 'OpenInteract::Group';
 $object_class =~ s/OpenInteract/$website_name/;
 my %field_num = { group_id => 0, name => 1 };
 foreach my $row ( @{ $data_rows } ) {
   my $object = $object_class->new();
   $object->{group_id} = $row->[ $field_num{group_id} ];
   $object->{name}     = $row->[ $field_num{name} ];
   $object->save({ is_add => 1, skip_security => 1,
                   skip_log => 1, skip_cache => 1 });
 }

Easy!

You can also specify operations to perform on the data before they are saved with the object. The most common operation of this is in security data:

  $security = [
                { spops_class => 'OpenInteract::Security',
                  field_order => [ qw/ class object_id scope scope_id security_level / ],
                  transform_default_to_id    => [ 'scope_id' ],
                  transform_class_to_website => [ 'class'] },
                [ 'OpenInteract::Group', 1, 'w', 'world', 1 ],
                [ 'OpenInteract::Group', 2, 'w', 'world', 4 ],
                [ 'OpenInteract::Group', 2, 'g', 'site_admin_group', 8 ],
                [ 'OpenInteract::Group', 3, 'w', 'world', 4 ],
                [ 'OpenInteract::Group', 3, 'g', 'site_admin_group', 8 ],
                [ 'OpenInteract::Handler::Group', 0, 'w', 'world', 4 ],
                [ 'OpenInteract::Handler::Group', 0, 'g', 'site_admin_group', 8 ]
  ];

So these steps would look like:

 my $website_name = 'MyWebsite';
 my $object_class = 'OpenInteract::Security';
 $object_class =~ s/OpenInteract/$website_name/;
 my %field_num = { class => 0, object_id => 1, scope => 2,
                   scope_id => 3, security_level => 4 };
 my $defaults = $R->CONFIG->{default_objects};
 foreach my $row ( @{ $data_rows } ) {
   my $object = $object_class->new();
   $object->{class}     = $row->[ $field_num{class} ];
   $object->{object_id} = $row->[ $field_num{object_id} ];
   $object->{scope}     = $row->[ $field_num{scope} ];
   my $scope_id         = $row->[ $field_num{scope_id} ];
   $object->{scope_id}  = $defaults->{ $scope_id } || $scope_id;
   $object->{level}     = $row->[ $field_num{security_level} ];
   $object->{class} =~ s/OpenInteract/$website_name/;
   $object->save({ is_add   => 1, skip_security => 1,
                   skip_log => 1, skip_cache    => 1 });
 }

There are currently just a few behaviors you can set to transform the data before it gets saved (see transform_data() above), but the interface is there to do just about anything you can imagine.

SQL Processing

The actions performed when you just want to insert data into tables is similar to those performed when you are inserting objects. The only difference is that you need to specify a little more. Here is an example:

  $data_link = [ { sql_type    => 'insert',
                   sql_table   => 'sys_group_user',
                   field_order => [ qw/ group_id user_id / ] },
                 [ 1, 1 ]
  ];

So we specify the action ('insert'), the table to operate on ('sys_group_user'), the order of fields in the data rows ('field_order', just like with processing objects) and then list the data.

You are also able to specify the data types. Most of the time this should not be necessary: if the database driver (e.g., DBD::mysql) supports it, the SPOPS::SQLInterface file has routines to discover data types in a table and do the right thing with regards to quoting values.

However, if you do find it necessary you can use the following simple type -> DBI type mappings:

 'int'   -> DBI::SQL_INTEGER(),
 'num'   -> DBI::SQL_NUMERIC(),
 'float' -> DBI::SQL_FLOAT(),
 'char'  -> DBI::SQL_VARCHAR(),
 'date'  -> DBI::SQL_DATE(),

Here is a sample usage:

  $data_link = [ { sql_type => 'insert',
                   sql_table => 'sys_group_user',
                   field_order => [ qw/ group_id user_id link_date priority_level / ],
                   field_type => { group_id       => 'int',
                                   user_id        => 'int',
                                   link_date      => 'date',
                                   priority_level => 'char' },
                  },
                 [ 1, 1, '2000-02-14', 'high' ]
  ];

More work needs to be done on this so, for instance, you could implement cleanup routines like this:

  $cleanup = [ { sql_type => 'delete',
                 sql_table => 'sys_group_user',
                 where  => "group_id = ? and user_id = ?" },
                 [ 1, 1 ]
  ];

Or:

  $cleanup = [ { sql_type => 'delete',
                 sql_table => 'sys_security',
                 transform_class_to_website => [ 1, 2 ] },
                 where  => "class = ? OR class = ?" },
                 [ 'OpenInteract::Group', 'OpenInteract::Handler::Group' ]
  ];

BUGS

None known.

TO DO

Setup removals properly

See example under "SQL Processing", above.

Other means of abstraction

Using something like Alzabo (see http://alzabo.sourceforge.net/) to provide schema and data translation abilities would be sweet. However, Alzabo is a whole nother ball of wax on top of OpenInteract...

Dumping data for transfer

It would be nice if you could do something like:

 oi_manage dump_sql --website_dir=/home/httpd/myOI --package=mypkg

And get in your data/dump directory a series of files that can be read in by another OpenInteract website for installation. This is the pie in the sky -- developing something like this would be really cool.

And we can, but only for SPOPS objects. It is quite simple for us to read data from a flat file, build objects from the data and save them into a random database -- SPOPS was built for this!

However, structures are a problem with this. Data that are not held in objects are a problem. And dealing with dependencies is an even bigger problem.

Single-action process

Creating a script that allowed you to do:

 oi_sql_process --database=Sybase \
                --apply=create_structure < table.sql > sybase_table.sql

would be pretty nifty.

Use as object (and other refactoring)

Every SQL install process should create an object with information like:

 * repository
 * package
 * database driver

The object (a factory method) would create an object based on the database driver which is a pointer to the OpenInteract::SQLInstall database-specific subclass. This subclass which would have the specific information necessary to make the actions happen for a particular database.

The object would then:

 * initialize the SQLInstall class for the package (do a 'require') as
   part of the constructor

And then have available a generic 'apply' class which took a specific action:

 * run through the structures ( $install->apply( 'create_structure' ) )
 * run through the security   ( $install->apply( 'install_security' ) )
 * run through the data       ( $install->apply( 'install_data' ) )

Each of these should probably be in an 'Action' subclass or something, since this is getting fairly hefty...

SEE ALSO

OpenInteract::Package

DBI

COPYRIGHT

Copyright (c) 2001-2002 intes.net, inc.. All rights reserved.

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

AUTHORS

Chris Winters <chris@cwinters.com>

Christian Lemburg <lemburg@aixonix.de> provided the initial idea and helped steer the module away from potentially rocky shoals.