Re: [sqlite] SQLite on Windows 2003

2010-04-07 Thread Mark Spiegel
Burnett, Joe wrote:
> Hi Teg,
>
> UTF-8, no special characters in the file name WorkData.s3db.
>
> Thanks,
>
> Joe 
>
> Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
> TECHNOLOGY
> 2 Contra Way
> Merrimack, NH 03054
> 603.791.5113
> cell: 603.289.0481
>   
If you can debug your application, more specifically SQLite, set a 
breakpoint in winOpen() and find out what return code CreateFile() is 
returning for the various calls.  That along with the arguments passed 
should help you work it out.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Q. about core SQLite library

2010-01-13 Thread Mark Spiegel


Sorry, Mr. Gold should contact DRH and ask about the encryption 
extension.  My error.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Q. about core SQLite library

2010-01-13 Thread Mark Spiegel

>   I was under the impression that is how SEE works.  Hooking the SQLite
>   Virtual File System interface seems like a very straight forward method.
>   All you really need to do is splice your code between the VFS that
>   the SQLite engine sees and the native VFS layer that comes with the
>   distribution, adding a bit of extra code to xRead() and xWrite() to
>   munge the data.  I've never tried it but the documentation makes it
>   look like something you could do without modification to the core
>   source and with very little code other than the actual encrypt/decrypt
>   routines, plus the interfaces required to configure them.  No actual
>   file system code, nor any OS specific code.
>   
>   In theory.  8-)
>
>   http://sqlite.org/c3ref/vfs.html
>   http://sqlite.org/c3ref/io_methods.html
>
>   
>
No, it is probably more correct to say that the SEE plugs into the 
pager.  It does not layer the VFS.  Look where the CODEC1 and CODEC2 
macros are used.

Mr. Kreibich should contact DRH and ask about the encryption extension.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Q. about core SQLite library

2010-01-13 Thread Mark Spiegel


Jay A. Kreibich wrote:
> On Tue, Jan 12, 2010 at 02:53:55PM -0500, Ray Gold scratched on the wall:
>   
>> Hello.
>>
>> My name is Ray Gold with Berliner, Corcoran & Rowe, LLP in Washington, DC.
>>
>> I am writing to ask if the core SQLite library (not the proprietary 
>> SQLite Extensions) has any encryption/decryption capabilities.
>> 
>
>   No.
>
>   However, the SQLite Encryption Extension is maintained by the same
>   people that maintain the core library.  It is "proprietary" only in
>   the sense that it is commercial, licensed software.
>
>   The core library does provide hooks to the OS layer and the
>   file-system layer.  It is possible to write your own encryption
>   layer if the extension did not meet your needs.
>
>-j
>
>   
I would avoid using the OS layer.  While you could implement your own 
encryption at the file system level, the license for the encryption 
extension is relatively inexpensive and maintained for you.  As I 
understand it, once purchased, it is good indefinitely.  Further, if it 
doesn't meet your encryption needs (unlikely) you could extend the 
encryption extension easier than adding encryption at the file system level.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
D. Richard Hipp wrote:
> Most varints are "type varints" and type varints are almost always a  
> single byte (the only exceptions being for large blobs or strings).   
> Varints are also used to store the total number of bytes in a row  
> (also usually one byte).  Most varints are a single byte.
>
> We, too, have profiled, and we agree that a lot of time is spent  
> decoding varints.  As you have already observed, the common case of a  
> single-byte varint is usually handled by by a macro and so never calls  
> the sqlite3GetVarint() decoder function.  And sqlite3GetVarint() is  
> very carefully coded to be fast even when it is called.  The varint  
> decoder is one of the more carefully scrutinized parts of SQLite.
>
> I'm scanning through some profiling output now and I'm seeing that  
> some varints are almost always a single byte (only 60 multibyte  
> varints out of 474350, in one example) while others are multibyte  
> about half the time.  I'm not seeing any cases where more then half  
> the varints are multibyte.
>
>
>   
Sorry I missed the obvious.  Multi-byte about half the time.  Read the 
numbers, skipped the words.  My grade school teachers would not be 
surprised. 

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


Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
D. Richard Hipp wrote:
> Most varints are "type varints" and type varints are almost always a  
> single byte (the only exceptions being for large blobs or strings).   
> Varints are also used to store the total number of bytes in a row  
> (also usually one byte).  Most varints are a single byte.
>
> We, too, have profiled, and we agree that a lot of time is spent  
> decoding varints.  As you have already observed, the common case of a  
> single-byte varint is usually handled by by a macro and so never calls  
> the sqlite3GetVarint() decoder function.  And sqlite3GetVarint() is  
> very carefully coded to be fast even when it is called.  The varint  
> decoder is one of the more carefully scrutinized parts of SQLite.
>
> I'm scanning through some profiling output now and I'm seeing that  
> some varints are almost always a single byte (only 60 multibyte  
> varints out of 474350, in one example) while others are multibyte  
> about half the time.  I'm not seeing any cases where more then half  
> the varints are multibyte.
>
>   
Right.  I observed that in the single byte case, the macro prevents the 
calling of the varint funtions.  The last time I profiled this was after 
Shane worked it over last year.  I believe the actual functions (not 
code generated by the macro) accounted for about 6% of the time spent in 
SQLite during our performance test suite.  Obviously, that is going to 
be highly variable depending on the type of data contained, and the 
types of  operations performed, etc.   Clearly the varints in some 
cases, will be almost exclusively single byte.  What I'm more curious 
about is their overall usage.  Are we looking at 60/474350 for the 
entire database (in your example) or just one particular use within the 
database?

FWIW, I experimented with several different encoding schemes that 
preserved the single byte properties and was quickly able to cut the 
time consumed in our profiling test in half.  Unfortunately, they all 
broke compatibility.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
D. Richard Hipp wrote:
> You are both right and both wrong.  There are two different integer  
> representations used in SQLite.
>
> (1) "varint" or variable length integer is an encoding of 64-bit  
> signed integers into between 1 and 9 bytes.  Negative values use the  
> full 9 bytes as do large positive values.  But small non-negative  
> integers use just one or two bytes.  Varints are used in places where  
> integers are expected to be small and non-negative, such as record  
> sizes in the btree (usually less than 100 bytes) and also for rowids.
>
> (2) When you store an integer into a column (a column other than the  
> rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed  
> integer.  The smallest possible representation is used, depending on  
> the magnitude of the integer.  The size used is actually recorded in a  
> separate varint (the "type" varint) that also determines that the  
> value stored is an integer and not (say) a string or blob or floating  
> point number or NULL.  A type varint of 1 means store a 1-byte  
> integer.  A type varint of 2 means store a 2-byte integer.  And so  
> forth.  A type varint of 8 (I think) means the value is exactly 0 so  
> don't store anything.  9 means the value is exactly 1.  And so forth.   
> Notice that the type varints are all small integers and are thus  
> themselves represented by a single byte.  Every value stored has a  
> type varint.  Additional data is stored as necessary.  A zero-byte  
> string or blob stores uses no space beyond its type varint.  A NULL  
> uses no space beyond its type varint. A numeric 0 or 1 uses no space  
> beyond its type varint.  An integer between -127 and +127 uses 1  
> additional byte beyond its varint.  A 1MB blob uses a million bytes of  
> additional space beyond its type varint.  And so forth.
>
>   
That makes sense.  Thank you for clarifying.  One further question.  It 
seems when we profile, that a lot of time is spent encoding and decoding 
varints.  Are there really that many multi-byte varints in use in the 
system? 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
Jay A. Kreibich wrote:
>   Integer values between -128 and 127 use only a single byte of storage above 
> and beyond  the header size that all values have.
>   
Not quite.  Values between 0 & 127 use 1 byte of storage.  Negative 
values use the full 9 bytes in my experience.  (I'm setting aside the 
integer 0 and integer 1 optimizations outlined in 
http://www.sqlite.org/fileformat.html#record_format with that statement.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

2009-07-08 Thread Mark Spiegel

Stan Bielski wrote:
> Hello again,
>
> Copying the database with Explorer and accessing it via sqlite is just
> a contrived example that exhibits the same problem I'm having in my
> application. The app does make a copy of the DB, but it has app-layer
> locking that will prevent modifications unless someone decides to
> start fiddling with the DB outside of my software (in which case I
> have bigger problems). The app is multi-threaded, and a thread other
> than the copying thread may attempt to open the DB and read from it
> while the copy is occurring.
>
> My contrived example aside, I just discovered that issuing the VACUUM
> command on the same 20 GB DB in sqlite3 causes similar memory issues,
> even when another process is not accessing the database file.
> sqlite3.exe has a peak working set of 40 MB in Task Manager, but
> Resource Monitor reports 99% Used Physical Memory (of 4 GB). If it
> were all buffer cache, I'd expect that simply copying the file would
> result in the same amount of memory being used, but it doesn't. I'm
> going to head to a Windows forum to try to find out more about what's
> happening, but the list users may want to be aware of this if they
> plan on using large sqlite DBs with Windows 2008.
>
> Thanks,
> -Stan
>
>
>   

Maybe. 

First, forget what I said about mapping the file.  That didn't make 
sense just minutes after I hit the send button.  I was a few cups of 
coffee shy of fully awake.

I just haven't done enough work with Win 2008 yet to be able to say with 
certainty what is going on.  However, here are a couple more things to 
consider.  First, it used to be in windows that the amount of address 
space (and RAM) that could be used for various things was fixed.  This 
is no longer the case.  What you _may_ be seeing is that in trying to 
help, Windows is allocating as much address space (and RAM) as it can to 
the cache manager.  The file is not opened for unbuffered access so NTFS 
is going to try to use the cache manager on the file.  I don't know if 
any of the user mode tools will tell you this.  If you have a kernel 
debugger attached to the machine in this state, the "!VM" command 
_might_ shed light on how much address space is allocated for what.

Second, it occurred to me that in the nominal copy case where you don't 
see a lot of RAM being consumed, explorer, knowing that it is just 
sequentially copying a file, may have it opened for unbuffered access 
for both source and destination.  This would bypass the cache manager 
completely.  I've implemented copy this way in the past to prevent some 
of the adverse effects of large copies on the system, but I have no idea 
if explorer would use this technique.  The downside is that for files 
already in use, copy can be a bit slower.  You can find out with filemon.

Wish I could offer more in the way of a solution...


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


Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

2009-07-08 Thread Mark Spiegel

> Just to be clear, I think Windows is really the one that is at fault
> here; the behavior seems very broken to me. Unfortunately, I need the
> app to work on Windows without this happening, so I need to figure out
> some kind of workaround in a.) Windows settings b.) the sqlite source
> or c.) how my app is using sqlite.
>
> Thanks for your help,
> -Stan
>   

