[sqlite] 3.2.8Undefined
Hi, all I install PHP and Apache on a WINDOWS XP box. After I enable the php_pdo.dll and php_pdo_sqlite.dll, the PHP info shows the sqlite version as 3.2.8Undefined. Why is it Undefined? And is it possible to get a later version of php_sqlite.dll , such as 3.3.3? Jack
Re: [sqlite] Problems with multiple threads?
As various people search for application and/or SQLite bugs related to multiple threads and BEGIN, let me try to aid the effort by better describing exactly what BEGIN does and suggesting some debugging tricks. Realize that BEGIN does not actually create any file locks or check to see if any file locks already exist, nor interact in any other way with the filesystem. File locks are only created by SELECT, UPDATE, INSERT, and DELETE statements. (OK, also CREATE and DROP statements, but let's ignore those for now for simplicity. Presumably the schema is fixed at the point where the problems are occuring.) All BEGIN does is to set a flag that says do not automatically perform a COMMIT after each write to the database. This is the autoCommit flag that I mentioned in a prior email. autoCommit is a boolean member of the sqlite3 structure. A lock is acquired at the beginning of each UPDATE, INSERT, or DELETE if it does not already exists. After each UPDATE, INSERT, or DELETE, sqlite checks the value of the autoCommit flag, and if it is true it automatically does a COMMIT. A read-lock is acquired before each SELECT if it does not already exists, and after the SELECT is done, the read-lock is dropped if autoCommit is true. So the BEGIN instruction does not do anything with the filesystem. It does not interact in any way with the operating system or with other database connections. All BEGIN does is clear the autoCommit flag. So it is hard to imagine how having other threads could possibly effect its behavior. At any time, you can determine the value of the autoCommit flag using the sqlite3_get_autocommit() API. See http://www.sqlite.org/capi3ref.html#sqlite3_get_autocommit I suggest that people who are getting back unexpected cannot start a transaction within a transaction errors should use the sqlite3_get_autocommit() API in some printf()s to trace the status of the autocommit flag within their application. Prior to running BEGIN, it should always be the case that sqlite3_get_autocommit() returns TRUE. If sqlite3_get_autocommit() returns FALSE, then the BEGIN that follows will give the cannot start... error. I suspect what is happening is that some prior COMMIT or ROLLBACK is not setting the autoCommit flag back to TRUE. This might be because the COMMIT or ROLLBACK failed. Or, there could be some kind of bug in SQLite that is causing the autoCommit flag to not be set correctly. I suspect the former, but am open to evidence pointing to the latter. It might be useful to use the sqlite3_get_autocommit() function to print out the value of the autoCommit flag after each COMMIT and ROLLBACK is executed. This might help to isolate the problem. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] disabling rollback journal
All, I have already posted some messages some months ago concerning disabling the creation of the journal file. It seems that currently there is no way to configure SQLite to do this. Journalling, it seems, is an integral part of the paging layer, is this correct? My question is thus, has anybody successfully disabled journalling of the database file? If nobody has done this could somebody suggest how difficult this would be for me to do myself. Or would this be considered as an option for a future release of SQLite? I ask as we find that journaling the database is just a performance and storage overhead for us. As I have stated before, our flash file system is 100% power-fail proof and thus the journalling of the database for us is not required. It just takes longer to save new records and the journal file uses up valuable space on our flash filesystem. I understand that journalling is important with most OS's to prevent possible database corruption but in our case this is not possible anyhow. If anybody can help me I will be most grateful. Mark DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Re: [sqlite] disabling rollback journal
Allan, Mark [EMAIL PROTECTED] wrote: our flash file system is 100% power-fail proof and thus the journalling of the database for us is not required. Without journalling, you cannot have a ROLLBACK command. And the semantics of UPDATE become UPDATE OR FAIL instead of the default UPDATE OR ABORT. The difference is subtle, but important. Does your flash file system actually implement atomic writes of multiple changes changes distributed across a single file? Or does it just guarantee that each individual write() is atomic. I'm guessing the latter. But without the former, you can still quite easily get database corruption after a power failure if you have no rollback journal. Any single UPDATE or INSERT or DELETE command will often result in multiple write() operations to the database file. If some of those write()s complete and others do not, your database will end up in a corrupt state. Note that you do not have to take a power failure for corruption to happen. Suppose a program is writing to the database, and does 2 of the 5 writes required to make a change to the database, but then the program is kill because a different thread in the program hit a bug and segfaults, or because the program is sent a SIGKILL (or the equivalent). Without a rollback journal, the database is left in an corrupt state with no way to recover. In summary, unless you filesystem implements transactions that span multiple write() requests, you still need a rollback journal. -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] disabling rollback journal
Dr Hipp, Thanks for your reply. I have contacted the authors of our filesystem HCC Embedded. The filesystem we are using is called EFFS, some information can be found at http://www.hcc-embedded.com/site.php if necessary. Apparently, yes the flash filesystem implements fully atomic writes, indeed this was one of the features that made us choose this particular filesystem. I know from development of our product that files are only updated when the file is flushed, i.e. when sqlite3OsSync() is called. Any fwrites without a flush are lost on power loss, therefore no corruption occurs. Here is a reply from the author of EFFS:- Hi Mark - changes to the file are uilt in mirror chains - when the file is closed or flushed then the new file replaces the old - i.e. the file is updated atomically. So I think you do not need this roll back. We take the view that thisis how a failsafe file system should work - it is only meaningful to change at known states - it should not be necessary for the user to know that each write creates an update - a relibale application would be well nigh imposible to design in this environment. So we think it should work really nicely with your database and that you can disable this rollback. Would be glad if you could mention to your SQLite contact the beauty of our system (:-)) Regards Dave I think based on your information and that from HCC that we can lose the journalling of the database without any potential data loss. Again my question is how can this be done? And how difficult would this be? I think this would benefit us a great deal as our target hardware is not the quickest and any performance enhancements would help a great deal. I would like to state that I do not mean to criticise SQLite or is functionality, it works well for us and has saved us much time in development and test. However we would like to know if it can be tweaked to our specific needs a little. Thanks again. Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 07 June 2006 14:14 To: sqlite-users@sqlite.org Subject: Re: [sqlite] disabling rollback journal Allan, Mark [EMAIL PROTECTED] wrote: our flash file system is 100% power-fail proof and thus the journalling of the database for us is not required. Without journalling, you cannot have a ROLLBACK command. And the semantics of UPDATE become UPDATE OR FAIL instead of the default UPDATE OR ABORT. The difference is subtle, but important. Does your flash file system actually implement atomic writes of multiple changes changes distributed across a single file? Or does it just guarantee that each individual write() is atomic. I'm guessing the latter. But without the former, you can still quite easily get database corruption after a power failure if you have no rollback journal. Any single UPDATE or INSERT or DELETE command will often result in multiple write() operations to the database file. If some of those write()s complete and others do not, your database will end up in a corrupt state. Note that you do not have to take a power failure for corruption to happen. Suppose a program is writing to the database, and does 2 of the 5 writes required to make a change to the database, but then the program is kill because a different thread in the program hit a bug and segfaults, or because the program is sent a SIGKILL (or the equivalent). Without a rollback journal, the database is left in an corrupt state with no way to recover. In summary, unless you filesystem implements transactions that span multiple write() requests, you still need a rollback journal. -- D. Richard Hipp [EMAIL PROTECTED] DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
[sqlite] DLLs containing user-defined SQL functions
I'm trying to add the ability to dynamically load DLLs containing SQL functions and collating sequences to SQLite. Things are working great on Unix, but I'm having issues with Windows. Windows experts, please help me. Suppose the main program (the .exe file) contains a function procA() and the DLL contains a function procB(). I want procB() to be able to call procA(). The idea is that the main program uses LoadLibrary() to pull in the DLL, then GetProcAddress() to find the address of procB(). Then the main program calls procB() in the DLL which in turn calls procA() in the main program. This all works great on Unix. When I use dlopen() to attach the shared library, the procA() reference in the shared library is automatically resolved to the address of procA() in the main program. But on Windows, I cannot get the DLL to compile because it is complaining about the missing procA(). Another way to ask the question is this: How do I build a DLL in windows that can call routines contained in the main program that attached the DLL using LoadLibrary()? -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] Problems with multiple threads?
For me, I have a bunch of threads writing to the database. That is the only part I do multithreaded. (All my read queries are handled after all the data is written.) I just use the scoped_lock operator from the Boost library at the top of my function that does the bind and step calls. I pass a pointer to the class containing that function and the mutex object to all my writer threads. It seems to work great; I don't even have SQLite compiled with threading enabled. My write function starts a new transaction every few thousand writes. Hi Bill, When you say handle read/write locking [your]self do you mean outside of SQLite in your code or by altering SQLite's source code? What algorithm do you employ?
Re: [sqlite] DLLs containing user-defined SQL functions
Hi Richard, I'm no windows expert, but why don't you use something like the tcl stubs mechanism? Build a static sqlite_stubs.a library and link all loadable dynamic libs against it. Kind regards Ulrich On Wednesday 07 June 2006 16:30, [EMAIL PROTECTED] wrote: I'm trying to add the ability to dynamically load DLLs containing SQL functions and collating sequences to SQLite. Things are working great on Unix, but I'm having issues with Windows. Windows experts, please help me. Suppose the main program (the .exe file) contains a function procA() and the DLL contains a function procB(). I want procB() to be able to call procA(). The idea is that the main program uses LoadLibrary() to pull in the DLL, then GetProcAddress() to find the address of procB(). Then the main program calls procB() in the DLL which in turn calls procA() in the main program. This all works great on Unix. When I use dlopen() to attach the shared library, the procA() reference in the shared library is automatically resolved to the address of procA() in the main program. But on Windows, I cannot get the DLL to compile because it is complaining about the missing procA(). Another way to ask the question is this: How do I build a DLL in windows that can call routines contained in the main program that attached the DLL using LoadLibrary()? -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] DLLs containing user-defined SQL functions
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 7:30 AM To: Sqlite-users Subject: [sqlite] DLLs containing user-defined SQL functions [snip] Another way to ask the question is this: How do I build a DLL in windows that can call routines contained in the main program that attached the DLL using LoadLibrary()? The main executable would have to export the function through a .DEF file, and your DLL would have to get the pointer to the function through GetProcAddress() as follows: proc = GetProcAddress(GetModuleHandle(NULL), exe_exported_func_name);
RE: [sqlite] DLLs containing user-defined SQL functions
-Original Message- From: Robert Simpson [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 7:55 AM To: 'sqlite-users@sqlite.org' Subject: RE: [sqlite] DLLs containing user-defined SQL functions -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 7:30 AM To: Sqlite-users Subject: [sqlite] DLLs containing user-defined SQL functions [snip] Another way to ask the question is this: How do I build a DLL in windows that can call routines contained in the main program that attached the DLL using LoadLibrary()? The main executable would have to export the function through a .DEF file, and your DLL would have to get the pointer to the function through GetProcAddress() as follows: proc = GetProcAddress(GetModuleHandle(NULL), exe_exported_func_name); A caveat: It's uncommon for executables to export functions, but not unheard of. Regular C/C++ programs will have no problem adapting to this mechanism, but other languages that either don't use an exe, or use a proxy exe, could not support it without additional SQLite API's to change the load behavior. You'd need options to either allow you to set a compile-time directive to hardcode the function, or to specify what HMODULE to call GetProcAddress() on.
[sqlite] Re: DLLs containing user-defined SQL functions
[EMAIL PROTECTED] wrote: Suppose the main program (the .exe file) contains a function procA() and the DLL contains a function procB(). I want procB() to be able to call procA(). While it is possible for the EXE to export a function via a .def file, and for DLL to import it, it's a configuration nightmare. The usual way to do what you want is for a DLL to export some kind of an initialization function that accepts a function pointer (a callback) as a parameter. See for example HttpExtensionProc: http://msdn.microsoft.com/library/en-us/iissdk/html/5f489650-d679-4523-8f44-4263c46e3c90.asp It's a main entry point that should be implemented by an ISAPI extension DLL (a plug-in module for Internet Information Server (IIS), MS' web server). Note how it takes EXTENSION_CONTROL_BLOCK structure as a parameter, that contains pointers to functions implemented by IIS executable. This is how the extension talks back to its host. This all works great on Unix. When I use dlopen() to attach the shared library, the procA() reference in the shared library is automatically resolved to the address of procA() in the main program. On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. Igor Tandetnik
[sqlite] :memory: DB releasing storage
Hello, Is there anything similar with a :memory: DB to auto-vacuum with a disk DB. I would like to release storage that is no longer being used by SQLite. Is this possible? Thanks, Rick Keiner
Re: [sqlite] disabling rollback journal
Allan, Mark [EMAIL PROTECTED] wrote: Apparently, yes the flash filesystem implements fully atomic writes, = indeed this was one of the features that made us choose this particular = filesystem. I know from development of our product that files are only = updated when the file is flushed, i.e. when sqlite3OsSync() is called. = Any fwrites without a flush are lost on power loss, therefore no = corruption occurs. I have been in contact with the developer of your flash filesystem and we are working on a solution now... -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] DLLs containing user-defined SQL functions
Igor Tandetnik [EMAIL PROTECTED] wrote: This all works great on Unix. When I use dlopen() to attach the shared library, the procA() reference in the shared library is automatically resolved to the address of procA() in the main program. On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. The disadvantages to the windows approach are obvious. Before I add this characteristic to the ever-growing list of reasons why I hate windows and especially hate programming for windows, I should be fair and ask if there are any advantages to the windows way of doing things that I have overlooked. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] :memory: DB releasing storage
Rick Keiner [EMAIL PROTECTED] wrote: Hello, Is there anything similar with a :memory: DB to auto-vacuum with a disk DB. I would like to release storage that is no longer being used by SQLite. Is this possible? I think auto-vacuum work with :memory: databases just as well as on-disk database. Are you having problems with it? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?]
On 6/7/06, Russell Leighton [EMAIL PROTECTED] wrote: So, this was very enlightening...I have a simple backup function that I now question is correct. It does: - execute begin // lock from writes -copy db file to new file byte by byte - execute commit // unlock ...I was thinking that begin would lock the file. I believe you want a BEGIN IMMEDIATE instead of a BEGIN. This is how I implemented my own version of it. If it doesn't work would you let us know? -- SqliteImporter, SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite
Re: [sqlite] DLLs containing user-defined SQL functions
[EMAIL PROTECTED] wrote: Igor Tandetnik [EMAIL PROTECTED] wrote: This all works great on Unix. When I use dlopen() to attach the shared library, the procA() reference in the shared library is automatically resolved to the address of procA() in the main program. On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. That explanation does not seem entirely accurate (especially the second sentence). If that were true, it owuld not be possible to release an updated DLL with re-arranged entry points and expect the caller EXE or DLL to link to it properly; yet that works. Refer to the blog of the guy at Microsoft who wrote (or rewrote) the DLL loader: http://blogs.msdn.com/mgrier/rss.aspx The disadvantages to the windows approach are obvious. Before I add this characteristic to the ever-growing list of reasons why I hate windows and especially hate programming for windows, I should be fair and ask if there are any advantages to the windows way of doing things that I have overlooked. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] DLLs containing user-defined SQL functions
On 6/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: The disadvantages to the windows approach are obvious. Before I add this characteristic to the ever-growing list of reasons why I hate windows and especially hate programming for windows, I should be fair and ask if there are any advantages to the windows way of doing things that I have overlooked. The result, if sold, will make more money ;)
[sqlite] Re: DLLs containing user-defined SQL functions
[EMAIL PROTECTED] wrote: Igor Tandetnik [EMAIL PROTECTED] wrote: On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. The disadvantages to the windows approach are obvious. Before I add this characteristic to the ever-growing list of reasons why I hate windows and especially hate programming for windows, I should be fair and ask if there are any advantages to the windows way of doing things that I have overlooked. From http://www.iecc.com/linker/linker10.html : The runtime performance costs of dynamic linking are substantial compared to those of static linking, since a large part of the linking process has to be redone every time a program runs. Every dynamically linked symbol used in a program has to be looked up in a symbol table and resolved. (Windows DLLs mitigate this cost somewhat, as we describe below.) Igor Tandetnik
[sqlite] Re: DLLs containing user-defined SQL functions
Dennis Jenkins [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: Igor Tandetnik [EMAIL PROTECTED] wrote: On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. That explanation does not seem entirely accurate (especially the second sentence). If that were true, it owuld not be possible to release an updated DLL with re-arranged entry points and expect the caller EXE or DLL to link to it properly; yet that works. Exactly where to look was perhaps too strong a stateent. What I mean is the import table lists the name (just the file name, without path) of the DLL and the name (or ordinal number) of the exported function. The loader still needs to search the path for the DLL file, and search the DLL's export table for the symbol name. What it does not have to do is search all the DLLs in the system and all the symbols in them to resolve dependencies, as it appears to be the case under Unix-like scheme. I don't really know how Unix loader works sufficiently well to engage in intelligent discussion. Igor Tandetnik
RE: [sqlite] Problems with multiple threads?
Thanks for an additional explanation, I used sqlite3_get_autocommit() for debugging and it helped me to find out that it really was my fault. There was an incorrect processing after COMMIT returned SQLITE_BUSY. So sorry for this. However, right after fixing this, I found another problem. It certainly can be my fault, but I don't see how could it be: If I don't use transactions, multiple threads seem to proceed well, but then right after I add BEGIN and COMMIT to some place, all threads lock eventually. I debugged it and found that _all_ threads accessing SQLite are in a loop waiting for an action to proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be somehow my fault, or is it some kind of a dead-lock in SQLite? In the simpliest form it takes only two threads to reproduce and the problem looks like: Thread 1: BEGIN TRANSACTION-- proceeded INSERT INTO ... -- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY COMMIT Thread 2: // no explicit transaction start here DELETE FROM ... -- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY Thread 2 can also look like this in order to reproduce the problem (situation in Thread 1 remains the same): BEGIN TRANSACTION-- proceeded DELETE FROM ... -- proceeded COMMIT -- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY No other thread calls any SQLite function. Do you have any idea what could be wrong? Thanks, Jiri
Re: [sqlite] Re: DLLs containing user-defined SQL functions
On Wed, 7 Jun 2006, Igor Tandetnik wrote: [EMAIL PROTECTED] wrote: Igor Tandetnik [EMAIL PROTECTED] wrote: On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. The disadvantages to the windows approach are obvious. Before I add this characteristic to the ever-growing list of reasons why I hate windows and especially hate programming for windows, I should be fair and ask if there are any advantages to the windows way of doing things that I have overlooked. From http://www.iecc.com/linker/linker10.html : The runtime performance costs of dynamic linking are substantial compared to those of static linking, since a large part of the linking process has to be redone every time a program runs. Every dynamically linked symbol used in a program has to be looked up in a symbol table and resolved. (Windows DLLs mitigate this cost somewhat, as we describe below.) Wow. That philosophy has diminishing returns as machines get faster. What foresight! This is one of the most painful aspects of Windows programming (among many) but can be somewhat mitigated by doing away with .def files: http://msdn2.microsoft.com/en-us/library/3y1sfaz2.aspx Basically, wrap the above in a macro, something like: #ifdef WIN32 #define EXPORT __declspec( dllexport ) #else #define EXPORT #endif Then declare functions as EXPORT int foo( int bar ); On UNIX and other sane environments, it does nothing as nothing is required. On Windows, an export symbol (or whatever it is) is created without the need for a .def file. Don't know if it'll work on .exe's, mind. You may have to dllimport the required function from the .exe to the dll. I don't know for sure. Igor Tandetnik Christian
RE: [sqlite] disabling rollback journal
I have been in contact with the developer of your flash filesystem and we are working on a solution now... That really is excellent news. Thanks for your interest and effort. I look forward to the solution. DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Re: [sqlite] Re: DLLs containing user-defined SQL functions
On Wed, 7 Jun 2006, Igor Tandetnik wrote: Dennis Jenkins [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: Igor Tandetnik [EMAIL PROTECTED] wrote: On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. That explanation does not seem entirely accurate (especially the second sentence). If that were true, it owuld not be possible to release an updated DLL with re-arranged entry points and expect the caller EXE or DLL to link to it properly; yet that works. Exactly where to look was perhaps too strong a stateent. What I mean is the import table lists the name (just the file name, without path) of the DLL and the name (or ordinal number) of the exported function. The loader still needs to search the path for the DLL file, and search the DLL's export table for the symbol name. What it does not have to do is search all the DLLs in the system and all the symbols in them to resolve dependencies, as it appears to be the case under Unix-like scheme. I don't really know how Unix loader works sufficiently well to engage in intelligent discussion. Under Unix, run time linking is basically exactly that, a run time link. The exe is loaded, and unresolved symbols are searched for in the required libraries. The list of required libraries is maintained in the exe, so the linker doesn't have to search all the system libraries. Shared libraries may also depend on further shared libraries, symbols being resolved on the way, until no more symbols are left (or no more libraries are left or cannot be found, when an unresolved symbol error is thrown.) The main difference appears that Unix just says Symbol foo is unresolved, look in libx, liby and libz whereas Windows says Symbol foo from liby is unresolved. The Unix way is more flexible in that things like LD_PRELOAD can override symbols from libraries. Very useful. Igor Tandetnik Christian
Re: [sqlite] Problems with multiple threads?
On 6/7/06, Jiri Hajek [EMAIL PROTECTED] wrote: However, right after fixing this, I found another problem. It certainly can be my fault, but I don't see how could it be: If I don't use transactions, multiple threads seem to proceed well, but then right after I add BEGIN and COMMIT to some place, all threads lock eventually. I debugged it and found that _all_ threads accessing SQLite are in a loop waiting for an action to proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be somehow my fault, or is it some kind of a dead-lock in SQLite? That looks like a deadlock. Can you take the transactions out? From your example they're not doing anything for you anyway with only a single statement within them
RE: [sqlite] Problems with multiple threads?
On Wed, 7 Jun 2006, Jiri Hajek wrote: However, right after fixing this, I found another problem. It certainly can be my fault, but I don't see how could it be: If I don't use transactions, multiple threads seem to proceed well, but then right after I add BEGIN and COMMIT to some place, all threads lock eventually. I debugged it and found that _all_ threads accessing SQLite are in a loop waiting for an action to proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be somehow my fault, or is it some kind of a dead-lock in SQLite? If one transaction already has a read lock, and another transaction has a reserved lock (trying to get a write lock), neither thread can get a write lock. One of the transactions must abort. Such a sequence might be (in order): Transaction 1: BEGIN; SELECT ... Transaction 2: BEGIN; DELETE ... (SQLITE_BUSY) T1 : UPDATE ... (SQLITE_BUSY) Both transactions are now deadlocked. It would be nice if SQLite told us this. However, SQLite detects the reserved lock and returns SQLITE_BUSY, telling niether transaction much other than to try again. If a reserved lock is detected when trying to promote an existing read lock, this is a deadlock situation and should perhaps return an error code of SQLITE_DEADLOCK instead? Christian
Re: [sqlite] BEGIN and Backup [was [sqlite] Problems with multiple threads?]
Thx! [EMAIL PROTECTED] wrote: Russell Leighton [EMAIL PROTECTED] wrote: So, this was very enlightening...I have a simple backup function that I now question is correct. It does: - execute begin // lock from writes -copy db file to new file byte by byte - execute commit // unlock I was thinking that begin would lock the file. Use BEGIN EXCLUSIVE instead of just BEGIN. The extra EXCLUSIVE keyword causes it to acquire a lock right away. -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?]
You can use BEGIN IMMEDIATE or BEGIN EXCLUSIVE depending on the type of lock you'd like. SQLite BEGIN syntax: http://sqlite.org/lang_transaction.html SQLite locks: http://sqlite.org/lockingv3.html SQLite Busy Handler: http://sqlite.org/capi3ref.html#sqlite3_busy_handler Pat -Original Message- From: Russell Leighton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 8:24 AM To: sqlite-users@sqlite.org Subject: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?] So, this was very enlightening...I have a simple backup function that I now question is correct. It does: - execute begin // lock from writes -copy db file to new file byte by byte - execute commit // unlock ...I was thinking that begin would lock the file. If I use an flock() [or should it be lockf() ?? ] to bracket the file copy will I be safe? I was hoping to have something more efficient than a table by table copy for backup. Thx [EMAIL PROTECTED] wrote: As various people search for application and/or SQLite bugs related to multiple threads and BEGIN, let me try to aid the effort by better describing exactly what BEGIN does and suggesting some debugging tricks. Realize that BEGIN does not actually create any file locks or check to see if any file locks already exist, nor interact in any other way with the filesystem. File locks are only created by SELECT, UPDATE, INSERT, and DELETE statements. (OK, also CREATE and DROP statements, but let's ignore those for now for simplicity. Presumably the schema is fixed at the point where the problems are occuring.) All BEGIN does is to set a flag that says do not automatically perform a COMMIT after each write to the database. This is the autoCommit flag that I mentioned in a prior email. autoCommit is a boolean member of the sqlite3 structure. A lock is acquired at the beginning of each UPDATE, INSERT, or DELETE if it does not already exists. After each UPDATE, INSERT, or DELETE, sqlite checks the value of the autoCommit flag, and if it is true it automatically does a COMMIT. A read-lock is acquired before each SELECT if it does not already exists, and after the SELECT is done, the read-lock is dropped if autoCommit is true. So the BEGIN instruction does not do anything with the filesystem. It does not interact in any way with the operating system or with other database connections. All BEGIN does is clear the autoCommit flag. So it is hard to imagine how having other threads could possibly effect its behavior. At any time, you can determine the value of the autoCommit flag using the sqlite3_get_autocommit() API. See http://www.sqlite.org/capi3ref.html#sqlite3_get_autocommit I suggest that people who are getting back unexpected cannot start a transaction within a transaction errors should use the sqlite3_get_autocommit() API in some printf()s to trace the status of the autocommit flag within their application. Prior to running BEGIN, it should always be the case that sqlite3_get_autocommit() returns TRUE. If sqlite3_get_autocommit() returns FALSE, then the BEGIN that follows will give the cannot start... error. I suspect what is happening is that some prior COMMIT or ROLLBACK is not setting the autoCommit flag back to TRUE. This might be because the COMMIT or ROLLBACK failed. Or, there could be some kind of bug in SQLite that is causing the autoCommit flag to not be set correctly. I suspect the former, but am open to evidence pointing to the latter. It might be useful to use the sqlite3_get_autocommit() function to print out the value of the autoCommit flag after each COMMIT and ROLLBACK is executed. This might help to isolate the problem. -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] Problems with multiple threads?
Do you have any transactions that look like: BEGIN SELECT INSERT/DELETE/UPDATE COMMIT If you do, you may have multiple threads trying to escalate from a SHARED to a RESERVED lock as described here: http://sqlite.org/capi3ref.html#sqlite3_busy_handler It's important that if you have multithreaded access and a strategy that involves either retrying or waiting on SQLITE_BUSY that you specify at the beginning of write transactions that the transaction requires a higher level lock. I do this using BEGIN IMMEDIATE. The thread currently on this list with subject [sqlite] BEGIN and Backup [was [sqlite] Problems with multiple threads?] Discusses a similar issue. Pat -Original Message- From: Jiri Hajek [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 9:26 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Problems with multiple threads? Thanks for an additional explanation, I used sqlite3_get_autocommit() for debugging and it helped me to find out that it really was my fault. There was an incorrect processing after COMMIT returned SQLITE_BUSY. So sorry for this. However, right after fixing this, I found another problem. It certainly can be my fault, but I don't see how could it be: If I don't use transactions, multiple threads seem to proceed well, but then right after I add BEGIN and COMMIT to some place, all threads lock eventually. I debugged it and found that _all_ threads accessing SQLite are in a loop waiting for an action to proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be somehow my fault, or is it some kind of a dead-lock in SQLite? In the simpliest form it takes only two threads to reproduce and the problem looks like: Thread 1: BEGIN TRANSACTION-- proceeded INSERT INTO ... -- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY COMMIT Thread 2: // no explicit transaction start here DELETE FROM ... -- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY Thread 2 can also look like this in order to reproduce the problem (situation in Thread 1 remains the same): BEGIN TRANSACTION-- proceeded DELETE FROM ... -- proceeded COMMIT -- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY No other thread calls any SQLite function. Do you have any idea what could be wrong? Thanks, Jiri
Re: [sqlite] BEGIN and Backup [was [sqlite] Problems with multiple threads?]
Pat Wibbeler [EMAIL PROTECTED] wrote: You can use BEGIN IMMEDIATE or BEGIN EXCLUSIVE depending on the type of lock you'd like. If you are just trying to make sure the database does not change while you back it up, then Jay's suggestion of BEGIN IMMEDIATE is the better approach (better than my suggestion of BEGIN EXCLUSIVE) since BEGIN IMMEDIATE only gets a read lock and thus allows other processes to continue reading the database while the backup is taking place. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Re: Re: DLLs containing user-defined SQL functions
Christian Smith [EMAIL PROTECTED] wrote: This is one of the most painful aspects of Windows programming (among many) but can be somewhat mitigated by doing away with .def files: http://msdn2.microsoft.com/en-us/library/3y1sfaz2.aspx Basically, wrap the above in a macro, something like: #ifdef WIN32 #define EXPORT __declspec( dllexport ) #else #define EXPORT #endif This technique still doesn't allow a DLL to link back to a symbol exported from an EXE, without jumping through hoops - the original topic of this thread. Another problem is that __declspec(dllexport) does not allow one to export an unmangled undecorated name exactly as it appears in your source code: at best you get an extra underscore in front, at worst you get a C++ mangled name. To export an undecorated name one still has to use .def files. And people using the DLL via LoadLibrary / GetProcAddress are rarely amused when the name they have to use in GetProcAddress does not match that in the documentation and header files. You may have to dllimport the required function from the .exe to the dll. I don't know for sure. The way it works, the linker produces a so-called import library (.LIB) together with the DLL. Import library has the same structure as a regular static library, but instead of actual code it contains references to its companion DLL. When building the EXE, you simply link against this import library as you would against a static library. Note an inherent chicken and egg problem: you can't build two DLLs (or an EXE and a DLL) using this approach where a circular dependency exists, that is, where DLL A needs a function exported from DLL B, and at the same time DLL B needs a function exported from DLL A. To successfully link DLL A, you need an import library from DLL B, but an import library is produced as a side effect of link process, and to link DLL B you need an import library from DLL A, which you can't build until you've built B, ... There is a way to break this circle with the use of so called export files (.exp ), but the technique is rather cumbersome. You don't want to go that way unless there's a gun to your head. Igor Tandetnik
Re: [sqlite] Problems with multiple threads?
Christian Smith [EMAIL PROTECTED] wrote: On Wed, 7 Jun 2006, Jiri Hajek wrote: However, right after fixing this, I found another problem. It certainly can be my fault, but I don't see how could it be: If I don't use transactions, multiple threads seem to proceed well, but then right after I add BEGIN and COMMIT to some place, all threads lock eventually. I debugged it and found that _all_ threads accessing SQLite are in a loop waiting for an action to proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be somehow my fault, or is it some kind of a dead-lock in SQLite? If one transaction already has a read lock, and another transaction has a reserved lock (trying to get a write lock), neither thread can get a write lock. One of the transactions must abort. Such a sequence might be (in order): Transaction 1: BEGIN; SELECT ... Transaction 2: BEGIN; DELETE ... (SQLITE_BUSY) T1 : UPDATE ... (SQLITE_BUSY) Both transactions are now deadlocked. It would be nice if SQLite told us this. However, SQLite detects the reserved lock and returns SQLITE_BUSY, telling niether transaction much other than to try again. If a reserved lock is detected when trying to promote an existing read lock, this is a deadlock situation and should perhaps return an error code of SQLITE_DEADLOCK instead? If an application already has a shared lock and it gets an SQLITE_BUSY while trying to do its first write, it can safely assume that it is in a deadlock situation. Anytime you get an SQLITE_BUSY return on the first write attempt of a transaction, the best way to deal with it is to rollback and retry the entire transaction. If it is inconvenient to rollback and retry the entire transaction, then start the transaction initially with BEGIN EXCLUSIVE. This will acquire the reserved lock immediately (instead of waiting to the first write occurs) and so you will either get an SQLITE_BUSY right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE statement until it works) or you can be assured of never getting another SQLITE_BUSY again until you try to COMMIT (and there too, you can simply rerun COMMIT repeatedly until it works.) I will look into translating selected SQLITE_BUSY returns into SQLITE_DEADLOCK. This will be a slight change in the way things work, so I'll have to move to a new minor version number: 3.4.0. But perhaps that is worth doing. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Re: DLLs containing user-defined SQL functions
Have the main program pass the DLL a callback table. Main and DLL can share the definition of the callback, but the DLL should do a reasonable runtime check that the table it is presented matches the one it expects.
Re: [sqlite] DLLs containing user-defined SQL functions
Hello,
It could be done using something like that :
===
sharedenv.h// used in calling program and DLL.
===
typedef struct{
pointer to functions funA,funB ... etc
} mysharedfuns;
=
Main Program
=
mysharedfuns sharedfuns;
main()
{
sharedfuns.funA= Pointer to the function funA
.. etc
LoadLibray (DLL);
InitDLL(sharedfuns);
}
=
DLL:
=
#include sharedenv.h
mysharedfuns mainfuns;
int InitDLL(mysharefuns *src)
{
mainfuns.funA=src-funA;
etc ..
}
The InitDLL function has to be called from the main program just after the
LoadLibrary.
The shared environment is then initialized; functions funA,funB ... are
callable from the DLL..
Best regards,
Laurent Blanquet.
==
- Original Message -
From: [EMAIL PROTECTED]
To: Sqlite-users sqlite-users@sqlite.org
Sent: Wednesday, June 07, 2006 4:30 PM
Subject: [sqlite] DLLs containing user-defined SQL functions
I'm trying to add the ability to dynamically load DLLs
containing SQL functions and collating sequences to
SQLite. Things are working great on Unix, but I'm having
issues with Windows. Windows experts, please help me.
Suppose the main program (the .exe file) contains a
function procA() and the DLL contains a function procB().
I want procB() to be able to call procA(). The idea
is that the main program uses LoadLibrary() to pull
in the DLL, then GetProcAddress() to find the address
of procB(). Then the main program calls procB() in
the DLL which in turn calls procA() in the main program.
This all works great on Unix. When I use dlopen() to
attach the shared library, the procA() reference in
the shared library is automatically resolved to the
address of procA() in the main program.
But on Windows, I cannot get the DLL to compile because
it is complaining about the missing procA().
Another way to ask the question is this: How do I build
a DLL in windows that can call routines contained in the
main program that attached the DLL using LoadLibrary()?
--
D. Richard Hipp [EMAIL PROTECTED]
- Original Message -
From: [EMAIL PROTECTED]
To: Sqlite-users sqlite-users@sqlite.org
Sent: Wednesday, June 07, 2006 4:30 PM
Subject: [sqlite] DLLs containing user-defined SQL functions
I'm trying to add the ability to dynamically load DLLs
containing SQL functions and collating sequences to
SQLite. Things are working great on Unix, but I'm having
issues with Windows. Windows experts, please help me.
Suppose the main program (the .exe file) contains a
function procA() and the DLL contains a function procB().
I want procB() to be able to call procA(). The idea
is that the main program uses LoadLibrary() to pull
in the DLL, then GetProcAddress() to find the address
of procB(). Then the main program calls procB() in
the DLL which in turn calls procA() in the main program.
This all works great on Unix. When I use dlopen() to
attach the shared library, the procA() reference in
the shared library is automatically resolved to the
address of procA() in the main program.
But on Windows, I cannot get the DLL to compile because
it is complaining about the missing procA().
Another way to ask the question is this: How do I build
a DLL in windows that can call routines contained in the
main program that attached the DLL using LoadLibrary()?
--
D. Richard Hipp [EMAIL PROTECTED]
- Original Message -
From: [EMAIL PROTECTED]
To: Sqlite-users sqlite-users@sqlite.org
Sent: Wednesday, June 07, 2006 4:30 PM
Subject: [sqlite] DLLs containing user-defined SQL functions
I'm trying to add the ability to dynamically load DLLs
containing SQL functions and collating sequences to
SQLite. Things are working great on Unix, but I'm having
issues with Windows. Windows experts, please help me.
Suppose the main program (the .exe file) contains a
function procA() and the DLL contains a function procB().
I want procB() to be able to call procA(). The idea
is that the main program uses LoadLibrary() to pull
in the DLL, then GetProcAddress() to find the address
of procB(). Then the main program calls procB() in
the DLL which in turn calls procA() in the main program.
This all works great on Unix. When I use dlopen() to
attach the shared library, the procA() reference in
the shared library is automatically resolved to the
address of procA() in the main program.
But on Windows, I cannot get the DLL to compile because
it is complaining about the missing procA().
Another way to ask the question is this: How do I build
a DLL in windows that can call routines contained in the
main program that attached the DLL using LoadLibrary()?
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] DLLs containing user-defined SQL functions
Igor Tandetnik [EMAIL PROTECTED] wrote: Note an inherent chicken and egg problem: you can't build two DLLs (or an EXE and a DLL) using this approach where a circular dependency exists, that is, where DLL A needs a function exported from DLL B, and at the same time DLL B needs a function exported from DLL A. To successfully link DLL A, you need an import library from DLL B, but an import library is produced as a side effect of link process, and to link DLL B you need an import library from DLL A, which you can't build until you've built B, ... There is a way to break this circle with the use of so called export files (.exp ), but the technique is rather cumbersome. You don't want to go that way unless there's a gun to your head. It's official then: The lack of sensible shared library loader is yet another reason to avoid windows at all costs. In fact, I'm thinking this reason will go near the top of the list Thanks, everybody, for your help. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] help - sqlite3_get_table returns SQLITE_ROW?
I have a sqlite3_get_table query that sometimes returns error code 100 ( SQLITE_ROW). Can someone explain what condition would cause this - I didn't think that this return code would happen for this function, since it should fill up the 'results' table. The query sometimes works, sometimes not... this is in a 'busy' application, it is not clear what condition causes this return code. It is *possible* that there is a locking problem, but I would have expected a different code (e.g.: SQLITE_BUSY). There is only one process, and only one open connection to the database. The query does not return a large amount of data - 1 or 2 K at most. System resources (i.e.: memory and disk) are more than adequate for the task. Some additional information: This is sqlite version is 3.3.4. nrows and ncols are both 0. result does not appear to be set to 'real' data. sqlite3_errmsg(db) returns an empty string. Thanks *** Doug Fajardo
RE: [sqlite] Problems with multiple threads?
If it is inconvenient to rollback and retry the entire transaction, then start the transaction initially with BEGIN EXCLUSIVE. This will acquire the reserved lock immediately (instead of waiting to the first write occurs) and so you will either get an SQLITE_BUSY right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE statement until it works) or you can be assured of never getting another SQLITE_BUSY again until you try to COMMIT (and there too, you can simply rerun COMMIT repeatedly until it works.) Thanks, I overlooked that by default transactions are DEFERRED in SQLite. It really fixes the problem. As I think about it, if I make _all_ transactions in my application IMMEDIATE, there shouldn't be any risk of a deadlock, right? Thanks, Jiri
RE: [sqlite] Problems with multiple threads?
Beginning everything with BEGIN IMMEDIATE should eliminate the possibility of deadlock, but you will serialize read-only operations. If your transactions are short or contention is low, using BEGIN IMMEDIATE makes things easy. However, if you find that you have a set of read-only operations that run frequently, or take a long time, you may want to consider simply using BEGIN on these (or for single statement reads, you could leave the transaction wrapping out altogether). Pat -Original Message- From: Jiri Hajek [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 11:33 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Problems with multiple threads? If it is inconvenient to rollback and retry the entire transaction, then start the transaction initially with BEGIN EXCLUSIVE. This will acquire the reserved lock immediately (instead of waiting to the first write occurs) and so you will either get an SQLITE_BUSY right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE statement until it works) or you can be assured of never getting another SQLITE_BUSY again until you try to COMMIT (and there too, you can simply rerun COMMIT repeatedly until it works.) Thanks, I overlooked that by default transactions are DEFERRED in SQLite. It really fixes the problem. As I think about it, if I make _all_ transactions in my application IMMEDIATE, there shouldn't be any risk of a deadlock, right? Thanks, Jiri
Re: [sqlite] DLLs containing user-defined SQL functions
Robert Simpson wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 10:36 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DLLs containing user-defined SQL functions It's official then: The lack of sensible shared library loader is yet another reason to avoid windows at all costs. In fact, I'm thinking this reason will go near the top of the list The Windows way does not seem as powerful as the Unix way. I hate the M$ operating systems, but I code for them almost every day. So my next statement isn't so much a defense of Microsoft , but a rebuttal to your assertion that the windows shared library loader is not sensible. The DLL mechanism made sense at the time it was created (8088, 640K ram, windows 1.0 running in real-mode in 320x200x4 graphics - not a lot of room for fancy features). You have to consider how and why the DLL mechanism evolved on windows, and why Microsoft went through so much effort to NOT break backwards compatibility. Microsoft could have fixed lots of design flaws in windows, but in doing so they would have broken the ability for the OS to run older software. They are (or were before Vista anyway) super paranoid about backwards compatibility. Raymond Chen blogs about this often in his Microsoft blog. http://blogs.msdn.com/oldnewthing/archive/category/2282.aspx DLLs were meant to share code AND resource objects back in the win16 days. Once third parties started writing code that took advantage of the way those DLLs worked, Microsoft could not change the interface. Pardon my ignorance about *nix, but what happens during this whole global symbol mapping thing if two libraries both export the same function name? The PE (exe,dll,sys) file format on Windows defines an import table. Each entry in the import table has both a DLL name AND a symbol name (or ordinal import). It is perfectly valid for one PE file to import two objects from two different PEs that both have the same symbol name. Convincing your compiler/linker to produce such a PE import table is left as an exercise to the reader ;) If SQLite only looked to the exe to provide this function, then what would happen to folks writing their own libraries that abstracted the database layer, but wanted to provide their own userdef functions from within their library ... would SQLite find the function in their library instead of the exe? What if the exe and/or two other dependent libraries all exported the function too and had their own userdefs ... how would SQLite handle all these libraries wanting to add their userdefs to SQLite? This is a non-issue. The problem is that sqlite.dll's import table MUST specify a PE source object for each symbol name. How would SQLITE.DLL know the name of your EXE at link time (when the OBJs are turned into the DLL). There are only two real ways for SQLITE to reach back into the EXE. 1) The EXE exports some symbols (for the magic functions that SQLITE wants to call). The EXE loads SQLITE (via import lib or LoadLibrary). The EXE calls a function in Sqlite passing it the HINSTANCE of the EXE. Sqlite uses the instance member (really a pointer to the load address of the PE header of the EXE) as teh first argument to GetProcAddress. 2) The EXE simply marshals the pointers into a structure (or passes them one at a time) into SQLITE by calling a function in SQLITE. But isn't this what we already have? Why are we trying to change it?
[sqlite] OT: SQL Help
With all the other discussions going on with more important matters please excuse this brief interruption.. I have two tables that are created thusly: CREATE TABLE files (filehash VARCHAR(48) UNIQUE, filestamp INTEGER, filename VARCHAR(2048)); CREATE TABLE snapshot (filehash VARCHAR(48) UNIQUE, filestamp INTEGER, filename VARCHAR(2048)); I suck at SQL, so I plead for your advice.. I want to be able to delete all rows from 'files' that do *not* exist in 'snapshot'. My efforts to date have done nothing but leave me with less hair. Thanks. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://pse.2cah.com Controlling pseudoephedrine purchases. http://www.mtsprofessional.com/ A Win32 email server that works for You.
RE: [sqlite] DLLs containing user-defined SQL functions
-Original Message- From: Dennis Jenkins [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 11:46 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DLLs containing user-defined SQL functions Robert Simpson wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 10:36 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DLLs containing user-defined SQL functions Pardon my ignorance about *nix, but what happens during this whole global symbol mapping thing if two libraries both export the same function name? The PE (exe,dll,sys) file format on Windows defines an import table. Each entry in the import table has both a DLL name AND a symbol name (or ordinal import). It is perfectly valid for one PE file to import two objects from two different PEs that both have the same symbol name. Convincing your compiler/linker to produce such a PE import table is left as an exercise to the reader ;) I know how Windows works -- being a Windows programmer :) I was asking about how *nix works. On the surface the *nix way resolving these global symbols seemed like a keen way for some kind of injection attack or something.
Re: [sqlite] DLLs containing user-defined SQL functions
Robert Simpson wrote: -Original Message- From: Dennis Jenkins [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 11:46 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DLLs containing user-defined SQL functions Robert Simpson wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 10:36 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DLLs containing user-defined SQL functions Pardon my ignorance about *nix, but what happens during this whole global symbol mapping thing if two libraries both export the same function name? The PE (exe,dll,sys) file format on Windows defines an import table. Each entry in the import table has both a DLL name AND a symbol name (or ordinal import). It is perfectly valid for one PE file to import two objects from two different PEs that both have the same symbol name. Convincing your compiler/linker to produce such a PE import table is left as an exercise to the reader ;) I know how Windows works -- being a Windows programmer :) I was asking about how *nix works. On the surface the *nix way resolving these global symbols seemed like a keen way for some kind of injection attack or something. You are 100% correct. I misread your email. Reading way too fast I'm not sure how Unix works (elf or a.out file formats). It is probably well documented. It is easy to do injection attacks on either platform. Just put a hacked copy of libc.so on the system (in /tmp even) and modify the user's share library path environment variable before invoking the application. You can do something very similar on windows. Just dump a hacked kernel32.dll into the same directory as the EXE. This might not work with SP2 of XP for system DLLs. However, if the EXE uses a non-system DLL (like libJpeg.dll), then just replace that one. Put some code into the DllMain function that installs whatever hook procedure you need, and viola! You have just compromised the EXE and can do anything on that system that you want that the user running the EXE has the rights to do. On windows you can also simply inject a foreign DLL into a running process. I've written some code to do it. My injected DLL enumerates all of the GDI objects in the ruuning app, allowing my to capture all of the HBITMAPs that back the HDCs. ;)
RE: [sqlite] OT: SQL Help
If filestamp is your identifier, the following statement will do the job... DELETE FROM files WHERE filestamp IN (SELECT filestamp FROM snapshot); I recommend that you check out some tutorials online and at least learn the basics. There's no reason to continue to suck at SQL. A good place to start is: http://www.w3schools.com/sql/default.asp A google search of 'sql tutorial' will turn up options than you can shake a stick at. Zack -Original Message- From: Craig Morrison [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 11:44 AM To: sqlite-users@sqlite.org Subject: [sqlite] OT: SQL Help With all the other discussions going on with more important matters please excuse this brief interruption.. I have two tables that are created thusly: CREATE TABLE files (filehash VARCHAR(48) UNIQUE, filestamp INTEGER, filename VARCHAR(2048)); CREATE TABLE snapshot (filehash VARCHAR(48) UNIQUE, filestamp INTEGER, filename VARCHAR(2048)); I suck at SQL, so I plead for your advice.. I want to be able to delete all rows from 'files' that do *not* exist in 'snapshot'. My efforts to date have done nothing but leave me with less hair. Thanks. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://pse.2cah.com Controlling pseudoephedrine purchases. http://www.mtsprofessional.com/ A Win32 email server that works for You.
Re: [sqlite] DLLs containing user-defined SQL functions
Dennis Jenkins [EMAIL PROTECTED] wrote: The Windows way does not seem as powerful as the Unix way. I hate the M$ operating systems, but I code for them almost every day. So my next statement isn't so much a defense of Microsoft , but a rebuttal to your assertion that the windows shared library loader is not sensible. The DLL mechanism made sense at the time it was created (8088, 640K ram, windows 1.0 running in real-mode in 320x200x4 graphics - not a lot of room for fancy features). You have to consider how and why the DLL mechanism evolved on windows, and why Microsoft went through so much effort to NOT break backwards compatibility. How does introducing a new shared library format that supports automatic bidirectional linking (as in Unix) break backwards compatibility? Nobody says they have to stop supporting DLLs. Just provide something better in addition to DLLs... -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Problems with multiple threads?
* Pat Wibbeler [EMAIL PROTECTED] [2006-06-07 20:50]: Beginning everything with BEGIN IMMEDIATE should eliminate the possibility of deadlock, but you will serialize read-only operations. Why? BEGIN IMMEDIATE acquires a for-read lock. Multiple for-read locks can be acquired concurrently. It is only for-write locks that can only be acquired in the absence of any other locks, which leads to serialisation. Putting all your read operations in BEGIN IMMEDIATE means that all your write operations will be serialised in relation to all other operations taking place, but read operations can proceed apace. Of course, if your writes are short and frequent, they will likely take much longer than necessary if all your operations acquire read locks before they *really* need them. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/
Re: [sqlite] :memory: DB releasing storage
Yes, apparently. The :memory: DB doesn't show the storage being reclaimed by
the OS. In fact, after some more analysis, it's not reusing storage already
allocated. :( Could that be?
After checking the vacuum.c code. It's not doing anything for an in-memory
DB. Would that be handled elsewhere?
/* Get the full pathname of the database file and create a
** temporary filename in the same directory as the original file.
*/
pMain = db-aDb[0].pBt;
zFilename = sqlite3BtreeGetFilename(pMain);
assert( zFilename );
if( zFilename[0]=='\0' ){
/* The in-memory database. Do nothing. Return directly to avoid causing
** an error trying to DETACH the vacuum_db (which never got attached)
** in the exit-handler.
*/
return SQLITE_OK;
}
Thanks,
Rick Keiner
On 6/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Rick Keiner [EMAIL PROTECTED] wrote:
Hello,
Is there anything similar with a :memory: DB to auto-vacuum with a
disk
DB. I would like to release storage that is no longer being used by
SQLite.
Is this possible?
I think auto-vacuum work with :memory: databases just as
well as on-disk database. Are you having problems with it?
--
D. Richard Hipp[EMAIL PROTECTED]
[sqlite] Help. Database 4096mb
Hi. In mine to a database after the size of a file of base has reached 4096 Mb, I am not added any more records. After a command sqlite3_step the mistake (sqlite3_errmsg) stands out: SQL logic error or missing database. Please help, it is necessary for me to process a lot of records. Operation system Windows XP (SP2) sqlite versions 3.3.6 File system NTFS
Re: [sqlite] :memory: DB releasing storage
Rick Keiner [EMAIL PROTECTED] wrote:
Yes, apparently. The :memory: DB doesn't show the storage being reclaimed by
the OS. In fact, after some more analysis, it's not reusing storage already
allocated. :( Could that be?
After checking the vacuum.c code. It's not doing anything for an in-memory
DB. Would that be handled elsewhere?
/* Get the full pathname of the database file and create a
** temporary filename in the same directory as the original file.
*/
pMain = db-aDb[0].pBt;
zFilename = sqlite3BtreeGetFilename(pMain);
assert( zFilename );
if( zFilename[0]=='\0' ){
/* The in-memory database. Do nothing. Return directly to avoid causing
** an error trying to DETACH the vacuum_db (which never got attached)
** in the exit-handler.
*/
return SQLITE_OK;
}
Auto-vacuum and VACUUM, in spite of similar names, are very different
mechanisms. You enable autovacuum by issuing a pragma:
PRAGMA auto_vacuum=ON;
prior to creating any tables in your :memory: database.
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] DLLs containing user-defined SQL functions
[EMAIL PROTECTED] wrote: Dennis Jenkins [EMAIL PROTECTED] wrote: The Windows way does not seem as powerful as the Unix way. I hate the M$ operating systems, but I code for them almost every day. So my next statement isn't so much a defense of Microsoft , but a rebuttal to your assertion that the windows shared library loader is not sensible. The DLL mechanism made sense at the time it was created (8088, 640K ram, windows 1.0 running in real-mode in 320x200x4 graphics - not a lot of room for fancy features). You have to consider how and why the DLL mechanism evolved on windows, and why Microsoft went through so much effort to NOT break backwards compatibility. How does introducing a new shared library format that supports automatic bidirectional linking (as in Unix) break backwards compatibility? Nobody says they have to stop supporting DLLs. Just provide something better in addition to DLLs... -- D. Richard Hipp [EMAIL PROTECTED] The windows DLLs _DO_ support bi-directional linking. A can depend on B and B can depend on A. The windows kernels actually have code to handle this. It is documented in the blog postings last summer that I mentioned earlier. It has done this since win95. Not sure about win 3.11. The problem is that under normal circumstances, you can't create the DLLs like this. You have to create a fake DLL B, generate the real A using fake Bs import library, then use the real A to generate a real B. But for us, A is a user's EXE and B is sqlite3.dll. Not very convenient. The user will be forced to compile their own SQLITE3.DLL file. As proof, consider the following exports from USER32.dll and GDI32.dll. They are circularly linked: tdump \WINDOWS\system32\gdi32.dll | grep \.dll Turbo Dump Version 4.2.16.1 Copyright (c) 1988, 1996 Borland International Imports from KERNEL32.dll Imports from ntdll.dll Imports from USER32.dll Exports from GDI32.dll tdump \WINDOWS\system32\user32.dll | grep \.dll Turbo Dump Version 4.2.16.1 Copyright (c) 1988, 1996 Borland International Imports from GDI32.dll Imports from KERNEL32.dll Imports from ntdll.dll Exports from USER32.dll Can you give a concrete example of what you are trying to do? This is my assumption: 1) You are STATICALLY linking sqlite3 into some program. There is no SQLITE3.DLL. 2) From the point of view of the OS, SQLITE does not exists. There is only the EXE and some system DLLs that you have no control over. 3) The EXE (from the OS point of view) wants to dynamically load a DLL that an sqlite programmer has created. This DLL will export certain symbols, like foo and bar. So the sqlite3 engine will use LoadLibrary and GetProcAddress to obtain function pointers to foo and bar. 4) foo and bar need to call normal (or hidden?) sqlite functions that reside in the EXE. For example, sqlite3_changes or sqlite3_errcode (actual names don't matter). 5) Step #4 fails because the EXE does not export those symbols. You can make the EXE export those symbols by creating a DEF file for the EXE. 6) You could also make this work if the user of SQLITE created a DLL instead of statically linking it in. In this case. both the addon.dll and prog.exe would have imports from sqlite3.dll. This would work beautifully, so long as prog.exe and addon.dll match the sqlite3.dll. Since we should all treat sqlite3* as an opaque structure, this should not be a big problem. 7) Idea from #6 is a no-go if the user is using the Sqlite3 crypto extension, as your license agreement requires that we use the crypto extension in such a way that a third party can't make use of it. IE, we can't put it into the sqlite3.dll file, as someone who did not pay for it could just take the DLL and have the functionality callable from their own app. Therefore, those of us that use the crypto extension in any insecure environment must statically link against sqlite.
Re: [sqlite] Problems with multiple threads?
Bill King wrote: It would be nice if SQLite told us this. However, SQLite detects the reserved lock and returns SQLITE_BUSY, telling niether transaction much other than to try again. If a reserved lock is detected when trying to promote an existing read lock, this is a deadlock situation and should perhaps return an error code of SQLITE_DEADLOCK instead? Christian According to DRH this scenario shouldn't happen. Begin should set a flag, and the second begin will bug out because the flag is set. This is what looks like happening in my scenario, and is definately wrong behaviour. begin should be just that begin, mutually exclusive, unless Dr Hipp want's to implement versioning based transaction schemes. Not, begin maybe i'm read, maybe i'm write, i'll decide later and woe betide any one else who tries to write. It may be wrong behavior, but that's how SQLite works. Sometimes you just have to learn the quirks of the system and then deal with them. File a bug report or submit a patch with the correct behavior and I am sure DRH will be more than happy to review it. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://pse.2cah.com Controlling pseudoephedrine purchases. http://www.mtsprofessional.com/ A Win32 email server that works for You.
Re: [sqlite] DLLs containing user-defined SQL functions
Dennis Jenkins wrote: You can do something very similar on windows. Just dump a hacked kernel32.dll into the same directory as the EXE. This might not work with SP2 of XP for system DLLs. However, if the EXE uses a non-system DLL (like libJpeg.dll), then just replace that one. Put some code into the DllMain function that installs whatever hook procedure you need, and viola! You have just compromised the EXE and can do anything on that system that you want that the user running the EXE has the rights to do. Dennis, This issue with the DLL search order has been changed in Win XP SP1 and later, as the following link explains. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncode/html/secure06122003.asp The pertinent section is copied below. DLL Search Order Has Changed No longer is the current directory searched first when loading DLLs! This change was also made in Windows XP SP1. The default behavior now is to look in all the system locations first, then the current directory, and finally any user-defined paths. This will have an impact on your code if you install a DLL in the application's directory because Windows Server 2003 no longer loads the 'local' DLL if a DLL of the same name is in the system directory. A common example is if an application won't run with a specific version of a DLL, an older version is installed that does work in the application directory. This scenario will fail in Windows Server 2003. The reason this change was made was to mitigate some kinds of trojaning attacks. An attacker may be able to sneak a bad DLL into your application directory or a directory that has files associated with your application. The DLL search order change removes this attack vector. The SetDllDirectory function, also available in Windows XP SP1, modifies the search path used to locate DLLs for the application and affects all subsequent calls to the LoadLibrary and LoadLibraryEx functions by the application. Dennis Cote
Re: [sqlite] Problems with multiple threads?
[EMAIL PROTECTED] wrote: Bill King [EMAIL PROTECTED] wrote: Christian Smith wrote: If one transaction already has a read lock, and another transaction has a reserved lock (trying to get a write lock), neither thread can get a write lock. One of the transactions must abort. Such a sequence might be (in order): Transaction 1: BEGIN; SELECT ... Transaction 2: BEGIN; DELETE ... (SQLITE_BUSY) T1 : UPDATE ... (SQLITE_BUSY) Both transactions are now deadlocked. It would be nice if SQLite told us this. However, SQLite detects the reserved lock and returns SQLITE_BUSY, telling niether transaction much other than to try again. If a reserved lock is detected when trying to promote an existing read lock, this is a deadlock situation and should perhaps return an error code of SQLITE_DEADLOCK instead? According to DRH this scenario shouldn't happen. Begin should set a flag, and the second begin will bug out because the flag is set. This is what looks like happening in my scenario, and is definately wrong behaviour. The flag that is set is private to each sqlite3 database connection. So setting the flag in one connection should not have any effect on any other connection. -- D. Richard Hipp [EMAIL PROTECTED] I understand why I'm getting the deadlock now, lazy locking, (it's against the logical grain of transaction/locking, but that's a whole other argument) . Maybe this should be highlighted with big arrows in the information around multi-threading, as starvation/deadlock happens and often, especially if you get the scenario: begin begin write (fail because of read lock) write ( busy deadlock) commit (fail, busy, read lock). commit (fail, busy, deadlock). Which is quite common in a multi-threaded environment. (Our situation is a multi-threaded directory scanner, inserting file entries into the database, so collision occurs often) The system descends into a sleep/wait scenario then, and with the increasing sleep periods, just... stops. It eventually recovers, but, if I'd known that had I used begin exclusive transaction in the first place to stop this deadlock scenario, I wouldn't have had the levels of grief that I have had. -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
Re: [sqlite] Problems with multiple threads?
Jay Sprenkle wrote: On 6/7/06, Bill KING [EMAIL PROTECTED] wrote: I understand why I'm getting the deadlock now, lazy locking, (it's against the logical grain of transaction/locking, but that's a whole other argument) . Maybe this should be highlighted with big arrows in the information around multi-threading, as starvation/deadlock happens and often, especially if you get the scenario: begin begin write (fail because of read lock) write ( busy deadlock) commit (fail, busy, read lock). commit (fail, busy, deadlock). Why are you putting transactions around single sql statements? There seems to be no benefit to it. Because it's not a single statement, it's several levels of tables, and the whole operation needs to be atomic for system consistency. Which is, essentially what transactions are for. -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
Re: [sqlite] Help. Database 4096 Mb in Windows XP
At 22:56 07/06/2006, you wrote: Hi. In mine to a database after the size of a file of base has reached 4096 Mb, I am not added any more records. After a command sqlite3_step the mistake (sqlite3_errmsg) stands out: SQL logic error or missing database. Please help, it is necessary for me to process a lot of records. Operation system Windows XP (SP2) sqlite versions 3.3.6 File system NTFS Perhaps you have compiled SQLite with a 32 bit IO c library. Compile again with a 64 bit IO library. It's a compiler error, not a SQLite one. HTH Las personas se dividen en tres grupos, los que saben contar y los que no. There are three groups of people, who can count, and who cannot.
Re: [sqlite] DLLs containing user-defined SQL functions
Robert Simpson wrote: Pardon my ignorance about *nix, but what happens during this whole global symbol mapping thing if two libraries both export the same function name? generally the first one is picked, though there's variations between OS. the search order for first is fairly flexible depending on environment settings and other options. there are also strong and weak symbols, where the weak symbol is only picked if no library provides a strong one. some systems also support specifying that all calls to foo in a library refer to the foo in that library, and not any other, but any other library calling foo may land in a different symbol. depending on what you pass to dlopen/dlsym, you can usually find the symbol you want. -- Ted Unangst www.coverity.com Coverity, Inc.
Re: [sqlite] Large DB Performance Questions
Hi Mark, have you tried to do a VACUUM on the database? It helps a lot when it comes to the 'read ahead' feature of the database. Michael Mark Drago wrote: Hello, I'm writing a web cache and I want to use SQLite to store the log of all of the accesses made through the web cache. The idea is to install this web cache in large institutions (1000-5000 workstations). The log database can grow in size very quickly and can reach in to the gigabytes after just a few days. Writing to the database is speedy enough that I haven't seen much of a problem. I collect the data for 1000 web requests and then insert them all in a single transaction using a prepared statement. This works rather well. The problem that I'm encountering has to do with generating reports on the data in the log database. SQLite is showing good performance on some simple queries, but that is not the case once something more advanced is involved, like an aggregate function for example. More over, once the SQLite file is cached in memory it is really quick. However, I can't count on this file being cached at all when a user goes to run the report. So, I've been clearing my file cache before running a test, and then running the same test again now that everything has been loaded in to the cache. Like I said, for most cases SQLite is fine, but here is one example where it doesn't fare as well. The system that I'm running these tests on is a P4 2.8GHz HT with 1 GB of RAM running Fedora Core 5 and using SQLite version 3.3.3 (being as that is what comes with FC5). I'm doing my tests with a database that is 732M in size and contains 1,280,881 records (the DB schema is included below). I clear the file cache by running the following command. I wait until it consumes all of memory and then I kill it: perl -e '@f[0..1]=0' I'm running the tests by running the following script: #!/bin/bash echo $1; | sqlite3 log.db /dev/null The query I'm running is the following: select count(host), host from log group by host; The results include the first time the query is run (when the file is not cached) and then the times of a few runs after that (when the file is cached). SQLite: 221.9s, 1.6s, 1.6s, 1.6s MySQL: 2.2s, 1.8s, 1.8s, 1.8s The MySQL tests were done with the following script: #!/bin/bash mysql -u root --database=log -e $1 /dev/null It is apparent that SQLite is reading the entire database off of the disk and MySQL somehow is not. The MySQL query cache is not in use on this machine and MySQL does not claim very much memory for itself before the test is conducted (maybe 30M). I've tried looking in to the output from 'explain' to see if SQLite was using the index that I have on the 'host' column, but I don't think it is. The output from 'explain' is included below. Note that the 'explain' output is from a different machine which is running SQLite 3.3.5 compiled from source as the SQLite on FC5 kept Segfaulting when I tried to use 'explain'. Any information or ideas on how to speed up this query are greatly appreciated. The only un-implemented idea I have right now is to remove some of the duplicated data from the schema in an attempt to reduce the size of the average row in the table. In some cases I can store just an integer where I'm storing both the integer and a descriptive string (category_name and category_no for example). Some of the other information in the schema holds data about things that are internal to the web cache (profile*, ad*, etc.). Thank you very much for any ideas, Mark. TABLE SCHEMA: CREATE TABLE log( log_no integer primary key, add_dte datetime, profile_name varchar(255), workstation_ip integer, workstation_ip_txt varchar(20), verdict integer, verdict_reason varchar(255), category_name varchar(80), category_no integer, set_user_name varchar(255), profile_zone varchar(40), profile_zone_no integer, author_user_name varchar(255), workstation_name varchar(255), workstation_group_name varchar(255), profile_association varchar(255), profile_association_no integer, protocol varchar(40), connection_type varchar(255), connection_type_no integer, host varchar(255), url text, ad_username varchar(255), ad_groups text, ad_domain varchar(255), ad_workstation_name varchar(255), ad_last_update_dte datetime); INDEXES: CREATE INDEX add_dte ON log (add_dte); CREATE INDEX profile_name ON log(profile_name); CREATE INDEX workstation_ip ON log(workstation_ip); CREATE INDEX verdict ON log (verdict); CREATE INDEX research_zone_no ON log(research_zone_no); CREATE INDEX profile_zone_no ON log(profile_zone_no); CREATE INDEX workstation_name ON log(workstation_name); CREATE INDEX workstation_group_name ON log(workstation_group_name); CREATE INDEX profile_association_no ON log(profile_association_no); CREATE INDEX connection_type_no ON log(connection_type_no); CREATE INDEX host ON log(host); CREATE INDEX ad_username on log(ad_username); CREATE INDEX ad_domain on log(ad_domain); CREATE INDEX ad_workstation_name on
Re: [sqlite] Problems with multiple threads?
* Pat Wibbeler [EMAIL PROTECTED] [2006-06-07 22:55]: It's entirely possible I'm reading these docs incorrectly, but this strategy has worked quite well for me. No, I don’t see any error in your reading. My apologies; I should have consulted the docs instead of going by mailing list posts. It’s interesting that there’s no way to force a SHARED lock to be obtained immediately. The available mechanisms allow serialising write operations with respect to each other, but not forcing a well-defined sequence of read operations relative to write operations. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/
Re: [sqlite] disabling rollback journal
Without journalling, you cannot have a ROLLBACK command. And the semantics of UPDATE become UPDATE OR FAIL instead of the default UPDATE OR ABORT. The difference is subtle, but important. There are other reasons to disable the rollback journal: Suppose you want to create a database for querying only. The real data persistence happens somewhere else. The database is there to organize the data and to access the data. Once the database is filled, it will not be modified. If the filling fails, you try to refill it again. You can also think of using it for a 'bulk fill': you only use is when you file the database with the (massive) initial data. From then on you use sqlite with journaling... Michael -- http://MichaelScharf.blogspot.com/
Re: [sqlite] DLLs containing user-defined SQL functions
What you're trying to do is possible without .def files or import files or whatever. Just put something like this in sqlite3.h before everything: #ifdef _WIN32 #define S3EXPORT __declspec(dllexport) #define S3IMPORT __declspec(dllimport) #define S3CALL __stdcall #ifdef _S3_USER_DLL_IMPLEMENTATION_ #define S3IMPEXP S3EXPORT #else #define S3IMPEXP S3IMPORT #endif #else #define S3EXPORT #define S3IMPORT #define S3CALL #define S3IMPEXP #endif And annotate your exportable function declarations and implementations accordingly. When you compile sqlite3.dll or sqlite3.exe you compile with -U_S3_USER_DLL_IMPLEMENTATION_ or simply do not define it. When a user compiles their own code/DLL they must compile with this flag: -D_S3_USER_DLL_IMPLEMENTATION_ For every single function that you want accessible to 3rd party DLLs you must prepend with the S3CALL macro just before the function name - this also includes function pointers. Before the return code you need to prepend the S3IMPEXP macro. S3IMPEXP int S3CALL sqlite3_open( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb /* OUT: SQLite db handle */ ); Each User DLL must have well known function(s) for sqlite to call the user's code. They must be declared as follows: S3EXPORT int S3CALL MyExampleDllEntryPoint(int whatever); This is a good source of information of how to make Java JNI DLLs with MinGW - basically the same thing you're trying to do: http://www.mingw.org/mingwfaq.shtml#faq-jni-dll Notice that a .def file is nowhere to be seen. All this symbol import/export nonsense does have advantages - it speeds up the loading/linking of symbols in shared libraries, and it reduces symbol pollution when you have to dynamically load/link several shared libraries into the same program, reducing the chance of symbol collision. GCC has recognized this fact and has added a similar feature to its compiler: http://gcc.gnu.org/wiki/Visibility __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Problems with multiple threads?
--- Nathaniel Smith [EMAIL PROTECTED] wrote: On Wed, Jun 07, 2006 at 01:24:38PM -0400, [EMAIL PROTECTED] wrote: If it is inconvenient to rollback and retry the entire transaction, then start the transaction initially with BEGIN EXCLUSIVE. This will acquire the reserved lock immediately (instead of waiting to the first write occurs) and so you will either get an SQLITE_BUSY right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE statement until it works) or you can be assured of never getting another SQLITE_BUSY again until you try to COMMIT (and there too, you can simply rerun COMMIT repeatedly until it works.) It would be convenient to have another form of BEGIN, in between DEFERRED and IMMEDIATE, whose effect was to immediately acquire the shared lock. That would allow read-only transactions to get this same level of programming convenience you describe, where one only has to be able to handle SQLITE_BUSY in one place. (Of course, one could simulate this now by immediately running a meaningless SELECT after each call to BEGIN, solely for the side-effect of acquiring the lock, but it seems less elegant and perhaps not guaranteed to continue working in the future.) Bill King's idea about using reader/writer locks is a much better idea and far less error prone and should be built into the SQLite library itself. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
