Re: [sqlite] Format change to fts2 module.

http://www.sqlite.org/cvstrac/tktview?tn=2046 should fix this for fts1 and fts2. -scott On 11/21/06, Ralf Junker <[EMAIL PROTECTED]> wrote: Hello Scott Hess, >>Not directly related to the format change, but did you have a chance >>to look at ticket #2046? > >Yes,

[sqlite] fts1/2 warnings.

On 11/29/06, Ralf Junker <[EMAIL PROTECTED]> wrote: * SQLITE_EXTENSION_INIT1 If I compile both fts1 and fts2 into the same executable with -DSQLITE_CORE=1 -DSQLITE_ENABLE_FTS1=1 -DSQLITE_ENABLE_FTS2=1 I receive a linker warning that sqlite3_api is defined in both fts1.c and fts2.c.

Re: [sqlite] Unicode Help

On 12/7/06, Nuno Lucas <[EMAIL PROTECTED]> wrote: On 12/7/06, Da Martian <[EMAIL PROTECTED]> wrote: > Ok, that does answer one of my questions I think. If I passed something not > in UTF-8 to sqlite would it return it exactly the same way I passed it in? > From your statement of chaos below I

Re: [sqlite] Determining Data Types

On 12/8/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I appreciate the insight, but I'm not sure it helps with my situation. This is what I'm trying to accomplish: 1. User selects 1 or more fields from a table using basic "SELECT fields FROM table" syntax, and the result is retrieved using

Re: [sqlite] Check for empty table

On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: Trying to find the fastest way to determine if a table has no rows. I think this will do: SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL; If a table has rows then the result should be 1. What's wrong with "SELECT COUNT(*) FROM

Re: [sqlite] Check for empty table

On 12/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Scott Hess" <[EMAIL PROTECTED]> writes: > On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: >> Trying to find the fastest way to determine if a table has no rows. >> >> I think this wi

Re: [sqlite] 3.3.10 data corruption on updating fts1 string table

On 1/11/07, ohadp <[EMAIL PROTECTED]> wrote: Scott Hess <[EMAIL PROTECTED]> writes: > CREATE VIRTUAL TABLE t USING fts1(content); > INSERT INTO t (rowid, content) VALUES (1, 'this is a test'); > UPDATE t SET content = 'that was a test' WHERE rowid = 1; > > --

Re: [sqlite] 3.3.10 data corruption on updating fts1 string table

lem is an ascii/unicode problem. > what happens is that i call sqlite3_column_text16 and get back an ASCII > string... > i haven't touched the rest of my code so let me ask if something's changed > in this latest release ? > > > Scott Hess wrote: >> >> On 1/11/07,

Re: [sqlite] 3.3.10 data corruption on updating fts1 string table

cii/unicode problem. >> > what happens is that i call sqlite3_column_text16 and get back an ASCII >> > string... >> > i haven't touched the rest of my code so let me ask if something's >> changed >> > in this latest release ? >> > >> > >

Re: [sqlite] 3.3.10 data corruption on updating fts1 string table

OK, there's definite meat, here. I have other reports of users seeing this problem. It's specifically related to doing UPDATE against an fts1 or fts2 table. INSERT and DELETE both work fine. As far as I can tell, UPDATE may have never worked, or may have worked only in specific circumstances.

[sqlite] fts1 corruption debugging.

[Find attached the file I'm using to debug this.] I think I've found the difference causing this, but I don't understand why it matters. It all should apply to fts2, the code in question didn't change in a way likely to change this. When an insert is done against an fts1 table, index_insert()

[sqlite] Re: fts1 corruption debugging.

http://www.sqlite.org/cvstrac/tktview?tn=2166 I'm probably not going to be back on this until Monday or Tuesday, unfortunately. -scott On 1/12/07, Scott Hess <[EMAIL PROTECTED]> wrote: [Find attached the file I'm using to debug this.] I think I've found the difference causing this,

Re: [sqlite] Sqlite3.3.11 - No differences except for sqlite3.h and os_win.c

BTW, http://www.sqlite.org/cvstrac/chngview?cn=3596 fixed a sort of nasty fts1/2 bug. Just in case you were looking for something else to pick up :-). Thanks, scott On 1/23/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Sandeep Suresh <[EMAIL PROTECTED]> wrote: > I got

Re: [sqlite] Sqlite3.3.11 - No differences except for sqlite3.h and os_win.c

On 1/24/07, Ralf Junker <[EMAIL PROTECTED]> wrote: Hello Scott Hess, Btw: Are there any chances that ticket #2183 could make it into 3.3.12? It is about a nasty crash which happens to FTS2 compiled with SQLITE_OMIT_SHARED_CACHE when many records are inserted. Even though FTS2 is n

Re: [sqlite] Appropriate uses for SQLite

I'd be _extremely_ leery of doing this on a network store. In theory, it should work just fine, but bridging theory and practice may very well cost you many sleepless nights. sqlite is in many ways easier than mysql, but mysql isn't _that_ much harder to use, and it just won't have this class

Re: [sqlite] Appropriate uses for SQLite

On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] wrote: > The problem is, not many network filesystems work correctly. I'm sure someone knows which versions of NFS have working file locking, at least under Linux. I doubt it is this easy. You need to line up a bunch of

