代码之家 › 专栏 › 技术社区 › MikeT

Android-SQLite——可以使用什么方法来管理现有数据库的不同版本?

android-assets android-sqlite

MikeT · 技术社区 · 7 年前

笔记这是一个很好的例子 问和回答你自己的问题 根据 share your knowledge, Q&A-style

通常,为使用SQLite的Android应用程序提供预先存在的数据库。数据库通常被放在assets文件夹中,如果数据库不存在,应用程序将复制数据库。

对于较新版本的应用程序,可以更改资产文件夹中的数据库,因为更改后的数据库将被复制。但是,从以前的版本升级应用程序时,数据库将存在,因此不会复制,也无法轻松地将其无缝删除,以便从资产文件夹中进行复制。

如果需要保留用户数据,则可能会产生进一步的复杂性。

例如,我最初部署一个数据库,其中有一个表,表中有3列,创建时使用:-

CREATE TABLE "user" (
  "_id" INTEGER NOT NULL,
  "user" TEXT,
  "password" TEXT,
  PRIMARY KEY ("_id")
)

包含以下数据:-

该应用的下一个化身希望利用一个已更改的模式,在该模式上有一个额外的列和一个独特的约束 使用者 列,例如:-

CREATE TABLE "user" (
  "_id" INTEGER NOT NULL,
  "user" TEXT,
  "password" TEXT,
  "email" TEXT,
  PRIMARY KEY ("_id"),
  CONSTRAINT "user" UNIQUE ("user")
);

此外,还添加了其他数据,例如:-

用户可以添加自己的数据,这些数据需要保留。因此,即使可能,删除数据库文件也会删除用户的数据。

可以使用哪些方法来管理现有数据库的不同版本?

1 回复 | 直到 5 年前

MikeT 7 年前

简而言之,一种方法是将新资产文件复制到适当的位置,同时保留原始数据库文件的副本,然后可以应用更新,以便有效地将用户的数据保存到新复制的数据库中。但是仅当应用程序正在升级以进行特定更改,并且数据库存在时。

如果数据库不存在,则应执行资产文件的标准副本。

下面是一个如何做到这一点的例子。

这个示例非常依赖于一个例程,该例程允许管理和查询资产文件夹中的文件和/或数据库文件本身。

一个班级 德巴塞坦德勒。JAVA 满足上述要求(以及在使用SQLiteOpenHelper时提取用户版本(又称数据库版本)的能力)。

注意,该类也已经在Android Pie上进行了测试,因此适合Android Pie,并因此提前写日志(Pie中的默认值)以及日志模式(之前的默认值)。
还要注意的是,如果使用WAL,那么应该确保数据库中的数据被完全检查 see - Write-Ahead Logging

它是:-

public class DBAssetHandler {

    static final String[] tempfiles = new String[]{"-journal","-wal","-shm"}; // temporary files to rename
    public static final String backup = "-backup"; //value to be appended to file name when renaming (psuedo delete)
    public static final  int OUCH = -666666666;

    /**
     * Check if the database already exists. NOTE will create the databases folder is it doesn't exist
     * @return true if it exists, false if it doesn't
     */
    public static boolean checkDataBase(Context context, String dbname) {

        File db = new File(context.getDatabasePath(dbname).getPath()); //Get the file name of the database
        Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove if publish App
        if (db.exists()) return true; // If it exists then return doing nothing

        // Get the parent (directory in which the database file would be)
        File dbdir = db.getParentFile();
        // If the directory does not exits then make the directory (and higher level directories)
        if (!dbdir.exists()) {
            db.getParentFile().mkdirs();
            dbdir.mkdirs();
        }
        return false;
    }


