Re: [Dbix-class] Monkey-patching around a MySQL/UTF8 bug
Give a try to connect with the option mysql_enable_utf8 = 1
http://search.cpan.org/~capttofu/DBD-mysql-4.029/lib/DBD/mysql.pm#DATABASE_HANDLES
On Thu, Feb 26, 2015 at 11:43 PM, David Cantrell da...@cantrell.org.uk wrote:
This bug in DBD::mysql is apparently not going to be fixed:
https://rt.cpan.org/Ticket/Display.html?id=60987
and it's preventing us from inserting, eg, an i-acute character into our
database. Our customer Mr. GarcÃa is Quite Irritated at this. It appears
that any non-ASCII character with code-point below 0x100 is affected
(higher codepoints like ij and ψ and ☃ are OK). As a work-around I've
done this in my application:
BEGIN {
my $old_ex = \DBIx::Class::Storage::DBI::_dbh_execute;
my $new_ex = sub {
foreach (@{$_[3]}) {
if(exists($_-[1]) defined($_-[1])) {
utf8::upgrade($_-[1])
}
}
return $old_ex-(@_);
};
{
no strict qw/ refs /;
no warnings 'redefine';
*DBIx::Class::Storage::DBI::_dbh_execute = $new_ex;
}
}
And it appears to work. However, I don't like monkey-patching like that.
Is there a better way that I haven't been able to find in the
DBIx::Class doco?
--
David Cantrell | Hero of the Information Age
Sobol's Law of Telecom Utilities:
Telcos are malicious; cablecos are simply clueless.
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Help with many to many relationship
Abo.pm
__PACKAGE__-has_many( abojrnabt = 'Dbc::Jrnabt', {'foreign.noabt'
= 'self.noabt'});
is supposed to be
Abo.pm
__PACKAGE__-has_many( abojrnabt = 'Dbc::Schema::Result::Jrnabt',
{'foreign.noabt' = 'self.noabt'});
http://search.cpan.org/~ribasushi/DBIx-Class-0.08270/lib/DBIx/Class/Relationship.pm
On Wed, Apr 2, 2014 at 2:53 PM, RAPPAZ Francois
francois.rap...@unifr.ch wrote:
The Schema.pm and Schema folder are in a Dbc folder. The Schema.pm has
package Dbc::Schema
The Result Folder in Schema folder holds the tables modules, for example
Jrnabt.pm has package Dbc::Schema::Result::Jrnabt
When I fetch data from another table without using relationship, for example
my $s = Dbc::Schema-connect(
$dsn,
$cfg-get_usr,
$cfg-get_psw,
{
PrintError = 0,
RaiseError = 1,
AutoCommit = 1,
mysql_enable_utf8=1
}
);
my $rs = $s-resultset('Ed')-search_rs( undef, {order_by = ['nom']} );
while (my $ed = $rs-next){
print $ed-nom , \n;
}
It works
François
-Original Message-
From: Hailin Hu [mailto:i...@h2l.name]
Sent: mercredi, 2. avril 2014 04:14
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] Help with many to many relationship
Show up the folder structure and the actual package names.
On Wed, Apr 2, 2014 at 12:09 AM, RAPPAZ Francois
francois.rap...@unifr.ch wrote:
Hi
I have two tables Abo (primary key: noabt) and Jrn (primary key:
nofm)
join by a linking table jrnabt (primary keys: nofm, noabt)
I have defined the relationship as
Abo.pm
__PACKAGE__-has_many( abojrnabt = 'Dbc::Jrnabt', {'foreign.noabt'
= 'self.noabt'});
Jrn.pm
__PACKAGE__-has_many(jrnjrnabt = 'Dbc::Jrnabt', {'foreign.nofm'
=
'self.nofm'});
Jrnabt.pm
__PACKAGE__-belongs_to(jrnabtjrn = 'Dbc::Jrn', {'foreign.nofm' =
'self.nofm'}); __PACKAGE__-belongs_to(jrnabtabo = 'Dbc::Abo',
{'foreign.noabt' = 'self.noabt'});
When I try
my $rs = $schema-resultset('Abo')-search_rs({'abojrnabt.nofm' =
'1'}, {join = 'abojrnabt'});
I got
DBIx::Class::Schema::source(): Can't find source for Dbc::Jrnabt at
U:\docs\perl\dokpe_i01_dbc\testdbc.pl line 62
What am I missing ?
Thanks
François Rappaz
Centre de documentation de la Faculté des Sciences Université de
Fribourg DokPe - Dokumentationszentrum der Naturwissenschaftlichen
Fakultät Universität Freiburg Pérolles CH-1700 Fribourg Switzerland
http://www.unifr.ch/dokpe/
Tel.: 41 (0)26 300 92 60
Fax.: 41 (0)26 300 97 30
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-
cl...@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] match against ...
You should at least give out the error message if you expect any
feedback from others.
Others will not create a same environment as yours and run the codes
you pasted. Generally, they just analyze the problem with the
description only. Me, too :)
On Wed, Apr 2, 2014 at 6:47 PM, RAPPAZ Francois
francois.rap...@unifr.ch wrote:
I'm struggling with code as
#sql I want to is SELECT * FROM jrn WHERE match(ti, ex, ad)
against('+$bla' in boolean mode) ORDER BY tri);
$bla is a string that holds the text to search
my $sqlmaker = SQL::Abstract-new(special_ops = [ {regex = qr/^match$/i,
handler = sub {
my ($me, $field, $op, $arg) = @_;
$arg = [$arg] if not ref $arg;
my $label = $me-_quote($field);
my ($placeholder) = $me-_convert('?');
my $placeholders = join , , (($placeholder) x @$arg);
my $sql = $me-_sqlcase('match') . ($label) .
$me-_sqlcase('against') . ($placeholders) ;
my @bind = $me-_bindtype($field, @$arg);
return ($sql, @bind);
}
},
]);
my ($where, @bind)= $sqlmaker-where(ti, ex, ad, tri, $bla);
$self-{log}-debug($where);
my $rs = $self-{schema}-resultset('Jrn')-search_rs(undef,
{where= $where});
But keep having error message. What is @bind for if I don't use it ?
Thanks for showing the right way to have this !
François
-Original Message-
From: Hailin Hu [mailto:i...@h2l.name]
Sent: vendredi, 28. mars 2014 18:36
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] match against ...
Hi,
Have a look at docs below:
http://search.cpan.org/~ribasushi/DBIx-Class-
0.08270/lib/DBIx/Class/ResultSet.pm#where
http://search.cpan.org/~ribasushi/SQL-Abstract-
1.77/lib/SQL/Abstract.pm#SPECIAL_OPERATORS
On Sat, Mar 29, 2014 at 12:46 AM, RAPPAZ Francois
francois.rap...@unifr.ch wrote:
Hi there
I haven't look deep enough in the doc pages, sorry, but how can I
translate this with resultset('table')-search_rs();
SELECT * FROM table WHERE match(field1, field2, field3)
against('+$bla' in boolean mode) ORDER BY field1 ?
$bla being taken from a field and having the spaces substituted with
+ :
my $bla = $b-get_text();
($bla=$bla)=~s/\s+/ \+/g;
Thanks
François
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-
cl...@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Help with many to many relationship
Show up the folder structure and the actual package names.
On Wed, Apr 2, 2014 at 12:09 AM, RAPPAZ Francois
francois.rap...@unifr.ch wrote:
Hi
I have two tables Abo (primary key: noabt) and Jrn (primary key: nofm) join
by a linking table jrnabt (primary keys: nofm, noabt)
I have defined the relationship as
Abo.pm
__PACKAGE__-has_many( abojrnabt = 'Dbc::Jrnabt', {'foreign.noabt' =
'self.noabt'});
Jrn.pm
__PACKAGE__-has_many(jrnjrnabt = 'Dbc::Jrnabt', {'foreign.nofm' =
'self.nofm'});
Jrnabt.pm
__PACKAGE__-belongs_to(jrnabtjrn = 'Dbc::Jrn', {'foreign.nofm' =
'self.nofm'});
__PACKAGE__-belongs_to(jrnabtabo = 'Dbc::Abo', {'foreign.noabt' =
'self.noabt'});
When I try
my $rs = $schema-resultset('Abo')-search_rs({'abojrnabt.nofm' = '1'},
{join = 'abojrnabt'});
I got
DBIx::Class::Schema::source(): Can't find source for Dbc::Jrnabt at
U:\docs\perl\dokpe_i01_dbc\testdbc.pl line 62
What am I missing ?
Thanks
François Rappaz
Centre de documentation de la Faculté des Sciences Université de Fribourg
DokPe - Dokumentationszentrum der Naturwissenschaftlichen Fakultät
Universität Freiburg Pérolles CH-1700 Fribourg Switzerland
http://www.unifr.ch/dokpe/
Tel.: 41 (0)26 300 92 60
Fax.: 41 (0)26 300 97 30
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] match against ...
Hi,
Have a look at docs below:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08270/lib/DBIx/Class/ResultSet.pm#where
http://search.cpan.org/~ribasushi/SQL-Abstract-1.77/lib/SQL/Abstract.pm#SPECIAL_OPERATORS
On Sat, Mar 29, 2014 at 12:46 AM, RAPPAZ Francois
francois.rap...@unifr.ch wrote:
Hi there
I haven't look deep enough in the doc pages, sorry, but how can I translate
this with resultset('table')-search_rs();
SELECT * FROM table WHERE match(field1, field2, field3) against('+$bla' in
boolean mode) ORDER BY field1 ?
$bla being taken from a field and having the spaces substituted with + :
my $bla = $b-get_text();
($bla=$bla)=~s/\s+/ \+/g;
Thanks
François
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Adding an additional custom join parameter
Can I ask why you need site_task table? In relationship view, it is the same as issue table. It is something like that you defined one many-to-many relation (task - site) through two bridge tables (site_task and issue). On Mon, Dec 9, 2013 at 10:41 AM, Andrew Beverley a...@andybev.com wrote: My database has a table that has 2 other has many tables related to it, best described by this diagram: http://files.andybev.com/schema.png I want to select from the task table, joining both other tables at the same time. In order for the joins to work correctly, in raw SQL I would use 2 conditions for the join of the issue table: issues.task_id = me.id AND issues.site_id = site_tasks.site_id However, by default, DBIx::Class only uses the first condition, as per my relationship definitions. How can I add the second condition? I have tried adding a second join condition to the relationship definition, but as far as I can tell only the 2 immediate tables can be specified. FWIW, the full SQL statement I am trying to execute is as follows: SELECT MAX( issue.completed ) AS max, period_qty FROM task me LEFT JOIN site_task ON site_task.task_id = me.id LEFT JOIN site ON site.id = site_task.site_id LEFT JOIN issue ON issue.task_id = me.id AND issue.site_id = site_task.site_id == Need this WHERE period_unit = 'week' GROUP BY site_task.id HAVING max DATE_SUB(NOW(), INTERVAL period_qty week) The values selected are not always correct without the second join condition. Thanks, Andy ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Adding an additional custom join parameter
Well, can you try this way?
Have join started from site_task, like this
$site_task-search(
...,
join = ['site', 'task', 'issue'],
)
The relationships may be like this?
_SITE_TASK_-belongs_to('site' = 'Site');
_SITE_TASK_-belongs_to('task' = 'Task');
_SITE_TASK_-might_have('issue' = 'Issue', {'foreign.site_id' =
'me.site_id', 'foreign.task_id' = 'me.task_id'}, {'join_type' =
'LEFT'});
Reference:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Relationship/Base.pm#add_relationship
I'm not sure if it works since relationship between site_task and
issue is ambiguous.
Good luck :)
On Tue, Dec 10, 2013 at 8:36 PM, Andrew Beverley a...@andybev.com wrote:
On Tue, 2013-12-10 at 19:42 +0900, Hailin Hu wrote:
Can I ask why you need site_task table?
In relationship view, it is the same as issue table.
That's a good question. The reason is that issues will often be
generated that are not related to a task, and likewise there will be
tasks that are related to sites but that do not have any issue to
link them (in which case the site_task table would be used).
It is something like that you defined one many-to-many relation (task
- site) through two bridge tables (site_task and issue).
You raise a valid point though, in that this is probably a poor way to
design such a database. Maybe I should only have one site_task table
to link the sites and tasks tables, and then have appropriate fields
within that single table to signify whether the entry is linking the 2
tables for reason of an issue or a task.
http://files.andybev.com/schema.png
Thanks,
Andy
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] fetching a column value directly
1. Define column host as unique in your Result pm, then you can use
$rs-find({ host = $host })-addr;
2. Use method first()
$hostAdd = $rs-search({host = $host})-get_column('addr')-first;
On Mon, Nov 4, 2013 at 2:29 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:
hello,
I have this host table, which has all unique records. So i want to get a
specific column value for a specific host. i am doing below, but i think it
is not efficient. is there a better way?
my $hostAdd_rs =
$schema-resultset('Host')-search({host=$host},{select=[qw/addr/]});
while (my $rec = $hostAdd_rs-next() ) {
$hostAdd = $rec-get_column('addr');
}
can we not do it in a single line command? without any while loop?
i tried, but getting various errors.
ty.
Rajeev
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] where does Main come from?
It seems that you already knew Main is the level representing Schema, so what's the problem? Maybe you prefer MyApp::Schema::ResultSet::*, but it is just a name, isn't it? You can change it as you like. Cheers On Mon, Sep 2, 2013 at 11:39 PM, Dave Howorth dhowo...@mrc-lmb.cam.ac.uk wrote: I'm just looking at DBIx::Class::Manual::Example It starts out by setting up a database and then goes on to 'Set up DBIx::Class::Schema' and all of a sudden it is creating a subdirectory called Main. Why? Where did that come from? It's supposed to be a simple example, so presumably there's a pretty important reason why this directory appears out of nowhere. Why is there no explanation of why we need that extra level of directory? Why is it called Main instead of Schema? Cheers, Dave ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] why the connection to database failed on the first time, success after refresh
It seems the error only occurs on connection. Do you have any sql statement in on_connect_do, which SQL server can't execute correctly? On Thu, Jun 6, 2013 at 2:38 PM, Hugh Wang yow...@verizon.net wrote: I am trying to use Catalyst, with DBIx::Class to connect to a MS Sqlserver database. after I start the Catalyst server, perl script/libApp_server.pl -r I connect to a page to open a table and display it, but the first time when I open the page, such as: http://localhost:3000/admin/ticket it shows error: [error] DBIx::Class::Storage::DBI::_do_query(): DBI Exception: DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver][SQL Server]'=' Grammer Error nearby (SQL -42000)(DBD: Execute immediate failed err=-1) at K:\LibApp\lib/LibApp/Controller /admin.pm line 47 but after I refresh to reload the page, it works good . if I restart the server, the error will show up again for the first time I open the same web page. the refresh of the same page, will work good. what's wrong with it? any one has experience thanks Hugh Wang ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
If you are using MySQL, I think query cache of MySQL works for you.
Hitting query cache is supposed fast enough for general purpose.
Is it really necessary to try to cache a RS, which is not a easy job,
I'm afraid.
On Mon, Jan 14, 2013 at 1:35 PM, Jesse Sheidlower jes...@panix.com wrote:
I have a Catalyst app that very frequently (pretty much every request)
requires several DBIC resultsets that return a small number of values,
that very rarely change. I'm trying to cache this, so I can update the
cache when the values change and not have to hit my DB a half-dozen
times on every request for data that is effectively static.
Originally the relevant line was along the lines of:
$c-stash-{subjects} = $c-model('WordsDB::Subject')-search();
I replaced this, following the C::P::Cache docs, with
unless ( $c-stash-{subjects} = $cache-get( 'subjects' ) ) {
$c-stash-{subjects} =
$c-model('WordsDB::Subject')-search();
$cache-set( 'subject', $c-stash-{subjects} );
}
However, this dies (on a second run, when it's actually hitting the
cache) with undef error - Can't call method select on an undefined
value at /usr/share/perl5/DBIx/Class/ResultSet.pm line 957. So I'm
assuming that I can't just stuff a RS into the cache and expect it to
work. Is there an easy way around it? (I know I could retrieve the
actual data and put that into the cache, but then I'd have to rewrite a
whole bunch of templates, that are expecting a resultset.)
Thanks.
Jesse Sheidlower
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Join Three Tables With Aggregate Functions
enable DBIC_TRACE=1 and run it, you can check the sql generated and see
what actually happened.
2012/10/05 19:06 sc...@simpzoid.com:
Dear All,
I have bit of SQL (mysql) which joins three tables to get all the records
form the first table and sums from the 2 remaining tables. The SQL looks
like:
select t1.*, t2_total, t3_total from t1
left join
(select t1.id as t1_id, sum(t2.total) as t2_total from t1 left
join t2 on
t2.t1_id=t1.id group by t1.id) as t2s on t1.id = t2s.t1_id
left join
(select t1.id as t1_id, sum(t3.total) as t3_total from t1 left
join t3 on
t3.t1_id=t1.id group by t1.id) as t3s on t1.id = t3s.t1_id
group by t1.id;
This works fine.
I reckon (obviously wrongly) that this translates to:
my @join = $schema-resultset('T1')-search({
},
{
+select= ['id' \['SUM(T2.total)'], \['SUM(T3.total)']],
as = [qw /id t2_total t2_total/ ],
join = ['T2', 'T3'],
group_by = 'id',
});
The search returns a resultset OK but the sums are multiplied by the
number of permutations, e.g. SUM(T2.total) is a factor of 3 high if there
3 results in T3.
I think the question I may be asking is what is the syntax to express a 3
table join, on T1 to T2 and T1 to T3 type arrangement.
Thanks,
Scott
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Automatic reconnection to failed over database
http://search.cpan.org/~capttofu/DBD-mysql-4.022/lib/DBD/mysql.pm#DATABASE_HANDLES Does mysql_auto_reconnect not work? On Thu, Aug 23, 2012 at 12:26 AM, Jorge Gonzalez jorge.gonza...@daikon.es wrote: Hi all, I am in the process of migrating several production apps using DBIx::Class to a clustered mysql database (HA). When my apps try to connect to the DB and can't, they fail (die, return errors, etc.). Since the cluster has a (very small) downtime during the failover process (node failure), I'd like that my apps just retried the connection in case it fails, stepping over the short downtime and reconnecting to the new cluster node which runs the DB after the failure. I have seen the DBIx::RetryOverDisconnects module and it seems it could serve the purpose, but I have googled and haven't seen any comments on using it on DBIx::Class models. Has anyone managed to integrate both modules (and use them!)? Or is there an easy way of doing what I want, that I'm missing? Thanks in advance BR J. ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Joining group of 2 joined tables together
you can try to define a self-reference has_one relationship on Table 2, and use the relationship in join. you can't join tables without relationship defined. On Wed, Aug 29, 2012 at 5:57 PM, t...@pasteur.fr wrote: Hi list, I am not sure if such complex join has been asked on the list, but I did not find a similar problem in the archive. Here are my tables Table1 Table2 fid (pk) srcfid(pk) name fid(fk) - references Table1.fid start stop The SQL query I'd like to translate with DBIx is the following SELECT * FROM Table1 nt1, Table2 nt2, Table 1 mt1, Table2 mt2 WHERE nt1.fid=nt2.fid AND mt1.fid=mt2.fid AND mt2.srcfid=nt2.srcfid . other where conditions. So my question is, does someone would know how to translate the particular WHERE clause (mt2.srcfid=nt2.srcfid) ? Thanks in advance for any clue. If this is not possible, I'll move to literal SQL using $dbh and $sth. Regards Emmanuel -- - Emmanuel Quevillon tuco at_ pasteur dot fr - ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] How to
where = { for = 'update' }
is supposed to work.
try to debug with DBIC_TRACE=1 and see what exactly the sql is.
On Tue, Jun 19, 2012 at 3:36 PM, Hardik Joshi hardik.ma...@gmail.com wrote:
Thanks Alex,
I have tried 'SELECT...FOR UPDATE' with normal DBI module and its working
well where as via DBIx::Class its not working as per requirement.
So, I feel I might missing something in DBIx::Class. do you have any idea
for that?
Thanks,
Hardik Joshi
On Tue, Jun 19, 2012 at 10:40 AM, Alex Erzin eaa-h...@yandex.ru wrote:
Not sure about DBIx::Class, but according to SQL you not need transaction
like transaction begin/commit, but need something like select for
update.
Quote from Mysql manual:
---
SELECT ... FOR UPDATE locks the rows and any associated index entries
...
Other transactions are blocked from updating those rows, from doing SELECT
... LOCK IN SHARE MODE, or from reading the data in certain transaction
isolation levels.
---
19.06.2012, 08:31, Hardik Joshi har...@mavenvista.com:
Hello,
I have am working on one real time auction application where user used to
enter new bid based on previous minimum bid value. There is possibility that
two user will get same minimum value for their next bid. So I would like to
restrict second user on select statement if already first user has fired
select statement and waiting for insert statement to complete.
So could any one can guide me how to deal with such condition in
DBIx::Class. I have tried
eg.
$schema-storage-txn_begin();
my $res = $schema-resultset('Test')-search($filter,
{for='update'})-first();
$schema-resultset('Test')-create($params);
$schema-storage-txn_commit();
Thanks,
Hardik Joshi
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
--
eaa@
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] table-per-subclass - how it works?
Is it acceptable like something below
package Target;
...
package Host;
...
__PACKAGE__-belong_to('target' = 'Target', 'target_id');
sub column_only_exists_in_target {
my $self = shift;
return $self-target-column_only_exists_in_target;
}
well, it is not a good solution, but it could work, I think :)
On Fri, Jun 15, 2012 at 3:04 PM, Alex Erzin eaa-h...@yandex.ru wrote:
Hello,
I need to implement table-per-class inheritance, as it simple made in
Hibernate in Java (more details can be found here
http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch10.html#inheritance-tablepersubclass),
but cannot find right way how to do it in DBIx::Class.
For example, I have two objects: Target (with properties ID, Description) and
Host (ID, Hostname, IP), Host inherited from Target, and on table-level there
are two tables Host and Target, joined by ID field.
CREATE TABLE `mydb`.`target` (
`target_id` INT NOT NULL ,
`description` VARCHAR(45) NULL ,
PRIMARY KEY (`target_id`) )
CREATE TABLE `mydb`.`host` (
`target_id` INT NOT NULL ,
`hostname` VARCHAR(45) NULL ,
`ip` VARCHAR(45) NULL ,
PRIMARY KEY (`target_id`) ,
CONSTRAINT `fk_host_target`
FOREIGN KEY (`target_id` )
REFERENCES `mydb`.`target` (`target_id` )
)
How to implemeng there classes Target and Host, so it possible to write
print $target-id(), $targer-description()
print $host-id(), $host-description(), $host-hostname(), $host-ip()
?
I have no ideas how to implement inheritance, and all my experiments are
failed - from Host i cannot get access to parent properties like description.
Could you help me please (with examples)?
Thanks.
--
eaa@
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] table-per-subclass - how it works?
I have no idea about standard solution, but another idea for reference :)
Package Base;
use base 'DBIx::Class::Core';
# parents' name should be a defined has_one or belong_to relationship
has 'parents' = (
is = 'rw',
isa = 'ArrayRef[Str]',
builder = _build_parents,
);
sub inflate_result {
my $self = shift;
my $ret = $self-next::method( @_ );
foreach my $relationship ( @{ $ret-parents } ) {
my $parent = $ret-$relationship;
my @columns = ... # @columns_in_$parent - @column_in_$ret
foreach my $column ( @columns ) {
# stolen from DBIx::Class's source
no strict 'refs';
no warnings 'redefine';
my $method = join '::', ref $ret, $column;
*$method = Sub::Name::subname( $method, sub {
return $ret-$parent-$column;
});
}
}
}
...
package Target;
extends 'Base';
...
package Host;
extends 'Base';
__PACKAGE__-belong_to('target' = 'Target', 'target_id');
sub _build_parents {[qw/target/]}
...
so just declare the attribute 'parents' in subclass is ok.
the task is not typical for me, but interesting.
On Fri, Jun 15, 2012 at 3:59 PM, Alex Erzin eaa-h...@yandex.ru wrote:
Yes, in some cases you are right, but you are saying about long inheritance
chains and other cases where inheritance is not enought flexible,
but i say about just one case in one place where inheritance is preferred for
me, and i try to find solution _how_ to implement inheritance, but not about
_why_not_ :)
15.06.2012, 10:43, Ben Tilly bti...@gmail.com:
The benefits of OOP are mostly in information hiding.
If you're creating a bunch of long inheritance chains, you're probably
doing OOP poorly. Composition is preferred. And maps *much* more
naturally onto a relational database.
On Thu, Jun 14, 2012 at 11:37 PM, Alex Erzin eaa-h...@yandex.ru wrote:
Hm... it really works, but where are benefits of OOP? ;)
There is no inheritance at all, and to access parent's properties I should
call parent directly, so inheritance just replaced with incapsulation, and
it's no one what i try to find.
Some years ago we have implemented inheritance in ORM in some commercial
project in Perl, later I have swithed to java and have found Hibernate it
really cool with ORM.
But now I have returned to Perl and I think that DBIx::Class can help me
with ORM, but right now i'm confused that cannot find standard solution
for typical task.
15.06.2012, 10:25, Hailin Hu i...@h2l.name:
Is it acceptable like something below
package Target;
...
package Host;
...
__PACKAGE__-belong_to('target' = 'Target', 'target_id');
sub column_only_exists_in_target {
my $self = shift;
return $self-target-column_only_exists_in_target;
}
well, it is not a good solution, but it could work, I think :)
On Fri, Jun 15, 2012 at 3:04 PM, Alex Erzin eaa-h...@yandex.ru wrote:
Hello,
I need to implement table-per-class inheritance, as it simple made in
Hibernate in Java (more details can be found here
http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch10.html#inheritance-tablepersubclass),
but cannot find right way how to do it in DBIx::Class.
For example, I have two objects: Target (with properties ID,
Description) and Host (ID, Hostname, IP), Host inherited from Target, and
on table-level there are two tables Host and Target, joined by ID field.
CREATE TABLE `mydb`.`target` (
`target_id` INT NOT NULL ,
`description` VARCHAR(45) NULL ,
PRIMARY KEY (`target_id`) )
CREATE TABLE `mydb`.`host` (
`target_id` INT NOT NULL ,
`hostname` VARCHAR(45) NULL ,
`ip` VARCHAR(45) NULL ,
PRIMARY KEY (`target_id`) ,
CONSTRAINT `fk_host_target`
FOREIGN KEY (`target_id` )
REFERENCES `mydb`.`target` (`target_id` )
)
How to implemeng there classes Target and Host, so it possible to write
print $target-id(), $targer-description()
print $host-id(), $host-description(), $host-hostname(), $host-ip()
?
I have no ideas how to implement inheritance, and all my experiments
are failed - from Host i cannot get access to parent properties like
description.
Could you help me please (with examples)?
Thanks.
--
eaa@
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
--
eaa@
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http
Re: [Dbix-class] table-per-subclass - how it works?
No, on subclass layer, Host has no relationship with Target.
The idea is a little tricky.
Inheritage is implemented according to the attribute 'parents' instead
of the natural class inheritage.
In class 'Host'
sub _build_parents {[qw/target/]}
This declaresd Host's parent as target( the relationship method name ).
Since 'Host' extends 'Base'
sub inflate_result {
This would be called when object is inited, which dynamically creates
so-called inherited accessors.
On Fri, Jun 15, 2012 at 5:30 PM, Alex Erzin eaa-h...@yandex.ru wrote:
I'm confused with package Host; extends 'Base';
I expect extends 'Target', not 'Base'. Is it correct?
15.06.2012, 12:06, Hailin Hu i...@h2l.name:
I have no idea about standard solution, but another idea for reference :)
Package Base;
use base 'DBIx::Class::Core';
# parents' name should be a defined has_one or belong_to relationship
has 'parents' = (
is = 'rw',
isa = 'ArrayRef[Str]',
builder = _build_parents,
);
sub inflate_result {
my $self = shift;
my $ret = $self-next::method( @_ );
foreach my $relationship ( @{ $ret-parents } ) {
my $parent = $ret-$relationship;
my @columns = ... # @columns_in_$parent - @column_in_$ret
foreach my $column ( @columns ) {
# stolen from DBIx::Class's source
no strict 'refs';
no warnings 'redefine';
my $method = join '::', ref $ret, $column;
*$method = Sub::Name::subname( $method, sub {
return $ret-$parent-$column;
});
}
}
}
...
package Target;
extends 'Base';
...
package Host;
extends 'Base';
__PACKAGE__-belong_to('target' = 'Target', 'target_id');
sub _build_parents {[qw/target/]}
...
so just declare the attribute 'parents' in subclass is ok.
the task is not typical for me, but interesting.
On Fri, Jun 15, 2012 at 3:59 PM, Alex Erzin eaa-h...@yandex.ru wrote:
Yes, in some cases you are right, but you are saying about long
inheritance chains and other cases where inheritance is not enought
flexible,
but i say about just one case in one place where inheritance is preferred
for me, and i try to find solution _how_ to implement inheritance, but not
about _why_not_ :)
15.06.2012, 10:43, Ben Tilly bti...@gmail.com:
The benefits of OOP are mostly in information hiding.
If you're creating a bunch of long inheritance chains, you're probably
doing OOP poorly. Composition is preferred. And maps *much* more
naturally onto a relational database.
On Thu, Jun 14, 2012 at 11:37 PM, Alex Erzin eaa-h...@yandex.ru wrote:
Hm... it really works, but where are benefits of OOP? ;)
There is no inheritance at all, and to access parent's properties I
should call parent directly, so inheritance just replaced with
incapsulation, and it's no one what i try to find.
Some years ago we have implemented inheritance in ORM in some
commercial project in Perl, later I have swithed to java and have found
Hibernate it really cool with ORM.
But now I have returned to Perl and I think that DBIx::Class can help
me with ORM, but right now i'm confused that cannot find standard
solution for typical task.
15.06.2012, 10:25, Hailin Hu i...@h2l.name:
Is it acceptable like something below
package Target;
...
package Host;
...
__PACKAGE__-belong_to('target' = 'Target', 'target_id');
sub column_only_exists_in_target {
my $self = shift;
return $self-target-column_only_exists_in_target;
}
well, it is not a good solution, but it could work, I think :)
On Fri, Jun 15, 2012 at 3:04 PM, Alex Erzin eaa-h...@yandex.ru wrote:
Hello,
I need to implement table-per-class inheritance, as it simple made
in Hibernate in Java (more details can be found here
http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch10.html#inheritance-tablepersubclass),
but cannot find right way how to do it in DBIx::Class.
For example, I have two objects: Target (with properties ID,
Description) and Host (ID, Hostname, IP), Host inherited from Target,
and on table-level there are two tables Host and Target, joined by ID
field.
CREATE TABLE `mydb`.`target` (
`target_id` INT NOT NULL ,
`description` VARCHAR(45) NULL ,
PRIMARY KEY (`target_id`) )
CREATE TABLE `mydb`.`host` (
`target_id` INT NOT NULL ,
`hostname` VARCHAR(45) NULL ,
`ip` VARCHAR(45) NULL ,
PRIMARY KEY (`target_id`) ,
CONSTRAINT `fk_host_target`
FOREIGN KEY (`target_id` )
REFERENCES `mydb`.`target` (`target_id` )
)
How to implemeng there classes Target and Host, so it possible to
write
print $target-id(), $targer-description()
print $host-id(), $host-description(), $host-hostname(),
$host-ip()
?
I have no ideas how to implement inheritance, and all my experiments
are failed - from Host i cannot get access to parent properties like
description.
Could you help me please (with examples)?
Thanks
Re: [Dbix-class] Need help with a search query
just as the error said, there is no such a relationship.
try to define a many-to-many relationship
http://search.cpan.org/~arodland/DBIx-Class-0.08196/lib/DBIx/Class/Relationship.pm#many_to_many
or use a multiple level join
join = {people_companies = company}
On Fri, Jun 15, 2012 at 11:42 AM, Dennis Daupert ddaup...@sbcglobal.net wrote:
For some reason the right search query syntax has been elusive.
I'm trying to get a list of people belonging to a particular company
where the company has a particular 'code' value.
==
Sample query code attempt:
--
sub get_ppl_by_org_code {
my ( $schema, $code ) = @_;
my $rs = $schema-resultset('Person')-search(
{
'company.code' = $code,
},
{
join = [qw/ company /], # also tried people_companies
}
);
return( $rs );
}
Gives error: No such relationship company on Person
==
Schemas: (produced by DBIx::Class::Schema::Loader)
--
__PACKAGE__-table(people);
__PACKAGE__-add_columns(
agent_id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0 },
first_name,
{ data_type = varchar, is_nullable = 1, size = 40 },
last_name,
{ data_type = varchar, is_nullable = 1, size = 40 },
snip
__PACKAGE__-has_many(
people_companies,
DB::Schema::Result::PeopleCompany,
{ foreign.agent_id = self.agent_id },
{ cascade_copy = 0, cascade_delete = 0 },
);
==
__PACKAGE__-table(group_company_xl);
__PACKAGE__-add_columns(
company_id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0 },
company,
{ data_type = varchar, is_nullable = 0, size = 50 },
service_center,
{ data_type = varchar, is_nullable = 1, size = 20 },
code,
{ data_type = varchar, is_nullable = 1, size = 5 },
snip
__PACKAGE__-has_many(
people_companies,
DB::Schema::Result::PeopleCompany,
{ foreign.company_id = self.company_id },
{ cascade_copy = 0, cascade_delete = 0 },
);
Seems odd, DBIx::Class::Schema::Loader produced accessor
with same name as the one for people table. Is that kosher?
==
__PACKAGE__-table(people_companies);
__PACKAGE__-add_columns(
agent_id,
{ data_type = integer, is_foreign_key = 1, is_nullable = 0 },
company_id,
{ data_type = integer, is_foreign_key = 1, is_nullable = 0 },
);
__PACKAGE__-belongs_to(
agent,
DB::Schema::Result::Person,
{ agent_id = agent_id },
{ is_deferrable = 1, on_delete = CASCADE, on_update = CASCADE },
);
__PACKAGE__-belongs_to(
company,
DB::Schema::Result::GroupCompanyXl,
{ company_id = company_id },
{ is_deferrable = 1, on_delete = CASCADE, on_update = CASCADE },
);
==
Can someone help me learn the secret handshake?
/dennis
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
