// 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); }