13 Ejemplo de aplicación SQLite

Vamos a desarrollar un programa basado en ejemplo 2 del curso de MySQL.

Recordemos el enunciado del problema:

Nuestro segundo ejemplo es más complicado. Se trata de gestionar una biblioteca, y nuestro cliente quiere tener ciertas herramientas a su disposición para controlar libros, socios y préstamos. Adicionalmente se necesita un control de los ejemplares de cada libro, su ubicación y su estado, con vistas a su retirada o restitución, para esto último necesita información sobre editoriales a las que se deben pedir los libros.

Tanto los libros como los socios estarán sujetos a ciertas categorías, de modo que según ellas cada libro podrá ser o no prestado a cada socio. Por ejemplo, si las categorías de los libros van de A a F, y la de los socios de B a F, un libro de categoría A nunca puede ser prestado a ningún socio. Estos libros sólo se pueden consultar en la biblioteca, pero no pueden salir de ella. Un libro de categoría B sólo a socios de categoría B, un libro de categoría C se podrá prestar a socios de categorías B y C, etc. Los libros de categoría F siempre pueden prestarse.

El sistema debe proporcionar también un método de búsqueda para libros por parte de los socios, por tema, autor o título. El socio sólo recibirá información sobre los libros de los que existen ejemplares, y sobre la categoría.

Además, se debe conservar un archivo histórico de préstamos, con las fechas de préstamo y devolución, así como una nota que el responsable de la biblioteca quiera hacer constar, por ejemplo, sobre el estado del ejemplar después de su devolución. Este archivo es una herramienta para la biblioteca que se puede usar para discriminar a socios "poco cuidadosos".

Los préstamos, generalmente, terminan con la devolución del libro, pero algunas veces el ejemplar se pierde o el plazo supera un periodo de tiempo establecido y se da por perdido. Estas circunstancias pueden cerrar un préstamo y provocan la baja del ejemplar (y en ocasiones la del socio :-). Nuestro archivo histórico debe contener información sobre si el libro fue devuelto o perdido.

Supondremos que vamos a desarrollar nuestro programa usando el compilador Code::Blocks, aunque el código y las acciones a tomar serán muy parecidas usando otros IDEs.

Empezaremos creando un nuevo proyecto, de tipo "Console application", en C++ y con el título "ejemplosqlite".

A continuación abrimos "Proyecto->Opciones para la construcción", vamos a la pestaña "linker settings", seleccionamos "ejemplosqlite", en el árbol de la izquierda, para que las opciones se apliquen tanto a la versión "debug" como a la "release", y agregamos la librería "sqlite".

Si la el fichero "sqlite3.dll" no está en una carpeta donde se pueda encontrar siempre (por ejemplo en system32, o cualquiera en el path), deberemos copiarla a la carpeta del proyecto. Haremos lo mismo con el fichero "sqlite3.exe", para poder consultar la base de datos desde consola, si fuera necesario.

Iremos añadiendo rutinas al programa, paso a paso, a medida que los vayamos explicando, de modo que cada fase de la aplicación pueda ser compilada y probada.

Crear la base de datos

Lo primero es crear la base de datos, si no existe, y abrirla si existe. Crear la base de datos es trivial, basta con intentar abrirla, si el fichero de base de datos no existe, se crea uno vacío. Nuestro primer programa es simple:

/*
 * Aplicación de ejemplo de uso de SQLite en C++
 * EjemploSQLite
 * Salvador Pozo, Con Clase (www.conclase.net) 
 * Marzo de 2012
 * Fichero: main.cpp
 * fichero principal
 * Incluir en el enlazador la librería libsqlite.a (sqlite)
 * Facilitar el acceso a la librería de enlace dinámico sqlite3.dll
 */
 
#include <iostream>
#include <sqlite/sqlite3.h>

using namespace std;

int main()
{
    int rc;
    sqlite3 *db;

    // Abrir base de datos
    rc = sqlite3_open("biblioteca.db", &db);
    if(SQLITE_OK != rc) {
        cout << "Error: No se puede abrir la base de datos" << endl;
		return 1;
    }
    // Aquí ira nuestro programa...
	
    // Cerrar base de datos
    sqlite3_close(db);
    return 0;
}

Después de ejecutar este programa podremos ver que se ha creado el fichero de la base de datos vacío.

Verificar si existen las tablas

El segundo paso es verificar si existen las tablas. Para ello haremos una consulta en cada una se ellas, y asumiremos que si se produce un error es porque la tabla no existe. SQLite no dispone de códigos de error específicos para indicar que una tabla no existe, aunque la función sqlite3_errmsg sí detecta ese error.

