jueves, 12 de diciembre de 2013

Read uncommitted


En esta entrada exploraremos cómo funciona el nivel de aislamiento READ UNCOMMITTED.

Antes de comenzar el ejercicio quisiera hacer notar que el nivel de aislamiento por defecto de SQL Server es READ COMMITTED, esto implica que no podemos leer la información sino hasta que ésta ha sido actualizada de forma definitiva (committed) en la base de datos o bien los cambios han sido deshechos a través de un rollback.

Por ejemplo, supongamos que la transacción 1 ejecuta la siguiente instrucción:
USE AdventureWorks GO BEGIN TRAN UPDATE Production.Product SET ListPrice += 1 WHERE ProductID = 1

Notamos que la transacción queda "abierta", es decir que no ha sido confirmada hacia la base de datos (committed) o deshecha (rollback), por lo tanto SQL Server mantendrá el bloqueo de tipo exclusivo sobre el producto cuyo id sea 1.

En otra ventana ejecutaremos la siguiente sentencia:
USE AdventureWorks GO BEGIN TRAN UPDATE Production.Product SET ListPrice += 1 WHERE ProductID = 1

Veremos que este query queda en espera debido a que el nivel de bloqueo que él requiere (READ COMMITTED) no le permite obtener la información que necesita debido a que la transacción 1 no ha finalizado. La transacción 1 tiene un bloqueo exclusivo que no es compatible con READ COMMITTED de otra transacción.

Esta información la podemos confirmar con la siguiente sentencia:
USE AdventureWorks GO SELECT DB_NAME(T.resource_database_id) AS database_name, OBJECT_NAME(P.[object_id]) FROM sys.dm_tran_locks T INNER JOIN sys.partitions P ON T.resource_associated_entity_id = P.hobt_id WHERE T.request_mode = 'X'

Veremos que se tiene un bloqueo exclusivo sobre algún elemento del objeto Production.Product. También se podría obtener la fila que está siendo bloqueada, pero perderíamos el enfoque de esta entrada.

Volvamos a la ventana de la transacción 1 y ejecutemos COMMIT, veremos que de forma casi inmediata la transacción 2 será desbloqueada y podrá obtener los datos que requiere.

¿Qué pasa cuando la información que queremos obtener es "inmutable"?, no quisiera utilizar la palabra inmutable en su totalidad debido a que en teoría los únicos datos inmutables de una base de datos son las llaves primarias, pero podemos suponer que el nombre de un producto es inmutable, sería muy extraño tener que actualizar el nombre de un producto. En este caso no requerimos que alguna otra transacción genere algún bloqueo sobre la lectura del Id y del Nombre del producto, por lo tanto, necesitamos leer los datos independientemente de los cambios que se estén realizando sobre ellos.

Antes de hacer cambios en el código, vamos a la ventana de la transacción 2 y ejecutamos COMMIT para no dejar transacciones abiertas (colgadas) en el manejador de bases de datos.

Volvamos a ejecutar el código de la transacción 1 para generar un bloqueo sobre el producto cuyo Id es 1, y vamos a cambiar un poco el código de la transacción 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID = 1

Podemos notar que ahora estamos estableciendo el nivel de aislamiento en READ UNCOMMITTED para poder leer datos que no han sido hechos "oficiales" en la base de datos. Si ejecutamos la transacción veremos que la información del nombre, producto y precio son inmediatamente devueltos.

Hasta ahora todo parecería que salió tal como lo deseamos, pero... ¿qué pasaría si necesitáramos realizar alguna acción tomando el precio del producto?, he ahí que vendría un fenómeno conocido como "dirty reads", eso quiere decir que podríamos leer un valor que no ha sido hecho oficial sobre la base de datos y que posiblemente su cambio pudiera ser deshecho, es decir que operaríamos con un valor que jamás debió ser tomado.

¿Qué debemos hacer?, dependerá de la operación y de los valores que queremos utilizar, deberemos establecer categorías de los datos que requieren un bloqueo exclusivo y también identificar los datos que pueden ser leídos durante una transacción de actualización.

Espero te haya resultado de utilidad esta entrada, nos vemos la siguiente.

miércoles, 11 de diciembre de 2013

Refrescando los metadatos de una vista


En esta entrada mostraré un fenómeno que se da cuando no establecemos de forma explícita las columnas que queremos devolver en una vista.

Comenzaremos creando una tabla, metiéndole datos y creando una vista que mostrará el contenido de toda la tabla:
CREATE TABLE Test( Id INT IDENTITY(1, 1), Value VARCHAR(10) ) GO INSERT INTO Test (Value) VALUES ('Valor 1'), ('Valor 2'), ('Valor 3') GO CREATE VIEW vTest AS SELECT * FROM Test GO SELECT * FROM vTest

Veremos que el resultado de la consulta a la vista nos devolverá el contenido completo de la tabla (tal como lo esperábamos):

Ahora vamos a modificar la estructura de nuestra tabla para agregar una columna calculada que muestre la primera letra de la columna Value:
ALTER TABLE Test ADD FirstLetter AS SUBSTRING(Value, 1, 1) PERSISTED

Ahora vamos a ejecutar un query que muestre todo el contenido de la tabla y también que muestre todo el contenido de la vista:

Notemos que la primera sentencia que trabaja sobre la tabla muestra las tres columnas que conforman al objeto, mientras que la segunda sentencia sólo muestra dos columnas, las columnas que existían en la tabla durante el momento de la creación de la vista. ¿Qué es lo que está pasando?, que los metadatos de la vista siguen siendo los mismos, es por ello que aunque cambió la definición de la tabla, la vista sigue utilizando las mismas columnas que antes.

La forma de corregirlo es a través del procedimiento almacenado sp_refreshview:
sp_refreshview 'vTest' GO SELECT * FROM vTest

Notemos que ahora ya se están mostrando las tres columnas que forman parte de la tabla.

Aunque te muestro cómo corregir este problema, no es nada recomendable utilizar un query del tipo SELECT * FROM Table porque se estará ignorando cualquier índice que haya sido creado sobre la tabla para optimizar su lectura.

Espero esta entrada te haya resultado de utilidad, nos vemos la siguiente.

martes, 10 de diciembre de 2013

Limpiar historial de respaldos


En esta entrada muy corta pero sumamente útil, te mostraré cómo eliminar el historial de copias de respaldo que se acumula en la base de datos msdb a lo largo del tiempo.

Comenzaré explicándote que no importa cómo saques el respaldo de tu base de datos, el historial de haberlo hecho se guarda, haya sido un respaldo de tipo Full, Log, Differential o de cualquier otro tipo.

Es común que en servidores que tengan implementado un esquema de Log Shipping, el tamaño de la base de datos msdb se dispare, esto se debe a que la cantidad de respaldos aumenta por la naturaleza propia de este esquema de alta disponibilidad y por lo tanto el historial de respaldos se incrementa.

Antes de limpiar el historial de respaldos es importante sacar un respaldo de la base de datos msdb, esto es para poder tener evidencia clara de las operaciones que se realizaron y en caso de una auditoría podamos demostrar que se hicieron los respaldos en tiempo y forma. También puede servirnos para demostrar que no se hicieron los respaldos, a veces la gente encargada de esta pequeña parte de la operación se le va el avión y no revisa que los respaldos se hayan ejecutado de forma correcta.

Si quisiéramos eliminar todo el historial de respaldos bastaría con utilizar el siguiente código:
DECLARE @Today DATETIME = GETDATE() EXEC msdb.dbo.sp_delete_backuphistory @Today

Veamos que el procedimiento almacenado sp_delete_backuphistory recibe como parámetro la fecha más antigua que se guardará en el historial de respaldos, eso implica que todo registro histórico de respaldo que se haya creado antes de esa fecha ya no estará almacenado en msdb.