    /**
     * Copy database file from the assets folder
     * (long version caters for asset file name being different to the database name)
     * @param context           Context is needed to get the applicable package
     * @param dbname            name of the database file
     * @param assetfilename     name of the asset file
     * @param deleteExistingDB  true if an existing database file should be deleted
     *                              note will delete journal and wal files
     *                              note doen't actually delete the files rater it renames
     *                              the files by appended -backup to the file name
     *                              SEE/USE clearForceBackups below to delete the renamed files
     */
    public static void copyDataBase(Context context, String dbname, String assetfilename, boolean deleteExistingDB) {

        final String TAG = "COPYDATABASE";
        int stage = 0, buffer_size = 4096, blocks_copied = 0, bytes_copied = 0;
        File f = new File(context.getDatabasePath(dbname).toString());
        InputStream is;
        OutputStream os;

        /**
         * If forcing then effectively delete (rename) current database files
         */
        if (deleteExistingDB) {
            //String[] tempfiles = new String[]{"-journal","-wal","-shm"};
            //String backup = "-backup";
            f.renameTo(context.getDatabasePath(dbname + "-backup"));
            for (String s: tempfiles) {
                File tmpf = new File(context.getDatabasePath(dbname + s).toString());
                if (tmpf.exists()) {
                    tmpf.renameTo(context.getDatabasePath(dbname + s + backup));
                }
            }
        }


        //Open your local db as the input stream
        Log.d(TAG,"Initiated Copy of the database file " + assetfilename + " from the assets folder."); //TODO remove if publishing
        try {
            is = context.getAssets().open(assetfilename); // Open the Asset file
            stage++;
            Log.d(TAG, "Asset file " + assetfilename + " found so attmepting to copy to " + f.getPath()); //TODO remove if publishing

            os = new FileOutputStream(f);
            stage++;
            //transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[buffer_size];
            int length;
            while ((length = is.read(buffer)) > 0) {
                blocks_copied++;
                Log.d(TAG, "Attempting copy of block " + String.valueOf(blocks_copied) + " which has " + String.valueOf(length) + " bytes."); //TODO remove if publishing
                os.write(buffer, 0, length);
                bytes_copied += length;
            }
            stage++;
            Log.d(TAG,
                    "Finished copying Database " + dbname +
                            " from the assets folder, to  " + f.getPath() +
                            String.valueOf(bytes_copied) + "were copied, in " +
                            String.valueOf(blocks_copied) + " blocks of size " +
                            String.valueOf(buffer_size) + "."
            ); //TODO remove if publishing
            //Close the streams
            os.flush();
            stage++;
            os.close();
            stage++;
            is.close();
            Log.d(TAG, "All Streams have been flushed and closed.");
        } catch (IOException e) {
            String exception_message = "";
            e.printStackTrace();
            switch (stage) {
                case 0:
                    exception_message = "Error trying to open the asset " + dbname;
                    break;
                case 1:
                    exception_message = "Error opening Database file for output, path is " + f.getPath();
                    break;
                case 2:
                    exception_message = "Error flushing written database file " + f.getPath();
                    break;
                case 3:
                    exception_message = "Error closing written database file " + f.getPath();
                    break;
                case 4:
                    exception_message = "Error closing asset file " + f.getPath();

            }
            throw new RuntimeException("Unable to copy the database from the asset folder." + exception_message + " see starck-trace above.");
        }
    }

    /**
     * Copy the databsse from the assets folder where asset name and dbname are the same
     * @param context
     * @param dbname
     * @param deleteExistingDB
     */
    public static void copyDataBase(Context context, String dbname, boolean deleteExistingDB) {
        copyDataBase(context, dbname,dbname,deleteExistingDB);
    }

    /**
     * Get the SQLite_user_vesrion from the DB in the asset folder
     *
     * @param context           needed to get the appropriate package assets
     * @param assetfilename     the name of the asset file (assumes/requires name matches database)
     * @return                  the version number as stored in the asset DB
     */
    public static int getVersionFromDBInAssetFolder(Context context, String assetfilename) {
        InputStream is;
        try {
            is = context.getAssets().open(assetfilename);
        } catch (IOException e) {
            return OUCH;
        }
        return getDBVersionFromInputStream(is);
    }

