DBIx example

Artist.pm

package MyApp::Schema::Result::Artist;

use warnings;
use strict;

use base qw( DBIx::Class::Core );

__PACKAGE__->table('artist');

__PACKAGE__->add_columns(
  artistid => {
    data_type => 'integer',
    is_auto_increment => 1
  },
  name => {
    data_type => 'text',
  },
);

__PACKAGE__->set_primary_key('artistid');

__PACKAGE__->add_unique_constraint([qw( name )]);

#__PACKAGE__->has_many('cds' => 'MyApp::Schema::Result::Cd', 'artistid');
__PACKAGE__->has_many(
    'cds',
    'MyApp::Schema::Result::Cd',
    [
        { 'foreign.artistid' => 'self.artistid' },
    ],
    {
        is_deferrable    => 0,
        join_type        => "LEFT",
        on_delete        => "CASCADE",
        on_update        => "NO ACTION",
        undef_on_null_fk => 0,
    }
);
1;

Cd.pm

package MyApp::Schema::Result::Cd;

use warnings;
use strict;

use base qw( DBIx::Class::Core );

__PACKAGE__->table('cd');

__PACKAGE__->add_columns(
  cdid => {
    data_type => 'integer',
    is_auto_increment => 1
  },
  artistid => {
    data_type => 'integer',
  },
  title => {
    data_type => 'text',
  },
  year => {
    data_type => 'datetime',
    is_nullable => 1,
  },
);

__PACKAGE__->set_primary_key('cdid');

__PACKAGE__->add_unique_constraint([qw( title artistid )]);

#__PACKAGE__->belongs_to('artist' => 'MyApp::Schema::Result::Artist', 'artistid');
__PACKAGE__->belongs_to(
    'artist',
    'MyApp::Schema::Result::Artist',
    [
        { 'foreign.artistid' => 'self.artistid' }
    ],
    { cascade_copy => 0, cascade_delete => 0 }
);
__PACKAGE__->has_many('tracks' => 'MyApp::Schema::Result::Track', 'cdid');

1;

testdb.pl

#!/usr/bin/env perl

use warnings;
use strict;

use lib '.';
use MyApp::Schema;

use Path::Class 'file';
my $db_fn = file($INC{'MyApp/Schema.pm'})->dir->parent->file('db/example.db');

# for other DSNs, e.g. MySql, see the perldoc for the relevant dbd
# driver, e.g perldoc L<DBD::mysql>.
my $schema = MyApp::Schema->connect("dbi:SQLite:$db_fn");

get_tracks_by_cd('Bad');
get_tracks_by_artist('Michael Jackson');

get_cd_by_track('Stan');
get_cds_by_artist('Michael Jackson');

get_artist_by_track('Dirty Diana');
get_artist_by_cd('The Marshall Mathers LP');

insert_new_cd('Emimem', 'Kamikaze');
sub get_tracks_by_cd {
    my $cdtitle = shift;
    print "get_tracks_by_cd($cdtitle):\n";
    my $rs = $schema->resultset('Track')->search(
        {
            'cd.title' => $cdtitle
        },
        {
            join     => [qw/ cd /],
        }
    );
    while (my $track = $rs->next) {
        print $track->title . "\n";
    }
    print "\n";
}

sub get_tracks_by_artist {
    my $artistname = shift;
    print "get_tracks_by_artist($artistname):\n";
    my $rs = $schema->resultset('Track')->search(
        {
            'artist.name' => $artistname
        },
        {
            join => {
                'cd' => 'artist'
            },
        }
    );
    while (my $track = $rs->next) {
        print $track->title . " (from the CD '" . $track->cd->title
          . "')\n";
    }
    print "\n";
}
sub get_cd_by_track {
    my $tracktitle = shift;
    print "get_cd_by_track($tracktitle):\n";
    my $rs = $schema->resultset('Cd')->search(
        {
            'tracks.title' => $tracktitle
        },
        {
            join     => [qw/ tracks /],
        }
    );
    my $cd = $rs->first;
    print $cd->title . " has the track '$tracktitle'.\n\n";
}

sub get_cds_by_artist {
    my $artistname = shift;
    print "get_cds_by_artist($artistname):\n";
    my $rs = $schema->resultset('Cd')->search(
        {
            'artist.name' => $artistname
        },
        {
            join     => [qw/ artist /],
        }
    );
    while (my $cd = $rs->next) {
        print $cd->title . "\n";
    }
    print "\n";
}

sub get_artist_by_track {
    my $tracktitle = shift;
    print "get_artist_by_track($tracktitle):\n";
    my $rs = $schema->resultset('Artist')->search(
        {
            'tracks.title' => $tracktitle
        },
        {
            join => {
                'cds' => 'tracks'
            }
        }
    );
    my $artist = $rs->first;
    print $artist->name . " recorded the track '$tracktitle'.\n\n";
}

sub get_artist_by_cd {
    my $cdtitle = shift;
    print "get_artist_by_cd($cdtitle):\n";
    my $rs = $schema->resultset('Artist')->search(
        {
            'cds.title' => $cdtitle
        },
        {
            join     => [qw/ cds /],
        }
    );
    my $artist = $rs->first;
    print $artist->name . " recorded the CD '$cdtitle'.\n\n";
}

sub insert_new_cd {
    my ($artistname, $cdtitle) = @_;
    print "Inserting new CD ($cdtitle):\n";
    my $artist = $schema->resultset('Artist')->find_or_create(
        {
            name => $artistname,
        }
    );
    my $cd = $artist->create_related('cds',
        {
            title => $cdtitle,
            year  => 2018,
        }
    );
    print "$cdtitle added as ". $cd->cdid ."\n";
}