Re: [sqlite] DeviceSQL

On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote: > That would be the Serialized Statement Extension, SSE. > The SSE provides the programmer with two new APIs: Would it be useful to generate human-readable VDBE "assemply"? Or, how do you develop parser changes that involve new

Re: [sqlite] DeviceSQL

On Fri, Dec 14, 2007 at 10:10:04AM -0600, Nicolas Williams wrote: > On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote: > > That would be the Serialized Statement Extension, SSE. > > The SSE provides the programmer with two new APIs: > > Would it be use

Re: [sqlite] DeviceSQL

On Fri, Dec 14, 2007 at 04:20:15PM +, [EMAIL PROTECTED] wrote: > The EXPLAIN option generates human-readable assembly. We > use EXPLAIN extensively when debugging and enhancing. Yes, but there's no assembler for EXPLAIN output, is there? Also, the only way to deal with EXPLAIN is to step

Re: [sqlite] DeviceSQL

On Fri, Dec 14, 2007 at 04:28:07PM -0600, John Stanton wrote: > Samuel R. Neff wrote: > >I'm not sure I see added value in incorporating procedural SQL in an > >embedded database like SQLite or DeviceSQL. Isn't the easily extensible > >mechanism that SQLite has for creating custom functions in

Re: [sqlite] Possible UNICODE LIKE, upper(), lower() function solution

I posted about this subject earlier too (in OpenSolaris we added Unicode tolower() and toupper() functions to SQLite 2.x). But the fact that SQLite 3.x already supports ICU disuaded me from pursuing the matter further. OpenSolaris has a light-weight Unicode API (licensed under the CDDL), much

Re: [sqlite] Using sqlite3 from multiple threads

On Wed, Jan 09, 2008 at 09:03:51PM +, [EMAIL PROTECTED] wrote: > In fact, the only company > I know of that makes use of shared libraries for SQLite is Apple. Solaris will be shipping SQLite 3.x as a shared library. > They can get away with this because

Re: [sqlite] Using sqlite3 from multiple threads

On Wed, Jan 09, 2008 at 10:06:30PM +, [EMAIL PROTECTED] wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > > Would you recommend that we not make SQLite 3.x in Solaris available to > > third parties? > > I think having a libsqlite3.so available is great. The

Re: [sqlite] Next Version of SQLite

On Sun, Jan 13, 2008 at 08:46:03PM -0600, Rick Langschultz wrote: > I was wondering what would constitute the creation of SQLite 4.0? IMO major implementation details changes are not necessarily a good rationale for bumping the major version number, not from a user's p.o.v. The authors might

Re: [sqlite] Next Version of SQLite

On Mon, Jan 14, 2008 at 10:55:42AM -0600, Fred Williams wrote: > >Which means that if the major version number changes, > > then it'll be for marketing purposes. > > Well seeing how SQLite is FREE, it does its own "marketing" with one > word, so to speak. Therefore I doubt

Re: [sqlite] Re: Re: Why are strings in hexadecimal notation always blobs?

On Sat, Jan 19, 2008 at 03:23:32AM +0700, Lothar Scholz wrote: > IT>Does this really make sense to you? > > Yes the only reason left for a BLOB would be a containing zero byte and > any illegal UTF8 sequence of bytes. Or wanting to avoid collations that are aware of, say, Unicode

Re: [sqlite] Foreign Constraint Triggers Across Attached Databases

On Thu, Jan 24, 2008 at 10:59:17AM -0500, Samuel R. Neff wrote: > Due to the restriction that triggers cannot span databases, I have my main > data tables, history tables, and the ActiveTransaction table all in the same > database. I'd really rather the history tables be in a separate database >

Re: [sqlite] Query problem

On Wed, Jan 23, 2008 at 10:24:53PM -0800, Scott Hess wrote: > Seems to me that GLOB is a poor substitute for REGEXP. At the shell If, as I suspect, many more users can enter simple globs than can enter simple regexps, then providing a GLOB operator and function in SQLite is very useful indeed.

