Page 1 of 1

Howto perform VACUUM command?

Posted: Tue 19 Jun 2018 10:06
by bairog
Hello.
How can I perfom VACUUM command using Entity Framework Code-First approach?
Is the following syntax correct:

Code: Select all

((IObjectContextAdapter)myDbContext).ObjectContext.ExecuteStoreCommand("VACUUM");

Re: Howto perform VACUUM command?

Posted: Fri 22 Jun 2018 13:47
by Shalex
It should work.

JIC: viewtopic.php?f=29&t=25596

If you encounter any difficulties, please contact us.

Re: Howto perform VACUUM command?

Posted: Wed 24 Oct 2018 07:23
by bairog
Shalex wrote: Fri 22 Jun 2018 13:47 It should work.
In fact that is NOT working :(.

Code: Select all

((IObjectContextAdapter)myDbContext).ObjectContext.ExecuteStoreCommand("VACUUM");
raises "SQLite error cannot VACUUM from within a transaction" exception. Of course there is no any transaction opened. And it doesn't matter whether I open coonection before or not:

Code: Select all

((IObjectContextAdapter)myDbContext).ObjectContext.Connection.Open();
Why that is not working?

BTW. The only approach that didn't raised an exception for me is:

Code: Select all

using (SQLiteConnection conn = new SQLiteConnection(mySQLiteConnectionStringBuilder.ToString()))
using (SQLiteCommand cmd = conn.CreateCommand())
{
  cmd.CommandText = "VACUUM";
  conn.Open();
  var rowsAffected = cmd.ExecuteNonQuery();
  conn.Close();
}
BUT:
1) I have sample.db (23.6 mb) and sample_big.db (47.3 mb) which contains the same data (second database is a copy of first database where all objects were deleted and added again). After VACUUM command second database becomes 45.7 mb. That is not what I expected? Shouldn't it be ~23.6 mb as the original database was? !NOT RELEVANT FOR NOW!
2) For some reason rowsAffected equals 0 even if I use two cmd.ExecuteNonQuery() one after another - first one slightly shrinks database from 47.3 mb to 45.7 mb, second one leaves it's size unchanged). Shouldn't it be real number of affected rows?

I use dotConnect for SQLite 5.11.1202 and EF6.

I've uploaded sample databases here

UPDATE 1) is not relevant - looks like I'm facing some cascade delete problems (I've described them in this topic).

Re: Howto perform VACUUM command?

Posted: Fri 26 Oct 2018 18:48
by Shalex
bairog wrote: Wed 24 Oct 2018 07:23[/b]2) For some reason rowsAffected equals 0 even if I use two cmd.ExecuteNonQuery() one after another - first one slightly shrinks database from 47.3 mb to 45.7 mb, second one leaves it's size unchanged). Shouldn't it be real number of affected rows?

I use dotConnect for SQLite 5.11.1202 and EF6.

I've uploaded sample databases here
Your download link says "The owner either removed the files or restricted access, or there's a typo in the link.". Could you please reupload a test project to Dropbox?

Re: Howto perform VACUUM command?

Posted: Sat 27 Oct 2018 06:03
by bairog

Re: Howto perform VACUUM command?

Posted: Tue 06 Nov 2018 08:51
by bairog
So, did you obtain my source code?
Did you reproduced the situation?

Re: Howto perform VACUUM command?

Posted: Thu 08 Nov 2018 20:04
by Shalex
We have successfully downloaded your sample databases and are investigating the issue. We will notify you about the result.

Re: Howto perform VACUUM command?

Posted: Fri 23 Nov 2018 18:44
by Shalex
bairog wrote: Wed 24 Oct 2018 07:23

Code: Select all

((IObjectContextAdapter)myDbContext).ObjectContext.ExecuteStoreCommand("VACUUM");
raises "SQLite error cannot VACUUM from within a transaction" exception. Of course there is no any transaction opened. And it doesn't matter whether I open coonection before or not:

Code: Select all

((IObjectContextAdapter)myDbContext).ObjectContext.Connection.Open();
Why that is not working?
1. Please execute

Code: Select all

    var conn = (((IObjectContextAdapter)myDbContext).ObjectContext.Connection as System.Data.Entity.Core.EntityClient.EntityConnection).StoreConnection;
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "VACUUM";
    cmd.ExecuteNonQuery();
instead of

Code: Select all

    (((IObjectContextAdapter)myDbContext).ObjectContext.Connection as System.Data.Entity.Core.EntityClient.EntityConnection).StoreConnection.Open();
    ((IObjectContextAdapter)myDbContext).ObjectContext.ExecuteStoreCommand("VACUUM");
JIC: be aware about the Auto Vacuum connection string parameter, refer to https://www.devart.com/dotconnect/sqlit ... tring.html
bairog wrote: Wed 24 Oct 2018 07:23

Code: Select all

using (SQLiteConnection conn = new SQLiteConnection(mySQLiteConnectionStringBuilder.ToString()))
using (SQLiteCommand cmd = conn.CreateCommand())
{
  cmd.CommandText = "VACUUM";
  conn.Open();
  var rowsAffected = cmd.ExecuteNonQuery();
  conn.Close();
}
[...]
For some reason rowsAffected equals 0 even if I use two cmd.ExecuteNonQuery() one after another - first one slightly shrinks database from 47.3 mb to 45.7 mb, second one leaves it's size unchanged). Shouldn't it be real number of affected rows?
2. The number of rows affected by the command is returned only for UPDATE, INSERT, and DELETE statements. For more information, navigate to https://docs.microsoft.com/en-us/dotnet ... tenonquery.