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:

Configurar SQL Server para activar Filestream
Crear una base de datos configurada para usar Filestream
Crear una tabla de tipo File Table para almacenar documentos y una tabla regular para almacenar información complementaria
Implementar un procedimiento almacenado para añadir documentos al catalogo
Implementar un procedimiento almacenado para eliminar documentos del catalogo
Implementar un procedimiento almacenado para consultar el catalogo
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:

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

-- HABILTAR FILESTREAM A NIVEL DE SERVIDOR
USE master
EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;
GO

--VALIDAR CONFIGURACION DE FILESTREAM
SELECT * FROM sys.configurations WHERE name LIKE '%file%'

-- HABILTAR FILESTREAM A NIVEL DE SERVIDOR

USE master

EXEC sp_configure filestream_access_level, 2;

RECONFIGURE;

--VALIDAR CONFIGURACION DE FILESTREAM

SELECT * FROM sys.configurations WHERE name LIKE '%file%'

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

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:

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:

-- CREACION DE UNA BD CON FILESTREAM
IF EXISTS(SELECT * FROM sys.databases WHERE NAME= 'CatalogoDocumentos')
  DROP DATABASE CatalogoDocumentos;
GO

CREATE DATABASE CatalogoDocumento
ON PRIMARY 
(Name = CatalogoDocumento, 
 FILENAME = 'D:\SQL Data\SQL120\CatalogoDocumento01_data.mdf'),
FILEGROUP CatDocFG CONTAINS FILESTREAM
(NAME = CatDocFS,
 FILENAME='D:\SQL Data\SQL120\CatDocFS')
LOG ON
(Name = CatalogoDocumentos,
 FILENAME = 'D:\SQL Data\SQL120\CatalogoDocumento_log.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'CatalogoDocumento');
GO

-- CREACION DE UNA BD CON FILESTREAM

IF EXISTS(SELECT * FROM sys.databases WHERE NAME= 'CatalogoDocumentos')

DROP DATABASE CatalogoDocumentos;

CREATE DATABASE CatalogoDocumento

ON PRIMARY

(Name = CatalogoDocumento,

FILENAME = 'D:\SQL Data\SQL120\CatalogoDocumento01_data.mdf'),

FILEGROUP CatDocFG CONTAINS FILESTREAM

(NAME = CatDocFS,

FILENAME='D:\SQL Data\SQL120\CatDocFS')

LOG ON

(Name = CatalogoDocumentos,

FILENAME = 'D:\SQL Data\SQL120\CatalogoDocumento_log.ldf')

WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N'CatalogoDocumento');

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:

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:

--CREACION DE UNA TABLA FILETABLE
USE CatalogoDocumento;
GO
CREATE TABLE CatalogoPlantilla AS FileTable
WITH
(FileTable_Directory = 'CatalogoPlantilla');
GO

--CREACION DE UNA TABLA FILETABLE

USE CatalogoDocumento;

CREATE TABLE CatalogoPlantilla AS FileTable

WITH

(FileTable_Directory = 'CatalogoPlantilla');

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:

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

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:

--TABLA PARA AÑADIR ATRIBUTOS AL DOCUMENTO
CREATE TABLE dbo.CatalogoPlantillaDetalle 
(stream_id uniqueidentifier NOT NULL,
 Descripcion varchar(255));
GO

--TABLA PARA AÑADIR ATRIBUTOS AL DOCUMENTO

CREATE TABLE dbo.CatalogoPlantillaDetalle

(stream_id uniqueidentifier NOT NULL,

Descripcion varchar(255));

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:

/*----------------------------------------------------------------------------------------
PROPOSITO	: AGREGAR UN DOCUMENTO DEL CATALOGO DE PLANTILLAS
PARAMETROS	: ExtNombreDocumento - Nombre del archivo del Documento que se quiere agregar
			  @RutaDocumento - Ruta física en la que se encuentra el archivo a cargar
			  @Descripcion - Descripcion ampliada del documento (OPCIONAL)
AUTOR		: Alberto De Rossi
FECHA		: 26-Oct-2016
PRUEBA		: EXECUTE dbo.CatalogoPlantilla_Agrega 
			  @ExtNombreDocumento = 'AsistenciaEmpleados.xlsx', 
			  @RutaDocumento = 'D:\TEMP\PLANTILLAS\AsistenciaEmpleados.xlsx',
			  @Descripcion = 'Plantilla para controlar la asistencia de empleados'
*/----------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.CatalogoPlantilla_Agrega
@ExtNombreDocumento varchar(250),
@RutaDocumento  varchar(2000),
@Descripcion varchar(1000)
AS

SET NOCOUNT ON 

DECLARE @Query nvarchar(1000),
@ParametroQuery nvarchar(1000),
@Stream_ID uniqueidentifier

