¿Qué día me dijiste que teníamos la reunión? (Manejo de fechas en SQLite3)

Como dicen en la propia documentación de SQLite, en SQLite3 no existe el tipo de datos fecha.

Cito directamente de http://www.sqlite.org/datatype3.html

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

    TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
    REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
    INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Así que lo más conveniente es declarar el tipo de ese campo como TEXT (o números, también vale), almacenar las fechas en el formato ISO8601 (básicamente: 'YYYY-MM-DD HH:MM:SS'), así además se puede ordenar por ese campo simplemente ordenando como texto, y utilizar las funciones de fecha de SQLite3 para procesarlas.

Por ejemplo, para hacer una comparación:

 1 select * from tabla where julianday(f1) > julianday('2014-01-01 00:00:00')

 

UN EJEMPLO PRÁCTICO

Bien, hasta aquí la teoría. Vamos a probarlo. Para ello, voy a utilizar SQLite Expert Personal, donde he creado una base de datos y dentro de ella crearé una tabla con un campo fecha.

1. Creamos la tabla

 1 CREATE TABLE PERSONAS
 2 (
 3   ID         NUMBER(10)  NOT NULL,
 4   NOMBRE     VARCHAR(30),
 5   APE1       VARCHAR(30),
 6   APE2       VARCHAR(30),
 7   FNACIM     DATETEXT  
 8 );

El tipo de dato DATETEXT (línea 7) se corresponde internamente en SQLite Expert con WideString, pero en realidad debería valer cualquier tipo de datos de texto.

2. Insertamos unos cuantos datos de ejemplo

 1 insert into PERSONAS(ID, NOMBRE, APE1, APE2, FNACIM) values (1, 'MARIANO', 'RAJOY', 'ZAPATERO', '1965-05-20');
 2 insert into PERSONAS(ID, NOMBRE, APE1, APE2, FNACIM) values (2, 'JOSE LUIS', 'RAJOY', 'ZAPATERO', '1970-06-20');
 3 insert into PERSONAS(ID, NOMBRE, APE1, APE2, FNACIM) values (3, 'PETER', 'SCHWARZENEGGER', NULL, '1973-08-30');
 4 insert into PERSONAS(ID, NOMBRE, APE1, APE2, FNACIM) values (4, 'ARNOLD', 'LANDA', 'CLARK', '1982-08-30');

Antes de consultar nada, comprobamos que los datos se han insertado bien, yéndonos a la pestaña "Data" de SQLite Expert

Fig. 1. Los datos. Cuántos conocidos por aquí.

3. Hacemos una consulta 

En ésta, utilizamos la función "julianday" para comparar fechas

 1 SELECT * FROM PERSONAS WHERE julianday(FNACIM) > julianday('1970-07-01')

El resultado:

Fig. 2. Resultado de la consulta. Los más jovencitos del grupo

Bueno, parece que todo ha ido bien.

Además de juliandate, SQLite incorpora otras cuatro funciones para trabajar con fechas: date, time, datetime y strftime. Os recomiendo la lectura de la página donde se describen (enlace al final).

Una vez que se sabe esta forma de trabajar con las fechas en SQLite3, la cuestión parece una tontería, pero el hecho de que SQLite no tuviera un tipo de dato nativo para las fechas me ha hecho perder un buen rato. Espero que no le pase a quien lea esto.

¿Y tú, te has peleado con fechas en SQLite?

Más info:

SQLite. http://www.sqlite.org/

Funciones de fecha en SQLite. http://www.sqlite.org/lang_datefunc.html

SQLite Expert Personal. Un estupendo gestor de bases de datos SQLite. http://www.sqliteexpert.com/download.html

Harry Potter y el increíble poder de las "no transacciones" o Cómo hacer inserciones masivas en SQLite3 sin pérdida de rendimiento

Andaba yo preocupado porque necesitaba hacer una copia local de unas tablas desde un servidor Informix a una BD local SQLite3. Todo esto desde una aplicación web hecha en PHP.

Ni corto ni perezoso, me lancé a la tarea. Mi primera idea fue hacer una consulta a la BD Informix y luego procesar cada registro, componer la correspondiente instrucción SQL INSERT y ejecutarla. Algo así

 1     $sql="SELECT * FROM $tabla where " . $where ;
 2     $rs=odbc_exec($conn, $sql); if (!$rs) {exit("$modname: Error en SQL [$sql]");}
 3  ...
 4  while (odbc_fetch_row($rs)) {
 5   ...
 6   $sql="insert into " . $tabla . "(" ...
 7   $res = $db_sqlite3->exec($sql);
 8   ...
 9  }//while

Pero me decepcioné cuando vi lo que se tardaba en hacer la copia

 Como véis, el primer bloque son 2.628 registros, y tarda 34"
El segundo bloque, 50.446 registros, tardó 11' 13", o sea, 673"

Fatal. Y eso que sólo estaba sacando una parte de los registros de la tabla. La tabla original tenía casi 90.000 registros, pero es que además necesitaba copiar otras tablas más grandes, incluso una de ellas con 8 millones de registros. Y a este paso, la copia iba a terminar para mi fecha de jubilación, aproximadamente.

La cosa quedó ahí aparcada un tiempo, y me desvié hacia otras cuestiones. El problema lo resolví de otra manera, pero me quedó la espinita clavada. ¿Por qué se tardaba tanto en hacer una copia, si yo había leído que SQLite3 era rapidísima? Estaba claro que no era cosa del Informix, ya que hacer una copia a un MDB de Access era muchísimo más rápido.

