¿Hay algún método que ayude a resolver problemas comunes de SQLite?

A menudo los errores relativamente simples causan problemas a menudo agravados por un malentendido de SQLite.

Por ejemplo:-

  • tablas y columnas no encontradas porque a menudo se supone que el método onCreate de un DBHelper (subclase de la clase SQLiteOpenHelper ) se ejecuta cada vez que se crea una instancia de DBHelper o cada vez que se ejecuta la aplicación. (Nota: onCreate solo se invoca automáticamente cuando se crea una Base de datos por primera vez y solo cuando se intenta utilizar uno de los SQLiteDatabase getReadableDatabase o getWriteableDatabse ; si se cambia la estructura / esquema de la base de datos, hay 3 formas simples de forzar onCreate ejecutar, a) borrar los datos de la aplicación, b) desinstalar la aplicación o b) si el método onUpgrade invoca el método onCreate (después de soltar la (s) tabla (s)) luego boost el número de versión de la base de datos).
  • Inserciones / Actualizaciones no funcionan pero no fallan.

Para los principiantes a SQLite también puede ser exasperante / desalentador no poder ver fácilmente lo que contiene la base de datos.

Entonces, ¿hay utilidades comunes que puedan ayudar?

¡Nota! esto tiene la intención de ser una pregunta para compartir el estilo de Q & A del conocimiento .

Las siguientes son algunas utilidades comunes que los principiantes pueden encontrar útiles, están diseñadas para ser no específicas y funcionan en cualquier base de datos / tabla.

Actualmente existen los siguientes métodos potencialmente útiles:

  • getAllRowsFromTable para getAllRowsFromTable un Cursor con todas las filas.
  • logCursorColumns para escribir las Columnas en un Cursor para el registro.
  • logCursorData para escribir los datos y las columnas del cursor en el registro.

¡Nota! Las solicitudes / sugerencias de adiciones / enmiendas serán definitivamente consideradas.

Además, hay algunos métodos utilizados por los anteriores o se utilizaron para probar / crear lo anterior:

  • getEmptyColumnLessCursor ( 100 puntos para el uso de este método 🙂 ).
  • getBytedata para devolver una cadena hexadecimal de la matriz de bytes dada (para el manejo de BLOB).
  • convertBytesToHex para convertir una matriz de bytes en una cadena hexadecimal (para el manejo de BLOB).

getAllRowsFromTable

El uso previsto es para crear un cursor para ser posteriormente examinado por los métodos logCursorColumns y logCursorData . Esto no debe usarse en una aplicación de producción (no es que no pueda ser).

Esto tiene la firma: –

 public static Cursor getAllRowsFromTable( SQLiteDatabase db, String tablename, boolean use_error_checking, String forceRowidAs) 

Dónde:-

  • El primer parámetro es la base de datos SQLite (requerida debido a la naturaleza genérica).
  • El segundo parámetro es el nombre de la tabla desde la que se obtienen los datos.
  • El tercer parámetro, si es verdadero, comprobará si la tabla existe antes de crear el cursor escribiendo en el registro si la tabla no está en la base de datos.
  • El cuarto parámetro, si no es nulo o si una cadena de longitud> 0, creará una columna adicional, nombrada de acuerdo con el parámetro con el contenido de ROWID (previsto para ayudar cuando no se haya proporcionado ningún alias para ROWID y, por lo tanto, ROWID no incluido). ¿Qué es ROWID?

