Streaming de libros Parte 2 – Indexación con Full Text Search

Full Text Search nos da la capacidad de indexar campos basados en caracteres (aunque su almacenamiento sea en binario) y hacer búsquedas complejas sobre esos textos.

Esta capacidad viene dada por los siguientes elementos:

  • SQL Full-text Filter Daemon Launcher: Se encarga de mantener actualizados los índices Full Text Search de cada tabla, asociado a cada instancia de Sql Server, pero corriendo en un proceso separado. Si no lo necesitamos, podremos poner su estado a Stop.
  • Full-text index : Índice full-text que indexa el contenido de la tabla. Solo puede haber uno por tabla y lenguaje. Por lo que, si nuestra aplicación maneja documentos en distintos idiomas, será necesario separarlos en tablas distintas.
  • iFilters: El motor de indexado debe de reconocer el tipo de fichero que contiene el campo varbinary(max), de ahí que la extensión de ese fichero se almacene en la columnda DocumentType. Para ver los tipos de ficheros que el motor es capaz de indexar, ejecutaremos la siguiente sentencia:
    •  
      •  
        •  
          •  

Si alguna extensión no aparece, es necesario instalar su iFilter específico. Por ejemplo, es el caso de los ficheros PDF de Adobe. En máquinas de 32 bit, el iFilter para ficheros PDF se instala con el propio Adobe Acrobat Reader, pero para máquinas de 64 bit es necesario instalar el componente Adobe PDF iFilter 9 for 64-bit platforms y seguir las instrucciones.

  • Consultas específicas para consultar campos indexados con Full-text Search, como CONTAINS o FREETEXT.

Comenzaremos con la creación del índice Full-Text search en nuestra tabla Book. Para ello, utilizaremos el Sql Management Studio.

  • Haciendo click con el botón derecho sobre nuestra tabla, desplegaremos el siguiente menú:

image

  • Seleccionar el índice único sobre la columna que anteriormente hemos creado:

image

  • Seleccionar la columna que contiene los datos varbinary a indexar, la columna que contiene el DocumentType y el lenguaje en que los libros están escritos:

image

  • Seleccionar el modo en que los cambios sobre el campo Data van a ser registrados.Salvo escenarios excepcionales, el modo más común y recomendando es Automatically:

image

  •  
    •  
      • Automático: Los cambios sobre el campo Data se registrarán en una tabla y el índice será actualizado automáticamente.
      • Manual: Los cambios sobre el campo Data se registrarán en una tabla, pero la actualización del índice conforme a esas modificaciones tendrá que ser explícito (llamado a mano).
      • No registrar cambios: Los cambios sobre el campo Data no se registrarán, por lo que el índice tendrá que ser rehecho para mantenerlo actualizado.
  • Seleccionar el catalog en el que se creará el índice ful-text y más opciones de búsqueda:

image

  •  
    • Es necesario seleccionar un catalog de tipo full-text para crear el índice. También en esa sección podemos establecer si se consideran las tildes para las búsquedas.
    • En la opción “Select full-text stoplist” se elige la lista de palabras stop (palabras que son, por ejemplo, nexos entre otras palabras, etc). Si se selecciona “system” se utilizarán las palabras stop del lenguaje del sistema. Otros valores son “off”, para que también sean indexadas, o la selección de una lista personalizada de este tipo de palabras especiales, ya sea porque queremos elegir una lista en un lenguaje distinto al del sistema o una específica. Con esta opción se pretende no crear confusión en la búsquedas y aliviar la carga del índice al no tener que indexar este tipo de palabras, que suelen ser numerosas.
  • En esta pantalla se seleccióna la planificación de la populación de la tabla y del full-text catalog.
  • Finalizando este wizard, se comenzará a popular el índice, ya que el tipo de registro de cambios se ha establecido en automático.

Ahora ya está indexado el campo Data con un índice full-text que, según está configurado, se actualizará automáticamente ante modificaciones en la tabla.

Podemos ver cómo queda el menú Full-text index de nuestra tabla:

image

 

 

 

 

 

La única opción que cabe comentar es Start Incremental Population, que realiza la populación del índice de manere incremental, con la restricción de que la tabla tiene que tener un campo timestamp para controlar ese proceso incremental.

Para posteriores post, utilizaremos las consultas CONTAINS y FREETEXT para consultar el campo indexado.

Streaming de libros Parte 1 – Almacenamiento en FileStream

