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.

[Ruediger Ihle]

On Tue, 8 Jul 2008 06:58:07 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> Based on my testing with 3.4.2 port, current directory does not work on
> that port.

Maybe. But we are talking about 3.5.9.

> Done some google and found an hint, Linux works with USER_HOME value
> for SQLite3.

Done some source code search and found absolutely no reference to
USER_HOME. Not on Linux and not on any other supported platform.

As David stated: The shell program (not the engine itself) is
investigating %USERPROFILE%, %HOME% and the combination of
%HOMEDRIVE% and %HOMEPATH% to identfy the user's home directory.
If none of the above is defined, it falls back to C:\.

If the "-init" parameter is not present on the command line, the
shell tries to load and execute a file named ".sqliterc" from the
home directory. When running in interactive mode, the home directory
is also used to maintain a command history (file ".sqlite_history")
on some platforms. But I don't think we have that enabled in OS/2...

In no case the home directory is used to locate a database file.

[Ruediger Ihle]

On Mon, 7 Jul 2008 14:55:00 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> Does the doc is syncron to the current implementation?

I don't know, but isn't documentation always behind ;-).

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

I don't fully understand the details, but I can assure you it's
definitively more complex than this...

[Rainer Stroebel]

Rainer Stroebel schrieb:
>

>
> Howto to shrink / vacuum / compact the SQLite files.
>
>
> 1. Manual Procedure
>
-----> snip

>
> 2. Automatic Procdures to Vacuum the SQLite file a request

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

Automatic procedure to Vacuum the SQLite files on request

Status of the implementation: ( as of 2008-07-08 20.00 Rainer D. Stroebel )

The prerequisites for the solution are meet. Peter Weilbacher has ported
the current SQLite 3.5.9 version and released the package on Hobbes.
Thanks - a happy user :-)

The procedure is implemented on my system and tested.

A Lock (Semaphore) is realised to prevent the start of the browser
during maintenance
and the start of the maintenance during an active browser session.

n generations of Backup of the Profile are automaticly maintained

N = 4 is realised in code, can be adapted by adding two line of code
for each additional generation

n generations of logs of a maintenance session are retaining
N = 3 are currently administrated

Todo

More clean up of the code, write a installation guide and publish the
solution.

[Trevor Hemsley]

On Thu, 26 Jun 2008 15:20:16 UTC in mozilla.dev.ports.os2, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

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

What produced this malformed Subject header? Was it by design that it has a
trailing CRLF pair or is this a bug in the posting agent

User-Agent: Mozilla/5.0 (OS/2; U; Warp 4.5; de-AT; rv:1.7.7) Gecko/20050425

It's showing up in Pronews with half the header included in the body of the
article because the subject line is followed by a blank one.

--
Trevor Hemsley, Brighton, UK
Trevor dot Hemsley at ntlworld dot com

[Rainer Stroebel]

Trevor Hemsley schrieb:

> On Thu, 26 Jun 2008 15:20:16 UTC in mozilla.dev.ports.os2, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>>Subject: FF 3.0 Perforance Issue: Vaccum the SQLite Files in the Profile
>
>
> What produced this malformed Subject header? Was it by design that it has a
> trailing CRLF pair or is this a bug in the posting agent
>
> User-Agent: Mozilla/5.0 (OS/2; U; Warp 4.5; de-AT; rv:1.7.7) Gecko/20050425
>
> It's showing up in Pronews with half the header included in the body of the
> article because the subject line is followed by a blank one.
>

Hello Trevor,

may be the trailing CRLR signe had come to the header field of the
message by pasting the line to the field.

Mozilla 1.7.7 does display the thread normally.

[Steve Wendt]

On 7/8/2008 1:09 PM, Rainer Stroebel wrote:

>> User-Agent: Mozilla/5.0 (OS/2; U; Warp 4.5; de-AT; rv:1.7.7)
>> Gecko/20050425
>

> Mozilla 1.7.7 does display the thread normally.

May I ask why you are using a version that is more than 3 years old?

[Trevor Hemsley]

Yes, this actually looks like a bug in Pronews from what I can see. The subject
line as retrieved from Giganews has a single blank on the next line and this
should really be treated as a continuation of the subject line. Pronews
misidentifies it as an "end of headers" line when it isn't.

[Rainer Stroebel]

Steve Wendt schrieb:

Just useing the news reader function,
the last Mozilla version before starting with Firefox.

Your are right, this component/function does require an update.
It is currently a low priortiy project on my angenda.

