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

android-sqlite

Question

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

Por ejemplo:-

• no se encuentran tablas y columnas 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 luego solo cuando se intenta utilizar uno de los SQLiteDatabase getReadableDatabase o getWriteableDatabse , si se cambia la estructura / esquema de la base de datos, entonces hay 3 formas simples de forzar a onCreate a ejecutar, a) Borrar los datos de la aplicación, b) desinstalar la aplicación ob) si el método onUpgrade invoca el método onCreate (después de onCreate las tablas) y luego aumentar el Número de versión de la base de datos).
• Las inserciones / actualizaciones no funcionan pero no fallan.

Para el novato en SQLite también puede ser irritante / desalentador no poder ver fácilmente lo que contiene la base de datos.

Entonces, ¿hay alguna utilidad común que pueda ayudar?

¡Nota! Esto pretende ser una pregunta para compartir conocimientos al estilo de preguntas y respuestas .

Answer 1

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 (requeridas por aplicaciones y generadas automáticamente) y eventos (en realidad 3 tablas como sqlite_master también existe y es requerido por SQLite).

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 primario (0 = no en el índice primario).

Para incluir logDatabaseInfo, reemplace CommonSQLiteUtilities (o cree una nueva clase) con lo siguiente (tenga en cuenta que esto incluye todas las demás 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 i.e. 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);
}
}

Answer 2

Las siguientes son algunas utilidades comunes que el principiante puede 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 en el registro.
• logCursorData para escribir los datos del cursor y las columnas en el registro.

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

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

• getEmptyColumnLessCursor ( 100 puntos por 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 crear un cursor para ser examinado posteriormente 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 para obtener los datos.
• El tercer parámetro, si es verdadero, verificará 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 del ROWID (destinado a ayudar cuando no se ha proporcionado ningún alias para ROWID y, por lo tanto, ROWID no es 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 i.e. 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 dicho Cursor puede no tener filas ni columnas (por ejemplo, en el caso de que la tabla no estuviera 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, especificar IS como el cuarto parámetro (tratar de usar ROWID AS IS , que a 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.<init>(SQLiteProgram.java:58)
10-09 18:57:52.592 3835-3835/? W/System.err: at android.database.sqlite.SQLiteQuery.<init>(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 ni columnas.

logCursorColumns

Está destinado a usarse 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 aparte de que proporciona columnas de logCursorColumns de información más extensas y que también proporciona información sobre 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.