Re: [sqlite] How to specify regular expression in a query?

On Mon, Jan 28, 2008 at 10:07:01AM +0100, Ralf Junker wrote: > Hello Bharath Booshan L, > > >>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; > > > >Will this query use index, if we had one, on filepath? > > No. It will do a full table scan. But it could, no? I suppose that to

Re: [sqlite] How to specify regular expression in a query?

On Mon, Jan 28, 2008 at 06:22:08PM +0100, Ralf Junker wrote: > I believe that this API would also ease implementations of Unicode > LIKE and GLOB. That's what I was thinking of. The Unicode extensions work by redefining the like, glob and regexp functions, and by adding collations. But surely

Re: [sqlite] Solaris make test compilation error

On Thu, Jan 31, 2008 at 02:32:45PM -0800, James Dennett wrote: > > > Undefined first referenced > > > symbol in file > > > sched_yield /var/tmp//cckDMcyL.o > > > ld: fatal: Symbol referencing errors. No output written to >

Re: [sqlite] Solaris bus error

I didn't get any core dumps in Solaris Nevada, but seven tests failed: Failures on these tests: vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2 Nico -- - To unsubscribe, send email to [EMAIL

Re: [sqlite] Solaris bus error

On Thu, Jan 31, 2008 at 04:38:27PM -0800, Ken wrote: > Solaris Nevada is an x86 system if I'm reading the Hardware > Compatibility List correctly. No, it's an unreleased version of Solaris (the basis for the next release after Solaris 10). It runs on x86 and SPARC, but I was trying this on my

Re: [sqlite] Mailing List Changes

On Tue, Feb 05, 2008 at 01:30:01AM +, [EMAIL PROTECTED] wrote: > And yet nearly everyone I know loaths that behavior. The > overwhelming majority of users prefer mailing list replies > to go back to the mailing list *only*. Users need to learn to do "list reply" or "reply all" :) Getting

Re: [sqlite] Solaris make test compilation error

BTW, in Solaris Nevada (build 44) sched_yield() has moved into libc. Which is why SQLite 3.5.5 builds without errors on Solaris Nevada. Auxiliary filters remain in librt so that linking with -lrt will work on all versions of Solaris. Nico -- ___

Re: [sqlite] SQLite Web Site

On Wed, Feb 06, 2008 at 02:46:31PM -0800, L. S. wrote: > O-o-o-okay! That explains it. > I do vaguely remember that issue being discussed, some time ago. > Well... I'm in Cali', and I'm out of luck on this Palm/Blazer. I can load the SQLite web page in Opera Mini on my phone...

Re: [sqlite] Why attach databases?

On Thu, Feb 07, 2008 at 06:17:19PM -0500, Samuel R. Neff wrote: > A blanket "stay away" is pretty extreme. I would rather be able to have True. One use case discussed before would be to archive history data from one DB to another, then reset the history tables in the first one.

Re: [sqlite] Why attach databases?

Given the restrictions on views and triggers (they cannot make reference to tables from more than one database) I'd say "stay away from ATTACH where possible" -- don't create new uses of it without good reason. ___ sqlite-users mailing list

Re: [sqlite] sqlite3_column_type

On Thu, Feb 14, 2008 at 02:42:16PM -0500, Igor Tandetnik wrote: > Michael Pitchford > <[EMAIL PROTECTED]> wrote: > > I'm having an issue with sqlite3_column_type in the current version > > of sqlite (3.5.6 at the time of this email). Unless I explicitly call > > sqlite3_step() on the statement,

Re: [sqlite] Using BLOBs in where fields

On Fri, Nov 03, 2006 at 11:39:47AM -0600, Gabriel Cook wrote: > So, is there any way to of thing with a BLOB? > > > WHERE substr(data, 1, 1) == x'bc' This works: WHERE substr(data, 1, 1) == CAST(X'...' AS TEXT); but only as long as X'...' has no NULs. And probably only as lon as data contains

Re: [sqlite] Music Files

