Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
SQLite vacuuming
27 views
Skip to first unread message
Lewis Rosenthal
Jun 1, 2012, 10:00:52 AM6/1/12
to
Greetings, all...
My databases have been getting kind of crufty of late. Looking for an
efficient way to vacuum the databases, I thought I'd pass along this tip
(and a question).
Instead of using a GUI app which allows for the maintenance of one file
at a time, the SQLite library (available - dare I say it - via yum from
Netlabs...not sure whether there's a WPI for it) can be called (assuming
it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
closed or at least with that profile inactive!) like so:
for %a in (*.sqlite) do sqlite3.exe %a vacuum;
(yes, the ";" is required)
(This is based on
http://www.commandlinefu.com/commands/view/4605/defragment-sqlite-databases-used-by-firefoxwin32-and-other-software.)
now, my question:
I was able to successfully vacuum 14 of 16 files. Two could not be
accessed (and there seemed to be no reason, as I used this on an
inactive profile):
places.sqlite
cookies.sqlite
Thoughts on that one?
(Steve: You might wan to add the above command line routine to your tips
page, which was what sent me off looking for a better mousetrap in the
first place. ;-)
Cheers/2
--
Lewis
-------------------------------------------------------------
Lewis G Rosenthal, CNA, CLP, CLE, CWTS
Rosenthal & Rosenthal, LLC www.2rosenthals.com
Need a managed Wi-Fi hotspot? www.hautspot.com
visit my IT blog www.2rosenthals.net/wordpress
-------------------------------------------------------------
My databases have been getting kind of crufty of late. Looking for an
efficient way to vacuum the databases, I thought I'd pass along this tip
(and a question).
Instead of using a GUI app which allows for the maintenance of one file
at a time, the SQLite library (available - dare I say it - via yum from
Netlabs...not sure whether there's a WPI for it) can be called (assuming
it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
closed or at least with that profile inactive!) like so:
for %a in (*.sqlite) do sqlite3.exe %a vacuum;
(yes, the ";" is required)
(This is based on
http://www.commandlinefu.com/commands/view/4605/defragment-sqlite-databases-used-by-firefoxwin32-and-other-software.)
now, my question:
I was able to successfully vacuum 14 of 16 files. Two could not be
accessed (and there seemed to be no reason, as I used this on an
inactive profile):
places.sqlite
cookies.sqlite
Thoughts on that one?
(Steve: You might wan to add the above command line routine to your tips
page, which was what sent me off looking for a better mousetrap in the
first place. ;-)
Cheers/2
--
Lewis
-------------------------------------------------------------
Lewis G Rosenthal, CNA, CLP, CLE, CWTS
Rosenthal & Rosenthal, LLC www.2rosenthals.com
Need a managed Wi-Fi hotspot? www.hautspot.com
visit my IT blog www.2rosenthals.net/wordpress
-------------------------------------------------------------
Dave Yeo
Jun 1, 2012, 10:57:04 AM6/1/12
to
Lewis Rosenthal wrote:
> Greetings, all...
>
> My databases have been getting kind of crufty of late. Looking for an
> efficient way to vacuum the databases, I thought I'd pass along this tip
> (and a question).
>
> Instead of using a GUI app which allows for the maintenance of one file
> at a time, the SQLite library (available - dare I say it - via yum from
> Netlabs...not sure whether there's a WPI for it) can be called (assuming
> it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
> closed or at least with that profile inactive!) like so:
>
> for %a in (*.sqlite) do sqlite3.exe %a vacuum;
>
> (yes, the ";" is required)
>
> (This is based on
> http://www.commandlinefu.com/commands/view/4605/defragment-sqlite-databases-used-by-firefoxwin32-and-other-software.)
>
> now, my question:
>
> I was able to successfully vacuum 14 of 16 files. Two could not be
> accessed (and there seemed to be no reason, as I used this on an
> inactive profile):
>
> places.sqlite
> cookies.sqlite
>
> Thoughts on that one?
The RPM build doesn't support WAL journalling? I'll try downloading the
> Greetings, all...
>
> My databases have been getting kind of crufty of late. Looking for an
> efficient way to vacuum the databases, I thought I'd pass along this tip
> (and a question).
>
> Instead of using a GUI app which allows for the maintenance of one file
> at a time, the SQLite library (available - dare I say it - via yum from
> Netlabs...not sure whether there's a WPI for it) can be called (assuming
> it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
> closed or at least with that profile inactive!) like so:
>
> for %a in (*.sqlite) do sqlite3.exe %a vacuum;
>
> (yes, the ";" is required)
>
> (This is based on
> http://www.commandlinefu.com/commands/view/4605/defragment-sqlite-databases-used-by-firefoxwin32-and-other-software.)
>
> now, my question:
>
> I was able to successfully vacuum 14 of 16 files. Two could not be
> accessed (and there seemed to be no reason, as I used this on an
> inactive profile):
>
> places.sqlite
> cookies.sqlite
>
> Thoughts on that one?
latest sqlite3 and testing, I'm due to make sure no OS/2 regressions
have snuck in anyways
Dave
Steve Wendt
Jun 1, 2012, 1:37:31 PM6/1/12
to
On 06/01/12 07:00 am, Lewis Rosenthal wrote:
> Instead of using a GUI app which allows for the maintenance of one file
> at a time
I haven't looked at it personally, but doesn't this tool handle all of them?
> Instead of using a GUI app which allows for the maintenance of one file
> at a time
http://hobbes.nmsu.edu/h-search.php?key=upssv
Lewis Rosenthal
Jun 1, 2012, 1:57:10 PM6/1/12
to
On 06/01/12 10:57 am, Dave Yeo thus wrote :
That would explain it, wouldn't it. But then, why only those *two*
databases? Are places & cookies the only two which would use WAL? I
thought they all did.
Thanks for the follow-up, Dave.
BTW, for everyone else, a manual compacting of places.sqlite reduced the
file from 10MB to 2MB (mine wasn't all that bad in the first place; I've
heard of places.sqlite growing in excess of 50MB). I used Ruediger's Qt3
build of SQLite Browser 1.3h to compact those last two files. I could
have dumped the contents and then recreated the databases from a prompt,
as well.
databases? Are places & cookies the only two which would use WAL? I
thought they all did.
Thanks for the follow-up, Dave.
BTW, for everyone else, a manual compacting of places.sqlite reduced the
file from 10MB to 2MB (mine wasn't all that bad in the first place; I've
heard of places.sqlite growing in excess of 50MB). I used Ruediger's Qt3
build of SQLite Browser 1.3h to compact those last two files. I could
have dumped the contents and then recreated the databases from a prompt,
as well.
Lewis Rosenthal
Jun 1, 2012, 2:05:24 PM6/1/12
to
On 06/01/12 01:37 pm, Steve Wendt thus wrote :
Indeed, the description on Hobbes says:
"UPSSV offers the user a batch function of reorganisation (SQL vaccum)
all of the database of the user profile at downtime of the browser at
his own convenience."
I guess i could give that one a go. I misread the statement above it,
when mentioned that FF was supposed to compact the databases during idle
time. Thanks for re-pointing me to it.
Frankly, I wonder why these databases aren't created with auto-vacuum
enabled. I should test using pragma to set this on the existing
databases to see if it makes a difference. My guess is that startup
could take considerably longer for large files to auto-vacuum. An
extension to provide a toggle for this would be nice (I know; it's FOSS,
which means that if I think it's a good idea, then I should do something
about it!). :-)
"UPSSV offers the user a batch function of reorganisation (SQL vaccum)
all of the database of the user profile at downtime of the browser at
his own convenience."
I guess i could give that one a go. I misread the statement above it,
when mentioned that FF was supposed to compact the databases during idle
time. Thanks for re-pointing me to it.
Frankly, I wonder why these databases aren't created with auto-vacuum
enabled. I should test using pragma to set this on the existing
databases to see if it makes a difference. My guess is that startup
could take considerably longer for large files to auto-vacuum. An
extension to provide a toggle for this would be nice (I know; it's FOSS,
which means that if I think it's a good idea, then I should do something
about it!). :-)
Steve Wendt
Jun 1, 2012, 4:40:27 PM6/1/12
to
On 06/01/12 11:05 am, Lewis Rosenthal wrote:
> Frankly, I wonder why these databases aren't created with auto-vacuum
> enabled. I should test using pragma to set this on the existing
> databases to see if it makes a difference. My guess is that startup
> could take considerably longer for large files to auto-vacuum.
I think that was one of the arguments, but poking around Bugzilla seems
> Frankly, I wonder why these databases aren't created with auto-vacuum
> enabled. I should test using pragma to set this on the existing
> databases to see if it makes a difference. My guess is that startup
> could take considerably longer for large files to auto-vacuum.
to suggest it's supposed to happen during idle time:
https://bugzilla.mozilla.org/show_bug.cgi?id=541373
https://bugzilla.mozilla.org/show_bug.cgi?id=595139
Looking in my profile, all of the files only add up to 6MB;
places.sqlite is the largest, at 1.7MB.
On OS/2, idle time can only be determined when Doodle's Screen Saver is
installed. Perhaps some of you with large *.sqlite files do not have
that installed? From readme.os2:
When Doodle's Screen Saver (DSSaver) v1.8 or later is installed,
the app can determine how long the user has been inactive. This
allows it to suspend reloads of "Live Bookmarks" and perform cleanup
operations during idle periods rather than while you're browsing.
Lewis Rosenthal
Jun 1, 2012, 4:59:35 PM6/1/12
to
On 06/01/12 04:40 pm, Steve Wendt thus wrote :
Interesting.
I *do* have DDS installed, and last night, for example, I left SM
running all night. Rainer alluded to the idle time vacuuming in the text
file and description of UPSSV, though I don't think I've really seen it
happen. I should check my other profiles, though. Perhaps there's a pref
which I don't have set. And frankly, it's time I migrated the data from
this profile, as it has collected a considerable amount of junk over the
years. I've just been putting it off.
Thanks for the links. I'll read up on them ASAP.
Cheers/2
I *do* have DDS installed, and last night, for example, I left SM
running all night. Rainer alluded to the idle time vacuuming in the text
file and description of UPSSV, though I don't think I've really seen it
happen. I should check my other profiles, though. Perhaps there's a pref
which I don't have set. And frankly, it's time I migrated the data from
this profile, as it has collected a considerable amount of junk over the
years. I've just been putting it off.
Thanks for the links. I'll read up on them ASAP.
Cheers/2
Steve Wendt
Jun 1, 2012, 5:03:02 PM6/1/12
to
On 06/01/12 01:59 pm, Lewis Rosenthal wrote:
>> Looking in my profile, all of the files only add up to 6MB;
>> places.sqlite is the largest, at 1.7MB.
>>
>> On OS/2, idle time can only be determined when Doodle's Screen Saver is
>> installed. Perhaps some of you with large *.sqlite files do not have
>
>> Looking in my profile, all of the files only add up to 6MB;
>> places.sqlite is the largest, at 1.7MB.
>>
>> On OS/2, idle time can only be determined when Doodle's Screen Saver is
>> installed. Perhaps some of you with large *.sqlite files do not have
>
> I *do* have DDS installed, and last night, for example, I left SM
> running all night.
Another thought... I seem to recall mention of some logic that is based
> running all night.
on the amount of free disk space. That partition was recently running
quite low (had to clean up some temporary junk), so it's possible that
is why my files got vacuumed efficiently.
Dariusz Piatkowski
Jun 1, 2012, 5:07:39 PM6/1/12
to
On Fri, 1 Jun 2012 14:00:52 UTC, Lewis Rosenthal
<lgros...@2-de-sp-am-2rosenthals.com> wrote:
> Greetings, all...
>
> My databases have been getting kind of crufty of late. Looking for an
> efficient way to vacuum the databases, I thought I'd pass along this tip
> (and a question).
>
> Instead of using a GUI app which allows for the maintenance of one file
> at a time, the SQLite library (available - dare I say it - via yum from
> Netlabs...not sure whether there's a WPI for it) can be called (assuming
> it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
> closed or at least with that profile inactive!) like so:
>
> for %a in (*.sqlite) do sqlite3.exe %a vacuum;
>
> (yes, the ";" is required)
....
<lgros...@2-de-sp-am-2rosenthals.com> wrote:
> Greetings, all...
>
> My databases have been getting kind of crufty of late. Looking for an
> efficient way to vacuum the databases, I thought I'd pass along this tip
> (and a question).
>
> Instead of using a GUI app which allows for the maintenance of one file
> at a time, the SQLite library (available - dare I say it - via yum from
> Netlabs...not sure whether there's a WPI for it) can be called (assuming
> it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
> closed or at least with that profile inactive!) like so:
>
> for %a in (*.sqlite) do sqlite3.exe %a vacuum;
>
> (yes, the ";" is required)
I used "Vacuum Places Improved 1.2" add-on...only one problem here, if you set
it to the AUTO mode (where you can pre-defined how often the vacuum proces runs
on it's own) it causes a severe havoc with Firefox. Each time a new URL is
opened it appears that the FULL vacuum gets executed. So I have the auto feature
disabled...
URL =>
https://addons.mozilla.org/en-US/firefox/addon/vacuum-places-improved/?src=api
Dave Yeo
Jun 1, 2012, 8:07:03 PM6/1/12
to
Lewis Rosenthal wrote:
>>> I was able to successfully vacuum 14 of 16 files. Two could not be
>>> >> accessed (and there seemed to be no reason, as I used this on an
>>> >> inactive profile):
>>> >>
>>> >> places.sqlite
>>> >> cookies.sqlite
>>> >>
>>> >> Thoughts on that one?
>> >
>> > The RPM build doesn't support WAL journalling? I'll try downloading the
>> > latest sqlite3 and testing, I'm due to make sure no OS/2 regressions
>> > have snuck in anyways
>> >
> That would explain it, wouldn't it. But then, why only those*two*
> databases? Are places& cookies the only two which would use WAL? I
>>> I was able to successfully vacuum 14 of 16 files. Two could not be
>>> >> accessed (and there seemed to be no reason, as I used this on an
>>> >> inactive profile):
>>> >>
>>> >> places.sqlite
>>> >> cookies.sqlite
>>> >>
>>> >> Thoughts on that one?
>> >
>> > The RPM build doesn't support WAL journalling? I'll try downloading the
>> > latest sqlite3 and testing, I'm due to make sure no OS/2 regressions
>> > have snuck in anyways
>> >
> That would explain it, wouldn't it. But then, why only those*two*
> thought they all did.
Looking at my profile, those are the only 2 that have a wal file
associated with them.
Dave
Dave Yeo
Jun 1, 2012, 8:18:16 PM6/1/12
to
I notice that my cookies.sqlite.wal file is actually bigger (30kb) then
cookies.sqlite which seems weird
Dave
Andy
Jun 2, 2012, 1:40:12 AM6/2/12
to
http://xsidebar.mozdev.org/modifiedmisc.html#vacuumplacesimproved
or to run manually what it apparently does:
Tools -> Web Development -> Error Console
Components.classes["@mozilla.org/browser/nav-history-service;1"].getSe
rvice(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSi
mpleSQL("VACUUM");
Evaluate
--
Andy
Jun 2, 2012, 2:01:22 AM6/2/12
to
that was larger than my current one so I switched it and sqlite3 could
not open it. I then ran the vacuum from the error console which
didn't seem to help in this case but then sqlite3 did open it fine
(though it ultimately was no smaller than what it began as, it
intially had increased by 5M but then got back down to almost the
original size of 15M).
The orginal places was 10M and is now 4M but unfortunately I forgot to
check sizes to determine if it was the sqlite3 or the error console as
I had run the error console first several times before shutting down
and running the sqlite3.
Andy
--
Andy
Jun 2, 2012, 2:07:59 AM6/2/12
to
launched Seamonkey and it grew back to the 10M. Running the command
from the error console did not affect the size. However, when I do
not run the command from the error console before closing Seamonkey
then sqlite3 cannot open the file. If I do run the command then
sqlite3 can vacuum it and it takes it back down to 4M but it gains it
right back as soon as I launch Seamonkey again.
--
Lewis Rosenthal
Jun 2, 2012, 3:15:50 PM6/2/12
to
On 06/02/12 02:07 am, Andy thus wrote :
How weird is that?
Frankly, I hadn't thought of running the vacuum from the error console;
thanks for pointing me in that direction. It's also possible, then, to
add something to the Tools menu via userChrome.js, like:
(function() {
var menuitem = document.createElement("menuitem");
menuitem.setAttribute("label", "Database Vacuum");
menuitem.setAttribute("accesskey", "V");
menuitem.setAttribute("oncommand",
'Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");');
var optionsitem = document.getElementById("addonsmgr");
optionsitem.parentNode.insertBefore(menuitem, optionsitem);
})();
And further still (haven't played with it, though) to make a submenu for
Vacuuming, and add all of the db's there, as well as a bit of javascript
to process the lot of them in sequence.
Frankly, I hadn't thought of running the vacuum from the error console;
thanks for pointing me in that direction. It's also possible, then, to
add something to the Tools menu via userChrome.js, like:
(function() {
var menuitem = document.createElement("menuitem");
menuitem.setAttribute("label", "Database Vacuum");
menuitem.setAttribute("accesskey", "V");
menuitem.setAttribute("oncommand",
'Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");');
var optionsitem = document.getElementById("addonsmgr");
optionsitem.parentNode.insertBefore(menuitem, optionsitem);
})();
And further still (haven't played with it, though) to make a submenu for
Vacuuming, and add all of the db's there, as well as a bit of javascript
to process the lot of them in sequence.
Lewis Rosenthal
Jun 2, 2012, 11:20:15 PM6/2/12
to
On 06/02/12 03:15 pm, Lewis Rosenthal thus wrote :
>>>> I was able to successfully vacuum 14 of 16 files. Two could not be
>>>> accessed (and there seemed to be no reason, as I used this on an
>>>> inactive profile):
>>>>
>>>> places.sqlite
>>>> cookies.sqlite
>>>>
<snip>
>>> My sqlite3 can open places but not cookies. I had a backup places
>>> that was larger than my current one so I switched it and sqlite3 could
>>> not open it. I then ran the vacuum from the error console which
>>> didn't seem to help in this case but then sqlite3 did open it fine
>>> (though it ultimately was no smaller than what it began as, it
>>> intially had increased by 5M but then got back down to almost the
>>> original size of 15M).
>>> The orginal places was 10M and is now 4M but unfortunately I forgot to
>>> check sizes to determine if it was the sqlite3 or the error console as
>>> I had run the error console first several times before shutting down
>>> and running the sqlite3.
>>>
I think Dave hit on it when he reminded me of the WAL journaling. I
think we may have a regression bug in our port of sqlite 3.7.2.
Supposedly, 3.7.0 and above could access WAL-journaled files. I haven't
tested my theory by moving one of my databases to a Linux box running
another build of sqlite, though, so this is merely conjecture.
>> I just answered some of it... I put the original places back and then
>> launched Seamonkey and it grew back to the 10M. Running the command
>> from the error console did not affect the size. However, when I do
>> not run the command from the error console before closing Seamonkey
>> then sqlite3 cannot open the file. If I do run the command then
>> sqlite3 can vacuum it and it takes it back down to 4M but it gains it
>> right back as soon as I launch Seamonkey again.
>>
I compacted my places.sqlite using the SQLite Manager extension. Using
that method, I got it down to 2MB from 10MB.
<snip>
> Frankly, I hadn't thought of running the vacuum from the error console;
> thanks for pointing me in that direction. It's also possible, then, to
> add something to the Tools menu via userChrome.js, like:
>
> (function() {
> var menuitem = document.createElement("menuitem");
> menuitem.setAttribute("label", "Database Vacuum");
> menuitem.setAttribute("accesskey", "V");
> menuitem.setAttribute("oncommand",
> 'Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");');
> var optionsitem = document.getElementById("addonsmgr");
> optionsitem.parentNode.insertBefore(menuitem, optionsitem);
> })();
>
well, for SeaMonkey, the above turned out to be:
(function() {
var menuitem = document.createElement("menuitem");
menuitem.setAttribute("label", "Database Vacuum");
menuitem.setAttribute("accesskey", "V");
menuitem.setAttribute("oncommand",
'Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");');
var optionsitem = document.getElementById("sync-setup");
optionsitem.parentNode.insertBefore(menuitem, optionsitem);
})();
(which places the menu item just above the menu separator above Web
Development.)
> And further still (haven't played with it, though) to make a submenu for
> Vacuuming, and add all of the db's there, as well as a bit of javascript
> to process the lot of them in sequence.
>
Ugh... Thinking about this some more, it's much easier to do menus in
xul than js, and more efficient in that case to just do an extension.
There is one which runs under FF (I tweaked it to install under SM), but
it's a bit rough:
https://addons.mozilla.org/en-US/firefox/addon/sqlite-optimizer/
The English is rather broken in the UI (I can't comment on the Korean or
the Japanese, and I haven't really looked at the German). Some people
complained that it trashed the db for Stylish, though I doubt that would
have anything to do with the extension itself (it just calls the same
routines we've been discussing). What it does do nicely is vacuums all
the databases and then reindexes them (I've added a reindex pass to my
offline script, as well).
I may clean up the UI a little bit and either see if the original author
wants to release an update or fork it.
> On 06/02/12 02:07 am, Andy thus wrote :
>> On Sat, 2 Jun 2012 06:01:22 UTC, "Andy" <abwi...@yahoo.com> wrote:
>>
>>> On Fri, 1 Jun 2012 14:00:52 UTC, Lewis Rosenthal
>>> <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>>>
>>>> Instead of using a GUI app which allows for the maintenance of one file
>>>> at a time, the SQLite library (available - dare I say it - via yum from
>>>> Netlabs...not sure whether there's a WPI for it) can be called (assuming
>>>> it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
>>>> closed or at least with that profile inactive!) like so:
>>>>
>>>> for %a in (*.sqlite) do sqlite3.exe %a vacuum;
>>>>
>>>> (yes, the ";" is required)
>>>>
<snip>
>> On Sat, 2 Jun 2012 06:01:22 UTC, "Andy" <abwi...@yahoo.com> wrote:
>>
>>> On Fri, 1 Jun 2012 14:00:52 UTC, Lewis Rosenthal
>>> <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>>>
>>>> Instead of using a GUI app which allows for the maintenance of one file
>>>> at a time, the SQLite library (available - dare I say it - via yum from
>>>> Netlabs...not sure whether there's a WPI for it) can be called (assuming
>>>> it's in the %PATH%) from the FF/SM/TB profile directory (with FF/SM/TB
>>>> closed or at least with that profile inactive!) like so:
>>>>
>>>> for %a in (*.sqlite) do sqlite3.exe %a vacuum;
>>>>
>>>> (yes, the ";" is required)
>>>>
>>>> I was able to successfully vacuum 14 of 16 files. Two could not be
>>>> accessed (and there seemed to be no reason, as I used this on an
>>>> inactive profile):
>>>>
>>>> places.sqlite
>>>> cookies.sqlite
>>>>
>>> My sqlite3 can open places but not cookies. I had a backup places
>>> that was larger than my current one so I switched it and sqlite3 could
>>> not open it. I then ran the vacuum from the error console which
>>> didn't seem to help in this case but then sqlite3 did open it fine
>>> (though it ultimately was no smaller than what it began as, it
>>> intially had increased by 5M but then got back down to almost the
>>> original size of 15M).
>>> The orginal places was 10M and is now 4M but unfortunately I forgot to
>>> check sizes to determine if it was the sqlite3 or the error console as
>>> I had run the error console first several times before shutting down
>>> and running the sqlite3.
>>>
think we may have a regression bug in our port of sqlite 3.7.2.
Supposedly, 3.7.0 and above could access WAL-journaled files. I haven't
tested my theory by moving one of my databases to a Linux box running
another build of sqlite, though, so this is merely conjecture.
>> I just answered some of it... I put the original places back and then
>> launched Seamonkey and it grew back to the 10M. Running the command
>> from the error console did not affect the size. However, when I do
>> not run the command from the error console before closing Seamonkey
>> then sqlite3 cannot open the file. If I do run the command then
>> sqlite3 can vacuum it and it takes it back down to 4M but it gains it
>> right back as soon as I launch Seamonkey again.
>>
that method, I got it down to 2MB from 10MB.
<snip>
> Frankly, I hadn't thought of running the vacuum from the error console;
> thanks for pointing me in that direction. It's also possible, then, to
> add something to the Tools menu via userChrome.js, like:
>
> (function() {
> var menuitem = document.createElement("menuitem");
> menuitem.setAttribute("label", "Database Vacuum");
> menuitem.setAttribute("accesskey", "V");
> menuitem.setAttribute("oncommand",
> 'Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");');
> var optionsitem = document.getElementById("addonsmgr");
> optionsitem.parentNode.insertBefore(menuitem, optionsitem);
> })();
>
(function() {
var menuitem = document.createElement("menuitem");
menuitem.setAttribute("label", "Database Vacuum");
menuitem.setAttribute("accesskey", "V");
menuitem.setAttribute("oncommand",
'Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");');
optionsitem.parentNode.insertBefore(menuitem, optionsitem);
})();
(which places the menu item just above the menu separator above Web
Development.)
> And further still (haven't played with it, though) to make a submenu for
> Vacuuming, and add all of the db's there, as well as a bit of javascript
> to process the lot of them in sequence.
>
xul than js, and more efficient in that case to just do an extension.
There is one which runs under FF (I tweaked it to install under SM), but
it's a bit rough:
https://addons.mozilla.org/en-US/firefox/addon/sqlite-optimizer/
The English is rather broken in the UI (I can't comment on the Korean or
the Japanese, and I haven't really looked at the German). Some people
complained that it trashed the db for Stylish, though I doubt that would
have anything to do with the extension itself (it just calls the same
routines we've been discussing). What it does do nicely is vacuums all
the databases and then reindexes them (I've added a reindex pass to my
offline script, as well).
I may clean up the UI a little bit and either see if the original author
wants to release an update or fork it.
Dave Yeo
Jun 3, 2012, 1:30:53 PM6/3/12
to
Dariusz Piatkowski wrote:
> I used "Vacuum Places Improved 1.2" add-on...only one problem here, if you set
> it to the AUTO mode (where you can pre-defined how often the vacuum proces runs
> on it's own) it causes a severe havoc with Firefox. Each time a new URL is
> opened it appears that the FULL vacuum gets executed. So I have the auto feature
> disabled...
>
> URL =>
> https://addons.mozilla.org/en-US/firefox/addon/vacuum-places-improved/?src=api
This add-on caused SeaMonkey to repeatedly crash here. Once in mozalloc
> I used "Vacuum Places Improved 1.2" add-on...only one problem here, if you set
> it to the AUTO mode (where you can pre-defined how often the vacuum proces runs
> on it's own) it causes a severe havoc with Firefox. Each time a new URL is
> opened it appears that the FULL vacuum gets executed. So I have the auto feature
> disabled...
>
> URL =>
> https://addons.mozilla.org/en-US/firefox/addon/vacuum-places-improved/?src=api
(allocates memory) twice silently and a SYS3171 in PMMerge.
SeaMonkey was using a lot of memory each time I witnessed the crash and
disabling the add-on fixed it.
Dave
Dariusz Piatkowski
Jun 3, 2012, 7:34:28 PM6/3/12
to
I do not let it AUTO clean, only manually...otherwise I have some strange things
hapenning with Firefox...not sure if that had anything to do with it or not
though.
Ray Davison
Jun 4, 2012, 2:07:29 PM6/4/12
to
Lewis Rosenthal wrote:
>
> BTW, for everyone else, a manual compacting of places.sqlite reduced the
> file from 10MB to 2MB (mine wasn't all that bad in the first place; I've
> heard of places.sqlite growing in excess of 50MB). I used Ruediger's Qt3
> build of SQLite Browser 1.3h to compact those last two files. I could
> have dumped the contents and then recreated the databases from a prompt,
> as well.
>
I had not looked at my places files for a while. I now have:
>
> BTW, for everyone else, a manual compacting of places.sqlite reduced the
> file from 10MB to 2MB (mine wasn't all that bad in the first place; I've
> heard of places.sqlite growing in excess of 50MB). I used Ruediger's Qt3
> build of SQLite Browser 1.3h to compact those last two files. I could
> have dumped the contents and then recreated the databases from a prompt,
> as well.
>
places.sqlite 10485760 4Jun12
places.sqlite.corrupt 10485760 3Oct11
places.sqlite-shm 32786 4Jun12
places.sqlite-wal 295160 4Jun12
Suggestions?
I once went looking for a database program that could work with sqlite.
Didn't seem to find anything I could use.
Ray
Steve Wendt
Jun 4, 2012, 2:22:15 PM6/4/12
to
On 6/4/2012 11:07 AM, Ray Davison wrote:
> I had not looked at my places files for a while. I now have:
>
> places.sqlite 10485760 4Jun12
> places.sqlite.corrupt 10485760 3Oct11
> places.sqlite-shm 32786 4Jun12
> places.sqlite-wal 295160 4Jun12
Given the age of the corrupt file, you can probably remove it. The
> I had not looked at my places files for a while. I now have:
>
> places.sqlite 10485760 4Jun12
> places.sqlite.corrupt 10485760 3Oct11
> places.sqlite-shm 32786 4Jun12
> places.sqlite-wal 295160 4Jun12
other two should go away with a clean close of the browser, I believe.
> I once went looking for a database program that could work with sqlite.
> Didn't seem to find anything I could use.
Lewis Rosenthal
Jun 4, 2012, 9:08:21 PM6/4/12
to
On 06/04/12 02:22 pm, Steve Wendt thus wrote :
>> I once went looking for a database program that could work with sqlite.
>> Didn't seem to find anything I could use.
>
> http://hobbes.nmsu.edu/h-search.php?key=sqlitebrowser
And don't forget the SQLiteManager extension:
https://addons.mozilla.org/en-US/seamonkey/addon/sqlite-manager/
which does a pretty good job. ;-)
> On 6/4/2012 11:07 AM, Ray Davison wrote:
>
<snip>
>
>> I once went looking for a database program that could work with sqlite.
>> Didn't seem to find anything I could use.
>
> http://hobbes.nmsu.edu/h-search.php?key=sqlitebrowser
https://addons.mozilla.org/en-US/seamonkey/addon/sqlite-manager/
which does a pretty good job. ;-)
Ruediger Ihle
Jun 5, 2012, 8:32:57 AM6/5/12
to
On Fri, 1 Jun 2012 17:57:10 UTC, Lewis Rosenthal <lgros...@2-de-sp-am-2rosenthals.com> wrote:
> Qt3 build of SQLite Browser 1.3h to compact those last two files.
I've just uploaded 1.3j, which is based on SQLite 3.7.8. The matching
> Qt3 build of SQLite Browser 1.3h to compact those last two files.
command line tool sqlite3.exe is included as well. With this you should
be able to process WAL databases as well.
--
Ruediger "Rudi" Ihle [S&T Systemtechnik GmbH, Germany]
http://www.s-t.de
Ruediger Ihle
Jun 5, 2012, 8:48:18 AM6/5/12
to
On Mon, 4 Jun 2012 18:07:29 UTC, Ray Davison <ray...@charter.net> wrote:
> places.sqlite-shm 32786 4Jun12
That's weird. The OS/2 version of SQLite is not supposed to
create "-shm" files. Only Windows and Unix versions do that.
> places.sqlite-shm 32786 4Jun12
That's weird. The OS/2 version of SQLite is not supposed to
create "-shm" files. Only Windows and Unix versions do that.
Ray Davison
Jun 5, 2012, 5:40:23 PM6/5/12
to
Ruediger Ihle wrote:
> On Mon, 4 Jun 2012 18:07:29 UTC, Ray Davison <ray...@charter.net> wrote:
>
>> places.sqlite-shm 32786 4Jun12
>
> That's weird. The OS/2 version of SQLite is not supposed to
> create "-shm" files. Only Windows and Unix versions do that.
I was running WinXP at the time.
> On Mon, 4 Jun 2012 18:07:29 UTC, Ray Davison <ray...@charter.net> wrote:
>
>> places.sqlite-shm 32786 4Jun12
>
> That's weird. The OS/2 version of SQLite is not supposed to
> create "-shm" files. Only Windows and Unix versions do that.
Ray
Steven Levine
Jun 5, 2012, 6:10:16 PM6/5/12
to
On Tue, 5 Jun 2012 12:48:18 UTC, "Ruediger Ihle"
<NOSPAM$R_I...@S-t.De> wrote:
Hi Rudi,
> > places.sqlite-shm 32786 4Jun12
> That's weird. The OS/2 version of SQLite is not supposed to
> create "-shm" files. Only Windows and Unix versions do that.
Ray shares profiles across eCS/OS2 and Windows platforms.
Steven
--
---------------------------------------------------------------------
Steven Levine <ste...@earthlink.bogus.net>
eCS/Warp/DIY etc. www.scoug.com www.ecomstation.com
---------------------------------------------------------------------
<NOSPAM$R_I...@S-t.De> wrote:
Hi Rudi,
> > places.sqlite-shm 32786 4Jun12
> That's weird. The OS/2 version of SQLite is not supposed to
> create "-shm" files. Only Windows and Unix versions do that.
Steven
--
---------------------------------------------------------------------
Steven Levine <ste...@earthlink.bogus.net>
eCS/Warp/DIY etc. www.scoug.com www.ecomstation.com
---------------------------------------------------------------------
Lewis Rosenthal
Jun 13, 2012, 11:31:57 AM6/13/12
to
On 06/05/12 08:32 am, Ruediger Ihle thus wrote :
Thanks so much, Ruediger! I'll give this a go right now.
Cheers/2
> On Fri, 1 Jun 2012 17:57:10 UTC, Lewis Rosenthal <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>
>> Qt3 build of SQLite Browser 1.3h to compact those last two files.
>
> I've just uploaded 1.3j, which is based on SQLite 3.7.8. The matching
> command line tool sqlite3.exe is included as well. With this you should
> be able to process WAL databases as well.
>
Sorry for the late follow-up!
>
>> Qt3 build of SQLite Browser 1.3h to compact those last two files.
>
> I've just uploaded 1.3j, which is based on SQLite 3.7.8. The matching
> command line tool sqlite3.exe is included as well. With this you should
> be able to process WAL databases as well.
>
Thanks so much, Ruediger! I'll give this a go right now.
Cheers/2
Lewis Rosenthal
Jun 13, 2012, 2:17:23 PM6/13/12
to
On 06/13/12 11:31 am, Lewis Rosenthal thus wrote :
got me to open a bug for 4OS2:
http://svn.netlabs.org/4os2/ticket/15
(Tried renaming sqlite3.exe in \usr\bin and creating a symlink to your
executable in \PROGRAMS\Sqlite Manager\OS2, which worked; however,
trying to execute that from 4OS2 did not. JdeBP handles executables via
symlinks, though.)
Anyway, after vacuuming, places.sqlite went down to ~2MB, and of course,
immediately after starting SM, it went right back up to ~10MB again. ;-)
I did get some fat trimmed from the others, though. I should add a
compacting pass to my Xshutdown folder, I guess.
> On 06/05/12 08:32 am, Ruediger Ihle thus wrote :
>> On Fri, 1 Jun 2012 17:57:10 UTC, Lewis Rosenthal <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>>
>>> Qt3 build of SQLite Browser 1.3h to compact those last two files.
>>
>> I've just uploaded 1.3j, which is based on SQLite 3.7.8. The matching
>> command line tool sqlite3.exe is included as well. With this you should
>> be able to process WAL databases as well.
>>
>
> Sorry for the late follow-up!
>
> Thanks so much, Ruediger! I'll give this a go right now.
>
Again, thanks, my friend! The new version worked straight off. It even
>> On Fri, 1 Jun 2012 17:57:10 UTC, Lewis Rosenthal <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>>
>>> Qt3 build of SQLite Browser 1.3h to compact those last two files.
>>
>> I've just uploaded 1.3j, which is based on SQLite 3.7.8. The matching
>> command line tool sqlite3.exe is included as well. With this you should
>> be able to process WAL databases as well.
>>
>
> Sorry for the late follow-up!
>
> Thanks so much, Ruediger! I'll give this a go right now.
>
got me to open a bug for 4OS2:
http://svn.netlabs.org/4os2/ticket/15
(Tried renaming sqlite3.exe in \usr\bin and creating a symlink to your
executable in \PROGRAMS\Sqlite Manager\OS2, which worked; however,
trying to execute that from 4OS2 did not. JdeBP handles executables via
symlinks, though.)
Anyway, after vacuuming, places.sqlite went down to ~2MB, and of course,
immediately after starting SM, it went right back up to ~10MB again. ;-)
I did get some fat trimmed from the others, though. I should add a
compacting pass to my Xshutdown folder, I guess.
Dave Yeo
Jun 13, 2012, 9:14:14 PM6/13/12
to
Lewis Rosenthal wrote:
> Again, thanks, my friend! The new version worked straight off. It even
> got me to open a bug for 4OS2:
>
> http://svn.netlabs.org/4os2/ticket/15
>
> (Tried renaming sqlite3.exe in \usr\bin and creating a symlink to your
> executable in \PROGRAMS\Sqlite Manager\OS2, which worked; however,
> trying to execute that from 4OS2 did not. JdeBP handles executables via
> symlinks, though.)
Symlink support in 4os2 is a long standing enhancement request :) For
> Again, thanks, my friend! The new version worked straight off. It even
> got me to open a bug for 4OS2:
>
> http://svn.netlabs.org/4os2/ticket/15
>
> (Tried renaming sqlite3.exe in \usr\bin and creating a symlink to your
> executable in \PROGRAMS\Sqlite Manager\OS2, which worked; however,
> trying to execute that from 4OS2 did not. JdeBP handles executables via
> symlinks, though.)
executables there is ln4exe a small executable forwarder
(http://bauxite.sakura.ne.jp/software/os2/misc/ln4exe-20090209b.zip).
What is JdeBP?
Dave
Steve Wendt
Jun 13, 2012, 10:11:37 PM6/13/12
to
On 6/13/2012 6:14 PM, Dave Yeo wrote:
>> JdeBP handles executables via symlinks, though.)
>
> What is JdeBP?
>> JdeBP handles executables via symlinks, though.)
>
The initials of a person (Jonathan de-Boyne Pollard, or something like
that). I assume that Lewis was referring to his CMD replacement (which
is annoyingly called cmd.exe, although it's not 100% compatible).
Dave Yeo
Jun 13, 2012, 11:06:12 PM6/13/12
to
which is a surprise as it is fairly old.
Actually it doesn't execute symlinks, just that Knut designed them to
put the link in plain text and it executes that. Linux and klibc will
read the link from the EA and follow it, at least on HPFS. Symlinks on
JFS are not compatible between Linux and OS/2
Dave
Steve Wendt
Jun 13, 2012, 11:11:18 PM6/13/12
to
On 06/13/12 08:06 pm, Dave Yeo wrote:
> Symlinks on JFS are not compatible between Linux and OS/2
Thanks, IBM!
> Symlinks on JFS are not compatible between Linux and OS/2
Steven Levine
Jun 14, 2012, 3:54:34 AM6/14/12
to
On Wed, 13 Jun 2012 18:17:23 UTC, Lewis Rosenthal
<lgros...@2-de-sp-am-2rosenthals.com> wrote:
Hi Lewis,
> Again, thanks, my friend! The new version worked straight off. It even
> got me to open a bug for 4OS2:
You mean an enhancement request. :-)
FWIW, I'm thinking about implementing #! support. We sorta need it to
deal with perl tainting. I can already do it for .cmd files since
it's really the same as exproc. However dealing with arbitrary
extensions is a bit more messy with the existing code.
Then their's my long dormant plans for usable unix paths support.
<lgros...@2-de-sp-am-2rosenthals.com> wrote:
Hi Lewis,
> Again, thanks, my friend! The new version worked straight off. It even
> got me to open a bug for 4OS2:
FWIW, I'm thinking about implementing #! support. We sorta need it to
deal with perl tainting. I can already do it for .cmd files since
it's really the same as exproc. However dealing with arbitrary
extensions is a bit more messy with the existing code.
Then their's my long dormant plans for usable unix paths support.
Lewis Rosenthal
Jun 14, 2012, 9:50:30 AM6/14/12
to
On 06/13/12 11:06 pm, Dave Yeo thus wrote :
Jonathan has a bit of a bad rap in certain circles; I'm really not sure
why. His 32-bit command interpreter is fairly good, though as Steve
points out, not fully compatible. He strives for posix-compliance, which
is a good thing, and the shell is fully 32-bit.
It's not that old, either (1.1.2 was released in 2009, IIRC):
http://homepage.ntlworld.com/jonathan.deboynepollard/Softwares/cmd.html
Steve is even mentioned in the acknowledgments as a beta tester. ;-)
Thanks for the link to ln4exe, Dave; I'll put that to good use!
why. His 32-bit command interpreter is fairly good, though as Steve
points out, not fully compatible. He strives for posix-compliance, which
is a good thing, and the shell is fully 32-bit.
It's not that old, either (1.1.2 was released in 2009, IIRC):
http://homepage.ntlworld.com/jonathan.deboynepollard/Softwares/cmd.html
Steve is even mentioned in the acknowledgments as a beta tester. ;-)
Thanks for the link to ln4exe, Dave; I'll put that to good use!
Lewis Rosenthal
Jun 14, 2012, 9:53:11 AM6/14/12
to
On 06/14/12 03:54 am, Steven Levine thus wrote :
> FWIW, I'm thinking about implementing #! support. We sorta need it to
> deal with perl tainting. I can already do it for .cmd files since
> it's really the same as exproc. However dealing with arbitrary
> extensions is a bit more messy with the existing code.
>
> Then their's my long dormant plans for usable unix paths support.
>
That would be handy...
Of course, one day, we'll all wake up to find that we're running our own
flavor of Unix with WPS on top of it!
> On Wed, 13 Jun 2012 18:17:23 UTC, Lewis Rosenthal
> <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>
> Hi Lewis,
>
>> Again, thanks, my friend! The new version worked straight off. It even
>> got me to open a bug for 4OS2:
>
> You mean an enhancement request. :-)
>
For Trac, they all go in the same way... :-)
> <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>
> Hi Lewis,
>
>> Again, thanks, my friend! The new version worked straight off. It even
>> got me to open a bug for 4OS2:
>
> You mean an enhancement request. :-)
>
> FWIW, I'm thinking about implementing #! support. We sorta need it to
> deal with perl tainting. I can already do it for .cmd files since
> it's really the same as exproc. However dealing with arbitrary
> extensions is a bit more messy with the existing code.
>
> Then their's my long dormant plans for usable unix paths support.
>
Of course, one day, we'll all wake up to find that we're running our own
flavor of Unix with WPS on top of it!
Steven Levine
Jun 15, 2012, 12:52:11 PM6/15/12
to
On Thu, 14 Jun 2012 13:53:11 UTC, Lewis Rosenthal
<lgros...@2-de-sp-am-2rosenthals.com> wrote:
Hi,
> For Trac, they all go in the same way... :-)
I guess you never tried the Type drop-down. :-)
> Of course, one day, we'll all wake up to find that we're running our own
> flavor of Unix with WPS on top of it!
That was the original Voyager plan. Too bad it morphed into something
else.
<lgros...@2-de-sp-am-2rosenthals.com> wrote:
Hi,
> For Trac, they all go in the same way... :-)
> Of course, one day, we'll all wake up to find that we're running our own
> flavor of Unix with WPS on top of it!
else.
Lewis Rosenthal
Jun 15, 2012, 3:54:53 PM6/15/12
to
On 06/15/12 12:52 pm, Steven Levine thus wrote :
instead of saying that I "opened a bug," I should have said that I
"opened a ticket." Tomatoes, tomatoes (between us).
>> Of course, one day, we'll all wake up to find that we're running our own
>> flavor of Unix with WPS on top of it!
>
> That was the original Voyager plan. Too bad it morphed into something
> else.
>
And before that, with the Taligent acquisition, and the original work
done for PPC, we could have ended up with a de-coupled WPS. Those are
Warpstock discussions, though. :-)
Cheers/2
> On Thu, 14 Jun 2012 13:53:11 UTC, Lewis Rosenthal
> <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>
> Hi,
>
>> For Trac, they all go in the same way... :-)
>
> I guess you never tried the Type drop-down. :-)
>
Oh, okay, if you want to split hairs... Yes, I did set it as an RFE.
> <lgros...@2-de-sp-am-2rosenthals.com> wrote:
>
> Hi,
>
>> For Trac, they all go in the same way... :-)
>
> I guess you never tried the Type drop-down. :-)
>
instead of saying that I "opened a bug," I should have said that I
"opened a ticket." Tomatoes, tomatoes (between us).
>> Of course, one day, we'll all wake up to find that we're running our own
>> flavor of Unix with WPS on top of it!
>
> That was the original Voyager plan. Too bad it morphed into something
> else.
>
done for PPC, we could have ended up with a de-coupled WPS. Those are
Warpstock discussions, though. :-)
Cheers/2
0 new messages