Do you see any reason to push it up?

[Rainer Stroebel]

Rainer Stroebel schrieb:

Requirements: Execution Time and HDD Disk Space

Current Size:

Total size of my Firefox 3.0 User Profile is about 56 MB

Size after Vaccum function

2008-07-09 12.02 4603904 0 places.sqlite
2008-07-08 12.02 30810112 0 urlclassifier3.sqlite

Performance on a Thinkpad T23 1.13 GHz - HPFS :

Start of Job Systemzeit: 12.02.13,48
End of Job Systemzeit: 12.03.00,51

Execution Time of Job : 00.00.47,03

The execution time is the "empty" execution time.
This is done on a well physical organized, compacted SQLite DB s.
The vacuum job has been run for testing of the Vacuum and Backup
Function of the procedure several times to day.

Disk requirement of the Procedure:

1. Temporary working space for the vacuum function
on the same drive as the profile
max size from any of SQLite files =

about 31 MB

2. Disk space for 4 generations of User Profile Backup

56 MB x 4 = 224 MB

[Rainer Stroebel]

Rainer Stroebel schrieb:
> Rainer Stroebel schrieb:
>
>> Rainer Stroebel schrieb:
>>
>>>
>>
>>>
>>> Howto to shrink / vacuum / compact the SQLite files.
>>>
>>>
>>> 1. Manual Procedure
>>>
>> -----> snip
>>
>>>
>>> 2. Automatic Procdures to Vacuum the SQLite file a request
>>>
>>> I like to do this via a command file or program execution on request.
>>>
>>
>>
>> Automatic procedure to Vacuum the SQLite files on request
>>

Name created for the the Project / Package :-)

Firefox 3.0 User Profile Save and SQLite Vacuum UPSSV

[Peter Weilbacher]

On 09.07.08 11:46, Rainer Stroebel wrote:
> Steve Wendt schrieb:

>> May I ask why you are using a version that is more than 3 years old?
>
> Just useing the news reader function,
> the last Mozilla version before starting with Firefox.
>
> Your are right, this component/function does require an update.
> It is currently a low priortiy project on my angenda.
>
> Do you see any reason to push it up?

Not sure what newsgroups you are reading but if it's more than this one
then you should be aware that Mozilla 1.7.7 has probably about 50
critical security problems in the MailNews area that are all fixed in
the latest Thunderbird 2.0.0.x and SeaMonkey 1.1.x releases.

[Rainer Stroebel]

Peter Weilbacher schrieb:

> On 09.07.08 11:46, Rainer Stroebel wrote:
>
>> Steve Wendt schrieb:
>>
>>> May I ask why you are using a version that is more than 3 years old?
>>
>>
>> Just useing the news reader function,
>> the last Mozilla version before starting with Firefox.
>>
>> Your are right, this component/function does require an update.
>> It is currently a low priortiy project on my angenda.
>>
>> Do you see any reason to push it up?
>
>
> Not sure what newsgroups you are reading but if it's more than this one
> then you should be aware that Mozilla 1.7.7 has probably about 50
> critical security problems in the MailNews area that are all fixed in
> the latest Thunderbird 2.0.0.x and SeaMonkey 1.1.x releases.

Thanks for the hint.

Moved the Thunderbird installation up on my to do list.

[Rainer Stroebel]

Ruediger Ihle schrieb:

> On Tue, 8 Jul 2008 06:58:07 UTC, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>
>>Based on my testing with 3.4.2 port, current directory does not work on
>>that port.
>
>
> Maybe. But we are talking about 3.5.9.
>
>
>
>>Done some google and found an hint, Linux works with USER_HOME value
>>for SQLite3.
>
>
> Done some source code search and found absolutely no reference to
> USER_HOME. Not on Linux and not on any other supported platform.
>
> As David stated: The shell program (not the engine itself) is
> investigating %USERPROFILE%, %HOME% and the combination of
> %HOMEDRIVE% and %HOMEPATH% to identfy the user's home directory.
> If none of the above is defined, it falls back to C:\.
>
> If the "-init" parameter is not present on the command line, the
> shell tries to load and execute a file named ".sqliterc" from the
> home directory. When running in interactive mode, the home directory
> is also used to maintain a command history (file ".sqlite_history")
> on some platforms. But I don't think we have that enabled in OS/2...
>
>
> In no case the home directory is used to locate a database file.
>
>
>

Okay, lets talk about the "shell" SQLite3 and not about the engine

