Re: [sqlite] Advice on breaking trigger recursion?

2011-01-07 Thread Dan Kennedy
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 > UPDATE syntax for changing NEW, thus I can't write something like: > > CREATE TRIGGER ... > BEGIN >

Re: [sqlite] Disabling foreign key cascading during re-insertion of existing data

2011-01-07 Thread Dan Kennedy
On 01/07/2011 09:19 PM, BareFeetWare wrote: > Hi All, > > I have developed some software that allows the user to change the schema of > tables. It does this by: > > 1. Backs up the rows from the old table into a temporary duplicate table > 2. Drops the old table > 3. Creates the new table > 4. Ins

Re: [sqlite] How to default journal_mode to PAGER_JOURNALMODE_WAL?

2011-01-07 Thread Dan Kennedy
On 01/07/2011 08:49 PM, Sam Carleton wrote: > Simon, > >> From what I gathered on the web site, WAL is a state of how the engine > works, not a type of database file. There's a flag stored in the database file. See under "Persistence of WAL Mode" here: http://www.sqlite.org/wal.html ___

Re: [sqlite] "Foreign key mismatch" error -- possible bug

2010-12-31 Thread Dan Kennedy
On 01/01/2011 11:27 AM, Kenneth Ballard wrote: > I apologize for not providing that initially. Here is the schema for the > three tables: > > CREATE TABLE [T3] ( > [f1] INTEGER NOT NULL CONSTRAINT [fk_T3_T2] REFERENCES [T2]([f1]) ON > DELETE CASCADE, > [f2] INTEGER NOT NULL, > [f3] INTE

Re: [sqlite] FTS4 snippets contains unintended content?

2010-12-29 Thread Dan Kennedy
of… 374 I think that is an expected result. The snippet() function is returning two fragments of text (since it cannot find a single fragment that contains all the terms in your query). The second fragment of text contains a single token - "374". Dan. ___

Re: [sqlite] BUSY on COMMIT when EXCLUSIVE is held is an unexpected result

2010-12-17 Thread Dan Kennedy
On 12/17/2010 07:54 PM, Ben Harper wrote: > I have this case: > > Thread 1 Thread 2 > -- > BEGIN EXCLUSIVE > BEGIN EXCLUSIVE -> BUSY > ... etc ... > BEGIN EXCLUSIVE -> BUSY > COMMIT >

Re: [sqlite] WAL index in memory - multiple connections

2010-12-09 Thread Dan Kennedy
vides). The alternative is to create your own VFS that stores the WAL index in heap memory. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with disableTerm() and virtual tables

2010-12-07 Thread Dan Kennedy
ld have something to do with the scanning costs your virtual tables are returning to SQLite. Does this happen in 3.7.4? Can you provide us with code for virtual tables that cause the bug to occur? Dan. ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] FTS3 bug?

2010-12-07 Thread Dan Kennedy
On 12/08/2010 04:18 AM, Iker Arizmendi wrote: > The function that opens a cursor for the simple tokenizer, > simpleOpen, does not set the "pTokenizer" member of the > returned cursor. Ie, it appears the following line is > missing: > > c->base.pTokenizer = pTokenizer; > > which causes problems

Re: [sqlite] WAL file size

2010-12-07 Thread Dan Kennedy
On 12/07/2010 09:49 PM, Yoni Londner wrote: > Hi, > > Yes, in this scheme the checksum is based on salt values and own frame > content.a > > Note that the current design solve a potential DB corruption bug in > sqlite. current WAL design is base on the fact that once sqlite writes > pages successfu

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

2010-12-04 Thread Dan Kennedy
On 12/04/2010 12:54 AM, Philip Graham Willoughby wrote: > On 2 Dec 2010, at 20:43, Sylvain Pointeau wrote: > >> Hi, >> >> I am on macosx sqlite 3.7.3 >> >> $ /usr/local/bin/sqlite3 test.db3 >> SQLite version 3.7.3 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sq

Re: [sqlite] WAL file size

2010-12-02 Thread Dan Kennedy
header and the frames own contents? Dan. On 12/02/2010 11:04 PM, Yoni Londner wrote: > Hi, > > I will start out by stating that I am not deeply familiar with the > sqlite& WAL file layout, but I will try anyway, from my current > understanding: > > The general proble

Re: [sqlite] EXPLAIN QUERY PLAN

2010-11-30 Thread Dan Kennedy
On 11/30/2010 11:38 PM, Duquette, William H (316H) wrote: > I've just discovered EXPLAIN QUERY PLAN; it looks quite useful, but one part > of the output is somewhat opaque. > > The command returns three columns: order, from, and detail. "order" is > evidently the order in which the indices are a

