FF 3.0 Perforance Issue: Vaccum the SQLite Files in the Profile

[Rainer Stroebel]

hello

doing an "Vaccum" / "Compact" function on the SQLite files of the profile
improves the performance at lot

Do you have the new security features

Tell me if the site I'm visiting is a suspected attack side
Tell me if the stie I'm viviting is a suspected forgery

active?

Then first change the preference

urlclassifier.updatecachemax from -1 to 104857600

(via using about:config).

The following SQLite file are part of your profile:

Verzeichnis von S:\mof-3000\mozilla\firefox\Profiles\5eqjlbn3.default

26.06.08 2.28 7168 0 content-prefs.sqlite
26.06.08 11.39 20480 0 cookies.sqlite
26.06.08 2.30 10240 0 downloads.sqlite
26.06.08 11.39 9216 0 formhistory.sqlite
26.06.08 2.31 2048 0 permissions.sqlite
26.06.08 11.39 3837952 0 places.sqlite
26.06.08 2.31 2048 0 search.sqlite
26.06.08 11.37 34660352 0 urlclassifier3.sqlite
8 Datei(en) 38549504 Byte belegt

Verzeichnis von
S:\mof-3000\mozilla\firefox\Profiles\5eqjlbn3.default\OfflineCache

22.06.08 19.46 4096 0 index.sqlite
1 Datei(en) 4096 Byte belegt

As you see, the file urlclassifier3.sqlite ist currenty 34 MB big.

This file is growing from 0 ( at creation of the profile )
to about 56 MB (before Vaccum/Ccmpact ) at my installation.

There is clear a need to "Vaccum" this file after creation of
a profile and first few surf sessions.

Hwoto to shrink / vacuum / compact the SQLite file.

1. Manual Way

1.1 Get the application SQLiteBrowse from Hobbes

http://hobbes.nmsu.edu/cgi-bin/h-search?key=SQLiteBrowser&pushbutton=Search

Install the application on your system

1.2 shut down Firefox 3.0

1.3 save your current profile

1.4 Start the SQLite Browser

Open DB S:\mof-3000\mozilla\firefox\Profiles\5eqjlbn3.default\
urlclassifier3.sqlite

select menu Menue "File/Compact Database"

function processes at my installation about 1 min

Than the meassage box " compact done" is display with okay bottom

caveat: at the current version of SQLiteBrowser 1.3b
the cursor is still at "clock - Busy indicator -
but click to bottom okay works on my installation

Close the DB.

Repeat the procedure for the rest of the SQLite files of your profile

at my installation after 3 day of surfing,

the vacuum / compact function shrinks the

file urlclassifier3.sqlite from 56 MB to 34 MB

1.5 Do a test with Firefox 3.0

performance of your system? :-)

2. automatic way to vacuum the SQLite file a request

I like to do this via a command file or program execution on request.

To built this fuction, I do need your help!

I have not found a binary of SQLite executable (at ff 3.0 SQLite
release level)

The RxSql package 2.5 is at SQLite 3.3.x

The SQLite is currently at SQLite 3.5.9 according project

kind regards

Rainer

References:
==============

Bug 439340 – Background cleanup actions block desktop on OS/2
https://bugzilla.mozilla.org/show_bug.cgi?id=439340

Firefox 3.0: Blockien des Systems durch minutenlange hohe HDD I/O
Aktivität" - Workaround
http://de.os2.org/forum/diskussion/index.php3?all=116554

http://www.sqlite.org/

http://sqlitebrowser.sourceforge.net/index.html

http://www.sqlite.org/lang_vacuum.html

http://rexxsql.sourceforge.net/

[Heiko Nitzsche]

> the vacuum / compact function shrinks the
>
> file urlclassifier3.sqlite from 56 MB to 34 MB

What the heck is stored in this db that requires 34MB?
Are all files Firefox 3 receives put in there?
Is it the new cache mechanism?

[Steve Wendt]

On 6/26/2008 9:49 AM, Heiko Nitzsche wrote:

>> the vacuum / compact function shrinks the
>> file urlclassifier3.sqlite from 56 MB to 34 MB
>
> What the heck is stored in this db that requires 34MB?

It's the Google-supplied list of malware sites (phishing, etc.).

[Rainer Stroebel]

Hallo,

here are the data about the groth behaviour of the SQLite file in my Profile

History: Size of SQLite File "urlclassifier3.sqlite" in my Firefox 3.0
profile

2008-06-20 12.25 Profile created

2008-06-22 19.46 51.064.832

