Android / Bases de datos relacionales

Bienvenidos sean a este post, hoy hablaremos sobre un tema muy particular en bases de datos, esta es la relacion entre tablas por medio de campos relacionados entre si, lo cual nos permite que al situarnos sobre una fila nos conceda el acceso automaticamente a la fila de la otra tabla relacionada.

Para entender este concepto un poco mejor vamos a volver sobre nuestra app Asteroides y su base de datos de puntuaciones, para ello primero hagamos una nueva clase y la llamaremos AlmacenPuntuacionesSQLiteRel para reemplazar el codigo generado por el siguiente:

package org.example.asteroides;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.Vector;

public class AlmacenPuntuacionesSQLiteRel extends SQLiteOpenHelper
        implements AlmacenPuntuaciones {

    public AlmacenPuntuacionesSQLiteRel(Context contexto){
        super(contexto,"puntuaciones",null,2);
    }

    @Override
    public void onCreate(SQLiteDatabase db){
        db.execSQL("CREATE TABLE usuarios ("
                + "usu_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                +" nombre TEXT, correo TEXT)");
        db.execSQL("CREATE TABLE puntuaciones2 ("
                + "pun_id  INTEGER PRIMARY KEY AUTOINCREMENT,"
                + " puntos INTEGER, fecha LONG,"
                + " usuario INTEGER, FOREIGN KEY (usuario)"
                + " REFERENCES usuarios (usu_id))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db,
                          int oldVersion, int newVersion){}

    @Override
    public Vector<String> listaPuntuaciones(int cantidad) {
        Vector<String> resultado = new Vector<String>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT puntos, nombre "
                + "FROM puntuaciones2, usuarios "
                + "WHERE usuario = usu_id "
                + "ORDER BY puntos DESC LIMIT " + cantidad,null);
        while(cursor.moveToNext()){
            resultado.add(cursor.getInt(0) + " " + cursor.getString(1));
        }
        cursor.close();
        db.close();
        return resultado;
    }

    public void guardarPuntuacion(int puntos, String nombre,
                                  long fecha){
        SQLiteDatabase db = getWritableDatabase();
        guardarPuntuacion(db,puntos,nombre,fecha);
        db.close();
    }

    public void guardarPuntuacion(SQLiteDatabase db,
                                  int puntos, String nombre, Long fecha){
        int usuario = buscaInserta(db, nombre);
        db.execSQL("PRAGMA foreign_keys = ON");
        db.execSQL("INSERT INTO puntuaciones2 VALUES (null,"
                +  puntos  + "," + fecha + ",'" + usuario + "')");
    }

    private int buscaInserta(SQLiteDatabase db, String nombre){
        Cursor cursor = db.rawQuery("SELECT usu_id FROM usuarios " +
                "WHERE nombre='" + nombre+ "'",null);
        if (cursor.moveToNext()){
            int resultado = cursor.getInt(0);
            cursor.close();
            return resultado;
        } else {
            cursor.close();
            db.execSQL("INSERT INTO usuarios VALUES" +
                    " (null,'" + nombre+ "','correo@dominio.ar')");
            return buscaInserta(db, nombre);
        }
    }
}

Como extendemos a la clase SQLiteOpenHelper debemos redefinir al metodo onUpgrade() y a onCreate(), por ahora a onUpgrade simplemente lo definimos sin instrucciones, en onCreate() por medio del atributo informado usaremos el metodo execSQL dos veces, el primero sera para crear una tabla llamada usuarios con tres campos (el id, el nombre y el correo del usuario), en nuestro siguiente execSQL crearemos la siguiente tabla llamada puntuaciones2 la cual se crea de forma similar pero la unica diferencia radica en estos comandos:

FOREIGN KEY (usuario) REFERENCES usuarios (usu_id)

