http://www.perlmonks.org?node_id=400024


in reply to Re^4: DB persistence framework for Perl Classes
in thread DB persistence framework for Perl Classes

And about the portability of Class::DBI, I can be wrong, but you are saying to me that DBI is portable over databases. Well, the SQL is not.

As somebody who fell into the "gotta write my own abstraction layer" trap, I have to disagree here.

Class::DBI exists to allow you to not write SQL; problems with database portability I've run into have been limitations in the DBD layer of DBI and not related to Class::DBI directly. A good example of this is trying to access MS SQL Server; DBD::Sybase doesn't support bind parameters using the FreeTDS library.

Code like this:

my $item = db::Inventory::Item->retrieve( 15 ); if( $item ) { $item->quantity( 15 ); }
works with every database I've tried so far where a Class::DBI driver exists.

If you're worried about database portability, you have to take into account variants of "sequence" methodologies across various databases. For example, both Postgres and Oracle have an object called a sequence, but they access them differently:

-- Oracle: INSERT INTO my_table VALUES( my_sequence.nextval, 'Some Value' ); -- Postgres: INSERT INTO my_table VALUES( nextval( 'my_sequence' ), 'Some Value' );
Heaven forbid you want to use a database where sequences don't exist but you want autoincrementing fields, say MySQL or SQL Server/Sybase:
--MySQL INSERT INTO my_table( seq_field, val_field ) VALUES (NULL, 'Some Value'); SELECT LAST_INSERT_ID(); -- SQL Server INSERT INTO my_table( val_field ) VALUES ( 'Some Value' ); SELECT SCOPE_IDENTITY();
Not to mention that MySQL will insert a requested value into the sequence field (if it doesn't violate a primary key constraint), where SQL Server will barf if you attempt to place any value into an autoincrementing field.

The code to do this with Class::DBI is exactly the same, no matter what database layer you deal with:

my $newRecord = db::Table->create( { val_field => 'Some Value' } );
And you don't have to deal with rewriting your SQL to handle different funkiness associated with different back ends. It Just Works.

Granted, there are things I don't like about Class::DBI:

What I tend to do is use Class::DBI for my user-type interactions where editing and small selection is involved (update my user preferences, edit an item, etc.) and use raw SQL for reporting in the web environment.

For back-end batch processing, I tend to do everything with raw DBI/Perl code unless I have to iterate over every single record and the queries driving my main batch loop aren't JOINed tables.

Replies are listed 'Best First'.
Re^6: DB persistence framework for Perl Classes
by perrin (Chancellor) on Oct 18, 2004 at 02:56 UTC
    Views are basically handled as tables in Class::DBI. If you're really saying that you want to access an ad hoc query as a Class::DBI object, I would suggest that is reporting and should not be done through Class::DBI. Class::DBI is mostly helpful for read/write applications, not for simple query-and-display jobs.

    Having separate user/pass options is usually pretty straightforward with Class::DBI when you aren't trying to use them with the same class names inside the same running perl process. If you can expand on the problem you're having, there might be an easy answer.

    Your last comment about transaction support sounds interesting, but I'm not quite sure what you mean by it. Are you saying that you want to update some objects and then commit some of your changes but not all of them?

      Are you saying that you want to update some objects and then commit some of your changes but not all of them?

      Nope. Just plain old, simple transactions.

      Say, for example, I'd like to update 10,000 records in a database from a command-line script and I'd also like to read/update one record at a time in a web page. Currently, I have two different Class::DBI ancestors to handle the one-off changes, and another to handle batching the mass updates into smaller chunks so the transaction logs in the database don't get hammered.

      Some of my problems stem, I'm sure, from enforcing least-privilege at the database and application levels; my Apache database user has privileges to update a very limited set of tables and can read almost all the tables, where the command line processes can update most tables. The web user updates one record at a time, while the command line process almost always updates in large batches.

      Class::DBI seems to get confused in the Apache environment if I have transactions enabled in the Web base class and I haven't figured out a way around this other than doing a $obj->dbi_commit() after every read. This is, in my opinion, really a Bad Idea, as you shouldn't commit a record that you haven't changed and probably don't have write access to begin with.

        Okay, it's not really clear to me why you're having trouble with the transactions. They aren't any different from vanilla DBI, and I use them after updating multiple records in the same way I would with straight DBI commands. Maybe you're using InnoDB and getting tricked by the confusing isolation level settings it uses? Many people have trouble with that. Data committed in one process can't be seen in other processes until they commit. I change the isolation level to something more like Oracle or Postgres to get around this.

        Anyway, you're more than welcome to bring the issue up on the Class::DBI mailing list, especially if you can give a more precise description of the symptoms you're seeing.