Archivos SQLITE

¿Qué es un archivo .SQLITE?

La extensión .sqlite indica el uso de SQLite 3. Este tipo de archivo pertenece a la familia de bases de datos relacionales sin uso de servidor, compuesta por un único archivo binario. El desarrollo y el desarrollo actual de la herramienta corresponden a D. Richard Hipp y The SQLite Consortium, respectivamente, desde el lanzamiento de SQLite 3 en 2004. Como consecuencia, las bases de datos .sqlite se volvieron muy comunes en el uso en escritorios, teléfonos, aplicaciones de navegadores web, dispositivos IoT y sistemas integrados.

Por lo general, un archivo de este tipo contendrá objetos como tablas, filas, índices, vistas, el esquema de la base de datos con la definición de la estructura de tablas, la lista libre a nivel de página y metadatos específicos de la aplicación en un solo archivo binario. Los archivos con la extensión especificada se pueden encontrar sobre todo en perfiles de aplicaciones, carpetas de caché, exportaciones de bases de datos o pruebas de bases de datos en sitios web con copias de seguridad de aplicaciones móviles, proyectos GIS con fines de análisis geoinformacional, así como en aplicaciones pequeñas e independientes que no usan un sistema de bases de datos relacional basado en servidor.

Además, algunos de los archivos SQLite 3 .sqlite podrían consistir en una base de datos cifrada con SQLCipher, producida con el software que implementa la variante de SQLite de Zetetic con capacidades de cifrado. Un archivo de este tipo representaría un patrón binario aleatorio que no funcionaría con las herramientas estándar de SQLite a menos que se proporcione la configuración adecuada de clave/cifrado.

Existen archivos .sqlite de SpatiaLite. Esos son bases de datos SQLite con tablas de metadatos GIS como 'geometry_columns' y 'spatial_ref_sys', así como campos que contienen geometría en formato de datos BLOB. Muy raramente podría tratarse de archivos SQLite 2 que comienzan con "This file contains an SQLite 2.1 database".

Cómo abrir un archivo .SQLITE

Detalles técnicos

Un archivo .sqlite genérico representa un archivo de base de datos binario SQLite 3, no un archivo/script SQL de texto. Empezando en el desplazamiento de byte 0, comienza con la firma de archivo de 16 bytes "SQLite format 3\0": 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00. Desde su aparición inicial en junio de 2004 junto con la versión 3.0.0 de la biblioteca SQLite, el formato de archivo no cambió y siguió describiéndose como "SQLite format 3". Actualmente, el formato #4 se usa como formato de esquema para archivos de base de datos SQLite.

El encabezado de la base de datos se almacena en los primeros 100 bytes de la página 1. Describe parámetros como el tamaño de página, la versión de escritura del formato de archivo, la versión de lectura del formato de archivo, los bytes reservados por página, las fracciones máxima/mínima del payload incrustado, las fracciones mínima/máxima del payload de hoja, el contador de cambios del archivo, el número de páginas de la base de datos, la dirección de la primera página troncal de la freelist, el número de páginas en la freelist, la cookie del esquema, el número de formato, el tamaño predeterminado de la caché de páginas, el número de página raíz btree más grande, el número de codificación de texto de la base de datos, la versión de usuario, el indicador de vacuum, el ID de la aplicación, el número para el que la versión es válida y la versión de SQLite.

El almacenamiento en .sqlite se basa en páginas con tamaños posibles iguales a potencias de dos en el rango de 512…65536 bytes (el más usado es 4096); las tablas e índices en un archivo .sqlite se almacenan como B-trees, las páginas de desbordamiento contienen el payload de fila desbordado o las entradas de índice, las páginas de freelist almacenan información de espacio eliminado y las páginas de auto-vacuum contienen una lista de punteros para la página de la base de datos.

Las bases de datos SQLite pueden contener codificación de texto UTF-8, UTF-16LE o UTF-16BE. Cada valor de celda almacenado en un archivo SQLite corresponde al modelo de tipado dinámico de SQLite y usa las clases de almacenamiento NULL, INTEGER, REAL, TEXT y BLOB; por lo tanto, el tipo de columna declarado no define con precisión el número de bytes de datos almacenados.

