your DBIx::Abstract perl module

[Darren Duncan]

Hello Andy, thank you for your quick reply.  (And this letter is CCd to my local victoria.pm for a backup reference copy.)

You said:
>I do.  You can take a look at it here:
>http://turner.mikomi.org/DBIx-Abstract-1.100.tar.gz

Thanks for the preview.  I had a look, but I went through quickly due to it being late here.  At first glance it looks much like the old version, but that probably makes sense since it is an incremental upgrade.  When I get the chance I will look closer.

>As for DBIx::Abstract, it needs to be revised in light of the more recent
>versions of DBI.  Many of the old convenience methods are now (kind of) in
>core DBI. For instance DBIx::Abstract's select_all_to_hashref is very
>similar to DBI's selectall_hashref.

True.  I also noticed that, despite continuing to provide select_all_to_hashref() in your module, you are still implementing it the hard way (using a loop to transform a fetched array to a hash) rather than just wrapping it around a call like '$rowset = $sth->fetchall_arrayref({});', which is what I do in my own module.  Is this an oversight, or did you want different output than my suggestion provides?

>I do need help in figuring out how to represent complex joins in Perl-land.
>And the field list could use some help.  Right now its fine for a list of
>fields but kind of falls down when more complex expressions are needed.

I can help you with this, since I had already thought of a way to solve more complex cases in preparation to making my own module.  I will outline the idea here (and please credit me if you use it):

1. To my knowledge, joins are always for relating records in two tables, so they don't need to be very complex like where/filter conditions are.  Also, join conditions are always anded/intersected and not ored, are they not?  So all you have to do is take a list of join conditions, where each join condition is a structure like this:

...
join => [
	{
		lhs_table => 'business',
		rhs_table => 'product',
		join_type => 'left',
		rel_fields => {
			fk_pt_one => 'pk_pt_one',
			fk_pt_two => 'pk_pt_two',
		},
	},
],
...

The above example should handle any join type, equal, inner, left, etc, and handle any case from 1 to N fields needed as a unique identifier of a table record.  As you can see, it is a hash ref per two tables being joined, and there is nothing in there at all that suggests what sql syntax looks like, which is quite different between Oracle and MySQL; this can even be used with databases who don't use sql as their interface language (but still support relational theory).

2. To deal with the common case where multiple records from the same table are joined together, directly or indirectly, here is a suggestion for making that work:

...
table => {
	myself => 'person',
	father => 'person',
	mother => 'person',
},
...

If you take this suggestion, then the keys in the hash are table aliases and the values in the hash are table names (the aliases are always unique, the names aren't always).  The caller and you would then use the table aliases declared here instead of the usual table names in all of the other parameters: join, fields, where, group, order...

3. I will note that the above example didn't say anything about dealing with sub-selects.  But if you decide (as I have) to use a new object to store the details of a select, such as what I mentioned above, then you can have these objects contain other objects when a sub-select or perhaps a union is meant to be used somewhere.

4. I won't give any details on 'fields' or 'where' suggestions right now, since they can be inordinately complex when one has to deal with functions and such, so another time.  But you're already further ahead in those areas, abstraction-wise, than you were with 'table' or 'join'.

>As for dialect differences.  I do have a rewrite that was built with that
>intention.

What I plan to do (and suggest to you) is to go further.  Make your 'public' module just an interface and/or input validator.  Let your dialect modules not only create sql but actually call the DBI methods themselves (and instantiate the DBI object for that matter).  On the other hand, if this sounds like a lot of duplication, you can have a different new 'private' module that contains functionality removed from your 'public' module, and from which your 'dialect' modules subclass.  Of course, this may be further than you want to go, but it's what I'm going to do.

>Other then inner joins I don't think you need any SQL now.  But yeah, that's
>the goal.  (Field lists can be passed as arrayrefs, but when they're
>constant it usually seems cleaner to just pass them as scalars.)

There is a lot more I want to do, such as call 'stored procedures' from my main application without knowing the database particulars for doing so (and even have it work magically for databases that don't even support stored procedures, but use the real stored procedures for those that do).  I also want to be able to verify/create/modify/delete database schema.  All without the application who calls the Abstract module knowing about sql.  And store metadata from which both selects and modifying sql can be generated.  But that's talk for another day...

... and now it's almost an hour later.  Time for rest.  Have a good night to you. -- Darren Duncan