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

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 select f.id, fp.rightmost_folder_id, f.parent_id, f.name || '/' || fp.path, fp.rank from folders f

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

Assuming "right folder id" is short-hand for "the id of the rightmost folder" ... with folderpath(id, rightmost_folder_id, parent_id, path, rank) as ( select id, rightmost_folder_id, parent_id, name, rank from folders union all select f.id, rightmost_folder_id,

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

Have the same table structure to represent a folder tree: CREATE TABLE FOLDERS([ID] INTEGER PRIMARY KEY, [PARENT_ID] INTEGER, [NAME] TEXT, [LEVEL] INTEGER, [RANK] TEXT) Data is as follows: ID PARENT_ID NAME LEVEL RANK

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

Hi Ryan, Okay, thanks for the clarification! Your explanation makes sense! Best, Manuel On Mon, May 13, 2019 at 8:25 PM R Smith wrote: > On 2019/05/13 11:42 AM, Manuel Rigger wrote: > > Hi Ryan, > > > > I hope my question did not offend you. I didn't expect that the answer to > > this

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

Ok, well that's weird. The pre-compiled version is following your results. The version I compiled for myself is doing the right thing. With pre-compiled version: SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

On 2019/05/13 11:42 AM, Manuel Rigger wrote: Hi Ryan, I hope my question did not offend you. I didn't expect that the answer to this question would be considered to be that obvious. Goodness, I was not offended and apologies if my reply read in that way - it was just an honest account - and

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

On 13-5-2019 20:06, David Raymond wrote: What version are you using? Because it seems to be working fine for me on 3.28.0 Windows CLI. D:\TEMP>sqlite3 SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

What version are you using? Because it seems to be working fine for me on 3.28.0 Windows CLI. SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t0

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

On 13-5-2019 19:36, Manuel Rigger wrote: Hi everyone, Consider the following test case: CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID; INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5); REINDEX; SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1

[sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

Hi everyone, Consider the following test case: CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID; INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5); REINDEX; SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL; -- returns 5| I would expect that the

Re: [sqlite] unsubscribe

should that no be below?  On 13-5-2019 11:21, Tim Streater wrote: On 13 May 2019, at 09:19, Christof Arnosti wrote: http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Do that yourself at the link above. ___ sqlite-users

Re: [sqlite] SQLite with single writer on Windows network share

Andrew Moss, on Monday, May 13, 2019 04:59 AM, wrote... >Many thanks for all your comments and suggestions. I will bear it all in >mind. Are you trying to shut us up? :-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

Hi Ryan, I hope my question did not offend you. I didn't expect that the answer to this question would be considered to be that obvious. Best, Manuel On Mon, May 13, 2019 at 9:57 AM R Smith wrote: > On 2019/05/13 12:56 AM, Manuel Rigger wrote: > > Hi everyone, > > > > It seems that a table

Re: [sqlite] unsubscribe

On 13 May 2019, at 09:19, Christof Arnosti wrote: > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Do that yourself at the link above. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] SQLite with single writer on Windows network share

Many thanks for all your comments and suggestions. I will bear it all in mind. On Sun, 12 May 2019 at 12:26, Warren Young wrote: > On May 11, 2019, at 5:52 PM, Jose Isaias Cabrera > wrote: > > > > Warren Young, on Saturday, May 11, 2019 06:20 PM, wrote... > >> > >> On May 11, 2019, at 4:10 PM,

Re: [sqlite] feature request -- enhance strftime() implementing %V, %g and %G for week of year according to ISO 8601

Hello Luuk, in your mentionend man page, there is a similar sentence which is also wrong and needs to be corrected: "When three of fewer days of the first calendar week of the new year fall within that year, then the ISO 8601 week-based system counts those days as part of week 53 of the

[sqlite] unsubscribe

-- Securiton AG, Alarm- und Sicherheitssysteme Christof Arnosti Grammetstrasse 14, 4410 Liestal Tel. +41 58 910 55 35 christof.arno...@securiton.ch , www.securiton.ch

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

On 2019/05/13 12:56 AM, Manuel Rigger wrote: Hi everyone, It seems that a table created in the temp database cannot have a parent table that is created in the main database and vice versa: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 PRIMARY KEY); CREATE TEMP TABLE t1 (c0, FOREIGN KEY(c0)