Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]
Quoth "James K. Lowden" , on 2014-02-09 17:49:15 -0500: > That's true, although it's not quite fair to say the filesystem cache > isn't "coherent". It's just not the Linux implementation. FYI, the term "coherent" I interpret as being used in the specific technical sense involved (data is reflected automatically between mmap and read/write), by relation to "cache coherent". So in that sense it is both correct and not a disparagement of the OpenBSD behavior. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Troubleshooting nested query performance issue
Quoth Petite Abeille , on 2014-02-03 23:49:14 +0100: > Not directly related to your question, but… why oh why do people > molest their queries by gratuitously and pointlessly aliasing > perfectly good table name to meaningless random one letter codes?!? > Masochism? Because otherwise you wind up with things like long_table_name.foo, long_table_name.bar, long_table_name.baz, long_table_name.quux, long_table_name.plugh, long_table_name.plover and that buries the changing part in a sea of sameness. The "real" reason I use aliases-always in the first place (but which wouldn't preclude longer aliases) is to semantically distinguish the underlying table from the source as used to construct a particular query; that also makes it more consistent when queries might have multiple sources from the same table. (I know not everyone thinks the same way, so there's a subjective element.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?
Quoth Eleytherios Stamatogiannakis , on 2014-01-23 14:37:23
+0200:
> Let me describe a use case where a not unique key and without rowid
> are most welcome. We have a distributed big data system here which
> uses SQLite for the partitions. To be able to efficiently execute
> join queries on splited partitions, we need to assemble the
> partitions of one side of the query to create an index on them.
Do you really need bag rather than set semantics? That is, can there
be a case where rows that are identical in _all_ columns need to be
treated as separate and (e.g.) have both copies show up in queries?
Most of the time, the way data is represented in relational databases,
this winds up requiring an arbitrary identity key anyway to be
practical (so one can manipulate a specific instance of an otherwise
identical row), or else it's equivalent to adding a count column to
turn {(x, y, z), (x, y, z)} into {(x, y, z, 2)}, though the latter has
a similar slight complexity hitch in the merge case to what you were
doing.
If you do require the above, I'm curious what data is being handled
here, since it's a rare case (but I understand if you don't wish to
say). If not, then you may actually have a primary key of the whole
row, in which case I'm not sure why inventing a rowid is needed.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What this function returns?
Quoth Igor Korot , on 2013-11-29 18:49:05 -0800: > Trying to change the return type to long does not solve the warning. > > Which value should this function return? As you can see from http://sqlite.org/c3ref/last_insert_rowid.html, it returns sqlite3_int64, a signed 64-bit integer type. The C99 name and I think the C++11 name for this is int64_t, which is probably what you want, but I vaguely recall the Microsoft compiler requires jumping through some kind of hoop to get it. You could just use sqlite3_int64 directly if you don't mind taking the header dependency. The truncation is actually a potential error: e.g., a row ID of 2^32 would be returned as 0 instead on a system with 32-bit int. It's the sort of thing you might not see in production for a while until it breaks everything suddenly a ways down the line. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Colons (was: RE: Groups and members)
Quoth Rob Richardson , on 2013-11-06 14:08:34 +: > In Igor's post below, what is the meaning of the colon in front of mypid? Parameters/placeholders. http://sqlite.org/lang_expr.html#varparam ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board
Quoth jitendar kumar , on 2013-10-19 14:48:46 +0530: > but the same compiled with ARM - Cortex A15 cross compiler and the query > executed on arm board it gives the output of EPOCH time. i guess the > function > gettimeofday() fails to add the time to the default EPOCH time. That sounds like it's not an SQLite-specific problem; if so, that's leading off-topic. Do other programs that use gettimeofday or clock_gettime also return an incorrect time when executed in this environment? Does the ARM board actually _have_ real-time clock hardware to store the current time in between resets? If so, is it set properly? If not, how is the current time acquired by the OS? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't seem to create DB with unvacuumed data [ for testing ]
Quoth Paul L Daniels , on 2013-10-12 13:57:00 +1000: > I've tried the pragma autovacuum=0 and journalling set to none to no > avail. Every time I exit from the command line SQlite3 tool I find > that the data I explicitly deleted has genuinely been removed. > > Any suggestions? PRAGMA secure_delete=0 ? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another 2 questions about SQLite
Quoth "James K. Lowden" , on 2013-07-12 12:30:13 -0400: > as the first one reads it. In fact, I'd be interested if you could > point to a single standard C library function that, when called > out-of-sequence, doesn't return an error but permits the process to > proceed destructively. free(). Now, can we stop the repeated philosophical arguments about these sort of things on the SQLite list? They are getting old and drifting off topic, I think. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] surprising behaviour: in-memory db allocs less than file db
Quoth Stephan Beal , on 2013-06-23 03:07:02 +0200: > file db: > ==16021== total heap usage: 856 allocs, 856 frees, 222,957 bytes allocated > > vs :memory: > ==16043== total heap usage: 832 allocs, 832 frees, 203,430 bytes allocated > > (Most of those allocs were done by my code, not sqlite.) i'm just curious > how the second one could possibly allocate less than the first. Here's a plausible explanation that I haven't thoroughly verified. For both cases, for such a small database, all the actual data is likely to wind up in the page cache. For an in-memory database, however, there's no need to manage an actual file descriptor, filename, filesystem-based locks, possibly certain header information... Especially, note that on Unix-y platforms, where POSIX fcntl locking is used, SQLite keeps internal global state matching up fds to inodes. This is so that if you open the same database multiple times, and a lock is ever taken on one connection, no other such connection can close its fd before the first lock is released. Otherwise, due to fcntl's broken design, the close would release the lock immediately, potentially resulting in database corruption. The difference is not particularly large, and is easily explainable by some combination of the above or similar. If you want something more exact, of course, feel free to run a source-level trace using your favorite allocation analysis software and report back. :-) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MMIO and VFS Obfuscation
Quoth Drake Wilson , on 2013-06-07 08:18:05 -0500: > Actually, I dropped a paragraph on the floor, sorry. It's probably better to > use > xRead for this, since in that case SQLite will manage its own memory for the > cache > of decrypted pages. The loss in that case, if you still use mmap behind the > scenes, > is that you don't respect PRAGMA mmap_size anymore since the rest of SQLite > will > think you're using "normal" I/O, and so it becomes impossible for other code > to > turn mmap off to avoid I/O-error-based execution faults or such. Gyaaah, sorry for all the little corrections---that's incomplete too, since you could still respond to SQLITE_FCNTL_MMAP_SIZE in xFileControl; you'd just have to make sure to apply it to the xRead path. Now I will be quiet for a while, since my head apparently isn't fully in it. c.c ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MMIO and VFS Obfuscation
Quoth Drake Wilson , on 2013-06-07 08:14:27 -0500: > If you really want, you might be able to implement xFetch to allocate a shadow > buffer, decrypt from the map into that, and return that pointer. Since it's > designed for accessing maps directly, though, I don't see it documented what > the lifetime of that pointer has to be---it might be "until the file is > closed", > in which case you have to keep those shadow buffers alive the entire time, but > you might still avoid taking syscalls that way. Actually, I dropped a paragraph on the floor, sorry. It's probably better to use xRead for this, since in that case SQLite will manage its own memory for the cache of decrypted pages. The loss in that case, if you still use mmap behind the scenes, is that you don't respect PRAGMA mmap_size anymore since the rest of SQLite will think you're using "normal" I/O, and so it becomes impossible for other code to turn mmap off to avoid I/O-error-based execution faults or such. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MMIO and VFS Obfuscation
Quoth Paul Vercellotti , on 2013-06-07 01:07:35 -0700: > The current system does in-place decryption on read (no copying), which adds > almost no overhead to the operation. Now in-place decryption with a > memory-mapped file seems like it would dirty the page, which could > accidentally get flushed back to the file on close potentially corrupting the > database - I'm wondering if that's a valid concern. Yes, you can't safely do it in-place on the mapped region; you'd have to make a copy. Since the big advantage of memory mapping is avoiding the copy, the benefit would be noticeably decreased, I'd think---though if your decryption code can do an out-of-place transformation "just as easily" then you could still elide that together with the copy and maybe be a bit easier on the cache than an explicit kernel->user copy plus an in-place transformation. If you really want, you might be able to implement xFetch to allocate a shadow buffer, decrypt from the map into that, and return that pointer. Since it's designed for accessing maps directly, though, I don't see it documented what the lifetime of that pointer has to be---it might be "until the file is closed", in which case you have to keep those shadow buffers alive the entire time, but you might still avoid taking syscalls that way. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.17 schedule
Quoth Richard Hipp , on 2013-05-03 14:00:28 -0400: > The release of SQLite 3.7.17 has been tentatively scheduled for > 2013-05-22. We do not anticipate any further application-visible changes, > though we may well merge the winOsTrace branch ( > http://www.sqlite.org/src/timeline?r=winOsTrace) prior to release. A > summary of changes can be seen here: > > http://www.sqlite.org/draft/releaselog/3_7_17.html I (predictably) approve of the following: - adding support for memory-mapped I/O but requiring explicit enable - secondary application ID support - the detection of file link mismanagement on Unixy systems - extension loading more compatible with C flat-namespace behavior I have not actively tested the latest snapshots yet. It would be useful to clarify the error behavior in the document on memory-mapped I/O. Currently, it reads: "Instead, the I/O error causes a signal which, if not caught by the application, results in a program crash." This could be read to imply that it is safe for the application to catch the signal and longjmp away to abort the operation. The documentation should explicitly say what states the SQLite data structures may be in in such a case. If this has not already been considered, the answer could be presumed to be that they may be in an inconsistent state and should not be further touched at all. Minor question: why is the application ID four octets rather than the eight that were originally suggested? It would also be convenient for the application_id pragma to be able to use strings or integers; I might split it into application_id_integer and application_id_string to minimize confusion over return values. But this is not essential. Do you accept "speculative" application ID registrations to avoid software that might or might not later be published potentially taking a sudden change of ID? Do you accept "two-level" registrations that would use the "application ID" as more of a "vendor ID" and the user_version as a per-vendor application+schema-version ID? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Nico Williams , on 2013-04-04 19:15:52 -0500: > This is off-topic, I know, so maybe we should continue this off-list, > if at all, but... Switching to private mail. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Nico Williams , on 2013-04-04 16:08:24 -0500: > This is very sad. But really, the OS should cause kvserv to hang > waiting for I/O from the device to complete (and you should get some > indication, in dmesg, on the console, in a dialog -something- that > there's a missing device that's needed). Sending SIGBUS because a > device is missing is a bit heavy-handed of the kernel! Well, the device is _gone_ from the perspective of the OS; the kernel has no way of knowing whether I intend to plug that USB device back in. The "removable media" aspect is a bit of a red herring; I am just using that as a convenient way of inducing a mostly-repeatable read failure at the hardware level. A more permanent case would be a bad sector on a magnetic disk. It would not make any sense for the kernel to pause the application indefinitely in case the sector can be magically restored in the future. In the case of read() or similar, you are already in a system call and the kernel can return an error code which the application must already know how to handle. In the case of mmap, what is interrupted is a processor-level memory access, and there is no provision for returning an error code; all that can be done is to reroute the entire control flow, and on Unixy systems that is done using signals. Now, user code that can assume it controls the entire process _does_ have the ability to establish a signal handler to fix up the access. E.g., one can map a zero page over the broken page, set a flag somewhere else saying "that data is corrupted", and then somewhere outside the inner processing loop, check the flag and abort the operation. But the sigaction interface is not flexible enough to make it safe to do this from library code in general, because signal handlers are process-wide. E.g., consider two libraries which both want safe access to memory-mapped files and are being invoked in different threads... AIUI, Windows's use of SEH is slightly better in this regard, since the relevant exception handler can be established using only local state. This still requires a compiler capable of emitting SEH frame establish/teardown code on Windows x86-32 (which had a patent fiasco a while back which may still be ongoing), and I think maybe appropriate unwind tables and framing on Windows x86-64, and it doesn't help the case of Unixy systems at all. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Drake Wilson , on 2013-04-04 10:20:44 -0500: > So it is perfectly okay to use unprotected mmap accesses if an I/O > error on the file will already make the entire process uncontinuable. > The question is whether this applies to arbitrary SQLite databases > that an application may open, and I suspect that (a) it probably > doesn't, and (b) this reliability transitivity behavior would be a > significant departure from earlier SQLite versions. Here is a much more direct and concrete example. Referenced files may be retrieved from: http://dasyatidae.net/files/2013/sqlite3-201304040051/ Here are the steps I used. This is on a modern Debian GNU/Linux AMD64 system. - Compile kvserv.c along with an _earlier_ (probably system) version of SQLite than the snapshot amalgamation mentioned above---I used: gcc -std=c99 -o kvserv kvserv.c -lsqlite3 -lpthread -ldl - Mount a removable disk that you don't care about very much (I used a spare USB flash disk), and copy keyval1.db to it. Unmount, unplug, replug, and remount the disk read-only. The database is deliberately a few megabytes in size to reduce the chance that all of it will be read ahead into cache; I used: echo 1 | sudo tee /proc/sys/vm/drop_caches a bit ad-hoc to help ensure this, though it should not theoretically be necessary. - Symlink the copied file to keyval.db in the current directory (all the other files should be on a reliable local disk), and ensure UDP port 11105 is not in use. Run kvserv. In a separate terminal, run something akin to: socat - udp6-datagram:[::1]:11105 (In retrospect I should have used a Unix-domain socket, but I do not have time to change it right now; I apologize for the inconvenience.) - Issue queries to the simple key-value server by entering keys, one per line, in the socat terminal. In particular, the keys 'a', 'b', and 'c' are defined in the given DB, along with all five-digit decimal numbers. Responses should be returned beginning with "OK" followed by either result data or nothing. - Unplug the removable disk hard, simulating a media failure. Issue additional queries. Responses should be returned beginning with "NG", indicating that there was an error retrieving the requested data. Repeating these steps, but compiling the application with the sqlite3.c from the 201304040051 snapshot amalgamation that uses unprotected mmap, causes the entire kvserv process to die with SIGBUS as soon as a query tries to access the volume while it is unplugged. Unless the design of kvserv.c is relevantly unreasonable, this should help demonstrate the danger of switching SQLite to use unprotected mmap by default. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Richard Hipp , on 2013-04-04 10:51:22 -0400: > Is this really a problem? Your executable and all of your shared libraries > are also mmapped into your address space. If accessing mmapped memory were > causing bus errors, then we'd be seeing bus errors all over the place. As I interpret it, this is because it's commonly assumed that if part of your executable code goes away, you cannot reliably continue (there is no way to know what to do now), so crashing the whole process is acceptable. A system integrator or administrator must choose the devices that will contain native code accordingly, since they can bound the reliability of almost the entire system. A similar argument applies for choosing swap devices that may back any anonymous memory; if a swap device fails, it is expected that a lot of things may crash. So it is perfectly okay to use unprotected mmap accesses if an I/O error on the file will already make the entire process uncontinuable. The question is whether this applies to arbitrary SQLite databases that an application may open, and I suspect that (a) it probably doesn't, and (b) this reliability transitivity behavior would be a significant departure from earlier SQLite versions. As a hypothetical, more concrete example, consider a cluster of DNS servers backed by mostly-read-only SQLite databases. The system integrator chooses highly reliable local ROM devices to store OS and application code, but due to size and update flexibility requirements, the database files are spread out and accessed via network filesystem. With unprotected mmap, if any storage backend goes down or suffers a media error, the entire DNS server process may crash upon trying to read it, as opposed to receiving an error code and returning temporary SERVFAIL responses for the affected data sets until the error can be repaired. (Arguably someone running such a service should plan for this in other ways too, but I think SQLite should not exacerbate the effects of such failures any more than necessary.) This can be avoided by explicitly turning mmap off, but due to this I would think that off should be the default, much like how WAL is not the default journal mode (despite its considerable benefits in many use cases) because it creates additional requirements that must be taken into account. Of course I may be missing something important here. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Richard Hipp , on 2013-04-04 08:02:34 -0400: > By making use of memory-mapped I/O, the current trunk of SQLite (which will > eventually become version 3.7.17 after much more refinement and testing) > can be as much as twice as fast, on some platforms and under some > workloads. [...] I'm curious how you plan to handle reliability against I/O errors in mmap mode. My understanding is that achieving this in a library without potentially interfering with the host program operation is extremely difficult on Linux, and is reliable but requires significant platform-specific juggling on Windows; I don't know as much about other OSes. Specifically, an I/O error faulting in an mmapped page can deliver a SIGBUS to the thread. If unhandled, this will crash the entire host application, and setting local signal handlers for just that case is hard-to-impossible to do reliably from libraries without a lot of coöperation from both the host application and any other library that needs the same thing. A possible way to partially test this (which I haven't tried against this SQLite yet) is to stop the reading process right before it reads a page that it has not yet touched, truncate the file to a length less than the page offset, then resume the original process. It now occurs to me (which it did not before) that WAL mode also has this danger to some extent with the -shm files, but this is mitigated mainly because (a) WAL mode must be turned on explicitly for a given database file and secondarily because (b) AIUI, the -shm files are only kept while any processes have the database open, and are small enough that they are very likely to stay in memory the entire time. (Even so, it may be worthwhile to mlock the regions before using them, which a quick grep does not find currently, but that is an open question, not a hard recommendation.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL Test failures on ARM
Quoth Bk , on 2013-03-17 21:25:21 -0700: > can some one point out what went wrong here ? Community lists do not provide guaranteed responses, and repeatedly hassling the list with requests for one is unlikely to make you any friends. Certainly I haven't responded because I don't have easy access to an ARM configuration with which to reproduce this. I would guess many others on the list are in a similar situation. You've now sent _seven_ messages in this thread with no content other than "someone talk to me". This is not polite. If you are in need of a guaranteed response, may I suggest hiring someone to be specifically responsible for giving you the results you need? http://www.hwaci.com/sw/sqlite/prosupport.html is an obvious place to start. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange eviction from Linux page cache
Quoth James Vanns , on 2013-02-07 16:32:49 +: > And I can confirm here that, over NFS, using just the sqlite3 CLI > the Linux page cache is cleared every time - all the damn pages that > were read in from disk are read in all over again; > > sqlite3 /nfs/file.db > $ select * from big_table # (vmtouch reports 163MB read and resident) > (don't even have to close sqlite3) > $ select * from big_table # (all pages evicted, SQLite begins to read 163MB > over NFS again) If you don't even have to close the SQLite shell for that to happen, I'm guessing it's an interaction with POSIX/fcntl file locking, which theoretically works over NFS but as I recall has some oddities. What happens if you do this? pragma locking_mode = exclusive; select * from ...; select * from ...; If the database is write-once and read-only, then exclusive locking mode should not actually block any readers (since no write locks are ever taken). If that helps in the two-selects case, it still might not help if unlock at process termination triggers your performance issue as well. In that case, try specifying the "unix-none" VFS to sqlite3_open_v2 for those readers only, which should turn all locking off for them. My other guess would be an interaction with the early open calls; AFAIK, unless SQLite is explicitly told to open the database read-only, it will try an O_RDWR open first, which will fail on a 0444 file but might plausibly trigger unfortunate codepaths somewhere in the kernel in the process of failing. I'm interested to see whether any of the above does any good, to improve my own knowledge of NFS. :-) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about date question
Quoth YAN HONG YE , on 2012-11-09 08:35:25 +: > > > //--why have noting in the result? This is not a realtime chat system. If you won't wait even ten minutes before squawking about the same thing again, a mailing list may not be for you. Are you understanding any of the responses at all? Give a sign of it if so! And "y" and "Y" are not the same thing. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about date question
Quoth YAN HONG YE , on 2012-11-09 08:28:53 +: > The result let crazy. Because they're STRINGS! There is NO DATE TYPE in SQLite! Not to mention if those values are intended to be ISO 8601, they should have zero padding; it should be "2012-09-07" and such---in which case they will compare correctly because that's the way that date format was designed, but it won't happen just because. The string "29" comes before the string "3" because "2" comes before "3". It doesn't automatically figure out what a string is "supposed" to be and compare accordingly. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about date question
Quoth YAN HONG YE , on 2012-11-09 05:45:06 +:
> why use supplierDate,date('now'),strftime(supplierDate) the result is not
> same result format?
> the one is 2012-11-09
> another is 12/01/2012
> ?
> and how to get the subtraction of two column?
SQLite calendar functions handles textual dates in ISO 8601 format,
but you're not asking strftime to extract a date from supplierDate and
format that; you're asking it to use supplierDate as a _format string_
and since it does not contain any % characters it is returned as-is.
The first argument to strftime is a format string, and the actual date
information is supplied as separate arguments.
You seem to be trying to treat SQLite as though it actually has a date
data type, but it doesn't. There are different _representations_ of
dates and times and durations and so forth, and only some of them will
work in some situations. Now _please_ try to understand this _before_
throwing another dozen SELECT statements at the list. Don't just ask
people to give you the magic formula or ask why it doesn't work when
you plug random things together; try to learn the underlying
principles properly.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE only deletes some records, not others
Quoth Corey Nelson , on 2012-07-08 00:15:36 -0600: > sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE "12.%"; Aside from the more immediately relevant aspects the other posters already mentioned, remember that double quotation marks in SQL are normally used for _identifiers_. For string literals, use single quotation marks. SQLite will sort of autocorrect the former into the latter sometimes, but it is not good practice to rely on this. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Interesting behavior with rowid aliasing and default values
(I apologize if this has been raised before; I see [1] which is vaguely related but not the same, and nothing on the ticket tracker or such. As an aside, it would be nice to have the links to the public offsite archives of sqlite-users replicated on the Mailman page for the list itself, since that's normally where I would expect to find information related to a specific mailing list.) [1] http://thread.gmane.org/gmane.comp.db.sqlite.general/10150 It seems that in SQLite 3.7.13 (and probably earlier versions), for a column that is an integer primary key (and therefore a rowid alias), default values are ignored, whereas a merely unique integer column that does not alias the rowid handles defaults like other columns. This bit me while I was trying to define a table constrained to hold only one row, for the purposes of storing database-wide parameters and metadata. Here's the use case that bit me, simplified: CREATE TABLE foo_master ( zero INTEGER NOT NULL PRIMARY KEY DEFAULT 0 CHECK (zero = 0), foo BLOB NULL DEFAULT NULL ); Subsequently doing an INSERT INTO foo_master DEFAULT VALUES fails with "constraint failed". Creating a similar table with CREATE TABLE fudge (x INTEGER NOT NULL PRIMARY KEY DEFAULT 500); without the CHECK constraint, and then doing an INSERT ... DEFAULT VALUES, yields the row (1) rather than (500). Changing PRIMARY KEY to UNIQUE or PRIMARY KEY DESC (the latter of which, per the CREATE TABLE documentation, does not create a rowid alias column) causes 500 to be inserted instead. I've placed the output of EXPLAIN INSERT ... DEFAULT VALUES in two cases below for reference (slightly reformatted). It is not _entirely_ unreasonable to imagine that default values on unique columns in general would result in strange behavior, since they are rarely useful, but I figured I would post this in case someone else runs into similar issues. Since it is easy to work around this by avoiding the rowid alias, I will simply do that in my application, but feedback on whether this was a conscious design choice in SQLite, whether it is documented somewhere that I have missed, and/or whether it is simply a bug or undefined behavior would be appreciated. ---> Drake Wilson After CREATE TABLE fudge (x INTEGER NOT NULL PRIMARY KEY DEFAULT 500), running EXPLAIN INSERT INTO fudge DEFAULT VALUES produces: addropcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 Goto0 9 0 00 2 OpenWrite 0 2 0 1 00 3 NewRowid0 1 0 00 4 Null0 2 0 00 5 MakeRecord 2 1 3 d 00 6 Insert 0 3 1 fudge 1b 7 Close 0 0 0 00 8 Halt0 0 0 00 9 Transaction 0 1 0 00 10 VerifyCookie0 15 0 00 11 TableLock 0 2 1 fudge 00 12 Goto0 2 0 00 After CREATE TABLE fudge (x INTEGER NOT NULL UNIQUE DEFAULT 500), running EXPLAIN INSERT INTO fudge DEFAULT VALUES produces: addropcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 Goto0 19 0 00 2 OpenWrite 0 2 0 1 00 3 OpenWrite 1 4 0 keyinfo(1,BINARY) 00 4 NewRowid0 2 0 00 5 Integer 500 3 0 00 6 HaltIfNull 19 2 3 fudge.x may not be NULL 00 7 SCopy 3 4 0 00 8 SCopy 2 5 0 00 9 MakeRecord 4 2 1 dd 00 10 SCopy 2 6 0 00 11 IsUnique1 13 6 4 00 12 Halt19 2 0 column x is not unique 00 13 IdxInsert 1 1 0 10 14 MakeRecord 3 1 6 d 00 15 Insert 0 6 2 fudge 1b 16 Close 0 0 0 00 17 Close 1 0 0 00 18 Halt0 0 0 00 19 Transaction 0 1 0 00 20 Verif
Re: [sqlite] What is so slow on dropping tables?
Quoth Roger Binns , on 2011-06-01 00:21:44 -0700: > On 05/31/2011 12:18 PM, Jan Hudec wrote: > > - Is there any way to speed it up? > > Another way (somewhat hinted at by Nico) is that you can create these tables > in separate databases and use attach to bring them in. To drop a table you > can just detach and then delete the database file (at a later point if > necessary). If the new database is going to be the approximately the same > size as the old one then you can overwrite the database header to cause the > database to be empty but already the allocated size so the operating system > won't have to go through a free then allocate cycle for the file blocks. This is a reasonable suggestion, but watch out for the limit on the number of attached databases. You cannot reliably have more than 30 of them on a custom compiled SQLite3 (for 32-bit integers; the doc is silent re int64 type) and the default is a compile-time limit of 10. Whether this is a problem depends on your data and application architecture. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select * on empty virtual table
Quoth Joseph Stockman , on 2011-04-29 15:14:07 +: > My apologies. No worries. > We set idxNum to zero, idxStr to NULL, needToFreeIdxStr to zero, > orderbyConsumed to zero and estimatedCost to 1000. But that still looks a bit wonky; I don't see anywhere in the virtual table docs that idxStr is allowed to be NULL rather than a string. (I interpret the opaqueness to mean that the string contents are opaque, not the pointer itself.) needToFreeIdxStr just distinguishes between a string on the heap and a presumed-static string for the purposes of memory ownership. Does this still fail if you use an empty string or other recognizable non-NULL sentinel string instead? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select * on empty virtual table
Quoth Joseph Stockman , on 2011-04-29 14:50:51 +: > Our trace shows that we enter our xBestIndex method, see that > both nConstraints and nOrderBy are zero, so we return without > modifying any of the structures. So essentially you're returning all undefined values? That doesn't seem like a valid thing to do. Presumably you should set the result values to indicate "no constraints used, no ordering consumed, an arbitrary high cost estimate, and an indicator for full-scan access (that will be recognized by the xFilter method)", no? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to reuse a prepared statement
Quoth Sam Carleton , on 2011-04-22 23:36:04 -0400: > I am implementing a dataset update process. There is a for loop going > through the dataset either doing an update on the row or deleting the > row. So there are two statements that are preparied, currently I am > using the same basic logic for both. The problem is the second time > around I get a SQLITE_MISUSE. > > What am I doing wrong? You probably need to sqlite3_reset the statement after stepping it. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS snippet()
Quoth Gert Van Assche , on 2011-04-13 22:35:49 +0200: > SELECT snippet(example, '[', ']') FROM example WHERE CONTEXT MATCH > (SELECT TOKEN FROM example); You're asking to match a single independently arbitrarily chosen token from anywhere in the table (which is not even the same as "matching at least one token from the table"), not whether it matches the one from the same row. Can you do WHERE CONTEXT MATCH TOKEN instead? I think you still need a full table scan for that, but it should return the right results unless FTS4 has some relevant restriction on the RHS of a MATCH. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Quoth Lynton Grice , on 2011-04-03 12:37:06 +0200:
> There are NULL characters
You mean NUL characters.
> in the first couple header fields [...]
>
> I am no C expert but I have the following table defined:
>
> char *queueTable = "CREATE TABLE [test] ( "
> "[idx] INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT, "
> "[raw_stream_in] BLOB, "
> "[num_bytes_in] INTEGER );";
I wouldn't recommend the use of [] for quoting identifiers (I'd prefer
double-quotes for more ANSI-esque SQL), but that looks like it should
work fine.
> sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
> SQLITE_STATIC );
Be careful with SQLITE_STATIC. AIUI, the lifetime of the buffer must
then be a superset of the lifetime of the statement.
> For testing purposes I simply read the 15MB file from file into a char *.
>
>char *buffer = NULL;
>FILE *fp;
>if((fp = fopen("./in.txt", "r"))==NULL){
> exit(1);
>}
Not "rb" for binary mode?
>fseek(fp, 0, SEEK_END);
>long int fsize = ftell(fp);
>printf("File size: %i\n", fsize);
>rewind(fp);
>buffer = malloc(fsize * sizeof(char));
>fread(buffer, 1, fsize, fp);
>buffer[fsize] = '\0';
You're overwriting beyond the end of the array. You don't need an
extra NUL here.
>fclose(fp);
>
> I then point the MSG "raw_stream_in" to the buffer:
>
> msg_in.raw_stream_in = buffer;
And you set msg.num_bytes_in where?
> I then use the following statement as mentioned before to insert the
> stream into the BLOB field:
>
> sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
> SQLITE_STATIC );
That looks fine by itself, subject to the caveats of SQLITE_STATIC
above and whether stmt and idx are valid.
> _So essentially all characters are copied until the first NULL
> char._
No. I strongly suspect that's a red herring.
In summary:
- Make sure msg.num_bytes_in is actually set to what you want.
- Make sure you're handling the lifetime of the buffer correctly;
for testing purposes I'd use SQLITE_TRANSIENT rather than
SQLITE_STATIC, since that evades that issue at the cost of a
memcpy.
- Don't write past the end of the array.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create table if not exists and insert seed data
Quoth Philip Graham Willoughby , on 2011-03-22 10:18:08 +: > Yes, I had this problem - if sqlite3_open_v2 had an equivalent to > O_EXCL it would make this a lot easier: you would only try to run > your schema/prepopulating SQL if the exclusive open worked. If it > failed you would retry a non-exclusive open and then assume the > database was initialised. If such a flag is added it would be > helpful for it to implicitly get an exclusive lock on the database > it creates so that no-one else can try any queries before the schema > is there. If you control the database schema, user_version is a convenient place to put such markers, albeit not a foolproof one; you can set it to a magic number after initializing the DB structure, then query it on open. The main failure mode is if someone hands you a completely unrelated database that already has schema elements in it that collide with yours. Querying page_count or doing « SELECT COUNT(*) FROM sqlite_master » may also allow you to determine whether you have just created a database, though it's also not foolproof since there's no interface guarantee linking the two. You probably want to do a BEGIN EXCLUSIVE before loading the schema in most cases. (The EXCLUSIVE may not strictly be necessary, but I find it makes things clearer.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In-memory database with persistent storage
Quoth Simon Friis , on 2011-03-21 14:13:46 +0100: > Is is possible to make SQLite load a database file into memory and > then save it back to the file again when the connection to the > database is closed? You cannot do that with a single database exactly, but you can use the Online Backup API <http://sqlite.org/backup.html> to copy all data from a file database into a memory database and then copy the new data back when you are finished. > Would it improve speed? That depends on your application. In general if you think too many file accesses is a speed problem and you are willing to spend more memory to deal with it, some of the first things to try would be fiddling with the cache_size, synchronous, and journal_mode PRAGMAs, depending on what tradeoffs you want to make. > - paldepind ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strange UB detected
Quoth Eugene N , on 2011-03-13 18:14:49 +0200: > uchar* pblah[1]; > > pblah[0] = (uchar*)malloc(10); > > pblah[1] = (uchar*)malloc(10); // notice the order > > sqlite3* db; Your C code is broken. pblah is an array of 1 element, which is accessible (among other ways) as pblah[0]. pblah[1] is out of bounds, and depending on how the compiler allocates those vars it may wind up aliasing the db pointer. This is not an SQLite problem. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adjusting strategy for ROWIDs
Quoth "Black, Michael (IS)" , on 2011-03-04 21:55:01 +: > SQlite's random() is a pseudo-random (as are most all) so there is > no collision until you get the same value back at which point it > just repeats the whole sequence again. Yes, it's a PRNG, but even if the total period is at least 2^64, that doesn't guarantee no repeated 64-bit values unless the output reflects the entire state, no? ISTR SQLite using (A)RC4. And that doesn't help between connections. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adjusting strategy for ROWIDs
Quoth Enrico Thierbach , on 2011-03-04 22:11:07 +0100: > If I insert a record into a table with a primary key column id, > Sqlite assigns a ROWID as outlined here > http://www.sqlite.org/autoinc.html. However, I would like to assign > a totally random rowid. Why do you want to do this? In particular, why would it not work to randomize the values from the application side during inserts? Random numbers collide faster than you might expect if you're not familiar with the birthday paradox; normally, truly-random values that are expected to have no collisions are generated using entropy from the environment and are at least 128 bits long. I suspect this is not what you want. If you just want them to be "random-looking" then it may be more convenient to relate the underlying ID and the exterior ID through a suitable permutation of the 64-bit integer space. If neither of those is true, you're probably looking at probing several times to avoid collisions, and that's not something the stock "pick a new row ID" mechanism handles AFAIK. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
Quoth Drake Wilson , on 2011-02-28 14:44:38 -0700: > Furthermore, another approach if the name<->FD thing is the only > requirement would be to retrieve all the original VFS methods at init > time (using sqlite3_vfs_find) and only alter a few of them when > registering the new one, essentially "subclassing" the VFS. I haven't > seen anything that would require changes except ones that are > filename-related and easily refactorable into a short stack of local > functions plus wrappers for the methods that take a filename argument, > but I haven't looked as closely as Pavel, Richard, et al. presumably > have. Actually, wait a minute. Here I was assuming there'd be a mechanism similar to /dev/fd or /proc/self/fd on most of the Unixes supported, per what Roger mentioned upthread. But that may wind up broken if the sandbox denies open() unilaterally, too. Hmm. I shouldn't try to do this before coffee... ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
Quoth Roger Binns , on 2011-02-28 13:03:43 -0800: > On 02/28/2011 12:41 PM, Drake Wilson wrote: > > Back on the original topic, I would rather think a custom VFS sounds > > like the way to go; > > It is technically correct that will work. However it is a *lot* of > maintenance work. > > The custom VFS would be a duplicate of the platform one (lots of code) but > with only a very small number of lines changed. Perhaps, but DRH in the earlier thread "upstreaming Chromium patches for file handle passing support" wrote: | os_unix.c is very unlikely to change in ways that you care about. It has | not changed in years in ways that would effect you, except for the addition | of new methods to support WAL for the 3.6.23->3.7.0 transition. Such | changes are very uncommon. You can continue to pull in newer versions of | SQLite without having to modify your customized chromium VFS. If you are | ever in doubt about whether or not it is safe to continue to use an old VFS | based on an older os_unix.c, then simply ask. [...] | The only thing that might trip you up is a bug fix in os_unix.c. Those are | very, very uncommon. In fact, I can't call to mind the last time we had any | serious bug in os_unix.c. I'm inclined to believe this nullifies the main argument against forking os_unix.c in the absence of strong evidence to the contrary. Furthermore, another approach if the name<->FD thing is the only requirement would be to retrieve all the original VFS methods at init time (using sqlite3_vfs_find) and only alter a few of them when registering the new one, essentially "subclassing" the VFS. I haven't seen anything that would require changes except ones that are filename-related and easily refactorable into a short stack of local functions plus wrappers for the methods that take a filename argument, but I haven't looked as closely as Pavel, Richard, et al. presumably have. The main tricky case is where POSIX advisory locking requires bogusly keeping earlier file descriptors open, and that's the case if you open the same underlying database file multiple times. Are there comments from the Chromium folks about whether multiple descriptors for the same file might get passed down to the sandboxed process? Could that be reasonably prevented on the parent-link side somehow? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
Quoth Roger Binns , on 2011-02-28 12:20:04 -0800: > You could do that in Unix in many years. I shipped product in 1994 that > used it extensively (a master daemon would use accept() and then pass the > new file handles to child processes as appropriate). > > "I_SENDFD" is the magic needed. The topic is even covered in Stevens. SCM_RIGHTS is a modern (and, I think, the most usual) way, operating over a Unix-domain socket. The header constant, at least, is specified in POSIX:2001, though I can't find a specification of its functionality there. On every Unix I've personally observed have it, it transmits an array of FDs, which are duplicated into the receiving process (and the numbers in the message translated accordingly). http://www.opengroup.org/onlinepubs/95399/basedefs/sys/socket.h.html The corresponding call on Windows is DuplicateHandle, which can copy a handle from one process to another, and is AIUI generally paired with other IPC to transmit the new handle value. http://msdn.microsoft.com/en-us/library/bb202788.aspx Back on the original topic, I would rather think a custom VFS sounds like the way to go; one should be able to foo_snprintf the underlying platform-specific handle into an open-string and then get it back out inside the custom VFS, no? There can be no incompatibility with the existing expected uses of the open-string, because those will all be broken in the use case described anyway. If it's inconvenient, you could provide your own chromium_sqlite3_openhandle(handle, ...) function which would do the conversion and call sqlite3_open behind the scenes. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reason for random names for the master journal?
Quoth Johns Daniel , on 2011-02-25 16:21:54 -0600: > Thank you very much for this info, Dan! Very useful. > > >From your description, it sounds like this requirement only applies if > there are 3 or more databases. Is this an issue with 2 databases? Yes, master journals are needed whenever more than one database is used. However: > We have 2 databases. We open one and attach the other one to it at the > very beginning of our single (multi-threaded) database process. If you allocate a master journal name for only that process, and there is only one instance starting up at a time (ensured externally), and it always attaches both databases with the same names and in the same order before doing anything else, it seems like it should be okay; if there is a hot master journal, then it will be destroyed at startup time when both databases have their respective journals recovered. However, I'm not sure how the threading affects this if you have multiple connections. It seems like it should still be okay if they're all using the exact same two databases, since there should not be a case where they both want to collide on the master journal where they would not also be colliding on at least one database, but I'm not confident about that. I would try it and see what happens, but also be rather cautious about the design in such cases; it's hard to judge more accurately without knowing more about the application. > -- Johns ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Composite primary key with autoincrement
Quoth Maciej Lotkowski , on 2011-02-16 09:45:45 +0100: > It's not about the real use case. I understand, that having such a > primary key probably doesn't make much sense. I'm asking if it is > technically possible to create a table like > > create table foo ( > id integer not null autoincrement, > another_id integer not null, > primary key(id, another_id) > ) > > > without Error: near "autoincrement": syntax error. No. Note that you can create an PRIMARY KEY AUTOINCREMENT and have a separate compound UNIQUE constraint including that column, but that doesn't allow (A, B) and (A, C) to exist simultaneously. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Composite primary key with autoincrement
Quoth Maciej Lotkowski , on 2011-02-15 23:28:01 +0100: > Is it possible to have composite primary key where one column is > autoincrement? From what I found in docs it looks like it is possible > to have either simple primary key on one autoincrement column or > composite PK on few columns without autoincrement. Am I right? If no, > what is the syntax to create a table with primary key "id" and > "another_id" where "id" is autoincrement? When would it make sense to do this? What value would another_id take if you try to insert with both of them null? More generally, could you show some example inserts with what behavior you expect? I suspect what you're looking for is best done some other way. ---> Drake Wilson ___ 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
Quoth Yves Goergen , on 2011-02-06 11:40:17 +0100: > 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? I can't reproduce this with SQLite 3.7.4 from Debian GNU/Linux: SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table foo (x); sqlite> insert into foo (x) values (x'123412340012341234'); sqlite> select length(x) from foo; length(x) 9 Silly question: are you sure you're actually _storing_ all the data? Can you verify that you can get all the bytes out in any way at all? Information about the schema in use would be helpful, in general. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to get a reliable Integer from a Real?
Quoth Oliver Peters , on 2011-02-02 18:25:04 +: > I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but > sometimes > I get a strange result: Don't do that. Read up on the Web about the properties of binary floating-point representations. They are not decimal and if you have specific precision requirements you should carefully analyze how the floating-point numbers will impact them first. Most people doing currency calculations should be using integers and treating them as fixed-point values. (The others are the ones who already know enough to recognize exactly why their cases are different.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a bad design decision
Quoth Ian Hardingham , on 2011-02-01 15:10:43 +: [paragraphs reordered] > As there are hundreds of queries around my server codebase I would > prefer not to have to change every single one to use a numeric id for a > user rather than a string - but maybe this is the only real way of > optimising this properly? That might be preferable, but what form do these queries take? Are you talking about joins with other tables, or lookups based on incoming parameters, or... ? If they're joins, then you need to change the semantics of the columns in the other tables and update the join expressions accordingly (which might propagate into the application if it also relies on those name columns being directly accessible). If they're lookups, then the effects could propagate into the surrounding application directly. Note that it's possible to do this sort of thing incrementally without totally breaking the queries in the middle, since you almost definitely want to keep a UNIQUE index on the name anyway. Regardless of that: > My core users table has a user defined by a string which is their name. > This string is used to address many other tables relating to users. Not > only is the primary key a string (which I understand is bad enough), but > I also have to use LIKE rather than = because the high level language I > use is a bit eccentric about case-ing. That sounds suspicious. If it's only case distinctions and not whitespace or other cruft, and in particular if the names in the database can be in a normalized form and it's only the parameters that are problematic, you can normalize the parameter on the SQL side first: « name = LOWER(:foo) », for instance. Or « name = TRIM(LOWER(:foo)) ». That might get you better index usage on the name regardless of whether you change to a synthetic integer primary key. Of course you have to do the normalization the same way when writing the records to the DB in the first place. > Thanks, > Ian ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Quoth Tito Ciuro , on 2011-02-01 09:01:09 -0200: > I don't think so. The fileSystemRepresentation method should we used > when dealing with file-based paths. But not when dealing with SQLite paths, unless I'm mistaken about what fileSystemRepresentation does. sqlite3_open_v2 always takes UTF-8 and does any filesystem-specific encoding transformations internally. (It may still be that it does it incorrectly on some platforms, in which case that may be a bug.) > -- Tito ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
Quoth Simon Slavin , on 2011-01-20 00:55:18 +: > Agreed. I was imagining that one file was on an external hard disk > and the other was on an internal disk. If the power to the external > hard disk goes off, one file will get updated but the other won't. > I don't know enough about the inner workings of SQLite to know if it > can cope. I suspect that for the files themselves it's okay so long as fsync is actually a hard sync (and not just a reordering barrier), which it's supposed to be anyway. I don't believe SQLite can be making any assumptions regarding the reordering of unsynchronized writes between files on a single device; if fsync is a hard sync then any ordering semantics are already propagated through the application to the other device in turn. However, if either file ever gets created or deleted during normal operation, then you have the problem of needing to sync the directory, and that probably won't work because SQLite won't hit the correct directory for whichever file is actually somewhere else. Also, if one file is a symlink and it gets deleted and recreated, then it will be recreated on the wrong filesystem. This suggests that actually the main database should be the symlink if one goes that route, and it should be precreated as an empty file (and synchronized to storage) before any operations begin. Either way, it's an unsupported configuration and should only be used with extreme caution, especially if the SQLite component may later be replaced with the expectation of backwards compatibility. > Simon. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement not returning any result
Quoth "Navaneeth.K.N" , on 2011-01-16 22:00:42 +0530: > However, I am wondring why the function (sqlite3_bind_text) don't > respect NULL character in the string and stop reading when it find > one? Because you didn't tell it to. Text and blobs in an SQLite database are not C strings; they can contain NULs. You can request that the pointer be interpreted as a pointer to a C string by passing a negative length; in that case, there must be a NUL terminator, and the NUL determines the length. If you give it an explicit length, you're saying that's the exact length in bytes, and any NUL characters within that number of bytes will be included in the string. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement not returning any result
Quoth "Navaneeth.K.N" , on 2011-01-16 21:31:42 +0530: > rc = sqlite3_bind_text(stmt, 2, tok->pattern, VARNAM_SYMBOL_MAX, > NULL); /* debugged and tok->pattern doesn't have any extra > characters. strlen(tok->pattern) return 1 */ ... and yet you're passing a length of VARNAM_SYMBOL_MAX instead, which I'm guessing is not 1. Pass the real length of the string (not the size of the buffer), or -1 to treat it as a NUL-terminated C string. Otherwise you're grabbing extra bogus bytes. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
Quoth Nicolas Williams , on 2011-01-07 19:12:13 -0600: > But the real problem is that my triggers will just recurse infinitely, > since I need both, AFTER INSERT and AFTER UPDATE triggers. The AFTER > INSERT trigger will trigger the AFTER UPDATE trigger, and that one will > trigger itself, recursing ad naseum. This [obviously] happens in the > case of UPDATEs too. > > I need a way to break this recursion. > > One idea I'm considering is to have two columns where I have one: [...] > Another idea is that I could use temp triggers and temp tables instead, [...] Pardon me if this is too awkward, but could you not use a trivial view (AS SELECT * FROM ...) and then make INSTEAD OF INSERT/UPDATE/DELETE triggers on that? The application could use the view exclusively; the physical table would be in the background, and the view would act like a shadow table. (The DELETE case would just be transparent and key on the row IDs, I expect, if you don't need to do anything there.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Circular foreign keys
Quoth Yves Goergen , on 2011-01-03 13:01:17 +0100: > So I have foreign keys from message_revision to message and the other > way around. This obviously won't work because when defining the table > message, the table message_revision isn't known yet. What do you mean? Creating two tables with circular foreign keys works fine for me. I haven't tried your tables specifically; if you get an error for them, could you please describe it in detail along with how you tried to create the tables and any subsequent queries? sqlite> pragma foreign_keys=on; sqlite> create table a (id integer not null primary key, bx integer null references b (id)); sqlite> create table b (id integer not null primary key, ax integer null references a (id)); sqlite> insert into a (id) values(3); sqlite> insert into b (id) values(5); sqlite> update a set bx = -42 where id = 3; Error: foreign key constraint failed sqlite> update a set bx = 5 where id = 3; sqlite> update b set ax = 109 where id = 5; Error: foreign key constraint failed sqlite> insert into a (id, bx) values (109, 5); sqlite> update b set ax = 109 where id = 5; sqlite> insert into a (id, bx) values (110, -3); Error: foreign key constraint failed In particular, if you never create table B, subsequent operations on A may fail, but the creation succeeds and allows you to create B later. Also, dropping the tables may be awkward unless you turn foreign keys off first, but... ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] random() function does not keep value in subquery
Quoth "smiths...@essess.org.uk" , on 2010-12-29 20:11:34 +: > In this instance above clearly random() function has been called more than > once, > as character 1 and character 2 are not part of the 'l2' string being analyzed. Confirmed behavior in SQLite 3.7.4 from Debian unstable. (I'm not completely sure whether it's a bug or not because I'd have to look at the definition of subselect sources more first.) Here's a minimal pair: sqlite> select a, a+1 from (select random() & 65535 as a); a a+1 39692 39693 sqlite> select a, a+1 from (select random() & 65535 as a from (select 'foo' as x)); a a+1 32219 10986 It looks like the latter makes the query optimizer handle the impure/nondeterministic function call differently: sqlite> explain select a, a+1 from (select random() & 65535 as a); addropcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 OpenEphemeral 0 1 0 00 2 Goto0 16 0 00 3 Function0 0 2 random(0) 00 4 Integer 65535 3 0 00 5 BitAnd 3 2 1 00 6 MakeRecord 1 1 3 00 7 NewRowid0 2 0 00 8 Insert 0 3 2 08 9 Rewind 0 15 0 00 10 Column 0 0 4 00 11 Integer 1 3 0 00 12 Add 3 4 5 00 13 ResultRow 4 2 0 00 14 Next0 10 0 01 15 Halt0 0 0 00 16 Goto0 3 0 00 sqlite> explain select a, a+1 from (select random() & 65535 as a from (select 'foo' as x)); addropcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 OpenEphemeral 1 1 0 00 2 Goto0 19 0 00 3 String8 0 1 0 foo 00 4 MakeRecord 1 1 2 00 5 NewRowid1 3 0 00 6 Insert 1 2 3 08 7 Rewind 1 18 0 00 8 Function0 0 2 random(0) 00 9 Integer 65535 3 0 00 10 BitAnd 3 2 4 00 11 Function0 0 2 random(0) 00 12 Integer 65535 6 0 00 13 BitAnd 6 2 3 00 14 Integer 1 6 0 00 15 Add 6 3 5 00 16 ResultRow 4 2 0 00 17 Next1 8 0 01 18 Halt0 0 0 00 19 Goto0 3 0 00 ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last_insert_rowid() question
Quoth Scott A Mintz , on 2010-12-27 14:41:23 -0500: > I know that I can use sqlite3_last_insert_rowid() to retrieve the value. > However, in a multi-threaded environment, it's possible for another thread > to do an INSERT before I get the first result. That'll only affect the last_insert_rowid if the other thread is using the same connection (and not just the same database), which ideally you shouldn't be doing anyway. Is that the case here? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table 'unique' constraints behave unexpectedly
Quoth Simon Slavin , on 2010-12-27 00:17:37 +: > The distinction between column constraints and table constraints is > part of the spec of SQL. Had you read the SQL spec first, you > wouldn't have started this thread. But nobody expects you to read > the SQL spec before using SQL. Except that in the syntax diagrams there are clearly-marked divisions labeled "column-constraint" and "table-constraint", and the first few sentences of the "Column Definitions" section describe the difference in basic terms: | a CREATE TABLE includes one or more column definitions, optionally | followed by a list of table constraints. Each column definition | consists of the name of the column, optionally followed by the | declared type of the column, then one or more optional column | constraints. The terms also often link back to the syntax diagrams. So it may have come from somewhere else (I don't recall reading the SQL specification personally), but the terms are certainly repeated in the SQLite doc. (I didn't start the thread either, FWIW, in case that was the specific "you" rather than the impersonal "you".) > Simon. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table 'unique' constraints behave unexpectedly
Quoth Simon Slavin , on 2010-12-26 23:49:50 +: > On the other hand, almost nobody who uses SQL has read the > specification document for SQL. And frankly none of them should > have to: it's easier to understand SQL than the specification for > SQL. So using a technical term that exists in the specification may > be useful for the SQLite developers, but may mean nothing to the > casual SQL user reading the SQLite documentation. Sorry, I don't quite understand here. To which term are you referring exactly? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table 'unique' constraints behave unexpectedly
Quoth Norman Gray , on 2010-12-26 22:02:39 +: > Aha: this is a key point. In order to successfully parse the > paragraph, the reader has to be aware that a column constraint and a > table constraint are importantly different things, even though they > look very similar to each other, to someone not steeped in SQL. I think this is part of the disconnect. I don't get the feeling that the SQLite documentation is intended for people who have little or no SQL background, but rather as a reference for those who are already familiar with SQL relational databases, but not SQLite particularly. So while the full syntax and semantics are provided so that one doesn't have to constantly cross-reference standards documents or such and so that important variations in the language handling can be called out more easily, I'd rather the manual not try to emphasize common elements such as the distinction between column and table constraint syntax. A general SQL tutorial or quick reference should be used for that. Note that the two paragraphs quoted refer to both the column and table forms of UNIQUE and PRIMARY KEY, and they are isomorphic to each other in that each has both forms, so the second paragraph implies repeating both pieces of information from the first. So there is no "different thought in between" exactly. Obviously this is easier for someone who already understands the base syntax well enough, but see above, &c. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table 'unique' constraints behave unexpectedly
Quoth Igor Tandetnik , on 2010-12-26 14:39:15 -0500: [quoting the documentation] > Each table in SQLite may have at most one PRIMARY KEY. If the > keywords PRIMARY KEY are added to a column definition, then the > primary key for the table consists of that single column. Or, if a > PRIMARY KEY clause is specified as a table-constraint, then the > primary key of the table consists of the list of columns specified > as part of the PRIMARY KEY clause. > > A UNIQUE constraint is similar to a PRIMARY KEY constraint, except > that a single table may have any number of UNIQUE constraints. Oh, _there_ it is. So it is written explicitly, just a little bit compressed. I was foolish and didn't link the latter paragraph to the former when doing textual search. Hmm. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table 'unique' constraints behave unexpectedly
Quoth Norman Gray , on 2010-12-26 19:11:02 +: > > A UNIQUE specifier on a column creates one UNIQUE constraint on > > exactly that column. UNIQUE specifiers on multiple columns are > > unrelated. You may specify a multi-column UNIQUE constraint by > > declaring it separately (and not as part of any column specification). > > Thanks for that. That's what I eventually worked out (as I noted). Hmm! There's something ironic about that in that it wasn't entirely clear to me that you'd understood it. :-) Note that there is no semantic distinction that completely separates "table constraints" from "column constraints" in the case of UNIQUE. That is, unless I am very much mistaken, CREATE TABLE t (a ... UNIQUE) and CREATE TABLE t (a ..., UNIQUE (a)) are exactly equivalent and the former is an abbreviation for the latter. The text in the CREATE TABLE page that discusses this says, in the section on column definitions: | The other constraints - NOT NULL, CHECK, UNIQUE, PRIMARY KEY and | FOREIGN KEY constraints - impose restrictions on the tables data, and | are are described under SQL Data Constraints below. It seems pretty clear to me that placing one of these in a column definition creates a constraint on only that column, just like each of the other column constraint specifiers, but I agree that it could be more explicitly written out. (Especially since CHECK is actually a special case; I think the implicit equivalence with table constraint ignores the column name entirely and it's more a semantic convenience for someone reading the SQL.) (Backseat authoring: I would tend to write the definitions out as approximate reductions to CHECK, CREATE UNIQUE INDEX, and/or FOREIGN KEY, but I don't have the resources to submit a full proposal right now...) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table 'unique' constraints behave unexpectedly
Quoth Simon Slavin , on 2010-12-26 18:28:28 +: > ... and to do that you create an index on that tuple, and require the index > to enforce uniqueness: > > CREATE UNIQUE INDEX tab ON t (a,b) If you like. I was referring to CREATE TABLE t (a, b, UNIQUE (a, b)). ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table 'unique' constraints behave unexpectedly
Quoth Norman Gray , on 2010-12-26 18:14:23 +: > The phrase "For each UNIQUE constraint on the table" appears to be > redundant; if it's not, then that might need to be adjusted. A UNIQUE specifier on a column creates one UNIQUE constraint on exactly that column. UNIQUE specifiers on multiple columns are unrelated. You may specify a multi-column UNIQUE constraint by declaring it separately (and not as part of any column specification). ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update not completely commit(ing)
Quoth Joe Bennett , on 2010-12-24 18:28:18 -0600: > Have a question regarding a particular issue I am dealing with... I > have a database in which I create a pivot table to get a no dupe list > using a 'select distinct'. I then take that list into Python and then > iterate over it to look up data in another table to add in the > latitude and longitude for each entry. Each entry could have over a > thousand 'dupes' that I am trying to update with the lat/lon... > Anyway, what I have noticed is that when I run the following Python > 'SQLite command' sometimes all the 'dupes' get updates and sometimes > some do and some don't as well as sometimes none get updated... I am > able to replicate this with the same SQLite command in SQLite Manager: > > update_data = 'update matrix set %s = %f, %s = %f where %s = "%s"' % > (A_B + '_Lat', Lat_Site, A_B + '_Lon',Lon_Site, A_B, Site[0]) This is pretty hard to determine from just that statement. Actually showing us your schema would help, and some example rows that you expect to be updated by a particular query (after all the substitutions), the results that you get instead, and how you got the results back out of the database to check (including full SELECT query if relevant). But first things first. *cues the instrumental accompaniment* o/` Oompa loompa doopity doo I've got another puzzle for you Oompa loompa floopity fliss If you are wise you're watching for this What do you get writing SQL strings Attempting to earn both your lexical wings Could be a typo that doubled your quotes Or maybe you misread the notes In the FAQ list... http://sqlite.org/faq.html#q24 Oompa loompa bloopity blurn SQL syntax is easy to learn It might help your queries work too Like the oompa loompa oompa Oompa loompa doopity do o/` ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rollback transaction if error
Quoth BareFeetWare , on 2010-12-23 15:10:30 +1100: > Is there a way to do this in pure SQL, without my application code > having to check for errors along the way and then interrogate the > SQL to look for a "commit" type line and replace it? This seems > pretty error prone and convoluted. Why would you introspect the SQL rather than generating the beginning and end of the transaction from a different part of the application code to each statement inside? Begin transaction, then start executing statements from a list; if any of them fail, stop executing further statements and roll back, otherwise commit at the end. Don't include the begin/end in the list. Does that not work for you? > Thanks, > Tom > BareFeetWare ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows performance problems associated with malloc()
Quoth Richard Hipp , on 2010-12-17 15:12:58 -0500: > Can somebody with windows-foo please explain to me what I need to do to > vs2010 so that it generates exe file that doe not depend on non-standard > DLLs? AFAIK, the platform-approved solution is to distribute the C runtime library with your application. If you're using Visual Studio, that's from the Visual Studio SDK Redistributable Components packages. This has resulted in a certain outcry on the Web of people trying to figure out how to link with the now-Windows-internal MSVCRT library directly. AIUI it's gotten harder over time. It's not really a supported approach, but articles like [1] insist on figuring out how to do it anyway; you can likely find similar topics elsewhere. Also see [2] from MSDN, particularly section "What is the difference between msvcrt.dll and msvcr100.dll?". [1] http://kobyk.wordpress.com/2007/07/20/dynamically-linking-with-msvcrtdll-using-visual-c-2005/ [2] http://msdn.microsoft.com/en-us/library/abx4dbyh.aspx ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting data using a compound SELECT
Quoth Igor Tandetnik , on 2010-12-12 10:30:24 -0500: > Make it > > INSERT INTO "cast" (titleID, castID) > VALUES > ((SELECT titleID FROM titles WHERE title = 'Alien'), > (SELECT artistID FROM artists WHERE lastname = 'Weaver')); > > Note the added VALUES keyword. Oh yes. D'oh! I think I accidentally hit kill-word before sending; sorry about that. (The other response about the table definitions is useful too.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting data using a compound SELECT
Quoth George Roberge , on 2010-12-12 09:49:13 -0500: > I get into trouble when I attempt to add the extra field: insert into > cast (titleID, castID) select titleID from titles where title='Alien' > select artistID from artists where lastname='Weaver'; There's two INSERT syntaxes. One takes only a SELECT and inserts all the rows from it, and one takes a list of expressions (which might include subquery SELECTs) and inserts one row with the resulting values. Expression INSERT is clearer in this case, and requires parentheses around the entire expr list and commas to separate the exprs. Then, a subquery requires parentheses to separate it from its surroundings. So: INSERT INTO "cast" (titleID, castID) ((SELECT titleID FROM titles WHERE title = 'Alien'), (SELECT artistID FROM artists WHERE lastname = 'Weaver')); "cast" can also be an SQL keyword, so it's better to quote it as an identifier. The keywords don't need to be in all-caps, but I tend to prefer that for stylistic reasons. I'm curious why you're doing this type of query in the first place, though, especially since you mention that your interactive experience is mostly with Access. Having additional context might allow more useful suggestions beyond purely syntactic issues. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] need help on escaping the ']' character in the identifier
Quoth Philip Graham Willoughby , on 2010-12-07 10:57:45 +: > Use the right quotes, single not double; this works for me: > > create table 'test]' (no integer); Yagh! Please don't call those the 'right' quotes in this case. Quoth http://sqlite.org/lang_keywords.html: | For resilience when confronted with historical SQL statements, | SQLite will sometimes bend the quoting rules above: | | * If a keyword in single quotes (ex: 'key' or 'glob') is used in a | context where an identifier is allowed but where a string | literal is not allowed, then the token is understood to be an | identifier instead of a string literal. | | * If a keyword in double quotes (ex: "key" or "glob") is used in a | context where it cannot be resolved to an identifier but where a | string literal is allowed, then the token is understood to be a | string literal instead of an identifier. | | Programmers are cautioned not to use the two exceptions described in | the previous bullets. We emphasize that they exist only so that old | and ill-formed SQL statements will run correctly. Future versions of | SQLite might change to raise errors instead of accepting the malformed | statements covered by the exceptions above. (I suspect the real answer is "don't do that", but I'm not entirely confident.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing multiple rows at once via a select statement
Quoth Jonathan Haws , on 2010-12-06 22:51:16 +:
> As an argument to the callback, I pass the address of the array.
> However, I cannot change that address and have it persist through to
> the next call for the next row.
This isn't an SQLite problem; it's a C problem. You need to make a
C-style closure: function plus pointer to structure of persistent
data. Define:
struct write_closure {
mystructure *next;
};
or whatever, then put a struct write_closure on the stack and pass a
pointer to that. Then you can mutate the members of the closure
structure (in this case, have each callback invocation increment the
next-pointer).
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Degrees of separation
Quoth Dariusz Matkowski , on 2010-12-03 18:46:20 -0500: > Phobic What? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Degrees of separation
Quoth "Black, Michael (IS)" , on 2010-12-03 17:07:23
-0600:
> INSERT INTO "know" VALUES('a','b');
> INSERT INTO "know" VALUES('a','c');
> INSERT INTO "know" VALUES('a','d');
> INSERT INTO "know" VALUES('aa','b');
> INSERT INTO "know" VALUES('b','bb');
> INSERT INTO "know" VALUES('b','cc');
> INSERT INTO "know" VALUES('b','dd');
> INSERT INTO "know" VALUES('c','ee');
> INSERT INTO "know" VALUES('c','ff');
[...]
> What's missing is the a|aa|b or a|b|aa relationship
You're talking about a symmetrical binary relation, I gather. So that
means that R(a, b) = R(b, a) for all a, b---but a table doesn't know
that.
> It works if 'aa','b' is inserted as 'b','aa' -- but I'd like to not
> depend on the ordering -- or maintain alphabetical order.
This feels like a constraint out of nowhere. Why not?
Anyway, neither of those will work directly. An obvious approach
would be to use two rows for any non-reflexive entry in the relation,
which is a small amount of application logic. Another would be to
rewrite the query to union the two directions together, then probably
always insert non-reflexive entries in lexicographical order for
consistency (to create an invariant of one row per pairing). The
latter might be most easily done with a view of « SELECT a, b FROM t
UNION SELECT b, a FROM t » but I'm not sure how efficient it would be.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE transactions failing with multiple threads
Quoth cricketfan , on 2010-11-30 12:11:52 -0800: > Drake, I am using SQLITE in threadsafe mode. Transaction inside another > transaction isnt that equivalent of nested transactions? Should that be > allowed? SQLite has named savepoints, but not nested BEGIN transactions. It's hard to tell what exactly you're doing from the description, such as why you're doing these updates with two threads to start with, so it's hard to give good advice. Perhaps you could show some example code? Which threading mode do you mean? Serialized or multithreaded? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE transactions failing with multiple threads
Quoth cricketfan , on 2010-11-30 07:49:36 -0800: > Also not that both threads are > using the same handle passed by main. No, don't do that. Using the same handle in two threads concurrently can break depending on the SQLite threading mode, and will gain you no parallelism in the modes where it works. Aside from that, transaction state is bound to a handle; you're starting a transaction and then trying to start another one inside it. Open two handles instead. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question
Quoth Jason Dixon , on 2010-11-27 15:31:37 -0600: > I was curious if there is a way to find out the cell number that a > particular SMS file is from once I have identified the file in the backup > folder on my computer. This doesn't have anything directly to do with SQLite. SQLite is only a database engine that is used by many applications to store different types of data. You might go search for help related to the specific handset software in use instead. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY
Quoth luuk34 , on 2010-11-26 12:49:53 +0100: > The extra column seems to work, > but i thought this should work too? I would imagine so, at first glance. > But the ORDER is wrong... How? The example you provided seems properly sorted. > sqlite> SELECT a,b FROM ( > ...> SELECT ID a, Price b FROM OrderTest WHERE Price < 200 > ...> UNION > ...> SELECT ID a, Price b FROM OrderTest WHERE Price > 500 > ...> ) > ...> ORDER BY a IS 0, b; > a|b > 3|0.0 > 4|25.0 > 1|50.0 > 2|75.0 > 5|100.0 > 7|1000.0 > 8|1.0 > sqlite> ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY
Quoth Waldemar Derr , on 2010-11-26 12:24:27 +0100: > --Don't working: (Error: 1st ORDER BY term does not match any column in the > result set.) > > SELECT * FROM OrderTest WHERE Price < 200 > UNION > SELECT * FROM OrderTest WHERE Price > 500 > ORDER BY Price IS 0, Price; >From http://sqlite.org/lang_select.html: | Otherwise, if the ORDER BY expression is any other expression, it is | evaluated and the the returned value used to order the output rows. If | the SELECT statement is a simple SELECT, then an ORDER BY may contain | any arbitrary expressions. However, if the SELECT is a compound | SELECT, then ORDER BY expressions that are not aliases to output | columns must be exactly the same as an expression used as an output | column. > Is this a bug? In MySQL it works as expected. Is there a workaround? Add another output column with the expression you want, give it a name, and ORDER BY that name. My guess is that this is because the multiple output column specification parts of a compound SELECT may result in different intrepretations of an arbitrary expr in that position, and it's not clear how any kind of inwards propagation would work without yielding surprising results. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performing multiple actions on a query
Quoth Ian Petts , on 2010-11-23 19:20:05 +1100: > I know I can run the query again with a DELETE command, but what if > the data has changed in between queries? Not a problem if you do both of them in the same transaction, AFAIK. Surround both statements with a BEGIN/COMMIT pair. If the condition is complicated enough and you want to save recomputing it, you can create a temporary table to hold the _rowid_ values from the original and then use WHERE _rowid_ IN (SELECT ...) from the temporary table to identify the rows to be moved. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collating sequences and performance
Quoth "Duquette, William H (316H)" , on 2010-11-18 14:08:10 -0800: > It seems to me that it shouldn't be necessary for SQLite to evaluate > FOO's comparison function when doing queries on mytable; the > collation order should be implicit in the mykey column's index. Is > this in fact the case? When doing which queries? How do you propose to look up a key value in the index without using the collation function? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] change sqlite table column type
Quoth lizhe , on 2010-11-15 10:59:40 +0800: > Dear Sir: > I have a table type is blob,I think update integer type, > How to solve my trouble? "alter" SQL? No. If you really need that you'll need to recreate the table. But manifest typing means you probably don't need it as much as you think. http://sqlite.org/lang_altertable.html shows that modifying existing column types in-place is not available. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with query
Quoth Jeff Archer , on 2010-11-13 11:20:51 -0800: > And I can get the number of unique Y offsets in a pattern like so: > > SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE > PatternID > = 1); > > Is it possible to have a single query that will generate a row for each > PattenID, COUNT(Offset_Y) combination? Does SELECT PatternID, COUNT(DISTINCT Offset_Y) FROM Tiles GROUP BY PatternID do what you're looking for? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lua-in-SQLite (was: Question about SQLite features.)
Quoth Petite Abeille , on 2010-11-11 21:05:15 +0100: > Or perhaps SQLite should embed Lua [1] as its powerful, fast, lightweight, > scripting language and be done with it :) Interestingly enough, there's problems with doing that with stock Lua: SQLite insists on having both 64-bit integers and doubles, and stock Lua only provides the latter, so (for instance) representing rowids properly becomes a pain. Recompiling to use integers instead of floats is easy, but having both is nontrivial, though there exist patches such as LNUM that will give you a variant numeric type. I'd also be cautious about possible platforms where isolating the resultant symbols from a host program that might be using an incompatible Lua would be difficult. Being a library results in different constraints on dependencies than for an out-of-process database engine, unfortunately. These aren't unsolvable, but it's a little harder than it might look. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?
Quoth "Jay A. Kreibich" , on 2010-11-10 18:43:06 -0600: > > The observed useful behavior is to have such a reference return the > > value from the first row in each group, > > I haven't verified this since 3.6.23.1, but in that version the > *last* row is the one that is returned. Hmm. Apparently I misremembered, then. > ORDER BY is applied after the GROUP BY and should not > have any meaning to the rows within a group. Any change > is likely a side-effect. It looks like you're right, and the resulting row selected is only arbitrary (though often the one with the largest rowid). This suggests that unless I'm misunderstanding the comparison, comparing SQLite's behavior of permitting this type of SELECT with Postgres's DISTINCT ON (as an earlier post in this thread did) is misleading, since DISTINCT ON is guaranteed to be semantically after ORDER BY processing and therefore allows controlling which row from a group is selected, if one is careful. Thanks for the corrections. >-j ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?
Quoth Alexey Pechnikov , on 2010-10-20 11:32:04 +0400: > This is just one replacement for "distinct on" clause, as example. And you > can use any sort order for non-aggregate values in your group so some > queries are more simple than equal "distinct on" form in other DBMS > (PostgreSQL, > etc.). The feature is extremely useful for many applications. So here's the followup million-dollar question, then: is this behavior actually guaranteed? I can't find any reference to how exactly a reference to a non-aggregate result from outside a GROUP BY is interpreted semantically in the SQLite documentation, and nor do I think it's specified in SQL proper, especially since other engines raise errors in this case. The observed useful behavior is to have such a reference return the value from the first row in each group, and then to allow control of within-group ordering using ORDER BY. But is this part of the public interface, or is it an oddity that may change in future revisions? Hipp's response upthread seems to indicate the former, but I'd rather be sure. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about manifest typing/data affinity
Quoth Tito Ciuro , on 2010-11-06 20:35:10 -0300: > "None: A column with a none affinity has no preference over storage > class. Each value is stored as the type provided, with no attempt to > convert anything." Note that type affinities are not usually specified as column types directly. Instead, an SQL column type is used. In fact, the column type "NONE" will be detected as NUMERIC affinity, per the rules in the documentation. I would use a blank type to declare a column of varying type; that would give the NONE affinity you desire. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem dumping a single line ot UTF8 text into a table (likely a n00b problem)
Quoth "J. Bobby Lopez" , on 2010-10-28 11:48:12 -0400:
> And here it is again when less (which doesn't translate the UTF8 text):
> t...@y^@p...@e^@ ^@ ^@ ^@ ^@ ^@ ^...@t^@i...@m^@e^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@
> ^@ ^@ ^@
> ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^...@u^@s...@e^@r^@ ^@ ^@ ^@ ^@ ^@ ^@
> ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@
> ^...@d^@e...@s^@c...@r^@i...@p^@t...@i^@o...@n^@
> ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^@ ^...@^m^@
That's little-endian UTF-16.
> Here is how I'm trying to insert the line into the database (perl):
>
> $insert_sql = "insert into data (filename, line) values (" .
> $dbh->quote($file) . "," . $dbh->quote($line) . ")";
> $dbh->do( $insert_sql );
Don't do that. Look up how to use placeholders in the DBI manual.
> Another think that crossed my mind is that maybe I haven't set up the
> database properly to accept UTF8 or UTF16 data, but I figured this was a
> default in SQLite3.
You have to pick one when you create the database, usually UTF-8. If
you want UTF-16 use « PRAGMA encoding = 'UTF-16' » (or 'UTF-16le' or
'UTF-16be') when you create the database.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN
Quoth Mathieu Schroeter , on 2010-10-20 15:41:51 +0200: > Thanks for your interesting answer. > > The first plan looks the best but it does not explain the ORDER BY. > If I could done this job with pencil and paper, my way will be: > > a) enumerate all entries in lol > b) for each entry look up a corresponding row in tmp using its > primary key > c) the loop over lol is complete, I see that tmp.data uses > `COLLATE foobar` then I look if an index exists. > d) Yes then I use idx_foobar instead of many calls on the > comparison function. In step (d), you're doing what? Sorting the resulting rows? How exactly would you use the index for that? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key on different database - possible?
Quoth Frank Millman , on 2010-10-20 11:47:06 +0200: > Ok, thanks. > > Is there any chance of it being considered for a future release? Search http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq for "foreign key". > Frank ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DB access privilege problem...
Quoth forforum , on 2010-10-19 22:32:28 -0700: > I am using sqlite DB and am creating my DB in USB drive(this is my > application requirement), My problem is that. when am creating my DB as a > admin in 1 system and taking the same USB in 2nd system who is having > limited rights, then 2nd system user is getting Read only Database > exception, > So i want to ask is there any access rights for Sqlite DB, so that where > ever i will carry my DB it should get update regardless of access rights.. This isn't an SQLite problem; this is an underlying platform problem with the mapping of filesystem metadata (such as security descriptors) on removable media. Do you have similar problems with other files? SQLite doesn't have any intra-file permissions; if the user can write to the file, they can write to the database, and if they can't, SQLite can't do anything about it. You might want to find a suitable forum for your class of operating system and ask them how to write your program to deal with removable media in the way that you would prefer. Note that an empty file is practically speaking a valid empty SQLite 3 database, so if you need to set characteristics of the file before doing the sqlite3_open(), you can open() or CreateFile() it or whatever beforehand. > Thanks in advance ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with aggregate query
Quoth Germán Herrera , on 2010-10-16 00:10:23 -0300: > As you may know, both MySQL and SQL Server engines would refuse to run > the last query, indicating an error because not all columns come from > aggregate functions and there is no "group by" clause.. > > Is this left on purpose?, can this behavior be switched? (already > searched in the Documentation, and in the list of pragmas and couldn't > find anything). I doubt it has to be "left on purpose"; in fact it's more the opposite. I would think it's more work to detect queries that use combined aggregate and non-aggregate results in ill-specified ways, depending on how one's query compiler is built, so it's just a matter of an extra feature that was never implemented because there was no need for it. Is there a reason you want this type of query to raise an error? Is it just a matter of a safety net, wanting to know when you're doing something that's not that well-defined? The query is semantically not very good, but there are many other kinds of meaningless queries that are valid SQL; it's not really SQLite's job to check that for you. (I don't know what the SQL92 standard has to say on this, FWIW.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] raising constraint violations when using SELECT DISTINCT with multiple tables
Quoth gc , on 2010-10-16 03:37:15 -0400:
> CREATE TABLE Code_Units("code", "unit", PRIMARY KEY ("code"));
> CREATE TABLE Base_Data_1 ("partner", "code", "unit", "value", PRIMARY KEY
> ("partner", "code"));
[...]
I might consider doing something like this (untested):
CREATE TEMPORARY TABLE Code_Units_staging
("code", "unit", PRIMARY KEY ("code", "unit"));
-- Note the difference in schema for the temporary table.
INSERT OR IGNORE INTO Code_Units_staging ("code", "unit")
SELECT DISTINCT "code", "unit" FROM Base_Data_1;
INSERT OR IGNORE INTO Code_Units_staging ("code", "unit")
SELECT DISTINCT "code", "unit" FROM Base_Data_2;
[...]
INSERT OR IGNORE INTO Code_Units_staging ("code", "unit")
SELECT DISTINCT "code", "unit" FROM Base_Data_N;
-- Now the rows with equal code/unit pairs have been merged, but
-- any with the same code and different units have not, and will
-- cause an error on this INSERT.
INSERT INTO Code_Units ("code", "unit")
SELECT "code", "unit" FROM Code_Units_staging;
(You don't strictly need the DISTINCT in this case; it might be faster
or slower though; I'd try it both ways if feasible.)
This is going to be very slow if errors are frequent, though, since it
won't catch them until you've iterated the entire set of data. On the
plus side, it does make it easier with some modification to detect
where the discrepancies were (add extra columns for source data set to
the staging table, say). How frequent are these invalid combinations
going to be, and how frequently does the Code_Units table need to be
updated?
Another approach would be to create a view and then use an INSTEAD OF
trigger on insertion into the view that ignores fully-identical rows,
raises an abort on same-code/different-unit rows, and does the insert
on new-code rows. Maybe something like (again, untested):
CREATE VIEW Code_Units_for_insert AS SELECT * FROM Code_Units;
CREATE TRIGGER slightly_different_insert
INSTEAD OF INSERT ON Code_Units
FOR EACH ROW BEGIN
INSERT OR IGNORE INTO Code_Units ("code", "unit")
VALUES (NEW."code", NEW."unit");
SELECT CASE WHEN (SELECT "unit" = NEW."unit" FROM Code_Units WHERE
"code" = NEW."code")
THEN 1 ELSE RAISE(ABORT, "Code associated with multiple
units")
END;
END;
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unsafe trigger functions (was: Registering a custom tokenizer per database rather than per connection)
Quoth Scott Hess , on 2010-10-12 10:33:54 -0700: > Since the tokenizer-registration code accepts an encoded pointer to a > vtable, it probably should be considered unsafe to expose to users. > > For Gears and Chrome, where SQLite is exposed to web developers, we > did manual tokenizer registration and disabled the SQL-based > registration code. This can be done using authorizers. Yes, it can. However, it seems like surprising behavior that opening an arbitrary database and writing to it can corrupt the host program (via a specially-crafted trigger) unless you apply a restrictive authorizer first. In particular, this makes writing to arbitrary databases from the SQLite shell unsafe without a lot of rigamarole, since I know of no way to even set an authorizer in that case; one would have to remember to select and examine all the triggers first. It may be that a better approach would be to be more restrictive about triggers by default, then allow the user to reset a separate "trigger authorizer" to something more permissive if it is desired. I think this would not break existing code so long as it did not restrict new functions by default, since calling these particular unsafe functions in triggers (I think only FTS3 registration falls into this category so far?) is already mostly useless. In fact, I think my thoughts on this are now well-formed enough to submit an actual ticket to this effect. Since the bug report page at http://www.sqlite.org/src/wiki?name=Bug+Reports says that posting to the list is the correct way to do this, please consider the above such a request. (I will look into a patch if I have time, though this is moderately unlikely.) Additional comments are naturally welcome. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Registering a custom tokenizer per database rather than per connection
Quoth Travis Orr , on 2010-10-12 08:17:38 -0700: > Drake Wilson said: > - However, it now occurs to me that it may be possible to use the > - fts3_tokenizer() function in a trigger, which is probably a bad thing > - when writing to untrusted databases. > > I suppose the only way to accomplish it would be to recompile sqlite3 > with my custom tokenizer in place. I think that may be more work that it > is worth. The idea of using a trigger is interesting, though I suspect > the performance hit from having to register the tokenizer on each > connection is minimal. Just to clarify, a trigger isn't useful for this in general, because you still have nowhere to get the function pointer from. Above I was worrying that the current SQLite code might be less safe than it could be as a result of allowing this in triggers by default. I wouldn't worry about the performance; the registration will likely be a minimal hit compared to setting up the connection in the first place. > Travis ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT DISTINCT and multi-column UNIQUE constrains
Quoth Joerg Sonnenberger , on 2010-10-09 12:20:26 +0200:
> Schema:
> CREATE TABLE symbol (file integer NOT NULL REFERENCES file (id), symbol
> varchar NOT NULL, revision varchar NOT NULL, branch boolean NOT NULL,
> UNIQUE (file, symbol));
>
> Query:
> SELECT DISTINCT symbol FROM symbol;
[...]
> What I expect to see is an index scan on the index of the UNIQUE constrain
> and picking the value without ever touch the table.
How would this happen? The index uses the columns in order, as far as
I know, so you'll have for instance ('file A', 'symbol 1'), then later
('file B', 'symbol 1') and so on, and you can't trivially get the
DISTINCT out of that without sorting in temporary storage.
Using UNIQUE (symbol, file) instead would seem the obvious solution.
Is there a reason you can't do that?
> Joerg
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Registering a custom tokenizer per database rather than per connection
Quoth Travis Orr , on 2010-10-08 13:37:25 -0700: > Is it possible to register a custom FTS3 tokenizer to be persistent in > the database so that it is available every time a connection is opened? Not really. How would this work? You open a database, and the function pointer for the tokenizer comes from... where? The physical pointer usually isn't guaranteed to be the same even for the same application running twice on the same machine. This would also have an impact on security: opening a database shouldn't allow it to access arbitrary C code from your program, and declaring which functions are allowed and which are not is just as much work as reregistering the tokenizer in the first place. However, it now occurs to me that it may be possible to use the fts3_tokenizer() function in a trigger, which is probably a bad thing when writing to untrusted databases. Hmm. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confitional IF in triggers
Quoth Russell A , on 2010-10-07 22:24:23 -0700: > This may be a really dumb question, but I've searched and can't find an > answer. > Do SQLite triggers support any conditional expressions, like IF, or is there > only the WHEN statement? If the latter, does that mean that multiple > conditions must be in separate triggers? Your answer is right in the docs, where the syntax diagrams at http://sqlite.org/lang_createtrigger.html will demonstrate that the body of a trigger is a sequence of UPDATE, INSERT, DELETE, and SELECT statements. There is no full procedural language. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting images (gif) in Blob field (with php)
Quoth Artur Reilin , on 2010-10-05 15:06:57 +0200: > What I'm currently using is get the file contents and then use > base64_encode to store them in database. But that just blows the size up. > I saw that the firefox database (places.sqlite) store the favicons > directly in database. I searched around and cannot found a doc how they do > this. > > Some days ago I found the following code: > > $image_handle = fopen($file,"rb"); > $image = addslashes(fread($image_handle,filesize($file))); Why are you using addslashes? This sounds like you're trying to embed the whole thing as an SQL literal. Don't do that. Use a parameter in the SQL, then bind it with type SQLITE3_BLOB, ideally using a column with NONE affinity (e.g., of type BLOB NULL) underneath. (The addslashes function is often a red flag in the general case.) See http://us2.php.net/manual/en/sqlite3stmt.bindvalue.php if you haven't already. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow operation
Quoth Ian Hardingham , on 2010-10-05 12:27:38 +0100: > CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL > UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date > TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT > DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT > '0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER > DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0, > noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '') Those *Record fields look like the sort of thing that will expand to include large blobs later on. If this is the case, possibly consider: - Moving the blobs into a separate table or tables, if they're really best represented as singular blobs. Frequently updating large blobs isn't going to give you very good performance, but keeping them in separate tables will help prevent them from impacting smaller updates. - Using separate tables and then actually storing the data in relational form, if it's suitably representable. This could result in much more efficient storage and access, because you'd be using the SQLite components in a more natural way. The presence of that \t suggests that you might be storing sequences of records in those fields to start with; those could well be separate rows in a suitable secondary table. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow operation
Quoth Ian Hardingham , on 2010-10-05 12:16:11 +0100: > Your query, > > UPDATE userTable SET playedInfIds = '' > > Still took two seconds actually... but significantly better than what I > was doing. You're doing this only once rather than once per row, right? On a table with around 3k rows, it seems a little odd that it would take that long, even if updating every row tends to be expensive in general. What does your schema look like, if I might ask? Is there significant concurrent access with that giant update? > Ian ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow operation
Quoth Ian Hardingham , on 2010-10-05 11:52:36 +0100:
> Hey guys. My apologies in advance if this is a slightly mundane question.
(Please don't start new threads by replying to random messages. The
resultant header information indicates falsely that your email is part
of the same thread.)
> I'm running this code from a scripting language bound to SQLite:
>
> %r = db.query("SELECT * FROM userTable", 0);
> %i = 0;
>
> db.query("BEGIN TRANSACTION", 0);
> while (%i < db.numRows(%r))
> {
> %username = db.getColumn(%r, name);
> db.query("UPDATE userTable SET playedInfIds='' WHERE name LIKE
> '?'", 0, %username);
> %i ++;
> }
> db.query("END TRANSACTION", 0);
Ah-heh?
A number of points come to mind fairly immediately:
- Don't keep a query from outside a transaction active inside it.
- Don't SELECT * when all you need is one column.
- You shouldn't have to iterate a result set by numerically iterating
until you hit the total number of rows, but I don't know what API
this is, so I don't know exactly how the replacement would look.
- This whole loop looks like it could be replaced with the single
query « UPDATE userTable SET playedInfIds = '' » because you're
just targeting all the rows, unless there's something unobviously
different that I've missed.
Right now you're doing a full table scan to get each name, then doing
another full table scan for each name to update each row with a
similar name. That's O(N^2) in the number of rows; with 3k rows, that
requires ~9M processing steps.
> Is there anything obvious I'm doing wrong? I know using LIKE is not
> ideal, but the scripting language does not guarantee case so it is
> necessary here.
Store the name in a canonical form (e.g., all lowercase) in the
database, then query based on that form. You can store the
non-canonical form next to it in a separate column if it's needed.
The fact that you are using LIKE suggests that 'ian' and 'Ian' should
be treated identically, but currently your primary key allows separate
rows to exist for each of those.
Also, PRIMARY KEY UNIQUE is redundant. A primary key is always
unique.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] disk IO error after windows resumes from sleep
Quoth Serena Lien , on 2010-10-05 11:46:18 +0100: > On a windows vista/win7 machine or a laptop which goes into sleep mode, when > it resumes and the application tries to open a database on a networked > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I don't > have a problem with this, if the OS has lost access to the network I can > imagine SQLITE_IOERR is quite valid. My question is, is there any way to > recover now from this error without forcing my application to exit and > restart? Any number of retries using sqlite3_open_v2 always continue to fail > with SQLITE_IOERR. > > It is possible the response will be "not sqlite's problem", but I would > appreciate any advice anyone has to give, I would say that unless SQLite is returning that error in unwarranted cases, this is really an application-level error recovery problem. What do you mean by "always continue to fail"? Is this the case even after you have verified that the desired file is accessible? Are you delaying retries at all? If the IOERR return code is truthfully signaling inability to access the file, then if this is an interactive application, you might signal the user to request a retry later. If it's a batch process, you might schedule a retry for later. If there's some alternative way of accessing the database or operating at reduced functionality without it, you might try that. It's hard to be more specific without knowing what kind of application is being developed. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query performance question
Quoth Drake Wilson , on 2010-10-05 03:24:01 -0700: > > My current task is to get the number of foods that belong to each > > group and have at least one weight data related to them. > > That suggests something like: > > SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count" > FROM ZFFFOODGROUP g > INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK >WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK) >GROUP BY g.Z_PK; Oh---actually I may have misinterpreted your semantics slightly. This will only find groups that have at least one such row. If you want to find groups with no such foods and get an explicit zero as a result, you would have to change that to a LEFT JOIN and then move the EXISTS somewhere else to ensure at least one row for the zero-result groups. I'd probably drop the WHERE, then replace the COUNT with SUM(EXISTS (SELECT ...)) because the EXISTS will return 1 for rows where the subselect returns non-empty, though there might be a better way to do that. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query performance question
Quoth Mail , on 2010-10-05 11:43:29 +0200: > SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP > = ? AND (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = > t1.ZFOOD) ) > ?) There should not need to be a DISTINCT when talking about a primary key. They will already be distinct by definition, no? (I'm assuming your primary keys will not be null, even though you don't have a NOT NULL constraint on the column.) Aside from that, if you want "at least one", EXISTS is the obvious operator to use rather than first requesting the count (and having to iterate all the rows even though only the first one is needed to demonstrate existence.) > My current task is to get the number of foods that belong to each > group and have at least one weight data related to them. That suggests something like: SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count" FROM ZFFFOODGROUP g INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK) GROUP BY g.Z_PK; (I think the WHERE EXISTS could be done with another join, but it would probably be more convoluted.) > When inserting 12 for ZFOODGROUP and 0 for count (I never check for > another count value here, it's always 0), I get a result of 761 > which takes 0.0591 seconds. As I have to fetch the counts of all > available groups, this query is sent in a loop which easily takes > several seconds to finish. Do the loop in the query itself using GROUP BY instead, if you can, rather than issuing several queries. My local, unrigorous measurements suggest that on my machine, with the provided example database, your original query takes 40 ms of CPU per run and the above takes 16 ms total; I imagine the use of GROUP BY and EXISTS and the lack of the extra DISTINCT are the primary factors, but I haven't checked thoroughly enough to say so confidently. I'm using SQLite 3.7.2 on Debian GNU/Linux sid AMD64. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
