search

ufoss-org / kotysa

The idiomatic way to write type-safe SQL in Kotlin
The Unlicense
117 stars 1 forks source link

Real Time and emits changes #125

Open joseluisgs opened 1 year ago

joseluisgs commented 1 year ago

Hi I have a question, Is there a way to generate and return changes in real time? i mean, when I use: CoroutinesSqlCLient returns a kotlinx.coroutines.flow.Flow this flow can emits a new result every time the database changes for that query? or it's just for once?

pull-vert commented 1 year ago

Hi @joseluisgs Current async results of a Query with coroutines Flow<T> or reactor Flux<T> is a way for the async database driver to return the matching query results asynchronously one by one, instead of waiting until the query has finished and then send all the results as a list in a blocking database driver. The answer of your question is : for now it's just for once.

Is there a way to generate and return changes in real time?

This could technically be possible, by merging the current async results (current behavior) and then subscribe to the selected columns that were updated.

I think this would involve too much of efforts for me, as it is a non-standard SQL feature. I suspect that each database provider must have a specific table change tracking model, so I would have to support all these platform specific implementations on all the supported databases : MySQL, PostgreSQL, MariaDB, H2, Oracle, Microsoft SQL Server (see their change tracking implementation [1]). And soon I will also add Google Cloud Spanner !

Also I am not aware of the extra cost for the database, and I am not sure how keeping connections opened for a very long time behaves on Transactional databases, and how much of them a Server can support. This kind of long time queries would have to be sent as Server Sent Events or Websockets.

I leave this idea opened but I do not plan to do it for now. I add the "help wanted" label, so if someone has the motivation to code this feature, a PR would be welcome !

[1] Microsft SQL Server note about Change Tracking :

When the TRACK_COLUMNS_UPDATED option is set to ON, the SQL Server Database Engine stores extra information about which columns were updated to the internal change tracking table. Column tracking can enable an application to synchronize only those columns that were updated. This can improve efficiency and performance. However, because maintaining column tracking information adds some extra storage overhead, this option is set to OFF by default.

joseluisgs commented 1 year ago

Hello. Thank you. Don't worry. It was only a question. It's nothing important and I know perfectly well the work it takes. Thank you very much for your kindness in answering me and for your work.

pull-vert commented 1 year ago

Hello. Thank you. Don't worry. It was only a question. It's nothing important and I know perfectly well the work it takes. Thank you very much for your kindness in answering me and for your work.

Thanks a lot @joseluisgs , it's a pleasure to discuss about new feature ideas from Kotysa users !