How can I run multiple queries on Android App Inspection

Question

I would like to add thousands of rows of data to an sqlite room database table, sales, in my android application. Below is an example query that I run in App Inspection to add a single row of data to the sales table in the database.

INSERT INTO sales VALUES (1, 'Lollipop sweets', 'Food', 'Other', 2, 10, 1680574354950)

I have thousands of rows to add to the sales table. In SQL it is normally possible to separate multiple queries with a semi-colon (;) and they all get executed one after the other. Is there an equivalent for the room database when using App Inspection queries? Is it possible to add all the rows at once using App Inspection queries? If this is not possible. What is a better way add the data?

Answer 1

Is it possible to add all the rows at once using App Inspection queries?

App Inspection only allows a single query to be executed.

Furthermore populating the database via App Inspection will only populate the database on the device(s) used via Android Studio. It would not be a suitable for an App that is made publicly available (if you have existing App then you would probably want to uninstall the App this will effectively delete the database and thus meet the database not existing criteria).

Is there an equivalent for the room database when using App Inspection queries?

Not specifically for Room or via Android Studio.

What is a better way add the data?

Probably to utilise a pre-populated database, that can be created and or maintained via an SQLite Tool, such as-

• Navicat for SQLite
• DBeaver
• DB Browser (SQLite)
• SQLite Studio

These all allow some form of import from external data (CSV at least)

However, it should be noted that Room is a restricted form of SQLite, and has expectations which are really requirements. It is suggested that you

1. start with the @Entity annotated classes (i.e. tables), then
2. create an appropriate @Database annotated abstract class that includes all the @Entity annotated classes, then
3. successfully compile the project, then
4. using the Android View inspect the directory named java(generated) and find the class that has the same name as the @Database annotated class but suffixed with _Impl, then
5. find the createAllTables method (function), 6 copy the SQL for the creation of the tables (ignore the room_master table) into the SQLite tool that you have chosen, then
6. populate the database accordingly, then
7. in Android Studio create an assets folder and copy the saved database file into the assets folder, then
8. add the .createFromAsset method to when you invoke the Room's .databaseBuilder method.

The App, when the database does not exist, will copy the file from the asset to it's expected location. If the App is distributed then the file is part of the package and will therefore be distributed.

---

Demo

---

Assuming the data is held in a spreadsheet:-

• more rows

Step 1 Create the @Entity annotated class (names assumed):-

@Entity
data class Sale(
    @PrimaryKey
    var saleid: Long?=null,
    var item: String,
    var type: String,
    var whatever: String,
    var unitsPurchased: Int,
    var unitCost: Int,
    var timestamp: Long
)

• note rather than sales the table will be named sale (normally the singular rather than plural)

Step 2 Create an @Database annotated abstract class and define the entity(ies) for the table(s):-

@Database(entities = [Sale::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {

}

Step 3 Successfully compile:-

BUILD SUCCESSFUL in 4s
34 actionable tasks: 7 executed, 27 up-to-date

Step 4/5 Locate the generated java with the SQL for creating the table and then :-

CREATE TABLE IF NOT EXISTS `sale` (`saleid` INTEGER, `item` TEXT NOT NULL, `type` TEXT NOT NULL, `whatever` TEXT NOT NULL, `unitsPurchased` INTEGER NOT NULL, `unitCost` INTEGER NOT NULL, `timestamp` INTEGER NOT NULL, PRIMARY KEY(`saleid`));

Step 6-A

Create the formula in the spreadsheet (in CELL J1 for example):-

="INSERT OR IGNORE INTO sale VALUES (" & A1 & ",'" & B1 & "','" & C1 & "','" & D1 & "'," & E1 & "," & F1 & "," & G1 & ");"

Copy the forumla to all the other rows with data and then copy all the rows in the J column. Paste these into an SQL/Query of the SQLite Tool (Navicat in this case):-

• as can be seen the rows have all been added.

Step 6-B save the database (with Navicat also close it to properly close the database):-

• note that currently the database file is called soanswer.db, the name does not matter as it can be renamed. However, what is important is that there is NOT an soanswer.db-wal or an soanswer.db-shm file.
  • If these other 2 files exist then the database has not been properly closed and issues may ensue

Step 7 create and the populate the assets folder accordingly.

Use File/New/Folder/Assets and use main :-

The go to the folder where the database file is stored right click it and copy it.

Right click the asset folder in Android Studio and paste renaming the file e.g. :-

Step 8 amend the Room databaseBuilder to use the createFromAsset method

At this stage there is no databaseBuilder method invocation, so:-

@Dao
interface SaleDAO {
    @Query("SELECT * FROM sale")
    fun getAllSales(): List<Sale>
}
@Database(entities = [Sale::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getSaleDAO(): SaleDAO /* Added as it would be added */
    
    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"sale.db")
                    .allowMainThreadQueries() /* included for the brevity of the demo */
                    .createFromAsset("sale.db") /* <<<<<<<<<< */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

• Note the @Dao annotated interface added so something can be done (the database will only be created if it is accessed)

Step 9 Show that it works. e.g. add and then run:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: SaleDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db= TheDatabase.getInstance(this)
        dao = db.getSaleDAO()
        for (s in dao.getAllSales()) {
            Log.d("SALEINFO","Item is ${s.item} Type is ${s.type} etc....")
        }
    }
}

RESULT

The log includes:-

2023-11-07 07:07:15.426 D/SALEINFO: Item is Lollipop sweets Type is Food etc....
2023-11-07 07:07:15.427 D/SALEINFO: Item is Something Else Type is Food etc....
2023-11-07 07:07:15.428 I/chatty: uid=10808(a.a.so77430897kotlinroomsales) identical 26 lines

App Inspection shows:-