BEGIN TRY 
  BEGIN TRANSACTION --TRANSACCION PARA GARANTIZAR GRABACION EN FILESTREAM Y TABLA DE DESCRIPCIONES
	--QUERY DINAMICO YA QUE LA FUCNION OPENROWSET NO ACEPTA LA RUTA COMO PARAMETRO
	SET @Query = N'INSERT INTO dbo.CatalogoPlantilla (name,file_stream) 
	SELECT @IntNombreDocumento AS NombreDocumento, * FROM OPENROWSET(BULK '''+@RutaDocumento+''', SINGLE_BLOB) AS FileData';
	SET @ParametroQuery = N'@IntNombreDocumento varchar(250)';
	PRINT @Query; --PRINT DE CONTROL
	EXEC sys.sp_executesql @Query, @ParametroQuery,  
						  @IntNombreDocumento = @ExtNombreDocumento;

	--SE OBTIENE EL ID DEL ARCHIVO RECIEN CARGADO
	SELECT @Stream_ID = stream_id FROM dbo.CatalogoPlantilla
	WHERE name=@ExtNombreDocumento;

	--SE ACTUALIZA LA TABLA DE DESCRIPCIONES
	INSERT INTO dbo.CatalogoPlantillaDetalle (stream_id, Descripcion)
	VALUES (@Stream_ID, @Descripcion);
	
  COMMIT;

END TRY
BEGIN CATCH
  IF  XACT_STATE() = -1
    ROLLBACK TRANSACTION;
  
  DECLARE @Error int = ERROR_NUMBER();
  IF @Error = 2627 --SI ES ERROR DE INDICE UNICO (NOMBRE) ADVIERTE QUE YA EXISTE
    SELECT 2627 AS NumError, 'Ya existe un documento con ese nombre' AS MsgError;
  ELSE
    SELECT ERROR_NUMBER() AS NumError,ERROR_MESSAGE() AS MsgError;

END CATCH
GO

/*----------------------------------------------------------------------------------------

PROPOSITO : AGREGAR UN DOCUMENTO DEL CATALOGO DE PLANTILLAS

PARAMETROS : ExtNombreDocumento - Nombre del archivo del Documento que se quiere agregar

@RutaDocumento - Ruta física en la que se encuentra el archivo a cargar

@Descripcion - Descripcion ampliada del documento (OPCIONAL)

AUTOR : Alberto De Rossi

FECHA : 26-Oct-2016

PRUEBA : EXECUTE dbo.CatalogoPlantilla_Agrega

@ExtNombreDocumento = 'AsistenciaEmpleados.xlsx',

@RutaDocumento = 'D:\TEMP\PLANTILLAS\AsistenciaEmpleados.xlsx',

@Descripcion = 'Plantilla para controlar la asistencia de empleados'

*/----------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.CatalogoPlantilla_Agrega

@ExtNombreDocumento varchar(250),

@RutaDocumento varchar(2000),

@Descripcion varchar(1000)

SET NOCOUNT ON

DECLARE @Query nvarchar(1000),

@ParametroQuery nvarchar(1000),

@Stream_ID uniqueidentifier

BEGIN TRY

BEGIN TRANSACTION --TRANSACCION PARA GARANTIZAR GRABACION EN FILESTREAM Y TABLA DE DESCRIPCIONES

--QUERY DINAMICO YA QUE LA FUCNION OPENROWSET NO ACEPTA LA RUTA COMO PARAMETRO

SET @Query = N'INSERT INTO dbo.CatalogoPlantilla (name,file_stream)

SELECT @IntNombreDocumento AS NombreDocumento, * FROM OPENROWSET(BULK '''+@RutaDocumento+''', SINGLE_BLOB) AS FileData';

SET @ParametroQuery = N'@IntNombreDocumento varchar(250)';

PRINT @Query; --PRINT DE CONTROL

EXEC sys.sp_executesql @Query, @ParametroQuery,

@IntNombreDocumento = @ExtNombreDocumento;

--SE OBTIENE EL ID DEL ARCHIVO RECIEN CARGADO

SELECT @Stream_ID = stream_id FROM dbo.CatalogoPlantilla

WHERE name=@ExtNombreDocumento;

--SE ACTUALIZA LA TABLA DE DESCRIPCIONES

INSERT INTO dbo.CatalogoPlantillaDetalle (stream_id, Descripcion)

VALUES (@Stream_ID, @Descripcion);

COMMIT;

END TRY

BEGIN CATCH

IF XACT_STATE() = -1

ROLLBACK TRANSACTION;

DECLARE @Error int = ERROR_NUMBER();

IF @Error = 2627 --SI ES ERROR DE INDICE UNICO (NOMBRE) ADVIERTE QUE YA EXISTE

SELECT 2627 AS NumError, 'Ya existe un documento con ese nombre' AS MsgError;

ELSE

SELECT ERROR_NUMBER() AS NumError,ERROR_MESSAGE() AS MsgError;

END CATCH

