Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

et 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 _

Re: [sqlite] Troubleshooting nested query performance issue

nks 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?

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?

ader 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. --

Re: [sqlite] Colons (was: RE: Groups and members)

Quoth Rob Richardson <rdrichard...@rad-con.com>, 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 --->

Re: [sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board

e 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 ]

emoved. > > 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

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-

Re: [sqlite] surprising behaviour: in-memory db allocs less than file db

sulting 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 ba

Re: [sqlite] MMIO and VFS Obfuscation

Quoth Drake Wilson <dr...@dasyatidae.net>, 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.

Re: [sqlite] MMIO and VFS Obfuscation

Quoth Drake Wilson <dr...@dasyatidae.net>, 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, thou

Re: [sqlite] MMIO and VFS Obfuscation

o 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

Re: [sqlite] SQLite version 3.7.17 schedule

;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

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

Quoth Nico Williams <n...@cryptonector.com>, 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 mail

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

n'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 <dr...@dasyatidae.net>, 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 S

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

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?

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

eed 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 mai

Re: [sqlite] Strange eviction from Linux page cache

ght 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-u

Re: [sqlite] about date question

g 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 th

Re: [sqlite] about date question

t 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

f 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 properl

Re: [sqlite] DELETE only deletes some records, not others

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 sql

[sqlite] Interesting behavior with rowid aliasing and default values

e, 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: a

Re: [sqlite] What is so slow on dropping tables?

m 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-u

Re: [sqlite] select * on empty virtual table

g 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)",

Re: [sqlite] how to reuse a prepared statement

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 ___ sq

Re: [sqlite] FTS snippet()

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

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

Re: [sqlite] Create table if not exists and insert seed data

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:

Re: [sqlite] In-memory database with persistent storage

esses 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

Re: [sqlite] strange UB detected

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 lis

Re: [sqlite] Adjusting strategy for ROWIDs

es, 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 _

Re: [sqlite] Adjusting strategy for ROWIDs

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

Re: [sqlite] adding fdopen to VFS?

Quoth Drake Wilson <dr...@begriffli.ch>, 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

Re: [sqlite] adding fdopen to VFS?

Quoth Roger Binns <rog...@rogerbinns.com>, 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

Re: [sqlite] adding fdopen to VFS?

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

Re: [sqlite] Reason for random names for the master journal?

er 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. >

Re: [sqlite] Composite primary key with autoincrement

ate 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

ake 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 sqlit

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

e 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?

ady 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

ou 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 __

Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases

ite3_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 m

Re: [sqlite] WAL on a separate filesystem?

ecially 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

g 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

.. 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 ___

Re: [sqlite] Circular foreign keys

e 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] Update not completely commit(ing)

ax 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

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

Re: [sqlite] Windows performance problems associated with malloc()

00.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:8

Re: [sqlite] Inserting data using a compound SELECT

> > 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 sql

Re: [sqlite] Inserting data using a compound SELECT

ve 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

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 lis

Re: [sqlite] Accessing multiple rows at once via a select statement

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 <dmatkow...@rim.com>, 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

ry 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 effic

Re: [sqlite] SQLITE transactions failing with multiple threads

IN 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? --

Re: [sqlite] SQLITE transactions failing with multiple threads

ill 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 mail

Re: [sqlite] Question

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 m

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

e> 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 >

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

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

/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. ---&g

Re: [sqlite] Custom collating sequences and performance

cit 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

Re: [sqlite] change sqlite table column type

s 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://sql

Re: [sqlite] Help with query

ible 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 mai

[sqlite] Lua-in-SQLite (was: Question about SQLite features.)

nfortunately. 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?

N 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

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

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

Re: [sqlite] Question about manifest typing/data affinity

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 affinit

Re: [sqlite] Problem dumping a single line ot UTF8 text into a table (likely a n00b problem)

ind 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 cre

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

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 <fr...@chagford.com>, 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". >

Re: [sqlite] DB access privilege problem...

base, 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.

Re: [sqlite] Problem with aggregate query

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

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)

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 natura

Re: [sqlite] Registering a custom tokenizer per database rather than per connection

Quoth Travis Orr <t...@ivl.com>, 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. > >

Re: [sqlite] SELECT DISTINCT and multi-column UNIQUE constrains

'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 tha

Re: [sqlite] Registering a custom tokenizer per database rather than per connection

abases. 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

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)

neral 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

e 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 t

Re: [sqlite] Slow operation

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? >

Re: [sqlite] Slow operation

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 __

Re: [sqlite] disk IO error after windows resumes from sleep

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 <dr...@begriffli.ch>, 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 &q

Re: [sqlite] query performance question

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 __

Re: [sqlite] When do I need SQLITE_THREADSAFE?

gardless of any of this. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite working with C++'s iostream

y files in both of the cases you mention, unless (in the latter case) you actually have per-block (or similar) crypto going on rather than purely streaming. (You do use seekp, but some underlying streams might not support it.) ---> Drake Wilson ___ sqlite-

[sqlite] Atomic commit assumptions regarding underlying writes

isk or "rewriting" sectors of a flash-based device, or similar information for filesystems that don't do in-place writes. Pointers would be appreciated. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Delete from A what is not in B

primary keys? A primary key declaration implicitly creates a unique index, yes. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Delete from A what is not in B

a.key = b.key) with a suitable composite equality in the subselect. > Is my data too big for SQLIte? is it really hanging or it could be taking 3 > days? Table A and (particularly) table B both have indices on those columns, right? ---> Drake Wilson _

Re: [sqlite] Trigger conditionally causing delete

riggers, which run only once when triggered by a statement that alters multiple rows. I prefer to use the explicit form, but omitting it would change nothing. The full CREATE TRIGGER syntax is of course part of the documentation, at http://sqlite.org/lang_createtrigger.html. ---> Drake Wilson

Re: [sqlite] Trigger conditionally causing delete

can use a WHEN clause: CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) BEGIN DELETE FROM summary WHERE key = OLD.key; END; ---> Drake Wilson ___ sqlite-users

Re: [sqlite] Tricky grouping query

to do this with SQL? Why not just do it in plain application logic? In the absence of more information, that would seem a more natural way to go about it. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accented characters and ODBC

choose which only at database creation time.) Similarly, make sure that you actually give it Unicode strings in the target encoding; there may be some autocorrection going on if you try to feed it Latin-1 characters, but I wouldn't rely on it. > Thanks, > Gr

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

ome these kinds of limitations in some cases. > Thanks again > > Lynton ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

writes. WAL mode would help with that, but it would increase the underlying complexity WRT filesystem and shared memory accesses. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

  1   2   >