On Mon, Nov 27, 2006 at 02:40:44PM -0600, John Stanton wrote: > You store them in the DB as a BLOB type, but save the data as a JPEG, > MP3, WAV or whatever it happens to be. The binary data resides as a DB > column and the same row may have other columns which could be text to > describe the

Re: [sqlite] Accommodating 'Insert' and 'Update'

On Tue, Nov 28, 2006 at 03:03:58PM -0600, Isaac Raway wrote: > Use an index on the table with your key values and call "INSERT OR > UPDATE INTO t(...) VALUES(...)" for all creation and update > operations. Unless you're dealing with a tremendous amount of data per > record this will be perfectly

Re: [sqlite] Query on database operations.

On Thu, Nov 30, 2006 at 08:03:00PM +0530, Kalyani Tummala wrote: > With indexes on every column(searched), the following is the heap size > for different database operations on a database with 100 records and 6 > tables with an avg of 10 to 15 fields each. You can probably get by quite well

Re: [sqlite] Re: sqlite_open

On Thu, Nov 30, 2006 at 10:52:55PM -0600, John Stanton wrote: > Sqlite has a carefully thought through minimalism. Feature creep would > detract from its function as a small footprint, embedded DB. If you > want different features there is nothing to stop you adding your own > library

Re: [sqlite] Preallocating fixed disk space for database ...

On Fri, Dec 01, 2006 at 08:35:24AM +0100, kamil wrote: > I want to preallocate disk space for database. I have only one table with ~1 > milion entries, each entry takes about 30 bytes. Entries are added/removed > but there is some maximum number of items, which can be put into the table > at

[sqlite] Batching functions

Suppose I want to add a user-defined function that may perform remote lookups. E.g., a function that maps user names, e-mail addresses, or what have you to internal ID forms (SIDs, POSIX UIDs, GUIDs, etc...) by asking a remote server to perform this mapping. Now suppose I wanted to do something

Re: [sqlite] Batching functions

On Sun, Dec 03, 2006 at 11:24:36PM -0600, John Stanton wrote: > How about running a daemon on your machine which gets the request from > your user defined function, does the lookup with a persistent connection > and asynchronously updates the row in the DB? It does not need to be a > daemon,

Re: [sqlite] Dealing with dates in the format yyyymmdd

On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote: > Still have the problem though how to compare dates in SQLite when the format > is the integer mmdd. Maybe I will need some custom SQLite function. What's difficult about comparing integers of the form mmdd? Comparing them is

Re: [sqlite] Re: Re: ip2long

On Tue, Dec 05, 2006 at 08:21:35PM +1100, Kevin Waterson wrote: > This one time, at band camp, "Trevor Talbot" <[EMAIL PROTECTED]> wrote: > > > > I'd store the IPs in the DB in integer form as Lloyd suggested, if > > range queries are your goal. Do conversion to and from display format > > in

Re: [sqlite] Re: Re: ip2long

On Tue, Dec 05, 2006 at 09:58:02AM -0700, Dennis Cote wrote: > select >case >when substr(ip, 2, 1) = '.' then -- one digit first quad >case >when substr(ip, 4, 1) = '.' then -- 1 digit second quad >case >when substr(ip, 6, 1) = '.' then -- 1 digit

Re: [sqlite] Re: Unicode Help

On Tue, Dec 05, 2006 at 06:53:28PM +0100, Marten Feldtmann wrote: > Perhaps it would be nice to change sqlite3 in that way, that (when columns > with storage class text) these columns are converted to the host platform > code page. But actually even in that situation you may have strings, which >

Re: [sqlite] a question about muticonnection

On Wed, Dec 06, 2006 at 10:04:42AM -0600, John Stanton wrote: > hongdong wrote: > >I just have a base question: > >assume user A and user B now both connection to a same database and both of > >them want to update a same record,but only one is allowed > >in this condition: > >A begin to browse

Re: [sqlite] Re: Unicode Help