[sqlite] Bug using fts tables in attached databases.

http://www.sqlite.org/cvstrac/tktview?tn=2219 ATTACH DATABASE 'test2.db' AS two; CREATE VIRTUAL TABLE two.t2 USING fts2(content); will put t2_content, t2_segments, and t2_segdir in database 'main' rather than database 'two'. In many cases everything will appear to work, because the tables

[sqlite] Re: Bug using fts tables in attached databases.

Fix is in http://www.sqlite.org/cvstrac/tktview?tn=2219 [This covers both fts1 and fts2.] -scott On 2/6/07, Scott Hess <[EMAIL PROTECTED]> wrote: http://www.sqlite.org/cvstrac/tktview?tn=2219 ATTACH DATABASE 'test2.db' AS two; CREATE VIRTUAL TABLE two.t2 USING fts2(c

Re: [sqlite] Re: Re: how to get field names of empty tables ?

I think only the dot commands are special (.help, etc). Everything else is fair game. Best reference for what you can feed a prepare or exec is http://www.sqlite.org/lang.html . -scott On 2/23/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: > Igor

Re: [sqlite] Is there an inverse for .import?

On 3/9/07, Gunnar Roth <[EMAIL PROTECTED]> wrote: Anderson, James H (IT) schrieb: > I need to "export" a table to a file in the same format as used by > .import, but I don't see any such cmd. Am I missing something, or does > such a cmd just not exist? Maybe its dumb but its called .dump ;-)

Re: [sqlite] Is there an inverse for .import?

.dump would be .read. -scott On 3/9/07, Scott Hess <[EMAIL PROTECTED]> wrote: On 3/9/07, Gunnar Roth <[EMAIL PROTECTED]> wrote: > Anderson, James H (IT) schrieb: > > I need to "export" a table to a file in the same format as used by > > .import, but

Re: [sqlite] SQLite 2 to SQLite 3 - Varchar sizes

On 3/9/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: I'm looking into ways of changing the schema type name when I do my SQLite2->SQLite3 conversion. Most likely I'll have to pipe the .dump from the SQLite2 db through a program to replace 'varchar' with 'text'... I'm working on that now! You

Re: [sqlite] journal - "Unable to open the database file"

On 3/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote: There's also this - CreateFileTransacted(): http://msdn2.microsoft.com/en-us/library/aa363859.aspx Doesn't this require Vista? -scott - To unsubscribe, send email

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

On 3/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Ion Silvestru <[EMAIL PROTECTED]> wrote: > To Ralf: > >As a side effect, the offsets() and snippet() functions stopped working, > >as they seem to rely on the presence of the full document text in the > >current implementation. > > Did you

Re: [sqlite] MingW32 help wanted

I took Makefile.linux-gcc, and made the obvious changes (there are mingw lines all over in there). EXE = .exe, SO =dll, SHPREFIX = , [that was nothing for that setting], and TCC, AR, and RANLIB set to the path to the appropriate commands from mingw. I should warn that I haven't actually built

Re: [sqlite] API enhancement

I don't see how your modified version is any better than just putting the sqlite3_bind_int() inside the loop. You've superficially lifted some code out of the loop, but sqlite3_step() is going to have to go through and bind all of the "pointer bound" variables in your suggested API, so it won't

Re: [sqlite] Index creation

An interesting approach would be to use some sort of async I/O facility to implement read-ahead. Short of that, I have found that in some cases, on some operating systems, implementing explicit read-ahead buffering for fts2 segment merges improves performance when the disk caches are cold.

Re: [sqlite] Re: FTS does not support REPLACE

