Simulación de mercado (Parte 24): Iniciando SQL (VII)

Introducción

En el artículo anterior, Simulación de mercado (Parte 23): Iniciando SQL (VI), se explicó una serie de pequeñas cosas que tú necesitas entender sobre SQL. Esto, para que consigas trabajar mínimamente con bases de datos. Pues bien, como todavía tenemos mucho de qué hablar y el asunto será bastante denso, no vamos a perder tiempo con introducciones. Vamos directo a lo que interesa.

Entender el problema

Las bases de datos no son más que un conjunto de registros en un archivo. Bien, pero, para entender lo que pretendo explicar aquí, vamos a usar el siguiente código:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL
17. );
18. 
19. INSERT INTO tb_Symbols (id, symbol) VALUES
20.     (2, 'PETR4'),
21.     (1, 'ITUB3'),
22.     (3, 'VALE3');
23. 
24. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
25.     ('2023-07-10', '22.00', 1),
26.     ('2023-07-11', '22.20', 1),
27.     ('2023-07-12', '22.40', 1),
28.     ('2023-07-13', '22.30', 1),
29.     ('2023-07-14', '22.60', 1),
30.     ('2023-07-10', '26.00', 2),
31.     ('2023-07-11', '26.20', 2),
32.     ('2023-07-12', '26.40', 2),
33.     ('2023-07-13', '26.30', 2),
34.     ('2023-07-14', '26.60', 2),
35.     ('2023-07-10', '62.00', 3),
36.     ('2023-07-11', '62.20', 3),
37.     ('2023-07-12', '62.40', 3),
38.     ('2023-07-13', '62.30', 3),
39.     ('2023-07-14', '62.60', 3);
40. 
41. SELECT tq.of_day AS 'Data da cotação',
42.        tq.price AS 'Preço Atual',
43.        ts.symbol AS 'Nome do Ativo'
44.     FROM tb_Quotes AS tq, tb_Symbols AS ts
45.     WHERE tq.fk_id = ts.id
46.     ORDER BY price DESC;

Código 01

Este código 01 no debería ser una novedad para ti. Esto se debe a que todo lo que se usa en él fue explicado en los artículos anteriores. Así, al ejecutarlo, tendremos como respuesta lo que se ve en la siguiente imagen.

Imagen 01

Bien, esta imagen muestra lo que se esperaba ver, por el simple hecho de que hemos creado tres símbolos y colocado cinco datos de cotización en cada uno, totalizando así 15 registros. Pero ahora vamos al problema. Y este surge, principalmente, cuando intentas eliminar algo de la base de datos. Pero ¿por qué empezaremos a tener problemas al eliminar registros de la base? No tiene mucho sentido. De hecho, en principio no lo tiene. Sin embargo, hay una cuestión que aparece cuando usamos bases o tablas relacionadas y que no ocurre cuando usamos otro esquema de construcción de la base. Uno de estos problemas puede verse al ejecutar lo que se muestra a continuación.

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. 
3. SELECT tq.of_day AS 'Data da cotação',
4.        tq.price AS 'Preço Atual',
5.        ts.symbol AS 'Nome do Ativo'
6.     FROM tb_Quotes AS tq, tb_Symbols AS ts
7.     WHERE tq.fk_id = ts.id
8.     ORDER BY price DESC;

Código 02

Puedes pensar: Bien, pero ¿qué tiene de malo hacer esto? Mi objetivo era precisamente borrar todos los registros de PETR4. De hecho, mi querido lector, al ejecutar la línea uno, SQL eliminará los registros de PETR4. Tanto es así que, cuando se ejecuta el comando SELECT de la línea 3, tenemos el siguiente resultado:

Imagen 02

Esta imagen prueba que el comando de la línea 1 funcionó. Pero ¿sabes realmente qué hizo el comando de la línea 1? Y más aún: ¿realmente sabes qué ocurrió dentro de la base de datos? Muchos dirían: Claro que sé lo que pasó. Pues bien, mi querido lector, entonces quiero que me respondas lo siguiente: ¿El código 01 crea o no una base de datos relacional? Si es así, ¿dónde se establece esa relación? Si la respuesta es no, ¿cómo podemos garantizar esa relación? Entiendo que muchos creen que saben manejar bases de datos solo porque saben escribir algunos comandos en SQL. Pero esto no es tan simple como parece. Y tampoco es tan complicada como muchos quieren hacerla parecer. De cualquier forma, quiero que entiendas una cosa:

El código 01 NO CREA UNA BASE DE DATOS RELACIONAL.

Claro que lo crea. Lo veo cuando declaro una clave foránea dentro de la tabla y la uso para referenciar datos de otra tabla. ¿Cómo que la base no es relacional? ¿Te volviste loco de una vez? No, mi querido lector. Voy a demostrarte que la base no es relacional. Mira la siguiente imagen.

Imagen 03

