foreign keys in fixtures for Test::DBIx::Class

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

foreign keys in fixtures for Test::DBIx::Class

Adam Witney
Hi,

I am trying to write some fixtures for testing with Test::DBIx::Class. I can do this for a simple one table test, but am having trouble doing it when I have a foreign key between two tables. In the file "t/etc/schema.pl" I have

fixture_sets => {
          'basic' => [
              {
                  Person => [
                      [ 'first_name', 'last_name', ],
                      [ 'John', 'Smith' ],
                  ]
              },{
                  Company => [
                      ['name'],
                      ['My Company']
                  ]
              }
          ]
      }

But I cannot figure out how to assign the Company to Person John  Smith. Does anyone have a working example of this syntax?

The becomes a problem when using foreign keys in PostgreSQL, as it can't create the Person row without an id for the company row

Thanks for any help

Adam

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: foreign keys in fixtures for Test::DBIx::Class

Ben Tilly
In the documentation there is the following example:

    fixtures_ok my $first_album = sub {
        my $schema = shift @_;
        my $cd_rs = $schema->resultset('CD');
        return $cd_rs->create({
            name => 'My First Album',
            track_rs => [
                {position=>1, title=>'the first song'},
                {position=>2, title=>'yet another song'},
            ],
            cd_artist_rs=> [
                {person_artist=>{person => $vanessa}},
                {person_artist=>{person => $john}},
            ],
        });
    }, 'You can even use a code reference for custom fixtures';

This creates a 'CD' object with a list of tracks, and two entries in a
join table that refer to artists who had been created separately.

You should be able to do the same thing.  Create a company, with a
result set that contains the people who are part of that company.

On Thu, Nov 20, 2014 at 7:06 AM, Adam Witney <[hidden email]> wrote:

> Hi,
>
> I am trying to write some fixtures for testing with Test::DBIx::Class. I can do this for a simple one table test, but am having trouble doing it when I have a foreign key between two tables. In the file "t/etc/schema.pl" I have
>
> fixture_sets => {
>           'basic' => [
>               {
>                   Person => [
>                       [ 'first_name', 'last_name', ],
>                       [ 'John', 'Smith' ],
>                   ]
>               },{
>                   Company => [
>                       ['name'],
>                       ['My Company']
>                   ]
>               }
>           ]
>       }
>
> But I cannot figure out how to assign the Company to Person John  Smith. Does anyone have a working example of this syntax?
>
> The becomes a problem when using foreign keys in PostgreSQL, as it can't create the Person row without an id for the company row
>
> Thanks for any help
>
> Adam
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: foreign keys in fixtures for Test::DBIx::Class

Adam Witney

Hi Ben,

Thanks for your email. I was hoping not to have to write too much perl code in the fixtures. Also, can that approach be used in a config file rather than the test file?

Thanks

Adam