What does the values %USERPROFILE%, %HOME%, %HOMEDRIVE% and %HOMEPATH%
and Drive "C" are relevant for the Current OS/2 port.

I just have an example:

My environment:
---------------

1. My installation does not have a C drive :-)

2. The shell SQLite3.exe is found by searching the Path env.

3. The DLL are found via the BeginLibDef setting.

4. The SQLite3.exe does work, it displays the version no according the
-version option.

5. The File form the -init is not found a the example.

6. The DPath does give access to the file, the shell does not evaluate
the DPath.

Test call and output:
---------------------

[S:\MOF-3000]SQLite3.exe -bail -batch -version -init
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\content-prefs.sqlite
3.5.9

[S:\MOF-3000]

[Rainer Stroebel]

Rainer Stroebel schrieb:

> Ruediger Ihle schrieb:
>
>> On Tue, 8 Jul 2008 06:58:07 UTC, Rainer Stroebel
>> <RainerxxS...@xxt-online.de> wrote:
>>

---> snip

additional test cases:

case 0: working as expected, -init filename
---------------------------------------------
result: The file is found, addressed with "absolute" path

[S:\MOF-3000]SQLite3.exe -bail -batch -version -init

s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\downloads.sqlite
-- Loading resources from
s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
PRAGMA legacy_file_format;

1

case 1: set USERPROFILE=S:\UPSSV-V-1-0
---------------------------------------
result: -init filename filename in dir S:\UPSSV-V-1-0 not found

[S:\MOF-3000]set USERPROFILE=S:\UPSSV-V-1-0
[S:\MOF-3000]

[S:\MOF-3000]set USERPROFILE
USERPROFILE=S:\UPSSV-V-1-0
[S:\MOF-3000]
[S:\MOF-3000]rem

[S:\MOF-3000]SQLite3.exe -bail -batch -version -init
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\content-prefs.sqlite
3.5.9

[S:\MOF-3000]

Case 2: set HOME=S:\UPSSV-V-1-0
----------------------------------
result: -init filename filename in dir S:\UPSSV-V-1-0 not found

[S:\MOF-3000]set HOME=S:\UPSSV-V-1-0
[S:\MOF-3000]

[S:\MOF-3000]set HOME
HOME=S:\UPSSV-V-1-0
[S:\MOF-3000]

[S:\MOF-3000]rem

[S:\MOF-3000]SQLite3.exe -bail -batch -version -init
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL

S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\search.sqlite
3.5.9

Case 3: set current directory to the dir containing the File
-------------------------------------------------------------
restult: works

S:\MOF-3000]s:

[S:\MOF-3000]cd \UPSSV-V-1-0

[S:\UPSSV-V-1-0]rem

[S:\UPSSV-V-1-0]SQLite3.exe -bail -batch -version -init

UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\content-prefs.sqlite

-- Loading resources from
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
PRAGMA legacy_file_format;

1

conclusio:

Does it make sense for the OS/2 port to request the OS/2 DPath
functionality?

[Rainer Stroebel]

Rainer Stroebel schrieb:

> Rainer Stroebel schrieb:
>
>> Ruediger Ihle schrieb:
>>
>>> On Tue, 8 Jul 2008 06:58:07 UTC, Rainer Stroebel
>>> <RainerxxS...@xxt-online.de> wrote:
>>>
> ---> snip
>
>>>

Additional test cases:

Using the sqlite3 shell command .read filename

------------------------------------
case 1. filename with absolute path
-------------------------------------
result: does not work --> "/" in path ignored

[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe

SQLite version 3.5.9
Enter ".help" for instructions

sqlite> .help
.bail ON|OFF Stop after hitting an error. Default OFF

---> snip

.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.schema ?TABLE? Show the CREATE statements

---> snip

.width NUM NUM ... Set column widths for "column" mode
sqlite> .echo ON
sqlite> .read
s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL

.read s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
can't open
"s:UPSSV-v-1-0UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL"
sqlite>

-------------------------------------------
case 2. filename found in current directory
--------------------------------------------
result: Works

[S:\upssv-v-1-0]S:\download\os2\SQLite\V3-5-9\SQLite3.exe

SQLite version 3.5.9
Enter ".help" for instructions

sqlite> .read UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;
PRAGMA cache_size;
2000
PRAGMA database_list;
0|main|
PRAGMA main.freelist_count;
0
vacuum;
PRAGMA main.freelist_count;
0
.quit
sqlite>

[Ruediger Ihle]

On Fri, 11 Jul 2008 16:46:50 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> What does the values %USERPROFILE%, %HOME%, %HOMEDRIVE% and %HOMEPATH%
> and Drive "C" are relevant for the Current OS/2 port.

If you do *NOT* specify "-init filename", the shell tries to load
and execute a file named ".sqliterc" from what it thinks is the
user's home directory. In other words: you can have an init script,
that is executed automatically whenever SQLite3.exe is started.

That's it. Not more and not less.

[Ruediger Ihle]

On Fri, 11 Jul 2008 18:37:51 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> Using the sqlite3 shell command .read filename

> ...

> result: does not work --> "/" in path ignored

Wrong. "\" is handled in a special way, not "/".

You have two choices:

1.) use forward slashes as path separator (.read C:/yourpath/yourfile)
2.) use double backslash as path separator (.read
C:\\yourpath\\yourfile)