Aunque no veamos las 30 combinaciones posibles, puedes notar que no tenemos ninguna referencia al símbolo PETR4. Sin embargo, el fk_id con valor 2 sí aparece, pero ese valor no figura en el id de los símbolos. Esto hace que la base de datos sea más propensa a presentar fallos de consistencia con el paso del tiempo, y el motivo se explicará dentro de poco. Pero ahora vamos a entender qué fue lo que, de hecho, hizo el comando DELETE en la base de datos. Como la base no es relacional, ambas tablas son independientes entre sí y solo mantienen una referencia simple entre los datos.

Este esquema tiene sus ventajas, aunque no garantiza que estemos, de hecho, creando una base de datos relacional. Y ahora vamos a entender el verdadero motivo. Como sabemos, el id 2 está libre. Esto se debe, simplemente, a que basta con mirar la tabla tb_Symbols, como puedes ver a continuación.

Imagen 04

Sabiendo esto, decides colocar otro símbolo en ese id libre, por ejemplo, BBDC4. Y esto se hace usando lo que se muestra a continuación.

Imagen 05

Pregunta: ¿Hay algo de malo en hacer esto? Bien, en principio no, ya que el id 2 estaba, de hecho, libre. Pero ahora viene el problema. Cuando empieces a agregar datos en la tabla tb_Quotes, tendrás valores no válidos dentro de tu base. Y es muy probable que solo llegues a notarlo después de bastante tiempo insertando datos en la base. Y, cuanto más tiempo pase, menor será la probabilidad de que notes que algo está mal. Pero supongamos que, por algún motivo que solo DIOS sabría explicar, tú, justo después de agregar BBDC4 en la tabla tb_Symbols, decides hacer una consulta en la base, como se muestra en la siguiente imagen.

Imagen 06

Un momento. ¿Cómo es eso? Acabo de agregar BBDC4 en la base, ¿y ya contiene información? Sí, mi querido lector. Y ese es el peligro de hacer las cosas creyendo que sabes lo que estás haciendo. Hacer las cosas con inocencia, o con la confianza de que ya lo sabes, termina generando este tipo de problema, que muchas veces solo aparece después de mucho tiempo. Entonces, ya no tienes forma de validar lo que está presente en la base de datos. Para resolver esto, tendríamos que cambiar la forma en que se está construyendo la base, para que exista, de hecho, una relación entre una clave primaria y una clave foránea. Así, ese mismo código 01 debería hacerse como se muestra a continuación.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. INSERT INTO tb_Symbols (id, symbol) VALUES
21.     (2, 'PETR4'),
22.     (1, 'ITUB3'),
23.     (3, 'VALE3');
24. 
25. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
26.     ('2023-07-10', '22.00', 1),
27.     ('2023-07-11', '22.20', 1),
28.     ('2023-07-12', '22.40', 1),
29.     ('2023-07-13', '22.30', 1),
30.     ('2023-07-14', '22.60', 1),
31.     ('2023-07-10', '26.00', 2),
32.     ('2023-07-11', '26.20', 2),
33.     ('2023-07-12', '26.40', 2),
34.     ('2023-07-13', '26.30', 2),
35.     ('2023-07-14', '26.60', 2),
36.     ('2023-07-10', '62.00', 3),
37.     ('2023-07-11', '62.20', 3),
38.     ('2023-07-12', '62.40', 3),
39.     ('2023-07-13', '62.30', 3),
40.     ('2023-07-14', '62.60', 3);
41.     
42. SELECT tq.of_day AS 'Data da cotação',
43.        tq.price AS 'Preço Atual',
44.        ts.symbol AS 'Nome do Ativo'
45.     FROM tb_Quotes AS tq, tb_Symbols AS ts
46.     WHERE tq.fk_id = ts.id
47.     ORDER BY price DESC;

Código 03

Observa que la única diferencia entre los scripts es la línea 17. Justamente ahí estamos garantizando que las tablas, de hecho, queden relacionadas entre sí. Hum, pero, si la solución es algo tan simple, ¿por qué no crear las cosas como se hace en este código 03? Bien, mi querido lector, el problema es que, al crear las cosas como ves en este código 03, terminamos haciendo más difícil eliminar registros de la base de datos. Para separar los asuntos, pasemos a un nuevo tema.

Eliminando registros en tablas relacionadas

Para entender, de hecho, por qué buena parte prefiere usar el código 01 en lugar del código 03 para crear las bases de datos, vamos a intentar hacer lo mismo que hicimos antes. Es decir, vamos a eliminar el símbolo PETR4 y, en su lugar, poner el símbolo BBDC4. Entonces, podrías intentar hacerlo usando lo que se muestra a continuación.

Imagen 07

Observa que, en esta imagen 07, el punto destacado está intentando eliminar PETR4 de la tabla tb_Symbols. Pero SQL nos informa que esto no será posible. Esto se debe a que, ahora, la base se creó usando el código 03. Muchos terminan irritándose con SQL y buscan una solución, y la más fácil es precisamente usar el código 01, lo que en algún momento va a generar problemas. Muy bien, lo que se muestra como respuesta de SQL a la solicitud, como puedes ver en la imagen 07, está muy lejos de ser un fallo. Esto indica que, de hecho, existe una relación entre los registros de las tablas tb_Symbols y tb_Quotes, que impide que algo se elimine de forma arbitraria.