> -----Original Message-----
> From: Ben Tilly [mailto:[hidden email]]
> Sent: 20. novembra 2014 19:13
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] foreign keys in fixtures for Test::DBIx::Class
>
> In the documentation there is the following example:
>
>     fixtures_ok my $first_album = sub {
>         my $schema = shift @_;
>         my $cd_rs = $schema->resultset('CD');
>         return $cd_rs->create({
>             name => 'My First Album',
>             track_rs => [
>                 {position=>1, title=>'the first song'},
>                 {position=>2, title=>'yet another song'},
>             ],
>             cd_artist_rs=> [
>                 {person_artist=>{person => $vanessa}},
>                 {person_artist=>{person => $john}},
>             ],
>         });
>     }, 'You can even use a code reference for custom fixtures';
>
> This creates a 'CD' object with a list of tracks, and two entries in a join table
> that refer to artists who had been created separately.
>
> You should be able to do the same thing.  Create a company, with a result set
> that contains the people who are part of that company.
>
> On Thu, Nov 20, 2014 at 7:06 AM, Adam Witney <[hidden email]>
> wrote:
> > Hi,
> >
> > I am trying to write some fixtures for testing with Test::DBIx::Class.
> > I can do this for a simple one table test, but am having trouble doing
> > it when I have a foreign key between two tables. In the file
> > "t/etc/schema.pl" I have
> >
> > fixture_sets => {
> >           'basic' => [
> >               {
> >                   Person => [
> >                       [ 'first_name', 'last_name', ],
> >                       [ 'John', 'Smith' ],
> >                   ]
> >               },{
> >                   Company => [
> >                       ['name'],
> >                       ['My Company']
> >                   ]
> >               }
> >           ]
> >       }
> >
> > But I cannot figure out how to assign the Company to Person John  Smith.
> Does anyone have a working example of this syntax?
> >
> > The becomes a problem when using foreign keys in PostgreSQL, as it
> > can't create the Person row without an id for the company row
> >
> > Thanks for any help
> >
> > Adam
> >
> > _______________________________________________
> > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> > IRC: irc.perl.org#dbix-class
> > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> > Searchable Archive:
> > http://www.grokbase.com/group/dbix-class@...
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-
> [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: foreign keys in fixtures for Test::DBIx::Class

Darius Jokilehto-2
In reply to this post by Adam Witney
You haven't specified the id columns, you need to do this explicitly, or like you said you won't be able to reference them. In other words:

fixture_sets => {
          'basic' => [
              {
                  Person => [
                      [ 'id', 'first_name', 'last_name', 'company_id' ],
                      [ 1, 'John', 'Smith', 10 ],
                  ]
              },{
                  Company => [
                      ['id', 'name'],
                      [10, 'My Company']
                  ]
              }
          ]

      }

This does mean that you'll have to run setval against your sequences to update them.

Also make sure your your foreign keys are deferrable so they get checked at the end of your transaction, not when the rows are inserted.

HTH,
Darius


On Thursday, 20 November 2014, 15:35, Adam Witney <[hidden email]> wrote:


>
>
>Hi,
>
>I am trying to write some fixtures for testing with Test::DBIx::Class. I can do this for a simple one table test, but am having trouble doing it when I have a foreign key between two tables. In the file "t/etc/schema.pl" I have
>
>fixture_sets => {
>          'basic' => [
>              {
>                  Person => [
>                      [ 'first_name', 'last_name', ],
>                      [ 'John', 'Smith' ],
>                  ]
>              },{
>                  Company => [
>                      ['name'],
>                      ['My Company']
>                  ]
>              }
>          ]
>      }
>
>But I cannot figure out how to assign the Company to Person John  Smith. Does anyone have a working example of this syntax?
>
>The becomes a problem when using foreign keys in PostgreSQL, as it can't create the Person row without an id for the company row
>
>Thanks for any help
>
>Adam
>
>_______________________________________________
>List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>IRC: irc.perl.org#dbix-class
>SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>
>
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: foreign keys in fixtures for Test::DBIx::Class

Adam Witney

Thanks, I had looked at that approach, but I had missed the is_deferrable part, thanks

Adam

-----Original Message-----
From: Darius Jokilehto [mailto:[hidden email]]
Sent: 22 November 2014 12:26
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] foreign keys in fixtures for Test::DBIx::Class

You haven't specified the id columns, you need to do this explicitly, or like you said you won't be able to reference them. In other words:

fixture_sets => {
          'basic' => [
              {
                  Person => [
                      [ 'id', 'first_name', 'last_name', 'company_id' ],
                      [ 1, 'John', 'Smith', 10 ],
                  ]
              },{
                  Company => [
                      ['id', 'name'],
                      [10, 'My Company']
                  ]
              }
          ]

      }

This does mean that you'll have to run setval against your sequences to update them.

Also make sure your your foreign keys are deferrable so they get checked at the end of your transaction, not when the rows are inserted.

HTH,
Darius


On Thursday, 20 November 2014, 15:35, Adam Witney <[hidden email]> wrote:


>
>
>Hi,
>
>I am trying to write some fixtures for testing with Test::DBIx::Class. I can do this for a simple one table test, but am having trouble doing it when I have a foreign key between two tables. In the file "t/etc/schema.pl" I have
>
>fixture_sets => {
>          'basic' => [
>              {
>                  Person => [
>                      [ 'first_name', 'last_name', ],
>                      [ 'John', 'Smith' ],
>                  ]
>              },{
>                  Company => [
>                      ['name'],
>                      ['My Company']
>                  ]
>              }
>          ]
>      }
>
>But I cannot figure out how to assign the Company to Person John  Smith. Does anyone have a working example of this syntax?
>
>The becomes a problem when using foreign keys in PostgreSQL, as it can't create the Person row without an id for the company row
>
>Thanks for any help
>
>Adam
>
>_______________________________________________
>List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>IRC: irc.perl.org#dbix-class
>SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>
>
>

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...