[sqlite] MMIO and VFS Obfuscation

Hi there, We use a vfs-based obfuscation system using the old xRead and xWrite methods.   I'm wondering if these can be adapted to work with the new memory-mapped i/o functionality in a way that still has the advantages of memory-mapped i/o? The current system does in-place decryption on read

Re: [sqlite] Avoiding table scans with complex queries

rg Sent: Friday, May 31, 2013 4:11 AM Subject: Re: [sqlite] Avoiding table scans with complex queries Paul Vercellotti wrote: > I've got a very slow query that seems to be oh-so-close to running > quickly, and it seems to come down to handling of OR operators. > When the query has one OR crit

[sqlite] Avoiding table scans with complex queries

Hi there, I've got a very slow query that seems to be oh-so-close to running quickly, and it seems to come down to handling of OR operators.  When the query has one OR criteria, it uses the correct indexes, but when there are two, it does a table scan, which in this case brings the query

[sqlite] Slowness with FTS Queries and non-FTS tables

Hi All, We've got some trouble with FTS4 queries taking too long in which we're looking for a subset of matching records in the FTS table, as narrowed by a non-FTS table. CREATE TABLE metadata( key AS INTEGER PRIMARY KEY, sectionID AS INTEGER ); CREATE INDEX sectionIdx on

[sqlite] SQLite Shell with Custom FTS Tokenizer

Hi there, We're using a custom FTS tokenizer in our database.  We'd like to use the SQLite shell to debug our database, but it can't deal with our FTS tables because the custom tokenizer isn't registered. Does someone have a clever way of getting a custom tokenizer into a build of the

[sqlite] Restrictions on JOINs with FTS tables

Hi there, I'm wondering if someone could help me understand the restrictions of queries that mix regular tables with FTS tables. Let's say you've got the following two tables, which have related records: CREATE TABLE indexes(recID int, metadata1 int);  CREATE VIRTUAL TABLE texts USING

[sqlite] FTS Find Tokens at Record Start

Hi there, I couldn't find this from the documentation: using FTS, how do you match records that contain certain tokens beginning at the start of the record (or any token position for that matter). For example, I want to match records that start with "Four score and seven years ago" but not

Re: [sqlite] Substring Search Across Many Columns

ary 25, 2013 12:01 PM Subject: Re: [sqlite] Substring Search Across Many Columns -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 25/01/13 11:30, Paul Vercellotti wrote: > I'm trying to match a substring (case-insensitive) across multiple > columns and want it to go fast; it's very slow

[sqlite] Substring Search Across Many Columns

Hi there, I'm trying to match a substring (case-insensitive) across multiple columns and want it to go fast; it's very slow to do a query like '... WHERE name LIKE "%fish%" OR desc LIKE "%red%" OR title LIKE "%soup%"...'  Will creating a composite index that has all those columns speed up a

Re: [sqlite] FTS substring behavior

te AFAIK, FTS doesn't support substring search. I also tried to edit FTS to find substring by changing simple tokenizer. It was worked partially, but not a good solution to use generally. 2012/11/9 Dan Kennedy <danielk1...@gmail.com> > On 11/09/2012 01:49 AM, Paul Vercellotti wrote: >

[sqlite] FTS substring behavior

Hi there, I wanted to clarify if FTS could provide any optimization for substring matches like '*ion*' or similar? That is, does it only scan the token index for matching tokens to locate the main table records that contain those tokens, or does it do a full table scan of the main table?

[sqlite] Getting query results as new records are added

Hi there, I'm wondering if there's a way to get "incremental" results to a query after it's started, that includes new records added after the query began? That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may

[sqlite] SQLite Encryption Extension Performance?

Hi there, We are considering using the SQLite Encryption Extension in one of our products, and are wondering what the performance characteristics of it are?   Does the encryption algorithm affect performance?   Any stats on this you might have would be useful. Thanks! -Paul

[sqlite] BLOBs' affect on DB performance

Hi there, I'm wondering how larger BLOBs in a database affect performance of accessing the non-blob data. We've got a database with potentially a few million records in the main tables (of just strings and ints), but joined to that data set we want to store up to thousands (maybe 75000 max)

[sqlite] Foreign keys and Temp Tables

Hi there, Are there any restrictions in SQLite on foreign key references in temporary tables? Is referential integrity enforced between temp tables and persistent ones? (That is, does the referential integrity checking take into account key references in a temp table to a regular table's