Android数据库陌生列表列 [英] Android database strangeness listing columns

问题描述

在读取Android SQLite数据库中的列的两种方法之间，我得到的结果不一致。

I am getting inconsistent results between two methods of reading the columns in an Android SQLite database.

首先，这是公认的数据库升级例程的一部分在这里回答：将SQLite数据库从一个版本升级到另一个版本？

First, this is part of a database upgrade routine as per the accepted answer here: Upgrade SQLite database from one version to another?

该技术涉及使用临时名称将当前表移开，使用新模式创建新表，然后将相关数据从旧表复制到新表中在删除旧的临时表之前。

The technique involves moving the current table away with a temporary name, creating a new table with the new schema, and then copying relevant data from the old table into the new one before deleting the old temporary table.

我遇到的特殊问题是从架构中删除列时。因此，表的旧版本中存在特定的列，而新版本中不存在。

The particular problem I have is when I remove a column from the schema. So, a particular column exists in the old version of the table, but not the new one.

该答案建议使用类似这样的方法来列出表中的列：

That answer suggests using a method like this to list the columns in the table:

/**
 * Returns a list of the table's column names.
 */
private List<String> getColumns(SQLiteDatabase db, final String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 1", null);
        if (c != null) {
            ar = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
        }
    } finally {
        if (c != null)
            c.close();
    }
    return ar;
}

在旧桌子上工作正常，然后我暂时将其移开命名并替换它。稍后当我在新创建的空表上再次运行相同的查询时，它仍然列出旧表架构，其中列名不再存在。看起来好像在为该查询重用过时的缓存结果。

That works fine on the old table, before I move it away with a temporary name and replace it. When I run the same query again later, on the newly-created empty table, it still lists the old table schema with the name of the column which no longer exists. It looks as if it's reusing stale cached results for that query.

如果我以其他方式读取列，而改用这种方式，则它将按预期返回新的列列表：

If I read the columns a different way, using this instead, then it returns the new column list as expected:

private void listColumns(SQLiteDatabase db, final String tableName) {

    final String query = "PRAGMA table_info(" + tableName + ");";
    Cursor c = db.rawQuery(query, null);
    while (c.moveToNext()) {
        Log.v("MyApp", "Column: " + c.getString(1));
    }
    c.close();
}

完整的顺序是：

final String tempTableName = "temp_" + tableName;

table.addToDb(db); // ensure it exists to start with

// get column names of existing table
final List<String> columns = getColumns(db, tableName);

// backup table
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tempTableName);

// create new table
table.addToDb(db);

// delete old columns which aren't in the new schema
columns.retainAll(getColumns(db, tableName));

// restore data from old into new table
String columnList = TextUtils.join(",", columns);
db.execSQL(String.format("INSERT INTO %s (%s) SELECT %s from %s", tableName, columnList, columnList,
                 tempTableName));

// remove backup
db.execSQL(DROP_TABLE + tempTableName);

产生不同结果的原因是什么？

What's the reason for the different results?

推荐答案

我从没有深入了解。我刚刚使用了我提到的第二种方法，但没有出现问题。

I never got to the bottom of this. I just ended up using the second method I mentioned, which doesn't exhibit the problem.

这篇关于Android数据库陌生列表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！