    /**
     * Get the version from the database itself without opening the database as an SQliteDatabase
     * @param context   Needed to ascertain package
     * @param dbname    the name of the dataabase
     * @return          the version number extracted
     */
    public static int getVersionFromDBFile(Context context, String dbname) {
        InputStream is;
        try {
            is = new FileInputStream(new File(context.getDatabasePath(dbname).toString()));
        } catch (IOException e) {
            return OUCH;
        }
        return getDBVersionFromInputStream(is);
    }

    /**
     * Get the Database Version (user_version) from an inputstream
     *  Note the inputstream is closed
     * @param is    The Inputstream
     * @return      The extracted version number
     */
    private static int getDBVersionFromInputStream(InputStream is) {
        int rv = -1, dbversion_offset = 60, dbversion_length = 4 ;
        byte[] dbfileheader = new byte[64];
        byte[] dbversion = new byte[4];
        try {
            is.read(dbfileheader);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
            return rv;
        }

        for (int i = 0; i < dbversion_length; i++ ) {
            dbversion[i] = dbfileheader[dbversion_offset + i];
        }
        return ByteBuffer.wrap(dbversion).getInt();
    }

    /**
     * Check to see if the asset file exists
     *
     * @param context           needed to get the appropriate package
     * @param assetfilename     the name of the asset file to check
     * @return                  true if the asset file exists, else false
     */
    public static boolean ifAssetFileExists(Context context, String assetfilename) {
        try {
            context.getAssets().open(assetfilename);
        } catch (IOException e) {
            return false;
        }
        return true;
    }


    /**
     * Delete the backup
     * @param context
     * @param dbname
     */
    public static void clearForceBackups(Context context, String dbname) {
        String[] fulllist = new String[tempfiles.length + 1];

        for (int i = 0;i < tempfiles.length; i++) {
            fulllist[i] = tempfiles[i];
        }
        fulllist[tempfiles.length] = ""; // Add "" so database file backup is also deleted
        for (String s: fulllist) {
            File tmpf = new File(context.getDatabasePath(dbname + s + backup).toString());
            if (tmpf.exists()) {
                tmpf.delete();
            }
        }
    }
}

希望方法名称和注释能够解释上述代码。

在资产文件中,有两个:-

pev1。分贝 -如上所述的原始现有数据库
pev1mod。分贝 -已修改(额外列、唯一约束和附加行)。

数据库助手(SQLOpenHelper的子类)即 PEV2DBHelper。JAVA ,需要注意的是,数据库版本( 数据库版本 )用于控制,因此不同于APK版本(可能比DB更频繁地更改)

尝试使用时发现问题 onUpgrade 方法,因此是另一种方法,即获取数据库的 用户版本 从文件中,而不是通过SQLITE数据库。

这是 PEV2DBHelper。JAVA :-

/**
 * MORE COMPLEX EXAMPLE RETAINING USER DATA
 */