Para nuestra segunda versión del programa crearemos una función que verifique la existencia de las nueve tablas que componen la base de datos, y que cree aquellas que no existen.

/*
 * Aplicación de ejemplo de uso de SQLite en C++
 * EjemploSQLite
 * Salvador Pozo, Con Clase (www.conclase.net)
 * Marzo de 2012
 * Fichero: main.cpp
 * fichero principal
 * Incluir en el enlazador la librería libsqlite.a (sqlite)
 * Facilitar el acceso a la librería de enlace dinámico sqlite3.dll
 */

#include <iostream>
#include <sqlite/sqlite3.h>

const int nTablas = 9;

bool VerificarTablas(sqlite3 *);

using namespace std;

int main()
{
    int rc;
    sqlite3 *db;

    // Abrir base de datos
    rc = sqlite3_open("biblioteca.db", &db);
    if(SQLITE_OK != rc) {
        cout << "Error: No se puede abrir la base de datos" << endl;
		return 1;
    }

    if(!VerificarTablas(db)) return -1;

    // Cerrar base de datos
    sqlite3_close(db);
    return 0;
}

bool VerificarTablas(sqlite3 *db) {
    char consulta[36];
    char *tabla[] = {
        "editorial",
        "libro",
        "autor",
        "tema",
        "ejemplar",
        "socio",
        "prestamo",
        "trata_sobre",
        "escrito_por"
    };
    char *tabla[] = {
        "editorial",
        "libro",
        "autor",
        "tema",
        "ejemplar",
        "socio",
        "prestamo",
        "trata_sobre",
        "escrito_por"
    };
    char *create[] = {
        "CREATE TABLE editorial("
          "claveeditorial INTEGER PRIMARY KEY,"
          "editorial TEXT,"
          "direccion TEXT,"
          "telefono TEXT);",
        "CREATE TABLE libro("
          "clavelibro INTEGER PRIMARY KEY,"
          "titulo TEXT,"
          "idioma TEXT,"
          "formato TEXT,"
          "claveeditorial INTEGER "
          "REFERENCES editorial(claveeditorial) "
          "ON DELETE SET NULL "
          "ON UPDATE CASCADE);",
        "CREATE TABLE autor("
          "claveautor INTEGER PRIMARY KEY,"
          "autor TEXT);",
        "CREATE TABLE tema("
          "clavetema INTEGER PRIMARY KEY,"
          "tema TEXT);",
        "CREATE TABLE ejemplar("
          "clavelibro INTEGER "
          "REFERENCES libro(clavelibro) "
          "ON DELETE CASCADE "
          "ON UPDATE CASCADE,"
          "numeroorden INTEGER NOT NULL,"
          "edicion INTEGER,"
          "ubicacion TEXT,"
          "categoria TEXT,"
          "PRIMARY KEY(clavelibro,numeroorden));",
        "CREATE TABLE socio("
          "clavesocio INTEGER PRIMARY KEY,"
          "socio TEXT,"
          "direccion TEXT,"
          "telefono TEXT,"
          "categoria TEXT);",
        "CREATE TABLE prestamo("
          "clavesocio INTEGER "
          "REFERENCES socio(clavesocio) "
          "ON DELETE SET NULL "
          "ON UPDATE CASCADE,"
          "clavelibro INTEGER "
          "REFERENCES ejemplar(clavelibro) "
          "ON DELETE SET NULL "
          "ON UPDATE CASCADE,"
          "numeroorden INTEGER,"
          "fecha_prestamo DATE NOT NULL,"
          "fecha_devolucion DATE DEFAULT NULL,"
          "notas TEXT);",
        "CREATE TABLE trata_sobre("
          "clavelibro INTEGER NOT NULL "
          "REFERENCES libro(clavelibro) "
          "ON DELETE CASCADE "
          "ON UPDATE CASCADE,"
          "clavetema INTEGER NOT NULL "
          "REFERENCES tema(clavetema) "
          "ON DELETE CASCADE "
          "ON UPDATE CASCADE);",
        "CREATE TABLE escrito_por("
          "clavelibro INTEGER NOT NULL "
          "REFERENCES libro(clavelibro) "
          "ON DELETE CASCADE "
          "ON UPDATE CASCADE,"
          "claveautor INTEGER NOT NULL "
          "REFERENCES autor(claveautor) "
          "ON DELETE CASCADE "
          "ON UPDATE CASCADE);"
    };
	
    for(int i = 0; i < nTablas; i++) {
        sprintf(consulta, "SELECT COUNT(*) FROM %s;", tabla[i]);
        if(SQLITE_OK != sqlite3_exec(db, consulta, 0, 0, 0)) {
            cout << "La tabla " << tabla[i] << " no existe." << endl;
            if(SQLITE_OK != sqlite3_exec(db, create[i], 0, 0, 0)) {
               cout << "Error al crear la tabla " << tabla[i] << endl;
               return false;
            }
        }
    }
    return true;
}