On Wed, Dec 06, 2006 at 10:06:12AM -0600, John Stanton wrote: > Marten Feldtmann wrote: > >But Tcl is not part of SQLite (and this is good) - this is just an > >add-on. The idea with the > >additional functions are pretty good ! > > > How does Sqlite become Sqbloated? By function creep, one

Re: [sqlite] a question about muticonnection

On Wed, Dec 06, 2006 at 11:36:11AM -0600, John Stanton wrote: > I fully understood. It is an age old problem that has puzzled > generations of system designers. My first exposure was over thirty > years ago. The approach we discovered worked was to treat it as a > transaction in the logical

Re: [sqlite] a question about muticonnection

On Wed, Dec 06, 2006 at 12:51:29PM -0600, John Stanton wrote: > To fully handle the situation you need to know how many users have a > transaction pending and are pondering ordering the item. That requires > some form of journal or "committed" total. If you have three items and > there are

Re: [sqlite] Check for empty table

On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote: > Nothing wrong, but is it the fastest? count(*) doesn't read every record in the table. - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] Re: Check for empty table

On Wed, Dec 13, 2006 at 01:33:35PM -0500, Igor Tandetnik wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > >On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote: > >count(*) doesn't read every record in the table. > > Does too. Run EXPLAIN and see for you

Re: [sqlite] Re: File Syste

On Wed, Dec 13, 2006 at 05:03:50PM -0400, Cesar Rodas wrote: > On 13/12/06, John Stanton <[EMAIL PROTECTED]> wrote: > > > >It is hard to imagine why you would want to use Sqlite B-Tree access. > > I am developing a File System, and I'd like to use B+ Tree and not lost time > and CPU understanding

Re: [sqlite] delayed (batch) transactions

On Wed, Dec 20, 2006 at 02:01:12AM +, [EMAIL PROTECTED] wrote: > Laszlo Elteto <[EMAIL PROTECTED]> wrote: > > I DO need Durability, so I don't want to drop that. In fact, I need and want > > normal transactional updates - just not immediately flushed to disk. > > If the information is not

Re: [sqlite] delayed (batch) transactions

On Wed, Dec 20, 2006 at 01:22:06PM -0500, Laszlo Elteto wrote: > Nested transactions would solve my problem - but only if it worked across > different connections. As I said there are many transactions from various > clients, they may use multiple connections (eg. on a server). I think nested >

Re: [sqlite] delayed (batch) transactions

On Tue, Dec 26, 2006 at 09:36:42AM -0800, Ken wrote: > > Your question boils down to this: Can you speed up transactions > > by dropping the durable property - the D in ACID. Yes you > > can. Actually, most client/server database engines already > > do this for you without telling you. Very few

Re: [sqlite] sqlite performance, locking & threading

On Sat, Dec 30, 2006 at 03:34:01PM +, Emerson Clarke wrote: > Technically sqlite is not thread safe. [...] Solaris man pages describe APIs with requirements like SQLite's as "MT-Safe with exceptions" and the exceptions are listed in the man page. That's still MT-Safe, but the caller has to

Re: [sqlite] sqlite performance, locking & threading

On Tue, Jan 02, 2007 at 11:56:42PM +, Emerson Clarke wrote: > The single connection multiple thread alternative apparently has > problems with sqlite3_step being active on more than one thread at the > same moment, so cannot easily be used in a safe way. But it is by far > the fastest and

Re: [sqlite] sqlite performance, locking & threading

On Thu, Jan 04, 2007 at 12:50:01AM +, Emerson Clarke wrote: > My oppologies, your right that explanation had been given. OK. > But i didnt actually take it seriously, i guess i found it hard to > believe that it being the easier option was the only reason why this > limitation was in place.

Re: [sqlite] how would you allow users to re-order rows arbitrarily?

On Sat, Jan 06, 2007 at 09:53:39PM -0800, Sean Payne wrote: > Suppose gui-users wanted to drag and drop rows in a table so that > they could shuffle it anyway that they wanted so that the rows > maintained that order the next time they accessed the table. Can > this be done without