Voy a intentar ilustrar a lo largo de una serie de post la gestión de un almacén de libros. Para ello, vamos a utilizar la tecnología File Stream para su almacenamiento en nuestra base de datos Sql Server 2008 R2 Express, la tecnología Full-Text Search para su posterior indexación y consulta de contenidos y WCF + MTOM para hacer posible el streaming (tanto upload como downdload) de nuestros libros.

File Stream es una manera de guardar información no estructurada, como videos, imágenes, etc, fuera de nuestro almacenaje estructurado, por lo que esa información estarán directamente almacenada en el sistema de ficheros, eliminando las penalizaciones, tanto de rendimiento como de tamaño, que supondría tenerla directamente albergada en nuestras tablas. Otra ventaja de File Stream es que nos permite aprovechar mejor los límites de tamaño que las versiones Express nos ofrecen, ya que esta información almacenada en File Stream no se tiene en cuenta para esas restricciones 🙂

El primer paso que daremos es activar el soporte para File Stream en nuestro motor de base de datos, para ello abriremos el Sql Server Configuration Manager para acceder a las propiedades de nuestra instancia y ahí activar el soporter para File Stream:

image

image

Otra forma de activar el File Stream es en la instalación de nuestro Sql Server.

El siguiente paso es crear una base de datos con un filegroup File Stream para almacenar nuestros libros, después le indicaremos a la base de datos que ese va a ser el lugar seleccionado para tal propósito. Esta tarea podemos llevarla a cabo bien con el Sql Management Studio o bien a través de la ejecución de un script.

Utilizando el Sql Management Studio seguiríamos los siguientes pasos:

  • Creación de FileGroup de tipo File Stream, lo llamaremos BooksFileStream.

image

  • En la sección General, indicamos a la base de datos en qué File Group de tipo File Stream va a almacenar los libros y cuál va a ser su ruta física en el sistema de ficheros en la propiedad Path. La llamaremos Books_Data.

image

 

Si hubieramos optado por la ejecución de un script para la creación de nuestra base de datos, tendríamos que haber generado algo parecido a lo siguiente:

CREATE DATABASE [Books] ON  PRIMARY
( NAME = N’Books’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLR2EXPRESS64MSSQLDATABooks.mdf’ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [BooksFileStream] CONTAINS FILESTREAM  DEFAULT
( NAME = N’Books_data’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLR2EXPRESS64MSSQLDATABooks_data’ )
LOG ON
( NAME = N’Books_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLR2EXPRESS64MSSQLDATABooks_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Books] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [Books].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO

En el que, además de las tareas típicas de la creación de la base de datos, se crea el File Group de tipo File Stream y se indica la ruta para el almacenamiento. También se comprueba si la base de datos tiene soporte para Full Text Search activado, y en caso negativo, lo activa.

Y ya hemos creado nuestra base de datos con soporte de File Stream. Ahora crearemos la tabla Book, en la que almacenaremos nuestros libros, con su título, el tipo de fichero y su fichero propiamente dicho.

En esta tarea no tendremos opción de elegir entre realizarla con el Sql Server Management Studio o ejecutar un script, ya que no hay manera de indicar, a través del Sql Server Management Studio, que un campo de una base de datos es File Stream. El script sería el siguiente:

CREATE TABLE [dbo].[Book] (
    [IdBook]       UNIQUEIDENTIFIER           ROWGUIDCOL NOT NULL,
    [Title]        NVARCHAR (MAX)             NULL,
    [DocumentType] NVARCHAR (10)              NULL,
    [Data]         VARBINARY (MAX) FILESTREAM NULL DEFAULT(0x),
    UNIQUE NONCLUSTERED ([IdBook] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY]
);

Me gustaría aclarar que File Stream en sí no es un tipo, si no la manera en que ese campo de la base de datos va a almacenarse. El tipo en sí es varbinary(max).

Adelantandonos a capítulos posteriores, hay que decir que, para la posterior creación de un índice Full Text Search es necesario contar en la tabla con un campo único no nulo decorado con la propiedad ROWGUIDCOL, que indica que la nueva columna es una columna de GUID de filas. Además, será necesario la creación de una columna NVARCHAR o tipo similar que almacenará la extensión del fichero en que hayamos guardado el libro (.docx, .pdf, etc), esta última columna es necesaria porque los datos indexados están en una columna de tipo varbinary y el motor de indexación Full Text Search necesita conocer el tipo de fichero sobre el que va a operar.

En el siguiente post hablaremos de la generación del índice Full Text Search y cómo útilizarlo para nuestras consultas.