'bad', 'tobyink'

SELECT me.poem_id, me.artwork_id, me.meter, me.rhyme, artwork.artwork_id, artwork.artist_id, artwork.name, artwork.year, artist.artist_id, artist.name, artist.country_id, country.country_id, country.iso_code, country.name, country.language_id, comments.comment_id, comments.user_id, comments.artwork_id, comments.text, user.user_id, user.country_id, user.name, country_2.country_id, country_2.iso_code, country_2.name, country_2.language_id FROM poem me  JOIN artwork artwork ON artwork.artwork_id = me.artwork_id  JOIN artist artist ON artist.artist_id = artwork.artist_id  JOIN country country ON country.country_id = artist.country_id LEFT JOIN comment comments ON comments.artwork_id = artwork.artwork_id LEFT JOIN user user ON user.user_id = comments.user_id LEFT JOIN country country_2 ON country_2.country_id = user.country_id WHERE ( ( comments.text != ? AND country.name != country_2.name AND user.name = ? ) ) ORDER BY me.poem_id

##</code><code>##

my $rs = $db->resultset('Painting');

while ( my $painting = $rs->next ) {
    say sprintf('%s: "%s" (%s)',
        $painting->artwork->artist->name,
        $painting->artwork->name,
        $painting->medium,
    );
}

##</code><code>##

$rs = $db->resultset('Play');

while ( my $play = $rs->next ) {
    say sprintf('%s: "%s" (%s)',
        $play->artwork->artist->name,
        $play->artwork->name,
        $play->genre,
    );
}

##</code><code>##

$rs = $db->resultset('Play');

while ( my $play = $rs->next ) {
    say sprintf('About "%s", %s said "%s"',
        $play->artwork->name,
        $_->user->name,
        $_->text,
    ) for $play->artwork->comments;
}

##</code><code>##

$rs = $db->resultset('Comment')->search({
    'user.name' => 'bliako',
    'me.text'   => 'good',
}, {
    join => ['user', { artwork => 'artist' }],
});

while ( my $comment = $rs->next ) {
    say sprintf('bliako likes: "%s" (%s)',
        $comment->artwork->name,
        $comment->artwork->artist->name,
    );
}

##</code><code>##

$rs = $db->resultset('Painting')->search({
    'user.name'     => 'discipulus',
    'comments.text' => { '-in' => ['bad', 'meh'] },
}, {
    join => { artwork => [ 'artist', { comments => 'user' } ] },
});

while ( my $painting = $rs->next ) {
    say sprintf('discipulus does not love "%s" (%s)',
        $painting->artwork->name,
        $painting->artwork->artist->name,
    );
}

##</code><code>##

$rs = $db->resultset('Poem')->search({
    'user.name'     => 'tobyink',
    'comments.text' => { '!=' => 'bad' },   # DBIx handles this NOT
    'country.name'  => \'!= country_2.name' # literal SQL for this NOT
}, {
    prefetch => {
        artwork => [
            { artist   => 'country' },
            { comments => { user => 'country' } },
        ],                          # table aliased here to 'country_2'
    },
});