Menús de acceso

Nuestra aplicación estará escrita para consola, de modo que necesitaremos alguna forma de comunicarnos con ella. Por comodidad, para el usuario, interactuaremos mediante un menú.

Aprovecharemos la disponibilidad de la base de datos para almacenar los menús en una tabla. Lo normal sería no mezclar los datos que maneja la aplicación con los que la hacen funcionar, pero en este caso nos limitaremos a añadir una tabla a la base de datos. Al contrario que el resto de las tablas, esta tendrá que contener información desde el principio, ya que los datos almacenados en esa tabla son imprescindibles para el funcionamiento de la aplicación.

Lo primero es diseñar un sistema de menús que nos permita hacer todas las tareas que indica el enunciado. Pero no nos interesa que todas las opciones se muestren desde el principio, ya que eso dificulta el manejo del programa. En lugar de eso crearemos una estructura en árbol para el menú, donde al activar ciertas opciones se despliegue un nuevo menú con las opciones disponibles. Por ejemplo, en un primer nivel mostraremos cinco opciones: "Ficheros maestros", "Libros", "Socios", "Préstamos" y "Salir". Si se elige "Ficheros maestros" se desplegará un segundo menú con las opciones de "Editoriales", "Autores", "Temas" y "Volver", etc.

Cada opción de menú tendrá asociado:

  • Un identificador único de opción de menú.
  • Un identificador de menú.
  • Una letra que active la opción.
  • El texto del menú.
  • Un identificador de menú hijo.
  • Un código de acción.

Aprovecharemos la estructura para almacenar los títulos de los submenús, para ello usaremos un valor especial de código de acción.

En nuestro proyecto añadiremos dos nuevos ficheros: menu.h y menu.cpp, que usaremos para declarar y definir las funciones, tipos y datos necesarios para manejar los menús.

En rigor, no necesitamos almacenar los menús en la base de datos, pero hacerlo tiene algunas ventajas, por ejemplo, permite traducir fácilmente los textos de los menús, inhibir algunas opciones, cambiar las teclas para activar ciertas opciones, etc.

El fichero menus.h queda así:

/*
 * Aplicación de ejemplo de uso de SQLite en C++
 * EjemploSQLite
 * Salvador Pozo, Con Clase (www.conclase.net)
 * Marzo de 2012
 * Fichero: menus.h
 * fichero de cabecera para implementar menús
 */

#include <sqlite/sqlite3.h>

#define TITULO              -1
#define SALIR               0
#define ABRIRMENU           1
#define NUEVAEDITORIAL      2
#define EDITAREDITORIAL     3
#define BORRAREDITORIAL     4
#define CONSULTAEDITORIAL   5
// Añadiremos más códigos de operación a medida que implementemos nuevas opciones

struct stmenu { // Estructura para inicializar tabla de menús
    int idmenu;
    char letra[2];
    char texto[64];
    int hijo;
    int accion;
};

// Prototipos:
bool IniciarMenu(sqlite3 *);
void MostrarMenu(sqlite3 *, int);
int LeerMenu(sqlite3 *, int&);

Usaremos tres funciones para manejar los menús:

IniciarMenu
Se encargará de crear la tabla "menu" y de insertar las filas necesarias. Si la tabla ya existe, no hará nada. Para obligar al programa a que genere la tabla de nuevo tendremos que borrarla manualmente desde la consola "sqlite3", con la sentencia "DROP TABLE menu;".
MostrarMenu
Evidentemente, esta función se encargará de mostrar el menú adecuado, dependiendo del nivel, que pasaremos como segundo parámetro.
LeerMenu
Leerá la respuesta del usuario, y devolverá el código de acción asociado a la elección, y si eso implica un cambio de nivel, modificará el valor del segundo parámetro. Este parámetro se pasa por referencia, a la entrada indica el nivel actual, y a la salida el nuevo nivel.

La implementación de estas funciones se hace en "menu.cpp":

