Bug #55686 for DBIx-Class-Schema-Loader: MSSQL _table_uniq

Thu Mar 18 05:35:17 2010 mark.zealey [...] pipex.net - Ticket created

Subject:

MSSQL _table_uniq_info detection incorrect

I just had an issue when upgrading from 4999_10 to 5003 that for mysql, the unique info is being incorrectly detected. As I don't need it, I just overrode the function to not return anything. The issue was to do with capitalization. The code generated was: __PACKAGE__->add_columns( "email", { data_type => "nvarchar", default_value => undef, is_nullable => 0, size => 200, }, "name", { data_type => "nvarchar", default_value => undef, is_nullable => 0, size => 200, }, "password", { data_type => "nvarchar", default_value => undef, is_nullable => 1, size => 200, }, ); __PACKAGE__->set_primary_key("email", "name"); __PACKAGE__->add_unique_constraint("PK_NavMigUKNonAtlas", ["Email", "Name"]); Which then err'd because the unique constraint columns of Email and Name didn't exist (as they have been incorrectly capitalized). Not sure why it's loading both a unique constraint and a pk though. Mark

Thu Mar 18 06:11:29 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Thu, 18 Mar 2010 06:10:11 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

Is this for MSSQL or MySQL? On Thu, Mar 18, 2010 at 05:35:23AM -0400, Mark Zealey via RT wrote: ... Show quoted text

> Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 > > > > I just had an issue when upgrading from 4999_10 to 5003 that for mysql, > the unique info is being incorrectly detected. As I don't need it, I > just overrode the function to not return anything. The issue was to do > with capitalization. The code generated was: > > __PACKAGE__->add_columns( > "email", > { > data_type => "nvarchar", > default_value => undef, > is_nullable => 0, > size => 200, > }, > "name", > { > data_type => "nvarchar", > default_value => undef, > is_nullable => 0, > size => 200, > }, > "password", > { > data_type => "nvarchar", > default_value => undef, > is_nullable => 1, > size => 200, > }, > ); > __PACKAGE__->set_primary_key("email", "name"); > __PACKAGE__->add_unique_constraint("PK_NavMigUKNonAtlas", ["Email", > "Name"]); > > Which then err'd because the unique constraint columns of Email and Name > didn't exist (as they have been incorrectly capitalized). Not sure why > it's loading both a unique constraint and a pk though. > > Mark

Thu Mar 18 06:11:42 2010 The RT System itself - Status changed from 'new' to 'open'

Fri Mar 19 17:29:39 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Fri, 19 Mar 2010 17:22:00 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

Can't reproduce this: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class-Schema-Loader.git;a=commitdiff;h=deedd5761dc499f7a9121734cd6a02eb8991c93f please send me the CREATE TABLE statement. On Thu, Mar 18, 2010 at 05:35:23AM -0400, Mark Zealey via RT wrote: Show quoted text

> Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 > > > > I just had an issue when upgrading from 4999_10 to 5003 that for mysql, > the unique info is being incorrectly detected. As I don't need it, I > just overrode the function to not return anything. The issue was to do > with capitalization. The code generated was: > > __PACKAGE__->add_columns( > "email", > { > data_type => "nvarchar", > default_value => undef, > is_nullable => 0, > size => 200, > }, > "name", > { > data_type => "nvarchar", > default_value => undef, > is_nullable => 0, > size => 200, > }, > "password", > { > data_type => "nvarchar", > default_value => undef, > is_nullable => 1, > size => 200, > }, > ); > __PACKAGE__->set_primary_key("email", "name"); > __PACKAGE__->add_unique_constraint("PK_NavMigUKNonAtlas", ["Email", > "Name"]); > > Which then err'd because the unique constraint columns of Email and Name > didn't exist (as they have been incorrectly capitalized). Not sure why > it's loading both a unique constraint and a pk though. > > Mark

Mon Mar 22 04:29:18 2010 mark.zealey [...] pipex.net - Correspondence added

From:

mark.zealey [...] pipex.net

Here's the create table according to our sql guys: USE [MailShot] GO /****** Object: Table [dbo].[NavMigUKNonAtlas] Script Date: 03/22/2010 08:22:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[NavMigUKNonAtlas]( [Email] [nvarchar](200) NOT NULL, [Name] [nvarchar](200) NOT NULL, [Password] [nvarchar](200) NULL, CONSTRAINT [PK_NavMigUKNonAtlas] PRIMARY KEY CLUSTERED ( [Email] ASC, [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Mon Mar 22 07:26:21 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Mon, 22 Mar 2010 07:26:01 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