Para saber dónde se establece la relación, y en muchos casos necesitas entender esto, tendrás que recurrir al diagrama de la base de datos, algo que vimos en el artículo anterior. Pero, como aquí solo tenemos dos tablas, esto resulta fácil de comprender. Así que podemos ir directo al punto.

Imagen 08

Y, para que no te queden dudas, el código de la imagen 08 se muestra a continuación.

01. DELETE FROM tb_Quotes 
02.     WHERE fk_id = (SELECT ts.id FROM tb_Symbols AS ts WHERE ts.symbol = 'PETR4');
03. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
04. 
05. SELECT tq.of_day AS 'Data da cotação',
06.        tq.price AS 'Preço Atual',
07.        ts.symbol AS 'Nome do Ativo'
08.     FROM tb_Quotes AS tq, tb_Symbols AS ts
09.     WHERE tq.fk_id = ts.id
10.     ORDER BY price DESC;

Código 04

Visto así, parece que estamos complicando las cosas. Pero, en realidad, las estamos haciendo más seguras. Observa que, en la línea uno de este código 04, le estamos diciendo a SQL que elimine algo de la tabla tb_Quotes. Ya en la línea dos, le decimos qué es lo que debe eliminar. Observa que la forma de interpretar esto es: SQL, ve a la tabla tb_Quotes y elimina todos los registros del símbolo PETR4 y, después, ve a la tabla tb_Symbols y elimina el registro del símbolo PETR4. Visto así, parece una exageración y que esto no va a funcionar. Pero mira el resultado cuando ejecutamos una consulta en la tabla tb_Quotes.

Imagen 09

Vaya. ¿Será que todo ocurrió como se esperaba? Vamos a intentar ahora agregar BBDC4 y, justo después, ver qué hay en la base de datos. Esto puede verse en la siguiente imagen.

Imagen 10

Puedes notar fácilmente que, en la zona marcada de la imagen 10, no tenemos el fk_id igual al id de BBDC4. Pero hacer una lectura así, en una base grande, puede resultar bastante confuso. Pero la idea no es centrarse en la consulta en sí, sino intentar entender qué hay dentro de la base de datos. Pero, si hacemos una consulta relacional, que sería lo que haríamos en la práctica, el resultado sería lo que se muestra en la siguiente imagen.

Imagen 11

Bien, aparentemente, no se muestra nada aquí. Pero ese sería, de hecho, el objetivo. En la zona en AZUL, tenemos lo que SQL mostrará como resultado de la consulta. En la zona VERDE, podemos visualizar el comando que se ejecutó. Por lo tanto, SQL no encontró ninguna ocurrencia del id de BBDC4 en el antiguo id de PETR4. Es decir, la base de datos está íntegra.

Muy bien, esto fue divertido, pero ahora vamos a hablar de un tema aún más divertido. Pero haremos esto en un nuevo tema.

Usando gatillos

Lo que veremos ahora solo deberá utilizarse si estás seguro de lo que estás haciendo. No intentes usar esto antes de entender lo que se vio en los temas anteriores. De lo contrario, terminarás metido en un enorme problema en SQL. Bien, hecha la advertencia, veamos de qué se trata. Supongamos que existe una base de datos muy compleja, en términos de estructura de tablas. Y esta base utiliza una relación muy específica entre esas tablas. Como viste en el tema anterior, si intentas eliminar un registro a partir de su clave primaria, y esta está siendo referenciada en otra tabla por medio de la clave foránea, no podrás eliminar el registro, a menos que primero elimines todas las referencias existentes a esa clave primaria.

La forma de hacer esto se explicó en el tema anterior. Cuando tenemos pocas tablas, hacer algo así es bastante simple y directo. Pero, si tenemos muchas tablas, la cosa empieza a complicarse, ya que, para cada tabla, tendrás que crear un comando DELETE.

La idea del gatillo es, simplemente, simplificar este tipo de tarea, de modo que, en lugar de usar varios comandos DELETE, pasarás a usar solo uno. Parece extraño, o incluso parece fácil. Y realmente es fácil hacerlo, mi querido lector. Pero existe un problema. Cada implementación de SQL maneja los gatillos de manera diferente. Por ejemplo, un código SQL orientado a usar SQLite puede tener un mecanismo de gatillo diferente del de un código similar en SQL Server o incluso MySQL. Aunque todo sea SQL, la forma de manejar los gatillos puede ser bastante diferente entre distintas implementaciones. Incluso dos implementaciones de SQLite pueden manejar los gatillos de forma diferente, ya que SQLite es de código abierto y, por lo tanto, puede modificarse para un objetivo determinado.

