Re: [sqlite] Building System.Data.SQLite
The suggested command did create the win32 setup for me, but I'd also like the x64 setup and this replaced: set_Release_x86_2010.bat with set_Release_x64_2010.bat and build again. This however also build the x86 target for some reason and no x64 setup file is produced (bake fails, complaining about missing x64 files). Can I install something that would make the whole build complete without having VS2008? Seems this is the easiest way, short of decoding all the bat files for their meaning. -- Bernhard -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: 10. august 2012 19:01 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Building System.Data.SQLite Bernhard Mogens Ege wrote: I have been looking at https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki to build System.Data.SQLite https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki%20to%20b uild%20System.Data.SQLite but when using the manual build, I do not get the setup files built.. The setup packages are created using Inno Setup, an open source tool available here: http://www.jrsoftware.org/ Once that tool is installed, the Setup\bake*.bat tools may be used to build the setup packages for System.Data.SQLite. Various other batch files in that same directory contain the environment variable settings needed by these tools. Then I use the automated build and follow the steps which work fine until step 11: bake_all.bat that complains about: Could not bake setup for Release/x86/2008. The bake_all.bat tool assumes that binaries have been built for all supported platforms and framework versions. When that is not the case, the bake.bat tool may be used instead. However, that tool is a bit more complex because it was not really intended to be called directly from the command line. Here is an example that will build the setup using the .NET 4.0-based setup for x86 (this assumes that the build.bat tool already completed successfully): CD /D %ROOT%\Setup vsSp.bat set_common.bat set_Release_x86_2010.bat bake.bat -- Joe Mistachkin ___ 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] Building System.Data.SQLite
Bernhard Mogens Ege wrote: This however also build the x86 target for some reason and no x64 setup file is produced (bake fails, complaining about missing x64 files). This means that the x64 binaries were not built first using build.bat. You should be able to build binaries for all supported platforms and versions of Visual Studio (as installed on your box) by making use of the build_all.bat tool instead. Can I install something that would make the whole build complete without having VS2008? Seems this is the easiest way, short of decoding all the bat files for their meaning. With the exception of the Visual Studio designer integration components, there is nothing really to install, per se. In fact, it is very highly recommended that the deployment of the System.Data.SQLite DLL and related components be done on an app-local basis, with the GAC being avoided altogether. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building System.Data.SQLite
But I did use build.bat after using set_Release_x64_2010.bat. This is what is so strange. Build_all.bat fails as it seems vsSp.bat detects that I have VS2008 installed, but this is installed by SQL 2008 R2 and lacks pretty much everything (used for business intelligence design only and has no C#/C++ compiler). I looked closer at build_all.bat and ended up with these commands that created the 2010 Win32/x64 setup files: SET YEARS=2010 Build_all.bat Bake_all.bat This seems to have made the build system skip the VS2008 generation completely and without errors, but I am not certain it is a safe way to do it (seems so, though). Final question: what is the difference between the bundle and non-bundle setup files? I tried viewing the download page but I cannot view it. I only get the About wiki page. I tried Firefox, Opera, Chrome and IE9 and none of them could show the download page. Cheers, Bernhard -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: 13. august 2012 11:51 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Building System.Data.SQLite Bernhard Mogens Ege wrote: This however also build the x86 target for some reason and no x64 setup file is produced (bake fails, complaining about missing x64 files). This means that the x64 binaries were not built first using build.bat. You should be able to build binaries for all supported platforms and versions of Visual Studio (as installed on your box) by making use of the build_all.bat tool instead. Can I install something that would make the whole build complete without having VS2008? Seems this is the easiest way, short of decoding all the bat files for their meaning. With the exception of the Visual Studio designer integration components, there is nothing really to install, per se. In fact, it is very highly recommended that the deployment of the System.Data.SQLite DLL and related components be done on an app-local basis, with the GAC being avoided altogether. -- Joe Mistachkin ___ 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] how to update the Moving average value
Earlier this year I was bothering this list with many questions about the sqlite virtual table support, because I needed (among other things) an efficient way to compute moving averages. And here is the result, free for anyone to play with: http://voksenlia.net/sqlite3/interpolate.c Rather than to average the last X values, it assumes that a table has a timestamp associated with the values that will be averaged, and what's going to be calculated is the average for a given period. The average of angles is also supported. It's a module which will take existing tables as input and create a virtual table with new columns for the moving average. It also allows you to look up any timestamp regardless of whether that timestamp exists in the underlying table(s). The returned value will be interpolated. It was written to be efficient on big datasets, but may become slow if used with virtual tables as input (or used recursively). My main use for this is to access weather data that I have stored. For instance, I have temperature (temp_out) stored somewhat irregularly at roughly 5 minute intervals, and I can now look up temp_out for any timestamp. Also, if I want the moving 24h temperature, I specify that in the virtual table declaration, and I can look up a new column temp_out__avg which will give me the moving average and it will work even if there are gaps in the data. More details are described in the comments of the C file. The module allowed me to make a web interface for my weather data: http://voksenlia.net/met/data/plot.php (Norwegian only) -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bug in windows 98
Hello from france, I have a bug with windows 98 and sqlite3.dll : Missing export kernel32.dll SetFilePointerEx The same code with previous version have no error. Regards, Roger// ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Réf. : Re: Make System.Data.SQLite 1.0.81.0 work with .NET 4.0. How?
I've managed to make it work. What I've done: - Uninstall SQLite provider for VS (both 3.5 and 4.0 versions were installed) - Download dlls for .NET 4.0 - Install SQLite provider for .net 4 and VS2010 sqlite-netFx40-setup-bundle-x86-2010-1.0.81.0.exe - Update my connection string in parent project Thanks for the help. - Jean Internet be...@web.de Envoyé par : sqlite-users-boun...@sqlite.org 08/11/2012 06:01 PM Veuillez répondre à sqlite-users@sqlite.org Pour sqlite-users@sqlite.org cc Objet Re: [sqlite] Make System.Data.SQLite 1.0.81.0 work with .NET 4.0. How? Am 10.08.2012 19:14, schrieb jean.nkengto...@externe.bnpparibas.com: Hello, I bet somebody already managed to make this work. Unhappily I can't access archives. Here is my problem: I'm building a Console Application to load and fill one SQLite DB. We recently decided to switch the application to framework 4.0, from framework 3.5. I've downloaded and deployed dll 1.0.81.0 on my application (added to project's references) from there: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki. Now on run, I'm stumbling on this error: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information. To me it looks like the compiler is trying to load the wrong dll, maybe from the GAC. Did you download one of these archives? - sqlite-netFx40-static-binary-bundle-x64-2010-1.0.81.0.zip - sqlite-netFx40-static-binary-bundle-Win32-2010-1.0.81.0.zip Usually I don't use the setup-files as I don't need the Visual Studio integration. You should be fine unzipping the system.data.sqlite.dll that fits your system and throw it into your bin-folder and you should be good to go. After some copy, replace of dlls, following http://www.tsjensen.com/blog/post/2011/06/04/SQLite+On+Visual+Studio+2010+Setup+Instructions.aspx , I still don't get it to work: Impossible to load metadata resource specified I saw some threads asking to add useLegacyV2RuntimeActivationPolicy, but that doesn't work. Plus, why should I add this, knowing that version 1.0.81.0 is supposed to be built in v4.0? I've never had to mess around with any hacks, nor should you. If you don't absolutely need the VS-Integration and installed the setup, you could try uninstalling it. Bernd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This message and any attachments (the message) is intended solely for the intended addressees and is confidential. If you receive this message in error,or are not the intended recipient(s), please delete it and any copies from your systems and immediately notify the sender. Any unauthorized view, use that does not comply with its purpose, dissemination or disclosure, either whole or partial, is prohibited. Since the internet cannot guarantee the integrity of this message which may not be reliable, BNP PARIBAS (and its subsidiaries) shall not be liable for the message if modified, changed or falsified. Do not print this message unless it is necessary,consider the environment. -- Ce message et toutes les pieces jointes (ci-apres le message) sont etablis a l'intention exclusive de ses destinataires et sont confidentiels. Si vous recevez ce message par erreur ou s'il ne vous est pas destine, merci de le detruire ainsi que toute copie de votre systeme et d'en avertir immediatement l'expediteur. Toute lecture non autorisee, toute utilisation de ce message qui n'est pas conforme a sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite. L'Internet ne permettant pas d'assurer l'integrite de ce message electronique susceptible d'alteration, BNP Paribas (et ses filiales) decline(nt) toute responsabilite au titre de ce message dans l'hypothese ou il aurait ete modifie, deforme ou falsifie. N'imprimez ce message que si necessaire, pensez a l'environnement. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, the page cache is shared across threads. That means that each thread must acquire a mutex on the page cache in order to read it. Which means that access to the page cache is serialized. I just ran our concurrency test in different configurations and I observer a similar behavior. That is, in the shared-cache mode, read-only transactions on the same table are executed pretty much sequentially. Also, your explanation doesn't feel quite satisfactory to me. In his original email, Eric mentioned that his table contains just 50 rows. Surely all this data would be loaded into the cache the first time it is requested and then accessed concurrently by all the threads. The only way I can see how the sequential performance could be explained here is if the cache mutex did not distinguish between readers and writers (which would seem to be a fairly natural thing to do). In our test, on the other hand, each thread queries its own set of rows from the table. So, based on your explanation, here each thread should end up with its own set of pages (more or less). However, even in this case, I still observe a near sequential performance. Any idea what else might be going on here? Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
On Mon, Aug 13, 2012 at 8:51 AM, Boris Kolpackov bo...@codesynthesis.comwrote: Also, your explanation doesn't feel quite satisfactory to me. In shared-cache mode, SQLite acquires a mutex on the shared cache, thus serializing access. The mutex is acquired because it is usually needed. You can argue that there are special cases where the mutex is not strictly needed. That point is debatable. But in any case, SQLite does not attempt to implement those special cases. For safety and reliability, SQLite always acquires a mutex when accessing the shared cache. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
The shared cache does not know that the table is small nor that there is nothing else to load. When a thread accesses that shared cache, it must protect itself from the data page it's on being modified, either because the page is simply flushed from the cache (if the cache does such things) or replaced by another page because this one happened to be the least recently used (or whatever rule the cache uses). (As with others, I haven't actually looked at the code.) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Boris Kolpackov Sent: Monday, August 13, 2012 8:51 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multi-Thread Reads to SQLite Database Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, the page cache is shared across threads. That means that each thread must acquire a mutex on the page cache in order to read it. Which means that access to the page cache is serialized. I just ran our concurrency test in different configurations and I observer a similar behavior. That is, in the shared-cache mode, read-only transactions on the same table are executed pretty much sequentially. Also, your explanation doesn't feel quite satisfactory to me. In his original email, Eric mentioned that his table contains just 50 rows. Surely all this data would be loaded into the cache the first time it is requested and then accessed concurrently by all the threads. The only way I can see how the sequential performance could be explained here is if the cache mutex did not distinguish between readers and writers (which would seem to be a fairly natural thing to do). In our test, on the other hand, each thread queries its own set of rows from the table. So, based on your explanation, here each thread should end up with its own set of pages (more or less). However, even in this case, I still observe a near sequential performance. Any idea what else might be going on here? Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ 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] Any plans to provide the sqlite3RunParser in the API
Hi, I've recently started using sqlite3 c-library. I'm using the in-memory databases which are pretty fast since I'm loading data dynamically into sqlite3 from a different backend (had to do this as we wanted to provide an SQL interface to our users). It would be very nice if I could also use sqlite3RunParser to get the list of tables I need to load into in-memory databases. Currently, I'm loading all the tables, which is a performance hit. Thanks in advance for suggestions / advices. bishwa ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, SQLite acquires a mutex on the shared cache, thus serializing access. Oh, that's quite a surprise. I would have never said that something like this is going on just by reading the documentation page on shared cache: http://www.sqlite.org/sharedcache.html Specifically, quoting Section 2.2, Table Level Locking: When two or more connections use a shared-cache, locks are used to serialize concurrent access attempts on a per-table basis. Tables support two types of locks, read-locks and write-locks. Locks are granted to connections - at any one time, each database connection has either a read-lock, write-lock or no lock on each database table. At any one time, a single table may have any number of active read-locks or a single active write lock. To read data [from] a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. From this I at the time concluded that there could be multiple connections reading from a table concurrently. But it appears I was wrong. Perhaps this page should be updated to reflect this. Specifically, the last paragraph in Section 2.0: There are three levels to the shared-cache locking model, transaction level locking, table level locking and schema level locking. They are described in the following three sub-sections. Should probably read something along these lines: There are four levels to the shared-cache locking model: transaction level locking, table level locking, cache level locking, and schema level locking. They are described in the following three sub-sections. Then a new section should be inserted between 2.2 and 2.3 that reads along these lines: 2.3 Cache Level Locking When a connection needs to access (read or write) data from a table, it must acquire an exclusive mutex on the shared cache. In particular, this means that in the shared cache mode actual access to the database pages, regardless of whether for reading or writing, is always serialized. If you find it suitable, feel free to use this to update the documentation. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Another idea for you: In our test, on the other hand, each thread queries its own set of rows from the table. So, based on your explanation, here each thread should end up with its own set of pages (more or less). However, even in this case, I still observe a near sequential performance. Even though each thread works basically with its own set of pages they all use the same cache and thus are subject to the combined limit on total number of pages in the cache. And to keep that combined limit working they need to maintain a combined LRU list of pages to know which pages should be evicted from cache first in case that's needed. Pavel On Mon, Aug 13, 2012 at 5:51 AM, Boris Kolpackov bo...@codesynthesis.com wrote: Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, the page cache is shared across threads. That means that each thread must acquire a mutex on the page cache in order to read it. Which means that access to the page cache is serialized. I just ran our concurrency test in different configurations and I observer a similar behavior. That is, in the shared-cache mode, read-only transactions on the same table are executed pretty much sequentially. Also, your explanation doesn't feel quite satisfactory to me. In his original email, Eric mentioned that his table contains just 50 rows. Surely all this data would be loaded into the cache the first time it is requested and then accessed concurrently by all the threads. The only way I can see how the sequential performance could be explained here is if the cache mutex did not distinguish between readers and writers (which would seem to be a fairly natural thing to do). In our test, on the other hand, each thread queries its own set of rows from the table. So, based on your explanation, here each thread should end up with its own set of pages (more or less). However, even in this case, I still observe a near sequential performance. Any idea what else might be going on here? Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ 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] Multi-Thread Reads to SQLite Database
There are four levels to the shared-cache locking model: transaction level locking, table level locking, cache level locking, and schema level locking. They are described in the following three sub-sections. This would be a bad way of documenting this because cache level locking works on a completely different level than transaction, table or schema level locking. The exclusive mutex is taken inside one call to sqlite3_step() and released upon exit. But all other locks have meta-level notion and work between calls to sqlite3_*() functions. Pavel On Mon, Aug 13, 2012 at 6:45 AM, Boris Kolpackov bo...@codesynthesis.com wrote: Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, SQLite acquires a mutex on the shared cache, thus serializing access. Oh, that's quite a surprise. I would have never said that something like this is going on just by reading the documentation page on shared cache: http://www.sqlite.org/sharedcache.html Specifically, quoting Section 2.2, Table Level Locking: When two or more connections use a shared-cache, locks are used to serialize concurrent access attempts on a per-table basis. Tables support two types of locks, read-locks and write-locks. Locks are granted to connections - at any one time, each database connection has either a read-lock, write-lock or no lock on each database table. At any one time, a single table may have any number of active read-locks or a single active write lock. To read data [from] a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. From this I at the time concluded that there could be multiple connections reading from a table concurrently. But it appears I was wrong. Perhaps this page should be updated to reflect this. Specifically, the last paragraph in Section 2.0: There are three levels to the shared-cache locking model, transaction level locking, table level locking and schema level locking. They are described in the following three sub-sections. Should probably read something along these lines: There are four levels to the shared-cache locking model: transaction level locking, table level locking, cache level locking, and schema level locking. They are described in the following three sub-sections. Then a new section should be inserted between 2.2 and 2.3 that reads along these lines: 2.3 Cache Level Locking When a connection needs to access (read or write) data from a table, it must acquire an exclusive mutex on the shared cache. In particular, this means that in the shared cache mode actual access to the database pages, regardless of whether for reading or writing, is always serialized. If you find it suitable, feel free to use this to update the documentation. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ 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] Multi-Thread Reads to SQLite Database
I wonder if it would be possible to refine the cache locking mechanism. If I understand the modified DB/Table locking semantics when running under a shared-cache, it appears that the cache page should be protected against readers and writers. Perhaps only the list of pages in the cache need to be protected by a higher-level mutex? That is, if you want access to a page, you grab the mutex, flag the page as 'in-use' with a reference counter, release the mutex, and go on about your business. If you have multiple readers, they would be able to access the physical page concurrently. When access is complete, the reference count would be decremented. Marc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
On 13 Aug 2012, at 3:11pm, Marc L. Allen mlal...@outsitenetworks.com wrote: I wonder if it would be possible to refine the cache locking mechanism. If I understand the modified DB/Table locking semantics when running under a shared-cache, it appears that the cache page should be protected against readers and writers. All shared assets must be protected against readers and writers. A reader needs to know that nothing is going to scramble the data while it's reading. A writer must know that nothing is reading the data it's about to change. (A little handwaving and a few exceptions, but that's the gist of it.) Perhaps only the list of pages in the cache need to be protected by a higher-level mutex? That is, if you want access to a page, you grab the mutex, flag the page as 'in-use' with a reference counter, release the mutex, and go on about your business. You are proposing one mutex per page. This is a system which many DBMSes use but it would /greatly/ slow down SQLite. Also it would complicate the code quite a bit since a write to one page often leaks over to neighbouring pages. If you have multiple readers, they would be able to access the physical page concurrently. When access is complete, the reference count would be decremented. To get the effect of this, simply stop using shared-cache. Let each process have its own cache. That way each process knows nothing is messing with its cache. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
I don't believe I'm suggesting one mutex per page. If I understand correctly, the purpose of the overall mutex is to prevent a page from being removed underneath a user. If the standard DB locking semantics are working properly, I think there is no possibility of a page's data from being modified underneath another user. That would be no different than a physical DB page being modified underneath another user. If the above it true, cache protection semantics are strictly concerned with page management. That is, a page is requested that is not in the cache and needs to be inserted into it. If the cache is full, another page needs to be released. All that is required is protecting pages currently in use from being released. I think, instead of a mutex serializing access to the entire cache, all that is needed is a mutex serializing access to the cache meta-data and the use of reference counts to help the page replacement algorithm make a good choice in which page to remove. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, August 13, 2012 10:23 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Multi-Thread Reads to SQLite Database On 13 Aug 2012, at 3:11pm, Marc L. Allen mlal...@outsitenetworks.com wrote: I wonder if it would be possible to refine the cache locking mechanism. If I understand the modified DB/Table locking semantics when running under a shared-cache, it appears that the cache page should be protected against readers and writers. All shared assets must be protected against readers and writers. A reader needs to know that nothing is going to scramble the data while it's reading. A writer must know that nothing is reading the data it's about to change. (A little handwaving and a few exceptions, but that's the gist of it.) Perhaps only the list of pages in the cache need to be protected by a higher-level mutex? That is, if you want access to a page, you grab the mutex, flag the page as 'in-use' with a reference counter, release the mutex, and go on about your business. You are proposing one mutex per page. This is a system which many DBMSes use but it would /greatly/ slow down SQLite. Also it would complicate the code quite a bit since a write to one page often leaks over to neighbouring pages. If you have multiple readers, they would be able to access the physical page concurrently. When access is complete, the reference count would be decremented. To get the effect of this, simply stop using shared-cache. Let each process have its own cache. That way each process knows nothing is messing with its cache. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building System.Data.SQLite
This doesn't appear to build everything needed as when I install the bundles (x86 and x64), nothing is added to the global assembly cache in C:\windows\assembly\ (did ask the installer to add to the GAC), making every binary using System.Data.SQLite fail. The files are put into C:\Program Files\.. as expected, though, but binaries do not know to look there. Also, the Designer adding for VS2010 isn't working properly as it isn't possible to enter a path with a space in it (Illegal sharacters in path.), making the VS addin a no-go. This is a quite annoying error which makes it very difficult for me to modify my sqlite models; close all VS2010, remove sqlite 1.0.82.0, possibly reboot, install 1.0.81.0 incl. VS plugin, start vs2010 and load my project, modify my sqlite model, close vs2010, uninstall 1.0.81.0, possibly reboot, install 1.0.82.0, start and load my project, test, continue programming. Maybe I can just use the Designer.dll from 1.0.81.0 with the rest from 1.0.82.0? That would be worth a try. This is getting a bit difficult for me to solve. :-/ -- Bernhard -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bernhard Mogens Ege Sent: 13. august 2012 12:32 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Building System.Data.SQLite But I did use build.bat after using set_Release_x64_2010.bat. This is what is so strange. Build_all.bat fails as it seems vsSp.bat detects that I have VS2008 installed, but this is installed by SQL 2008 R2 and lacks pretty much everything (used for business intelligence design only and has no C#/C++ compiler). I looked closer at build_all.bat and ended up with these commands that created the 2010 Win32/x64 setup files: SET YEARS=2010 Build_all.bat Bake_all.bat This seems to have made the build system skip the VS2008 generation completely and without errors, but I am not certain it is a safe way to do it (seems so, though). Final question: what is the difference between the bundle and non-bundle setup files? I tried viewing the download page but I cannot view it. I only get the About wiki page. I tried Firefox, Opera, Chrome and IE9 and none of them could show the download page. Cheers, Bernhard -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: 13. august 2012 11:51 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Building System.Data.SQLite Bernhard Mogens Ege wrote: This however also build the x86 target for some reason and no x64 setup file is produced (bake fails, complaining about missing x64 files). This means that the x64 binaries were not built first using build.bat. You should be able to build binaries for all supported platforms and versions of Visual Studio (as installed on your box) by making use of the build_all.bat tool instead. Can I install something that would make the whole build complete without having VS2008? Seems this is the easiest way, short of decoding all the bat files for their meaning. With the exception of the Visual Studio designer integration components, there is nothing really to install, per se. In fact, it is very highly recommended that the deployment of the System.Data.SQLite DLL and related components be done on an app-local basis, with the GAC being avoided altogether. -- Joe Mistachkin ___ 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] Any plans to provide the sqlite3RunParser in the API
On Mon, Aug 13, 2012 at 03:43:46PM +0200, Bishwa Shrestha scratched on the wall: Hi, I've recently started using sqlite3 c-library. I'm using the in-memory databases which are pretty fast since I'm loading data dynamically into sqlite3 from a different backend (had to do this as we wanted to provide an SQL interface to our users). If you want to provide an SQL interface to an existing data source, I would strongly recommend you look at Virtual Tables. They look and smell like normal tables, but all of their access and data generation is done via application level code. You can write a series of functions that essentially slip in between the SQL engine and the data access layer, and can redirect requests for table data to an external source. Virtual Tables can be read-write or read-only. Read-only tables are pretty fast to implement, while read-write tables would allow external applications to modify the backend (no idea if that is desirable or not in your case). Virtual Tables are an extremely powerful and often overlooked part of SQLite. You can do some pretty amazing and powerful things with them, as evident by the Full Text Search engine in SQLite, as well as the R-Trees extension, both of which are built on top of Virtual Tables. SQLite docs: http://www.sqlite.org/vtab.html http://www.sqlite.org/c3ref/create_module.html Virtual Tables are also covered fairly well in Using SQLite: http://shop.oreilly.com/product/9780596521196.do -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Pavel, Pavel Ivanov paiva...@gmail.com writes: There are four levels to the shared-cache locking model: transaction level locking, table level locking, cache level locking, and schema level locking. They are described in the following three sub-sections. This would be a bad way of documenting this because cache level locking works on a completely different level than transaction, table or schema level locking. The exclusive mutex is taken inside one call to sqlite3_step() and released upon exit. But all other locks have meta-level notion and work between calls to sqlite3_*() functions. I agree. But I also think that documenting it like this is better than not documenting it at all. This read serialization behavior of SQLite was a complete surprise to me. Maybe instead of documenting it, we should just fix it. I personally will rather write code than documentation ;-). Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Marc, Marc L. Allen mlal...@outsitenetworks.com writes: If I understand correctly, the purpose of the overall mutex is to prevent a page from being removed underneath a user. If the standard DB locking semantics are working properly, I think there is no possibility of a page's data from being modified underneath another user. Yes, that's my understanding also. This is all taken care of at higher (transaction and table) locking levels. If the above it true, cache protection semantics are strictly concerned with page management. That is, a page is requested that is not in the cache and needs to be inserted into it. If the cache is full, another page needs to be released. All that is required is protecting pages currently in use from being released. I think, instead of a mutex serializing access to the entire cache, all that is needed is a mutex serializing access to the cache meta-data and the use of reference counts to help the page replacement algorithm make a good choice in which page to remove. All sounds very sensible to me. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in windows 98
roger 66 wrote: I have a bug with windows 98 and sqlite3.dll : Missing export kernel32.dll SetFilePointerEx Would you try again with the latest trunk code and see if that clears the issue? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building System.Data.SQLite
Bernhard Mogens Ege wrote: Build_all.bat fails as it seems vsSp.bat detects that I have VS2008 installed, but this is installed by SQL 2008 R2 and lacks pretty much everything (used for business intelligence design only and has no C#/C++ compiler). Yeah, the Visual Studio detection logic in the batch file tools is somewhat limited. I looked closer at build_all.bat and ended up with these commands that created the 2010 Win32/x64 setup files: SET YEARS=2010 Build_all.bat Bake_all.bat Those commands seem perfectly reasonable to me. In fact, the YEARS environment variable *is* designed to be overridden in such a manner just in case the Visual Studio detection logic will not work correctly on a particular machine. This seems to have made the build system skip the VS2008 generation completely and without errors, but I am not certain it is a safe way to do it (seems so, though). Yes, this should be fine. Final question: what is the difference between the bundle and non-bundle setup files? I tried viewing the download page but I cannot view it. I only get the About wiki page. I tried Firefox, Opera, Chrome and IE9 and none of them could show the download page. The bundle packages contain the mixed-mode assembly, which itself contains both the native and managed code used for interacting with SQLite. The non-bundle packages contain separate native and managed assemblies that are used together to interact with SQLite. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building System.Data.SQLite
Bernhard Mogens Ege wrote: This doesn't appear to build everything needed as when I install the bundles (x86 and x64), nothing is added to the global assembly cache in C:\windows\assembly\ (did ask the installer to add to the GAC), making every binary using System.Data.SQLite fail. The files are put into C:\Program Files\.. as expected, though, but binaries do not know to look there. Installing the System.Data.SQLite DLLs in the GAC is not normally recommended. Instead, they should probably be installed along side the application binaries (i.e. app-local). Setting a reference to the managed (or mixed-mode) assembly for System.Data.SQLite is normally all that is required to make the applications work as the DLL should then automatically be copied to the build output directory. If you have Visual Studio 2010, then you may want to look into using the official NuGet package for System.Data.SQLite, seen here: http://nuget.org/packages/System.Data.SQLite Also, the Designer adding for VS2010 isn't working properly as it isn't possible to enter a path with a space in it (Illegal sharacters in path.), making the VS addin a no-go. This is a quite annoying error which makes it very difficult for me to modify my sqlite models; close all VS2010, remove sqlite 1.0.82.0, possibly reboot, install 1.0.81.0 incl. VS plugin, start vs2010 and load my project, modify my sqlite model, close vs2010, uninstall 1.0.81.0, possibly reboot, install 1.0.82.0, start and load my project, test, continue programming. Maybe I can just use the Designer.dll from 1.0.81.0 with the rest from 1.0.82.0? That would be worth a try. The designer components are a bit tricky to get working right, even in the best of circumstances. They actually *do* require the setup package be run in order to operate properly. Also, since Visual Studio is always a 32-bit native application, that setup package is the one that must be used. Here is the direct link to the correct package: http://system.data.sqlite.org/downloads/1.0.81.0/sqlite-netFx40-setup-bundle -x86-2010-1.0.81.0.exe -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to recognize a sqlite database file?
How can I judge whether a file is a database file? Is there a function that I can use for this purpose? Help! Hope for you reply. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
On 14 Aug 2012, at 2:57am, daedae11 daeda...@126.com wrote: How can I judge whether a file is a database file? Is there a function that I can use for this purpose? You can use the SQLite library call to open it and see whether you get an error back. But it might be better to open the file as binary/text and read the beginning of it. All SQLite files begin with SQLite format 3 and then a 0x00 character. Of course, there's nothing to stop someone writing such a file themself but it would have to be a joke. See http://www.sqlite.org/fileformat.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
In linux I use the command 'file yourfilenamehere' and I get as an answer something like 'yourfilenamehere: SQLite 3.x database' if it is an Sqlite3 database. Regards, Luis On Tue, Aug 14, 2012 at 09:57:33AM +0800, daedae11 wrote: How can I judge whether a file is a database file? Is there a function that I can use for this purpose? Help! Hope for you reply. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- o W. Luis Mochán, | tel:(52)(777)329-1734 /(*) Instituto de Ciencias Físicas, UNAM | fax:(52)(777)317-5388 `/ /\ Apdo. Postal 48-3, 62251 | (*)/\/ \ Cuernavaca, Morelos, México | moc...@fis.unam.mx /\_/\__/ O ascii ribbon campaign - stop html mail - www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
Thank you. I got it. And I have another question. Does function sqlite3_exec support UTF-16? At 2012-08-14 10:01:13,Simon Slavin slav...@bigfraud.org wrote: On 14 Aug 2012, at 2:57am, daedae11 daeda...@126.com wrote: How can I judge whether a file is a database file? Is there a function that I can use for this purpose? You can use the SQLite library call to open it and see whether you get an error back. But it might be better to open the file as binary/text and read the beginning of it. All SQLite files begin with SQLite format 3 and then a 0x00 character. Of course, there's nothing to stop someone writing such a file themself but it would have to be a joke. See http://www.sqlite.org/fileformat.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
On 14 Aug 2012, at 3:05am, daedae11 daeda...@126.com wrote: Thank you. I got it. And I have another question. Does function sqlite3_exec support UTF-16? No. sqlite3_exec() is a wrapper around sqlite3_prepare_v2(), and sqlite3_prepare_v2() expects UTF-8. For UTF-16 you should be using sqlite3_prepare16_v2() . But for some reason there's no sqlite3_exec16() . However, _exec is relatively simple to write. You can see what it does here: http://sqlite.org/c3ref/exec.html So all you should need to do is sqlite3_prepare16_v2() sqlite3_step() andsqlite3_finalize() Good luck with it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
If I use the group of: sqlite3_prepare16_v2() sqlite3_step() andsqlite3_finalize() how can I get the names of a table's columns from a SELECT sql sentence? At 2012-08-14 10:11:31,Simon Slavin slav...@bigfraud.org wrote: On 14 Aug 2012, at 3:05am, daedae11 daeda...@126.com wrote: Thank you. I got it. And I have another question. Does function sqlite3_exec support UTF-16? No. sqlite3_exec() is a wrapper around sqlite3_prepare_v2(), and sqlite3_prepare_v2() expects UTF-8. For UTF-16 you should be using sqlite3_prepare16_v2() . But for some reason there's no sqlite3_exec16() . However, _exec is relatively simple to write. You can see what it does here: http://sqlite.org/c3ref/exec.html So all you should need to do is sqlite3_prepare16_v2() sqlite3_step() andsqlite3_finalize() Good luck with it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
daedae11 daeda...@126.com wrote: If I use the group of: sqlite3_prepare16_v2() sqlite3_step() andsqlite3_finalize() how can I get the names of a table's columns from a SELECT sql sentence? sqlite3_column_name[16] -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
On 14 Aug 2012, at 4:14am, daedae11 daeda...@126.com wrote: If I use the group of: sqlite3_prepare16_v2() sqlite3_step() andsqlite3_finalize() how can I get the names of a table's columns from a SELECT sql sentence? To get the names of columns of a table, use the SQL command PRAGMA table_info(table-name) as documented here: http://www.sqlite.org/pragma.html#pragma_table_info You can use the above sequence to execute the PRAGMA command. It works exactly the same way as if you were executing a SELECT command. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recognize a sqlite database file?
Thank you. And I found these functions: const char *sqlite3_column_database_name(sqlite3_stmt*,int); const void *sqlite3_column_database_name16(sqlite3_stmt*,int); const char *sqlite3_column_table_name(sqlite3_stmt*,int); const void *sqlite3_column_table_name16(sqlite3_stmt*,int); const char *sqlite3_column_origin_name(sqlite3_stmt*,int); const void *sqlite3_column_origin_name16(sqlite3_stmt*,int); maybe it's more convenient. At 2012-08-14 11:44:14,Igor Tandetnik itandet...@mvps.org wrote: daedae11 daeda...@126.com wrote: If I use the group of: sqlite3_prepare16_v2() sqlite3_step() andsqlite3_finalize() how can I get the names of a table's columns from a SELECT sql sentence? sqlite3_column_name[16] -- Igor Tandetnik ___ 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