// This may become it's own command
private async Task <LibraryDto> CreateLibrary(string title, string description, int userId)
{
const string sql = @"
INSERT INTO plum.libraries (title, description, created_by)
VALUES (@title, @description, @userId)
RETURNING id, title, description, created_by, created_at";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return((await cnn.QueryAsync <LibraryDto>(sql, new { title, description, userId })).Single());
}
}
public async Task <IDictionary <int, Role> > Handle(GetRolesForMembers request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT
rol.id,
rol.title,
rpv.privilege_alias,
mem.id AS membership_id
FROM plum.roles rol
INNER JOIN plum.memberships mem
ON mem.role_id = rol.id
LEFT JOIN plum.role_privileges rpv
ON rpv.role_id = rol.id
WHERE mem.id = ANY(@MembershipIds)
ORDER BY rol.created_at";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
var dtos = await cnn.QueryAsync <RoleDto>(sql, request);
return(dtos
.GroupBy(d => d.MembershipId)
.ToDictionary(g => g.Key, g => _mapper.Map <Role>(g)));
}
}
private async Task <Member> GetSignedInMember(GetSignedInMember request)
{
var nameIdentifier = request.User.GetNameIdentifier();
const string sql = @"
SELECT
mem.id,
mem.user_id,
mem.library_id,
(CASE
WHEN mem.display_name = ''
THEN usr.display_name
ELSE mem.display_name END) AS display_name,
usr.display_name AS full_name,
mem.created_at,
usr.id = lib.created_by AS is_creator
FROM plum.users usr
INNER JOIN plum.memberships mem
ON usr.id = mem.user_id
INNER JOIN plum.libraries lib
ON lib.id = mem.library_id
WHERE usr.google_claim_nameidentifier = @nameIdentifier
AND mem.library_id = @LibraryId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
var dtos = await cnn.QueryAsync <MemberDto>(sql, new { nameIdentifier, request.LibraryId });
return(_mapper.Map <Member>(dtos.Single()));
}
}
public async Task <Unit> Handle(ReplacePrivilegesOfRole request, CancellationToken cancellationToken)
{
const string deleteSql = @"
DELETE FROM plum.role_privileges
WHERE role_id = @RoleId";
const string insertSql = @"
INSERT INTO plum.role_privileges (role_id, privilege_alias)
VALUES (@RoleId, @Alias)
ON CONFLICT DO NOTHING";
var rps = request.Privileges.Distinct().Select(p => new { p.Alias, request.RoleId });
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.ExecuteAsync(deleteSql, request);
await cnn.ExecuteAsync(insertSql, rps);
}
scope.Complete();
}
return(Unit.Value);
}
public async Task <ReplyDto> Handle(CreateAnnotationReply request, CancellationToken cancellationToken)
{
const string sql = @"
WITH REP AS (
INSERT INTO plum.replies (user_id, text, annotation_id)
VALUES (@UserId, @Text, @AnnotationId)
RETURNING id, text, user_id, annotation_id
)
SELECT
REP.id,
REP.text,
REP.annotation_id,
REP.user_id,
(CASE
WHEN mem.display_name = ''
THEN USR.display_name
ELSE mem.display_name END) AS display_name
FROM REP
INNER JOIN plum.annotations ANN
ON ANN.id = @AnnotationId
INNER JOIN plum.videos VID
ON VID.id = ANN.video_id
INNER JOIN plum.memberships MEM
ON MEM.user_id = REP.user_id AND VID.library_id = MEM.library_id
INNER JOIN plum.users USR
ON REP.user_id = USR.id";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return((await cnn.QueryAsync <ReplyDto>(sql, request)).SingleOrDefault());
}
}
public async Task <AnnotationDto> Handle(CreateAnnotation request, CancellationToken cancellationToken)
{
const string sql = @"
WITH ANN AS (
INSERT INTO plum.annotations (user_id, comment, timestamp, video_id)
VALUES (@UserId, @Comment, @Timestamp, @VideoId)
RETURNING id, comment, timestamp, user_id, video_id
)
SELECT
ANN.id,
ANN.comment,
ANN.timestamp,
ANN.user_id,
(CASE
WHEN MEM.display_name = ''
THEN USR.display_name
ELSE MEM.display_name END)
FROM ANN
INNER JOIN plum.memberships MEM
ON MEM.user_id = ANN.user_id
INNER JOIN plum.users USR
ON MEM.user_id = USR.id
INNER JOIN plum.videos VID
ON MEM.library_id = VID.library_id AND VID.id = ANN.video_id";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return((await cnn.QueryAsync <AnnotationDto>(sql, request)).SingleOrDefault());
}
}
public async Task <IEnumerable <InvitationDto> > Handle(GetInvitationsForLibrary request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT
inv.id AS id,
inv.url_key as url_key,
inv.created_at as created_at,
mem.display_name as display_name,
usr.display_name as full_name,
rol.title as role_title,
lib.title as library_title,
inv.expires_at as expires_at,
rol.id as role_id,
mem.id as membership_id,
lib.id as library_id
FROM plum.invitations inv
INNER JOIN plum.memberships mem
ON mem.id = inv.membership_id
INNER JOIN plum.users usr
ON usr.id = mem.user_id
INNER JOIN plum.roles rol
ON rol.id = inv.role_id
INNER JOIN plum.libraries lib
ON lib.id = rol.library_id
WHERE
inv.deleted_at IS NULL
AND (inv.expires_at > NOW() OR inv.expires_at IS NULL)
AND lib.id = @LibraryId
ORDER BY inv.created_at DESC";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return(await cnn.QueryAsync <InvitationDto>(sql, request));
}
}
public async Task <IEnumerable <MemberDto> > GetMembersOfLibrary(GetMembersOfLibrary request)
{
const string sql = @"
SELECT
mem.id,
usr.id AS user_id,
lib.id AS library_id,
(CASE
WHEN mem.display_name = ''
THEN usr.display_name
ELSE mem.display_name END) AS display_name,
usr.display_name AS full_name,
mem.created_at,
usr.id = lib.created_by AS is_creator
FROM plum.users usr
INNER JOIN plum.memberships mem
ON usr.id = mem.user_id
INNER JOIN plum.libraries lib
ON lib.id = mem.library_id
WHERE mem.library_id = @LibraryId
ORDER BY mem.created_at";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return(await cnn.QueryAsync <MemberDto>(sql, new { request.LibraryId }));
}
}
public async Task <Unit> Handle(CreateVideo request, CancellationToken cancellationToken)
{
var userId = request.UserId;
var libraryId = request.LibraryId;
var title = request.Title;
var link = request.Link;
var description = request.Description;
// TODO - Remove this and add validation to make sure only valid youtube urls can get added
var parsedUrl = link;
if (link.Length == 43)
{
parsedUrl = link.Substring(32, 11);
}
const string sql = @"
WITH videoURLS AS (
INSERT INTO plum.video_urls (url)
VALUES (@parsedUrl)
RETURNING id, url
)
INSERT INTO plum.videos (title, description, video_url_id, library_id)
SELECT @title, @description, id, @libraryId FROM videoURLS";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.ExecuteAsync(sql, new { userId, libraryId, title, parsedUrl, description });
}
return(Unit.Value);
}
private async Task <IEnumerable <LibraryDto> > SearchLibraries(GetSearchResults request)
{
const string sql = @"
SELECT lib.id, lib.title, lib.description, lib.created_by, lib.created_at
FROM plum.libraries lib
INNER JOIN plum.memberships mem
ON lib.id = mem.library_id
WHERE
to_tsvector(title || ' ' || description)
@@ plainto_tsquery(@Query)
AND mem.user_id = @UserId
AND lib.deleted_at IS NULL
ORDER BY lib.created_at DESC";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return(await cnn.QueryAsync <LibraryDto>(sql, request));
}
}
public async Task <Unit> Handle(DeleteRoleById request, CancellationToken cancellationToken)
{
const string sql = @"UPDATE plum.roles SET deleted_at = NOW() WHERE id = @RoleId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.ExecuteAsync(sql, request);
}
return(Unit.Value);
}
public async Task <UserDto> Handle(CreateUserWithoutAuth request, CancellationToken cancellationToken)
{
const string sql = @"
INSERT INTO plum.users(display_name)
VALUES (@DisplayName)
RETURNING id, created_at, display_name";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return((await cnn.QueryAsync <UserDto>(sql, new { request.DisplayName })).Single());
}
}
public async Task <IEnumerable <LibraryDto> > Handle(GetLibrariesCreatedByUserId request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT id, title, description, created_by, created_at
FROM plum.libraries
WHERE created_by = @UserId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return(await cnn.QueryAsync <LibraryDto>(sql, new { request.UserId }));
}
}
public async Task <LibraryDto> Handle(GetLibraryDtoById request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT id, title, description, created_by, created_at
FROM plum.libraries
WHERE id = @Id";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return((await cnn.QueryAsync <LibraryDto>(sql, new { request.Id })).Single());
}
}
public async Task <int> Handle(CreateRole request, CancellationToken cancellationToken)
{
const string sql = @"
INSERT INTO plum.roles (title, library_id)
VALUES (@Title, @LibraryId)
RETURNING id";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return((await cnn.QueryAsync <int>(sql, request)).Single());
}
}
public async Task <IEnumerable <ReplyDto> > Handle(GetAnnotationRepliesByVideoId request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT replies.id, annotation_id, text, replies.user_id
FROM plum.replies
INNER JOIN plum.annotations ON annotations.id = replies.annotation_id
WHERE annotations.video_id = @videoId AND replies.deleted_at IS NULL";
using (var conn = _sqlConnectionFactory.GetSqlConnection())
{
return(await conn.QueryAsync <ReplyDto>(sql, new { request.VideoId }));
}
}
public async Task <UserDto> Handle(GetUserByUserId request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT id, display_name, google_claim_nameidentifier, created_at
FROM plum.users
WHERE id = @UserId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
var users = await cnn.QueryAsync <UserDto>(sql, new { request.UserId });
return(users.Single());
}
}
public async Task <VideoDto> Handle(GetVideoById request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT videos.id, videos.title, videos.description, videos.library_id, video_urls.url
FROM plum.videos
INNER JOIN plum.video_urls
ON videos.video_url_id = video_urls.id
WHERE videos.id = @Id";
using (var conn = _sqlConnectionFactory.GetSqlConnection())
{
return((await conn.QueryAsync <VideoDto>(sql, new { request.Id })).Single());
}
}
public async Task <Unit> Handle(UpdateRoleOfMember request, CancellationToken cancellationToken)
{
const string sql = @"
UPDATE plum.memberships
SET role_id = @RoleId, display_name = @DisplayName
WHERE id = @MemberId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.QueryAsync(sql, request);
}
return(Unit.Value);
}
public async Task <IEnumerable <LibraryDto> > Handle(GetLibrariesForUser request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT lib.id, lib.title, lib.description, lib.created_by, lib.created_at
FROM plum.libraries lib
INNER JOIN plum.memberships mem
ON lib.id = mem.library_id
WHERE mem.user_id = @UserId
AND lib.deleted_at IS NULL";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return(await cnn.QueryAsync <LibraryDto>(sql, new { request.UserId }));
}
}
public async Task <Unit> Handle(DeleteMemberOfLibrary request, CancellationToken cancellationToken)
{
var membershipId = request.MembershipId;
const string sql = @"
DELETE FROM plum.memberships
WHERE id = @membershipId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.QueryAsync(sql, new { membershipId });
}
return(Unit.Value);
}
public async Task <UserDto> Handle(GetSignedInUserDto request, CancellationToken cancellationToken)
{
var nameIdentifier = request.User.GetNameIdentifier();
const string sql = @"
SELECT id, display_name, google_claim_nameidentifier, created_at
FROM plum.users
WHERE google_claim_nameidentifier = @nameIdentifier";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
var users = await cnn.QueryAsync <UserDto>(sql, new { nameIdentifier });
return(users.SingleOrDefault());
}
}
public async Task <Unit> Handle(DeleteAnnotationReply request, CancellationToken cancellationToken)
{
const string sql = @"
UPDATE plum.replies
SET deleted_at = NOW()
WHERE
user_id = @UserId
AND id = @ReplyId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.ExecuteAsync(sql, request);
}
return(Unit.Value);
}
public async Task <Unit> Handle(DeleteVideoFromLibrary request, CancellationToken cancellationToken)
{
var videoId = request.VideoId;
const string sql = @"
UPDATE plum.videos
SET deleted_at = NOW()
WHERE id = @videoId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.QueryAsync(sql, new { videoId });
}
return(Unit.Value);
}
public async Task <IEnumerable <VideoDto> > Handle(GetVideosOfLibrary request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT
videos.title,
videos.description,
videos.id
FROM plum.videos
WHERE videos.library_id = @LibraryId
AND videos.deleted_at IS NULL
ORDER BY videos.created_at DESC";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
return(await cnn.QueryAsync <VideoDto>(sql, new { request.LibraryId }));
}
}
public async Task <Unit> Handle(EnsureUserIsPersisted request, CancellationToken cancellationToken)
{
var name = request.User.GetName();
var nameidentifier = request.User.GetNameIdentifier();
const string sql = @"
INSERT INTO plum.users (display_name, google_claim_nameidentifier)
VALUES (@name, @nameidentifier)
ON CONFLICT ON CONSTRAINT users_google_claim_nameidentifier_key DO NOTHING";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.ExecuteAsync(sql, new { name, nameidentifier });
}
return(Unit.Value);
}
public async Task <Unit> Handle(UpdateDisplayName request, CancellationToken cancellationToken)
{
var newDisplayName = request.NewDisplayName;
var membershipId = request.MembershipId;
const string updateDisplayNameSql = @"
UPDATE plum.memberships
SET display_name = @newDisplayName
WHERE id = @membershipId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.QueryAsync(updateDisplayNameSql, new { newDisplayName, membershipId });
}
return(Unit.Value);
}
public async Task <Unit> Handle(UpdateVideoInfo request, CancellationToken cancellationToken)
{
var videoId = request.VideoId;
var newTitle = request.NewTitle;
var newDescription = request.NewDescription;
const string sql = @"
UPDATE plum.videos
SET title = @newTitle, description = @newDescription
WHERE id = @videoId";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.QueryAsync(sql, new { videoId, newTitle, newDescription });
}
return(Unit.Value);
}
public async Task <IEnumerable <Role> > Handle(GetRolesForLibrary request, CancellationToken cancellationToken)
{
const string sql = @"
SELECT rol.id, rol.title, rpv.privilege_alias
FROM plum.roles rol
LEFT JOIN plum.role_privileges rpv
ON rpv.role_id = rol.id
WHERE
library_id = @LibraryId
AND rol.deleted_at IS NULL
ORDER BY rol.created_at";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
var dtos = await cnn.QueryAsync <RoleDto>(sql, request);
return(_mapper.Map <IEnumerable <Role> >(dtos));
}
}
public async Task <Unit> Handle(CreateInvitation request, CancellationToken cancellationToken)
{
const string sql = @"
INSERT INTO plum.invitations (url_key, role_id, membership_id, expires_at)
VALUES (@UrlKey, @RoleId, @MembershipId, @ExpiresAt)";
using (var cnn = _sqlConnectionFactory.GetSqlConnection())
{
await cnn.ExecuteAsync(sql, new
{
UrlKey = GenerateUrlKey(),
request.RoleId,
request.MembershipId,
request.ExpiresAt
});
}
return(Unit.Value);
}
