dbix-class

How do I cleanly extract MySQL enum values in Perl?

I have some code which needs to ensure some data is in a mysql enum prior to insertion in the database. The cleanest way I've found of doing this is the following code: sub enum_values { my ( $self, $schema, $table, $column ) = @_; # don't eval to let the error bubble up my $columns = $schema->storage->dbh->selectrow_hashr...

How can I filter DBIX::Class resultsets with external data?

Using DBIx::Class and I have a resultset which needs to be filtered by data which cannot be generated by SQL. What I need to do is something effectively equivalent to this hypothetical example: my $resultset = $schema->resultset('Service')->search(\%search); my $new_resultset = $resultset->filter( sub { my $web_service = shift;...

How can I use DBD::Proxy with DBIx::Class?

I need to get a database connection through a firewall, and also limit what queries can be run. DBD::Proxy seems to be the perfect solution for this. However, I'm currently using DBIx::Class, and can't figure out how to hook them together. In particular, DBD::Proxy doesn't take SQL; it takes particular named queries. But DBIx::Class doe...

How do I have a persistent DBIx::Class in CGI::Application with mod_perl?

I am using CGI::Application on mod_perl with DBIx::Class and I'd like to have something like new define a new dbic schema on instantiation. So far I haven't been able to get it to work. The closest thing I have come to is a superclass that has a connect() method that returns a new object, but I would rather it be already be connected...

What's the right way to display a DBIx::Class ResultSet in my Catalyst project that uses Template Toolkit?

Given a DBIx::Class resultset, for example: my $rs = $c->model("DB::Card")->search({family_name => "Smith"}); the tutorials I've read use the stash to pass an arrayref of rows: $c->stash->{cards} = [$rs->all]; This results in the query getting executed at this point, and the resulting objects stuffed into the stash, so they can be ...

Can I pretty-print the DBIC_TRACE output in DBIx::Class?

Setting the DBIC_TRACE environment variable to true: BEGIN { $ENV{DBIC_TRACE} = 1 } generates very helpful output, especially showing the SQL query that is being executed, but the SQL query is all on one line. Is there a way to push it through some kinda "sql tidy" routine to format it better, perhaps breaking it up over multiple lin...

How do I make a DBIx::Class relationship with a fixed join condition?

We have a link table that can handle multiple types of object on one side, and I can't work out how to get from one of these objects to the link table using has_many. Example: link table contains: id link_id link_table resource_id 1 1 page 3 2 1 page 5 3 2 page 3 4 1 not_page 1 Buildi...

Can I have a "Parent has_many GrandChildren through Children" relationship in DBIx::Class?

I'd like to have functionality like this: $parent->get_grandchildren_by_category({category => 'foo'}); I can do it easily outside of the parent class with a simple chained join: $schema->resultset('Parent')->search( { 'me.id' => 62, 'grandchildren.category' => 'foo' }, { join => {'children' => 'grandchildren'} } ); But in...

How can I have DBIC persistent database connection in mod_perl?

I am using mod_perl for my web application. Currently, I plan to use a mysql database across the network. In every CGI request to display_customer_transaction.cgi, my script will Open up database connection across network Perform query on the database using SQL statement Analysis the data retrieved from database Print out the data in H...

Select from multiple tables in DBIx::Class

I have the following DBIx::Class code : my $where = 'me.loginid = ? AND me.code = ?'; my @bind = ( $loginID, $code ); my $tip_signals = $bom_schema->resultset('Table1')->search_literal( $where, @bind, { join => 'table2', group_by => [ 'me.id' ], '+select' => [ {'count' => '*'}, 'table2.id' ], '+a...

How do I make DBIx::Class join tables using other operators than `=`?

Summary I've got a table of items that go in pairs. I'd like to self-join it so I can retrieve both sides of the pair in a single query. It's valid SQL (I think), the SQLite engine actually does accept it, but I'm having trouble getting DBIx::Class to bite the bullet. Minimal example package Schema; use parent 'DBIx::Class::Schema';...

Parsing a Gmail-style advanced search syntax?

I want to parse a search string similar to that provided by Gmail using Perl. An example input would be "tag:thing by:{user1 user2} {-tag:a by:user3}". I want to put it into a tree structure, such as {and => [ "tag:thing", {or => [ "by:user1", "by:user2", ]}, {or => [ {not => "tag:a"}, "by:use...

Does DBIx::Class have transparent caching?

In the C#/.Net world, there are ORMs such as NHibernate or ActiveRecord that includes transparent caching: database updates are transparently replicated to the cache, objects are retrieved directly from the cache when available, etc (often with memcached). It doesn't look like transparent caching is available in Perl with DBIx::Class. D...

How do I get values from the link table in DBIx::Class?

In my app I have 2 table, books and tags, and the link table book_tags. The link table also contains the number of times the book was tagged with this particular tag. I can add a tag by doing $book->add_tag($tag, { tag_count => 10 }); However when I retrieve the tags for a book @tags = $book->tags(); it does not seem to return the ...

Which book has the best discussion of DBIx::Class?

I don't know if the DBIx::Class ORM is substantial enough to justify an entire book on the subject but I'd like to get a recommendation for a book that goes into the details of the DBIx::Class ORM. ...

How do I get DBIx::Class to collate with UTF-8?

Hi folks, I am trying to implement a web search functionality on a MySQL database using DBIx::Class::Resultset. I have it working apart from one problem: when searching for 'ü' the search is performed by MySQL as 'u' (in other words without the umlaut). The same is done for the other 'extended ASCII' characters. The table and the connec...

DBIx::Class result classes and actual tables

I'm studying DBIx classes and I'm a bit confused since my interaction with a database so far has been plain SQL queries in PHP code. Anyway, as I understand it, the class operates with the schema defined in the result classes instead of interacting directly with the database. The schema can be either be built manually via the various ....

Preventing DBIx::Class from calling everything related to a new, not-yet-inserted row?

I have a parent/child relationship in my schema. I'd like to use very similar code to modify an existing parent as to create a new one. The edit case is easy to find the children: my $parent = $resultset->find($parent_id); my @children = $parent->children->all However, in the new case, something weird happens: my $parent = $resultset...

How to Subselect with DBIx::Class?

I'm starting with DBIx::Class and i have a subselect that wanted to be in DBIx::Class, but i'm getting confused and can't build the code. My MySQL select is this one: Select name from tblCategory where id = ( Select id from tblCategory where id = ( Select id from tblRadio where name = "RFM" ) ); I read that DBIx::Cla...

right-hand-side MySQL functions in DBIx::Class

In DBIx::Class, when I generate a query using this syntax: ... 'Time(submitted_at)' => { '>' => 'Time(Now()-Interval ' . $wait_period . ' minute)' } ... The generated query is perfect except for the fact that the function on the right hand side is in quotes. ... AND ( Time(submitted_at) > 'Time(Now()-Interval 5 minute)' ) ... If i...