[Rainer Stroebel]

Ruediger Ihle schrieb:

> On Fri, 11 Jul 2008 18:37:51 UTC, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>>Using the sqlite3 shell command .read filename
>>...
>>result: does not work --> "/" in path ignored
>
>
> Wrong. "\" is handled in a special way, not "/".
>
> You have two choices:
>
> 1.) use forward slashes as path separator (.read C:/yourpath/yourfile)
> 2.) use double backslash as path separator (.read
> C:\\yourpath\\yourfile)
>
>
>

okay, we have a working solution: :-)
Works both with the current port OS/2

Test 1: use forward slashes as path separator
----------------------------------------------

[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe
SQLite version 3.5.9
Enter ".help" for instructions

sqlite> .read
s:/upssv-v-1-0/UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL

PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;
PRAGMA cache_size;
2000
PRAGMA database_list;
0|main|
PRAGMA main.freelist_count;
0
vacuum;
PRAGMA main.freelist_count;
0
.quit
sqlite>

Test 2: use double backslash as path separator
----------------------------------------------

[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe
SQLite version 3.5.9
Enter ".help" for instructions

sqlite> .read
s:\\upssv-v-1-0\\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL

[Rainer Stroebel]

Ruediger Ihle schrieb:

> On Fri, 11 Jul 2008 16:46:50 UTC, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
>
>>What does the values %USERPROFILE%, %HOME%, %HOMEDRIVE% and %HOMEPATH%
>>and Drive "C" are relevant for the Current OS/2 port.
>
>
> If you do *NOT* specify "-init filename", the shell tries to load
> and execute a file named ".sqliterc" from what it thinks is the
> user's home directory. In other words: you can have an init script,
> that is executed automatically whenever SQLite3.exe is started.
>
> That's it. Not more and not less.
>
>

okay,

done a test with the ".sqliterc" file in the current directory

[S:\]type .sqliterc
.show
.quit

[S:\]sqlite359

[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe

SQLite version 3.5.9
Enter ".help" for instructions
sqlite>

.show is not executed

the test cases of the my previous posting show

%USERPROFILE%, %HOME% does not work

Do I do something wrong at the test cases?

[Ruediger Ihle]

On Sat, 12 Jul 2008 13:11:51 UTC, Rainer Stroebel
<RainerxxS...@xxt-online.de> wrote:

> done a test with the ".sqliterc" file in the current directory

Why that ???

".sqliterc" has to be in the user's home directory. For OS/2
that means %HOME% or %USERPROFILE% or %HOMEDRIVE%\%HOMEPATH%.
The fallback to C:\ in case none of the above is defined doesn't
work, which is a bug in both OS/2 and Windows. However, it's
questionable anyway...

[Trevor Hemsley]

On Tue, 8 Jul 2008 20:40:55 UTC in mozilla.dev.ports.os2, "Trevor Hemsley"
<Trevor....@mytrousers.ntlworld.com> wrote:

> On Tue, 8 Jul 2008 20:09:46 UTC in mozilla.dev.ports.os2, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
> > may be the trailing CRLR signe had come to the header field of the
> > message by pasting the line to the field.
> >
> > Mozilla 1.7.7 does display the thread normally.
>
> Yes, this actually looks like a bug in Pronews from what I can see. The subject
> line as retrieved from Giganews has a single blank on the next line and this
> should really be treated as a continuation of the subject line. Pronews
> misidentifies it as an "end of headers" line when it isn't.

But turns out to be one of those fixes that spreads and spreads and hits a whole
bunch of stuff... :-(

I think it's now fixed and will try to get a build released sooner rather than
later but need to use it for a while and make sure this hasn't had knock-on
effects in other places.