Mailing List Archive: Catalyst: Users

DB Functions in the Schema

Index | Next | Previous | View Threaded

trevor.phillips at gmail

Aug 17, 2009, 1:12 AM

Post #1 of 6 (1096 views)
Permalink

DB Functions in the Schema

Is there an easy way to represent a DB function call as a named column
within a DBIx::Class schema?

eg;

If at the DB level I would do something like:
select updated, from_unixtime(updated) as updated2 from mytable;
... can I have;

__PACKAGE__->add_columns(
"updated", { data_type => "TIMESTAMP" }
"updated2", { something => "from_unixtime(updated)", etc...}
...
);

Thanks.

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

frioux at gmail

Aug 17, 2009, 6:31 AM

Post #2 of 6 (1026 views)
Permalink

Re: DB Functions in the Schema [In reply to]

I don't know the answer to this off the top of my head, but I do know that
you mailed it to the wrong people :-) You are much more likey to get a good
answer if you send this to the DBIC ML.

Good luck!

On Aug 17, 2009 3:13 AM, "Trevor Phillips" <trevor.phillips [at] gmail>
wrote:

Is there an easy way to represent a DB function call as a named column
within a DBIx::Class schema?

eg;

If at the DB level I would do something like:
select updated, from_unixtime(updated) as updated2 from mytable;
... can I have;

__PACKAGE__->add_columns(
"updated", { data_type => "TIMESTAMP" }
"updated2", { something => "from_unixtime(updated)", etc...}
...
);

Thanks.

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

lee at laylward

Aug 17, 2009, 8:41 AM

Post #3 of 6 (1022 views)
Permalink

Re: DB Functions in the Schema [In reply to]

I have done stuff like that in the past by using the
DBIx::Class::DynamicDefault module. There may be a better way to do it,
though, so it is probably worth asking on the DBIC mailing list.

--
Lee

On Mon, 17 Aug 2009 16:12:58 +0800
Trevor Phillips <trevor.phillips [at] gmail> wrote:

> Is there an easy way to represent a DB function call as a named column
> within a DBIx::Class schema?
>
> eg;
>
> If at the DB level I would do something like:
> select updated, from_unixtime(updated) as updated2 from mytable;
> ... can I have;
>
> __PACKAGE__->add_columns(
> "updated", { data_type => "TIMESTAMP" }
> "updated2", { something => "from_unixtime(updated)", etc...}
> ...
> );
>
> Thanks.
>

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

trevor.phillips at gmail

Aug 17, 2009, 7:22 PM

Post #4 of 6 (1013 views)
Permalink

Re: DB Functions in the Schema [In reply to]

On Mon, Aug 17, 2009 at 9:31 PM, fREW Schmidt<frioux [at] gmail> wrote:
> I don't know the answer to this off the top of my head, but I do know that
> you mailed it to the wrong people :-)� You are much more likey to get a good
> answer if you send this to the DBIC ML.

I considered that, but the Catalyst bunch are such a friendly helpful
mob, I thought I'd try here first. ^_^

It doesn't feel quite right to subscribe to a list (DBIC ML), ask a
question, then unsubscribe again.

> On Aug 17, 2009 3:13 AM, "Trevor Phillips" <trevor.phillips [at] gmail>
> wrote:
>
> Is there an easy way to represent a DB function call as a named column
> within a DBIx::Class schema?
>
> eg;
>
> If at the DB level I would do something like:
> �select updated, from_unixtime(updated) as updated2 from mytable;
> ... can I have;
>
> __PACKAGE__->add_columns(
> � "updated", { data_type => "TIMESTAMP" }
> � "updated2", { something => "from_unixtime(updated)", etc...}
> ...
> );
>
> Thanks.
>
> --
> Trevor Phillips �- http://dortamur.livejournal.com/
> "On nights such as this, evil deeds are done. And good deeds, of
> course. But mostly evil, on the whole."
> � � �-- (Terry Pratchett, Wyrd Sisters)
>
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/
>
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/
>
>

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

mdietrich at cpan

Aug 18, 2009, 2:24 AM

Post #5 of 6 (1017 views)
Permalink

Re: DB Functions in the Schema [In reply to]

Hi,

Am 18.08.2009 um 04:22 schrieb Trevor Phillips:

> It doesn't feel quite right to subscribe to a list (DBIC ML), ask a
> question, then unsubscribe again.

you could ask a single question on the irc channel like I do ;-):
irc.perl.org#dbix-class

Though I don't know the answer, maybe the deflate and inflate hooks
can call SQL functions? But for dates you can use Perl functions or
define your column as datetime (see:
DBIx::Class::InflateColumn::DateTime).

Also good luck from me!

matt

--
rainboxx Matthias Dietrich
Freier Software Engineer

rainboxx | Tel.: +49 (0) 151 / 50 60 78 64
T�lzer Str. 19 | Mail: matt [at] rainboxx
70372 Stuttgart | WWW : http://www.rainboxx.de

XING: https://www.xing.com/profile/Matthias_Dietrich18
GULP: http://www.gulp.de/profil/rainboxx.html

Attachments:

PGP.sig (0.19 KB)

trevor.phillips at gmail

Aug 18, 2009, 2:41 AM

Post #6 of 6 (999 views)
Permalink

Re: DB Functions in the Schema [In reply to]

On Tue, Aug 18, 2009 at 5:24 PM, Matthias Dietrich<mdietrich [at] cpan> wrote:
>
> you could ask a single question on the irc channel like I do ;-):
> irc.perl.org#dbix-class

Ooh, I might give that a go when I have a minute! (And at the right time of day)

> Though I don't know the answer, maybe the deflate and inflate hooks can call
> SQL functions? �But for dates you can use Perl functions or define your
> column as datetime (see: DBIx::Class::InflateColumn::DateTime).

I was hoping to be able to do it as part of the query when it gets the
rest of the normal columns, since one of the reasons was to reduce
multiple calls to the DB (or other routines).

And DateTime was what got me pulling my hair out to investigate this
alternative in the first place! A simple query with a dozen lines was
taking around 2 seconds to return, and I'd put in an extra method,
which was doing some DateTime calls. Without calling that method, the
request took a much more reasonable 300ms.

In the end I found the culprit was because I set a timezone of "local"
- if I hard-coded it to my actual timezone, it was fast, but using
"local", was slow-as, even though it translated to the same thing!

> Also good luck from me!

Thanks!

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads