Move data from sqlite database file to room

Question

I have an project that deals with an external database (sqlite database file) and I want to move to the room database as follows

1. The contents of the database (student_database.db) are initially entered by a third-party application, for example (DB Browser for SQLite).
2. When I run my app the datebase room is created and put the data from student_database.db 3 My app displays data from (room db) in Recycler View
3. New data can be added to the database (room)

Here are some files of my project

 @Entity(tableName = "books_table")
public class Book {

 @PrimaryKey(autoGenerate = true)
 @ColumnInfo(name = "book_id")
 private long id;

 @ColumnInfo(name = "book_name")
 private String name;

 @ColumnInfo(name = "all")
 private long all;

 @ColumnInfo(name = "profile_id")
 private long profileId;

 public Book() {
 }

 public Book(String name, long all, long profileId) {
     this.name = name;
     this.all = all;
     this.profileId = profileId;
 }


@Dao
public interface BookDAO {

 @Insert
 void insertBook(Book... book);

 @Update
 void updateBook(Book... book);

@Entity (tableName = "profiles_table")
public class Profile {

    @PrimaryKey(autoGenerate = true)
    long id;

    @ColumnInfo (name = "profile_name")
    String profileName;

    public Profile() {
    }

    public Profile(long id, String profileName) {
        this.id = id;
        this.profileName = profileName;
    }

    public Profile(String profileName) {
        this.profileName = profileName;
    }

@Dao
public interface ProfileDAO {

    @Insert
    void insertProfile(Profile... profile);

    @Update
    void updateProfile(Profile... profile);

@Database(entities = {Book.class  , Profile.class}, version = 1, exportSchema = false)
public abstract class MyRoomDatabase extends RoomDatabase {

    public abstract BookDAO bookDao();
    public abstract ProfileDAO profileDAO();

    private static volatile MyRoomDatabase INSTANCE;

    private static final int NUMBER_OF_THREADS = 4;

    static final ExecutorService databaseWriteExecutor =
            Executors.newFixedThreadPool(NUMBER_OF_THREADS);



    static MyRoomDatabase getDatabase(final Context context) {
        if (INSTANCE == null) {
            synchronized (MyRoomDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                            MyRoomDatabase.class, "student_prepare_room_database")
                            .addCallback(sRoomDatabaseCallback)
                            .build();
                }
            }
        }
        return INSTANCE;
    }
    

    private static RoomDatabase.Callback sRoomDatabaseCallback = new RoomDatabase.Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            super.onCreate(db);

            // If you want to keep data through app restarts,
            // comment out the following block
            databaseWriteExecutor.execute(() -> {
                // Populate the database in the background.
                // If you want to start with more words, just add them.

            });
        }
    };
}

student_database.db

CREATE TABLE "profile_table" ("id" INTEGER,"profile_name" TEXT,PRIMARY KEY("id")); [1]: https://i.sstatic.net/lDhtR.png

CREATE TABLE "books_table" ("book_id" INTEGER,"book_name" TEXT,"all" INTEGER,"profile_id" INTEGER,PRIMARY KEY("book_id" AUTOINCREMENT)); [2]: https://i.sstatic.net/NiuZe.png

Answer 1

Typically this would be undertaken by making the external database an asset (pre-packaged database) and utilising .createFromAsset which will undertake the copy of the database.

However, using the above does REQUIRE that the asset database strictly conforms to the room schema which is defined by the classes annotated with @Entity and included in the entities list/array as provided via the @Database annotation.

The pre-packaged database can be modified to adapt by using the prePackagedDatabaseCallback. However, it is probably simpler to make changes to the external database so that it does match what room expects.

In your case, as an example, you have a mismatch in the profile table as in the external/pre-packaged database you have the id as (effectively) INTEGER PRIMARY KEY whilst by using @PrimaryKey(autoGenerate = true) room expects INTEGER PRIMARY KEY AUTOINCREMENT.

• AUTOINCREMENT (in room autogenerate = true) is inefficient and rarely needed. You could change to use @PrimaryKey.

Let Room make it Easy

Only one example of mismatches has been discussed as there is very likely little need to delve into all the mismatches that would cause issues. That is room will generate the expected CREATE TABLE .... statements when you compile the project when the classes annotated with @Entity have been created and included in the list of classes in the @Database annotation. Thus you can easily make the required alterations to the external/pre-packaged tables based upon the SQL created by room.

The SQL created by Room is found in the java(generated) (visible in the Android View)m, the class to look for is one named the same as the class annotated with @Database but suffixed with _Impl. The SQL is found in the method named createAllTables. You do not want to create the room_master_table that is a table that room uses for storing a hash representation of the schema and is used for check if changes have been made to the schema.

Demonstration

Using the code/schemas as per your question (with some subtle changes to simplify the demo).

Step 1 - Pre-Packaged Database as per your schema

• Obviously you have this

First a Database was created according to your SQL and populated with some 100 profiles and 10000 books in a third party tool (Navicat) as per:-

DROP TABLE IF EXISTS "profile_table";
DROP TABLE IF EXISTS "books_table";
CREATE TABLE IF NOT EXISTS "profile_table" ( "id" INTEGER, "profile_name" TEXT, PRIMARY KEY("id") );
CREATE TABLE "books_table" ( "book_id" INTEGER, "book_name" TEXT, "all" INTEGER, "profile_id" INTEGER, PRIMARY KEY("book_id" AUTOINCREMENT) );
/* Generate some data 100 profiles and 10000 books*/
WITH loop(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM loop LIMIT 100)
INSERT INTO profile_table (profile_name) SELECT  'Profile'||x FROM loop;
WITH loop(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM loop LIMIT 10000)
INSERT INTO books_table (book_name,`all`,profile_id) SELECT 'Book'||x,abs(random() % 10),(abs(random()) % (SELECT max(id) FROM profile_table)) + 1 FROM loop;
/* Display the data */
SELECT * FROM books_table JOIN profile_table ON profile_id=id ORDER BY CAST(substr(profile_name,8) AS INTEGER);