Si se sacara un respaldo cada 30 días de la base de datos msdb, bien podríamos estar limpiando el historial de la siguiente manera:
DECLARE @MonthAgo DATETIME = DATEADD(DAY, -30, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @MonthAgo

Espero esta entrada te haya resultado útil, nos vemos la siguiente.

lunes, 9 de diciembre de 2013

IDENTITY vs SCOPE_IDENTITY()


En esta entrada exploraremos la diferencia entre estas dos funciones del sistema que mucha gente utiliza y que es sumamente importante conocer el valor que están devolviendo.

Comenzaremos creando dos tablas, en una agregaremos valores y la otra servirá para llevar un historial muy sencillo de movimientos:
CREATE TABLE TestIdentity( Id INT IDENTITY(1, 1), Value VARCHAR(10) NOT NULL ) GO CREATE TABLE TestIdentityLog( Id INT IDENTITY(1, 1), Task VARCHAR(10), Value VARCHAR(10) )

En la tabla TestIdentity iremos guardando valores de prueba y la tabla TestIdentityLog estará guardando el historial de movimientos que se han llevado a cabo sobre la tabla TestIdentity. Debemos notar que en las dos tablas tenemos una columna IDENTITY.

Ahora vamos a crear dos triggers, uno para el INSERT y otro para el DELETE:
CREATE TRIGGER TestIdentity_Insert ON TestIdentity FOR INSERT AS BEGIN INSERT INTO TestIdentityLog (Task, Value) SELECT 'INSERT', Value FROM INSERTED END GO CREATE TRIGGER TestIdentity_Delete ON TestIdentity FOR DELETE AS BEGIN INSERT INTO TestIdentityLog (Task, Value) SELECT 'DELETE', Value FROM DELETED END

Ambos guardan el movimiento en la tabla de historial, sólo que el primero es para INSERT y el segundo para DELETE

Vamos a crear un procedimiento almacenado para agregar un valor a TestIdentity, de paso mostraremos el resultado de la ejecución de los dos valores que nos interesan: @@IDENTITY y SCOPE_IDENTITY():
CREATE PROC pAddValue( @Value VARCHAR(10) ) AS BEGIN INSERT INTO TestIdentity (Value) VALUES (@Value) PRINT '@@IDENTITY: ' + CAST(@@IDENTITY AS VARCHAR) PRINT 'SCOPE_IDENTITY(): ' + CAST(SCOPE_IDENTITY() AS VARCHAR) END

Ahora vamos a crear un procedimiento almacenado que nos permita eliminar un elemento de la tabla TestIdentity:
CREATE PROC pDeleteValue( @Id INT ) AS BEGIN DELETE TestIdentity WHERE Id = @Id END

Habitualmente se piensa que podemos utilizar cualquiera de los dos sin problema, pero al ejecutar el siguiente código veremos que ambas nos mostrarán números diferentes en la tercera ejecución:
SET NOCOUNT ON GO EXEC pAddValue 'Valor 1' GO EXEC pDeleteValue 1 GO EXEC pAddValue 'Valor 1'

El primer EXEC mostrará que @@IDENTITY devuelve 1 y SCOPE_IDENTITY() devuelve 1, pero veremos que el tercer EXEC mostrará que @@IDENTITY devuelve 3 y SCOPE_IDENTITY() devuelve 2.

Esto se debe a que @@IDENTITY devuelve el último valor obtenido a través de una columna IDENTITY, este valor pertence a la columna Id de la tabla TestIdentityLog, los trigger que se están disparando ante el INSERT o DELETE son los que están realizando esa inserción y por lo tanto el último valor asignado es el de la tabla TestIdentityLog.

La función SCOPE_IDENTITY() devuelve el último valor asignado por IDENTITY en el entorno actual de ejecución, para nuestro procedimiento pAddValue el único IDENTITY que tiene a su alcance es el que está en la columna Id de la tabla TestIdentity, es por ello que devuelve el valor 2.

Como podrás notar, es importante conocer la diferencia entre las dos opciones que tenemos, dependerá del objetivo del procedimiento o de la operación cuál te resulte adecuado a tus necesidades; generalmente utilizarás SCOPE_IDENTITY()

Espero esta entrada te haya resultado útil, nos vemos la siguiente.

viernes, 6 de diciembre de 2013

Mapa con geolocalización


En esta entrada crearemos un mapa de Google Maps utilizando la API versión 3.14 y ubicaremos un marcador en la posición actual del usuario utilizando la geolocalización del dispositivo.

Para comenzar crearemos un sitio web en VS2010 o VS2012 y agregaremos tres carpetas: css, img y js. En la carpeta js colocaremos jQuery, creamos un archivo map.js y también lo agregamos a la carpeta js. Creamos un archivo de hoja de estilos llamado site.css y lo colocamos en nuestra carpeta css.

Para el icono de la posición actual del usuario podemos utilizar uno de los que aparecen en el sitio http://mapicons.nicolasmollet.com, tienen un diseño atractivo y se puede personalizar el color de fondo. Una vez elegido el icono lo guardamos en nuestra carpeta img con el nombre current_location.png

Una vez realizados estos pasos nuestro Explorador de Soluciones debería verse de la siguiente forma:

Vamos a incorporar todos los elementos junto con la referencia a la API de Google Maps en nuestra página HTML con el siguiente código:
<head> <title>Ubicación actual</title> <script src="http://maps.google.com/maps/api/js?v=3.14&sensor=false&language=es-mx" type="text/javascript"></script> <script src="js/jquery-1.10.2.min.js" type="text/javascript"></script> <link href="css/site.css" rel="stylesheet" type="text/css" /> <script src="js/map.js" type="text/javascript"></script> </head> <body> <div id="map"></div> </body> </html>

Vamos a abrir nuestra hoja de estilos y vamos a colocar algunos estilos para que el mapa ocupe la pantalla completa del dispositivo:
html, body { margin: 0px; height: 100%; } #map { width: 100%; height: 100%; }

Ahora vamos a generar el código que nos permitirá la creación del mapa, la localización de la ubicación del usuario y la colocación del respectivo marcador en el mapa.

Abriremos nuestro archivo map.js y comenzaremos con la obtención de la ubicación actual del usuario:
///<reference path="jquery-1.10.2.min.js" /> var blogger = blogger || {}; blogger.start = function () { if (window.clientInformation) { window.clientInformation.geolocation.getCurrentPosition( function (e) { var latlng = new google.maps.LatLng(e.coords.latitude, e.coords.longitude); initialize(latlng); }, function (e) { initialize(null); }, { enableHighAccuracy: true, timeout: 5000, maximumAge: 0 } ); } else { window.navigator.geolocation.getCurrentPosition( function (e) { var latlng = new google.maps.LatLng(e.coords.latitude, e.coords.longitude); initialize(latlng); }, function (e) { initialize(null); }, { enableHighAccuracy: true, timeout: 5000, maximumAge: 0 } ); } function initialize(e) { } } $(document).ready(function () { blogger.start(); });

En la primera línea de código estamos agregando una referencia a la librería jQuery para que el editor tome la definición de las funciones de esa librería y nos facilite la codificación.

Estamos creando un espacio de nombres llamado blogger para que el manejo de nuestras variables y funciones no vayan a causar conflicto con otras que estén depositadas en el espacio global. Una vez que haya finalizado la carga de la página estamos mandando a llamar a la función blogger.start que será la encargada de todo el trabajo.

Estamos validando que exista la propiedad window.clientInformation para poder diferenciar entre el navegador Safari y los demás, veamos que cuando no existe esa propiedad la obtención de la localización actual del usuario se hace a través del objeto window.navigator

La función getCurrentPosition recibe tres argumentos, el primero marca la función que se mandará a llamar cuando se haya podido obtener la posición del usuario, el segundo es la función que se ejecutará cuando no se haya podido obtener la localización del usuario y el tercero es un conjunto de opciones que nos permiten establecer si queremos la mayor exactitud posible, el tiempo en milisegundos que esperaremos para obtener la posición y el tiempo que esta posición estará guardada en caché.

Veamos que cuando la localización haya sido exitosa, se estará construyendo un objeto google.maps.LatLng, este objeto nos permite establecer ubicaciones en la notación (Latitud, Longitud) en el mapa.

Ahora vamos a codificar nuestra función initialize que creará el mapa y el marcador con la ubicación actual del usuario.

Comenzaremos con la creación del mapa centrándolo en la ubicación del usuario con un zoom de 14:
function initialize(e) { var mapOptions = { center: e, zoom: 14 }; blogger.map = new google.maps.Map(document.getElementById('map'), mapOptions); }

Veamos que el constructor del objeto google.maps.Map nos pide el nodo donde se alojará el mapa (en nuestro caso un div cuyo id es "map") y también un literal object que tenga la configuración del mapa. Vamos a abrir nuestro archivo index.htm con el navegador y veamos que nos pedirá la autorización para enviar nuestra localización a la página web, esto es completamente normal.

Ahora vamos a crear un marcador que utilice la imagen que tenemos en la carpeta img y lo colocaremos en la posición actual del usuario: function initialize(e) { var mapOptions = { center: e, zoom: 14 }; blogger.map = new google.maps.Map(document.getElementById('map'), mapOptions); var markerOptions = { position: e, map: blogger.map, title: 'Usted se encuentra aquí', icon: 'img/current_location.png' } var currentPositionMarker = new google.maps.Marker(markerOptions); }

El constructor del objeto google.maps.Marker nos pide un objeto literal que traiga la configuración del marcador, en este caso nos pide la posición del marcador (google.maps.LagLnt), el mapa donded estará localizado el marcador (blogger.map), el texto que mostrará como tooltip del marcador y la url del icono que queremos utilizar. En caso de que no utilicemos el atributo "icon", entonces se colocará el marcador que tiene por defecto Google Maps.

Si visitamos de nueva cuenta nuestro sitio, veremos que colocará el marcador en nuestra posición actual.

Espero te haya resultado de utilidad esta entrada, actualmente muchas aplicaciones están requiriendo del uso de mapas y Google Maps es una opción muy buena dada la facilidad que tiene su API para ser utilizada.

¡Nos vemos la siguiente!

jueves, 5 de diciembre de 2013

Web Workers


En esta entrada veremos la implementación de un Worker de HTML5 muy sencillo pero con implicaciones de conocimiento que vale la pena aprender.

Los Worker se hicieron para ejecución de tareas asíncronas que se ejecutarán en un hilo ajeno al que está ejecutando la página web. Tan ajeno que desde el Worker no tenemos acceso al DOM de la página, es decir que toda la sincronización se hace a través de mensajes, de objetos que sean serializables. Por excelencia utilizaremos JSON.

El problema planteado es que necesitamos realizar peticiones cada 10 segundos a un endpoint el cual devolverá un valor estadísticamente único, simulando que debemos estar checando el servidor para ver si hay datos nuevos para actualizar la interfaz del usuario.

Para esto vamos a preparar nuestro entorno de desarrollo (VS2010 o VS2012):

  1. Crear un sitio web
  2. Agregar una carpeta llamada js
  3. Agregar un archivo index.htm
  4. Agregar un Generic Handler llamado GetRandom.ashx
  5. Agregar jQuery a la carpeta js
  6. Agregar dos archivos js a la carpeta: tools.js y randomWorker.js

El archivo tools.js será el que esté en comunicación con el randomWorker, éste último es el que realizará las peticiones a GetRandom.ashx para un valor nuevo.

Prepararemos nuestro archivo index.htm con el siguiente código:
<head> <title>Valor aleatorio a través de Worker</title> <script src="js/jquery-1.7.2.min.js" type="text/javascript"></script> <script src="js/tools.js" type="text/javascript"></script> </head> <body> <p>Este es el valor aleatorio generado desde el servidor: <span id="lblAleatorio"></span></p> </body>

Vamos a abrir nuestro randomWorker.js para programar la tarea asíncrona que se estará ejecutando cada 10 segundos:
self.onmessage = function (e) { var xmlhttp = new XMLHttpRequest(); xmlhttp.onreadystatechange = function () { if (xmlhttp.readyState == 4 && xmlhttp.status == 200) { postMessage(JSON.parse(xmlhttp.response)); } } var url = '../GetRandom.ashx'; xmlhttp.open('GET', url); xmlhttp.send(); }

Estamos creando una instancia de XMLHttpRequest para realizar las peticiones a GetRandom.ashx. ¿Por qué no utilizamos $.ajax?, porque jQuery está basado en el DOM y por lo tanto no puede ser utilizado desde un Worker.

Estamos asignando un manejador al evento onreadystatechange que se dispara cada vez que hay un cambio en el estado de la petición. Los valores para readyState son los siguientes:

0: Sin inicializar, es decir que el método open no ha sido llamado.
1: Cargando, ya fue abierto pero el método send no ha sido llamado.
2: El método send ya fue llamado.
3: La respuesta está siendo descargada.
4: Todas las operaciones han finalizado.

El atributo status establece un valor para el tipo de respuesta que se está recibiendo. Los códigos de estado pueden tener los siguientes valores:

1xx: El servidor ya recibió las cabeceras y se puede proceder con el envío del cuerpo de la petición.
2xx: La solicitud fue recibida por el servidor y fue correcta.
3xx: Para terminar la solicitud el cliente debe de tomar acciones adicionales. Generalmente encontraremos que se debe a que el recurso ya fue cambiado de dirección.
4xx: La sintaxis de la petición no es la correcta o bien esta no puede ser procesada. Esto indica un error del lado del cliente.
5xx: Ocurrió un error del lado del servidor.

Tomando en cuenta lo anterior, el readyState que nos interesa es el 4 y el status es el 200, es por ello que esa validación se está haciendo en el if.

Nuestro GetRandom.ashx estará devolviendo información en formato JSON por lo que procedemos a procesarla y con la función postMessage se la enviamos al cliente del Worker (tools.js)

Notemos también que no estamos utilizando la función window.setInterval para programar la petición cada 10 segundos, eso es debido a que en el Worker no tenemos acceso al DOM y por lo tanto no tenemos acceso al objeto window.

Abrimos nuestro archivo tools.js y colocamos el siguiente código:
var tools = tools || {}; tools.Worker = new Worker('js/randomWorker.js'); tools.Worker.onmessage = function (m) { $('#lblAleatorio').text(m.data.value); }; tools.Worker.onerror = function (e) { alert('Ocurrió un problema durante la ejecución del Worker'); } $(document).ready(function () { tools.Worker.postMessage('getRandomValue'); window.setInterval(function () { tools.Worker.postMessage('getRandomValue'); }, 10000); });

Estamos creando un espacio de nombres "tools" para que cualquier variable que utilicemos no entre en conflicto con otra que esté en la página. Después estamos creando una instancia del objeto Worker que será el encargado de ejecutar la tarea asíncrona establecida en el archivo randomWorker.js.

Estamos asignando un manejador al evento onmessage del objeto Worker que recibirá el resultado de la ejecución asíncrona del Worker. Notemos que el resultado viene en la propiedad data. El resultado de la ejecución la colocaremos en nuestro elemento span con el id lblAleatorio.

Por si hay algún error, estamos también estableciendo un manejador para el evento onerror. Al finalizar la carga de la página, estamos realizando una petición inicial y dejamos programada una nueva petición en intervalos de 10 segundos. El método postMessage requiere que le sea pasado un argumento, entonces estamos enviando un texto muy sencillo y que realmente en este ejercicio no tiene la mayor importancia porque de él no depende alguna ejecución por parte del Worker.

Finalmente vamos a programar nuestro GetRandom.ashx con el siguiente código:
public class GetRandom : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "application/json"; context.Response.Write("{\"value\": \"" + Guid.NewGuid().ToString() + "\"}"); } public bool IsReusable { get { return false; } } }

