/ 
OpenInteract-1.99_01
River stage zero No dependents
/ OpenInteract2::SQLInstall

NAME

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

SYNOPSIS

# PACKAGE AUTHORS
# Define a SQLInstaller for your package

package OpenInteract2::SQLInstall::MyPackage;

use strict;
use base qw( OpenInteract2::SQLInstall );

my %TABLES = (
   sybase  => [ 'myobj_sybase.sql' ],
   oracle  => [ 'myobj_oracle.sql', 'myobj_sequence.sql' ],
   default => [ 'myobj.sql' ],
);

# We only define one object in this package
sub get_structure_set {
    return 'myobj';
}

# Since we only have one set we can ignore it
sub get_structure_file {
    my ( $self, $set, $type ) = @_;
    return 'myobj_sybase.sql'                           if ( $type eq 'Sybase' );
    return [ 'myobj_oracle.sql', 'myobj_sequence.sql' ] if ( $type eq 'Oracle' );
    return 'myobj.sql';
}

# INSTALLER USERS
# Use this class in a separate program
use OpenInteract2::Context qw( CTX );
use OpenInteract2::SQLInstall;

my $package = CTX->repository->fetch_package( 'mypackage' );;
my $installer = OpenInteract2::SQLInstall->new_from_package( $package );

# Do one at a time
$installer->install_structure;
$installer->install_data;
$installer->install_security;

# ... or all at once
$installer->install_all;

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 oi2_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.

USERS: HOW TO MAKE IT HAPPEN

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 oi2_manage program. For instance:

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

This will install all of the structures, data and security objects necessary for the package 'mypackage' to function. You can also install the pieces individually:

oi2_manage install_sql_structure --website_dir=/home/httpd/myOI --package=mypackage
oi2_manage install_sql_data --website_dir=/home/httpd/myOI --package=mypackage
oi2_manage install_sql_security --website_dir=/home/httpd/myOI --package=mypackage

As long as you have specified your databsources properly in your conf/server.ini file and enabled any custom associations between the datasources and SPOPS objects, everything should flow smooth as silk.

DEVELOPERS: CODING

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.

Subclassing: Methods to override

First, the basics. Here's the scoop on what you can override:

init( \%params )

Called from new() just before returning the object. All items in \%params that are object fields have already been set in the object, the other entries remain untouched.

If there's a problem you should die with a useful error message.

Returns: nothing.

install_structure()

If you have needs that declaration cannot fill, you can install the structures yourself. You have access to the full OpenInteract2::Context object so you can get datasources, lookup SPOPS object information, etc. (See more in section on customization below.)

get_structure_set()

Returns a set of keys used for matching up structure files with datasources. (A structure file normally delineates a single table but can also describe other objects, like sequences, generators or even indices.) The return value is either a simple scalar or an arrayref. Each member must be:

'system'

For structures to be installed to the OI system database.

'datasource: NAME'

For structures to be installed to a particular datasource 'NAME'. This is useful for tables that can be configured for a particular datasource but aren't an SPOPS object. The method should lookup the proper datasource from the server configuration or some other resource.

spops-key

For structures to be installed in the datasource used by spops-key.

So if you have two objects defined in your package you might have something like:

sub get_structure_set {
    return [ 'objectA', 'objectB' ];
}

Where 'objectA' and 'objectB' are SPOPS keys.

And in get_structure_file() you may have:

sub get_structure_file {
    my ( $self, $set, $driver ) = @_;
    if ( $set eq 'objectA' ) {
        return [ 'objectA.sql', 'objectALookup.sql' ];
    }
    elsif ( $set eq 'objectB' ) {
        if ( $driver eq 'Oracle' ) {
            return [ 'objectB-oracle', 'objectB-sequence' ];
        }
        return 'objectB.sql';
    }
    else {
        oi_error "Set '$set' not defined by this package.";
    }
}

Note that you could also force the user to install all objects to the same database, which makes sense for tables that use JOINs or whatnot:

sub get_structure_set {
    return 'objectA';
}

