Manejo de información no estructurada con File Table–Parte 1

Filetable es una de las nuevas características interesantes, disponible desde SQL Server 2012 en adelante y que complementa Filestream. Esta característica no esta disponible en SQL Azure Database ya que tiene que ver con el sistema de archivos y se requiere acceso al subsistema de almacenamiento, lo cual no es posible bajo el esquema PaaS de SQL Azure Database. Si esta disponible bajo el esquema IaaS usando maquinas virtuales de Azure con SQL Server instalado

Este artículo lo vamos a dividir en dos partes:

  • Parte 1: explicación de Filetable e implementación a nivel de base de datos
  • Parte 2: Consumo de FIleTable desde una aplicación .NET para manejo de archivos

Entonces comencemos por explicar qué es File Table. Según BOL con File Table ahora puede almacenar archivos y documentos en tablas especiales (File Tables) y tener acceso a ellos desde las aplicaciones Windows como si estuviesen almacenados en el sistema de archivos

Para esta prueba de concepto de File Table vamos a utilizar SQL Server 2014 para simular el manejo de un catálogo de plantillas de documento. Vamos a seguir los siguientes pasos:

  1. Configurar SQL Server para activar Filestream
  2. Crear una base de datos configurada para usar Filestream
  3. Crear una tabla de tipo File Table para almacenar documentos y una tabla regular para almacenar información complementaria
  4. Implementar un procedimiento almacenado para añadir documentos al catalogo
  5. Implementar un procedimiento almacenado para eliminar documentos del catalogo
  6. Implementar un procedimiento almacenado para consultar el catalogo
  7. Implementar un procedimiento almacenado consumir documentos del catalogo

Comencemos por explicar qué es FILESTREAM. Esta característica está disponible desde la versión SQL Server 2008 R2 y permite a las aplicaciones basadas en SQL Server almacenar datos no estructurados, como documentos e imágenes, en el sistema de archivos. Las aplicaciones pueden aprovechar las API de transmisión de datos al mismo tiempo que mantienen la coherencia transaccional entre los datos no estructurados y los datos estructurados correspondientes. Esto último se refiere a que podemos relacionas registros de tablas con archivos de documentos

Ahora si, comencemos con los pasos establecidos al inicio del artículo:

1. Configurar SQL Server para activar Filestream

  • Activamos FILESTREAM a través de Configuration Manager, en las propiedades del servicio, haciendo clic en las opciones de habilitar Filestream y permitir accesos remotos para el consumo de archivos. El recurso compartido de Windows es la ruta compartida que se va a utilizar para acceder a los archivos almacenados en FILESTREAM:

image

  • Habilitamos el acceso transaccional completo a través del procedimiento almacenado del sistema SP_CONFIGURE

Este es el resultado de la consulta de validación;

image

NOTA: En el procedimiento almacenado a la propiedad de filestream_access_level se le asigna el valor de 2 para permitir acceso a tanto a Transact-SQL como a las APIs de Windows. Estos son los valores permitidos para esa  propiedad son:

  • 0 – Deshabilita la compatibilidad de FILESTREAM para esta instancia
  • 1 – Habilita FILESTREAM para el acceso a Transact-SQL
  • 2 – Habilita FILESTREAM para el acceso de transmisión por secuencias a Transact-SQL y Win32

SI ejecutamos el comendo NET SHARE desde la ventana de comandos, notaremos que la unidad Catalogos es una unidad compartida del sistema. A través de esa unidad compartida accederemos a los archivos del File Table:

SNAGHTML5295421

2. Crear una base de datos configurada para usar Filestream

Ahora vamos a crear una base de datos con un grupo de archivos de FILESTREAM para poder crear posteriormente las tablas de tipo FileTable:

En la creación de esta base de datos se está considerando un grupo de archivos CatDicFG que apunta a la ruta donde se almacenarán los documentos de FILESTREAM, que serán controlados por SQL Server. Veamos como se observa en las propiedades dela base de datos creada:

image

3. Crear una tabla de tipo File Table para almacenar documentos y una tabla regular para almacenar información complementaria

Ahora procedemos a crear una tabla de tipo Files Table, en la que almacenaremos algunos documentos:

Para crear esta tabla no es requerido definir los campos, ya que File Table tiene su propia estructura. Solo debemos definir la carpeta en la que se almacenarán los documentos. En este caso, CatalogoPlantilla será una subcarpeta de la ruta definida para FILESTREAM. SI ubicamos el nuevo objeto creado, se aprecia que hay una opción para explorar el directorio:

image

Obsérvese la ruta compartida. Por ahora, vemos la carpeta compartida vacía:

image

En el siguiente paso comenzaremos a agregar archivos al File Table

4. Implementar un procedimiento almacenado para añadir documentos al catalogo

Cuando creamos la File Table, mencionamos que la tabla ya tiene sus campos definidos. Pero qué pasa si quiero incluir algunos atributos adicionales para los documentos? Para ello creamos una tabla regular, y la asociamos al FIle Table a través de una llave, de la siguiente forma:

El campo stream_id es el que nos va a ayudar a asociar el atributo Descripcion con el documento en el File Table. En este caso solo tenemos un atributo adicional, pero podemos incluir los ue necesitemos

5. Implementar un procedimiento almacenado para eliminar documentos del catalogo

Ahora vamos a implementar un Procedimiento almacenado que nos permita agregar un documento al File Table. Este procedimiento utiliza OPENROWSET para cargar el documento y utiliza una transacción para garantizar consistencia ya que se graba en el FIle Table y en la tabla de detalle de la planilla:

Hacemos una prueba ingresando un documento:

Si exploramos nuevamente la ruta del File Table, vemos que aparece el documento que acabamos de cargar:

image

6. Implementar un procedimiento almacenado para consultar el catalogo

Con este procedimiento podemos consultar el catálogo desde una aplicación, asociando el File Table con la tabla de detalle para obtener la descripción:

Esto es lo que obtenemos:

image

Esta lista puede ser utilizada para mostrar los documentos disponibles en una librería de plantilla a través de una aplicación web, para que los usuarios los consuman. Pero para consumir un documento necesitamos un procedimiento que ubique un documento individualmente

7. Implementar un procedimiento almacenado para  consumir documentos del catalogo

Con este procedimiento consultamos un documento en particular, utilizando el nombre del documento:

Este es el resultado de ejecutar este procedimiento:

image

Con este resultado, podemos abrir el documento en su aplicación nativa. El campo file_type puede ser utilizado para llamar al ejecutable adecuado para consumir el documento

En este artículo hemos revisado cómo implementar File Table en SQL Server. En la parte dos exploraremos la forma de consumir esta implementación desde una aplicación en .NET

Deja un comentario

Tu dirección de correo electrónico no será publicada.