Square pegs and round holes

1
Square pegs and round holes

• A reflection on ClassDBI

2
Who am I?

• Developer at Monash University
• Applications in and related to the staff and
  student portal
• Built upon open source
• Perl, HTMLMason, Apache

3
ClassDBI

• Database abstraction layer for Perl
• Allows mapping of classes to database tables
• Each row of the table is an instance of the class

4
The square peg
5
Initial development

• Bulk written in Perl, remainder PL/SQL and Java
• Chose ClassDBI as it gave us a leg up due to
  time constraint
• But not as much as we expected

6
Clash of standards

• MusicCD-gthas_a(
• artist gt 'MusicArtist
• )
• print cd-gtartist-gtname

7
Clash of standards

• MusicCD-gthas_a(
• artist_id gt 'MusicArtist
• )
• print cd-gtartist_id-gtname

8
Clash of standards

• MusicCD-gthas_a(
• artist_id gt 'MusicArtist
• )
• artist \artist_id
• print cd-gtartist-gtname

9
Clash of standards

• MusicCD-gthas_a(
• artist_id gt 'MusicArtist
• )
• get_artist \get_artist_id
• set_artist \set_artist_id
• print cd-gtget_artist-gtname

10
Playing nice with friends

• Our expectation was for error at runtime
• If remote service is not available
• Module loads
• Only when used is error indicated
• By return value or an exception

11
Playing nice with friends

• ClassDBI connects to database, and errors, at
  module load
• Which mean an error at load paradigm
• If database is unavailable
• Module will not load
• Thus stopping remaining code from loading
• We needed to be more defensive than usual and
  wrap the use in an eval

12
I canna do it captain!

• Takes XML extract of all user accounts - upwards
  of 90,000
• Compares against data in database
• Updates accordingly

13
First attempt

• Run through extract and call retrieve() for each
  record
• 90,000 database queries
• Database bottleneck
• Runtime of over an hour

14
Second attempt

• Load all objects into memory via retrieve_all()
• Single database query
• no longer the bottleneck
• Now encounter memory allocation issues
• Still a runtime of over an hour

15
Third attempt

• Avoid ClassDBI and use DBI directly
• Single database query gave all records in an
  array of hash references
• Then we only call retrieve() if a change is
  required
• Runtime of less than ten minutes

16
Fourth attempt

• Did not try as third attempt gave satisfactory
  results
• Would have probably loaded entire extract and
  then used iterator from retrieve_all()

17
Overview of initial development

• A success
• Built working system in a few weeks
• ClassDBI gave significant head start. Although
  we needed to
• learn how to use it
• bend to our standards
• avoid in some cases for performance

18
Maintenance and enhancement

• Now been running for over two years
• Huge increase in usage
• Second largest installation in the world
• Also expansion of functionality
• Can at times be frustrating

19
Where is the object?

• print section-gtget_course()
• Many hours wasted
• A number is printed
• Not the expected blessed referent
• Due to stringification feature of ClassDBI
• That we had not mentioned in our documentation

20
Unforeseen implications

• Extended update(), create() and delete() to
  require a username for an audit trail
• object-gtdelete(username)
• Worked well
• Until we tried to use features such as cascade
  delete

21
Unforeseen implications

• ClassDBI follows has many relationships and
  calls delete()
• Does not know to pass our username
• Which makes it fail
• Our solution to a business need limited the
  features we could use
• Better knowledge of ClassDBI internals should
  provide a better solution

22
Building a round peg
23
Development

• All Perl solution
• Except for Oracle database
• Perl CGI for public website
• Perl under HTMLMason for administration
  interface
• Perl for batch load process

24
ClassDBI or not ClassDBI

• Our data model led to a matching set of classes
  and database schema
• But
• ClassDBI was not available on the target
  environment
• Recent issues (at the time) soured our opinion
  of ClassDBI
• Decided to build our classes from scratch

25
It lives

• Quickly became apparent that some form of
  framework was needed.
• So we built our own

26
Basic setup

• use base 'Base'
• __PACKAGE__-gt_set_table_name( table' )
• __PACKAGE__-gt_setup_attribtes(
• qw code name ... ,
• )

27
has_a relationship

• __PACKAGE__-gt_setup_has_a_relationship(
• 'class' gt 'Faculty',
• 'get_method' gt 'get_faculty',
• 'set_method' gt 'set_faculty',
• )

28
has_many relationship

• __PACKAGE__-gt_setup_has_many_relationship(
• 'class' gt 'OtherClass',
• 'table' gt 'other_table',
• 'get_method' gt 'get_others',
• 'add_method' gt 'add_other',
• 'remove_method' gt 'remove_other',
• )

29
has_many relationship

• __PACKAGE__-gt_setup_has_many_relationship(
• 'class' gt 'OtherClass',
• 'table' gt 'other_table',
• 'get_method' gt 'get_others',
• )

30
Compensating for issues

• Built to expect our standards
• Follows error at runtime paradigm
• Still need to bypass in some cases
• Only built what we needed/wanted
• So no stringification to primary key
• Natively requires username for audit trail

31
Was it worth it?

• Course Finder project was a success
• Framework allowed later changes to be
  implemented quickly
• We knew exactly how our framework worked

32
Would we do it again?

• No.
• Because we can reuse this framework
• Which we have done in one other project and fed
  enhancements back

33
Another peg

• There are other database abstraction frameworks
• DBIxClass is biggest alternative
• Either didnt exist or appear on our radar at the
  time
• They are not excluded for future projects

34
Conclusion

• I dont really have one

35
Conclusion

• Any framework is beneficial
• But you need one that does what you need
• And you will need to bypass it

36
Questions?
37

• Blackboard Learning System Vista Enterprise
  License Release 3 Patch something or other.
  Potato!

38
This slide left intentionally blank