Fey, Another Freakin' ORM

Dave Rolsky

Why another ORM?

• Alzabo was really old crap
• But I still liked parts of it
• Wanted a similar but modern tool without the baggage

Fey anatomy

• Fey distro - core modules
• Fey::Loader
• Fey::DBIManager
• Fey::Test
• Fey::ORM and Fey::ORM::Mock

SQL generation with Perl

• Fey::SQL
• Fey::Literal
• Fey::Placeholder

SQL Functions

my $avg_track_length =
    Fey::Literal::Function->new(
        'AVG', $track_t->column('length')
    );

my $select = Fey::SQL->new_select();

Select

$select
  ->select( $track_t )
  # figures out appropriate join
  ->from( $album_t, $track_t )
  ->where( $album_t->column('year'), '>',
           Fey::Placeholder->new() )
  ->and( $track_t->column('length'), '>',
         $avg_track_length )
  ->order_by( $album_t->column('name'),
              $track_t->column('name') );

# quotes the crap out of everything
my $sth = $dbh->prepare( $select->sql($dbh) );
$sth->execute( 1990 );

Auto-placeholders

my $select = Fey::SQL->new_select();

$select->select( $track_t )
       # figures out appropriate join
       ->from( $album_t, $track_t )
       ->where( $album_t->column('year'),
                   '=', 2005 );
       ->and( $album_t->column('artist_id'), '=',
              $tommy_heavenly6->artist_id() );

my $sth = $dbh->prepare( $select->sql($dbh) );
$sth->execute( $select->bind_params() );

• Very handy, but can be turned off
• Always does proper quoting

Fey core philosophy

• Leverages your SQL knowledge
• Looks SQL-y
• Simple data structures, explicit comparisons

Why use it?

• Awfully verbose for simple SQL!
• Primary use case - dynamically generating complex SQL
• I seem to need to do this a lot
• If you don't, Fey may not be for you

Support Any Query

• Make it easier to generate SQL dynamically
• Subselects
• Complex joins with join constraints

FROM TableA
     LEFT OUTER JOIN TableB USING (foo_id)
     WHERE TableB.size > 10

Be Helpful

• Auto joins
• Ignore the same join twice
• De-coupled from an ORM
• But not too helpful

Fey::Loader

• Define your schema in standard SQL
• Point Fey::Loader at your DBMS

my $loader = Fey::Loader->new( dbh => $dbh );
my $schema = $loader->make_schema();

Fey::Loader

• Works with SQLite, Pg, and MySQL so far
• Uses DBI's *_info methods (mostly)
• If your DBMS supports them, making a Fey::Loader subclass is easy(ish)

Fey::Loader

• Use it, don't define your schema in Perl

Schema Introspection

my $loader = Fey::Loader->new( dbh => $dbh );
my $schema = $loader->make_schema();

for my $table ( $schema->tables() ) {
    print $table->name(), "\n";

    for my $column ( $table->columns() ) {
        print $column->name(), "\n";
    }
}

Logical Schema Only

• Schema, tables, columns, foreign keys
• No indexes
• Also no triggers, stored procs, domains, etc.

Fey::DBIManager

• Manages a set of 1+ DBI handles
• Primarily for the benefit of Fey::ORM - but works standalone
• Handles forking & threading properly
• Can be subclassed for DWIMing with master/slave setups

Fey::DBIManager

• Most people will not need to look too closely
• Just set up your Fey::DBIManager::Source at load time

Fey::DBIManager example

package MyApp::Schema;

my $source =
    Fey::DBIManager::Source->new(
        dsn          => ...,
        username     => ...,
        password     => ...,
        post_connect => \&_set_dbh_attributes,
    );

sub _set_dbh_attributes {
    my $dbh = shift;

    $dbh->{pg_enable_utf8} = 1;
    $dbh->do( 'SET TIME ZONE UTC' );
}

Fey::DBIManager post-connect

• Post-connect gets called whenever Fey::DBIManager reconnects
• After a fork or in a new thread
• More or less transparent

Fey::ORM

• Builds on top of Fey core and Fey::DBIManager

Fey::ORM is SQLy

• A SQL-oriented ORM
• Thinking in sets, not objects
• Not a dumb object store
• Design your tables first

Fey::ORM is Moosey

• Subclasses Moose metaclasses
• Declarative
• You can use all Moose features in your classes!

Fey::ORM - MyApp::Schema

package MyApp::Schema;

use Fey::ORM::Schema;

{
    my $source = Fey::DBIManager::Source->new( ... );

    my $schema =
        Fey::Loader->new( dbh => $source->dbh() )
                   ->make_schema();

    has_schema $schema;

    __PACKAGE__->DBIManager()->add_source($source);
}

MyApp::Schema::Album

package MyApp::Schema::Album;
use MyApp::Schema;
use Fey::ORM::Table;

{
    my $schema = MyApp::Schema->Schema();

    has_table( $schema->table('Album') );

    has_one( $schema->table('Artist') );

    has_many 'tracks' => (
        table    => $schema->table('Track'),
        order_by =>
            [ $schema->table('Track') 
                     ->column('track_number') ],
        cache    => 1,
    );
}

