Xojo2018r4, Mac. In the code below, “me” is an SQLiteDatabase object.
me.SQLExecute( "DROP TABLE "+TableName )
if me.Error then
System.DebugLog “ERROR: Could not drop table”
me.DisplayError( true )
me.SQLExecute( “VACUUM” )
The table does get dropped. The DB file however remains the same size. Checking the file data manually shows the deleted table is still there. Calling VACUUM has absolutely no effect.
I also have this pragma already set in the DB constructor:
me.SQLExecute "PRAGMA auto_vacuum = 1"
The DB file is never vacuumed. The deleted data never goes away.
This is a 64-bit app but I also tested with 32-bit compiling and the result is the same. It doesn’t work.
I’ve missed something, or this is a bug?
for the drop what is the ERROR MESSAGE?
as to VACUUM ,… I don’t believe it is allowed to be inside a Transaction… check the error message there as well
With database operations you can rarely have too many error checks
and to being “same size”… it could be the OS filesystem… not SQLite… VACUUM optimizes the database internally but may not necessarily change the physical file size.
isn’t the vacuum effective only after a certain size of the table you drop ?
From the Docs:
but you should get that in the ErrorMessage as well.
Vacuum doens’t care about the size of the “garbage collection”…
but I don’t think it actually changes the file area used by the OS, meaning you might not “notice” a filesize reduction
How can this be ? Isnt
me be a reserved word ?
My project on my SQLite db file works fine and I have a size (down) change
the original user deleted some Records (probably) with Xojo2014r4, running in the IDE (I cannot test the Standalone).
My code is in a PushButton and Xojo does not like that (
Me = New SQLiteDatabase) at all.
Code moved to a Window Method share the same trouble(s)
“----------------” to a Module share the same trouble(s).
Me is blue.
[quote=426939:@Emile Schwarz]@Aaron Hunt Xojo2018r4, Mac. In the code below, “me” is an SQLiteDatabase object.
How can this be ? Isnt me be a reserved word ?[/quote]
I understand the OP as that the code mentioned is in a subclass of SQLiteDatabase.
Me is the same as
self and referring to the instance if used outside of a control.
It’s a subclassed SQLiteDatabase object.
[quote=426937:@Tobias Bussmann]From the Docs:
but you should get that in the ErrorMessage as well.[/quote]
The problem was indeed that VACUUM can’t be done within a transaction. Although, there was no error code. Calling VACUUM without the transaction works finally.
But the pragma simply seems not to work. Setting it = 1 should vacuum automatically after a table is deleted, but this doesn’t happen, so the pragma appears to have absolutely no effect.
Thank you all for the help.
Autovacuum will only work if one of the vacuum modes was set before creating the table. You can then change between mode 1 and 2 on the fly, but you cannot activate auto_vacuum for a table that was created with the default = off.
EDIT: Only found a German site giving explanations, but maybe a translator app will help a bit:
Thanks, that explains it.
[quote=426954:@Ulrich Bogun]Only found a German site giving explanations, but maybe a translator app will help a bit:
German is fine. I speak German. I live in Germany.
I had also read the SQLite documentation (in English) but I had overlooked the facts stated there that would have answered my questions.