Una forma de implementar gatillos en SQLite puede verse en el siguiente esquema.

Imagen 12

Este mismo esquema puede verse en lang_createtrigger. Allí, además de este esquema, podrás ver una breve explicación de cómo funciona. Entender este modelo de esquema es simple. Además, nos ayuda bastante a entender la secuencia de comandos que necesitamos para crear un gatillo.

Bien, pero ¿qué intenta decirnos toda esta complicación de flechas y texto? Ya que no consigo entender casi nada. Calma, mi querido lector, con el tiempo te acostumbrarás. Pero vamos a entender esto sin estrés. Observa que, en la parte superior de la imagen, tenemos un círculo. Allí comienza el código. Cada comando está marcado con un círculo, y las flechas indican cuál puede ser el siguiente comando que deberá darse. Para que no se vuelva algo aburrido y quede solo en la teoría, vamos a ver cómo se crea un gatillo. ¿Recuerdas el código 03? Pues bien, para añadir un gatillo a aquel código, todo lo que necesitamos hacer es crear algo parecido a esto que se ve en la imagen 12. El resultado es el código que se muestra a continuación.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TRIGGER tr_DeleteSymbol BEFORE DELETE ON tb_Symbols
13. BEGIN
14.     DELETE FROM tb_Quotes WHERE fk_id = OLD.id;
15. END;
16. 
17. CREATE TABLE IF NOT EXISTS tb_Quotes
18. (
19.     of_day NOT NULL,
20.     price NOT NULL,
21.     fk_id NOT NULL,
22.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
23. );
24. 
25. INSERT INTO tb_Symbols (id, symbol) VALUES
26.     (2, 'PETR4'),
27.     (1, 'ITUB3'),
28.     (3, 'VALE3');
29. 
30. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
31.     ('2023-07-10', '22.00', 1),
32.     ('2023-07-11', '22.20', 1),
33.     ('2023-07-12', '22.40', 1),
34.     ('2023-07-13', '22.30', 1),
35.     ('2023-07-14', '22.60', 1),
36.     ('2023-07-10', '26.00', 2),
37.     ('2023-07-11', '26.20', 2),
38.     ('2023-07-12', '26.40', 2),
39.     ('2023-07-13', '26.30', 2),
40.     ('2023-07-14', '26.60', 2),
41.     ('2023-07-10', '62.00', 3),
42.     ('2023-07-11', '62.20', 3),
43.     ('2023-07-12', '62.40', 3),
44.     ('2023-07-13', '62.30', 3),
45.     ('2023-07-14', '62.60', 3);
46.     
47. SELECT tq.of_day AS 'Data da cotação',
48.        tq.price AS 'Preço Atual',
49.        ts.symbol AS 'Nome do Ativo'
50.     FROM tb_Quotes AS tq, tb_Symbols AS ts
51.     WHERE tq.fk_id = ts.id
52.     ORDER BY price DESC;
53. 

Código 05

Ahora observa cuál es la diferencia entre el código 03 y este código 05. Notarás que la diferencia es exactamente el gatillo que estamos construyendo. Básicamente, podrás entender todo lo que se coloca en este código 05, excepto una cosa. ¿De dónde salió ese valor OLD? ¿Y cómo funciona realmente este gatillo? Bien, una cosa a la vez. Ese OLD es algo directamente vinculado a SQLite. Es decir, depende por completo de la implementación de SQLite. Otras implementaciones pueden usar otro nombre, por lo que es necesario consultar la documentación para obtener más detalles.

Pero OLD quiere decir antiguo. En este caso, refiriéndose al id. ¿Pero qué id? No estamos pasando ningún parámetro al gatillo. ¿Cómo podemos hacer referencia a algo que no estamos indicando? Calma. Ya te dije que esto de los gatillos es un tanto confuso. Por eso, primero necesitas entender lo que expliqué sobre cómo trabajar con tablas y sistemas más simples. Los gatillos serían la parte más avanzada de la programación SQL. Por eso, no tengas prisa, ve con calma.

Para entender de dónde viene este valor OLD, tenemos que entender cómo funciona el código. Entonces, después de ejecutar este código 05, vamos a eliminar el símbolo PETR4 de la base y, en su lugar, poner el símbolo BBDC4. Pero lo haremos de una manera diferente de la que ya vimos, precisamente porque ahora contamos con un gatillo. Para hacer este cambio, usamos el código que se muestra a continuación.

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. INSERT INTO tb_Symbols(id, symbol) VALUES(2, 'BBDC4');
3. 
4. SELECT tq.of_day AS 'Data da cotação',
5.        tq.price AS 'Preço Atual',
6.        ts.symbol AS 'Nome do Ativo'
7.     FROM tb_Quotes AS tq, tb_Symbols AS ts
8.     WHERE tq.fk_id = ts.id
9.     ORDER BY price DESC;

Código 06

Así, al ejecutar el código 06, tendremos como resultado lo que se muestra a continuación.

Imagen 13

