[Dbix-class] Error when using distinct => 1 with select => ...

[Octavian Rasnita]

Hello,

I tried to use the following select query, but it gives an error:

use TB::Schema;
$ENV{DBIC_TRACE}++;

my $schema = TB::Schema->connect( "dbi:mysql:intranet", "root" );

my $rs = $schema->resultset('Performance')->search( {}, {
   select => [ { date => 'date_time' } ],
   as => [ 'date' ],
   distinct => 1,
} );

$rs->all;

This gives the following result:

SELECT DATE( date_time ) FROM performance me GROUP BY :
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st 
execute failed: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near '' 
at line 1 [for Statement "SELECT DATE( date_time ) FROM performance me GROUP 
BY "] at D:\test_dbic\test.pl line 18


If I replace the line:
   distinct => 1,

with:
   group_by => [ { date => 'date_time' } ],

Then it works fine and it prints the query:
SELECT DATE( date_time ) FROM performance me GROUP BY DATE( date_time ):

Isn't the first way using distinct => 1 recommended, or there is a bug in 
DBIC?


The Result class with POD and other non-important columns removed is:

package TB::Schema::Result::Performance;

use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';

__PACKAGE__->load_components("InflateColumn::DateTime");
__PACKAGE__->table("performance");

__PACKAGE__->add_columns(
 "id",
 {
   data_type => "integer",
   extra => { unsigned => 1 },
   is_auto_increment => 1,
   is_nullable => 0,
 },
 "date_time",
 {
   data_type => "datetime",
   datetime_undef_if_invalid => 1,
   is_nullable => 0,
 },
#... other fields
);

__PACKAGE__->set_primary_key("id");
__PACKAGE__->meta->make_immutable;
1;

Thanks

--Octavian


___
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] Features comparison among ORMS?

[Octavian Rasnita]

Hi Rob,

From: "Rob Kinyon" 
> What sort of features are you looking for? All the Perl ORMs wrap DBI,
> so connecting to different databases isn't a problem. Among the Perl
> ORMs, only DBIx::Class has resultsets, arguably the most powerful ORM
> concept. (It's not just among Perl ORMs - I don't know of any other
> ORM that has resultsets.) All the ORMs you mentioned support the
> various relationships (belongs_to, has_many, has_one, and
> many_to_many).
> 
> In terms of speed benchmarks, those are mostly useless. 99% of the
> time spent within an ORM is actually time spent within the database.
> 90% of that speed is dependent on how badly you designed your tables.
> 
> That said, DBIx::Class provides prefetching, which can speed up very
> specific use-cases. (Again, something I've never seen in any other
> ORM.)
> 
> In general, the advice I'd give is:
> * If you're already using an ORM, use it (unless it's Class::DBI, in
> which case convert to DBIx::Class).
> * If you're using Catalyst, convert to DBIx::Class.
> * Otherwise, use DBIx::Class.
> 
> Does that help?
> 
> Rob
> 




Thank you for your answer, but I think I put a too general question.

I use DBIC and Catalyst for many years and I know what DBIC can do, but I can't 
compare it to other ORMS.

So I want to be able to compare it because everybody knows or heard about 
Hybernate, and most ORMs comparisons on the web are among many other ORMS than 
DBIC, and those benchmarks are pretty useless, I agree, but they are pretty 
good for promoting those ORMS.

So my problem is that if somebody asks me, I can't tell why DBIC is better than 
other ORMS, or with other words I don't know:
Which are the advantages of DBIC when it is compared with Hybernate, 
ActiveRecord, SQLAlchemy...?
and also which are its disadvantages... because it would be also helpful to 
know and be prepared to be told about them.

Without knowing these things, at least theoreticly (without really using all 
those ORMS), it is hard to promote DBIC and pretend that it is the best but 
almost nobody knows about it.

Perl 5 is considered ugly by most of the programmers, but it can be promoted by 
showing how powerful tools it offers when comparing it with other languages, 
and DBIC is an important tool. My target audience is not the Perl users group 
which surely know that DBIC is the best Perl ORM, but those who pretend that 
Java is better for some reasons, like the fact that it can use the best ORM - 
Hybernate, or that Python is the best because it can use SQLAlchemy, or that 
Ruby is great because it can use ActiveRecord...

This is why it could be helpful to be able to show that yes, those ORMS are 
great, but DBIC is better because it can do all what those ORMS can do, plus it 
has some very helpful  additional features like...

Octavian


___
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

[Dbix-class] Features comparison among ORMS?

[Octavian Rasnita]

Hi,

Does anyone know if there is somewhere a features comparison among the most 
used ORMS?

I tried to find one that includes Hybernate, ActiveRecord, SQLAlchemy, DBIC, 
but I couldn't find such a thing.

I found a lot of comparisons for Java and DotNet ORMS and usually speed 
benchmarks.

--Octavian


___
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] Fine-tuning of prefetched relationships

[Octavian Rasnita]

From: "Peter Rabbitson" 
> what is so scary/undesirable about "modifying the schema" ?
> 
> That is a real question - you are not the first to word things thusly. I 
> need to figure out where the fear to add an extra definition is coming 
> from.
> 
> Cheers




I can tell you my source of fear of adding custom code to Result and ResultSet 
modules.

If I add the code directly in other modules where I need to access DBIC, when I 
don't need that code, I can change it or delete it and it is all right.
If I add code in Result/ResultSet modules, I can't do the same thing very easy, 
because I don't know if that code is not used in some other place, and I need 
to search/grep trying to find if a certain method is used somewhere else, and 
if it is, modify it in all the places to expect the same arguments.

So I tend to create new and new methods and they accumulate and don't create  a 
very clean code.

Is there a way to check easily if a certain method in Result or ResultSet 
modules is used somewhere in the code?

Octavian


___
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] order by field?

[Octavian Rasnita]

From: "Peter Rabbitson" 