Thanks for the concise report. I'm going to take a look at this today, to see if it's an fts1/2 problem. If it's _not_, I'll still look at it, but perhaps with less eventual success :-). -scott On 4/9/07, Paul Quinn <[EMAIL PROTECTED]> wrote: Very simple to replicate: CREATE VIRTUAL

Re: [sqlite] Re: FTS does not support REPLACE

On 4/10/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: I checked the code and conflict handling mechanisms (OR ERROR, OR ABORT, OR REPLACE) do not apply to virtual tables. Something to think about anyhow... Do we want conflict handling for FTS (and other virtual modules)? I think OR REPLACE

Re: [sqlite] Re: FTS does not support REPLACE

On 4/11/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: On Tue, 2007-04-10 at 09:26 -0700, Scott Hess wrote: > On 4/10/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: > > I checked the code and conflict handling mechanisms (OR ERROR, > > OR ABORT, OR REPLACE) do

Re: [sqlite] MySQL vs. SQLite

On Tue, Jun 1, 2010 at 2:37 PM, Simon Slavin wrote: > On 1 Jun 2010, at 7:11pm, Israel Lins Albuquerque wrote: >> About that future release functionality. Will be possible to know whats >> temporary index are created? >> Using that information will be easy to know what

Re: [sqlite] Is it possible to generate tf-idf matrix from the FTS3 table?

On Sat, Jun 5, 2010 at 10:29 AM, Han-Teng Liao wrote: >    I intend to use my existing datasets stored in sqlite3 database for some > linguistic analysis for Chinese language. After I have successfully > installed and run the FTS3 Extension and ICU Extension, I am curious

Re: [sqlite] unexpected large journal file

On Fri, Jun 18, 2010 at 5:24 PM, Eric Smith wrote: > Jay A.  Kreibich wrote: >> Yes.  Hence the "and this is the important part" comment.  Most of >> the time when people are building billion-row files, they're building >> a new DB by importing a static source of data.  If

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Eric, You should reduce your demonstration case to something you'd be willing to post the code for. Probably using synthetic data (preferably generated data). There's something missing in the thread right now, and it's unlikely to be exposed by random shots in the dark. -scott On Tue, Jun

Re: [sqlite] Database corruption on Linux ext3

On Tue, Jul 13, 2010 at 8:24 PM, Simon Slavin wrote: > It might be useful to figure out whether we're aiming for > detection or correction. By 'correction' I don't mean recovery > of all information, I mean restoring the database to some state > it was in just after a

Re: [sqlite] Database corruption on Linux ext3

On Tue, Jul 13, 2010 at 10:03 PM, Scott Hess <sh...@google.com> wrote: > On Tue, Jul 13, 2010 at 8:24 PM, Simon Slavin <slav...@bigfraud.org> wrote: >> It might be useful to figure out whether we're aiming for >> detection or correction.  By 'correction' I don't mean rec

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

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. Please review it. >> >> This is upstreaming of >>

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

2010, at 12:44 PM, Scott Hess wrote: > >> On Thu, Jul 29, 2010 at 10:05 PM, Dan Kennedy >> <danielk1...@gmail.com> wrote: >>> On Jul 30, 2010, at 8:39 AM, Paweł Hajdan, Jr. wrote: >>>> I'm attaching a suggested patch to verify nu

Re: [sqlite] Optimizing Songbird

On Mon, Aug 2, 2010 at 11:11 AM, Simon Slavin wrote: > On 2 Aug 2010, at 7:06pm, Richard Hipp wrote: >> Most of the slow queries seem to be "SELECT count(*) FROM ".  Such >> queries have to visit every row in the table (in order to count the rows) >> and thus get

Re: [sqlite] Optimizing Songbird

