Re: [sqlite] Fwd: Problem on Windows 10 machines

In Windows you get a frowny face "modern icon" (about 5 inches square) and "something went wrong, sorry about your luck". --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

Try the same test using 147 columns in each table. 1 column is rather trivial. Even a kindergarten kid could do it in no time using crayons and the wall. And of course the output of INTERSECT is ordered. It uses a sorter to perform the intersection. And of course the output is distinct, it

Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

Hi All, The reason I consider to use VACUUM is that: when I insert 10k and delete 10k records for a number of times, the db file size keeps constant in each iteration. On the other hand, if I terminate the program manually and start the iteration again, the db file size increase once in the first

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 2017/09/07 12:35 AM, Cecil Westerhof wrote: ​It does not, but this does: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer")) ); Instead of "int" you need "integer". yes of course... My bad, sorry, but at least you've solved it :)

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-07 0:57 GMT+02:00 Simon Slavin : > > > On 6 Sep 2017, at 11:31pm, Cecil Westerhof wrote: > > > 2017-09-07 0:20 GMT+02:00 Richard Hipp : > > > >> On 9/6/17, Cecil Westerhof wrote: > >> > >>> Maybe this

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-07 0:36 GMT+02:00 Wolfgang Enzinger : > Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof: > > > 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger : > > >> Add this trigger and everything is fine. ;-) > >> > >> CREATE TRIGGER

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 6 Sep 2017, at 11:31pm, Cecil Westerhof wrote: > 2017-09-07 0:20 GMT+02:00 Richard Hipp : > >> On 9/6/17, Cecil Westerhof wrote: >> >>> Maybe this is correct, but it is certainly confusing. >> >> The constraint check

Re: [sqlite] Should the INTEGER not be cast to a REAL

Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof: > 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger : >> Add this trigger and everything is fine. ;-) >> >> CREATE TRIGGER weights_float_force_datatype >> BEFORE INSERT ON weights >> FOR EACH ROW >> BEGIN >> INSERT INTO

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-07 0:05 GMT+02:00 R Smith : > On 2017/09/06 11:58 PM, R Smith wrote: > >> Your CHECK constraint should really find that the value is acceptable >> when it is either a REAL, OR an INT, because both those types of data >> satisfies your requirement. >> >> > To be

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-07 0:20 GMT+02:00 Richard Hipp : > On 9/6/17, Cecil Westerhof wrote: > > > > Maybe this is correct, but it is certainly confusing. > > > > The constraint check occurs before the implicit conversion. > ​Should that not be the other way around? But

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger : > Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof: > > > 2017-09-07 0:05 GMT+02:00 R Smith : > > > >> On 2017/09/06 11:58 PM, R Smith wrote: > >> > >>> Your CHECK constraint should really find that

