Re: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?)

1nickt this is a great demonstration and guide. What I tried last night was to bootstrap a DBIx::Class::Schema from your SQL and then take ownership of those schemata, meaning: making any changes in there and not to the SQL. (I have noticed dbicdump produces pod content too which is helpful).
$ mysql --defaults-extra-file=config/mysql.credentials < ../experiment
+s/1nickt-db/schema.sql 

$ dbicdump -o debug=1 -o dump_directory=. Art::Schema 'dbi:mysql:datab
+ase=Art;mysql_read_default_file=config/mysql.credentials;mysql_read_d
+efault_group=client'

# discovered the credentials file option of mysql, see above

Art::Schema::Result::Artist->table("artist");
Art::Schema::Result::Artist->add_columns(
  "artist_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "name",
  { data_type => "varchar", is_nullable => 0, size => 32 },
  "country_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
);
Art::Schema::Result::Artist->set_primary_key("artist_id");
Art::Schema::Result::Artist->add_unique_constraint("artist_name_uk", [
+"name"]);
Art::Schema::Result::Artwork->table("artwork");
Art::Schema::Result::Artwork->add_columns(
  "artwork_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "artist_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "name",
  { data_type => "varchar", is_nullable => 0, size => 128 },
  "year",
  { data_type => "smallint", extra => { unsigned => 1 }, is_nullable =
+> 0 },
);
Art::Schema::Result::Artwork->set_primary_key("artwork_id");
Art::Schema::Result::Artwork->add_unique_constraint("artwork_name_arti
+st_uk", ["name", "artist_id"]);
Art::Schema::Result::Comment->table("comment");
Art::Schema::Result::Comment->add_columns(
  "comment_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "user_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "artwork_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "text",
  { data_type => "blob", is_nullable => 1 },
);
Art::Schema::Result::Comment->set_primary_key("comment_id");
Art::Schema::Result::Comment->add_unique_constraint("comment_artwork_u
+ser_uk", ["artwork_id", "user_id"]);
Art::Schema::Result::Country->table("country");
Art::Schema::Result::Country->add_columns(
  "country_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "iso_code",
  { data_type => "varchar", is_nullable => 0, size => 6 },
  "name",
  { data_type => "varchar", is_nullable => 0, size => 16 },
  "language_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
);
Art::Schema::Result::Country->set_primary_key("country_id");
Art::Schema::Result::Country->add_unique_constraint("country_iso_code_
+uk", ["iso_code"]);
Art::Schema::Result::Country->add_unique_constraint("country_name_uk",
+ ["name"]);
Art::Schema::Result::Language->table("language");
Art::Schema::Result::Language->add_columns(
  "language_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "name",
  { data_type => "varchar", is_nullable => 0, size => 32 },
);
Art::Schema::Result::Language->set_primary_key("language_id");
Art::Schema::Result::Language->add_unique_constraint("language_name_uk
+", ["name"]);
Art::Schema::Result::Painting->table("painting");
Art::Schema::Result::Painting->add_columns(
  "painting_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "artwork_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "medium",
  {
    data_type => "enum",
    default_value => "other",
    extra => { list => ["acrylic", "oil", "watercolour", "other"] },
    is_nullable => 0,
  },
  "genre",
  {
    data_type => "enum",
    default_value => "other",
    extra => {
      list => [
        "history",
        "portrait",
        "genre",
        "landscape",
        "still life",
        "modern",
        "other",
      ],
    },
    is_nullable => 0,
  },
);
Art::Schema::Result::Painting->set_primary_key("painting_id");
Art::Schema::Result::Painting->add_unique_constraint("painting_artwork
+_uk", ["artwork_id"]);
Art::Schema::Result::Play->table("play");
Art::Schema::Result::Play->add_columns(
  "play_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "artwork_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "num_acts",
  { data_type => "smallint", extra => { unsigned => 1 }, is_nullable =
+> 0 },
  "genre",
  {
    data_type => "enum",
    default_value => "other",
    extra => { list => ["comedy", "tragedy", "history", "other"] },
    is_nullable => 0,
  },
);
Art::Schema::Result::Play->set_primary_key("play_id");
Art::Schema::Result::Play->add_unique_constraint("play_artwork_uk", ["
+artwork_id"]);
Art::Schema::Result::Poem->table("poem");
Art::Schema::Result::Poem->add_columns(
  "poem_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "artwork_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "meter",
  {
    data_type => "enum",
    default_value => "other",
    extra => {
      list => ["iamb", "trochee", "spondee", "anapest", "dactyl", "oth
+er"],
    },
    is_nullable => 0,
  },
  "rhyme",
  {
    data_type => "enum",
    default_value => "other",
    extra => {
      list => ["end", "internal", "slant", "rich", "eye", "identical",
+ "other"],
    },
    is_nullable => 0,
  },
);
Art::Schema::Result::Poem->set_primary_key("poem_id");
Art::Schema::Result::Poem->add_unique_constraint("poem_artwork_uk", ["
+artwork_id"]);
Art::Schema::Result::User->table("user");
Art::Schema::Result::User->add_columns(
  "user_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "country_id",
  {
    data_type => "smallint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "name",
  { data_type => "varchar", is_nullable => 0, size => 32 },
);
Art::Schema::Result::User->set_primary_key("user_id");
Art::Schema::Result::User->add_unique_constraint("user_name_uk", ["nam
+e"]);
Art::Schema::Result::Artist->has_many(
  "artworks",
  "Art::Schema::Result::Artwork",
  { "foreign.artist_id" => "self.artist_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Artist->belongs_to(
  "country",
  "Art::Schema::Result::Country",
  { country_id => "country_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::Artwork->belongs_to(
  "artist",
  "Art::Schema::Result::Artist",
  { artist_id => "artist_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::Artwork->has_many(
  "comments",
  "Art::Schema::Result::Comment",
  { "foreign.artwork_id" => "self.artwork_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Artwork->might_have(
  "painting",
  "Art::Schema::Result::Painting",
  { "foreign.artwork_id" => "self.artwork_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Artwork->might_have(
  "play",
  "Art::Schema::Result::Play",
  { "foreign.artwork_id" => "self.artwork_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Artwork->might_have(
  "poem",
  "Art::Schema::Result::Poem",
  { "foreign.artwork_id" => "self.artwork_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Comment->belongs_to(
  "artwork",
  "Art::Schema::Result::Artwork",
  { artwork_id => "artwork_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::Comment->belongs_to(
  "user",
  "Art::Schema::Result::User",
  { user_id => "user_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::Country->has_many(
  "artists",
  "Art::Schema::Result::Artist",
  { "foreign.country_id" => "self.country_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Country->belongs_to(
  "language",
  "Art::Schema::Result::Language",
  { language_id => "language_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::Country->has_many(
  "users",
  "Art::Schema::Result::User",
  { "foreign.country_id" => "self.country_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Language->has_many(
  "countries",
  "Art::Schema::Result::Country",
  { "foreign.language_id" => "self.language_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::Painting->belongs_to(
  "artwork",
  "Art::Schema::Result::Artwork",
  { artwork_id => "artwork_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::Play->belongs_to(
  "artwork",
  "Art::Schema::Result::Artwork",
  { artwork_id => "artwork_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::Poem->belongs_to(
  "artwork",
  "Art::Schema::Result::Artwork",
  { artwork_id => "artwork_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
Art::Schema::Result::User->has_many(
  "comments",
  "Art::Schema::Result::Comment",
  { "foreign.user_id" => "self.user_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
Art::Schema::Result::User->belongs_to(
  "country",
  "Art::Schema::Result::Country",
  { country_id => "country_id" },
  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRIC
+T" },
);
[download]
Comment on Re: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?) Select or Download Code