Yes, filemon can be extremely useful.  As you've seen, it can really 
help with ordering and sharing problems.

If you think about it, it makes sense that explorer would want to open 
the file only allowing read sharing.  As others have pointed out, you 
wouldn't want an inconsistent copy of the file.  Allowing only shared 
read access is the easiest way to get that.  Once that first opens 
fails, explorer seems to have a fall back plan.  I can only speculate at 
this point what that is.  If I simply had to guess, I would say that 
explorer might be creating a memory map of the file, possibly with copy 
on write semantics.  It could then read from the map to perform the 
copy.  Writes outside explorer would automagically not be seen by the 
process performing the copy, in this case explorer.  It's the first 
thing that comes to mind that might chew up so much RAM/page file.  
However that's just a guess. 

It looks like explorer is making an honest effort to see that you don't 
get an inconsistent copy of the file.  IMO, that isn't broken, but 
desired in the generic case.  Making consistent copies of giant files 
that are in use elsewhere is not what explorer was made for.  That's 
more of a job for a backup application.

If you simply must go down this route, I would try to post your question 
(in a more generic, less SQLite centric form) to one of the Windows 
internals forums, maybe an NTFS forum like NTFSD over at OSR online, 
etc.  You might look for a forum on MSFT's site.  I'm sure someone has 
already been down this road and can give you a specific explanation.

In the meantime, you may want to look at the backup interface provided 
by SQLite.  This should avoid any problems with getting an inconsistent 
snapshot which you just can't avoid with explorer even if you solve the 
RAM issue.  You could write a command line program that just performs a 
copy on a database in short order to use in place of explorer.  Someone 
here could probably post pseudo code for that in just a few minutes.  
Maybe this presents its own set of problems, I don't know.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

2009-07-07 Thread Mark Spiegel
Stan Bielski wrote:
> In the course of copying a largish (20 GB) database file while
> accessing it via sqlite3, the machine became very unresponsive. I
> opened task manager and found that the system was using a huge amount
> of virtual memory, causing it to thrash. Per-process memory usage
> looked normal and did not add up to anywhere near system-wide VM
> usage.
>   
If you can, use the filemon tool:

http://technet.microsoft.com/en-us/sysinternals/bb896642.aspx

This should give you a picture of who is opening the file and with what 
flags.  In particular, how is your SQLite app and Explorer opening the file?

Filemon can generate a painful amount of output, but it may be worth it 
to see what is going on.

Do you have task manager set to show processes from all users?

When you say "huge amount of virtual memory", what exactly do you mean?  
What statistic in task manager are you referring to?

When you get in this situation, what process is using the most CPU?  Is 
the system CPU bound or I/O bound?

Don't forget the perfmon tool.  It can be helpful in figuring out these 
kinds of problems as well.

When you say the system is sluggish, does that mean the mouse is 
sluggish?  Apps won't start?  IE is unresponsive?  Can you be a little 
more specific?  Note that when copying or even using big files like 
this, you can overrun the cache manager.  It will be filled with data 
from your file forcing out everything else of use.  It can take awhile 
for the system to recover from this.  Some AV products will create this 
situation when they do a system wide scan for example.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Reducing the size of executable linked with sqlite3.c

2009-06-09 Thread Mark Spiegel
http://www.sqlite.org/compile.html

There are options you can use to disable/remove unused features.  Some 
can give significant size savings.  (I did this on Windows, YMMV.)  
Unfortunately, you can't just use these with the amalgamated source.  
You will probably have to rebuild it.  It's not hard once you get it 
figured out.  There is another doc page that describes how to do that, 
but I don't see it off hand.

chandan wrote:
> Hi,
> I am using the "Amalgamation" version of SQLite. I wanted to know 
> the compile time options (if any) to reduce the size of the executable 
> that is linked with sqlite3.c file.
>
> Regards,
> chandan
> ___
> 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


Re: [sqlite] 64 bit compatibility of sqlite 3.5.7

2009-06-04 Thread Mark Spiegel
For 32 bits it should be a long, for 64 bites, a 64 bit integer type.  
For example __int64 in Windows.

sqlite3_intptr_t has to be large enough to hold the larger of an integer 
or a pointer on each platform.  We had to fix this too...

Gopala Surya wrote:
> Hi All
> We have been using sqlite version 3.5.7 for our development in a 32
> bit environment. We are moving to 64 bit and I  am trying to build
> sqlite3.c.
> I see that sqlite3.c ver 3.5.7 has a typedef as follows:
>
> typedef int sqlite3_intptr_t;
>
> This causes the compiler to complain about incompatibilities in
> pointer and int sizes in 64-bit
> Now am I safe in changing int to long as in
>
> typedef long sqlite3_intptr_t;
>
> After this change I see that my 64 bit build goes through, but I am
> not sure as to what other assumptions the code makes regarding these
> data types.
> Any help shall be greatly appreciated.
> Thanks
> -Gopala
> ___
> 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


Re: [sqlite] SQLite version 3.6.14

2009-05-08 Thread Mark Spiegel
Based on what I've read, it certainly could.  As I understand it, there 
is a single queue for all the writes so the writes for multiple 
databases, journals, etc. are going to that single queue.  Based on 
that, it is certainly "possible" that your overall throughput and 
throughput for any individual database could be negatively affected.

Note that my file system knowledge is restricted to Windows platforms.  
Please don't extend my speculation to other platforms.  I also note that 
I have done no performance testing with this VFS myself and have seen no 
data provided.

Doug wrote:
> Would this perhaps affect throughput in the case where multiple database
> files are open?  For example, I have a handful of databases (10?) that are
> open at any given time, and reads and writes are taking place on separate
> threads.  Naturally writes that happen to the same database ultimately get
> serialized by the database-level locks, but writes to other databases
> continue to work.  But using the async feature would serialize all reads and
> writes to all databases, is that correct?
>
> Thanks
> Doug
>
>   
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Christian Smith
>> Sent: Friday, May 08, 2009 7:24 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite version 3.6.14
>>
>> On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote:
>> 
>>> On May 8, 2009, at 5:21 PM, Christian Smith wrote:
>>>
>>>   
 On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote:
 
> A new optional extension is included that implements an
> asynchronous I/
> O backend for SQLite on either windows or unix.  The asynchronous
>   
>> I/O
>> 
> backend processes all writes using a background thread.  This
>   
>> gives
>> 
> the appearance of faster response time at the cost of durability
>   
>> and
>> 
> additional memory usage.  See http://www.sqlite.org/asyncvfs.html
>   
>> for
>> 
> additional information.
>   
 What are the benefits of using async I/O over "PRAGMA synchronous =
 OFF"?
 If AIO is used for the rollback journal as well, you've lost your
 
>> ACID
>> 
 properties already, so you may as well just use "PRAGMA synchronous
 =  OFF"
 anyway and keep the code simpler.
 
>>> That's not the case. You lose the Durability property, in that a
>>>   
>> COMMIT
>> 
>>> statement may return before a transaction is stored on the persistent
>>> media,
>>> but transactions are still Atomic, Consistent and Isolated.
>>>
>>> When using the "PRAGMA synchronous=off" your database might be
>>>   
>> corrupted
>> 
>>> by a power failure or OS crash. When using asynchronous IO this
>>>   
>> should
>> 
>>> not
>>> be possible (assuming the hardware is not being untruthful - just as
>>> when
>>> using regular "PRAGMA synchronous=full" mode without the async IO
>>>   
>> VFS).
>>
>>
>> Ah, the bulb has lit. Because the writes and syncs are processed by the
>> single queue in order, journal writes are guaranteed to be synced and
>> consistent before main in-place updates to the db file.
>>
>> Might be worth mentioning this in the documentation, as this is not
>> clear without examining the source.
>>
>> In that case, I like it :)
>>
>> Is this something that might be made the default in the future, with
>> the
>> addition of some synchronization between foreground and background
>> threads
>> on the xSync messages to emulate the existing "PRAGMA synchronous=full"
>> behaviour?
>>
>> 
>>> Dan.
>>>   
>> Christian
>> ___
>> 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


Re: [sqlite] Validating a file is a SQLite DB

2009-04-23 Thread Mark Spiegel
I would use the sqlite3_open_V2() call.  It won't read read the whole 
database, just enough to get started, including the header.  It will 
save you having to write and debug your own mechanism.

