More questions about SQLDelight + iOS from my side...

# kotlin-native

[Arkadii Ivanov]

More questions about SQLDelight + iOS from my side 🙂
1. I am creating a new in-memory database instance for every test case. But on iOS it may contain data from previous test cases. On Android it is always clean. Is it expected behaviour?
2. Looks like last_insert_rowid() does not work on iOS, but works on Android. Is it just not implemented yet?

[basher]

1. In-memory for the native driver I believe uses an on-disk cache to make threads work. Not exactly sure what the workarounds are. We ended up Just creating new ones each time and cleaning up after. If you do the create statements in a transaction during your test, the cost is low.
2. It works, but you need to do it in a transaction to force the call onto the writer thread. last_insert_rowid (along with many other SQLite functions) are connection-specific/sensitive. Android may be using a single connection, which is why it works. There may be configurations for Android that "break" it in a similar way

[basher]

https://sqlite.org/sharedcache.html talks about the caching behavior

[basher]

Haven't tried using "memory" yet. That may do it and may invalidate my claim about threads

[Arkadii Ivanov]

Thanks @basher , the question 2 is solved by using transactions. Thanks! The question 1 is still open. The "memory" database name did not help. So clearing the database in @After for now.

[Arkadii Ivanov]

I see that new instances of the database and its driver are created for every test case, however the "create" callback is executed only once. Which means the database itself is somehow cached.

[basher]

Dang. Not sure then sorry. In our tests, we just don't use in-memory and delete the files after each

[Arkadii Ivanov]

Alright, for now I will clear the database inside @Before or @After

[russhwolf]

I think if you call close() on the driver between tests it will clear the in-memory data

[mbonnin]

https://github.com/touchlab/SQLiter/issues/7

[mbonnin]

Looks like there's an option for in-memory DB in SQLiter but it might not be exposed by SQLDelight (which uses SQLiter under the hood)

[mbonnin]

(Also 2. sounds like a nice lint check to implement 🤓 )

[kpgalligan]

Sorry, been off the slacks today. SQLiter is due for some updates. Sqlite has some odd edge cases, so you have to sort of tread carefully, but will take a look at in memory updates.

[Arkadii Ivanov]

@kpgalligan "so you have to sort of tread carefully" - this sounds like something important, but I can't understand what does this mean 🙂 Could you please elaborate?

[kpgalligan]

I mean with the driver work. The drivers hide the weirdness. For example, I forget the details off hand, but something really bad happens with multiple unnamed memory connections. It's been over a year since I went deep on it. I'd need to jump back in, but these use cases in mind.

[Arkadii Ivanov]

Now it's clear, thanks!

[kpgalligan]

This is what I was thinking of. You can create multiple connections for in memory, but they need a name and a weird file cache thing. Otherwise, I think we need to rewire things to use a single memory connection. I forget exactly what goes wrong otherwise, but I remember it wasn't great. In any case, I'll be poking around the driver: https://github.com/touchlab/SQLiter/blob/512a5b1262391a9f05faeef137611b33a53c6140/SQLiter/src/mingwMain/kotlin/co/touchlab/sqliter/DatabaseFileContext.kt#L14

[louiscad]

Added a solution idea in SQLiter issue #7 for the unnamed in-memory database problem: https://github.com/touchlab/SQLiter/issues/7#issuecomment-737074718