Re: Postgresql VIEWs

[prev] [thread] [next] [Date index for 2005/02/09]

Sorry guys, I'm about half way there now. I don't know what I was doing 
wrong exactly, but I did change the structure of my class files.

I'm on Mac OS X so it goes like this:

/Library
-->Perl
---->5.8.1
------>My     #the folder I added for my files.

Within "My" I have all my table files as well as my "TestDB.pm" file 
which contains my connection info.

------------
#TestDB.pm:

package My::TestDB;

use strict;
use warnings;
use Class::DBI::FromCGI;
use base 'Class::DBI';

my ($dsn, $username, $password) = getConfig();

__PACKAGE__->set_db('Main',
                              $dsn,
                              $username,
                              $password,
                              {AutoCommit=>0},
                             ) or die "Cannot connect: " . $DBI::errstr;;

sub getConfig {
        return ('dbi:Pg:dbname=the_database_name','some_user_name', 
'numbers_and_letters');
}

1;

------------
#here is a typical TABLE. Xuser.pm

package My::Xuser;

use strict;
use base 'My::TestDB';

__PACKAGE__->table('xuser');
__PACKAGE__->columns(All => qw/xuser_id full_name login_name 
login_password email_address phone_number other_info show_all_kits 
is_employee is_admin change_stamp is_abscoa is_tlc is_manager 
is_sales/);

__PACKAGE__->sequence('global_seq');
1;

------------
#here is a typical VIEW

package My::Xadmin;

use strict;
use base 'My::TestDB';

__PACKAGE__->table('xadmin');
__PACKAGE__->columns(All => qw/xuser_id full_name login_name 
login_password email_address phone_number other_info 
user_permissions/);

__PACKAGE__->sequence('global_seq');
1;
------------

I have RULEs set up that take care of update, delete and insert. Notice 
the VIEW Xadmin is mapped to the TABLE Xuser. Now that Xadmin is 
working, I can drop the file for Xuser. Everything I need to do to it 
can be done through Xadmin.

Andreas Fromm asked about primary keys being missing in VIEWs. I always 
have at least one primary key in my VIEWs, otherwise it would be 
difficult to do anything but SELECT. Mostly, when combining two TABLEs 
in one VIEW, I still only need one primary key because I can follow the 
relationship to edit the TABLE that doesn't have it's primary in the 
VIEW.

As a newbie to Perl I'm very happy to have this working this way 
because as of right now I have a transactional MRP system that I built 
in Postgres that uses Microsoft Access for the front end. Access does 
nothing more than CRUD on VIEWs for this system. All the more complex 
stuff takes place with TRIGGERs and RULEs. It should be fairly easy now 
to build a web front end to replace Access.

At one time I was going to do this with WebObjects, but I no longer 
have time for it. The development cycle and learning curve were are a 
huge obstacle for me. YMMV, but I've gone farther with Perl in two 
months than I have with WebObjects in two years!

Justin Tocci
Fort Wayne, IN