Ejemplos de operaciones CRUD en SQLite mediante objetos ORM

Hemos estudiado todas las funciones necesarias para la implementación del ciclo de vida completo de la información en la base de datos, es decir, CRUD (Create, Read, Update, Delete). Pero antes de pasar a la práctica, necesitamos completar la capa ORM.

De los apartados anteriores ya se deduce que la unidad de trabajo con la base de datos es un registro: puede ser un registro de una tabla de la base de datos o un elemento de los resultados de una consulta. Para leer un único registro a nivel ORM, introduzcamos la clase DBRow. Cada registro es generado por una consulta SQL, por lo que su manejador se pasa al constructor.

Como sabemos, un registro puede constar de varias columnas, cuyo número y tipos nos permiten encontrar funciones DatabaseColumn. Para exponer esta información a un programa MQL utilizando DBRow, reservamos las variables pertinentes: columns y un array de estructuras DBRowColumn (la última contiene tres campos para almacenar el nombre, tipo y tamaño de la columna).

Además, los objetos de DBRow pueden, si es necesario, almacenar en caché en sí mismos los valores obtenidos de la base de datos. Para ello se utiliza el array data de tipo MqlParam. Como no sabemos de antemano qué tipo de valores habrá en una columna concreta, utilizamos MqlParam como una especie de tipo universal Variant disponible en otros entornos de programación.

La variable cursor realiza un seguimiento del número de registro actual de los resultados de la consulta. Hasta que se complete la solicitud, cursor es igual a -1.

El método virtual DBread se encarga de ejecutar la consulta; llama a DatabaseRead.

Más adelante veremos por qué necesitábamos un método virtual. El método público next, que utiliza DBread, proporciona el «desplazamiento» por los registros de resultados y tiene el siguiente aspecto:

Si se accede a la consulta por primera vez, asignamos memoria y leemos la información de la columna. Si se ha solicitado el almacenamiento en caché, rellenamos adicionalmente el array data. Para ello, se llama al operador sobrecargado '[]' para cada columna. En ella, dependiendo del tipo de valor, llamamos a la función DatabaseColumn adecuada y ponemos el valor resultante en uno u otro campo de la estructura MqlParam.

El método getBlob se proporciona para leer completamente datos binarios de campos BLOB (utilice el tipo uchar como S para obtener un array de bytes si no hay información más específica sobre el formato del contenido).

Para los métodos descritos, el proceso de ejecución de una consulta y de lectura de sus resultados puede representarse mediante el siguiente pseudocódigo (deja entre bastidores las clases DBSQLite y DBQuery existentes, pero pronto las uniremos todas):

No es elegante escribir explícitamente un bucle a través de las columnas en cada ocasión, por lo que la clase proporciona un método para obtener los valores de todos los campos del registro.

Además, la clase recibió por conveniencia sobrecargas del operador '[]' y el método getBlob para leer campos por sus nombres en lugar de índices. Por ejemplo:

De esta manera podrá acceder a las columnas seleccionadas.

No obstante, obtener los elementos del registro individualmente, como un array MqlParam, no puede ser llamado un auténtico enfoque POO. Sería preferible leer el registro de la tabla de la base de datos entera en un objeto, una estructura de aplicación. Recordemos que la API de MQL5 proporciona una función adecuada: DatabaseReadBind. Aquí es donde obtenemos la ventaja de la capacidad de describir una clase derivada DBRow y anular su método virtual DBRead.

Esta clase de DBRowStruct es una plantilla y espera como parámetro S una de las estructuras simples que se permiten enlazar en DatabaseReadBind.

Con una clase derivada, podemos obtener objetos de la base casi sin problemas.

Ahora es el momento de convertir el pseudocódigo en código funcional enlazando DBRow/DBRowStruct con DBQuery. En DBQuery, añadimos un puntero automático al objeto DBRow, que contendrá datos sobre el registro actual a partir de los resultados de la consulta (si se ha ejecutado). El uso de un puntero automático libera al código de llamada de preocuparse por liberar los objetos DBRow: se eliminan con DBQuery o cuando se vuelven a crear debido al reinicio de la consulta (si es necesario). La inicialización del objeto DBRow o DBRowStruct se completa mediante un método de plantilla start.

