[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules (revisited)

2015-12-09 Thread a...@zator.com
Hi list: In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change the search engine from FTS3/4 modules to FTS5, by means off: 1. Define the directive #define SQLITE_ENABLE_FTS5 1 2.- Create the table: CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread R Smith
On 2015/12/09 10:35 PM, Wade, William wrote: > I may be reading different documentation (or perhaps just misreading it). > > https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, > which applies to the original post) "If no ROWID is specified on the insert > ... [the] usual

[sqlite] Obscure peculiarity with 3.9.2

2015-12-09 Thread R Smith
On 2015/12/09 10:55 PM, Richard Hipp wrote: > On 12/9/15, R Smith wrote: >> One of our systems suddenly started to play up after upgrading to 3.9.2 >> from 3.8.8 (so I am not sure exactly when the oddity was introduced). >> >> SQLite: >> v 3.9.2 linked on WIndows (various versions) in a 32 bit

[sqlite] Obscure peculiarity with 3.9.2

2015-12-09 Thread R Smith
One of our systems suddenly started to play up after upgrading to 3.9.2 from 3.8.8 (so I am not sure exactly when the oddity was introduced). SQLite: v 3.9.2 linked on WIndows (various versions) in a 32 bit application via the exact 32bit DLL published on the downloads page on sqlite.org. The

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 9:10pm, David Baird wrote: > My cocnern is that: the database engine shouldn't > needlessly waste/discard perectly good chunks of rowids Please don't think of them as 'chunks'. There is no order to the numbers it picks. An alternative way to think of the AUTOINCREMENT

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
On Dec 9, 2015 7:14 PM, "Simon Slavin" wrote: > > > On 9 Dec 2015, at 9:10pm, David Baird wrote: > > > My cocnern is that: the database engine shouldn't > > needlessly waste/discard perectly good chunks of rowids > > Please don't think of them as 'chunks'. There is no order to the numbers it

[sqlite] System.Data.SQLite version 1.0.99.0 released

2015-12-09 Thread Joe Mistachkin
System.Data.SQLite version 1.0.99.0 (with SQLite 3.9.2) is now available on the System.Data.SQLite website: https://system.data.sqlite.org/ Further information about this release can be seen at: https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki Please post on the

[sqlite] Setting locale for ORDER BY

2015-12-09 Thread Cecil Westerhof
I want that ? comes after e, not after z. When using Python I get the right ordering of ORDER BY with the following statement: locale.setlocale(locale.LC_ALL, 'en_GB.UTF-8') but I am now using Java. I am using here: Locale.setDefault(new Locale("en_GB.UTF-8")); But that does change the

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 8:35pm, Wade, William wrote: > https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, > which applies to the original post) "If no ROWID is specified on the insert > ... [the] usual algorithm is to give the newly created row a ROWID that is > one larger

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Wade, William
I may be reading different documentation (or perhaps just misreading it). https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, which applies to the original post) "If no ROWID is specified on the insert ... [the] usual algorithm is to give the newly created row a ROWID that

[sqlite] Making data unique

2015-12-09 Thread Andrew Stewart
Thanks Keith. This works. Andrew -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Monday, December 07, 2015 6:53 PM To: SQLite mailing list Subject: Re: [sqlite] Making data

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
> 1) Click Start etc. That won't show the debug output. Wish it was that simple. > If you want to prove your dll is working (or not > working), you should attempt to isolate it, for direct testing. Not sure what you mean with that. I know an error occurs in sqlite3.dll and hopefully the debug

[sqlite] Bug with DATETIME('localtime')

2015-12-09 Thread Vitaly Baranov
Hi, I've found a bug with using 'localtime' in functions DATETIME(), DATE(), TIME(). Platform: Windows 7. Steps to reproduce: 1. Set your system time zone as "Russia Time Zone 2, (UTC+03:00) Moscow, St. Petersburg, Volgograd)". 2. Execute the following script: SELECT DATETIME(1414267200,

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
On Dec 9, 2015 8:06 AM, "Richard Hipp" wrote: > > On 12/9/15, David Baird wrote: > > > > Looks like it's fixed, as long as I stick to new versions. *cross fingers* > > Bisecting shows that the change in behavior occurred here: > https://www.sqlite.org/src/timeline?c=56bc5ce8 > > As others have a

[sqlite] Obscure peculiarity with 3.9.2

