Pharo-SQLite3: closing connections immediately and deleting the database file
Hi I like to develop my db schema as I develop the code - TDD-style. So I need to keep dropping the database each and every time I need to update the schema and rerun the code. The problem is that even though I close the connection using #ensure: the reference remains and I cannot delete the database file immediately. ``` buildSchema ``` ``` | fp | ``` ``` ``` ``` (fp := self dbFilePath asFileReference) exists ``` ``` ifTrue: [ fp delete ]. ``` ``` ``` ``` [ ``` ``` self db beginTransaction. ``` ``` self writeSchema; writeData. ``` ``` self db commitTransaction ] ensure: [ self cleanUp ] ``` I even tried running this before everything else to no avail: ``` SQLite3Connection allInstancesDo: [ :each | each close ]. ``` ``` SmalltalkImage cleanUp. ``` ``` SmalltalkImage current garbageCollect. ``` I keep getting a #CannotDeleteFileException because there is still a reference to the database file. If I do wait a seemingly random amount of time, the reference is cleared. How can I get the VM to release the reference to the database file immediately? Pharo 8/64-bit, Windows 10 Regards, Vince
Hi Looking at SQLite3Connection>>#close: "Let FFIExternalResourceManager take care." "dbHandle ifNotNil: \[ library close: dbHandle \]." dbHandle := nil. isOpen := false. The commented out lines means that SQLite3Library>>#apiClose is not called when calling SQLite3Connection>>#close. But looking at https://www.sqlite.org/c3ref/close.html and FFIExternalResourceManager it seems like it should take care of releasing resources (i.e., finalizing prepared queries etc) before calling SQLite3Library>>#apiClose. So this is by design? I will have a go at finalizing everything and call SQLite3Library>>#apiClose, at least while developing. Vince
Hi Actually I will just do a bunch of DROP TABLE IF EXISTS instead of dropping the db altogether. Thanks, Vince
I've been doing exactly that. It is, creating a new DB file for each test, and I was having the same problem as you. I suggested a change to the driver to cover that situation: https://github.com/pharo-rdbms/Pharo-SQLite3/pull/22/files Try applying that change and see if that works. I still prefer to recreate the database, it will give you a full fresh start, and if you want you can halt before the deletion of the db, to analyze its internal state. Best regards! Esteban A. Maringolo On Sat, Aug 7, 2021 at 11:13 PM <vinref@gmail.com> wrote:
Hi
Actually I will just do a bunch of
DROP TABLE IF EXISTS
instead of dropping the db altogether.
Thanks, Vince
Not exactly what you are asking for, but did you consider using sqlite in memory? On Sat, 7 Aug 2021 at 23:23, Esteban Maringolo <emaringolo@gmail.com> wrote:
I've been doing exactly that. It is, creating a new DB file for each test, and I was having the same problem as you.
I suggested a change to the driver to cover that situation: https://github.com/pharo-rdbms/Pharo-SQLite3/pull/22/files
Try applying that change and see if that works.
I still prefer to recreate the database, it will give you a full fresh start, and if you want you can halt before the deletion of the db, to analyze its internal state.
Best regards!
Esteban A. Maringolo
On Sat, Aug 7, 2021 at 11:13 PM <vinref@gmail.com> wrote:
Hi
Actually I will just do a bunch of
DROP TABLE IF EXISTS
instead of dropping the db altogether.
Thanks, Vince
After I set up the tests I knew that was possible, but never knew how to actually do it. Regards! Esteban A. Maringolo On Thu, Aug 12, 2021 at 8:55 PM Julián Maestri <serpi90@gmail.com> wrote:
Not exactly what you are asking for, but did you consider using sqlite in memory?
On Sat, 7 Aug 2021 at 23:23, Esteban Maringolo <emaringolo@gmail.com> wrote:
I've been doing exactly that. It is, creating a new DB file for each test, and I was having the same problem as you.
I suggested a change to the driver to cover that situation: https://github.com/pharo-rdbms/Pharo-SQLite3/pull/22/files
Try applying that change and see if that works.
I still prefer to recreate the database, it will give you a full fresh start, and if you want you can halt before the deletion of the db, to analyze its internal state.
Best regards!
Esteban A. Maringolo
On Sat, Aug 7, 2021 at 11:13 PM <vinref@gmail.com> wrote:
Hi
Actually I will just do a bunch of
DROP TABLE IF EXISTS
instead of dropping the db altogether.
Thanks, Vince
As far as I know you need to tell it to open a file called :memory: and in Pharo-SQLite3 you should be able to use: SQLite3Connection memory I've never done this before in Pharo, but it's worth trying, it should be faster and easier to set up. https://www.sqlite.org/inmemorydb.html https://github.com/pharo-rdbms/Pharo-SQLite3/blob/7614fe8d0ac03fc94ed6be7e61... On Fri, 13 Aug 2021 at 00:26, Esteban Maringolo <emaringolo@gmail.com> wrote:
After I set up the tests I knew that was possible, but never knew how to actually do it.
Regards!
Esteban A. Maringolo
On Thu, Aug 12, 2021 at 8:55 PM Julián Maestri <serpi90@gmail.com> wrote:
Not exactly what you are asking for, but did you consider using sqlite
in memory?
On Sat, 7 Aug 2021 at 23:23, Esteban Maringolo <emaringolo@gmail.com>
wrote:
I've been doing exactly that. It is, creating a new DB file for each test, and I was having the same problem as you.
I suggested a change to the driver to cover that situation: https://github.com/pharo-rdbms/Pharo-SQLite3/pull/22/files
Try applying that change and see if that works.
I still prefer to recreate the database, it will give you a full fresh start, and if you want you can halt before the deletion of the db, to analyze its internal state.
Best regards!
Esteban A. Maringolo
On Sat, Aug 7, 2021 at 11:13 PM <vinref@gmail.com> wrote:
Hi
Actually I will just do a bunch of
DROP TABLE IF EXISTS
instead of dropping the db altogether.
Thanks, Vince
Hi I double-check changes to the db by running the SQLite3 client in a terminal. The memory db is only accessible by the same client/session so it wonât work. I also run multiple processes doing different things in the same image so each process needs its own connection/handle to SQLite3 so that wont work either. I am using DROP TABLE IF NOT EXISTS to drop all tables before the main schema and it works perfectly. But I did think of using a SQLite3 memory db to hold session info - i.e, cookies. Or I could just use a Dictionary. I havenât implemented this yet but does anyone have any thoughts? Vince
participants (3)
-
Esteban Maringolo -
Julián Maestri -
vinref＠gmail.com