DBIx::Class and SQL::Abstract

So, I've been working on this database driven website for a while and I'm rather surprised it took me so long to trip over this problem. How do you write an Oracle date range query with DBIx:Class? (The answer is not, as some might have it "stop using oracle".)

The sql is simple enough, but it has the inherent challenge for an ORM of needing to pass functions into the where clause.

select * from mytable where mydate between to_date('20090922 00:00:00', 'YYYYMMYY HH24:MI:SS') and to_date('20090922 23:59:59', 'YYYYMMYY HH24:MI:SS')

The DBIx:Class Cookbook talks a bit about calling functions from a select clause, but doesn't specifically address the where clause as different from the select clause. And between is sort of a funny case anyhow. A look at SQL::Abstract on which DBIx::Class's where clauses are based yielded examples which *help*. You're supposed to be able to use a reference to an array ref to do something like this:

fieldname =>{$operator, \[$function_snippet => @values_to_plug_in]}

But my code attempting multiple variations on this with the between keyword was not working.
So I sent email to the DBIx::Class folks, and sure enough. Looks like there was a bug in SQL::Abstract. Which got swatted almost instantly. Go Peter Rabbitson.

The following code is a working example of a date range query where start and end are dates passed in.

my $rs = $schema->resultset('User_Read_Log')->search(
{
datetime => {
"between",
[
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$start 00:00:00" ],
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$end 00:00:00" ]
]
}

This is current and working as of SQL::Abstract to 1.60.

More indignities in web development

Here I am putting off my perl blog entry until the last day of the week again. And then not posting about perl. But it's about web dev, which matters. Recently there's been a lot of coverage of the fact that IE must die. Here's something you can use to block its use.

I promise next week will be more explicitly about perl. But inasmuch as I've been fighting IE6 in my css all week, this is where my head is. And yes, IE must die.

Template Toolkit Tip for the Week

For the love of god, why did it take me 3 months to find a command to chomp white space? You're sick unto death of having to view the source of your html after a template has produced it and it's full of blank lines? Follow the link. All you need to do is add a "-" to your template toolkit markup.

[% some command here %]

becomes

[% some command here -%]

I suppose the overarching meta point here? Read documentation even when you don't think you need it directly for the task at hand. You'll find all sorts of goodies in there.

Catalyst and DBIX::Class tip

I hadn't been using DBIX::Class with Catalyst very long before I realized that stuffing an array of Row objects into a session was a Bad Idea. a. You probably don't need all that schema and database state info that comes with it just to load your data into your html. b. you probably don't really want to try to stuff a live connection to a database into your session object and c. the things take up a boat load of session memory.

Although the DBIX::Class docs are actually pretty good it took me a while to come up with the answer:

DBIx::Class::ResultClass::HashRefInflator. If all you need is your data stuffed into a hashref and you're not using prefetches, this is what you want to use to keep your results light weight.

Sample code below is simple and works like a charm:

use DBIx::Class::ResultClass::HashRefInflator;

my $rs = $schema->resultset('CD');
$rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
while (my $hashref = $rs->next) {
...
}

Open mouth insert foot.

So I was at YAPC:NA, which for people new to perl, is probably both the cheapest and highest quality perl conference in North America and you should totally go. And I was sitting at lunch with, among other people, Richard Dice the President of The Perl Foundation. We talked about all sorts of things. Kids daycare in Canada versus the US. Finding time to volunteer in the perl community. You know, lunch and new acquaintance blather. When it came to which talk we were going to next, I found myself uttering the words "Nah... I'm not going to [some talk]. It's about perl6 which is sort of irrelevant."

The table got quiet. Not crickets and staring at me quiet, but definitely "Yea. You're not actually as smart as I thought you were" quiet. Because saying perl6 is irrelevant to the President of The Perl Foundation.... well. It's sorta like telling a mother her baby is ugly. Both pointless and rude.

So herewith, I take it back. Your baby's not ugly. I'm just way more enamored of ways of making perl5 take on some of the utility and cleanness of perl6 than I am of getting to use perl6 since I can't actually use it yet. To mix metaphors, I hate admiring candy I don't get to eat yet. And oddly though I like other people's children, I'm not fond of their baby pictures much either.

Yet Another Perl Blog

Ho look. It's a "mission statement". Why is this blog here? Welp. This blog is here for a couple of reasons. First, perl is this crazy annoying unique wonderful language. It's a programming language with what I can only call slang. It's got more tribal knowledge and cultural nuance than anything else I've ever worked in, for good and ill. It's got interest, flexibility power, and, quite notoriously, enough rope to hang oneself on. Perl creates a fascinating meta-problem. (I'm all about fabulous meta problems.) How do you learn a really rich and nuanced language quickly and well?

I must've picked up and put down perl at least 4 times over the years. I'll ramp up, do a medium sized object oriented project and then put it down again and not program with it for months until I'm forced to actually maintain the stuff I wrote at which time I'm reviewing dereferencing syntax yet again. I'm spending a stupid amount of time re-learning things. And THIS leaves me wondering how to do it each time a little better and a little faster.

This blog is flatly not for the 4000 cpan contributors in the world. This is for people who have to learn perl and are drowning the "There's More than One Way to do It" philosophy. This blog is for people who maybe heard about Modern Perl, Enlightened Perl, and perl5i and wonder why the heck they'd want to use any of them (and maybe you don't). This blog is for people who have never dropped into irc to ask for help and think if they did mst might eat them. This is also very definitely for people who have no idea who mst is.

Speaking of said surly bastard, I refuse on general principle to admit to myself that I might actually be doing this to participate in his iron man challenge. I'm hanging on to my delusions that I did this without a kick in the pants. I've thought about doing this for over a year. But I'll be trying hard to update it weekly.

So this is first post. Welcome people. It's Yet Another Perl Blog. Which, if you work in perl, you'll know is just about the most boring and uncreative blog name EVER.