Re: [sqlite] attach in transaction

On Mon, Jan 08, 2007 at 03:42:47PM +, [EMAIL PROTECTED] wrote: > [EMAIL PROTECTED] wrote: > > Can someone tell me why attach cannot be called within transaction? > > I do not recall. > > Clearly a DETACH will not work inside a transaction if the > table being detached has been accessed or

Re: [sqlite] Sqlite design question

On Tue, Jan 09, 2007 at 01:28:21PM +0100, Florian Weimer wrote: > If your database isn't too large, and you aren't running on Windows, > you could make a copy of the database before updating it, so that > readers and the writer work on different databases. I wish ZFS would allow one to

Re: [sqlite] How to optimize a select that gets 17 rows from a 700k row DB (via perl using DBI)?

On Sat, Jan 13, 2007 at 12:57:43AM -0500, [EMAIL PROTECTED] wrote: > The SQLite database > is INSERTed into because I want to keep historical data. The rationale > for this is explained later. For your main application (finding the least

Re: [sqlite] Re: How to optimize a select that gets 17 rows from a 700k row DB (via perl using DBI)?

On Sat, Jan 13, 2007 at 06:35:20PM -0500, [EMAIL PROTECTED] wrote: > I guess that's the trick, to have the "current" or at least "recent" > database and then the historical one. As of now, the process of polling > the 17 machines takes about 40 seconds or so (when I first started running > the

Re: [sqlite] File db to memory db and back

On Fri, Jan 19, 2007 at 12:51:36AM +0100, Nemanja Corlija wrote: > On 1/19/07, Nicolas Williams <[EMAIL PROTECTED]> wrote: > >Why don't you use a trigger to duplicate INSERTs into the memory db to > >the disk db? > > > Nicolas, thanks for the idea. Didn't really

Re: [sqlite] Pager Question... Open Source Project

On Mon, Jan 22, 2007 at 09:59:56AM -0600, John Stanton wrote: > A neat way to implement a pager is to memory map a file to make it > shared virtual memory. Then you need some form of mutex to synchronize > access to it. If you are sharing it between processes or threads you > need a lock flag

Re: [sqlite] Pager Question... Open Source Project

On Mon, Jan 22, 2007 at 05:13:44PM -0600, John Stanton wrote: > >Of course. The point is that 32-bit memory models will impact the > >design of the this pager. Either only support 64-bit memory models, > >window your memory mappings, or only support small databases. > > > Or be adaptive and

Re: [sqlite] Pager Question... Open Source Project

On Mon, Jan 22, 2007 at 06:06:53PM -0600, John Stanton wrote: > Alternately use a 64 bit machine and a 64 bit OS. Did you not read what I wrote? That was one of the solutions I offered. The issue is that memory mapping runs into the 32-bit VM space wall and you have three options: too bad, go

Re: [sqlite] SQLite last inserted id

If you'd have a unique index on that column then you could just use "INSERT OR IGNORE ..." Nico -- - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] How to conditionally drop a table?

On Thu, Jan 25, 2007 at 10:35:23AM -0500, Anderson, James H (IT) wrote: > I need a way to drop a table only if that table exists. How would I do > that? You'd think this would work: SELECT CASE WHEN (SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'foo') > 0

Re: [sqlite] Re: selecting a random record from a table

On Fri, Jan 26, 2007 at 09:16:41AM -0700, Dennis Cote wrote: > The offset mechanism proposed by Igor earlier is far more efficient as > long as you know the size of the table. You can always get the size from > a count query, which also requires a table scan, but even that is less > expensive

Re: [sqlite] Re: Re: selecting a random record from a table

On Fri, Jan 26, 2007 at 12:58:13PM -0500, Igor Tandetnik wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > >I can't see why this doesn't work reliably, but if it did it would be > >O(1). > > Imagine that you have just two records in your table, with ROWIDs of

Re: [sqlite] Re: Re: Re: selecting a random record from a table

On Fri, Jan 26, 2007 at 01:38:07PM -0500, Igor Tandetnik wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > >But I read that as "goto to offset 2 and return the first row after > >offset 2." > > Why offset 2, when the clause reads, say, OFFSET 500? Also,

Re: [sqlite] UNIQUE constraint on column

On Wed, Jan 31, 2007 at 05:23:29PM -0500, Shane Harrelson wrote: > > I have two tables, an "Objects" table with a foreign key into a second > "Strings" table which is composed of unique values. It is a many to > one relationship, that is, several Objects may reference the same > String. When

Re: [sqlite] Equivalent syntax?

On Wed, Jan 31, 2007 at 06:31:20PM -0500, Anderson, James H (IT) wrote: > Is cast documented on the sqlite website? I couldn't find it. http://www.sqlite.org/ Click on 'syntax', click on 'expression', arrive at: http://www.sqlite.org/lang_expr.html

Re: [sqlite] marking transaction boundaries

On Tue, Jun 22, 2010 at 08:35:14AM -0700, b s wrote: > hi, > long ago, drh had proposed a trigger like mechanism that > can be invoked at the begin/end of a transaction. > http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html > > the general consensus was there is no use other than up'ng >

Re: [sqlite] 64 bit libsqlite for AIX and Solaris

On Thu, Jun 24, 2010 at 01:37:50PM +0530, Sushil wrote: > I am looking for 64 bit libsqlite for AIX and Solaris. Is there a > place from where I can get them pre-built ? For OpenSolaris you can find SQLite3 packages, including a 64-bit build of the library, in the OpenSolaris IPS /release package

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jun 28, 2010 at 11:30:49AM -0400, Eric Smith wrote: > From the docs: > > > Under Unix, you should not carry an open SQLite database across a > > fork() system call into the child process. Problems will result if you > > do. > > What if I fork a process that promises not to use the

Re: [sqlite] open db cx to fork(2)d children

On Fri, Jul 09, 2010 at 04:52:35PM -0400, Eric Smith wrote: > > > I strongly recommend that you always make the child side of fork(2) > > either exit(2) or exec(2) immediately. > > Sorry Nico, I never saw this response -- I appreciate it! > > What do you mean, "immediately"? Good question.

Re: [sqlite] open db cx to fork(2)d children

On Fri, Jul 09, 2010 at 02:22:37PM -0700, Roger Binns wrote: > On 07/09/2010 01:52 PM, Eric Smith wrote: > > What do you mean, "immediately"? As I said, my child comes to life, > > does some work without touching (its copy of) existing SQLite strucures, > > and then calls exit(2). > > I'll bet

Re: [sqlite] open db cx to fork(2)d children

On Fri, Jul 09, 2010 at 02:38:08PM -0700, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/09/2010 02:31 PM, Nicolas Williams wrote: > > The trick to making that go fast is to use pthread_atfork() to get the > > new PID on the child side of

Re: [sqlite] open db cx to fork(2)d children

On Sat, Jul 10, 2010 at 11:27:41AM -0700, Roger Binns wrote: > On 07/10/2010 07:12 AM, Eric Smith wrote: > > Your wrapper is nice -- have you considered folding something like it > > into the core (disabled by default, enabled by a compile-time flag) and > > submitting it to drh for official

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 01:59:30PM -0500, Nicolas Williams wrote: > The result is that you end up with a tiny penalty for fork > detection: two loads, a compare and a likely-not-taken branch. Actually three loads, two compares and two likely-not-taken branch, unless you kn

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 02:47:19PM -0700, Roger Binns wrote: > About the only correct thing to do in a process using SQLite through a fork > is to terminate the process. You can return an error to the caller too. Of course, if you're using the mutex functions to do this then it's too late, so

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 08:43:32PM -0700, Roger Binns wrote: > Earlier you were trying to optimise out calls to getpid() and now you want > every SQLite function source to be changed? No, just entry points into the library. What makes you think that I meant that every function in the library

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 08:43:32PM -0700, Roger Binns wrote: > > They're insane (the _first_, not last, fildes close(2) in a process > > drops all locks on the underlying file), but the child won't clobber the > > parent's locks. > > That is assuming all components (C libraries, threading,

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 11:25:07PM -0700, Roger Binns wrote: > > No, just entry points into the library. What makes you think that I > > meant that every function in the library should check this? > > I also meant entry points but wasn't specific. There are a lot of them that > acquire/release

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

On Fri, Aug 06, 2010 at 02:11:33PM -0400, Richard Hipp wrote: > If "ch" is an unsigned char then how is the following unsafe: > > ch = (ch<0x80) ? tolower(ch) : ch > > And why does it need to be changed to > > ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch; > > There is only one such

Re: [sqlite] Suggestion for project: Chat

On Tue, Aug 24, 2010 at 10:23:55PM +0530, Roger Binns wrote: > On 08/24/2010 09:51 PM, Artur Reilin wrote: > > I thinking about coding a chat with sqlite. > > The hard part of implementing chat is not storing the messages, but > rather dealing with all the clients at the same time especially

Re: [sqlite] Detecting storage class from C

On Wed, Sep 08, 2010 at 07:01:19PM -0400, Igor Tandetnik wrote: > Schrum, Allan wrote: > > I guess I'm confused as to why sqlite3_column_type() would not work? It > > works for me on queries and I get back either > > SQLITE_INTEGER, SQLITE_FLOAT, or SQLITE3_TEXT. While

Re: [sqlite] what could be the reason that natural join stops working ?

On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote: > On 15-09-2010 11:36, Benoit Mortgat wrote: > > Are you sure that after altering your tables adding columns, natural > > join still only joins on vlid? > > > no, very stupid of me !! > I added a column to each of the tables, with the

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

On Fri, Sep 17, 2010 at 02:02:44PM -0400, Igor Tandetnik wrote: > Keith Roberts wrote: > > I think what I really mean is I want a text column with only > > one unique value, that is also indexed. > > UNIQUE constraint will do just that. > > > Also ,I guess the INTEGER

Re: [sqlite] Need help with self-join (I think)

On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote: > CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT > NOT NULL); > INSERT INTO Test (Year, Name) VALUES (2007, 'A'); > INSERT INTO Test (Year, Name) VALUES (2007, 'B'); > INSERT INTO Test (Year, Name)