> On Tue, Dec 18, 2012 at 10:53:25AM +0200, Octavian Rasnita wrote:
>> From: "Peter Rabbitson" 
>> 
>> > On Mon, Dec 17, 2012 at 11:04:22AM +0100, QE :: Felix Ostmann wrote:
>> >> I am doing such a order with the following SQL:
>> >> 
>> >> ...
>> >> ORDER BY
>> >>   (department = 'Marketing') DESC,
>> >>   (department = 'Sales') DESC,
>> >>   (department = 'Financial') DESC,
>> >>   (department = 'IT') DESC,
>> >>   (department = 'Operations') DESC,
>> >>   ...
>> >> 
>> >> But there is no special way to use this with DBIx::Class :-/
>> >> 
>> > 
>> > Can you elaborate? You can always do:
>> > 
>> > order_by => \' anything you feel like goes here'.
>> > 
>> > Is this not sufficient?
>> > 
>> > Cheers
>> > 
>> 
>> 
>> 
>> I can do:
>> 
>> order_by => \'field(department, "Marketing", "Sales", "IT")',
>> 
>> But the problem is that those names of the departments should be manually 
>> cleaned/escaped before inserting them in that script, to avoid SQL injection.
> 
> I actually misread the above, didn't pay enough attention that only department
> is an identifier. You'd want to do:
> 
> order_by => \[ 'field(department, ?, ?, ?)',
>  [ {} => 'Marketing' ],
>  [ {} => 'Sales' ],
>  [ {} => 'IT' ],
> ],
> 
> Cheers



Great if this method works with order_by too.

Thanks.

Octavian


___
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] order by field?

[Octavian Rasnita]

From: "Peter Rabbitson" 


> On Tue, Dec 18, 2012 at 10:53:25AM +0200, Octavian Rasnita wrote:
>> From: "Peter Rabbitson" 
>> 
>> > On Mon, Dec 17, 2012 at 11:04:22AM +0100, QE :: Felix Ostmann wrote:
>> >> I am doing such a order with the following SQL:
>> >> 
>> >> ...
>> >> ORDER BY
>> >>   (department = 'Marketing') DESC,
>> >>   (department = 'Sales') DESC,
>> >>   (department = 'Financial') DESC,
>> >>   (department = 'IT') DESC,
>> >>   (department = 'Operations') DESC,
>> >>   ...
>> >> 
>> >> But there is no special way to use this with DBIx::Class :-/
>> >> 
>> > 
>> > Can you elaborate? You can always do:
>> > 
>> > order_by => \' anything you feel like goes here'.
>> > 
>> > Is this not sufficient?
>> > 
>> > Cheers
>> > 
>> 
>> 
>> 
>> I can do:
>> 
>> order_by => \'field(department, "Marketing", "Sales", "IT")',
>> 
>> But the problem is that those names of the departments should be manually 
>> cleaned/escaped before inserting them in that script, to avoid SQL injection.
>> 
> 
> Well - you need to check beforehand anyway if the requested columns are
> in fact available. Or are you just letting the RDBMS throw in this case?
> 
>> And another problem might be that the syntax above won't be portable to 
>> other databases.
> 
> But the *concept* itself is not sanely portable to many RDBMS. Hence
> giving this technique space in the official API seems unwise.


Oh, in this case, searching for portability is useless indeed.

Thanks.

Octavian


___
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] order by field?

[Octavian Rasnita]

From: "Peter Rabbitson" 

> On Mon, Dec 17, 2012 at 11:04:22AM +0100, QE :: Felix Ostmann wrote:
>> I am doing such a order with the following SQL:
>> 
>> ...
>> ORDER BY
>>   (department = 'Marketing') DESC,
>>   (department = 'Sales') DESC,
>>   (department = 'Financial') DESC,
>>   (department = 'IT') DESC,
>>   (department = 'Operations') DESC,
>>   ...
>> 
>> But there is no special way to use this with DBIx::Class :-/
>> 
> 
> Can you elaborate? You can always do:
> 
> order_by => \' anything you feel like goes here'.
> 
> Is this not sufficient?
> 
> Cheers
> 



I can do:

order_by => \'field(department, "Marketing", "Sales", "IT")',

But the problem is that those names of the departments should be manually 
cleaned/escaped before inserting them in that script, to avoid SQL injection.

And another problem might be that the syntax above won't be portable to other 
databases.

I was wondering if there is a more portable method, similar to 'page' and 
'rows' keys that makes the select ... limit X,y query portable to all 
databases, no matter if they support the 'limit' keyword or not.

Octavian


___
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

[Dbix-class] order by field?

[Octavian Rasnita]

Hi,

In MySQL is possible to do something like:

select * from table_name order by field(department, 'Marketing', 'Sales', 
'Financial', 'IT', 'Operations');

In other databases it should be done differently

Is it possible to do this search with DBIC?

--Octavian


___
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] Correct (and secure) searching using -like?

[Octavian Rasnita]

From: Bill Moseley 


  On Mon, Oct 8, 2012 at 12:49 AM, Octavian Rasnita  wrote:

  It doesn't look to be very secure to quote the variable $name this way.



  It's still a bind parameter.   But, what I do is remove any existing special 
characters and make sure $name has enough (for some value of enough) characters 
to make it a reasonable search.  Searching for %i% isn't very useful and can 
return a lot of rows.
  At one time I tried to escape special characters but found it cleaner to just 
remove.

  **
  I have also deleted the special chars, but I wanted to be sure that it would 
work securely without deleting them.
  But now I think it should be secure.


  Depending on what you are searching, I suspect often the correct answer is to 
use a full-text search (e.g. tsearch2 in Postgresql) instead.



  **
  I use MySQL, but it is just a simple search in a small table and a fulltext 
search wouldn't be useful.

  Thanks.

  Octavian.
___
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] Correct (and secure) searching using -like?

[Octavian Rasnita]

From: will trillich 

  Octavian -- Inlining the values, as you say, would be fraught with peril -- 