2008-06-26 00.15 53.800.960 before frist time SQLite Function Vacuum
2008-06-26 00.48 34.660.352 after Vacuum ,
Vacuum execution time: 60 sec - about

2008-06-26 11.37 34.660.352

2008-06-28 17.03 34.660.352 before Vacuum
2008-06-28 17.51 29.278.208 after Vacuum
Vacuum execution time: 45 sec

2008-06-29 08.40 29.458.432 current

Conclusion:

Shortly after profile creation and first few surf session
a Vaccum of urlclassifier3.sqlite is strongly, highly recommend
and than on a regular schedule

Rainer

[William L. Hartzell]

Sir:

I have the opinion that this needs to be a feature of the program
startup. Any chance you could fill a bug to see if the change would fly
past the powers that are? This would affect SeaMonkey also.
--
Bill
Thanks a Million!

[Peter Weilbacher]

On 29.06.08 17:55, William L. Hartzell wrote:

> Rainer Stroebel wrote:
>> Conclusion:
>>
>> Shortly after profile creation and first few surf session
>> a Vaccum of urlclassifier3.sqlite is strongly, highly recommend
>> and than on a regular schedule

Can you actually _measure_ a performance gain from before vacuuming?

> I have the opinion that this needs to be a feature of the program
> startup. Any chance you could fill a bug to see if the change would fly
> past the powers that are? This would affect SeaMonkey also.

That won't be happening, I'm pretty sure that hardly anybody will want
to delay loading the application by 60s or more just to clean out a
database.
--
Please | Official Warpzilla Ports: http://www.mozilla.org/ports/os2/
reply in |
newsgroup | Enhanced OS/2 builds: http://pmw-warpzilla.sf.net/
Steve's Warpzilla Tips: http://www.os2bbs.com/os2news/Warpzilla.html

[Cris]

Hi all,
I was having BAD performance problems with FF3, until I read this
thread. Following the suggestion given here, I was able to fix FF3 so
that now it is working smoothly. However, I still have a problem: if I
close FF3, it will hang. The window is closed, but the process stays
there forever with high CPU consumption, and I can't kill it no matter
what I try. If instead of closing it in the usual way I kill it
directly, it closes as expected.

Does anybody else see this behavior?

Just out of curiosity: at the office I have FF3 on Windows Server
2003, and it works smoothly. I checked to see if it creates a huge
urlclassifier3.sqlite, but the is _no_trace_ of that file. There is a
urlclassifier2.sqlite instead. Why?

Thank you
Bye

Cris

[Rainer Stroebel]

Cris schrieb:

Hello Chirs,

please check your profile under security on the office maschine,

both feature of 2. and 3 Box are probably not active

Tell me if the site I'm visiting is a suspected attack side

Tell me if the stie I'm visiting is a suspected forgery

so no SQLite files for this feature are generated

Kind Regards

Rainer

[Peter Weilbacher]

On 04.07.2008 09:13, Cris wrote:
> Hi all,
> I was having BAD performance problems with FF3, until I read this
> thread. Following the suggestion given here, I was able to fix FF3 so
> that now it is working smoothly. However, I still have a problem: if I
> close FF3, it will hang. The window is closed, but the process stays
> there forever with high CPU consumption, and I can't kill it no matter
> what I try. If instead of closing it in the usual way I kill it
> directly, it closes as expected.
>
> Does anybody else see this behavior?

No. But if you start if from the command line, do you see any output?
Maybe redirect it, like this
firefox.exe > output.log 2>&1
and then look at the log file.

If you are running any plugins (prime suspects: Flash and Java, as
always) you might want to try without, and see if that helps.

> Just out of curiosity: at the office I have FF3 on Windows Server
> 2003, and it works smoothly. I checked to see if it creates a huge
> urlclassifier3.sqlite, but the is _no_trace_ of that file. There is a
> urlclassifier2.sqlite instead. Why?

Don't know, same for me on Linux and Windows at work, although the
relevant settings are the same as on OS/2 at home.
--
Please Enhanced OS/2 builds of Mozilla applications
reply in http://pmw-warpzilla.sf.net/
newsgroup

[Peter Weilbacher]

On Thu, 26 Jun 2008 15:20:16 UTC, Rainer Stroebel wrote:

> 2. automatic way to vacuum the SQLite file a request
>
> I like to do this via a command file or program execution on request.
>
> To built this fuction, I do need your help!
>
> I have not found a binary of SQLite executable (at ff 3.0 SQLite
> release level)
>
>
> The RxSql package 2.5 is at SQLite 3.3.x
>
> The SQLite is currently at SQLite 3.5.9 according project

