1. Mango 0.01000_12 Released
2. Five a day, May 10
3. Five a day, part 3
4. (Five a day) x 2
5. Catalyst book code
6. Rose::HTML::Form and DBIx::Class
7. Spring 2008 - a UKUUG Conference Review
8. Catalyst - Accelerating Perl Web Application Development
9. Writing Facebook apps in Catalyst
10. Announcing Osgood: An Event Repository
11. Hanging Antlers On Your ResultSets
12. Ayalike: Content Management using Catalyst
13. Refreshing a row from the DB - another note to myself
14. Automatic DBIC schema creation from a database
15. Mango 0.01000_10 Released
16. Catalyst Cologne: My Wife FTW
17. Introducing App::TemplateServer
18. Mango 0.01000_07 Released
19. New Year. New Progress.
20. WoC part two: Database and Domain Design
21. Vienna WoC TODO Manager series on shadowcat.co.uk
22. A Christmas Catalyst Site: I Watch Soccer
23. Update to 'Tags and search and DBIx::Class'
24. Catalyst Book
25. Have you ever forgotten where some subroutine comes from?
26. Database Quoting with DBIx::Class
27. Visualizing your DBIC schema
28. Catalyst Tips: Part 1
29. Catalyst::Controller::reCAPTCHA
30. WIAB hits version 0.02
31. Catalyst::Example::InstantCRUD updated to newer versions of underlying libraries
32. Mango 0.01000_06 Released
33. Database Abstraction - code vs infrastructure
34. Catalyst and DBIx::Class: Part 1
35. Sorry for the Delay
36. DBIx-Class-QueryLog 1.0.0 Released
37. High Availability using Catalyst & FastCGI external serv
38. The Catalyst hosting web page
39. Catalyst Integration: Day 99
40. DBIx::Class - virtual columns?
41. Fat Model
42. Handel 1.0 Released
43. Handel 0.99_19 Released
44. Catalyst swag to hide your shame
45. DBIx::Class 0.08001 released (and a podcast)
46. Single Sign-On (SSO) with and without subdomains
47. Perl, singletons, DAOs oh my!
48. Catalyst Javascript Testbed
49. Mango 0.01000_04 Released
50. Catalyst Integration: Day 26

0.01000_12 Mon May 12 21:18:25 2008
    - Fixed various controllers to use Chained instead of index : Private
    - Converted Live Catalyst tests to Test::Class and added tests for path config option and subclass renames
    - Added RSS tests to live tests
    - Added Admin tests

0.01000_11 Sun Apr 13 20:39:27 2008
    - Added just enough pod to get spelling/coverage happy. Needs serious cleanup.
    - Added critic tests and set perltidy standard for dist
    - Refactor View::Feed. Needs more tests.

Yesterday I released Osgood::Client and today, after realizing I had botched the upload, Osgood::Server. Unfortunately the documentation is a little thin, so I’ll take this opportunity to both inform the world and the module of it’s purpose.

Osgood is a passive, persistent, stateless event repository. The current docs say queue rather than repository but we’ve decided it’s a bit of a misnomer. A primer on the aforementioned explanation:

• Passive: Osgood doesn’t seek out your events, it only waits for notification and query
• Stateless: Querying an event does not change it
• Repository: Once you’ve inserted an event it stays there forever

So what’s that mean? Osgood (::Server) is a system wherein you record the fact that something happened. A client library is provided that allows you to do just that (::Client). You can also query the server to ask it what has happened and it will inform you. I believe an example is in order.

Magazines.com’s backend is basically a big order pipeline after the order is taken. We are phasing out an AS/400 which is currently handling fulfillment of orders. If a customer calls us and wants to cancel a magazine, our our order entry system cancels the order and then needs to notify the AS/400. We have an audit table for order line items, so we would query that table for fresh cancels and send them to the 400 to finish the process.

All was well! Then our Marketing team approached us with a good idea: Send customers who’ve canceled an email enticing them back! It was a great idea until we released that we basically had one shot in our aforementioned setup to send a cancel, and we were using it. There was no way to keep up with the status. Had we sent it to the 400? What about to our email service provider? Were there other things we might do in the future? Osgood was born.

Osgood is based around Events. You use ::Client to send an event to the Osgood server, like so:

my $event = new Osgood::Event(
	object => 'Moose',
	action => 'farted',
	date_occurred => DateTime->now(),
	params => {
		name => 'Hector'
	}
);