Re: [sqlite] Should the INTEGER not be cast to a REAL

Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof: > 2017-09-07 0:05 GMT+02:00 R Smith : > >> On 2017/09/06 11:58 PM, R Smith wrote: >> >>> Your CHECK constraint should really find that the value is acceptable >>> when it is either a REAL, OR an INT, because both

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 9/6/17, Cecil Westerhof wrote: > > Maybe this is correct, but it is certainly confusing. > The constraint check occurs before the implicit conversion. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-07 0:05 GMT+02:00 R Smith : > On 2017/09/06 11:58 PM, R Smith wrote: > >> Your CHECK constraint should really find that the value is acceptable >> when it is either a REAL, OR an INT, because both those types of data >> satisfies your requirement. >> >> > To be

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 11:54:35PM +0200, R Smith wrote: > It's still remarkable that in both tests 5 and 6 I've used the very same PK > setup, yet Test 6 was significantly faster with the added ORDER BY clause. > In tests 1 through 4 I did not use a PK at all, just plain INT data field, > but

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 2017/09/06 11:58 PM, R Smith wrote: Your CHECK constraint should really find that the value is acceptable when it is either a REAL, OR an INT, because both those types of data satisfies your requirement. To be specific, this should work for you: CREATE TABLE weights( float REAL,

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-06 23:58 GMT+02:00 R Smith : > On 2017/09/06 11:37 PM, Cecil Westerhof wrote: > >> But should in the first case the 0 not be cast to a 0.0? >> > > What makes you believe SQLite should massage the data into specific types > for you without you requesting it explicitly?

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 2017/09/06 11:37 PM, Cecil Westerhof wrote: But should in the first case the 0 not be cast to a 0.0? What makes you believe SQLite should massage the data into specific types for you without you requesting it explicitly? In fact, that would consume valuable extra CPU cycles and would

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 11:17 PM, Nico Williams wrote: If you'll redo this I'd urge you to use WITHOUT ROWIDS. First, that's almost always the right thing to do anyways. Second, it won't perform worse but likely will perform better. Third, write performance definitely should improve with WITHOUT

Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-06 23:49 GMT+02:00 Jens Alfke : > > > > On Sep 6, 2017, at 2:37 PM, Cecil Westerhof > wrote: > > > > But should in the first case the 0 not be cast to a 0.0? > > No, SQLite ignores column type declarations. There's a whole article on > the

Re: [sqlite] Should the INTEGER not be cast to a REAL

> On Sep 6, 2017, at 2:37 PM, Cecil Westerhof wrote: > > But should in the first case the 0 not be cast to a 0.0? No, SQLite ignores column type declarations. There's a whole article on the website on SQLite's dynamic approach to data typing. —Jens

[sqlite] Should the INTEGER not be cast to a REAL

I defined the following table: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) = "real") ); I try the following insert: INSERT INTO testing (float) VALUES (0) But this gives: CHECK constraint failed: float When I try this insert: INSERT INTO testing (float)

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 10:57:41PM +0200, R Smith wrote: > On 2017/09/06 8:26 PM, Nico Williams wrote: > >On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > >>-- Another interesting thing to note: The INTERSECT test produces ORDERED > >>-- output, which suggests that an ORDER-BY addition

Re: [sqlite] Fwd: Problem on Windows 10 machines

Well, on one machine I get a crash with no feedback at all. On the other Win 10 machine I get a massage, but these messages are meaningless, pointing to procedures that are not involved at all. RBS On 6 Sep 2017 22:06, "Simon Slavin" wrote: > > > On 6 Sep 2017, at

Re: [sqlite] Fwd: Problem on Windows 10 machines

On 6 Sep 2017, at 10:03pm, Bart Smissaert wrote: > When my wrapper makes the call to the Sqlite dll my app crashes With what error ? Segmentation fault ? Privilege violation ? I don’t think I’ve seen any crash which doesn’t produce an error report of some kind,

Re: [sqlite] Fwd: Problem on Windows 10 machines

When my wrapper makes the call to the Sqlite dll my app crashes, so I have no further information about what the problem is. Same happens every time. Note that the exact same dll is all fine on my Win 7 machine. RBS On 6 Sep 2017 21:54, "Simon Slavin" wrote: > > > On 6

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 8:26 PM, Nico Williams wrote: On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: -- Another interesting thing to note: The INTERSECT test produces ORDERED -- output, which suggests that an ORDER-BY addition to the query would -- favour the INTERSECT method. Nothing about

Re: [sqlite] Fwd: Problem on Windows 10 machines

On 6 Sep 2017, at 8:15pm, Bart Smissaert wrote: > On 2 different Win 10 machines I get a crash though when using the dll. What crash ? What error ? Is it the same one every time ? Simon. ___ sqlite-users mailing list

Re: [sqlite] Problem with mailing list

Try like this: Have a std_call compiled dll (sqlite3 3.20.1) that works perfectly fine on a Win 7 machine. On 2 different Win 10 machines I get a crash though when using the dll. This happens already when I run sqlite3_initialize. When I omit that step it will crash on the next first call to the

Re: [sqlite] Problem with mailing list

Strange thing the reply to your e-mail came straight through, but nil yet of that resent mail. RBS On Wed, Sep 6, 2017 at 12:05 PM, Scott Doctor wrote: > Check your spam folder. Some messages get trapped from the list in there > occassionally > > On September 6, 2017