Hacemos una prueba ingresando un documento:

--Añadimos una plantilla EXCEL al File Table
EXECUTE dbo.CatalogoPlantilla_Agrega 
@ExtNombreDocumento = 'AsistenciaEmpleados.xlsx', 
@RutaDocumento = 'D:\TEMP\PLANTILLAS\AsistenciaEmpleados.xlsx',
@Descripcion = 'Plantilla para controlar la asistencia de empleados'

--Añadimos una plantilla EXCEL al File Table

EXECUTE dbo.CatalogoPlantilla_Agrega

@ExtNombreDocumento = 'AsistenciaEmpleados.xlsx',

@RutaDocumento = 'D:\TEMP\PLANTILLAS\AsistenciaEmpleados.xlsx',

@Descripcion = 'Plantilla para controlar la asistencia de empleados'

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

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:

/*----------------------------------------------------------------------------------------
PROPOSITO    : CONSULTAR INFORMACION DE TODO EL CATALOGO DE PLANTILLAS
PARAMETROS    : NA
AUTOR        : Alberto De Rossi
FECHA        : 26-Oct-2016
PRUEBA        : EXEC dbo.CatalogoPlantilla_Consulta 
*/----------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.CatalogoPlantilla_Consulta
AS

SET NOCOUNT ON

SELECT cp.stream_id,COALESCE(cpd.Descripcion, 'Sin Descripcion') AS Descripcion,
name ,file_type ,cached_file_size,creation_time,last_access_time
FROM CatalogoPlantilla cp
LEFT JOIN CatalogoPlantillaDetalle cpd
   ON cp.stream_id=cpd.stream_id;
GO

/*----------------------------------------------------------------------------------------

PROPOSITO : CONSULTAR INFORMACION DE TODO EL CATALOGO DE PLANTILLAS

PARAMETROS : NA

AUTOR : Alberto De Rossi

FECHA : 26-Oct-2016

PRUEBA : EXEC dbo.CatalogoPlantilla_Consulta

*/----------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.CatalogoPlantilla_Consulta

SET NOCOUNT ON

SELECT cp.stream_id,COALESCE(cpd.Descripcion, 'Sin Descripcion') AS Descripcion,

name ,file_type ,cached_file_size,creation_time,last_access_time

FROM CatalogoPlantilla cp

LEFT JOIN CatalogoPlantillaDetalle cpd

ON cp.stream_id=cpd.stream_id;

Esto es lo que obtenemos:

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:

/*----------------------------------------------------------------------------------------
PROPOSITO    : CONSULTAR INFORMACION DE UN ARCHIVO EN PARTICULAR DEL CATALOGO DE PLANTILLAS
PARAMETROS    : @NombreDocumento - Nombre del archivo del Documento que se quiere consultar
AUTOR        : Alberto De Rossi
FECHA        : 26-Oct-2016
PRUEBA        : EXEC dbo.CatalogoPlantilla_ConsultaDocumento @NombreDocumento = 'AsistenciaEmpleados.xlsx'
*/----------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.CatalogoPlantilla_ConsultaDocumento
@NombreDocumento varchar(250)
AS

SET NOCOUNT ON

SELECT cp.stream_id,COALESCE(cpd.Descripcion, 'Sin Descripcion') AS Descripcion,
name ,file_type ,CAST(file_stream AS VARCHAR) FileContent,file_stream ,
cached_file_size ,creation_time ,last_write_time ,last_access_time
FROM CatalogoPlantilla cp
JOIN CatalogoPlantillaDetalle cpd
   ON cp.stream_id=cpd.stream_id
WHERE cp.name =  @NombreDocumento;
GO

/*----------------------------------------------------------------------------------------

PROPOSITO : CONSULTAR INFORMACION DE UN ARCHIVO EN PARTICULAR DEL CATALOGO DE PLANTILLAS

PARAMETROS : @NombreDocumento - Nombre del archivo del Documento que se quiere consultar

AUTOR : Alberto De Rossi

FECHA : 26-Oct-2016

PRUEBA : EXEC dbo.CatalogoPlantilla_ConsultaDocumento @NombreDocumento = 'AsistenciaEmpleados.xlsx'

*/----------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.CatalogoPlantilla_ConsultaDocumento

@NombreDocumento varchar(250)

SET NOCOUNT ON

SELECT cp.stream_id,COALESCE(cpd.Descripcion, 'Sin Descripcion') AS Descripcion,

name ,file_type ,CAST(file_stream AS VARCHAR) FileContent,file_stream ,

cached_file_size ,creation_time ,last_write_time ,last_access_time

FROM CatalogoPlantilla cp

JOIN CatalogoPlantillaDetalle cpd

ON cp.stream_id=cpd.stream_id

WHERE cp.name = @NombreDocumento;

Este es el resultado de ejecutar este procedimiento:

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

Comparte Este artículo:

Deja una respuesta Cancelar la respuesta