while ( my $poem = $rs->next ) {
    say sprintf(q{tobyink doesn't hate "%s" (%s)},
        $poem->artwork->name,
        $poem->artwork->artist->name,
    );
}

##</code><code>##

$rs = $db->resultset('Comment')->search({
    'country.language_id' => \'= country_2.language_id', # literal SQL
}, {
    prefetch => [
        { user => { country => 'language' } },
        { artwork => { artist => 'country' } },
    ],
});

while ( my $comment = $rs->next ) {
    say sprintf('%s : %s said about "%s" (%s) : "%s"',
        $comment->user->country->language->name,
        $comment->user->name,
        $comment->artwork->name,
        $comment->artwork->artist->name,
        $comment->text,
    );
}

##</code><code>##

use strict;
use warnings;
use utf8::all;
use feature 'say';

#---------------------------------------------------------------------#
# This section for demo setup only

# Create the database with the SQL schema below
my $sql = do { local $/, <DATA> };
system('mysql', '-e', $sql);

# Create the DBIx::Class schema classes
system('dbicdump', '-o', 'quiet=1', '-o', 'dump_directory=.',
       'Art::Schema', 'dbi:mysql:database=Art');

# Use the schema classes
unshift @INC, '.';
eval 'use Art::Schema'; # normally loaded, um, normally
#---------------------------------------------------------------------#

# Initialize the schema as a DBIx schema object
my $db = Art::Schema->connect('DBI:mysql:database=Art', undef, undef, {
    RaiseError => 1, mysql_enable_utf8 => 1,
});

# Create the "Mona Lisa" and all its related objects.
# Nothing is in the DB at this point (but no need to worry
# about getting the IDs to use for the FK columns)
$db->resultset('Painting')->create({
    artwork => {
        name   => 'Mona Lisa',
        year   => 1503,
        artist => {
            name => 'Leonardo Da Vinci',
            country => {
                iso_code => 'IT',
                name     => 'Italy',
                language => {
                    name => 'Eatalian',
                },
            },
        },
    },
    genre  => 'portrait',
    medium => 'oil',
});

# Create another Da Vinci painting.
# (no need to worry about the fact that there is
# already an artist record for Da Vinci)
$db->resultset('Painting')->create({
    artwork => {
        name   => 'Salvator Mundi',
        year   => 1500,
        artist => {
            name => 'Leonardo Da Vinci', # needed here
            country => {                 # silently ignored here
                iso_code => 'IT',
                name     => 'Italy',
                language => {
                    name => 'Eatalian',
                },
            },
        },
    },
    genre  => 'history',
    medium => 'oil',
});

# Create one more Da Vinci painting.
# (starting from the artist record)
my $artist = $db->resultset('Artist')->find({
    name => 'Leonardo Da Vinci',
});

$artist->create_related('artworks', {
    name   => 'John the Baptist',
    year   => 1517,
})->create_related('painting', {
    genre  => 'history',
    medium => 'oil',
});

# Create "Hamlet" and Shakespeare along with it.
# (new country and language also created)
my $hamlet = $db->resultset('Play')->create({
    artwork => {
        name   => 'Hamlet',
        year   => 1599,
        artist => {
            name => 'William Shakespeare',
            country => {
                iso_code => 'GB-ENG',
                name     => 'England',
                language => {
                    name => 'English',
                },
            },
        },
    },
    genre    => 'tragedy',
    num_acts => 5,
});

# Create a couple more works by the Bard.
# (Using tortuous one-statement syntax here purely as a demonstration)
$hamlet->artwork->artist->create_related('artworks', {
    name => 'As You Like It',
    year => '1599',
})->create_related('play', {
    genre    => 'comedy',
    num_acts => 5,
})->artwork->artist->create_related('artworks', {
    name => 'Venus and Adonis',
    year => '1593',
})->create_related('poem', {
    meter => 'iamb',
    rhyme => 'end',
});

# Create a new language and a couple of countries that use it.
$db->resultset('Language')->create({
    name => 'Spanish',
    countries => [{
        name     => 'Chile',
        iso_code => 'CL',
    }, {
        name     => 'Spain',
        iso_code => 'ES',
    }],
});

# Create some more countries and set the language they use.
# (language is created if it does not already exist)
$db->resultset('Country')->create({
    name     => $_->{name},
    iso_code => $_->{code},
    language => { name => $_->{lang} },
}) for (
    { name => 'Palestine',     code => 'PS',     lang => 'Arabic' },
    { name => 'Egypt',         code => 'EG',     lang => 'Arabic' },
    { name => 'Scotland',      code => 'GB-SCT', lang => 'English' },
    { name => 'United States', code => 'US',     lang => 'English' },
    { name => 'Malta',         code => 'MT',     lang => 'Maltese' },
);

# Some more paintings and painters ...
$db->resultset('Painting')->create($_) for ({
   genre  => 'portrait',
   medium => 'oil',
   artwork => {
       name   => 'Portrait of Baldassare Castiglione',
       year   => 1514,
       artist => {
           name    => 'Raffaello Sanzio',
           country => { name => 'Italy' },
       },
   },
}, {
   genre  => 'genre',
   medium => 'oil',
   artwork => {
       name   => 'Benefits Supervisor Sleeping',
       year   => 1995,
       artist => {
           name    => 'Lucien Freud',
           country => { name => 'England' },
       },
   },
}, {
   genre  => 'modern',
   medium => 'acrylic',
   artwork => {
       name   => 'A Bigger Splash',
       year   => 1967,
       artist => {
           name    => 'David Hockney',
           country => { name => 'England' },
       },
   },
}, {
   genre  => 'modern',
   medium => 'oil',
   artwork => {
       name   => 'Femme à la guitare',
       year   => 1917,
       artist => {
           name    => 'María Gutiérrez Blanchard',
           country => { name => 'Spain' },
       },
   },
});

# Create more artists ...
my @artists = (
    ['Ghassan Zaqtan'        => 'PS'],
    ['Naguib Mahfouz'         => 'EG'],
    ['Pablo Neruda'          => 'CL'],
    ['Federico García Lorca' => 'ES'],
);

$db->resultset('Artist')->create({
    name => $_->[0], country => { iso_code => $_->[1] },
}) for @artists;

# ... create some more poems
$db->resultset('Poem')->create($_) for ({
    artwork => {
        name   => '&#1575;&#1604;&#1578;&#1604;&#1575;&#1604; &#1575;&#1604;&#1605;&#1575;&#1604;&#1581;&#1577;',
        year   => 1998,
        artist => { name => 'Ghassan Zaqtan'},
    },
}, {
    artwork => {
        name   => '2000 &#1575;&#1605; &#1575;&#1604;&#1604;',
        year   => 2003,
        artist => { name => 'Ghassan Zaqtan'},
    },
}, {
    artwork => {
        name   => 'El Niño Mudo',
        year   => 1927,
        artist => { name => 'Federico García Lorca'},
    },
}, {
    artwork => {
        name   => 'Sonnet 17',
        year   => 1959,
        artist => { name => 'Pablo Neruda' },
    },
}, {
    artwork => {
        name   => 'Sonnet 17',
        year   => 1609,
        artist => { name => 'William Shakespeare' },
    },
    meter  => 'iamb',
});

# ... a couple of plays
$db->resultset('Play')->create($_) for ({
    num_acts => 1,
    artwork  => {
        name   => '&#1578;&#1581;&#1578; &#1575;&#1604;&#1605;&#1592;&#1604;&#1577;',
        year   => 1973,
        artist => { name => 'Naguib Mahfouz'},
    },
}, {
    num_acts => 1,
    artwork  => {
        name   => 'El Maleficio de la Mariposa',
        year   => 1920,
        artist => { name => 'Federico García Lorca'},
    },
});

# Finally, some users and their countries ...
$db->resultset('User')->create($_) for (
    { name => '1nickt',     country => { iso_code => 'GB-ENG' } },
    { name => 'discipulus', country => { iso_code => 'IT' } },
    { name => 'tobyink',    country => { iso_code => 'GB-ENG' } },
    { name => 'bliako',     country => { iso_code => 'MT' } },
    { name => 'marioroy',   country => { iso_code => 'US' } },
    { name => 'marto',      country => { iso_code => 'GB-SCT' } },
);

# ... and at last, their comments.
#
# Each user gets 17 comments, one for each artwork. If the user chooses
# to comment more than once on an artwork, one of the user's comments
# is consumed and the existing comment about the artwork is updated, as
# a user can only have one comment stored on a given artwork.

my @comments = ('bad','meh','good');
my @artworks = $db->resultset('Artwork')->all;

for my $user ( $db->resultset('User')->all ) {
    for (0 .. $#artworks) {
        my $i = int rand($#artworks);
        $artworks[ $i ]->update_or_create_related('comments', {
            text    => $comments[ int rand(3) ],
            user_id => $user->user_id,
        });
    }
}

#---------------------------------------------------------------------#

## Complex queries against the database via DBIx relationsips

say "\nLIST OF PAINTINGS";

my $rs = $db->resultset('Painting');

while ( my $painting = $rs->next ) {
    say sprintf('%s: "%s" (%s)',
        $painting->artwork->artist->name,
        $painting->artwork->name,
        $painting->medium,
    );
}

#---------------------------------------------------------------------#
say "\nLIST OF PLAYS";

$rs = $db->resultset('Play');

while ( my $play = $rs->next ) {
    say sprintf('%s: "%s" (%s)',
        $play->artwork->artist->name,
        $play->artwork->name,
        $play->genre,
    );
}

#---------------------------------------------------------------------#
say "\nCOMMENTS ON PLAYS";

$rs = $db->resultset('Play');

while ( my $play = $rs->next ) {
    say sprintf('About "%s", %s said "%s"',
        $play->artwork->name,
        $_->user->name,
        $_->text,
    ) for $play->artwork->comments;
}

#---------------------------------------------------------------------#
say "\nART THAT BLIAKO LIKES";
# Starting here from the comments.
# Showing multiple joins.

$rs = $db->resultset('Comment')->search({
    'user.name' => 'bliako',
    'me.text'   => 'good',
}, {
    join => ['user', { artwork => 'artist' }],
});

while ( my $comment = $rs->next ) {
    say sprintf('bliako likes: "%s" (%s)',
        $comment->artwork->name,
        $comment->artwork->artist->name,
    );
}

#---------------------------------------------------------------------#
say "\nPAINTINGS THAT DISCIPULUS DOES NOT LOVE";
# Starting here from the paintings.
# Showing multiple and multi-level joins.

$rs = $db->resultset('Painting')->search({
    'user.name'     => 'discipulus',
    'comments.text' => { '-in' => ['bad', 'meh'] },
}, {
    join => { artwork => [ 'artist', { comments => 'user' } ] },
});

while ( my $painting = $rs->next ) {
    say sprintf('discipulus does not love "%s" (%s)',
        $painting->artwork->name,
        $painting->artwork->artist->name,
    );
}


#---------------------------------------------------------------------#
say "\nPOEMS NOT BY ENGLISH ARTISTS THAT TOBYINK DOESN'T HATE";
# Showing multiple multi-level joins; 'prefetch' needed here

$rs = $db->resultset('Poem')->search({
    'user.name'     => 'tobyink',
    'comments.text' => { '!=' => 'bad' },   # DBIx handles this NOT
    'country.name'  => \'!= country_2.name' # literal SQL for this NOT
}, {
    prefetch => {
        artwork => [
            { artist   => 'country' },
            { comments => { user => 'country' } },
        ],                          # table aliased here to 'country_2'
    },
});

while ( my $poem = $rs->next ) {
    say sprintf(q{tobyink doesn't hate "%s" (%s)},
        $poem->artwork->name,
        $poem->artwork->artist->name,
    );
}

#---------------------------------------------------------------------#
say "\nCOMMENTS BY USERS WHO SPEAK THE SAME LANGUAGE AS THE ARTIST";

$rs = $db->resultset('Comment')->search({
    'country.language_id' => \'= country_2.language_id', # literal SQL
}, {
    prefetch => [
        { user => { country => 'language' } },
        { artwork => { artist => 'country' } },
    ],
});

while ( my $comment = $rs->next ) {
    say sprintf('%s : %s said about "%s" (%s) : "%s"',
        $comment->user->country->language->name,
        $comment->user->name,
        $comment->artwork->name,
        $comment->artwork->artist->name,
        $comment->text,
    );
}

__DATA__

create database if not exists Art
    character set = utf8mb4 collate = utf8mb4_unicode_ci;

use Art;

drop table if exists comment;
drop table if exists user;
drop table if exists play;
drop table if exists poem;
drop table if exists painting;
drop table if exists artwork;
drop table if exists artist;
drop table if exists country;
drop table if exists language;

create table language (
    language_id smallint unsigned not null primary key auto_increment,
    name varchar(32) not null,
    unique key language_name_uk (name)
);

create table country (
    country_id smallint unsigned not null primary key auto_increment,
    iso_code varchar(6) not null,
    name varchar(16) not null,
    language_id smallint unsigned not null,
    unique key country_iso_code_uk (iso_code),
    unique key country_name_uk (name),
    constraint country_language_fk foreign key (language_id) references language (language_id)
);

create table artist (
    artist_id smallint unsigned not null primary key auto_increment,
    name varchar(32) not null,
    country_id smallint unsigned not null,
    unique key artist_name_uk (name),
    constraint artist_country_fk foreign key (country_id) references country (country_id)
);

create table artwork (
    artwork_id smallint unsigned not null primary key auto_increment,
    artist_id smallint unsigned not null,
    name varchar(128) not null,
    year smallint unsigned not null,
    unique key artwork_name_artist_uk (name, artist_id),
    constraint artwork_artist_fk foreign key (artist_id) references artist (artist_id)
);

create table painting (
    painting_id smallint unsigned not null primary key auto_increment,
    artwork_id smallint unsigned not null,
    medium enum('acrylic','oil','watercolour','other') not null default 'other',
    genre enum('history','portrait','genre','landscape','still life','modern','other') not null default 'other',
    unique key painting_artwork_uk (artwork_id),
    constraint painting_artwork_fk foreign key (artwork_id) references artwork (artwork_id)
);

create table poem (
    poem_id smallint unsigned not null primary key auto_increment,
    artwork_id smallint unsigned not null,
    meter enum('iamb','trochee','spondee','anapest','dactyl','other') not null default 'other',
    rhyme enum('end','internal','slant','rich','eye','identical','other') not null default 'other',
    unique key poem_artwork_uk (artwork_id),
    constraint poem_artwork_fk foreign key (artwork_id) references artwork (artwork_id)
);

create table play (
    play_id smallint unsigned not null primary key auto_increment,
    artwork_id smallint unsigned not null,
    num_acts smallint unsigned  not null,
    genre enum('comedy','tragedy','history','other') not null default 'other',
    unique key play_artwork_uk (artwork_id),
    constraint play_artwork_fk foreign key (artwork_id) references artwork (artwork_id)
);

create table user (
    user_id smallint unsigned not null primary key auto_increment,
    country_id smallint unsigned not null,
    name varchar(32) not null,
    unique key user_name_uk (name),
    constraint user_country_fk foreign key (country_id) references country (country_id)
);

create table comment (
    comment_id smallint unsigned not null primary key auto_increment,
    user_id smallint unsigned not null,
    artwork_id smallint unsigned not null,
    text blob,
    unique key comment_artwork_user_uk (artwork_id, user_id),
    constraint comment_artwork_fk foreign key (artwork_id) references artwork (artwork_id),
    constraint comment_user_fk foreign key (user_id) references user (user_id)
);