Re: [sqlite] Caveat in parsing create table statement
On Fri, 13 May 2011 12:06:23 -0400, Jean-Christophe Deschamps j...@antichoc.net wrote: Dear list, Is there a way to make SQLite accept this kind of constraint: CREATE TABLE tab ( id INTEGER NOT NULL, data INTEGER, CHECK(data = 0 or not exists (select 1 from tab where id = data))); Off the top of my head, I do not believe subqueries are allowed at all in CHECK constraints. Hmmm… sqlite CREATE TABLE One (id INTEGER); sqlite CREATE TABLE Two (other INTEGER, ... CHECK (other IN (SELECT id FROM One))); Error: subqueries prohibited in CHECK constraints sqlite It appears I remembered correctly. Yes, I also once had a use case for that; I solved it easily with a trigger. Not sure about the doc issue you also mention. Very truly, SAMUEL ADAM ◊ http://certifound.com/ ◊ I read list mail sporadically. 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States April 15, 2011 Courtroom Video in re Adam v. Supreme Court of N.J.: http://www.youtube.com/watch?v=GPw2W2-Ujyc This toy exemple doesn't mean much as it is and the actual situation is a bit more involved. Anyway, the crux of it is that the table name doesn't yet exist when the parser looks at the constraint, hence SQLite issues a no such table: tab error. The docs say that a check table constraint can be any expression but this is clearly not the complete picture. I tend to think that this statement should be accepted, but I'm in no way an expert in ISO SQL. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
On Sun, 08 May 2011 09:36:43 -0400, Jean-Christophe Deschamps
j...@antichoc.net wrote:
Change that into:
select date('2011-04-29', quote(-3) || ' day');
(note the space before day).
Looks like a parsing change.
Apparently, yes, between the 3.6 and 3.7 lineages:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg58853.html
Very truly,
SAMUEL ADAM ◊ http://certifound.com/ ◊ I read list mail sporadically.
763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States
April 15, 2011 Courtroom Video in re Adam v. Supreme Court of N.J.:
http://www.youtube.com/watch?v=GPw2W2-Ujyc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining how many columns were returned in a query
On Sun, 08 May 2011 11:09:36 -0400, Simon Slavin slav...@bigfraud.org wrote: On 8 May 2011, at 4:00pm, Sam Carleton wrote: How does one go about finding out how many rows a query returns? This was asked earlier this week. There is no magic way. Step through the rows and count them. You can, of course, do a preliminary SELECT for 'count(*)' and see what answer is returned. How about: SELECT count() FROM (original query’s SELECT statement); Depending on the query, this might be possible and/or more obvious: SELECT count() FROM original query’s join-source WHERE original query’s WHERE clause; Quick test: sqlite CREATE TABLE Test (col1 INTEGER, col2 INTEGER); sqlite INSERT INTO Test VALUES (0, 1); sqlite INSERT INTO Test VALUES (1, 1); sqlite INSERT INTO Test VALUES (1, 2); sqlite SELECT count() FROM Test WHERE col2 = 1; 2 sqlite SELECT count() FROM Test WHERE col2 = 2; 1 sqlite SELECT count() FROM Test WHERE col2 = 0; 0 sqlite SELECT count() FROM ... (SELECT col1 FROM Test WHERE col2 = 1); 2 sqlite SELECT count() FROM ... (SELECT col1 FROM Test WHERE col2 = 3); 0 That looks like a fairly “magic way” to me—and I have actually used that method with nontrivial queries. Am I missing something? Too, I know how an aggregate function is made; and I don’t see how this *wouldn’t* work for an obvious implementation of a count() function. Very truly, SAMUEL ADAM ◊ http://certifound.com/ ◊ I read list mail sporadically. 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States April 15, 2011 Courtroom Video in re Adam v. Supreme Court of N.J.: http://www.youtube.com/watch?v=GPw2W2-Ujyc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and SIGFPE
[In general reply to interesting answers by Messrs. Williams and Binns:] The potential SIGFPE in my case is optionally raised by a numeric library as part of its error-handling mechanism; its manual expressly suggests installing a signal handler and longjmp()ing back to find out what went wrong. It’s a neat functionality, much more convenient and efficient from the user’s side than if()ing context bits after every op; most people would just do what the manual says, without stopping to think about what would happen in multithreaded code. I guess that’s why Dr. Hipp advises people not to use threads. In this case, the issue is compounded by being a tricky race condition (a) unlikely to manifest itself during any kind of normal testing, (b) requiring precisely a certain cross-thread interaction between seemingly unconnected software components, and (c) severe enough to really trash the stack. I’m not writing anything multithreaded right now. But next month or next year, the humble little SQL user functions I now make could grow up and get plugged into something bigger. And before I longjmp(), I like to know with certainty where I will land. So I believe I should disable the library’s SIGFPE generation and do an ugly mish-mash of error checks instead. For the archives, though, my original question has not been answered. SQLite itself does not do anything with or to signals; indeed, it does not and should not #include signal.h. Therefore, the default behavior on SIGFPE is is “implementation-defined” per C standard; it may be SIG_IGN, or it may cause the computer to halt and catch fire. On Fri, 18 Feb 2011 22:32:07 -0500, Roger Binns rog...@rogerbinns.com wrote: Using builtins I see NULL or errors being returned: sqlite .mode insert sqlite select 1/0.0; INSERT INTO table VALUES(NULL); sqlite select abs(-9223372036854775808); Error: integer overflow I predict that if I looked at the sources, I’d find that Dr. Hipp made SQLite’s divide operator check for a zero underneath it. But as far as I understand, there is no nontrivial way for SQLite to guarantee it can prevent generating other floating-point exceptions. The foregoing does not show what will happen if SQLite is fed a calculation resulting in a s-NaN, subnormal number, inexact, c.; and as noted, the default signal handler is implementation-defined. Signed integer overflow is a whole different ball of wax (officially “undefined” behavior including specifically in stdlib.h abs(); probably also input-checked in SQLite). Any numerics experts (which I am not) or fp-software gurus care to chime in? *If* I am correct in my inductive hypothesis that the SQLite core may in rare circumstances trip SIGFPE, I have some suggestions as to documenting that—including a clear note for idiots that this is not an SQLite problem, but rather a general platform-/compiler-dependent programming issue which may affect SQLite. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use sqlite and pthread together?
On Sat, 19 Feb 2011 17:12:31 -0500, Pavel Ivanov paiva...@gmail.com wrote: [snip] On Windows it’s different - process is much more heavy-weight object than thread and involves much bigger system load to support it. There’s an official general advice for Windows: better create a new thread in the same process than a new process. Mr. Ivanov explained what I was saying better than I did. My unclear offhand comment about fork()/exec() was an allusion to why *nix developed much lighter-weight processes than Windows, viz., decades of a fork()/exec() custom and practice. (Indeed, I believe that’s precisely why Linux went to the trouble of re-engineering fork() with COW.) I intended to address the overhead of running, and inadvertently introduced a red herring about overhead of starting. Speaking as a user, by the way, I don’t think I actually have *any* Windows applications which use worker processes for concurrency the same way my *nix server daemons do. There’s a reason for that. Lots to say about threads, but well—that will need await another thread. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiler warnings in R-Tree code under Visual StudioExpress
On Sat, 19 Feb 2011 10:37:42 -0500, Afriza N. Arief afriza...@gmail.com wrote: On Sat, Feb 19, 2011 at 6:27 AM, Samuel Adam a...@certifound.com wrote: A FAQ[2] isn’t enough, as we can see. To put it another way: Bug reporters should have probable cause before they bug others. A compiler warning is only a reasonable articulable suspicion. Note that “probable cause” doesn’t imply computer expertise; “it crashes” is probable cause. But a compiler warning only means that a dumb piece of melted sand (i.e., a computer) running a static analysis suggested there might be perhaps something wrong with it, maybe. Not that any actual misbehavior was observed. Relying on a compiler warning means abdicating wetware to kneel in thrall at the feet of silicon dioxide. It’s wrong and it’s stupid. N.b., I am not accusing hereby Mr. Black of so relying; I just happened to reply to his message, because the uninitialized-memory trick seemed apropos of his message and I think he as a C coder would duly appreciate the argument (whether or not he agrees). But the original poster, Mr. Arief, posted an apparent copy-and-paste of such warnings with aught other said but a helpful link to where we can download MSVC Express. It happens here every few months; I am sick of it, ten thousand other list readers are probably sick of it, and it peeves the SQLite team sufficiently that they have a FAQ[2,idem] on the topic. [snip] Now, I actually did read Testing Process Page[3] a few months back when I first knew about SQLite but unfortunately it was not carved into my brain since I didn't face any warning when compiling SQLite at that time. I have googled the warning I found and search the mailing list but did not find satisfying result. During my short time searching, I only found the FAQ[2] which again, I think should be revised to refer to Testing Process Page[3]. Did I mention that I actually read a bit of the code around the warning and thought of a way to fix it? Nah, I believe I haven't mention it. I gave up because I afraid my fix would actually introduce bugs. And unfortunately I didn't find/read Testing Process Page[3] during my recent search. Thank you, Afriza N. Arief Since you say you checked the sources and found something you thought might merit a change, I suggest that you post your suggested fix so someone who knows the R-Tree module insides can advise of whether it will help or hurt. Since your original post was even helpful enough to provide a link to where people could download MSVC++ Express to reproduce the warnings, and you say you had already read the FAQ on compiler warnings, may I ask why you made no mention thereby of what you had already found reading the source? SQLite bugs are relatively rare; speaking to experience, the last time I reported one on-list, I was promptly shown it had already been found and fixed in trunk. So if you found a bug, I advise you should not hesitate to stomp on it. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use sqlite and pthread together?
On Sun, 20 Feb 2011 14:46:06 -0500, Nico Williams n...@cryptonector.com wrote: On Sun, Feb 20, 2011 at 6:28 AM, Samuel Adam a...@certifound.com wrote: On Sat, 19 Feb 2011 17:12:31 -0500, Pavel Ivanov paiva...@gmail.com wrote: [snip] On Windows it’s different - process is much more heavy-weight object than thread and involves much bigger system load to support it. There’s an official general advice for Windows: better create a new thread in the same process than a new process. Mr. Ivanov explained what I was saying better than I did. My unclear offhand comment about fork()/exec() was an allusion to why *nix developed much lighter-weight processes than Windows, viz., decades of a fork()/exec() custom and practice. (Indeed, I believe that’s precisely why Linux went to the trouble of re-engineering fork() with COW.) I intended to address the overhead of running, and inadvertently introduced a red herring about overhead of starting. You seem to be conflating the weightiness of a notion of process with the weightiness of interfaces for creating processes. I appreciate your extensive (if wildly offtopic) analysis as quoted below. You thoroughly misunderstood what I said, though. Again, my fork()/exec() comment was directed to the same “cultural thing” as you spoke about in a different context; and my object thereby was to posit __why__ *nix kernel developers have more incentive to make sure processes run light. Winapi doesn’t offer a really equivalent pair of syscalls, nor an extensive existing fork-exec practice, so NT kernel developers needn’t optimize that use case; whereas *nix kernel folks must of practical necessity design their process models to support a typical *nix code pattern. If they do not so do, their users will complain bitterly about the overhead of all their daemons’ zillion workers *after* those workers are started with the classic fork()/exec(). This being off-topic as it is, I must decline to continue discussing OS process practice in front of 10,000 or so people (or so I heard) who tuned in for discussion about SQLite. You said some very interesting stuff, though, particularly as to the TLB. I’d like to leave the door open to engaging such discussions in an appropriate venue sometime (ENOTIME for the foreseeable future). Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g fork() has nothing to do with whether a notion of process is light-weight or not. And quite aside from that, fork() is only as light-weight as the writable resident set size of the parent process. Long, long ago fork() would copy the parent's address space. Later on fork() implementations started marking what should be writable pages as read-only in the MMU page table entries for the process in order to catch writes and then copy-on-write. COW works fine for single-threaded processes when the child of fork() intends to exec() or exit() immediately and the parent is willing to wait for the child to do so. But for a heavily multi-threaded process with a huge RSS, such as a web browser, COW is a performance disaster as it means cross-calls to do MMU TLB shoot down, and then incurring a potentially large number of page faults in the parent as those threads continue executing. Nowadays it's often simpler and faster to just copy the writable portion of the parent's RSS... vfork(), OTOH, need only result in cross-calls to stop the parent's threads, but no page table manipulations, TLB shootdowns, data copies, nor page faults need be incurred. And a true posix_spawn() wouldn't even have to stop the parent's threads (but using vfork() makes posix_spawn perform so well compared to fork() that, for example, Solaris' posix_spawn() just uses vfork()). In Solaris, for example, we've obtained major performance improvements by having applications such as web browsers use posix_spawn() or vfork() in preference to fork(). In any case, fork() is not an essential attribute of an operating system's notion of process, but an incidental one (related to how one creates processes). In terms of essential attributes, Unix and Windows processes compare to each other, and Windows and Unix threads (POSIX threads) also compare to each other (roughly anyways, as some pthreads implementations have M:N mappings to kernel constructs while others have 1:1, and so on). Yes, Linux has clone(2), which allows one to decide just what parts of the parent's various attributes the child will share with the parent or get a copy of from the parent, but because the standard is pthreads, in practice most developers on Linux constrain themselves to using pthreads, thus the concept of clone(2) is not that relevant
Re: [sqlite] SQLite and SIGFPE
On Sun, 20 Feb 2011 14:51:12 -0500, Nico Williams n...@cryptonector.com wrote: On Sun, Feb 20, 2011 at 6:24 AM, Samuel Adam a...@certifound.com wrote: [big snip] Any numerics experts (which I am not) or fp-software gurus care to chime in? *If* I am correct in my inductive hypothesis that the SQLite core may in rare circumstances trip SIGFPE, I have some suggestions as to [snip] Why don’t you try it? It’s not a question which can be answered by a trivial type-numbers-into-the-shell test. I can immediately think of at least two-and-a-half distinct practical reasons why not; a real numerics expert with extensive fp programming experience could easily infer what I am talking about, then add a few more I’ve probably never heard of. One reason is that different compilers, compiler switches, processor targets, processor version “errata” (bugs), and combinations thereof botch up floating point calculations in different ways—meaning that your ./sqlite3 and my sqlite3.exe may give subtly different results in some tests; the other reasons, which are more obvious, I will leave as an exercise to the reader. I know enough to understand the problem area conceptually, and to ask some precise questions for addressing the practical impact thereof. In other words, with apologies to Einstein, I am sufficiently knowledgeable to understand that I know almost nothing. Thus do I ask, if any Real Mathematicians or fp-calc specialists hit this thread, please do feel free to strut your stuff. As I said: *If* SQLite can in rare circumstances cause a signal to be raised, the result of which is officially “implementation-defined” in C, that really ought be investigated and documented. (And if SQLite can *guarantee* no SIGFPE *ever*—under any possible inputs, period, bar none—then I would be pleasantly quite surprised, and that should also be documented.) Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Compiler warnings in R-Tree code under Visual StudioExpress
On Fri, 18 Feb 2011 09:32:52 -0500, Black, Michael (IS) michael.bla...@ngc.com wrote: I'll agree that excessive worry is counterproductive. But when I see warnings about pointer size mismatch and integer truncation I get worried. So, what would you do if you found this to be the right tool for a job? http://research.swtch.com/2008/03/using-uninitialized-memory-for-fun-and.html This is the story of a clever trick that’s been around for at least 35 years, in which array values can be left uninitialized and then read during normal operations, yet the code behaves correctly no matter what garbage is sitting in the array. […] Notice what just happened: sparse can have any arbitrary values in the positions for integers not in the set, those values actually get used during membership tests, and yet the membership test behaves correctly! (This would drive valgrind nuts.) My list history shows[1] that I know how to catch real bugs on a compiler warning. But really, the signup process for this list needs a big scary TOS agreement containing the stipulation, “I promise not to complain about compiler warnings unless I have actually READ THE SOURCE CODE and found actual evidence of an actual bug.” That, with a liquidated damages provision. A FAQ[2] isn’t enough, as we can see. To put it another way: Bug reporters should have probable cause before they bug others. A compiler warning is only a reasonable articulable suspicion. Note that “probable cause” doesn’t imply computer expertise; “it crashes” is probable cause. But a compiler warning only means that a dumb piece of melted sand (i.e., a computer) running a static analysis suggested there might be perhaps something wrong with it, maybe. Not that any actual misbehavior was observed. Relying on a compiler warning means abdicating wetware to kneel in thrall at the feet of silicon dioxide. It’s wrong and it’s stupid. N.b., I am not accusing hereby Mr. Black of so relying; I just happened to reply to his message, because the uninitialized-memory trick seemed apropos of his message and I think he as a C coder would duly appreciate the argument (whether or not he agrees). But the original poster, Mr. Arief, posted an apparent copy-and-paste of such warnings with aught other said but a helpful link to where we can download MSVC Express. It happens here every few months; I am sick of it, ten thousand other list readers are probably sick of it, and it peeves the SQLite team sufficiently that they have a FAQ[2,idem] on the topic. [snip] If you're going to decide to ignore it then put a comment in the code that says don't bother to use options X/Y/Z to look for warnings...we have chosen to ignore them. Much as I regularly take issue with other items therein, when both the FAQ[2,idem] and another document thereby referenced[3] explain SQLite’s position on compiler warnings, it is futile to expect that somebody who missed that would read a source code comment. Did I mention the FAQ[2,supra]? Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g [1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg56813.html [2] http://www.sqlite.org/faq.html#q17 [3] http://www.sqlite.org/testing.html#staticanalysis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite and SIGFPE
I recently started writing some SQLite user functions which may generate
SIGFPE. I’m a strong advocate of multi-threading—thank you Dr. Hipp, I
like running with scissors!—so in case my little functions ever get called
in multi-threaded code, I must needs enter the dark and dangerous world of
threadsafe signal handling.
My problem right now is that no matter what strange magic (or ugly
kludges) I work in my own functions, if any other code generates SIGFPE
elsewhere at the wrong time, the handler will walk straight into the wrong
stack snapshot. This, of course, will almost certainly crash the entire
process and very likely may corrupt data. I just don’t see a way around
this without platform-specific code.
My question hereby is, will the SQLite core itself ever trigger SIGFPE?
It seems it must be possible, in as much as SQLite regularly mashes up
user floats. To put it another way: Can the SQLite core *guarantee* that
it will never trigger SIGFPE? (Almost certainly no, but bears asking.)
SQLite question ends here. For those with a head for details, my current
plan is to do as follows in very exactly and precisely this order:
1.) Acquire global mutex (well, I did say “ugly kludge”)
2.) Set my signal handler for SIGFPE (which uses longjmp())
== If any other thread which triggers SIGFPE here, whatever enters
the signal handler will fall into a stack full of arbitrary
garbage.
3.) if(setjmp(mystack)) { /*ERROR*/ } else { /*F.P. calcs*/ }
== If any other thread which triggers SIGFPE here, whatever enters
the signal handler will will enter the stack for the current
thread. Oops!
4.) Restore previous SIGFPE handler
5.) Release global mutex
6.) Curse at signal.h for not being more thread-happy, and
hope that the C standards folks realize such a use case
should be fulfilled sometime before the next Ice Age.
(I have macros defined to do these things.)
P.S.—For those who might criticize my use of globals and the awful amount
of blocking it could create: I originally wanted to malloc() for separate
jmp_buf space and make separate mutexes for each connection to my user
functions. It seemed to make sense, since an sqlite3 object can only be
used by one thread at a time. Then I realized the obvious problem: I’m
dealing with a signal handler. Duh. Back to square one.
Very truly,
Samuel Adam ◊ http://certifound.com/
763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use sqlite and pthread together?
On Thu, 17 Feb 2011 07:30:47 -0500, Richard Hipp d...@sqlite.org wrote: Using threads is like running with scissors - You are likely to get hurt and so the best approach is to not do it. If you want to run queries in parallel, I suggest putting each query in a separate process. If your knowledge of threads is so limited that you don't know how to enable them and you are trying to use pthreads on windows, then your chances of getting hurt are compounded. This is all the more reason to use separate processes, not threads, for parallelism. FYI, Windows NT is documented to have light threads and heavy processes. To my knowledge, it just was not designed with the goal of *nix/Plan 9/et al.’s more-or-less cheap and easy fork()/exec(). Of course, since the original poster was using pthreads, he probably doesn’t care. (There exists a popular pthreads/win32 package; it is reputed slow, I cannot attest either way as thereto, and it may or may not be what the original poster was using.) Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bi-directional unique
On Wed, 09 Feb 2011 20:14:19 -0500, Igor Tandetnik itandet...@mvps.org wrote: On 2/9/2011 7:29 PM, Samuel Adam wrote: […snip garbage…] It seems (1, 2) and (2, 1) would result in distinct xk values, thus defeating the point of the exercise. It is again possible to insert two pairs that differ only in order. You are right. I’ve now devised two different back-of-the-envelope proofs that I was trying to achieve the mathematically impossible. (Now watch this: For strike three, somebody will show that it is not only possible, but trivial.) Incidentally, I believe I just provided an unintentional object lesson in the merit of being just a bit formal sometimes. What I was trying to do (which may or may not have been obvious to Mr. Tandetnik, et al.) is to find k = F(x, y) = F′(y, x) such that k would retain the information of whether F or F′ was used. Q.E.D. (and affix palm to forehead). On the bright side, this bungling on my part led me into an interesting general problem with sets and permutations. That’s not topical, however, as I have already showed for Mr. Black’s purpose that extra information cannot be stored in k without breaking the equality. Plus as I’ve said before[1] and yesterday quite well demonstrated, I am bad at math. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g [1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg56438.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Thu, 10 Feb 2011 15:21:57 -0500, Yves Goergen nospam.l...@unclassified.de wrote: On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote: On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby phil.willoug...@strawberrycat.com wrote: What about: UPDATE message_revision SET Data = CAST (Data AS BLOB); Y’know the urban legend about the folks at the restaurant who design a complicated plan for switching the contents of salt and pepper shakers which have their hats juxtaposed, and then a passer-by suggests just switching the caps? I don't know that story nor do I understand it. But would that trigger thing work? That would be something I could set up easily. Off-topic not-quite-analogy by way of jest at self. I suggested a temporary workaround which was a hair’s breadth from permanently fixing your existing data; Mr. Willoughby pointed that out with the above-quoted UPDATE line. As to Mr. Willoughby’s triggers (which you snipped), yes, they look quite correct and you should try them. It's not that important issue for my anymore. I've simply decided to not store binary data in the SQLite database in this application anymore but instead write it to disk. Storing files in the database should only be of interest here if you cannot write to disk, but when using SQLite you obviously can do that. For the archives, I emphasize hereby again that SQLite is not 8-bit crippled. I insert and retrieve ordinary-sized images, audio, PDFs, c. from SQLite on a regular basis; I don’t have problems, because I bind such things as BLOB. I also oft use core function length() on BLOB values, including those containing NUL bytes, again without problems. If I saw a bug in SQLite, I’d call it that point-blank. Indeed, I did take the opportunity to raise what I argue is a documented design flaw (which only affects corner cases and is absolutely irrelevant to your problem). But your problem simply was not caused by any incorrectness in SQLite. As amply explained with reference to docs, you told SQLite you were inserting TEXT; so length() complied with its documented behavior for measuring the length of TEXT: It counted Unicode characters, *not bytes*, up to and not including the first U+. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Thu, 10 Feb 2011 16:38:40 -0500, Stephan Beal sgb...@googlemail.com wrote: On Sun, Feb 6, 2011 at 2:36 PM, Samuel Adam a...@certifound.com wrote: * Make sure the binding is done as BLOB and not TEXT. PDO probably has its own flags defined for this. This is the part that tells SQLite whether you are inserting TEXT or BLOB. http://www.php.net/manual/en/pdostatement.bindparam.php When inserting the GIF, the 3rd argument to bindParam() must be PDO::PARAM_LOB (not BLOB, interestingly). The default is PDO::PARAM_STR, which almost certainly results in the OP's frustration. Thanks for actually looking this up. But on Sun, 06 Feb 2011 10:53:05 -0500, Yves Goergen nospam.l...@unclassified.de actually said he was using “a PDO method to execute a prepared statement with an array of values to be used as parameters”. That appears to be this: http://www.php.net/manual/en/pdostatement.execute.php input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR. In other words, he is using a PHPism which hands off only one type and then blaming SQLite for treating his data as the wrong type. (That interface is bizarre, by the way: Either you can bind the parameters yourself and then call it with no arguments, or bind nothing yourself and pass it an array of values which will be treated as text. Mr. Goergen appears to be doing the latter.) Worse, PDO::PARAM_LOB is for binding a stream and not a regular variable: http://www.php.net/manual/en/pdo.lobs.php And it is for “objects [] either textual or binary in nature” (Id.). As usual with PHP, the docs are very imprecise on what the database will actually get. “Example #14[:] Inserting an image into a database” thereby (based on an fopen() stream) looks promising, but still does not document the type told to the database. If there is a way anywhere in PDO to explicitly hand SQLite a BLOB type and/or so do from a variable, the PDO constants list http://www.php.net/manual/en/pdo.constants.php fails to disclose it. PHP’s SQLite3 class does have a SQLITE3_BLOB flag. But that is not what Mr. Goergen is using. Easiest solution: The UPDATE and triggers suggested on Mon, 07 Feb 2011 03:16:54 -0500 by Philip Graham Willoughby phil.willoug...@strawberrycat.com. Best solution: Reading and understanding the docs, particularly as to SQLite’s type system, and then making a few thoughtful decisions as to architecture. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] generating mini-calendar: DATE and quoting...
On Thu, 10 Feb 2011 17:17:29 -0500, fearless_fool rdp...@gmail.com wrote: [snip] A query that does NOT work is: sqlite SELECT DATE(2011-02-05 21:42:20, units.digit DAY) AS d FROM digits AS units; http://www.sqlite.org/lang_datefunc.html http://www.sqlite.org/lang_select.html (and a few others) Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] generating mini-calendar: DATE and quoting...
On Thu, 10 Feb 2011 17:55:57 -0500, Jay A. Kreibich j...@kreibi.ch wrote:
On Thu, Feb 10, 2011 at 02:47:29PM -0800, fearless_fool scratched on the
wall:
Meh. I have a solution, but I don't like it very much because it feels
convoluted:
sqlite select strftime('%Y-%m-%d', julianday('2011-01-01') + digit)
as d from digits;
This takes advantage that JULIANDAY is in units of days. I'll go with
this
unless some guru suggests something cleaner.
SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits;
s/'days'/' days'/
(Thanks for the tip on quotes; I should have added
http://www.sqlite.org/lang_expr.html and
http://www.sqlite.org/lang_keywords.html .)
Very truly,
Samuel Adam ◊ http://certifound.com/
763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bi-directional unique
On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS) michael.bla...@ngc.com wrote: I have a need to create a unique bi-directional relationship. You can think of it as pairings of people who eat dinner together. Two questions come to mind: (a) Do you trust app-level code to maintain data integrity, or do you need SQLite to do this? (b) How much relational rigor do you need? Will the values be used for some kind of relational algebra, or is SQLite simply serving as an ACID reliability layer? Since you’ve been considering the bit-math tricks suggested by Mr. Wilcoxson, the answers to these questions may let you consider some XOR cleverness. Unfortunately, I halfway wrote this up before I realized that SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function and/or app-land code. Still, with the following, you can store any pairs of 63-bit integers = 0. In pure SQL: CREATE TABLE (k INTEGER PRIMARY KEY, x INTEGER); -- WRONG: INSERT INTO (k, x) VALUES (:x ^ :y, :x); INSERT INTO (k, x) VALUES (xor(:x, :y), :x); -- Faster on the app level; you understand. SELECT x, xor(k, x) AS y FROM ; (Add NOT NULL or CHECK(typeof(x) IS 'integer') and salt to taste. N.b., I *think* the above binding scenario will work but have not tested it.) [1] 2009·12·15 thread with reference to ^ patch by Will Clark: Message-ID: off7402127.00e0cd73-onc125768d.0045dfc4-c125768d.0049d...@gfs-hofheim.de http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html Key points: * Pair uniqueness is enforced for free. At least, I think it’s really for free because SQLite always requires a unique rowid. Somebody please correct me if there is any penalty for user-selected rowids, which would make the performance impact nonzero. * Order of (x, y) versus (y, x) pairings is preserved. Sorts on y will be a pain, though. * No extra indices are required. * I don’t see a reasonable way to stop arbitrary data from being stuffed in from within SQLite, even with a user function; for although :y is being bound on INSERT, a CHECK constraint has no way to touch it. But see below for a modified table with a different set of tradeoffs. * Since two small integers XORed will be another small integer, you do not suffer the loss of variable-length integer storage as spoken of by Messrs. Vlasov and Tandetnik. * XOR is *fast*. And the number of integers is kept to a bare minimum (for keeping up to 63 bits for each), cutting cache pressure at all levels—from SQLite’s page-cache to the processor caches. I am no expert in optimization, but the foregoing practically begs to be benchmarked. * If for some reason you can’t use xor(k, x) for all your SQL needs (foreign keys come to mind), add another explicit y column. You then lose some of the foregoing advantages. But then, a trivial (and probably quite fast) pure-SQL constraint could then be used to enforce some integrity: CREATE TABLE ( k INTEGER PRIMARY KEY, x INTEGER, y INTEGER, CHECK (k IS xor(x, y)) -- NOT NULL for free! ); i * If you try to use negative integers, your database will trigger a HCF instruction. At the cost of some more performance, CHECK(x = 0 AND xor(k, x) = 0) will *partially* solve that. I say “partially” because per the foregoing, SQLite cannot guarantee that y = k^x unless you use the modified table, anyway. Bear in mind, this suggestion stems from a personal bias toward clever XOR tricks; at that, I once wrote a set of endian-swab functions with no (explicit) temporary variables, purely using XOR-swap and shifts. I found it the most pleasant way to satisfy aliasing rules; yet I am to this day uncertain whether the result qualifies as abstract art. P.S.: Consider the foregoing a real-life use case in support of adding a bitwise ^ operator to SQLite. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g create table t(i int, j int); insert into t(1,2); insert into t(2,1); should give an error because the pairing of 1-2 already exists. insert into t(3,2); OK insert into t(3,1); OK insert into t(1,3); should be error You can't guarantee that one column is less than the other so there's no win there. Speed is of the utmost concern here so fast is really important (how many ways can I say that???). Is there anything clever here that can be done with indexes or such? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate
Re: [sqlite] Bi-directional unique
On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking dbic...@yahoo.com wrote: I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you would fail to insert proper pairs. Or am I missing something? (At least I assume that the integers are not limited to just 1 2 or 3 as in the examples. You are right, as xoring on my fingers would have verified. In polite terms, evidently I just demonstrated publicly math as not my forté || today as not my day. Apologies for the noise. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g David On 02/09/2011 05:58 PM, Samuel Adam wrote: On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS) michael.bla...@ngc.com wrote: I have a need to create a unique bi-directional relationship. You can think of it as pairings of people who eat dinner together. Two questions come to mind: (a) Do you trust app-level code to maintain data integrity, or do you need SQLite to do this? (b) How much relational rigor do you need? Will the values be used for some kind of relational algebra, or is SQLite simply serving as an ACID reliability layer? Since you’ve been considering the bit-math tricks suggested by Mr. Wilcoxson, the answers to these questions may let you consider some XOR cleverness. Unfortunately, I halfway wrote this up before I realized that SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function and/or app-land code. Still, with the following, you can store any pairs of 63-bit integers= 0. In pure SQL: CREATE TABLE (k INTEGER PRIMARY KEY, x INTEGER); -- WRONG: INSERT INTO (k, x) VALUES (:x ^ :y, :x); INSERT INTO (k, x) VALUES (xor(:x, :y), :x); -- Faster on the app level; you understand. SELECT x, xor(k, x) AS y FROM ; (Add NOT NULL or CHECK(typeof(x) IS 'integer') and salt to taste. N.b., I *think* the above binding scenario will work but have not tested it.) [1] 2009·12·15 thread with reference to ^ patch by Will Clark: Message-ID: off7402127.00e0cd73-onc125768d.0045dfc4-c125768d.0049d...@gfs-hofheim.de http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html Key points: * Pair uniqueness is enforced for free. At least, I think it’s really for free because SQLite always requires a unique rowid. Somebody please correct me if there is any penalty for user-selected rowids, which would make the performance impact nonzero. * Order of (x, y) versus (y, x) pairings is preserved. Sorts on y will be a pain, though. * No extra indices are required. * I don’t see a reasonable way to stop arbitrary data from being stuffed in from within SQLite, even with a user function; for although :y is being bound on INSERT, a CHECK constraint has no way to touch it. But see below for a modified table with a different set of tradeoffs. * Since two small integers XORed will be another small integer, you do not suffer the loss of variable-length integer storage as spoken of by Messrs. Vlasov and Tandetnik. * XOR is *fast*. And the number of integers is kept to a bare minimum (for keeping up to 63 bits for each), cutting cache pressure at all levels—from SQLite’s page-cache to the processor caches. I am no expert in optimization, but the foregoing practically begs to be benchmarked. * If for some reason you can’t use xor(k, x) for all your SQL needs (foreign keys come to mind), add another explicit y column. You then lose some of the foregoing advantages. But then, a trivial (and probably quite fast) pure-SQL constraint could then be used to enforce some integrity: CREATE TABLE ( k INTEGER PRIMARY KEY, x INTEGER, y INTEGER, CHECK (k IS xor(x, y)) -- NOT NULL for free! ); i * If you try to use negative integers, your database will trigger a HCF instruction. At the cost of some more performance, CHECK(x= 0 AND xor(k, x)= 0) will *partially* solve that. I say “partially” because per the foregoing, SQLite cannot guarantee that y = k^x unless you use the modified table, anyway. Bear in mind, this suggestion stems from a personal bias toward clever XOR tricks; at that, I once wrote a set of endian-swab functions with no (explicit) temporary variables, purely using XOR-swap and shifts. I found it the most pleasant way to satisfy aliasing rules; yet I am to this day uncertain whether the result qualifies as abstract art. P.S.: Consider the foregoing a real-life use case in support of adding a bitwise ^ operator to SQLite. Very truly, Samuel Adam ◊http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non
Re: [sqlite] Bi-directional unique
On Wed, 09 Feb 2011 18:51:45 -0500, Samuel Adam a...@certifound.com wrote: On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking dbic...@yahoo.com wrote: I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you would fail to insert proper pairs. Or am I missing something? (At least I assume that the integers are not limited to just 1 2 or 3 as in the examples. You are right, as xoring on my fingers would have verified. In polite terms, evidently I just demonstrated publicly math as not my forté || today as not my day. Apologies for the noise. At risk of worsening my today’s foot-in-mouth syndrome, I believe that the following design would keep *some* of the advantages I outlined at cost of the following: (a) It would only work for 31-bit integers (or 32-bit integers with additional tricks to store two 32-bit unsigneds in a 64-bit signed). (b) Increased computational cost. However, it adds one advantage (in addition to being correct!): (c) It would collapse the full informational content of x and y into the same column xk (viz., the all-important INTEGER PRIMARY KEY). And still, no additional indices are required. In pseudo-C: int32_t x, y; int64_t xk; /* k lives in the low bits of xk */ if (x 0 || y 0) ; /* Return an error. */ xk = (int64_t)x32 | x^y; /* Weird integer concatenation; yes, precedence is right. */ If you don’t mind living dangerously, use union or pointer tricks to coerce uint32_t x, y into an sqlite3_int64. Upon the foregoing, only one column (xk INTEGER PRIMARY KEY) is required to actually store the data. With the foregoing pseudo-C placed in an SQL user function, however, this is also possible: CREATE TABLE ( xk INTEGER PRIMARY KEY, x INTEGER, y INTEGER, CHECK (xk IS compose_xk(x, y)) ); I think Shannon bit me before, but he’s on my side now. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g David On 02/09/2011 05:58 PM, Samuel Adam wrote: On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS) michael.bla...@ngc.com wrote: I have a need to create a unique bi-directional relationship. You can think of it as pairings of people who eat dinner together. Two questions come to mind: (a) Do you trust app-level code to maintain data integrity, or do you need SQLite to do this? (b) How much relational rigor do you need? Will the values be used for some kind of relational algebra, or is SQLite simply serving as an ACID reliability layer? Since you’ve been considering the bit-math tricks suggested by Mr. Wilcoxson, the answers to these questions may let you consider some XOR cleverness. Unfortunately, I halfway wrote this up before I realized that SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function and/or app-land code. Still, with the following, you can store any pairs of 63-bit integers= 0. In pure SQL: CREATE TABLE (k INTEGER PRIMARY KEY, x INTEGER); -- WRONG: INSERT INTO (k, x) VALUES (:x ^ :y, :x); INSERT INTO (k, x) VALUES (xor(:x, :y), :x); -- Faster on the app level; you understand. SELECT x, xor(k, x) AS y FROM ; (Add NOT NULL or CHECK(typeof(x) IS 'integer') and salt to taste. N.b., I *think* the above binding scenario will work but have not tested it.) [1] 2009·12·15 thread with reference to ^ patch by Will Clark: Message-ID: off7402127.00e0cd73-onc125768d.0045dfc4-c125768d.0049d...@gfs-hofheim.de http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html Key points: * Pair uniqueness is enforced for free. At least, I think it’s really for free because SQLite always requires a unique rowid. Somebody please correct me if there is any penalty for user-selected rowids, which would make the performance impact nonzero. * Order of (x, y) versus (y, x) pairings is preserved. Sorts on y will be a pain, though. * No extra indices are required. * I don’t see a reasonable way to stop arbitrary data from being stuffed in from within SQLite, even with a user function; for although :y is being bound on INSERT, a CHECK constraint has no way to touch it. But see below for a modified table with a different set of tradeoffs. * Since two small integers XORed will be another small integer, you do
Re: [sqlite] Bi-directional unique
On Wed, 09 Feb 2011 17:54:37 -0500, Igor Tandetnik itandet...@mvps.org wrote: On 2/9/2011 5:49 PM, Jim Wilcoxson wrote: I assumed you could generate k in a procedural language outside of SQL, but if you want to do this purely in SQL, I think you can just say: create table t(k int primary key) insert into t values (min(?,?)32 | max(?,?)) and bind i,j,i,j to the parameters. Or make it insert into t values (min(:first,:second)32 | max(:first,:second)) and bind two parameters like you always did. If the order of (i, j) versus (j, i) is unimportant, then this is superior on most points to my corrected bit-XOR design. Normalizing away that order may be desired, so discarding it could be an advantage. But the XOR preserves that order, and that order may be part of the data; so it really depends on application requirements. And the XOR could also be done in pure SQL with SQLite patched to have a ^ operator. Due credit: I of course cribbed from the quoted idea to correct my previous error. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby phil.willoug...@strawberrycat.com wrote: SELECT length(CAST (Data AS BLOB)) FROM message_revision; What about: UPDATE message_revision SET Data = CAST (Data AS BLOB); Y’know the urban legend about the folks at the restaurant who design a complicated plan for switching the contents of salt and pepper shakers which have their hats juxtaposed, and then a passer-by suggests just switching the caps? Thank you, Mr. Willoughby! Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g As a one-time command to correct the table. There is also the option of using triggers to ensure future Data values are kept as BLOB if the INSERT code cannot be corrected conveniently: CREATE TRIGGER trigName AFTER INSERT ON message_revision WHEN TYPEOF(Data)!='blob' BEGIN UPDATE message_revision SET Data = CAST (Data AS BLOB) WHERE message_revision.rowid = NEW.rowid; END; CREATE TRIGGER trigName2 AFTER UPDATE OF Data ON message_revision WHEN TYPEOF(Data)!='blob' BEGIN UPDATE message_revision SET Data = CAST (Data AS BLOB) WHERE message_revision.rowid = NEW.rowid; END; ___ 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] LENGTH on a BLOB field stops at NUL byte
On Sun, 06 Feb 2011 07:15:42 -0500, Yves Goergen
nospam.l...@unclassified.de wrote:
On 06.02.2011 12:01 CE(S)T, Simon Davies wrote:
length() should return the number of bytes for a blob.
What does typeof() tell you about the data?
It says text. Now tested with SQLite 3.7.4, this is the only command
Question for SQLite devs: Any plans to support strict affinity? I seem
to recollect a mention of this in the docs at some point. If not, then
perhaps add a tip like the following to the FAQ.
For the original poster: To prevent problems like this, if it will always
be a BLOB then add a constraint to your table:
CHECK (typeof(Data) IS 'blob')
Your INSERTs will fail if inserting TEXTual data then. (This usage of the
IS operator is relatively recent SQLite; use = otherwise. I use IS in
case you accidentally reference an invalid column name or whatnot; I am
not absolutely sure this is necessary.) N.b., as a side effect it will
render the column NOT NULL. If the column can be NULL, do the constraint
as such:
CHECK (typeof(Data) IN ('blob', 'null'))
Yes, it will slow down INSERTs a bit; data integrity is more important, as
you have just seen.
Very truly,
Samuel Adam ◊ http://certifound.com/
763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
line client I have here.
Here's the statement how the table was created:
CREATE TABLE message_revision (
MessageRevisionId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
MessageId INTEGER NOT NULL REFERENCES message (MessageId) ON
DELETE CASCADE,
CreatedTime DATETIME NOT NULL,
Author INTEGER NOT NULL REFERENCES user (UserId),
Subject VARCHAR(255),
Content MEDIUMTEXT,
HtmlContent MEDIUMTEXT,
Summary VARCHAR(255),
ModerationState TINYINT NOT NULL DEFAULT 0,
Draft BOOLEAN NOT NULL DEFAULT FALSE,
ContentType VARCHAR(255),
Data BLOB);
The column in question is Data.
I can verify that the entire image file has been stored as it is
entirely returned in a SELECT query and displayed in the web browser.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Sun, 06 Feb 2011 08:10:49 -0500, Yves Goergen nospam.l...@unclassified.de wrote: On 06.02.2011 13:52 CE(S)T, Samuel Adam wrote: For the original poster: To prevent problems like this, if it will always be a BLOB then add a constraint to your table: CHECK (typeof(Data) IS 'blob') Your INSERTs will fail if inserting TEXTual data then. I don't want my INSERT to fail, I want SQLite to see that I'm inserting binary data. There is only one way to insert data with PHP/PDO. I give it binary data and it's supposed to do it right. Incorrect. SQLite has no way to know what you are inserting, unless it is told. Remember that a computer’s heart and brains are dumb melted sand, fancily arranged; a userland library such as SQLite can’t add psychic powers to that. I not too familiar with PDO; check its docs. Tips on what to look for: * You should be using bound parameters on INSERT. If you are not, change your code. This will eliminate a whole list of potential problems. * Make sure the binding is done as BLOB and not TEXT. PDO probably has its own flags defined for this. This is the part that tells SQLite whether you are inserting TEXT or BLOB. Moreover: * Per its docs, SQLite does not validate UTF-(8|16) TEXT. However, a 0 byte is technically valid UTF-8 (and typical UTF-16 is chock-full of 0 bytes). Note to devs: I know that SQLite’s TEXT code relies heavily on zero-termination; this does mean that length() can mysteriously fail on valid UTF-8. This is of course irrelevant to the OP’s problem, because length() counts characters for TEXT and bytes for BLOB (and thus can sometimes return *very* *subtly* different numbers—Mr. Goergen, take note). * From an architectural perspective, it is better for an INSERT to fail than to experience mysterious problems such as length() only reading up to a 0 byte. If you are inserting a GIF image as TEXT, then failure will occur at some point; adding the CHECK constraint I suggested forces the failure to occur (a) early on, (b) in a well-defined manner, and, (c) with an error (code 19 if memory serves, “constraint failed”) rather than an incorrect result. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Sun, 06 Feb 2011 08:22:58 -0500, Black, Michael (IS) michael.bla...@ngc.com wrote: http://www.sqlite.org/c3ref/blob_open.html http://www.sqlite.org/c3ref/blob_bytes.html Mr. Black, is that a tactful hint that the original poster should rewrite his app in C rather than PHP? (Not that I’d disagree—not at all!—just wanted to clarify, since o.p.’s problem originated with SQL core function length() which “returns the length of X in characters if X is a string, or in bytes if X is a blob”[1].) [1] http://www.sqlite.org/lang_corefunc.html (P.S.—For precision, the quoted doc should say “text value” rather than “string”.) Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Yves Goergen [nospam.l...@unclassified.de] Sent: Sunday, February 06, 2011 4:40 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] LENGTH on a BLOB field stops at NUL byte Hi, I'm storing small files in a BLOB field in the database. I'd like to determine the size of such a file with an SQL query without fetching the data. I tried using the LENGTH() function on that field but it stops counting at the first NUL byte. A 3.2 KiB GIF image is reported to be only 7 bytes long, that's just the file header length. Is this a bug or should I use another function on binary data? The SQLite version is 3.6.20 from PHP 5.3.1 on Windows XP. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de ___ 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] EXT :Re: LENGTH on a BLOB field stops at NUL byte
On Sun, 06 Feb 2011 09:23:59 -0500, Black, Michael (IS) michael.bla...@ngc.com wrote: C is better than everything else, isn't it :-) I say it’s the language you best appreciate after all the others betray you. Yet more to the point: asm, bf, or punch-cards would be faster to develop correct code in, less painful to write, more efficient, and less bug-prone than PHP. My thus snobbery is justified by experience (well—as to the PHP part), as is the wisdom of never presuming that the biggest social-network/flash-video websites know what they are doing. He needs to add the type argument to bindvalue and tell it it's a blob. http://us2.php.net/manual/en/sqlite3stmt.bindvalue.php Actually he is using PDO, which is a different beast. PHP has at least three different ways to use SQLite (and AFAIK still comes with SQLite2 as one of them). None of them offers full access to all the useful features provided by SQLite3’s C API. Sorting out which library and version is being used in each place can be a pain, too, as briefly seen (but not really explored) in a thread I was involved in last week. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Samuel Adam [a...@certifound.com] Sent: Sunday, February 06, 2011 7:49 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] LENGTH on a BLOB field stops at NUL byte On Sun, 06 Feb 2011 08:22:58 -0500, Black, Michael (IS) michael.bla...@ngc.com wrote: http://www.sqlite.org/c3ref/blob_open.html http://www.sqlite.org/c3ref/blob_bytes.html Mr. Black, is that a tactful hint that the original poster should rewrite his app in C rather than PHP? (Not that I’d disagree—not at all!—just wanted to clarify, since o.p.’s problem originated with SQL core function length() which “returns the length of X in characters if X is a string, or in bytes if X is a blob”[1].) [1] http://www.sqlite.org/lang_corefunc.html (P.S.—For precision, the quoted doc should say “text value” rather than “string”.) Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Yves Goergen [nospam.l...@unclassified.de] Sent: Sunday, February 06, 2011 4:40 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] LENGTH on a BLOB field stops at NUL byte Hi, I'm storing small files in a BLOB field in the database. I'd like to determine the size of such a file with an SQL query without fetching the data. I tried using the LENGTH() function on that field but it stops counting at the first NUL byte. A 3.2 KiB GIF image is reported to be only 7 bytes long, that's just the file header length. Is this a bug or should I use another function on binary data? The SQLite version is 3.6.20 from PHP 5.3.1 on Windows XP. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Sun, 06 Feb 2011 10:53:05 -0500, Yves Goergen nospam.l...@unclassified.de wrote: On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote: [snip] * Make sure the binding is done as BLOB and not TEXT. PDO probably has its own flags defined for this. This is the part that tells SQLite whether you are inserting TEXT or BLOB. There is a PDO method to execute a prepared statement with an array of values to be used as parameters. There is no way to specify additional information about how to interpret these values in this method. But there is another method to bind each value separately, and it has another argument to pass some data type. I'd need to change the way I execute my SQL statements to make use of it. Depending on your PHP version, a (binary) cast may help; if you are writing the GIF image as a string-literal in your source code (probably not), b will specify the correct type directly; if you are opening an external file, check your fopen() flags or whatnot. N.b., I cannot predict whether PDO will use this type information or ignore it. I'd expect that SQLite known on its own what data type a column is and respect it. Seems like SQLite is sometimes more type-agnostic than PHP, where I take great care of data types in this special application. SQLite also cares for types: http://www.sqlite.org/datatype3.html PHP has somewhat similar “duck typing”, and its types are much more complicated; thus you should already be familiar with the concept. For now, I just won't save files to the database with SQLite but instead on disk. I won't get to rewriting the database class anytime soon but I'll look into it then. I'm wondering why I get all the data back but SQLite can't count its characters... And the image I get back from SQLite looks error-free so it probably didn't make a single mistake handling it as text data. What you are wondering has been previously explained in this thread, in precise terms and with reference to docs. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Sun, 06 Feb 2011 11:41:03 -0500, Teg t...@djii.com wrote: Hello Yves, You could alway mime/uu/yenc encode it into text before insert, and do the reverse when you retrieve it. Then the problem goes away. No, it doesn’t: Then SQLite *really* has no way of telling the byte length of the value, which SQL core function length() does perfectly well for properly-stored BLOBs (and which is what Mr. Goergen actually wanted). SQLite handles 8-bit BLOBs just fine, at that; there is never any need thereby to bend, fold, spindle, or mutilate data with ugly 7-bit workarounds. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g C Sunday, February 6, 2011, 10:53:05 AM, you wrote: YG On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote: * You should be using bound parameters on INSERT. If you are not, change your code. This will eliminate a whole list of potential problems. YG I already do that. * Make sure the binding is done as BLOB and not TEXT. PDO probably has its own flags defined for this. This is the part that tells SQLite whether you are inserting TEXT or BLOB. YG There is a PDO method to execute a prepared statement with an array of YG values to be used as parameters. There is no way to specify additional YG information about how to interpret these values in this method. But YG there is another method to bind each value separately, and it has YG another argument to pass some data type. I'd need to change the way I YG execute my SQL statements to make use of it. YG I'd expect that SQLite known on its own what data type a column is and YG respect it. Seems like SQLite is sometimes more type-agnostic than PHP, YG where I take great care of data types in this special application. YG For now, I just won't save files to the database with SQLite but instead YG on disk. I won't get to rewriting the database class anytime soon but YG I'll look into it then. YG I'm wondering why I get all the data back but SQLite can't count its YG characters... And the image I get back from SQLite looks error-free so YG it probably didn't make a single mistake handling it as text data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On Sun, 06 Feb 2011 11:54:37 -0500, Samuel Adam a...@certifound.com wrote: On Sun, 06 Feb 2011 11:41:03 -0500, Teg t...@djii.com wrote: Hello Yves, You could alway mime/uu/yenc encode it into text before insert, and do the reverse when you retrieve it. Then the problem goes away. No, it doesn’t: Then SQLite *really* has no way of telling the byte length of the value, I take that back: Those desiring the Rube Goldberg route to data bliss may code up mime/uu/yenc encode/decode SQLite extensions, compile as dynamic libraries, load on the database connection using appropriate PHP calls (does PDO support this? SQLite3 class does), and proceed thenceforth as such: SELECT length(uudecode(mousetrap(Data))) FROM message_revision; Or, take the easier route: Bind as a BLOB, and use the constraint I suggested earlier to prevent future mistyping of data. For a quick workaround on the existing database, Mr. Goergen may also try this: SELECT length(CAST (Data AS BLOB)) FROM message_revision; Tested: SQLite version 3.7.5 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE (); sqlite INSERT INTO VALUES (CAST (X'61006263' AS TEXT)); sqlite SELECT typeof() FROM ; text sqlite SELECT length() FROM ; 1 sqlite SELECT length(CAST ( AS BLOB)) FROM ; 4 sqlite Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g which SQL core function length() does perfectly well for properly-stored BLOBs (and which is what Mr. Goergen actually wanted). SQLite handles 8-bit BLOBs just fine, at that; there is never any need thereby to bend, fold, spindle, or mutilate data with ugly 7-bit workarounds. Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g C Sunday, February 6, 2011, 10:53:05 AM, you wrote: YG On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote: * You should be using bound parameters on INSERT. If you are not, change your code. This will eliminate a whole list of potential problems. YG I already do that. * Make sure the binding is done as BLOB and not TEXT. PDO probably has its own flags defined for this. This is the part that tells SQLite whether you are inserting TEXT or BLOB. YG There is a PDO method to execute a prepared statement with an array of YG values to be used as parameters. There is no way to specify additional YG information about how to interpret these values in this method. But YG there is another method to bind each value separately, and it has YG another argument to pass some data type. I'd need to change the way I YG execute my SQL statements to make use of it. YG I'd expect that SQLite known on its own what data type a column is and YG respect it. Seems like SQLite is sometimes more type-agnostic than PHP, YG where I take great care of data types in this special application. YG For now, I just won't save files to the database with SQLite but instead YG on disk. I won't get to rewriting the database class anytime soon but YG I'll look into it then. YG I'm wondering why I get all the data back but SQLite can't count its YG characters... And the image I get back from SQLite looks error-free so YG it probably didn't make a single mistake handling it as text data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin slav...@bigfraud.org wrote: On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote: I have a PHP script that builds an sqlite3 client database for a ps3 application. Depending on where I run the build the script (Gentoo or Mac OS X) I get a database file that has different semantics for a column declared as an integer pk: CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, sort_name TEXT NOT NULL, fanships_count INTEGER, image_url VARCHAR(255)); For the database file build on OS X or Windows I need to run: SELECT * FROM Directors WHERE id=1 For the database file built on Gentoo I need to run: SELECT * FROM Directors WHERE id='1' Otherwise I get no results. Very strange. Naturally, the form where you need the quotes is wrong. You have explicitly defined 'id' as an INTEGER. Actually since this is an FTS3 table, the form with the quotes is the correct one. As Mr. da Silveira alluded later in his post, in FTS3, the row ID and only the row ID is an INTEGER (“with aliases docid [] allowed as an alias along with the usual rowid, oid and _oid_” per sqlite.org/fts3.html). So since the actual values *should* be TEXT, the first thing that comes to mind is an interaction with SQLite’s type affinities—but that doesn’t explain the difference on different platforms. Probative questions: * Is PHP’s PDO being used here, or PHP’s Sqlite3 class? * Are weird PHP settings re magic quotes, etc. different between the platforms? (That could make the quoted string not work on one platform—but it doesn’t explain in the other direction.) * Is the database being populated with parameter binding, or not? If so, is id being bound as SQLITE3_INTEGER? (I don’t know off the top of my head what that will do with an FTS3 table.) * Are the sqlite3 library versions the same on all platforms? (AFAIK fts3 has been under heavy development. The whole problem could simply be a difference in behavior between versions.) * By the way, I don’t know whether the sqlite3 bundled in PHP even includes/builds with fts3. I have no idea either if this affects the PDO driver, or if that uses a system-installed library. You may want to check to see where your PHP is getting its sqlite3 with fts3 support. Better approach: Cut PHP from the equation and examine the databases in the sqlite3 shell. Core function typeof() might be helpful to see if the tables actually contain different data, as they most probably do not. (Best approach: Cut PHP from the equation, period.) Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 08:44:22 -0500, Simon Slavin slav...@bigfraud.org
wrote:
On 31 Jan 2011, at 1:24pm, Samuel Adam wrote:
[snip]
Actually since this is an FTS3 table, the form with the quotes is the
correct one. As Mr. da Silveira alluded later in his post, in FTS3, the
row ID and only the row ID is an INTEGER
But from his CREATE command, isn't the column called 'id' an alias to
the rowid ?
FTS3 docs do not mention id, as I quoted. Moreover, id is *not*
normally an alias for rowid:
sqlite CREATE TABLE Test (id TEXT);
sqlite INSERT INTO Test (id) VALUES ('This is a test.');
sqlite SELECT typeof(id) FROM Test;
text
sqlite CREATE TABLE Test2 ( TEXT);
sqlite INSERT INTO Test2 VALUES ('There should be no column named id
in thi
s table.');
sqlite SELECT rowid, id, FROM Test2;
1|id|There should be no column named id in this table.
(That last happens because SQLite helpfully reinterprets the double-quoted
non-identifier as a string value.)
But what I did miss is that these are VIRTUAL tables, not real ones.
And therefore you cannot test the contents of the table by opening the
database with the command-line tool.
I forgot: I always compile my sqlite3 shell with FTS3, c. It is easy
from the amalgamation: Just set defs correctly, compile sqlite3.c and
shell.c, and link. A configure script is not needed.
Would it be possible to temporarily remove the word 'VIRTUAL' from the
CREATE command ? Would the rest of the application stop working ? If
this does work, then the contents of the table can be tested as I
outlined earlier.
Well, then, FTS3 would no longer work; and any tests thereupon would be
irrelevant to actual usage by the original poster.
Samuel Adam a...@certifound.com
763 Montgomery Road
Hillsborough, NJ 08844-1304 • United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin slav...@bigfraud.org wrote: In the definition given in the original post, which I quoted, the table was defined as follows: CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, sort_name TEXT NOT NULL, fanships_count INTEGER, image_url VARCHAR(255)); Under this situation, would SQLite not use 'id' as an alias to rowid ? Ah yes, you are correct. Evidence, using sqlite3 shell compiled with FTS3: sqlite CREATE VIRTUAL TABLE Test USING fts3( ... id INTEGER PRIMARY KEY NOT NULL, ... Content TEXT); sqlite INSERT INTO Test (id, Content) VALUES( ... 101, ... 'This is a test.'); sqlite .mode line sqlite SELECT typeof(id), typeof(Content) FROM Test; typeof(id) = integer typeof(Content) = text Continuing on, to address the original poster’s question: sqlite SELECT * FROM Test WHERE id = 101; id = 101 Content = This is a test. sqlite SELECT * FROM Test WHERE id = '101'; sqlite You were right; I was wrong. I saw FTS3 and somehow missed the PK definition. Of course, this still does not answer why the OP observed different results on different platforms. Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 11:46:21 -0500, Samuel Adam a...@certifound.com wrote: On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin slav...@bigfraud.org wrote: In the definition given in the original post, which I quoted, the table was defined as follows: CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, sort_name TEXT NOT NULL, fanships_count INTEGER, image_url VARCHAR(255)); Under this situation, would SQLite not use 'id' as an alias to rowid ? […] You were right; I was wrong. I saw FTS3 and somehow missed the PK definition. Spoke too soon. SQLite actually ignores the PK definition, and does *not* use id as an alias for the rowid. Evidence coming in another post; I think I found a bug. Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 06:30:55 -0500, Gabe da Silveira g...@websaviour.com wrote: I have an issue with an fts3 table sqlite 3.6.22. I have a PHP script that builds an sqlite3 client database for a ps3 application. Depending on where I run the build the script (Gentoo or Mac OS X) I get a database file that has different semantics for a column declared as an integer pk: CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL, sort_name TEXT NOT NULL, fanships_count INTEGER, image_url VARCHAR(255)); Again I test hereby using the sqlite3 shell compiled with FTS3. First of all, to set up a minimal test case: sqlite CREATE VIRTUAL TABLE Test USING fts3( ... id INTEGER PRIMARY KEY, ... Content TEXT, ... count INTEGER); sqlite INSERT INTO Test (id, Content, count) VALUES( ... 101, ... 'This is a test.', ... 999); Note that SQLite ignores the PRIMARY KEY definition, and does not use id as an alias for the rowid: sqlite SELECT rowid FROM Test WHERE id = 101; rowid = 1 Yet somehow, SQLite heeds the INTEGER definitions: sqlite SELECT typeof(id), typeof(Content), typeof(count) FROM Test; typeof(id) = integer typeof(Content) = text typeof(count) = integer Either the docs are wrong, or there is a bug. Quoting §1.2 of the FTS3 docs: http://www.sqlite.org/fts3.html#section_1_2 If column names are explicitly provided for the FTS table as part of the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally specified for each column. This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose. The same applies to any constraints specified along with an FTS column name - they are parsed but not used or recorded by the system in any way. (Ignoring PRIMARY KEY is consistent with the part about constraints, by the way.) The foregoing test results were obtained SQLite 3.7.2. Perhaps later I should try also with the latest and/or 3.6.22 per OP. Impact on Mr. da Silveira’s SELECT query: sqlite SELECT * FROM Test WHERE id = 101; id = 101 Content = This is a test. count = 999 sqlite SELECT * FROM Test WHERE id = '101'; sqlite According to the docs, results from those queries should be reversed. I suspect that Mr. da Silveira’s different platforms actually have different SQLite versions, and that one version is consistent with the docs whereas the other is not. But this thread has already seen the dangers of speculation. Mr. da Silveira, can you confirm whether *all* your platforms use 3.6.22 and have a proper FTS3 baked in? Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 12:34:24 -0500, Samuel Adam a...@certifound.com wrote: According to the docs, results from those queries should be reversed. I suspect that Mr. da Silveira’s different platforms actually have different SQLite versions, and that one version is consistent with the docs whereas the other is not. But this thread has already seen the dangers of speculation. Mr. da Silveira, can you confirm whether *all* your platforms use 3.6.22 and have a proper FTS3 baked in? My version hypothesis was flat-out incorrect. Repeating all tests in 3.6.22, the results are exactly the same as I showed with 3.7.2. First, showing my compile options (this is on Windows): sqlite-3.6.22cl /nologo /Ox /fp:strict /Fesqlite3.6.22.exe /DSQLITE_DEFAULT_FILE_FORMAT=4 /DSQLITE_DEFAULT_RECURSIVE_TRIGGERS /DSQLITE_ENABLE_STAT2 /DSQLITE_ENABLE_FTS3 /DSQLITE_ENABLE_FTS3_PARENTHESIS /DSQLITE_ENABLE_UNLOCK_NOTIFY /DSQLITE_ENABLE_COLUMN_METADATA /DSQLITE_SOUNDEX=1 /DSQLITE_ENABLE_MEMORY_MANAGEMENT /DSQLITE_ENABLE_RTREE shell.c sqlite3.c shell.c sqlite3.c Generating Code... sqlite-3.6.22.\sqlite3.6.22.exe SQLite version 3.6.22 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .mode line sqlite SELECT sqlite_source_id(); sqlite_source_id() = 2010-01-05 15:30:36 28d0d7710761114a44a1a3a425a6883c661f06e7 sqlite CREATE VIRTUAL TABLE Test USING fts3( ... id INTEGER PRIMARY KEY, ... Content TEXT, ... count INTEGER); sqlite INSERT INTO Test (id, Content, count) VALUES( ... 101, ... 'This is a test.', ... 999); sqlite SELECT rowid FROM Test WHERE id = 101; rowid = 1 sqlite SELECT typeof(id), typeof(Content), typeof(count) FROM Test; typeof(id) = integer typeof(Content) = text typeof(count) = integer sqlite SELECT * FROM Test WHERE id = 101; id = 101 Content = This is a test. count = 999 sqlite SELECT * FROM Test WHERE id = '101'; sqlite Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 13:16:55 -0500, Scott Hess sh...@google.com wrote: The FTS3 code mostly passes values directly down to and up from the underlying tables, so if you bound an integer, it is likely to come back out as an integer when you query. Just to be clear: In the tests I posted earlier with both 3.7.2 and 3.6.22, INSERTing an INTEGER without parameter binding (which isn’t supported in the shell) resulted in an INTEGER coming back out. The FTS3 columns declared INTEGER indeed seem to behave as regular INTEGER columns in regular, non-MATCH queries (although I did not test to see if affinity would coerce a '1' to INTEGER on insertion). Is this the expected behavior? If so, it is a doc bug; if not, it’s a code bug. (By the way—and way off-topic—I just noticed on my last post that this list removes format=flowed from the Content-Type of mails. Is that intended? It rather uglifies things.) Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 13:37:35 -0500, Samuel Adam a...@certifound.com
wrote:
The FTS3
columns declared INTEGER indeed seem to behave as regular INTEGER columns
in regular, non-MATCH queries (although I did not test to see if affinity
would coerce a '1' to INTEGER on insertion).
No coercion is done in the FTS3 table. Using the same FTS3 Test table
setup as in my previous mail, again in 3.7.2:
sqlite INSERT INTO Test (id, Content, count) VALUES(
... '121',
... 'The id and count columns were declared INTEGER.',
... '12321');
sqlite SELECT typeof(id), typeof(Content), typeof(count) FROM
Test;
typeof(id) = text
typeof(Content) = text
typeof(count) = text
Thus is this table neither animal nor vegetable nor mineral.
sqlite CREATE TABLE Normal (id INTEGER);
sqlite INSERT INTO Normal (id) VALUES ('121');
sqlite SELECT typeof(id) FROM Normal;
typeof(id) = integer
Very truly,
Samuel Adam a...@certifound.com
763 Montgomery Road
Hillsborough, NJ 08844-1304 • United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 14:05:15 -0500, Gabe da Silveira g...@websaviour.com wrote: [...] On Mon, Jan 31, 2011 at 1:24 PM, Samuel Adam a...@certifound.com wrote: [...] * Is the database being populated with parameter binding, or not? If so, is id being bound as SQLITE3_INTEGER? (I don’t know off the top of my head what that will do with an FTS3 table.) Yes, PDO binding. Here's the actual code to show how parameters are bound at the PHP level: https://gist.github.com/23ce0a99a0c0a1c059d2 I am not too familiar with PDO. For the reasons set forth below, however, the important part is the parameter binding. (For the list archives, the code you linked indeed binds as INTEGER.) Good tip, I was not aware of typeof(). The data is indeed differnt: https://gist.github.com/b4008e84726e2014b16a Since you are using parameter binding, I believe Scott Hess answered this earlier. On Mon, 31 Jan 2011 13:16:55 -0500, Mr. Hess sh...@google.com wrote: The FTS3 code mostly passes values directly down to and up from the underlying tables, so if you bound an integer, it is likely to come back out as an integer when you query. But since FTS3 doesn't make any explicit provision for this, I wouldn't recommend depending on it in your code, unless you're happy with periodically finding the edge cases where your integer unexpectedly turns into text. FTS3 accesses the data as TEXT only, without regard to type. In C parlance, what you are doing is undefined behavior. On one box, you found “the edge [case] where your integer unexpectedly turns into text”, and on the other box, you didn’t. I suggested rewriting your schema. Non-TEXT data which will not be subjected to a MATCH search is best stored in another table and JOINed with the FTS3 table, as Mr. Hess also explained. Also, specifications such as VARCHAR(255) are not meaningful to SQLite3; see http://www.sqlite.org/datatype3.html . As an added bonus, this investigation uncovered behavior which is either an FTS3 bug or a doc bug, per my previous mails. Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, 31 Jan 2011 15:33:24 -0500, Gabe da Silveira g...@websaviour.com wrote: On Mon, Jan 31, 2011 at 7:42 PM, Samuel Adam a...@certifound.com wrote: I suggested rewriting your schema. Non-TEXT data which will not be subjected to a MATCH search is best stored in another table and JOINed with the FTS3 table, as Mr. Hess also explained. Also, specifications such as VARCHAR(255) are not meaningful to SQLite3; see http://www.sqlite.org/datatype3.html . Agreed this would be nice, unfortunately we have an gaming console app that's already been through the formal QA process based on a complex SQL schema, and for which the developers are not under our budgetary control. I'd say we have almost no chance of making this happen at this point given the complexity of the application and database (the two fts tables cut across a lot of concerns). I can’t help you there other than to say, give your boss the facts and point out that if QA approved that schema, QA failed its job. (Bear in mind that I’m the man who recently sued a state supreme court; characteristically any advice from me is focused on factual correctness solely, exclusively, and at the potential expense of other considerations such as keeping friends in a department which failed its job, or maintaining your state of employment.) You may also point out that you have a much bigger problem with your schema: Also, I'm not sure how we would avoid the undefined case anyway, because the primary keys for these things are all integers. Per my previous mail, FTS3 is quietly ignoring your PRIMARY KEY declaration on id. To use your ID values as an INTEGER PRIMARY KEY, you will need to INSERT, SELECT, and otherwise manipulate your ID values using one of the identifiers “docid [] rowid, oid [or] _oid_” according to FTS3 docs. Do the following query on your table to confirm this: SELECT rowid, id FROM Directors; Per my tests, you will most probably find different values in each column. (If the values are same, this is a coincidence you cannot rely on.) The rowid is the actual PRIMARY KEY in an FTS3 table—and since you are not inserting values for it explicitly, it is automatically assigned. Presumably this pass-through behavior is what allows integers to be used as join columns even in an fts table. If this edge case persists, where a bound integer ends up as a string internally, won't joins fail as well? No. If you are using an integer PK to do the JOIN (which is a good idea), you need to join on rowid in your FTS3 table and an INTEGER PRIMARY KEY in your other table(s). With your current schema, your primary key constraint is not enforced; referential integrity is not guaranteed; and if bad data is accidentally inserted, joins (and other actions) may fail *even* on the box where your id is coming out as an INTEGER. I'm working on a standalone test script to narrow down the problem... I suggest you refocus your efforts; unless you want to dig into FTS3 sources with an eye to patching for your needs, the problem is already diagnosed. Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? 3.7.5 RC2 FTS4 division by zero exception
On Mon, 31 Jan 2011 18:08:41 -0500, Ralf Junker ralfjun...@gmx.de wrote:
This SQL:
drop table if exists t11;
CREATE VIRTUAL TABLE t11 USING fts4;
INSERT INTO t11 VALUES('quitealongstringoftext');
INSERT INTO t11 VALUES('anotherquitealongstringoftext');
UPDATE t11_stat SET value = X'';
I actually think this is a documentation bug/failure-to-warn liability:
WARNING: Deliberately junking FTS’ internal tables may
not only corrupt your FTS table, but also cause SQLite
to crash. Don’t do this. Also do not use the hair dryer
while in the bathtub, attempt to stop the chainsaw’s chain
with your hand, c. Inserting peanuts into your SQLite
database may cause it to contain nuts.
Please add to FTS docs §6 so you can avoid being sued.
SELECT matchinfo(t11, 'nxa') FROM t11 WHERE t11 MATCH 'a*';
leads to a division by zero exception in fts3_snippet.c, line 1171:
pInfo-aMatchinfo[iCol] =
(u32)(((u32)(nToken0x)+nDoc/2)/nDoc);
nDoc is zero here, which is not tested for, but I believe it should.
Ralf
Does the crash also occur when the zero occurs naturally, or is that
codepath not exercised? Perhaps an assert() is in order.
Very truly,
Samuel Adam a...@certifound.com
763 Montgomery Road
Hillsborough, NJ 08844-1304 • United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly
On Fri, 26 Nov 2010 07:27:02 -0500, Simon Slavin slav...@bigfraud.org wrote: On 26 Nov 2010, at 6:52am, Niklas Bäckman wrote: You are right of course. The shell should not count code points, but graphemes. http://unicode.org/faq/char_combmark.html#7 [snip] Or would it be possible to write such a graphemelen(s) function in not too many lines of C code without needing any external Unicode libraries? No. Sorry, but Unicode was not designed to make it simple to figure out such a function. You need lots of data to figure out how the compound characters work. “Lots of data” can still be represented efficiently: http://www.strchr.com/multi-stage_tables (I am not affiliated with that site in any way.) Such coding tricks seem more usually used for case-folding tables, script identification, and so forth; but I don’t see why the same principles couldn’t be used for all Unicode properties, including the combiner stuff. You don’t need ICU or a similar monstrosity to get at Unicode properties. Big, heavy libraries will help you support CLDR, different collations for every language, calendrical calculations and conversions, and so on, and so forth. Excluding Unihan, basic Unicode-property lookups should compile down much lighter in weight than SQLite itself. N.b., there is a severe bug (pointers calculated based on truncated 16-bit values above plane-0) in a popular Unicode-properties SQLite extension. The extension only attempts covering a few high-plane characters—if memory serves, three of them in array 198; but with the high-bits snipped off, I rather doubt those will be what is actually affected. I attempted contacting the author about the bug last year when I discovered it, but was unable to find a private contact method on a brief glance through the author’s site. Perhaps the bug has been fixed by now; I never checked back; anyone who intelligently investigates compiler warnings would not be bitten anyway. I write off the whole episode as a victory for spammers. Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Efficient Paths in Practice Through a 21-bit Codespace (Re: SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly)
On Fri, 26 Nov 2010 08:40:42 -0500, Jean-Christophe Deschamps j...@q-e-d.org wrote: At 14:26 26/11/2010, [Samuel Adam a...@certifound.com] wrote: N.b., there is a severe bug (pointers calculated based on truncated 16-bit values above plane-0) in a popular Unicode-properties SQLite extension. […] I believe you refer to Ioannis code. Yes. I found this 16-bit truncation and decided to expand that trie to 32-bit in order to support those characters correctly. With due regard to the fact that Mr. Deschamps evidently wrote working code and I thus far apparently have not, I have a suggestion as to space/time tradeoffs. 32 bits to cover Unicode’s 21-bit space always irked me. 24 bits won’t do due to alignment issues, and 16 bits is just too small. However: (a) 99% of usage in 99% of apps is confined to the Basic Multilingual Plane (Plane 0). [Source: The same fundament as from which springs the majority of published statistics.] (b) Modern operating systems typically load executables (including libraries) using memory mapping. If RAM is constrained, an intelligent virtual memory subsystem will leave any unused tables on disk most of the time, only to be faulted-in for the 1% cases. (c) A code path which uses 16-bit-based tables for the BMP, and only invokes a separate path through 32-bit-based tables for Planes 1–16, will permit *smaller, less-wasteful* tables to be the ones kept in RAM for the 99% cases. (No) thanks to contemporary chip architects, the problem thence becomes how best to effect these in-practice space savings without unacceptable time loss (usually in a tight loop) for extra branching. For now, all I can say is that goto is a smart programmer’s intimate companion. Unicode properties and characteristically similar data being quite commonly needed, I suspect such a method would have uses far beyond SQLite. (Perhaps I should patent it sometime within the next 365 days. g) Very truly, Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ratio of test code to database code
On Fri, 12 Nov 2010 13:09:07 -0500, Simon Slavin slav...@bigfraud.org wrote: On 12 Nov 2010, at 4:25pm, Dr. David Kirkby wrote: I think in general mathematical software like Sage is very difficult to test. Yes, I read your response and agree with this. But it's worth pointing out that commercial maths applications like MatLab /do/ have testing like that. That's one of the reasons they're so expensive. I'm sure they don't test every possible value, but they are thorough and they are run for every point version. I think I know what you mean, and I have no wish to be snide hereby; yet resist the obvious, I cannot: If considering the set of real numbers, wouldn’t testing “every possible value” require at least א -sub-1 tests? With א -sub-1 tests betwixt each test? Ad infinitum, literally? Someone please answer this urgent question, as I am bad at math and I’d like to get to the bottom of it. free(OT); return 0; } Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ([0] …with extraneous spaces because I doubt everybody’s MUA supports BIDI overrides properly.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ratio of test code to database code
On Wed, 10 Nov 2010 08:53:40 -0500, Dr. David Kirkby david.kir...@onetel.net wrote: Someone recently said he felt that a test:code ratio of 1:1 is about optimal, so Where do people get notions like this? Cf.: : Someone recently said he felt that programmer productivity is about : optimal at 100 lines of code per day, so we fired everyone who How else are people to measure things they don’t understand? It can all be so much easier: : Someone recently said he felt that the practical value of an : equation is best measured by its length, so we decided to : stop using E = mc². : Someone recently said he felt that a circumference:radius ratio of : 4 is about optimal, so we legislated thus the value of π. : Someone recently said he felt that a publications:year ratio : of 16 is about optimal, so Gauss was an incompetent mathematician. “Facere e natura” is the mother of “pauca sed matura”. Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to represent UUID
On Mon, 08 Nov 2010 12:24:22 -0500, George george.r...@gmail.com wrote: Hi there. I'm wondering what the best way to represent a 16 byte UUID in sqlite might be (I didn't see any specific data type for UUIDs in the documentation, but forgive me if there is). I could just stringify or blob it, but I might have to index a large number of items by this identifier (thousands) , so speed is a consideration more than space would be. BLOBs are always compared using memcmp(). Hopefully that is an intrinsic in your compiler. TEXT strings may be compared case-sensitively, case-insensitively, or with a user-defined collating function. Note that you can bind and insert any arbitrary bytes as a TEXT value; SQLite simply assumes you are inserting valid UTF-8/UTF-16 (depending on database encoding). I therefore presume (but do not know for certain) that in the unlikely event you somehow have something faster than memcmp(), you could stuff your 16-byte UUIDs in as TEXT and apply your user collation. Yes, that would be a very ugly hack; and there may (or may not) be additional user-function overhead which would overwhelm the speed advantage of a hypothetical faster-than-memcmp() function. Those are the only two SQLite3 datatypes capable of holding a 16-byte value. Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
On Wed, 03 Nov 2010 18:35:52 -0400, Stephen Chrzanowski pontia...@gmail.com wrote: Couldn't you do something like: select length(FieldName) from TableName where Condition=True ? The result would be the size. Otherwise, the only thing I can think of Caution: This will return the size in bytes of a BLOB field, but the size in *characters* of a TEXT field. Except for lobotomized 7-bit-only text, the two will almost never be the same in a UTF-8 database. If the database is encoded in UTF-16, there are exactly two bytes for every plane-0 character and exactly four bytes for every character in plane 1 and up. Casting to a BLOB will result in the text being simply reinterpreted as a BLOB, which is probably what the original poster wants. If applied to a numeric (INTEGER or FLOAT) value, length() will first cast input to TEXT, then return the size in characters; since the cast will only return values 127, the result will be in bytes for UTF-8 and half the byte-size for UTF-16. Casting to BLOB will not fix this, as the value is still first cast to TEXT. Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
On Wed, 03 Nov 2010 18:24:57 -0400, Scott A Mintz sami...@ra.rockwell.com wrote: Is it possible to construct a query that will tell me the total size in bytes of the result set? Specifically, in a messaging protocol that returns data we need to fragment the reply and it would be nice to know how much data or how many packets will be required to send the response. Per my other post, for TEXT values, you can CAST to BLOB and then use length(). Assuming the C API, I am guessing that it’s much more efficient play with sqlite3_column_bytes(), sqlite3_column_bytes16(), sqlite3_value_bytes(), and/or sqlite3_value_bytes16(). These will return byte-counts (with no zero terminator) for both TEXT and BLOB values. As far as I can tell, you will need to count the bytes in numeric values yourself. (I’ve looked into this before, and just glanced into it again.) SQLite uses its own variable-length integer representation internally, occupying between 1 and 64 bits per value; if this is for a network protocol, you probably want to count the bytes in protocol representation rather than SQLite’s representation. Counting the bytes (or lack thereof) in NULL values should probably also be done in your protocol’s representation. Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
On Wed, 03 Nov 2010 19:17:48 -0400, Samuel Adam a...@certifound.com wrote: SQLite uses its own variable-length integer representation internally, occupying between 1 and 64 bits per value; if this is for a Sorry to reply to my own post; I wish to be precise. By “internally”, I meant (and should have said) “in the database file format”. Which raises another question: If you want the byte-length of an SQLite integer, do you mean as stored in the database, or as processed in-memory, typically via sqlite3_int64 and sqlite3_uint64? Looking at the typedefs, these latter *could* compile to something bigger than 8 bytes on exotic platforms (although it would be difficult for them to be smaller). Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Crash SQLite [version: 3.6.23.1; tested: shell]
CREATE TABLE Test (Number INTEGER DEFAULT (123)); -- Was originally a dynamic default with strftime(). -- Cut down for test case/to find crash. CREATE TRIGGER Automatic BEFORE INSERT ON Test BEGIN DELETE FROM Test; END; INSERT INTO Test DEFAULT VALUES; -- Crash here. (Yes, I have an actual use case; that’s how I found the bug.) Most recent version tested: * SQLite version 3.6.23.1 * Application tested: sqlite3.exe shell from sqlite.org (file date 2010-03-29) * Platform tested: Windows * Dr. Watson claimed exception number c005 (access violation): “The exception generated was c005 at address 00436B85 (nosymbols)” * A few other versions and configurations have crashed as well. * First noticed in SQLite 3.6.19. Plug for outstanding feature request: DECIMAL type (such as IEEE 754-2008 BCD format). Why? Humans use decimal; computers ought be made to serve humans, not vice versa; and if you put a penny into a database, you should be able to get a penny back out. Very truly, Samuel Adam http://certifound.com/+A 763 Montgomery Road • Hillsborough, New Jersey (U.S.) Justice: http://certifound.com/ Spotlight: http://certifound.com/+A9097BE/JPMorgan.Chase.delenda.est. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique index on REAL values and equality check
On Sun, 13 Dec 2009 15:16:08 -0500, Alexey Pechnikov
pechni...@mobigroup.ru wrote:
Hello!
I have some frustration about
create table test (
save_date REAL unique
);
insert into test values (julianday('now'));
select * from test;
2455179.34204073
insert into test select * from test;
Error: constraint failed
select count(*) from test where save_date=2455179.34204073;
0
As we can see, the unique index can check equlity of REAL values
but the = operator can not. it's fantastic I think :-)
Smells like a decimal/binary rounding error.
Try it from C (or whatever), feeding back *exactly* the result of your
SELECT as a bound value. I would be quite surprised if = didn’t work.
Although I haven’t done the math here, experience suggests that you are
dealing with two values which are actually unequal on the bit level.
SQLite really needs a DECIMAL type (e.g. based on IEEE 754-2008 BCD) so
these questions can be answered “you’re using the wrong type” rather than
“the database can’t count like a normal human”. BCD floats still have
rounding issues common to all floats, but let’s take one thing at a time; on
a different but related note, things like this should never exist:
http://www.sqlite.org/faq.html#q16
I am aware that platform support is awful, of course.
Samuel Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique index on REAL values and equality check
On Sun, 13 Dec 2009 17:02:05 -0500, Alexey Pechnikov pechni...@mobigroup.ru wrote: Hello! On Monday 14 December 2009 00:24:42 Samuel Adam wrote: Smells like a decimal/binary rounding error. It'is not the answer to my question. Why the unique _index_ can understand when REAL values are equal? Because the unique index is not rounding and not converting binary to decimal. Going back to your original post, when you did this: select * from test; 2455179.34204073 …it gave you a rounded decimal approximation[†], which you then used here: select count(*) from test where save_date=2455179.34204073; 0 By contrast, this used the actual floating-point value, not the rounded value: insert into test select * from test; Error: constraint failed As I said, take the result of your SELECT * programmatically and use parameter binding for your SELECT count()… WHERE. If it still returns 0, then it means my answer to your correction is incorrect[‡]. But I doubt it will return 0. For further information, do web searches regarding “decimal floating point” and “binary decimal conversion”. This kind of error is quite common, and is one of the computing industry’s dirtiest open secrets. Contemplate this: If you put a penny into a binary float, you do not get a penny back out. Binary floats are manifestly unsuitable for most applications found in day-to-day human life; but programmers don’t know this, so they keep using binary floats incorrectly, and most people don’t care, so platforms support of decimal floats is not universal as it should be. Samuel Adam [†] Again, I haven’t done the math to verify that this is your problem. It’s impossible to take that step without extracting the 8-byte floating point value stored in your database. But this has “decimal conversion error” written all over it. Or perhaps another floating point round error, with the same end results: = seems to fail. Decimal conversion looks like the most probable culprit here. [‡] See above. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: compile-time foreign keys setting (patch attached)
On Fri, 11 Dec 2009 05:05:49 -0500, wcl...@gfs-hofheim.de wrote: I hope I'm not duplicating a request already made by someone, but it'd be great if the foreign keys flag can be set as a compile option (like the recursive triggers flag). At the least, it is a request that someone has been *intending* to make. By the way, SQLITE_DEFAULT_RECURSIVE_TRIGGERS is not documented at http://www.sqlite.org/compile.html. For those working from the amalgamation and willing to live dangerously, insertions are needed at 3.6.21 sqlite3.c original lines 96740 and 6633. (Jump to 96737 and 6629 for context.) With a 3.65MiB source file, an efficient text editor is recommended. According to the shell, it appears to work beautifully: sqlite3 SQLite version 3.6.21 Enter .help for instructions Enter SQL statements terminated with a ; sqlite PRAGMA foreign_keys; 1 sqlite PRAGMA recursive_triggers; 1 sqlite To some of us who use SQLite and care deeply for referential integrity, Dr. Hipp’s October 8 surprise announcement of native foreign key enforcement has thus far been one of 2009’s two biggest pieces of software-industry news. (The other was the release of Windows 7.) That speaks both to the state of the software industry, and to the critical import of foreign key enforcement. Samuel Adam As a simple patch that I hope can be merged into the next release (made against the sources for 3.6.21)... diff U3 main.c main.c --- main.c Fri Dec 11 10:51:37 2009 +++ main.c Fri Dec 11 10:44:06 2009 @@ -1603,6 +1603,9 @@ #if SQLITE_DEFAULT_RECURSIVE_TRIGGERS | SQLITE_RecTriggers #endif +#if SQLITE_DEFAULT_FOREIGN_KEYS + | SQLITE_ForeignKeys +#endif ; sqlite3HashInit(db-aCollSeq); #ifndef SQLITE_OMIT_VIRTUALTABLE diff U3 sqliteInt.h sqliteInt.h --- sqliteInt.h Fri Dec 11 10:51:37 2009 +++ sqliteInt.h Fri Dec 11 10:44:09 2009 @@ -357,6 +357,10 @@ # define SQLITE_DEFAULT_RECURSIVE_TRIGGERS 0 #endif +#ifndef SQLITE_DEFAULT_FOREIGN_KEYS +# define SQLITE_DEFAULT_FOREIGN_KEYS 0 +#endif + /* ** Provide a default value for SQLITE_TEMP_STORE in case it is not specified ** on the command-line ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