¿Qué? ¿Cómo así? Ya lo sé, me estás engañando. Esto es una broma. Ya quisiera que ese fuera el caso, mi querido lector. Pero es exactamente así, debido a la existencia del gatillo. Cuando se ejecute la línea uno de este código 06, se eliminará todo registro que esté, de algún modo, vinculado al símbolo PETR4 de la base de datos. Y, como ahora el id con valor igual a dos está libre, podemos, usando la segunda línea del código 06, agregar el símbolo BBDC4 con ese valor de id. Por eso vuelvo a insistir: antes de intentar usar gatillos, procura entender primero cómo funciona el SQL más básico. De lo contrario, terminarás completamente perdido en medio del código que se estará ejecutando.

Insertando datos con gatillos

Bien, si lo que se vio en el tema anterior te pareció algo complicado de entender, entonces prepárate. Porque ahora veremos algo realmente complicado de entender, que es precisamente insertar datos utilizando gatillos. Antes de empezar, vuelvo a recordarte, mi querido lector, que el conocimiento madura poco a poco. Es muy importante que primero procures entender las bases antes de empezar a aventurarte en cuestiones más complejas, donde realmente se necesita tener un conocimiento previo adecuado para entender lo que está ocurriendo. No intentes usar algo antes de entender realmente cómo funcionan las cosas.

Para entender cómo insertar datos utilizando gatillos, necesitamos empezar por algo más simple. Entonces, vamos a retroceder algunos pasos y revisar primero algunos conceptos más básicos. Esto, para evitar que termines adentrándote en algo que quizá no se haya explicado debidamente antes. Recordando, una vez más, que lo que veremos aquí se aplica a una implementación estándar de SQLite. En caso de duda, consulta en la documentación de la implementación que estás intentando usar cómo establecer el funcionamiento de lo que veremos aquí. Así, vamos a empezar por el código que se muestra a continuación.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );

Código 07

Creo que, a estas alturas, todos aquí ya pueden entender qué está haciendo este código 07 y qué tipo de base de datos se construirá cuando comencemos a insertar registros dentro de las tablas creadas por este código 07. Pero quizá haya otra cosa que no haya quedado debidamente aclarada en el tema anterior, y es justamente el hecho de que:

ANTES DE CREAR LA BASE DE DATOS, necesitamos establecer los gatillos que contendrá.

Por la forma en que se han venido explicando las cosas, quizá hayas tenido la impresión de que puedes añadir o incluso eliminar gatillos en cualquier momento en una base de datos. En realidad, no es exactamente así como funcionan las cosas. En el momento en que SQL crea el archivo que contendrá la base de datos, todo lo que necesitaremos deberá quedar debidamente definido. En cierto modo, salvo que la implementación lo permita, no podrás añadir ni eliminar procedimientos operativos de una base de datos ya creada. Un ejemplo de implementación que permite añadir o eliminar cosas es SQL Server. En él, puedes añadir o eliminar métodos de gestión en cualquier momento, ya que normalmente los scripts no quedan vinculados a la base de datos.

Pero, cuando se trata de gatillos, esto normalmente no se maneja de esa manera. Así que procura estudiar las cosas antes de salir por ahí creando bases de datos al azar. Esto, para evitar sorpresas en el futuro. Por lo tanto, lo siguiente que debemos hacer será añadir un gatillo que trabajará durante el proceso de inserción de registros en la base de datos. De esta forma, el código 07 se convertirá en el código 08, que se muestra a continuación en su totalidad.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;
23. END;

Código 08

Ahora, no olvides mirar la imagen 12: lo que se crea aquí, en el código 08. Es decir, cuando se trata de gatillos, aquella misma imagen cubre los comandos DELETE, INSERT y UPDATE. Recordando, una vez más, que necesitas entender lo que aparece en el código.

El objetivo de esta actualización hecha en el código 07 para generar el código 08 es muy simple. Siempre que insertemos algún símbolo nuevo en la tabla tb_Symbols, este gatillo que estamos creando actualizará automáticamente el registro para que las letras se coloquen en mayúsculas. Hombre, ¿estás bromeando? No, mi querido lector. Normalmente, en una base de datos, necesitamos que los datos puedan comprobarse de alguna manera y, cuando sea necesario, ajustarse de cierta forma.

Esto que hacemos en el código 08, aunque parezca banal, tiene todo el sentido en ciertos escenarios. Para comprobar si este código 08 puede o no cumplir este objetivo, vamos a usar el código que se muestra a continuación. Recuerda, una vez más, que puedes ejecutar el código 08 y, bastante después, ejecutar el código 09 y, aun así, todo debería funcionar perfectamente bien.

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. SELECT * FROM tb_Symbols;

Código 09

Observa que estamos agregando valores tanto en mayúsculas como en minúsculas. Pero, dentro de la base de datos, estos registros que el código 09 agregará deberán quedar todos en mayúsculas. Esto puede verse cuando se ejecuta la línea cinco y se genera el siguiente resultado, que se muestra a continuación.

