Hace unos días empezamos a programar utilizando SQLite para gestionar nuestra información usando SQL de toda la vida.
Bien, SQLite tiene la interfaz clásica, que vimos en el post anterior: sqlite3_open(), sqlite3_close(), sqlite3_exec()… con utilidades básicas para trabajar.
Por un lado, podemos tener más control sobre la base de datos, por ejemplo, podemos definir cómo abirmos la base de datos, tenemos códigos de error extendidos, ventajas con respecto al tratamiento interno de la memoria y algunas cosas más. La utilización es muy parecida.
Tabla de contenidos
Prepared statements con SQLite
Por otro lado, el enfoque con callbacks está muy bien, pero en ocasiones necesitamos el resultado de un sqlite3_exec() inmediatamente, justo debajo de esa línea, sin que tengamos que pasar contextos a una función y cambiar nuestra manera de pensar, es decir, hago una query y debajo tengo el resultado. Vamos a utilizar prepared statements con SQLite:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | #include <stdio.h> #include <stdlib.h> #include "sqlite3.h" int panic(char *error, int code) { fprintf(stderr, "Error (%d): %s\n", code, error); exit(-1); } int main(int argc, char* argv[]) { sqlite3 *db; sqlite3_stmt *query; char *error = 0; int res; char *sql; int i; if ( (res=sqlite3_initialize()) != SQLITE_OK) panic("No se puede inicializar SQLite", res); /* Abre la base de datos como sólo lectura, ver constantes SQLITE_OPEN_* para más info. */ if ( (res=sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL) )!= SQLITE_OK) panic("No puedo abrir la base de datos", res); /* Prepara la sonsulta SQL */ if ( (res=sqlite3_prepare_v2(db, "SELECT * FROM events", -1, &query, NULL) )!= SQLITE_OK) panic("No puedo ejecutar la consulta", res); while ( (res = sqlite3_step(query)) == SQLITE_ROW) { printf ("Columnas encontradas: %d\n", sqlite3_data_count(query)); for (i=0; i< sqlite3_column_count(query); ++i) { printf ("%s (%s) (Type: %d) => (%d bytes) %s\n", sqlite3_column_name(query, i), sqlite3_column_decltype(query, i), sqlite3_column_type(query, i), sqlite3_column_bytes(query, i), sqlite3_column_text(query, i)); } printf("\n"); } /* Finaliza la consulta y realiza limpieza */ sqlite3_finalize(query); sqlite3_close_v2(db); /* Open database */ return 0; } |
Aquí vemos cómo podemos lanzar la SELECT y obtener debajo el resultado, con alguna información adicional (para conocer algunas funciones más de SQLite), para ello tendremos:
- sqlite3_step(sqlite3_stmt*) : Itera entre los resultados devueltos en la consulta. Si tenemos una consulta que devuelve 4 resultados, tendremos que llamar a esta función 4 veces, cada vez podrá extraer los datos de una fila. Si todo va bien devuelve SQLITE_ROW, si no hay más filas, SQLITE_DONE y otra cosa en caso de error.
- sqlite3_data_count(sqlite3_stmt*) : Nos devuelve el número de columnas. Siempre y cuando hayamos hecho un sqlite3_step() antes.
- sqlite3_column_count(sqlite3_stmt*) : Devuelve el número de columnas que hay en la fila actual
- sqlite3_column_name(sqlite3_stmt*, int index) : Devuelve el nombre de la columna número index
- sqlite3_column_decltype(sqlite3_stmt*, int) : Devuelve el tipo de dato exacto de la columna especificada. Aunque en SQLite tenemos sólo 5 tipos de dato (NULL, INT, FLOAT, BLOB, TEXT), hay datos derivados mucho más complejos, por ejemplo DATETIME para fecha y hora, que convierte directamente a TEXT.
- sqlite3_column_type(sqlite3_stmt*, int) : Devuelve el tipo de dato de SQLite para la columna dada, (SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, SQLITE_NULL)
- sqlite3_column_bytes(sqlite3_stmt*, int) : Devuelve la longitud en bytes del dato de la columna (como si fuera un string)
- sqlite3_column_text(sqlite3_stmt*, int) : Devuelve el dato de la columna especificada en forma de cadena de caracteres (char*))
Escogiendo el tipo de variable correcto en C
Anteriormente hemos visto cómo los valores devueltos por SQLite se convierten a string (todos son convertibles), y podemos verlos en pantalla. Aunque a veces, seguro que no nos interesa eso, si pedimos un número a SQLite, lo queremos en forma de número, sin necesidad de transformarlo luego. Con este segundo ejemplo, vemos que dependiendo del valor devuelto por sqlite3_column_type() extraemos el valor con uno u otro tipo:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | #include <stdio.h> #include <stdlib.h> #include "sqlite3.h" int panic(char *error, int code) { fprintf(stderr, "Error (%d): %s\n", code, error); exit(-1); } int main(int argc, char* argv[]) { sqlite3 *db; sqlite3_stmt *query; char *error = 0; int res; char *sql; int i; if ( (res=sqlite3_initialize()) != SQLITE_OK) panic("No se puede inicializar SQLite", res); if ( (res=sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL) )!= SQLITE_OK) panic("No puedo abrir la base de datos", res); if ( (res=sqlite3_prepare_v2(db, "SELECT * FROM events", -1, &query, NULL) )!= SQLITE_OK) panic("No puedo ejecutar la consulta", res); while ( (res = sqlite3_step(query)) == SQLITE_ROW) { printf ("Columnas encontradas: %d\n", sqlite3_data_count(query)); for (i=0; i< sqlite3_column_count(query); ++i) { printf ("%s (%s) (Type: %d) => (%d bytes)", sqlite3_column_name(query, i), sqlite3_column_decltype(query, i), sqlite3_column_type(query, i), sqlite3_column_bytes(query, i)); switch (sqlite3_column_type(query, i)) { case SQLITE_INTEGER: printf ("%d", sqlite3_column_int(query,i)); break; case SQLITE_FLOAT: printf ("%lf", sqlite3_column_double(query,i)); break; case SQLITE_BLOB: printf ("BINARIO"); break; case SQLITE_TEXT: printf ("%s", sqlite3_column_text(query,i)); break; case SQLITE_NULL: printf ("NULL"); break; default: printf ("Not supported"); /* No deberíamos entrar nunca */ } printf("\n"); } printf("\n"); } /* query cleanup */ sqlite3_finalize(query); sqlite3_close_v2(db); /* Open database */ return 0; } |
Vinculando argumentos (binding arguments)
Además de la precompilación de las consultas que vamos a utilizar para poder ejecutarlas más rápido (sobre todo si ejecutamos la consulta repetidas veces), una de las ventajas de los prepared statements es que nos permiten vincular argumentos de forma segura en una consulta. Siempre hay caracteres que no se pueden pasar en un sistema, cuando los argumentos son de tipo TEXT, si metemos una comilla entre el texto podemos cerrar la query y puede que no queramos eso. También puede ser utilizado por usuarios malintencionados para romper nuestros programas. Y bueno, también nos vale para tratar todos los argumentos que pasamos a SQLite de la misma forma, y olvidarnos de poner comillas en los textos, dejar los números sin ellas y poner bien los NULL, vamos a hacer una pequeña consulta con parámetros sobre el mismo programa de antes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | #include <stdio.h> #include <stdlib.h> #include "sqlite3.h" int panic(char *error, int code) { fprintf(stderr, "Error (%d): %s\n", code, error); exit(-1); } int main(int argc, char* argv[]) { sqlite3 *db; sqlite3_stmt *query; char *error = 0; int res; char *sql; int i; if ( (res=sqlite3_initialize()) != SQLITE_OK) panic("No se puede inicializar SQLite", res); if ( (res=sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL) )!= SQLITE_OK) panic("No puedo abrir la base de datos", res); if ( (res=sqlite3_prepare_v2(db, "SELECT * FROM events WHERE timestamp>? AND timestamp<? AND level>? AND message LIKE ?", -1, &query, NULL) )!= SQLITE_OK) panic("No puedo ejecutar la consulta", res); printf("Tengo que vincular %d parámetros\n", sqlite3_bind_parameter_count(query)); if ( (res=sqlite3_bind_text(query, 1, "2015-01-01", -1, SQLITE_STATIC)) ) panic("No puedo vincular el argumento 1", res); if ( (res=sqlite3_bind_text(query, 2, "2015-04-01", -1, SQLITE_STATIC)) ) panic("No puedo vincular el argumento 2", res); if ( (res=sqlite3_bind_int(query, 3, 12) ) ) panic("No puedo vincular el argumento 3", res); if ( (res=sqlite3_bind_text(query, 4, "%again%", -1, SQLITE_STATIC)) ) panic("No puedo vincular el argumento 4", res); while ( (res = sqlite3_step(query)) == SQLITE_ROW) { printf ("Columnas encontradas: %d\n", sqlite3_data_count(query)); for (i=0; i< sqlite3_column_count(query); ++i) { printf ("%s (%s) (Type: %d) => (%d bytes)", sqlite3_column_name(query, i), sqlite3_column_decltype(query, i), sqlite3_column_type(query, i), sqlite3_column_bytes(query, i)); switch (sqlite3_column_type(query, i)) { case SQLITE_INTEGER: printf ("%d", sqlite3_column_int(query,i)); break; case SQLITE_FLOAT: printf ("%lf", sqlite3_column_double(query,i)); break; case SQLITE_BLOB: printf ("BINARIO"); break; case SQLITE_TEXT: printf ("%s", sqlite3_column_text(query,i)); break; case SQLITE_NULL: printf ("NULL"); break; default: printf ("Not supported"); /* No deberíamos entrar nunca */ } printf("\n"); } printf("\n"); } /* query cleanup */ sqlite3_finalize(query); sqlite3_close_v2(db); /* Open database */ return 0; } |
En este caso, vemos que hemos colocado interrogaciones en la query inicial, cada interrogación será sustituída por un valor que luego especificaremos con sqlite3_bind_xxxx(), en este caso, vamos a especificar rangos de fechas y un número.
sqlite3_bind_int() sólo necesita la query, la posición del parámetro que va a modificar (¡¡ojo!! El primero es el 1 y no el 0) y su valor.
sqlite3_bind_text() es un poco especial, son cadenas de caracteres, y sabemos que C es muy suyo para eso. Tenemos que pasarle:
- La query, vamos el puntero al sqlite_stmt para el que queremos asignar el parámetro
- El número de parámetro a vincular
- La cadena que queremos vincular… hasta aquí vamos bien, es similar al sqlite_bind_int()
- Tamaño de la cadena (por si tenemos un número exacto de caracteres), pero si no es así, ponemos un número negativo y cogerá hasta el primer terminador (\0) que encuentre.
- SQLITE_STATIC o SQLITE_TRANSIENT, dependiendo de si el valor de la cadena que hemos pasado cambiará con el tiempo o no. Como el valor tenemos que conservarlo, imaginemos que usamos una variable para especificar la cadena, y dicha variable será sobreescrita en breve (puede que porque sea una variable temporal, o porque los sqlite3_bind_text() están dentro de una función y las cadenas son variables locales). Como aquí vemos que la cadena no cambia usamos SQLITE_STATIC, pero en caso de duda, mejor usamos SQLITE_TRANSIENT, hacemos que SQLite se haga una copia de la cadena, y nos curamos en salud.
SQLite y C++
Es cierto que todos los ejemplos que he puesto son para lenguaje C, sin ++, aunque podemos utilizarlo de forma muy parecida. De todas formas, seguro que los programadores de C++ agradecen tener una biblioteca que utilice esta base de datos a la manera C++ (the C++ way!), y que ahorre un poco de tiempo, y escritura.
Hay varias bibliotecas para utilizarlo, pero la única que he visto más o menos en desarrollo todavía es sqlite3pp (Al menos, actualizada en 2015).
En su github hay varios ejemplos interesantes.
Actualización 4/9/2015 : Gracias a Manu, he arreglado la explicación de sqlite3_data_count() que efectivamente cuenta las columnas del resultado y no las filas.
Foto: Travis Warren (Flickr CC-by)