# Now we don't care what the value of $set is...

sub get_structure_file {
    my ( $self, $set, $driver ) = @_;
    my @base = ( 'objectA.sql', 'objectALookup.sql' );
    if ( $driver eq 'Oracle' ) {
        return [ @base, 'objectB-oracle', 'objectB-sequence' ];
    }
    return [ @base, 'objectB.sql' ];
}

get_structure_file( $set_name, $driver_type )

Return an arrayref of filenames based on the given $set_name and $driver_type. This should include any tables and supporting objects, like sequences for PostgreSQL/Oracle or generators for FirebirdSQL/InterBase. See examples above.

install_data()

If you have needs that declaration cannot fill, you can install data yourself. You have access to the full OpenInteract2::Context object so you can get datasources, lookup SPOPS object information, etc. (See more in section on customization below.)

get_data_file()

Returns an arrayref of filenames with data to import. See discussion below on importing data for more information on what these files can contain.

install_security()

If you have needs that declaration cannot fill, you can install security objects yourself. You have access to the full OpenInteract2::Context object so you can get datasources, lookup SPOPS object information, etc. (See more in section on customization below.)

get_security_file()

Returns an arrayref of filenames with security data to import.

transform_data( $importer )

This is optional and called by the process behind install_data() and install_security(). By default OI will change fields marked under 'transform_default' and 'transform_now' as discussed in the data import documentation below. But if you have other install-time transformations you'd like to accomplish you can do them here.

The $importer is a SPOPS::Import object. You can get the field order and modify the data in-place:

my $install_time = time;
my $field_order = $importer->fields_as_hashref;
foreach my $data ( @{ $importer->data } ) {
    my $idx = $field_order->{myfield};
    $data->[ $idx ] = ( $install_time % 2 == 0 ) ? 'even' : 'odd';
}

So here's an example of a subclass that puts a number of the above items together:

package OpenInteract2::MyPackage::SQLInstall;

use strict;
use base qw( OpenInteract2::SQLInstall );
use OpenInteract2::Context qw( CTX );

# Lookup in the server configuration the name of the field to
# transform. (This is not actually there, just an example.)

sub init {
    my ( $self ) = @_;
    $self->{_my_transform_field} = CTX->server_config->{mypackage}{transform};
}

sub get_structure_set {
    return 'objectA';
}

# We don't care what the value of $set is since there's only one
# possible value

sub get_structure_file {
    my ( $self, $set, $driver ) = @_;
    my @base = ( 'objectA.sql', 'objectALookup.sql' );
    if ( $driver eq 'Oracle' ) {
        return [ @base, 'objectB-oracle', 'objectB-sequence' ];
    }
    return [ @base, 'objectB.sql' ];
}

sub transform_data {
    my ( $self, $importer ) = @_;
    my $install_time = time;
    my $field_order = $importer->fields_as_hashref;
    my $idx = $field_order->{ $self->{_my_transform_field} };
    return unless ( $idx );
    foreach my $data ( @{ $importer->data } ) {
        $data->[ $idx ] = ( $install_time % 2 == 0 ) ? 'even' : 'odd';
    }
    # Remember to call the main method!
    $self->SUPER::transform_data( $importer );
}

DEVELOPERS: IMPORTING DATA

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 an 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 = [ { import_type => 'object',
                  spops_class => 'OpenInteract2::Group',
                  field_order => [ qw/ group_id name / ] },
                [ 1, 'admin' ],
                [ 2, 'public' ],
                [ 3, 'site admin' ],
];

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

my $object_class = 'OpenInteract2::Group';
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 = [
              { import_type       => 'object',
                spops_class       => 'OpenInteract2::Security',
                field_order       => [ qw/ class object_id scope scope_id security_level / ],
                transform_default => [ 'scope_id' ] },
              [ 'OpenInteract2::Group',         1, 'w', 'world', 1 ],
              [ 'OpenInteract2::Group',         2, 'w', 'world', 4 ],
              [ 'OpenInteract2::Group',         2, 'g', 'site_admin_group', 8 ],
              [ 'OpenInteract2::Group',         3, 'w', 'world', 4 ],
              [ 'OpenInteract2::Group',         3, 'g', 'site_admin_group', 8 ],
              [ 'OpenInteract2::Action::Group', 0, 'w', 'world', 4 ],
              [ 'OpenInteract2::Action::Group', 0, 'g', 'site_admin_group', 8 ]
];