I have finally packaged SQLite 3.5.9, it's now on Hobbes. (Currently in
/pub/imcoming, should be in /pub/os2/dev/database/ soon.) That should
be fine to work with the Firefox files and script whatever you want to
do. But don't expect support from me on that! I just maintain the OS/2
version but don't have time to actually use it.

--
Please | Official Warpzilla Ports: http://www.mozilla.org/ports/os2/
reply in |
newsgroup | Enhanced OS/2 builds: http://pmw-warpzilla.sf.net/

[Rainer Stroebel]

Peter Weilbacher schrieb:

> On Thu, 26 Jun 2008 15:20:16 UTC, Rainer Stroebel wrote:
>
>
>>2. automatic way to vacuum the SQLite file a request
>>
>> I like to do this via a command file or program execution on request.
>>
>> To built this fuction, I do need your help!
>>
>> I have not found a binary of SQLite executable (at ff 3.0 SQLite
>>release level)
>>
>>
>> The RxSql package 2.5 is at SQLite 3.3.x
>>
>> The SQLite is currently at SQLite 3.5.9 according project
>
>
> I have finally packaged SQLite 3.5.9, it's now on Hobbes. (Currently in
> /pub/imcoming, should be in /pub/os2/dev/database/ soon.) That should
> be fine to work with the Firefox files and script whatever you want to
> do. But don't expect support from me on that! I just maintain the OS/2
> version but don't have time to actually use it.

Hallo Peter,

thanks for the new port.

The following example have been tested with old SQLite 3.4.2 port.

The vacuum command does work, but do not physical shrink the DB file.

I will test the example with the new port 3.5.9

Here is the example code:

======================
File: s:\SQLite342.cmd
======================

rem mode 80,50
rem
rem SET LIBPATHSTRICT=T
rem
SET BEGINLIBPATH=S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\dll;
rem
S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\bin\SQLite3.exe %1 %2 %3 %4 %5 %6

===================
File: s:\sql-in.txt
===================

-- ***********************************
-- File S:\sql-in.txt
-- SQL Statemets for Vacuum a Database
-- ***********************************
.echo on
PRAGMA legacy_file_format;
PRAGMA temp_store_directory;
PRAGMA cache_size;
PRAGMA database_list;
--
PRAGMA main.freelist_count;
--
vacuum;
--
PRAGMA main.freelist_count;
.quit

============================================================
File: SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
============================================================

rem Example for Vacuum the SQLite Files of a user profile
rem
rem File: S:\SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
rem
rem 2008-07-07 11.0 V1.0 Rainer D. Stroebel
rem
rem
rem Test the command file:
rem
rem S:\SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
>consol.txt 2>&1
rem

set Path_to_User_Profile_SQLite_Files=P:\SQLite-Test-DBs

rem ************************
rem create the test bases
rem ************************

del %Path_to_User_Profile_SQLite_Files%\*.sqlite
copy S:\mof-profile-save\mof-3000-2008-06-26-00-15\*.sqlite
%Path_to_User_Profile_SQLite_Files%\*.*

rem Does not work, the complete path to the DBs has to be defined
direct not relative!!
rem Set USER_HOME=P:\

set SQLite_DB_01=%Path_to_User_Profile_SQLite_Files%\content-prefs.sqlite
set SQLite_DB_02=%Path_to_User_Profile_SQLite_Files%\cookies.sqlite
set SQLite_DB_03=%Path_to_User_Profile_SQLite_Files%\downloads.sqlite
set SQLite_DB_04=%Path_to_User_Profile_SQLite_Files%\formhistory.sqlite
set SQLite_DB_05=%Path_to_User_Profile_SQLite_Files%\permissions.sqlite
set SQLite_DB_06=%Path_to_User_Profile_SQLite_Files%\places.sqlite
set SQLite_DB_07=%Path_to_User_Profile_SQLite_Files%\search.sqlite
set SQLite_DB_08=%Path_to_User_Profile_SQLite_Files%\urlclassifier3.sqlite

P:
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_01% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_02% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_03% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_04% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_05% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_06% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_07% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_08% .exit

s:

=====================================================
consol output for the call for urlclassifier3.sqlite
=====================================================

P:\]call s:\sqlite342.cmd -batch -init s:\sql-in.txt
P:\SQLite-Test-DBs\urlclassifier3.sqlite .exit

[P:\]rem mode 80,50

[P:\]rem

[P:\]rem SET LIBPATHSTRICT=T