Veamos que el ContentType de la respuesta está establecido en el MIME que necesita JSON, también deberemos notar que estamos generando un valor estadísticamente único con el objeto Guid.

Voy a visitar el archivo index.htm con IE 11. Esto es debido a que quiero hacer notar algo en particular con este navegador:

Ejecución con Internet Explorer 11

Podemos esperar más tiempo pero veremos que el valor único no es actualizado, esto se debe al caché del navegador. ¿Cómo podemos probarlo?, vamos a abrir las herramientas de desarrollo presionando F12 y activando la captura de red:

Captura del tráfico de red con la herramienta para desarrolladores de Internet Explorer 11

¿Qué está pasando?, podemos ver que el status es 304, esto significa que la petición está siendo servida desde el caché del navegador y no está yendo hasta el servidor por el nuevo valor. Para corregir este detalle vamos a ajustar el código de GetRandom.ashx de la siguiente manera:
context.Response.ContentType = "application/json"; context.Response.AppendHeader("Cache-Control", "no-cache, no-store, must-revalidate"); context.Response.AppendHeader("Pragma", "no-cache"); context.Response.AppendHeader("Expires", "0"); context.Response.Write("{\"value\": \"" + Guid.NewGuid().ToString() + "\"}");

Una vez realizado el cambio volvemos a visitar nuestro index.htm y veremos que cada 10 segundos el valor será actualizado.

Este ha sido un ejercicio muy sencillo pero aborda varias cosas que vale la pena notar:

  1. No tenemos acceso al DOM desde un Worker.
  2. No podemos utilizar jQuery en un Worker dado que jQuery está basado en el DOM.
  3. La comunicación entre el Worker y la página (en nuestro caso tools.js) debe ser a través de mensajes de texto. Es por ello que se recomienda el uso de JSON.
  4. Deshabilitamos el caché en nuestro Handler para evitar que el navegador no realice la petición al servidor al despachar el resultado desde el caché.

Espero les haya resultado de mucha utilidad, nos vemos la siguiente.

miércoles, 4 de diciembre de 2013

Variables privadas en javascript



En esta entrada quiero abordar el tema de la implementación de variables privadas en un objeto javascript. Seguramente para muchas personas resultará muy básico, pero en proyectos que he participado y en los cursos que imparto me he encontrado que no se conoce esta característica de los objetos implementados en javascript.

Comenzaremos con un objeto sumamente sencillo y plantearemos algunas interrogantes que resultarán interesantes:
function Persona(nombre, apellido, edad) { this.Nombre = nombre; this.Apellido = apellido; this.Edad = edad; }

