MySQL en notacion JSON y tablas clave-valor

MySQL en notacion JSON y tablas clave-valor

Amo MySQL!! vivaaaaa MySQL este pequeno manejador de bases de datos relacionales a agregado mejoras al lenguaje de consulta SQL, si bien se sale del estadar ANSI, lo hace de una forma genial, nos ahorra trabajo.

Fuentes:

mysql to json
http://www.thomasfrank.se/mysql_to_json.html

GROUP_CONCAT useful GROUP BY extension:

http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/

Objetivo: explorar la posibilidad del uso de tablas clave-valor en base de datos relacionales.

Me he estado rompiendo la cabeza con esto desde hace un par de día, las tablas de las bases de datos relacionales por lo general mantienen un numero de campo fijos y crecen de forma vertical al agregar nuevos registros. (Aunque no hay nada que impida crecer horizontalmente agregando nuevos campos)

Encontre un articulo de Thomas Frank que habla de una forma muy rápida de transformar registros de una tabla de mysql a la notacion de JSON únicamente usando el lenguaje SQL, al principio no lo relacione, pero de pronto una posibilidad surgio en mi mente.

Simple, una tabla clave-valor podría ser agrupada por id y todos el grupo de registros concadenados con group_concat, de tal forma que produzca una lista de clave:valor separados por comas PERO esto solo me abre la posibilidad de guardar y recuperar un objeto JSON quedando un vació en las búsquedas y las comparaciones que ya son solucionadas en el modelo relacional puro.

Si, lo se, esto se puede modelar de otra manera puramente relacional, teniendo una tabla de objetos con todos los campos comunes y por cada clase  de objeto otra tabla especializada adicional, así, que si se tienen 20 clases distintas de objetos habrá por lo menos unas 21 tablas en la base de datos, la mayor ventaja de esta solución es  que todo funciona como se espera en el propio modelo relacional. El ejercicio de hacer una forma alternativa  es satisfacer mi curiosidad intelectual, una solución mas adecuada es usar directamente una base de datos clave-valor, un directorio LDAP ,por ejemplo, ya es diseñado para crecer tanto vertical como horizontalmente y están basados en motores de base de datos clave-valor.

Ejemplo

id	clave	valor
1	producto	medias
1	color	blancas
1	precio	1$

Como se aprecia la tabla crece verticalmente como una tabla relacional ordinaria, no obstante, representan atributos de un objeto, todos los tipos de objetos pueden estar en la misma tabla, no es necesario llenar todos los atributos y se pueden agregar cuantos se necesiten (creciendo en forma horizontal) en fin, tienen varias ventaja de una base de datos clave-valor (respetando las diferencias)

Hay varias desventajas, en primer lugar cuando se desea hacer una búsqueda filtrando por el valor de los atributos solo se puede hacer facilmente con disyunciones:

 (clave="producto" and valor="medias") or (clave="color" and valor="blancas")

busco un objeto donde un atributo o el otro, o ambos, coinciden, pero, una conjunción no es posible de esta forma,  debido a que un and solo funciona para campos dentro de un mismo registro.

Es aquí donde nos es útil agrupar los registros y colocar las condiciones en el having

SELECT id,  group_concat(concat(clave,":"),valor) AS objecto
FROM objetos
GROUP BY id  HAVING objeto like "%precio:1%" and objeto like "%producto:medias%"

Existen otras alternativas, pero esta es la mas rebuscada =) produce una lista separada por comas:

1,    producto: medias, color:blancas, precio:1$

Otra desventaja que salta a la vista son los tipo de datos, todas las operaciones en este caso son sobre cadenas, sin embargo, a medida que leo otros blogs aparecen nuevas ideas que apuntas a que es posible una implementacion  mixta clave-valor-relacional con un uso practico.

-----------------
Bueno ha pasado algunos días y ciertamente al seguir tercamente usando este método surgen mas ideas, por ejemplo, una da las desventajas era el no poder usar conjunciones entre atributos, pero note que sucede exactamente lo mismo cuando se usa un indice FullText y en compensación las búsquedas con Match/Against producen un nivel de relevancia por el cual ordenar los registros encontrados, y nadie se queja de eso.

Salta a la vista que la tabla clave-valor podría usarse como un indice, también encontré forma de ordenar por nivel de relevancia, así que hacer búsquedas ya no es un dilema.

Bien, pero que hacer con los tipos de dato, pues lo mismo que hacen algunos interpretes dinamicamente tipados =) colocar varios campos 'valor' cada uno de un tipo distinto, de forma tal que el valor de un atributo pueda ser colocado en uno de esos campo (o en varios, quien dice que no).

id	clave	valor_text	valor_real	valor_date
1	producto	medias	(null)	(null)
1	color	blancas	(null)	(null)
1	precio	1$	1.0	(null)

Se podría agregar un campo llamado tipo donde es indicado el tipo de dato o bien inferirlo a partir de que campo esta lleno o no.

Notas sobre lenguajes de programación

Estas notas sobre lenguajes de programación pueden llegar a ser muy diversas y me gustaría que los lectores del tema mandaran sus propias notas con el objetivo de ver actuar distintos lenguajes sobre un problema especifico.

1. Problema: Invertir el orden de los campos de una cadena separada por punto (.)
    Caso: podemos imaginarnos un servicio DHCP que ejecuta un shell script para actualizar dinamicamente el DNS.  El hostname y dirección IP son pasados como argumentos al shell script. Se requiere invertir el orden de la direccion IP para llenar el registro inverso del DNS.

Solución con awk:

echo "192.168.0.1"| awk -F "." '{print $4"."$3"."$2"."$1 }'

Solución con el mandato read nativo de shell script

IFS="."; echo 192.168.0.1|read a b c d;  echo "$d.$c.$b.$a"

En ambos casos se define el separador de campo y el orden es establecido conforme lo necesario, la solución usando read se ejecuta en la mitad del tiempo (0.002seg)  que la de awk.

Es notable que la cantidad de caracteres utilizados para escribir ambas lineas de código es casi la misma, siendo la de read apenas mas corta, la semántica es idéntica  y pese a las diferencias en la sintaxis, en composición son perfectamente análogas.

Llama la atención que la existencia de la función read dentro del shell script parece equipararlo con awk, al menos en este caso.

2. Problema: Convertir una serie de registro de una base de datos a la notacion de JSON únicamente con SQL

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users

Resultando en:

 [  {username:'mike',email:'mike@mikesplace.com'},
     {username:'jane',email:'jane@bigcompany.com'},
     {username:'stan',email:'stan@stanford.com'}  
]

En un lenguaje de propósito general bastaría o se tendría que usar variables auxiliares para acomular los registros previamente formateados.