Re: [sqlite] WAL mode and backup API

2010-11-30 Thread Dan Kennedy
> BTW: > Is there any chance that in the future the wal mode > will avoid that the backup API will restart on DB > changes during the backup loop ? > Currently, even in wal mode, it does restart when changes > are detected while the backup is ongoing. What happens if you open a read transaction o

Re: [sqlite] WAL mode and backup API

2010-11-30 Thread Dan Kennedy
On 11/30/2010 05:03 PM, Marco Bambini wrote: > Hello, > > if a running sqlite database is in WAL mode and a backup is performed on that > db using the sqlite3_backup API, does that process is considered like a > reader and can proceed concurrently with other readers and with the other > writer?

Re: [sqlite] searching with like on FTS3

2010-11-29 Thread Dan Kennedy
On 11/30/2010 12:09 AM, boscowitch wrote: > Hi recently I noticed that i can't search with the like '%searchword%' > syntax on an FTS3 virtual table. > > And with "match" i can't search on example sentences (the indexed data > is a japanese dictionary an therefore has no spaces in example sentence

Re: [sqlite] WAL, durability, and synchronous=NORMAL

2010-11-26 Thread Dan Kennedy
On 11/25/2010 09:04 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > >> In WAL mode with synchronous=NORMAL, when the user commits >> a transaction, it is written into the WAL file. No sync >> until a checkpoint happens. So if the power fails

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

2010-11-25 Thread Dan Kennedy
On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote: > Hi all, > > I'm noticing a new failure with SQLite 3.7.3 as compared to the previous > version I was using, 3.6.23.1. Are you able to share the database and the query that causes the assert() to fail? __

Re: [sqlite] WAL, durability, and synchronous=NORMAL

2010-11-25 Thread Dan Kennedy
On 11/25/2010 08:49 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > >> On 11/25/2010 03:24 PM, Twylite wrote: >>> > > Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't > like. > >>>

Re: [sqlite] autoindex1 test fail on linux debian

2010-11-25 Thread Dan Kennedy
On 11/25/2010 02:43 PM, Yoni wrote: > Hi, > > I get the following fail when running sqlite3 tests, under linux debian, > with TCL 8.4. > Code is latest from fossil. > Any help appreciated. Try with 8.5 or newer. ___ sqlite-users mailing list sqlite-use

Re: [sqlite] WAL, durability, and synchronous=NORMAL

2010-11-25 Thread Dan Kennedy
On 11/25/2010 03:24 PM, Twylite wrote: > Hi, > > I am seeking technical information on the durability of transactions > with journal_mode=WAL and synchronous=NORMAL. > > Specifically, in the event of a power failure, can the following ever > happen: > (1) Loss of the last transaction completed. > (

Re: [sqlite] NO_GETTOD

2010-11-22 Thread Dan Kennedy
On 11/22/2010 11:48 PM, Black, Michael (IS) wrote: > Bug in 3.7.3? > gcc -c -DNO_GETTOD sqlite3.c > sqlite3.c: In function âunixCurrentTimeInt64â: > sqlite3.c:27870: error: âiâ undeclared (first use in this function) > sqlite3.c:27870: error: (Each undeclared identifier is reported only once > sqli

Re: [sqlite] Compile 64bit version of SQLIte on Solaris?

2010-11-22 Thread Dan Kennedy
On 11/22/2010 03:25 PM, Lynton Grice wrote: > Hi there, > > That does not work, I tried that already. > > # /usr/local/bin/gcc -m64 -D_FILE_OFFSET_BITS=64 -c -fPIC -DHAVE_USLEEP > sqlite3.c > sqlite3.c:1: sorry, unimplemented: 64-bit mode not compiled in > # > > And I am on a 64-bit Solaris box...

Re: [sqlite] Custom collating sequences and performance

2010-11-18 Thread Dan Kennedy
> the collation function? > > I was thinking of queries like > > SELECT mykey FROM mytab ORDER BY mykey The collation sequence callback will not be invoked in that case. SQLite will just iterate from start to finish of the index b

Re: [sqlite] Trouble with TRIGGERS

2010-11-16 Thread Dan Kennedy
ing which triggers might be needed is the type of statement (UPDATE, DELETE, INSERT) and for UPDATES, the columns updated. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange WAL mode on 5GB+ database

2010-11-11 Thread Dan Kennedy
sqlite> pragma journal_mode=delete; > Error: database is locked > sqlite> pragma integrity_check; > ok > sqlite> pragma journal_mode=delete; > delete Is this a repeatable problem? Any other processes accessing the database when you run the test? Dan. __

Re: [sqlite] INSERT OR IGNORE with rtree virtual tables

2010-11-08 Thread Dan Kennedy
gt;> Is this a bug, or operating as designed? Virtual tables (like r-tree and fts3) ignore ON CONFLICT clauses. Just the way it is unfortunately. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query planner bug on "distinct" clause

2010-11-08 Thread Dan Kennedy
If the (rowid>?) clause identifies a small number of rows, then the last assumption will be incorrect - using the rowid index would be much faster. But SQLite doesn't know this when formulating a query plan. Regards, Dan. > The correct plan may be: > sqlite> drop INDEX obj

Re: [sqlite] A question about transactions

2010-11-04 Thread Dan Kennedy
t explicit user request) >> rollbacks >> the whole transaction disregarding any savepoints? > > I believe ON CONFLICT ROLLBACK (and its equivalents, like > RAISE(ROLLBACK) ) would roll back the whole transaction. That's the usual cause. Also, if an OOM or IO e

Re: [sqlite] Mistake in threadtest3.c

2010-11-02 Thread Dan Kennedy
_1 and > dynamic_triggers_2 in > the first instance, like it does in the second instance? Fair question. But in this case no. The idea was to have one thread modifying the triggers. Then several other reader threads using the database with the reader threads using both shared and unshared ca

Re: [sqlite] BUG

2010-10-26 Thread Dan Kennedy
On Oct 26, 2010, at 2:22 PM, Alexey Pechnikov wrote: >> But view_user statement makes no attempt to select the last >> version. It > picks some arbitrary random version. You might want to consider > something > like this: > > Why you wrote about "some arbitrary random version" when we have

Re: [sqlite] Foreign key on different database - possible?

2010-10-20 Thread Dan Kennedy
On Oct 20, 2010, at 4:47 PM, Frank Millman wrote: > Dan Kennedy wrote: >> >> On Oct 20, 2010, at 3:59 PM, Frank Millman wrote: >> >>> Hi all >>> >>> I am using SQLite 3.7.2 on Fedora 10. >>> >>> I have multiple databases, which

Re: [sqlite] Foreign key on different database - possible?

2010-10-20 Thread Dan Kennedy
On Oct 20, 2010, at 3:59 PM, Frank Millman wrote: > Hi all > > I am using SQLite 3.7.2 on Fedora 10. > > I have multiple databases, which I can access concurrently by using > the > 'attach' command, and then referring to each table using > 'database.tablename'. It works well. > > Now I want to

Re: [sqlite] Scaling of Cache

2010-10-18 Thread Dan Kennedy
ay the sqlite3_soft_heap_limit() limit), then when a page is required one connection, it can steal the buffer space from some other connections page cache. Dan. > Thanks > Doug > > >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-us

Re: [sqlite] Attach on disk database to memory database

2010-10-15 Thread Dan Kennedy
On Oct 15, 2010, at 11:40 PM, Schoinya wrote: > > Hello everybody > > I'm trying to attach on disk database to in memory database. > > But I get the strange error : SQLite error unrecognized token: ":" > > The following is the code: > >SQLiteConnection connInMemory = new > SQLit

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

2010-10-15 Thread Dan Kennedy
st that the second mmap() call is zeroing the memory mapped by the first mmap() call. Which is, as you might expect, confusing SQLite. I guess it could also be the second ftruncate() call that is zeroing our mapped memory. That would be even odder though... Dan. > May just need some QNX lo

Re: [sqlite] Just want to double check on index need

2010-10-14 Thread Dan Kennedy
On Oct 14, 2010, at 11:56 PM, Alan Chandler wrote: > On 14/10/10 17:28, Dan Kennedy wrote: >> >> On Oct 14, 2010, at 10:43 PM, Alan Chandler wrote: >>> CREATE TABLE div_winner_pick ( > ... >>> PRIMARY KEY (cid,confid,divid,uid) >>> ); > > .

Re: [sqlite] Just want to double check on index need

2010-10-14 Thread Dan Kennedy
advantage to create it > separately. It should be clearer. Basically the index would be redundant if it contains the same columns in the same order as the primary key. Or a prefix thereof. i.e. the following indexes would be all be redundant (pure ove

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

2010-10-14 Thread Dan Kennedy
On Oct 14, 2010, at 5:53 PM, Raj, Praveen wrote: > Hi Dan, > > I did some more investigation on the issue and i feel there is > synchronization problem happening here. > > After mmapping the shm (wal index) file to process memory, the WAL > indexes are written into the m

Re: [sqlite] handling of BLOB bound parameters

2010-10-14 Thread Dan Kennedy
ou have an sqlite3_trace() hook registered. If it's a problem, maybe you can clear the hook (by passing NULL to sqlite3_trace()) temporarily while executing queries that use large blobs. Or don't use it at all, if you can live without SQL tracing. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

2010-10-08 Thread Dan Kennedy
to dig into memory mapping to find whats happening > and hopefully find a solution. > > Dan - Do you have any idea on why this could be happening? Sounds like a bug in QNX to me. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

2010-10-07 Thread Dan Kennedy
On Oct 7, 2010, at 5:50 PM, Shopsland gmail wrote: > Hi, > > Given this simple query with a subquery in FROM and a join with a > FTS3 table: > > SELECT news1.number, fts_news.title > FROM (SELECT number FROM news LIMIT 50) as news1, fts_news > WHERE news1.number=fts_news.docid > > The query run

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

2010-10-06 Thread Dan Kennedy
On Oct 6, 2010, at 9:39 PM, Raj, Praveen wrote: > Hi Dan, > > For debugging purpose we use the QNX Momentics which is the IDE > provided by QNX. I believe to certain extent > we should be able to debug the issue using IDE. In what situations > does the shared memory bl

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

2010-10-06 Thread Dan Kennedy
(we use a separate mapping for each 32KB block) is zeroing the first. Somehow. Do you have a good debugger for the platform? Dan. > Any insights on whats happening here would be of great help. > > Thanks, > Praveen > > -Original Message- > From: sqlite-users-boun...@sqli

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

2010-10-02 Thread Dan Kennedy
puts [file size test.db-wal] puts [file size test.db-shm] or similar after the test case. Here the files are 1024, 4333512 and 65536 bytes. From what you are saying it sounds like the problem is occurring when the -shm file grows from 32KB to 64KB. Dan. _

Re: [sqlite] Referential integrity based on a view.

2010-09-30 Thread Dan Kennedy
uires testing the FK constraint. More details here: http://www.sqlite.org/foreignkeys.html#fk_indexes Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

2010-09-24 Thread Dan Kennedy
On Sep 23, 2010, at 9:57 PM, Borra, Kishore Babu wrote: > Hi, > > I require some help in getting the fix for some memory corruption > issues, occurring while using the sqlite3 library. It would be very > helpful, if you can guide to fix the below issues or atleast provide > some info on thi

Re: [sqlite] COUNT very slow

2010-09-24 Thread Dan Kennedy
On Sep 24, 2010, at 3:13 PM, Michele Pradella wrote: > I have an SQLite DB of about 9GB with about 2.500.000 records. > I can't understand why the "select COUNT(*) from log" statement is > extremely slow, it takes me about 9-10 minutes! In SQLite, count() is obliged to traverse the entire table

Re: [sqlite] Overflow Page

2010-09-21 Thread Dan Kennedy
On Sep 22, 2010, at 12:23 PM, Scott Weigand wrote: > > Hello List, > Apologies if this is the wrong list to post to. Can anyone tell me > if an overflow page is encapsulated within a B-Tree page or if it > starts on a standard page boundary and has its first 4 bytes as the > next overflow

Re: [sqlite] cast from pointer to integer of different size

2010-09-16 Thread Dan Kennedy
On Sep 16, 2010, at 2:09 AM, jagjeet singh nain wrote: > Hi, > I was compiling sqlite on 64 bit OS and i got following warning > message. > > sqlite3.c: In function 'rtreeInit': > sqlite3.c:118698:20: warning: cast from pointer to integer of > different size > Two versions were tried, 3.6.23.

Re: [sqlite] Understanding Shared-Cache Mode

2010-09-11 Thread Dan Kennedy
hought is each and every time the module code gets a > connection, it simply calls int sqlite3_enable_shared_cache(int) to > make sure that connection is in the shared-pool. Am I correct in > assuming that the cost of calling int sqlite3_enable_sh

Re: [sqlite] memory wasted shm mapped file (3.7.2)

2010-09-10 Thread Dan Kennedy
w about the internal logic that requires this, but on > Windows 350 > regions is a maximum in this case. Does it mean that linux share > address > space for superset/subset regions in contrary to Windows logic? It is separate. This bug was in the OS specific win32 layer Dan. ___

Re: [sqlite] memory wasted shm mapped file (3.7.2)

2010-09-09 Thread Dan Kennedy
On Sep 9, 2010, at 10:04 PM, Max Vlasov wrote: > On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy > wrote: > >> >> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote: >> >>> Hi, do you have some news about the wasted memory? have you found >>> the &g

Re: [sqlite] "create table as" syntax

2010-09-09 Thread Dan Kennedy
On Sep 9, 2010, at 3:56 PM, thomas veymont wrote: > hello, > > I'm trying to use the "CREATE TABLE AS" syntax to create a table and > insert > in the same time a default row. > > e.g : > > sqlite> CREATE TABLE test (x NUMERIC) AS (SELECT 25 AS x); > Error: near "AS": syntax error > > oops. > >

Re: [sqlite] memory wasted shm mapped file (3.7.2)

2010-09-09 Thread Dan Kennedy
On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote: > Hi, do you have some news about the wasted memory? have you found the > reason for the windows backend? Fixed here: http://www.sqlite.org/src/ci/f213e133f6 Does the problem still show up for you using fossil tip? > do you think it

Re: [sqlite] FTS3 finds too much: Slash special meaning? Something else?

2010-09-03 Thread Dan Kennedy
On Sep 2, 2010, at 6:37 PM, Lukas Haase wrote: > Hi, > > I use FTS3 (SQLITE_ENABLE_FTS3) with enhanced query syntax > (SQLITE_ENABLE_FTS3_PARENTHESIS). > > Now if I search for a string like '2002/91/AH' there are lots of items > which do NOT contain this string. This is a query: > > SELECT rowid,

[sqlite] iCalendar (*.ics) import ?

2010-09-02 Thread Dan White
I have Google-ed references to a utility called ical2sqlite, which claims to be able to make a database out of an iCalendar data file. Anyone on this list have any experience with it ? “Sometimes I think the surest sign that intelligent life exists elsewhere in the universe is that none of it h

Re: [sqlite] upstreaming Chromium patches for file handle passing support

2010-09-01 Thread Dan Kennedy
or where to create a master journal file if the client writes to the database? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upon table creation, multiple CHECK constraints do not work with FTS3

2010-08-29 Thread Dan Kennedy
uctor failed Maybe it thinks the two "CHECK(1)" identifiers represent a duplicate column name. CHECK constraints will not work with either fts3 or rtree virtual tables. If they are parsed at all, they will probably not do what you are expecting. Dan. _

Re: [sqlite] Asynchronous I/O Module For SQLite vs Single Thread mode

2010-08-29 Thread Dan Kennedy
.sqlite.org/threadsafe.html) or does this necessarily mean > that the > Multi-threads or serialized modes must be used? I think it's Ok to have SQLite in single-thread mode. At least, I can't see why not. Dan. ___ sqlite-users mailing list

Re: [sqlite] Why the deadlock?

2010-08-25 Thread Dan Kennedy
On Aug 25, 2010, at 11:04 PM, Pavel Ivanov wrote: >> Prior to version 3.6.5 SQLite used to delay committing the >> transaction until all SELECT statements had finished. But that >> behavior was deemed to be less intuitive. > > But this is the current 3.7.1 documentation > (http://www.sqlite.org/l

Re: [sqlite] Why the deadlock?

2010-08-25 Thread Dan Kennedy
fulness of > the statement. Maybe somebody somewhere forgot to set autocommit to 0 > when select started executing? > > Dan, can you shed some light on this strange behavior? When you commit a transaction, SQLite upgrades to an EXCLUSIVE lock so that it can write to the database file. Once it

Re: [sqlite] errors running test suite (couldn't execute "testfixture")

2010-08-20 Thread Dan Kennedy
d I do to make it pass? Either add "." to the PATH variable or do another update. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in FTS3 when trying to rename table within a transaction

2010-08-18 Thread Dan Kennedy
. For now, fts3 tables should only renamed outside of transactions. Dan. > > See below: > > D:\temp>sqlite3 tmp.db > SQLite version 3.6.23.1 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table NonF

Re: [sqlite] EXTERNAL: Record corruption on Mac OS X 10.6 (Snow Leopard)

2010-08-17 Thread Dan Kennedy
> I'm using SQLITE_STATIC since the memory buffer returned by > cStringUsingEncoding should be valid until the object is deallocated, > which doesn't happen until after the statement is executed. You could try using SQLITE_TRANSIENT instead to verify this. Or just go straight to valgrind. Good

Re: [sqlite] Why the deadlock?

2010-08-16 Thread Dan Kennedy
statement 4. After that it releases all locks. Cannot obtain the required EXCLUSIVE lock, due to the PENDING lock held by thread 2. http://www.sqlite.org/lockingv3.html#pending_lock > - Now thread 2 can execute statement 3. This would be true, but we don't get t

Re: [sqlite] Why the deadlock?

2010-08-16 Thread Dan Kennedy
On Aug 17, 2010, at 1:48 AM, Nikolaus Rath wrote: > Hello, > > The script below fails with > > Deadlock detected when executing 'DELETE FROM foo WHERE id=2' > > What I think should be happening instead is this: > > - When executing statement 1, the main thread obtains a SHARED lock. > > - When ex

Re: [sqlite] Distinct Bug

2010-08-13 Thread Dan Kennedy
into t_distinct_bug values ('1', '3', 'f'); > > select a > from (select distinct a, b > from t_distinct_bug) > => 1 > > I'd have thought it should return > 1 > 1 > 1 Thanks for this report. Now fixed in fossil tip. Bug here: http://www.sqlite.org/src/info/e4b8a2ba6e Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a table that show all the available function from sqlite3?

2010-08-13 Thread Dan Kennedy
On Aug 13, 2010, at 11:37 PM, Peng Yu wrote: > Hi, > > http://www.sqlite.org/docs.html > > I don't see a table that shows all the available functions in sqlite3. > Would you please let me know if there is such a table? http://www.sqlite.org/lang_corefunc.html http://www.sqlite.org/lang_dat

Re: [sqlite] Reserve database pages

2010-08-13 Thread Dan Kennedy
On Aug 13, 2010, at 8:41 PM, Max Vlasov wrote: > On Fri, Aug 13, 2010 at 1:38 PM, Max Vlasov > wrote: > >> >> I can approximately calculate, how big the new database will grow. Is >>> there a way to tell SQLite to reserve an inital space or numer of >>> pages >>> instead of letting the datab

Re: [sqlite] Backup API, .backup command, and writing to handle live backups?

2010-08-10 Thread Dan Kennedy
On Aug 11, 2010, at 1:25 AM, David Barrett wrote: > On 08/08/2010 10:00 PM, Dan Kennedy wrote: >> >> On Aug 9, 2010, at 11:17 AM, David Barrett wrote: >>> 3) When an application performs read/write queries on the database >>> in >>> parallel to the

Re: [sqlite] performance, transactions and wal checkpoints

2010-08-10 Thread Dan Kennedy
pens as part of the first SQL statement run). Are things any different if you change the sqlite3_wal_checkpoint() to sqlite3_exec(conn, "PRAGMA wal_checkpoint", 0, 0, 0)? Dan. > #include "sqlite3.h" > #include "stdio.h" > #include "stdlib.h&

Re: [sqlite] performance, transactions and wal checkpoints

2010-08-10 Thread Dan Kennedy
tion (but in the same thread). > It only worked if I do the checkpoint on the same connection. You cannot run a checkpoint from within a transaction. If you are in shared-cache mode, this means you cannot run a checkpoint while any connection to the same database has an open transaction. Does tha

Re: [sqlite] Process memory space exhausted in 3.7.0

2010-08-10 Thread Dan Kennedy
suppose. > If you break up the insert into chunks > _and_close_the_connection_between_chunks_ then the error does not > occur. Does this imply that if you add an sqlite3_exec("COMMIT;BEGIN") every 10,000 inserts the program still does not run to completion? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Dan Kennedy
conclusion of your two insert statements, table "a" contains (1, 1) and table "b" contains (1, 2). Since the contents of table "b" violate the FK constraint, an exception is thrown. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write-ahead logging and database locking

2010-08-08 Thread Dan Kennedy
On Aug 9, 2010, at 12:07 PM, Dan Kennedy wrote: > > On Aug 9, 2010, at 11:30 AM, David Barrett wrote: > >> I'm reading up on the new write-ahead logging feature, and I'm >> unclear >> on one point: does WAL only help alleviate multi-threaded locking (by

Re: [sqlite] Write-ahead logging and database locking

2010-08-08 Thread Dan Kennedy
On Aug 9, 2010, at 11:30 AM, David Barrett wrote: > I'm reading up on the new write-ahead logging feature, and I'm unclear > on one point: does WAL only help alleviate multi-threaded locking (by > allowing other threads to continue reading while one is writing), or > does WAL also help between mu

Re: [sqlite] Backup API, .backup command, and writing to handle live backups?

2010-08-08 Thread Dan Kennedy
On Aug 9, 2010, at 11:17 AM, David Barrett wrote: > I *think* I know the answers these questions, but can you please > confirm > them for me? > > 1) Does the sqlite3 command-line app .backup command use the > http://www.sqlite.org/backup.html API, and thus only read-lock the > database for brie

Re: [sqlite] how to clone revision 3.2.6 using fossil

2010-08-03 Thread Dan Kennedy
On Aug 3, 2010, at 1:38 PM, Belisko Marek wrote: > Hi, > > I will need to clone sqlite3 version 3.2.6. > Could you please give me some commands how to do it in fossil? fossil clone http://www.sqlite.org/src sqlite.fossil fossil open sqlite.fossil fossil update 1cdfe66714 __

Re: [sqlite] "database disk image is malformed" while using WAL

2010-08-02 Thread Dan Kennedy
On Aug 2, 2010, at 6:13 PM, Dan Kennedy wrote: > > On Aug 2, 2010, at 1:42 PM, Yoni Londner wrote: > >> Hi, >> Forgot to mention that the inserting should be inside a >> transactions, so >> complete repro steps are: >> 1. open sqlite connection. >

Re: [sqlite] "database disk image is malformed" while using WAL

2010-08-02 Thread Dan Kennedy
checkpoint finished. Thanks for this report. If possible, can you try your test with the latest from fossil (newer than e75b52d156)? Thanks. http://www.sqlite.org/src/zip/SQLite-016486c7d544dcf9.zip?uuid=016486c7d544dcf9b7422cb0fb9804aa1c418f68 Please post if you need an ama

Re: [sqlite] WAL still detects deadlock

2010-07-31 Thread Dan Kennedy
On Jul 31, 2010, at 10:47 PM, Edzard Pasma wrote: > Op 31-jul-2010, om 14:16 heeft Dan Kennedy wrote: > >> >> On Jul 31, 2010, at 12:02 AM, Edzard Pasma wrote: >> >>> Hello, >>> >>> The following scenario raises a BUSY error immediately >>

Re: [sqlite] WAL still detects deadlock

2010-07-31 Thread Dan Kennedy
On Jul 31, 2010, at 12:02 AM, Edzard Pasma wrote: > Hello, > > The following scenario raises a BUSY error immediately > > process A. keeps a write lock > process B keeps a read-lock and tries to promote this to a write-lock > > This is the traditional SQLite deadlock situation, detected by the >

Re: [sqlite] [PATCH] Verify number of arguments in icuRegexpFunc

2010-07-29 Thread Dan Kennedy
On Jul 30, 2010, at 12:44 PM, Scott Hess wrote: > On Thu, Jul 29, 2010 at 10:05 PM, Dan Kennedy > wrote: >> On Jul 30, 2010, at 8:39 AM, Paweł Hajdan, Jr. wrote: >>> I'm attaching a suggested patch to verify number of arguments >>> in icuRegexpFunc

Re: [sqlite] [PATCH] Verify number of arguments in icuRegexpFunc

2010-07-29 Thread Dan Kennedy
On Jul 30, 2010, at 8:39 AM, Paweł Hajdan, Jr. wrote: > I'm attaching a suggested patch to verify number of arguments > in icuRegexpFunc. Please review it. > > This is upstreaming of > http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/icu-regexp.patch?revision=34807&view=markup >

Re: [sqlite] Couple of questions about WAL

2010-07-23 Thread Dan Kennedy
On Jul 23, 2010, at 8:56 PM, Doug wrote: > Thanks for your explanations Dan. The new WAL feature sounds great > and I'm > excited to try it. Two questions below: > >> When in WAL mode, clients use file-locks to implement a kind of >> robust (crash-proof) referen

Re: [sqlite] Couple of questions about WAL

2010-07-22 Thread Dan Kennedy
> If I do a BEGIN, SELECT1 and at that point a writer does BEGIN > IMMEDIATE, SELECT3, UPDATE, COMMIT, and then I continue with SELECT2 > COMMIT, will SELECT1 and SELECT2 have a consistent view of the > database > unaffected by the UPDATE in the middle. In other words, is the > Readers > view

Re: [sqlite] Couple of questions about WAL

2010-07-22 Thread Dan Kennedy
On Jul 22, 2010, at 6:07 PM, Alan Chandler wrote: > I have been reading about WAL, and there are a few questions I would > like to ask. > > 1) I am slightly confused about readers building the WAL index. It > says > way down the page > > quote: > > Using an ordinary disk file to provide shared