[P:\]rem

[P:\]SET BEGINLIBPATH=S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\dll;

[P:\]rem

[P:\]S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\bin\SQLite3.exe -batch
-init s:\sql-in.txt P:\SQLite-Test-DBs\urlclassifier3.sqlite .exit
-- Loading resources from s:\sql-in.txt
PRAGMA legacy_file_format;

1
PRAGMA temp_store_directory;

PRAGMA cache_size;

2000
PRAGMA database_list;

0|main|P:\SQLite-Test-DBs\urlclassifier3.sqlite
--

PRAGMA main.freelist_count;

1451
--

vacuum;

--

PRAGMA main.freelist_count;

0
.quit

[P:\]s:

[Rainer Stroebel]

Here are teh test result with the new built of SQLite 3.5.9

=== profile from first sessiones with FF 3.0, without any vacuum
executed on the profile

Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\SQLite-Test-DBs

7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
20.06.08 21.49 7168 0 content-prefs.sqlite
26.06.08 0.16 20480 0 cookies.sqlite
25.06.08 14.31 12288 0 downloads.sqlite
25.06.08 17.51 9216 0 formhistory.sqlite
20.06.08 13.16 2048 0 permissions.sqlite
26.06.08 0.16 3948544 0 places.sqlite
20.06.08 12.26 2048 0 search.sqlite
26.06.08 0.15 53800960 0 urlclassifier3.sqlite
10 Datei(en) 57802752 Byte belegt
1050610 K Byte frei
[S:\]

======================= after the vacumm

[P:\SQLite-Test-DBs]dir P:\SQLite-Test-DBs\*.*

Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\SQLite-Test-DBs

7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
7.07.08 12.30 7168 0 content-prefs.sqlite
7.07.08 12.30 18432 0 cookies.sqlite
7.07.08 12.30 10240 0 downloads.sqlite
7.07.08 12.30 9216 0 formhistory.sqlite
7.07.08 12.30 2048 0 permissions.sqlite
7.07.08 12.30 3661824 0 places.sqlite
7.07.08 12.30 2048 0 search.sqlite
7.07.08 12.31 34660352 0 urlclassifier3.sqlite
10 Datei(en) 38371328 Byte belegt
1070041 K Byte frei
[P:\SQLite-Test-DBs]

It does look okay!!

Now here are the updated sample code to 3.5.9:
=============================================

======================
File: s:\SQLite359.cmd
======================

rem mode 80,50
rem
SET LIBPATHSTRICT=T
rem
SET BEGINLIBPATH=S:\download\os2\SQLite\V3-5-9;S:\download\os2\libc063;
rem
S:\download\os2\SQLite\V3-5-9\SQLite3.exe %1 %2 %3 %4 %5 %6

rem 2008-07-07 11.00 V1.0 Rainer D. Stroebel
rem 2008-07.07 12.15 V1.0.1 SQLite342.cmd --> SQLite359,cmd

rem
rem Test the command file:
rem
rem S:\SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
>consol.txt 2>&1
rem

set Path_to_User_Profile_SQLite_Files=P:\SQLite-Test-DBs
set SQLite-CMD=s:\SQLite359.cmd

rem ************************
rem create the test bases
rem ************************

del %Path_to_User_Profile_SQLite_Files%\*.sqlite
copy S:\mof-profile-save\mof-3000-2008-06-26-00-15\*.sqlite
%Path_to_User_Profile_SQLite_Files%\*.*

rem Does not work, the complete path to the DBs has to be defined
direct not relative!!
rem Set USER_HOME=P:\

set SQLite_DB_01=%Path_to_User_Profile_SQLite_Files%\content-prefs.sqlite
set SQLite_DB_02=%Path_to_User_Profile_SQLite_Files%\cookies.sqlite
set SQLite_DB_03=%Path_to_User_Profile_SQLite_Files%\downloads.sqlite
set SQLite_DB_04=%Path_to_User_Profile_SQLite_Files%\formhistory.sqlite
set SQLite_DB_05=%Path_to_User_Profile_SQLite_Files%\permissions.sqlite
set SQLite_DB_06=%Path_to_User_Profile_SQLite_Files%\places.sqlite
set SQLite_DB_07=%Path_to_User_Profile_SQLite_Files%\search.sqlite
set SQLite_DB_08=%Path_to_User_Profile_SQLite_Files%\urlclassifier3.sqlite

dir %Path_to_User_Profile_SQLite_Files%\*.*

P:
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_01% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_02% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_03% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_04% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_05% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_06% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_07% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_08% .exit