El tipo DBValue es una estructura ficticia que sólo se necesita para indicar al programa que cree el objeto DBRow subyacente, sin violar la compilabilidad de la línea con la llamada a DatabaseReadBind.

Con el método start, todos los fragmentos de pseudocódigo anteriores pasan a funcionar gracias a la siguiente preparación de la solicitud:

Este ejemplo lee meta-información sobre la configuración de una tabla concreta de la base de datos (la creamos en el ejemplo DBcreateTableFromStruct.mq5 en la sección Ejecutar consultas sin enlace de datos MQL5): cada columna se describe mediante un registro independiente con varios campos (estándar SQLite), que se formaliza en la estructura DBTableColumn.

Para evitar que el usuario tenga que escribir cada vez un bucle con la traducción de los registros de resultados a objetos de estructura, la clase DBQuery proporciona un método de plantilla readAll que rellena un array de estructuras referenciada con información de los resultados de la consulta. Un método similar readAll rellena un array de punteros a objetos DBRow (esto es más adecuado para recibir los resultados de consultas sintéticas con columnas de diferentes tablas).

En un cuarteto de operaciones, el método CRUD DBRowStruct::get es el responsable de la letra R (Read). Para que la lectura de un objeto sea funcionalmente más completa, soportaremos la recuperación puntual de un objeto de la base de datos por su identificador.

La gran mayoría de las tablas en bases de datos SQLite tienen una clave primaria rowid (a menos que el desarrollador por una razón u otra haya utilizado la opción «WITHOUT ROWID» en la descripción), por lo que el nuevo método read tomará un valor de clave como parámetro. Por defecto, se asume que el nombre de la tabla es igual al tipo de la estructura receptora, pero puede cambiarse por otro alternativo a través del parámetro table. Teniendo en cuenta que una solicitud de este tipo es una solicitud de una sola vez y debe devolver un registro, tiene sentido colocar el método read directamente en la clase DBSQLite y gestionar los objetos de vida corta DBQuery y DBRowStruct<S> en su interior.

El trabajo principal lo realiza la consulta SQL «SELECT * FROM '%s' WHERE %s=%ld;», que devuelve un registro con todos los campos de la tabla especificada al coincidir con la clave rowid.

Ahora puede crear un objeto específico de la base de datos de la siguiente manera (se supone que el identificador que nos interesa debe estar almacenado en alguna parte).

Por último, en algunos casos complejos en los que se requiere la máxima flexibilidad en la consulta (por ejemplo, una combinación de varias tablas, normalmente un SELECT con un JOIN, o consultas anidadas), todavía tenemos que permitir un comando SQL explícito para obtener una selección, aunque esto viola el principio ORM. Esta posibilidad se abre con el método DBSQLite::prepare, que ya hemos presentado en el contexto de la gestión de consultas preparadas

Hemos considerado todas las formas principales de lectura.

Sin embargo, aún no tenemos nada que leer de la base de datos, porque nos hemos saltado el paso de añadir registros.

Intentemos implementar la creación de objetos (C). Recordemos que, en nuestro concepto de objeto, los tipos de estructura definen semiautomáticamente tablas de base de datos (mediante macros DB_FIELD). Por ejemplo, la estructura Struct permitía crear una tabla «Struct» en la base de datos con un conjunto de columnas correspondientes a los campos de la estructura. Para ello, hemos incluido un método de plantilla createTable en la clase DBSQLite. Ahora, por analogía, necesita escribir un método de plantilla insert, que añadiría un registro a esta tabla.

