Checking if a column exists in an application database in Android

Question

Is there a nice way in Android to see if a column exists in a table in the application database? (I know there are questions similar to this one already, but there don't seem to be any that are Android specific.)

Answer 1

cursor.getColumnIndex(String columnName) returns -1 if, the column doesn't exist. So I would basically perform a simple query like "SELECT * FROM xxx LIMIT 0,1" and use the cursor to determine if the column, you are looking for, exists

OR

you can try to query the column "SELECT theCol FROM xxx" and catch an exception

Answer 2

My function based on @martinpelants answer:

private boolean existsColumnInTable(SQLiteDatabase inDatabase, String inTable, String columnToCheck) {
    Cursor mCursor = null;
    try {
        // Query 1 row 
        mCursor = inDatabase.rawQuery("SELECT * FROM " + inTable + " LIMIT 0", null);

        // getColumnIndex() gives us the index (0 to ...) of the column - otherwise we get a -1
        if (mCursor.getColumnIndex(columnToCheck) != -1)
            return true;
        else
            return false;

    } catch (Exception Exp) {
        // Something went wrong. Missing the database? The table?
        Log.d("... - existsColumnInTable", "When checking whether a column exists in the table, an error occurred: " + Exp.getMessage());
        return false;
    } finally {
        if (mCursor != null) mCursor.close();
    }
}

Simply call:

boolean bla = existsColumnInTable(myDB,"MyTable","myColumn2check");

Answer 3

I actually wrote this function that seems pretty clean:

private boolean field_exists( String p_query )
{
    Cursor mCursor  = mDb.rawQuery( p_query, null );

    if  (  ( mCursor != null ) && ( mCursor.moveToFirst()) )
    {
        mCursor.close();
        return true ;
    }

    mCursor.close();
    return false ;
}

I call it like this:

if  ( field_exists( "select * from sqlite_master "              
    + "where name = 'mytable' and sql like '%myfield%' " ))
{
    do_something ;
}

Answer 4

Here is my solution to the issue which adds to flexo's solution a little.

You can put this method in any class, perhaps your SQLiteOpenHelper extending class.

public static boolean columnExistsInTable(SQLiteDatabase db, String table, String columnToCheck) {
    Cursor cursor = null;
    try {
        //query a row. don't acquire db lock
        cursor = db.rawQuery("SELECT * FROM " + table + " LIMIT 0", null);

        // getColumnIndex()  will return the index of the column 
        //in the table if it exists, otherwise it will return -1
        if (cursor.getColumnIndex(columnToCheck) != -1) {
            //great, the column exists
            return true;
        }else {
            //sorry, the column does not exist
            return false;
        }

    } catch (SQLiteException Exp) {
        //Something went wrong with SQLite. 
        //If the table exists and your query was good,
        //the problem is likely that the column doesn't exist in the table.
        return false;
    } finally {
        //close the db  if you no longer need it
        if (db != null) db.close();
        //close the cursor 
        if (cursor != null) cursor.close();
    }
}

Answer 5

If you use ActiveAndroid

public static boolean createIfNeedColumn(Class<? extends Model> type, String column) {
        boolean isFound = false;
        TableInfo tableInfo = new TableInfo(type);

        Collection<Field> columns = tableInfo.getFields();
        for (Field f : columns) {
            if (column.equals(f.getName())) {
                isFound = true;
                break;
            }
        }
        if (!isFound) {
            ActiveAndroid.execSQL("ALTER TABLE " + tableInfo.getTableName() + " ADD COLUMN " + column + " TEXT;");
        }
        return isFound;
    }

Answer 6

At the risk of just posting the same solution but shorter. Here's a cut down version based on @flexo's

  private boolean doesColumnExistInTable(SupportSQLiteDatabase db, String tableName, String columnToCheck) {
        try (Cursor cursor = db.query("SELECT * FROM " + tableName + " LIMIT 0", null)) {
            return cursor.getColumnIndex(columnToCheck) != -1;
        } catch (Exception Exp) {
            // Something went wrong. we'll assume false it doesn't exist
            return false;
        }
    }

And in Kotlin

  private fun doesColumnExistInTable(db: SupportSQLiteDatabase, tableName: String, columnToCheck: String): Boolean {
        try {
            db.query("SELECT * FROM $tableName LIMIT 0", null).use { cursor -> return cursor.getColumnIndex(columnToCheck) != -1 }
        } catch (e: Exception) {
            // Something went wrong. we'll assume false it doesn't exist
            return false
        }
    }

Answer 7

this is my testing code:

String neadle = "id"; //searched field name
String tableName = "TableName";
boolean found = false;

SQLiteDatabase mDb = ActiveAndroid.getDatabase();
Cursor mCursor  = mDb.rawQuery( "SELECT * FROM sqlite_master WHERE name = '"+tableName+"' and sql like '%"+neadle+"%'" , null);
mCursor.moveToFirst();
String fie = ",";

if (mCursor.getCount() > 0) {
    String[] fields = mCursor.getString(mCursor.getColumnIndex("sql")).split(",");
    for (String field: fields) {
        String[] fieldNameType = field.trim().split(" ");
        if (fieldNameType.length > 0){
            fie += fieldNameType[0]+",";
        }
    }
}else {
    //table not exist!
}
if (mCursor != null) mCursor.close();
// return result: 
found = fie.contains(","+neadle+",");