/*
 * Aplicación de ejemplo de uso de SQLite en C++
 * EjemploSQLite
 * Salvador Pozo, Con Clase (www.conclase.net)
 * Marzo de 2012
 * Fichero: menus.cpp
 * fichero de implementación de menús
 */

#include <iostream>
#include "menus.h"

stmenu menu[] = {
    // Nivel, letra, texto, submenú, acción
    {1,"-","---MENU PRINCIPAL---",0,TITULO},
    {1, "1", "Maestros >", 2, ABRIRMENU},
    {1, "2", "Libros >", 3, ABRIRMENU},
    {1, "3", "Socios >", 4, ABRIRMENU},
    {1, "4", "Prestamos >", 5, ABRIRMENU},
    {1, "0", "Salir", 0, SALIR},
    {2,"-","---TABLAS MAESTRAS---",0,TITULO},
    {2, "1", "Editoriales >", 6, ABRIRMENU},
    {2, "2", "Autores >", 7, ABRIRMENU},
    {2, "3", "Temas >", 8, ABRIRMENU},
    {2, "0", "Salir <", 1, ABRIRMENU},
    {6,"-","---MENU EDITORIALES---",0,TITULO},
    {6, "1", "Nuevo", 0, NUEVAEDITORIAL},
    {6, "2", "Editar", 0, EDITAREDITORIAL},
    {6, "3", "Borrar", 0, BORRAREDITORIAL},
    {6, "4", "Consultar", 0, CONSULTAEDITORIAL},
    {6, "0", "Salir <", 2, ABRIRMENU},
    {3,"-","---MENU LIBROS---",0,TITULO},
    {3, "0", "Salir <", 1, ABRIRMENU},
    {4,"-","---MENU SOCIOS---",0,TITULO},
    {4, "0", "Salir <", 1, ABRIRMENU},
    {5,"-","---MENU PRESTAMOS---",0,TITULO},
    {5, "0", "Salir <", 1, ABRIRMENU},
    {7,"-","---MENU AUTORES---",0,TITULO},
    {7, "0", "Salir <", 2, ABRIRMENU},
    {8,"-","---MENU TEMAS---",0,TITULO},
    {8, "0", "Salir <", 2, ABRIRMENU}
};

using namespace std;

bool IniciarMenu(sqlite3 *db) {
    sqlite3_stmt *ppStmt;
    int rc;
    char consulta[100];

    if(SQLITE_OK != sqlite3_exec(db, "SELECT COUNT(*) FROM menu", 0, 0, 0)) {
        // if(SQLITE_OK != sqlite3_exec(db, "DROP TABLE menu;", 0, 0, 0)) return false;
        if(SQLITE_OK != sqlite3_exec(db, "CREATE TABLE menu(iditem INTEGER PRIMARY KEY,"
                                     "idmenu INTEGER,letra TEXT,texto TEXT,hijo INTEGER,accion INTEGER);", 0, 0, 0)) return false;
        if(SQLITE_OK != sqlite3_exec(db, "BEGIN;", 0, 0, 0)) return false;

        strcpy(consulta, "INSERT INTO menu(idmenu,letra,texto,hijo,accion) VALUES(@mid,@let,@txt,@hij,@acc);");
        rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
        if( rc!=SQLITE_OK ){
            cout << sqlite3_errmsg(db) << endl;
            return false;
        } else {
            for(int i = 0; i < sizeof(menu)/sizeof(stmenu); i++) {
                sqlite3_bind_int(ppStmt, sqlite3_bind_parameter_index(ppStmt, "@mid"), menu[i].idmenu);
                sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt, "@let"), menu[i].letra, -1, SQLITE_STATIC);
                sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt, "@txt"), menu[i].texto, -1, SQLITE_STATIC);
                sqlite3_bind_int(ppStmt, sqlite3_bind_parameter_index(ppStmt, "@hij"), menu[i].hijo);
                sqlite3_bind_int(ppStmt, sqlite3_bind_parameter_index(ppStmt, "@acc"), menu[i].accion);
                sqlite3_step(ppStmt);
                sqlite3_reset(ppStmt);
            }
            sqlite3_finalize(ppStmt);
        }
        if(SQLITE_OK != sqlite3_exec(db, "COMMIT;", 0, 0, 0)) return false;
    }
    return true;
}