2015-12-09 Thread Richard Hipp
On 12/9/15, R Smith wrote: > One of our systems suddenly started to play up after upgrading to 3.9.2 > from 3.8.8 (so I am not sure exactly when the oddity was introduced). > > SQLite: > v 3.9.2 linked on WIndows (various versions) in a 32 bit application via > the exact 32bit DLL published on

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
Will look at this, it is a VB6 console app and that may just do the job: http://vb.mvps.org/samples/Console/ RBS On Wed, Dec 9, 2015 at 1:03 PM, wrote: > On Windows you will get a console and standard output if you are running a > console application, and otherwise not. > > I think you need a

[sqlite] Obscure peculiarity with 3.9.2

2015-12-09 Thread J Decker
Once upon a time, journals would delete when all statements were closed. I find now to get all journals to delete you must also close all connections(opens). Figure at some point someone decided it was more optimal to leave journals around instead of recreating them on every statement (which it

[sqlite] How to see SQLite debugging information

2015-12-09 Thread David Bennett
On Windows you will get a console and standard output if you are running a console application, and otherwise not. I think you need a simple console app to call your ActiveX DLL, or find some other way. Windows GUI app and standard output do not play well together. Regards David M Bennett FACS

[sqlite] JSON1 detecting if a path exists vs. json null value

2015-12-09 Thread Richard Hipp
On 12/9/15, jeremy at copiousfreetime.org wrote: > So far it looks as if the way to distinguish between a json key existing > with a > value of null vs. not existing is to use `json_type` > > sqlite> select json_type('{"a":2,"c":[4,5,{"f":7}]}', '$.x') IS NULL; > 1 > sqlite> select

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread R Smith
On 2015/12/09 2:21 PM, David Baird wrote: > Looks like it's fixed, as long as I stick to new versions. *cross > fingers* Thanks! Just to add to my previous reply: It wasn't "fixed", since it was never before "wrong", it was merely changed in a way that happens to currently suit your needs,

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread R Smith
On 2015/12/09 2:21 PM, David Baird wrote: > On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch wrote: > > Just tried two different versions: > > SQLite version 3.8.2 2013-12-06 14:53:30 (that is bundled with my OS): has > the undesired behavior > > SQLite version 3.9.2 2015-11-02 18:31:45 (that I

[sqlite] JSON1 detecting if a path exists vs. json null value

2015-12-09 Thread jer...@copiousfreetime.org
On December 9, 2015 at 13:05:37 PM, Richard Hipp (drh at sqlite.org) wrote: >? > Also are there optimizations in place so that a column that is a json string? > is only? > parsed once if it is involved in json1 functions? For example:? >? No. The JSON parsing turned out to be so fast that such

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Clemens Ladisch
David Baird wrote: > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior Works for me. Are you sure you actually executed this actual code? guarantees that a newly inserted row

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Adam Devita
"This is on a Win7 machine. How do I bring up that console window?" 1) Click Start 2) type cmd you will see cmd.exe in the list of programs to run. 3) click on cmd.exe To run MS-excell from the command line, you can call it from the full path (check version): "C:\Program Files\Microsoft

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Dominique Devienne
On Wed, Dec 9, 2015 at 12:59 PM, Graham Holden wrote: > If you have, or create, a VB6 standalone EXE that calls SQLite, you should > be possible to get it to show stdout/stderr. > OP already explained VB code is wrapped in an ActiveX dynamically used/loaded by MS Excel. So no user-controlled

[sqlite] JSON1 detecting if a path exists vs. json null value

