My favorites | Sign in
Project Home Wiki Issues Source
READ-ONLY: This project has been archived. For more information see this post.
Search
for
DBRPresentation  
DBR Presentation for San Diego perl mongers
Updated Nov 18, 2010 by dan...@drjays.com





















DrJays.com Inc. says:
Welcome to San Diego Perl Mongers
Wifi info: SSID iban_conference - Conference code 03A819










































DBR: A sordid tale

( or: How I learned to stop worrying and love the ORM )
DBR is a Database wrapper library; a shameless reinvention of the wheel.
  • A highly focused implementation of an ORM
  • Some interesting bells and whistles.
  • Not trying to be all things to all people
  • "Any customer can have a car painted any color that he wants, so long as it is black."
Intended audience: People with medium to large size projects with a high number of tables / records, and an aversion to pain.
Get the code:

Definitions

  • DBR: Database Repository
  • ORM: Object Relational Mapper(ing)
  • RDBMS: Relational Database Management System (Mysql, PostgreSQL, Oracle, Sybase, etc)
  • OODBMS: Object Oriented Database Management System (ZODB)

What is an ORM anyways?

  • ORM Stands for Object Relational mapper.
  • Eliminates the need for writing SQL
  • Makes an RDBMS act like an OODBMS (Best of both worlds)

Why Not an OODBMS?

  • Because you already have an RDBMS
  • and... Migrating to an OODBMS is a pain in the ass
  • Because you just aren't ready for that kind of commitment
  • No better than an RDBMS when it comes to understanding your data
  • Ad-Hoc SQL queries are handy and powerful
  • They tend to be language dependent
  • They are just not that into you

Why not do it the old fashioned way, with SQL?

  • SQL injection attacks
  • Value quoting has to be perfect
  • Lots of boilerplate code
  • Nobody's stopping you
  • Some of your codebase can use SQL, while the rest can use DBR. They can play nice together.

So... Why DBR?

  • Gets rid of the boilerplate code BoilerPlateCode1
    • 20-40% reduction of overall code volume.
    • Just say NO to cut-and-paste coding.
  • Dramatically reduces Pre/Post processing of data
  • See CodeComparison1
  • Common tasks made easy
  • See CodeComparison1
  • Increases Readability
  • less code is easier-to-understand code
  • Understands your data
  • status 'active' is legit, status 'fubar' is bogus
  • Large queries use less memory
  • Uses blessed arrayrefs internally, yielding a much smaller memory footprint
  • Optimizes queries for you (record prefetch)
  • Stop worrying about executing thousands of tiny queries inside loops, It's taken care of transparently.
  • Value translation
  • Gracefully handles large schemas (hundreds of tables and beyond)
  • Easy and concise syntax
  • Small is beautiful
  • Intelligent field prefetch
  • Who cares what fields you will use... just use them already.
  • Database agnostic
  • Currently supports Mysql and SQLite. PostgreSQL and Oracle support coming soon.

How's it better than those other libraries?

DBR is not better... But it just might be better for you.

Here's why:

