Re: [sqlite] SQLite on Windows 2003
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
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] Q. about core SQLite library
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
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] Booleans in SQLite
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] Booleans in SQLite
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
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
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] Repost: Accessing a DB while copying it causes Windows to eat virtual memory
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
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
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
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
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
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
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
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)
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, mSqliteMemory[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?
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 nothing to get rid of comments, then replace ^$\n With nothing 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
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
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
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
[sqlite] Syntax sanity question...
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] Syntax sanity question...
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
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 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 bet is turn it on with CE and off on Vista, because it appears to
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 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
Re: [sqlite] Vista frustrations
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 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
[sqlite] Perf improvements in 3.5.9
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?
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
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] SQLITE_OMIT_xxx
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
[sqlite] Moving port from 3.5.1 to 3.5.7...
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] Moving port from 3.5.1 to 3.5.7...
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
Re: [sqlite] Memory Usage
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 listunsigned int, malloc_alloc 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] Disk caching impacts performance.
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] SQLITE_OMIT_MEMORY_ALLOCATION
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] Proposed sqlite3_initialize() interface
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?
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
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] -
[sqlite] winLock() in SQLITE 3.5.1...
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] -
Re: [sqlite] winLock() in SQLITE 3.5.1...
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...
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] Question regarding I/O under the new VFS model
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] -
Re: [sqlite] Question regarding I/O under the new VFS model
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] -