[sqlite] Fwd: Problem on Windows 10 machines

-- Forwarded message -- From: Bart Smissaert Date: Tue, Sep 5, 2017 at 12:55 PM Subject: Problem on Windows 10 machines To: General Discussion of SQLite Database < sqlite-users@mailinglists.sqlite.org> Have a std_call compiled dll (sqlite3 3.20.1) that

Re: [sqlite] Problem with mailing list

Had a look, but not in there, Will just resend it. RBS On Wed, Sep 6, 2017 at 12:05 PM, Scott Doctor wrote: > Check your spam folder. Some messages get trapped from the list in there > occassionally > > On September 6, 2017 1:28:21 AM PDT, Bart Smissaert < >

Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

The journal_size_limit in WAL mode is for when the WAL file resets (everything checkpointed successfully). It doesn't limit transaction size in any way. It's simply "when everything has checkpointed: cut the file back to at most this size" rather than the normal mode of: "wal file size will

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > -- Another interesting thing to note: The INTERSECT test produces ORDERED > -- output, which suggests that an ORDER-BY addition to the query would > -- favour the INTERSECT method. Nothing about INTERSECT requires it to produce ordered

[sqlite] JOIN vs. INTERSECT speed and efficiency

Hi all, For those interested, after a recent thread from a poster called Joe asking about the most efficient way to find values that coincide from two separate tables, a response from Clemens Ladisch and a further elaboration from myself suggested the following: SELECT v FROM t1 INTERSECT

Re: [sqlite] GCC and DLL

On 6 Sep 2017, at 4:12pm, Papa wrote: > Are the SQLite3 DLLs, in the Precompiled Binaries for Windows, compatible > with MinGW-64? SQLite is entirely C code and has nothing in which depends on C++ features or settings. There shouldn’t be any problems. Is there something

Re: [sqlite] Geeting degrade while using multhi threading

> On Sep 4, 2017, at 7:22 AM, Senthil Kumar Chellappan > wrote: > > I am using only select operation ,but it gets degreaded(response time) when > i calls the API thur multi threading Internally SQLite uses locks to serialize calls by multiple threads. If

[sqlite] GCC and DLL

Are the SQLite3 DLLs, in the Precompiled Binaries for Windows, compatible with MinGW-64? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join, Union, Subquery or what?

Am 06.09.2017 um 14:32 schrieb R Smith: These suggestions from Clemens will work exactly as you want, but I need to add that it assumes the records all perfectly match between the tables, even flags, ID column etc. This means, if it doesn't work as you expect, you can still use the exact

Re: [sqlite] Sharing data between desktop and Android

I use an app called AirDroid: https://www.airdroid.com/ It goes beyond copying files to/from phone and pc, but it is one of its functionalities. On Wed, Sep 6, 2017 at 6:08 AM, Cecil Westerhof wrote: > 2017-09-06 12:54 GMT+02:00 Andy Ling : > > > >

Re: [sqlite] Join, Union, Subquery or what?

Joe wrote: > my SQLite database has two tables Katalog and ZKatalog with the same > structure. One of the columns is called DDatum. What's the most efficient > way to > > (1) Select records, which are only in Katalog, but not in ZKatalog? SELECT * FROM Katalog EXCEPT SELECT * FROM ZKatalog; >

Re: [sqlite] Sharing data between desktop and Android

2017-09-06 12:54 GMT+02:00 Andy Ling : > > Cecil Westerhof wrote: > > > I am thinking about writing some Android applications. I would like to > > > share data between the phone (or tablet) and de desktop. What is the > best > > > way to do this? In a way that would also be

Re: [sqlite] Problem with mailing list

Check your spam folder. Some messages get trapped from the list in there occassionally On September 6, 2017 1:28:21 AM PDT, Bart Smissaert wrote: >For some reason it seems postings I send sometimes don't get through or >maybe they do get through but I can't see them.

Re: [sqlite] Sharing data between desktop and Android

