Howto perform VACUUM command?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Howto perform VACUUM command?

Post by bairog » Tue 19 Jun 2018 10:06

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");

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Howto perform VACUUM command?

Post by Shalex » Fri 22 Jun 2018 13:47

It should work.

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

If you encounter any difficulties, please contact us.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: Howto perform VACUUM command?

Post by bairog » Wed 24 Oct 2018 07:23

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).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Howto perform VACUUM command?

Post by Shalex » Fri 26 Oct 2018 18:48

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?

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: Howto perform VACUUM command?

Post by bairog » Sat 27 Oct 2018 06:03


bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: Howto perform VACUUM command?

Post by bairog » Tue 06 Nov 2018 08:51

So, did you obtain my source code?
Did you reproduced the situation?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Howto perform VACUUM command?

Post by Shalex » Thu 08 Nov 2018 20:04

We have successfully downloaded your sample databases and are investigating the issue. We will notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Howto perform VACUUM command?

Post by Shalex » Fri 23 Nov 2018 18:44

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.

Post Reply