Re: [sqlite] SQLite DB backups on Windows

[Eric Minbiole]

> What we're thinking of doing is pushing the PENDING_BYTE from the first 
> byte past the 1GB boundary to somewhere deep in the 64-bit range (such 
> as perhaps the 1TB boundary).  We would have to update many lock and 
> unlock calls in os_win.c to do so, mainly adding a high-order 32-bit 
> number to the lock offset.  Is anyone aware of any issues with doing so, 
> either with SQLite or Windows?  Would we have to change anything else in 
> SQLite other than in os_win.c?  We don't use anything older than Windows 
> 2000, so older systems shouldn't be a concern for us.

Might it be simpler to extract the underlying file handle from the 
sqlite3 structure?  Once you have this handle, you could manually 
perform a file copy, reading & writing a block at a time.  Since it's 
the same handle that was granted the lock, you should have no access 
restrictions.  Though I haven't tried it, I would hope that the 
block-by-block copy would have similar performance to the O/S CopyFile call.

Granted, there may not be any "supported" way to extract the file handle 
from the sqlite3 struct.  However, it has to be at least as easy as 
updating the code to move the PENDING_BYTE location, as you propose 
above.  Plus, it avoids the compatibility issues mentioned by DRH.

~Eric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite DB backups on Windows

[D. Richard Hipp]

On Aug 1, 2008, at 3:14 PM, Arun Bhalla wrote:

> Hi there,
>
> We are performing backups of the SQLite DB file by opening an  
> IMMEDIATE
> transaction and then copying the file.  (cf.
> ,
> )
>
> On Windows we use CopyFileA (within the same process that opened the  
> DB
> transaction) to perform the copy.  We've noticed that copies fail on
> Windows for databases larger than 1 GB in size.  We suspect this is  
> due
> to SQLite locking bytes around the 1GB offset whenever a transaction  
> is
> opened on Windows.  This may be due to the fact that the locking file
> handle gets exclusive access; other file handles opened by the same
> process also cannot access the same locked region. (cf.
> )
>
> What we're thinking of doing is pushing the PENDING_BYTE from the  
> first
> byte past the 1GB boundary to somewhere deep in the 64-bit range (such
> as perhaps the 1TB boundary).  We would have to update many lock and
> unlock calls in os_win.c to do so, mainly adding a high-order 32-bit
> number to the lock offset.  Is anyone aware of any issues with doing  
> so,
> either with SQLite or Windows?  Would we have to change anything  
> else in
> SQLite other than in os_win.c?  We don't use anything older than  
> Windows
> 2000, so older systems shouldn't be a concern for us.


You can change it and it will probably work.  But the resulting SQLite  
library will not be compatible with the standard SQLite library.  If  
you application has the database locked, the sqlite3.exe CLI that you  
download from the SQLite website will not realize this (because the  
locks are in the wrong place) and trouble will result.


D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite DB backups on Windows

[Arun Bhalla]

I forgot to mention that we're using SQLite 3.5.9.

Arun

Arun Bhalla wrote:
> Hi there,
> 
> We are performing backups of the SQLite DB file by opening an IMMEDIATE 
> transaction and then copying the file.  (cf. 
> , 
> )
> 
> On Windows we use CopyFileA (within the same process that opened the DB 
> transaction) to perform the copy.  We've noticed that copies fail on 
> Windows for databases larger than 1 GB in size.  We suspect this is due 
> to SQLite locking bytes around the 1GB offset whenever a transaction is 
> opened on Windows.  This may be due to the fact that the locking file 
> handle gets exclusive access; other file handles opened by the same 
> process also cannot access the same locked region. (cf. 
> )
> 
> What we're thinking of doing is pushing the PENDING_BYTE from the first 
> byte past the 1GB boundary to somewhere deep in the 64-bit range (such 
> as perhaps the 1TB boundary).  We would have to update many lock and 
> unlock calls in os_win.c to do so, mainly adding a high-order 32-bit 
> number to the lock offset.  Is anyone aware of any issues with doing so, 
> either with SQLite or Windows?  Would we have to change anything else in 
> SQLite other than in os_win.c?  We don't use anything older than Windows 
> 2000, so older systems shouldn't be a concern for us.
> 
> Thanks,
> Arun
> ___
> 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] SQLite DB backups on Windows

[Arun Bhalla]

Hi there,

We are performing backups of the SQLite DB file by opening an IMMEDIATE 
transaction and then copying the file.  (cf. 
, 
)

On Windows we use CopyFileA (within the same process that opened the DB 
transaction) to perform the copy.  We've noticed that copies fail on 
Windows for databases larger than 1 GB in size.  We suspect this is due 
to SQLite locking bytes around the 1GB offset whenever a transaction is 
opened on Windows.  This may be due to the fact that the locking file 
handle gets exclusive access; other file handles opened by the same 
process also cannot access the same locked region. (cf. 
)

What we're thinking of doing is pushing the PENDING_BYTE from the first 
byte past the 1GB boundary to somewhere deep in the 64-bit range (such 
as perhaps the 1TB boundary).  We would have to update many lock and 
unlock calls in os_win.c to do so, mainly adding a high-order 32-bit 
number to the lock offset.  Is anyone aware of any issues with doing so, 
either with SQLite or Windows?  Would we have to change anything else in 
SQLite other than in os_win.c?  We don't use anything older than Windows 
2000, so older systems shouldn't be a concern for us.

Thanks,
Arun
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users