Re: [sqlite] Affinity Performance - Non Issue?

On 15 May 2019, at 2:37am, D Burgess wrote: > I have a SQLite database that was ported from mysql. Most of the > column definitions remain unchanged. > There is lots of CHAR and VARCHAR definitions and contrary to Simon's > response they mean what they say. No problem with SELECTing an existing

Re: [sqlite] Affinity Performance - Non Issue?

Hi Stephen, I have a SQLite database that was ported from mysql. Most of the column definitions remain unchanged. There is lots of CHAR and VARCHAR definitions and contrary to Simon's response they mean what they say. 90+ % of the data is fixed length CHAR. (codes and fixed labels). When the databa

Re: [sqlite] Affinity Performance - Non Issue?

On 14 May 2019, at 5:50pm, Stephen Chrzanowski wrote: > I've seen it grumbled about before about giving a field a property of CHAR, > and have seen the correction that it should be TEXT. > I understand that SQLite doesn't

Re: [sqlite] Instr(x, y, z) ?

Thanks for that, will study it. I have been fiddling and found a reasonable solution. It has to do with the CTE to display a folder structure as posted before: with folderpath(id, rightmost_folder_id, parent_id, path, rank, cnt) as (select id, id, parent_id, name, rank, 0 from folders union all se

Re: [sqlite] Odd exception when creating a connection object

Barry Roberts wrote: > > InvalidOperationException: code = Misuse (21), message = > System.Data.SQLite.SQLiteException (0x87EF): bad parameter or other API > misuse > Are you using multiple AppDomains? Do you have other threads in the process accessing SQLite via its native API while the

Re: [sqlite] Instr(x, y, z) ?

Well, one way is that you can make a sub-query that uses a recursive CTE to find it. I've got something that appears to work, but it seems overly convoluted, so I'm going to feel embarrassed when someone posts a simple elegant version in a couple minutes. Say for example you have create table

[sqlite] Instr(x, y, z) ?

Is it possible with the existing SQL core string functions to find the position of occurrence z of string y in string x? The standard Instr function only does this for the first occurrence, but I would like to specify the second, third, fourth etc. occurrence of the specified string. As this is wit

Re: [sqlite] Problem with REAL PRIMARY KEY

It seems that this example now works as expected: CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0)); INSERT INTO t1(c1) VALUES (0X7ff);; SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1); I guess that this test case triggered the same issue as the one I later reported in the email "S

[sqlite] [Re] Odd exception when creating a connection object

Hi, I have just taken a look at the source code between the 1.0.107.0 and 1.0.110.0 drivers, and there are a lot of changes in the SQLiteLog.Initialize code area, and how the SQLiteConnection now calls into it. We are seeing more of the same exceptions thrown during testing with 110 (we never h

[sqlite] Affinity Performance - Non Issue?

Hey all; I've seen it grumbled about before about giving a field a property of CHAR, and have seen the correction that it should be TEXT. I understand that SQLite doesn't really "care" what the contents of the field is, but, just out of curiosity, is there a kind of performance hit on using CHAR

[sqlite] PRAGMA reverse_unordered_selects=true results in row not being fetched

Hi everyone, I found a curious bug, which I could reproduce only with a very specific statement sequence: PRAGMA reverse_unordered_selects=true; CREATE TABLE t1 (c0, c1); CREATE TABLE t2 (c0 INT UNIQUE); INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL); INSERT INTO t2(c0) VALUES (1); SELECT 1, NUL

Re: [sqlite] CTE to Get Path In a Tree

Thanks, that works perfect indeed. RBS On Tue, May 14, 2019 at 3:23 AM Keith Medcalf wrote: > > That should of course be: > > with folderpath(id, rightmost_folder_id, parent_id, path, rank) > as ( > select id, id, parent_id, name, rank > from folders > union all > sel