Delete diff tables when session crashes about spine-database-api HOT 14 CLOSED

In GitLab by @manuelma on Nov 16, 2018, 18:24

mentioned in commit c26d539

from spine-database-api.

In GitLab by @manuelma on Nov 16, 2018, 20:55

mentioned in commit toolbox@a29d7e32c21f099556ff9549a9f21d18841bef90

from spine-database-api.

In GitLab by @manuelma on Nov 17, 2018, 00:48

mentioned in commit toolbox@9f81d59a1713e06f3f086986bf2b74c263f1728e

from spine-database-api.

In GitLab by @manuelma on Nov 21, 2018, 12:39

mentioned in commit 61a5a77

from spine-database-api.

In GitLab by @manuelma on Nov 24, 2018, 23:10

mentioned in commit toolbox@e248e16fa4fec232dcd37ee015efcde6b939245a

from spine-database-api.

In GitLab by @manuelma on Mar 25, 2019, 17:42

assigned to @manuelma

from spine-database-api.

In GitLab by @manuelma on Mar 25, 2019, 17:46

@PekkaSavolainen @PvPer @ererkka

The difficulty here is we only delete diff_ tables from the database when the close method is called on the DiffDatabaseMapping object. It works well as long as one remembers and doesn't make Python crash. It's awful when developing.

Do you know if it's possible to manage this better? I've looked at Python destructors and it doesn't seem to be the answer here. People also recommend the context manager approach.

Help?

from spine-database-api.

In GitLab by @PvPer on Mar 26, 2019, 09:07

I think the whole diff thing is a bit hacky to begin with, we are essentially rewriting sessions for sqlite.

There is a plugin, https://sqlite.org/sessionintro.html, probably not straightforward to use in python.

Use the sqlalchemy session more. It does some session work, but not sure if it can do all we want.

Use a in memory database or temporary file and then attach that database to the original via the attach command: https://sqlite.org/lang_attach.html

Use temp tables instead: https://www.sqlite.org/tempfiles.html#temp_databases, this seems to be what we want, creates a temporary extra file that is removed when the session is closed, not sure what happens if the session crashes but we should try this one out.

from spine-database-api.

In GitLab by @PekkaSavolainen on Mar 26, 2019, 10:56

changed title from Delete diff tables when session cr{-u-}shes to Delete diff tables when session cr{+a+}shes

from spine-database-api.

In GitLab by @manuelma on Mar 26, 2019, 21:10

mentioned in commit bb52556

from spine-database-api.

In GitLab by @manuelma on Mar 26, 2019, 21:13

The temp tables approach seems to work, so I just went with that. Please let me know if I broke something.

(The trick to make it work is to use the same Connection object both to create the temp tables, and the Session. Otherwise, the Session is unable to see the temp tables.)

In the future we may come up with a less hacky approach, but for now this seems to work just fine (apart from this detail, which I hope is now solved).

Thanks for the input @PvPer

from spine-database-api.

In GitLab by @manuelma on Mar 29, 2019, 20:33

mentioned in commit d08b25a

from spine-database-api.

In GitLab by @manuelma on Mar 29, 2019, 20:33

mentioned in commit toolbox@ab9ccc795270398ead5a62098ef8cf88545cbf55

from spine-database-api.

In GitLab by @manuelma on May 30, 2019, 19:04

This is handled for sqlite backend, remains to check other backends.

from spine-database-api.