Las funciones de compresión no están incorporadas en el formato .sqlite. SQLCipher usa cifrado a nivel de página de base de datos y oculta la cabecera visible de SQLite para su lectura de forma predeterminada; debe usar explícitamente "plaintext-header" para dejar la cabecera sin protección. El journaling probablemente creará los archivos complementarios llamados como input.sqlite-journal, input.sqlite-wal y input.sqlite-shm que almacenarán los cambios confirmados antes de truncar un archivo .sqlite no cerrado correctamente. El tipo MIME de la aplicación suele ser "application/vnd.sqlite3", y "application/x-sqlite3" puede encontrarse en sistemas antiguos; la capacidad teórica máxima es de aproximadamente 281TB con page-size=64KiB, pero los tamaños habituales de archivos SQLite van desde algunos kilobytes hasta algunos gigabytes.

Cómo convertir archivos .SQLITE

Un archivo .sqlite coherente debe crearse primero mediante la interfaz de respaldo de SQLite, mediante el comando de shell ".backup" de sqlite3 o mediante vacuum into command, antes de la migración. Para hacer una exportación .sql portátil (para guardar en control de código fuente, moverla), ejecuta sqlite3 input.sqlite ".dump" > output.sql.Copy Aunque .sql se guarda como un archivo de texto que contiene código SQL para crear la misma estructura y contenido de la base de datos, los BLOBs reciben un tratamiento especial; aun así, las particularidades específicas de sqlite a menudo requieren cambios, si la .db necesita migrarse a un motor distinto de SQLite.

Para casos de uso tipo hoja de cálculo, excel o aplicaciones de limpieza de datos, así como cualquier tarea de migración/exportación de una sola tabla, csv es una buena elección como sqlite3 -header -csv input.sqliteCopy "SELECT * FROM table_name;" > table_name.csv. Cuando la exportación tiene que cumplir con un contrato/estándar de API y no involucrar código SQL para el lado del consumidor, json es lo que se requiere como sqlite3 -json input.sqliteCopy "SELECT * FROM table_name;" > table_name.json.

Para migraciones más grandes a PostgreSQL, la mejor opción sería pgloader input.sqlite postgresql://user:password@localhost/dbname con asignación automática a los tipos de destino y creación de las estructuras necesarias en el lado de PostgreSQL; en el caso de SpatiaLite, usa ogr2ogr -f GeoPackage output.gpkg input.sqlite para convertir la base de datos .sqlite de características espaciales (se admite la columna geometry) a .gpkg, .shp, .geojson (para web) o al formato de PostgreSQL de PostGIS. La extensión sqlite de Duckdb puede exportar algunas de las tablas a archivos .parquet. Los formatos de exportación CSV y JSON no conservan claves foráneas, etc.

Problemas comunes y soluciones

"file is not a database" - Esto suele significar que ese archivo no admite lectura o que no es una base de datos SQLite 3.*. El archivo puede estar cifrado con SQLCipher, comprimido/renombrado, ser una versión heredada (SQLite 2.*) o tener incorrectos los primeros bytes. Comprueba el primer byte del archivo con un editor hexadecimal: los archivos correctos comienzan con los primeros bytes 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 . Las versiones heredadas de SQLite empiezan con This file contains an SQLite 2.1 database.

"database disk image is malformed": Hay corrupción en la estructura de tu base de datos. Ejecuta el comando PRAGMA integrity_check; Si hay alguna corrupción, guarda una copia del archivo actual y ejecuta .recover en la shell sqlite3.

"database is locked": "database table is locked". Lo más probable es que la base de datos esté retenida por otra transacción /WAL. Puedes terminar este programa o trabajar con la copia de seguridad de la base de datos. "unable to open database file". Por lo general, significa un problema relacionado con una ruta, permisos, un directorio aislado o de solo lectura, carpetas principales que faltan, problemas al acceder a recursos compartidos de red, etc., y NO con el problema de formato. Al copiar el archivo principal .sqlite en modo WAL, los cambios confirmados pueden almacenarse dentro del archivo auxiliar .sqlite-wal. Copia ambos archivos .sqlite y -wal + -shm o bien haz una copia completa usando la Backup API de SQLite.

"no such table": Por lo general, usaste una conexión incorrecta o attached-database tiene un esquema incompatible o distinto, tu aplicación no ha completado todas las migraciones, o el archivo no tiene tablas y acaba de crearse.

“file is encrypted or is not a database" dentro de la interfaz gráfica: Significa SQLCipher. Proporciona la frase de contraseña para el descifrado, el número de iteraciones de KDF, la versión de compatibilidad de SQLCipher para tu caso particular.

Consideraciones de seguridad

