SDK de Android: Gestión de bases de datos Sqlite3 de varias tablas

Este tutorial proporcionará una aplicación interactiva de creación y administración de bases de datos de múltiples tablas para dispositivos Android. Se proporcionarán capacidades de búsqueda y navegación en la base de datos, así como una ventana emergente de entrada de consultas que tomará consultas de forma libre y mostrará los resultados en un cuadro de desplazamiento.

SQLite3 es una base de datos transaccional liviana con pequeños requisitos de memoria y almacenamiento en disco. Se usa ampliamente en sistemas operativos móviles como Android, iPhone, etc. Ahora es una base de datos completamente relacional con soporte de clave externa.

Una base de datos generalmente contiene una o más tablas. Las tablas están diseñadas para almacenar datos de cuatro tipos: Integer, Real, String y Blob (datos binarios o grandes). Hay muchos buenos tutoriales sobre la creación y gestión de bases de datos de una sola tabla.

Para una discusión completa de los conceptos básicos detrás de la creación de tablas, consulta:

• Foros de Marakana
• "Code Project"
• Otro blog de Android

Dado que Android no proporciona un administrador de interfaz de base de datos, estos tutoriales nos enseñan formas eficientes de crear y administrar una base de datos de una sola tabla.

En esencia, se requieren los siguientes pasos:

1. Primero se define una clase "Ayudante" o "Helper" de Base de Datos que se extiende desde la clase SQLiteOpenHelper. Esto ayuda a crear o abrir una base de datos, actualizar la base de datos y definir tablas, columnas, índices, disparadores y vistas para una base de datos.

2. Con esta clase auxiliar, se diseña una clase de adaptador de base de datos donde se programan las herramientas básicas de administración de base de datos CRUD (Crear, Leer, Actualizar y Eliminar).

3. Las consultas SQL se utilizan para administrar operaciones CRUD. La obtención de datos se realiza con el uso de cursores. Las bases de datos reales suelen tener más de una tabla. Esto crea problemas en la creación y gestión de las tablas.

El siguiente es el procedimiento estándar para un diseño de base de datos de tablas múltiples:

1. Dado que la base de datos está montada en el dispositivo Android, una vez creada, no se puede modificar en el dispositivo del teléfono. Los cambios requieren una intervención manual, como quitar la base de datos del dispositivo, modificarla con Sqlite3 Manager y volver a montarla en el dispositivo del teléfono.

2. Todas las tablas deben crearse al mismo tiempo en la función onCreate de la clase auxiliar de base de datos. Esta función se llama cuando se crea una instancia de la clase de adaptador de base de datos. Cada tabla debe tener su propio SQL de creación. El comando execSql debe ejecutarse para cada tabla por separado.

3. ¿Cómo gestionamos estas tablas? ¿Necesitamos adaptadores separados para cada tabla? Si alguien tiene una gran cantidad de tablas, la sobrecarga del adaptador sería enorme. Desafortunadamente, la mayoría de los tutoriales publicados se basan en este enfoque.

¿No sería bueno crear la base de datos, muchas tablas y administrarlas con una sola interfaz de adaptador? Sí, podría ser. Este tutorial describirá un diseño de base de datos de este tipo con dos interfaces gráficas de usuario (GUI) de Android utilizando dos clases de actividad de Android. La primera actividad, StartIntMulti, proporciona una interfaz de entrada de usuario simple donde se introducirán los diseños de la base de datos y la tabla. Después de completar la entrada, la segunda actividad, ManageIntMulti, proporciona la gestión básica de CRUD y una capacidad de navegación de tabla con búsqueda utilizando solo una clase de adaptador de base de datos. También se ha diseñado un cuadro de entrada de consultas en el que se puede ejecutar cualquier consulta independientemente de la interfaz de la tabla seleccionada.

Este tutorial asume que el lector está familiarizado con la creación y administración de la base de datos de una sola tabla. Seguiremos un buen ejemplo de base de datos de múltiples tablas de Android Sqlit.

Comencemos con la primera interfaz GUI que se muestra a continuación.