Se pasa al método un objeto de una estructura, para cuyo tipo debe existir el array DBEntity<S>::prototype <S> rellenado (se rellena con macros). Gracias a este array, podemos formar una lista de parámetros (más concretamente, sus sustitutos '?n'): esto se hace mediante el método estático qlist. Sin embargo, la preparación de la consulta sigue siendo media batalla. En el código siguiente, tendremos que vincular los datos de entrada en función de las propiedades del objeto.

Se ha añadido una sentencia «RETURNING rowid» al comando «INSERT», de modo que cuando la consulta tiene éxito, esperamos una única fila de resultados con un valor: rowid nuevo.

El código fuente del método insert tiene un punto al que hay que prestar especial atención. Para vincular valores a parámetros de consulta, llamamos al método object.bindAll(q). Esto significa que en la estructura de la aplicación que desea integrar con la base, es necesario implementar un método de este tipo que proporciona todas las variables de miembro para el motor.

Además, para identificar los objetos, se supone que existe un campo con una clave primaria, y sólo el objeto «sabe» cuál es este campo. Así, la estructura dispone del método rowid, que cumple una doble acción: en primer lugar, transfiere al objeto el identificador de registro asignado en la base de datos y, en segundo lugar, permite averiguar este identificador a partir del objeto, si ya ha sido asignado anteriormente.

El método DBSQLite::update (U) para modificar un registro es similar en muchos aspectos a insert, por lo que se propone familiarizarse con él. Su base es la consulta SQL «UPDATE '%s' SET (%s)=(%s) WHERE rowid=%ld;», que se supone que pasa todos los campos de la estructura (objetobindAll()) y la clave (objetorowid()).

Por último, mencionamos que la eliminación puntual (D) de un registro por un objeto se implementa en el método DBSQLite::remove (la palabra delete es un operador MQL5).

Vamos a mostrar todos los métodos en un script de ejemplo DBfillTableFromStructArray.mq5, donde se define la nueva estructura Struct.

Haremos varios valores de tipos de uso común como campos de la estructura.

En el campo de cadena image, el código de llamada especificará el nombre del recurso gráfico o el nombre del archivo, y en el momento de la vinculación a la base de datos, los datos binarios correspondientes se copiarán como BLOB. Posteriormente, cuando leamos datos de la base de datos en objetos Struct, los datos binarios acabarán en la cadena image pero, por supuesto, con distorsiones (porque la línea se romperá en el primer byte nulo). Para extraer con precisión los BLOB de la base de datos, deberá llamar al método DBRow::getBlob (basado en DatabaseColumnBlob).

La creación de metainformación sobre los campos de la estructura Struct proporciona las siguientes macros. Basándose en ellos, un programa MQL puede crear automáticamente una tabla en la base de datos para los objetos Struct, así como iniciar la vinculación de los datos pasados a las consultas basándose en las propiedades de los objetos (esta vinculación no debe confundirse con la vinculación inversa para obtener los resultados de las consultas, es decir, DatabaseReadBind).

Para rellenar un pequeño array de prueba, el script dispone de variables de entrada: especifican un trío de divisas cuyas cotizaciones entrarán en el campo number. También hemos incrustado dos imágenes estándar en el script para probar el trabajo con BLOBs: «irán» al campo image. El campo timestamp será rellenado automáticamente por nuestras clases ORM con la marca de tiempo de modificación o inserción actual del registro. La clave primaria del campo id tendrá que ser rellenada por el propio SQLite.

Dado que los valores de las variables de consulta de entrada (esas mismas '?n') se ligan, en última instancia, mediante las funciones DatabaseBind o DatabaseBindArray bajo los números, nuestra estructura bindAll en el método debe establecer una correspondencia entre los números y sus campos: se supone una numeración simple en el orden de declaración.

El método rowid es muy sencillo.

Una vez definida la estructura, describimos un array de prueba de 4 elementos. Sólo 2 de ellos tienen imágenes adjuntas. Todos los objetos tienen cero identificadores porque aún no están en la base de datos.

