Hi,
As per the link http://www.sqlite.org/lang_vacuum.html , SQLite claims that
"*The VACUUM command works by copying the contents of the database into a
temporary database file and then overwriting the original with the contents
of the temporary file*." In this phrase "*The VACUUM command works by
copying the contents of the database into a temporary database file*" seem
to be working fine where as " *then overwriting the original with the
contents of the temporary file*" is not.
As i tested, sqlite creates temp file but it does not overwrite the original
file with temp file content.
I am using sqlite-amalgamation-3_7_3.zip with WIN32 configuration.
Please help me to find the solution for this problem.
Thanks,
Sudha
On Thu, Feb 24, 2011 at 3:04 PM, Sudha Venkatareddy <sudha....@gmail.com>wrote:
> Hi,
>
> I fixed the code in such a way that the temp files which are created with
> below flags are deleted during close.
>
> dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY
> | FILE_ATTRIBUTE_HIDDEN
> | FILE_FLAG_DELETE_ON_CLOSE;
>
>
> so..
>
> * Problem 1. *Running VACUUM leaves 3 temporary files in the temp
> directory
> which are not deleted when main DB is closed.* - Fixed*
>
> *Problem 2.* Upon applying VACUUM command on say main DB file MyDb.db ,
> and
> closing the main DB connection, the size of the main DB file MyDb.db does
> not change where as one of the temp file(etilqs_*) will actually contain
> the
> reduced size of the same data as of main DB file. - *Not Fixed
>
> *Here i am attaching the MyDb.db as well as temp file "etilqs_*" which
> gets created while running VACUUM command. Its content is same as MainDb.db
> except the file size.
> Expected output: content and file size of Mydb.db should be same as
> etilqs_Hm4RUi6JPXcMZ17 and file name should be remained as MyDb.db.
>
> Please help me in fixing this problem.
>
> Thanks,
> Sudha
>
>
> On Thu, Feb 24, 2011 at 11:50 AM, Sudha Venkatareddy
> <sudha....@gmail.com>wrote:
>
>> Hi,
>>
>> The porting is done in little tricky way due to limited support from
>> underlying platform.
>>
>> Ported code does not completely follow WINDOWS. it is WINDOWS + WINCE
>> configuration.
>>
>> Porting is done as below.
>>
>> *Step 1.* Main macros defnined in the source include:
>>
>> #define SQLITE_DEBUG 0
>> #define SQLITE_OS_OTHER 1
>> #define SQLITE_MUTEX_OTHER_OS 1
>> #define SQLITE_CORE 1
>> #define SQLITE_AMALGAMATION 1
>> SQLITE_OS_OTHER is same as SQLITE_OS_WIN
>>
>> *Step 2.* SQLITE_OS_WINCE is not defined but isNT() is defined as 1.
>> #define isNT() 1
>>
>> Here is the code snippet of otherOsClose() API for your reference.
>> ====================================================
>> #define MX_CLOSE_ATTEMPT 3
>> static int otherOsClose(sqlite3_file *id){
>> int rc, cnt = 0;
>> otherOsFile *pFile = (otherOsFile*)id;
>> assert( id!=0 );
>> assert( pFile->pShm==0 );
>> OSTRACE2("CLOSE %d\n", pFile->h);
>> do{
>> rc = CloseHandle(pFile->h);
>> /* SimulateIOError( rc=0; cnt=MX_CLOSE_ATTEMPT; ); */
>> }while( rc==0 && ++cnt < MX_CLOSE_ATTEMPT && (Sleep(100), 1) );
>> #if SQLITE_OS_WINCE
>> #define WINCE_DELETION_ATTEMPTS 3
>> winceDestroyLock(pFile);
>> if( pFile->zDeleteOnClose ){
>> int cnt = 0;
>> while(
>> DeleteFileW(pFile->zDeleteOnClose)==0
>> && GetFileAttributesW(pFile->zDeleteOnClose)!=0xffffffff
>> && cnt++ < WINCE_DELETION_ATTEMPTS
>> ){
>> Sleep(100); /* Wait a little before trying again */
>> }
>> free(pFile->zDeleteOnClose);
>> }
>> #endif
>> OSTRACE3("CLOSE %d %s\n", pFile->h, rc ? "ok" : "failed");
>> OpenCounter(-1);
>> return rc ? SQLITE_OK : SQLITE_IOERR;
>> }
>> ============================================================
>>
>> With configuration steps 1 and 2 as mentioned above, will there be any
>> problems?
>>
>> Since SQLITE_OS_WINCE is not defined as 1, the file will not be deleted on
>> close. I can make some hack to enable pFile->zDeleteOnClose and modify the
>> ported code to delete the file if pFile->zDeleteOnClose is true without
>> actually defining macro SQLITE_OS_WINCE.
>>
>> SQLITE_OS_WINCE can not be enabled since it requires winceLocks to be
>> implemented which can not be supported at the moment.
>>
>>
>> Thanks,
>> Sudha
>>
>> On Wed, Feb 23, 2011 at 8:14 PM, Shane Harrelson <sh...@sqlite.org>wrote:
>>
>>> Hi-
>>>
>>> On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
>>> temporary files automatically deleted after they are closed. WINCE
>>> doesn't support this flag, so you will see special logic in os_win.c,
>>> wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
>>> files. You mentioned in an earlier post that you had ported to your
>>> platform based on this code. Could you check that your ported code
>>> includes this logic?
>>>
>>> -Shane
>>>
>>> On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy <sudha....@gmail.com>
>>> wrote:
>>> > Hi,
>>> >
>>> > I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
>>> > it is slightly related to it but the temporary files are created while
>>> > running VACUUM command.
>>> > ---------------------------------------------------------------
>>> > Ticket 2829:
>>> >
>>> > This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
>>> >
>>> > if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
>>> > - | SQLITE_OPEN_SUBJOURNAL) ){
>>> > + | SQLITE_OPEN_SUBJOURNAL |
>>> SQLITE_OPEN_DELETEONCLOSE) ){
>>> >
>>> >
>>> ------------------------------------------------------------------------------
>>> >
>>> > The temp files were created in the below call sequence:
>>> >
>>> > -----------------------------------------------------------------
>>> > 62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
>>> > 61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
>>> > 60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
>>> > 59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
>>> > 58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
>>> > 57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
>>> > 56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
>>> > 55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
>>> > 54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
>>> > 53 sqlite3Step() sqlite3.c:59380 0x3af87b34
>>> > 52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
>>> > 51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
>>> > ------------------------------------------------------------------
>>> >
>>> >
>>> >
>>> > Basically there 2 problems associated when i run VACUUM command.
>>> > Problem 1. Running VACUUM leaves 3 temporary files in the temp
>>> directory
>>> > which are not deleted when main DB is closed.
>>> > Problem 2. Upon applying VACUUM command on say main DB file MyDb.db ,
>>> and
>>> > closing the main DB connection, the size of the main DB file MyDb.db
>>> does
>>> > not change where as one of the temp file(etilqs_*) will actually
>>> contain the
>>> > reduced size of the same data as of main DB file.
>>> >
>>> > I am not sure if this is the expected behaviour or there is some bug in
>>> the
>>> > flow.
>>> >
>>> > Please let me know if there is a solution to resolve this issue.
>>> >
>>> > Thanks,
>>> > Sudha
>>> >
>>> > On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin <slav...@bigfraud.org>
>>> wrote:
>>> >
>>> >>
>>> >> On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
>>> >>
>>> >> > *Actual output: MyDb.db remains size 23KB(size not changes from
>>> original)
>>> >> > and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same
>>> as
>>> >> > MyDb.db but the size is reduced to 13KB*
>>> >>
>>> >> Your problem is probably related to
>>> >>
>>> >> http://www.sqlite.org/cvstrac/tktview?tn=2829
>>> >>
>>> >> . It's quite legitimate for your symptoms to occur while the database
>>> >> handle is still open but you should not be seeing those files after
>>> you have
>>> >> closed the connection to the database. Either you are not closing the
>>> >> database connection properly, or some part of the API you're using is
>>> not
>>> >> closing the database connection properly.
>>> >>
>>> >> I'm not familiar with how this problem manifests because I don't use
>>> >> Windows, so I'll leave it up to an expert to tell you if it needs
>>> fixing
>>> >> somehow.
>>> >>
>>> >> Simon.
>>> >> _______________________________________________
>>> >> sqlite-users mailing list
>>> >> sqlite-users@sqlite.org
>>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> >>
>>> > _______________________________________________
>>> > sqlite-users mailing list
>>> > sqlite-users@sqlite.org
>>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>>
>>
>>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