Tommy Ocel wrote:
> Hi,
>
> Other than using sqlite3_open_V2() with a SQLITE_OPEN_READONLY flag, which 
> would fully load the database and be time-consuming, anybody know of a quick 
> way to verify that a file is actually a SQLite3 database file?  (I'm reusing 
> a file extension for an upgrade, so want to quickly test if the file in new 
> SQL or old proprietary.)
>
> Thx for any feedback,
> Tommy
> ___
> 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


Re: [sqlite] Run-Time Check Failure

2009-04-22 Thread Mark Spiegel
The compiler is not broken.  If this behavior in the compiler bugs you, 
(pun intended) you can disable the warning locally or globally.

This change is not obfuscating the code.  It is not a work-around.  It 
is making your intentions clear.  When I read the line as written, I 
have to research for some  non-trivial amount of time, to figure out if 
what it is doing is what is intended.  When I read the line as modified 
with the mask, it is clear as a bell.  A comment wouldn't hurt either.

With all due respect, you really can't be complaining about obfuscating 
code that is filled with one character variable names, passes integers 
in pointer values, has a 4500+ line function, was composed with a 
keyboard missing a space key, etc, etc, etc.

D. Richard Hipp wrote:
> On Apr 22, 2009, at 11:49 AM, sql...@fauvelle.net wrote:
>
>   
>>> This is probably not a bug.  There are places in the SQLite code  
>>> where
>>> we deliberately discard all but the lower 8 bits of an integer.  But,
>>> if you like to tell us *where* in the code this occurs, I'll be happy
>>> to verify it for you.
>>>   
>> In sqlite3.c big file, it's in static u8 randomByte(void) function, on
>> line 16707 :
>>
>>   wsdPrng.j += wsdPrng.s[i] + k[i];
>>
>> wsdPrng.j = 246, and wsdPrng.s[i] + k[i] = 28, so adding it will be  
>> more
>> than 255. If it's deliberate, a bitmask 0xFF would solve the problem.
>> 
>
>
> This is not error in the SQLite code.  The code here is correct.  The  
> bug is in your compiler.
>
> Adding a work-around so that this will work in your compiler makes the  
> code rather more complicated:
>
>  wsdPrng.j = (wsdPrng.j + wsdPrng.s[i] + k[i]) & 0xff;
>
> I am opposed to obfuscating the code in this way because of your  
> compiler bug.  Is there some command-line option or something on your  
> compiler that can turn off the silly overflow check?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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


Re: [sqlite] SQLite crashing on iPhone (or so says Apple)

2009-04-21 Thread Mark Spiegel
I'm a bit confused by the following:

"The assign 100K or so to each database connection's lookaside memory 
allocator using sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) 
immediately after it is opened."

If memory is at a premium, why would you reserve a large amount of it 
for SQLite's "look aside allocator"?  (It's really a zone allocator.)  
This SQLite mechanism ostensibly attempts to trade memory for speed.  If 
memory is at a premium, in this case a fixed upper bound, that trade off 
doesn't seem to make sense.  I would think in a case where memory is 
tight, zero bytes should be reserved.

Jason Boehle wrote:
 I have written an application for the iPhone called Grocery iQ that
 uses SQLite.  I don't link to or use the built-in SQLite library on
 the iPhone.  Instead, I compile the SQLite amalgamation into the
 executable.  The SQLite version currently being used in our app is
 3.6.7.
 
>>> I sent instructions to Brian Killen on how you can download the latest
>>> version of SQLite+CEROD.  Perhaps recompiling will help.
>>>   
>
> Are there any particular bug fixes or changes that you know of that
> might address my problem?  I'm all for upgrading the SQLite version,
> it's just that we will have to do several days of testing to verify it
> works well, resubmit to Apple, then wait 5+ days to hear from them if
> it works or not.  Although given their tech support response times, we
> may have all of that done before I ever hear back from them.
>
>   
 * before opening the database, the only other SQLite API calls are:
   sqlite3_config(SQLITE_CONFIG_HEAP, [0], 3145728,
 512); // mSqliteMemory is declared as: unsigned char
 mSqliteMemory[3145728];
 
>>> You will probably do better to allocate most of that 3MB to page cache
>>> using sqlite3_config(SQLITE_CONFIG_PAGECHACHE, ...).  The assign 100K
>>> or so to each database connection's lookaside memory allocator using
>>> sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) immediately after it
>>> is opened.  With the above, usually a 100K or so is enough heap,
>>> though more might be required if you are holding many prepared
>>> statements or if you are using unusually big prepared statements.
>>>
>>> Oops.  I'm late for meeting.  More to follow later tonight.
>>>   
>> As I was saying
>>
>> Use sqlite3_status() to actually measure your memory usage.  Make
>> adjustments once you know how the memory is being used.  Don't guess;
>> measure. Also remember that later versions of SQLite use less memory
>> for storing prepared statements, so you might want to upgrade if
>> memory is an issue.  Limit your cache sizes using the cache_size
>> pragma.  Make use of sqlite3_soft_heap_limit() if you need to.  Or
>> right a custom pcache implementation that limits the amount of memory
>> used for the page cache.
>> 
>
> Thank you for the tips on tuning the memory usage.  I will definitely
> use this advice when working on Grocery iQ 2.0.  The way I have it
> working now though, I shouldn't be experiencing any problems like
> Apple has reported, right?  If SQLite fails any allocations, it should
> return an error and fail gracefully, correct?
>
> -Jason
> ___
> 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


Re: [sqlite] PRAGMA doesn't support parameter binds?

2009-04-13 Thread Mark Spiegel
As an alternative, you can use the WinDgb tool from MSFT.  It is not the 
nicest debugger in the world, but understands all the MSFT PDB formats.  
It is free for download from the MSDN site.  While it is a bit slow with 
large source files like the SQLite amalgamation, it does handle them fine.

m...@mwlabs.de wrote:
> How to debug the SQLite amalgation with Visual Studio 2008. 
>
> To get the debugger going you need to strip out the comments and empty lines
> from the sqlite.c source file.
> This can be done easily with two regular expressions for search and replace:
>
> First replace (Ctrl+H)
>
> (/\*(\n|.)@\*/)|(//.*$)
>
> With  to get rid of comments, then replace
>
> ^$\n
>
> With  to get rid of the empty lines.
>
> This brings down the sqlite.c to less than 60,000 lines, and the debugger
> will work again.
>
>
> 'Hope this helps.
>
> -- Mario
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Vinnie
> Sent: Monday, April 13, 2009 12:53 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] PRAGMA doesn't support parameter binds?
>
>
> Sorry for only posting when I have a problem...but...
>
> I'm doing
>
> PRAGMA user_version=?;
>
> And getting result code SQLITE_ERROR (1) from sqlite3_prepare_v2(). Of
> course I can't step into the sqlite3.c code because the Visual Studio 2008
> debugger gets hopelessly confused when confronted with a file whose line
> number representations exceed the capacity of an unsigned 16 bit integer.
>
> ___
> 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


Re: [sqlite] virtual file system

2009-03-26 Thread Mark Spiegel
As far as I'm aware, sqlite3_vfs_register() is the only way to register 
your own VFS.

Martin Pfeifle wrote:
> Dear all, 
>
> I have a question regarding virtual file systems.
> I assume I can load my own virtual file system by calling
> the  c-function sqlite3_vfs_register(...).
>
> Am I right that I cannot load a virtual file system by a pragma command or
> a core function similar to load_extension?
>
> I would appreciate very much if this were possible.
>
> Best Martin
>
>
>
>   
> ___
> 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


Re: [sqlite] VFS implementation guidance

2008-11-04 Thread Mark Spiegel
I would start with an existing VFS implementation and modify it to use 
the primitives available to you.  I started with the OS_WIN and modified 
it.  That will probably be easier than starting from scratch.

You can switch VFSs with each open.  You can use a different one for 
each DB open if you like.  (I do that now.)  I don't think you can 
switch the VFS for a given DB handle once it is opened.  You will 
probably have to close it and reopen.

Note that you can also, via #define values, exclude the pre-defined VFS 
implementations and just use your own.

Brown, Daniel wrote:
> Thanks for the pointers Roger and the example tests.  
>
> Is it possible to change the VFS SQLite is using while SQLite is
> running?  I'm looking at creating two different VFS implementations and
> it would be great to be able to switch between implementations as
> required, I'd be looking to switch VFS during program start-up and
> before any databases are loaded or used.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
> Sent: Tuesday, November 04, 2008 12:39 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VFS implementation guidance
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Brown, Daniel wrote:
>   
>> Are there any guides to implementing a VFS (sqlite3_vfs) for SQLite?
>> 
> A
>   
>> good practices guide would be as useful.   I already have an existing
>> file system API/library for the target system so I guess it is mostly
>> just matching up the API with the VFS implementation via some wrapper
>> functions?
>> 
>
> Pretty much just implement the functions as documented.  One gotcha is
> that xRandomness is only called once and is only called on the default
> VFS which makes testing it fun.
>
> Another is that xGetLastError is never called so you don't need to
> implement it.  http://www.sqlite.org/cvstrac/tktview?tn=3337
>
> You then need to run queries that exercise all parts of the VFS.  If you
> want some guidance, this is what I use:
>
> http://code.google.com/p/apsw/source/browse/apsw/trunk/tests.py#4759
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkkQssUACgkQmOOfHg372QTuLwCgygTWzPSW3CCHnQONXiEcKXf7
> 5XEAnR7DYzMf+hvXCORi/I/hpWgWF/t3
> =3tEY
> -END PGP SIGNATURE-
> ___
> 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


