diff options
Diffstat (limited to 'back-end/src/Model')
| -rw-r--r-- | back-end/src/Model/BookmarkEntry.cs | 44 | ||||
| -rw-r--r-- | back-end/src/Model/BookmarkStore.cs | 88 | ||||
| -rw-r--r-- | back-end/src/Model/SimpleBookmarkContext.cs | 7 | ||||
| -rw-r--r-- | back-end/src/Model/UserSettingsDbStore.cs | 6 |
4 files changed, 103 insertions, 42 deletions
diff --git a/back-end/src/Model/BookmarkEntry.cs b/back-end/src/Model/BookmarkEntry.cs index 0ce7644..fc981ec 100644 --- a/back-end/src/Model/BookmarkEntry.cs +++ b/back-end/src/Model/BookmarkEntry.cs @@ -32,6 +32,7 @@ namespace SimpleBookmark.Model internal sealed partial class BookmarkEntry : DbModelBase, IUserEntity, IJsonOnDeserialized { [Key] + [MaxLength(64)] public override string Id { get; set; } public override DateTime Created { get; set; } @@ -39,12 +40,13 @@ namespace SimpleBookmark.Model public override DateTime LastModified { get; set; } [JsonIgnore] + [MaxLength(64)] public string? UserId { get; set; } - [MaxLength(100)] + [MaxLength(200)] public string? Name { get; set; } - [MaxLength(200)] + [MaxLength(300)] public string? Url { get; set; } [MaxLength(500)] @@ -53,7 +55,7 @@ namespace SimpleBookmark.Model //Json flavor [NotMapped] [JsonPropertyName("Tags")] - public string[]? JsonTags + public string?[]? JsonTags { get => Tags?.Split(','); set => Tags = value is null ? null : string.Join(',', value); @@ -61,7 +63,7 @@ namespace SimpleBookmark.Model //Database flavor as string [JsonIgnore] - [MaxLength(100)] + [MaxLength(100)] public string? Tags { get; set; } public static IValidator<BookmarkEntry> GetValidator() @@ -71,21 +73,29 @@ namespace SimpleBookmark.Model validator.RuleFor(p => p.Name) .NotEmpty() .Matches(@"^[a-zA-Z0-9_\-\|\., ]+$", RegexOptions.Compiled) - .MaximumLength(100); + .MaximumLength(200); validator.RuleFor(p => p.Url) .NotEmpty() .Matches(@"^https?://", RegexOptions.Compiled) - .MaximumLength(200); + .MaximumLength(300); + //Description should be valid utf-8 and not exceed 500 characters validator.RuleFor(p => p.Description) + .Matches(@"^[\u0000-\u007F]+$", RegexOptions.Compiled).When(p => !string.IsNullOrEmpty(p.Description)) + .WithMessage("Description contains illegal unicode characters") .MaximumLength(500); - //Tags must be non-empty and alphanumeric only, no spaces + //Tags must be non-empty and alphanumeric only, no spaces, only if tags are not null validator.RuleForEach(p => p.JsonTags) - .NotNull() - .NotEmpty() - .Matches(@"^[a-zA-Z0-9]+$", RegexOptions.Compiled); + .Matches(@"^[a-zA-Z0-9\-]+$", RegexOptions.Compiled).When(v => v.JsonTags is not null && v.JsonTags.Length > 0, ApplyConditionTo.CurrentValidator) + .WithMessage("Tags for this bookmark contain invalid characters -> {PropertyValue}") + .Length(1, 64).When(v => v.JsonTags is not null && v.JsonTags.Length > 0, ApplyConditionTo.CurrentValidator) + .WithMessage("One or more tags for this bookmark are too long"); + + validator.RuleFor(p => p.Tags) + .MaximumLength(100) + .WithMessage("You have too many tags or tag names are too long"); return validator; } @@ -96,6 +106,20 @@ namespace SimpleBookmark.Model Name = Name?.Trim(); Url = Url?.Trim(); Description = Description?.Trim(); + + //Trim tags array + if(JsonTags != null) + { + for (int i = 0; i < JsonTags.Length; i++) + { + JsonTags[i] = JsonTags[i].Trim(); + } + } + + if(string.IsNullOrWhiteSpace(Tags)) + { + Tags = null; + } } } } diff --git a/back-end/src/Model/BookmarkStore.cs b/back-end/src/Model/BookmarkStore.cs index 8578976..ec020e8 100644 --- a/back-end/src/Model/BookmarkStore.cs +++ b/back-end/src/Model/BookmarkStore.cs @@ -22,15 +22,18 @@ using System.Threading.Tasks; using System.Collections.Generic; using VNLib.Utils; +using VNLib.Plugins; using VNLib.Plugins.Extensions.Data; using VNLib.Plugins.Extensions.Loading; using VNLib.Plugins.Extensions.Data.Abstractions; - +using VNLib.Plugins.Extensions.Loading.Sql; namespace SimpleBookmark.Model { - internal sealed class BookmarkStore(IAsyncLazy<DbContextOptions> dbOptions) : DbStore<BookmarkEntry> + internal sealed class BookmarkStore(PluginBase plugin) : DbStore<BookmarkEntry> { + private readonly IAsyncLazy<DbContextOptions> dbOptions = plugin.GetContextOptionsAsync(); + ///<inheritdoc/> public override IDbQueryLookup<BookmarkEntry> QueryTable { get; } = new BookmarkQueryLookup(); @@ -52,6 +55,8 @@ namespace SimpleBookmark.Model public async Task<BookmarkEntry[]> SearchBookmarksAsync(string userId, string? query, string[] tags, int limit, int page, CancellationToken cancellation) { + BookmarkEntry[] results; + ArgumentNullException.ThrowIfNull(userId); ArgumentNullException.ThrowIfNull(tags); ArgumentOutOfRangeException.ThrowIfLessThanOrEqual(limit, 0); @@ -59,31 +64,56 @@ namespace SimpleBookmark.Model //Init new db connection await using SimpleBookmarkContext context = new(dbOptions.Value); - await context.OpenTransactionAsync(cancellation); - //Start with userid + //Build the query starting with the user's bookmarks IQueryable<BookmarkEntry> q = context.Bookmarks.Where(b => b.UserId == userId); + //reduce result set by search query first + if (!string.IsNullOrWhiteSpace(query)) + { + q = WithSearch(q, query); + } + + q = q.OrderByDescending(static b => b.Created); + + /* + * For some databases server-side tag filtering is not supported. + * Client side evaluation must be used to finally filter the results. + * + * I am attempting to reduce the result set as much as possible on server-side + * evaluation before pulling the results into memory. So search, ordering and + * first tag filtering is done on server-side. The final tag filtering is done + * for multiple tags on client-side along with pagination. For bookmarks, I expect + * the result set to at worst double digits for most users, so this should be fine. + * + */ + if (tags.Length > 0) { - //if tags are set, only return bookmarks that match the tags - q = q.Where(b => b.Tags != null && tags.All(t => b.Tags!.Contains(t))); + + //filter out bookmarks that do not have any tags and reduce by the first tag + q = q.Where(static b => b.Tags != null && b.Tags.Length > 0) + .Where(b => EF.Functions.Like(b.Tags, $"%{tags[0]}%")); } - if (!string.IsNullOrWhiteSpace(query)) + if(tags.Length > 1) { - //if query is set, only return bookmarks that match the query - q = q.Where(b => EF.Functions.Like(b.Name, $"%{query}%") || EF.Functions.Like(b.Description, $"%{query}%")); + //Finally pull all results into memory + BookmarkEntry[] bookmarkEntries = await q.ToArrayAsync(cancellation); + + //filter out bookmarks that do not have all requested tags, then skip and take the requested page + results = bookmarkEntries.Where(b => tags.All(p => b.JsonTags!.Contains(p))) + .Skip(page * limit) + .Take(limit) + .ToArray(); } - - //return bookmarks in descending order of creation - q = q.OrderByDescending(static b => b.Created); - - //return only the requested page - q = q.Skip(page * limit).Take(limit); - - //execute query - BookmarkEntry[] results = await q.ToArrayAsync(cancellation); + else + { + //execute server-side query + results = await q.Skip(page * limit) + .Take(limit) + .ToArrayAsync(cancellation); + } //Close db and commit transaction await context.SaveAndCloseAsync(true, cancellation); @@ -91,14 +121,19 @@ namespace SimpleBookmark.Model return results; } + private static IQueryable<BookmarkEntry> WithSearch(IQueryable<BookmarkEntry> q, string query) + { + //if query is set, only return bookmarks that match the query + return q.Where(b => EF.Functions.Like(b.Name, $"%{query}%") || EF.Functions.Like(b.Description, $"%{query}%")); + } + public async Task<string[]> GetAllTagsForUserAsync(string userId, CancellationToken cancellation) { ArgumentNullException.ThrowIfNull(userId); //Init new db connection await using SimpleBookmarkContext context = new(dbOptions.Value); - await context.OpenTransactionAsync(cancellation); - + //Get all tags for the user string[] tags = await context.Bookmarks .Where(b => b.UserId == userId) @@ -116,11 +151,19 @@ namespace SimpleBookmark.Model .ToArray(); } + public async Task<ERRNO> DeleteAllForUserAsync(string userId, CancellationToken cancellation) + { + await using SimpleBookmarkContext context = new(dbOptions.Value); + + context.Bookmarks.RemoveRange(context.Bookmarks.Where(b => b.UserId == userId)); + + return await context.SaveAndCloseAsync(true, cancellation); + } + public async Task<ERRNO> AddBulkAsync(IEnumerable<BookmarkEntry> bookmarks, string userId, DateTimeOffset now, CancellationToken cancellation) { //Init new db connection await using SimpleBookmarkContext context = new(dbOptions.Value); - await context.OpenTransactionAsync(cancellation); //Setup clean bookmark instances bookmarks = bookmarks.Select(b => new BookmarkEntry @@ -162,8 +205,7 @@ namespace SimpleBookmark.Model public IQueryable<BookmarkEntry> GetSingleQueryBuilder(IDbContextHandle context, params string[] constraints) { string bookmarkId = constraints[0]; - string userId = constraints[1]; - + string userId = constraints[1]; return from b in context.Set<BookmarkEntry>() where b.UserId == userId && b.Id == bookmarkId diff --git a/back-end/src/Model/SimpleBookmarkContext.cs b/back-end/src/Model/SimpleBookmarkContext.cs index 2470695..25343d9 100644 --- a/back-end/src/Model/SimpleBookmarkContext.cs +++ b/back-end/src/Model/SimpleBookmarkContext.cs @@ -22,12 +22,12 @@ using VNLib.Plugins.Extensions.Loading.Sql; namespace SimpleBookmark.Model { - internal sealed class SimpleBookmarkContext : TransactionalDbContext, IDbTableDefinition + internal sealed class SimpleBookmarkContext : DBContextBase, IDbTableDefinition { public DbSet<BookmarkEntry> Bookmarks { get; set; } - public DbSet<UserSettingsEntry> BmSettings { get; set; } + public DbSet<UserSettingsEntry> SbSettings { get; set; } public SimpleBookmarkContext(DbContextOptions options) : base(options) { } @@ -56,7 +56,6 @@ namespace SimpleBookmark.Model .WithColumn(p => p.Name) .AllowNull(true) - .MaxLength(100) .Next() .WithColumn(p => p.Version) @@ -70,12 +69,10 @@ namespace SimpleBookmark.Model .WithColumn(p => p.Description) .AllowNull(true) - .MaxLength(500) .Next() .WithColumn(p => p.Tags) .AllowNull(true) - .MaxLength(100) .Next(); } diff --git a/back-end/src/Model/UserSettingsDbStore.cs b/back-end/src/Model/UserSettingsDbStore.cs index aa44fa2..d392262 100644 --- a/back-end/src/Model/UserSettingsDbStore.cs +++ b/back-end/src/Model/UserSettingsDbStore.cs @@ -33,9 +33,8 @@ namespace SimpleBookmark.Model //Init new db connection await using SimpleBookmarkContext context = new(dbOptions.Value); - await context.OpenTransactionAsync(cancellation); - UserSettingsEntry? settings = await context.BmSettings.FirstOrDefaultAsync(p => p.UserId == userId, cancellation); + UserSettingsEntry? settings = await context.SbSettings.FirstOrDefaultAsync(p => p.UserId == userId, cancellation); //Close db and commit transaction await context.SaveAndCloseAsync(true, cancellation); @@ -50,10 +49,9 @@ namespace SimpleBookmark.Model //Init new db connection await using SimpleBookmarkContext context = new(dbOptions.Value); - await context.OpenTransactionAsync(cancellation); //Search for existing settings entry - UserSettingsEntry? existing = await context.BmSettings.FirstOrDefaultAsync(p => p.UserId == userId, cancellation); + UserSettingsEntry? existing = await context.SbSettings.FirstOrDefaultAsync(p => p.UserId == userId, cancellation); if (existing is null) { |