Generador de sentencias básicas SQL

Muchas veces le he preguntado a algunos compañeros y también he visto en diferentes foros a personas haciendo preguntas como:
¿Como generar sentencias SQL automáticamente?
¿Como extraer/utilizar el generador automático de sentencias SQL en phpmyadmin?
¿Alguien sabe como crear 'automágicamente' las sentencias SQL básicas?
....

Y la lista sigue. Últimamente he visto una baja en la tendencia a preguntar esto y más por que seguro las personas se resignan a digitar palabra por palabra estas sentencias, pero para esto ya hice mi propia solución, la cual me tomó alrededor de 3 horas, pero ya tiene las funciones básicas necesarias.


La primera solución que se le viene a casi todos a la cabeza, es utilizar el código del PMA. Vamos! es open source y es reutilizable.

El problema es que muy pocos están dispuestos a estudiar este código, su documentación y extraer los scripts necesarios, que a la larga cuentan con una buena cantidad de archivos que en nuestro proyecto no tendrían mucho uso por que sencillamente a la hora de depurarlos y quitarle el código extra queda un archivo de 2 o 3 líneas, el cual no parece que valga la pena tenerlo a parte, pero 3 días después de estar estudiando el código de PMA, y haberle encontrado el truco, nos damos cuenta que ese archivo si era importante, toca reestudiarlo y para una frustración un poco más marcada, tiene desde funciones lambda (escuetamente implementadas en php) hasta llamadas recursivas desde archivos a otros archivos y toca casi volver a empezar todo desde cero (0). Si me ha pasado, al principio, no estudiaba el código y borraba lo que no necesitaba, luego me daba cuenta que por un par de líneas de ese archivo me ahorraba hasta 3 métodos en otra clase. (Pinches funciones recursivas).

Una solución que me dieron fue que revisara la base de datos "information_schema" que viene con phpmyadmin, y exactamente eso hizo que se me iluminaran los ojos de alegría y me ruborizara por unos instantes, por que cuando empecé a analizar la estructura de todas y cada una de esas tablas, me dí cuenta que era una "meta base de datos", o sea una base de datos con información sobre las otras bases de datos. :D

Si se revisa cuidadosamente cada una de las tablas, las cuales por su nombre dejan ver que contienen claramente, uno se da cuenta que tiene toda la información necesaria para empezar con esta ardua tarea. Por ejemplo, están las tablas de:

CHARACTER_SETS: Conjuntos de caracteres
COLUMNS: Exactamente eso, información sobre las columnas de todas las bases de datos a las que phpmyadmin tenga acceso
SCHEMATA: Información básica sobre cada base de datos
TABLES: Información sobre todas las tablas de cada una de las bases de datos.
TABLE_CONSTRAINTS: Información sobre las relaciones de las tablas de todas las bases de datos, específicamente las que usan el motor innoDB.

Es cuando uno se da cuenta que toda la información es aprovechable. Usar este recurso en nuestro proyecto para la universidad o para la intranet de la empresa, no tiene pierde, ya que generalmente y no solo con PMA, si no con otros gestores visuales escritos en cualquier otro lenguaje, el programador tiene absoluto control sobre la información, las configuraciones de las aplicaciones y servicios, además de tener incluso un método para conectarse remotamente en el caso que necesite hacer reparaciones con scripts personalizados (PHP, python, java, etc), los cuales hacen uso de funciones y privilegios disponibles solo para los administradores del sistema.

El problema es cuando se implementa nuestra aplicación en un servidor ajeno en donde todos estos privilegios se ven notablemente reducidos, y que solamente los tendríamos pagando por un servidor dedicado ya sea este virtual o no, en cuyo caso las bases de datos del tipo "information_schema" de PMA, estan fuera del alcance del administrador de la aplicación, por obvias razones. 3:) Jajajajajajaja.

Por ello me tomo la molestia de obsequiarles los siguiente:

CONTENIDO

Archivo 1: Clase singleton para la base de datos
Explicación:
Una implementación del patrón de diseño singleton para bases de datos. El patrón de diseño singleton, nos permite crear una y solo una instancia de la clase en cuestión, permitiendo de este modo tener control sobre el objeto recién creado para evitar que
múltiples instanciaciones generen un conflicto. Yo se que la naturaleza "sin estado" (stateless) de la web, no permite utilizar
este patrón efectivamente, sin añadir un par de líneas de código más (con "par" me refiero a unas doscientas divididas en 4 archivos ;) ). Lo cual espero hacer pronto.

Singleton (Wikipedia)
Singleton (php Senior)
Implementando singleton (Cristalab)
Para los fanseses de otros lenguajes (Programando en .net.  Lo sé, no es que sea muy software libre, pero explican bien)

El problema de esto es que es muy difícil encontrar información al respecto, ya que solo está en internet. Que problemita, ¿no? ;)
No está de más aclarar que este patrón de diseño se PUEDE IMPLEMENTAR EN CASI CUALQUIER lenguaje de programación y no he dicho en ningún momento que solo funcione en PHP. Ya me hicieron un comentario extraño por ahí hace un par de días, y tiene que ver con que C y Javascript no tienen clases. En javascript todo es un objeto, y se pueden utilizar algunos enfoques para implementar el patrón singleton como la unión de un closure + declaración de objeto literal.