Events are composed of an object, an action and a time. These fields are all stored as VARCHAR(64)s, so you can put anything you like up to that size.

The optional params accepts a hashref of name value pairs. This allows storage of information that doesn’t fit Osgood’s Object/Event setup, but that you might need when using the event. A good example would be the primary key of the specific Moose that farted. Note that you can also use get_param and set_param on an Event object.

my $list = new Osgood::EventList(events => [ $event ])
my $client = new Osgood::Client(
	url => 'http://localhost',
	list => $list
);
my $retval = $client->send();
if($list->size() == $retval) {
	print "Success :)\n";
} else {
	print "Failure :(\n";
}

You can put as many events into an EventList as you like. Then you create a Client and send it! Above we check that send returned a number that matches the events we sent.

That’s all well and good, but how do you use what you’ve stored? The client also works the other way around:

my $retval = $client->query({
     object => 'Moose',
     action => 'farted',
});
 
if($retval) {
	# Gets an EventList
	my $list = $client->list();
	my $iterator = $list->iterator();
	while($iterator->has_next()) {
		my $event = $iterator->next();
		print $event->get_param('name')." farted at ".$event->date_occurred()."\n";
	}
} else {
	print "Oh noes!\n";
}

We asked Osgood for all the Moose/farted events and we got them back. One note of interest is that Events are assigned an ID when they are stored, and that ID is present when you get them in a query. This will come in useful later.

The query method supports many other options as of this writing:

• date_after: date after the specified one
• date_before: date before the specified one
• id: all events with an id greater than the one specified
• limit: only return the specified number of events

So back to our original problem. Rather than make our internal cancel process complex, we simply create a row in a queue table which is emptied frequently and sent to Osgood. Our backend processing jobs then query Osgood using the id parameter to query to find every event that was created since the last one it processed. Now we can create an unlimited number of backend jobs that work from the same event. Our email sending and publisher notification of cancels happen independently.

This work was inspired by past work with Publisher/Subscriber systems and message queues like the venerable JMS. It bears very little resemblance to those ideas, but one might be able to squint a bit and see the similarity.

Osgood::Server is a Catalyst application using the XML::REST plugin and ::Client is Moose based with some help from MooseX::Iterator and some XML::XPath and XML::DOM magic for serializing and deserializing the eventlist.

If you have any questions, feel free to drop me an email or hit me up on irc.perl.org as gphat.

At some point in the not too distant future, the intarweb’s magazine consumers will be perusing a new magazines.com. I’ve got the responsibility of building it. One of the requirements is to allow our Merchandising and Marketing folk to get their jobs done without too much fuss. The system they use will consume sets of products. They might want to build a set of magazines in a certain category, under a certain price. What kind of tool am I talking about?

A Query Builder.

I can hear your eyes rolling already. We’ve all written them. But I’m going to share an extra-cool method for doing so.

Here’s the list of ingredients you’ll need.

• Your existing DBIC application.
• Experience with custom resultsets.
• An understanding of chained resultset usage.
• Moose
• MooseX::Method

Now that you’ve collected your ingredients, let’s prepare them.

First, note that we aren’t allowing our end users to build literal queries in this recipe. We are going to allow them to chain calls to our resultset. This is a much better option, in my opinion. A lot of the complexity can be removed by simplifying some heinous or dangerous SQL into a simple resultset method. With the help of Moose we will create a custom resultset that allows us to introspect the methods available for building and show them to the user automatically.

Now, we’ll be working on a Product example. Our end user needs to build a query that finds all the products in a price range, of a certain type and in a certain category.

Since you’re already versed in custom resultsets I’ll just show you the finished product:

package Our::ResultSet;
use Moose;
use MooseX::Method;
use MooseX::Util::TypeConstraints;
 
extends 'DBIx::Class::ResultSet';
 
subtype 'Currency'
	=> as 'Object'
	=> where { $_->isa('Math::Currency') };
 
coerce 'Currency'
	=> from 'Num'
		=> via { Math::Currency->new($_) };
 
method price => named (
	operator => { isa => 'Comparator', required => 1 },
	amount	=> { isa => 'Currency', required => 1, coerce => 1 }
) => sub {
	my ($self, $args) = @_;
 
	if($args->{'operator'} eq '=') {
		return $self->search({ price => $args->{'amount'}->as_float() });
	}
 
	return $self->search({
		price => {
			$args->{'operator'} => $args->{'amount'}->as_float()
		}
	});
};