RoseDB::Object and DBIx::Class are great libraries, but they are too generic for some jobs, especially if those jobs are big. Furthermore, they:

  • Require Perl code for every... single... table.
  • Lots of tables? have fun hand coding hundreds of modules.
  • They DO Support limited Schema scanning, but kiss most of their features goodbye.
  • Very inefficient queries
    • RoseDB fetches every single field from the DB, even if you only need a few.
    • DBIx::Class executes thousands of tiny selects. One per accessor call.
  • Funky / cumbersome transaction handling
    • RoseDB requires you pass a specially created object to every query to be in the transaction.
    • DBIx::Class requires the creation of a special anonymous sub representing the transaction.
    • DBR is much simpler, all you need to do is a single ->begin, and then a ->commit.
    • Supports nested transactions (emulated for Mysql), and auto rollback.
  • Bulky/Ugly query syntax
    • RoseDB:
    •    Album::Manager->get_albums ( query => price => { lt => 1.00 } ); # Drugs
    • DBIx::Class:
    •    $schema->resultset('Album')-> search( { price =>{ '<' => 1.00 } ); 
    • DBR:
    •    $dbh->album->where( price => LT $value ); #Pfm
  • They Don't manage your hostnames / usernames / passwords
  • DBR does.
  • No value translation or constraints checking
  • But hey, they don't stop you from writing it your dammed self

How does it work?

The concepts:
  • DBR uses a simple conf file and a lightweight config Database.
  • Use DBR Admin to register and scan your existing database schema, Then configure relationships, value translation, etc.
  • DBR Remembers the fields you use in each calling scope of your code, and optimizes field retrieval for next time.
  • Reads ahead up to 1,000 records, anticipating records that it thinks you'll use
  • Queries return lightweight objects, No autoload.
  • Very fast.

Some DBR Features

  • Low bar to entry
  • You can use DBR in two different levels: Basic and Advanced
  • Manages DB instances, and their connect data
  • (Hostname, username, password etc)
  • Relationship aware
  • Automatic constraint checking
  • Stop relying on the database to check your numeric ranges and string lengths (grr mysql)
  • Really easy joins and subqueries
  •    $dbrh->tablenameA->where (myfield => 'foo', 'relationshipB.fieldname' => 'bar');
  • Enums
  • A different approach to value enumeration that you might actually use
  • Smartvalues
  • Certain values returned are actually overload objects... With handy consequences.
    • Datetime fields are cake
    • No, really... it's as easy as falling off the sofa. All your old date mangling/ beautifying code will vanish.
    • Dollar/Percent auto-formatting
  • Novel error handling for chaining
  • Returns false (overload dummy object) when it experiences an error, but won't crash your program with a null ref error.
          $dbrh->artists->where(some_criteria => 'foo')->next->name or die "Badness"
                               Returns false in both places ^      ^
  • Automatic result set chunking
  • Retrieving 1 Miiiillion records? no problem. DBR will retrieve records as you consume them, not all at once.
  • Query read-ahead
  • Stop worrying about query performance for nested loops, start focusing on your code.
  • Ncurses based Admin tool
  • Edit relationships, fields, DB replicas (instances) etc.

DBR Admin

A curses application - Get started in a hurry

┌ DBR Admin Main Menu ───────────────────────────────────────────────────┐
│< Close >                                                               │
│                                                                        │
│Enums                                                                   │
│Schemas                                                                 │
│                                                                        │
│                                                                        │
│                                                                        │
│ ____  ____  ____	 _	 _           _				 │
│|  _ \| __ )|  _ \     / \   __| |_ __ ___ (_)_ __			 │
│| | | |  _ \| |_) |   / _ \ / _` | '_ ` _ \| | '_ \			 │
│| |_| | |_) |  _ <   / ___ \ (_| | | | | | | | | | |			 │
│|____/|____/|_| \_\ /_/   \_\__,_|_| |_| |_|_|_| |_|			 │
│                                                                        │
│Global keys:                                                            │
│Control-Q: Quit                                                         │
│Tab: next input widget                                                  │
│Enter or right-arrow: select                                            │
│Up/Down arrows: previous/next item in current input widget		 │
│Mouse supported depending on OS (Linux - probably, Mac - probably not)  │
└────────────────────────────────────────────────────────────────────────┘

Value Translation

Value translation is very important. Most pre and post processing code is dedicated to translating values going into or coming out of the database.

Some very handy value translators

  • UnixTime
  • Dollars
  • Enum
  • Percent

What is an Enum?

Enumerated values are very handy, because:

  • They are much like constants for a database field
  • Great for status fields, and more.
  • Using numbers hinders readability
  • Mysql enums suck
    • nonstandard datatype
    • no fancy value for presentation/listing to the user
  • DBR Enums are useful because they are three part: (id, handle, Fancy Name) not two.

Smartvalue examples

ALL of these translators return "smart" overload objects that morph into the best value for the job at hand. Some examples:

  • print $dollars
  • prints: $1,000.00
  • $dollars == 1000.00
  • TRUE
  • print $somedate
  • prints 09/16/08 08:51:31 PST
  • $somedate == 1221580291
  • TRUE
  • print $somedate + '7 days'
  • prints: 09/23/08 08:51:31 PST
  • print $somedate->midnight
  • prints 09/16/08 00:00:00 PST
  • print $somedate->endofday
  • prints 09/16/08 23:59:59 PST
  • print $some_enum_value
  • prints: Ready to ship
  • $some_enum_value eq 'shipready'
  • TRUE
  • $some_enum_value->in('shipready shipped settled')
  • TRUE

And so on...

See ObjectsAndMethods

Query Optimization

Field prefetch

DBR automatically profiles the fields you use. It saves this information and uses it in subsequent queries. the below code results in exactly one DB query

# get all the cars salesman #3 sold
my $cars = $dbrh->cars->where(salesman_id => 3);

while( my $car = $cars->next ){
   print $car->VIN . "\n";
}

Relationship Prefetch

When calling a relationship accessor, DBR reads ahead and fetches the relationship records it expects you'll use. The below code results in exactly Two DB queries.

# get all the cars salesman #3 sold
my $cars = $dbrh->cars->where(salesman_id => 3);

while( my $car = $cars->next ){
   print $car->VIN . "\n";

   while(my $option = $car->options->next){ #Just works the way it should
        print "\t" . $option->name . "\n";
   }
}

More Examples

See examples directory

Future Enhancements

  • Built in attributes
  • Dynamic fields attached to an object, stored in a records-based table structure.
  • Simpler Many to many relationships
  • Field aliases
  • Macros
  • Password value translator

Q&A Time

Ready... GO

Powered by Google Project Hosting