DBI (and DBIx::Class) know better, so it's not a problem. It's not special to 
the -like operator, it's part of how DBIC works. 
  [cut]
  So if someone put nefarious strings in one of the ID values, it'd still be 
quoted.



   
  Yep, thanks. I was wrong, because if DBIC escapes the special chars in the 
values for -like hash keys, it shouldn't matter if those values are stored in a 
scalar var, or a quoted string that might contain scalar vars.

  Octavian
___
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

[Dbix-class] Correct (and secure) searching using -like?

[Octavian Rasnita]

Hi,

I've seen examples of searching in a database using the LIKE operator like:

$rs = $rs->search( {
name => { -like => "%$name%" },
} );

It doesn't look to be very secure to quote the variable $name this way.
Or maybe the special chars in the whole composed string "%$name%" are then 
escaped if -like key is used?
Or is there a better alternative?

Thanks.

--Octavian


___
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

[Dbix-class] It is not possible to "downgrade" a schema...

[Octavian Rasnita]

Hi,

Using Catalyst helper I tried to update the DBIC schema that was created with 
the Catalyst helper, but on another computer, and it gave the following error:

DBIx::Class::Schema::Loader::make_schema_at(): It is not possible to 
"downgrade" a schema that was loaded with use_moose => 1 to use_moose => 0, due 
to differing custom content at 
/home/user1/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/Catalyst/Helper/Model/DBIC/Schema.pm
 line 635 

Then I tried to install the latest version of DBIx::Class::Schema::Loader and 
the latest Catalyst helper, and then the latest DBIx::Class, and run the 
Catalyst helper to update the schema, but it still gives that error.

What can I do? Where can I specify use_moose => 1 to make it work?

Thanks.

--Octavian


___
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] Using a scalar ref with update_or_create

[Octavian Rasnita]

From: Websuche :: Felix Ostmann 
  Subject: Re: [Dbix-class] Using a scalar ref with update_or_create


  We run into the same problem and simple split the problem. Make a find, then 
update or create (simple look what update_or_create do internally)


  Oh, so the answer to my question seems to be that it is not possible to use 
