Re: [Dbix-class] [Script] dbic_ddl_admin.pl a script for creating sql diffs, deploying schemas and upgrading them
Hi, Gordon Irving wrote (25 Nov 2009 19:46:38 GMT) : I have knocked up a script, dbic_ddl_admin.pl for creating ddl files and sql diffs. The script can also be used to deploy to new databases or upgrade existing schemas. I have found it useful and would like to share it with other DBIC users. Thanks. By the way, is the At the moment, only SQLite and MySQL are supported. statement that can be read in DBIx::Class::Schema::Versioned's POD (0.08112) still valid? This POD also says that This is due to spotty behaviour in the SQL::Translator producers, please help us by enhancing them. Ask on the mailing list or IRC channel for details. May I have the details wrt. PostgreSQL? Bye, -- Guillaume Chambriat g...@riseup.net ___ 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] Duplicate entry error on create
Hi,
I am getting the following error when I attempt to create a record.
DBD::mysql::st execute failed: Duplicate entry '0' for key 3 [for
Statement INSERT INTO products ( distribution, caption,
contributor_id, ...) VALUES(( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) with
ParamValues: 0='1', 1='Earth', 2='3')
Is 'key 3' referring to 'the 3rd item in the INSERT list (contributor_id)?
contributor_id is a foreign key. There is a one_to_many between
products and the Contributors class/table.
__PACKAGE__-belongs_to(
contributor_id,
MyAPP::Schema::Contributors,
{ contributor_id = contributor_id },
);
Assuming the error is referring to belongs_to, is it complaining
because there is an existing record in that table? Or more explicitly,
instead of this:
my $row = $schema-resultset('Products')-find_or_create({
distribution = 1,
caption= 'Earth',
contributor_id = {code=$var,
name=$name},
});
should I be doing
my $contrib = $schema-resultset('Contributors')-find_or_create({
code = $var,
name = $name,
},
{ key = code_UNIQUE});
my $row = $schema-resultset('Products')-find_or_create({
distribution = 1,
caption= 'Earth',
contributor_id =$contrib
});
Am I expecting too much for find_and_create to
automagically_vivicationally cascade into related tables?
TIA,
Dp.
___
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] create failures with has_one
2009/10/19 Dermot paik...@googlemail.com:
2009/10/19 Wallace Reis wall...@reis.org.br:
On 15/10/2009, at 21:08, Dermot wrote:
my $record = {
'validation_pass' = 1,
'files' = [],
'active' = 1,
'name' = 'Gustav MR',
'clipdata' = [
{
'contrib_id' = 2,
}
],
'contrib_id' = 2,
};
Use a HashRef for clipdata rel instead of a ArrayRef.
Here, you should note that currently find (and update) part of *_or_create
methods will fail for nested relations, so you need to call
find(update)_or_create for relationships separately.
Is this still true? At least of 0.08114.
Dp.
___
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] Duplicate entry error on create
Dermot == Dermot paik...@googlemail.com writes:
Dermot Hi, I am getting the following error when I attempt to
Dermot create a record.
Dermot DBD::mysql::st execute failed: Duplicate entry '0' for key 3
Dermot [for Statement INSERT INTO products ( distribution,
Dermot caption, contributor_id, ...) VALUES(( ?, ?, ?, ?, ?, ?, ?,
Dermot ?, ?, ?, ? ) with ParamValues: 0='1', 1='Earth', 2='3')
Dermot Is 'key 3' referring to 'the 3rd item in the INSERT list
Dermot (contributor_id)?
No, that's a mysql error, you're probably violating a constraint on your
table with those values.
Dermot should I be doing
Dermot
Dermot my $contrib = $schema-resultset('Contributors')-find_or_create(
Dermot {
Dermot code = $var,
Dermot name = $name,
Dermot },
Dermot { key = code_UNIQUE }
Dermot );
Dermot my $row = $schema-resultset('Products')-find_or_create(
Dermot {
Dermot distribution = 1,
Dermot caption= 'Earth',
Dermot contributor_id = $contrib
Dermot }
Dermot );
Dermot Am I expecting too much for find_and_create to
Dermot automagically_vivicationally cascade into related tables?
No, you're just doing it wrong, try:
my $row = $schema-resultset('Products')-find_or_create(
{
distribution = 1,
caption = 'Earth',
contributor = { code = $var, name = $name }
}
);
--
Eden Cardim Need help with your Catalyst or DBIx::Class project?
Code Monkeyhttp://www.shadowcat.co.uk/catalyst/
Shadowcat Systems Ltd. Want a managed development or deployment platform?
http://edenc.vox.com/http://www.shadowcat.co.uk/servers/
___
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] Duplicate entry error on create
Dermot == Dermot paik...@googlemail.com writes:
Dermot __PACKAGE__-belongs_to( contributor_id,
MyAPP::Schema::Contributors,
Dermot { contributor_id = contributor_id },
Dermot );
Also note you might want to change this to
__PACKAGE__-belongs_to( contributor, MyAPP::Schema::Contributors,
{ contributor_id = contributor_id },
);
To avoid confusion between the contributor relationship and the
contributor_id column.
--
Eden Cardim Need help with your Catalyst or DBIx::Class project?
Code Monkeyhttp://www.shadowcat.co.uk/catalyst/
Shadowcat Systems Ltd. Want a managed development or deployment platform?
http://edenc.vox.com/http://www.shadowcat.co.uk/servers/
___
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] Duplicate entry error on create
2009/12/9 Eden Cardim edencar...@gmail.com:
Dermot == Dermot paik...@googlemail.com writes:
Dermot Hi, I am getting the following error when I attempt to
Dermot create a record.
Dermot DBD::mysql::st execute failed: Duplicate entry '0' for key 3
Dermot [for Statement INSERT INTO products ( distribution,
Dermot caption, contributor_id, ...) VALUES(( ?, ?, ?, ?, ?, ?, ?,
Dermot ?, ?, ?, ? ) with ParamValues: 0='1', 1='Earth', 2='3')
Dermot Is 'key 3' referring to 'the 3rd item in the INSERT list
Dermot (contributor_id)?
No, that's a mysql error, you're probably violating a constraint on your
table with those values.
Yes. I was hoping I could find out what 'key 3' was referring to.
No, you're just doing it wrong, try:
my $row = $schema-resultset('Products')-find_or_create(
{
distribution = 1,
caption = 'Earth',
contributor = { code = $var, name = $name }
}
);
Oh! Should I be using the name of the accessor_name from the belongs_to?
Dp.
___
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] Duplicate entry error on create
Dermot wrote: 2009/12/9 Eden Cardim edencar...@gmail.com: Dermot == Dermot paik...@googlemail.com writes: Dermot Hi, I am getting the following error when I attempt to Dermot create a record. Dermot DBD::mysql::st execute failed: Duplicate entry '0' for key 3 Dermot [for Statement INSERT INTO products ( distribution, Dermot caption, contributor_id, ...) VALUES(( ?, ?, ?, ?, ?, ?, ?, Dermot ?, ?, ?, ? ) with ParamValues: 0='1', 1='Earth', 2='3') Dermot Is 'key 3' referring to 'the 3rd item in the INSERT list Dermot (contributor_id)? No, that's a mysql error, you're probably violating a constraint on your table with those values. Yes. I was hoping I could find out what 'key 3' was referring to. The third unique index on the table. Ronald ___ 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] Duplicate entry error on create
2009/12/9 Ronald J Kimball rkimball+dbixcl...@pangeamedia.com: Dermot wrote: 2009/12/9 Eden Cardim edencar...@gmail.com: Dermot == Dermot paik...@googlemail.com writes: Dermot Hi, I am getting the following error when I attempt to Dermot create a record. Dermot DBD::mysql::st execute failed: Duplicate entry '0' for key 3 Dermot [for Statement INSERT INTO products ( distribution, Dermot caption, contributor_id, ...) VALUES(( ?, ?, ?, ?, ?, ?, ?, Dermot ?, ?, ?, ? ) with ParamValues: 0='1', 1='Earth', 2='3') Dermot Is 'key 3' referring to 'the 3rd item in the INSERT list Dermot (contributor_id)? No, that's a mysql error, you're probably violating a constraint on your table with those values. Yes. I was hoping I could find out what 'key 3' was referring to. The third unique index on the table. Ronald Brilliant. Thank you Ronald that helps narrow it down. Dp. ___ 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] MSSQL - problem sorting by joined column while limiting rows
Alan Humphrey wrote:
-Original Message-
From: Peter Rabbitson [mailto:rabbit+d...@rabbit.us]
Sent: Saturday, December 05, 2009 1:35 AM
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column
while limiting rows
Alan Humphrey wrote:
Hi -
If I try to sort by a column in a joined table in a MSSQL database
AND limit
the number of rows, the generated SQL is bad. Here's the code:
my $result = $schema-resultset('SurveyorsSurveySites')-search({},
{join = 'surveyor',
order_by = ['surveyor.name'],
rows = 5
});
And here's the generated SQL.
SELECT TOP 100 PERCENT id, surveyor_id, survey_site_id, year FROM (
SELECT
TOP 5 me.id, me.surveyor_id, me.survey_site_id, me.year FROM
surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id =
me.surveyor_id ORDER BY surveyor.name ASC ) me ORDER BY
surveyor.name
It looks like right-side joins finally got nailed. Please test this
branch[1] and report how things are. As a bonus you now get proper
offset-past-end-of-rs limits (i.e. you have 5 rows, you request
rows = 3, offset = 3 - you get 2 rows, not 3 as before).
Cheers
[1] http://dev.catalyst.perl.org/repos/bast/DBIx-
Class/0.08/branches/mssql_rno_pagination/
This solution fails on MSSQL2000. Here's the generated SQL:
SELECT * FROM ( SELECT orig_query.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1))
) AS rno__row__index FROM (SELECT me.id, me.surveyor_id, me.survey_site_id,
me.year FROM (SELECT TOP 100 PERCENT me.id, me.surveyor_id,
me.survey_site_id, me.year FROM surveyors_survey_sites me JOIN surveyors
surveyor ON surveyor.id = me.surveyor_id ORDER BY surveyor.name) me)
orig_query ) rno_subq WHERE rno__row__index BETWEEN 11 AND 20
And the error:
DBI Exception: DBD::Sybase::st execute failed: Server message number=195
severity=15 state=10 line=1 server=SQLA4 text='ROW_NUMBER' is not a
recognized function name.
Server message number=170 severity=15 state=1 line=1 server=SQLA4 text=Line
1: Incorrect syntax near ')'.
Server message number=170 severity=15 state=1 line=1 server=SQLA4 text=Line
1: Incorrect syntax near 'orig_query'.
Apparently ROW_NUMBER() was introduced in MSSQL2005.
That said, I've arranged to move the database to MSSQL2008 late this week.
I'll let you know how things go after the transition.
Ok, please try the same branch, make sure it works cleanly against mssql 2000.
Of course your original problem will not be fixed, as it is not really possible
with Top-limit. Once you upgrade to = 2005, all problems should go away.
Cheers
___
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] create failures with has_one
On 09/12/2009, at 13:38, Dermot wrote:
2009/10/19 Dermot paik...@googlemail.com:
2009/10/19 Wallace Reis wall...@reis.org.br:
On 15/10/2009, at 21:08, Dermot wrote:
my $record = {
'validation_pass' = 1,
'files' = [],
'active' = 1,
'name' = 'Gustav MR',
'clipdata' = [
{
'contrib_id' = 2,
}
],
'contrib_id' = 2,
};
Use a HashRef for clipdata rel instead of a ArrayRef.
Here, you should note that currently find (and update) part of *_or_create
methods will fail for nested relations, so you need to call
find(update)_or_create for relationships separately.
Is this still true? At least of 0.08114.
Dp.
Yes, and that applies to the problem you described in another thread (about
dupes).
--
wallace reis/wreis Catalyst and DBIx::Class consultancy with a clue
Software Engineer and a commit bit: http://shadowcat.co.uk/catalyst/
Shadowcat Systems Limited
http://www.shadowcat.co.uk http://www.linkedin.com/in/wallacereis
___
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] MSSQL - problem sorting by joined column while limiting rows
-Original Message-
From: Peter Rabbitson [mailto:rabbit+d...@rabbit.us]
Sent: Wednesday, December 09, 2009 10:48 AM
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column
while limiting rows
Alan Humphrey wrote:
-Original Message-
From: Peter Rabbitson [mailto:rabbit+d...@rabbit.us]
Sent: Saturday, December 05, 2009 1:35 AM
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column
while limiting rows
Alan Humphrey wrote:
Hi -
If I try to sort by a column in a joined table in a MSSQL database
AND limit
the number of rows, the generated SQL is bad. Here's the code:
my $result = $schema-resultset('SurveyorsSurveySites')-search({},
{join = 'surveyor',
order_by = ['surveyor.name'],
rows = 5
});
And here's the generated SQL.
SELECT TOP 100 PERCENT id, surveyor_id, survey_site_id, year FROM (
SELECT
TOP 5 me.id, me.surveyor_id, me.survey_site_id, me.year FROM
surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id =
me.surveyor_id ORDER BY surveyor.name ASC ) me ORDER BY
surveyor.name
It looks like right-side joins finally got nailed. Please test this
branch[1] and report how things are. As a bonus you now get proper
offset-past-end-of-rs limits (i.e. you have 5 rows, you request
rows = 3, offset = 3 - you get 2 rows, not 3 as before).
Cheers
[1] http://dev.catalyst.perl.org/repos/bast/DBIx-
Class/0.08/branches/mssql_rno_pagination/
This solution fails on MSSQL2000. Here's the generated SQL:
SELECT * FROM ( SELECT orig_query.*, ROW_NUMBER() OVER( ORDER BY
(SELECT(1)) ) AS rno__row__index FROM (SELECT me.id, me.surveyor_id,
me.survey_site_id, me.year FROM (SELECT TOP 100 PERCENT me.id,
me.surveyor_id, me.survey_site_id, me.year FROM surveyors_survey_sites
me JOIN surveyors surveyor ON surveyor.id = me.surveyor_id ORDER BY
surveyor.name) me) orig_query ) rno_subq WHERE rno__row__index BETWEEN
11 AND 20
And the error:
DBI Exception: DBD::Sybase::st execute failed: Server message
number=195 severity=15 state=10 line=1 server=SQLA4 text='ROW_NUMBER'
is not a recognized function name.
Server message number=170 severity=15 state=1 line=1 server=SQLA4
text=Line 1: Incorrect syntax near ')'.
Server message number=170 severity=15 state=1 line=1 server=SQLA4
text=Line 1: Incorrect syntax near 'orig_query'.
Apparently ROW_NUMBER() was introduced in MSSQL2005.
That said, I've arranged to move the database to MSSQL2008 late this
week. I'll let you know how things go after the transition.
Ok, please try the same branch, make sure it works cleanly against
mssql 2000.
Of course your original problem will not be fixed, as it is not really
possible
with Top-limit. Once you upgrade to = 2005, all problems should go
away.
Cheers
Peter -
Here's the result of the latest code. First, I had to make changes to the
check version routine. I'm currently arguing with my host, but right now
they're claiming that access to system stored procedures on a shared MSSQL
server represents a security risk to other databases so they're not inclined to
let me execute xp_msver. The code, as written, craps out if xp_msver doesn't
exist. I changed it to return a default:
Index: lib/DBIx/Class/Storage/DBI/MSSQL.pm
===
--- lib/DBIx/Class/Storage/DBI/MSSQL.pm (revision 8055)
+++ lib/DBIx/Class/Storage/DBI/MSSQL.pm (working copy)
@@ -230,13 +230,20 @@
sub _get_mssql_version {
my $self = shift;
- my $data = $self-_get_dbh-selectrow_hashref('xp_msver ProductVersion');
+ my $version = 0;
+ my $data = undef;
+ eval {
+ $data = $self-_get_dbh-selectrow_hashref('xp_msver ProductVersion');
+ };
- if ($data-{Character_Value} =~ /^(\d+)\./) {
- return $1;
- } else {
- $self-throw_exception(q{Your ProductVersion's Character_Value is missing or
malformed!});
+ unless ( $@ ) {
+ if ($data-{Character_Value} =~ /^(\d+)\./) {
+ $version = $1;
+# } else {
+# $self-throw_exception(q{Your ProductVersion's Character_Value is missing or
malformed!});
+ }
}
+ return $version;
}
sub sql_maker {
Note that I've commented out the exception thrown when the character is
missing/malformed. My thinking is that if the server has that screwed up
there's nothing the user can do about it anyway, so we may as well revert to
the default. On the other hand, it might be nice to know why you're not
getting the generated code you're expecting. The modified code is consistent
in that if there are any problems it silently reverts to a default value.
Whether that's the right course of action is something you'll have to decide.
Once we get past the missing stored procedure the generated SQL still has
problems:
DBIx::Class::ResultSet::next(): DBI Exception: DBD::Sybase::st execute failed:
Server message number=107