Y ahora llega el momento en el que entra mi gran amigo Miguel en acción. El otro día vino a verme al curro, y, mientras nos tomábamos un café, surgió el tema de SQLite3 y le conté mi problema.

Un par de horas después tenía un correo suyo en mi buzón, con enlaces a estas dos estupendas entradas:

Hola Jose

Tu problema de lentitud no parece que sea SOLO de indices. Como las BD de SQLite son ficheros hay otro problema añadido de bloqueo de ficheros.

Mira estos enlaces:

http://tech.vg.no/2011/04/04/speeding-up-sqlite-insert-operations/
http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/

Después de leerlos, he comprendido la causa del problema. Básicamente, la cosa es que cada INSERT que se ejecuta dentro del bucle consiste en una transacción. SQLite3 se asegura de que se consolida en disco antes de dar por terminada la operación. Lo que se necesita en casos como este es ejecutar TODAS las instrucciones en una única transacción, y no cada una por separado. Esto es lo principal. Aparte, se pueden parametrizar ciertos valores en la BD para ayudar a mejorar el rendimiento:


1) Desactivar el modo de funcionamiento síncrono de SQLite3

 1 $db_sqlite3->exec("PRAGMA synchronous=OFF");

2) Iniciar una transacción justo antes del bucle, y cerrarla al salir

 1     $sql="SELECT * FROM $tabla where " . $where ;
 2     $rs=odbc_exec($conn, $sql); if (!$rs) {exit("$modname: Error en SQL [$sql]");}
 3  ...
 4  $db_sqlite3->exec("BEGIN TRANSACTION");
 5  while (odbc_fetch_row($rs)) {
 6   ...
 7   $sql="insert into " . $tabla . "(" ...
 8   $res = $db_sqlite3->exec($sql);
 9   ...
10  }//while
11  $db_sqlite3->exec("COMMIT TRANSACTION");

3) Otros PRAGMA para mejorar el rendimiento

 1  $db_sqlite3->exec("PRAGMA count_changes=OFF");
 2  $db_sqlite3->exec("PRAGMA journal_mode=MEMORY");
 3  $db_sqlite3->exec("PRAGMA temp_store=MEMORY");

4) Utilizar sentencias precompiladas (prepared statements)

Yo empleé en primer lugar la técnica 1) y el resultado en ese caso fue prometedor:

En este caso, el primer bloque tarda 19", frente a los 34" del primer caso. O sea, una mejora del 44%. Vamos bien.
El segundo bloque tardó 5' 41", o sea, 341", frente a los 673" del primer caso. O sea, una mejora del 49%. Bien, muy bien.

Pero la gran mejora vino al hacer lo segundo, y que he mencionado antes: abrir una transacción antes de entrar en el bucle, y cerrarla al salir. Resultados:

Primer bloque: 1" ¡Impresionante! Una mejora del 97%
Segundo bloque: 7". Una mejora del 99%. ¿Es para flipar o no lo es?

La opción 3) (otros PRAGMAS) también la probé, pero no hay variaciones significativas.

Y como no os lo voy a dar todo hecho, el tema de probar con las sentencias preparadas os lo dejo para que lo probéis vosotros.

Por supuesto, me encantaría oír vuestros comentarios.

SQL - Seleccionar sólo las primeras N filas

El otro día metí la pata haciendo un SELECT genérico de una tabla de la que desconocía el tamaño. El procesador se puso casi al 100% durante 5 minutos, y el disco duro no dejaba de rascar. Los usuarios que compartían (sufrían) conmigo el acceso a la BD estuvieron experimentando un rendimiento pésimo durante esos 5 minutos (bueno, los pocos que intentaron trabajar en ese período), que a mí se me hicieron eternos. El problema es que la tabla tenía casi 2 millones de registros y esto fue er... bueno... "un poco" pesado. Encima, el resultado fue a una página web, así que os podéis imaginar el tamaño del "ascensor" (el recuadro) de la barra de desplazamiento. Casi no se podía pinchar en él de pequeño que se quedó.

El caso es que me interesaba simplemente ojear el contenido de esa tabla, me hubiera bastado con recuperar unos pocos registros, digamos, unos 100 para hacerme una idea de su contenido. Bueno, pues eso se puede hacer sin necesidad de poner filtros a los datos (cosa que en su momento no podía hacer pues no tenía ni idea de los campos y datos de la tabla).

En ORACLE

SELECT * FROM TABLA WHERE ROWNUM <100

En MS SQL Server y en SYBASE
SELECT TOP 10 * FROM TABLA

En INFORMIX
SELECT FIRST 100 * FROM TABLA


Actualización 02/09/2014 para incluir SQLite3, que me ha hecho falta hoy. Hay que añadir LIMIT(N) al final de la consulta

En SQLite3
SELECT * FROM TABLA LIMIT(100)

Si hubiera cláusulas WHERE u ORDER BY, hay que poner la opción LIMIT al final de todo, o sea:

SELECT * FROM TABLA WHERE CONDICION ORDER BY CAMPO LIMIT(100)

Las dichosas eñes - PHP + Informix + SQLite3

En PHP, al migrar datos de una BD Informix a una SQLite3, me he encontrado con que las eñes se grababan mal. Los datos los recupero así:

$ape1=trim(odbc_result($rs,"ape1"));

Solución: usar la función utf8_encode.

$ape1=utf8_encode(trim(odbc_result($rs,"ape1")));

Y todo funciona a la perfección.