Re: [sqlite] Tricky grouping query

On Fri, Oct 01, 2010 at 08:37:07PM +0100, Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into

Re: [sqlite] SQLite database sync

On Thu, Oct 07, 2010 at 09:09:19PM +0200, Petite Abeille wrote: > On Oct 6, 2010, at 5:50 PM, David Haymond wrote: > > If I copy, I don't want to transfer EVERY record to the server each time I > > sync, because that would be a waste of bandwidth. What is the best way to > > copy only those

Re: [sqlite] Speed up DELETE of a lot of records

On Fri, Oct 08, 2010 at 05:49:18PM +0100, Simon Slavin wrote: > On 8 Oct 2010, at 5:48pm, Stephan Wehner wrote: > > On Fri, Oct 8, 2010 at 7:14 AM, Michele Pradella > > wrote: > >> "science fiction?" was a rhetorically question. I'm only wondering > >> about what is

Re: [sqlite] programmatic way of determining fragmentation?

On Sat, Oct 23, 2010 at 02:12:19AM +0400, Max Vlasov wrote: > As for your initial question, I think fragmentation evaluation is possible > with the help of VFS. I'd keep a total sum of of absolute difference between > consequent read offsets for xRead operation. In this case if some xRead >

Re: [sqlite] WAL file growth concern