Esta es una interfaz necesaria para tener un creador de base de datos interactivo. La gestión de datos se maneja a través de una clase DataAttrib. Esta clase tiene varios miembros que ayudan a almacenar y obtener los datos de entrada.

Los dos primeros cuadros de edición requieren entradas de nombre de base de datos y número de versión. Ingresemos ChessDb como el nombre de la base de datos y 1 como el número de versión. Haz clic en el botón "Nueva base de datos" para guardar las entradas. La entrada se guarda a través de las funciones setDbName y setDbVersion de la clase. El miembro de la clase setDbStatus guarda el estado "nuevo" o "antiguo" de la base de datos (para una base de datos creada anteriormente, utiliza el botón "Base de datos antigua" y el botón "Iniciar base de datos" para pasar a la siguiente actividad).

Los siguientes tres cuadros de edición toman la entrada del nombre de la tabla. Para nuestros propósitos, ingresa "Players" como el nombre de la tabla. Los nombres de las tablas se guardan en una matriz tbl_Names[], y el número de tablas se registra en setNumTables.

Los siguientes dos cuadros de edición toman el nombre del campo y las entradas del tipo de campo. El primer campo de cada tabla es "_id", que es un tipo entero. Es la clave principal y se incrementa automáticamente. Se programa automáticamente y no es necesario introducirlo. En el nombre del campo, ingresa "fname" como la entrada del nombre del campo y "text" como una entrada de tipo (No uses "" en los cuadros de entrada). El "not null" está predeterminado en el propio programa. El nombre del campo y el tipo de campo se guardan en matrices bidimensionales, Col_Names[][] y Col_Types. El número de columnas se registra en la matriz Ncols[].

Presiona el botón "Agregar campo" para guardar la entrada. Esto borra el cuadro "Nombre de campo". Ahora, en el mismo cuadro de entrada "Nombre de campo", ingresa la segunda variable de campo "lname". Asegúrate de borrar el cuadro de entrada antes de ingresar nuevas entradas. La entrada de tipo es "text". Presiona el botón "Agregar campo" nuevamente. Como hemos terminado con la primera definición de tabla, presionaremos el botón "Crear tabla". Asegúrate de no presionar este botón a menos que se hayan definido todos los campos.

La "eliminación en cascada" debe estar "en eliminación de cascada" en la última línea. Observa que he cambiado la estructura de la tabla de la definida en la referencia para mantener la coherencia de las tablas. Si deseas un índice primario compuesto, puedes crear esta tabla a través de la GUI del creador de consultas en la siguiente interfaz.

Eso es todo por ahora. Si tienes más tablas, las puedes ingresar de la misma manera. La programación entre bastidores es una programación Java bastante estándar para aplicaciones de Android. Los datos se guardan en la clase DatabaseAttrib. Echa un vistazo a la estructura de esta clase. Consiste en la función getter y setter básica para las entradas descritas anteriormente. Presiona el botón "Iniciar base de datos" para pasar al siguiente nivel de administración de la base de datos.

La siguiente GUI muestra la gestión de la base de datos mediante la clase "Actividad ":

Examinemos el proceso general de la actividad de gestión de bases de datos. Cuando el usuario lanza la base de datos nueva o antigua, esta actividad, ManageIntMulti, recibe toda la información sobre las tablas, los nombres de campo y los tipos de la clase DataAttrib. En la parte superior de la GUI, además de las etiquetas TextBox, hay una interfaz de control Spinner selector de tabla. El control Spinner se completa con los nombres de estas tablas. Es un control de botón de radio en el que solo se puede seleccionar un elemento en cualquier momento. El elemento que se selecciona es un nombre de tabla en particular.

A continuación, se crea una instancia del adaptador de base de datos, tableAdapter, solo una vez. La función onCreate crea todas las tablas en la base de datos. Mira el fragmento de código de este proceso;

1

if (DATABASE_STATUS == "new" )