update_or_create. :-(

  Octavian

___
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

[Dbix-class] Using a scalar ref with update_or_create

[Octavian Rasnita]

Hi,

I have tried to use:

$self->update_or_create({
  symbol => $symbol,
  market => $market,
  last_update => \'now()',
});

Am I doing something wrong?

The problem is the row:
  last_update => \'now()',

...because the generated SQL for select looks like:
SELECT ... FROM table_name me WHERE ( ( ... AND me.last_update now() AND ... )

If I add an "=" in that line like:
  last_update => \'=now()',

...then I got another error because the generated SQL for insert becomes:
INSERT INTO table_name(...) VALUES ( ?, =now(), ?, ... )

Isn't possible to use a DB function with update_or_create?

In this case I can send a DateTime string instead of \'now()' but in other 
cases it might be impossible...

Thanks.

Octavian


___
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] Does DBIC support ->create() with more clob fields?

[Octavian Rasnita]

From: "Peter Rabbitson" 

> Octavian Rasnita wrote:
>> Hi all,
>> 
>> I have an Oracle table with 2 clob columns and I am trying to add a new 
>> record in it, using:
>> 
>> my $row = $schema->resultset( 'TblName' )->create( {
>> question => 'This is the first question',
>> answer => 'Answer 1',
>> date_time_q => '2012-01-01 00:00:00',
>> date_time_a => '2012-01-01 10:00:00',
>> } );
>> 
>> my $result = $schema->resultset( 'Sugestii' )->find( $row->id );
>> 
>> print 'question: ', $result->question, "\n";
>> print 'answer: ', $result->answer, "\n";
>> 
>> The result is:
>> 
>> question: Answer 1the first question
>> answer:  
>> 
>> It seems that the data is broken. The values for both clob columns are 
>> stored in the first clob column in the table, or better said, the value of 
>> the second column overwrites the value of the first column, so if the value 
>> of the second column is longer than the value of the first column, the first 
>> column will contain just the value of the second column.
>> And the second column is always empty.
>> 
>> I have searched a lot for helpful information on the net, and I found more 
>> web pages telling very explicitly that an Oracle table can contain just a 
>> single clob column, and many other pages telling also explicitly that an 
>> Oracle table can contain more clob column (and just a single long 
>> column...), so I don't know what's the truth or if it matters.
>> 
>> This code was working before with DBIC but now it is not working anymore...
>> I am using Perl 5.14.2, DBIC 0.08196, DBD::Oracle 1.38, Oracle 11G under 
>> Ubuntu 11.
>> 
>> Do you have any idea what could be the problem? Any solution or workaround?
> 
> Crap! I would hazard a guess this is some subtle behavior change in
> DBD::Oracle. In any case DBIC needs to move away from using the buggy
> blob support in DBD::Oracle, and use the blob OCI calls directly.
> 
> Can you please augment the oracle blob test[1] to work with 2 columns
> at the time instead of 1 as it is now? Better test coverage (I expect
> the tests to fail) will make it easier to implement the necessary
> changes.
> 
> Thanks!
> 
> [1] 
> http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/73oracle.t;h=907c278cd229ab2a58c28b665ca3f356c2471810;hb=HEAD#l374
> 


Yes, I will try to do that.

In the meantime I have tested if the values can be inserted using DBI and it 
worked, so I don't know if DBD::Oracle is the problem because DBI also uses it:

$dbh->do( "alter session set nls_date_format = '-MM-DD HH24:MI:SS'" );
$dbh->do( "alter session set nls_timestamp_format = '-MM-DD HH24:MI:SS.FF'" 
);
$dbh->do( "alter session set nls_timestamp_tz_format='-MM-DD HH24:MI:SS.FF 
TZHTZM'" );
$dbh->do( "alter session set NLS_COMP='LINGUISTIC'" );
$dbh->do( "alter session set NLS_SORT='BINARY_AI'" );

my $sth = $dbh->prepare( "insert into sugestii(id, date_time_q, date_time_a, 
question, answer) values(?, ?, ?, ?, ?)" );

$sth->execute( 1, '2012-01-01 00:00:00', '2012-01-01 10:00:00', 'This is 
the first question', 'Answer 1' );


...and the values for the fields `question` and `answer` were exactly as they 
should be.

(And I have also tried with some older versions of DBD::Oracle but with the 
same error when using DBIC).

Octavian


___
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

[Dbix-class] Does DBIC support ->create() with more clob fields?

[Octavian Rasnita]

Hi all,

I have an Oracle table with 2 clob columns and I am trying to add a new record 
in it, using:

my $row = $schema->resultset( 'TblName' )->create( {
question => 'This is the first question',
answer => 'Answer 1',
date_time_q => '2012-01-01 00:00:00',
date_time_a => '2012-01-01 10:00:00',
} );

my $result = $schema->resultset( 'Sugestii' )->find( $row->id );

print 'question: ', $result->question, "\n";
print 'answer: ', $result->answer, "\n";

The result is:

question: Answer 1the first question
answer:  

It seems that the data is broken. The values for both clob columns are stored 
in the first clob column in the table, or better said, the value of the second 
column overwrites the value of the first column, so if the value of the second 
column is longer than the value of the first column, the first column will 
contain just the value of the second column.
And the second column is always empty.

I have searched a lot for helpful information on the net, and I found more web 
pages telling very explicitly that an Oracle table can contain just a single 
clob column, and many other pages telling also explicitly that an Oracle table 
can contain more clob column (and just a single long column...), so I don't 
know what's the truth or if it matters.

This code was working before with DBIC but now it is not working anymore...
I am using Perl 5.14.2, DBIC 0.08196, DBD::Oracle 1.38, Oracle 11G under Ubuntu 
11.

Do you have any idea what could be the problem? Any solution or workaround?

The SQL queries generated by the code above are:

alter session set nls_date_format = '-MM-DD HH24:MI:SS': 
alter session set nls_timestamp_format = '-MM-DD HH24:MI:SS.FF': 
alter session set nls_timestamp_tz_format='-MM-DD HH24:MI:SS.FF TZHTZM': 
alter session set NLS_COMP='LINGUISTIC': 
alter session set NLS_SORT='BINARY_AI': 

INSERT INTO sugestii ( answer, date_time_a, date_time_q, id, question) VALUES ( 
?, ?, ?, ?, ? ): 'Answer 1', '2012-01-01 10:00:00', '2012-01-01
 00:00:00', '9734', 'This is the first question'

SELECT me.id, me.date_time_q, me.name_q, me.email_q, me.question, 
me.date_time_a, me.name_a, me.email_a, me.answer, me.markup_lang, me.active 
FROM sugestii me WHERE ( me.id = ? ) : '9734'

Thanks.

Octavian


___
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] Dummy DBIC columns?

[Octavian Rasnita]

From: Bill Moseley 




  I have two databases that are very similar.  For example, the "person" table 
in one db has an "email" column and the other does not have that column, 
otherwise the tables are the same.


  My application uses the schema with the table that has the "email" column, so 
there's places in the application that call $person->get_column( 'email' ), and 
also $person_rs->create( { name => $name, email => $email } );  


  Now, I want to use the second schema with the same application, but as 
mentioned above does not have an "email" column on the person table.  it's not 
important that the column does not exist.


  My question is what can I do to make get_column and create (and other methods 
that assume there is an "email" column) work without this column defined in the 
result class.  Is there any way to have define a column that is never used when 
constructing database queries?




  Hi,

  Use in the Result class:

  __PACKAGE__->mk_group_accessors( simple => 'email' );

  Octavian
___
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] Module versioning

[Octavian Rasnita]

From: "Robert Kinyon" rob.kin...@gmail.com
> At $work, we build RPMs of the stuff we depend on. Then, we build RPMs of
> our stuff with dependencies on the RPMs we built of CPAN modules.
> Then, we have our own internal RPM repository that we deploy to prod
> from.
> 
> That way, we control our upgrades, we know what we have where, and we
> don't worry about module $VERSION numbers.
> 
> Rob


Hi Rob,

Do you know some pages with information about this process?

I am interested in:
- generate rpm and deb packages from CPAN packages;
- Find out if there are special problems in case of the modules that use XS 
code;
- create a local rpm/deb repository;
- information about the workflow in general, because there are few informations 
and comparisons among the workflow types.

Thanks.

Octavian



___
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] DBIx::Class with PerlApp

[Octavian Rasnita]

From: "Peter Rabbitson" 
> Octavian Rasnita wrote:
>> And it works, although with a low performance because DBIx::Class has a big 
>> startup lag.
>> 
> 
> This is not really correct. The slow startup comes from FS operations
> while load_namespaces scans for result classes. There are ways to
> mitigate this if so required, DBIC itself is quite fast to startup.
> 


Please tell me where can I find more information about how to do that.

Thanks.

Octavian


___
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] DBIx::Class with PerlApp

[Octavian Rasnita]

I have succeeded to build a Windows executable using:

perlapp --add=Moose --add=MooseX::NonMoose 
--add=MooseX::NonMoose::Meta::Role::Class 
--add=MooseX::NonMoose::Meta::Role::Constructor --add=namespace::autoclean 
--add=DBIx::Class::Schema test.pl

And the program test.pl is a simple program that uses DBIx::Class.

And it works, although with a low performance because DBIx::Class has a big 
startup lag.

DBIx::Class is appropriate for persistent environments.

Octavian

- Original Message - 
From: "Ogla Sungutay" 
To: 
Sent: Wednesday, February 16, 2011 2:13 AM
Subject: [Dbix-class] DBIx::Class with PerlApp


> Hi guys,
> 
> Has anyone had any experience with ActiveState Perl & PerlApp? I build my
> DBIx::Class project as a Windows executable but PerlApp cannot find or
> detect
> the generated source, causing the app to crash.
> 
> Thanks.
> 
> --
> V. Ogla Sungutay,
> GUI Programmer
> www.lyciasoft.com
> skype:oglasungutay
>