Ejemplo de uso: –

  mEventsDBHelper = new EventsDBHelper(this); // Get all rows from table (this exist or should do) Cursor events = CommonSQLiteUtilities.getAllRowsFromTable( mEventsDBHelper.getEventsDB(), EventsDBHelper.TBNAME, CommonSQLiteUtilities.ERROR_CHECKING_ON, null ); // Deisgned to be problematic ie no such table Cursor ooops = CommonSQLiteUtilities.getAllRowsFromTable( mEventsDBHelper.getEventsDB(), "NOTATABLE", CommonSQLiteUtilities.ERROR_CHECKING_ON, "rumplestiltskin" ); 
  • La segunda invocación da como resultado la siguiente línea en el registro: –

     D/SQLITE_CSU: Table NOTATABLE was not located in the SQLite Database Master Table. 
  • Siempre se devolverá un Cursor, aunque ese Cursor puede no tener filas ni columnas (por ejemplo, en el caso en que la tabla no estaba en la base de datos).

  • Las excepciones quedan atrapadas e informadas cuando se consulta la tabla de la base de datos (si es así). por ejemplo, especificando IS como el 4º parámetro (tratando efectivamente de usar ROWID AS IS , que SQLIte no le gusta, dará como resultado algo similar a: –


 10-09 18:57:52.591 3835-3835/? E/SQLiteLog: (1) near "IS": syntax error 10-09 18:57:52.592 3835-3835/? D/SQLITE_CSU: Exception encountered but trapped when querying table events Message was: near "IS": syntax error (code 1): , while compiling: SELECT rowid AS IS , * FROM events 10-09 18:57:52.592 3835-3835/? D/SQLITE_CSU: Stacktrace was: 10-09 18:57:52.592 3835-3835/? W/System.err: android.database.sqlite.SQLiteException: near "IS": syntax error (code 1): , while compiling: SELECT rowid AS IS , * FROM events 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034) 10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202) 10-09 18:57:52.592 3835-3835/? W/System.err: at mjt.sqlitedbexamples.CommonSQLiteUtilities.getAllRowsFromTable(CommonSQLiteUtilities.java:97) 10-09 18:57:52.592 3835-3835/? W/System.err: at mjt.sqlitedbexamples.MainActivity.onCreate(MainActivity.java:61) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.app.Activity.performCreate(Activity.java:5990) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.app.ActivityThread.access$800(ActivityThread.java:151) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.os.Handler.dispatchMessage(Handler.java:102) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.os.Looper.loop(Looper.java:135) 10-09 18:57:52.593 3835-3835/? W/System.err: at android.app.ActivityThread.main(ActivityThread.java:5254) 10-09 18:57:52.593 3835-3835/? W/System.err: at java.lang.reflect.Method.invoke(Native Method) 10-09 18:57:52.593 3835-3835/? W/System.err: at java.lang.reflect.Method.invoke(Method.java:372) 10-09 18:57:52.593 3835-3835/? W/System.err: at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903) 10-09 18:57:52.593 3835-3835/? W/System.err: at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698) 
  • El Cursor devuelto estará vacío sin filas o columnas.

logCursorColumns

Está destinado a ser utilizado para escribir información de columna sobre un cursor (que normalmente reflejaría la base de datos, especialmente si se usa getAllRowsFromTable ).

Esto tiene la firma: –

  public static void logCursorColumns(Cursor csr) 

Dónde :-

  • El primer parámetro es un Cursor (cualquier Cursor).

Ejemplo de uso: –

¡Nota! Siguiendo con el ejemplo anterior.

  CommonSQLiteUtilities.logCursorColumns(events); CommonSQLiteUtilities.logCursorColumns(ooops); 

Esto dará como resultado una salida a lo largo de las líneas de:

 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: logCursorColumns invoked. Cursor has the following 8 columns. 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 1 is _id 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 2 is starts 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 3 is starts_timestamp 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 4 is ends 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 5 is ends_timestamp 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 6 is description 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 7 is counter 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: Column Name 8 is bytedata 10-09 18:57:52.593 3835-3835/? D/SQLITE_CSU: logCursorColumns invoked. Cursor has the following 0 columns. 

logCursorData

Esto es muy similar a logCursorColumns salvo que proporciona columnas más extensas de información regrabadora y que también proporciona información que regraba los datos reales almacenados en la base de datos.

Esto tiene la firma: –

  public static void logCursorData(Cursor csr) 

Dónde :-

  • El primer parámetro es un Cursor (cualquier Cursor).

Ejemplo de uso: –

¡Nota! Siguiendo con el ejemplo anterior.

  CommonSQLiteUtilities.logCursorData(events); CommonSQLiteUtilities.logCursorData(ooops); 