Re: [sqlite] Compile SQLite3 for MS Windows Driver Kit user-mode application

2008-10-07 Thread Mark Spiegel
Not sure why the Win32 DLL is not compatible.  I would think it should 
be.  You might want to work that out first.  Can you elaborate?

As for compiling with the WDK, it can be done.  The amalgamated source 
is best.

The flood of warnings is a pain.  SQLite dev claims they are all 
spurious, but with so many I wouldn't venture to guess how they can tell.

For W32, you should be able to disable "treat warnings as errors" for 
just the sqlite3.c file if your development organization allows this.  
If you are building 64 bits, then you have more work to do.  The last 
time I ported in new SQLite source, it still cast 32 bit integers into 
64 bit pointers.  The WDK compiler isn't going to allow this without 
some source modifications.  I did write a ticket so this might be 
fixed.  As of the last time I checked, it was not.

Bjorn Rauch wrote:
> Hello,
>  
> Has anybody tried to compile SQLite3 with the MS WDK? The Win32 DLL is not 
> compatible as far as I understand and recompiling with the WDK is necessary. 
> But using the source code as is results in many warnings (mostly conversion 
> errors). The WDK does not tollerate these.
>  
> Best regards,
> Björn
> _
> News, entertainment and everything you care about at Live.com. Get it now!
> http://www.live.com/getstarted.aspx
> ___
> 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


Re: [sqlite] Syntax sanity question...

2008-10-01 Thread Mark Spiegel
Thank you.  Yes, I've seen how the 16 bit versions just pass through to 
the 8 bit versions.  Living in a UNICODE world...

Looking at the source, this define is only used in test_config.c and 
mkkeywordhash.c.  I didn't discover mkkeywordhash.c originally.  Just to 
improve my ability to navigate the source, the #ifdef in mkkeywordhas.c 
is the one that beat me?

I'll remove this #define when I move forward to the more recent version 
of SQLite and try again.

D. Richard Hipp wrote:
> On Oct 1, 2008, at 6:25 PM, Mark Spiegel wrote:
>   
>> -DSQLITE_OMIT_CONFLICT_CLAUSE=1
>> 
>
> This disables REPLACE.
>
> Also, just so you will know, sqlite3_prepare16() works by converting  
> the SQL into UTF8 then calling sqlite3_prepare().
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Syntax sanity question...

2008-10-01 Thread Mark Spiegel
Doing some experimenting in my application and having trouble with an 
"INSERT OR REPLACE" statement.

SQLite version 3.5.9

Table schema:

CREATE TABLE DDS (FileId INTEGER PRIMARY KEY, _ModTime INTEGER, _CreTime 
INTEGER , _vtresult INTEGER, _md5 BINARY, _sha1 BINARY, _sha256 BINARY, 
_StabString__ TEXT, _caInt INTEGER, _caStr TEXT, _caBin BINARY);

SQL statement:

INSERT OR REPLACE INTO DDS 
(_ModTime,_CreTime,_vtresult,_md5,_sha1,_sha256,_StabString__,_caInt,_caStr,_caBin,
 
FileId) VALUES(?,?,?,?,?,?,?,?,?,?,?);

When I run Prepare16 (statement is UNICODE) I get an error 
SQLITE_ERROR.  The error text is: "near "REPLACE": syntax error".

If I remove the "OR REPLACE" Prepare16 call succeeds.

If I run this from in the command line tool replacing all the "?" with 
appropriate values, it succeeds.  I was using the 3.5.9 command line tool.

For my application, I've regenerated the amalgamated source and am 
building with a number of options to reduce size and control behavior.  
They are:

-DTHREADSAFE=1
-DSQLITE_OMIT_FLOATING_POINT=1
-DSQLITE_ENABLE_MEMORY_MANAGEMENT=1
-DSQLITE_OMIT_AUTHORIZATION=1
-DSQLITE_OMIT_AUTOINCREMENT=1
-DSQLITE_OMIT_AUTOVACUUM=1
-DSQLITE_OMIT_BLOB_LITERAL=1
-DSQLITE_OMIT_COMPLETE=1
-DSQLITE_OMIT_COMPOUND_SELECT=1
-DSQLITE_OMIT_CONFLICT_CLAUSE=1
-DSQLITE_OMIT_DATETIME_FUNCS=1
-DSQLITE_OMIT_EXPLAIN=1
-DSQLITE_OMIT_FOREIGN_KEY=1
-DSQLITE_OMIT_INTEGRITY_CHECK=1
-DSQLITE_OMIT_LOAD_EXTENSION=1
-DSQLITE_OMIT_MEMORYDB=1
-DSQLITE_OMIT_PROGRESS_CALLBACK=1
-DSQLITE_OMIT_REINDEX=1
-DSQLITE_OMIT_SUBQUERY=1
-DSQLITE_OMIT_TEMPDB=1
-DSQLITE_OMIT_TCL_VARIABLE=1
-DSQLITE_OMIT_TRIGGER=1
-DSQLITE_OMIT_VIEW=1
-DSQLITE_OMIT_VIRTUALTABLE=1
-DSQLITE_OMIT_FAULTINJECTOR=1

SQLITE_OMIT_CONFLICT_CLAUSE has me a bit worried, but as far as I can 
see, it has been added for future work and doesn't do anything yet.  It 
is not present in parse.y.  It seems to only appear in one of the TCL 
test script files.  Obviously, I will remove this before adding my new 
"INSERT OR REPLACE" stuff to production code.

I went so far as to step through the parsing of the statement.  It is 
failing when evaluating the "REPLACE" token.  Before I really start 
digging into this I wanted to see if anyone else has any experience that 
might help. 

1) Is the syntax correct?  I suspect so because it worked with the 
command line tool, unless there is a typo that eludes me.
2) Can anyone point to a SQLITE compile option that would interfere with 
the "REPLACE" keyword?

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


Re: [sqlite] Vista frustrations

2008-09-18 Thread Mark Spiegel
The SQLite part was an analogy.  That must have been beyond you.  You 
can have the last word.  You're beyond my help.