dir %Path_to_User_Profile_SQLite_Files%\*.*

s:

[Rainer Stroebel]

Peter Weilbacher schrieb:

>
> I have finally packaged SQLite 3.5.9, it's now on Hobbes. (Currently in
> /pub/imcoming, should be in /pub/os2/dev/database/ soon.) That should
> be fine to work with the Firefox files and script whatever you want to
> do. But don't expect support from me on that! I just maintain the OS/2
> version but don't have time to actually use it.

Hello Peter,

the new port does work better than old 3.4.2.

Please have a look at the realisation of the

PRAGMA temp_store_directory;

at/with/ in ? the port.

excerpt from sqlite doc:

======================================================================

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory-name';

Query or change the setting of the "temp_store_directory" - the
directory where files used for storing temporary tables and indices are
kept. This setting lasts for the duration of the current connection only
and resets to its default value for each new connection opened.

When the temp_store_directory setting is changed, all existing
temporary tables, indices, triggers, and viewers are immediately
deleted. In practice, temp_store_directory should be set immediately
after the database is opened.

The value directory-name should be enclosed in single quotes. To
revert the directory to the default, set the directory-name to an empty
string, e.g., PRAGMA temp_store_directory = ''. An error is raised if
directory-name is not found or is not writable.

The default directory for temporary files depends on the OS. For
Unix/Linux/OSX, the default is the is the first writable directory found
in the list of: /var/tmp, /usr/tmp, /tmp, and current-directory. For
Windows NT, the default directory is determined by Windows, generally
C:\Documents and Settings\user-name\Local Settings\Temp\. Temporary
files created by SQLite are unlinked immediately after opening, so that
the operating system can automatically delete the files when the SQLite
process exits. Thus, temporary files are not normally visible through ls
or dir commands.

============================================================================

It does not use the OS/2 default dir value of env TMP

Example:

[P:\SQLite-Test-DBs]S:\download\os2\SQLite\V3-5-9\SQLite3.exe -batch

-init s:\sql-in.txt P:\SQLite-Test-DBs\urlclassifier3.sqlite .exit
-- Loading resources from s:\sql-in.txt
PRAGMA legacy_file_format;

1
PRAGMA temp_store_directory;

PRAGMA cache_size;

2000
PRAGMA database_list;

0|main|P:\SQLite-Test-DBs\urlclassifier3.sqlite
--

PRAGMA main.freelist_count;

1451
--

vacuum;

--

PRAGMA main.freelist_count;

0
.quit

[P:\SQLite-Test-DBs]

[Rainer Stroebel]

New test:

