This is the fourth post of a series started with this post about a MonoRail and ActiveRecord tutorial.
Last time we wrote the Album class and we have seen how to implement the primary key mapping and simple properties mapping.
With simple properties mapping I mean the class properties that will go to map simple data type in the database. Instead with complex properties I will mean the class properties that will go to map a relationship with another table in the database.
For the album class there are simple properties (ID, Name and Year) and complex properties (Support, Artists and Genres).
The simple properties are mapped to standard data types: ID (primary key) to int, Name to string, Year to Int16.
The complex properties are mapped to object types: Support to Support Object, Artists to an IList of Artist Object and Genres to an IList of Genre objects.
Now to go on with the tutorial we should continue writing the other classes (otherwise Album class will not compile), but in this part I first want to show you how to implement relationships in your class.
For a complete introduction to relationship mapping in ActiveRecord you can look here. In this tutorial we will use the most important relationship mapping kinds: BelongsTo, HasAndBelongsToMany (simple and with an attribute).
BelongsTo Relationship mapping (1-n relationship)
In our sample, Support and Album are 1-n related.
In ActiveRecord this is translated using the BelongsTo attibute:
[BelongsTo("supportid")] public Support Support { get { return support; } set { support = value; } }
Doing so, we will be able from an Album instance to access its corresponding Support:
Support cdSupport = Support.FindByName("CD"); Album newAlbum = new Album(); newAlbum.Support = cdSupport;
This is implemented in the database with a relationship between the parent (Support) and the child (Album):
CREATE TABLE album ( id integer NOT NULL, name character varying(150) NOT NULL, "year" smallint, supportid smallint, CONSTRAINT album_pkey PRIMARY KEY (id), CONSTRAINT fk_support FOREIGN KEY (supportid) REFERENCES support (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE album OWNER TO castle;
You can change the cascading behavior using the cascade named parameter in the BelongsTo attribute of the class. For example setting Cascade=CascadeEnum.All will be reflected in the schema with an ON UPDATE CASCADE ON DELETE CASCADE behavior. Setting Cascade=CascadeEnum.Delete will be translated as an ON UPDATE NO ACTION ON DELETE CASCADE behavior.
Probably for our sample the best cascading behavior is ON UPDATE CASCADE ON DELETE NO ACTION (We want that the supportid attribute of album table will be updated if the id attribute of support table is updated, but we do not want that the rows in album table of certain support are deleted if that support is deleted on the support table).
HasAndBelongToMany Relationship mapping without attributes (n-n relationship)
In our sample Album and Artist have a n-n relationships, without attributes.
In ActiveRecord this is translated using the HasAndBelongToMany attibute:
[HasAndBelongsToMany(typeof(Artist), Table = "ArtistAlbum", ColumnKey = "albumid", ColumnRef = "artistid")] public IList Artists { get { return artists; } set { artists = value; } }
This is implemented in the database with an intermediate table (ArtistAlbum) and two relationship between the parent (Album, Artist) and the child (ArtistAlbum):
CREATE TABLE artistalbum ( artistid integer NOT NULL, albumid integer NOT NULL, CONSTRAINT fk_artist FOREIGN KEY (artistid) REFERENCES artist (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_album FOREIGN KEY (albumid) REFERENCES album (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE artistalbum OWNER TO castle;
Doing so, we will be able from an Album instance to access its corresponding Artist(s) and viceversa:
Artist newArtist = new Artist(); newArtist.Name = "The Velvet Underground"; newArtist.Create(); // album Album newAlbum = new Album(); newAlbum.Artists.Add(newArtist);
Also for this kind of relationship is possible to change the cascading behavior using the cascade parameter. Here the best choice would be ON UPDATE CASCADE ON DELETE CASCADE for both the relationships, so we could use the CascadeEnum.All named parameter in the HasAndBelongToMany attribute.
HasAndBelongToMany Relationship mapping with attributes (n-n relationship)
In our sample Album and Genre have a n-n relationships, with an attribute (main).
In ActiveRecord this is translated using the HasAndBelongToMany attibute in the same way like we did for the Album-Artist relationship. The only difference is that we need to implement a class AlbumGenre for managing the attribute.
[HasAndBelongsToMany(typeof(Genre), Table = "AlbumGenre", ColumnKey = "albumid", ColumnRef = "genreid")] public IList Genres { get { return genres; } set { genres = value; } }
This is implemented in the database with an intermediate table (AlbumGenre) and two relationship between the parent (Album, Genre) and the child (AlbumGenre):
CREATE TABLE albumgenre ( id integer NOT NULL, main BOOLEAN, albumid integer, genreid smallint, CONSTRAINT albumgenre_pkey PRIMARY KEY (id), CONSTRAINT fk_genre FOREIGN KEY (genreid) REFERENCES genre (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_album FOREIGN KEY (albumid) REFERENCES album (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE albumgenre OWNER TO castle;
We need now to implement the class AlbumGenre:
using System; using Castle.ActiveRecord; using NHibernate.Expression; namespace net.paolocorti.Samples.Castle.Tutorial.Models { /// <summary> /// AlbumGenre: it is needed to manage the main attribute in the many2many relationship /// </summary> [ActiveRecord("AlbumGenre")] public class AlbumGenre : ActiveRecordBase { private int _id; private Album _album; private Genre _genre; private bool _main; /// <summary> /// Primary Key /// </summary> [PrimaryKey(PrimaryKeyType.Native)] //Use sequence for Postgres, Native for SQL Server and MySQL public int Id { get { return _id; } set { _id = value; } } /// <summary> /// Album /// </summary> [BelongsTo("albumid")] public Album Album { get { return _album; } set { _album = value; } } /// <summary> /// Genre /// </summary> [BelongsTo("genreid")] public Genre Genre { get { return _genre; } set { _genre = value; } } /// <summary> /// Main genre /// </summary> [Property] public bool Main { get { return _main; } set { _main = value; } } /// <summary> /// Get AlbumGenre of an album /// </summary> /// <param name="album"></param> /// <returns></returns> public static AlbumGenre[] FindByAlbum(Album album) { return (AlbumGenre[])FindAll(typeof(AlbumGenre), Expression.Eq("Album.Id", album.Id)); } /// <summary> /// Get AlbumGenre from an album and a genre /// </summary> /// <param name="album"></param> /// <param name="genre"></param> /// <returns></returns> public static AlbumGenre Find(Album album, Genre genre) { return (AlbumGenre)FindOne(typeof(AlbumGenre), Expression.Eq("Album.Id", album.Id), Expression.Eq("Genre.Id", genre.Id)); } /// <summary> /// Get Main genre of an album /// </summary> /// <param name="album"></param> /// <returns></returns> public static Genre FindMainGenre(Album album) { AlbumGenre[] albumGenres = FindByAlbum(album); Genre mainGenre = null; foreach(AlbumGenre ag in albumGenres) { if (ag.Main == true) { mainGenre = ag.Genre; } } return mainGenre; } } }
Doing so, we will be able from an Album instance to access its corresponding Genres:
Support cdSupport = Support.FindByName("CD"); Genre rockGenre = Genre.FindByName("Rock"); Genre grungeGenre = Genre.FindByName("Grunge"); // artist Artist newArtist = new Artist(); newArtist.Name = "Nirvana"; newArtist.Create(); // album Album newAlbum = new Album(); newAlbum.Artists.Add(newArtist); newAlbum.Name = "Never Mind"; newAlbum.Year = 1992; newAlbum.Genres.Add(rockGenre); newAlbum.Genres.Add(grungeGenre); newAlbum.Support = cdSupport; newAlbum.Create(); // albumgenre AlbumGenre[] albumGenre = AlbumGenre.FindByAlbum(newAlbum); for (int i = 0; i < albumGenre.Length; i++) { if (albumGenre[i].Genre.Name == "Grunge") { albumGenre[i].Main = true; albumGenre[i].Save(); } }
Next time we will end writing our domain model classes.
I completely don’t understand this sentence, could you please explain what you mean??:
“We want that the supportid attribute of album table will be updated if the id attribute of support table is updated, but we do not want that the rows in album table of certain support are deleted if that support is deleted on the support table”
hi i enjoyed the read