On Mon, Mar 22, 2010 at 04:29:18AM -0400, Mark Zealey via RT wrote: Show quoted text

> Queue: DBIx-Class-Schema-Loader > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 >

... Thanks for the SQL. It's still getting lowercased here, so I'm not sure where your problem is coming from. If you look at _table_uniq_info in lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm it has: my $col = lc $row->{COLUMN_NAME}; That line has always been there since the MSSQL support was added and is there in 0.05003.

Mon Mar 22 10:57:29 2010 mark.zealey [...] webfusion.com - Correspondence added

From:

mark.zealey [...] webfusion.com

Yeah I've seen the code; my only guess was that it's an issue to do with inheritance order in ODBC/Microsoft_SQL_Server.pm as I'm using an ODBC connection. Not 100% sure why it does this, especially as C3 should solve this issue. It seems to work better if I put the following in ODBC/Microsoft_SQL_Server.pm sub _table_uniq_info { warn 'moo'; goto &DBIx::Class::Schema::Loader::DBI::MSSQL::_table_uniq_info } ie forces it to go to the correct place; but that's very messy.

Mon Mar 22 11:55:34 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Mon, 22 Mar 2010 11:55:14 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

Crap, c3 stuff always completely confuses me. What if you switch the: use base qw/ DBIx::Class::Schema::Loader::DBI::ODBC DBIx::Class::Schema::Loader::DBI::MSSQL /; into: use base qw/ DBIx::Class::Schema::Loader::DBI::MSSQL DBIx::Class::Schema::Loader::DBI::ODBC /; in the ODBC driver? On Mon, Mar 22, 2010 at 10:57:30AM -0400, Mark Zealey via RT wrote: Show quoted text

> Queue: DBIx-Class-Schema-Loader > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 > > > Yeah I've seen the code; my only guess was that it's an issue to do with > inheritance order in ODBC/Microsoft_SQL_Server.pm as I'm using an ODBC > connection. Not 100% sure why it does this, especially as C3 should > solve this issue. It seems to work better if I put the following in > ODBC/Microsoft_SQL_Server.pm > > sub _table_uniq_info { warn 'moo'; goto > &DBIx::Class::Schema::Loader::DBI::MSSQL::_table_uniq_info } > > ie forces it to go to the correct place; but that's very messy.

Mon Mar 22 12:03:05 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Mon, 22 Mar 2010 12:02:46 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

On Mon, Mar 22, 2010 at 10:57:30AM -0400, Mark Zealey via RT wrote: Show quoted text

> Queue: DBIx-Class-Schema-Loader > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 > > > Yeah I've seen the code; my only guess was that it's an issue to do with > inheritance order in ODBC/Microsoft_SQL_Server.pm as I'm using an ODBC > connection. Not 100% sure why it does this, especially as C3 should > solve this issue. It seems to work better if I put the following in > ODBC/Microsoft_SQL_Server.pm > > sub _table_uniq_info { warn 'moo'; goto > &DBIx::Class::Schema::Loader::DBI::MSSQL::_table_uniq_info } > > ie forces it to go to the correct place; but that's very messy.

Should also mention that my test runs with either load order, not sure if my test is wrong. It's in the repo, t/16mssql_common.t git clone git://git.shadowcat.co.uk/dbsrgits/DBIx-Class-Schema-Loader.git sl In git I also took out ODBC as a base as MSSQL has its own _tables_list now, and it is no longer needed, maybe this will work better for you. Aiming for a 0.05004 release later today.

Wed Mar 24 07:57:07 2010 mark.zealey [...] webfusion.com - Correspondence added

From:

mark.zealey [...] webfusion.com

Afraid I can't test this as it b0rks even earlier. Here's a DBI_TRACE=1 output: <- prepare(' SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(object_id('sitefusiondata_old', 'U'), 'ID', 'IsIdentity') = 1 AND TABLE_NAME = 'sitefusiondata_old' AND COLUMN_NAME = 'ID' ')= DBI::st=HASH(0x70be460) at MSSQL.pm line 113 <- execute= -1 at MSSQL.pm line 119 <- fetchrow_array= 'ID' row1 at MSSQL.pm line 121 <- quote('sitefusiondata_old')= ( ''sitefusiondata_old'' ) [1 items] at MSSQL.pm line 128 <- quote('ID')= ( ''ID'' ) [1 items] at MSSQL.pm line 128 <- prepare(' SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'sitefusiondata_old' AND COLUMN_NAME = 'ID' ')= undef at MSSQL.pm line 128 <- DESTROY(DBI::st=HASH(70be850))= undef at MSSQL.pm line 133 Can't call method "execute" on an undefined value at /usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema/Loader/DBI/MSSQL.pm line 133.