[Sorry for the mis-fire.] On Mon, Aug 2, 2010 at 11:11 AM, Simon Slavin wrote: > On 2 Aug 2010, at 7:06pm, Richard Hipp wrote: >> Most of the slow queries seem to be "SELECT count(*) FROM ".  Such >> queries have to visit every row in the table (in order to count the

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

Pawel, You should probably pull the current SQLite code and make sure the patch even applies, and if not, check to make sure that the problem hasn't already been fixed. ext/fts3 should no longer have the flaw in question, as that code was heavily rewritten. Chromium's SQLite was last synced

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

This bug comment describes the problem: http://code.google.com/p/chromium/issues/detail?id=15261#c20 excerpt: > Apparently the problem is caused by tolower(), whose behavior is affected by > current > locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than 'i', > because >

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: > FTS3 only searches full terms/words by default, but I think if I built a > custom > tokenizer that returned all the suffix trees for a name: FTS3 can do prefix searches, MATCH 'a*'. Also, it aimed to support multiple

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

Hipp <d...@sqlite.org> wrote: > FTS3 updated here:  http://www.sqlite.org/src/ci/b8b465ed2c > > On Fri, Aug 6, 2010 at 2:24 PM, Scott Hess <sh...@google.com> wrote: > >> This bug comment describes the problem: >>   http://code.google.com/p/chromium/issu

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts <vieuxt...@gmail.com> wrote: > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess <sh...@google.com> wrote: >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts <vieuxt...@gmail.com> wrote: >>> FTS3 only searches full terms/word

Re: [sqlite] [PATCH] cache preloading

On Mon, Aug 16, 2010 at 7:58 PM, Shawn Wilsher wrote: > On Mon, Aug 16, 2010 at 5:13 PM, Paweł Hajdan, Jr. > wrote: >> Is it something you'd like to include in SQLite? If so, does the patch need >> any adjustments before that's possible? > I'm

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

I'm not sure Chromium has any fts1 databases, I think the original patch was applied there for completeness. The change from fts2 to fts3 has been made in the history system, but it only applies to new data, and hasn't yet rolled out to stable. So we wouldn't be able to even start to cease using

Re: [sqlite] Feature request: copying vacuum

On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich wrote: > On Wed, Sep 01, 2010 at 11:41:00AM -0700, Taras Glek scratched on the wall: >> Currently VACUUM takes care of sqlite-level fragmentation. Unfortunately >> it does little for fs-level fragmentation since the same file is

Re: [sqlite] Feature request: copying vacuum

On Wed, Sep 1, 2010 at 10:09 PM, Max Vlasov wrote: >> I agree with Jay - while it is tempting to have SQLite bite off >> optimizing this kind of thing, it's pretty far out of scope.  Next >> we'll be talking about running SQLite on raw partitions! >> > > Scott, thought about

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

On Wed, Sep 1, 2010 at 10:20 PM, Max Vlasov wrote: >> I wonder whether it would be possible to extend the VFS in a way that would >> make our use case possible (transferring a file handle over process >> boundary). Please note that we do it on all platforms. On POSIX we pass

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

On Mon, Oct 11, 2010 at 8:13 AM, Jay A. Kreibich wrote: > On Mon, Oct 11, 2010 at 02:08:54PM +0200, Michele Pradella scratched on the > wall: >>   Ok so the main idea it's always the same: split the DELETE to make the >> operation on less records, but do it more often. > >  

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

On Tue, Oct 12, 2010 at 8:40 AM, Drake Wilson wrote: > Just to clarify, a trigger isn't useful for this in general, because > you still have nowhere to get the function pointer from.  Above I was > worrying that the current SQLite code might be less safe than it could > be as

Re: [sqlite] Many many tables... performance wise?

Having a table with an owner_id, key, and value, with a unique index on (owner_id, key) will probably be more efficient than having a separate table per owner. Also, it will be easier to code safely, because bind parameters don't work on table names (I'm assuming you're using dynamic table names

Re: [sqlite] Full text search FTS3 of files

On Sun, Oct 17, 2010 at 11:13 PM, Dami Laurent (PJ) wrote: >>Is it possible to use FTS3 for search without storing the actual file >>contents/search terms/keywords in a row. In other words, create a FTS3 >>tables with rows that only contains an ID and populate the

Re: [sqlite] Stored procedures (was: Question about SQLite features.)

On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare wrote: > IMO, if you're implementing database logic (ie constraints and triggers) in > application code, then you're reinventing the wheel, making your package > unnecessarily complex and grossly inefficient. If you're just

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

On Fri, Dec 10, 2010 at 12:20 PM, Vander Clock Stephane wrote: > to limit the file fragmentation i want to increase the size of the > database file (with the windows API function). Consider http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html . -scott

Re: [sqlite] pragma vs select for introspection

On Mon, Dec 13, 2010 at 1:27 PM, Puneet Kishor wrote: > Wols Lists wrote: >> On 13/12/10 01:38, Darren Duncan wrote: >>> Darren Duncan wrote: Wols Lists wrote: > Dunno how well that approach translates into a relational engine, > because Pick has several very

Re: [sqlite] WAL on a separate filesystem?

On Wed, Jan 19, 2011 at 12:15 PM, Dustin Sallings wrote: > This isn't a question so much about value judgment (I've already > argued that some, though mentioning maintenance tools is helpful > there, too).  It comes down to whether reliability of SQLite itself > would be reduced

