What's Wrong with ORM

Dave Cross <dave@mag-sol.com>

Magnum Solutions Ltd

What is ORM?

  • Object Relational Mapping

  • Converts between Objects and Relational Databases

  • Tables become classes. Rows become objects

  • Relations become classes. Tuples become objects

  • e.g. Class::DBI, DBIx::Class, Jifty::DBI, etc...

  • e.g. ActiveRecord, Hibernate, Django

Class::DBI Examples

  • Class::DBI allows you to write code like this

  •   use Music::Artist;
      foreach my $art (Music::Artist->retrieve_all) {
        print $art->id, ' ', $art->name, "\n";
      }
  • Or like this

  •   my $art = Music::Artist->retrieve($id);
      if ($art) {
        foreach my $song ($art->songs) {
          print $song->title, "\n";
        }
      }

What's Wrong With ORM?

  • Too much typing

  • Breaks DRY

  • Not clever enough

Too Much Typing

  package Music::DBI;
  use base 'Class::DBI';
  # Don't hard-code these!!
  Music::DBI->connection('dbi:mysql:musicdb', 'username',
                         'password');
  package Music::Artist;
  use base 'Music::DBI';
  Music::Artist->table('artist');
  Music::Artist->columns(All => qw/id name genre/);
  Music::Artist->has_many(songs => 'Music::Song');
  package Music::Song;
  use base 'Music::DBI';
  Music::Song->table('song');
  Music::Song->columns(All => qw/id title artist/);
  Music::Song->has_a(artist => 'Music::Artist');

Too Much Typing (cont)

  • This is a very simple system

  • Two tables with a few columns

  • Usually you'll need more tables and a lot more columns

Breaks DRY

  • Don't Repeat Yourself

  • If the same information is stored twice then versions can get out of step

  • This information is already in the database

Table Definitions

  CREATE TABLE artist (
    id     INTEGER PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(50),
    genre  VARCHAR(5)
  );
  CREATE TABLE song (
    id     INTEGER PRIMARY KEY AUTO_INCREMENT,
    title  VARCHAR(50),
    artist INTEGER FOREIGN KEY REFERENCES artist(id)
  );

Not Clever Enough

  • We're putting lots of metadata into our databases

  • ORM systems should make use of that

  • Current ORM systems don't do that

    • Or don't do it enough

  • They need to be cleverer

Database Metadata (An Aside)

  • Some people don't put enough metadata in their databases

  • Just tables and columns

  • No relationships. No constraints

  • You may as well make each column VARCHAR(255)

  • Describe your data in your database

  • It's what your database is for

  • It's what your database does best

No Metadata (Excuse 1)

  • "This is the only application that will ever access this database"

  • Bollocks

  • All data will be shared eventually

  • People will update your database using other applications

  • Can you guarantee that someone won't use mysql to update your database?

No Metadata (Excuse 2)

  • "Our database doesn't support those features"

  • Bollocks

  • MySQL 3.x is not a database

    • It's a set of data files with a vaguely SQL-like query syntax

  • MySQL 4.x is a lot better

  • MySQL 5.x is most of the way there

  • Don't be constrained by using inferior tools

Learn to Love Metadata

  • Metadata is your friend

  • Learn more about your database's advanced features

  • Or hire a DBA

List of Table Names

  • All database systems provide a list of tables in the database

  • SHOW TABLES

  • ORM can automatically create a class for each table

  • Many ORM systems do this already

  • If you're creating classes individually then you're wasting your time

List of Columns in a Table

  • All database systems provide a list of columns in a table

  • DESC [table]

  • ORM can automatically create an attribute for each column

  • Many ORM systems do this already

  • If you're listing the attributes in your classes then you're wasting your time

Column Data Types

  • All database systems show you the data types for columns

  • DESC [table]

  • ORM systems should use this information to further define the attributes

    • Valid values

    • Class mapping (DATETIME -> DateTime)

  • User defined data types

Relationships

  • Most database systems allow you to define primary key/foreign key relationships

  • SHOW TABLE STATUS FROM $dbname LIKE '[table]'

  • Define actions to take if an update breaks referential consistancy

    • Don't allow salesperson record to be deleted if there are associated customer records

  • ORM systems can use this to automatically generate relationships between classes

  • If you're defining lists of has_a and has_many relationships then you're wasting your time

Constraints

  • Many database systems allow you to define constraints on a column

    • e.g. Sex must be 'M' or 'F'

    • e.g. Age must be >= 18

  • ORM systems can use this to validate data

Triggers

  • Many databases allow triggers to be defined on database actions

  • Insert, update, delete data

  • Carry out cross-column and cross-table checks

    • If Age >= 18 then NI number is not null

    • When invoice is marked as paid, insert new shipment record

  • Allow transaction to be rolled back if constraints not met

  • ORM systems need to handle errors generated by triggers

The Downside

  • Performance

  • You don't want to query all of your metadata tables each time your program runs

  • You rarely need all of the classes in one run of the program

  • Best to generate class definitions ahead of time

  • Regenerate automatically when schema changes

  • SQL::Translator does this for Class::DBI (and DBIx::Class)

  • Still a long way to go tho'

Conclusion

  • ORM makes your life a lot easier

  • But it could be easier still

  • Database metadata is your friend

  • I have no code - only ideas

Questions

  • Any questions?