Re: [sqlite] SQLite version 3.7.0

2010-07-22 Thread Dan Kennedy
ns would continue writing without causing an error. You cannot delete a file while it is open on windows, so this doesn't come up on win32. This happened with a couple of Tcl tests too. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON

2010-07-20 Thread Dan Kennedy
On Jul 21, 2010, at 11:12 AM, wrote: > > In my case, the DB is created by the older version of application and > now the new version of application is installed and End user has > option > to see old data with selecting old version of DB so its completely on > end user wish to choose the loc

Re: [sqlite] Nested set "make space" query, sum(column) returns NULL with no match

2010-07-18 Thread Dan Kennedy
On Jul 17, 2010, at 8:17 PM, Graham Leggett wrote: > Hi all, > > I have been trying to find an efficient way of shifting the left and > right values for multiple insertions of new nodes in the nested set > model in one go, and I have been struggling to do this with sqlite. > > I have populated a

Re: [sqlite] sqlite database handle caching and write permissions

2010-07-16 Thread Dan Kennedy
On Jul 16, 2010, at 2:05 PM, JT Olds wrote: >> Unsafe. Using the authorizer callback instead to figure out if a >> statement may write the database is a better way: >> >> http://www.sqlite.org/c3ref/c_alter_table.html >> http://www.sqlite.org/c3ref/set_aut

