Is SQLite/Android Room the right database for my app?

Question

I'm building an app where the user will control a few sliders, produce an output based on those sliders, and then that output will be saved so they can scroll through their history. The sliders will be labeled with information and attributes assigned, the outputs of the sliders will be procedurally generated (with the exception of a few pre-coded examples). So for example, if three sliders are "red", "green", and "blue", then sliding the red and green sliders all the way on would output "yellow", and then "yellow" would be saved in the user's history with the current date and time. All of this will be done on-device with nothing coming from or going to a server.

On the data management end, this seems like three databases, each with a many-to-many relationship with the others. ie, each slider will be attached to multiple outputs and each output can be attached to multiple sliders; then, each output can be attached to multiple timestamps, although each timestamp will be attached to just one output.

In order to achieve this, I think I need to use SQLite + Room for data management. I'm imagining one table for the sliders, a second table for the outputs, and a third for the user's history, plus join tables for sliders-to-outputs and outputs-to-history.

My only coding experience is hobbyist-level web development, so Kotlin, SQLite, room, relational databases, and Android development is all new to me. I'm just trying to figure out what tools I need to learn in order to build this so I don't invest weeks of time just to learn I'm not even using the right tools for the job. I don't know what I don't know, so any help is appreciated.

Answer 1

Is SQLite/Android Room the right database for my app?

In short Yes.

All of this will be done on-device with nothing coming from or going to a server.

Then SQLite/Room would be suitable as each device would have it's own database.

On the data management end, this seems like three databases, each with a many-to-many relationship with the others.

There would only be the need for a single database and probably just the one table and there would be no need for relationships.

I don't know what I don't know, so any help is appreciated.

Perhaps consider the following working demo that would appear to do something along the lines of what you need:-

• Displays 3 sliders
• has a save button that will save the current slider settings (roughly)
  • Type can be entered in the Edit Text to save a description as well as the values
  • The timestamp (down to a second rather than to a millisecond) of when the entry was made
• displays a list of the saved sliders
• if an item in the list is clicked then the sliders will be changed according to the values saved

So first the @Entity annotated class (the table) SliderHiistory:-

@Entity
data class SliderHistory(
    @PrimaryKey
    var sliderHistoryId: Long?=null,
    var sliderTimestamp: Long= System.currentTimeMillis() / 1000,
    var sliderDescription: String,
    var sliderRed: Float,
    var sliderGreen: Float,
    var sliderBlue: Float
)

• Each var will be a column (value) in the table.
  • The sliderHistoryId column is a special column, as such, it uniquely identifies the row (it will be 1 for the first row, probably 2 for the next and so on). The value, if not provided, will be generated by SQLite.
    • this is a special value an will be an alias of the rowid, this is perhaps the best explanation https://www.sqlite.org/autoinc.html
  • sliderTimestamp column will store the timestamp (date/time) of when the instance is created (unless overridden by specifying a value, it is not intended to be overwritten)
  • sliderDescription allows a description to be saved, which may be useful to the end user.
  • sliderRed, SliderGreen and SliderBlue will hold the 3 values.

To access the data (insert and extract) an @Dao annotated interface SliderHistoryDao

@Dao
interface SliderHistoryDao {
    @Insert
    fun insert(sliderHistory: SliderHistory): Long
    @Query("SELECT * FROM sliderHistory WHERE sliderHistoryId=:id")
    fun getSliderSettingsFromHistory(id: Long): SliderHistory
}

• One function to insert data, one function to extract a single row according to id as a SliderHistory object.

To tie the above together an @Database annotated abstract class SliderDatabase :-

@Database(entities = [SliderHistory::class], exportSchema = false, version = 1)
abstract class SliderDatabase: RoomDatabase() {
    abstract fun getSliderHistoryDao(): SliderHistoryDao

    companion object {
        private var instance: SliderDatabase?=null
        fun getInstance(context: Context): SliderDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,SliderDatabase::class.java,"slider.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as SliderDatabase
        }
    }
}

• The database will be saved in a file named slider.db in the default folder (data/data/<the_package_name>/databases). Note for convenience and brevity, the demo runs on the main thread (it is not recommended for a published App to access the database on the main thread).

The function getInstance in the companion object uses what is know as a singleton, so only a single instance of the database class is retrieved throughout.

The Activity MainActivity uses the layout activity_main which defines the 3 Sliders , the EditText for the description, the Button to save the data and the ListView that is used to display the stored data:-

?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Hello World!" />

    <com.google.android.material.slider.Slider
        android:contentDescription="Red"
        android:id="@+id/red_slider"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        >
    </com.google.android.material.slider.Slider>
    <com.google.android.material.slider.Slider
        android:contentDescription="Green"
        android:id="@+id/green_slider"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        >
    </com.google.android.material.slider.Slider>

    <com.google.android.material.slider.Slider
        android:contentDescription="Blue"
        android:id="@+id/blue_slider"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        >
    </com.google.android.material.slider.Slider>

    <EditText
        android:id="@+id/save_Description"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        >
    </EditText>

    <Button
        android:id="@+id/save_button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="SAVE"
        >
    </Button>

    <ListView
        android:id="@+id/history_listview"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@color/teal_200"
        >
    </ListView>

</LinearLayout>

Finally Main_Activity that ties everything altogether:-

class MainActivity : AppCompatActivity() {

