SQLite

//use SQLiteDatabase in some client
class SQLiteActivity : AppCompatActivity() {
    
    private lateinit var database : SQLiteDatabase

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        //get instance of writable or readable database to reuse it in multiple queries
        database = Database(this).writableDatabase
        //use database by defined methods below
    }

    override fun onDestroy() {
        //close costly database connection
        database.close()
        super.onDestroy()
    }
    
    //some database manage methods, there could be inside some manager with conversion some object type into raw data
    //notice how many lines of code are needed to do simple single query operation

    fun put() {
        //prepare data
        val values = ContentValues()
        values.put(DatabaseContract.Person.COLUMN_NAME, "Jack")
        values.put(DatabaseContract.Person.COLUMN_AGE, 50)

        //put the data and get id of new entry, returns -1 if fails
        val id = database.insert(DatabaseContract.Entry.TABLE_PERSON, null, values)
    }

    fun read() {
        //define the query params
        val table = DatabaseContract.Person.TABLE_PERSON
        val columns : Array<String>? = null //get all columns
        val selection = DatabaseContract.Person.COLUMN_NAME + " = ?" //columns for WHERE
        val args = arrayOf("Jack") //values for WHERE
        val groupBy = null //ignore
        val filterBy = null //ignore
        val sortOrder = DatabaseContract.Person.COLUMN_AGE + " DESC"

        //make query and read data from the cursor by iterator methods
        val cursor : Cursor = database.query(table, columns, selection, args, groupBy, filterBy, sortOrder)
        while (cursor.moveToNext()) {
            val age = cursor.getString(cursor.getColumnIndex(DatabaseContract.Person.COLUMN_AGE))
            //do something with item
        }
        cursor.close()
    }

    fun delete() {
        //define query params
        val table = DatabaseContract.Person.TABLE_PERSON
        val selection = DatabaseContract.Person.COLUMN_NAME + " LIKE ?"
        val args = arrayOf("Jack")

        //delete entries and get number of the removed items
        val count = database.delete(table, selection, args)
    }

    fun update() { 
        //prepare data
        val values = ContentValues()
        values.put(DatabaseContract.Person.COLUMN_AGE, 51)
        
        //define query params
        val table = DatabaseContract.Person.TABLE_PERSON
        val selection = DatabaseContract.Person.COLUMN_NAME + " LIKE ?"
        val args = arrayOf("Jack")
        
        //update entries and updated count
        val count = database.update(table, values, selection, args)
    }
}

//extend SQLiteOpenHelper
class Database(context : Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "Person.db"
    }

    //define SQL queries, use DatabaseContract
    private val SQL_CREATE =
        "CREATE TABLE ${DatabaseContract.Person.TABLE_PERSON} (" +
            "${BaseColumns._ID} INTEGER PRIMARY KEY," +
            "${DatabaseContract.Person.COLUMN_NAME} TEXT," +
            "${DatabaseContract.Person.COLUMN_AGE} INTEGER)"

    private val SQL_DELETE =
        "DROP TABLE IF EXISTS ${DatabaseContract.Person.TABLE_PERSON}"

    override fun onCreate(db: SQLiteDatabase?) {
        //just create database with specific structure
        db?.execSQL(SQL_CREATE)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        //do something when database structure has updated, e.g. clear all old data
        db?.execSQL(SQL_DELETE)
        onCreate(db)
    }

    //implement other methods like onDowngrade, onConfigure
}

//good practice is to define database scheme contract to simpler manage it
object DatabaseContract {

    //create inner class for each Table, BaseColumns has primary key field called _ID
    object Person : BaseColumns {
        const val TABLE_PERSON = "person"
        const val COLUMN_NAME = "name"
        const val COLUMN_AGE = "age"
    }
}

//represents a table of database, define scheme here
@Entity(
    tableName = "person",
    foreignKeys = [ForeignKey(entity = Address::class, parentColumns = ["id"], childColumns = ["addressId"])])
data class Person
(
    @PrimaryKey(autoGenerate = true)
    val id : Long,

    @ColumnInfo(name = "name") //set column nam for this field
    var fullName : String,

    //by default column name is field name so no need to use @ColumnInfo
    var age : Int,

    //some annotations can be declared as @Entity parameters e.g. foreignKeys instead of @ForeignKey
    var addressId : Long,

    @Embedded //fields of this class are columns of the table
    var Contact : Contact
)
{
    @Ignore //tell Room to ignore this constructor
    constructor(fullName : String, age : Int, addressId : Long, contact : Contact) : this(0, fullName, age, addressId, contact)

    //some class body
}

