538493 - Provide Capability to Compress All SQLite Databases

Status: NEW

(SeaMonkey :: General, enhancement)

Description

[David E. Ross]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.6) Gecko/20091206 SeaMonkey/2.0.1
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.6) Gecko/20091206 SeaMonkey/2.0.1

It was reported in bug #515538 that places.sqlite can grow without bound.  This is a result of the fact that, as entries are deleted from an SQLite database, their spaces in the database remain empty and unused.  

The fix for bug #515538 will address only places.sqlite.  However, other SQLite databases may also be afflicted.  For disc space, this is analogous to a memory leak.  

Either there should be a user interface to compress all .sqlite files in the current profile, or else this should be automatic each time a profile is launched.  

Reproducible: Always




Between the SeaMonkey browser and Thunderbird (which is substantially replicated in SeaMonkey), the following .sqlite files have been seen:  
cookies.sqlite
downloads.sqlite
formhistory.sqlite
permissions.sqlite
places.sqlite
signons.sqlite
urlbarhistory.sqlite
webappsstore.sqlite
index.sqlite
global-messages-db.sqlite
urlclassifier2.sqlite

Since bug #515538 only addresses places.sqlite and none of the others listed here (per <https://bugzilla.mozilla.org/show_bug.cgi?id=515538#c20>) this is NOT a duplicate of that bug report.

Comment 1

[dickvl]

Can the Product be changed to "All"?
Thunderbird have already been mentioned and I would like to see this in Firefox as well.

Another argument to have this feature is for creating a backup of files in your profile folder.
In such cases you want the files to be as small as possible and don't want to backup files with empty space.

This code in the Tools > Error Console works to Vacuum places.sqlite:
const Cc=Components.classes,Ci=Components.interfaces;
Cc["@mozilla.org/browser/nav-history-service;1"].
    getService(Ci.nsPIPlacesDatabase).
    DBConnection.executeSimpleSQL("VACUUM");

After some testing I've found that in Firefox 3.6 I can vacuum downloads.sqlite and formhistory.sqlite with this code:

const Cc=Components.classes,Ci=Components.interfaces;
Cc["@mozilla.org/download-manager;1"] .
    getService(Ci.nsIDownloadManager).
    DBConnection.executeSimpleSQL("VACUUM");

Cc["@mozilla.org/satchel/form-history;1"].
    getService(Ci.nsIFormHistory2).
    DBConnection.executeSimpleSQL("VACUUM");

I'm interested in a command to vacuum signons.sqlite and cookies.sqlite.
I've tried this for signons.sqlite, but that doesn't work.

const Cc=Components.classes,Ci=Components.interfaces;
Cc["@mozilla.org/login-manager/storage/mozStorage;1"].
   getService(Ci.nsILoginManagerStorage).
   DBConnection.executeSimpleSQL("VACUUM");

Is there a command to send a vacuum command to signons.sqlite?

Comment 2

[David E. Ross]

Per comment #1, this may belong in Core:SQL.  Since I'm not sure, however, I will leave it to a Mozilla developer to make that change.

Comment 3

[David E. Ross]

This is especially important for places.sqlite, which is now over 10 MB in my SeaMonkey 2.1RC1 installation.  All the other .sqlite databases COMBINED total less than 1.7 MB.