Fred Williams wrote:
> I never said a word aboout SQLite.  You ass U Me too much I suspect.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Mark Spiegel
> Sent: Thursday, September 18, 2008 11:25 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
>
> I'm sorry, I have to take issue with that statement.  The design of the
> file system/cache manager is not "pitiful".  It strives to provide good
> performance in the entire application space, not just your little corner
> of it.  It is doing the best it can with the "hint" you've given it.  If
> another (or no) hint provides better performance in your application,
> who's fault is that?  Do you realize that without the cache manager,
> fast I/O would not be possible?  Run on a debug system where only IRP
> based I/O is possible any you will be singing another tune in a hurry.
> Why do you think these hints are even available?  It is to help you
> optimize your application.
>
> The SQLite memory subsystem doesn't work well on my platform  I don't
> run around calling SQLite "pitiful".  I recognize that the authors'
> implementation(s) is probably a good performance compromise in the
> generic case.  If it is a big enough problem (which it is for me), I
> write my own version to optimize my performance.  While better, the
> integer encoding is not as good as it could be for me.  Does that mean
> the SQLite is pitiful?
>
> I should also note that as of the last time I talked to her, Molly is no
> longer handling the cache manager.  I believe she has moved back into
> the kernel group after a brief departure, but is working on something
> else.  I haven't seen the talks that Robert refers to, but suspect they
> are close to the versions I have seen in person.  I would bet they are
> still very useful and relevant.
>
> Fred Williams wrote:
>   
>> Is a sad day when an application program is forced to compensate for
>> 
> pitiful
>   
>> OS design and performance :-(
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
>> Sent: Thursday, September 18, 2008 10:31 AM
>> To: 'General Discussion of SQLite Database'
>> Subject: Re: [sqlite] Vista frustrations
>>
>>
>> After watching Molly Brown's Channel9 videos on the cache manager, I'm
>> convinced the behavior for SQLite should be to not give the filesystem any
>> hints about caching and let the cache manager itself figure it out.  The
>> exception being Windows CE, where we can confirm that when this flag is
>> 
> not
>   
>> set, the device will use compression in memory and degrade performance.
>>
>> If that's the general consensus, I'll open a ticket.
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
>> Sent: Thursday, September 18, 2008 7:56 AM
>> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
>> Subject: Re: [sqlite] Vista frustrations
>>
>> FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
>> cache manager (CC) in Windows and the underlying file system(s).  With
>> respect to the cache manager, it is going to affect whether or not there
>> is read ahead, how much read ahead will be used, and how long data will
>> remain in the cache (or another way, how quickly it will be dropped).
>> It has been some time since I've talked to the Queen of Cache Manger
>> about this, but as I recall CC will try to figure out what you are doing
>> if you don't give it a hint.  If you do give it a hint, then it is going
>> to run with that hint no matter what the cost.  Note that CC or the file
>> system are perfectly within their right to ignore your hints.  CC
>> generally does honor them.  NTFS, well that's another matter.
>>
>> It has been MY experience (YMMV) that database and temp file reads are
>> fairly random.  Database files also have the "nice" property that read
>> and writes are often sector (page) aligned.  Journal files should be
>> opened for sequential scan and are generally not sector (page) aligned.
>> Setting SQLite aside for a moment, for very large files that are only
>> going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
>> huge performance gains.  However, if most or all of a file is going to
>> be touched, even in random order, then it doesn't get you much and can
>&

Re: [sqlite] Vista frustrations

2008-09-18 Thread Mark Spiegel
I'm sorry, I have to take issue with that statement.  The design of the 
file system/cache manager is not "pitiful".  It strives to provide good 
performance in the entire application space, not just your little corner 
of it.  It is doing the best it can with the "hint" you've given it.  If 
another (or no) hint provides better performance in your application, 
who's fault is that?  Do you realize that without the cache manager, 
fast I/O would not be possible?  Run on a debug system where only IRP 
based I/O is possible any you will be singing another tune in a hurry.  
Why do you think these hints are even available?  It is to help you 
optimize your application.

The SQLite memory subsystem doesn't work well on my platform  I don't 
run around calling SQLite "pitiful".  I recognize that the authors' 
implementation(s) is probably a good performance compromise in the 
generic case.  If it is a big enough problem (which it is for me), I 
write my own version to optimize my performance.  While better, the 
integer encoding is not as good as it could be for me.  Does that mean 
the SQLite is pitiful?

I should also note that as of the last time I talked to her, Molly is no 
longer handling the cache manager.  I believe she has moved back into 
the kernel group after a brief departure, but is working on something 
else.  I haven't seen the talks that Robert refers to, but suspect they 
are close to the versions I have seen in person.  I would bet they are 
still very useful and relevant.

Fred Williams wrote:
> Is a sad day when an application program is forced to compensate for pitiful
> OS design and performance :-(
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
> Sent: Thursday, September 18, 2008 10:31 AM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Vista frustrations
>
>
> After watching Molly Brown's Channel9 videos on the cache manager, I'm
> convinced the behavior for SQLite should be to not give the filesystem any
> hints about caching and let the cache manager itself figure it out.  The
> exception being Windows CE, where we can confirm that when this flag is not
> set, the device will use compression in memory and degrade performance.
>
> If that's the general consensus, I'll open a ticket.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
> Sent: Thursday, September 18, 2008 7:56 AM
> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
> FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
> cache manager (CC) in Windows and the underlying file system(s).  With
> respect to the cache manager, it is going to affect whether or not there
> is read ahead, how much read ahead will be used, and how long data will
> remain in the cache (or another way, how quickly it will be dropped).
> It has been some time since I've talked to the Queen of Cache Manger
> about this, but as I recall CC will try to figure out what you are doing
> if you don't give it a hint.  If you do give it a hint, then it is going
> to run with that hint no matter what the cost.  Note that CC or the file
> system are perfectly within their right to ignore your hints.  CC
> generally does honor them.  NTFS, well that's another matter.
>
> It has been MY experience (YMMV) that database and temp file reads are
> fairly random.  Database files also have the "nice" property that read
> and writes are often sector (page) aligned.  Journal files should be
> opened for sequential scan and are generally not sector (page) aligned.
> Setting SQLite aside for a moment, for very large files that are only
> going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
> huge performance gains.  However, if most or all of a file is going to
> be touched, even in random order, then it doesn't get you much and can
> hurt you.  Most SQLite data bases _probably_ fall into that second
> case.  If you have enough memory and a small enough file such that the
> cache manager can hold the entire file, you are golden.  That's why some
> people see such great SQLite performance by just sequentially reading
> their DB files before running their SQLite application.
>
> The elephants in the room with that previous paragraph is 1) the amount
> of RAM in the system and 2) the other applications running.  Windows
> will try to share its resources among all the applications running as
> best it can.
>
> I have not seen any "bugs" in SQLite in this area.  It gives a
> reasonable hint for the general case.  To be fair however, I should note
> that I have my own VFS.  It does unbuffered I/O for database files and

Re: [sqlite] Vista frustrations

2008-09-18 Thread Mark Spiegel
FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the 
cache manager (CC) in Windows and the underlying file system(s).  With 
respect to the cache manager, it is going to affect whether or not there 
is read ahead, how much read ahead will be used, and how long data will 
remain in the cache (or another way, how quickly it will be dropped).  
It has been some time since I've talked to the Queen of Cache Manger 
about this, but as I recall CC will try to figure out what you are doing 
if you don't give it a hint.  If you do give it a hint, then it is going 
to run with that hint no matter what the cost.  Note that CC or the file 
system are perfectly within their right to ignore your hints.  CC 
generally does honor them.  NTFS, well that's another matter.

It has been MY experience (YMMV) that database and temp file reads are 
fairly random.  Database files also have the "nice" property that read 
and writes are often sector (page) aligned.  Journal files should be 
opened for sequential scan and are generally not sector (page) aligned.  
Setting SQLite aside for a moment, for very large files that are only 
going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show 
huge performance gains.  However, if most or all of a file is going to 
be touched, even in random order, then it doesn't get you much and can 
hurt you.  Most SQLite data bases _probably_ fall into that second 
case.  If you have enough memory and a small enough file such that the 
cache manager can hold the entire file, you are golden.  That's why some 
people see such great SQLite performance by just sequentially reading 
their DB files before running their SQLite application.

The elephants in the room with that previous paragraph is 1) the amount 
of RAM in the system and 2) the other applications running.  Windows 
will try to share its resources among all the applications running as 
best it can.

I have not seen any "bugs" in SQLite in this area.  It gives a 
reasonable hint for the general case.  To be fair however, I should note 
that I have my own VFS.  It does unbuffered I/O for database files and 
sequential, cached I/O for journal files.  If you think you can get 
better performance with different flags, create your own VFS, starting 
with the Windows VFS and make the changes.  You can get as sophisticated 
with your hints as you want.  You can write your own caching system if 
you've ingested way too much caffeine.  (Did I mention that the VFS 
stuff is great!)

I would not as a general rule advise people (customers) to change the 
way their Windows system caches globally for the benefit of one of your 
applications.  Eventually, that is going to bite you with some support 
calls.

Jay A. Kreibich wrote:
> On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the wall:
>
>   
>> The second is that SQLite when opening a file under Windows explicitly
>> tells Windows that the file will be used for random access even though
>> that is not the case.  Windows uses this hint to override its builtin
>> heuristics which can cause bug #1.
>> 
>
>   
>> Bug #2 is that SQLite is lying to the operating system and could result
>> in performance degradation if the operating system actually pays
>> attention to the hint.
>> 
>
>   SQLite is not "lying."  After poking around a bit to refresh my
>   understanding of SQLite's file structure, I think it is safe to say
>   that SQLite will almost never do a sequential file read, even if
>   you're doing a sequential table scan.
>
> sequential table scan != sequential file access
>   
>   There are some specific situations when you might get bursts of sequential
>   reads, but only for very specific page layouts with very specific
>   types of queries.  In short, not the common case.  Furthermore, even
>   those patterns can get broken up and shuffled around depending on the
>   state of SQLite's page cache-- especially if it is bumped up a few
>   dozen megs.  So simply running different types of queries can change
>   the access patterns (this is true of the OS's file system cache as
>   well, of course).
>
>   It might be worth instrumenting a few systems and having a look, but
>   in general, if you had to label SQLite's access pattern, I think
>   "random" would be the most appropriate label.
>
>
>
>   I also contend that if the Windows file cache becomes some kind of
>   bumbling idiot if you actually try to define an access pattern, then
>   something is wrong.  There is a very good reason why the POSIX
>   functions for doing this kind of thing are called "*advise()".  You
>   might seed the heuristic statistics in a specific direction, but they
>   should never be totally over-ridden.  That quickly leads to stupid
>   behaviors, like grabbing all the RAM on the system and not letting go.
>
>
>
>   Of course, we could argue philosophy for a long time.  In the here
>   and now to work around MS's inconsistencies, it looks like the best
>   

[sqlite] Perf improvements in 3.5.9

2008-05-15 Thread Mark Spiegel
The release notes for 3.5.9 indicate that performance improvements have 
been made around the way integers are stored.

"Performance enhancement: Reengineer the internal routines used to 
interpret and render variable-length integers."

Can someone in dev add some color to this statement?  What types of 
operations does this affect?  Any information on the magnitude of 
improvement and how this is measured?

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


Re: [sqlite] OMIT_VIEW / TRIGGER bug?

2008-04-22 Thread Mark Spiegel
This was a problem for me too.  I just chalked it up to deleting options 
and using the amalgamated source.  (Seem to recall reading that this is 
not recommended.)

Replace:

SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32, 
int);

with:

#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32, 
int);
#else   /* #if !defined(SQLITE_OMIT_VIEW) && 
!defined(SQLITE_OMIT_TRIGGER) */
#  define sqlite3MaterializeView(A,B,C,D,E) 0
#endif  /* #if !defined(SQLITE_OMIT_VIEW) && 
!defined(SQLITE_OMIT_TRIGGER) */

