| Subject: | Tables with reserved names fail to load |
When trying to load a MySQL schema that contains a table with a reserved
name ("group"), DBIx::Class::Schema::Loader 0.03000 emits several
errors, stemming from its failure to enclose the table name in
backticks. The attached patch fixes the issue on my system by putting
backticks around the table names, but doesn't attempt to support other
database engines; it may need further work before it can be applied.
Ubuntu Linux 6.06 (kernel 2.6.15), Perl 5.8.7, DBI 1.50, DBIx::Class
0.06002, MySQL 5.0.22.
Full error output:
knightley::SchemaLoader::DBAuto::Group->table("group");
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 t o use near 'group WHERE 1=0' at line 1 at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader/DBI.pm line 95.
Can't set DBI::st=HASH(0x90c4008)->{NAME_lc}: unrecognised attribute
name or invalid value at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader/DBI.pmline 96.
knightley::SchemaLoader::DBAuto::Group->add_columns(());
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 t o use near 'group' at line 1 at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader/DBI/mysql.pm line 71.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader/DBI/mysql.pm line 72.
group has no primary key at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader.pm line 122
knightley::SchemaLoader::DBAuto::Membership->table("membership");
knightley::SchemaLoader::DBAuto::Membership->add_columns("id", "group",
"member", "role");
knightley::SchemaLoader::DBAuto::Membership->set_primary_key("id");
knightley::SchemaLoader::DBAuto::Membership->add_unique_constraint(["id"],
undef);
knightley::SchemaLoader::DBAuto::User->table("user");
knightley::SchemaLoader::DBAuto::User->add_columns("id", "email",
"salt", "password", "job", "birthday", "balance");
knightley::SchemaLoader::DBAuto::User->set_primary_key("id");
knightley::SchemaLoader::DBAuto::User->add_unique_constraint(["id"], undef);
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 t o use near 'group' at line 1 at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader/DBI/mysql.pm line 37.
DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader/DBI/mysql.pm line 38.
Couldn't instantiate component "knightley::M::DBAuto", "Cannot load
schema class 'knightley::SchemaLoader::DBAuto': Can't use an undefined
value as an ARRAY reference at
/usr/local/share/perl/5.8.7/DBIx/Class/Schema/Loader/DBI/mysql.pm line 38.
Compilation failed in require at
/usr/local/share/perl/5.8.7/Catalyst/Model/DBIC/Schema.pm line 275.
at /usr/share/perl5/Module/Pluggable/Fast.pm line 86" at
/usr/share/perl5/Module/Pluggable/Fast.pm line 86
| Subject: | mysql-table-name.patch |
--- DBIx/Class/Schema/Loader/DBI.pm 2006-09-28 01:09:46.000000000 -0700
+++ DBIx/Class/Schema/Loader/DBI.pm.orig 2006-09-28 01:09:04.000000000 -0700
@@ -91,7 +91,7 @@
$table = $self->{db_schema} . $self->{_namesep} . $table;
}
- my $sth = $dbh->prepare("SELECT * FROM `$table` WHERE 1=0");
+ my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0");
$sth->execute;
return \@{$sth->{NAME_lc}};
}
--- DBIx/Class/Schema/Loader/DBI/mysql.pm 2006-09-28 01:02:57.000000000 -0700
+++ DBIx/Class/Schema/Loader/DBI/mysql.pm.orig 2006-09-28 01:02:07.000000000 -0700
@@ -31,7 +31,7 @@
my $dbh = $self->schema->storage->dbh;
- my $query = "SHOW CREATE TABLE `${table}`";
+ my $query = "SHOW CREATE TABLE ${table}";
my $sth = $dbh->prepare($query)
or die("Cannot get table definition: $table");
$sth->execute;
@@ -67,7 +67,7 @@
if(!exists($self->{_mysql_keys}->{$table})) {
my %keydata;
my $dbh = $self->schema->storage->dbh;
- my $sth = $dbh->prepare("SHOW INDEX FROM `$table`");
+ my $sth = $dbh->prepare("SHOW INDEX FROM $table");
$sth->execute;
while(my $row = $sth->fetchrow_hashref) {
next if $row->{Non_unique};