Howto perform VACUUM command?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 98
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: 8368
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: 98
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: 8368
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: 98
Joined: Mon 29 Apr 2013 09:05

Re: Howto perform VACUUM command?

Post by bairog » Sat 27 Oct 2018 06:03


bairog
Posts: 98
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: 8368
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.

Post Reply