So these steps would look like:

my $object_class = 'OpenInteract2::Security';
my %field_num = { class => 0, object_id => 1, scope => 2,
                  scope_id => 3, security_level => 4 };
my $defaults = CTX->server_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->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 = [ { import_type => 'dbdata',
                 sql_table   => 'sys_group_user',
                 field_order => [ qw/ group_id user_id / ] },
               [ 1, 1 ]
];

So we specify the import type ('dbdata'), 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 = [ { import_type => 'dbdata',
                 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' ]
];

Additionally you can create Perl code to do this for you.

DEVELOPERS: CUSTOM BEHAVIOR

(Or: "The Declaration Is Not Enough")

As mentioned above, you can override any of the install_* methods for the ultimate flexibility. For instance, in the base_user package we create a 'superuser' object with a password generated at runtime.

You can do anything you like in the install_structure, install_data or install_security methods. You have the full OpenInteract2::Context available to you, including the configuration for the SPOPS objects, datasources, and full server configuration.

Responsibilities

When you implement custom behavior you have certain responsibilities. The contract with programs using this object says that every 'file' is associated with a status and, if it failed, an error message. (It may also be associated with a statement and datasource name.) Once the actions are completed the user can query this object to see what was done along with the status of the actions and any errors that were encountered.

The word file is in quotes because it should really be something more abstract like 'distinct action'. But because most of the time actions are file-based and everyone understands files, that's the way it is. But you're not constrained by this. So in the example above where we create the superuser object I could give that action a name of 'create administrator' and everyone would know what I meant.

For example, here's what such an implementation might look like:

sub install_data {
    my ( $self ) = @_;
    my $action_name = 'create administrator';
    my $server_config = CTX->server_config;
    my $email = $server_config->{mail}{admin_email};
    my $id    = $server_config->{default_objects}{superuser};
    my $user = CTX->lookup_object( 'user' )
                  ->new({ email      => $email,
                          login_name => 'superuser',
                          first_name => 'Super',
                          last_name  => 'User',
                          user_id    => $id });
    my $password = SPOPS::Utility->generate_random_code(8);
    if ( $server_config->{login}{crypt_password} ) {
        $user->{password} = SPOPS::Utility->crypt_it( $password );
    }
    eval { $user->save({ is_add        => 1,
                         skip_security => 1,
                         skip_cache    => 1,
                         skip_log      => 1 }) };
    if ( $@ ) {
        $log->error( "Failed to create superuser: $@" );
        $self->_set_state( $action_name,
                           undef,
                           "Failed to create admin user: $@",
                           undef );
    }
    else {
        my $msg_ok = join( '', 'Created administrator ok. ',
                               '**WRITE THIS PASSWORD DOWN!** ',
                               "Password: $password" );
        $self->_set_state( $action_name, 1, $msg_ok, undef );
    }

    # If we needed to process any data files in addition to the
    # above, we could do:
    # $self->SUPER::install_data();
}

Custom Methods to Use

process_data_file( @files )

Implemented by this class to process and install data from the given data files. If you're generating your own files it may prove useful.

_set_status( $file, 0|1 )

_set_error( $file, $error )

_set_statement( $file, $statement )

_set_datasource( $file, $datasource_name )

_set_state( $file, 0|1, $error, $statement )

BUGS

None known.

TO DO

Dumping data for transfer

It would be nice if you could do something like:

oi2_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.

SEE ALSO

SPOPS::Manual::ImportExport

SPOPS::Import

OpenInteract2::Package

DBI

COPYRIGHT

Copyright (c) 2002-2003 Chris Winters. 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>