SQLite 3 estándar no contiene macros ni ejecutables incrustados como sí lo hacen los documentos de Microsoft Office. Por lo tanto, analizar esas tablas mediante cualquiera de aplicaciones como la herramienta de interfaz de línea de comandos sqlite3, DB Browser for SQLite o SQLiteStudio sería menos peligroso que abrir esos documentos. Aun así, un archivo dañino puede causar problemas en la propia biblioteca SQLite, un visor GUI, el software SIG de escritorio QGIS, la biblioteca GDAL / OGR o las extensiones de acceso a bases de datos espaciales SpatiaLite, así como en el código de aplicaciones que decodifican valores BLOB y la estructura especial de registros dentro de la estructura de tabla de las bases de datos SQLite.

Las extensiones cargables en SQLite son bibliotecas externas de código C y no código incrustado dentro de la base de datos, por lo que no deberían habilitarse en absoluto. Además, no deberías abrir archivos .dll / .so / .dylib desconocidos de internet. Las bases de datos sospechosas deberían abrirse para su análisis en modo de solo lectura siempre que sea posible. Los archivos de copia de seguridad (.sqlite-journal, .wal) y los archivos complementarios (.sqlite-shm) deberían considerarse e inspeccionarse por separado para evitar la exposición de la información que contienen.

Las bases de datos simples podrían contener mensajes personales, archivos de cookies, datos de ubicación, información sobre condiciones personales o médicas, claves de acceso API, historial de navegación y la información sobre datos que antes estaban en una tabla (incluso si la tabla se había vaciado o limpiado con una app), estando los datos antiguos visibles desde esos archivos complementarios.

Historia y versiones

SQLite surgió en el año 2000, cuando D. Richard Hipp creó una biblioteca de base de datos relacional SQL que funciona sin tener otro proceso dedicado para ejecutar un servidor. Al año siguiente, en 2001, se lanzó la versión 2 usando un formato de archivo antiguo, que, aunque ahora está en desuso, todavía existe en algunos sistemas de archivo heredados y en hardware de larga vida útil.

SQLite 3.0.0 se lanzó en junio de 2004 e incorporó el formato de archivo “SQLite format 3”, capacidades de tipado mejoradas, compatibilidad con codificación de texto UTF-8 y UTF-16, y un motor SQL mejorado. SQLite 3.7.0, que se lanzó en 2010, introdujo el sistema de registro write-ahead log, añadiendo dos archivos auxiliares, -wal y -shm, con mayor concurrencia entre lectura y escritura.

El formato de archivo de SQLite 3 se publica como un estándar abierto documentado que tiene buenas propiedades de longevidad; la organización de SQLite afirma que las bases de datos creadas con sus versiones actuales seguirían siendo accesibles décadas en el futuro. El desarrollo de software de SQLite se ofrece como software de dominio público en varias jurisdicciones, aunque con la opción de obtener garantías comerciales de pago del mismo equipo de SQLite que lo desarrolla. La compatibilidad con formato cifrado llegó con el lanzamiento de SQLCipher, mientras que la funcionalidad de tablas espaciales y GIS fue posible gracias a SpatiaLite antes de la aparición de GeoPackage como un importante estándar común de OGC, ya que la extensión de archivo .sqlite está actualmente en uso y se distribuye dentro de numerosos sistemas como los propios OSs, bases de datos, navegadores, etcétera.

Formatos relacionados

Los archivos .db y .sqlite3 usan el mismo formato SQLite 3 que .sqlite; mientras que .sqlite y .sqlite3 tienen nombres más descriptivos, .db es demasiado vago. .db3 es la tercera variante, a menudo observada en el software antiguo para los sistemas operativos de Microsoft y sus equivalentes móviles.

Los archivos .sql son una exportación en texto plano de toda la estructura y los datos, útil para diffs en git y para una lectura más fácil, aunque cargarlos en las bases de datos lleva más tiempo y, además, no contiene una estructura binaria de páginas que hace SQLite. Las extensiones llamadas .duckdb apuntan a DuckDB, una base de datos usada para analítica eficiente; si buscas ese tipo de análisis, entonces elige DuckDB y deja las transacciones, los índices y las restricciones SQL para SQLite (.parquet es un formato de archivo de base de datos columnar similar a .duckdb).

.mdb y su sucesor .accdb almacenan todo el contenido de las bases de datos con formularios, informes y otras cosas específicas de Access. Pasar de .mdb y .accdb a SQLite significa perder la compatibilidad de Access para aplicaciones simples sin sus servicios extra. El tipo de archivo .gpkg corresponde a los archivos OGC GeoPackage. Usa formato SQLite, así que usa SpatiaLite para las operaciones geográficas con SQLite y .gpkg para el intercambio GIS de OGC. .mbtiles es la otra extensión de las bases de datos SQLite, útil para exportar mapas de teselas.