void MostrarMenu(sqlite3 *db, int nivel) {
    sqlite3_stmt *ppStmt;
    int rc;
    char consulta[80];
    int nLineas;
    char titulo[64];

    // Contar items:
    sprintf(consulta, "SELECT COUNT(*) FROM menu WHERE idmenu=%d AND accion!=-1;", nivel);
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc==SQLITE_OK ){
        if(SQLITE_ROW == sqlite3_step(ppStmt)) {
            nLineas = sqlite3_column_int(ppStmt, 0);
        }
        sqlite3_finalize(ppStmt);
    }

    // 24 líneas en blanco (Borrar pantalla):
    for(int i = 0; i < 24; i++) cout << endl;

    // Titulo:
    strcpy(titulo, "---MENU---"); // Titulo por defecto
    sprintf(consulta, "SELECT texto FROM menu WHERE idmenu=%d AND accion=-1;", nivel);
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc==SQLITE_OK ){
        if(SQLITE_ROW == sqlite3_step(ppStmt)) {
            strcpy(titulo, (const char*)sqlite3_column_text(ppStmt, 0));
        }
        sqlite3_finalize(ppStmt);
    }

    cout << "\t\t" << titulo << "\n" << endl;

    strcpy(consulta, "SELECT letra,texto FROM menu WHERE idmenu=@mid AND accion!=-1;");
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc!=SQLITE_OK ){
        cout << "Error: " << sqlite3_errmsg(db) << endl;
    } else {
        sqlite3_bind_int(ppStmt, sqlite3_bind_parameter_index(ppStmt, "@mid"), nivel);
        while(SQLITE_ROW == sqlite3_step(ppStmt)) {
            cout << sqlite3_column_int(ppStmt, 0) << ") " << sqlite3_column_text(ppStmt, 1) << endl;
        }
        sqlite3_finalize(ppStmt);
    }
    // Más líneas en blanco:
    for(int i = 0; i < (18-nLineas); i++) cout << endl;
    cout << "\tOpcion: ";
}

int LeerMenu(sqlite3 *db, int& nivel) {
    char resp[2];
    sqlite3_stmt *ppStmt;
    int rc;
    char consulta[64];
    int retval;

    cin >> resp;
    sprintf(consulta, "SELECT hijo,accion FROM menu WHERE letra='%s' AND idmenu=%d;", resp, nivel);
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc!=SQLITE_OK ){
        cout << "Error: " << sqlite3_errmsg(db) << endl;
    } else {
        if(SQLITE_ROW == sqlite3_step(ppStmt)) {
            retval = sqlite3_column_int(ppStmt, 1);
            if(retval == ABRIRMENU) nivel = sqlite3_column_int(ppStmt, 0);
        }
        sqlite3_finalize(ppStmt);
    }
    return retval;
}

Por último, modificaremos el fichero "main.cpp" para integrar los menús:

/*
 * Aplicación de ejemplo de uso de SQLite en C++
 * EjemploSQLite
 * Salvador Pozo, Con Clase (www.conclase.net)
 * Marzo de 2012
 * Fichero: main.cpp
 * fichero principal
 * Incluir en el enlazador la librería libsqlite.a (sqlite)
 * Facilitar el acceso a la librería de enlace dinámico sqlite3.dll
 */

#include <iostream>
#include <sqlite/sqlite3.h>
#include "menus.h"

const int nTablas = 9;

bool VerificarTablas(sqlite3 *);

using namespace std;

int main()
{
    int rc;
    sqlite3 *db;
    int nivel=1;
    bool salir;

    // Abrir base de datos
    rc = sqlite3_open("biblioteca.db", &db);
    if(SQLITE_OK != rc) {
        cout << "Error: No se puede abrir la base de datos" << endl;
		return 1;
    }

    if(!VerificarTablas(db)) return -1;
    if(!IniciarMenu(db)) return -1;

    do {
        MostrarMenu(db, nivel);
        switch(LeerMenu(db, nivel)) {
            case ABRIRMENU:
               // Nada que hacer.
			   break;
            case NUEVAEDITORIAL:
            case EDITAREDITORIAL:
            case BORRAREDITORIAL:
            case CONSULTAEDITORIAL:
               cout << "No implementado" << endl;
               break;
            case SALIR:
               salir = true;
        }
    } while(!salir);

    // Cerrar base de datos
    sqlite3_close(db);
    return 0;
}
...
// El resto es igual que antes...

Las modificaciones en main se limitan a añadir una llamada a IniciarMenu, y un bucle en el que se muestra el menú, se lee la respuesta del usuario y se procesa el resultado, hasta que se elija la opción de salir.

Ahora ya podemos empezar a implementar las distintas opciones. Usaremos dos ficheros para cada submenú, uno con los prototipos de las funciones y otro con la implementación.