Publi

7 Tareas comunes que echamos de menos en SQLite si venimos de otra base de datos SQL.

5577388841_c86d1b41c2_o

Si en nuestra aplicación utilizamos una base de datos SQLite pero no estamos muy familiarizamos con la forma de trabajar de este pequeño motor, esta es tu guía. Aunque SQL es el mismo para muchas bases de datos (sobre todo las que tienen estas tres letras en su nombre: MSSQL, MySQL, PostgreSQL…), siempre hay unas pequeñas diferencias.
Esto no es una guía completa de SQLite, sólo una pequeña chuleta para algunas tareas comunes que pueden traernos de cabeza. Para los ejemplos, he utilizado algunas bases de datos SQLite de Firefox. Aunque otros programas como Skype, Dropbox, Thunderbird, muchísimos programas de Android… también usan SQLite.

Saber las tablas de una base de datos

Si estamos en el cliente SQLite oficial podemos escribir:

.tables
moz_anno_attributes  moz_favicons         moz_items_annos
moz_annos            moz_historyvisits    moz_keywords
moz_bookmarks        moz_hosts            moz_places
moz_bookmarks_roots  moz_inputhistory

Aunque si estamos programando una aplicación, debemos utilizar otra consulta:
SELECT name FROM sqlite_master WHERE type=’table’;
moz_places
moz_historyvisits
moz_inputhistory
moz_hosts
moz_bookmarks
moz_bookmarks_roots
moz_keywords
sqlite_sequence
moz_favicons
moz_anno_attributes
moz_annos
moz_items_annos
sqlite_stat1

Describir una tabla

O saber cómo está formada, qué campos tiene, qué tipos de datos maneja… para SQLite. Cuando queremos hacer una petición a una tabla en este sistema y debemos saber cómo se llaman los campos que queremos pedir. Podemos hacer en el cliente de SQLite:

.schema moz_places
CREATE TABLE moz_places (   id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid TEXT, foreign_count INTEGER DEFAULT 0 NOT NULL);
CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id);
CREATE INDEX moz_places_hostindex ON moz_places (rev_host);
CREATE INDEX moz_places_visitcount ON moz_places (visit_count);
CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);
CREATE INDEX moz_places_lastvisitdateindex ON moz_places (last_visit_date);
CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url);
CREATE UNIQUE INDEX moz_places_guid_uniqueindex ON moz_places (guid);

Incluso lo podemos hacer con la tabla sqlite_master (la que tiene información sobre las tablas):
.schema sqlite_master
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);

Otras formas de pedir esta información pueden ser:

PRAGMA table_info(moz_places);
0|id|INTEGER|0||1
1|url|LONGVARCHAR|0||0
2|title|LONGVARCHAR|0||0
3|rev_host|LONGVARCHAR|0||0
4|visit_count|INTEGER|0|0|0
5|hidden|INTEGER|1|0|0
6|typed|INTEGER|1|0|0
7|favicon_id|INTEGER|0||0
8|frecency|INTEGER|1|-1|0
9|last_visit_date|INTEGER|0||0
10|guid|TEXT|0||0
11|foreign_count|INTEGER|1|0|0

