From 709d58aa8bed5346c6d520f8b7ab4cfd780f4679 Mon Sep 17 00:00:00 2001 From: Serghei Cebotari Date: Thu, 18 Jan 2024 22:32:40 +0300 Subject: Add index to DB --- RhSolutions.Api/Controllers/SearchController.cs | 4 +- .../Migrations/20230511043408_Init.Designer.cs | 69 -------------------- RhSolutions.Api/Migrations/20230511043408_Init.cs | 41 ------------ .../Migrations/20240117210635_Init.Designer.cs | 74 ++++++++++++++++++++++ RhSolutions.Api/Migrations/20240117210635_Init.cs | 48 ++++++++++++++ .../Migrations/RhSolutionsContextModelSnapshot.cs | 9 ++- RhSolutions.Api/Models/RhsolutionsContext.cs | 6 +- 7 files changed, 136 insertions(+), 115 deletions(-) delete mode 100644 RhSolutions.Api/Migrations/20230511043408_Init.Designer.cs delete mode 100644 RhSolutions.Api/Migrations/20230511043408_Init.cs create mode 100644 RhSolutions.Api/Migrations/20240117210635_Init.Designer.cs create mode 100644 RhSolutions.Api/Migrations/20240117210635_Init.cs diff --git a/RhSolutions.Api/Controllers/SearchController.cs b/RhSolutions.Api/Controllers/SearchController.cs index f0402ec..162e961 100644 --- a/RhSolutions.Api/Controllers/SearchController.cs +++ b/RhSolutions.Api/Controllers/SearchController.cs @@ -23,9 +23,9 @@ namespace RhSolutions.Api.Controllers public IAsyncEnumerable SearchProducts([FromQuery] string query) { return context.Products - .Where(p => EF.Functions.ToTsVector( - "russian", string.Join(' ', new[] { p.Name, string.Join(' ', p.ProductLines)})) + .Where(p => EF.Functions.ToTsVector("russian", p.Name) .Matches(EF.Functions.WebSearchToTsQuery("russian", query))) + .Where(p => p.ProductLines.Contains("RAUTITAN")) .OrderByDescending(p => p.IsOnWarehouse) .AsAsyncEnumerable(); } diff --git a/RhSolutions.Api/Migrations/20230511043408_Init.Designer.cs b/RhSolutions.Api/Migrations/20230511043408_Init.Designer.cs deleted file mode 100644 index 887bd5a..0000000 --- a/RhSolutions.Api/Migrations/20230511043408_Init.Designer.cs +++ /dev/null @@ -1,69 +0,0 @@ -// -using System; -using System.Collections.Generic; -using Microsoft.EntityFrameworkCore; -using Microsoft.EntityFrameworkCore.Infrastructure; -using Microsoft.EntityFrameworkCore.Migrations; -using Microsoft.EntityFrameworkCore.Storage.ValueConversion; -using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; -using RhSolutions.Models; - -#nullable disable - -namespace RhSolutions.Api.Migrations -{ - [DbContext(typeof(RhSolutionsContext))] - [Migration("20230511043408_Init")] - partial class Init - { - /// - protected override void BuildTargetModel(ModelBuilder modelBuilder) - { -#pragma warning disable 612, 618 - modelBuilder - .HasAnnotation("ProductVersion", "7.0.5") - .HasAnnotation("Relational:MaxIdentifierLength", 63); - - NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); - - modelBuilder.Entity("RhSolutions.Models.Product", b => - { - b.Property("Id") - .HasColumnType("text"); - - b.Property("DeliveryMakeUp") - .HasColumnType("double precision"); - - b.Property>("DeprecatedSkus") - .IsRequired() - .HasColumnType("text[]"); - - b.Property("IsOnWarehouse") - .HasColumnType("boolean"); - - b.Property("Name") - .IsRequired() - .HasColumnType("text"); - - b.Property("Price") - .HasColumnType("numeric"); - - b.Property>("ProductLines") - .IsRequired() - .HasColumnType("text[]"); - - b.Property("ProductMeasure") - .HasColumnType("integer"); - - b.Property("ProductSku") - .IsRequired() - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("Products"); - }); -#pragma warning restore 612, 618 - } - } -} diff --git a/RhSolutions.Api/Migrations/20230511043408_Init.cs b/RhSolutions.Api/Migrations/20230511043408_Init.cs deleted file mode 100644 index 7f9427f..0000000 --- a/RhSolutions.Api/Migrations/20230511043408_Init.cs +++ /dev/null @@ -1,41 +0,0 @@ -using System.Collections.Generic; -using Microsoft.EntityFrameworkCore.Migrations; - -#nullable disable - -namespace RhSolutions.Api.Migrations -{ - /// - public partial class Init : Migration - { - /// - protected override void Up(MigrationBuilder migrationBuilder) - { - migrationBuilder.CreateTable( - name: "Products", - columns: table => new - { - Id = table.Column(type: "text", nullable: false), - Name = table.Column(type: "text", nullable: false), - ProductSku = table.Column(type: "text", nullable: false), - DeprecatedSkus = table.Column>(type: "text[]", nullable: false), - ProductLines = table.Column>(type: "text[]", nullable: false), - IsOnWarehouse = table.Column(type: "boolean", nullable: false), - ProductMeasure = table.Column(type: "integer", nullable: false), - DeliveryMakeUp = table.Column(type: "double precision", nullable: true), - Price = table.Column(type: "numeric", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_Products", x => x.Id); - }); - } - - /// - protected override void Down(MigrationBuilder migrationBuilder) - { - migrationBuilder.DropTable( - name: "Products"); - } - } -} diff --git a/RhSolutions.Api/Migrations/20240117210635_Init.Designer.cs b/RhSolutions.Api/Migrations/20240117210635_Init.Designer.cs new file mode 100644 index 0000000..8f61c8e --- /dev/null +++ b/RhSolutions.Api/Migrations/20240117210635_Init.Designer.cs @@ -0,0 +1,74 @@ +// +using System; +using System.Collections.Generic; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; +using RhSolutions.Models; + +#nullable disable + +namespace RhSolutions.Api.Migrations +{ + [DbContext(typeof(RhSolutionsContext))] + [Migration("20240117210635_Init")] + partial class Init + { + /// + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "8.0.0") + .HasAnnotation("Relational:MaxIdentifierLength", 63); + + NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); + + modelBuilder.Entity("RhSolutions.Models.Product", b => + { + b.Property("Id") + .HasColumnType("text"); + + b.Property("DeliveryMakeUp") + .HasColumnType("double precision"); + + b.Property("DeprecatedSkus") + .IsRequired() + .HasColumnType("text[]"); + + b.Property("IsOnWarehouse") + .HasColumnType("boolean"); + + b.Property("Name") + .IsRequired() + .HasColumnType("text"); + + b.Property("Price") + .HasColumnType("numeric"); + + b.Property>("ProductLines") + .IsRequired() + .HasColumnType("text[]"); + + b.Property("ProductMeasure") + .HasColumnType("integer"); + + b.Property("ProductSku") + .IsRequired() + .HasColumnType("text"); + + b.HasKey("Id"); + + b.HasIndex("Name") + .HasAnnotation("Npgsql:TsVectorConfig", "russian"); + + NpgsqlIndexBuilderExtensions.HasMethod(b.HasIndex("Name"), "GIN"); + + b.ToTable("Products"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/RhSolutions.Api/Migrations/20240117210635_Init.cs b/RhSolutions.Api/Migrations/20240117210635_Init.cs new file mode 100644 index 0000000..ca7ba91 --- /dev/null +++ b/RhSolutions.Api/Migrations/20240117210635_Init.cs @@ -0,0 +1,48 @@ +using System.Collections.Generic; +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace RhSolutions.Api.Migrations +{ + /// + public partial class Init : Migration + { + /// + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.CreateTable( + name: "Products", + columns: table => new + { + Id = table.Column(type: "text", nullable: false), + Name = table.Column(type: "text", nullable: false), + ProductSku = table.Column(type: "text", nullable: false), + DeprecatedSkus = table.Column(type: "text[]", nullable: false), + ProductLines = table.Column>(type: "text[]", nullable: false), + IsOnWarehouse = table.Column(type: "boolean", nullable: false), + ProductMeasure = table.Column(type: "integer", nullable: false), + DeliveryMakeUp = table.Column(type: "double precision", nullable: true), + Price = table.Column(type: "numeric", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_Products", x => x.Id); + }); + + migrationBuilder.CreateIndex( + name: "IX_Products_Name", + table: "Products", + column: "Name") + .Annotation("Npgsql:IndexMethod", "GIN") + .Annotation("Npgsql:TsVectorConfig", "russian"); + } + + /// + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropTable( + name: "Products"); + } + } +} diff --git a/RhSolutions.Api/Migrations/RhSolutionsContextModelSnapshot.cs b/RhSolutions.Api/Migrations/RhSolutionsContextModelSnapshot.cs index 0052799..de714c1 100644 --- a/RhSolutions.Api/Migrations/RhSolutionsContextModelSnapshot.cs +++ b/RhSolutions.Api/Migrations/RhSolutionsContextModelSnapshot.cs @@ -18,7 +18,7 @@ namespace RhSolutions.Api.Migrations { #pragma warning disable 612, 618 modelBuilder - .HasAnnotation("ProductVersion", "7.0.5") + .HasAnnotation("ProductVersion", "8.0.0") .HasAnnotation("Relational:MaxIdentifierLength", 63); NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); @@ -31,7 +31,7 @@ namespace RhSolutions.Api.Migrations b.Property("DeliveryMakeUp") .HasColumnType("double precision"); - b.Property>("DeprecatedSkus") + b.Property("DeprecatedSkus") .IsRequired() .HasColumnType("text[]"); @@ -58,6 +58,11 @@ namespace RhSolutions.Api.Migrations b.HasKey("Id"); + b.HasIndex("Name") + .HasAnnotation("Npgsql:TsVectorConfig", "russian"); + + NpgsqlIndexBuilderExtensions.HasMethod(b.HasIndex("Name"), "GIN"); + b.ToTable("Products"); }); #pragma warning restore 612, 618 diff --git a/RhSolutions.Api/Models/RhsolutionsContext.cs b/RhSolutions.Api/Models/RhsolutionsContext.cs index be0fcb5..e21818a 100644 --- a/RhSolutions.Api/Models/RhsolutionsContext.cs +++ b/RhSolutions.Api/Models/RhsolutionsContext.cs @@ -18,5 +18,9 @@ public class RhSolutionsContext : DbContext .PrimitiveCollection(e => e.DeprecatedSkus) .ElementType() .HasConversion(typeof(SkuConverter)); - } + builder.Entity() + .HasIndex(b => new { b.Name }) + .HasMethod("GIN") + .IsTsVectorExpressionIndex("russian"); + } } -- cgit v1.2.3