Saturday, January 30, 2021

MS SQL Server FTS iFilter to search inside PDF, docx, pptx, and xlsx


To View the current supported document types that is available for full-text indexing operations. 

SELECT *
FROM sys.fulltext_document_types


To Support .docx, .pptx, .xlsx 

download and install the next file

https://www.microsoft.com/en-us/download/details.aspx?id=17062


To Support PDF Files

download and install Adobe PDF iFilter 11

http://ardownload.adobe.com/pub/adobe/acrobat/win/11.x/PDFFilter64Setup.msi


Then run the next command to load pdf extension

EXEC sp_fulltext_service 'load_os_resources', 1 
GO
EXEC sp_fulltext_service 'verify_signature', 0
GO


after windows reboot, the new iFilter will be ready and index the documents inside FileTable







Sunday, January 24, 2021

Connect .Net Core 5.0 MVC to Solr 8 using SolrExpress

 Download the next packages using package manager

 Install-Package SolrExpress -Version 5.5.0  
 Install-Package SolrExpress.Solr5 -Version 5.5.0  
 Install-Package SolrExpress.DI.CoreClr -Version 5.5.0 


In Startup.cs


 public void ConfigureServices(IServiceCollection services)  
     {  
 .....  
       services.AddSolrExpress<SolrModel>(builder => builder  
           .UseOptions(  
                 q => {   
                     q.HasHostAddress("http://IP:8983/solr/ZING/");  
                    }  
                 )  
           .UseSolr5());   
 .....  
     }  


Define Model

   public class SolrModel : Document  
   {  
     [SolrField("biblo_id")]  
     public int biblo_id { get; set; }  
     [SolrField("title")]  
     public string title { get; set; }  
     [SolrField("author")]  
     public string author { get; set; }  
   }  