> ___
> 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] potential convert from rose::db

[Octavian Rasnita]

From: "Jim Green" 

Hello,
when I was reading about moose and orm I found dbic will be based on
moose in next major release. I am currently using RDBO and was
satisfied with it with some exceptions(no sybase support right now, no
potential move to moose)..

RDBO has performance advantage compared with DBIC according to this
benchmark http://code.google.com/p/rose/wiki/RDBOBenchmarks. Since I
like moose and would like to have a standard/popular orm with moose as
base. Could anyone convince me that DBIC performance penalty is minor
compared with its potential integration with moose, also with moose
integration I guess the performance will be hit?

Thanks!
Jim.



If the most important thing is the performance, then I think you don't need 
to use any ORM.


If other things are more important than the performance, (thinking that the 
database access is the main cause of slowness, and not the SQL strings 
generation), then you need to compare those things among the ORMs, and not 
their speed.


Octavian


___
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] DBIx::Class, mysql and 'SQL_CALC_FOUND_ROWS'/'SELECT FOUND_ROWS()'

[Octavian Rasnita]

From: "Emmanuel OTTON" 

Le 15 déc. 2010 à 11:29, Matthew Braid a écrit :

> I already know how to get paged results. I already know about the
> pager object. Guess how the pager object gets its 'total number of
> records' count when using mysql? SELECT COUNT(*)..., even though mysql
> has SQL_CALC_FOUND_ROWS and FOUND_ROWS() available, which means
> DBIx::Class does NOT use the most efficient means of getting paged
> results when it comes to mysql - otherwise it'd already be using
> SQL_CALC_FOUND_ROWS under the hood (which it isn't, at least according
> to the debug output when DBIC_TRACE is true). Portability is nice, but
> I'd like it to _work better_. Purity in this case can take a back seat
> to practicality.
> 
>> From everything I've seen, DBIx::Class simply does not support the
> SQL_CALC_FOUND_ROWS and FOUND_ROWS() options of mysql (even invisibly
> under the hood), and thus is a sub-optimal choice when it comes to
> pagination of large sets of data.
> 
> I'm currently looking at adding functionality to
> DBIx::Class::ResultSet and DBIx::Class::Storage::DBI::mysql so that
> the 'hit the database' class of methods in DBIx::Class::ResultSet will
> accept an attribute of something like 'concurrent_count', and
> DBIx::Cass::Storage::DBI::mysql will change it's select query when it
> is in effect (and hopefully other storage engines will ignore it).
> It's a little fiddly, but I think it's doable. I don't want to add
> SQL_CALC_FOUND_ROWS to _all_ select statements under mysql because for
> queries with LIMIT that you don't care about the count for it is less
> efficient again.
> 
> Fingers crossed.
> 
> MDB

Sorry, made a fool of myself by answering too fast without fully understanding 
your question.

Just two cents more, for what it's worth: depending on the complexity of the 
query, some users measured faster response time with the double (select + 
select count) method than with the found_rows (which may be the reason why 
DBIx::Class makers, in all their wisdom, chose to use this method ?).


It happened to me to have much slower searches when *using* found_rows in some 
cases, so yes, it is possible.
It wouldn't be bad if there would be a way of choosing the wanted method though.

Something like:

rows => 20,
page => $page,
type_of_counting => 'select_rows',

type_of_counting may have importance only for MySQL so the code might be 
portable...

Octavian


___
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

[Dbix-class] test1 from gmail

[Octavian Rasnita]

Octavian


___
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

[Dbix-class] test2 - from ssifbroker

[Octavian Rasnita]

Octavian


___
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] Unicode conversion problems

[Octavian Rasnita]

Hi Jesse,

Check:

http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/

or search for
mysql double encoding
with Google and you'll find more step by step instructions.

I also had that problem and I dumped a few tables with mysqldump (because not 
all the tables were containing bad encoded data) then I have re-imported them 
with a good encoding. I was using DBIx::Class::UTF8Columns but I switched to 
not using it anymore, but I added the attribute mysql_enable_utf8 => 1 to the 
model config.

Octavian

- Original Message - 
From: "Jesse Sheidlower" 
To: "DBIx::Class user and developer list" 
Sent: Tuesday, July 06, 2010 12:55 AM
Subject: Re: [Dbix-class] Unicode conversion problems


On Mon, Jul 05, 2010 at 11:02:02PM +0200, Matias E. Fernandez wrote:
> Hello Jesse
> 
> I'm pretty sure your data has been UTF-8 encoded twice. Consider this example:
> 
> use strict;
> use warnings;
> 
> use Encode;
> 
> # $string is UTF-8, but Perl doesn't know
> my $string = 'Pérez-Reverte, Arturo Кири́ллица ქართული  汉字 / 漢';
> # $double_utf8 contains the double UTF-8 encoded string
> # note that this is an implicit ISO-8859-1 to UTF-8 conversion
> my $double_utf8 = Encode::encode('UTF-8', $string);
> 
> print "double encoded UTF-8:\n", "$double_utf8\n\n";
> 
> # let Perl believe that $double_utf8 is UTF-8
> Encode::_utf8_on($double_utf8);
> # run $double_utf8 through a UTF-8 to ISO-8859-1 conversion
> my $double_utf8_to_latin1 = Encode::decode('ISO-8859-1', $double_utf8);
> 
> print "double UTF-8 to ISO-8859-1:\n", "$double_utf8_to_latin1\n\n";

Right, that looks "correct". But this is latin1, not UTF-8,
so...

> So why is your data in the database double encoded UTF-8?
> The problem is that you're not using the mysql_enable_utf8
> option (see the DBD::mysql documentation). If you don't use
> that option as a part to the call to 'connect()', DBD::mysql
> will the configure the connection in a way that MySQL
> believes it's being sent ISO-8859-1. Because you're table is
> configured to store character data as UTF-8, MySQL converts
> the received data from ISO-8859-1 to UTF-8. There you have
> double encoded UTF-8!

