SQLite en 5 minutos

En este post hablamos de datos y Apps. 

Siempre he pensado que lo más importante en el desarrollo de Apps es el contenido. De nada vale usar las últimas novedades del sistema operativo ni efectos visuales espectaculares si tu App no tiene contenido. Y de igual forma, hay auténticos "truños" (p.ej. las primeras versiones de cierta red social...) cuyas descargas se cuentan por millones, ¿por qué? por que tiene fotos, contactos, mensajes... de millones de usuarios, es decir contenido.

En Software, los contenidos son datos y los datos hay que almacenarlos y gestionarlos. Cuando necesitas almacenar una alta cantidad de datos y acceder a ellos de forma rápida y eficiente tecnologías como SharedPreferences (Android) y NSUserDefaults (iOS) ya no son suficientes. Necesitamos más artillería, y esta lleva el nombre de SQLite. 

Es una tecnología sencilla (los archivos de bases de datos consisten en archivos planos autocontenidos), conocida (un par de nociones de SQL y ya se puede hacer mucho), multiplataforma (la misma base de datos os servirá para Android e iOS) y con las ventajas de trabajar con una base de datos (operaciones simples como buscar, insertar, modificar filas y/o columnas se realizan de forma muy sencilla) y además ofrece una alta capacidad de almacenamiento (cadenas de texto, fechas, números... difícilmente serán un problema).

Como he comentado, una base de datos SQLite consiste en un único archivo autocontenido, es decir toda la información de la base de datos se guarda en ese mismo archivo y no hacen falta archivos adicionales. 

Si trabajáis en Windows tenéis varias posibilidades (en orden): formatear e instalar alguna distro de Linux, instalar cygwin, bajaros el precompilado de SQLite, o utilizar alguna herramienta gráfica de terceros.

En cualquier caso vamos a considerar que tenéis correctamente instalada y configurada la versión 3 de SQLite y un Terminal (OSX), un Shell (Linux) o cualquier tipo de consola de línea de comandos. Es decir podemos ejecutar y obtenemos:

$ sqlite3
SQLite version 3.7.17

Antes que otra cosa, posibles causas de error  y pequeñas recomendaciones:

• No olvidar el ; (punto y coma) al final de las sentencias.
• El apóstrofe (') que sale es el que normalmente en un teclado español aparece en la tecla "?" (a la derecha del "0").
• El prompt ($) puede variar dependiendo de vuestro Shell y sesión. Cuando estáis dentro de la sesión de SQLite el prompt cambia a "sqlite>" (ah, y el prompt es lo que aparece en la línea donde introducís las órdenes).
• Los datos que devuelve el Shell, están en cursiva para que los distingáis fácilmente.

Vamos a crear una simple base de datos para guardar las puntuaciones de un juego llamado ABC:

1. Crear base de datos (la extensión no es necesaria pero ayuda a reconocer el tipo de archivo):



$ sqlite3 ABC.sqlite3
SQLite version 3.7.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";" 



2. Creamos la tabla de puntuaciones (cada entrada tendrá un campo para guardar el nombre y otro campo para guardar la puntuación)



sqlite> create table puntuaciones(nombre varchar(10), puntuacion smallint);



3. Insertamos algunas puntuaciones:



sqlite> insert into puntuaciones values('Pepe',10);
sqlite> insert into puntuaciones values('Juan', 20);



4. Leemos las puntuaciones:



sqlite> select * from puntuaciones;
Pepe|10
Juan|20



5. Añadimos un nuevo jugador y leemos las puntuaciones de forma ordenada ("asc" de menor a mayor y "desc" de mayor a menor):



sqlite> insert into puntuaciones values('Eva', 15);
sqlite> select * from puntuaciones order by puntuacion asc;
Pepe|10
Eva|15
Juan|20



6. Obtenemos la puntuación de un jugador:



sqlite> select puntuacion from puntuaciones where nombre='Eva';
15



7. Imaginemos que el juego tiene varios modos de dificultad (0,1,2...), creamos una columna para el modo y actualizamos las entradas al modo en el que consiguieron la puntuación:



sqlite> alter table puntuaciones add column modo smallint;
sqlite> update puntuaciones set modo=0 where nombre='Pepe';
sqlite> update puntuaciones set modo=2 where nombre='Eva';
sqlite> update puntuaciones set modo=0 where nombre='Juan';
sqlite> select * from puntuaciones;
Pepe|10|0
Juan|20|0
Eva|15|2



8. Y finalmente para salir de la sesión:

sqlite> .exit

Ya tenéis creada vuestra base de datos con las puntuaciones del juego en el archivo "ABC.sqlite3" (o como lo hayáis nombrado). Evidentemente son ejemplos sencillos fácilmente adaptables a vuestras necesidades.

Más cosas

Tabla con los típicos campos id "autoincrement" y timestamp con la fecha por defecto de inserción:

CREATE TABLE nombre_de_tabla (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  t TIMESTAMP
  DEFAULT CURRENT_TIMESTAMP
);

Ver las tablas que hay en una base de datos:

sqlite> .tables

Obtener información de una tabla (varios métodos):

sqlite> .schema nombre_de_tabla
sqlite> pragma table_info(nombre_de_tabla);  

Borrar una tabla:

sqlite> drop table hombre_de_tabla;      

Supongamos que tenemos la tabla "clientes" (con campos "id" y "nombre") y la tabla "compras" (con campos "cliente_id" y "precio"). Y queremos obtener todos los nombres de clientes que hayan realizado una compra de más de 50$:

sqlite> select X.* from clientes X inner join compras Y on X.id = Y.cliente_id where Y.precio>=50;

SQLite admite múltiples tipos de datos en su sintaxis, pero todos los convierte a alguno de estos tipos básicos según unas reglas definidas.

• NULL
• INTEGER
• REAL
• TEXT
• BLOB