Notemos que se está declarando un objeto Persona que tendrá tres propiedades públicas: Nombre, Apellido y Edad. Podemos perfectamente validar la información que estamos recibiendo antes de asignarla a nuestras propiedades de la siguiente manera:
function Persona(nombre, apellido, edad) { this.Nombre = nombre; this.Apellido = apellido; if (!isNaN(edad) && Number(edad) > 18) { this.Edad = edad; } else { this.Edad = null; } } var prueba = new Persona('Pepe', 'Pérez', 30); if (prueba.Edad) alert('La edad es: ' + String(prueba.Edad)); else alert('La edad no fue asignada');

Si primero lo probamos con 30 veremos que la edad es correctamente asignada, pero si le asignamos un valor menor o igual a 18 o bien una palabra, veremos que la propiedad Edad tendrá un valor null.

Hasta ahora todo va bien, pero, ¿qué pasa si una vez que es construido el objeto le asignamos un valor no válido a la propiedad pública Edad?:
var prueba = new Persona('Pepe', 'Pérez', 'Hola'); if (prueba.Edad) alert('La edad es: ' + String(prueba.Edad)); else { alert('La edad no fue asignada'); prueba.Edad = 'Hola'; alert('La edad es: ' + prueba.Edad); }

Veremos que el valor 'Hola' habrá sido asignado a Edad sin problema alguno.

Para evitar este problema y validar la información antes de que ésta sea actualizada en nuestros objetos, podemos implementar miembros privados expuestos a través de funciones públicas. En este caso lo haremos con la Edad:
function Persona(nombre, apellido, edad) { this.Nombre = nombre; this.Apellido = apellido; var m_Edad = null; if (!isNaN(edad) && Number(edad) > 18) { m_Edad = edad; } this.getEdad = function () { return m_Edad; } this.setEdad = function (e) { if (!isNaN(edad) && Number(edad) > 18) { m_Edad = edad; } } }

Veamos que ya no existe la propiedad pública Edad, ahora tenemos un miembro privado llamado m_Edad el cual es leído a través de la función getEdad y actualizado a través de la función setEdad. Dentro de la función setEdad estamos validando que sea una edad válida, una vez que pasó nuestra validación, procedemos a asignar el valor. Probemos con el código que habíamos escrito antes con unas pequeñas adecuaciones para utilizar las nuevas funciones:
var prueba = new Persona('Pepe', 'Pérez', 'Hola'); if (prueba.getEdad()) alert('La edad es: ' + String(prueba.getEdad())); else { alert('La edad no fue asignada'); prueba.setEdad('Hola'); alert('La edad es: ' + prueba.getEdad()); }

Veremos que el segundo mensaje mostrará 'La edad es: null', esto se debe a que la edad 'Hola' no fue asignada a nuestro miembro privado m_Edad debido a que no era un valor válido.

Espero te haya resultado de utilidad esta pequeña entrada, nos vemos la siguiente.

martes, 3 de diciembre de 2013

Índices y memoria



Habitualmente se piensa que el resultado positivo de un índice sólo se traduce en una lectura más rápida de datos. En esta entrada demostraré que también nos ayudan a reducir el uso de memoria al cargar menos páginas en el buffer de SQL Server.

Recordemos que SQL Server coloca en memoria los datos antes de poder despacharlos, esto se hace de esa forma para que la futura lectura de los datos sea mucho más rápida y no tenga que ir hasta los dispositivos de almacenamiento para recuperar la información.

Quiero hacer un ejercicio muy sencillo pero significativo con la base de datos AdventureWorks. El objetivo es obtener el total de ventas de los productos de color rojo. Analizaremos la cantidad de páginas que se cargan en memoria para poder despachar el resultado.

Nuestro query queda de la siguiente manera:
SELECT SUM(SOD.LineTotal) AS Total FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID WHERE P.Color = 'Red'

Para obtener la cantidad de páginas que está utilizando nuestra base de datos AdventureWorks en memoria, podemos conectarnos a ella y ejecutar el siguiente query:
SELECT COUNT(*) AS paginas_usadas FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID()

Comenzaremos limpiando la memoria y verificando que no se estén usando páginas por parte de la base de datos AdventureWorks:
USE AdventureWorks GO CHECKPOINT DBCC DROPCLEANBUFFERS GO SELECT COUNT(*) AS paginas_usadas FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID()

Vamos a ejecutar el query que obtiene el total de ventas de los productos de color rojo y verificamos de nueva cuenta la cantidad de páginas utilizadas por la base de datos AdventureWorks:
SELECT SUM(SOD.LineTotal) AS Total FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID WHERE P.Color = 'Red' GO SELECT COUNT(*) AS paginas_usadas FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID()
En mi caso la cantidad de páginas usadas es de 772

Ahora vamos a crear un índice que nos ayude a optimizar el query, éste será creado sobre la columna ProductID e incluirá las columnas UnitPrice, UnitPriceDiscount y OrderQty las cuales son utilizadas para calcular LineTotal. Nuestro código queda de la siguiente manera:
CREATE INDEX ixSProductID ON Sales.SalesOrderDetail (ProductId) INCLUDE (UnitPrice, UnitPriceDiscount, OrderQty)

Volvemos a limpiar la memoria, ejecutamos nuestro query y verificamos la cantidad de páginas utilizadas por la base de datos AdventureWorks:
CHECKPOINT DBCC DROPCLEANBUFFERS GO SELECT SUM(SOD.LineTotal) AS Total FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID WHERE P.Color = 'Red' GO SELECT COUNT(*) AS paginas_usadas FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID()
En mi caso la cantidad de páginas usadas es de 251

Notemos que la memoria necesaria para despachar el query se redujo en un 67.48%. ¿Qué debemos notar?, que los índices no sólo reducen el tiempo que tarda un query en ejecutarse sino también la cantidad de páginas que SQL Server necesita colocar en memoria para despachar el resultado.

Espero les haya resultado de utilidad esta entrada, nos vemos en la siguente.

domingo, 10 de noviembre de 2013

Herencia en javascript


Me he encontrado que es muy común que los programadores que trabajan para el ambiente web tengan la idea de que la herencia en javascript no es posible, pero realmente sí existe, su implementación no es tan evidente como en C# pero sí se puede realizar.

Vamos a crear una clase que se llamará Vehicle de la que heredaremos para crear la clase Car y Boat.

Comencemos por crear un proyecto en Visual Studio de tipo ASP NET Empty Web Site, ahí agregaremos una página llamada Inheritance.htm y comenzaremos abriendo una etiqueta para código javascript.

La clase Vehicle va a tener las propiedades:
  1. Model.
  2. Year.
  3. EngineSize.
También tendrá las siguientes funciones:
  1. IsStarted(), la cual devolverá un booleano indicando si el motor está encendido.
  2. Start(), marcará el motor del vehículo como encendido.
  3. Stop(), marcará el motor del vehículo como apagado.
