Persistence framework for MySQL 4.x (InnoDB)

mman has asked for the wisdom of the Perl Monks concerning the following question:

Hello folks, I need to choose persistence module from CPAN for working with MySQL 4.x (InnoDB).

I have referental integrity. My DB schema includes several tables with hierarchy and one-to-many relationships ( so I have multiple column primary keys).

I've looked at Alzabo (all good, but no persistence), SPOPS and Tangram (no multiple primary key support, SPOPS manual says that I need to code mul_col_pk methods by hand ).

So here's some requirements for framework:

- persistence (or I have to store data in some data structure, doing it with HoAoHoAo.. and so on is tooo weird)
- automatic schema reverse engineer (low priority)
- relationship support (multi column primary keys is a must)
- reference constraints (low priority)
- transactions (low priority)

Does this beauty exist?

Or maybe some advices for 'in memory' data storing framework to work with Alzabo, or maybe SPOPS hacking advice or secret ftp that contains Tangram 3 =)

Thank you.

Comment on Persistence framework for MySQL 4.x (InnoDB)

Replies are listed 'Best First'.
Re: Persistence framework for MySQL 4.x (InnoDB) by fenLisesi (Priest) on Nov 01, 2006 at 11:12 UTC
I have been using Class::DBI and Class::DBI::Sweet, but monks seem to be slowly gravitating towards the two modules that davorg recommends above as well as Rose::DB::Object that merlyn has recently recommended. Could you clarify two points for me, though: You shouldn't really have to use multiple-column primary keys, is there a particular reason for your design? You say referential integrity is important, but transactions are low priority. Why is that?	[reply]
Re^2: Persistence framework for MySQL 4.x (InnoDB) by mman (Novice) on Nov 01, 2006 at 13:56 UTC
Thanks for reply. 1. Yes. I really need multicolumn primary keys, because I have such DB scheme and I think there's no way out. Suppose you have a bug with many legs: BUG(id(pk), name)---*LEG(bug_id(pk1), leg_number(pk2), length) Is there another possible method to do such scheme? One-to-many relationship. 2. I'm using MySQL 4.x. Transactions is not too much concern me, cause I'll have only one SELECT'er and only one INSERT'er in my DB. Autocommit will satisfy me, I suppose. Maybe later I'll switch to MyISAM engine. Referental integrity is not to shoot myself in foot, while filling mapped structure and then saving it into DB. But I think relationships (many-to-one, one-to-may, one-to-one) is sufficient.	[reply]
Re^3: Persistence framework for MySQL 4.x (InnoDB) by fenLisesi (Priest) on Nov 01, 2006 at 14:16 UTC
How about: `BUG( id(pk), name ) LEG( id(pk), bug_id(fk), leg_number, length )` [download] and a unique index on (bug_id, leg_number)?	[reply] [d/l]
Re^4: Persistence framework for MySQL 4.x (InnoDB) by Anonymous Monk on Nov 01, 2006 at 15:41 UTC
Re^4: Persistence framework for MySQL 4.x (InnoDB) by mman (Novice) on Nov 01, 2006 at 14:28 UTC
Re: Persistence framework for MySQL 4.x (InnoDB) by davorg (Chancellor) on Nov 01, 2006 at 10:29 UTC
You should probably look at DBIx::Class or Jifty::DBI. -- <http://dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re: Persistence framework for MySQL 4.x (InnoDB) by perrin (Chancellor) on Nov 01, 2006 at 14:51 UTC
Both Rose::DB::Object and DBIx::Class meet all of your requirements.	[reply]
Re^2: Persistence framework for MySQL 4.x (InnoDB) by mman (Novice) on Nov 02, 2006 at 06:29 UTC
Thank you for advice.	[reply]