On Fri, Oct 22, 2010 at 09:56:22PM -0400, Richard Hipp wrote: > On many (most?) filesystems, it is faster to overwrite an existing area of a > file than it is to extend the file by writing past the end. That's why > SQLite doesn't truncate the WAL file on each checkpoint - so that subsequent >

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

On Tue, Oct 19, 2010 at 09:39:44AM -0400, Richard Hipp wrote: > On Mon, Oct 18, 2010 at 8:19 AM, wrote: > > > I made an error in my SQL when I did not include one of my non-aggregate > > columns in my group. I was surprised that Sqlite did not catch this, and > > even

Re: [sqlite] Trouble with TRIGGERS

Do you have recursive triggers enabled? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column?mode does not align UTF8-strings correctly

On Fri, Nov 26, 2010 at 06:52:56AM +, Niklas Bäckman wrote: > Igor Tandetnik writes: > > Note that counting codepoints, while it happens to help with your > > particular data, won't help in general. Consider combining > > diacritics: U+00E4 (small A with diaeresis) looks the

Re: [sqlite] Low-load methods of finding previous or next row

On Tue, Dec 14, 2010 at 05:09:04PM +, Simon Slavin wrote: > I recently found out that when you use LIMIT in SQLite the engine > still processes all applicable records even if it only has to return > the number you asked for. I suspect that this makes something I used > to do inefficient. So