Esto dará como resultado una salida a lo largo de las líneas de:

 10-09 19:30:31.801 1455-1455/? D/SQLITE_CSU: logCursorData Cursor has 6 rows with 8 columns. 10-09 19:30:31.801 1455-1455/? D/SQLITE_CSU: Information for row 1 offset=0 For Column _id Type is INTEGER value as String is 1 value as long is 1 value as double is 1.0 For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9 For Column starts_timestamp Type is INTEGER value as String is 1507537831783 value as long is 1507537831783 value as double is 1.507537831783E12 For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column description Type is STRING value as String is TESTEVENT 001 just description value as long is 0 value as double is 0.0 For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 00000000 10-09 19:30:31.802 1455-1455/? D/SQLITE_CSU: Information for row 2 offset=1 For Column _id Type is INTEGER value as String is 2 value as long is 2 value as double is 2.0 For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9 For Column starts_timestamp Type is INTEGER value as String is 1507537831785 value as long is 1507537831785 value as double is 1.507537831785E12 For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column description Type is STRING value as String is TESTEVENT 002 description and bytearray value as long is 0 value as double is 0.0 For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 0001020304050607080900 10-09 19:30:31.802 1455-1455/? D/SQLITE_CSU: Information for row 3 offset=2 For Column _id Type is INTEGER value as String is 3 value as long is 3 value as double is 3.0 For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9 For Column starts_timestamp Type is INTEGER value as String is 1507537831789 value as long is 1507537831789 value as double is 1.507537831789E12 For Column ends Type is INTEGER value as String is 15254678 value as long is 15254678 value as double is 1.5254678E7 For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column description Type is STRING value as String is TESTEVENT 003 desc, bytes and endint value as long is 0 value as double is 0.0 For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 7F7E7D7C 10-09 19:30:31.802 1455-1455/? D/SQLITE_CSU: Information for row 4 offset=3 For Column _id Type is INTEGER value as String is 4 value as long is 4 value as double is 4.0 For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9 For Column starts_timestamp Type is INTEGER value as String is 1507537831792 value as long is 1507537831792 value as double is 1.507537831792E12 For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column description Type is STRING value as String is TESTEVENT 004 desc, bytes and endlong value as long is 0 value as double is 0.0 For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 38424C56606A747E 10-09 19:30:31.803 1455-1455/? D/SQLITE_CSU: Information for row 5 offset=4 For Column _id Type is INTEGER value as String is 5 value as long is 5 value as double is 5.0 For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9 For Column starts_timestamp Type is INTEGER value as String is 1507537831794 value as long is 1507537831794 value as double is 1.507537831794E12 For Column ends Type is INTEGER value as String is 1699999999 value as long is 1699999999 value as double is 1.699999999E9 For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column description Type is STRING value as String is TESTEVENT 005 desc, endint value as long is 0 value as double is 0.0 For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 00000000 10-09 19:30:31.803 1455-1455/? D/SQLITE_CSU: Information for row 6 offset=5 For Column _id Type is INTEGER value as String is 6 value as long is 6 value as double is 6.0 For Column starts Type is INTEGER value as String is 1507537831 value as long is 1507537831 value as double is 1.507537831E9 For Column starts_timestamp Type is INTEGER value as String is 1507537831796 value as long is 1507537831796 value as double is 1.507537831796E12 For Column ends Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column ends_timestamp Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column description Type is STRING value as String is TESTEVENT 006 desc, endlong value as long is 0 value as double is 0.0 For Column counter Type is INTEGER value as String is 0 value as long is 0 value as double is 0.0 For Column bytedata Type is BLOB value as String is unobtainable! value as long is unobtainable! value as double is unobtainable! value as blob is 00000000 

y para el Cursor vacío (ooops): –

 10-09 19:30:31.804 1455-1455/? D/SQLITE_CSU: logCursorData Cursor has 0 rows with 0 columns. 