Re: [sqlite] sqlite database handle caching and write permissions

2010-07-15 Thread Dan Kennedy
On Jul 16, 2010, at 6:33 AM, JT Olds wrote: > I really would rather not depend on what is in the SQL itself, as the > concern I have has nothing to do with whether or not the user runs > SELECT, but whether or not this will cause the library to write to > disk. I'd love to decouple those two thin

Re: [sqlite] SQLITE_CANTOPEN returned from sqlite3_step

2010-07-15 Thread Dan Kennedy
On Jul 15, 2010, at 8:35 PM, Yoav Apter wrote: > Hi > > We are building a Windows application using a read-only Sqlite > database. When executing many queries in a short time, we sometimes > get SQLITE_CANTOPEN from sqlite3_step. Checking GetLastError gives > us error code 123: The filename

Re: [sqlite] Documentation error in explanation of "IN subquery" (lang_expr.html)

2010-07-14 Thread Dan Kennedy
On Jul 15, 2010, at 12:25 AM, Simon Slavin wrote: > > On 14 Jul 2010, at 5:13pm, Richard Hipp wrote: > >> Improvements to the IN operator documentation can be found here: >> >>http://www.sqlite.org/draft/lang_expr.html#in_op > > I find that table difficult to understand: you have some mutuall

Re: [sqlite] FTS3 - NEAR operator

