[Openinteract-commits] CVS: OpenInteract2/lib/OpenInteract2/Manage/Package CreatePackageFromTable.pm,1.1,1.2

[Chris W. <la...@us...>]

Update of /cvsroot/openinteract/OpenInteract2/lib/OpenInteract2/Manage/Package
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv966

Modified Files:
	CreatePackageFromTable.pm 
Log Message:
OIN-129: make updates to use mysql as a table source (needed to redo how we get metadata...)


Index: CreatePackageFromTable.pm
===================================================================
RCS file: /cvsroot/openinteract/OpenInteract2/lib/OpenInteract2/Manage/Package/CreatePackageFromTable.pm,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** CreatePackageFromTable.pm	17 Feb 2005 05:18:53 -0000	1.1
--- CreatePackageFromTable.pm	18 Feb 2005 03:28:08 -0000	1.2
***************
*** 57,61 ****
  }
  
! my %VALID_DRIVERS = map { $_ => 1 } qw( Pg mysql SQLite );
  
  sub validate_param {
--- 57,61 ----
  }
  
! my %VALID_DRIVERS = map { $_ => 1 } qw( Pg mysql );
  
  sub validate_param {
***************
*** 75,79 ****
          }
          unless ( $VALID_DRIVERS{ $driver } ) {
!             return "I cannot use '$driver' to connect; I'm restricted to: " .
                     join( ', ', sort keys %VALID_DRIVERS );
          }
--- 75,79 ----
          }
          unless ( $VALID_DRIVERS{ $driver } ) {
!             return "I cannot use '$driver' database; I'm restricted to: " .
                     join( ', ', sort keys %VALID_DRIVERS );
          }
***************
*** 83,114 ****
  }
  
