Search for ORM with Multi-Table-Object Support

[Xel]

Hi all,

I am searching for a good ORM. I found some ORMs but somehow none of them seems to have what I am looking for.

I need something like Multi-Table-Objects.

Well I found some ORMs supporting them, but not in perl. For example there are SQL Alchemy for python and hibernate for java.

I need are objects consisting out of more than just one table. Joined together as needed but transparent for the application.

e.g.:
User consisting out of following tables:
user user_contact user_settings user_profile

As needed the contact-info, the settings and the profile are joined together into the user-object.

Yes the example is clumsy and no one will see a need in having them all in one object. But think about historically grown data structures. Changing them is nearly impossible without having an abstraction layer between the application code and the data-structures.

This can not be managed with one-to-one relations, like they are available in nearly every ORM. Being able to handle this with one object rather than a relation is a great thing, enables one to push fields around the database, split tables and do whatever needs to be done to the Database without changing more than just one class.

If they would be in different classes moving a field to another table would require to change two classes and every code-line accessing the attribute in any way.

See I want to have some more abstraction from the Database within the application.

I do not only want to stop loading specific fields, I want to keep tables slim, small and fast.

I want to be able to change database-layout (up to a certain extent) without having to change tons of lines of sql code widely spread among hundreds of modules and scripts.

What I specially need in an ORM:

• Multi-Table-Mapping-Objects
• Lazy-Loading (Single Columns as well as groups of columns)
• Agregation Functions
• All sorts of relationships
• Flexible ways to query data from database
• Multi-Database-Support (no need for emmulated joins or stuff between them - just need to be able to fetch some objects from one DB and others from another one)

I see no problems with most of them, but with the first one...

Does anyone know a ORM fitting my needs in perl?

Regards Alex

[Your Mother]

Why did you reject DBIC? It's probably what you want. The following is a brief tour of its power.

Multi-Table-Mapping-Objects

Pseudo-code which assumes some manual set-up in the schema files (many to many relationships are not discoverable and the example would require overload on the role class to work as shown)–

for my $user ( $users->all )
{
    print $user->name, $/;
    print "    Roles: ", join(", ", $user->roles) || "none", $/;
    for my $role ( $user->roles )
    {
        print "Users in this role: ";
        print join(", ", map { $_->name } $role->role->users);
    }
}

DBIC has notions of the column, the resultset, the table, the record, the connection, the relationships, and more. Many ORMs are record oriented and get ugly/stuck easily since it's all a ball of mud around a row. DBIC handles this really nicely.

Lazy-Loading (Single Columns as well as groups of columns)

my $full_rs = $schema->resultset("user");
my @ids = $full_rs->get_column("id")->all;

# -or-

my $simple_rs = $full_rs->search({},
                                 { columns => [qw/ id /] });

print $_->id, $/ for $simple_rs->all;

# -or, no objects-