> Cecil Westerhof wrote: > > I am thinking about writing some Android applications. I would like to > > share data between the phone (or tablet) and de desktop. What is the best > > way to do this? In a way that would also be convenient for other people. > > I use an Android app that does this. It

Re: [sqlite] Sharing data between desktop and Android

2017-09-06 12:01 GMT+02:00 Andy Ling : > Cecil Westerhof wrote: > > I am thinking about writing some Android applications. I would like to > > share data between the phone (or tablet) and de desktop. What is the best > > way to do this? In a way that would also be convenient

Re: [sqlite] Sharing data between desktop and Android

Cecil Westerhof wrote: > I am thinking about writing some Android applications. I would like to > share data between the phone (or tablet) and de desktop. What is the best > way to do this? In a way that would also be convenient for other people. I use an Android app that does this. It has a

[sqlite] Problem with mailing list

For some reason it seems postings I send sometimes don't get through or maybe they do get through but I can't see them. I posted something yesterday at 12:55 pm (Problem on Windows 10 machines) and I still can't see that posting on the list. I mail from my normal GMail account. Is there anything

Re: [sqlite] dbSize calculation

On 9/4/17, zhiting zhu wrote: > Hi, > > I send this email to the dev mailing list but no response. It seems this > mailing list has more activity and attention. I have a question about how > dbSize is calculated when I was reading the source code of sqlite3. In the >

Re: [sqlite] how to compile and debug with the original source code, not the AMALGAMATION?

On 9/4/17, ze tian wrote: > Hi, > I am dealing with some optimizing work on sqlite, but the amalgation > file sqlite3.c seems not intuitively. When I try to compile the original > source code, some problems happen, seems not easy to deal with. Can you help > me

[sqlite] Upgrading transaction with statements using triggers and contentless fts5 causes SQLITE_BUSY timeouts

I've hit a problem that causes simple parallel inserts to hit the 30 second busy timeout. Here's the setup, using suggested trigger setup from the fts5 doc page: CREATE TABLE resource(id, title, data); -- Full-text search (fts) for resources CREATE VIRTUAL TABLE resource_fts USING fts5 (

[sqlite] dbSize calculation

Hi, I send this email to the dev mailing list but no response. It seems this mailing list has more activity and attention. I have a question about how dbSize is calculated when I was reading the source code of sqlite3. In the description of the dbSize, it said: "If the size of the file is not an

[sqlite] Geeting degrade while using multhi threading

Hi All, I am using the SQLite version 3.20 . I have created the inmemory database with shared cachemode as shown below "FullUri=file::memory:?cache=shared;PRAGMA locking_mode = NORMAL;Read Only=True;Pooling=True;Max Pool Size=120" I am using only select operation ,but it gets

[sqlite] how to compile and debug with the original source code, not the AMALGAMATION?

Hi, I am dealing with some optimizing work on sqlite, but the amalgation file sqlite3.c seems not intuitively. When I try to compile the original source code, some problems happen, seems not easy to deal with. Can you help me provide a compile script, like CMakeLists.txt, to compile the source

[sqlite] Join, Union, Subquery or what?

Hi, all, my SQLite database has two tables Katalog and ZKatalog with the same structure. One of the columns  is called DDatum. What's the most efficient way to (1) Select records, which are only in Katalog, but not in ZKatalog? (2) Select records, which are in Katalog and in ZKatalog? (3)

Re: [sqlite] [EXTERNAL] Is there a performance difference between COUNT(*) and COUNT(name)

On Wed, Sep 6, 2017 at 7:56 AM, Hick Gunter wrote: > Count() needs to extract the field from the record, tallying > only those that are NOT NULL. > Technically it would not need to "extract" the field, only lookup the row header and see whether that field/column is NULL or

Re: [sqlite] Sharing data between desktop and Android

Cecil Westerhof wrote: > I am thinking about writing some Android applications. I would like to > share data between the phone (or tablet) and de desktop. What is the best > way to do this? In a way that would also be convenient for other people. There is no good way to go over the USB connection