Estructura de tablas: tipos de datos y restricciones

Al describir los campos de la tabla, es necesario especificar los tipos de datos para ellos, pero el concepto de un tipo de datos en SQLite es muy diferente de MQL5.

MQL5 es un lenguaje fuertemente tipado: cada variable o campo de estructura siempre conserva el tipo de datos según la declaración. SQL, por su parte, es un lenguaje poco tipado: los tipos que especificamos en la descripción de la tabla no son más que una recomendación. El programa puede escribir un valor de un tipo arbitrario en cualquier «celda» (un campo del registro), y la «celda» cambiará de tipo, lo que, en concreto, puede ser detectado por la función MQL integrada DatabaseColumnType.

Por supuesto, en la práctica, la mayoría de los usuarios tienden a ceñirse a los tipos de columna de «respeto».

La segunda diferencia significativa en el mecanismo de tipos de SQL es la presencia de un gran número de palabras clave que describen los tipos, pero todas estas palabras se reducen en última instancia a cinco clases de almacenamiento. Al ser una versión simplificada de SQL, SQLite en la mayoría de los casos no distingue entre palabras clave del mismo grupo (por ejemplo, en la descripción de una cadena con un límite de longitud VARCHAR(80), este límite no se controla, y la descripción es equivalente a la clase de almacenamiento TEXT), por lo que es más lógico describir el tipo por el nombre del grupo. Los tipos específicos se dejan solo por compatibilidad con otros SGBD (pero esto no es importante para nosotros).

En la siguiente tabla se enumeran los tipos MQL5 y sus correspondientes «afinidades» (que significan características generalizadoras de los tipos SQL).

Al escribir un valor en la base de datos SQL determina su tipo según varias reglas:

• La ausencia de comillas, punto decimal o exponente da INTEGER
• La presencia de un punto decimal y un exponente significa REAL
• El enmarcado de comillas simples o dobles señala el tipo TEXT
• Un valor NULL sin comillas corresponde a la clase NULL
• Los literales (constantes) con datos binarios se escriben como una cadena hexadecimal prefijada con 'x'

La función SQL especial typeof permite comprobar el tipo de un valor. Por ejemplo, se puede ejecutar la siguiente consulta en MetaEditor.

Enviará lo siguiente a la tabla de resultados:

No puede comprobar valores para NULL comparando '=' (porque el resultado también dará NULL), debe utilizar el operador especial NOT NULL.

SQLite impone algunos límites a los datos almacenados: algunos de ellos son difíciles de alcanzar (y por tanto los omitiremos aquí), pero otros pueden tenerse en cuenta a la hora de diseñar un programa. Así, el número máximo de columnas de la tabla es 2000, y el tamaño de una fila, BLOB, y en general de un registro, no puede superar el millón de bytes. Se elige el mismo valor que el límite de longitud de la consulta SQL.

En cuanto a fechas y horas, SQL puede en teoría almacenarlas en tres formatos, pero sólo el primero coincide con datetime en MQL5:

• INTEGER - el número de segundos desde 1970.01.01 (también conocido como «Unix epoch»)
• REAL - el número de días (con fracciones) desde el 24 de noviembre de 4714 a.C.
• TEXT - fecha y hora con precisión al milisegundo en el formato «AAAA-MM-DD HH:mm:SS.sss», opcionalmente con la zona horaria, para lo cual se añade el sufijo «[±]HH:mm» con un desfase respecto a UTC.

Un tipo de almacenamiento de fecha real (también llamado día juliano, para el que existe una función SQL integrada Julianday) es interesante porque permite almacenar el tiempo con una precisión de milisegundos. En teoría, esto también se puede hacer como una cadena de formato 'AAAA-MM-DDTHH:mm:SS.sssZ', pero dicho almacenamiento es muy poco económico. La conversión del «día» en el número de segundos con una parte fraccionaria, a partir de la fecha familiar 1970.01.01 00:00:00, se realiza según la fórmula: julianday('now') - 2440587.5) * 86400.0. 'Ahora' denota aquí la hora UTC actual, pero puede cambiarse a otros valores descritos en la documentación de SQLite. La constante 2440587.5 es exactamente igual al número de días «naturales» para la fecha «cero» especificada: el punto de inicio de la «época Unix».

