In IT world subjects like DDD, CQRS, event sourcing are subject that are still extremely popular. It is hard to be on a IT conference and haven't got an option to see at least one presentation that is somehow related to one of aforementioned techniques. Though, it can be still interesting! And as I said in my previous post: I'm on the mission to get my SQL to the higher level. So can those two worlds be combined? Sure they can!
After your introduction of CQRS into your system, you can try to separate read model from the write model completely. Like - move all the aggregates from the "write" database to some other (usually no-sql) database. It seems to be obvious that ongoing aggregation of values can be highly more efficient than selecting all rows and performing some reducion - for example on every request.
What I'm going to do? I'm going to write some SQL to create an aggregate - and update it using a database trigger every time some other table will be modified. That will cause sort of separation between read and write models, but I leave one database for both.
Step 1. Create tables
Movies and their ratings will be our case study. Movie can be rated, and aggregate is an average rating for a movie. We need Movies table, Ratings table, and RatingAggregate to hold computed values.
create table Movies( Id int not null identity(1,1) primary key, Name varchar(max) not null ) create table Ratings( Id int not null identity(1,1) primary key, Value int not null, MovieId int not null foreign key references Movies ) create table RatingAggregate( MovieId int not null primary key foreign key references Movies, Value float not null default(0.0), RatingsCount int not null default(0) )
Step 2. Add some data
Pretty obvious, right?
insert into Movies(Name) values ('Deadpool'), ('Lord of the rings'), ('Sharknado'), ('Hobbit')
Step 3. Create a trigger
Average can be defined as:
Average_new = ((Average_before * number of ratings) + new rating) / (number of ratings + 1)
At the beginning we can initialize average and number of ratings to zeros. After that we perform the necessary updates.
create trigger RatingAggregateUpdate on Ratings after insert as begin set transaction isolation level serializable --begin transaction -- initialize those which are not present yet insert into RatingAggregate(MovieId, Value, RatingsCount) select distinct i.MovieId as MovieId, 0.0 as Value, 0 as RatingsCount from inserted i left join RatingAggregate ra on i.MovieId = ra.MovieId where ra.MovieId IS NULL -- update values update RatingAggregate set Value = ((ra.Value*ra.RatingsCount)+ratingsForMovies.RatingsSum)/(ra.RatingsCount+ratingsForMovies.RatingsCount), RatingsCount = ra.RatingsCount + ratingsForMovies.RatingsCount from RatingAggregate ra inner join (select i.MovieId, SUM(i.Value) as RatingsSum, COUNT(*) as RatingsCount from inserted i group by i.MovieId) as ratingsForMovies on ra.MovieId = ratingsForMovies.MovieId --commit transaction end go
You may say - hey what is happening here. When trigger is fired we get "inserted" values. That is a set, not just a single value! That's why we have to group those inserted values. But chill out - those "ones" from the initial average definition are replaced by sum of the ratings and number of the ratings grouped by the movie. That's all.
Step 4. Insert some ratings
Let's just simulate that our users has added some ratings:
insert into Ratings(Value, MovieId) values (5, 1), (5, 1), (1, 2), (1, 2), (3, 3), (3, 3), (4, 4), (4, 4) go insert into Ratings(Value, MovieId) values (7, 1), (7, 1), (3, 2), (3, 2), (5, 3), (5, 3), (6, 4), (6, 4) go
(I'm using two insert statements here for testing purposes)
Step 5. Select aggregated data
Finally, we can use our read model and find the average ratings for the movies.
select movies.Name, COALESCE(ra.Value, 0) as Rating from Movies movies left outer join RatingAggregate ra on movies.Id = ra.MovieId
Calculating average ratings for movies using the built-in AVG function is obvious:
select movies.Id, COALESCE(AVG(CAST(r.Value as float)), 0.0) as Rating from Movies movies left outer join Ratings r on movies.Id = r.MovieId group by movies.Id
But that is just an average, right? Things usually can became much more complicated. In every other case we can use User Defined Functions or... this fancy trigger-based solution ;>
To sum up: we can create aggregates using database triggers ;)
Click for source code
P.S. Some more selects
Careful reader probably have found out that when using an aggregate I had selected movies' names, but only ids in the AVG-based example. That's right - if I want to get names in the second case I'd have to make one more step. The step can be implemented in more than one way (I tried in 4 ways, but they are actually all more or less alike). Dive into code and check now :) Farewell!