    /* UI variables to be set when the activity is started */
    lateinit var sliderRed: Slider
    lateinit var sliderGreen: Slider
    lateinit var sliderBlue: Slider
    lateinit var butonSave: Button
    lateinit var editTextSaveDescription: EditText
    lateinit var listviewHistory: ListView
    /* These will be reset */
    var adapter: SimpleCursorAdapter?=null
    var csr: Cursor?=null

    /* The database/room variables */
    lateinit var db: SliderDatabase /* an instance of the SliderDatabase */
    lateinit var dao: SliderHistoryDao /* for accessing the DAO functions */

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        /* set up the UI variables */
        sliderRed = this.findViewById(R.id.red_slider)
        sliderGreen = this.findViewById(R.id.green_slider)
        sliderBlue = this.findViewById(R.id.blue_slider)
        butonSave = this.findViewById(R.id.save_button)
        editTextSaveDescription = this.findViewById(R.id.save_Description)
        listviewHistory = this.findViewById(R.id.history_listview)

        /* Prepare to use the database */
        db = SliderDatabase.getInstance(this)
        dao = db.getSliderHistoryDao()

        /* setup the Save button on click listener */
        setUpSaveButton()
        /* setup the ListView */
        setOrRefreshHistoryList()
    }

    /* Function to prepare the Save button so it saves the current settings when clicked */
    fun setUpSaveButton() {
        butonSave.setOnClickListener {
            dao.insert(
                SliderHistory(
                    sliderRed = sliderRed.value,
                    sliderGreen = sliderGreen.value,
                    sliderBlue = sliderBlue.value,
                    sliderDescription = editTextSaveDescription.text.toString()
                )
            )
            setOrRefreshHistoryList()
        }
    }

    /* Function to prepare (first invocation) or refresh the ListView
        display data, if any, and setting the onItemClickListener so that
        if an item in the ListView is clicked it gets the stored values and
        applies them to the slider
    * */
    fun setOrRefreshHistoryList() {
        /* first get the latest data */
        /* Note as a Cursor adapter is used, a query is used as opposed to retrieving SliderHistoryObjetcs
            A CursorAdapter is very simple to implement, it being designed to work with Cursors
         */
        csr = db.getOpenHelper().writableDatabase.query("SELECT *,sliderhistoryId AS ${BaseColumns._ID}, sliderRed||':'||sliderGreen||':'||sliderBlue AS sliderValues FROM sliderhistory")
        if (adapter==null) {
            adapter = SimpleCursorAdapter(
                this,
                android.R.layout.simple_list_item_2,
                csr,
                arrayOf("sliderValues","sliderDescription"),
                intArrayOf(android.R.id.text1,android.R.id.text2),
                0
            )
            listviewHistory.adapter = adapter

            /* setup the onItemClickListener */
            listviewHistory.setOnItemClickListener { parent, view, position, id ->
                val sh = this.dao.getSliderSettingsFromHistory(id)
                sliderRed.value = sh.sliderRed
                sliderGreen.value = sh.sliderGreen
                sliderBlue.value = sh.sliderBlue
            }
        } else {
            adapter!!.swapCursor(csr)
        }
    }
}

Results

When initially run :-

• The database, at this stage is empty (although created due to the attempt to extract data).

If Black (or anything) is entered into the Edit Text and the SAVE button is clicked then :-

i.e. the row that was inserted, is now displayed.

Now if

• the red slider is move all the way over to the right and red is entered into the edit text and save is clicked, and then
• only the green slider is all the way over to the right and green is entered into the edit text and save is clicked, and then
• likewise for blue and then
• all three sliders are moved and white is entered then:-

Now if Black is clicked in the List :-

Red is clicked :-

etc. (of course intermediate values will be saved likewise, that is all three values are saved every time).

Now if you wanted to extract the date and time and have it show in the ListView instead of the combined colour values, then changing the line:-

csr = db.getOpenHelper().writableDatabase.query("SELECT *,sliderhistoryId AS ${BaseColumns._ID}, sliderRed||':'||sliderGreen||':'||sliderBlue AS sliderValues FROM sliderhistory")

to

csr = db.getOpenHelper().writableDatabase.query("SELECT *,sliderhistoryId as ${BaseColumns._ID}, datetime(sliderTimestamp,'unixepoch') AS sliderValues FROM sliderhistory")

will facilitate this resulting in, for example :-

Additional

The following subtle changes enable the loaded colour to be seen and also introduces a 4th colour element (transparency).

First a TextView is added to the layout:-

<TextView
    android:id="@+id/swatch"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text=""
    >
</TextView>

(after the 3 sliders (you might want a fourth))

Then a new function (for the demo include as a function in MainActivity) :-

fun convertRGandBtoInt(red: Int, green: Int, blue: Int): Int {
    return (red * 65536) + (green * 256) + blue
}

• this was located between the last two curly brackets }'s

And then after the line (in the onItemClickListener) sliderBlue.value = sh.sliderBlue the following lines were added :-

            val bg = convertRGandBtoInt((sh.sliderRed * 255F).toInt(), (sh.sliderGreen * 255F).toInt(), (sh.sliderBlue * 255F).toInt())
            listviewHistory.setBackgroundColor(bg + (128 * 65536 * 256)) /* 50 % transparent */
            colourSwatch.setBackgroundColor(bg + (255 * 65536 * 256)) /* solid */

• The first changes the background colour of the ListView BUT with it semi-transparent (so you can see the items), thus it is not the real colour.
• The second changes the colour of the TextView but this time with no transparency, so the actual colour.

With this and the Black,Red,Green, Blue and White saved then clicking not only sets the sliders but also changes the colours e.g.

Black

Red

Green

etc