[sqlite] How can xBestIndex discern 'a' = col COLLATE NOCASE vs 'a' = col

I have a virtual table, but I cannot figure out how to discover the collating sequence of a query expression passed to xBestIndex. As far as I can tell, these three statements ... SELECT * FROM tab WHERE col = 'val'; SELECT * FROM tab WHERE col = 'val' COLLATE BINARY; SELECT * FROM tab WHERE col

Re: [sqlite] Bug or Problem in Sqlite3.exe when importing UTF8

Jens Hantschel wrote: > I am facing problems when importing a file to a database-table with > sqlite3.exe and ".import"-parameter. > > The file is encoded in UTF8 an every time i import the file in the first > column of the first row there is a special character >

Re: [sqlite] How to make this calculation in VIEW

Thank you, Simon and Owen. I have tried and both solutions work equally well.  I have actually used C code to make the calculation as I pull out the data. But to help further my understanding of SQL/SQLite, can anyone comment on potential performance differences of the 2 SQL solutions. CREATE

Re: [sqlite] creating a sqlite db on raw disk ?

On 19 Nov 2010, at 7:04am, Yang wrote: > when I create a db on a file system, I guess a query process Wait ... are you talking here about creating a new database file or querying one what already exists ? > has to go > through 2 levels of seeks ? > first sqlite finds the B-tree node that

Re: [sqlite] Corrupted database with duplicated primary keys

Due to attach limits this is the part 001 of the file! - "Israel Lins Albuquerque" escreveu: > Attached has a database corrupted. > We use the version 3.6.23.1 in wince. > > the command: > pragma integrity_check; > > show many errors and > > Duplicate

Re: [sqlite] Corrupted database with duplicated primary keys

Due to attach limits this is the part 002 of the file! - "Israel Lins Albuquerque" escreveu: > Attached has a database corrupted. > We use the version 3.6.23.1 in wince. > > the command: > pragma integrity_check; > > show many errors and > > Duplicate

Re: [sqlite] UPDATE during SELECT

"Igor Tandetnik" writes: > Nikolaus Rath wrote: >> I understand that running INSERT or DELETE during an active SELECT query >> can get me into trouble. But is it safe to run (in pseudocode): >> >> for value in

Re: [sqlite] UPDATE during SELECT

> for value in "SELECT main_column IN mytable": >   UPDATE mytable SET other_column='foobar' WHERE main_column=value Exactly this sequence is safe. Things can go nuts in case if you have index on other_column and you do something like this: for value in "SELECT main_column IN mytable WHERE

Re: [sqlite] UPDATE during SELECT

Nikolaus Rath wrote: > "Igor Tandetnik" writes: >> Nikolaus Rath wrote: >>> I understand that running INSERT or DELETE during an active SELECT query >>> can get me into trouble. But is it safe to run (in pseudocode): >>> >>> for value

Re: [sqlite] Custom collating sequences and performance

On 11/18/10 8:57 PM, "Dan Kennedy" wrote: On 11/19/2010 05:22 AM, Duquette, William H (316H) wrote: > On 11/18/10 2:16 PM, "Drake Wilson" wrote: > > Quoth "Duquette, William H (316H)", on > 2010-11-18 14:08:10 -0800:

Re: [sqlite] creating a sqlite db on raw disk ?

Thanks for your detailed explanation. for your first question: I mean creating a new database file. since you asserted " >> innodb allows creating a db on a raw disk partition, can we do the >> same on sqlite? > > No. You need a file system of some kind. Or, at least, your operating > system

[sqlite] Bug or Problem in Sqlite3.exe when importing UTF8

Hello, I am facing problems when importing a file to a database-table with sqlite3.exe and ".import"-parameter. The file is encoded in UTF8 an every time i import the file in the first column of the first row there is a special character occuring in the field. I can't use encoding ASCII

[sqlite] When is corruption acceptable?

I have an application that was writing to a sqlite db until we ran out of disk space. The app failed to store data, commit, etc... and then was killed. I've got the data moved off to the side after it broke. integrity check says this: *** in database main *** Main freelist:

Re: [sqlite] When is corruption acceptable?

Did you try and do an .export of your database? That would be my first thing to try. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Dustin Sallings Sent:

Re: [sqlite] When is corruption acceptable?

>        1. When is it acceptable for sqlite to leave a corrupt database that > can't be used? It's never acceptable. SQLite specifically written and tested to manager "out of disk space" errors appropriately without database corruption. >        I've got the data moved off to the side after it

[sqlite] problem reading a row of data