Comenzaremos por crear la clase Vehicle con sus tres miembros públicos:
function Vehicle(model, year, engineSize) { this.model = model; this.year = year; this.engineSize = engineSize; }
Ahora vamos a agregar un miembro privado que guardará el estado que tiene el motor del vehículo y las funciones que permitirán la administración de ese estado:
function Vehicle(model, year, engineSize) { this.model = model; this.year = year; this.engineSize = engineSize; var engineIsStarted = false; this.isStarted = function() { return engineIsStarted; } this.start = function () { engineIsStarted = true; } this.stop = function () { engineIsStarted = false; } }
Aquí deberemos notar que al declarar engineIsStarted como variable, se convierte en un miembro privado y por lo tanto el que utilice una instancia de nuestra clase Vehicle no podrá manipular de forma directa a esta variable, sólo a través de las funciones que hemos creado.
Hasta ahora hemos creado lo que normalmente se haría para la declaración de una clase con algunos miembros públicos, un miembro privado y algunas funciones.
Para comenzar con la implementación de la herencia, deberemos empotrar el código que llevamos en una IIFE (Immediately Invoked Function Expression), asignarlo a una variable y regresar la instancia de la ejecución de la IIFE.    Nuestro código queda de la siguiente manera:
var Vehicle = (function(){ function Vehicle(model, year, engineSize) { this.model = model; this.year = year; this.engineSize = engineSize; var engineIsStarted = false; this.isStarted = function () { return engineIsStarted; } this.start = function () { engineIsStarted = true; } this.stop = function () { engineIsStarted = false; } } return Vehicle; }());
Ahora vamos a crear nuestra clase Car que heredará de Vehicle y aparte agregará un miembro público llamado wheelCount.    Comenzaremos creando nuestra clase Car como habitualmente lo haríamos, con su constructor recibiendo las cuatro propiedades que va a tener un carro (model, year, engineSize, wheelCount) pero sólo crearemos un miembro público llamado wheelCount dado que los otros tres los heredaremos de Vehicle:
function Car(model, year, engineSize, wheelCount) { this.wheelCount = wheelCount; }
Ahora vamos a empotrar el código en una IIFE tal como lo hicimos con Vehicle:
var Car = (function () { function Car(model, year, engineSize, wheelCount) { this.wheelCount = wheelCount; } return Car; }());
Para implementar la herencia, debemos especificar en la IIFE la clase de la que se va a heredar así como también agregar un parámetro a la IIFE donde se recibirá a la instancia de la clase padre:
var Car = (function (parent) { function Car(model, year, engineSize, wheelCount) { parent.call(this, model, year, engineSize); this.wheelCount = wheelCount; } return Car; }(Vehicle));
Para terminar la implementación debemos modificar el prototipo de la clase Car, el constructor y mandar a llamar al constructor de la clase base:
var Car = (function (parent) { Car.prototype = new Vehicle(); Car.prototype.constructor = Car; function Car(model, year, engineSize, wheelCount) { parent.call(this, model, year, engineSize); this.wheelCount = wheelCount; } return Car; }(Vehicle));
Vamos crear la clase Boat con la diferencia de que las propiedades extras que tendrá son width y length:
var Boat = (function (parent) { Boat.prototype = new Vehicle(); Boat.prototype.constructor = Boat; function Boat(model, year, engineSize, width, length) { parent.call(this, model, year, engineSize); this.width = width; this.length = length; } return Boat; }(Vehicle));
Con esto ya logramos que las clases Car y Boat heredaran de Vehicle, sólo falta probar que realmente funcione y esto lo haremos con otra IIFE:
(function () { var aCar = new Car('Chevy', 2000, 1.6, 4); alert('Car isStarted: ' + aCar.isStarted()); aCar.start(); alert('Car isStarted: ' + aCar.isStarted()); alert('Model: ' + aCar.model + '\nYear: ' + aCar.year + '\nEngine size: ' + aCar.engineSize + '\nWheel count: ' + aCar.wheelCount); var aBoat = new Boat('SL350', 2008, 0.7, 2, 3); alert('Boat isStarted: ' + aBoat.isStarted()); aBoat.start(); alert('Boat isStarted: ' + aBoat.isStarted()); alert('Model: ' + aBoat.model + '\nYear: ' + aBoat.year + '\nEngine size: ' + aBoat.engineSize + '\nWidth: ' + aBoat.width + '\nLength: ' + aBoat.length); }());
Cuando ejecutemos nuestro código veremos que la primera alerta nos dirá que el carro no está encendido, después de mandar a llamar a la función start() y volver a verificar el estado del carro, veremos que la alerta dirá que el carro ya está encendido.    Al mostrar la información con la tercera alerta, veremos que las propiedades fueron correctamente asignadas.

Con la parte del código que prueba la clase Boat el resultado será similar, a diferencia que mostrará Width y Length que son propias de la clase Boat.

¿Cosas que se pueden lograr con esto?, es difícil decirlo porque dependerá mucho de lo que quieres hacer y hasta dónde quieres llegar, pero seguro que ya tendrás una herramienta más para mejorar tu codificación.

Espero te haya resultado útil la entrada, nos vemos en la siguiente.

miércoles, 10 de julio de 2013

SQL Server SARG



En la mayor parte de los proyectos de consultoría en los que he trabajado, me he encontrado que los predicados de las sentencias están mal escritos, o dicho de otra forma no son óptimos.

¿Cuál es el predicado de una sentencia?, es aquel que limita las filas que va a devolver la sentencia.   Para entender mejor este concepto vamos a analizar unos queries sobre la base de datos AdventureWorks.

SELECT ProductId, Name, ListPrice
FROM Production.Product
WHERE Color = 'White'


En este query vamos a obtener los productos que son de color blanco.    Si revisamos un poco su plan de ejecución (Ctrl + L) y colocamos el mouse encima de la operación "Clustered Index Scan" vamos a ver algo como esto:


Si te fijas, el predicado es la parte del WHERE.

Ahora vamos a analizar otro query, uno que nos devuelva todos los productos que pertenezcan a la categoría 2 (Components).

SELECT P.ProductID, P.Name, P.ListPrice
FROM Production.Product P
INNER JOIN Production.ProductSubcategory PS
ON P.ProductSubcategoryID = PS.ProductSubcategoryID
WHERE PS.ProductCategoryID = 2


Vamos a obtener su plan de ejecución y si colocamos el mouse encima de la operación "Clustered Index Scan" sobre el objeto ProductSubcategory.PK_ProductSubcategoryID veremos algo como esto:

 

El predicado de nueva cuenta es la parte del WHERE.

Una vez que hemos entendido este punto, es importante notar que un predicado mal escrito puede llevarnos a no utilizar los índices que se hayan creado para la optimización de las consultas, es decir que nuestros predicados no sean SARG (Search Argument).

¿Cómo reconocer cuando un predicado contiene non-SARG?, es sencillo, cuando una operación se lleva a cabo sobre las columnas de nuestras tablas, ese predicado no será óptimo.

Por ejemplo, supongamos que queremos obtener todos los productos a los que realizando un 10% de descuento vayan a tener un precio menor a 100 y mayor a 0.    El query normal que escribiríamos sería el siguiente:

SELECT ProductId, Name, ListPrice
FROM Production.Product
WHERE ListPrice * 0.9 < 100
AND ListPrice > 0


Si revisamos su plan de ejecución (costo 0.0127757) y colocamos el mouse encima de la operación "Clustered Index Scan" veremos que el siguiente predicado:


Notemos que el predicado está llevando a cabo una conversión de la columna Production.Product.ListPrice a un tipo de dato NUMERIC(19, 4).

Si es un query que se va a estar utilizando mucho entonces lo lógico sería crear un índice que nos permita obtener la información de forma mucho más rápida:

CREATE NONCLUSTERED INDEX ixPProduct_ListPrice
ON Production.Product (ListPrice)
INCLUDE (ProductId, Name)


En este query sencillo SQL Server podrá utilizar el índice pero nuestra sentencia no está totalmente optimizada.    Veamos de nuevo el plan de ejecución (costo 0.0053521) y observemos el predicado:


Ahora la operación que se está realizando es un "Index Seek" que es mucho mejor que un "Clustered Index Scan", pero la conversión que se está realizando sobre la columna Production.Product.ListPrice le está afectando a nuestro query.    Si recordamos nuestras clases de matemáticas veremos que es lo mismo:

ListPrice * 0.9 < 100

que

ListPrice < (100 / 0.9)

La diferencia en SQL radicará que la operación ya no se va a realizar sobre la columna, sino le vamos a dar un valor fijo (111.11).

Modifiquemos nuestro query para que quede de la siguiente manera:

SELECT ProductId, Name, ListPrice
FROM Production.Product
WHERE ListPrice < 111.11
AND ListPrice > 0


Si vemos el plan de ejecución (costo 0.0051023) y revisamos el predicado, veremos que la operación ya no se está realizando sobre cada fila de nuestro índice, mejora que se ve reflejada en el costo del plan de ejecución.


La mejora no es brutal, pero si estuviéramos hablando de grandes cantidades de información, seguro sería significativa dado que dejaríamos de utilizar recursos del servidor para atender esta sentencia.



Ahora veamos un ejemplo más interesante, supongamos que nos están pidiendo los productos que hayan sido vendidos en la primera semana del año 2003.    Con la función DATEPART podemos obtener el número de semana de una fecha pasándole como primer parámetro WEEK, y con la función YEAR podemos obtener el año de una fecha.    Nuestro query pudiera ser escrito de la siguiente manera:

SELECT P.ProductID, P.Name, SOD.UnitPrice
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD
ON P.ProductID = SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE DATEPART(WEEK, SOH.OrderDate) = 1
AND YEAR(SOH.OrderDate) = 2003


Si vemos su plan de ejecución podremos saber que el costo es de 1.38725 y que se está haciendo un barrido de la tabla Sales.SalesOrderHeader.

Vamos a crear un índice en la columna OrderDate de la tabla Sales.SalesOrderHeader para ayudar a que nuestra consulta sea más rápida:

CREATE NONCLUSTERED INDEX ixSSalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader(OrderDate)


Si volvemos a obtener el plan de ejecución de nuestro query (costo 0.91095) veremos que la operación del barrido de la tabla Sales.SalesOrderHeader ha cambiado por un barrido del índice que acabamos de crear.

Una operación Scan sobre un índice no es algo que queramos para nuestros planes de ejecución dado que no se estaría explotando al máximo la funcionalidad de los índices.