Re: [sqlite] Deleting the sqlite journal file?

On Fri, Jan 21, 2011 at 5:33 PM, Pavel Ivanov wrote: >>        (2) Are there any changes that can/should be made >> to sqlite3 so that it can identify the bogus journal in this >> scenario and discard it? > > Define the word "bogus". How should SQLite understand that the

Re: [sqlite] integer or string in fts3 table

On Mon, Jan 31, 2011 at 9:15 AM, Samuel Adam wrote: > On Mon, 31 Jan 2011 11:46:21 -0500, Samuel Adam wrote: >> On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin >> wrote: >>> In the definition given in the original post, which I

Re: [sqlite] will FTS speed up [LIKE '%foo%'] searches? or: how does iTunes do it?

On Fri, Feb 18, 2011 at 12:08 AM, David M. Cotter wrote: > so i am still left wondering if searching substrings is really any faster > using FTS. You may want to search the archives, as this has come up before. I don't recall if anyone had an inspired solution. You could

Re: [sqlite] apostrophes in strings...

You can also convert: ATTACH DATABASE x AS y KEY z to: SELECT sqlite_attach(x, y, z) where the parameters can be turned into bind arguments. Then embedded quotes won't be an issue. -scott On Sun, Feb 20, 2011 at 11:31 AM, Pavel Ivanov wrote: > I believe doubling the

Re: [sqlite] apostrophes in strings...

I believe the key is used for attaching to encrypted databases. -scott On Wed, Feb 23, 2011 at 7:15 AM, Sam Carleton wrote: > Kevin, > > Thank you, that is what I needed.  Now to statisfy my curiosity...  What > exactly is the KEY value? > > On Tue, Feb 22, 2011 at

Re: [sqlite] apostrophes in strings...

On Wed, Feb 23, 2011 at 12:50 PM, BareFeetWare <list@barefeetware.com> wrote: > On 21/02/2011, at 8:11 AM, Scott Hess wrote: >> You can also convert: >>  ATTACH DATABASE x AS y KEY z >> to: >>  SELECT sqlite_attach(x, y, z) >> where the param

Re: [sqlite] Fwd: Fwd: fts virtual table questions

On Tue, Mar 1, 2011 at 4:54 PM, Paul Shaffer wrote: > I had an API problem, and I now think that the virtual table does not need > to be created each time. I can't find documentation that covers this. I don't think there is documentation which covers this, because it's

Re: [sqlite] fts2, fts3 and stemming

On Tue, Feb 19, 2008 at 1:49 PM, Bryan Oakley <[EMAIL PROTECTED]> wrote: > My code uses fts2, and for the first time today I discovered that no > stemming appears to be going on under the covers. Googling around I > see that I need to add "tokenize porter" when creating my table. > Adding that

Re: [sqlite] fts2, fts3 and stemming

On Tue, Feb 19, 2008 at 2:08 PM, Bryan Oakley <[EMAIL PROTECTED]> wrote: > On Feb 19, 2008 3:59 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > > On Tue, Feb 19, 2008 at 1:49 PM, Bryan Oakley <[EMAIL PROTECTED]> wrote: > > > Also, I've seen references to

Re: [sqlite] Does sqlite3_prepare() clean up after itself if it fails?

On Mon, Mar 3, 2008 at 10:16 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Jerry Krinock <[EMAIL PROTECTED]> wrote: > > The Blob Example [1] contains code [2] in which, if sqlite3_prepare() > > fails, the subsequent call to sqlite3_finalize() is skipped. Is this > > OK? > > If

Re: [sqlite] Does sqlite3_prepare() clean up after itself if itfails?

On Mon, Mar 3, 2008 at 10:47 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Scott Hess <[EMAIL PROTECTED]> wrote: > > On Mon, Mar 3, 2008 at 10:16 AM, Igor Tandetnik > > <[EMAIL PROTECTED]> wrote: > >> Jerry Krinock <[EMAIL PROTECTED]> wrote

Re: [sqlite] Cross DB triggers?

