Personally, I haven't seen such a module in the DBIx namespace, but I'd really like such a module to allow for better performance in Class::DBI, as there, the joins on multiple tables are implemented on the Perl side of things. This approach is good, as it allows for huge flexibility, but as 99% of my use cases can be handled on the SQL side of things, I'd really like to declare the paths through my schema and have Perl figure out how to get from one table to the next via joins.

Consider looking at how SQL::AbstractSearch does things, but in any case, such a module would be very fine in my eyes.

I know you have your API laid out already, but here are some ideas how I'd like to use the module to declare joins in a Class::DBI setting:

__PACKAGE__->has_many_direct( abcds => [ a => 'b.ab' => 'c.bc' => 'd.c
+d' ] );
[download]

or, maybe by declaring the "fast path" options outside of the "has_many" section would be easier, as then all generated queries could use it:

package A;
__PACKAGE__->direct_join( b => 'b.ab' );
package B;
__PACKAGE__->direct_join( c => 'c.bc' );
# ... and so on
[download]

but that "solution" has the drawback of only allowing/suggesting one path through the table schema, where you might have more than one, say, a movie, having connections to a person via both, the roles and the directors.

The API isn't set in stone at all. Frankly, what I really have is an engine and I'm kinda looking for someone to tell me what existing module I should graft my engine into. Maybe Class::DBI is the correct place to offer this as a patch to. It seems to be the most well-used RDBMS distro behind DBI/DBD::* ...

As for only allowing one path through the table schema ... that's actually been a problem I've run into. Behind the scenes, I build a graph of the schema, then walk the graph to find the correct connections. Let's say you have the following schema:

movie:
    id       integer
    name     varchar
    director integer foreign key director.id

director:
    id     integer
    person integer foreign key person.id
    salary float

roles:
    movie  integer foreign key movie.id
    person integer foreign key person.id
    type   enum ('lead', 'supporting', 'bit')

person:
    id   integer
    name varchar
[download]

Now, let's say you want to get the names of the director and lead for a given movie. If I were hand-building the SQL, I would do the following:

SELECT d_p.name AS director_name
      ,l_p.name AS lead_name
  FROM movie m
  JOIN director d ON (d.id = m.director)
  JOIN person d_p ON (d.person = person.id)
  JOIN roles r ON (r.movie = movie.id)
  JOIN person l_p ON (r.person = person.id)
 WHERE movie.name = ?
   AND r.type = 'lead'
[download]

Right now, the engine I have isn't smart enough to realize that I need to use the person table twice. Part of the problem there is the API. I don't know how to allow the user to specify "Join to person through director and alias it as d_p" as well as "Join to person through roles and alias it as l_p".

*ponders*

Maybe, the aliases are specified as part of the schema description. So, if you use a given alias for a table, it's joined through one path and if you use the other, a second join (like above) is made through the other path. I think I can code this up, but I'd really love to have a good set of requirements ... (Hint!)

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

   use SQL::Abstract;

    my $sql = SQL::Abstract->new;

    my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order
+);
    my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
    my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);
    my($stmt, @bind) = $sql->delete($table, \%where);

    # Then, use these in your DBI statements
    my $sth = $dbh->prepare($stmt);
    $sth->execute(@bind);
[download]

SQL::Abstract looks like it only does single-table queries. My module will build N-table queries where the tables are anywhere in your schema, so long as they are connected in some fashion.

Maybe my module is really an extension to SQL::Abstract?

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Maybe my module is really an extension to SQL::Abstract?

Maybe SQL::Abstract::Join? But I would only use that name is you truely were extending SQL::Abstract in some way to support joins. I think that SQL:: is a namespace which can be added too, but IMO SQL::Abstract:: should only be added to for things specific to or working with SQL::Abstract.

Can we see more of the API? It looks like a very interesting module, I am curious how it works, etc in a larger example.

And no, I dont know of anything like this on CPAN, then again, I either like to write my own SQL or use our in house OO Persistence tools.

-stvn

I love SQL::Abstract, in particular for INSERT, UPDATE and DELETE, but it's support for SELECT is IMO a bit limited. I see its disadvantages in (t least) these areas:
1. SELECT is designed only for use for single tables, no support for any kind of JOIN
2. No support for aggregated queries, using GROUP BY and HAVING.

I'd love to see a module that supports these, but I wouldn't like to see Yet Another Incompatible Syntax. So yes, I vote for at least compatibility with SQL::Abstract, better yet, cooperation with it. That you might achieve by making your module an extension to it.

But perhaps I'd like it best if you contacted the author/maintainer of SQL::Abstract, and together find a way to merge your respective modules, making your module actually part of SQL::Abstract. Co-authorship, so to speak.

    select => [
        'A.foo',
        'E.bar',
    ],
[download]

to allow me to provide field aliases, like so:

    select => [
        'A.foo',                                  #normal
        { 'A.foo' => 'the_foo' },                 #simple
        { 'E.bar' => { alias => 'the_e_bar' } },  #flexible
    ],
[download]

resulting in the following SQL:

SELECT A.foo, A.foo the_foo, E.bar the_e_bar
[download]

/J

You could do that right now by doing:

select => [
    'A.foo AS foo',
    'E.bar AS bar',
],
[download]

which is what we do. However, I can definitely see adding different kinds of APIs, so that different SQL flavors can be supported. Thanks!

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Ok. I just wasn't sure you had that covered :)

Other than the SQL dialect issue, I don't really see any advantage of my suggested syntax over yours.

/J

Perhaps rather than first creating a user-facing module, you could separate out the abstract table mapping engine into a separate module that could be used behind the scenes by other modules like Class::DBI, DBIx::SQLEngine, and some future SQL::Abstract extension?

Ok. As one of the potential clients, I offer you a challenge - what sort of API do you want to have in this behind-the-scenes module? What requirements do you, the maintainer of DBIx::SQLEngine need in order to make this work for you?

In fact, I offer this challenge up to anyone who might want to use this type of functionality. What specifications are needed? What API? What features? What type of performance? One thing to note is that this is completely PurePerl, if that makes a difference.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.