Re: adding users to jdbc repository

Kenny Smith Fri, 20 Jun 2003 10:42:05 -0700

I certainly can't say that my solution is the best, but it works for me. I'm running a mod_perl app (I tried doing it in Java, but it just took too darn long) and I've written a couple of perl modules that encapsulate the creation/maintenance/deletion of POP accounts. I attached two Perl Modules to this message, they aren't usable out of the box for you, but maybe it can serve as an example.

For what Noel is talking about below, refer to the _encodePassword() subroutine in POP.pm.

Kenny Smith

P.S. BaseObject.pm is my database abstraction module and POP.pm extends from it so that I don't have to write DBI code over and over.

Noel J. Bergman wrote:

- Use Java code to drive the James admin interface.
- Use JDBC to simple insert new users into the database database table.

Or do what I do: manipulate the database directly using SQL.  DigestUtil
runs standalone, so you can use it in a script to encode passwords.

#
# $Id: BaseObject.pm,v 1.1.1.1 2003/05/25 17:18:34 journals Exp $
#


package My::James::BaseObject ;

use strict ;
use lib '/usr/www/journalscape/lib' ;
use My::James ;

sub delete
{
        my ( $self ) = shift() ;
        my ( $sql , $dbh , $sth , $rc , $class ) ;

        $class = ref( $self ) ;

        $sql = "DELETE FROM $self->{'_table_name'} WHERE $self->{'_pk_field_name'}=?" ;
        $dbh = My::James->getDBH() ;
        $rc = $sth = $dbh->prepare( $sql ) ;
        if ( ! $rc ) {
                $rc = undef() ;
                $self->setErrorMessage( "Unable to prepare $class->delete(): 
".$dbh->errstr() ) ;
        }
        else {
                $rc = $sth->execute( $self->{$self->{'_pk_field_name'}} ) ;
                if ( ! $rc ) {
                        $rc = undef() ;
                        $self->setErrorMessage( "Unable to execute $class->delete(): 
".$sth->errstr() ) ;
                }
                else {
                        $rc = 1 ;
                }
        }
        return( $rc ) ;
}

sub exists
{
        my ( $self ) = shift() ;
        my ( $id ) = shift() ;
        my ( $sql , $data , $count ) ;

        $count = undef() ;

        $sql = "SELECT COUNT(*) as cnt FROM $self->{'_table_name'} WHERE 
$self->{'_pk_field_name'}=?" ;
        $data = $self->fetchHashData( $sql , [ $id ] ) ;
        if ( defined ( $data ) ) {
                $count = $data->{'cnt'} ;
        }
        return( $count ) ;
}

sub fetchHashData
{
        my ( $self ) = shift() ;
        my ( $sql ) = shift() ;
        my ( $bind_params ) = shift() || [] ;
        my ( $class ) = ref( $self ) ;
        my ( $sub ) = (caller(1))[3] ;
        my ( $dbh , $rc , $data , $sth , $param_count , $tmp_sql ) ;

        $dbh = My::James->getDBH() ;
        $rc = $sth = $dbh->prepare( $sql ) ;
        if ( ! $rc ) {
                $data = undef() ;
                $self->setErrorMessage( "Unable to prepare $class->$sub(): 
".$dbh->errstr() ) ;
        }
        else {
                $tmp_sql = $sql ;
                while( $tmp_sql =~ /\?/ ) {
                        $tmp_sql =~ s/\?// ;
                        $param_count++ ;
                }
                if ( $param_count != scalar( @{$bind_params} ) ) {
                        use Carp qw(cluck) ;
                        cluck( $sql ) ;
                }
 
                $rc = $sth->execute( @{$bind_params} ) ;
                if ( ! $rc ) {
                        $rc = undef() ;
                        $self->setErrorMessage( "Unable to execute $class->$sub(): 
".$sth->errstr() ) ;
                }
                else {
                        if ( $sth->rows() == 0 ) {
                                $data = {} ;
                        }
                        else {
                                $data = $sth->fetchrow_hashref() ;
                                if ( ! $data ) {
                                        $data = undef() ;
                                        $self->setErrorMessage( "Unable to fetch 
$class->$sub(): ".$sth->errstr() ) ;
                                }
                        }
                }
        }

        return( $data ) ;
}

sub fetchListData
{
        my ( $self ) = shift() ;
        my ( $sql ) = shift() ;
        my ( $bind_params ) = shift() || [] ;
        my ( $class ) = ref( $self ) ;
        my ( $sub ) = (caller(1))[3] ;
        my ( $dbh , $rc , $list , $sth ) ;

        $dbh = My::James->getDBH() ;
        $rc = $sth = $dbh->prepare( $sql ) ;
        if ( ! $rc ) {
                $list = undef() ;
                $self->setErrorMessage( "Unable to prepare $class->$sub(): 
".$dbh->errstr() ) ;
        }
        else {
                $rc = $sth->execute( @{$bind_params} ) ;
                if ( ! $rc ) {
                        $rc = undef() ;
                        $self->setErrorMessage( "Unable to execute $class->$sub(): 
".$sth->errstr() ) ;
                }
                else {
                        $list = $sth->fetchall_arrayref( {} ) ;
                        if ( ! $list ) {
                                $list = undef() ;
                                $self->setErrorMessage( "Unable to fetch 
$class->$sub(): ".$sth->errstr() ) ;
                        }
                }
        }

        return( $list ) ;
}

sub get
{
        my ( $self ) = shift() ;
        my ( $attribute ) = shift() ;
        return( $self->{$attribute} ) ;
}

sub getAll
{
        my ( $self ) = shift() ;
        return( \%{$self} ) ;
}

sub getErrorMessage
{
        my ( $self ) = shift() ;
        return( $self->{'_ERROR_MESSAGE_'} ) ;
}

sub populateInfo
{
        my ( $self ) = shift() ;
        my ( $id ) = shift() || undef() ;
        my ( $sql , $sth , $rc , $dbh , $info , $key , $class ) ;

        $class = ref( $self ) ;

        if ( ! defined ( $id ) ) {
                $rc = undef() ;
                $self->setErrorMessage( "Did not provide an id to 
$class->populateInfo()" ) ;
        }
        else {
                $sql = "SELECT * FROM $self->{'_table_name'} WHERE 
$self->{'_pk_field_name'}=?" ;
                $dbh = My::James->getDBH() ;
                $rc = $sth = $dbh->prepare( $sql ) ;
                if ( ! $rc ) {
                        $rc = undef() ;
                        $self->setErrorMessage( "Unable to prepare 
$class->populateInfo(): ".$dbh->errstr() ) ;
                }
                else {
                        $rc = $sth->execute( $id ) ;
                        if ( ! $rc ) {
                                $rc = undef() ;
                                $self->setErrorMessage("Unable to execute 
$class->populateInfo(): ".$sth->errstr());
                        }
                        else {
                                $info = $sth->fetchrow_hashref() ;
                                if ( ! $info ) {
                                        $rc = undef() ;
                                        $self->setErrorMessage( "Unable to fetch 
$class->populateInfo(): ".
                                                                $sth->errstr() );
                                }
                                else {
                                        foreach $key ( keys %{$info} ) {
                                                $self->{$key} = $info->{$key} ;
                                        }
                                }
                        }
                }
                $sth->finish() ;
                $dbh->disconnect() ;
        }
        return( $rc ) ;
}

sub populateInfoArbitrary
{
        my ( $self ) = shift() ;
        my ( %column_data ) = @_ ;
        my ( $sql , $sth , $rc , $dbh , $info , $key , $class , @bind_params , 
@columns ) ;

        $class = ref( $self ) ;

        $sql = "SELECT * FROM $self->{'_table_name'} WHERE " ;
        foreach $key ( keys %column_data ) {
                push( @columns , "$key=?" ) ;
                push( @bind_params , $column_data{$key} ) ;
        }
        $sql .= join( " AND " , @columns ) ;
        $dbh = My::James->getDBH() ;
        $rc = $sth = $dbh->prepare( $sql ) ;
        if ( ! $rc ) {
                $rc = undef() ;
                $self->setErrorMessage( "Unable to prepare 
$class->populateInfoArbitrary(): ".
                                        $dbh->errstr() ) ;
        }
        else {
                $rc = $sth->execute( @bind_params ) ;
                if ( ! $rc ) {
                        $rc = undef() ;
                        $self->setErrorMessage( "Unable to execute 
$class->populateInfoArbitrary(): ".
                                                $sth->errstr() ) ;
                }
                else {
                        if ( $sth->rows() == 0 ) {
                                $rc = undef() ;
                                $self->setErrorMessage( "Query did not match any rows" 
) ;
                        }
                        elsif ( $sth->rows() != 1 ) {
                                $rc = undef() ;
                                $self->setErrorMessage( "Query did not match exactly 
one row" ) ;
                        }
                        else {
                                $info = $sth->fetchrow_hashref() ;
                                if ( ! $info ) {
                                        $rc = undef() ;
                                        $self->setErrorMessage( "Unable to fetch 
$class->populateInfoArbitrary(): ".
                                                                $sth->errstr() );
                                }
                                else {
                                        foreach $key ( keys %{$info} ) {
                                                $self->{$key} = $info->{$key} ;
                                        }
                                }
                        }
                }
        }
        $sth->finish() ;
        $dbh->disconnect() ;
        return( $rc ) ;
}

sub set
{
        my ( $self ) = shift() ;
        my ( $attribute ) = shift() ;
        my ( $value ) = shift() ;
        if ( ! defined ( $self->{$attribute} ) || $value ne $self->{$attribute} ) {
                $self->{$attribute} = $value ;
                $self->{'_'.$attribute.'_state'} = 'notsync' ;
        }
}

sub setErrorMessage
{
        my ( $self ) = shift() ;
        my ( $msg ) = shift() ;

        $self->{'_ERROR_MESSAGE_'} = $msg ;
        My::James->logError( $msg ) ;
}

sub insert
{
        my ( $self ) = shift() ;
        my ( $class ) = ref( $self ) ;
        my ( $rc , $sql , @keys , @placeholders , @params , $key ) ;

        $self->setDefaultValues() ;
        @keys = sort( grep { !/^_/ } keys ( %{$self} ) ) ;

        $sql  = "INSERT INTO $self->{'_table_name'} (" ;
        $sql .= join(",",@keys);
        $sql .= ") VALUES (";

        foreach $key ( @keys ) {
                push( @placeholders , '?' ) ;
                push( @params , $self->{$key} ) ;
        }

        $sql .= join(",",@placeholders) ;
        $sql .= ")" ;

        return( $self->prepareExecute( $sql , [EMAIL PROTECTED] ) ) ;
}

sub setDefaultValues
{
}

sub update
{
        my ( $self ) = shift() ;
        my ( $class ) = ref( $self ) ;
        my ( $rc , $sql , @keys , @bits , @params , $key ) ;

        if ( ! $self->{$self->{'_pk_field_name'}} ) {
                $self->setErrorMessage( "Cannot update $class without database Id" ) ;
                $rc = undef() ;
        }
        else {
                @keys = sort( grep { !/^_/ } keys ( %{$self} ) ) ;

                $sql  = "UPDATE $self->{'_table_name'} SET " ;

                foreach $key ( @keys ) {
                        if ( defined ($self->{'_'.$key.'_state'}) && 
                             $self->{'_'.$key.'_state'} eq 'notsync') {
                                push( @bits , "$key=?" ) ;
                                push( @params , $self->{$key} ) ;
                        }
                }

                if ( scalar( @bits ) == 0 ) {
                        $rc = 1 ;
                }
                else {
                        $sql .= join(",",@bits) ;
                        $sql .= " WHERE $self->{'_pk_field_name'}=?" ;
                        push( @params , $self->{$self->{'_pk_field_name'}} ) ;
        
                        $rc = $self->prepareExecute( $sql , [EMAIL PROTECTED] ) ;
                }
        }
        return( $rc ) ;
        
}

sub prepareExecute
{
        my ( $self ) = shift() ;
        my ( $sql ) = shift() ;
        my ( $bind_params ) = shift() || [] ;
        my ( $class ) = ref( $self ) ;
        my ( $sub ) = (caller(1))[3] ;
        my ( $dbh , $rc , $sth ) ;

        print STDERR "sql=$sql\n" ;
        $dbh = My::James->getDBH() ;
        $rc = $sth = $dbh->prepare( $sql ) ;
        if ( ! $rc ) {
                $rc = undef() ;
                $self->setErrorMessage( "Unable to prepare $class->$sub($sql): 
".$dbh->errstr() ) ;
        }
        else {
                $rc = $sth->execute( @{$bind_params} ) ;

                if ( ! $rc ) {
                        $rc = undef() ;
                        $self->setErrorMessage( "Unable to execute 
$class->$sub(".My::James->getSQLDebug($sql,$bind_params)."): ".$sth->errstr() ) ;
                }
                elsif ( $sql =~ /^\s*insert /i ) {
                        $self->{$self->{'_pk_field_name'}} = $sth->{'mysql_insertid'} ;
                }
        }

        $dbh->disconnect() ;
        return( $rc ) ;
}

1;

#
# $Id: POP.pm,v 1.1.1.1 2003/05/25 17:18:34 journals Exp $
#
package My::James::POP ;

use strict ;
use DBI ;
use Data::Dumper ;
use Carp qw(cluck) ;

use lib '/usr/www/journalscape/lib' ;

use base 'My::James::BaseObject' ;
use My::James ;

sub new
{
    my ( $proto ) = shift() ;
    my ( $self ) = {
        '_table_name'    => 'users' ,
        '_pk_field_name' => 'username' ,
    } ;
    return ( bless( $self , $proto ) ) ;
}

sub create 
{
        my ( $self ) = shift() ;
        my ( $user ) = shift() ;
        my ( $pass ) = shift() ;
        my ( $encoded_pass ) ;

        $encoded_pass = $self->_encodePassword( $pass ) ;

        $self->set( 'username' , $user ) ;
        $self->set( 'pwdHash' , $encoded_pass ) ;
        return( $self->insert() ) ;
}

sub delete
{
        my ( $self ) = shift() ;
        my ( $sql ) ;

        $sql = "DELETE FROM inbox WHERE repository_name=?" ;
        if ( defined ( $self->prepareExecute( $sql , [ $self->get('username') ] ) ) ) {
                return( $self->SUPER::delete() ) ;
        }
        else {
                return( undef() ) ;
        }
}


sub getExistingUsers
{
        my ( $self ) = shift() ;
        my ( $sql ) ;

        $sql = "SELECT username FROM users ORDER BY username" ;

        return( $self->fetchListData( $sql , [] ) ) ;
}

sub getInboxMessageCount
{
        my ( $self ) = shift() ;
        my ( $sql , $data ) ;

        $sql = "SELECT count(*) as cnt FROM inbox WHERE repository_name=?" ;
        $data = $self->fetchHashData( $sql , [ $self->get('username') ] ) ;
        return( $data->{'cnt'} || 0 ) ;
}


sub setPassword
{
        my ( $self ) = shift() ;
        my ( $password ) = shift() ;

        $self->set( 'pwdHash' , $self->_encodePassword( $password ) ) ;
}

sub _encodePassword
{
        my ( $self ) = shift() ;
        my ( $pass ) = shift() ;
        my ( $java , $james_home , $classpath , $class , 
             $cmd , $output , $line , $encoded_pass ) ;

        $java = '/usr/local/java2/bin/java' ;
        $james_home = '/usr/local/jakarta-james' ;
        $classpath = "$james_home/lib/mail_1_3.jar:$james_home/build/classes" ;
        $class = "org.apache.james.security.DigestUtil" ;
        $cmd = "$java -cp \"$classpath\" $class -alg SHA %s|" ;
        $cmd = sprintf( $cmd , $pass ) ;
        open( CMD , $cmd ) ;
        $output = '' ;
        while( $line = <CMD> ) {
                $output .= $line ;
        }
        close( CMD ) ;

        ( $encoded_pass ) = $output =~ /^Hash is: (\S+)$/ ;
        return( $encoded_pass ) ;
}

1;

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: adding users to jdbc repository

Reply via email to