Nuevas características SQLite en Xojo 2022r4

A continuación encontrarás traducido al español la entrada publicada originalmente en el Blog de Xojo y escrita por Paul Lefebvre. Puedes leer el artículo original en este enlace.

En Xojo 2022 Release 4 hemos actualizado la Librería SQLite a SQLite v3.39.4. Anteriormente estábamos usando en Xojo la versión 3.36.0, y se han realizado varias actualizaciones desde entonces. Por tanto, me gustaría destacar algunas de las más notables:

• Tablas STRICT
• PRAGMA table_list
• RIGHT y FULL OUTER JOIN
• Soporte JSON incorporado

A continuación encontrarás más información sobre cada una de ellas.

Tablas STRICT

Una de las capacidades más inusuales de SQLite es que no le importa el tipo de datos que almacena en cada columna. Si bien puedes indicar un tipo para una columna, no es realmente más que una sugerencia, ya que también admite cualquier otro tipo de dato para la columna. Este comportamiento es diferente del ofrecido por otras bases de datos relacionales y puede suponer a veces una fuente de confusión.

Ahora puedes crear tablas usando la palabra clave STRICT para forzar los tipos requeridos en las columnas y que siempre se compruebe el tipo de datos cuando se almacena la información en ellas.

Aún estás limitado a INT, INTEGER, REAL, TEXT y BLOB. Esto significa que todavía no hay tipos de datos DATE o DATETIME tal y como puedes encontrar en otras bases de datos. En vez de ello se utiliza TEXT con el formato YYYY-MM-DD.

Sin embargo, se ha añadido un tipo ANY que esencialmente te permite indicar que la columna puede contener cualquiera. Esto te permite tener una mezcla de tipos específicos y tipos genéricos en tus tablas STRICT.

Observa que la palabra clave STRICT se indica al final del comando CREATE TABLE:

CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID)) STRICT;

La documentación de SQLite ofrece más información sobre la nueva característica de tabla STRICT.

PRAGMA table_list

Con Xojo siempre puedes obtener el listado de tablas utilizando el método Database.Tables(). Sin embargo, si quieres obtener el listado de tablas usando SQL has de consultar directamente la tabla sqlite_master.

Ahora hay un simple PRAGMA que hace lo mismo:

PRAGMA table_list

Devuelve un listado de las tablas y otros detalles sobre la tabla (lo cual puede cambiar en el tiempo según la documentación de SQLite).

RIGHT y FULL OUTER JOIN

La unión de tablas es una tarea común en SQL. El tipo de unión más común es un INNER JOIN donde sólo se incluyen en el resultado las filas comunes a ambas tablas. Otros tipos de uniones menos habituales son LEFT OUTER, RIGHT OUTER y FULL OUTER (a veces se omite OUTER cuando nos referimos a este tipo de uniones).

SQLite ya contaba con soporte para uniones LEFT OUTER desde hace tiempo, pero no así el soporte para RIGHT OUTER y FULL OUTER. Ahora sí están soportados, permitiéndote realizar consultas más complejas y obtener mayor compatibilidad con las bases de datos de “mayor entidad”.

Puedes aprender más sobre este tipo de uniones en W3 Schools.

Soporte JSON

Me he reservado la más importante para el final: ahora las bases de datos SQLite pueden trabajar con JSON en las columnas. He aquí una tabla sencilla para trabajar con datos JSON almacenados en la columna players:

CREATE TABLE team(id INTEGER PRIMARY KEY, Name TEXT, players TEXT);
INSERT INTO TEAM VALUES (NULL, 'Seagulls', '[ {"Name":"Bob","position":"1B"}, {"Name":"Tom","position":"2B"}
]')
INSERT INTO TEAM VALUES (NULL, 'Pigeons', '[ {"Name":"Bill","position":"1B"}, {"Name":"Tim","position":"2B"}
]')
INSERT INTO TEAM VALUES (NULL, 'Crows', '[ {"Name":"Betty","position":"1B"}, {"Name":"Tina","position":"2B"}
]')

Digamos que quieres obtener el primer jugador de cada equipo. Sin soporte JSON en SQLite tendrías que obtener todos los datos JSON de la columna y parsearlos por separado; pero ahora puedes hacerlo así en SQL:

SELECT players -> 0 FROM team

La anterior sentencia SQL dice: obtén por cada fila el primer elemento del array en los datos JSON de players. Así es como obtendrías el listado con todos los jugadores de todos los equipos:

SELECT team.Name, json_each.value -> 'Name' FROM team, json_each(team.players)

Y si quieres obtener el valor concreto sin las comillas, puedes utilizar el operador ->> (y también renombrar el resultado):

SELECT team.Name, json_each.value ->> 'Name' As PlayerName FROM team, json_each(team.players)

El soporte JSON de SQLite puede hacer mucho, mucho más; puedes leer sobre ello en la página de documentación de JSON en SQLite.