Imagen 14

Como si fuera magia, podemos observar, en la zona demarcada, que los valores fueron convertidos. Pero, antes de que pienses que esto ocurre incluso sin usar gatillos, debo recordarte que SQL no cambia ni ajusta valores de tipo string. Del mismo modo en que fueron escritos originalmente, SQL los mantendrá, salvo que utilices un gatillo, como el que se creó en el código 08, cuya finalidad es modificar o incluso actualizar algún registro dentro de la base de datos.

Ahora que ya viste el resultado, seguramente tienes cierta curiosidad por entender cómo funciona el gatillo implementado en el código 08. Esto se debe a que no existe ninguna mención a él en el código 09. Y este tipo de situación suele dejar a muchos principiantes en SQL con la mosca detrás de la oreja. En principio, el gatillo no debería ejecutarse, ya que no estamos haciendo ninguna referencia directa a él.

Bien, entonces volvamos nuestra atención al código 08 para entender cómo funciona el gatillo. La línea veinte debe leerse exactamente como está escrita en el código 08. Por ahora, no intentes interpretar nada, solo lee lo que está escrito en la línea veinte. Para ayudarte, voy a mostrarte cómo deberías hacerlo.

Crea un gatillo, si este no existe, cuyo nombre será tr_InsertSymbol, que deberá ejecutarse DESPUÉS de insertar algo en la tabla tb_Symbols

Observa que no es complicado. Todo es cuestión de prestar atención a lo que queremos que se haga. Muy bien, las líneas 21 y 23 contienen solo palabras reservadas de SQL para delimitar el cuerpo de algún procedimiento con el que queremos trabajar. Nuevamente, vuelve a la imagen 12 para más detalles, ya que lo que se muestra aquí no es más que un simple ejemplo ilustrativo. Ahora vamos a entender la línea veintidós. Debe leerse del mismo modo que se leyó la línea veinte. Y, una vez más, para ayudarte en este proceso, deberías leerla de la siguiente forma:

Actualiza la tabla tb_Symbols, haciendo que symbol tome el valor de upper(NEW.symbol) cuando NEW.id sea igual a id

Nuevamente, acabamos de ver aquí algo que depende de SQLite. Esto ocurre porque NEW solo existe en SQLite. Cuando hablamos de eliminar registros, usamos la palabra OLD. Y ahora usamos la palabra NEW. Esto, en principio, parece bastante confuso. Y, en efecto, me estoy confundiendo, mi amigo autor. No consigo entender cuándo usar NEW y cuándo usar OLD. Y el motivo es que creamos un gatillo llamado tr_InsertSymbol. Entonces, al agregar algún dato a la base de datos, estamos actualizando la base, lo que, en principio, haría que los valores no fueran nuevos, sino antiguos. Hombre, esto es una locura.

Lo sé, mi querido lector. De hecho, hay algunos detalles que hacen que las cosas resulten algo confusas al principio. Pero, a medida que vayas usándolas y estudiándolas, dejarán de ser confusas y pasarán a tener sentido. Aun así, voy a intentar simplificar las cosas para que sea posible entender cuándo usar NEW y cuándo usar OLD. Al menos, lo suficiente para que te confundas menos.

En la operación DELETE, la información que se está eliminando YA EXISTE en la base de datos y dejará de existir. Por eso usamos OLD. En la operación INSERT, la información NO EXISTE en la base de datos y pasará a existir. Por eso usamos NEW. La parte confusa es justamente el hecho de que el gatillo de la línea veinte del código 08 está diciendo que el gatillo debe ejecutarse DESPUÉS de que la información haya sido insertada en la base de datos. Sin embargo, debes considerar que ese dato sigue siendo nuevo y antes no existía en la base de datos.

Ahora bien, la cosa se vuelve un poco más complicada de entender cuando el gatillo está vinculado al comando UPDATE. En ese caso, tendrás que pensar de una manera un poco más amplia. Así que presta atención. La información que entrará en sustitución de la que ya existe en la base de datos deberá tratarse como NEW. En cambio, la información que existe en la base de datos y está siendo sustituida deberá tratarse como OLD. Si entiendes esto, podrás manejar cualquier script de gatillo que se ejecute en una base de datos SQLite estándar. Digo SQLite estándar, porque puede que la implementación maneje esto de una manera diferente. En ese caso, procura estudiar su documentación.

Eligiendo un camino

Ahora que ya sabemos cómo podemos trabajar con un gatillo para insertar registros en la base de datos, garantizando así la integridad de los datos, necesitamos ver otra cosa igual de importante. Esto se debe a que la integridad de la base de datos solo está garantizada en parte. Pero ¿por qué dices que solo en parte? El motivo es el comando UPDATE, mi querido lector. Recuerda que la garantía que se creó se basó en el comando INSERT.