Wed Mar 24 16:32:33 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Wed, 24 Mar 2010 16:32:07 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

This is very strange, why would DBD::ODBC refuse to prepare a statement? This happens consistently? On Wed, Mar 24, 2010 at 07:57:09AM -0400, Mark Zealey via RT wrote: Show quoted text

> Queue: DBIx-Class-Schema-Loader > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 > > > Afraid I can't test this as it b0rks even earlier. Here's a DBI_TRACE=1 > output: > > <- prepare(' > SELECT COLUMN_NAME > FROM INFORMATION_SCHEMA.COLUMNS > WHERE COLUMNPROPERTY(object_id('sitefusiondata_old', 'U'), > 'ID', 'IsIdentity') = 1 > AND TABLE_NAME = 'sitefusiondata_old' AND COLUMN_NAME = 'ID' > ')= DBI::st=HASH(0x70be460) at MSSQL.pm line 113 > <- execute= -1 at MSSQL.pm line 119 > <- fetchrow_array= 'ID' row1 at MSSQL.pm line 121 > <- quote('sitefusiondata_old')= ( ''sitefusiondata_old'' ) [1 items] > at MSSQL.pm line 128 > <- quote('ID')= ( ''ID'' ) [1 items] at MSSQL.pm line 128 > <- prepare(' > SELECT COLUMN_DEFAULT > FROM INFORMATION_SCHEMA.COLUMNS > WHERE TABLE_NAME = 'sitefusiondata_old' AND COLUMN_NAME = 'ID' > ')= undef at MSSQL.pm line 128 > <- DESTROY(DBI::st=HASH(70be850))= undef at MSSQL.pm line 133 > Can't call method "execute" on an undefined value at > /usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema/Loader/DBI/MSSQL.pm > line 133.

Wed Mar 24 18:26:25 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Wed, 24 Mar 2010 18:26:03 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

On Wed, Mar 24, 2010 at 07:57:09AM -0400, Mark Zealey via RT wrote: Show quoted text

> Queue: DBIx-Class-Schema-Loader > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 > > > Afraid I can't test this as it b0rks even earlier. Here's a DBI_TRACE=1 > output:

... Update your git clone, I've wrapped those calls in eval {} (and fixed some other things that I broke wrt. mixed-case tables/columns.)

Thu Mar 25 05:30:16 2010 mark.zealey [...] webfusion.com - Correspondence added

From:

mark.zealey [...] webfusion.com

That's working much better now. However, it would be nice if you didn't lc the table names - or at least did that in the mapping functions so that it can be overridden if need be. For example, I want to keep the capitalization but strip out the non-alphanumerics. This worked fine in your _10 version. Mark

Thu Mar 25 05:46:08 2010 rkitover@cpan.org - Correspondence added

Subject:	Re: [rt.cpan.org #55686] MSSQL _table_uniq_info detection incorrect
Date:	Thu, 25 Mar 2010 05:45:44 -0400
To:	Mark Zealey via RT <bug-DBIx-Class-Schema-Loader [...] rt.cpan.org>
From:	Rafael Kitover <rkitover [...] cpan.org>

On Thu, Mar 25, 2010 at 05:30:18AM -0400, Mark Zealey via RT wrote: Show quoted text

> Queue: DBIx-Class-Schema-Loader > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=55686 > > > That's working much better now. However, it would be nice if you didn't > lc the table names - or at least did that in the mapping functions so > that it can be overridden if need be. For example, I want to keep the > capitalization but strip out the non-alphanumerics. This worked fine in > your _10 version. > > Mark

Just fixed that in git.

Thu Mar 25 06:02:34 2010 mark.zealey [...] webfusion.com - Correspondence added

From:

mark.zealey [...] webfusion.com

Perfect. Looks great. Mark

Sat Apr 10 08:37:32 2010 rkitover [...] cpan.org - Correspondence added

Should be fixed in 0.06001

Sat Apr 10 08:37:34 2010 rkitover [...] cpan.org - Status changed from 'open' to 'resolved'

Bug #55686 for DBIx-Class-Schema-Loader: MSSQL _table_uniq_info detection incorrect