or equivalent.  As I recall, this function is called from within an if 
block whose condition (because of the #defines) will never be true.  For 
those of us who must live with the MSFT compilers, this is a problem.

Richard Klein wrote:
> I fixed my OPTS in the Makefile so that they are in sync
> with my compilation options.
>
> Now all the unresolved references in the parser have dis-
> appeared, but I'm still left with two unresolved references
> to the function sqlite3MaterializeView():
>
> delete.obj : error LNK2019: unresolved external symbol 
> _sqlite3MaterializeView referenced in function _sqlite3DeleteFrom
> update.obj : error LNK2019: unresolved external symbol 
> _sqlite3MaterializeView referenced in function _sqlite3Update
>
> The function sqlite3MaterializeView() is defined in the
> file delete.c, as follows:
>
> 
> #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
> /*
> ** Evaluate a view and store its result in an ephemeral table.  The
> ** pWhere argument is an optional WHERE clause that restricts the
> ** set of rows in the view that are to be added to the ephemeral table.
> */
> void sqlite3MaterializeView(
>...
> ){
>...
> }
> #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
> 
>
> In my application, I've defined SQLITE_OMIT_VIEW, but *not*
> SQLITE_OMIT_TRIGGER; that is, I want TRIGGERs, but not VIEWs.
>
> It would seem that in the conditional compilation expression
> shown above, the && should be replaced by ||:
>
> #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
>
> In other words, if VIEW *or* TRIGGER is supported, then define
> the function sqlite3MaterializeView().
>
> Making that change fixes the problem.
>
> - Richard
> ___
> 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


Re: [sqlite] SQLITE_OMIT_xxx

2008-04-21 Thread Mark Spiegel
Understood.  I use the amalgamated source for maximum performance.

Re 1: Look at Source Insight for editing.  The only dig I have at it is 
that it won't let me split a window into the same source file like the 
VC editor will.  Since I do all my building with command line tools, I 
don't need the build environment in VC.

Re 2: Look at using Araxis Merge instead of the Perforce dif tool.

Richard Klein wrote:
> Thanks, Mark!
>
> I use the individual source files rather than the amalgamation,
> for several reasons:
>
> (1) Visual Studio has trouble generating line number info for
> files that have more than 64K lines.
>
> (2) Perforce (our version control software) has trouble diff'ing
> two versions of a large file.
>
> (3) We build SQLite for many different target platforms, using
> various C and C++ compilers.  We get many (i.e. hundreds) of
> warnings, and even some errors.  When fixing these problems,
> it is simply easier to edit many smaller files rather than one
> huge, unwieldy file.
>
> - Richard
>
> Mark Spiegel wrote:
>   
>> That's what I do.  Once your makefile is set up, make the "sqlite3.c" 
>> target if you want an amalgamated source file.  Be sure to carefully 
>> coordinate the defined values between the preprocessing step (to 
>> generate your source file(s)) and the build of your application/dll. 
>>
>> If you are using amalgamated source, you may find a few other small 
>> problems when building your app, but they are easy to fix.
>>
>> Richard Klein wrote:
>> 
>>>> Richard Klein wrote:
>>>> 
>>>> 
>>>>> In order to reduce SQLite's memory footprint in my embedded
>>>>> application, I want to use the SQLITE_OMIT_xxx options to
>>>>> remove unneeded features from SQLite.
>>>>>
>>>>> Using Cygwin running on Windows, I have successfully down-
>>>>> loaded the canonical sources and autoconfigured the Makefile.
>>>>>
>>>>> The Makefile seems to indicate that in order to generate
>>>>> the parser, opcodes, and keyword hash function so that they
>>>>> omit the unneeded features, I need only add the following
>>>>> line to the Makefile:
>>>>>
>>>>> OPTS = -DSQLITE_OMIT_xxx -DSQLITE_OMIT_yyy ...
>>>>>
>>>>> Is this correct?
>>>>>
>>>>>   
>>>>>   
>>>> I believe so, but I haven't ever used the OMIT options when building 
>>>> SQLite.
>>>>
>>>> Are you having a problem when you do this?
>>>>
>>>> Dennis Cote
>>>>
>>>> 
>>>> 
>>> I haven't tried it yet ... I'll let you know if I have any
>>> problems.  Thanks!
>>>
>>> - Richard
>>>
>>> ___
>>> 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
>
>   

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


Re: [sqlite] SQLITE_OMIT_xxx

2008-04-21 Thread Mark Spiegel
That's what I do.  Once your makefile is set up, make the "sqlite3.c" 
target if you want an amalgamated source file.  Be sure to carefully 
coordinate the defined values between the preprocessing step (to 
generate your source file(s)) and the build of your application/dll. 

If you are using amalgamated source, you may find a few other small 
problems when building your app, but they are easy to fix.

Richard Klein wrote:
>> Richard Klein wrote:
>> 
>>> In order to reduce SQLite's memory footprint in my embedded
>>> application, I want to use the SQLITE_OMIT_xxx options to
>>> remove unneeded features from SQLite.
>>>
>>> Using Cygwin running on Windows, I have successfully down-
>>> loaded the canonical sources and autoconfigured the Makefile.
>>>
>>> The Makefile seems to indicate that in order to generate
>>> the parser, opcodes, and keyword hash function so that they
>>> omit the unneeded features, I need only add the following
>>> line to the Makefile:
>>>
>>> OPTS = -DSQLITE_OMIT_xxx -DSQLITE_OMIT_yyy ...
>>>
>>> Is this correct?
>>>
>>>   
>> I believe so, but I haven't ever used the OMIT options when building SQLite.
>>
>> Are you having a problem when you do this?
>>
>> Dennis Cote
>>
>> 
> I haven't tried it yet ... I'll let you know if I have any
> problems.  Thanks!
>
> - Richard
>
> ___
> 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


Re: [sqlite] Moving port from 3.5.1 to 3.5.7...

2008-03-21 Thread Mark Spiegel
The closest memory implementation would be the default one.  The other 
implementations that try to do their own pool management are 
unacceptable as memory system wide to too valuable to allow SQLite to 
allocate pool that it is not currently using.   I am also fundamentally 
opposed to trying to outsmart the memory manager until someone can 
demonstrate a need to do so.  I have yet to see a successful effort to 
do this in my environment.

Back to the default implementation, the trouble is that there is too 
much heavy synchronization and it prevents me from using available high 
performance memory management primitives and available debugging 
support.  Performance and space are critical factors.  I'm working in 
the NT system it would be unreasonable of me to expect that any of your 
implementations to be well suited to that environment.  That's why I 
found the SQLITE_OMIT_MEMORY_ALLOCATION exciting.  I was fully expecting 
from the beginning that this would be an area that I would have to 
implement just like the VFS and mutex support.  The difference is that 
now I have to make a few changes to the amalgamated source to do it 
rather than just #define a value.  No problem.

Thanks for your help.

[EMAIL PROTECTED] wrote:
> Mark Spiegel <[EMAIL PROTECTED]> wrote:
>   
>> I'm looking to jump my code port forward from 3.5.1 to 3.5.7. 
>>
>> Clearly I have some memory management work to do since 
>> SQLITE_OMIT_MEMORY_ALLOCATION support has been dropped.  None of the 
>> existing allocation implementations look acceptable so I'll have to roll 
>> my own,
>> 
>
> What do you need that none of mem[12345].c provide?
>
>   
>> but that looks pretty straight forward.
>>
>> Two questions:
>>
>> 1) Has the VFS interface changed from 3.5.1 to 3.5.7?
>> 
>
> No.
>
>   
>> 2) Is the SQLITE_MUTEX_APPDEF #define still supported in the same manner 
>> from 3.5.1 to 3.5.7?  (It appears that it is, but it never hurts to ask.)
>> 
>
> Yes.
>
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
> ___
> 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] Moving port from 3.5.1 to 3.5.7...

2008-03-21 Thread Mark Spiegel
I'm looking to jump my code port forward from 3.5.1 to 3.5.7. 

Clearly I have some memory management work to do since 
SQLITE_OMIT_MEMORY_ALLOCATION support has been dropped.  None of the 
existing allocation implementations look acceptable so I'll have to roll 
my own, but that looks pretty straight forward.

Two questions:

1) Has the VFS interface changed from 3.5.1 to 3.5.7?
2) Is the SQLITE_MUTEX_APPDEF #define still supported in the same manner 
from 3.5.1 to 3.5.7?  (It appears that it is, but it never hurts to ask.)

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


Re: [sqlite] Memory Usage

2007-11-19 Thread Mark Spiegel

Well said.

While it may be true that some memory allocators are lacking, the ones I 
use are quite good.  I view with great suspicion developers who thinks 
they can outsmart the pool allocator.  These folks usually add great 
complexity while having at best a neutral impact on performance and 
robustness.  As you point out, they can only optimize for their module, 
not globally.  Any changes of this type should be carefully tested of 
course, but just as importantly backed up by thorough performance data.


Joe Wilson wrote:

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
  

Our studies to date indicate that SQLite neither leaks nor fragments
memory.  Preventing leaks is relatively easy.  Preventing memory
fragmentation less so.  Yet we are not seeing memory fragmentation
as a problem for the workloads we have tested.

Nevertheless, we cannot *prove* that SQLite, in its current form,
will never fragment memory.  However, we are working toward a
future release where such a proof will be possible, at least for
certain well-defined operating parameters.   We just are not quite
there yet.



Pool allocators can be effective for certain classes of problems
and can exhibit desirable deterministic properties. But a library does 
not exist in isolation. You must consider the entire program memory space.
If every library used its own distinct pools then a program that 
uses many of such libraries (sqlite, apache portable runtime, GNU STL,
whatever) may ultimately end up with is sub-optimal memory utilization 
for the entire program. Space reserved for one library, but not currently 
in use might be better put to use by another library's short-lived 
operation, for example. 

Using the same allocator for the entire program can give it optimization 
opportunities that may not necessarily exist with distinct library-specific 
memory pools. 


An example from Hoard's docs (mostly speed related, as opposed to space):

http://www.cs.umass.edu/~emery/hoard/faqs.html

  I'm using the STL but not seeing any performance improvement. Why not?

  In order to benefit from Hoard, you have to tell STL to use malloc 
  instead of its internal custom memory allocator:


  typedef list mylist;

For some problems library-specific allocators are very useful. 
You have to consider other factors as well.




  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  