That’s a lot of code, but the concept is very simple: Create your resultset’s methods with MooseX::Method and then you can do the following magic:

my $rs = $schema->resultset('Product');
my $map = $rs->meta->get_method_map();
foreach my $method (keys(%{ $map })) {
	# See MooseX::Method docs for what to do with this.
	my $signature = $method->signature();
	# Profit!
}

Having the method’s ’signature’ allows you to get a list of the parameters that the method expects, as well as the types and ‘required’ status. That sounds like the recipe for some go-getting developer to construct a query building tool!

One warning. MooseX::Method currently lacks a permanent API for acquiring this information. The current API is to call export on the signature. You can Dumper the resulting hashref and see pretty easily how to get what you need. Keep an eye on The CPAN or in irc.perl.org’s #moose for updates.

So to use this recipe, you’ll have to construct your own mechanism for taking the above information and generating a UI for your application. I got my Catalyst based query-builder working this afternoon.

If you have any questions feel free to drop into irc.perl.org and shoot a message to gphat. You can find me in #moose.

UPDATE: The demo and trac links are down atm, as Ayalike is being moved to new hosting.

I’ve been hacking away for the last week or so on a content management system (henceforth CMS). We are going to need a CMS for some future $work projects and I’m generally unhappy with all that I’ve reviewed. So many of the existing ’solutions’ seem to be more in the business of providing you with a ‘platform’. Most of the folks I know build platforms for a living, we need only find a way to manage the content.

So with that in mind, Ayalike (pronounced like ‘a uh like’) aims to provide the following features:

• Multisite, multiuser, multirole
• Output agnostic
• Versioning
• Pluggable publishing
• Output processing

Three of those features are complete. Publishing works, but is currently not pluggable. Output processing is there as well, but I’ve yet to hook it into anything but unit tests.

That’s all kind of abstract, here’s what I’ve done in a more concrete form: This week I modified one of our internal applications to use a custom Template Toolkit provider that communicated with Ayalike and provided ‘preview’ functionality on the live site without changes to the application itself. The application functioned normally, rendering compiled templates from the filesystem, unless given a special parameter. In that case it requested the entry from Ayalike over HTTP using it’s wiki-esque page viewing feature. Obviously real apps would protect this feature with logins.

I’m not quite ready to tout it on the mailing lists or in IRC yet, but I’m very excited about the project and I hope that it might become the solution for my future work projects. Regardless, Catalyst needs a CMS.

Are you interested? Visit the Trac site. There’s even a working demo, cleared every half hour.

0.01000_10 Fri Jan 18 22:27:19 2008
    - Added Atom/RSS Feeds to Wishlists
    - Added Atom/RSS Feeds to Products(tags)
    - Added Feed() action attribute and enable_(feeds|atom|rss)_feed
    - Fixed REST->entity() to use new config
    - Stop importing blessed in Feed View