MyApp::Schema::Album

package MyApp::Schema::Album;

transform 'release_date'
    => deflate { blessed $_[1]
                 ? DateTime::Format::Pg
                       ->format_datetime( $_[1] )
                 : $_[1] }
    => inflate { DateTime::Format::Pg
                     ->parse_datetime( $_[1] ) };

MyApp::Schema::Album

package MyApp::Schema::Album;

has_many 'artists' => (
    table       => $schema->table('Artist'),
    # SELECT Artist.*
    # FROM Track JOIN Artist USING (artist_id)
    # WHERE Track.album_id = ?
    select      => ..., # Fey::SQL::Select object
    bind_params => sub { $_[0]->album_id() },
    cache       => 1,
);

MyApp::Schema::Album

• "Select-based" attributes

has 'track_count' => (
    metaclass   => 'FromSelect',
    select      => $select,
    bind_params =>
        sub { $_[0]->album_id() },
);

Constructors

• Constructor works with any candidate key

my $album =
    MyApp::Schema::Album->new(
        album_id => 42 );

Constructors

• Can define your own "load one row" logic
• For example, load a user based on username and password

Constructors

sub _load_from_dbms {
    my $self = shift;
    my $p    = shift;

    return unless exists $p->{password};

    my $select = $self->_select_by_credentials();

    return if
        $self->_get_column_values(
            $select,
            [ $p->{username}, sha512( $p->{password} ) ],
        );

    die 'Invalid username or password';
}

Insert, Update, Delete

my $album = MyApp::Schema::Album->insert(
                title     => 'Realism',
                year      => 2009,
                artist_id => 42,
            );

$album->update( year => 2010 );

$album->delete();

Fey::ORM::Table + Moose

package MyApp::Schema::Album;

use Fey::ORM::Table;

# Plain old Moose attributes
has 'reverse_title' => (
    is      => 'ro',
    isa     => 'Str',
    default => sub { reverse $_[0]->title() },
);

# Plain old Moose roles
with 'MyApp::Role::MusicCollection;

# Plain old Moose method modifiers
before 'update' => sub { ... };

• All the power of Moose, plus more!

Fey::Object::Iterator

• Uses DBI statement handles behind the scenes
• Can return multiple objects in one $iterator->next() call - a join iterator
• Use Fey::SQL in your model to build complex queries

Custom Select

my $select = MyApp::Schema->SQLFactoryClass()->new_select();

my $schema = MyApp::Schema->Schema();

my $track_t = $schema->table('Track');

$select->select($track_t)
       ->where( $track_t->column('album_id'),
                '=', $self->album_id() )
       ->and  ( $track_t->column('has_been_played'),
                '=', 0 )
       ->order_by( $track_t->column('title') );

Custom Select

package MyApp::Schema::Album;

sub unheard_tracks {
    my $self = shift;

    my $select = ...;

    my $dbh =
        MyApp::Schema->DBIManager()
                     ->source_for_sql($select)->dbh();

    return Fey::Object::Iterator::FromSelect->new(
        classes => ['MyApp::Schema::Track'],
        select  => $select,
        dbh => $dbh,
        bind_params => [ $select->bind_params() ],
    );
}

Object Caching

MyApp::Schema->EnableObjectCaches();

my $user1 = MyApp::Schema::User->new( user_id => 42 );
my $user2 = MyApp::Schema::User->new( user_id => 42 );

MyApp::Schema->ClearObjectCaches();

my $user3 = MyApp::Schema::User->new( user_id => 42 );

Transactions

my $trans = sub {
    $user->delete();

    MyApp::Schema::Log->insert( message => 'deleted user' );
};

MyApp::Schema->RunInTransaction($trans);

More Fey::ORM features

• Tries very hard to be efficient
• Minimize # of SQL queries

Fey::ORM MOP

• Full introspection for attributes & methods
• New metaclasses for HasA (has one and has many)
• Like Moose, extendable via the meta API

Fey::ORM is not done!

• Still a work in progress
• Will expand as I use it
• Needs more per-DBMS modules (like Fey::SQL::Pg)
• Either in Fey::ORM, Fey::SQL, or both

Fey::ORM::Mock

• Data seeding and insert/update/delete tracking
• Ever used DBD::Mock?
• It's really annoying!
• Built on top of DBD::Mock

Data seeding

use MyApp::Schema;

my $mock = Fey::ORM::Mock->new(
               schema_class => 'MyApp::Schema' )

$mock->seed_class(
    'MyApp::Schema::Album' =>
        { title  => 'Get Lost',
          artist => 'Magnetic Fields',
        },
        { ... },
);

# next select will see this data

Tracking insert/update/delete

use MyApp::Schema;

my $mock = Fey::ORM::Mock->new(
               schema_class => 'MyApp::Schema' );

my $album = MyApp::Schema::Album->insert(...);
$album->delete();

my $history =
    $mock->recorder()->actions_for('MyApp::Schema::Album');

Learning More

• Best place for help is #fey on irc.perl.org
• Also a mailing list with no traffic yet
• http://www.fey-project.org/ - not much content yet

Thank you

• Questions?