Re: [sqlite] SQLITE_OMIT_MEMORY_ALLOCATION

2007-11-08 Thread Mark Spiegel
I'm sure I can use some #defines to point to my own malloc, realloc, and 
free routines.  Unfortunately, it may be some time before I get to this 
so I'll have to stick with 3.5.1 for now.


Before I do that, I'm going to have to study the later source to try to 
get a better understanding of what you are trying to accomplish with the 
memory management changes. 


[EMAIL PROTECTED] wrote:

Mark Spiegel <[EMAIL PROTECTED]> wrote:
  
I just started looking at 3.5.2 and notice that support for 
SQLITE_OMIT_MEMORY_ALLOCATION was removed.  This is a problem for me.  
Is there some reasoning behind this?



Yes.  We are doing a lot of experimental work on the
memory allocation.  See

   http://www.sqlite.org/mpool/timeline

We quickly found that the SQLITE_OMIT_MEMORY_ALLOCATION
feature severely limited our options in designing new
memory allocation architectures.  We might to back with
some other means of providing user-definable memory allocation
in the future, but for the time being you have to either
use malloc, or else use SQLITE_MEMORY_SIZE to enable the
zero-malloc memory allocator.

Can you compile with macros like this:  -Dmalloc=my_app_malloc
-Drealloc=my_app_realloc -Dfree=my_app_free to get the effect
you want?

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Disk caching impacts performance.

2007-11-08 Thread Mark Spiegel
As Trevor cautioned, you run the risk of really impacting the other 
applications running on the system and the system itself.  As you fill 
up the Windows cache manager with your file, you will push out other 
relevant data including data that the OS may be using.  This will cause 
applications to run slower until the data is reloaded and new 
applications to start slower.  The cache manager will drop cached data 
as it loads your file.  Assuming that the cache manager is not already 
using memory at the limit allowed, it will ask the memory manager for 
more pages which will cause the memory manager to (on an LRU basis) drop 
pages that are backed by a file via a mapping and write pages to the 
paging file in cases where the pages are not backed by a map.  These 
writes (if they happen) are expensive in a relative sense and will now 
be mixed with your reads.  If the OS is not under stress and has 
sufficient resources, you probably won't see any of this.


Given that you can't write your own VFS, there is not much to suggest, 
but one question to ask.  Is the sum of the time for the pre-read you 
perform and the subsequent database operation(s) smaller than doing the 
database operation(s) without the pre-read?  I see that in the 3.5.x 
source Dr. Hipp gives the file system the proper random access hint to 
the file system when opening databases.  This is just a hint to the 
cache manager and it is not obligated to honor it, but it will 
effectively shut down most read ahead and large block reads which is 
what you are getting when you sequentially pre-read.


One more thing, did raising the limit on the number of pages SQLITE can 
cache internally have any effect?



Trevor Talbot wrote:

On 11/8/07, Julien Renggli <[EMAIL PROTECTED]> wrote:

  

As I said, the "nasty" trick works and we'll live with it (writing our
own FileSystem is not an option). We would just like to understand
better what's happening, to know why and when the "first run" is so much
slower. And should we read the whole file beforehand, only parts of it?
Does it depend on how much RAM is installed, ...? If you have any hints
they are welcome. I guess I should ask NTFS experts as well.



What you've discovered is pretty accurate: when the delay is due to
disk I/O, pre-reading the file will load it into the OS's disk cache,
so subsequent accesses are faster.  It will depend on available RAM,
not only what is physically installed but also memory pressure from
other running applications, other disk I/O in progress, various OS
settings, etc.  There isn't any way to accurately predict it.

Reading the entire file when the OS is unwilling to cache all of it
will simply result in only part of the file being cached.  It may also
cause other data to be pushed into the pagefile, slowing down
applications when they later access their own stale data.  In the
worst case, it could slow everything down for a short time.

Reading the entire file from start to finish is pretty much the only
effective way to pull it into cache.  An ifstream is not the most
efficient way to do that, since it does its own buffering, but that's
not important as far as the disk caching effects are concerned.

VACUUM removes internal fragmentation and writes rows in order, which
helps make disk I/O more sequential.  You may be able to one-up it by
inserting data in the order you intend to access it (but create any
indexes afterward).  Beyond that, I'm not aware of anything that would
help.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  




Re: [sqlite] Proposed sqlite3_initialize() interface

2007-10-30 Thread Mark Spiegel

Dr. Hipp,

On the fly initialization is a big concern for me because I have the 
misfortune to live in a massively multi-threaded environment.  So I am 
very much in favor of this change.  I see that there are already some 
other proposals out there, but would urge you to make the interface 
change in the manner described because it maintains clarity.  A single 
thread must initialize the sqlite module before any other operations are 
allowed.  (You can enforce that with debug code.)  I would also add one 
suggestion.  Add a sqlite3_deinitialize() call as well.  This function 
would be called after all other calls have completed and there are no 
more resources in use.  While it may be a noop at this time, it may not 
be at some point.  It is also a good place for debug code to ensure that 
all resources have been released.


Mark Spiegel

[EMAIL PROTECTED] wrote:

As currently implemented, SQLite3 requires no initialization.
You just start calling SQLite3 interfaces and they work.  We
can pull off this trick on Unix because pthread mutexes can
be initialized statically at compile-time.

  static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;

On win32, we have to initialize mutexes at run-time, but this
can be done within a contrived mutex that we build off of
a static integer using InterlockedIncrement().  And mutex
initialization apparently never fails on win32, so we do not
have to worry with reporting errors that occur during
mutex initialization.

But there are other operating systems using SQLite that do
not work this way.  They need a way to initialize mutexes
(and possibly other objects such as malloc) prior to running
any SQLite interface.  And the initialization needs to be able
to fail and return an error code.

To accomodate this need, we are considering an incompatible
API change to SQLite.  We are thinking of requiring that an
application invoke:

int sqlite3_initialize(...);

prior to using any other SQLite interface.  (The parameters to
sqlite3_initialize() are not yet designed.)  It will be an error
to use any other SQLite interface without first invoking
sqlite3_initialize() exactly one.  It is also an error to 
invoke sqlite3_initialize() more than once.


Existing applications that use SQLite would have to be modified
to invoke sqlite3_initialize().  Presumably this would happen
very early in main(), before any threads were created.  No other
code changes would be required.

This is still just an idea.  If you think that adding a new
required sqlite3_initialize() interface would cause serious
hardship for your use of SQLite, please speak up now.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Mechanism for pre-allocating contiguous files for Sqlite?

2007-10-29 Thread Mark Spiegel
I'm writing a VFS right now.  One of the parameters to the open is the 
file type (various types of DBs and journals).  You should be able to 
use that info. 

Also, look for a took called config on the sysinternals site.  It allows 
you to pre-allocate contiguous files.  Better, it allows you to defrag a 
single file.


Also, if you are feeling particularly sadistic, you could build a 
defragger into your open/close routines using the appropriate NTFS 
IOCTLs.  ;-)


Gary Moyer wrote:

Hi Teg,

Isn't an open issued for the database and journal separately?

I'm very familiar with the behavior for read-only, not so much for
read/write...

Regards,
-- Gary


On 10/28/07, Teg <[EMAIL PROTECTED]> wrote:
  

Hello Gary,

Sunday, October 28, 2007, 4:51:11 PM, you wrote:

GM> Hi Teg,

GM> Have you considered the SQLite VFS?

GM> Regards,
GM> -- Gary


GM> On 10/28/07, Teg <[EMAIL PROTECTED]> wrote:


I'd like to pre-allocate the DB for Sqlite so, I can ensure it's a
contiguous block of space on the disk. I'm aware of the "Insert a
bunch of data then delete" method but, it doesn't ensure a contiguous
block on disk. Is there some way I can allocate a file with OS calls


and


then
use it as an SQLite DB?

C






-


To unsubscribe, send email to [EMAIL PROTECTED]




-



Tried. One problem is at the VFS level, the code doesn't know if it's
writing to a journal or main DB file. You can pre-allocate in VFS
but, when the journal and main DB are combined, the main DB grows by
journal file size (or so I seem to observe).

This would probably have to be done in the pager.

--
Best regards,
Tegmailto:[EMAIL PROTECTED]



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





  




Re: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process

2007-10-25 Thread Mark Spiegel
Here's a bit more locking info that I found useful to help understand it 
all out of the archives:


http://www.mail-archive.com/sqlite-users@sqlite.org/msg02845.html

If you are writing a Windows app, you can use a named mutex which can be 
shared across processes. 

I have need for a blocking (as opposed to SQLITE_BUSY) mechanism as 
well.  As the referenced thread points out, it is not straightforward.  
Still working something out...



Lee Crain wrote:
Ken, Igor, 


I read the article you referenced. Much appreciated.
http://sqlite.org/lockingv3.html

I didn't want to complicate my original questions with the intricate
details of the application requirements which involve not allowing any
database access while certain other operations are executing. I think a
MUTEX, even with its inherent performance limitations, is the best
solution.

Thanks for your replies,

Lee Crain

P.S. Ken, I'm pretty certain that a MUTEX is both an intra- and
inter-process mutual exclusion object. 