No se está realizando un uso óptimo del índice debido a que nuestro criterio de búsqueda no es SARG, estamos llevando a cabo una operación fila a fila sobre la columna OrderDate de nuestra tabla.    ¿Cómo podemos corregirlo?, usando las fechas específicas que cubran el rango de la siguiente manera:

SELECT P.ProductID, P.Name, SOD.UnitPrice
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD
ON P.ProductID = SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE OrderDate >= '20030101'
AND OrderDate <= '20030104'


Si volvemos a revisar el plan de ejecución (costo 0.362978) veremos que el barrido del índice ha cambiado por una búsqueda, es decir un Index Seek, mejora que se ha visto reflejada en el costo de nuestra sentencia:




Como conclusión, cuando no utilizamos SARG no estaremos utilizando las estructuras optimizadas para nuestras consultas o bien le estaremos dando trabajo de más a SQL para poder despachar el resultado.    Hay que evitar en lo posible las operaciones sobre las columnas de la base de datos durante las búsquedas.

Espero esta entrada te sea de mucha utilidad y ayude a que mejores tus consultas.

martes, 9 de julio de 2013

Validando email en SQL



En ocasiones necesitamos verificar que un correo electrónico tenga un formato válido, es decir una bandeja y su dominio respectivos divididos por una arroba.

En esta entrada veremos una forma sumamente simple pero que servirá como punta de lanza para que utilices el potencial que tienen los ensablados del .NET Framework para integrarlos en SQL Server.

Hay que decir y dejar muy claro que no es la panacea para las cosas complicadas de SQL, es simplemente un punto más, una herramienta más que tenemos a la mano para poder utilizar y facilitar nuestro trabajo.

Hacer la función que valide el formato del correo electrónico se podría describir en un proceso muy simple:
  1. Crear la función como estática en .NET.
  2. Crear un objeto ASSEMBLY en nuestra base de datos.
  3. Crear la función que será mapeada hacia un elemento almacenado en el ASSEMBLY.
Creando función en .NET

Vamos a crear un proyecto de tipo Class Library en Visual Studio y eliminaremos el archivo Class1.cs que trae por defecto la plantilla.

Vamos agregar una clase que alojará nuestra función estática que realizará la validación del correo electrónico, el código quedaría de la siguiente manera:

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

namespace EmailValidator
{
    public class Validators
    {
        [SqlFunction]
        public static SqlBoolean ValidateEmail(SqlString email)
        {
            return SqlBoolean.Parse(Regex.IsMatch(email.Value, @"^\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$").ToString());
        }
    }
}


El atributo SqlFunction que está calificando a la función ValidateEmail le hace saber a SQL Server que ahí hay una función que puede utilizarse.

Debemos manejar tipos de dato de SQL Server, es por ello que la función devuelve un valor de tipo SqlBoolean y recibe una cadena del tipo SqlString los cuales están definidos en el espacio de nombres System.Data.SqlTypes.

La clase RegEx está definida en el espacio de nombres System.Text.RegularExpressions y su función IsMatch devuelve un valor booleano indicando que su primer parámetro cumple con el patrón definido en el segundo parámetro.

Antes de continuar al siguiente paso de esta entrada, deberemos anotar la ruta completa para llegar a nuestra función EmailValidator.Validators.ValidateEmail.

Vamos a compilar nuestro proyecto y deberá generar el DLL con nuestra función incluida.

Debemos validar el framework bajo el que está siendo construido nuestro DLL, si va a ser utilizado en SQL Server 2008 deberá estar compilado en el framework 2.0, en caso que vayamos a utilizarlo en SQL Server 2012 puede estar compilado en el framework 4.0

Crear ASSEMBLY en base de datos

Vamos a obtener la ruta completa para llegar al DLL que acabamos de generar y la utilizaremos para importarlo en nuestra base de datos.

Vamos a abrir un nuevo query que utilice la base de datos de usuario de tu elección, en mi caso tengo una base de datos llamada ExpertsExchange.

USE ExpertsExchange
GO
CREATE ASSEMBLY Validators
FROM 'D:\Personal\Blogger\EmailValidator\EmailValidator\bin\Debug\EmailValidator.dll'
WITH PERMISSION_SET = SAFE


Nuestro assembly se va a llamar Validators y tendrá un conjunto de permisos SAFE, ¿qué significa esto?

SAFE (valor por defecto) es el nivel más recomendado dado que no permite que la función o funciones incluidas en el assembly tengan acceso al sistema de archivos, a la red, a las variables de entorno o al registro de windows.

EXTERNAL_ACCESS le permite al assembly utilizar recursos externos como son archivos, red, variables de entorno y registro de windows.

UNSAFE le permite al assembly utilizar recursos externos tal como lo hace EXTERNAL_ACCESS y aparte le permite al assembly ejecutar código no administrado (código no hecho en .NET)

Crear función mapeada

Ahora vamos a crear una función escalar que reciba como parámetro el correo electrónico que se quiere validar y devuelva un BIT que indique si es válido o no.

La parte interesante es que sólo declararemos la firma de la función, el cuerpo del código ya está definido en nuestro assembly.

USE ExpertsExchange
GO
CREATE FUNCTION udfValidateEmail(
    @Email NVARCHAR(255)
)
RETURNS BIT
AS EXTERNAL NAME Validators.[EmailValidator.Validators].ValidateEmail


Notemos que la firma es como la de cualquier función que crearíamos en SQL Server, la diferencia radica en el código que está después de la especificación del tipo de resultado de nuestra función.

Aun cuando un correo electrónico no lleva caracteres extraños, es imprescindible utilizar NVARCHAR dado que las cadenas que maneja .NET son UNICODE y por lo tanto SQL Server envía NVARCHAR a .NET para que éstas sean manejadas.

¿Cómo saber qué poner en external name?, es fácil:

SQLAssemblyName.[FullNamespace.Class].NETFunctionName


Ahora vamos a probar nuestra función con algunos correos electrónicos tanto válidos como no válidos:

USE ExpertsExchange
GO
SELECT dbo.udfValidateEmail(N'valid@mail.com')
SELECT dbo.udfValidateEmail(N'invalid@mail..com')
SELECT dbo.udfValidateEmail(N'invalid@mail')
SELECT dbo.udfValidateEmail(N'valid@mail.com.mx')


Si recibes un mensaje de error indicando que el .NET Framework está deshabilitado es porque SQL Server no acepta la ejecución de código CLR externo por defecto.    Vamos a habilitarlo con el siguiente código:

sp_configure 'clr enabled', 1
GO
RECONFIGURE


Vuelve a probar la función y verás que el primer y cuarto correos son válidos y el segundo y tercero no lo son.

Espero te haya resultado de utilidad esta entrada, la integración de funciones CLR a SQL Server es un tema apasionante debido a la gran diversidad de soluciones que se pueden implementar via .NET, pero ten mucho cuidado, esta no es la solución a todos nuestros problemas, necesitamos hacer pruebas que demuestren que el uso de recursos y la velocidad de respuesta son las deseadas.

jueves, 4 de julio de 2013

Emails a tabla



En esta entrada quiero mostrar una forma para recibir una lista de correos electrónicos divididos por coma o por punto y coma y separarlos en filas independientes en una tabla, una vez desarrollado el algoritmo crearemos la función que podremos utilizar de forma muy simple en cualquier base de datos.

Para entender plenamente el ejercicio creo muy importante el explicar brevemente cómo operan las diferentes funciones para manejo de cadenas que utilizaremos en el código.

REPLACE(string, stringToFind, stringToReplace)

Esta función reemplaza todas las apariciones de una cadena en específico por la cadena que nosotros queramos.     Por ejemplo, supongamos que queremos sustituir todas las letras "a" con acento por "&aacute;", el código sería el siguiente:

SELECT REPLACE('Me salieron ámpulas por subirme al árbol', 'á', '&aacute;')

CHARINDEX(stringToFind, string[, startIndex])

Esta función busca la primera posición de izquierda a derecha en la que encuentre la cadena stringToFind dentro de string.    El tercer parámetro opcional indica el índice a partir del cual queremos comenzar la búsqueda de la cadena stringToFind.    Por ejemplo, queremos obtener el índice de la primera ",":

SELECT CHARINDEX(',', 'Primero, segundo y tercero')

La función devolverá un valor 0 cuando no haya encontrado la cadena que se estaba buscando.

RTRIM(string) y LTRIM(string)

La función RTRIM elimina todos los espacios en blanco que encuentre a la derecha de la cadena string, es decir, todos los espacios en blanco de relleno que pueda tener la cadena, en inglés encontramos que a esto se le llama trailing spaces.

La función LTRIM elimina todos los espacios en blanco que encuentre al inicio de la cadena string.

La combinación de ambas funciones RTRIM(LTRIM(string)) tiene un resultado como el de la función Trim de un objeto String de .NET