I am now, but there was a point when I hadn't been, or these
tables were first set up as latin-1, or some other screwup.
The problem is, the tables do exist now.

> The solution is simply to use mysql_enable_utf8 as part of
> the call to 'connect()'. If you're using DBIx::Class I
> recommend also disabling the mysql_auto_reconnect option,
> this will save you a lot of headache.

But that doesn't help me right now, it only helps me for the
future.

That is, I currently have data in the database, some of which
is double-encoded UTF-8. If I try to retrieve this, setting
mysql_enable_utf8 doesn't help. That is if I take my existing
data (e.g. the example I originally posted), connect to MySQL
with mysql_enable_utf8, and pull the data with a Perl script,
I still get junk.

In your above example you show how to un-double-encode the
data I have, but only by turning it into latin1, right? How do
I take my existing data and turn it into proper UTF-8, at
which point I can make sure everything is set correctly so
that I never have this problem again?

Thanks for looking at this so closely.

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] minimalistic Moose / DBIC glue module

[Octavian Rasnita]

What do you think it is the preferable way of passing the parameters to the 
custom methods defined in the ResultSet class for other more complex methods 
like ->search(), update(), create(), update_or_create()...?
(as a list of parameters, or as a hash?)

->add($val1, $val2, $val3);

then in the ResultSet:

sub add {
  my ($self, $val1, $val2, $val3) = @_;
  $self->create(var1 => $val1, var2 => $val2, var3 => $val3);
}

or

->add(var1 => $val1, var2 => $val2, var3 => $val3);

then in the resultset:

sub add {
  my ($self, %params) = @_;
  $self->create(%params);
}

I am not sure which would be the most maintainable method yet...

Octavian

- Original Message - 
From: "Eden Cardim" 
To: "DBIx::Class user and developer list" 
Sent: Sunday, June 27, 2010 4:41 AM
Subject: Re: [Dbix-class] minimalistic Moose / DBIC glue module


>> "John" == John Napiorkowski  writes:
> 
>John> I generally try to do this as well, however I find that doing
>John> this for simple finds on primary keys gets a bit tedious, and
>John> frankly I wonder if I'm not wasting time and adding to
>John> complexity.  Would be interested in your thoughts.
> 
> Yes, letting ->find pass through is ok, since it isn't a very complex
> part of the DBIC API, things like ->next and ->all are ok too, as long
> as you remain agnostic about what underlying object is actually handling
> the methods.
> 
> ___
> 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] Excluding columns from a query

[Octavian Rasnita]

From: "fREW Schmidt" 
On Thu, Feb 18, 2010 at 7:02 AM, BUCHMULLER Norbert 
wrote:



On Wed, 17 Feb 2010 09:19:44 -0600 fREW Schmidt wrote:

>1. Only have the remove-columns affect the default select, since
> having it affect an explicit columns list is a little silly.
>2. Having it affect the *current* select list; the use-case for this
>would be if you had an explicit columns list in previous search (or 
> a

>predefined search more likely) and you wanted to take away a column
> from that.

I'd vote for #2 as it would make possible things like that (a variant of
the use-case you mentioned):

 $rs->method_that_adds_a_calculated_column->search(
  ...
  {
 remove_columns => ['big_text_column'],
  }
 );  # and still have the calculated column (added via "+columns")

Also this behaviour is the less surprising: with #1 the behaviour of
'remove_columns' (or '-columns' or whatever it will be called)
would depend on whether a given column comes from the result source or
from a previous search() call.

norbi



ok, I've heard from I think 4 people regarding this now and everyone is in
favor of #2.  I'll implement it soon (within a week hopefully.)

--
fREW Schmidt
http://blog.afoolishmanifesto.com



Would it be possible to specify columns from joined tables like in the line 
below?


 remove_columns => ['big_text_column', 
'another_table.another_big_column'],


It would be great!

Octavian


___
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] Excluding columns from a query

[Octavian Rasnita]

From: "Robert Sedlacek" 


Hey Nick,

Nick Wellnhofer wrote:

It's simply an optimization. I have a table with a text column that can
contain tens of KB of data per row. I also have some queries that don't
need that column, so I'd like to avoid unnecessarily fetching all that
content from the DB.


Depending on the queries you need to perform you might get away with
separating the larger content out into a separate result source that you
prefetch only when required.

regards,
--
Robert 'phaylon' Sedlacek



Do I understand correctly that it is possible to create a Result class with 
fewer columns, give it another name but make it use the same table, and use 
it in relations as the original Result class?


Is it possible to subclass a Result class and just specify that we want to 
skip some columns from it?

If yes, is there a method for "deleting" those columns?

Thanks.

Octavian


___
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] So they want ME to give a presentationaboutDBIx::Class

[Octavian Rasnita]

From: "Ihnen, David" 
Good point, especially for an introduction... why to use an ORM.
I'll have to spin up myself a bit on Template Toolkit (I'd been using 
HTML::Template previously) and will probably have to merely mention rather 
than explore the FormMu stuff, as I've never worked in Catalyst.

David

You can tell them that they can use a resultset in a template directly like:
[% IF users != 0 -%]

[% WHILE (user = users.next) -%]
 [% user.id %] [% user.name %]

 [% blogs = user.blogs_rs -%]
 [% WHILE (blog = blogs.next) -%]
   [% blog.title %]

   [% articles = blog.articles_rs -%]
   [% WHILE (article = articles.next) -%]
 [% article.title %] [% article.body %]
   [% END -%]
 [% END -%]
[% END -%]

[% END -%]

And that loop of loops executes an SQL query only if the resultset "users" 
contains elements. Some of the conditions may not have a relation with the 
database and the user could use [% IF something == "ok" %], and if the var 
`something` won't be equal to "ok", then no SQL query will be executed.


Of course, using this way, the designer will be able to print something like