- my %TEXT = map { $_ => 1 } (
-     SQL_WVARCHAR, SQL_WCHAR, SQL_CHAR, SQL_VARCHAR,
- );
- 
- my %DATE = map { $_ => 1 } (
-     SQL_DATE, SQL_TYPE_DATE,
- );
- 
- my %DATETIME = map { $_ => 1 } (
-     SQL_DATETIME, SQL_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP_WITH_TIMEZONE,
- );
- 
- my %NUMBER = map { $_ => 1 } (
-     SQL_TINYINT, SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE,
- );
- 
- my %BOOLEAN = map { $_ => 1 } (
-     SQL_BIT, SQL_BOOLEAN,
- );
- 
  sub run_task {
      my ( $self ) = @_;
      $log ||= get_logger( LOG_OI );
  
!     my $dsn  = $self->param( 'dsn' );
!     my $user = $self->param( 'username' );
!     my $pass = $self->param( 'password' );
!     my $table = $self->param( 'table' );
  
      my $dbh = DBI->connect( $dsn, $user, $pass )
--- 83,95 ----
  }
  
  sub run_task {
      my ( $self ) = @_;
      $log ||= get_logger( LOG_OI );
  
!     my $dsn        = $self->param( 'dsn' );
!     my ( $driver ) = $dsn =~ /^dbi:([^:]+):.*$/i;
!     my $user       = $self->param( 'username' );
!     my $pass       = $self->param( 'password' );
!     my $table      = $self->param( 'table' );
  
      my $dbh = DBI->connect( $dsn, $user, $pass )
***************
*** 118,125 ****
      my $sql = "SELECT * FROM $table WHERE 1 = 0";
  
!     my ( $sth );
      eval {
!         $sth = $dbh->prepare( $sql );
!         $sth->execute();
      };
      if ( $@ ) {
--- 99,106 ----
      my $sql = "SELECT * FROM $table WHERE 1 = 0";
  
!     my ( $table_h );
      eval {
!         $table_h = $dbh->prepare( $sql );
!         $table_h->execute();
      };
      if ( $@ ) {
***************
*** 128,137 ****
      }
  
!     my @fields = @{ $sth->{NAME} };
!     my @types  = @{ $sth->{TYPE} };
  
!     $sth->finish;
  
!     my @keys = eval { $dbh->primary_key( undef, undef, $table ) };
      if ( $@ ) {
          oi_error "Cannot retrieve primary key info from '$table': $@";
--- 109,167 ----
      }
  
!     my @fields = @{ $table_h->{NAME} };
!     my $num_fields = scalar @fields;
  
!     my $key_field = $self->_get_key_field( $dbh, $table_h, $driver, $table, @fields );
!     my @field_info = $self->_get_table_info( $dbh, $driver, $table, @fields );
  
!     my ( $name_field );
!     for ( @field_info ) {
!         # make a best-guess...
!         my $name = $_->{name};
!         if ( $name =~ /(title|name)/ ) {
!             $name_field ||= $name;
!         }
!         if ( $name eq $key_field ) {
!             $_->{is_key} = 1;
!         }
!     }
!     $name_field ||= $key_field;
! 
! 
!     my $package_name = $self->param( 'package' )->[0];
!     my $package = OpenInteract2::Package->create_skeleton({
!         name       => $package_name,
!         brick_name => 'package_from_table',
!         brick_vars => {
!             key_field    => $key_field,
!             name_field   => $name_field,
!             fields       => \@field_info,
!             field_titles => [ map { $_->{display} } @field_info ],
!             field_names  => [ map { $_->{name} } @field_info ],
!             table        => $table,
!         },
!     });
!     my $msg = sprintf( 'Package %s created ok in %s',
!                        $package->name, $package->directory );
!     $self->_ok( "create package $package_name", $msg );
!     return;
! }
! 
! sub _get_key_field {
!     my ( $self, $dbh, $sth, $driver, $table, @fields ) = @_;
!     my @keys = ();
!     eval {
!         if ( $driver eq 'mysql' ) {
!             my $key_idx = $sth->{mysql_is_pri_key};
!             for ( 0 .. ( scalar @fields - 1 ) ) {
!                 if ( $key_idx->[ $_ ] ) {
!                     push @keys, $fields[ $_ ];
!                 }
!             }
!         }
!         else {
!             @keys = $dbh->primary_key( undef, undef, $table );
!         }
!     };
      if ( $@ ) {
          oi_error "Cannot retrieve primary key info from '$table': $@";
***************
*** 142,171 ****
                   "the keys [", join( ', ', @keys ), "].";
      }
!     my $key_field = $keys[0];
  
!     my ( $name_field );
  
      my @field_info = ();
!     my $num_fields = scalar @fields - 1;
!     foreach my $idx ( 0 .. $num_fields ) {
!         my $field = $fields[ $idx ];
!         # make a best-guess...
!         if ( $field =~ /(title|name)/ ) {
!             $name_field ||= $field;
          }
!         my $type  = $types[ $idx ];
!         $sth      = $dbh->column_info( undef, undef, $table, $field );
!         my $row   = $sth->fetchrow_hashref;
!         my $is_boolean = $self->_is_boolean( $row );
          my $is_number  = ( $is_boolean ) ? 0 : $NUMBER{ $type };
          $log->info( "Field '$field' info: ",
                      "[Type: $type] ",
!                     "[Size: $row->{COLUMN_SIZE}] ",
!                     "[Nullable? $row->{NULLABLE}] ",
!                     "[Default: $row->{COLUMN_DEF}] ",
                      "[is_text: $TEXT{ $type }] ",
                      "[is_date: $DATE{ $type }] ",
                      "[is_datetime: $DATETIME{ $type }] ",
!                     "[is_number: $NUMBER{ $type }] ",
                      "[is_boolean: $is_boolean] " );
  
--- 172,234 ----
                   "the keys [", join( ', ', @keys ), "].";
      }
!     unless ( $keys[0] ) {
!         oi_error "Sorry, no primary key seems to be available table '$table'";
!     }
!     return $keys[0];
! }
! 
  
! 
! my %TEXT = map { $_ => 1 } (
!     SQL_WVARCHAR, SQL_WCHAR, SQL_CHAR, SQL_VARCHAR,
! );
! 
! my %DATE = map { $_ => 1 } (
!     SQL_DATE, SQL_TYPE_DATE,
! );
! 
! my %DATETIME = map { $_ => 1 } (
!     SQL_DATETIME, SQL_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP_WITH_TIMEZONE,
! );
! 
! my %NUMBER = map { $_ => 1 } (
!     SQL_TINYINT, SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE,
! );
! 
! my %BOOLEAN = map { $_ => 1 } (
!     SQL_BIT, SQL_BOOLEAN,
! );
! 
! 
! 
! sub _get_table_info {
!     my ( $self, $dbh, $driver, $table, @fields ) = @_;
! 
!     my $sth = $dbh->column_info( undef, undef, $table, '%' );
!     my %by_field = ();
!     while ( my $row = $sth->fetchrow_hashref ) {
!         $by_field{ $row->{COLUMN_NAME} } = { %{ $row } };
!     }
  
      my @field_info = ();
!     foreach my $field ( @fields ) {
!         my $metadata = $by_field{ $field };
!         unless ( $metadata ) {
!             oi_error "Field name mismatch -- got field '$field' from ",
!                      "'NAME' attribute of statement handle but it was ",
!                      "not found when getting data using 'column_info()'.";
          }
!         my $type = $metadata->{DATA_TYPE};
!         my $is_boolean = $self->_is_boolean( $driver, $metadata );
          my $is_number  = ( $is_boolean ) ? 0 : $NUMBER{ $type };
          $log->info( "Field '$field' info: ",
                      "[Type: $type] ",
!                     "[Size: $metadata->{COLUMN_SIZE}] ",
!                     "[Nullable? $metadata->{NULLABLE}] ",
!                     "[Default: $metadata->{COLUMN_DEF}] ",
                      "[is_text: $TEXT{ $type }] ",
                      "[is_date: $DATE{ $type }] ",
                      "[is_datetime: $DATETIME{ $type }] ",
!                     "[is_number: $is_number] ",
                      "[is_boolean: $is_boolean] " );
  
***************
*** 173,177 ****
              name        => $field,
              display     => $self->_to_display_name( $field ),
-             is_key      => ( $field eq $key_field ),
              is_text     => $TEXT{ $type },
              is_date     => $DATE{ $type },
--- 236,239 ----
***************
*** 179,217 ****
              is_number   => $is_number,
              is_boolean  => $is_boolean,
-             size        => $row->{COLUMN_SIZE},
-             nullable    => $row->{NULLABLE},
-             default     => $row->{COLUMN_DEF},
              sql_type    => $type,
          };
      }
! 
!     $name_field ||= $key_field;
! 
!     my $package_name = $self->param( 'package' )->[0];
!     my $package = OpenInteract2::Package->create_skeleton({
!         name       => $package_name,
!         brick_name => 'package_from_table',
!         brick_vars => {
!             key_field    => $key_field,
!             name_field   => $name_field,
!             fields       => \@field_info,
!             field_titles => [ map { $_->{display} } @field_info ],
!             field_names  => [ map { $_->{name} } @field_info ],
!             table        => $table,
!         },
!     });
!     my $msg = sprintf( 'Package %s created ok in %s',
!                        $package->name, $package->directory );
!     $self->_ok( "create package $package_name", $msg );
!     return;
  }
  
  sub _is_boolean {
!     my ( $self, $metadata ) = @_;
!     my $type = $metadata->{DATA_TYPE};
!     return 1 if ( $BOOLEAN{ $type } );
      my $type_name = $metadata->{TYPE_NAME};
!     return 1 if ( $type_name =~ /^(boolean|bit)$/i );
!     return 0;
  }
  
--- 241,268 ----
              is_number   => $is_number,
              is_boolean  => $is_boolean,
              sql_type    => $type,
+             db_type     => $metadata->{TYPE_NAME},
+             size        => $metadata->{COLUMN_SIZE},
+             nullable    => $metadata->{NULLABLE},
+             default     => $metadata->{COLUMN_DEF},
          };
      }
!     return @field_info;
  }
  
  sub _is_boolean {
!     my ( $self, $driver, $metadata ) = @_;
      my $type_name = $metadata->{TYPE_NAME};
!     if ( $driver eq 'mysql' ) {
!         return 0 unless ( 'tinyint' eq lc $type_name );
!         return 0 unless ( $metadata->{COLUMN_SIZE} == 1 );
!         return 1;
!     }
!     else {
!         my $type = $metadata->{DATA_TYPE};
!         return 1 if ( $BOOLEAN{ $type } );
!         return 1 if ( $type_name =~ /^(boolean|bit)$/i );
!         return 0;
!     }
  }