El código CommonSQLiteUtilities.java: –

 public class CommonSQLiteUtilities { public static final boolean ERROR_CHECKING_ON = true; public static final boolean ERROR_CHECKING_OFF = false; // SQLite MASTER TABLE definitions static final String SQLITE_MASTER = "sqlite_master"; static final String SM_TABLE_TYPE_COLUMN = "type"; static final String SM_NAME_COLUMN = "name"; static final String SM_TABLENAME_COLUMN = "tbl_name"; static final String SM_ROOTPAGE_COLUMN = "rootpage"; static final String SM_SQL_COLUMN = "sql"; static final String SM_TYPE_TABLE = "table"; static final String SM_TYPE_INDEX = "index"; static final String CSU_TAG = "SQLITE_CSU"; private CommonSQLiteUtilities() {} /** * Generic get all rows from an SQlite table, * allowing the existence of the table to be checked and also * allowing the ROWID to be added AS a supplied string * * @param db The SQLiteDatabase * @param tablename The name of the table from which the * returned cursor will be created from; * Note! * @param use_error_checking Whether ot not to try to detect errors * currently just table doesn't exist, * true to turn on, false to turn off * ERROR_CHECKING_ON = true * ERROR_CHECKING_OFF = false * @param forceRowidAs If length of string passed is 1 or greater * then a column, as an alias of ROWID, will be * added to the cursor * @return the extracted cursor, or in the case of the * underlying table not existing an empty cursor * with no columns */ public static Cursor getAllRowsFromTable(SQLiteDatabase db, String tablename, boolean use_error_checking, String forceRowidAs) { String[] columns = null; // Tablename must be at least 1 character in length if (tablename.length() < 1) { Log.d(CSU_TAG,new Object(){}.getClass().getEnclosingMethod().getName() + " is finishing as the provided tablename is less than 1 character in length" ); return new MatrixCursor(new String[]{}); } // If use_error_checking is true then check that the table exists // in the sqlite_master table if (use_error_checking) { Cursor chkcsr = db.query(SQLITE_MASTER,null, SM_TABLE_TYPE_COLUMN + "=? AND " + SM_TABLENAME_COLUMN + "=?", new String[]{SM_TYPE_TABLE,tablename}, null,null,null ); // Ooops table is not in the Database so return an empty // column-less cursor if (chkcsr.getCount() < 1) { Log.d(CSU_TAG,"Table " + tablename + " was not located in the SQLite Database Master Table." ); // return empty cursor with no columns return new MatrixCursor(new String[]{}); } chkcsr.close(); } // If forcing an alias of ROWID then user ROWID AS ???, * if(forceRowidAs != null && forceRowidAs.length() > 0) { columns = new String[]{"rowid AS " +forceRowidAs,"*"}; } // Finally return the Cursor but trap any exceptions try { return db.query(tablename, columns, null, null, null, null, null); } catch (Exception e) { Log.d(CSU_TAG,"Exception encountered but trapped when querying table " + tablename + " Message was: \n" + e.getMessage()); Log.d(CSU_TAG,"Stacktrace was:"); e.printStackTrace(); return new MatrixCursor(new String[]{}); } } /** * Create and return a Cursor devoid of any rows and columns * Not used, prehaps of very little use. * @param db The Sqlite database in which the cursor is to be created * @return The empty Cursor */ private static Cursor getEmptyColumnLessCursor(SQLiteDatabase db) { return new MatrixCursor(new String[]{}); } /** * Write column names in the passed Cursor to the log * @param csr The Cursor to be inspected. */ public static void logCursorColumns(Cursor csr) { Log.d(CSU_TAG, new Object(){}.getClass().getEnclosingMethod().getName() + " invoked. Cursor has the following " + Integer.toString(csr.getColumnCount())+ " columns."); int position = 0; for (String column: csr.getColumnNames()) { position++; Log.d(CSU_TAG,"Column Name " + Integer.toString(position) + " is " + column ); } } /** * Write the contents of the Cursor to the log * @param csr The Cursor that is to be displayed in the log */ public static void logCursorData(Cursor csr) { int columncount = csr.getColumnCount(); int rowcount = csr.getCount(); int csrpos = csr.getPosition(); //<<< added 20171016 to Log.d(CSU_TAG, new Object(){}.getClass().getEnclosingMethod().getName() + " Cursor has " + Integer.toString(rowcount) + " rows with " + Integer.toString(columncount) + " columns." ); csr.moveToPosition(-1); //Ensure that all rows are retrieved <<< added 20171016 while (csr.moveToNext()) { String unobtainable = "unobtainable!"; String logstr = "Information for row " + Integer.toString(csr.getPosition() + 1) + " offset=" + Integer.toString(csr.getPosition()); for (int i=0; i < columncount;i++) { logstr = logstr + "\n\tFor Column " + csr.getColumnName(i); switch (csr.getType(i)) { case Cursor.FIELD_TYPE_NULL: logstr = logstr + " Type is NULL"; break; case Cursor.FIELD_TYPE_FLOAT: logstr = logstr + "Type is FLOAT"; break; case Cursor.FIELD_TYPE_INTEGER: logstr = logstr + " Type is INTEGER"; break; case Cursor.FIELD_TYPE_STRING: logstr = logstr + " Type is STRING"; break; case Cursor.FIELD_TYPE_BLOB: logstr = logstr + " Type is BLOB"; break; } String strval_log = " value as String is "; String lngval_log = " value as long is "; String dblval_log = " value as double is "; String blbval_log = ""; try { strval_log = strval_log + csr.getString(i); lngval_log = lngval_log + csr.getLong(i); dblval_log = dblval_log + csr.getDouble(i); } catch (Exception e) { strval_log = strval_log + unobtainable; lngval_log = lngval_log + unobtainable; dblval_log = dblval_log + unobtainable; try { blbval_log = " value as blob is " + getBytedata(csr.getBlob(i),24); } catch (Exception e2) { e2.printStackTrace(); } } logstr = logstr + strval_log + lngval_log + dblval_log + blbval_log; } Log.d(CSU_TAG,logstr); } csr.moveToPosition(csrpos); // restore cursor position <<< added 20171016 } /** * Return a hex string of the given byte array * @param bytes The byte array to be converted to a hexadecimal string * @param limit the maximum number of bytes; * note returned string will be up to twice as long * @return The byte array represented as a hexadecimal string */ private static String getBytedata(byte[] bytes, int limit) { if (bytes.length < limit) { return convertBytesToHex(bytes); } else { byte[] subset = new byte[limit]; System.arraycopy(bytes,0,subset,0,limit); return convertBytesToHex(subset); } } // HEX characters as a char array for use by convertBytesToHex private final static char[] hexarray = "0123456789ABCDEF".toCharArray(); /** * Return a hexadecimal string representation of the passed byte array * @param bytes The byte array to be represented. * @return The string representing the byte array as hexadecimal */ private static String convertBytesToHex(byte[] bytes) { char[] hexstr = new char[bytes.length * 2]; for (int i=0; i < bytes.length; i++) { int h = bytes[i] & 0xFF; hexstr[i * 2] = hexarray[h >>> 4]; hexstr[i * 2 + 1] = hexarray[h & 0xF]; } return new String(hexstr); } } 

Para usar el código simplemente cree la clase CommonSQLiteUtilities y copie el código anterior.

Adición 1 – logDatabaseInfo

Esto escribirá cierta información sobre la base de datos en el registro, por ejemplo:

 10-12 06:32:12.426 20575-20575/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.sqlitedbexamples/databases/eventsDB 10-12 06:32:12.426 20575-20575/? D/SQLITE_CSU: Database Version = 1 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT) 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table Name = events Created Using = CREATE TABLE events(_id INTEGER PRIMARY KEY, starts INTEGER DEFAULT (strftime('%s','now')),starts_timestamp INTEGER DEFAULT 0,ends INTEGER DEFAULT 0,ends_timestamp INTEGER DEFAULT 0,description TEXT, counter INTEGER DEFAULT 0,bytedata BLOB DEFAULT x'00000000') 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = _id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = starts ColumnType = INTEGER Default Value = strftime('%s','now') PRIMARY KEY SEQUENCE = 0 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = starts_timestamp ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = ends ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = ends_timestamp ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = description ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = counter ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0 10-12 06:32:12.427 20575-20575/? D/SQLITE_CSU: Table = events ColumnName = bytedata ColumnType = BLOB Default Value = x'00000000' PRIMARY KEY SEQUENCE = 0 