[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe -version -init
sql-in-test.txt p:\SQLite-Test-DBs\dow
nload.sqlite
-- Loading resources from sql-in-test.txt

PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;

PRAGMA temp_store_directory='P:\TMP';
PRAGMA temp_store_directory;
P:\TMP

PRAGMA cache_size;
2000
PRAGMA database_list;

0|main|P:\SQLite-Test-DBs\download.sqlite
--
PRAGMA main.freelist_count;
0

--
vacuum;
--
PRAGMA main.freelist_count;
0
.quit

3.5.9

[S:\]

Problem is minor;

Inital value is not set in OS/2 port

[Ruediger Ihle]

On Mon, 7 Jul 2008 11:03:48 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> It does not use the OS/2 default dir value of env TMP

What makes oyu believe that ?

--
Ruediger "Rudi" Ihle [S&T Systemtechnik GmbH, Germany]
http://www.s-t.de
Please remove all characters left of the "R" in my email address

[Rainer Stroebel]

Ruediger Ihle schrieb:

> On Mon, 7 Jul 2008 11:03:48 UTC, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>>It does not use the OS/2 default dir value of env TMP
>
>
> What makes oyu believe that ?
>
>
>

Hallo Ruediger,

PRAGMA temp_store_directory;
PRAGMA temp_store_directory='P:\TMP';
PRAGMA temp_store_directory;
P:\TMP

The first

PRAGMA temp_store_directory;

does not replay with an value of the OS/2 env variable:

it reply no value set!!

[Rainer Stroebel]

Rainer Stroebel schrieb:

additional data to the test:
============================

Run an dir command during execution:

no sqlites file in P:\TMP

but found a journal file in the DB driectory:

Should this jounnal file not be located in the tmp dir?

OS/2 Strg+Esc = Fensterliste HELP = Hilfe
[L:\]dir P:\sqlite-test-DBs\*.*

Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415

Verzeichnis von P:\sqlite-test-DBs

7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..

7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.39 34660352 0 urlclassifier3.sqlite
7.07.08 13.53 32822904 0 urlclassifier3.sqlite-journal
11 Datei(en) 71194232 Byte belegt
1002013 K Byte frei

[L:\]dir P:\sqlite-test-DBs\*.*

Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415

Verzeichnis von P:\sqlite-test-DBs

7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..

7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.39 34660352 0 urlclassifier3.sqlite
7.07.08 13.53 34732168 0 urlclassifier3.sqlite-journal
11 Datei(en) 73103496 Byte belegt
1000428 K Byte frei

[L:\]dir P:\sqlite-test-DBs\*.*

Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415

Verzeichnis von P:\sqlite-test-DBs

7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..

7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.53 34660352 0 urlclassifier3.sqlite

10 Datei(en) 38371328 Byte belegt

1069891 K Byte frei

[L:\]dir P:\sqlite-test-DBs\*.*

Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415

Verzeichnis von P:\sqlite-test-DBs

7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..

7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.53 34660352 0 urlclassifier3.sqlite

10 Datei(en) 38371328 Byte belegt

1069891 K Byte frei

[L:\]

[Rainer Stroebel]

Rainer Stroebel schrieb:

just to ducment it more clearly:

here ist the input file:

-- ***********************************
-- File S:\sql-in-test.txt

-- SQL Statemets for Vacuum a Database
-- ***********************************
.echo on
PRAGMA legacy_file_format;

PRAGMA temp_store_directory;
PRAGMA temp_store_directory='P:\TMP';
PRAGMA temp_store_directory;

PRAGMA cache_size;
PRAGMA database_list;
--
PRAGMA main.freelist_count;

--
vacuum;
--
PRAGMA main.freelist_count;

.quit

Please compare it to the documented results

[Ruediger Ihle]

On Mon, 7 Jul 2008 11:46:20 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> The first
>
> PRAGMA temp_store_directory;
>
> does not replay with an value of the OS/2 env variable:
>
> it reply no value set!!

If temp_store_directory is not explicitely set by a PRAGMA
directive, it is NULL. What you see is the normal behavior.
Not just on OS/2, but on any platform SQLite is running on.

However, I just discovered that the OS/2 implementation will
ignore temp_store_directory completely :-(. That's is really
a bug which we should fix eventually.

[Ruediger Ihle]

On Mon, 7 Jul 2008 12:03:21 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> no sqlites file in P:\TMP

No wonder. temp_storage_directory is used for temporary tables. Probably
you didn't do any operations that created such tables or didn't look at
the directory at the "right" time.

Also it appears that temp_storage_directory is ignored in OS/2 anyway...

> Should this jounnal file not be located in the tmp dir?

No. From th docs:

The rollback journal is always located in the same directory as the
database
file and has the same name as the database file except with the 8
characters
"-journal" appended.

Note, that a journal is not a temporary table. So even if we fix the
temp_storage_directory thing, the journal would still be where it is
now. However, on some platforms the file might get created with the
"hidden" attribute so that users normally don't see it ;-)

[Peter Weilbacher]

On 07.07.2008 14:40, Ruediger Ihle wrote:

> However, I just discovered that the OS/2 implementation will
> ignore temp_store_directory completely :-(. That's is really
> a bug which we should fix eventually.

Thanks for the hint. I have found the missing lines.

--
Please Enhanced OS/2 builds of Mozilla applications
reply in http://pmw-warpzilla.sf.net/
newsgroup

[Rainer Stroebel]

Ruediger Ihle schrieb:

> On Mon, 7 Jul 2008 11:46:20 UTC, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>>The first
>>
>>PRAGMA temp_store_directory;
>>
>>does not replay with an value of the OS/2 env variable:
>>
>>it reply no value set!!
>
>
>
> If temp_store_directory is not explicitely set by a PRAGMA
> directive, it is NULL. What you see is the normal behavior.
> Not just on OS/2, but on any platform SQLite is running on.
>
> However, I just discovered that the OS/2 implementation will
> ignore temp_store_directory completely :-(. That's is really
> a bug which we should fix eventually.
>
>
>

Please have a look about the paragraph with the default value.

The question is: Witch value is the default value for the OS/2 port?

Null? Does make no sense to me.

[Rainer Stroebel]

Ruediger Ihle schrieb:

> On Mon, 7 Jul 2008 12:03:21 UTC, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>
>>no sqlites file in P:\TMP
>
>
> No wonder. temp_storage_directory is used for temporary tables. Probably
> you didn't do any operations that created such tables or didn't look at
> the directory at the "right" time.
>
> Also it appears that temp_storage_directory is ignored in OS/2 anyway...
>
>
>
>>Should this jounnal file not be located in the tmp dir?
>
>
> No. From th docs:
>
> The rollback journal is always located in the same directory as the
> database
> file and has the same name as the database file except with the 8
> characters
> "-journal" appended.
>
>
> Note, that a journal is not a temporary table. So even if we fix the
> temp_storage_directory thing, the journal would still be where it is
> now. However, on some platforms the file might get created with the
> "hidden" attribute so that users normally don't see it ;-)
>
>

I just rereading the doc again.

I do not think, the -journal file created by the vacuum command is a
real journal file.

From the doc of the vaccum command.

The VACUUM command cleans the main database by copying its contents to a
temporary database file and reloading the original database file from
the copy. This eliminates free pages, aligns table data to be
contiguous, and otherwise cleans up the database file structure.

Does the doc is syncron to the current implementation?

I think, the physical implementation of the Vacuum command is just copying
the datatbase to an database with the journal suffix.

After succesfully done this, the old file is deleted and the
-journal file is renamed to the original file name.

If the operation failed, the "journal" file is just deleted.

The rename methode requries: the file has to be in the same directory.

If a temp dir is used, additional I/O would be required.

If you have a big DB, that could be a performace grain,
if you have the temp file on a different phyical drive.
Even if you have to copy the file back.
SQLit does not expect to run onf configurations with 2 physical drives,
so the rename methode is implemented.

What do I learn:
================

The jorunal files are part of the same dir as the DB.

The vacuum command requieres free disk space greater or equal the size
of the DB to be sure. ( on the same drive )

The actual required size is the size of the new compacted DB.

[Ruediger Ihle]

On Mon, 7 Jul 2008 13:36:34 UTC, Peter Weilbacher
<news...@weilbacher.org> wrote:

> Thanks for the hint. I have found the missing lines.

Be sure to have a look at the character set stuff. I suspect,
that the temp_store_directory is already UTF8 while the
environment variables are not...

[Ruediger Ihle]

NULL is the default value for temp_store_directory on any platform.

The meaning of that is: "The user has not specified something
special, so let's use a platform-specific value that is hard-
coded into the library itself". This platform-specific default
is *NOT* reported back via "PRAGMA temp_store_directory".

In case of OS/2 the environment variables "TEMP", "TMP" and
"TMPDIR" are checked (in that order). If none of them is defined,
the root directory of the current drive is assumed to be the
directory for temporary tables.

[Rainer Stroebel]

Ruediger Ihle schrieb:

> On Mon, 7 Jul 2008 14:08:33 UTC, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>>The question is: Witch value is the default value for the OS/2 port?
>>
>>Null? Does make no sense to me.
>
>
>
> NULL is the default value for temp_store_directory on any platform.
>
> The meaning of that is: "The user has not specified something
> special, so let's use a platform-specific value that is hard-
> coded into the library itself". This platform-specific default
> is *NOT* reported back via "PRAGMA temp_store_directory".
>

> In case of OS/2 .
>
>
>

Okay, that information is very valueable to understand the working of
the port :-)

PRAGMA temp_store_directory

The default directory for temporary files for the OS/2 port is set
according the following rules:

The environment variables "TEMP", "TMP" and "TMPDIR" are checked (in

that order). If none of them is defined, the root directory of the
current drive is assumed to be the directory for temporary tables

NULL is the default value for temp_store_directory on any platform.

If the value ist NULL, the system uses the default directory.

Suggestion:

Polish my english and add the information to the readme of the port and
may be to the SQLite Documenation.

I try to be conform to the text on the syntax description

[Rainer Stroebel]

Peter Weilbacher schrieb:

> On Thu, 26 Jun 2008 15:20:16 UTC, Rainer Stroebel wrote:

>
> I have finally packaged SQLite 3.5.9, it's now on Hobbes. (Currently in
> /pub/imcoming, should be in /pub/os2/dev/database/ soon.) That should
> be fine to work with the Firefox files and script whatever you want to
> do. But don't expect support from me on that! I just maintain the OS/2
> version but don't have time to actually use it.

Hello Peter, hello Rüdiger,

thanks for the solving of the PRAGMA temp_store_directory Question.

From my test with the 3.4.2 port I learn the Sqlite3 does not use
the current directory and does not user the USER_HOME.

I had to address the DB with the "Full" Path.

This behaviour change with new port positivly.

Now the current directory is used,

The port does not user the env USER_HOME.

This is different to the Linux port.

======================================================
Can following info be add to the Readme of the port?
======================================================

SQLite3.exe [OPTIONS] FILENAME [SQL]

FILENAME

If no path with FILENAME is specified, the OS/2 port uses the current
directory.
It doe not make use of USER_HOME env. variable

==End of text for the readme =========================

Example of the system behaviour:

[S:\]del download.sqlite

[S:\]Set USER_HOME=P:\SQLite-Test-DBs

[S:\]sqlite359 download.sqlite

[S:\]rem mode 80,50

[S:\]rem

[S:\]SET LIBPATHSTRICT=T

[S:\]rem

[S:\]SET BEGINLIBPATH=S:\download\os2\SQLite\V3-5-9;S:\download\os2\libc063;

[S:\]rem

[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe download.sqlite
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .database
seq name file
--- ---------------
----------------------------------------------------------
0 main S:\download.sqlite
sqlite> .quit

[S:\]

A new database "download.sqlite" is created in the current directory

[Dave Yeo]

On 07/07/08 09:45 am, Rainer Stroebel wrote:
> From my test with the 3.4.2 port I learn the Sqlite3 does not use
> the current directory and does not user the USER_HOME.
>
> I had to address the DB with the "Full" Path.
>
> This behaviour change with new port positivly.
>
> Now the current directory is used,
>
> The port does not user the env USER_HOME.
>
> This is different to the Linux port.
>
> ======================================================
> Can following info be add to the Readme of the port?
> ======================================================
>
> SQLite3.exe [OPTIONS] FILENAME [SQL]
>
> FILENAME
>
> If no path with FILENAME is specified, the OS/2 port uses the current
> directory.
> It doe not make use of USER_HOME env. variable

(looking at the source of 3.5.7 since that is what I have here)
The only references to the home directory are in shell.c (not counting
tk/tcl stuff) For OS/2 it tries %USERPROFILE% then %HOME% then falls
back on C:\
I don't see any reference to USER_HOME
Dave

[Peter Weilbacher]

On 07.07.08 18:45, Rainer Stroebel wrote:

> From my test with the 3.4.2 port I learn the Sqlite3 does not use
> the current directory and does not user the USER_HOME.

It _does_ use the current directory, unless you give a path. That is
expected behavior for any application.

> I had to address the DB with the "Full" Path.
>
> This behaviour change with new port positivly.
>
> Now the current directory is used,
>
> The port does not user the env USER_HOME.
>
> This is different to the Linux port.

In what way? It ignores USER_HOME just like on Linux.

> ======================================================
> Can following info be add to the Readme of the port?
> ======================================================
>
> SQLite3.exe [OPTIONS] FILENAME [SQL]
>
> FILENAME
>
> If no path with FILENAME is specified, the OS/2 port uses the current
> directory.
> It doe not make use of USER_HOME env. variable

Do you expect it to write %USER_HOME%\FILENAME instead of FILENAME?
Why?

[Rainer Stroebel]

Peter Weilbacher schrieb:

> On 07.07.08 18:45, Rainer Stroebel wrote:
>
>> From my test with the 3.4.2 port I learn the Sqlite3 does not use
>> the current directory and does not user the USER_HOME.
>
>
> It _does_ use the current directory, unless you give a path. That is
> expected behavior for any application.
>
>> I had to address the DB with the "Full" Path.
>>
>> This behaviour change with new port positivly.
>>
>> Now the current directory is used,
>>
>> The port does not user the env USER_HOME.
>>
>> This is different to the Linux port.
>
>
> In what way? It ignores USER_HOME just like on Linux.
>
>> ======================================================
>> Can following info be add to the Readme of the port?
>> ======================================================
>>
>> SQLite3.exe [OPTIONS] FILENAME [SQL]
>>
>> FILENAME
>>
>> If no path with FILENAME is specified, the OS/2 port uses the current
>> directory.
>> It doe not make use of USER_HOME env. variable
>
>
> Do you expect it to write %USER_HOME%\FILENAME instead of FILENAME?
> Why?

I do find the current behavour okay!

Based on my testing with 3.4.2 port, current directory does not work on
that port.
I had to address the File with "full" path to recognise the DB.
Done some google and found an hint, Linux works with USER_HOME value
for SQLite3. Test it with 3.4.2 - it does not work.

Now the new port does work with the current dir as default.
That is big improvement to the 3.4.2 port!
I do see no need for the USER_HOME on an OS/2 Port.

Just to document the fact I suggest the Readme addition.