Pero hay un problema en el comando UPDATE que necesitamos resolver. Piensa en lo siguiente: ¿Qué ocurre si el usuario desea cambiar algo en la base de datos usando un comando UPDATE? Como SQL no sabe qué reglas adoptar, cualquier dato que se introduzca mediante un comando UPDATE será aceptado por SQL y sustituirá algún dato validado previamente. Para demostrarlo, mira el código que se muestra a continuación.

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. 
6. UPDATE tb_Symbols SET symbol = 'iTub4' WHERE id = 3;
7.
8. SELECT * FROM tb_Symbols;

Código 10

Este código 10 deberá ejecutarse en una base de datos construida con el código 08. Esto, para que obtengas el siguiente resultado que se muestra a continuación.

Imagen 15

En la zona demarcada, puedes observar algo bastante extraño en la base de datos. Algo que no debería estar ahí, precisamente porque todos los valores deberían estar en mayúsculas. Pero, debido a la línea seis de este código 10, SQL modificó el registro y, con ello, la integridad de la base quedó comprometida. Observa que todo lo que se hace depende del objetivo que se quiera alcanzar. No existe una fórmula única. Primero necesitamos entender el problema y solo después buscar los medios para resolverlo. Sin embargo, todo esto debe quedar definido y determinado antes de que la base empiece a recibir datos sensibles.

Entonces, supongamos lo siguiente: una vez que un registro haya sido creado, ya no podrá modificarse. Esto nos dará una forma de resolver este tipo de problema visto anteriormente. Pero, si permites que la base pueda actualizarse, los datos que se inserten en el registro ya existente deberán seguir alguna regla. En ese caso, tendremos que seguir otro camino para resolver la cuestión del comando UPDATE.

Pero también puedes permitir que el usuario modifique el nombre del símbolo. Sin embargo, al hacerlo, el usuario tendrá la ilusión de que el nombre fue cambiado. Pero, dentro de la base, lo que realmente se hará será una copia de los datos, con la creación de un nuevo registro. El registro antiguo quedaría en una situación en la que el usuario no podría acceder directamente a los datos. Pero un administrador de la base podría recuperar todos los datos, ya que el registro original se mantendría intacto. Este tipo de planteamiento nos llevaría a otra solución que habría que implementar. Es decir, cada caso es un caso. No existe, en ningún caso, una solución que sirva para todos.

Así, para ejemplificar un poco mejor las cosas, vamos a ver cómo sería el tratamiento del comando UPDATE para que el usuario pudiera cambiar el nombre del símbolo, pero de forma que, al final, el nombre se mantuviera dentro de las mismas reglas impuestas durante un comando INSERT. Para hacerlo, podríamos usar la siguiente modificación del script original, que se muestra a continuación.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;

Código 11

Pero espera un momento. Ahora sí que quieres bromear. ¿Cómo puede ser que el código del gatillo del comando UPDATE sea casi una copia perfecta del gatillo del comando INSERT? ¿Estás seguro de que esto va a funcionar? Tengo mis dudas, ya que, si hacemos un INSERT, se disparará el gatillo de la línea veinte. Pero, como en la línea 22 estamos usando el comando UPDATE, creo que también se disparará el gatillo implementado en la línea 25. Cuando se ejecute el comando de la línea 27, SQL entrará en bucle. La verdad, creo que esto no va a funcionar.

Bien, si de verdad observaste esta secuencia de ejecución, mi querido lector, significa que todavía no entendiste cómo SQL maneja los gatillos. Pero no pasa nada. Ahora bien, si creas una nueva base de datos usando este código 11 y, justo después, intentas ejecutar de nuevo el código 10 dentro de esta nueva base de datos, mira lo que ocurrirá.

Imagen 16

Ahora bien, entiende lo siguiente: cuando el usuario solicite insertar un registro en la tabla tb_Symbols, SQL podrá insertar o no el registro. En caso de que lo inserte, SQL ejecutará el gatillo de la línea veinte del código 11. Este, a su vez, hará que se ejecute la línea 22, actualizando así los datos contenidos en la base de datos. Pero esa línea 22 forzará a SQL a ejecutar el gatillo de la línea 25. Hasta este punto, tenías razón. Una vez que se ejecute la línea 27, pediremos a SQL que vuelva a actualizar la base de datos.

Sin embargo, esta vez estaremos forzando el contenido del registro a un determinado patrón, que era justamente el que existía en el momento en que el registro fue creado por el comando INSERT. Así, conseguimos cubrir tanto el comando INSERT como el comando UPDATE. Aunque esta no es la mejor forma de hacerlo, para que consigas entenderlo, esta fue la forma más simple que encontré de explicar cómo podríamos hacer las cosas.

Una última cosa que quiero mostrar es lo siguiente: suponiendo que quieras evitar que algún registro sea modificado mediante un comando UPDATE, una manera relativamente simple de hacerlo es usando el código que se muestra a continuación, en el momento de crear la base de datos.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;
29. 
30. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol_Before BEFORE UPDATE ON tb_Symbols
31.     WHEN upper(NEW.symbol) != upper(OLD.symbol)
32. BEGIN
33.     SELECT RAISE(ABORT, 'ERROR: Unable to update this record...');
34. END;

