in reply to SQL Syntax error from recursive DBIx::Class populate

Hi Boldra,

with some minor modifications your script works as intended:

package TestDB::Scene { use parent 'DBIx::Class::Core'; __PACKAGE__->table('scenes'); __PACKAGE__->add_columns(qw<title time description film>); __PACKAGE__->set_primary_key(qw<time>); }; package TestDB::Film { use parent 'DBIx::Class::Core'; __PACKAGE__->table('movies'); __PACKAGE__->add_columns(qw<title director>); __PACKAGE__->set_primary_key(qw<title>); __PACKAGE__->has_many( scenes => 'TestDB::Scene', 'time' ); }; package TestDB::Director { use parent 'DBIx::Class::Core'; __PACKAGE__->table('directors'); __PACKAGE__->add_columns(qw<name>); __PACKAGE__->set_primary_key(qw<name>); __PACKAGE__->has_many( movies => 'TestDB::Film', 'title' ); }; package TestDB { use parent 'DBIx::Class::Schema'; __PACKAGE__->load_classes(qw<Film Director Scene>); }; package main { use Modern::Perl; main: { my $schema = deploy_new_to_temp_file(); my $rs = $schema->resultset('Director'); my @population = ( { name => "Lesly Tack", movies => [ { title => 'Alligator', director => '#Lesly Tack', scenes => [ { title => '1st swamp pan', time => '05:36-12:01', description => 'Murder in a Swamp base +d Collection', film => '#Alligator', }, ] } ] } ); my @sources = $schema->sources; # for my $source (@sources) { # print "Table: ", $source, "\n"; # print "Columns: ", (join ", ", $schema->source($source)- +>columns), "\n"; # print "Relationships: ", $schema->source($source)->relat +ionships, "\n"; # } $rs->populate( \@population ); } sub deploy_new_to_temp_file { my $schema = TestDB->connect('dbi:SQLite:dbname=test.db'); $schema->deploy( { add_drop_table => 1 } ); return $schema; } };

But I think, you have to redesign your database a little, because the columns 'director' in table movies and 'film' in table scenes seem redundant.

Cheers Brian

Replies are listed 'Best First'.
Re^2: SQL Syntax error from recursive DBIx::Class populate
by Boldra (Curate) on Nov 23, 2016 at 10:41 UTC

    Thanks for your ideas Brian,

    It looks like you're doing something quite different to what I intended. I need the foreign keys (film in scene and director in films) to make relationships, so they're not redundant. By providing the foreign keys explicitly, the relationships aren't inferred by their position in the hashref (which works with <3 levels), which would be fine, but I have to use the keys of the parent object, or the relationship doesn't exist.

    By the way, the strange data and relationship names are inspired by the cascading-delete unit test for DBIC: t/cdbi/23-cascade.t.



    - Boldra