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 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"; } }
Too much typing
Breaks DRY
Not clever enough
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');
This is a very simple system
Two tables with a few columns
Usually you'll need more tables and a lot more columns
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
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) );
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
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
"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?
"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
Metadata is your friend
Learn more about your database's advanced features
Or hire a DBA
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
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
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
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
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
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
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'
ORM makes your life a lot easier
But it could be easier still
Database metadata is your friend
I have no code - only ideas
Any questions?