Código 12

Aquí la cosa es un poco diferente. Pero, si vuelves a usar el código 10 en una base creada con este código 12, verás el siguiente resultado.

Imagen 17

Ahora quiero que prestes mucha atención a esta imagen 17 e intentes entender qué creó el código 12 para que, cuando se ejecutara el código 10, obtuviéramos este resultado. Observa que la línea uno del código 10 se ejecutará con éxito, creando así los registros en la base de datos. Sin embargo, cuando se ejecute la línea seis, se disparará un error, como se indica en la imagen 17. Es en este punto donde quiero que prestes atención a los datos que aparecen en la imagen 17. Observa la tercera línea del mensaje. Ahora vuelve al código 12 y verás que es exactamente el mismo mensaje que aparece en la línea 33. ¿Cómo es posible?

Al intentar insertar algo en la tabla, ejecutaremos el gatillo de la línea 20, justo después de que el registro haya sido insertado. Esto hará que la línea 22 ordene actualizar el registro. Bien, pero, antes de que la actualización ocurra, se ejecutará el gatillo de la línea 30. Solo después ejecutaremos el gatillo de la línea 25. Observa que cambiamos la forma en que trabajaba el código 11. Y el motivo de esto es precisamente realizar la comprobación de la línea 31. Observa que esta comprobación verifica si el registro que estamos intentando actualizar es diferente o igual al que ya existe en la base de datos. Al realizar esta comprobación, garantizaremos que el registro no llegue a modificarse. Esto se debe a que, si el registro es diferente, el gatillo se ejecutará y hará que la línea 33 entre en ejecución. Cuando SQL ejecute esa línea 33, se enviará un mensaje a quien solicitó la actualización, explicando que la operación no podrá ejecutarse por cualquier motivo.

Este tipo de modelado de la base de datos puede ser interesante en algunos casos. Procura estudiar esto, porque puede evitarte futuros dolores de cabeza, mi querido lector.

Consideraciones finales

Lo que mostré aquí es solo la base de una serie de cosas que necesitas estudiar para poder trabajar con SQL. Sé que mucha gente frunce el ceño cuando el tema es SQL, diciendo que estudiar y entender SQL es una tontería, que es mucho más fácil crear nosotros mismos archivos para almacenar datos que podrían colocarse en una base de datos, entre muchas otras cosas. Pero quiero recordarte, mi querido lector, que SQL es una de las herramientas más agradables que existen y que puede ayudarte a hacer cosas que, de otro modo, se harían con mucho más trabajo y esfuerzo.

Muchas de las cosas que veo a la gente hacer con Python, o incluso con Excel, podrían resolverse mucho más fácilmente si se usara SQL. Pero, como SQL es algo que requiere estudio y atención, buena parte prefiere romperse la cabeza intentando combinar datos e información, usando para ello programación clásica. Pero te garantizo: estudiar SQL realmente valdrá mucho la pena. Más aún cuando empieces a entender cómo pueden relacionarse los datos entre sí.

Así, aquí doy por concluido el tema sobre SQL, y queda en tus manos continuar el aprendizaje que se inició aquí. En el próximo artículo veremos cómo se aplica todo esto que se mostró cuando unimos SQL con MQL5. Verás que, hasta ahora, apenas has rozado la superficie de lo que realmente podemos hacer con MetaTrader 5.

Archivo	Descripción
Experts\Expert Advisor.mq5	Demuestra la interacción entre Chart Trade y el Asesor Experto (es necesario el Mouse Study para la interacción)
Indicators\Chart Trade.mq5	Crea la ventana para configurar la orden a ser enviada (es necesario el Mouse Study para la interacción)
Indicators\Market Replay.mq5	Crea los controles para la interacción con el servicio de reproducción/simulador (es necesario el Mouse Study para la interacción)
Indicators\Mouse Study.mq5	Permite la interacción entre los controles gráficos y el usuario (necesario tanto para operar el sistema de repetición como en el mercado real)
Servicios\Market Replay.mq5	Crea y mantiene el servicio de reproducción y simulación de mercado (archivo principal de todo el sistema)
Código VS C++\Server.cpp	Crea y mantiene un socket servidor desarrollado en C++ (versión MiniChat).
Code in Python\Server.py	Crea y mantiene un socket en Python para la comunicación entre MetaTrader 5 e Excel
Indicators\Mini Chat.mq5	Permite implementar un minichat mediante un indicador (requiere el uso de un servidor para funcionar)
Experts\Mini Chat.mq5	Permite implementar un mini chat mediante un asesor experto (requiere el uso de un servidor para funcionar)
Scripts\SQLite.mq5	Demuestra el uso de un script SQL mediante MQL5
Files\Script 01.sql	Demuestra la creación de una tabla simple, con clave foránea
Files\Script 02.sql	Demuestra la adición de valores en una tabla