Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
On 15 September 2012 03:38, Darren Duncan dar...@darrenduncan.net wrote: Brian Katzung wrote: If I recall correctly, I read in a cookbook somewhere (can't seem to find it now) that for rows with no primary key, you can use: __PACKAGE__-set_primary_key(__PACKAGE__-columns); (making the entire row be a multi-column primary key). Well that is indeed how things should work; when there is no primary or unique key explicitly defined, there should be an implicit one ranging over all the columns. However, SQL doesn't work that way and would allow duplicate rows, and so then the question is what behavior do you expect your Perl layer to have? If you edit a duplicate row, is it supposed to change all copies or just one? -- Darren Duncan It might be worth finding out (or, I guess you already will know) if it's safe to delete any duplicate rows; that might render the final question there moot :) Certainly Brian's suggestion is quite an elegant way to do what must be done. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
And this answer demonstrates perfectly to your managers that their conception of 'database' is broken. -- Sent from my phone, so apologies for any spelling errors, brevity, etc. On 15/09/2012, at 12:38, Darren Duncan dar...@darrenduncan.net wrote: Brian Katzung wrote: If I recall correctly, I read in a cookbook somewhere (can't seem to find it now) that for rows with no primary key, you can use: __PACKAGE__-set_primary_key(__PACKAGE__-columns); (making the entire row be a multi-column primary key). Well that is indeed how things should work; when there is no primary or unique key explicitly defined, there should be an implicit one ranging over all the columns. However, SQL doesn't work that way and would allow duplicate rows, and so then the question is what behavior do you expect your Perl layer to have? If you edit a duplicate row, is it supposed to change all copies or just one? -- Darren Duncan On 2012-09-14 14:53, Derek W wrote: Ah, you meant just on Catalyst side, to tell it that the id column is the primary. I didn't think of that. Thanks! I'll give that a shot. On Fri, Sep 14, 2012 at 11:40 AM, Robert Wohlfarth rbwohlfa...@gmail.com wrote: On Fri, Sep 14, 2012 at 1:31 PM, Derek W derekwro...@gmail.com wrote: Thanks, I figured that would probably fix it, but the problem is I am not allowed to make any changes to the DB structure itself. I suppose I need to convince them why it's in the best interest to have a primary key... You can set the primary key in the schema file. For example, add a line like __PACKAGE__-set_primary_key( id ); to lib/MyApp/Schema/Result/MyTable.pm. You do not need to change the database. DBIx::Class will then use id whenever it wants a primary key. -- Robert Wohlfarth ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
On Fri, Sep 14, 2012 at 11:43 AM, Derek W derekwro...@gmail.com wrote:
The problem is our database structure here. The 3 tables I need to
use do not have any relationships set. There is no primary keys on
these tables. I had to manually code the relationships between the
tables because the create helper could not - obviously. Now that I'm
getting to the point of trying to add functionality such as deleting,
Catalyst seems to blow up because it requires primary key for such
calls as action_for('delete').
I ran into something similar where I work. In the model code, I defined an
auto number field as the primary key. That makes DBIx::Class happy.
--
Robert Wohlfarth
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
Thanks, I figured that would probably fix it, but the problem is I am
not allowed to make any changes to the DB structure itself. I suppose
I need to convince them why it's in the best interest to have a
primary key...
On Fri, Sep 14, 2012 at 11:11 AM, Robert Wohlfarth
rbwohlfa...@gmail.com wrote:
On Fri, Sep 14, 2012 at 11:43 AM, Derek W derekwro...@gmail.com wrote:
The problem is our database structure here. The 3 tables I need to
use do not have any relationships set. There is no primary keys on
these tables. I had to manually code the relationships between the
tables because the create helper could not - obviously. Now that I'm
getting to the point of trying to add functionality such as deleting,
Catalyst seems to blow up because it requires primary key for such
calls as action_for('delete').
I ran into something similar where I work. In the model code, I defined an
auto number field as the primary key. That makes DBIx::Class happy.
--
Robert Wohlfarth
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
--
Derek Wrobel
425-243-3355
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
On Fri, Sep 14, 2012 at 1:31 PM, Derek W derekwro...@gmail.com wrote: Thanks, I figured that would probably fix it, but the problem is I am not allowed to make any changes to the DB structure itself. I suppose I need to convince them why it's in the best interest to have a primary key... You can set the primary key in the schema file. For example, add a line like __PACKAGE__-set_primary_key( id ); to lib/MyApp/Schema/Result/MyTable.pm. You do not need to change the database. DBIx::Class will then use *id* whenever it wants a primary key. -- Robert Wohlfarth ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
Ah, you meant just on Catalyst side, to tell it that the id column is the primary. I didn't think of that. Thanks! I'll give that a shot. On Fri, Sep 14, 2012 at 11:40 AM, Robert Wohlfarth rbwohlfa...@gmail.com wrote: On Fri, Sep 14, 2012 at 1:31 PM, Derek W derekwro...@gmail.com wrote: Thanks, I figured that would probably fix it, but the problem is I am not allowed to make any changes to the DB structure itself. I suppose I need to convince them why it's in the best interest to have a primary key... You can set the primary key in the schema file. For example, add a line like __PACKAGE__-set_primary_key( id ); to lib/MyApp/Schema/Result/MyTable.pm. You do not need to change the database. DBIx::Class will then use id whenever it wants a primary key. -- Robert Wohlfarth ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ -- Derek Wrobel 425-243-3355 ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
If I recall correctly, I read in a cookbook somewhere (can't seem to find it now) that for rows with no primary key, you can use: __PACKAGE__-set_primary_key(__PACKAGE__-columns); (making the entire row be a multi-column primary key). - Brian On 2012-09-14 14:53, Derek W wrote: Ah, you meant just on Catalyst side, to tell it that the id column is the primary. I didn't think of that. Thanks! I'll give that a shot. On Fri, Sep 14, 2012 at 11:40 AM, Robert Wohlfarth rbwohlfa...@gmail.com wrote: On Fri, Sep 14, 2012 at 1:31 PM, Derek W derekwro...@gmail.com wrote: Thanks, I figured that would probably fix it, but the problem is I am not allowed to make any changes to the DB structure itself. I suppose I need to convince them why it's in the best interest to have a primary key... You can set the primary key in the schema file. For example, add a line like __PACKAGE__-set_primary_key( id ); to lib/MyApp/Schema/Result/MyTable.pm. You do not need to change the database. DBIx::Class will then use id whenever it wants a primary key. -- Robert Wohlfarth ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ -- Brian Katzung, Kappa Computer Solutions, LLC Leveraging UNIX, GNU/Linux, open source, and custom software solutions for business and beyond Phone: 847.412.0713 http://www.kappacs.com ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Using Catalyst on MS SQL DB that has tables with no primary keys
Brian Katzung wrote: If I recall correctly, I read in a cookbook somewhere (can't seem to find it now) that for rows with no primary key, you can use: __PACKAGE__-set_primary_key(__PACKAGE__-columns); (making the entire row be a multi-column primary key). Well that is indeed how things should work; when there is no primary or unique key explicitly defined, there should be an implicit one ranging over all the columns. However, SQL doesn't work that way and would allow duplicate rows, and so then the question is what behavior do you expect your Perl layer to have? If you edit a duplicate row, is it supposed to change all copies or just one? -- Darren Duncan On 2012-09-14 14:53, Derek W wrote: Ah, you meant just on Catalyst side, to tell it that the id column is the primary. I didn't think of that. Thanks! I'll give that a shot. On Fri, Sep 14, 2012 at 11:40 AM, Robert Wohlfarth rbwohlfa...@gmail.com wrote: On Fri, Sep 14, 2012 at 1:31 PM, Derek W derekwro...@gmail.com wrote: Thanks, I figured that would probably fix it, but the problem is I am not allowed to make any changes to the DB structure itself. I suppose I need to convince them why it's in the best interest to have a primary key... You can set the primary key in the schema file. For example, add a line like __PACKAGE__-set_primary_key( id ); to lib/MyApp/Schema/Result/MyTable.pm. You do not need to change the database. DBIx::Class will then use id whenever it wants a primary key. -- Robert Wohlfarth ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