On Mon, Mar 3, 2008 at 3:09 PM, Jeff Hamilton <[EMAIL PROTECTED]> wrote: > I have a setup with two databases that have tables which refer to each > other. I'd like to create triggers to handle cleanup when items are > deleted from one database that are referred to from the second > database.

Re: [sqlite] Does an sqlite3_get_table() results set get updated.

On Mon, Mar 10, 2008 at 6:15 PM, Neville Franks <[EMAIL PROTECTED]> wrote: > I'm displaying tree's which are built from SQL queries and I ideally > want the tree control to reflect updates to the underlying tables > without have to do queries all over again and rebuild the trees from >

Re: [sqlite] Does an sqlite3_get_table() results set get updated.

On Tue, Mar 11, 2008 at 1:30 PM, Neville Franks <[EMAIL PROTECTED]> wrote: > Wednesday, March 12, 2008, 3:50:46 AM, you wrote: > SH> On Mon, Mar 10, 2008 at 6:15 PM, Neville Franks <[EMAIL PROTECTED]> > wrote: > >> I'm displaying tree's which are built from SQL queries and I ideally > >>

Re: [sqlite] Major memory leak

If the maximum memory footprint is too large, then you should arrange to have a smaller memory footprint. For instance, you can use PRAGMA cache_size to reduce the footprint there, use PRAGMA temp_store to make sure you aren't storing temporary tables in memory, call sqlite3_release_memory() to

Re: [sqlite] Count(1)

A little bit more info: SELECT COUNT(*) is implemented as a full table scan, so SQLite is visiting every row in the table, which will get slower and slower as the table gets bigger and the database fragments. This differs from many database engines (which implement an optimization for this)

Re: [sqlite] Virtual table used to query big external database

You can _generally_ work around this kind of problem using sub-selects. So instead of: SELECT x FROM fts_table WHERE fts_table MATCH "y" OR fts_table MATCH "z"; You might write: SELECT x FROM fts_table WHERE rowid IN (SELECT rowid FROM fts_table WHERE fts_table MATCH "y") OR rowid IN (SELECT

Re: [sqlite] Count(1)

d no > join or where clause). > > Thanks, > > Sam > > > > On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > > > A little bit more info: SELECT COUNT(*) is implemented as a full > > table scan, so SQLite is visiting every row

Re: [sqlite] Count(1)

the savings would be > 10x > > Regards -- Noah > > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess > Sent: Friday, April 04, 2008 9:15 AM > To: General Discussion of SQLite Database > Subject: Re:

Re: [sqlite] how to select uncomitted rows?

Until the data is committed, it's not really in the database. If you crash, it will be rolled back. So if it's really important to know what data has been written to the database but not committed, why don't you just track what you're writing to the database in an in-memory data structure of

Re: [sqlite] how to select uncomitted rows?

to do much. -scott On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > The reason I did not keep track in a seperate table was because I wanted to > do it using triggers. But triggers don't trigger until commit. > > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess

Re: [sqlite] how to select uncomitted rows?

fter > being committed, would this work? Would the temporary trigger in the router > connection actually trigger? I guess I need to try this out. > > This is my own home project. I am my own boss. Once I have my design > figured out I will post it for analysis. > > Thanks, >

Re: [sqlite] how to select uncomitted rows?

EGIN; A) sqlite> BEGIN; A) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection a'); B) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection b'); B) SQL error: database is locked -scott On Thu, Apr 17, 2008 at 5:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > What will

Re: [sqlite] how to select uncomitted rows?

Grr. Copy/paste error. The create statement was: CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, config TEXT); On Thu, Apr 17, 2008 at 5:20 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > Just to be clear on "try it out", I mean something like the following, > where A)

Re: [sqlite] multiple writers for in-memory datastore

If you create a file on disk and set PRAGMA synchronous = OFF, you should get pretty close to the performance of a shared in-memory database on most modern desktop operating systems - maybe close enough that you won't care to do anything beyond that. If you further look at the recent

Re: [sqlite] String is changing after inserting into database

My experience is that this kind of problem tends towards a he-said, she-said type of thing, and what happens in the end is that everyone is correct from their perspective, but there was some little bit of non-obvious thing that made one party's understanding of the encoding slightly different from

Re: [sqlite] trigger and attached db

You cannot create a trigger which crosses between databases because the system cannot know that the database will be attached when the trigger fires. Triggers are part of the consistency guarantee, so this is bad. It would be interesting to have something like "CREATE TEMPORARY TRIGGER ... "

