El API básico de SQLite

Acceder a la BD

Vamos a ver aquí cómo abrir la base de datos para comenzar a trabajar con ella.

Abrir y cerrar la BD

En el API C de SQLite la base de datos se representa en nuestro código mediante un puntero a un struct de tipo sqlite3. En swift podemos representar un puntero de C con el tipo OpaquePointer, así que en nuestro código tendremos algo como

var db: OpaquePointer? = nil

Para abrir la BD se usa la función sqlite3_open, a la que hay que pasarle el path de la base de datos y la referencia a este struct

sqlite3_open(path_db, &db);

Vamos a crear una clase DBManager en la que implementaremos los métodos que interactúan con la base de datos. En esta clase creamos una variable miembro privada para almacenar la referencia a la base de datos.

class DBManager {
    var db : OpaquePointer? = nil   
}

Podemos implementar un inicializador que abra la base de datos suponiendo que está incluida en el bundle

class DBManager {
    var db : OpaquePointer? = nil

    init(conDB nombreDB : String) {
        if let dbPath = Bundle.main.path(forResource: nombreDB, ofType: "db") {
            if sqlite3_open(dbPath, &(self.db)) == SQLITE_OK {
                print("Base de datos \(dbPath) abierta OK")
            }
            else {
                let error = String(cString:sqlite3_errmsg(db))
                print("Error al intentar abrir la BD: \(error) ")
            }
        }
        else {
            print("El archivo no se encuentra")
        }
    }
}

Del código anterior debemos destacar los siguientes aspectos:

• Las llamadas al API de SQLite devuelven la constante SQLITE_OK si no ha habido error, así que para saber si sqlite3_open ha tenido éxito comprobamos si ha devuelto este valor.

• Si se produce un error, para ver cuál es podemos usar la función sqlite3_errmsg, que devuelve el mensaje del error más reciente asociado a una llamada hecha al API de SQLite. Al ser código C el mensaje de error se devuelve como un char *, ya que en C no existe el tipo String. En nuestro código convertimos este tipo a un String de Swift, para poder tratarlo (imprimirlo, en nuestro caso). Esta conversión la hacemos con el inicializador String(cString:)

• A la inversa, las funciones C que requieren un char * como parámetro aceptan directamente un String de Swift. Esto por ejemplo pasa al hacer el sqlite3_open, que en realidad requiere que se le pase un char * con el path de la BD.

Para cerrar la base de datos se usa la función sqlite3_close() a la que se le pasa como parámetro el puntero al struct con la información de la base de datos.

Abrir la BD con “permiso de escritura”

Un problema que tiene incluir físicamente la base de datos en el bundle de la aplicación es que este es de solo lectura. Eso quiere decir que no podríamos ejecutar consultas de actualización sobre la base de datos.

La forma más habitual de resolver este problema es crear una copia de la base de datos en algún directorio con permiso de escritura cuando se arranca la aplicación. Típicamente se usa el directorio Documents ya que es el más apropiado para este propósito.

Aquí tenemos un método que copia la BD del bundle al directorio Documents, si es que no se ha copiado ya:

func copyDB(conNombre nombre : String)->URL? {
    let fileManager = FileManager.default
    let docsURL = fileManager.urls(for: .documentDirectory, in: .userDomainMask)[0]
    let dbCopiaURL = docsURL.appendingPathComponent(nombre).appendingPathExtension("db")
    if fileManager.fileExists(atPath: dbCopiaURL.path) {
        return dbCopiaURL
    }
    else {
        if let dbOriginalURL = Bundle.main.url(forResource: nombre, withExtension: "db") {
            if (try? fileManager.copyItem(at: dbOriginalURL, to: dbCopiaURL)) != nil {
                return dbCopiaURL
            }
            else {
                return nil
            }
        }
        else {
            return nil
        }
    }
}

Tendremos que modificar el inicializador del apartado anterior para que llame a este método y después no abra la base de datos del bundle sino la de Documents:

class DBManager {
    var db : OpaquePointer? = nil