On Controller Page


 using Microsoft.Extensions.DependencyInjection;  
 using Newtonsoft.Json;  
 using SolrExpress;  
 using SolrExpress.Configuration;  
 using SolrExpress.DI.CoreClr;  
 using SolrExpress.Options;  
 using SolrExpress.Search.Extension;  
 using SolrExpress.Search.Parameter.Extension;  
 using SolrExpress.Search.Result.Extension;  
 using SolrExpress.Solr5.Extension;  
 using SolrExpress.Solr5.Update;  
 namespace ArabAuthorsIndex.Controllers  
 {  
   public class SolrController : Controller  
   {  
     private DocumentCollection<SolrModel> _solrModel;  
     public SolrController(DocumentCollection<SolrModel> solrModel)  
     {  
       _solrModel = solrModel;  
     }  
     public string search()  
     {  
       _solrModel  
           .Select()  
           //.Fields(d => d.biblo_id, d => d.author)  
           //.FacetField(d => d.author)  
           .Filter(d => d.biblo_id, 1)  
           .Limit(10) //.Limit(itemsPerPage)  
           //.Offset(2) //.Offset(page)  
           .Execute()  
           //.Information(out var information)  
           .Document(out var documents)  
           //.Facets(out var facets)  
           ;  
       return JsonConvert.SerializeObject(documents, Formatting.Indented);  
     }  
     public string update()  
     {  
       var document1 = new SolrModel  
       {  
         author = "Mohamed",  
         title = "ymmud"  
       };  
       var document2 = new SolrModel  
       {  
         author = "Rafie",  
         title = "ymmud2"  
       };  
       _solrModel.Update().Add(document1, document2).Execute();  
       _solrModel.Update().Delete("2").Execute();  //delete document by solr id
       return search();  
     }  




Connect .Net Core 5.0 Console App to Solr 8 using SolrExpress

 For Console or any One Page Web APP

Task
Connect to Solr http://IP:8983/solr/CoreName/ and get fields (title, author) where title=laser,
PageSize=10 and get the results from page 2
Use author as Facet field 

Use Package manager to get the next Packages

 Install-Package SolrExpress -Version 5.5.0  
 Install-Package SolrExpress.Solr5 -Version 5.5.0  
 Install-Package SolrExpress.DI.CoreClr -Version 5.5.0 



Code:

 using Microsoft.Extensions.DependencyInjection;  
 using Newtonsoft.Json;  
 using SolrExpress;  
 using SolrExpress.Configuration;  
 using SolrExpress.DI.CoreClr;  
 using SolrExpress.Options;  
 using SolrExpress.Search.Extension;  
 using SolrExpress.Search.Parameter.Extension;  
 using SolrExpress.Search.Result.Extension;  
 using SolrExpress.Solr5.Extension;  
 using SolrExpress.Solr5.Update;  


Model

   public class SolrModel : Document  
   {  
     [SolrField("title")]  
     public string title { get; set; }  
     [SolrField("author")]  
     public string author { get; set; }  
   }  



       var services = new ServiceCollection()  
           .AddSolrExpress<SolrModel>(builder => builder  
           .UseOptions(q => q.HasHostAddress("http://IP:8983/solr/CoreName/"))  
           .UseSolr5());  
       var serviceProvider = services.BuildServiceProvider();  
       DocumentCollection<SolrModel> _solrModel = serviceProvider.GetRequiredService<DocumentCollection<SolrModel>>();  
       _solrModel  
         .Select()  
         .Fields(d => d.title, d => d.author)  
         .FacetField(d => d.author)  
         .Filter(d => d.title, "laser")  
         .Limit(10) //.Limit(itemsPerPage)  
         .Offset(2) //.Offset(page)  
         .Execute()  
         .Information(out var information)  
         .Document(out var documents)  
         .Facets(out var facets);  
       Console.WriteLine(JsonConvert.SerializeObject(information, Formatting.Indented));
       Console.WriteLine(JsonConvert.SerializeObject(documents, Formatting.Indented));
Console.WriteLine(JsonConvert.SerializeObject(facets, Formatting.Indented));


Sample  Result

         ** For Informations  
         {  
            "PageSize": 10,  
            "PageNumber": 1,  
            "PageCount": 1,  
            "HasPreviousPage": false,  
            "HasNextPage": false,  
            "IsFirstPage": true,  
            "IsLastPage": true,  
            "DocumentCount": 1,  
            "ElapsedTime": "00:00:00.0080000",  
            "NextCursorMark": null  
           }  
         
       ** For documents  
       [  
        {  
         "title": "laser",  
         "author": "Bashir, Lubna Z."
        }  
       ]  
       
       ** For facets  
       [  
         {  
         "Name": "title",  
         "FacetType": 0,  
         "Tag": null,  
         "Values": [  
           {  
           "Key": "University admission system using machine learning /  ",  
           "Quantity": 1,  
           "Facets": null  
           }  
         ]  
         }  
       ]  
        


Here are list Filters that can use for query 


Use case

How to

Solr Query generated

Query to find all informed values (conditional AND)

query.Field(f => f.Categories).All("category1", "category2")

cat:("category1" AND "category2")

Query to find some of informed values (conditional OR)

query.Field(f => f.Categories).Any("category1", "category2")

cat:("category1" OR "category2")

Query to find something starts with informed value

query.Field(f => f.Categories).StartsWith("c")

cat:"c*"

Query to find exact informed value

query.Field(f => f.Categories).EqualsTo("category1")

cat:"category1"

Query to find negate informed value

query.Field(f => f.Categories).NotEqualsTo("category1")

NOT(cat:"category1")

Query to find someting in informed range

query.Field(f => f.Price).InRange(1, 10)

price:[1 TO 10]

Query to find someting greater than informed value

query.Field(f => f.Price).GreaterThan(1)

price:[1 TO *]

Query to find someting less than informed value                

query.Field(f => f.Price).LessThan(1)    

price:[* TO 10] 

Query expression isolating in a groupquery.Group(price=> price.Filed(f => f.Price).InRange(1, 10).Or(popularity => popularity.Field(f => f.Popularity).GreaterThan(5)))(price:[1 TO 10] OR  popularity:[5 TO *])





Friday, January 22, 2021

Uploading files with ASP.NET Core

 To upload file you can use Model type  IFormFile, which is found in the Microsoft.AspNet.Http namespace.

On View 

<form method="post" asp-action="Index" asp-controller="Home" enctype="multipart/form-data">
    <input type="file" name="files" multiple/>
    <input type="submit" value="Upload" />
</form>

On Controller

public class HomeController : Controller
{
    private IHostingEnvironment _environment;

    public HomeController(IHostingEnvironment environment)
    {
        _environment = environment;
    }
    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public async Task<IActionResult> Index(ICollection<IFormFile> files)
    {
        var uploads = Path.Combine(_environment.WebRootPath, "uploads");
        foreach (var file in files)
        {
            if (file.Length > 0)
            {
                using (var fileStream = new FileStream(Path.Combine(uploads, file.FileName), FileMode.Create))
                {
                    await file.CopyToAsync(fileStream);
                }
            }
        }
        return View();
    }
}

Notes that we receive files using ICollection<IFormFile>
IFormFile objects share some properties, methods like 
Length
- FileName
- Name : returns the value of the name attribute on the upload control.
- CopyToAsync method to save file to HD


To Upload multi-Files with other form info, use Model like this 
public class FileDescriptionShort
{
        public int Id { get; set; }
        public string Description { get; set; }
        public string Name { get; set; }
        public ICollection<IFormFile> File { get; set; }
}

public async Task<IActionResult> Index(FileDescriptionShort fileDescriptionShort)
{ }


To Upload One File use define it in Model like this
        [Required]
        [FileExtensions(Extensions = "jpg,jpeg")]
        public IFormFile File { get; set; }
For Multi-Files use 
        [Required]
        public ICollection<IFormFile> File { get; set; }

To download file from FileTable based on file ID

        PM> Install-Package Microsoft.AspNetCore.StaticFiles

        public string Get_contentType(string fileName)
        {
            var provider = new FileExtensionContentTypeProvider();
            string contentType;
            if (!provider.TryGetContentType(fileName, out contentType))
            {
                contentType = "application/octet-stream";
            }
            return contentType;
        }

        [HttpGet]
        public FileStreamResult Download(int id)
        {
            var path = "c:\\path\\a.pdf";
            var stream = new FileStream(path, FileMode.Open);
            return  File(stream, Get_contentType(path) );
        }

How to upload file to amazon s3 through web api

        [HttpPost]
        public async Task<string> PostAsync([FromBody] string FileName, IFormFile File2Upload)
        {
            if (File2Upload != null)
            {
                using (var client = new AmazonS3Client(AWSkey, AwsSecret, RegionEndpoint.USEast2))
                {
                    using (var newMemoryStream = new MemoryStream())
                    {
                        File2Upload.CopyTo(newMemoryStream);

                        var uploadRequest = new TransferUtilityUploadRequest
                        {
                            InputStream = newMemoryStream,
                            Key = FileName, //fileUpload.ProfileImage.FileName.ToString(),
                            BucketName = S3folderName,
                            CannedACL = S3CannedACL.PublicRead
                        };

                        var fileTransferUtility = new TransferUtility(client);
                        await fileTransferUtility.UploadAsync(uploadRequest);
                    }
                }
                return "https://S3folderName.s3.us-east-2.amazonaws.com/" + FileName;
} return ""; }

SQL Server FILESTREAM AND FILETABLE

If the stored objects are on average larger than 1 MB, FileStream is the best option because of the faster read access. For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.


FILESTREAM
: save large files to DB, 
FileStream data type is implemented as a varbinary (max) column.

  

FILETABLE: (sub type of filestream), support direct access to file from file system and support full-text search


SETTING UP FILESTREAM AND FILETABLE IN SQL SERVER

1) For Sql Server 2019: windows > run > SQLServerManager15.msc




2) Enable FILESTREAM access level with Transact-SQL code

--0 = Disables FILESTREAM support for this instance.
--1 = Enables FILESTREAM for Transact-SQL access.
--2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.

USE Master
GO

EXEC sp_configure filestream_access_level ,2
RECONFIGURE

3) Create folder "FILESERVER" on c:\

4)  Create DB with name "FILESDB"

CREATE DATABASE FILESDB
ON PRIMARY (NAME = FS,FILENAME = 'C:\FILESERVER\FILESDB.mdf'),
FILEGROUP FileStreamFS CONTAINS FILESTREAM(NAME = FStream, FILENAME = 'c:\FILESERVER\Fs')
LOG ON  (NAME = FILESDBLog, FILENAME = 'C:\FILESERVER\FILESDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FILESERVER')
GO


5) Create table with name "FileTable"

CREATE TABLE Documents AS FileTable
GO



6) View Table on HD by right click on FileTable and choose "Explore FileTable Directory"