2

			{

3

4

			String str1 ="";

5

			 for ( int i= 0; i<Num_Tbls; i++)

6

			 {

7

				 String SuppString = " ";

8

				 str1 = " ";

9

			    for ( int j=0; j< Num_Cols[i]-1; j++)

10

		        {

11

12

		   	       SuppString =  SuppString + Var_Names[i][j] + " " + Var_Types[i][j] +" not null"+", ";

13

14

		        }

15

		         str1 = "create table " +

16

		                  Tbl_Names[i] +

17

		       " (" +

18

		           ID_FIELD + " integer primary key autoincrement, " +

19

				   SuppString + Var_Names[i][Num_Cols[i]-1] + " " + Var_Types[i][Num_Cols[i]-1] + " not null" +

20

		       ");";

21

22

			     db.execSQL(str1);

23

			     Log.d(DATABASE_NAME, "onCreate: " + Tbl_Names[i]);

24

25

			   }

26

27

			}

Esta es una de las formas en que se puede implementar el algoritmo de creación de tablas múltiples. Cada tabla, nombre de campo y tipo de campo se ensambla en una cadena SQL. El campo de ID es "_id" y está programado (no ingresado) para cada tabla. Después del ensamblaje de la cadena SQL, db.execSql (str1) ejecuta y crea la tabla SQL. En teoría, este método de entrada interactiva no debería limitar el número de tablas o el número de campos. Sin embargo, DataAttrib establece una limitación de dimensión de [10] para matrices unidimensionales y [10][10] para matrices bidimensionales. Puede aumentarse a cualquier valor deseado. Además, la función de verificación de duplicados requiere al menos dos columnas de tabla además del campo _id.

A continuación, se buscan los duplicados en una tabla con una declaración SQL que utiliza los dos primeros campos en los criterios de búsqueda. Los nombres de los campos condicionales se recuperan del cursor mediante la consulta PRAGMA en la tabla actual. El uso de la consulta PRAGMA alivia el uso de múltiples adaptadores de bases de datos. El cursor de la base de datos se genera con el proceso db.rawQuery(…). El SQL de búsqueda típico debería ser familiar para los usuarios de la base de datos ACCESS de la siguiente manera:

1

   String chkVar ="( " + xtbl + "." + colName[1] +"||' '||"+ xtbl + "." + colName[2]+" )";

2

   String sql =     "SELECT "  +

3

	                 chkVar + " AS Var1, "  +

4

	      " count" + chkVar + " " + " as NumDupes" +

5

	      " FROM " + xtbl +" Group by " + chkVar + " having ((count" + chkVar + ")>1);";

donde xtbl es la tabla seleccionada actualmente. Si se encuentran duplicados, el usuario recibe una alerta con alertas de Toast y los resultados se imprimen en la vista de desplazamiento debajo del cuadro de edición con la etiqueta "query". Los datos duplicados no se eliminan automáticamente. Se puede hacer de manera más eficiente utilizando Run Query y una interfaz CRUD.

A continuación, se configuran todos los componentes de una vista y se definen las funciones de devolución de llamada para los escuchas de los botones.

