| Subject: | D:C:S:L does not respect FK default relationships for MySQL |
When creating a schema from an existing MySQL database,
DBIx::Class::Schema::Loader is generating relationships for foreign keys
with attributes of 'ON DELETE CASCADE' and 'ON UPDATE CASCADE' even when
the underlying database has attributes of 'ON DELETE RESTRICT' or 'ON
UPDATE RESTRICT'.
The attached .sql loads a database with a single table and a simple
self-
referential FK. If you load this table and then use
DBIx::Class::Schema::Loader to dump it to a directory, you will see that
the FK 'self_fk_constraint' in the created schema has both 'ON DELETE
CASCADE' and 'ON UPDATE CASCADE' even though the original table is
created with both set to 'RESTRICT'. I have also attached the relevant
result module.
Preliminary testing seems to indicate that this does not in some manner
circumvent the actual database definition; a script attempting to
perform a delete dies due to the failure in the underlying database
structure. However, the schema does not reflect the actual database and
I am unsure as to any possible unintended side effects.
mysql -u <user> -p < database.sql
dbicdump -o dump_directory=./ Test::Schema dbi:mysql:database=test_dcsl
<user> <password>
Dumping manual schema for Test::Schema to directory ./ ...
Schema dump completed.
I am using perl 5.12.4, DBIx::Class::Schema::Loader v0.07020:
This is perl 5, version 12, subversion 4 (v5.12.4) built for i686-linux-
gnu-thread-multi-64int
perl -e 'use DBIx::Class::Schema::Loader; print
$DBIx::Class::Schema::Loader::VERSION;'
0.07020
| Subject: | database.sql |
DROP DATABASE IF EXISTS test_dcsl;
CREATE DATABASE test_dcsl;
USE test_dcsl;
CREATE TABLE `test_dcsl`.`test_table` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`id_ref` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `ref_index`(`id_ref`),
CONSTRAINT `self_fk_constraint` FOREIGN KEY `self_fk_constraint` (`id_ref`)
REFERENCES `test_table` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
| Subject: | TestTable.pm |
use utf8;
package Test::Schema::Result::TestTable;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
=head1 NAME
Test::Schema::Result::TestTable
=cut
use strict;
use warnings;
use base 'DBIx::Class::Core';
=head1 TABLE: C<test_table>
=cut
__PACKAGE__->table("test_table");
=head1 ACCESSORS
=head2 id
data_type: 'integer'
extra: {unsigned => 1}
is_auto_increment: 1
is_nullable: 0
=head2 id_ref
data_type: 'integer'
extra: {unsigned => 1}
is_foreign_key: 1
is_nullable: 0
=cut
__PACKAGE__->add_columns(
"id",
{
data_type => "integer",
extra => { unsigned => 1 },
is_auto_increment => 1,
is_nullable => 0,
},
"id_ref",
{
data_type => "integer",
extra => { unsigned => 1 },
is_foreign_key => 1,
is_nullable => 0,
},
);
=head1 PRIMARY KEY
=over 4
=item * L</id>
=back
=cut
__PACKAGE__->set_primary_key("id");
=head1 RELATIONS
=head2 id_ref
Type: belongs_to
Related object: L<Test::Schema::Result::TestTable>
=cut
__PACKAGE__->belongs_to(
"id_ref",
"Test::Schema::Result::TestTable",
{ id => "id_ref" },
{ is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
);
=head2 test_tables
Type: has_many
Related object: L<Test::Schema::Result::TestTable>
=cut
__PACKAGE__->has_many(
"test_tables",
"Test::Schema::Result::TestTable",
{ "foreign.id_ref" => "self.id" },
{ cascade_copy => 0, cascade_delete => 0 },
);
# Created by DBIx::Class::Schema::Loader v0.07020 @ 2012-08-13 14:13:44
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:gfFnXT59GLX5HKfos7Bt7A
# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;