[% user.password %] or [% user.another_private_info %]

and the programmers might not want to allow the designers of the templates 
to have that freedom, but if this is not an issue, it is a very helpful way 
of using DBIC.


Octavian


___
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] So they want ME to give a presentation aboutDBIx::Class

[Octavian Rasnita]

From: "Ihnen, David" 
So seems to be interest in the Seattle Perl Users Group to learn more about 
using DBIx::Class - and since no good deed (like suggesting an interesting 
topic) goes unpunished, I'm drafted to do a presentation next month to the 
group about DBIx::Class!


I know I'll be deriving a lot of my material from the fine manual pages and 
cookbook and my experience, but thought I'd toss this out there in case 
anybody has further thoughts or 'don't forget to tell them...' sorts of 
things to say as I collect materials to prepare for this.



Don't forget to tell them by the use of DBIC in TT templates and maybe even 
about using HTML::FormFu::Model::DBIC.

These show good reasons for using an ORM in general instead of DBI.

Octavian


___
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] Removing rows older than a given number of minutes ...

[Octavian Rasnita]

From: "Rob Kinyon" 


On Sat, Jan 23, 2010 at 16:28, Kiffin Gish  wrote:

I want to delete all rows with a given state which have not been
modified for a given time in seconds.

my @rows = rs->search({ state => $state }, { last_modified => ??? });
$_->delete for (@rows);

How can I best do this?


$rs->search({
   state => $state,
   last_modified => [ "< TIMEDIFF( NOW(), ? SECONDS", $seconds ],
})->delete_all;

Standard SQL::Abstract stuff, described in both the DBIC cookbook and
the SQL::Abstract docs.


Is the following SQL

< TIMEDIFF( NOW(), ? SECONDS

a standard SQL code supported by more databases?

Until now I was using SQL codes like

< now() - interval ? second

but I am not sure if the keyword "interval" is used in other databases than 
MySQL.


Thanks.

Octavian


___
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] Fwd: [Catalyst] Selecting from more tables(DBIC-bug?)

[Octavian Rasnita]

From: "Carl Johnstone" 

Octavian Rasnita wrote:

my $uuu = $schema->resultset('User')->search({},{
prefetch => {blogs => 'blog_comments'},
select => ['me.id'],
as => ['user_id'],
});

print $uuu->first->username;

I know, but I would like to prefetch only just a few columns from the
joined tables, not all of them.

Even if I would add to that arrayref some columns from the joined
table,
DBIC will get all the columns from those tables.


If you called $uuu->first->blogs->title with the prefetch, it would 
perform

another query and pull back all the columns in the related row.

With prefetch you're giving DBIC a hint that you'll be performing those
inflations, so it builds a single query (using a join) that allows it to
pre-inflate. So currently the behaviour of DBIC with and without the
prefetch are the same.


So is it not possible to create the following query with DBIC?

SELECT me.id, blogs.id, blog_comments.id
FROM user me LEFT JOIN blog blogs ON blogs.user = me.id
LEFT JOIN blog_comment blog_comments ON blog_comments.blog = blogs.id;


You could switch to a straight join which should allow you to specify your
columms although they would then be in the primary resultset rather than 
as

related objects.


Can you please tell me how to do that?

Thanks.

Octavian


___
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] Fwd: [Catalyst] Selecting from more tables (DBIC-bug?)

[Octavian Rasnita]

From: "iain" 




my $uuu = $schema->resultset('User')->search({},{
prefetch => {blogs => 'blog_comments'},
select => ['me.id'],
as => ['user_id'],
});

print $uuu->first->username;

I think the single column that should be printed should be me.id, but 
here is the generated SQL:


SELECT me.id, blogs.id, blogs.user, blogs.date_create, blogs.date_modify, 
blogs.title, blogs.body, blogs.markup_lang, blogs.tags, blogs.active, 
blog_comments.id, blog_comments.user, blog_comments.blog, 
blog_comments.date_time, blog_comments.body, blog_comments.markup_lang, 
blog_comments.active FROM user me LEFT JOIN blog blogs ON blogs.user = 
me.id LEFT JOIN blog_comment blog_comments ON blog_comments.blog = 
blogs.id ORDER BY blogs.user, blog_comments.blog:


Is it normal to get all the columns from the joined tables?



Yes. You have asked DBIC to prefetch the data from the blogs tables. So it 
has.


Iain.



I know, but I would like to prefetch only just a few columns from the joined 
tables, not all of them.


Even if I would add to that arrayref some columns from the joined table, 
DBIC will get all the columns from those tables.


So, it is not possible to select only some specific columns from the joined 
tables?


Thanks.

Octavian


___
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] Fwd: [Catalyst] Selecting from more tables (DBIC- bug?)

[Octavian Rasnita]

From: "Ronald J Kimball" 

Thomas L. Shinnick wrote:


I'm having some of the same questions regarding +select and +as .  It
seems that using '+select' and '+as' does not stop other columns from
being returned.  I'm using DBIC 0.08115.


What do you expect +select and +as to do??

+select

Indicates additional columns to be selected from storage. Works
the same as "select" but adds columns to the selection.

+as

Indicates additional column names for those added via "+select".
See "as".

+select and +as are behaving exactly as documented.  If you want to select 
/only/ the specified columns use select and as, not +select and +as.


Ronald



Hi Ronald,

I am not sure my message will reach the list, because it rejects my messages 
as spam, but here is a short test I made:


$ENV{DBIC_TRACE}++;
use BRK::Schema;

my $schema = BRK::Schema->connect("dbi:mysql:database=brk", "root");

my $uuu = $schema->resultset('User')->search({},{
prefetch => {blogs => 'blog_comments'},
select => ['me.id'],
as => ['user_id'],
});

print $uuu->first->username;

I think the single column that should be printed should be me.id, but here 
is the generated SQL:


