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://sqlitebrowser.sourceforge.net/index.html
http://www.sqlite.org/lang_vacuum.html
http://rexxsql.sourceforge.net/
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?
>> 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.).
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
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!
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
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
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
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
> 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/
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:
=== 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:
>
> 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]
[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
> 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
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!!
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:\]
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
> 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.
> 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 ;-)
> 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
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.
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.
> 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...
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.
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
>
> 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
(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
> 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.