-Original Message-
From: Ken [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 24, 2007 2:22 PM

To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: Some Questions Regarding Access To a SQLite
Database By More Than One Process

If you are using seperate processes then a mutex will not help since it is
local to a process. A semaphore could be used however.

You can use a begin immediate around all statements that perform DML
(ins/upd/sel)

Then loop on the busy at the begin immediate command. This is a fairly
simple thing to do.

Then for selects you'll need only test the prepare/ and first step  After
the first step you should not get a sqlite busy.


Lee Crain <[EMAIL PROTECTED]> wrote: Igor,

I did say "controlled" concurrency. 


I'll rephrase question 3.

3) Would use of a MUTEX to avoid the dreaded "SQLite busy" condition be a
good solution? Or is some other method of avoiding a busy condition
recommended?

Lee Crain

__


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 24, 2007 1:36 PM

To: SQLite
Subject: [sqlite] Re: Some Questions Regarding Access To a SQLite Database
By More Than One Process

Lee Crain  wrote:
  

1. Can multiple processes "concurrently" access the same SQLite
database?



Yes.

  

2. If so, can multiple processes maintain an open connection to the
database? Or must the connection be opened and closed, before and
after,
respectively, each database access?



You can have multiple open connections, from the same or different 
processes, at any given time. You can keep a connection open as long as 
necessary.


  

3. Would the use of a MUTEX as access protection be adequate to
successfully implement controlled "concurrency"?



I'm not sure I understand this question. Mutexes are all about _not_ 
allowing concurrency.


Igor Tandetnik 



--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---




-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  




Re: [sqlite] winLock() in SQLITE 3.5.1...

2007-10-22 Thread Mark Spiegel
Thanks Dan.  How about the second part.  Should the PENDING_LOCK be 
taken en route from the SHARD_LOCK to EXCLUSIVE_LOCK?  Which is right, 
the code or the function header?


Dan Kennedy wrote:

On Mon, 2007-10-22 at 10:56 -0700, Mark Spiegel wrote:
  
I posted this to the list last Wednesday and haven't seen a reply.  
D0n't want to create traffic in the defect database if I am in error.  
Anyone want to take a crack at this?


While working on a VFS for use in 3.5.1, I was looking at the winLock() 
and have a question. 

Is it possible for the lock on a winFile object to progress from 
SHARED_LOCK to EXCLUSIVE_LOCK without first acquiring a RESERVED_LOCK?



In one case.

After first obtaining a shared-lock on the database file, SQLite checks
for a hot-journal in the file system. If there is such a journal, it
tries to obtain the EXCLUSIVE lock so that it can do the rollback
operation. If it fails to get the exclusive lock, the pager layer
immediately drops any shared lock that is held and returns SQLITE_BUSY
to the caller. The assumption being that some other connection is
rolling back the hot-journal.

So I think you are right that there is a race condition, but it is
a benign one. 


Dan.

  
Assuming that it is, it seems that the comments at the start of the 
function are at odds with the implementation a little bit.  The comments 
indicate that a PENDING_LOCK will be an intermediate state between a 
SHARED_LOCK and EXCLUSIVE_LOCK.  This would make sense based on the 
other SQLITE docs I've read.  However, as I read the code it would seem 
that a SHARED_LOCK is not acquired if proceeding from a SHARED_LOCK to 
EXCLUSIVE_LOCK.  The PENDING_LOCK is only acquired if the current lock 
type is NO_LOCK or the current lock type is RESERVED_LOCK and an 
EXCLUSIVE_LOCK is desired.


The reason I'm asking is that later in the function where the 
EXCLUSIVE_LOCK is acquired, the SHARED_LOCK is released before trying to 
acquire the EXCLUSIVE_LOCK.  Without the protection of the PENDING_LOCK, 
it seems there is a race condition here where a winFile object may think 
it has tried (and possibly failed) to promote a SHARED_LOCK to an 
EXCLUSIVE_LOCK without realizing that at least once, and possibly twice, 
it was in a state where it held no lock at all.





-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] winLock() in SQLITE 3.5.1...

2007-10-22 Thread Mark Spiegel
I posted this to the list last Wednesday and haven't seen a reply.  
D0n't want to create traffic in the defect database if I am in error.  
Anyone want to take a crack at this?


While working on a VFS for use in 3.5.1, I was looking at the winLock() 
and have a question. 

Is it possible for the lock on a winFile object to progress from 
SHARED_LOCK to EXCLUSIVE_LOCK without first acquiring a RESERVED_LOCK? 

Assuming that it is, it seems that the comments at the start of the 
function are at odds with the implementation a little bit.  The comments 
indicate that a PENDING_LOCK will be an intermediate state between a 
SHARED_LOCK and EXCLUSIVE_LOCK.  This would make sense based on the 
other SQLITE docs I've read.  However, as I read the code it would seem 
that a SHARED_LOCK is not acquired if proceeding from a SHARED_LOCK to 
EXCLUSIVE_LOCK.  The PENDING_LOCK is only acquired if the current lock 
type is NO_LOCK or the current lock type is RESERVED_LOCK and an 
EXCLUSIVE_LOCK is desired.


The reason I'm asking is that later in the function where the 
EXCLUSIVE_LOCK is acquired, the SHARED_LOCK is released before trying to 
acquire the EXCLUSIVE_LOCK.  Without the protection of the PENDING_LOCK, 
it seems there is a race condition here where a winFile object may think 
it has tried (and possibly failed) to promote a SHARED_LOCK to an 
EXCLUSIVE_LOCK without realizing that at least once, and possibly twice, 
it was in a state where it held no lock at all.


I apologize in advance if I've misread the code.

Thanks in advance...




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] winLock() in SQLITE 3.5.1...

2007-10-17 Thread Mark Spiegel
While working on a VFS for use in 3.5.1, I was looking at the winLock() 
and have a question. 

Is it possible for the lock on a winFile object to progress from 
SHARED_LOCK to EXCLUSIVE_LOCK without first acquiring a RESERVED_LOCK? 

Assuming that it is, it seems that the comments at the start of the 
function are at odds with the implementation a little bit.  The comments 
indicate that a PENDING_LOCK will be an intermediate state between a 
SHARED_LOCK and EXCLUSIVE_LOCK.  This would make sense based on the 
other SQLITE docs I've read.  However, as I read the code it would seem 
that a SHARED_LOCK is not acquired if proceeding from a SHARED_LOCK to 
EXCLUSIVE_LOCK.  The PENDING_LOCK is only acquired if the current lock 
type is NO_LOCK or the current lock type is RESERVED_LOCK and an 
EXCLUSIVE_LOCK is desired.


The reason I'm asking is that later in the function where the 
EXCLUSIVE_LOCK is acquired, the SHARED_LOCK is released before trying to 
acquire the EXCLUSIVE_LOCK.  Without the protection of the PENDING_LOCK, 
it seems there is a race condition here where a winFile object may think 
it has tried (and possibly failed) to promote a SHARED_LOCK to an 
EXCLUSIVE_LOCK without realizing that at least once, and possibly twice, 
it was in a state where it held no lock at all.


I apologize in advance if I've misread the code.

Thanks in advance...


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Question regarding I/O under the new VFS model

2007-10-11 Thread Mark Spiegel
Thank you Dr. Hipp.  The DB info makes perfect sense.  Since I have to 
accommodate the initial read in the DB file you don't need to check on 
the change counter.  I'll handle it already.


With regard to the journal files, I understand that they will for the 
most part be written sequentially (with the exception of the header).  
That is a useful hint for these types of files.  It sounds like journal 
reads and writes  won't be sector sized and/or sector aligned.  I can 
accommodate that with little effort as well.



[EMAIL PROTECTED] wrote:

Mark Spiegel <[EMAIL PROTECTED]> wrote:
  
I take this to mean that paged-aligned sector I/O will always be used 
for all the DB types.  Will there ever be an I/O to a DB file that will 
not follow these rules.



When first opening a database, 100 bytes are ready from the very
beginning of the database file.  Part of this header tells us how
big the pages are.  The initial read is page-aligned but it is not
a read of a complete page, obviously, since at that time we do not
know how big the page is.

Otherwise, I think all database I/O is of complete pages.  There
might be an exception for updating the change counter in the header.
I'd have to check.

  
What about the journal types?  Will their I/O 
be sector sized and aligned I/O as well?



No.  Journals are mostly sequentially written, increasing the
file size as it is written.  There is a sector-sized header 
at the beginning of the journal that is skipped at first 
and then overwritten at the end.  If the cache spills, 
this sector-sized header might also be repeated at various 
points in the middle of the file.


--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Question regarding I/O under the new VFS model

2007-10-11 Thread Mark Spiegel
I'm working with implementing my own VFS.  If the developer(s) could 
answer a couple of questions, it would be helpful.


In section 2.1.4 of the SQLite 3.4.2 to 3.5.0 document, reference is 
made to "a database file will be doing page-aligned sector reads and 
writes in random order" regarding the file types that may be passed to 
the function represented by the xOpen parameter in the sqlite3_vfs 
structure.  Of the database types listed:


SQLITE_OPEN_MAIN_DB
SQLITE_OPEN_MAIN_JOURNAL
SQLITE_OPEN_TEMP_DB
SQLITE_OPEN_TEMP_JOURNAL
SQLITE_OPEN_TRANSIENT_DB
SQLITE_OPEN_SUBJOURNAL
SQLITE_OPEN_MASTER_JOURNAL

I take this to mean that paged-aligned sector I/O will always be used 
for all the DB types.  Will there ever be an I/O to a DB file that will 
not follow these rules.  What about the journal types?  Will their I/O 
be sector sized and aligned I/O as well?


High marks for abstracting memory allocation and the mutex object.  This 
is good stuff!  The file system abstraction looks very good as well.


Thanks in advance


-
To unsubscribe, send email to [EMAIL PROTECTED]
-