Lo anterior es para el nombre de la base de datos eventsDB , la versión es 1 , hay 2 tablas android_metadata (requerido por Apps y generado automáticamente) y eventos (en realidad, existen 3 tablas como sqlite_master y SQLite lo requiere).

El SQL utilizado para crear la tabla también se incluye para cada una de las tablas enumeradas.

Para cada tabla, las columnas se enumeran mostrando el nombre de la columna, su tipo , su valor predeterminado y su secuencia dentro del índice principal (0 = no en el índice principal).

Para incluir logDatabaseInfo, reemplace la clase CommonSQLiteUtilities (o create new) con lo siguiente (tenga en cuenta que esto incluye todas las otras utilidades según la respuesta original): –

 public class CommonSQLiteUtilities { public static final boolean ERROR_CHECKING_ON = true; public static final boolean ERROR_CHECKING_OFF = false; // SQLite MASTER TABLE definitions static final String SQLITE_MASTER = "sqlite_master"; static final String SM_TABLE_TYPE_COLUMN = "type"; static final String SM_NAME_COLUMN = "name"; static final String SM_TABLENAME_COLUMN = "tbl_name"; static final String SM_ROOTPAGE_COLUMN = "rootpage"; static final String SM_SQL_COLUMN = "sql"; static final String SM_TYPE_TABLE = "table"; static final String SM_TYPE_INDEX = "index"; static final String PRAGMA_STATEMENT = "PRAGMA "; static final String PRAGMA_DATABASELIST = "database_list"; static final String PRAGMA_USERVERSION = "user_version"; static final String PRAGMA_ENCODING = "encoding"; static final String PRAGMA_FOREIGNKEYLIST = "foreign_key_list"; static final String PRAGMA_INDEXINFO = "index_info"; static final String PRAGMA_INDEXLIST = "index_list"; static final String PRAGMA_TABLEINFO = "table_info"; static final String PRAGMA_DBLIST_SEQ_COL = "seq"; static final String PRAGMA_DBLIST_NAME_COL = "name"; static final String PRAGMA_DBLIST_FILE_COL = "file"; static final String PRAGMA_TABLEINFO_CID_COL = "cid"; static final String PRAGMA_TABLEINFO_NAME_COl = "name"; static final String PRAGMA_TABLEINFO_TYPE_COL = "type"; static final String PRAGMA_TABLEINFO_NOTNULL_COL = "notnull"; static final String PRAGMA_TABLEINFO_DEFAULTVALUE_COL = "dflt_value"; static final String PRAGMA_TABLEINFO_PRIMARYKEY_COL = "pk"; static final String CSU_TAG = "SQLITE_CSU"; private CommonSQLiteUtilities() {} /** * Write Database information to the log; * Information wrttien is: * the database path, (will/should show connected databases) * the version number (note! user version ie version coded in DBHelper), * the tables in the database (includes android_metadata but not sqlite_master), * the columns of the tables * @param db The SQLite database to be interrogated */ public static void logDatabaseInfo(SQLiteDatabase db) { // Issue PRAGMA database_list commnand Cursor dblcsr = db.rawQuery(PRAGMA_STATEMENT + PRAGMA_DATABASELIST,null); // Write databases to the log while (dblcsr.moveToNext()) { Log.d(CSU_TAG,"DatabaseList Row " + Integer.toString(dblcsr.getPosition() + 1) + " Name=" + dblcsr.getString(dblcsr.getColumnIndex(PRAGMA_DBLIST_NAME_COL)) + " File=" + dblcsr.getString(dblcsr.getColumnIndex(PRAGMA_DBLIST_FILE_COL)) ); } dblcsr.close(); // Issue PRAGMA user_version to get the version and write to the log //Note! to set user_version use execSQL not rawQuery Cursor uvcsr = db.rawQuery(PRAGMA_STATEMENT + PRAGMA_USERVERSION,null); while (uvcsr.moveToNext()) { Log.d(CSU_TAG,"Database Version = " + Integer.toString(uvcsr.getInt(uvcsr.getColumnIndex(PRAGMA_USERVERSION)))); } uvcsr.close(); // Select all table entry rows from sqlite_master Cursor tlcsr = db.rawQuery("SELECT * FROM " + SQLITE_MASTER + " WHERE " + SM_TABLE_TYPE_COLUMN + "='" + SM_TYPE_TABLE + "'" ,null); // For each table write table information to the log // (inner loop gets column info per table) while (tlcsr.moveToNext()) { String current_table = tlcsr.getString(tlcsr.getColumnIndex(SM_TABLENAME_COLUMN)); Log.d(CSU_TAG, "Table Name = " + current_table + " Created Using = " + tlcsr.getString(tlcsr.getColumnIndex(SM_SQL_COLUMN)), null ); // Issue PRAGMA tabel_info for the current table Cursor ticsr = db.rawQuery(PRAGMA_STATEMENT + PRAGMA_TABLEINFO + "(" + current_table + ")", null ); // Write column info (see headings below) to the log while (ticsr.moveToNext()) { Log.d(CSU_TAG,"Table = " + current_table + " ColumnName = " + ticsr.getString(ticsr.getColumnIndex(PRAGMA_TABLEINFO_NAME_COl)) + " ColumnType = " + ticsr.getString(ticsr.getColumnIndex(PRAGMA_TABLEINFO_TYPE_COL)) + " Default Value = " + ticsr.getString(ticsr.getColumnIndex(PRAGMA_TABLEINFO_DEFAULTVALUE_COL)) + " PRIMARY KEY SEQUENCE = " + Integer.toString( ticsr.getInt(ticsr.getColumnIndex(PRAGMA_TABLEINFO_PRIMARYKEY_COL)) ) ); } ticsr.close(); } tlcsr.close(); } /** * Generic get all rows from an SQlite table, * allowing the existence of the table to be checked and also * allowing the ROWID to be added AS a supplied string * * @param db The SQLiteDatabase * @param tablename The name of the table from which the * returned cursor will be created from; * Note! * @param use_error_checking Whether ot not to try to detect errors * currently just table doesn't exist, * true to turn on, false to turn off * ERROR_CHECKING_ON = true * ERROR_CHECKING_OFF = false * @param forceRowidAs If length of string passed is 1 or greater * then a column, as an alias of ROWID, will be * added to the cursor * @return the extracted cursor, or in the case of the * underlying table not existing an empty cursor * with no columns */ public static Cursor getAllRowsFromTable(SQLiteDatabase db, String tablename, boolean use_error_checking, String forceRowidAs) { String[] columns = null; // Tablename must be at least 1 character in length if (tablename.length() < 1) { Log.d(CSU_TAG,new Object(){}.getClass().getEnclosingMethod().getName() + " is finishing as the provided tablename is less than 1 character in length" ); return new MatrixCursor(new String[]{}); } // If use_error_checking is true then check that the table exists // in the sqlite_master table if (use_error_checking) { Cursor chkcsr = db.query(SQLITE_MASTER,null, SM_TABLE_TYPE_COLUMN + "=? AND " + SM_TABLENAME_COLUMN + "=?", new String[]{SM_TYPE_TABLE,tablename}, null,null,null ); // Ooops table is not in the Database so return an empty // column-less cursor if (chkcsr.getCount() < 1) { Log.d(CSU_TAG,"Table " + tablename + " was not located in the SQLite Database Master Table." ); // return empty cursor with no columns return new MatrixCursor(new String[]{}); } chkcsr.close(); } // If forcing an alias of ROWID then user ROWID AS ???, * if(forceRowidAs != null && forceRowidAs.length() > 0) { columns = new String[]{"rowid AS " +forceRowidAs,"*"}; } // Finally return the Cursor but trap any exceptions try { return db.query(tablename, columns, null, null, null, null, null); } catch (Exception e) { Log.d(CSU_TAG,"Exception encountered but trapped when querying table " + tablename + " Message was: \n" + e.getMessage()); Log.d(CSU_TAG,"Stacktrace was:"); e.printStackTrace(); return new MatrixCursor(new String[]{}); } } /** * Create and return a Cursor devoid of any rows and columns * Not used, prehaps of very little use. * @param db The Sqlite database in which the cursor is to be created * @return The empty Cursor */ private static Cursor getEmptyColumnLessCursor(SQLiteDatabase db) { return new MatrixCursor(new String[]{}); } /** * Write column names in the passed Cursor to the log * @param csr The Cursor to be inspected. */ public static void logCursorColumns(Cursor csr) { Log.d(CSU_TAG, new Object(){}.getClass().getEnclosingMethod().getName() + " invoked. Cursor has the following " + Integer.toString(csr.getColumnCount())+ " columns."); int position = 0; for (String column: csr.getColumnNames()) { position++; Log.d(CSU_TAG,"Column Name " + Integer.toString(position) + " is " + column ); } } /** * Write the contents of the Cursor to the log * @param csr The Cursor that is to be displayed in the log */ public static void logCursorData(Cursor csr) { int columncount = csr.getColumnCount(); int rowcount = csr.getCount(); int csrpos = csr.getPosition(); //<<< added 20171016 Log.d(CSU_TAG, new Object(){}.getClass().getEnclosingMethod().getName() + " Cursor has " + Integer.toString(rowcount) + " rows with " + Integer.toString(columncount) + " columns." ); csr.moveToPosition(-1); //Ensure that all rows are retrieved <<< added 20171016 while (csr.moveToNext()) { String unobtainable = "unobtainable!"; String logstr = "Information for row " + Integer.toString(csr.getPosition() + 1) + " offset=" + Integer.toString(csr.getPosition()); for (int i=0; i < columncount;i++) { logstr = logstr + "\n\tFor Column " + csr.getColumnName(i); switch (csr.getType(i)) { case Cursor.FIELD_TYPE_NULL: logstr = logstr + " Type is NULL"; break; case Cursor.FIELD_TYPE_FLOAT: logstr = logstr + "Type is FLOAT"; break; case Cursor.FIELD_TYPE_INTEGER: logstr = logstr + " Type is INTEGER"; break; case Cursor.FIELD_TYPE_STRING: logstr = logstr + " Type is STRING"; break; case Cursor.FIELD_TYPE_BLOB: logstr = logstr + " Type is BLOB"; break; } String strval_log = " value as String is "; String lngval_log = " value as long is "; String dblval_log = " value as double is "; String blbval_log = ""; try { strval_log = strval_log + csr.getString(i); lngval_log = lngval_log + csr.getLong(i); dblval_log = dblval_log + csr.getDouble(i); } catch (Exception e) { strval_log = strval_log + unobtainable; lngval_log = lngval_log + unobtainable; dblval_log = dblval_log + unobtainable; try { blbval_log = " value as blob is " + getBytedata(csr.getBlob(i),24); } catch (Exception e2) { e2.printStackTrace(); } } logstr = logstr + strval_log + lngval_log + dblval_log + blbval_log; } Log.d(CSU_TAG,logstr); } csr.moveToPosition(csrpos); // restore cursor position <<< added 20171016 } /** * Return a hex string of the given byte array * @param bytes The byte array to be converted to a hexadecimal string * @param limit the maximum number of bytes; * note returned string will be up to twice as long * @return The byte array represented as a hexadecimal string */ private static String getBytedata(byte[] bytes, int limit) { if (bytes.length < limit) { return convertBytesToHex(bytes); } else { byte[] subset = new byte[limit]; System.arraycopy(bytes,0,subset,0,limit); return convertBytesToHex(subset); } } // HEX characters as a char array for use by convertBytesToHex private final static char[] hexarray = "0123456789ABCDEF".toCharArray(); /** * Return a hexadecimal string representation of the passed byte array * @param bytes The byte array to be represented. * @return The string representing the byte array as hexadecimal */ private static String convertBytesToHex(byte[] bytes) { char[] hexstr = new char[bytes.length * 2]; for (int i=0; i < bytes.length; i++) { int h = bytes[i] & 0xFF; hexstr[i * 2] = hexarray[h >>> 4]; hexstr[i * 2 + 1] = hexarray[h & 0xF]; } return new String(hexstr); } }