En la función principal OnStart, creamos o abrimos una base de datos (por defecto MQL5Book/DB/Example2.sqlite). Por si acaso, intentamos eliminar la tabla «Struct» para garantizar la reproducibilidad de los resultados y la depuración cuando se repita el script, entonces crearemos una tabla para la estructura Struct.

En lugar de añadir objetos de uno en uno, utilizamos un bucle:

En este bucle utilizaremos una implementación alternativa del método insert, que toma como entrada un array de objetos a la vez y los procesa en una única solicitud, lo cual es más eficiente (pero el foso general del método es el método insert considerado anteriormente para un objeto).

Ahora vamos a intentar seleccionar registros de la base de datos según algunas condiciones; por ejemplo, aquellos que no tienen una imagen asignada. Para ello, vamos a preparar una consulta SQL envuelta en el objeto DBQuery, y luego obtendremos sus resultados de dos maneras: a través de la vinculación a estructuras Struct o a través de las instancias de la clase genérica DBRow.

Ambas opciones deberían dar el mismo resultado, aunque presentado de forma diferente (véase el registro más abajo).

A continuación, nuestro script hace una pausa de 1 segundo para que podamos notar los cambios en las marcas de tiempo de las próximas entradas que cambiaremos.

A los objetos del array result[] les asignamos la imagen «yuan.bmp» que se encuentra en la carpeta junto al script. A continuación, actualizamos los objetos en la base de datos.

Después de ejecutar el script, puede asegurarse de que los cuatro registros tienen BLOBs en el navegador de base de datos incorporado en MetaEditor, así como la diferencia en las marcas de tiempo de los dos primeros y los dos últimos registros.

Vamos a demostrar la extracción de datos binarios. Primero veremos cómo se asigna un BLOB al campo de cadena image (los datos binarios no son para el registro, sólo lo hacemos a efectos de demostración).

A continuación, leemos los datos completos con getBlob (la longitud total es mayor que la de la línea anterior).

Necesitamos obtener el archivo temp.bmp.raw, idéntico a MQL5/Files/Images/dollar.bmp.raw, que se crea en el método Struct::bindAll con fines de depuración. Así, es fácil verificar la correspondencia exacta de los datos binarios escritos y leídos.

Tenga en cuenta que, dado que almacenamos el contenido binario del recurso en la base de datos, no se trata de un archivo fuente BMP: los recursos producen la normalización del color y almacenar un array de píxeles sin encabezado con metainformación sobre la imagen.

Mientras se ejecuta, el script genera un registro detallado. En concreto, la creación de una base de datos y una tabla se marca con las siguientes líneas.

La consulta SQL para insertar un array de objetos se prepara una vez y luego se ejecuta varias veces con previnculación de datos diferentes (aquí sólo se muestra una iteración). El número de llamadas a la función DatabaseBind coincide con las variables '?n' de la consulta ('?4' es sustituido automáticamente por nuestras clases con la llamada a la función SQL STRFTIME('%s') para obtener la marca de tiempo UTC actual).

A continuación, un array de estructuras con claves primarias ya asignadas rowid se envía al registro en la primera columna.

La selección de registros sin imágenes da el siguiente resultado (ejecutamos esta consulta dos veces con métodos diferentes: la primera vez rellenamos el array de estructuras Struct, y la segunda es el array DBRow, del que para cada campo obtenemos el «valor» en forma de MqlParam).

La segunda parte del script actualiza un par de registros encontrados sin imágenes y les añade BLOBs.

Por último, al obtener datos binarios de dos formas - incompatible, a través del campo de cadena image como resultado de la lectura de todo el objeto DatabaseReadBind (esto sólo se hace para visualizar la secuencia de bytes en el registro), y compatible, a través de DatabaseRead y DatabaseColumnBlob - obtenemos resultados diferentes: por supuesto, el segundo método es correcto, pues se restablecen la longitud y el contenido del BLOB en 4096 bytes.

Resumiendo el resultado intermedio del desarrollo de nuestro propio envoltorio ORM, presentamos un esquema generalizado de sus clases.

Diagrama de Clases ORM (MQL5<->SQL)