//by default tableName is a class name, indices speeds up select but slows down insert or update
@Entity(tableName = "address", indices = [Index(value = ["street"])])
data class Address(@PrimaryKey(autoGenerate = true) val id : Long, var city : String, var street : String) {

    @Ignore
    constructor(city : String, street: String) : this(0, city, street)
}

data class Contact(var phone : String, var email : String)

//define methods to use database
@Dao
interface PersonDao {

    //provide query command for @Query annotation
    @Query("SELECT * FROM person")
    fun getAll() : List<Person>

    @Query("SELECT * FROM person WHERE name LIKE :name LIMIT 1")
    fun findByName(name : String) : Person

    @Query("SELECT * FROM person INNER JOIN address ON address.id = person.id WHERE address.city LIKE :city")
    fun findByCity(city : String) : List<Person>

    //do not have to provide any SQL with @Insert, when conflict just replace so it works also as update
    //return id primary key
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(person : Person) : Long

    @Insert
    fun insertAll(vararg persons : Person)

    //do not have to provide any SQL with @Update or @Delete but it can be also replace by normal @Query
    @Update
    fun update(person : Person)

    @Delete
    fun delete(person : Person)

    //make transaction with atomic operations
    @Transaction
    suspend fun increaseAgeForAll() {
        for (person in getAll()) {
            person.age = person.age + 1
            update(person)
        }
    }
}

@Dao
interface AddressDao {

    @Query("SELECT * FROM address")
    fun getAll() : List<Address>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(address: Address) : Long

    @Delete
    fun delete(address: Address)
}

//serves as the access point, define list of entities associated with database
@Database(entities = [Person::class, Address::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() { //must be abstract and extends RoomDatabase

    //abstract no arg methods with returns of entities
    abstract fun personDao() : PersonDao
    abstract fun addressDao() : AddressDao

    //define more methods for other entities class
}

class RoomActivity : AppCompatActivity(), CoroutineScope by MainScope() {

    lateinit var database : AppDatabase
    lateinit var personDao : PersonDao
    lateinit var addressDao : AddressDao

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        //create or inject instance, it could be Singleton
        //can be created also by inMemoryDatabaseBuilder() which helps testing
        database = Room
            .databaseBuilder(this, AppDatabase::class.java, "AppDatabase")
            .build()
        //there are more builders options like allowMainThreadQueries()

        //access to database by dao
        personDao = database.personDao()
        addressDao = database.addressDao()

        //must be run on the background thread otherwise exception will be thrown
        doSomeOperations()
    }

    fun doSomeOperations() {
        launch(Dispatchers.IO) {
            createData()
            val person = selectData()
            updateData(person)
            deleteData(person)
            runTransaction()
        }
    }

    fun createData() {
        //put some data using dao example methods
        val address = Address("Poznan", "sw. Marcin")
        val contact = Contact("111 222 333", "jack@daniels.com")
        val addressId = addressDao.insert(address)
        personDao.insert(Person("Jack Daniels", 50, addressId, contact))

        val polwiejskaId = addressDao.insert(Address("Poznan", "Polwiejska"))
        val johnnie = Person("Johnnie Walker", 60, polwiejskaId, Contact("222 333 444", "johnnie@walker.com"))
        val william = Person("William Grant", 70, polwiejskaId, Contact("333 444 555", "william@grant.com"))
        personDao.insertAll(johnnie, william)
    }

    fun selectData() : Person {
        //example of getting data usage
        val persons = personDao.getAll()
        val person = personDao.findByName("Jack Daniels")
        return person
    }

    fun updateData(person : Person) {
        person.age = 20
        personDao.update(person)
    }

    fun deleteData(person : Person) {
        personDao.delete(person)
    }

    suspend fun runTransaction() {
        //run transaction by annotated method
        personDao.increaseAgeForAll()

        //or this can be achieve also by transaction directly on database
        database.runInTransaction {
            for (person in personDao.getAll()) {
                person.age = person.age + 1
                personDao.update(person)
            }
        }
    }
}

class MigrationActivity : AppCompatActivity() {

    //address entity is extended by new building number column and database upgraded version

    lateinit var database : AppDatabase

    //provide proper SQL migration
    val MIGRATION_1_2 = object: Migration(1, 2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("ALTER TABLE address ADD COLUMN number TEXT")
            //if new column can not be null inject some default values by execSQL
        }
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        database = Room
            .databaseBuilder(this, AppDatabase::class.java, "RoomDatabase")
            .addMigrations(MIGRATION_1_2) //pass more of them if needed
            .build()
    }
}