public class PEV2DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "pev1.db";
    public static final String ASSETTOCOPY_DBV2 = "pev1mod.db"; //<<<<<<<<<< changed DB
    public static final int DBVERSION = 2; //<<<<<<<<<< increase and db file from assets will copied keeping existing data
    Context mContext;

    public PEV2DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);


        int dbversion = DBAssetHandler.getVersionFromDBFile(context,DBNAME);
        Log.d("DBFILEVERSION","Database File Version = " + String.valueOf(dbversion));
        int af1version = DBAssetHandler.getVersionFromDBInAssetFolder(context,DBNAME);
        Log.d("DBFILEVERSION","Asset Database File Version = " + String.valueOf(af1version));
        int af2version = DBAssetHandler.getVersionFromDBInAssetFolder(context,ASSETTOCOPY_DBV2);
        Log.d("DBFILEVERSION","Asset Database File Version = " + String.valueOf(af2version));

        // cater for different DBVERSIONS (for testing )
        if (!DBAssetHandler.checkDataBase(context,DBNAME)) {

            //If new installation of the APP then copy the appropriate asset file for the DB
            switch (DBVERSION) {
                case 1:
                    DBAssetHandler.copyDataBase(context,DBNAME,DBNAME,false);
                    break;
                case 2:
                    DBAssetHandler.copyDataBase(context,DBNAME,ASSETTOCOPY_DBV2,false);
                    break;
            }
        }

        // If DBVERSION upgraded to 2 with modified DB but wanting to preserve used data
        if (DBAssetHandler.checkDataBase(context,DBNAME) & (DBVERSION > DBAssetHandler.getVersionFromDBFile(context, DBNAME)) & (DBVERSION == 2) ) {

            String[] oldcolumns = new String[]{"user","password"};

            // Copy in the new DB noting that delete option renames old (truue flag important)
            DBAssetHandler.copyDataBase(context,DBNAME,ASSETTOCOPY_DBV2,true);

            //Get the newly copied database
            SQLiteDatabase newdb = SQLiteDatabase.openDatabase(context.getDatabasePath(DBNAME).toString(),null,SQLiteDatabase.OPEN_READWRITE);
            //Get the old database (backup copy)
            SQLiteDatabase olddb =  SQLiteDatabase.openDatabase(context.getDatabasePath(DBNAME + DBAssetHandler.backup).toString(),null,SQLiteDatabase.OPEN_READWRITE);

            //Prepare to insert old rows (note user column is UNIQUE so pretty simple scenario just try inserting all and duplicates will be rejected)
            ContentValues cv = new ContentValues();
            Cursor oldcsr = olddb.query("user",null,null,null,null,null,null);
            newdb.beginTransaction();
            while (oldcsr.moveToNext()) {
                cv.clear();
                for (String columnname: oldcolumns) {
                    cv.put(columnname,oldcsr.getString(oldcsr.getColumnIndex(columnname)));
                }
                newdb.insert("user",null,cv);
            }
            newdb.setTransactionSuccessful();
            newdb.endTransaction();
            newdb.close();
            olddb.close();

            // Finally delete the renamed old database
            DBAssetHandler.clearForceBackups(context,DBNAME);
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }
}

请注意,添加、删除和提取行的方法几乎没有膨胀。然而,它有点过于复杂,因为它处理版本之间的切换以便于演示。

最后是一个示例活动,它调用利用PEV2DBHelper,将模式和表中的行写入日志。

使用的活动是 主要活动。JAVA 而且是:-

public class MainActivity extends AppCompatActivity {