En la singleton que se provee con esta publicación, queda a su consieración cambiar los valores de los parámetros para la conexión con la base de datos. Una buena práctica en producción es crear un usuario "editor" y darle privilegios limitados con respecto a las tablas, para así utilizar esos datos en nuestros scripts php.

Archivo 2: Script generador de sentencias SQL
Explicación:

No voy a entrar en mucho detalle de como funciona, igual para eso tienen el código que intenté hacerlo lo más legible posible. Una de las razones por las que hago un foreach para casi cada variable, es por que no soy partidario de tener más de 3 niveles o 4 (en casos totalmente necesarios) de sangrado, teniendo en cuenta que aplica solo para las sentencias que conforman el código dentro de funciones y métodos, permitiendo así extender la profundidad hasta 5 o 6 niveles si el código se encuentra dentro de una función o clase.

Es recomendable por lo menos utilizar la clase mysqli, ya que el uso de la extensión mysql está desaconsejado.
En el constructor de la clase Database, se recibe el nombre de la base de datos a utilizar en el último parámetro, y de este depende que datos van a ver a la hora de utilizar este archivo.

Se utiliza la sentencia 'SHOW TABLES' para obtener la estructura principal de la base de datos.
Luego se recogen los nombres de las tablas en un array, para luego iterar sobre este y hacer más fácil el uso de estos valores, con los cuales el objetivo principal es obtener la descripción de las tablas con la sentencia 'DESCRIBE tbl';

Luego se obtiene un array por cada campo de la tabla, y dependiendo del valor entregado por el campo 'Type', se escoge un método de manipulación de datos. Aquí se envian los valores guardándolos en una variable con el mismo nombre del campo, se recomienda utilizar una clase con la cual a la hora de crear los objetos, se validen los datos de entrada, para así, no sobrecargar las funciones que van a contener el modelo sql de manipulación de datos.

Aquí también falta que se reconozcan algunos tipos de MySql,  como el timestamp, real, blob, point y otros que se usan de acuerdo al criterio y necesidad de cada proyecto, lo cual se puede solucionar agregando los valores necesarios en el patrón de la expresión regular usado en las funciones preg_match.  Tengan en cuenta que al agregar por ejemplo el valor 'int' dentro de la expresión regular, esta la reconoce en cadenas de texto compuestas como bigint, tinyint, mediumint, etc.  Por otro lado en el caso de los tipos bool y boolean, son alias del tipo tinyint(1), y puede que existan otros.

Por otro lado, les aconsejo que a la hora de enviar los datos a la función, lo hagan por medio de un array asociativo, de este modo se evitan tener que poner en orden todas y cada una de las variables. Un pequeño tip: Crear un método de la clase respectiva, que devuelva ese array, lo cual depende de las necesidades de cada proyecto, ya que algunas clases contienen campos extras de datos derivados, que no se guardan en las tablas pero que si son necesario calcularlos una sola vez y almacenarlos dentro de cada objeto respectivamente, ahorrando así algo de tiempo de ejecución, como por ejemplo, el cálculo del total de los detalles en una factura.

En las consultas creadas por medio de este script, aconsejo el uso de la función sprintf, para cargar los datos externos (ingresados por el usuario o provenientes de otra fuente que no sea uds) en la consulta, y también el uso de la función de escapado de caracteres especiales:
$mysqli->real_escape_string($string);

En la línea 49 del archivo 'prueba.php', pueden cambiar el número del índice, para escoger la tabla de la cual quieren que se generen las consultas. Ese número lo pueden escoger al ejecutar el script y ver la lista creada en el navegador, el cual tiene predeterminada la primera tabla obtenida en el array $tables para evitar algún posible error.

Con respecto a los insert, en la mayoría, el índice primario de algunas tablas se genera creando un campo AUTO_INCREMENT, por lo tanto pueden escoger ese campo en la respectiva consulta, cambiar el especificador de formato por el valor NULL, en el primer parámetro recibido por la función sprintf (la consulta sql con los especificadores %d, %s, %f), y seguidamente elimina el parámetro correspondiente en la lista que le sigue.

Si tienen una base de datos no muy grande o se arriesgan pueden generar todas las sentenciasde todas las tablas, con la penúltima sección comentada entre las líneas 97 y 151.

Finalmente aclaro que este script se puede mejorar de muchas maneras, lo pueden utilizar para crear un archivo de configuraciones, o incluso para crear su propia base de datos parecida a la que expliqué al inicio de este artículo. El método en sí debería ser aplicable en casi cualquier lenguaje de programación del lado del servidor.  También se pueden utilizar plantillas y otras técnicas como en php o C++

PD: Si se preguntan que onda con la forma de programar, pueden encontrar respuestas a esto
aquí => http://pear.php.net/manual/en/standards.php
y aquí => http://framework.zend.com/manual/1.12/en/coding-standard.html

Ojalá les sirva de algo.

Enlace: Generador básico sql