The query showing data such as :-

• 
• There are in fact 10000 rows displayed

Step 2 - Create the Asset The assets directory was created and the database copied and pasted as student_database.db :-

• Note a copy of this was made for re-runability

Step 3 add createFromAsset and other changes to run initial test

The MyRoomDatabase class was changed to add .createFromAsset :-

            if (INSTANCE == null) {
                INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                        MyRoomDatabase.class, "student_prepare_room_database")
                        .createFromAsset("student_database.db") //<<<<< STEP 3 - ADDED
                        .addCallback(sRoomDatabaseCallback) //<<<< added for convenience/brevity
                        .allowMainThreadQueries()
                        .build();
            }

Additionally BookDAO was amended by adding:-

@Query("SELECT * FROM books_table")
List<Book> getAllBooks();

• to allow simple access to the database (unless accessed the database won't be opened checked etc.).

Lastly MainActivity was coded as:-

public class MainActivity extends AppCompatActivity {

    MyRoomDatabase db;
    BookDAO bookDAO;
    ProfileDAO profileDAO;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = MyRoomDatabase.getDatabase(this);
        bookDAO = db.bookDao();
        profileDAO = db.profileDAO();
        for (Book b: bookDAO.getAllBooks()) {
            Log.d("BOOKINFO","Book is " + b.getName());
        }
    }
}

Step 4 Room SQL

The project was then compiled (getters/setters added) the createALLTables being:-

• Note AUTOINCREMENT on the profile table.

STEP 5 RUN (see Note)

• Note This step is purely to demonstrate what happens if the database is not altered.

With all the above, if the App is then run (not that it should be as it will fail). As expected it fails with the Expected/Found mismatch issue BUT clearly it has copied the asset:-

2022-01-05 07:59:02.200 1941-1941/? D/AndroidRuntime: Shutting down VM
2022-01-05 07:59:02.202 1941-1941/? E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so70580333prepackageddatabase, PID: 1941
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so70580333prepackageddatabase/a.a.so70580333prepackageddatabase.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: books_table(a.a.so70580333prepackageddatabase.Book).
     Expected:
    TableInfo{name='books_table', columns={all=Column{name='all', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, book_id=Column{name='book_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, book_name=Column{name='book_name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, profile_id=Column{name='profile_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='books_table', columns={all=Column{name='all', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, profile_id=Column{name='profile_id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, book_id=Column{name='book_id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, book_name=Column{name='book_name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)

STEP 6 Amending the pre-packaged database

The 3rd party tool is revisited and the following is used:-

DROP TABLE IF EXISTS books_table_original;
DROP TABLE IF EXISTS profile_table_original;
ALTER TABLE books_table RENAME TO books_table_original;
ALTER TABLE profile_table RENAME TO profile_table_original;
/* COPIED FROM createAllTables method */
CREATE TABLE IF NOT EXISTS `books_table` (`book_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `book_name` TEXT, `all` INTEGER NOT NULL, `profile_id` INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS `profiles_table` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `profile_name` TEXT);
INSERT INTO profiles_table SELECT * FROM profile_table_original;
INSERT INTO books_table SELECT * FROM books_table_original;
/* CLEANUP */
DROP TABLE IF EXISTS books_table_original;
DROP TABLE IF EXISTS profile_table_original;
VACUUM;

• note that the expected/found issues were not even looked at

  • if they were looked at you can see that the All column is expected to be NOT NULL but it found that the All column was defined without and thus allows NULLS (mismatch).
  • likewise for the book_id column (another mismatch)
  • likewise for the profile_id column (another mismatch)
  • if these are fixed then the profiles_table would then result in more mismatches as the table names are different (altered/corrected in the above).

• note that if foreign key constraints are introduced then the order in which tables are deleted matters as does the order in which they are created (but I believe room sorts the tables accordingly).

• The above assumes that the column sequence is the same for the tables in the pre-packaged database as they are in SQL built by Room. If the sequence differs then the INSERT can be modified to either:-

  • specify the the columns to be inserted in the order they are selected e.g. INSERT INTO profiles_table (id,profile_name) SELECT * FROM profile_table_original

  • specify the SELECT columns in the order according to definition e.g. INSERT INTO profiles_table SELECT (id,profile_name) FROM profile_table_original

  • specify columns rather than * for both the INSERT and the SELECT e.g. INSERT INTO profiles_table (profile_name,id) SELECT (profile_name,id) FROM profile_table_original

    • Note that this last example purposefully uses a different column order to demonstrate a different order. It does not affect the order of the columns in either table just the order of the columns in the intermediate output that is built within SQLite.

After running the above, the database is saved and copied to the assets folder.

• 
• in this case the database was initially pasted as AlteredFroRoom_student_database.db, the student_database.db ass deleted and then the AlterForRoom_student_database.db copied and then pasted as student_database.db. Note for an App that is to be distributed you would not want to have additional copies of the database as this increases the size of the distribution.

The App is then uninstalled and then rerun and it runs successfully.

• Note that as the database has been copied, the onCreate callback is not invoked (the onOpen would be).

The Log includes:-

2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book1
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book2
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book3
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book4
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book5
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book6
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book7
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book8
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book9
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book10
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book11
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book12
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book13
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book14
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book15
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book16
....