Create a better relationship

[FCO]

model CD {
   has           $!artist-id is referencing{ Artist.id };
   has Artist $.author is relationship{ .artist-id };
}
model Artist {
   has UInt $.id is column{ :id };
   has CD   @.cds is relationship{ .artist-id };
}

The trait will receive a block and run it passing: if the type of the attribute is positional the attribute type, else the model’s type. It’s return should be a column that is referencing some other column. It will create a new result seq using that.

[FCO]

has Track %.cd-tracks{CD} is relationship({ .artist-id },{ .cd-id });

$artist.cds-tracks.keys; # list the artist’s cds
$artist.cds-tracks.values; # list the artists’s tracks
$artist.cds-tracks{ :title("cd title") }; # list the tracks of the cd with title “cd title” of the artist

[moritz]

 has Track %.cd-tracks{CD} is relationship({ .artist-id },{ .cd-id });

How does that work? What kind of object is passed to the callbacks? I don't even see an object that has a cd-id attribute or method.

[FCO]

The idea is: for the first Block it'll pass a CD type object and for the second one a Track type object. both gotten from the type of the parameter.

as it does with the positional one.

@moritz And I'm sorry... its not all the code... its based on this example: https://github.com/FCO/Red/wiki/CD-sample
cd-id is from the Track class

[FCO]

lib/Track.pm6

use Red;

model CD { ... }
model Artist { ... }

model Track {
   has Uint  $.id             is column{ :id, :!nullable };
   has Uint  $.cd-id          is referencing{ CD.id };
   has Str   $.title          is column;
   has Track %.cds-tracks{CD} is relationship({ .artist-id },{ .cd-id });

   ::?CLASS.^add-unique-constraint: { .cd-id, .title };
}

lib/CD.pm6

use Red;
use Track;

model Artist { ... }

model CD {
   has UInt   $.id          is column{ :id, :!nullable };
   has UInt   $!artist-id   is referencing{ Artist.id };
   has Str    $.title       is column;
   has UInt   $.year        is column;
   has Artist $.artist      is relationship{ .artist-id };
   has Track  @.tracks      is relationship{ .cd-id };

   ::?CLASS.^add-unique-constraint: { .artist-id, .title };
}

lib/Artist.pm6

use Red;
use CD;

model Artist {
   has UInt $.id    is column{ :id, :!nullable };
   has Str  $.name  is column{ :unique, :!nullable };
   has CD   @.cds   is relationship{ .artist-id };
}

[MattOates]

So one thing missing is perhaps annotating the primary key (possibly composite) rather than just unique constraint, I assume here the :id on a column is that its a primary key with an auto index for that one column? perhaps :pk ? with :id being a special single column case of that. In SQLAlchemy at least the auto increment is implied by the type of a primary key, if its a single int column its assumed its a serial etc. I actually think thats a mistake because its not obvious when you're new, and sometimes you might not actually want it as behaviour.

[FCO]

Yes, the :id means primary key and you can :id more than one column to make a composed pk (on the arguments order). But if you prefer you could also do ::?CLASS.^add-primary-key: { .bla, .ble } (NYI).
there are more planned ways to "create a primary key", like #18 and #19.

[perlpilot]

Was just playing around with the syntax a little bit. I hope it makes sense since I'm a kinda tired right now:

model CD {
   column UInt   $.id          is ID{ :!nullable, :auto-increment };
   column UInt   $!artist-id   is referencing{ Artist.id };
   column Str    $.title;
   column UInt   $.year;

   column Artist $.artist      is has-one{ Artist.artist-id };
   column Track  @.tracks      is has-many{ Track.cd-id };

   ::?CLASS.^add-unique-constraint: { .artist-id, .title };
}

model Artist {
   column UInt $.id    is ID{ :!nullable, :auto-increment };
   column Str  $.name  is constraint{ :unique, :!nullable };
   column CD   @.cds   is has-many{ .artist-id };
}

model Track {
   column Uint  $.id             is ID{ :!nullable, :auto-increment };
   column Uint  $.cd-id          is has-one{ CD.id };
   column Str   $.title;
   column Track %.cds-tracks{CD} is relationship({ .artist-id },{ .cd-id }); 

   ::?CLASS.^add-unique-constraint: { .cd-id, .title };
}

[FCO]

Hi @perlpilot! Thanks for your help!

My idea of not using explicitly the type, as on is has-many{ Track.cd-id }, because I plan to use an alias for that.
I was thinking about has-many and has-one... the original idea was that it can be inferred by the tipe of the attribute, if its Positional or not.
I think I like the column keyword.
And why uppercased ID?

Another thing: on actual implementation relationship aren't column. Do you think it should be?

[perlpilot]

My idea of not using explicitly the type, as on is has-many{ Track.cd-id }, because I plan to use an alias for that.

I was thinking that it could be either or. Left off for terseness, but allowed for documentation purposes.

I was thinking about has-many and has-one... the original idea was that it can be inferred by the tipe of the attribute, if its Positional or not.

I dunno. Why I used explicit naming of the relationships was for documentation purposes. It makes it a little easier to see what's going on.

And why uppercased ID?

I told you I was tired :-) I should have expounded a little on what was going on in my head. Uppercase ID because it seems "special" in that it could imply :!nullable and :auto-increment (or, of course, those could also be spelled out).

Another thing: on actual implementation relationship aren't column. Do you think it should be?

I dunno. Even though they aren't actual columns, they act as such as far as the ORM is concerned. Maybe another declarator?

model CD {
    ...
    relationship Artist $.artist   is has-one{ .artist-id }
}

It jibes with my leaning towards self-documention.

I like where you're going with this, but it feels like one of those things where if you get all the details right, it'll be awesome and if you miss a few things, it'll just be meh. For instance, the way the %.cds-tracks{CD} relationship is expressed doesn't seem quite right to me (that might just be in my head though :-). It feels like there should be something better.

Anyway, keep up the good work! Maybe get some input from people who have built ORMs (like Matt Trout). More input and more ideas can only make it better. :-)

[FCO]

I dunno. Why I used explicit naming of the relationships was for documentation purposes. It makes it a little easier to see what's going on.

What do you think should happen if some one do: has @.cds is has-one{ .cd-id }?

I told you I was tired :-) I should have expounded a little on what was going on in my head. Uppercase ID because it seems "special" in that it could imply :!nullable and :auto-increment (or, of course, those could also be spelled out).

Had you had time to take a look at #18 and #19?

[FCO]

@perlpilot, @moritz @MattOates : what do you guys think about this?

model Artist { ... }

model CD {
   has UInt      $.id        is id;;
   has           $!artist-id is referencing{ Artist.id };
   has Artist    $.author    is relationship{ .artist-id };
}

model Track {
   has UInt   $.id     is id;
   has        $!cd-id  is referencing{ CD.id };
   has CD     $.cd     is relationship{ .cd-id };
   has Artist $.artist = $!cd.artist;
}

model Artist {
   has UInt  $.id     is id;
   has CD    @.cds    is relationship{ .artist-id };
   has Track @.tracks = @!cds.flatmap: *.tracks;
}

[FCO]

This last example is what's "working" now!
what do you guys think?