Supongamos que queremos eliminar todos los espacios a la izquierda y a la derecha de una palabra:

SELECT RTRIM(LTRIM('    con espacios antes y después    '))

SUBSTRING(string, startIndex, length)

Esta función extrae length caracteres de la cadena string a partir del caracter startIndex.    Por ejemplo, queremos extraer la palabra "penca" de la siguiente frase:

SELECT SUBSTRING('Grabé en la penca de un maguey', 13, 5)

La palabra "penca" comienza en el caracter número 13 y tiene una longitud de 5.

Es importante notar que en SQL Server las cadenas comienzan con el índice 1, no como en .NET donde comienzan en el índice 0.

Bueno, una vez que tenemos estas funciones en mente comencemos con la programación de nuestra función.

Vamos a comenzar por declarar una variable donde se encuentren los correos electrónicos que recibiriemos como parámetro en la función:

DECLARE @Emails VARCHAR(2000) = 'email1@domain1.com, email2@domain2.com; email3@domain3.com '

Como podrás notar el primer y segundo correo están divididos por una coma mientras que el segundo y tercer correo están divididos por un punto y coma.    También es importante ver que hay un espacio en blanco al final de la cadena.

Esta serie de cosas las puse así para tomar en cuenta posibles errores por parte del usuario al momento de hacerle llegar el parámetro a la función.

Vamos a cambiar los ";" por "," y así esté homogéneo el divisor de correos:

SET @Emails = REPLACE(@Emails, ';', ',')

Vamos a necesitar un par de variables, una que lleve la posición actual de análisis de la cadena de correos y otra que tenga la posición donde se encuentra la ",":

DECLARE @CurrentPos INT = 1
DECLARE @CommaPos INT = CHARINDEX(',', @Emails)


Vamos a crear una tabla temporal para ir guardando los correos, esta tabla será declarada como parte de la firma de nuestra función:

DECLARE @Result TABLE (
    Email VARCHAR(255)
)


El algoritmo es sencillo, mientras siga habiendo "," hay más correos por analizar, debido a ello estableceremos un ciclo WHILE:

WHILE @CommaPos > 0
BEGIN
END


Cuando queramos extraer el correo electrónico, deberemos sacar del caracter @CurrentPos hasta el índice donde se encuentra la "," pero sin incluir la ",".    También recordemos que tenemos que quitar cualquier espacio en blanco al inicio o al final del correo:

INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))


Debemos poner especial atención al tercer parámetro, en .NET generalmente le colocaríamos un -1 pero como en SQL Server la posición del primer caracter es 1, no es necesario realizar ese pequeño ajuste.

Vamos a actualizar nuestras variables de navegación @CurrentPos y @CommaPos:

SET @CurrentPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)


Notemos que en este caso estamos utilizando el tercer parámetro de la función CHARINDEX, esto se debe a que la búsqueda de la "," queremos que se realice después de la que ya habíamos encontrado.

Nuestro WHILE quedaría de la siguiente manera:

WHILE @CommaPos > 0
BEGIN
    INSERT INTO @Result (Email)
    VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))

    SET @CurrentPos = @CommaPos + 1
    SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
END


Al salir del WHILE estaremos en condiciones de obtener el último correo con el siguiente código:

INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))


Nuestro código final debe quedar de la siguiente manera:

DECLARE @Emails VARCHAR(2000) = 'email1@domain1.com, email2@domain2.com; email3@domain3.com '
SET @Emails = REPLACE(@Emails, ';', ',')

DECLARE @CurrentPos INT = 1
DECLARE @CommaPos INT = CHARINDEX(',', @Emails)

DECLARE @Result TABLE (
    Email VARCHAR(255)
)

WHILE @CommaPos > 0
BEGIN
    INSERT INTO @Result (Email)
    VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))

    SET @CurrentPos = @CommaPos + 1
    SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
END

INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))

SELECT * FROM @Result


Una vez que lo hayamos probado y verifiquemos que funciona tal como lo esperábamos, vamos a encapsularlo en una función para poder reutilizarlo:

CREATE FUNCTION udfSplitEmails(
    @Emails VARCHAR(2000)
)
RETURNS @Result TABLE(
    Email VARCHAR(255)
)
AS
BEGIN
    SET @Emails = REPLACE(@Emails, ';', ',')

    DECLARE @CurrentPos INT = 1
    DECLARE @CommaPos INT = CHARINDEX(',', @Emails)

    WHILE @CommaPos > 0
    BEGIN
        INSERT INTO @Result (Email)
        VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))

        SET @CurrentPos = @CommaPos + 1
        SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
    END

    INSERT INTO @Result (Email)
    VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))

    RETURN
END


Ahora se podrá utilizar de la siguiente manera:

SELECT * FROM udfSplitEmails('email1@domain1.com, email2@domain2.com; email3@domain3.com ')

Espero te haya resultado de utilidad esta entrada, es una función que se puede incluso integrar en la base de datos model para que forme parte del machote de una base de datos cuando sea creada en el servidor.

miércoles, 12 de junio de 2013

WCF REST Service con JQuery



En esta nueva entrada veremos la forma de exponer un servicio WCF a través de un endpoint que soportará peticiones REST desde un cliente jquery.

¿Por qué un servicio WCF?, recordemos de la implementación del servicio está totalmente separada de la forma de comunicación, esto significa que podremos utilizar la misma implementación para exponer nuestra información a clientes de otras plataformas o a computadoras o servidores que se encuentren en la red interna de nuestro host.    Todo esto sin tener que estar generando nuevas implementaciones.

Es importante notar que utilizaremos la base de datos AdventureWorks, en caso de que no la tengas la puedes obtener de la siguiente dirección: http://msftdbprodsamples.codeplex.com/releases/view/93587

Abriremos el Visual Studio 2010 ejecutándolo como administrador (para evitar el uso de netsh para apartar el puerto) y comenzaremos por crear una solución en blanco llamada WCFJQuery, en esta solución vamos a agregar una biblioteca de clases y la llamaremos AWDB.

AWDB.

En este proyecto vamos a eliminar el archivo Class1.cs que trae por defecto y vamos a agregar un nuevo elemento de tipo ADO.NET Entity Data Model.    Si ya sabes cómo crear un modelo entonces sáltate hasta la creación del siguiente proyecto, la indicación es crear el modelo llamado AW con Production.ProductSubcategory y Production.Product con las entidades pluralizadas, de lo contrario sigue conmigo.

Al nuevo elemento de tipo ADO.NET Entity Data Model vamos a llamarlo AW.edmx, en el asistente elegimos la opción "Generate from database", vamos a crear la conexión hacia AdventureWorks utilizando autenticación de Windows dando clic en el botón "New connection", escribimos el nombre del servidor y elegimos la base de datos AdventureWorks de la lista desplegable, damos clic en "Test connection" y deberá salir un mensaje como el que se ve en la imagen.


Damos clic en Ok para cerrar el mensaje que dice que la conexión fue exitosa y damos clic en Ok para crear la conexión, el nombre de la cadena de conexión será AdventureWorksEntities.    Al dar clic en siguiente nos mostrará la lista de objetos utilizables y deberemos elegir las tablas Production.ProductSubcategory y Production.Product, elegimos la opción "Pluralize or singularize ..." y al modelo lo nombraremos AWModel; la configuración deberá quedar como se ve en la siguiente imagen:


Damos clic en finalizar y ya tenemos nuestro modelo terminado.

AWServices.

Vamos a agregar un proyecto de tipo biblioteca de clases y lo llamaremos AWServices, eliminamos el archivo Class1.cs que trae por defecto.    Agregamos referencia al proyecto AWDB, también tenemos que agregar referencia a System.Data.Entity y System.ServiceModel.Web.

También agregamos un elemento de tipo WCF Service llamado ProductsCatalog y una clase llamada Product que tenga las propiedades públicas Id (int), Name (string) y ListPrice (decimal) siendo todas de lectura-escritura, recuerda hacerla pública, por defecto al agregar una clase la declara como privada.

Ahora agregamos otra clase que se llame Subcategory que tendrá las propiedades públicas Id (int) y Name (string) siendo de lectura-escritura, recuerda hacerla pública.

En IProductsCatalog vamos a eliminar la operación DoWork que aparece por defecto y vamos a agregar una nueva que nos permitirá obtener los productos que pertenezcan a una subcategoría en particular con el siguiente código:

[OperationContract]
List<Product> GetProducts(int subcategoryId);

También vamos a agregar otra operación que nos devolverá la lista de subcategorías que estén registradas en la base de datos con el siguiente código:

[OperationContract]
List<Subcategory> GetSubcategories();

