android - sqlite 使用表中的额外字段升级数据库并保留旧数据

Question

我有一个使用 sqlite 数据库的应用程序。数据库将交易数据存储在交易表中。我想在 Transactions 表中添加一个额外的字段，同时保留以前数据库中的旧数据。

我已经覆盖了 SqliteOpenHelper 类中的 onUpgrade 方法。我尝试将交易表的名称更改为 OldTrasnsactions，然后重新创建交易表并从 OldTransactions 复制数据。

Android 在编译 Alter 语句时说 Transactions 表不存在。我在执行 onUpgrade 时正确地增加了版本号。

有人知道我做错了什么吗？

下面是报错和SqliteOpenHelper类。

注意 Transactions 中额外的新字段是 C_TRANSACTIONS_DRIVER

.

    10-28 14:33:12.550: E/AndroidRuntime(3171): FATAL EXCEPTION: IntentService[SendOutstandingTransactions]
    10-28 14:33:12.550: E/AndroidRuntime(3171): android.database.sqlite.SQLiteException: no such table: TABLETRANSACTIONS (code 1): , while compiling: ALTER TABLE TABLETRANSACTIONS RENAME TO OldTABLETRANSACTIONS
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:909)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:520)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1719)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1650)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at com.carefreegroup.rr3.LoginValidate$DBHelper.onUpgrade(LoginValidate.java:643)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at com.carefreegroup.rr3.LoginValidate.queryAllFromCarer(LoginValidate.java:275)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at com.carefreegroup.rr3.SendOutstandingTransactions.onHandleIntent(SendOutstandingTransactions.java:63)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:65)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.os.Handler.dispatchMessage(Handler.java:99)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.os.Looper.loop(Looper.java:158)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.os.HandlerThread.run(HandlerThread.java:60)
    10-28 14:33:12.560: E/EmbeddedLogger(508): App crashed! Process: com.carefreegroup.rr3
    10-28 14:33:12.565: E/EmbeddedLogger(508): App crashed! Package: com.carefreegroup.rr3 v13 (2.0.5e)
    10-28 14:33:12.570: E/EmbeddedLogger(508): Application Label: CareFree



private class DBHelper extends SQLiteOpenHelper {

        // database name and version number
        public static final String DB_NAME = "carefreemobiledb.db";
        public static final int DB_VERSION = 51;

        // table names
        public static final String TABLETRANSACTIONS = "transactions";



        public DBHelper() {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sqlToCreateTransactionsTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s INT, %s TEXT, %s TEXT )",
                            TABLETRANSACTIONS, C_ID, C_TYPE, C_COMPANY_ID,
                            C_PERSON_ID, C_NAME, C_TAG_ID, C_STATUS,
                            C_TAG_SCAN_TIME, C_TAG_SENTSERVER_TIME,
                            C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE);

            db.execSQL(sqlToCreateTransactionsTable);
            Log.e(TAG, "oncreate " + sqlToCreateTransactionsTable);



        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

//          db.execSQL("drop table if exists " + TABLETRANSACTIONS);
//          db.execSQL("drop table if exists " + TABLECARER);
//          db.execSQL("drop table if exists " + TABLETRANSACTIONSMAP);
//          db.execSQL("drop table if exists " + TABLEPHONE);
//          db.execSQL("drop table if exists " + TABLECOMPANYID);
//          db.execSQL("drop table if exists " + TABLEBACKGROUNDSERVICES);
//          db.execSQL("drop table if exists " + TABLEMESSAGE);
//          db.execSQL("drop table if exists " + TABLEDUPLICATETRANSACTIONS);
//          db.execSQL("drop table if exists " + TABLECACHEDROTA);
//          Log.e(TAG, "onUpgrade dropped all tables apart from message and transactions");

            db.execSQL("ALTER TABLE TABLETRANSACTIONS RENAME TO OldTABLETRANSACTIONS");

            db.execSQL("CREATE TABLE TABLETRANSACTIONS(C_ID INTEGER PRIMARY KEY, C_TYPE TEXT," +
                    " C_COMPANY_ID TEXT,C_PERSON_ID TEXT, C_NAME TEXT, C_TAG_ID TEXT, C_STATUS TEXT,C_TAG_SCAN_TIME TEXT, " +
                    "C_TAG_SENTSERVER_TIME INT,C_TRANSACTIONS_LATITUDE TEXT, C_TRANSACTIONS_LONGITUDE TEXT, C_TRANSACTIONS_DRIVER TEXT)");

            db.execSQL("INSERT INTO TABLETRANSACTIONS (C_ID, C_TYPE, C_COMPANY_ID, C_PERSON_ID,  C_NAME,  C_TAG_ID, C_STATUS, C_TAG_SCAN_TIME, " +
                "C_TAG_SENTSERVER_TIME,  C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE, C_TRANSACTIONS_DRIVER ) SELECT C_ID, C_TYPE, C_COMPANY_ID, " +
                "C_PERSON_ID,  C_NAME,  C_TAG_ID, C_STATUS, C_TAG_SCAN_TIME, C_TAG_SENTSERVER_TIME,  C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE FROM OldTABLETRANSACTIONS");

            db.execSQL("DROP TABLE OldTABLETRANSACTIONS");


            Log.e(TAG, " SQLiteOpenHelper onUpgrade: finished copying old data to new transaction table");

            Toast.makeText(nfcscannerapplication, "SQLiteOpenHelper onUpgrade: finished copying old data to new transaction table", Toast.LENGTH_LONG);
            //this.onCreate(db);

        }

    }

Answer 1

您的交易表的名称似乎是“交易”，但您正在寻找“TABLETRANSACTIONS”，可能是因为在 Java 语句和 SQL 之间进行转换。 C_ID 等其他常量也是如此。

要么更改所有 SQL 语句以引用事务，要么使用如下常量值:

db.execSQL("ALTER TABLE "+TABLETRANSACTIONS+" RENAME TO Old"+TABLETRANSACTIONS);