Android room database: get autogenerated id at time of assignment

Question

I have an android database with an entity something like

id (autogenerated)	string	filename
0	cow	myFolder/0.mp3
3	sheep	myFolder/3.mp3

In other words, I want to use the autogenerated id to be included in a filename once the entity has been assigned. So I would like to do calls like

data.insert("cow")

and have it automatically assign an id and a filename of the form `"myfolder/"+[id]+".mp3"

If I naively attempt to do this in the entity main method, with this.filename = "myfolder/"+this.id+".mp3" then, since the id has not been set yet, this.id returns its null value (0).

Is it possible to do this?

Answer 1

You can change the type of id to String and every time you insert a new item you generate a new UUID and set that UUID as id and filename, like that:

suspend fun insertItem(string: String) {
    val uuid = UUID.randomUUID().toString()
    val item = Item(
        id = uuid,
        string = string,
        fileName = "myFolder/$uuid.mp3"
    )
    dataDao.insert(item)
}

You can even pass that uuid as param to the insertItem function if you want to use it outside to generate a file or something:

suspend fun insertItem(string: String, uuid: String) {
    val item = Item(
        id = uuid,
        string = string,
        fileName = "myFolder/$uuid.mp3"
    )
    dataDao.insert(item)
}

Answer 2

If you have the @Entity as (for example):-

@Entity(indices = [Index(value = ["string"], unique = true)])
data class Item(
    @PrimaryKey
    var id: Long?=null,
    var string: String,
    var filename: String = "unresolved"
) {
    fun getIdFromFilename(folder: String = "MyFolder", extension: String = "mp3" ): String {
        return this.filename.replace("${folder}${File.separator}","").replace(".${extension}","")
    }
} 

• As you can see the string column has a unique index so for example you could not have cow twice (index can be removed if you want rows for similar values)
• Note that autoGenerate = true has not been used instead just the @PrimaryKey annotation with a nullable integer type, this works in the same way as if coding autoGenerate other than that in theory the generated value can be less should a massive number of rows be inserted. See https://sqlite.org/autoinc.html for more noting the very first line.
• id is Long?, (from the link above, in theory id can exceed the highest value that an Int can be, so Long rather than Int is really correct)
• the getIdFromFilename is a bonus that may or may not be useful, but has been used in the demo below

With the following @Dao annotated interface as :-

@Dao
interface AllDao{

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(item: Item): Long
    @Update(onConflict = OnConflictStrategy.IGNORE)
    fun update(item: Item): Int

    @Transaction
    @Query("")
    fun insertItemResolvingFileName(item: Item, folder: String = "MyFolder", extension: String = "mp3"): Long {
        val id = insert(item)
        if (id > -1) {
            update(Item(id,item.string,"${folder}${File.separator}${id}.${extension}"))
        }
        return id
    }

    @Query("SELECT * FROM `item`")
    fun getAllItems(): List<Item>
}

• @Transaction used as there are multiple accesses
• @Query("") fools Room into thinking there is a query and thus allows a transaction to be used

Then you would typically use the insertItemResolvingFileName function to insert, which inserts the Item and then updates it changing the filename according to the id. The function has the flexibility to specify both the folder and the extension

Demo

Putting this into action (note on the Main Thread for convenience and brevity) using :-

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

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        dao.insertItemResolvingFileName(Item(string = "cow"))
        dao.insertItemResolvingFileName(Item(string = "sheep"))
        dao.insertItemResolvingFileName(Item(string = "elephant"))
        dao.insertItemResolvingFileName(Item(string = "elephant")) /* Ignored */

        dao.insertItemResolvingFileName(Item(id =9999, string = "tortoise")) /* Controlled ID */

        /* inadvertent duplicate controlled id */
        val newItem = Item(id = 9999,"hedgehog")
        if (dao.insertItemResolvingFileName(newItem) < 0) {
            dao.insertItemResolvingFileName(Item(null,newItem.string,newItem.filename))
        }

        for (item in dao.getAllItems()) {
            Log.d("ITEMINFO","Item string is ${item.string} FileName is ${item.filename} id is ${item.id} idFromFileName is ${item.getIdFromFilename()}")
        }
    }
}

Running the above results in the Log including :-

D/ITEMINFO: Item string is cow FileName is MyFolder/1.mp3 id is 1 idFromFileName is 1
D/ITEMINFO: Item string is sheep FileName is MyFolder/2.mp3 id is 2 idFromFileName is 2
D/ITEMINFO: Item string is elephant FileName is MyFolder/3.mp3 id is 3 idFromFileName is 3
D/ITEMINFO: Item string is tortoise FileName is MyFolder/9999.mp3 id is 9999 idFromFileName is 9999
D/ITEMINFO: Item string is hedgehog FileName is MyFolder/10000.mp3 id is 10000 idFromFileName is 10000

• noting that the second attempt to insert elephant has been ignored due to the unique index.

Although not used above the id is returned from the insertItemResolvingFileName* method (it will be -1 if the insert was ignored).

Using App Inspection then :-

You may notice that tortoise and hedgehog have been inserted a little differently.

• tortoise has been inserted using a specific id (controlled)

• hedgehog similarly BUT using the same id as tortoise, which would not be allowed as it is a duplicate, so it has been handled to then autogenerate the id.

• in comparison to using a UUID, in regards to the comment I always avoid using auto increment integer as id, I prefer using uuid string it's safer and I can control it, even if I get a data from api I can save it to room database without any problem and have the same id in room and api. :-

  • AUTOINCREMENT can, and in most cases should, be avoided (see link above)

  • Less space is taken up to store the value (at most 8 bytes but as little as 1 byte)

  • Less space is taken up as, at least for Room at present, all tables are tables that have the rowid (Room doesn't support WITHOUT ROWID tables), so it exists and uses space even if alternatives such as UUID are used.

  • There is no safety issue in comparison to UUID SQLite will generate a unique id before storage itself becomes an issue (unless using AUTOINCREMENT aka autoGenerate = true)

    • If id/UUID is coming from an API then there is no safety issue other than any introduced by the API.

  • Use of an alias of the rowid can be up to twice as fast; SQLite is designed to favour it's use.

An alternative approach could be to utilise a TRIGGER, this would probably be more efficient. However, Room doesn't cater that well for TRIGGERs and you would need to use/override the onOpen and or the onCreate methods via a CallBack. Their use can also cause confusion as their existence isn't that evident.

Here's a demo of a TRIGGER that could be used, but not it's implementation in Room :-

DROP TABLE IF EXISTS `Item`;
CREATE TABLE IF NOT EXISTS `Item` (`id` INTEGER, `string` TEXT NOT NULL, `filename` TEXT NOT NULL, PRIMARY KEY(`id`));
DROP TRIGGER IF EXISTS generate_filename;

/* The TRIGGER */
CREATE TRIGGER IF NOT EXISTS generate_filename AFTER INSERT ON `Item` BEGIN
    UPDATE `Item` SET `filename` = 'MyFolder/'||new.id||'.mp3' WHERE id = new.id;
END;

/* TEST */
INSERT INTO `Item` (`string`,`filename`) VALUES ('cow',''),('sheep',''),('elephant','');
SELECT * FROM `Item`;


DROP TABLE IF EXISTS `Item`; /* Cleanup Environment *.

The result, as per the SELECT, being:-

Yet another way could be to utilise a CTE (common table expression aka a temporary table). This extracts the highest existing id, adds 1 and then inserts the new row with the filename as it should be.

This is simply a single query BUT the drawback is that it does not use the convenience @Insert but rather an @Query (no need for @Transaction as it is a single transaction) therefore the id is not returned and therefore if the duplicate string is passed then it is ignored without any indication that it has been ignored.

The @Dao function is :-

@Query("WITH cte(id) AS (SELECT COALESCE(max(id),0)+1 FROM `Item`) INSERT OR IGNORE INTO `Item` SELECT id,:string,'MyFolder/'||id||'.mp3' FROM cte;")
fun insert(string: String)

• could easily be modified to pass folder and extension if desired.

Changing the Activity code to use (insert using this method before any other rows and also after all other rows):-

    dao.insert("Panda") /* Test with no existing data*/
    dao.insertItemResolvingFileName(Item(string = "cow"))
    dao.insertItemResolvingFileName(Item(string = "sheep"))
    dao.insertItemResolvingFileName(Item(string = "elephant"))
    dao.insertItemResolvingFileName(Item(string = "elephant")) /* Ignored */
    dao.insertItemResolvingFileName(Item(id =9999, string = "tortoise")) /* Controlled ID */

    /* inadvertent duplicate controlled id */
    val newItem = Item(id = 9999,"hedgehog")
    if (dao.insertItemResolvingFileName(newItem) < 0) {
        dao.insertItemResolvingFileName(Item(null,newItem.string,newItem.filename))
    }
    dao.insert("kangaroo")

And the result is then :-

D/ITEMINFO: Item string is Panda FileName is MyFolder/1.mp3 id is 1 idFromFileName is 1
D/ITEMINFO: Item string is cow FileName is MyFolder/2.mp3 id is 2 idFromFileName is 2
D/ITEMINFO: Item string is sheep FileName is MyFolder/3.mp3 id is 3 idFromFileName is 3
D/ITEMINFO: Item string is elephant FileName is MyFolder/4.mp3 id is 4 idFromFileName is 4
D/ITEMINFO: Item string is tortoise FileName is MyFolder/9999.mp3 id is 9999 idFromFileName is 9999
D/ITEMINFO: Item string is hedgehog FileName is MyFolder/10000.mp3 id is 10000 idFromFileName is 10000
D/ITEMINFO: Item string is kangaroo FileName is MyFolder/10001.mp3 id is 10001 idFromFileName is 10001

Further

Another option is to not even store the filename as just with the id (which can be obtained when inserting) or the string (if unique).

As an example perhaps consider:-

@Query("SELECT " +
        "CASE " +
        "WHEN id IS NOT NULL THEN :theFolder||id||:theExtension " +
        "ELSE '' " +
        "END " +
        "AS whatever " +
        "FROM `Item` " +
        "WHERE id=:id " +
        "OR string LIKE :startWildCharacter||:searchString||:endWildCharacter " +
        "LIMIT  1;"
)
/* if (if....then....else is CASE WHEN THEN ELSE END in SQLite SQL) no id then return empty string */
/* AS whatever gives the result a meaningful (sort of) column name but note that as only the single result, then the column name does not matter */
fun getFilePathFlexible(
    /* default of -12345 will not be found (although could be if using negative id's (beyond the scope of the question)) */
    /* alternately could be null */
    id: Long = -12345,
    /* search within the string column (see combined with wild characters if given) */
    /* default is unlike to find anything */
    searchString: String = "not_by_string",
    /* the folder, defaults to MyFolder but can be specified */
    theFolder: String = "MyFolder",
    /* the extension, defaults to .mp3 but can be specified */
    theExtension: String = ".mp3",
    /* the wild character or a string that prefixes the search String (intended for wild character % or _), defaults to nothing */
    /* similar for suffix of the search String */
    /* see https://sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators */
    startWildCharacter: String = "",
    endWildCharacter: String = ""
): String

• perhaps a little overboard but plenty of potential considerations
• hopefully comments say enough

Using (following on from the above) then with:-

    val TAG = "FILENAME INFO"
    Log.d(TAG,"Result is ${dao.getFilePathFlexible()}") /* OOOPS on purpose (i.e. all defaults should get )*/
    Log.d(TAG,"Result is ${dao.getFilePathFlexible(searchString = "kangaroo")}")
    Log.d(TAG,"Result is ${dao.getFilePathFlexible(id = 1000)}") /* you'd know this anyway but could be used to validate the id exists (null if not)*/
    Log.d(TAG,"Result is ${dao.getFilePathFlexible(id = 10000)}") /* you'd know this anyway but could be used to validate the id exists */
    Log.d(TAG,"Result is ${dao.getFilePathFlexible(searchString = "o", startWildCharacter = "_", endWildCharacter = "_")}") /* how or now or cow or ton or low .... */
    Log.d(TAG,"Result is ${dao.getFilePathFlexible(searchString = "ph", startWildCharacter = "ele", endWildCharacter = "ant", theFolder = "notMyFolder", theExtension = ".3mp")}")

Results in :-

D/FILENAME INFO: Result is null
D/FILENAME INFO: Result is MyFolder10001.mp3
D/FILENAME INFO: Result is null
D/FILENAME INFO: Result is MyFolder10000.mp3
D/FILENAME INFO: Result is MyFolder2.mp3
D/FILENAME INFO: Result is notMyFolder4.3mp

i.e. results as anticipated

1. search for nothing = get nothing
2. search for kangaroo = id 10001
3. search for non existent id 1000 id (skipped id's when inserting 9999) = get nothing
4. search for o found cow aka id 2 (underscore = single wild character)
5. search for ele + ph + ant (elephant) found id 10001

Answer 3

Well I can do this in the following way:

In the repository on the insert method (using a thread) we write

dataDao.insert(item)
dataDao.updateFilename()

where in the dao I have something like:

    @Query("UPDATE myTable SET filename = ('myfolder/' || id || '.mp3')")
    void updateFilename();

Of course, this means when we use a viewmodel to say myRepository.insert(item), we can include any filename in item - it will be overwritten in the corresponding repository method.

Obviously, whilst this will be fine for my small scale project, a better way to proceed is to introduce a separate method in the repository like void InsertWithAutomaticFilename or something which will be more transparent.

Perhaps somebody could agree or disagree with this way of proceeding? Or maybe there is a better way within the entity or dao itself?