Devolviéndonos un elemento por fila con los datos (ID de Columna, nombre, tipo, si puede ser NULL, valor por defecto, y si es clave primaria.

También podemos hacer otra cosa:

SELECT sql FROM sqlite_master WHERE name=’moz_places’;
CREATE TABLE moz_places (   id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid TEXT, foreign_count INTEGER DEFAULT 0 NOT NULL)

Conocer el último ID insertado

En SQLite, a no ser que se diga lo contrario, siempre tendremos un ID de fila para cada una de las filas que insertemos en cada tabla. Este Id de fila (o ROWID) no es visible si hacemos un SELECT * FROM tabla, aunque si podríamos hacer un SELECT rowid,* FROM tabla. Este ID identifica de forma unívoca una fila dentro de una tabla. Así evitamos tener que hacerlo nosotros, por ejemplo. En muchos sistemas de base de datos, podemos obtener el último ID insertado en una tabla, y aquí también. Si por ejemplo en una tabla hacemos:

INSERT INTO mitabla VALUES(43, 12, ‘Dato de ejemplo’);
SELECT last_insert_rowid();
4

Si queremos probarlo con el SELECT de antes sobre la misma tabla, veremos que el último dato tiene un ROWID 4.

NOW() fecha y hora actual

Si queremos plasmar la fecha actual en un campo de la base de datos, en muchos sistemas de base de datos SQL encontramos la función NOW(), aunque no en SQLite. Pero tenemos otra forma de sacar ese mismo dato:

SELECT date(‘now’)
2016-08-08
SELECT datetime(‘now’);
2016-08-08 10:24:04
SELECT time(‘now’);
10:24:32

Aunque también tenemos algunas posibilidades muy chulas como por ejemplo utilizar strftime() para dar la fecha en el formato que queramos, o la posibilidad de hacer operaciones con la fecha, como se indica a continuación:

SELECT strftime(‘%H:%M %d/%m/%Y (%s)’, ‘now’, ‘localtime’);
12:27 08/08/2016 (1470659251)
SELECT datetime(‘now’, ‘+1 month’);
2016-09-08 10:30:20
SELECT datetime(‘now’, ‘+1 month’, ‘+5 days’, ‘-3 hours’);
2016-09-13 07:31:50

Permitiéndonos muchas posibilidades. Por cierto, date(), datetime() y time() llaman internamente a strftime() por lo que strftime() puede ser utilizada con todos los modificadores que queramos.

Formateando la salida del SELECT

El cliente de SQLite permite obtener la salida formateada de diferentes maneras. Y eso es muy útil si utilizamos SQLite en nuestros scripts.

.mode column
SELECT id, url, guid FROM moz_places LIMIT 5;
1           http://www.mozilla.com/en-US/firefox/central/  nudGvVlQ89nh
2           http://www.ubuntu.com/                         caldnkjR-PGQ
3           http://wiki.ubuntu.com/                        LMwsRxTf2nMp
4           https://answers.launchpad.net/ubuntu/+addques  uX46uNYpYg4k
5           http://www.debian.org/                         AiVsuHkboUwM
.headers on
SELECT id, url, guid FROM moz_places LIMIT 1;
id          url                                            guid
----------  ---------------------------------------------  ------------
1           http://www.mozilla.com/en-US/firefox/central/  nudGvVlQ89nh
.mode csv
SELECT * FROM moz_hosts LIMIT 4;
id,host,frecency,typed,prefix
1,mozilla.com,140,0,
2,ubuntu.com,140,0,
3,wiki.ubuntu.com,140,0,
4,answers.launchpad.net,128,0,

Y muchos modos más si utilizamos .help

Obtener la versión con una consulta

Aunque tanto desde su interfaz C como desde el cliente, podemos obtener la versión de SQLite, puede que alguna vez necesitemos una consulta para ello, o para obtener esta versión junto con más cosas. Podemos hacerlo así:

SELECT sqlite_version();
3.8.6

Formatear columnas

¿Y si, formateamos columnas como si estuviéramos programando en C? A partir de SQLite 3.8.3 (2014) podemos utilizar la función printf() en nuestras consultas y hacer cosas tan chulas como:

SELECT printf(‘%s-%.2d-%.4d’, host, frecency, typed) AS out FROM moz_hosts LIMIT 4;
out
--------------------
mozilla.org-100-0000
ubuntu.com-2075-0001
wiki.ubuntu.com-100-
answers.launchpad.ne
SELECT printf(‘%05d’, 123);
00123
SELECT printf (‘%09.4f’, 43.23);
0043.2300
SELECT printf (‘%X’, 12380);
305C

Y esto tiene muchas más posibilidades que un CONCAT().

Foto Principal: Chris Dlugosz

También podría interesarte....

Leave a Reply