SQLite - 附加数据库

Question

我按照网上的一些信息尝试连接sqlite数据库，以便从一个sqlite数据库复制表格到另一个，但是似乎无法正常工作。我尝试使用以下代码连接数据库：

DB_PATH = context.getDatabasePath("WineDB.sqlite").toString();
SQLiteDatabase backupDatabase = backupDBHandler.getWritableDatabase();
backupDatabase.execSQL("ATTACH '" + DB_PATH + "' AS 'tempDb'");

到目前为止，它一直没有出现错误。 然后我尝试通过从tempDb复制来创建一个新表格在备份数据库中：

sqlDB.execSQL("CREATE TABLE my_Producent AS SELECT * FROM tempDb.my_Producent");

在执行时出现错误，提示“没有这样的表 'tempDb.my_Producent'”，但我确定该表存在于数据库“WineDB.sqlite”中。我是在onCreate方法中创建它的，而这个方法会在将数据库附加到backupDatabase之前调用。

感谢任何帮助 祝好 user2302510

- user1071762

---

ATTACH语句中的tempDb应该没有单引号。在SQLite中，单引号主要用于分隔字符串字面量。语句应采用以下形式：ATTACH 'drive/path/database_name' AS database_alias; - PositiveLogic

DB_PATH 的值是什么？ - CL.

@CL。DB_PATH是应用程序中生成的数据库路径，其中默认存储数据库。在我的情况下，它是/data/data/com.app.name/databases/WineDB.sqlite。 - user1071762

@PositiveLogic 我也尝试过不加引号。但是没有任何区别。 - user1071762

检查该文件是否存在。 - CL.

Answer 1

@Phil。是的，我让它工作了。但是我现在真的不记得当时的问题是什么了，所以我只会写一组简化后的步骤，涉及从firstDB复制到secondDB。我在firstDB的SQLiteOpenHelper中执行以下操作。

public class firstDBHandler extends SQLiteOpenHelper {

    SQLiteDatabase firstDB;
    //FIRST_DB_NAME is something like 'xxx1.sqlite'
    private static String FIRST_DB_PATH = context.getDatabasePath(FIRST_DB_NAME).toString();

    public void copyFromFirstToSecond(String secondDBName, int secondDBVersion) {
        //use the same context as for the firstDBHandler
        secondDBHandler = new SecondDBHandler(myContext, secondDBName, secondDBVersion);
        //here you create the secondDB if it does not exist yet
        try {
            secondDBHandler.createDataBase();
        } catch (IOException e) {
            e.printStackTrace();
        }
        SQLiteDatabase secondDB = secondDBHandler.getWritableDatabase();

        //note there are single quotation marks around FIRST_DB_PATH but none around tempDb
        secondDB.execSQL("ATTACH DATABASE '" + FIRST_DB_PATH + "' AS tempDb");

        //here you start to copy the tables from firstDB first by checking if the table exists in secondDB (secondDB is now the 'main' one, tempDB is the attached firstDB
        secondDB.execSQL("DROP TABLE IF EXISTS main." + SECOND_DB_TABLE_NAME);

        //here you create a table as a copy of
        secondDB.execSQL("CREATE TABLE main." + SECOND_DB_TABLE_NAME + " AS SELECT * FROM tempDb." + FIRST_DB_TABLE_NAME);

        //you can run the last two lines of code as many times as you need to copy all of the tables

        //after you have copied all of them, you need to detach the tempDB
        secondDB.execSQL("DETACH tempDb"); 
    }

}

public class SecondDBHandler extends SQLiteOpenHelper {

     //SECOND_DB_NAME is something like 'xxx2.sqlite'
    private static String SECOND_DB_PATH = context.getDatabasePath(SECOND_DB_NAME).toString();
     public void createDataBase() throws IOException {

          boolean dbExist = checkDataBase();
          if (dbExist) {
              // do nothing - database already exist
          } else {
              //This calls onCreate method of this SecondDBHandler, where you 
              //create tables that you need initially (but not those which you
              //intent to copy from firstDB)
              this.getReadableDatabase();
              this.close();
          }
    }

    public boolean checkDataBase() {
        File file = new File(SECOND_DB_PATH);
        return file.exists();
    }    
}

那么你只需要调用：

FirstDBHandler firstDBHandler = new FirstDBHandler(getBaseContext(), FIRST_DB_NAME, FIRST_DB_VERSION);
firstDBHandler.getReadableDatabase();
firstDBHandler.copyFromFirstToSecond(SECOND_DB_NAME, SECOND_DB_VERSION);

当然，对于SQLiteOpenHandler，两个onCreate方法都缺失了，但是应用程序可以自行决定在这里执行什么操作。

希望我的回答没有错误。我自己还没有测试过它，只是从我的更复杂的代码中提取了一个简化版本。如果有一些赞，我会将其标记为答案。

- user1071762

Answer 2

我找到了两种附加数据库的方法。

1. do NOT "attach database" in onCreate and onUpgrade. It will fail because of "Cannot attach database within transaction". But you can attach database when you init database, then call getReadableDatabase() to make sure database will be created and upgraded. Then you can run the attach command.

   sqLiteHelper = new SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION); // new SQLiteOpenHelper
   SQLiteDatabase db = sqLiteHelper.getReadableDatabase(); // make sure the database will be created
   db.execSQL("ATTACH DATABASE '/databases/xx.db' AS xx;"); // do attach database
   

2. But if you want to do attach before onUpgrade(), try this way:

在onCreate()中使用endTransaction，然后进行附加操作，最后再beginTransaction。

    public void onCreate(SQLiteDatabase db) {
        this.createTables(db); // do the normal onCreate() stuff

        db.setTransactionSuccessful();
        db.endTransaction();  // end transaction, so that you can do attach

        db.execSQL("ATTACH DATABASE '/databases/xx.db' AS xx;"); // do attach database
        db.execSQL("DETACH DATABASE xx;"); // detach database

        db.beginTransaction(); // begin transation again
    }

原因：

读取SQLiteOpenHelper.java的函数，我发现onCreate()和onUpgrade()会在事务过程中同时被调用。

- franny zhao

---

这不起作用，因为调用“getReadableDatabase()”将自动调用“onUpgrade()”。那么如何在调用onUpgrade()之前执行附加命令？ - Riccardo Leschiutta

嗯...我猜SQLite不希望你在onUpgrade()之前执行attach操作... - franny zhao

我找到了在onUpgrade()之前附加的方法，详见更新后的答案~ - franny zhao

为什么你在附加数据库之后立即分离数据库？ - shirley

在我的项目中，附加的数据库以后是无用的，所以我将其分离。你可以根据你的项目需要进行任何适当的操作。 - franny zhao