    PEV2DBHelper mDBHlpr2; //DBHelper for example that retains user data

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        doPEV2(); // Test simple more complex scenario
    }


    private void doPEV2() {

        mDBHlpr2 = new PEV2DBHelper(this);
        SQLiteDatabase db = mDBHlpr2.getWritableDatabase();
        Cursor csr = db.query("sqlite_master",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
        csr = db.query("user",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
        if (PEV2DBHelper.DBVERSION == 1) {
            addUserData(db);
            csr = db.query("user",null,null,null,null,null,null);
            DatabaseUtils.dumpCursor(csr);
        }
        csr.close();
        db.close();
    }

    /**
     * Add some user data for testing presevation of that data
     * @param db    the SQLitedatabase
     */
    private void addUserData(SQLiteDatabase db) {
        ContentValues cv = new ContentValues();
        cv.put("user","mr new user");
        cv.put("password","a password");
        db.insert("user",null,cv);
    }
}

后果

1.首次运行时,DBVERSION为1(全新应用程序)

在本例中,资产文件 pev1。分贝 是从资产文件夹复制的。输出为:-

2019-02-22 19:07:54.676 28670-28670/? D/DBFILEVERSION: Database File Version = -666666666
2019-02-22 19:07:54.677 28670-28670/? D/DBFILEVERSION: Asset Database File Version = 0
2019-02-22 19:07:54.677 28670-28670/? D/DBFILEVERSION: Asset Database File Version = 0
2019-02-22 19:07:54.677 28670-28670/? D/DBPATH: DB Path is /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:07:54.677 28670-28670/? D/COPYDATABASE: Initiated Copy of the database file pev1.db from the assets folder.
2019-02-22 19:07:54.677 28670-28670/? D/COPYDATABASE: Asset file pev1.db found so attmepting to copy to /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:07:54.677 28670-28670/? D/COPYDATABASE: Attempting copy of block 1 which has 4096 bytes.
2019-02-22 19:07:54.677 28670-28670/? D/COPYDATABASE: Attempting copy of block 2 which has 4096 bytes.
2019-02-22 19:07:54.677 28670-28670/? D/COPYDATABASE: Finished copying Database pev1.db from the assets folder, to  /data/user/0/mjt.so54807516/databases/pev1.db8192were copied, in 2 blocks of size 4096.
2019-02-22 19:07:54.678 28670-28670/? D/COPYDATABASE: All Streams have been flushed and closed.
2019-02-22 19:07:54.678 28670-28670/? D/DBPATH: DB Path is /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:07:54.701 28670-28670/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@71528f1
2019-02-22 19:07:54.701 28670-28670/? I/System.out: 0 {
2019-02-22 19:07:54.701 28670-28670/? I/System.out:    type=table
2019-02-22 19:07:54.701 28670-28670/? I/System.out:    name=user
2019-02-22 19:07:54.701 28670-28670/? I/System.out:    tbl_name=user
2019-02-22 19:07:54.702 28670-28670/? I/System.out:    rootpage=2
2019-02-22 19:07:54.702 28670-28670/? I/System.out:    sql=CREATE TABLE "user" (
2019-02-22 19:07:54.702 28670-28670/? I/System.out:   "_id" INTEGER NOT NULL,
2019-02-22 19:07:54.702 28670-28670/? I/System.out:   "user" TEXT,
2019-02-22 19:07:54.702 28670-28670/? I/System.out:   "password" TEXT,
2019-02-22 19:07:54.702 28670-28670/? I/System.out:   PRIMARY KEY ("_id")
2019-02-22 19:07:54.702 28670-28670/? I/System.out: )
2019-02-22 19:07:54.702 28670-28670/? I/System.out: }
2019-02-22 19:07:54.702 28670-28670/? I/System.out: 1 {
2019-02-22 19:07:54.702 28670-28670/? I/System.out:    type=table
2019-02-22 19:07:54.702 28670-28670/? I/System.out:    name=android_metadata
2019-02-22 19:07:54.702 28670-28670/? I/System.out:    tbl_name=android_metadata
2019-02-22 19:07:54.702 28670-28670/? I/System.out:    rootpage=3
2019-02-22 19:07:54.702 28670-28670/? I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2019-02-22 19:07:54.702 28670-28670/? I/System.out: }
2019-02-22 19:07:54.702 28670-28670/? I/System.out: <<<<<
2019-02-22 19:07:54.703 28670-28670/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@1e20cd6
2019-02-22 19:07:54.703 28670-28670/? I/System.out: 0 {
2019-02-22 19:07:54.703 28670-28670/? I/System.out:    _id=1
2019-02-22 19:07:54.703 28670-28670/? I/System.out:    user=Fred
2019-02-22 19:07:54.703 28670-28670/? I/System.out:    password=fredpassword
2019-02-22 19:07:54.703 28670-28670/? I/System.out: }
2019-02-22 19:07:54.703 28670-28670/? I/System.out: 1 {
2019-02-22 19:07:54.703 28670-28670/? I/System.out:    _id=2
2019-02-22 19:07:54.703 28670-28670/? I/System.out:    user=Mary
2019-02-22 19:07:54.704 28670-28670/? I/System.out:    password=marypassword
2019-02-22 19:07:54.704 28670-28670/? I/System.out: }
2019-02-22 19:07:54.704 28670-28670/? I/System.out: <<<<<
2019-02-22 19:07:54.705 28670-28670/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@acdb57
2019-02-22 19:07:54.705 28670-28670/? I/System.out: 0 {
2019-02-22 19:07:54.705 28670-28670/? I/System.out:    _id=1
2019-02-22 19:07:54.705 28670-28670/? I/System.out:    user=Fred
2019-02-22 19:07:54.705 28670-28670/? I/System.out:    password=fredpassword
2019-02-22 19:07:54.706 28670-28670/? I/System.out: }
2019-02-22 19:07:54.706 28670-28670/? I/System.out: 1 {
2019-02-22 19:07:54.706 28670-28670/? I/System.out:    _id=2
2019-02-22 19:07:54.706 28670-28670/? I/System.out:    user=Mary
2019-02-22 19:07:54.706 28670-28670/? I/System.out:    password=marypassword
2019-02-22 19:07:54.706 28670-28670/? I/System.out: }
2019-02-22 19:07:54.706 28670-28670/? I/System.out: 2 {
2019-02-22 19:07:54.706 28670-28670/? I/System.out:    _id=3
2019-02-22 19:07:54.706 28670-28670/? I/System.out:    user=mr new user
2019-02-22 19:07:54.706 28670-28670/? I/System.out:    password=a password
2019-02-22 19:07:54.706 28670-28670/? I/System.out: }
2019-02-22 19:07:54.706 28670-28670/? I/System.out: <<<<<

-666是版本,因为不存在文件,因此尝试从文件获取版本时返回默认值,以指示无法获取版本。

2.第二次运行时,除了版本号为1外,其他都一样。

2019-02-22 19:09:43.724 28730-28730/mjt.so54807516 D/DBFILEVERSION: Database File Version = 1
2019-02-22 19:09:43.724 28730-28730/mjt.so54807516 D/DBFILEVERSION: Asset Database File Version = 0
2019-02-22 19:09:43.724 28730-28730/mjt.so54807516 D/DBFILEVERSION: Asset Database File Version = 0
2019-02-22 19:09:43.725 28730-28730/mjt.so54807516 D/DBPATH: DB Path is /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:09:43.725 28730-28730/mjt.so54807516 D/DBPATH: DB Path is /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:09:43.729 28730-28730/mjt.so54807516 I/System.out: >>>>> 
..... etc

3.将DBVERSION更改为2后的下一次运行

2019-02-22 19:13:49.157 28866-28866/mjt.so54807516 D/DBFILEVERSION: Database File Version = 1
2019-02-22 19:13:49.158 28866-28866/mjt.so54807516 D/DBFILEVERSION: Asset Database File Version = 0
2019-02-22 19:13:49.158 28866-28866/mjt.so54807516 D/DBFILEVERSION: Asset Database File Version = 0
2019-02-22 19:13:49.158 28866-28866/mjt.so54807516 D/DBPATH: DB Path is /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:13:49.158 28866-28866/mjt.so54807516 D/DBPATH: DB Path is /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:13:49.158 28866-28866/mjt.so54807516 D/COPYDATABASE: Initiated Copy of the database file pev1mod.db from the assets folder.
2019-02-22 19:13:49.159 28866-28866/mjt.so54807516 D/COPYDATABASE: Asset file pev1mod.db found so attmepting to copy to /data/user/0/mjt.so54807516/databases/pev1.db
2019-02-22 19:13:49.159 28866-28866/mjt.so54807516 D/COPYDATABASE: Attempting copy of block 1 which has 4096 bytes.
2019-02-22 19:13:49.159 28866-28866/mjt.so54807516 D/COPYDATABASE: Attempting copy of block 2 which has 4096 bytes.
2019-02-22 19:13:49.159 28866-28866/mjt.so54807516 D/COPYDATABASE: Attempting copy of block 3 which has 4096 bytes.
2019-02-22 19:13:49.159 28866-28866/mjt.so54807516 D/COPYDATABASE: Attempting copy of block 4 which has 4096 bytes.
2019-02-22 19:13:49.159 28866-28866/mjt.so54807516 D/COPYDATABASE: Finished copying Database pev1.db from the assets folder, to  /data/user/0/mjt.so54807516/databases/pev1.db16384were copied, in 4 blocks of size 4096.
2019-02-22 19:13:49.159 28866-28866/mjt.so54807516 D/COPYDATABASE: All Streams have been flushed and closed.
2019-02-22 19:13:49.186 28866-28866/mjt.so54807516 E/SQLiteDatabase: Error inserting password=fredpassword user=Fred
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: user.user (code 2067 SQLITE_CONSTRAINT_UNIQUE)
        at 
    .........
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
2019-02-22 19:13:49.191 28866-28866/mjt.so54807516 E/SQLiteDatabase: Error inserting password=a password user=mr new user
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: user.user (code 2067 SQLITE_CONSTRAINT_UNIQUE)
        at 
   .............
2019-02-22 19:13:49.209 28866-28866/mjt.so54807516 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@34252b0
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out: 0 {
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:    type=table
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:    name=user
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:    tbl_name=user
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:    rootpage=2
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:    sql=CREATE TABLE "user" (
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:   "_id" INTEGER NOT NULL,
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:   "user" TEXT,
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:   "password" TEXT,
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:   "email" TEXT,
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:   PRIMARY KEY ("_id"),
2019-02-22 19:13:49.210 28866-28866/mjt.so54807516 I/System.out:   CONSTRAINT "user" UNIQUE ("user")
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out: )
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out: }
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out: 1 {
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    type=index
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    name=sqlite_autoindex_user_1
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    tbl_name=user
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    rootpage=4
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    sql=null
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out: }
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out: 2 {
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    type=table
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    name=android_metadata
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    tbl_name=android_metadata
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    rootpage=3
2019-02-22 19:13:49.211 28866-28866/mjt.so54807516 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2019-02-22 19:13:49.212 28866-28866/mjt.so54807516 I/System.out: }
2019-02-22 19:13:49.212 28866-28866/mjt.so54807516 I/System.out: <<<<<
2019-02-22 19:13:49.212 28866-28866/mjt.so54807516 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@c8f0529
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out: 0 {
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out:    _id=1
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out:    user=Fred
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out:    password=fredpassword
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out:    email=fred@email.com
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out: }
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out: 1 {
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out:    _id=2
2019-02-22 19:13:49.213 28866-28866/mjt.so54807516 I/System.out:    
...... etc

MikeT 7 年前

注意:这个答案是对主要答案的补充

如果只引入新的数据/模式而不需要保留用户数据,则可以使用更简单版本的数据库助手,例如:-

PVE1DBHelper。JAVA

/**
 * SIMPLE CASE EXAMPLE WHENEVER APP's DBVERSION is changed re-copy asset DB to db file
 */
public class PEV1DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "pev1.db";
    public static final int DBVERSION = 1; //<<<<<<<<<< increase and db file from assets will be re-copied
    Context mContext;

    public PEV1DBHelper(Context context) {

        super(context, DBNAME, null, DBVERSION);
        mContext = context;

        int dbversion = DBAssetHandler.getVersionFromDBFile(mContext,DBNAME);
        Log.d("DBFILEVERSION","Database File Version = " + String.valueOf(dbversion));

        // Alternative to onUpgrade
        // bypass issues with potential DB re-open already closed due to onUpgrade being passed SQLiteDatabase
        // i.e. done before any attempt to get open the database
        if (DBVERSION > dbversion & DBAssetHandler.checkDataBase(mContext,DBNAME)) {
            Log.d("UPGRADING","Re-copying database file from the assets file due to App DBVERSION change.");
            DBAssetHandler.copyDataBase(mContext,DBNAME,true);
            DBAssetHandler.clearForceBackups(mContext,DBNAME);
        }

        // Original copy from the assets folder
        if (!DBAssetHandler.checkDataBase(mContext,DBNAME)) {
            Log.d("INITIALDBCOPY","Copying database file from the assets file due to DB not existing.");
            DBAssetHandler.copyDataBase(mContext,DBNAME,true); // no need for true as existing should exist
            DBAssetHandler.clearForceBackups(mContext,DBNAME); // also no need for clearing backups as none
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

注意下面的评论 资产文件夹中的原始副本 第i部分,仅一行 DBAssetHandler.copyDataBase(mContext,DBNAME,false); 可以使用