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/filesTry 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/7614fe8d0ac03fc94ed6be7e61e28d9111a5d482/doc/getting_started.md#creating-a-connection
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