Re: [sqlite] Writer Starvation Question

Weirdo response, but ... make sure you have HAVE_USLEEP defined. We kept seeing something similar, and kept looking at the code for sqliteDefaultBusyCallback(), and the code looked right, but the problem remained, until we eventually started questioning every assumption. At which point we found

Re: [sqlite] FTS search negative term syntax

Interesting point. This seems like the kind of thing that could be implemented in the existing fts codebase without involving a version change. It also may be more general than just hyphenated words, for instance $12.50 might be more usefully translated as the phrase search "12 50" than all

Re: [sqlite] FTS search negative term syntax

On Thu, May 1, 2008 at 1:41 AM, Ralf Junker <[EMAIL PROTECTED]> wrote: >>Fair warning, though: It's not entirely clear that the fts search >>syntax should aim to hew too closely to consumer-oriented search >>syntax. > > Interesting point, too. Up to now, I always perceived the FTS search syntax to

Re: [sqlite] Problems with SQLITE_OMIT_xxx

Something you can do in such cases is instead of putting a comment at the beginning of the line, change the define: -DXSQLITE_OMIT_TRIGGER\ Yeah, so there's a chance that you've just randomly changed the define to enable Scary Feature X, if that's really a concern add more

Re: [sqlite] PRAGMA journal_mode not implemented?

On Tue, May 6, 2008 at 7:41 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: >> Since PERSIST is likely to be faster than DELETE on >> most platforms, is there ever a reason *not* to use >> it? > > In PERSIST mode, you have two files associated with your database > instead of one. Whenever you

Re: [sqlite] Indexing virtual tables

I'm not quite clear on your question - why wouldn't you just create any indices you need within the virtual-table implementation itself? Sort of like how fts uses SQLite tables to implement data-storage for the full-text index. -scott On Mon, May 5, 2008 at 10:13 AM, Aladdin Lampé <[EMAIL

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

On Tue, May 13, 2008 at 4:51 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > The currently documented behavior of sqlite3_close() is that when it > called on a database connection that has unfinalized prepared > statements is to return SQLITE_BUSY and fail to close the connection. > The rational

Re: [sqlite] FTS3 Question

Should the 'data' table be joinable with the 'category' table in some way? Are you trying to match rows in 'data' which contain _all_ of the 'query' items from 'category', or which contain _any_ of the 'query' items from 'category'? Do you mean to have a WHERE clause or anything on what you're

Re: [sqlite] FTS3 Question

query' items from 'category'? ' > > > > -----Original Message- > On Behalf Of Scott Hess > > Should the 'data' table be joinable with the 'category' table in some > way? Are you trying to match rows in 'data' which contain _all_ of > the 'query' items from 'category', or which contain

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

On Tue, May 20, 2008 at 1:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > In Bram's case, he was surprised that the last-insert-rowid changed > because he is not thinking about how FTS works behind the scenes. And > this is reasonable. There is a lot of magic in FTS that programmers > are

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Bram, I don't think there is a solid workaround of the form you suggest. The last_insert_rowid() you're getting is for the segment in the internal segdir table, and is unrelated to the rowid of the overall virtual table. What you should be able to do is to call last_insert_rowid() IMMEDIATELY

Re: [sqlite] How can I count the number of times a word present in text indexed with FTS 3?

There is not currently any support for this in fts. Put another way, you can count the words like you'd count them without fts :-). Long-term, I do plan to add support for figuring out some meta-information about tokens across the index, but there's nothing really driving that at this time.

[sqlite] Recent minor fts3 changes.

Since fts3 development is sort of sporadic, I figure it's worth a heads-up about a minor feature I've recently checked in. -scott *** http://www.sqlite.org/cvstrac/chngview?cn=5417 optimize() function. This takes all segments in the fts3 index and merges them into a single segment. As a side

Re: [sqlite] FTS index size

On Fri, Jul 18, 2008 at 12:54 AM, Jiri Hajek <[EMAIL PROTECTED]> wrote: > from what I have read about FTS3, it stores the original data as well as the > index needed for fast full-text access. Therefore, in several posts here it > was recommended to use two tables joined one-to-one in case it's

Re: [sqlite] FTS index size

Again, you've given a relatively broad description of what you're trying to do. I could make up a bunch of stuff and answer my own question, but you'd probably rather than I considered the problem _you_ are having. It sounds like you've actually committed code to do this - post the SQL CREATE

<    1   2   3   4   5   >