Hay muchas formas de ingresar datos en una tabla. Aquí utilizamos cuatro campos de entrada EditBox (F1, Fld2, Fld3 y Fld 4) para el manejo de datos. F1 está reservado para el campo _id y el usuario solo interactúa con él al eliminar o actualizar una tabla. Los otros campos son para nombres de campo, como fname y lname, en la tabla "Players". Si tienes más de tres entradas, puedes usar el cuadro "Query" para realizar cualquier operación CRUD. Ingresemos "Bobby" en Fld2 y "Fisher" en Fld3 (Nunca poner ", ya que se utilizan aquí sólo para aclarar). Presiona el botón "Crear". Esto guarda los datos en la tabla de la base de datos. Puedes ingresar tantos datos como desees. Examinemos este esquema de inserción de datos con un poco más de detalle. El siguiente es el fragmento de código relacionado.

1

private void createData()

2

  {

3

  	try

4

  	{

5

6

  	 	 String xtbl = input.getText().toString();

7

  	 	 String [] x = getInputStr();

8

  	 	 Cursor cursor = getPragma();

9

10

  		 int noCols = cursor.getColumnCount();

11

12

  		 int k = 0;

13

  	     String [] colName = new String[noCols];

14

15

  	     ContentValues values = new ContentValues();

16

17

  	 	  while (cursor.moveToNext()) {

18

  	      for (int i=0; i<cursor.getColumnCount(); i++)

19

  	      {

20

  	         colName[i] = cursor.getString(i);

21

22

  	      }

23

  	       if (k > 0 )

24

  	    	 values.put(colName[1], x[k]);

25

26

  	       k++;

27

  	 	 }

28

29

 	     db1.insert(xtbl, null, values);

30

 	     cursor.close();

31

  	}

32

  	catch (Exception e)

33

  	{

34

  		Log.e("sql Error", e.toString());

35

  		e.printStackTrace();

36

  	}

37

  }

Este es un esquema típico de inserción de datos. Como es habitual, hay muchas otras formas posibles de realizar la inserción de datos en una tabla. Aquí xtbl son los datos seleccionados en el control giratorio. Los nombres de los campos provienen de la variable x[] de las entradas. Llamamos PRAGMA para obtener los nombres de campo de esta tabla en particular como colName[]. Usamos una clase ContentValues para asignar las entradas a los nombres de columna adecuados. El db1.insert(…) finalmente inserta las entradas en la base de datos.

Las otras operaciones CRUD, a saber, Crear, Leer, Actualizar y Eliminar, siguen una lógica similar con las instrucciones SQL apropiadas.

El botón Leer requiere la entrada del número _id. Pon el número _id en el cuadro F1 y presiona este botón para que los datos se muestren en otros cuadros.

El botón "Actualizar" también funciona con la entrada _id. Primero, obtén los datos correctos con una operación de lectura, luego completa los cambios en otros cuadros de entrada. Presiona el botón Actualizar para guardar tus cambios.

El botón Eliminar también funciona con el campo _id. Ingresa el número _id y presiona el botón Eliminar. Los datos se eliminarán permanentemente sin ninguna advertencia a menos que se infrinjan algunas restricciones de clave externa.

Las filas de botones "first, rev, next, last y search" se utilizan para navegar por los datos en la tabla. Primero inserta tantas entradas como desees en la tabla. Con estos botones puedes navegar por tus datos de entrada. Si tienes muchas entradas, encontrar un registro sería realmente frustrante. El botón de búsqueda resulta útil para este propósito. Todas las búsquedas se realizan en la combinación de los dos primeros campos ingresados en Fld2 y Fld3. La búsqueda toma entradas parciales y no distingue entre mayúsculas y minúsculas.

La navegación de la base de datos utiliza los nombres de campo de PRAGMA y db.query adecuado para obtener todos los datos de la tabla en un cursor. La navegación se realiza con las funciones cursor.moveToFirst(), cursor.moveToLast(), cursor.setPosition(), cursor, getPosition, cursor.moveToPrevious() y cursor.moveToNext().

Las operaciones de búsqueda utilizan la opción LIKE. Se ha informado que este tipo de criterios ralentiza la búsqueda en tablas grandes. El sitio web para desarrolladores de Android tiene un método alternativo de búsqueda rápida. Para hacer que el proceso de búsqueda no distinga entre mayúsculas y minúsculas y tomar entradas parciales, se utiliza la siguiente condición where de la función searchData().

1

 String str2 = "lower(" + y[1] + ")" + " like "+ "lower("+ "'%"+xsearch[0].trim()+"%'"+ ")" +" and " +

2

  	 	               "lower(" + y[2] + ")" + " like "+ "lower("+ "'%"+xsearch[1].trim()+"%'"+ ")";

donde y[] es el nombre del campo y xsearch[] es la cadena de búsqueda. Este es un esquema muy útil. Muchos otros algoritmos de búsqueda eficientes también se utilizan en búsquedas en bases de datos. También se genera un índice en los dos campos de búsqueda para acelerar el proceso de búsqueda binaria.

A continuación, Run Query es una herramienta bastante útil para ejecutar consultas. Cuando presionas el botón Ejecutar consulta, aparece un cuadro de diálogo personalizado emergente. Este enfoque funciona bien para ingresar consultas largas debido al área de visualización limitada en el emulador. Escribe una consulta que devuelva algunos datos, como "SELECT * from cualquier_tabla"; y presiona el botón "Ir". Los datos obtenidos se mostrarán en un cuadro de desplazamiento debajo del botón "Ejecutar consulta". Las consultas se pueden ejecutar en cualquier tabla independientemente de la actualmente seleccionada en el control Spinner. Puedes hacer lo que quieras con la base de datos con esta opción. Sal de la pantalla de entrada de consultas presionando el botón "Salir".

El botón "Atrás" te regresa a la actividad de entrada de datos. Solo puedes reiniciar las bases de datos activas desde aquí. Para las nuevas, tendrás que salir e iniciar sesión en la aplicación nuevamente.

Una declaración SQL muy útil para una programación eficiente es el PRAGMA, que tiene la siguiente estructura:

PRAGMA table_info(nombre_de_la_tabla);

Esto genera una salida de cinco columnas. La segunda columna es el nombre de la variable de campo y la tercera es el tipo de campo.

Puedes cambiar la selección de la tabla y puedes hacer la gestión de tu base de datos a tu gusto. Todas estas operaciones para todas las tablas se realizan con una sola clase de adaptador de base de datos.

Cuando se selecciona una base de datos existente, el nombre y la versión de la base de datos se pasan de la GUI de entrada a la GUI de navegación. La base de datos se conecta en la función onCreate del adaptador proporcionando la ruta a la base de datos existente. Asegúrate de cambiar el nombre del paquete en la ruta para adecuarlo a tu ubicación. Los nombres de las tablas se obtienen utilizando el siguiente SQL:

1

"SELECT name FROM "+ " sqlite_master WHERE type='table' " +

2

	      " AND name NOT LIKE 'sqlite_%'  AND name NOT LIKE 'android%' UNION ALL" +

3

	      " SELECT name FROM sqlite_temp_master WHERE type='table' " +

4

	      " ORDER BY 1;";

donde 'table' es una variable interna del esquema de la base de datos. El resto de la gestión de la base de datos es la misma que en el caso de la nueva base de datos.

La entrada "Query" de la base de datos tiene una serie de limitaciones de caracteres del EditBox. La prueba crítica del control de "Run query" viene con el siguiente sql en el artículo referido;

1

 sql =     "SELECT "  +

2

    " Players.fname||' '|| Players.lname AS PlayerName, "  +

3

    " SUM((Games.weight*GameResults.score)) AS TotalWeightedScore " +

4

    " FROM GameResults " +

5

   " JOIN Players " +

6

   "         ON (GameResults.playerid=Players._id) "  +

7

   " JOIN Games  "   +

8

    "        ON (GameResults.gameid=Games._id) "  +

9

   " GROUP BY GameResults.playerid " +

10

   " ORDER BY TotalWeightedScore DESC;";

Esta es una versión Java de SQL. Para poner en el cuadro de Consulta, debes deshacerte de los signos de puntuación "y +. Utiliza el del artículo de referencia. Incluso esta consulta larga se puede ejecutar con la entrada de consulta emergente. Asegúrate de escribir la consulta correctamente, de lo contrario, puede fallar o no sucederá nada.

El botón "Salir" te permite salir de esta aplicación. Si creaste nuevas tablas, índices y activadores, tendrás que salir de la configuración actual y volver a ingresar para ver las cosas nuevas.

Es posible que hayas notado que el adaptador de base de datos solo se instancia una vez. El uso de PRAGMA y una programación cuidadosa alivia la necesidad de varios adaptadores engorrosos. Algunos de ustedes pueden estar usando esquemas similares en su trabajo diario, pero para el resto de nosotros, ¡este debería ser un esquema nuevo y eficiente!