[Xamarin.Forms] Uso de SQLite, múltiples tablas, relaciones y operaciones en cascada

Introducción

El trabajo con datos en dispositivos móviles se ha convertido ya en algo común y habitual en el desarrollo de aplicaciones. Existe una gran variedad de tipos de datos y formas de almacenamiento:

Archivos de texto. Texto plano o html cacheado en el espacio de almacenamiento aislado de la aplicación.
Imágenes. En el espacio de almacenamiento aislado de la aplicación o almacenadas en directorios conocidos del sistema.
Archivos serializados. Archivos XML o Json con objetos serializados.
Bases de datos. Cuando se requieren datos estructurados, obtener información más compleja con consultas avanzadas entre otro tipo de necesidades, la posibilidad de las bases de datos es la elección idónea.

Las ventajas de utilizar una base de datos son múltiples:

Almacenamiento estructurado con eficacia alta.
Posibilidad de utilizar consultas y aplicar filtros.
Posibilidad de reutilizar conocimientos de base de datos en la gestión de datos en nuestras aplicaciones móviles.

Introducción a SQLite

SQLite es un motor de base de datos Open Source utilizado en todas las plataformas móviles y adoptado tanto por Apple como Google como Microsoft. El uso de SQLite en aplicaciones móviles es una gran opción ya que:

La base de datos es pequeña y fácil de portar.
La base de datos se concentra en un pequeño archivo.
Implementa la mayor parte del estándar SQL92.

Arrancamos el proyecto

Comenzamos creando una aplicación Xamarin.Forms utilizando una librería NET Standard:

Nueva aplicación Xamarin.Forms usando Net Standard

Tras crear la aplicación, añadimos las carpetas básicas para aplicar el patrón MVVM además del paquete NuGet de Autofac para la gestión del contenedor de dependencias.

Con el proyecto y estructura base creada, vamos a añadir SQLite al proyecto. Para ello, vamos a usar NuGet. Vamos a añadir en cada proyecto de la solución la última versión disponible del paquete utilizando NuGet. El paquete a utilizar es sql-net-pcl, implementación Open Source con soporte a .NET.

Tras añadir la referencia vamos a crear una interfaz que defina como obtener la conexión con la base de datos y abstraer la funcionalidad específica de cada plataforma. Trabajando con SQLite, el único trabajo específico a implementar en cada plataforma es determinar la ruta a la base de datos.

public interface IPathService
{
     string GetDatabasePath();
}

En Android, la implementación de IPathService nos permite obtener la ruta a la base de datos.

[assembly: Dependency(typeof(PathService))]
namespace TodoSqlite.Droid.Services.Sqlite
{
     public class PathService : IPathService
     {
          public string GetDatabasePath()
          {
               string path = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
               return Path.Combine(path, AppSettings.DatabaseName);
          }
     }
}

NOTA: Utilizamos el atributo assembly:Dependency para poder realizar la resolución de la implementación con DependencyService.

En iOS:

[assembly: Dependency(typeof(PathService))]
namespace TodoSqlite.iOS.Services.Sqlite
{
     public class PathService : IPathService
     {
          public string GetDatabasePath()
          {
                string docFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
                string libFolder = Path.Combine(docFolder, "..", "Library", "Databases");

                if (!Directory.Exists(libFolder))
                {
                     Directory.CreateDirectory(libFolder);
                }

                return Path.Combine(libFolder, AppSettings.DatabaseName);
          }
     }
}

El archivo de la base de datos lo situamos dentro de la carpeta Library dentro del espacio de almacenamiento de la aplicación.

Y en UWP:

[assembly: Dependency(typeof(PathService))]
namespace TodoSqlite.UWP.Services.Sqlite
{
     public class PathService : IPathService
     {
          public string GetDatabasePath()
          {
               return Path.Combine(ApplicationData.Current.LocalFolder.Path, AppSettings.DatabaseName);
          }
     }
}

Todo listo para comenzar!

La definición de modelos

En nuestra aplicación, trabajaremos con elementos del listado ToDo, una única entidad sencilla.

public class TodoItem
{
     [PrimaryKey, AutoIncrement]
     public int Id { get; set; }
     public string Name { get; set; }
     public string Notes { get; set; }
     public bool Done { get; set; }
}

La gestión de campos especiales o relacionados las gestionamos mediante el uso de etiquetas. En nuestro ejemplo establecemos el campo Id como clave primaria gracias a la etiqueta PrimaryKey y además que autoincremente con el uso de AutoIncrement.

Trabajando con SQLite

Para trabajar con la base de datos utilizaremos DependencyService para obtener la implementación de IPathService y obtener la ruta a la base de datos en cada plataforma.

var databasePath = DependencyService.Get<IPathService>().GetDatabasePath();

Con la ruta de la base de datos, creamos una conexión:

var sqlCon = new SQLiteAsyncConnection(databasePath);

Comenzamos creando la tabla necesaria en la base de datos.

await _sqlCon.CreateTableAsync<TodoItem>().ConfigureAwait(false);

Continuamos con las operaciones básicas de CRUD. Para obtener la información almacenada en una tabla podemos acceder a la tabla y obtener el listado utilizando el método ToListAsync.