Además del tipo, cada campo puede tener una o varias restricciones, que se escriben con palabras clave especiales después del tipo. Una restricción describe los valores que puede tomar el campo e incluso permite automatizar la cumplimentación de acuerdo con la finalidad predefinida del campo.

Consideremos las principales limitaciones.

Al añadir un nuevo registro, si no se especifica el valor del campo, el sistema introducirá automáticamente el valor (constante) especificado aquí o calculará la expresión (función).

Al añadir un nuevo registro, el sistema comprobará que la expresión, que puede contener nombres de campo como variables, es verdadera. Si la expresión es falsa, el registro no se insertará y el sistema devolverá un error.

El sistema comprueba que todos los registros de la tabla tengan valores diferentes para este campo. Si se intenta añadir una entrada con un valor que ya existe, se producirá un error y la adición no tendrá lugar.

Para controlar la unicidad, el sistema crea implícitamente un índice para el campo especificado.

Un campo marcado con este atributo es utilizado por el sistema para identificar registros en una tabla y enlaces a ellos desde otras tablas (así es como se forman las relaciones relacionales, que dan nombre a las bases de datos relacionales en cuestión como SQLite). Obviamente, esta función también incluye un índice único.

Si la tabla no tiene un campo de tipo INTEGER con el atributo PRIMARY KEY, el sistema crea automáticamente de forma implícita una columna de este tipo denominada rowid. Si su tabla tiene un campo entero declarado como clave primaria, también estará disponible con el alias rowid.

Si se añade a la tabla un registro con un rowid omitido o NULL, SQLite le asignará automáticamente el siguiente número entero (de 64 bits, correspondiente a long en MQL5), mayor que el máximo rowid de la tabla en 1. El valor inicial es 1.

Normalmente, el contador sólo se incrementa en 1 cada vez, pero si el número de registros insertados alguna vez en una tabla (y posiblemente borrados después) supera long, el contador saltará al principio y el sistema intentará encontrar números libres. Pero esto es poco probable. Por ejemplo, si escribe ticks en una tabla a una velocidad media de 1 tick por milisegundo, el desbordamiento se producirá en 292 millones de años.

Solo puede haber una clave primaria, pero puede constar de varias columnas, lo que se hace utilizando una sintaxis distinta de las restricciones directamente en la descripción de la tabla.

Volvamos a las restricciones.

Esta restricción solo puede especificarse como complemento de la CLAVE PRIMARIA, lo que garantiza que los identificadores se incrementen en todo momento. Esto significa que los ID anteriores, incluso los utilizados en las entradas eliminadas, no se volverán a seleccionar. No obstante, este mecanismo se implementa en SQLite de forma menos eficiente que una simple CLAVE PRIMARIA en términos de recursos informáticos y, por tanto, no se recomienda su uso.

Esta restricción prohíbe añadir un registro a la tabla en la que este campo no esté rellenado. De manera predeterminada, cuando no hay ninguna restricción, cualquier campo no único puede omitirse en el registro añadido y se establecerá como NULL.

Estas instrucciones permiten rellenar automáticamente un campo con la hora (sin fecha), la fecha (sin hora) o la hora UTC completa en el momento en que se insertó el registro (siempre que la sentencia SQL INSERT no escriba explícitamente nada en este campo, ni siquiera NULL). SQLite no sabe detectar automáticamente la hora de un cambio de registro de forma similar; para ello tendrá que escribir un disparador (lo que está fuera del alcance del libro).

Desafortunadamente, las restricciones del grupo CURRENT_TIMESTAMP se implementan en SQLite con una omisión: la marca de tiempo no se aplica si el campo es NULL. Esto distingue a SQLite de otros motores SQL y de cómo el propio SQLite maneja los NULL en los campos de clave primaria. Resulta que para el etiquetado automático no se puede escribir todo el objeto en la base de datos, sino que es necesario especificar de forma explícita todos los campos excepto el campo con la fecha y la hora. Para resolver el problema, necesitamos una opción alternativa en la que la función SQL STRFTIME('%s') se sustituya en la consulta compilada para las columnas correspondientes.