Re: [sqlite] Low-load methods of finding previous or next row

On Wed, Dec 15, 2010 at 03:56:06AM -0600, Nicolas Williams wrote: > SELECT * FROM toy > WHERE > a >= (SELECT a FROM toy WHERE id = 6) OR > (a = (SELECT a FROM toy WHERE id = 6) AND > b <= (SELECT b FROM toy WHERE id = 6)) OR > (a = (SE

Re: [sqlite] Low-load methods of finding previous or next row

On Wed, Dec 15, 2010 at 04:29:42AM -0600, Nicolas Williams wrote: > The ORed terms are optimized as a UNION, with each sub-query using the > index. That's three index operations per column that you order by. Not > bad. s/three/one/ ___ sql

Re: [sqlite] Low-load methods of finding previous or next row

And if you use parametrized queries then you get this query plan: 0|0|TABLE toy VIA MULTI-INDEX UNION 0|0|TABLE toy WITH INDEX toy_abc 0|0|TABLE toy WITH INDEX toy_abc 0|0|TABLE toy WITH INDEX toy_abc The ORed terms are optimized as a UNION, with each sub-query using the index. That's three

Re: [sqlite] tcl and NULL

IMO the best solution is to have a special procedure passed to the Tcl SQL function that must be called to set the return value of the SQL function. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Advice on breaking trigger recursion?

On Sat, Jan 08, 2011 at 12:07:08PM +0700, Dan Kennedy wrote: > On 01/08/2011 08:12 AM, Nicolas Williams wrote: > > I need to use recursive triggers. In some cases I want to "normalize" > > values of some columns of NEW being INSERTed or UPDATEd, but there's no > >

Re: [sqlite] Advice on breaking trigger recursion?

On Fri, Jan 07, 2011 at 09:54:07PM -0600, Nicolas Williams wrote: > On Sat, Jan 08, 2011 at 01:29:23AM +, Simon Slavin wrote: > > On 8 Jan 2011, at 1:12am, Nicolas Williams wrote: > > > I need to use recursive triggers. In some cases I want to "normalize" > &g

Re: [sqlite] How to track a record if the aliased id is changed

On Wed, Jan 12, 2011 at 10:07:36PM +, Simon Slavin wrote: > On 12 Jan 2011, at 9:57pm, Max Vlasov wrote: > > Simon, your reply led me to the following sequence: > > - I know the rowid of the record I'm changing. I remember all integers (and > > all other data) I'm going to change in the Update

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 10:59:29AM +, Simon Slavin wrote: > His problem is that he doesn't know which rows are aliased to rowid, > so he can't provide a list of column names. So the following might be > closer > > CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable > BEGIN >

Re: [sqlite] How to track a record if the aliased id is changed

On Fri, Jan 14, 2011 at 10:20:22AM +, Philip Graham Willoughby wrote: > On 13 Jan 2011, at 17:27, Nicolas Williams wrote: > > On Thu, Jan 13, 2011 at 10:59:29AM +, Simon Slavin wrote: > >> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable >

  1   2   3   >