SELECT me.id, blogs.id, blogs.user, blogs.date_create, blogs.date_modify, 
blogs.title, blogs.body, blogs.markup_lang, blogs.tags, blogs.active, 
blog_comments.id, blog_comments.user, blog_comments.blog, 
blog_comments.date_time, blog_comments.body, blog_comments.markup_lang, 
blog_comments.active FROM user me LEFT JOIN blog blogs ON blogs.user = me.id 
LEFT JOIN blog_comment blog_comments ON blog_comments.blog = blogs.id ORDER 
BY blogs.user, blog_comments.blog:


Is it normal to get all the columns from the joined tables?

Thank you.

Octavian


___
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] convention for naming primary keysto avoidambiguousselects

[Octavian Rasnita]

From: "Peter Rabbitson" <[EMAIL PROTECTED]>

I think that if the query needs to search in a table named "me" which is
not the primary table, it probably gives another name instead of "me"
for the main table, so that name shouldn't need to be hard coded in the
programs.

By the way, does anyone know what happends if a secondary table is named
"me"?


The name of the table is irrelevant as they are all aliased anyway. If
you have a relationship called 'me' (hence JOIN  me), then I
believe DBIC will switch it to me2 (just like it does with stacked joins
over the same relationship[1]). If not - it might very well be a bug,
tests/patches welcome :)

Cheers



Ok, I understand. So the main table will be named "me" and the "me" table 
will be named "me2".


But I still don't see yet why it wouldn't be possible to prepend the prefix 
"me." to all the columns from the where conditions if those columns don't 
have any kind of prefix.


Octavian


___
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] convention for naming primary keys to avoidambiguousselects

[Octavian Rasnita]

From: "Noel Burton-Krahn" <[EMAIL PROTECTED]>

This is a reasonable query:  load a person with address by the
person's id.  The 'id' column is unambiguous in the search call.
However, DBIx generates ambiguous SQL:

DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st execute
failed: Column 'id' in where clause is ambiguous [for Statement
"SELECT COUNT( * ) FROM person me LEFT JOIN address address ON (
address.person_id = me.id ) WHERE ( id = ? )" with ParamValues: 0='1']
at ./t/dbix_unique_prefix_where.t line 116

The simple way to fix it is to prefix "id" as "me.id".  I'd argue DBIx
should prefix all unqualified column names in the first argument to
search().  Asking users to rely on DBIx's current internal strategy of
using "me" as a prefix is not safe.  What if "me" changes to "this" in
a future release?  What if I make a table named "me"?

~Noel


Yes you are right, but maybe others already tried to find a solution for 
this, but couldn't do it.


If the query searches only in a single table, then the column name can be 
used without the table prefix.
If the query uses 2 tables, but the column name is not found in both tables, 
then the column name can be also used without a prefix.
If the query searches in 2 or more tables and the column name appears more 
than in a table, then the prefix should be added.


Well, in the first 2 cases, it is not a problem if that prefix is also 
added, so this solution can be possible.


I think that if the query needs to search in a table named "me" which is not 
the primary table, it probably gives another name instead of "me" for the 
main table, so that name shouldn't need to be hard coded in the programs.


By the way, does anyone know what happends if a secondary table is named 
"me"?


Octavian


___
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] convention for naming primary keys to avoid ambiguousselects

[Octavian Rasnita]

From: "David Schmidt" <[EMAIL PROTECTED]>

Hello List,

I used to have an "id INTEGER PRIMARY KEY" column in every table.
Now I am using DBIC and repeatedly run into this kind of error.

SELECT me.id, me.title, me.artist FROM cds me WHERE ( me.artist = ? ): '2'
SELECT me.id, me.title, me.artist, artist.id, artist.name FROM cds me
JOIN artists artist ON ( artist.id = me.artist ) WHERE ( id = ? ): '1'
DBI Exception: DBD::SQLite::db prepare_cached failed: ambiguous column
name: id(1) at dbdimp.c line 271 [for Statement "SELECT me.id,
me.title, me.artist, artist.id, artist.name FROM cds me  JOIN artists
artist ON ( artist.id = me.artist ) WHERE ( id = ? )"] at
/usr/local/share/perl/5.8.8/DBIx/Class/Schema.pm line 954

is it best(-DBIC)-practice to name primary keys _id to
avoid these errors or is there some other solution?

thanks in advance

David


It is better to use table_name.id in the DBIC code that wants to access the 
column id of that table, of course, only in case you need to use 2 or more 
tables in the query.


$c->model('DB::TableName')->search({
 'the_table.id' => 1,
}
...

Octavian



___
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: FW: [Dbix-class] Filtering module

[Octavian Rasnita]

From: "Matt S Trout" <[EMAIL PROTECTED]>


$obj->get_column($col); # COLUMN VALUE

$obj->$col; # ATTRIBUTE VALUE

If I could time travel, I'd make it _get_column so this fact was obvious.
But too much code now relies on it, sadly.

I suspect when we create a new, cleaner row object interface, perhaps as a
factor out in the 09 process, things like get_column will simply go away 
so

people don't keep making this mistake.



I remember that in some cases $obj->$row doesn't work and we must use 
$obj->get_column('col').


Was DBIC improved so this is not the case anymore?

Octavian


___
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] RHEL / CentOS performance finally fixed?

[Octavian Rasnita]

Hi,

Does anyone know if (and how) this patch can be applied to Fedora?

Thanks.

From: "Nigel Metheringham" <[EMAIL PROTECTED]>
>>> Looks like they've (finally) fixed the bless/overload issue.
>>
>>
>> Is this the bug referred to by the DBIx startup message?
> 
> Yes.
> 
> And the bug that all the fuss on slashdot and the like was about a few  
> weeks back.
> 
> Has anyone done any decent testing on this yet, or looked at what  
> patchset they actually applied?
> 
> Nigel.
> --
> [ Nigel Metheringham [EMAIL PROTECTED] ]
> [ - Comments in this message are my own and not ITO opinion/policy - ]
> 
> 
> ___
> 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/[EMAIL PROTECTED]

___
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/[EMAIL PROTECTED]