Re: [sqlite] [EXTERNAL] Re: Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

2019-08-14 Thread Hick Gunter
OK now I see. I think this is a major bug in the code generator because it breaks documented behaviour. In lines 8 to 12, SQlite is building records for an ephemeral "to do" table. Line 8: retrieve the value of the primary key with OPFLAG_NOCHNG set and store result in R6 Line 9: store the

Re: [sqlite] [EXTERNAL] Re: Programming methodology (was DEF CON (wasL A license plate of NULL))

2019-08-14 Thread Hick Gunter
How about #define is_true(tf) ((uintptr_t)0 != (uintptr_t)(tf)) -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Don V Nielsen Gesendet: Dienstag, 13. August 2019 22:42 An: SQLite mailing list Betreff: [EXTERNAL] Re:

Re: [sqlite] [EXTERNAL] Re: DEF CON (wasL A license plate of NULL)

2019-08-14 Thread Hick Gunter
But surely any compiler worth ist salt would optimize away all of that code and just use the result of the expression given as argument in the call ;) -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden

[sqlite] specify CHECK clause to date text field

2019-08-14 Thread Martin
Hi, Using the precompiled CLI for MacOS (10.13.6) I stumbled upon the following to do with the date() function: sqlite> .version SQLite 3.29.0 2019-07-10 17:32:03 fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 zlib version 1.2.11 clang-10.0.1 sqlite> select

[sqlite] Create multipe tables, many-to-many design

2019-08-14 Thread dboland9
I'm doing an app. that uses sqlite, and has a many-to-many relationship. The areas I need some guidance are: * Best way to create multiple tables the first time the app. is started. * How to create a MTM relationship and add/modify data. I can create tables (Python) by putting the code in

[sqlite] Best way to create multiple tables?

2019-08-14 Thread dboland9
I'm doing an app. that has multiple tables in sqlite. When the app. is run for the first time, or the tables are lost/damaged, it will create the database and all tables. My question is if there is a best way to create 5 tables? I can put al the Python code into one function, pass the path to

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Clemens Ladisch
Martin wrote: > sqlite> select date('2019-02-29'); -- not a leap year > 2019-02-29 > I would appreciate any advice on the preferred way to specify a > CREATE TABLE .. CHECK clause > to guard inserting a -mm-dd date into a text field. sqlite> select date('2019-02-29', '+0 days');

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Adrian Ho
On 12/8/19 10:59 PM, Martin wrote: > sqlite> .version > SQLite 3.29.0 2019-07-10 17:32:03 > fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 > zlib version 1.2.11 > clang-10.0.1 > sqlite> select date('2019-02-00'); -- null > > sqlite> select date('2019-02-01'); -- ok >

Re: [sqlite] Programming methodology (was DEF CON (wasL A license plate of NULL))

2019-08-14 Thread Richard Damon
On 8/13/19 4:47 PM, Richard Hipp wrote: > On 8/13/19, Jose Isaias Cabrera wrote: >> I see all of you smart programmers using this >> non-column matching behavior, and I ask myself why? > Because that's the way Dennis Richie did it. :-) There are many ways to format code, and many programmers

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Richard Hipp
On 8/14/19, Adrian Ho wrote: > Here's a Dirty Little Secret: All the SQLite date functions are centered > around strftime(), which is not implemented in a strictly correct sense > in *every* Unix-like platform I've seen. Not true. SQLite implements its own date and time computations, based on

[sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

2019-08-14 Thread Deon Brewis
sqlite3_interrupt is documented as: “It is safe to call this routine from a thread different from the thread that is currently running the database operation” SQLITE_CONFIG_SINGLETHREAD is documented as: “puts SQLite into a mode where it can only be used by a single thread” Which one wins ?

Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

2019-08-14 Thread Richard Hipp
On 8/14/19, Deon Brewis wrote: > sqlite3_interrupt is documented as: > “It is safe to call this routine from a thread different from the thread > that is currently running the database operation” > > SQLITE_CONFIG_SINGLETHREAD is documented as: > “puts SQLite into a mode where it can only be used

[sqlite] Incorrect query result

2019-08-14 Thread Eric Boudaillier
Hi, I am experiencing incorrect query result with SQLite 3.25.2 and 3.28. Attached are the database and a Tcl script running 3 queries. The database and the queries have been reduced to the minimum, so they are not really relevant, but demonstrates better where is the problem. Also note that the

Re: [sqlite] Incorrect query result

2019-08-14 Thread Jay Kreibich
Alas, the mailing list does not allow attachments. -j > On Aug 14, 2019, at 8:24 AM, Eric Boudaillier > wrote: > > Hi, > > I am experiencing incorrect query result with SQLite 3.25.2 and 3.28. > Attached are the database and a Tcl script running 3 queries. > The database and the queries

[sqlite] Fw: multi-multi join/bridge table, table creation questions

2019-08-14 Thread dboland9
Sent with [ProtonMail](https://protonmail.com) Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, August 14, 2019 8:54 AM, dboland9 wrote: > I'm doing a Python app (not that the language really matters - just for > context) that uses SQLite to store data. I have a many-to-many (MTM)

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Adrian Ho
On 14/8/19 8:47 PM, Richard Hipp wrote: > On 8/14/19, Adrian Ho wrote: >> Here's a Dirty Little Secret: All the SQLite date functions are centered >> around strftime(), which is not implemented in a strictly correct sense >> in *every* Unix-like platform I've seen. > Not true. > > SQLite

[sqlite] multi-multi join/bridge table, table creation questions

2019-08-14 Thread dboland9
I'm doing a Python app (not that the language really matters - just for context) that uses SQLite to store data. I have a many-to-many (MTM) relationship. After a lot of Googling and reading I have concluded that: >> I need to create the join/bridge table just like all the other tables. In

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Adrian Ho
On 14/8/19 8:47 PM, no...@null.net wrote: > > CREATE TABLE table_a( > dt TEXT -- NOT NULL if you like > CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime')) > ); Sorry, that 'localtime' qualifier is a non-starter; that will throw a "non-deterministic function in index

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread nomad
On Wed Aug 14, 2019 at 09:08:26PM +0800, Adrian Ho wrote: > On 14/8/19 8:47 PM, no...@null.net wrote: > > > > CREATE TABLE table_a( > > dt TEXT -- NOT NULL if you like > > CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime')) > > ); > > Sorry, that 'localtime' qualifier

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Keith Medcalf
Converting the date from naive (unknown timezone) format to naive utc or naive localtime will result in a different date and it will not match for valid dates depending on the timezone of your computer and the vagaries of the OS localtime conversions, and the particular time-of-day at which

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread nomad
> It seems the date function does not check that the date is valid, > only the format. I've run into the same issue. Don't remember if it has been raised on the list, but I have a vague memory that it fell into the WONTFIX category :-( > Consequently, I would appreciate any advice on the

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Adrian Ho
On 14/8/19 8:33 PM, Clemens Ladisch wrote: > CREATE TABLE t ( > date date CHECK (date = date(date, '+0 days')) > ); Sadly, this isn't sufficient for guarding against malformed dates like '2019-02-00' and '2019-02-1' that the OP listed, because the CHECK expression in those cases resolves to

Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

2019-08-14 Thread Deon Brewis
Intended use is to cancel long running SQLITE background operations on other threads if the user needs UI responsiveness on the main thread. Even though the operations are background, we need the CPU & disk back for the user. Once the user becomes idle again, the background operations restart.

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-14 Thread Stephen Chrzanowski
On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > > > Why are you storing the timezone? You display the TZ of the user who is, > > later, viewing the data. And that user could be anywhere. > > Because the actual time on the clock on the wall matters. > I want to know cashiers that are making

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-14 Thread Warren Young
On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski wrote: > > On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > >>> Why are you storing the timezone? You display the TZ of the user who is, >>> later, viewing the data. And that user could be anywhere. >> >> Because the actual time on the clock

Re: [sqlite] specify CHECK clause to date text field

2019-08-14 Thread Adrian Ho
On 14/8/19 8:46 PM, Adrian Ho wrote: > On 14/8/19 8:33 PM, Clemens Ladisch wrote: >> CREATE TABLE t ( >> date date CHECK (date = date(date, '+0 days')) >> ); > Sadly, this isn't sufficient for guarding against malformed dates like > '2019-02-00' and '2019-02-1' that the OP listed, because the

Re: [sqlite] multi-multi join/bridge table, table creation questions

2019-08-14 Thread Igor Tandetnik
On 8/14/2019 8:54 AM, dboland9 wrote: I'm doing a Python app (not that the language really matters - just for context) that uses SQLite to store data. I have a many-to-many (MTM) relationship. After a lot of Googling and reading I have concluded that: I need to create the join/bridge table

Re: [sqlite] Best way to create multiple tables?

2019-08-14 Thread Tim Streater
On 12 Aug 2019, at 13:51, dboland9 wrote: > I'm doing an app. that has multiple tables in sqlite. When the app. is run > for the first time, or the tables are lost/damaged, it will create the > database and all tables. My question is if there is a best way to create 5 > tables? > > I can put

Re: [sqlite] Create multipe tables, many-to-many design

2019-08-14 Thread Keith Medcalf
On Monday, 12 August, 2019 14:05, dboland9 wrote: >Hiking_Table Trails_Table >Joining_Table >- - -- >- >hike_id PK trail_id PK >hike_id FK >hike_date TEXT

Re: [sqlite] Create multipe tables, many-to-many design

2019-08-14 Thread Keith Medcalf
> UNIQUE (date, destination) should of course be UNIQUE (start, destination) >create index HikedTrails on HikeTrail (hike_id, trail_id); >create index TrailsHiked on HikeTrail (trail_id, hike_id); Both of these should be UNIQUE indexes and could be declared in the create table ... and of

Re: [sqlite] Create multipe tables, many-to-many design

2019-08-14 Thread Keith Medcalf
For maximum space efficiency and using a supporting version of SQLite3 you could declare the N:M table as: create table HikeTrail ( hike_id INTEGER NOT NULL REFERENCES Hikes, trail_id INTEGER NOT NULL REFERENCES Trails, PRIMARY KEY (hike_id, trail_id), UNIQUE (trail_id, hike_id) )

Re: [sqlite] [EXTERNAL] Re: DEF CON (wasL A license plate of NULL)

2019-08-14 Thread Warren Young
On Aug 14, 2019, at 12:27 AM, Hick Gunter wrote: > > But surely any compiler worth ist salt would optimize away all of that code > and just use the result of the expression given as argument in the call ;) You joke, but the answer is “Maybe.” See https://godbolt.org/z/K9g-ai In English,

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-14 Thread Stephen Chrzanowski
Yep, I agree, to which is where I pointed out that you'd need the additional information for that store location. But that's all you'd need, and only in that one location. Your UI (Or whatever specialized report generation) would have to do the math from the UTC time, and convert it accordingly