> For the complete documentation index, see [llms.txt](https://mastermoviles.gitbook.io/persistencia-en-dispositivos-m-viles-ios/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mastermoviles.gitbook.io/persistencia-en-dispositivos-m-viles-ios/intro-1/2.2_api_sqlite.md).

# 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

```swift
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*

```swift
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.

```swift
class DBManager {
    var db : OpaquePointer? = nil   
}
```

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

```swift
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:

```swift
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`:

```swift
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:

```swift
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()`](http://sqlite.org/c3ref/prepare.html) 
  * 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](https://es.wikipedia.org/wiki/Tiempo_Unix)" (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:

```swift
//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`](https://developer.apple.com/reference/foundation/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](http://www.owsiak.org/?p=326).

### 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 `?`

```swift
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)

```swift
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

```swift
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.

```swift
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](https://www.sqlite.org/c3ref/bind_blob.html)

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:

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