2010-07-03 Thread Dan Kennedy
On Jul 3, 2010, at 2:37 AM, Patel, Vinit wrote: > Hi : > > I was wondering how the NEAR operator works for the SQL database. > In the SQLite test suite, in test file fts3near.test, One of the > test case is as follows > > //fts3near-3.1 test case > > db eval { > DELETE FROM t1; > INSERT INTO

Re: [sqlite] database corruption problem

2010-06-08 Thread Dan Kennedy
On Jun 9, 2010, at 12:51 AM, Dave Segleau wrote: > > On 6/8/2010 9:25 AM, Dan Kennedy wrote: >> >> Those pragmas should not cause a problem. Simon's referring >> to "PRAGMA synchronous". The docs for which explain the >> risks assumed b

Re: [sqlite] database corruption problem

2010-06-08 Thread Dan Kennedy
On Jun 8, 2010, at 10:54 PM, Daniel Stutzbach wrote: > On Tue, Jun 8, 2010 at 10:02 AM, Simon Slavin > wrote: > >> I'm not sure whether a power-cut at a particularly bad time could >> cause >> something like this. The journaling mechanism built into SQLite >> should be >> avoiding it, but

Re: [sqlite] Bug and possible fix: Access violation in rtree.c:nodeGetRowid()

2010-06-02 Thread Dan Kennedy
On Jun 2, 2010, at 7:19 PM, Simon Slavin wrote: > > On 2 Jun 2010, at 10:12am, Dan Kennedy wrote: > >> The fix you propose would ignore the (suspected) corruption >> and continue without reporting it to the user. Which might be >> the best thing for some > > bu

Re: [sqlite] Bug and possible fix: Access violation in rtree.c:nodeGetRowid()

2010-06-02 Thread Dan Kennedy
ations. Better than crashing anyhow. On the other hand, it isn't a particularly pleasant thought that this can happen... Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

<    6   7   8   9   10   11   12   13   14   15   >