# use parent "DBIx::Class::ResultSet::HashRef"; <- in resultset class.
my $ids = map { $_->{id} }
      $schema->resultset("user")
             ->search({},{columns => ["id"])
             ->hashref_rs
             ->all;

Agregation Functions

The get_column above is an example. DBIx::Class::ResultSetColumn has more, like–

printf "\$%.2f\n", $user->orders->get_column("total")->sum;

All sorts of relationships

• DBIx::Class::Relationship
• DBIx::Class::Manual::Cookbook

Also see the amazing DBIx::Class::Tree::Mobius for an inner-relationship hybrid of nested intervals and materialized paths.

Flexible ways to query data from database

Chained resultsets are everything good in the world. You essentially build up complementary named fragments and make your code compact, syntactical, and fun. For example (assumes you wrote the resultset code, which is easy, to do it)–

  $schema->resultset("Company")
         ->find($id)
         ->users
         ->active
         ->with_overdue_balance;

Multi-Database-Support

No. Yes. What?

DBIC supports pretty much everything DBI does. It does not support joining across DBs. You can certainly have a schema instance for each DB exchanging information at the Perl level.

More reading

• DBIx::Class
• DBIx::Class::Row
• DBIx::Class::ResultSet
• DBIx::Class::ResultSetColumn

Update: missing adverbly.

[Xel]

Thanks for your answer,

I am not sure if you mean the same as I do, when refering to Multi-Table-Mapping-Objects.

I mean an object, which consists of one row out of one table and another one out of another table.

What I would wish to do is

# Tablesetup:
[user]
id
username
password

[user_contact]
id
firstname
lastname
street
zip
city
phone

[user_profile]
id
profile_text
profile_image

# Code-Example

$user->load({id => 1, fetch => qw('username firstname lastname')});

print $user->firstname; # prints prefetched firstname
print $user->lastname; # prints prefetched lastname
print $user->profile_text; # prints lazy-loaded profile_text

As I understand DBIC I would do the following (ignoring lazy-loading):

$user = $schema->resultset('User')->find(1);
print $user->user_contact->firstname;
print $user->user_contact->lastname;
print $user->user_profile->profile_text;

Well, of course I could get around this limitation by writing my own setters and getters accessing the sub-objects from within the parent-object. Maybe this is the best way to do it, but I tought there may be ORMs which can handle this behavior already.

Regards
Alex

[Your Mother]

It might just be personal bias but that feels messy compared to the affordance it gives. You could potentially do the same thing with Moose using handles on the foreign objects attributes. That seems like a mistaken can of worms and a performance ding though… As you mentioned, you could write your own shortcut routines and you could make it semi-lazy with has_column_loaded.

There are two natural ways you can do some of what you want in DBIC. The first is keeping the DB in line with the code: a VIEW. This is mysql syntax–

CREATE VIEW uview AS
  SELECT
       u.id
       ,u.username
       ,c.name
       ,c.address
       ,p.text
       ,p.image
  FROM user u
       ,contact c
       ,profile p
  WHERE c.user = u.id
        AND
        p.user = u.id
        ;

And then you create a regular DBIC result class for it. I tried a loader with dbicdump and it seems to work just fine.

Alternatively you could do a DBIC in-code view class. With __PACKAGE__->result_source_instance->is_virtual(1) and the same SQL as the result_source_instance->view_definition. Both of these suffer from returning readonly objects and the real VIEW additionally will likely create real, and ultimately useless/confusing, tables upon deployment while the in-code view will not. (Update: it occurs to me that could put the is_virtual on the real VIEW too to keep it from creating a new table on $schema->deploy… but I didn't test and it's not put in by the loader/dumper defaults.)

But for convenience and a sort of permanent join/prefetch this would do what you want (as long as you only want to read).

[preaction]

DBIC is simpler than SQLAlchemy, so it doesn't do this kind of thing for you. Personally, I like the obvious separation of the objects: It makes the data model and relationships more apparent from the code (though I would remove the user_ prefix from the relationships). I've rarely had all of my code (DBIC or SQLAlchemy) survive database schema changes, unless I had planned for that schema to change (simple adding/removing optional columns for example).

[trwww]

DBIx::Class certainly has what you are asking for with has_one, and combining Subset_of_related_fields and Incomplete_related_objects.

I'd put together an example for you but its all right there.

Also, you didn't mention you'd be doing updates, as mentioned elsewhere I believe a view would make the most sense.

[JavaFan]

I'm not the biggest fan of ORMs (they look nice and dandy at first, and then they hurt), so I don't know the answer to your question.

But,

I want to be able to change database-layout (up to a certain extent) without having to change tons of lines of sql code widely spread among hundreds of modules and scripts.

Do note that you don't need an ORM to be able to archive that. You can easily write a layer (and it's up to you to determine how many files this layer has) that provides access to the database -- that is, if you change the layout of your database, all you need to change is this layer. In fact, this layer can be written in the database itself (stored procedures) or in a different language. There's no need to map each row into an object. That's just one way of doing it.

[Xel]

Thanks for the annotation. You are surely right - and I know about the caveats of ORMs.

Of course one does not need to do it the OO way - but we really need a place to store all the stuff which has to be done whenever records are processed in any way. This can be done in an static module or in an object as well.

I think it is attractive to do both (abstraction and collecting all the code) in one step.

In regard to stored procedures I do not dare to use them, because we hat massive trouble in using triggers in our replication due to mysql-bugs (which were really tricky to hunt down) and I have no interest in bugtracing mysql's stored procedures with replication...

[tospo]

++ for DBIC
I'm using it in my current project and I also have to build multi-table objects. These are quite large objects in my case with quite a lot of data, so it is split up across tables and I only need to assemble the full object when a single record is selected.
I just have a 'to_xxx_object' method in the result class for the table that stores the bulk of the data, which just gathers all the data from the related result classes and instantiates the big object. You end up with a single place in your code that needs to change if the tables change.

[Xel]

Hey - that sounds like a solution. And like nearly the same use-case as mine.

As I understand you, this can even be insertable/updateable - just write back any changes to the related objects and save them back to the database.

Thank you very much - I will try it out.

[tospo]

hmm, my post got lost, anyway, I was just saying that you can definitely do that.
You will want to provide a 'insert_xxx_object' method that knows how your object has to be broken up and issues all the create->()->create_related() calls to write them to the database. This one would go into a ResulSet class for your 'main' table (or any one of the tables that are involved in storing this object), which you may have to create first.

[sundialsvc4]

I share the opinion that you should use an ORM (or not ...) to construct an application-specific object or objects that knows about the underlying table relationships.   You want to centralize the responsible application logic in that one place and thing.

Why do I say this?   Well, because there might be nothing worse to a computer person than “mergers and acquisitions.”   One company buys another one and suddenly they’ve got to work together.   I guarantee you that their database structures (hell, their servers ...) are not the same.   If the database-level logic is concentrated then maybe you have a fighting chance.   But, even if the database access employs the “velvet mittens” of your ORM de jour, if that logic is instead scattered throughout the app, you’re scrooooood sunk.

With that simplification, I think that the “multi-table object” requirement is seriously relieved.

[nikosv]

Multi-Table-Mapping-Objects;I need are objects consisting out of more than just one table. Joined together as needed but transparent for the application.

This is a scenario for a view.You can also have a view that supports updates/DML on the underlying tables thjrough the view;for example check Oracle's materilized view

Using it maybe there is no need for an ORM

[Xel]

I am sorry, but I can not use views. They can be updatable - yes. But they are (in MySQL which we are using) not insertable (reffering to Multi-Table-Views, whenever you want to insert into more than one table at once)

There are just to many restrictions to be able to build a seperation-layer with views.

I would really wish I could just use them and change nothing at the application at all. But they only do half the job.