public async Task<IList<TodoItem>> GetAll()
{
     var items = new List<TodoItem>();
     using (await Mutex.LockAsync().ConfigureAwait(false))
     {
          items = await _sqlCon.Table<TodoItem>().ToListAsync().ConfigureAwait(false);
     }

     return items;
}

NOTA: Podemos realizar consultar SQL utilizando el método QueryAync.

A la hora de insertar, verificamos si estamos ante un registro existente o no, para realizar el registro de un nuevo elemento o actualizar uno existente con los métodos InsertAsync o UpdateAsync respectivamente.

public async Task Insert(TodoItem item)
{
      using (await Mutex.LockAsync().ConfigureAwait(false))
      {
           var existingTodoItem = await _sqlCon.Table<TodoItem>()
           .Where(x => x.Id == item.Id)
           .FirstOrDefaultAsync();

          if (existingTodoItem == null)
          {
               await _sqlCon.InsertAsync(item).ConfigureAwait(false);
          }
          else
          {
               item.Id = existingTodoItem.Id;
               await _sqlCon.UpdateAsync(item).ConfigureAwait(false);
          }
     }
}

Eliminar es una acción sencilla realizada con el método DeleteAsync.

await _sqlCon.DeleteAsync(item);

El resultado del ejemplo:

Tenéis el código fuente disponible e GitHub:

Múltiples tablas

Con lo visto hasta aquí, tenemos suficiente para gestionar una base de datos local en Android, iOS y Windows. Sin embargo, ¿cómo hacemos relaciones entre diferentes tablas?, ¿y operaciones en cascada que afecten a múltiples tablas a la vez?.

Con sqlite-net-pcl, podemos trabajar con diferentes tablas y realizar relaciones. Veamos un ejemplo:

public class Stock 
{ 
     [PrimaryKey, AutoIncrement] 
     public int Id { get; set; } 
     public string Symbol { get; set; } 
} 
 
public class Valuation 
{ 
     [PrimaryKey, AutoIncrement] 
     public int Id { get; set; } 
     [Indexed] 
     public int StockId { get; set; } 
     public DateTime Time { get; set; } 
     public decimal Price { get; set; } 
}

Es importante resaltar el uso de atributo Indexed. Estamos asociando las tablas Valuation y Stock.

Creamos ambas tablas utilizando el método CreateTable por cada tabla:

db.CreateTable<Stock>();       
db.CreateTable<Valuation>();

Y obtenemos la información relacionada con ambas tablas gracias al uso de queries:

return db.Query<Valuation> ("select * from Valuation where StockId = ?", stock.Id);

De esta forma obtendríamos los registros de la tabla Valuation dado un SotckId específico.

Esta es una forma simple de trabajar. Sin embargo, existe un wrapper de SQLite.NET llamado SQLite-Net Extensions que amplía funcionalidades como permitir gestionar relaciones (uno a uno, uno a varios, varios a uno y varios a varios), operaciones en cascada además de otras opciones interesantes de forma muy sencilla.

Vamos a regresar a nuestra aplicación de tareas. Para aprender como gestionar más de una tabla, vamos a añadir una segunda tabla de pasos en las tareas. De modo que, una tarea tendrá N pasos.

Veamos la creación de las tablas. Creamos una segunda tabla (recuerda, una clase) llamada Step:

[Table("Steps")]
public class Step
{
     [PrimaryKey, AutoIncrement]
     public int Id { get; set; }

     public string Name { get; set; }

     [ForeignKey(typeof(TodoItem))]
     public int WorkerId { get; set; }
}

Al crear directamente la definición, vamos a especificar relaciones entre tablas. En este caso, destacamos una propiedad que será la ForeignKey de la tabla TodoItem.

[Table("TodoItems")]
public class TodoItem
{
     [PrimaryKey, AutoIncrement]
     public int Id { get; set; }
     public string Name { get; set; }
     public string Notes { get; set; }
     public bool Done { get; set; }
     [OneToMany(CascadeOperations = CascadeOperation.CascadeInsert)]
     public List<Step> Steps { get; set; }
}

Gracias a las extensiones aportadas, podemos definir directamente la relación entre las tablas TodoItems y Steps. En este caso será un OneToMany. Por defecto, las operaciones no son recursivas. Podemos modificar este compartamiento estableciendo operaciones en cascada. En nuestro caso, usaremos CascadeOperations para establecer el insertar registros en cascada (al registrar un nuevo TodoItem, registraremos también sus Steps).

Para crear ambas tablas, al igual que con sqlite-net-pcl, utilizamos el método CreateTableAsync:

await _sqlCon.CreateTableAsync<Step>(CreateFlags.None).ConfigureAwait(false);
await _sqlCon.CreateTableAsync<TodoItem>(CreateFlags.None).ConfigureAwait(false);

Operaciones en cascada

A la hora de insertar registros, contamos con diferentes métodos en las extensiones de SQLite:

InsertWithChildren
InserOrReplaceWithChildren
InserAllWithChildren
InsertOrReplaceAllWithChildren

Su uso dependerá de si vamos a insertar o reemplazar y del número de registros.

NOTA: De cada método existe una version asíncrona.

await _sqlCon.InsertWithChildrenAsync(item, recursive: true).ConfigureAwait(false);

También existen otras opciones interesantes como:

Actualizar en cascada.
Eliminar en cascada.
Propiedades sólo de lectura.
Diferentes tipos de relaciones y relaciones inversas.
Etc.