2015-12-09 Thread jer...@copiousfreetime.org
Hi, I?m experimenting with the json1 extension, and I?d like to confirm the proper way to detect if a key exists in the json, vs its value being null. For example: ? sqlite> select json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); ? sqlite> select json_extract('{"a":2,"c":[4,5,{"f":7}]}',

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
If all else fails it would be no problem to write a little VB6 exe that calls the ActiveX dll. Not much code should be needed in that to reproduce that crash situation. Would something like the free DOSBox do the job? Will try later. RBS On Wed, Dec 9, 2015 at 12:12 PM, Dominique Devienne

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Graham Holden
If you have, or create, a VB6 standalone EXE that calls SQLite, you should be possible to get it to show stdout/stderr. By default, such EXEs are marked as "GUI" programs: if launched from a command prompt, the prompt returns immediately and they don't have a "console". However, you can change

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Random Coder
On Wed, Dec 9, 2015 at 10:14 AM, Adam Devita wrote: > To run MS-excell from the command line, you can call it from the full > path (check version): > > "C:\Program Files\Microsoft Office\Office15\Excel.exe" You can also run "start excel" to launch the current version of Excel. Both of these are

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Dominique Devienne
On Wed, Dec 9, 2015 at 3:50 AM, Richard Hipp wrote: > On 12/8/15, Bart Smissaert wrote: > > So, what/where is that standard output channel? > SQLite does not have any facilities for debugging in a GUI on Windows. Any chance to add a way to redirect debugging / troubleshooting output to a file

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
That would be very useful and I take it wouldn't be difficult to add. RBS On Wed, Dec 9, 2015 at 7:53 AM, Dominique Devienne wrote: > On Wed, Dec 9, 2015 at 3:50 AM, Richard Hipp wrote: > > > On 12/8/15, Bart Smissaert wrote: > > > So, what/where is that standard output channel? > > SQLite

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
> The standard output is what displays on your screen when you are in a DOS box. OK, thanks, will look into that. RBS On Wed, Dec 9, 2015 at 2:50 AM, Richard Hipp wrote: > On 12/8/15, Bart Smissaert wrote: > > So, what/where is that standard output channel? > > This is on a Win7 machine. How

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
> Are you using Excel or VB6? They're very different things. I use both. Have done for many years and by now I did indeed figure out they are not the same :) > freopen etc. Thanks, will give that a try. > can step through the sqlite code in a debug build That would be great, but not sure how

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Richard Hipp
On 12/9/15, David Baird wrote: > > Looks like it's fixed, as long as I stick to new versions. *cross fingers* Bisecting shows that the change in behavior occurred here: https://www.sqlite.org/src/timeline?c=56bc5ce8 As others have a pointed out, though, the "desired" behavior is "undocumented".

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch wrote: > David Baird wrote: > > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior > > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior > > Works for me. Are you sure you actually executed this actual code? > >

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
Hi, I have a use case in which a table is being used to associate integers to unique strings. When loading the strings, it's possible there may be some redundancies, which INSERT OR IGNORE successfully ignores. However, redundant values that should have been ignored _sometimes_ have a side effect

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 12:45am, Bart Smissaert wrote: > Surely there must be simple way to dump debugging data to say a text file. Sure. Run Excel as a command rather than launching it in the Windows GUI. Simon.

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 12:45am, Bart Smissaert wrote: > Excel doesn't do much, all the real work is done by that ActiveX dll and I > have full access to that as I coded that. > Surely there must be simple way to dump debugging data to say a text file. Sure. Run your own program, not one someone

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
Excel doesn't do much, all the real work is done by that ActiveX dll and I have full access to that as I coded that. Surely there must be simple way to dump debugging data to say a text file. RBS On Wed, Dec 9, 2015 at 12:37 AM, Simon Slavin wrote: > > On 9 Dec 2015, at 12:35am, Bart

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 12:35am, Bart Smissaert wrote: > So, do I need to somehow re-direct this output to a text file? I don't think it can be done. If the actual program you're running is Excel then you're relying on Excel to do this stuff. And since Excel is a spreadsheet program and not a

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
So, do I need to somehow re-direct this output to a text file? RBS On Wed, Dec 9, 2015 at 12:32 AM, Simon Slavin wrote: > > On 9 Dec 2015, at 12:30am, Bart Smissaert > wrote: > > > So, what/where is that standard output channel? > > This is on a Win7 machine. How do I bring up that console

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 12:30am, Bart Smissaert wrote: > So, what/where is that standard output channel? > This is on a Win7 machine. How do I bring up that console window? > There is no development environment here. I am running this from Excel. Then Excel is getting the output. And since Excel is

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Bart Smissaert
So, what/where is that standard output channel? This is on a Win7 machine. How do I bring up that console window? There is no development environment here. I am running this from Excel. So, I have a standard Windows sqlite3.dll, a std_call dll (to make SQLite accessible to VB6) a .tlb file an

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Simon Slavin
On 8 Dec 2015, at 10:18pm, Bart Smissaert wrote: >> on standard output > But how this work in concrete steps? Does code have to be added to SQLite > to write for example to a text file? SQLite sends text to the standard output channel while your program is making SQLite calls. You should be