Donde diremos que el campo usuario de la tabla puntuaciones2, por medio de de FOREIGN KEY, y en REFERENCES primero le diremos a cual tabla debemos relacionarla, en este caso usuarios, y entre parentesis pongamos la columna de dicha base, en este caso usu_id, nuestro siguiente paso sera redefinir a los metodos de AlmacenPuntuaciones debido a que la implementamos, primero hablemos de listaPuntuaciones().

En este metodo primero crearemos un objeto llamado resultado de tipo Vector para almacenar el resultado de nuestro query, luego crearemos un objeto llamado db para permitir a nuestra base ser accesible para lectura, despues crearemos un cursor para conectarnos a la base de datos, por medio de rawQuery ejecutaremos un query:

SELECT nombre, puntos FROM puntuaciones2, usuarios WHERE usuario = usu_id 
     ORDER BY puntos DESC LIMIT cantidad

En este caso le diremos que nos traiga los valores de nombre y puntos de la tabla puntuaciones2 y usuarios, la condicion dice donde usuario, de la tabla puntuaciones2, sea igual a usu_id, de la tabla usuarios, a su vez lo ordene por los puntos en forma descendente y el limite estara establecido por la cantidad informada. Despues tendremos dos metodos que se llaman iguales pero son diferentes, en el primer caso recibiremos los tres datos esenciales (nombre, puntos y fecha) luego crearemos un objeto llamado db donde diremos que lo habilitaremos para escritura, despues llamaremos a guardarPuntuacion() pero esta vez con cuatro atributos y por ultimo cerraremos la conexion de la base, nuestro siguiente metodo sera el guardarPuntuacion con los cuatro atributos, aqui crearemos un objeto llamado usuario donde llamara a buscaInserta(), luego lo explicaremos, despues ejecutaremos un comando para que nos permite trabajar con llaves externas (FOREIGN KEY) y nuestro siguiente query insertara los datos en la tabla puntuaciones2 (usuario, puntos y fecha), nuestro ultimo metodo sera buscaInserta().

En este metodo primero crearemos el cursor y utilizaremos la base informada en db y el metodo rawQuery() para poder hacer una busqueda de si el valor informado en nombre existe, despues tendremos un condicional donde chequeara primero si existe un resultado y en caso de ser verdadero creara una variable llamada resultado donde le asignaremos ese valor y lo devolveremos y con esto asignaremos el valor a nombre en el metodo guardarPuntuacion, en caso contrario cierra a cursor, ejecuta un query para ingresar el nuevo dato, en este caso al correo le asignamos un valor generico para luego devolver el resultado de volver a hacer la busqueda en este metodo, esto deberia resultar en un valor dado que ahora nombre existe en la tabla usuarios.

Solo nos faltan dos modificaciones mas para poder probar nuestras modificaciones, la primera la haremos en MainActivity donde modificaremos a la variable almacen de la siguiente forma:

almacen = new AlmacenPuntuacionesSQLiteRel(this);

Para nuestra ultima modificacion debemos activar primero el emulador o conectar el dispositivo donde probamos nuestra app, una vez iniciado iremos al Device File Explorer y desde ahi eliminaremos todos los archivos del directorio databases, como se ve en la siguiente imagen

Los seleccionan y los eliminan, esto debemos hacerlo antes de probar nuestra app porque de lo contrario no se llamara al metodo onCreate porque ya existe una base de datos, con estas modificaciones realizadas podremos probar nuestra app, si la probamos sin jugar veremos una pantalla como esta

Si lo probamos y jugamos un poco deberemos tener una pantalla similar a esta

En este caso podemos observar como se comenzaron a almacenar nuestras puntuaciones y si vuelven al Device File Explorer tendran que tener generado los dos archivos que borramos previamente.

En resumen, hoy hemos hablado sobre como relacionar dos bases de datos, como relacionar un campo para vincular la informacion entre ambas tablas, hemos modificado a Asteroides para chequearlo, hemos visto cual es el encargado de efectuar esto, espero les haya sido util sigueme en Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.