    init(conDB nombreDB : String) {
        if let dbCopiaURL = copyDB(conNombre: nombreDB) {
            if sqlite3_open(dbCopiaURL.path, &(self.db)) == SQLITE_OK {
                print("Base de datos \(dbCopiaURL) abierta OK")
            }
//continúa como antes...

Consultas de selección

Compilar y ejecutar una consulta de selección

El esquema general para ejecutar una consulta de selección y recorrer los registros resultantes es el siguiente:

1. Compilar la query (convertirla de SQL en "modo texto" a un formato "ejecutable" por SQLite)

2. Ir avanzando registro a registro por los resultados, mientras queden registros

   • Obtener los campos que nos interesen del registro actual, sabiendo el número de columna que ocupan. Las columnas empiezan en 0

   • Habitualmente “empaquetaremos” los valores del registro actual en un objeto o struct de Swift, e iremos construyendo una lista de resultados (un array es lo más sencillo).

3. Liberar la memoria ocupada por la query compilada

Vamos a ver un ejemplo de código que implementa este esquema:

let querySQL = "SELECT * FROM Personas"
var statement : OpaquePointer?;
var lista : [Persona] = [];

let result = sqlite3_prepare_v2(db, querySQL, -1, &statement, nil)
if (result==SQLITE_OK) {
    while (sqlite3_step(statement) == SQLITE_ROW) {
        let id = sqlite3_column_int(statement, 0)
        let nombre = String(cString: sqlite3_column_text(statement, 1))
        let persona = Persona(id: Int(id), nombre: nombre)
        lista.append(persona)
    }
}
sqlite3_finalize(statement);
for p in lista {
    print("\(p.id) \(p.nombre)")
}

• En la función sqlite3_prepare_v2()

  • Los dos primeros argumentos son el struct que representa la base de datos y la cadena con la query.

  • Aunque en el ejemplo no usamos esta funcionalidad se podrían poner varias sentencias SQL en la misma cadena y compilar solo una de ellas. El tercer argumento de indica la longitud en caracteres que queremos tomar de la query (-1 indica leer toda la cadena).

  • Con el último argumento SQLite nos informa qué parte de query queda por ejecutar, en este caso pasamos nil ya que no nos interesa esta funcionalidad.

• Vamos avanzando por los registros con sqlite_step(). Mientras que esta función devuelva SQLITE_ROW tenemos una nueva fila y podemos obtener sus campos con sqlite_column_XXX(), donde XXX es el tipo de campo

• De nuevo tenemos el problema de que como el API de SQLite está en C, devuelve char * para los campos de tipo cadena, y no String por lo que debemos hacer la conversión con el inicializador String(cString:)

• Aquí usamos la estrategia de “empaquetar” los datos obtenidos de la BD en un objeto y guardar todos los objetos en un array.

• Finalmente sqlite3_finalize libera la memoria ocupada por la query

Fechas en SQLite

SQLite no tiene el tipo fecha. Posibles alternativas son representar una fecha en formato cadena o bien como un entero, el típico "UNIX timestamp" (número de segundos transcurridos desde el 1 de enero de 1970). Por ejemplo si sabemos que la fecha está almacenada como un timestamp podríamos hacer algo como:

//supongamos que la columna 2 es una fecha en formato timestamp UNIX
let unix_time = sqlite3_column_int(statement, 2);
//TimeInterval es equivalente a Double. Hacemos una conversión
let fecha = Date(timeIntervalSince1970: TimeInterval(valor))
//La clase DateFormatter nos permite mostrar una fecha
let df = DateFormatter()
//Hay una serie de estilos predefinidos
df.dateStyle = .full
print("\(df.string(from: fecha))")

En el ejemplo se usa la clase DateFormatter para mostrar la fecha, se puede configurar para variar el locale y el formato concreto. Hay una serie de formatos predefinidos (.short, .medium, .full, ...) y también podemos especificar mediante una cadena de formato qué componentes queremos mostrar y en qué orden. Para más información consultar la documentación de DateFormatter.

También podríamos almacenar las fechas en formato cadena (formato año-mes-dia horas:minutos:segundos). Parsear las fechas será algo más tedioso que con timestamps, podéis consultar este ejemplo.

Consultas con parámetros

En el ejemplo del apartado anterior la consulta SQL era una cadena fija pero en muchos casos necesitaremos parametrizar la consulta (por ejemplo obtener todos los alumnos cuyo apellido empiece por una letra, o los nacidos después de una fecha determinada).

Aunque podemos construir el String concatenando subcadenas es más "limpio" usar parámetros, que además nos protegen contra posibles intentos de inyección SQL. Al igual que en la mayoría de BD los parámetros se especifican con el símbolo ?

let querySQL = @"SELECT * FROM alumnos WHERE fecha_nacimiento < ?"

Para instanciar el parámetro con un determinado valor usaremos la familia de funciones sqlite3_bind_XXX, en la que las XXX indican el tipo de datos del parámetro (int, double, text,…). Esta instanciación se debe hacer después de "preparar" la query con sqlite3_prepare_v2 pero antes de ejecutarla con sqlite3_step().

Por ejemplo, vamos a buscar todas las personas que tengan más de 18 años (cuya fecha de nacimiento sea anterior a la actual restándole 18 años)

let querySQL = "SELECT * FROM alumnos WHERE fecha_nacimiento<?"
var statement : OpaquePointer?

let result = sqlite3_prepare_v2(db, querySQL, -1, &statement, nil)
let cal = Calendar.current
let hace18 = cal.date(byAdding: Calendar.Component.year, value: -18, to: Date(), wrappingComponents: false)
sqlite3_bind_int(statement, 1, Int32(hace18!.timeIntervalSince1970))

Nótese que para instanciar un parámetro debemos conocer su posición y que las posiciones de los parámetros comienzan en 1, no en 0.

En SQLite también se pueden definir parámetros por nombre con el formato :nombre, por ejemplo

let querySQL = @"SELECT * FROM alumnos WHERE fecha_nacimiento < :fecha";

No obstante el API C no nos permite instanciar el parámetro directamente por nombre. Primero debemos obtener su posición con la función sqlite3_bind_parameter_index(sentencia,nombre) y luego aplicar la ya conocida sqlite3_bind_XXX().

Consultas de actualización

Como ya hemos comentado, para que se puedan ejecutar consultas de actualización sobre la base de datos esta tiene que estar almacenada en un directorio con permisos de escritura

Las consultas de actualización se manejan de un modo similar a las de selección: se preparan con sqlite3_prepare_v2(), se vinculan los parámetros si estos existen con sqlite3_bind_XXX() y se ejecutan con sqlite3_step(). Por supuesto en ellas no hay bucle para recorrer los resultados.

let querySQL = "INSERT INTO alumnos (nombre, fecha_nacimiento) VALUES (?,?)"
var statement : OpaquePointer?
sqlite3_prepare_v2(db, querySQL, -1, &statement, nil);
sqlite3_bind_text(statement, 1, nombre, -1, nil);
sqlite3_bind_int(statement, 2, Int32(fechaNacimiento.timeIntervalSince1970));
let result = sqlite3_step(statement);
if (result==SQLITE_DONE) {
    print("Registro almacenado OK");
}

Como vemos, cuando se ejecuta con éxito una consulta de actualización, la ejecución devuelve el valor SQLITE_DONE.

Nótese que el sqlite3_bind_text tiene un par de parámetros adicionales con respecto a sqlite3_bind_int. En los casos más habituales se pasan siempre los valores -1 y nil respectivamente. Para ver más información sobre estos parámetros, consultar la documentación de SQLite

Podemos contar cuántas filas han sido afectadas por la consulta de actualización (en el ejemplo cuántas filas se han insertado, o sea 1) con la función sqlite3_changes() a la que hay que pasarle el struct que representa a la base de datos:

var db : OpaquePointer?
...
print("Filas afectadas: \(sqlite3_changes(db))";