0.01000_07 Mon Jan 7 19:07:23 2008
    - Added Catalyst::View::TT/Session/Cookie/Store to PREREQ
    - Fixed problem in tests with latest Error.pm
    - Updated XML::Atom PREREQ and test
    - Mango->share now uses module_dir and falls back to ../../share when using
        local INC files
    - Added mango.pl to create app/config/data/components/database
    - Moved users specific controller/form/templates into Users/users namespace
    - Fixed urls to deal with not ending in /
    - Updating an Attribute wasn't setting update()
    - Converted admin to RESTish access
    - Controllers now inherit from M::Catalyst:Controller (which does REST/Form
    - Merge plugins into Plugin::Application
    - Added Auth helpers is_admin/unauthorized
    - Added REST helpers want_html/browser/yaml/feed, etc
    - Form I18N is now FIELD_LABEL_$FIELD instead of LABEL_$FIELD
    - Form I18N is now BUTTON_LABEL_$FIELD instead of LABEL_$FIELD
    - Form I18N is now CONSTRAINT_$CONSTRAINT instead of $FIELD_$CONTSTRAINT
    - Wishlist admin works mostly, with tests

My Catalyst book is out and is beginning to appear in the hands of fellow Catalyst users! You can buy it from Amazon and local book stores, but it looks like the publisher (Packt) has the best price right now.

Basically, this book covers getting started with Catalyst. The goal is to take you from "what's MVC?" to being able to write complete web applications with Catalyst. We'll build a few complete applications including an address book, a mini-blog, and ChatStat (which is basically Ircxory but with a Template Toolkit interface instead of one built with Template::Declare).

Along the way you'll learn all about Catalyst, DBIx::Class, and the Template Toolkit. I cover REST interfaces, AJAX (with Jemplate), RSS, testing, deployment, and more. The book is very hands-on oriented; I usually explain what sort of code I want to write, write the code, and then explain what the code does. If necessary, we look at theoretical examples of when related features would be useful, and then move on to the next piece of code.

The full PR blurb is on the book's website, so please take a look.

So for a column called output I would CONCAT to it like so:

$row->update( { output => \"CONCAT(output, $new_stuff)" } );

Which is groovy as long as $new_stuff didn't contain single quotes or question marks. Single quotes throw off database quoting and question marks throw off DBI...

So there's a 'quote' function available via DBI which takes care of the single quotes - usually - but completely ignores question marks. Look at it in DBI.pm - it's pretty, um, bare-bones.

So I would call DBI's quote function & then use an ugly regex to prepend all question mark's that didn't already begin with a backslash with a backslash - and this worked - mostly.

There were several problem that would crop up intermittently & of course always at the exact wrong time. If a single quote was already preceded by a backslash calling DBI's quote made it worse & the update would fail (note DBI's quote function replaces all single quotes with 2 single quotes & then puts single quotes around the entire string.)

I also had problem with my regex seemingly not working in all cases to properly hide the question marks from DBI & would get errors occasionally about providing too many or too few parameters for the given number of question marks.

Welp after just dealing with the errors that would occasionally pop up I finally got off my butt & came up with a quoting fix that so far as been 100% reliable for any combinations of single quotes, question marks, & backslashes before them.

The secret is to totally get rid of those character & replace them with char(39) and char(63) for single quote and question mark respectively.

The deal is to delete any backslashes before any single quote or question marks in the output, the replace those character with their 'char' equivalents in-between single quotes and commas (as CONCAT can take multiple values).

Here's what it looks like:

sub concat_db {
my($row, $column, $value) = @_;

# get rid of an backshlased single quotes
$value =~ s/\\'/'/g;

# clean up single quotes
$value =~ s/'/', char(39), '/g;

# clean up '?'s
$value =~ s/\\\?/?/g;
$value =~ s/\?/', char(63), '/g;

# concat it
$row->update({ $column => \"CONCAT($column, '$value')" });
}


I put this code in my top-level DBIC::Schema class & call it thusly:

MYDB::concat_db($row, 'output', "My wacky \'output\' ??? 'yoyo' ");

& magically the correct quoting happens.

Here's what that string looks like after its been quoted:

'My wacky ', char(39), 'output', char(39), ' ', char(63), '', char(63), '', char(63), ' ', char(39), 'yoyo', char(39), ''

Note the first & last single quote get put there in the CONCAT statement itself.

Happy Quoting!!

It's Catalyst::Controller::reCAPTCHA on cpan and comes with documentation and a simple example application. If anyone wants to add patches for Captcha::reCAPTCHA::Mailhide api, feel free :-)

By the way, watch out for testing this on your dev box - I got myself banned from reCaptcha for several hours by posting continually failing recaptchas.

• Fixed Template->share when Mango has never been installed
• Stripped action from yml forms, now get path_prefix on the fly
• Base Controllers have form_directory set to cope with subclass rename
• Cart Controller works with add/update/clear/save/delete
• Added Forms plugin companion to Forms controller
• Cart forms is not build from sub-forms

• Form->render now localizes field labels/submit
• Views now fall back to auto/Mango/templates
• Added Form/FormFile attributes to Form controller

I've worked on a number of database-driven projects and no matter how much people want database abstraction, it was always difficult to code and maintain. I was recently reminded of this when I read this Drupal article on dropping PostgreSQL support. Not only can it be difficult to maintain support for multiple databases, but it may be difficult to find developers.

