[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.

Estructura del proyecto

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.

sqlite-net-pcl

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:

Ver 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.

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

Ver GitHub

Recordad que podéis dejar cualquier comentario, sugerencia o duda en los comentarios.

Más información

Anuncios

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

  1. Buenas tardes.
    Ante todo quería agradecer lo útil que es este blog para los que estamos aprendiendo a programar con Xamarin.

    Y mi duda es la siguiente. En una aplicacion con varias tablas (8, por decir un número) en la que las operaciones de guardar y borrar, etc se hacen desde diferentes pantallas de la aplicación, ¿hay que implementar en la clase SqliteService las funciones de Insert, Remove, GetAll para cada una de las tablas??? Entiendo que no porque eso sería repetir mucho código casi igual… ¿Cómo habría que hacer para que por ejemlo a la función Insert le llegue un parámetro de tipo tabla y en función de la tabla que sea guarde el registro en el lugar correspondiente?

    Espero haberme explicado bien. Muchas gracias y un saludo.

      • Llevo unos días intentando hacer lo que comenté pero no lo consigo. Mirando por mi cuenta llegué a la conclusión de que la forma de hacerlo es con Generics. He declarado una intefaz para el campo id (común a todas las tablas) para que se herede por las clases tabla. Y la cabecera del método Insert la he cambiado por Task Insert(T registry) where T : ITables, new(); donde ITables es la interface mencionada. Lo que sucede es que me da un error en tiempo de ejecución en la línea…
        var existingRegistry = await _sqlCon.Table()
        .Where(x => x.Id == registry.Id).FirstOrDefaultAsync();
        Da la sensación de que en _sqlCon.Table() es donde está el fallo, porque si en lugar de la T (para que sirva con cualquier tabla) uso una clase en concreto (código original) sí funciona… No sé por qué puede ser esto…

        ¿Me podrías ayudar a encontrar una solución para adaptar el código original y que los métodos Insert, Remove, GetAll, etc… sirvan para cualquier tabla con la que se les invoque?

      • La línea que falla en tiempo de ejecución es ésta, que en el comentario anterior me he comido el …
        var existingRegistry = await _sqlCon.Table()
        .Where(x => x.Id == registry.Id).FirstOrDefaultAsync();

      • Veo que el que se lo está comiendo no soy yo, si no el blog…
        En _sqlCon.Table(), entre la palabra Table y el paréntesis va lo siguiente:
        Símbolo de menor que una T y símbolo de mayor que.

        Igual que entre la palabra Insert de la cabecera y (T registry).

        Y tb se ha comido las palabras Public, virtual y async de antes de Task

      • Hola,

        Debo revisar el tema de los comentarios en el blog, no sabía que se “come” carácteres.
        A por la duda. Te contesto ahora rápido me apunto tu duda como idea para hacer un ejemplo / artículo.
        Puedes usar tipos genéricos de la siguiente forma:

        public async Task Insert(T item)
        {
        using (await Mutex.LockAsync().ConfigureAwait(false))
        {
        await _sqlCon.InsertWithChildrenAsync(item, recursive: true).ConfigureAwait(false);
        }
        }

        Para hacer algo más genérico (y completo) deberías crearte una interfaz similar a:

        T Insert(T model);
        T Update(T model);
        bool Delete(T model);
        T Select(int pk) where T : new();
        T[] SelectAll() where T : new();

        Creas una implementación de la interfaz con tipos genéricos. Posteriormente, podemos continuar de diferentes formas. Una sencilla sería, crear una clase por cada tabla. Por ejemplo, por la tabla TodoItem:

        public class TodoItemService : GenericSqlService, IService
        {
        public TodoItemService() : base() { }
        }

        Espero que me haya quedado fácil de entender.

        Un saludo.

  2. Siguiendo el segundo de los ejemplos que me indicas, me surge una duda con la sintaxis en una de las partes en la que he adaptado el código…
    Voy a ver si puedo explicarme bien teniendo en cuenta el inconveniente de la desaparición de cirtos caracteres (jeje)…

    La línea de código en la que tengo la duda es:
    builder.RegisterType().As();
    de la clase ViewModelLocator.

    Porque al adaptarla al código del segundo ejemplo, como te decía, la línea queda algo así (según cómo lo he entendido, que puede ser que esté equivocado):
    builder.RegisterType<GenericSqliteRepository>().As<ISqliteService>();
    De forma que T sea algo genérico y que sirva para todas las clases que se correspondan con las tablas de la BD.
    Pero me da un error de compilación porque no sé cómo indicarle sintácticamente al compilador lo de la T.

    Me alegro que tome todo esto como idea para hacer un ejemplo porque sería genial. Y vamos, ya estoy deseando ver cómo hay que hacer todo…

    Saludos y gracias.

  3. A ver, empizo con las apreciaciones:
    Línea de código original:
    builder.RegisterTypeMENORQUESqliteServiceMAYORQUE().AsMENORQUEISqliteServiceMAYORQUE();

    Línea de código adaptada:
    builder.RegisterTypeMENORQUEGenericSqliteRepositoryMENORQUE T MAYORQUEMAYORQUE().AsMENORQUEISqliteServiceMENORQUE T MAYORQUEMAYORQUE();

    Espero que se entienda.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

w

Conectando a %s