I'm trying to use a select statement to read a row of data from a DB. I have created the db myself, using another program that uses sqlite, and have verified (using a gui SQlite db browser) that the file does in fact have data in it (2 tables, both about 1.3 million rows, the file is around 150Mb

Re: [sqlite] problem reading a row of data

Try showing us your code...sounds like you're doing something wrong if the query works elsewhere. Here's my simple example that does something similar to what you describe: #include #include "sqlite3.h" main() { sqlite3 *db; sqlite3_stmt *stmt; char *errmsg=NULL;

[sqlite] FTS3 snippets() grouping

A question: Is is possible to group the data from the snippets() column in a result? The query would look something like the following (which doesn't work): >> SELECT snippet(search, '', '', '...') as extract, count() as count FROM >> search WHERE content MATCH 'search term' GROUP BY extract

Re: [sqlite] problem reading a row of data

Thanks God it's Friday. I'm an utter and complete idiot. I was using the wrong filename. Misspelled it by a single character. I'm going to pretend this never happened ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] joining two sequences?

Hello, Given two tables describing sequences of key value pairs, what would be a reasonable way to join them? For example, assuming two table foo and bar with identical structure: create temporary table foo ( key integer not null, value text not null, constraint

Re: [sqlite] FTS3 snippets() grouping

On Nov 19, 2010, at 9:46 PM, Matthew Leffler wrote: > A question: Is is possible to group the data from the snippets() column in a > result? The query would look something like the following (which doesn't > work): > >>> SELECT snippet(search, '', '', '...') as extract, count() as count

Re: [sqlite] FTS3 snippets() grouping

I thought of that but I get an error with that query: >> unable to use function snippet in the requested context Thanks! Matthew On Nov 19, 2010, at 2:08 PM, Petite Abeille wrote: > > On Nov 19, 2010, at 9:46 PM, Matthew Leffler wrote: > >> A question: Is is possible to group the data from

[sqlite] how can I use a larger cache ? "Application Defined Page Cache." ??

I read from the docs that by setting "PRAGMA cache_size = Number-of-pages;" (http://www.sqlite.org/pragma.html#pragma_cache_size) I can use a larger cache, but the same paragraph mentions that how much is used is actually "at the discretion of the application defined page cache" I do not set

Re: [sqlite] FTS3 snippets() grouping

On Nov 19, 2010, at 10:14 PM, Matthew Leffler wrote: > I thought of that but I get an error with that query: > >>> unable to use function snippet in the requested context > Pesky function :P Then, if everything else fails, you could write it down in a temp table, and group that sigh...

Re: [sqlite] When is corruption acceptable?

On Nov 19, 2010, at 12:17, Pavel Ivanov wrote: >>1. When is it acceptable for sqlite to leave a corrupt database that >> can't be used? > > It's never acceptable. SQLite specifically written and tested to > manager "out of disk space" errors appropriately without database > corruption.

Re: [sqlite] creating a sqlite db on raw disk ?

On Thu, Nov 18, 2010 at 11:04:02PM -0800, Yang scratched on the wall: > innodb allows creating a db on a raw disk partition, can we do the > same on sqlite? Not out of the box, but it could be done by writing a VFS driver. It is an idea I've toyed with, but I don't really have the low-level

Re: [sqlite] creating a sqlite db on raw disk ?

On Fri, Nov 19, 2010 at 10:09 AM, Jay A. Kreibich wrote: > On Thu, Nov 18, 2010 at 11:04:02PM -0800, Yang scratched on the wall: > > > innodb allows creating a db on a raw disk partition, can we do the > > same on sqlite? > > Not out of the box, but it could be done by writing

Re: [sqlite] threads and last_insert_rowid()

On Tue, Nov 16, 2010 at 09:35:21AM -0500, Pavel Ivanov scratched on the wall: > But as I see in SQLite sources sqlite3_exec does acquire > connection's mutex, so nothing can be executed in between statements. > Thus if you execute this line as one call to sqlite3_exec then it > won't suffer from

Re: [sqlite] joining two sequences?

This should return a the equivalent keys in the two maps. The basic idea is to compare the values in each key in foo(left outer join foo) with the values for each key in bar where there are any matching values(left outer join bar) and only select those with a complete match( inner join). Not

[sqlite] page cache vs OS cache

I wonder why we need page cache, since we already have OS file cache. in other words, if we simply set OS cache to be very large, would it have the same effect as page cache? thanks Yang ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] EXTERNAL:Re: Strange Corruption

The idea that integrity_check is 100% foolproof is wishful thinking. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010