One solution of modern programming is to move database abstraction from the code to the infrastructure using a ORM (Object-Relational Mapper) or Data Mapper. A ORM and Data Mapper abstracts the database for you so you no longer have to do tie db abstraction to each app. Not only does it let you code once for multiple databases it lets your users migrate their data from one database to another. This blog runs Typo which is based on Ruby on Rails and ActiveRecord. I've been contemplating migrating Typo from MySQL to PostgreSQL and I've been told that it would be as simple as exporting the data with YAML, updating the database.yml file and importing the data. I haven't gotten around to doing it yet but it is a powerful idea. ActiveRecord is a data mapper and isn't as flexible as a full blown ORM but it gets the job done for the most part. For a full-blown ORM, I think of Perl's DBIx::Class which provides a full OO interface to the RDBMS allowing you to code just once for multiple DBs without limiting you when you want to use some esoteric database-specific SQL. DBIx::Class is often used with the Catalyst Framework but is also used by itself.

There are PHP frameworks out there like Symfony and Cake but do any of them have stand-alone ORMs? If so, could Drupal move to something like that and solve their maintainership problems once and for all? Drupal is part of the Go PHP5 effort so there should be no issue using PHP 5 OO. Something to think about for the Drupal folks if a PHP ORM is available.

Understanding ORM

Object-Relational mapping (aka O/RM, ORM, and O/R mapping) is a programming technique for converting data between incompatible type systems in databases and Object-oriented programming languages. This creates, in effect, a "virtual object database" which can be used from within the programming language.

Getting to know DBIx::Class

Using DBIx::Class in Catalyst

$ catalyst.pl MyTestApp

$ cd MyTestApp
$ ./script/mytestapp_create.pl model DB DBIC::Schema DB::Schema create=static dbi:Pg:dbname=ticketingsystem chris
 exists "/home/chris/MyTestApp/script/../lib/MyTestApp/Model"
 exists "/home/chris/MyTestApp/script/../t"
Dumping manual schema for DB::Schema to directory /home/chris/MyTestApp/script/../lib ...
Schema dump completed.
created "/home/chris/MyTestApp/script/../lib/MyTestApp/Model/DB.pm"
created "/home/chris/MyTestApp/script/../t/model_DB.t"
$ 

$ head -13 lib/MyTestApp/Model/DB.pm
package MyTestApp::Model::DB;

use strict;
use base 'Catalyst::Model::DBIC::Schema';

__PACKAGE__->config(
    schema_class => 'DB::Schema',
    connect_info => [
        'dbi:Pg:dbname=ticketingsystem',
        'chris',
        
    ],
);
$ 

$ cat lib/DB/Schema.pm 
package DB::Schema;

# Created by DBIx::Class::Schema::Loader v0.03009 @ 2007-09-04 19:26:55

use strict;
use warnings;

use base 'DBIx::Class::Schema';

__PACKAGE__->load_classes;

1;

$ 

$ cat lib/DB/Schema/Status.pm 
package DB::Schema::Status;

# Created by DBIx::Class::Schema::Loader v0.03009 @ 2007-09-04 19:26:55

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("status");
__PACKAGE__->add_columns(
  "id",
  {
    data_type => "integer",
    default_value => "nextval('status_id_seq'::regclass)",
    is_nullable => 0,
    size => 4,
  },
  "name",
  {
    data_type => "character varying",
    default_value => undef,
    is_nullable => 0,
    size => 40,
  },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many(
  "users_ticket_status_logs",
  "DB::Schema::UsersTicketStatusLog",
  { "foreign.status_id" => "self.id" },
);
__PACKAGE__->has_many(
  "staff_ticket_status_logs",
  "DB::Schema::StaffTicketStatusLog",
  { "foreign.status_id" => "self.id" },
);
__PACKAGE__->has_many(
  "tickets",
  "DB::Schema::Ticket",
  { "foreign.status_id" => "self.id" },
);

1;

• load_components: Loads the components you need, which should almost always be the same (PK::Auto and Core) unless you need custom components detailed in the DBIx::Class component manual. [technically I guess you don't need PK::Auto as it's in Core now]


• table: obviously sets the name of the table


• add_columns: defines the columns of the table


• set_primary_key: sets the primary key of the table


• has_many: one of a few relationship definition functions for the table, see more about them in detail here.

Summary

I just uploaded the 1.0.0 release of DBIx::Class::QueryLog to the CPAN.

The 1.0.0 label is mostly just a not to it’s maturity. I’ve been using QueryLog extensively at $job — most recently to tune a multi-million order conversion script — and a 1.0 is in order.

The only new feature is a refactoring of the ‘analysis’ code into a seperate object, as well as a new mode that totals up queries, combinining same queries and keeping a count and total time for all executions.

Have fun!