Re: [sqlite] System.Data.SQLite Field Name are surrounded by double quotes for Views
Vincent DARON wrote: Does it mean that it's the expected behaviour of SQLite ? I think so, yes. Technically, the double quotes supplied in the original SELECT query were superfluous. If you remove them, they will not be present in the results. Would it be possible for System.Data.SQLite to remove these double quotes if present, as they are not required any more once you have a C# string ? Possible, yes. Likely, no. System.Data.SQLite strives to return exactly what it is given by SQLite, to the maximum extent possible. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Pavel On Sun, Oct 14, 2012 at 4:38 AM, Elefterios Stamatogiannakis est...@gmail.com wrote: I have some questions for those that know the innards of SQLite. What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? Concerning above questions, i've found a great article about a portable interpreter implementation that produces a close to JITed performance, interpreter: http://www.emulators.com/docs/nx25_nostradamus.htm Another idea for producing a portable JIT (without an assembly backend) is what QEMU does, by chaining precompiled functions. Arguably QEMU's way is more heavy/complex than using an interpreter, but maybe it wouldn't bloat SQLite that much, and SQLite would remain portable across platforms. I'm asking above questions, because i believe that due to SQLite running on billions of devices it needs to be as efficient as possible. Due to the number of deployments, it may burn GWatts of power across all these devices (i haven't done the calculation). Thanks, lefteris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
On 14/10/2012 2:26 PM, Pavel Ivanov wrote: What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Umm... yes it does. http://www.sqlite.org/vdbe.html For the OP's question, it's very efficient compared to what I've seen in, say, postgres, but I don't know how it would compare to something like python or ruby. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
On Sun, Oct 14, 2012 at 12:07 PM, Ryan Johnson ryan.john...@cs.utoronto.ca wrote: On 14/10/2012 2:26 PM, Pavel Ivanov wrote: What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Umm... yes it does. http://www.sqlite.org/vdbe.html Maybe there's some conflict of terminology here. But as I understand it converting SQL query into a set of opcodes representing all operations needed to execute the query and then executing these opcodes is not interpreting, it's parsing. Interpreting is more related to some full-blown execution languages like python, perl, javascript or something like that. These languages indeed require some technologies like JIT. But they are not applicable to SQL. Maybe only to PL/SQL-like language, but it doesn't exist in SQLite. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite VM questions
On Sun, Oct 14, 2012 at 7:38 AM, Elefterios Stamatogiannakis est...@gmail.com wrote: I have some questions for those that know the innards of SQLite. What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite uses a simple byte-code interpreter implemented as a loop around a big switch statement with a separate case for each opcode. The implementation is in the file vdbe.c. See http://www.sqlite.org/src/artifact/31523df2b986?ln for the latest version of this file. The opcodes in the SQLite byte-code are unlike what you would fine in the VMs for javascript or python. SQLite opcodes are much higher level. In procedural languages, the VM needs to be dominated by opcodes to (for example) add a pair of values together and store the result. SQLite has such opcodes, but they are infrequently used and do not amount to much in terms of performance cost. The bulk of CPU time in the SQLite VM is spent in higher level opcodes such as OP_Insert (which inserts a new entry into a B-Tree) or OP_Column (which decodes the bytes of a row as described at http://www.sqlite.org/fileformat2.html#record_format and return the N-th column of that row). These high-level opcodes are implemented as thousands of lines of highly tuned C code. The overhead of instruction dispatch is insignificant in comparison, according to measurements we have done using cachegrind. Concerning above questions, i've found a great article about a portable interpreter implementation that produces a close to JITed performance, interpreter: http://www.emulators.com/docs/**nx25_nostradamus.htmhttp://www.emulators.com/docs/nx25_nostradamus.htm Another idea for producing a portable JIT (without an assembly backend) is what QEMU does, by chaining precompiled functions. Arguably QEMU's way is more heavy/complex than using an interpreter, but maybe it wouldn't bloat SQLite that much, and SQLite would remain portable across platforms. I'm asking above questions, because i believe that due to SQLite running on billions of devices it needs to be as efficient as possible. Due to the number of deployments, it may burn GWatts of power across all these devices (i haven't done the calculation). Thanks, lefteris. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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] SQLite VM questions
On 14 Oct 2012, at 8:07pm, Ryan Johnson ryan.john...@cs.utoronto.ca wrote: On 14/10/2012 2:26 PM, Pavel Ivanov wrote: What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Umm... yes it does. http://www.sqlite.org/vdbe.html For the OP's question, it's very efficient compared to what I've seen in, say, postgres, but I don't know how it would compare to something like python or ruby. SQLite is pretty good. Python is widely seen as slow, though it's not really, it's just difficult to write a fast interepreter for such general code. Ruby is about the same, with the same problems. SQLite can and is highly optimised for doing the things SQL needs done and nothing else. It doesn't have to have the flexibility of a general-purposes programming language, so the opcodes are better designed for database management. And it has a great benefit that EXPLAIN and EXPLAIN QUERY PLAN are excellent tools which have been used to figure out problems and improve it still further. Someone without experience is not going to be able to just dive in and make something better. Huge effort is going on right now to speed up JavaScript so that's a crowded market. If you're looking for something to speed up, aim at Python. On 14 Oct 2012, at 9:08pm, Pavel Ivanov paiva...@gmail.com wrote: Maybe there's some conflict of terminology here. But as I understand it converting SQL query into a set of opcodes representing all operations needed to execute the query and then executing these opcodes is not interpreting, it's parsing. Interpreting is more related to some full-blown execution languages like python, perl, javascript or something like that. The 'interpreter' phase of SQLite is turning SQL commands into VDBE opcodes. The first phase of that is parsing. But at this level were just haggling over language. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DatabaseError: database disk image is malformed
I'm trialling a centralised SQLite database to store process automation metadata with a known, small, fixed number clients (around 10) as part of a Jenkins CI system running on Windows. The clients connect occasionally and run simple, quick (sub-second) SELECT queries and even less frequent INSERTs using Python APIs. Due to the nature of the workflow, I'd estimate the chances of concurrent access are pretty low, although not impossible by any means. Recently, I've started seeing these errors related to some of the INSERT calls: DatabaseError: database disk image is malformed StorageError: database disk image is malformed Given the scenario (and being familiar with http://www.sqlite.org/whentouse.html), I suspect this almost certainly an issue caused by concurrent writes to the database, but I'd like some advice before move to MySQL or similar. Would using TRANSACTIONs on the INSERTs prevent this issue? If necessary, by combining this with a delay-retry mechanism in the client code. Any other tips or tricks to shore-up the robustness, or should I just limber up my throwing arm and call for the towel? Cheers, Larry P.S. just fmi, I'd also love to know --definitively-- if concurrent SELECT calls would ever be an issue. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DatabaseError: database disk image is malformed
On 15 Oct 2012, at 3:28am, Larry Knibb larry.kn...@gmail.com wrote: Recently, I've started seeing these errors related to some of the INSERT calls: DatabaseError: database disk image is malformed StorageError: database disk image is malformed Given the scenario (and being familiar with http://www.sqlite.org/whentouse.html), I suspect this almost certainly an issue caused by concurrent writes to the database, but I'd like some advice before move to MySQL or similar. Would using TRANSACTIONs on the INSERTs prevent this issue? If necessary, by combining this with a delay-retry mechanism in the client code. No. Specifying an INSERT without a transaction just makes SQL wrap it in a transaction of its own. I don't think that will improve anything. What you can do is introduce your own timeout and see if this changes anything, just as something to try. http://www.sqlite.org/c3ref/busy_timeout.html Any other tips or tricks to shore-up the robustness, or should I just limber up my throwing arm and call for the towel? Are you connecting across a network ? Even if it's just one server and a network-mounted disk. Generally, network file systems do not implement locking properly and this can present problems. Are you using any PRAGMAs ? I'm not recommending them -- in fact lack of PRAGMAs is probably the least likely to generate errors -- but it's important for that kind of problem. If you're using any, try it without. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DatabaseError: database disk image is malformed
On 15 October 2012 10:48, Simon Slavin slav...@bigfraud.org wrote: On 15 Oct 2012, at 3:28am, Larry Knibb larry.kn...@gmail.com wrote: What you can do is introduce your own timeout and see if this changes anything, just as something to try. http://www.sqlite.org/c3ref/busy_timeout.html Thanks Simon. I'm new to Python so not sure how to call this C API from my Python script. http://docs.python.org/library/sqlite3.html doesn't offer the answer; similarly a Google search for http://www.google.co.uk/search?q=call+sqlite+c+functions+from+python is likewise uninspiring. Do you know where I can read about this? Cheers, Larry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DatabaseError: database disk image is malformed
On 15 Oct 2012, at 3:58am, Larry Knibb la...@knibb.co.uk wrote: On 15 October 2012 10:48, Simon Slavin slav...@bigfraud.org wrote: http://www.sqlite.org/c3ref/busy_timeout.html Thanks Simon. I'm new to Python so not sure how to call this C API from my Python script. http://docs.python.org/library/sqlite3.html doesn't offer the answer You just need to know where to look because the Python interface for SQLite3 is weird. It's an optional second parameter to the sqlite3.connect() routine. But having found that and read it, the default for Python is 5 seconds, unlike the default for SQLite3 which is to immediately present an error. And if Python really is giving you a reasonable timeout like 5 seconds this is probably nothing to do with the problem you're getting. I think you need someone who is more familiar with the 'malformed' error indication than I am. I really can't guess what's faking this response, assuming that it is a byproduct of networking and the database really isn't malformed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DatabaseError: database disk image is malformed
On Sunday, 14 October, 2012, at 20:28, Larry Knibb said: I'm trialling a centralised SQLite database to store process automation metadata with a known, small, fixed number clients (around 10) as part of a Jenkins CI system running on Windows. The clients connect occasionally and run simple, quick (sub-second) SELECT queries and even less frequent INSERTs using Python APIs. Due to the nature of the workflow, I'd estimate the chances of concurrent access are pretty low, although not impossible by any means. Recently, I've started seeing these errors related to some of the INSERT calls: DatabaseError: database disk image is malformed StorageError: database disk image is malformed Define clients. Do you mean multiple client processes running on a single computer against a database hosted on an attached local device, such as on a Terminal Server for example? Or do you mean multiple clients connecting over LANMAN/CIFS/NFS to a database file sitting on a remote fileserver? Given the scenario (and being familiar with http://www.sqlite.org/whentouse.html), I suspect this almost certainly an issue caused by concurrent writes to the database, but I'd like some advice before move to MySQL or similar. If file locking is working correctly, you cannot have multiple writes to a database. Write operations to the database are exclusive. Would using TRANSACTIONs on the INSERTs prevent this issue? If necessary, by combining this with a delay-retry mechanism in the client code. You may wish to try forcing locks to be acquired sooner when updating the database by using BEGIN IMMEDIATE or perhaps even BEGIN EXCLUSIVE before updating the database, and COMMIT when you are done. You will then also need a busy-timeout so that other readers/writers will wait for that operation to complete. You can set the timeout when creating the connection with sqlite3 in python, or by using PRAGMA busy_timeout Any other tips or tricks to shore-up the robustness, or should I just limber up my throwing arm and call for the towel? Cheers, Larry P.S. just fmi, I'd also love to know --definitively-- if concurrent SELECT calls would ever be an issue. Multiple connections can concurrently read (as in SELECT). Writing is exclusive. For multiple processes on the same machine, WAL mode permits multiple readers and a single writer where readers do not block the writer and the writer does not block readers. Latency of locking operations over network connections can play havoc, however. It is possible if you are using a network mounted database file that the write operations are not being flushed properly and or the locks are not propagating in a timely fashion. http://www.sqlite.org/lang_transaction.html http://www.sqlite.org/lockingv3.html --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DatabaseError: database disk image is malformed
On 15 October 2012 11:54, Simon Slavin slav...@bigfraud.org wrote: I think you need someone who is more familiar with the 'malformed' error indication than I am. I really can't guess what's faking this response, assuming that it is a byproduct of networking and the database really isn't malformed. What's interesting is that you're right about it being a fake response (not sure how you knew that... you didn't say). The database is NOT actually corrupted and re-running a job will more-often-than-not just succeed on the second time of trying. So something in my setup is causing erroneous malformed errors and failing INSERTs. Looks like Keith had some tips in the next response. Thanks for your time Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DatabaseError: database disk image is malformed
On 15 October 2012 12:32, Keith Medcalf kmedc...@dessus.com wrote: Define clients. Do you mean multiple client processes running on a single computer against a database hosted on an attached local device, such as on a Terminal Server for example? Or do you mean multiple clients connecting over LANMAN/CIFS/NFS to a database file sitting on a remote fileserver? It's the second one. The database file is being accessed over the network. If file locking is working correctly, you cannot have multiple writes to a database. Write operations to the database are exclusive. As Simon correctly guessed, the database isn't actually being corrupted; I'm just getting an error that suggests that it is. So I suspect the writes are not conflicting, but one is failing (possibly) due to another happening at the same time and whatever locking/waiting not being observed properly results in this 'malformed' error rather than a blocking error. You may wish to try forcing locks to be acquired sooner when updating the database by using BEGIN IMMEDIATE or perhaps even BEGIN EXCLUSIVE before updating the database, and COMMIT when you are done. You will then also need a busy-timeout so that other readers/writers will wait for that operation to complete. You can set the timeout when creating the connection with sqlite3 in python, or by using PRAGMA busy_timeout Thanks - I'll give that a shot. Latency of locking operations over network connections can play havoc, however. It is possible if you are using a network mounted database file that the write operations are not being flushed properly and or the locks are not propagating in a timely fashion. http://www.sqlite.org/lang_transaction.html http://www.sqlite.org/lockingv3.html Reading up now... Cheers, Larry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