Ya que tenemos el contrato de nuestro servicio, ahora vamos a crear la implementación del mismo en el archivo ProductsCatalog.cs.

Eliminamos el procedimiento DoWork que trae por defecto, damos clic derecho en la interfaz que implementa y elegimos Implement Interface - Implement Interface, esto nos creará los stub necesarios para la implementación de las funciones que tiene la interfaz.

En la función GetSubcategories vamos a proyectar las subcategorías que están en la base de datos hacia una lista de objetos de tipo Subcategory con el siguiente código:

public List<Subcategory> GetSubcategories()
{
    WebOperationContext.Current.OutgoingResponse.Headers.Add("Access-Control-Allow-Origin", "*");
    using (AWDB.AdventureWorksEntities db = new AWDB.AdventureWorksEntities())
    {
        return db.ProductSubcategories.Select(sc => new Subcategory()
        {
            Id = sc.ProductSubcategoryID,
            Name = sc.Name
        }).ToList();
    }
}

En la función GetProducts buscaremos a los productos que pertenecen a la subcategoría cuyo identificador recibimos como parámetro y los proyectamos hacia una lista de objetos de tipo Product con el siguiente código:

public List<Product> GetProducts(int subcategoryId)
{
    WebOperationContext.Current.OutgoingResponse.Headers.Add("Access-Control-Allow-Origin", "*");
    using (AWDB.AdventureWorksEntities db = new AWDB.AdventureWorksEntities())
    {
        return db.Products.Where(p => p.ProductSubcategoryID == subcategoryId).Select(p => new Product()
        {
            Id = p.ProductID,
            Name = p.Name,
            ListPrice = p.ListPrice
        }).ToList();
    }
}

En ambas operaciones encontraremos una línea que agrega una cabecera Access-Control-Allow-Origin con un valor "*", esto es para que pueda ser utilizado desde cualquier dominio.

Hasta ahora nuestro servicio ya está listo para ser alojado y utilizado, pero debemos hacerle unas pequeñas modificaciones o ajustes a la implementación para que soporte REST.    Vamos a agregar el espacio de nombres System.ServiceModel.Web en la implementación del servicio y vamos a agregar el siguiente atributo a la función GetSubcategories:

[WebGet(ResponseFormat = WebMessageFormat.Json, UriTemplate = "Subcategories")]
public List<Subcategory> GetSubcategories()

Con esta configuración el mensaje que se devolverá a los clientes que realicen el request a la dirección Subcategories estará en formato JSON con lo que podremos utilizarlo de forma muy sencilla desde javascript.

También vamos a ajustar la función GetProducts para que reciba solicitudes utilizando REST:

[WebGet(ResponseFormat = WebMessageFormat.Json, UriTemplate = "Products?scid={subcategoryId}")]
public List<Product> GetProducts(int subcategoryId)

En esta configuración podemos notar que estamos agregando una variable por query string que se llamará scid y cuyo valor estará asignado al parámetro subcategoryId que solicita nuestra función.
Compila la solución y corrije cualquier detalle que aparezca en la lista de errores, si seguiste al pie de la letra lo que hemos hecho hasta ahora no deberás tener problema alguno.

AWHost.

Vamos a agregar un proyecto de tipo Aplicación de Consola y lo llamaremos AWHost, en este proyecto se estará alojando nuestro servicio AWServices.

Vamos a agregar referencia a System.ServiceModel y al proyecto AWServices.    Agregamos un nuevo elemento de tipo Application Configuration File y le dejaremos el nombre por defecto que mostrará: app.config, debemos abrir este archivo y pegar la cadena de conexión que tenemos en el proyecto AWDB en su archivo app.config, posteriormente registraremos nuestro servicio ProductsCatalog utilizando el siguiente código:

<system.serviceModel>
    <behaviors>
        <endpointBehaviors>
            <behavior name="HelpEnabled">
                <webHttp helpEnabled="true"/>
            </behavior>
        </endpointBehaviors>
    </behaviors>
    <services>
        <service name="AWServices.ProductsCatalog">
            <endpoint address="" binding="webHttpBinding" contract="AWServices.IProductsCatalog" behaviorConfiguration="HelpEnabled"></endpoint>
            <host>
                <baseAddresses>
                    <add baseAddress="http://JorgeToriz-PC:8001/AWServices"/>
                </baseAddresses>
            </host>
        </service>
    </services>
</system.serviceModel>

Deberás modificar el atributo baseAddress para que haga referencia al nombre de tu equipo, en el código que estoy poniendo hace referencia a mi equipo.

El behavior HelpEnabled permitirá que la dirección http://JorgeToriz-PC:8001/AWServices/help exponga todos los servicios que están a la disposición, en esta descripción aparecerá la forma en que deben llamarse y también los resultados que devolverán las funciones.

En el proyecto necesitamos referencia a System.ServiceModel.Web para poder alojar este servicio REST, para poder hacerlo necesitamos que el proyecto utilice el .NET Framework 4 y no el .NET Framework 4 Client Profile.    Para cambiar esta configuración abriremos las propiedades del proyecto y en el tab Application buscaremos la opción "Target Framework", de la lista desplegable deberemos elegir ".NET Framework 4", el IDE nos mostrará un mensaje de aviso, vamos a darle que "Sí" para que realice los cambios necesarios.

Ahora sí, vamos a agregar referencia a System.ServiceModel.Web y vamos a abrir nuestro archivo Program.cs, en él agregaremos el espacio de nombres System.ServiceModel.Web y escribiremos el siguiente código para poner disponible nuestro servicio:

static void Main(string[] args)
{
    WebServiceHost host = new WebServiceHost(typeof(AWServices.ProductsCatalog));
    host.Open();
    Console.WriteLine("El servicio ya está en ejecución, presiona cualquier tecla para detenerlo");
    Console.ReadKey();
    host.Close();
}

Al ejecutar el proyecto (sin debug) nos deberá mostrar una pantalla como esta:


Si visitamos la URL http://JorgeToriz-PC:8001/AWServices/help (cambiando JorgeToriz-PC por el nombre de tu equipo) deberá mostrar el navegador nuestra lista funciones como en la siguiente imagen:


Si damos clic en Products o Subcategories nos mostará la información que devuelve cada una de las URL.

AWClient.

Vamos a agregar un sitio web vacío a nuestra solución al que llamaremos AWClient, en ese proyecto crearemos una carpeta llamada js donde meteremos el script de jquery, si no lo tienes puedes descargar jquery de esta liga (http://code.jquery.com/jquery-1.10.1.min.js).

Dentro de nuestro sitio web vamos a agregar una página HTML a la que llamaremos ProductsCatalog, enlla agregaremos referencia a jquery y también agregaremos el siguiente código que hace llamadas utilizando REST para poder obtener el catálogo de productos:

<table>
    <tr>
        <td>Subcategories</td>
        <td>
            <select id="ddlSubcategories" onchange="return loadProducts()"></select>
            <script language="javascript" type="text/javascript">
                function loadProducts() {
                    $.ajax({
                        url: 'http://JorgeToriz-PC:8001/AWServices/Products?scid=' + $('#ddlSubcategories').val(),
                        type: 'GET',
                        success: loadProducts_success
                    });
                    return false;
                }
                function loadProducts_success(d) {
                    var products = '<table border="1"><tr><td>Id</td><td>Name</td><td>List price</td></tr>';
                    for (var i = 0; i < d.length; i++) {
                        products += '<tr>';
                        products += '<td>' + d[i].Id + '</td>';
                        products += '<td>' + d[i].Name + '</td>';
                        products += '<td>' + d[i].ListPrice + '</td>';
                        products += '</tr>';
                    }
                    products += '</table';
                    $('#divProducts').html(products);
                }
                function loadSubcategories() {
                    $.ajax({
                        url: 'http://JorgeToriz-PC:8001/AWServices/Subcategories',
                        type: 'GET',
                        success: loadSubcategories_success
                    });
                }
                function loadSubcategories_success(d) {
                    var subcategories = $('#ddlSubcategories');
                    for (var i = 0; i < d.length; i++) {
                        $('<option value="' + d[i].Id + '">' + d[i].Name + '</option>').appendTo(subcategories);
                    }
                    loadProducts();
                    subcategories.focus();
                }
                $(document).ready(loadSubcategories);
            </script>
        </td>
    </tr>
</table>
<div id="divProducts"></div>

Resumen.

Como podemos ver no es tan complicado el exponer un servicio WCF para ser utilizado como REST desde javascript, un punto fino es permitir las llamadas desde otros dominios utilizando la cabecera Access-Control-Allow-Origin en el WebOperationContext actual, mientras que el otro punto importante es elegir bien la estructura de las direcciones para REST y configurarlas en la implementación del servicio utilizando el atributo WebGet.

Espero te haya resultado de utilidad esta entrada.