Sql Field Indexing - Limit fields and content types #8869
Comments
|
That's a feature that would need to be added. The other option is to create a custom IndexingProvider if you want some fine tuned as of now. |
|
That's something that was designed, by defining something similar to a yessql index provide, in javascript, such that we could create dynamic projections of a content item, and store it in lucene or a sql table. This was postponed because it's not simple to implement. |
|
I was thinking of using the sql field indexing as is and only add an extension point to check if the field should be indexed based on a setting in the field definition screen (like lucene does). I'll give it a shot and see what comes up. |
|
I agree with this. |
|
/cc @jptissot |
|
@gianik Yes, exactly what I was thinking of. Allow/Disallow to index a field or not by adding configuration when the SQL Indexing feature is enabled. Also, at the same time, add a way to re-index everything because right now it will only index new content items ; not existing ones. So, we need a way to repopulate these index tables. |
|
rebuild index button? |
|
This should use the same logic as Lucene, keep a cursor with the list of indexing tasks, this time in the database. I don't think it should follow the Lucene UI exactly, since there is a single index in this module. I don't think it supports multiple indexes, though it could technically be possible at least with an extra column in the tables (not the ideal for perf but will work). But the ultimate solution is really to have something driven by javascript to create dynamic map/reduce projections. Like yessql does, but dynamically. |
|
lets assume u do the map reduce,, wouldn't it still be nice to lessen the disk space used on the index by removing unwanted fields? |
|
I agree, with @sebastienros There are multiple index tables in this feature like @sebastienros says which differenciate it from Lucene. One per field type actually. I'm not sure how to implement the javascript part of it for now as I've not thinked about it at all. I need to focus on other PR's first. |
|
Here are some ways to dynamically update data using FreeSQL, http://www.freesql.net/en/guide/Update-Data.html#_5-update-according-to-the-dto |
|
I tried a hook that would block updates to the content definition public interface IContentDefinitionHandler
{
Task AfterDeleteTypeDefinition(ContentTypeDefinitionRecord record);
Task AfterStorePartDefinition(ContentPartDefinition contentPartDefinition);
Task AfterStoreTypeDefinition(ContentTypeDefinition contentTypeDefinition);
Task<bool> BeforeDeleteTypeDefinition(ContentTypeDefinitionRecord record);
Task<bool> BeforeStorePartDefinition(ContentPartDefinition contentPartDefinition);
Task<bool> BeforeStoreTypeDefinition(ContentTypeDefinition contentTypeDefinition);
Task<bool> BeforeDeletePartDefinition(ContentPartDefinitionRecord record, IEnumerable<ContentTypeDefinition> typesWithPart);
Task AfterDeletePartDefinition(ContentPartDefinitionRecord record, IEnumerable<ContentTypeDefinition> typesWithPart);
}The trigger in public async void StoreTypeDefinition(ContentTypeDefinition contentTypeDefinition)
{
var results = await Handlers.InvokeAsync((handler) => handler.BeforeStoreTypeDefinition(contentTypeDefinition), _logger);
if (results.Any(x => !x))
{
return;
}
Apply(contentTypeDefinition, Acquire(contentTypeDefinition));
UpdateContentDefinitionRecord();
await Handlers.InvokeAsync((handler) => handler.AfterStoreTypeDefinition(contentTypeDefinition), _logger);
}
public void StorePartDefinition(ContentPartDefinition contentPartDefinition)
{
var results = Handlers.InvokeAsync((handler) => handler.BeforeStorePartDefinition(contentPartDefinition), _logger).GetAwaiter().GetResult();
if (results.Any(x => !x))
{
return;
}
Apply(contentPartDefinition, Acquire(contentPartDefinition));
UpdateContentDefinitionRecord();
Handlers.InvokeAsync((handler) => handler.AfterStorePartDefinition(contentPartDefinition), _logger).GetAwaiter().GetResult();
} |
|
And then created a Extention Method on ISchemarBuilder public static ISchemaBuilder CreateMapIndexTable(this ISchemaBuilder builder,
ContentTypeDefinition contentType,
Action<ICreateTableCommand> table, string collection) |
|
The idea is to trigger the update table structure hook every time the type /Part is updated |
|
In addition to the above calculation based on the ISchemaBuilder API, Freesql entity synchronization: public async Task<int> CreateAsync()
{
//Use FreeSql
//create or update table , auto create or update index
_freeSql.CodeFirst.SyncStructure<VbenMenuPartIndex>();
SchemaBuilder.CreateForeignKey<VbenMenuPartIndex>();
return await Task.FromResult(1);
}The following code is my test with Natasha ，You can also export the generated types for use in source code Note: Natasha also supports dynamic assembly loading，For example, it can be used to implement dynamic loading of OC modules using Natasha.CSharp;
using System.Reflection;
NatashaInitializer.InitializeAndPreheating();
string text = @"
namespace HelloWorld
{
public class Test{
public Test(){
Name=""111"";
}
public string Name;
public int Age{get;set;}
}
}";
//Create dynamic classes from the script
AssemblyCSharpBuilder oop = new AssemblyCSharpBuilder();
//Even if you add 100 classes, they will all be compiled in one assembly
oop.Add(text);
//The following assembly will have the classes you added to Syntax
Assembly assembly = oop.GetAssembly();
//Or use the secondary API NAssembly
//This class has API functions such as CreateClass/CreateInterface, but the final build is built in the same AssemblyCSharpBuilder
var asm = new NAssembly("MyAssembly");
asm.AddScript(text);
var type = asm.GetTypeFromShortName("Test");
var type1 = asm.GetTypeFromFullName("HelloWorld.Test");
Console.WriteLine(); |
|
It looks like I've been reinventing the wheel,
|
|
Hi @sebastienros I use the following method to create an ISchemaBuilder var schemaBuilder = new SchemaBuilder(_store.Configuration, await YesSession.BeginTransactionAsync(), true);
1,2 worked fine, and the table was created successfully. It occurs when a document is queried from the database var shellConfig = ShellScope.Current.ServiceProvider.GetRequiredService<IShellConfiguration>();
var dbOptions = shellConfig.Get<DatabaseShellsStorageOptions>();
var docs = YesSession.Query<ContentItem, ContentItemIndex>()
.Where(x => x.Latest && x.ContentType == model.TypeName)
.OrderBy(x => x.Id);My code has been submitted to Github: |
|
#11086 related |
|
Does this also handle the mapping from the content item properties to index fields ? |
|
Done for me. I introduced Natasha, the.NET dynamic compiler Then use Freesql to rebuild index data in batches
|
|
The generated class，They are just strings that are used for table generation, and if you copy them and save them into your code, you can use them directly in background code development using EasyOC.Core.Indexs;
using FreeSql.DataAnnotations;
namespace EasyOC.DynamicTypeIndex.IndexModels
{
[EOCIndex("IDX_{tablename}_DocumentId", "DocumentId,ContentItemId")]
[EOCTable(Name = "Customer_DIndex")]
public class CustomerDIndex : FreeSqlDocumentIndex
{
[Column(StringLength = 26)]
public string ContentItemId { get; set; }
[Column(Name = "Name", IsNullable = true, StringLength = -1)]
public System.String Name { get; set; }
[Column(Name = "CustNum", IsNullable = true, StringLength = -1)]
public System.String CustNum { get; set; }
[Column(Name = "MarketSegment", IsNullable = true, StringLength = 26)]
public System.String MarketSegment { get; set; }
[Column(Name = "Source", IsNullable = true, StringLength = 26)]
public System.String Source { get; set; }
[Column(Name = "Industry", IsNullable = true, StringLength = 26)]
public System.String Industry { get; set; }
[Column(Name = "CustClass", IsNullable = true, StringLength = 26)]
public System.String CustClass { get; set; }
[Column(Name = "SalesOwner", IsNullable = true, StringLength = 26)]
public System.String SalesOwner { get; set; }
[Column(Name = "AddressPart_CountryName", IsNullable = true, StringLength = -1)]
public System.String AddressPart_CountryName { get; set; }
[Column(Name = "AddressPart_Province", IsNullable = true, StringLength = -1)]
public System.String AddressPart_Province { get; set; }
[Column(Name = "AddressPart_City", IsNullable = true, StringLength = -1)]
public System.String AddressPart_City { get; set; }
[Column(Name = "AddressPart_PostalCode", IsNullable = true, StringLength = -1)]
public System.String AddressPart_PostalCode { get; set; }
[Column(Name = "AddressPart_Details", IsNullable = true, StringLength = -1)]
public System.String AddressPart_Details { get; set; }
[Column(Name = "AddressPart_Name", IsNullable = true, StringLength = -1)]
public System.String AddressPart_Name { get; set; }
}
}The generated table structure Data after synchronization |
|
Does any one working on this? |
|
Not on my side. |
I would like to start using the sql field indexing to run sql queries filtering on field values.
But Im concerned that you cant fine tune what you want to index (eg what fields per content type).
And everything is indexed by default , raising performance and maintanance issues.
Is there some reason we cannot specify what fields per content type to index, like we do in lucene indexing ?
Maybe Im missing something.
@Skrypt
The text was updated successfully, but these errors were encountered: