Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
Okay, thanks for the clarification! Best, Manuel On Mon, May 13, 2019 at 1:38 AM J. King wrote: > On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger > wrote: > > >As far as I understood, the main and temp databases are always loaded > >and > >cannot be detached. But, as you pointed out, I can understand that this > >makes sense in the general case. > > Note that the temp database is specific to a given connection while the > main one is not. Creating foreign relations to the temp database would > corrupt another connection's view of the same database > -- > J. King > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger wrote: >As far as I understood, the main and temp databases are always loaded >and >cannot be detached. But, as you pointed out, I can understand that this >makes sense in the general case. Note that the temp database is specific to a given connection while the main one is not. Creating foreign relations to the temp database would corrupt another connection's view of the same database -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
Thanks for your quick response! On Mon, May 13, 2019 at 1:17 AM Simon Slavin wrote: > On 12 May 2019, at 11:56pm, Manuel Rigger wrote: > > > Is this intended? > > Yes. Because the temp database disappears when you close your connection. > So you would open a database and find that either a parent or a child table > had disappeared. Which would mean the database was corrupt. > I would not consider the database to be corrupt though, since the behavior for a missing parent table is documented. For example, it is supported to drop a parent table that is still referenced. > > Actually it's not limited to temp. All FOREIGN KEY relationships must be > between two tables in the same database. Because if they were in different > databases you might load one database and not the other. > As far as I understood, the main and temp databases are always loaded and cannot be detached. But, as you pointed out, I can understand that this makes sense in the general case. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
On 12 May 2019, at 11:56pm, Manuel Rigger wrote: > Is this intended? Yes. Because the temp database disappears when you close your connection. So you would open a database and find that either a parent or a child table had disappeared. Which would mean the database was corrupt. Actually it's not limited to temp. All FOREIGN KEY relationships must be between two tables in the same database. Because if they were in different databases you might load one database and not the other. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mixing main and temp databases in foreign keys is not supported
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) REFERENCES t0(c0)); INSERT INTO t1(c0) VALUES (1); -- no such table: temp.t0 Is this intended? It somehow seems so, since the parent table can also not be prefixed by "temp." when declaring the foreign key in the above example. However, I did not find a note in the documentation that this is not supported. Would it make sense to describe this in the limitations at https://www.sqlite.org/foreignkeys.html? Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CTE to Get Path In a Tree
On 5/12/2019 2:14 PM, Igor Tandetnik wrote: On 5/12/2019 6:19 AM, Philip Bennefall wrote: Hi everyone, I have a tree of folders and I want to find the complete path from any arbitrary point back to the top level directory. The schema is: CREATE TABLE IF NOT EXISTS folders( id INTEGER PRIMARY KEY, parentFolderId INTEGER REFERENCES folders(id) ON DELETE CASCADE ON UPDATE CASCADE, name TEXT NOT NULL); I made the following CTE: WITH RECURSIVE folderTree (id, path) AS(SELECT id, name FROM folders WHERE parentFolderId IS NULL UNION ALL SELECT folders.id, folderTree.path || '/' || folders.name FROM folders, folderTree WHERE folders.parentFolderId = folderTree.id) SELECT path FROM folderTree WHERE id=?1; This produces the correct result, but I am wondering if there is a more efficient way? This query seems to generate the entire tree and then do a table scan to find just the one row I am looking for. Can I start from the given row and *only* traverse upwards through the levels until I find a node with no parent? Just reverse the conditions. Something like this (not tested): WITH RECURSIVE folderPath(id, parentId, path) AS(SELECT id, parentFolderId, name FROM folders WHERE id=?1) UNION ALL SELECT f.id, f.parentFolderId, f.name || '/' || fp.path FROM folders f join folderPath fp on (f.id = fp.parentId)) SELECT path FROM folderPath WHERE parentId is null; Thanks, that seems to work with a couple of very minor tweaks. The query plans are somewhat different and my gut feeling is that the one you wrote is better, but I will measure against some larger datasets just to be sure. Thanks for the quick response! Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CTE to Get Path In a Tree
On 5/12/2019 6:19 AM, Philip Bennefall wrote: Hi everyone, I have a tree of folders and I want to find the complete path from any arbitrary point back to the top level directory. The schema is: CREATE TABLE IF NOT EXISTS folders( id INTEGER PRIMARY KEY, parentFolderId INTEGER REFERENCES folders(id) ON DELETE CASCADE ON UPDATE CASCADE, name TEXT NOT NULL); I made the following CTE: WITH RECURSIVE folderTree (id, path) AS(SELECT id, name FROM folders WHERE parentFolderId IS NULL UNION ALL SELECT folders.id, folderTree.path || '/' || folders.name FROM folders, folderTree WHERE folders.parentFolderId = folderTree.id) SELECT path FROM folderTree WHERE id=?1; This produces the correct result, but I am wondering if there is a more efficient way? This query seems to generate the entire tree and then do a table scan to find just the one row I am looking for. Can I start from the given row and *only* traverse upwards through the levels until I find a node with no parent? Just reverse the conditions. Something like this (not tested): WITH RECURSIVE folderPath(id, parentId, path) AS(SELECT id, parentFolderId, name FROM folders WHERE id=?1) UNION ALL SELECT f.id, f.parentFolderId, f.name || '/' || fp.path FROM folders f join folderPath fp on (f.id = fp.parentId)) SELECT path FROM folderPath WHERE parentId is null; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve "latest" fileio.c, test_windirect.c/h files?
On 2019-05-12 21:39, Warren Young wrote: On May 11, 2019, at 10:46 PM, Justin Clift wrote: One of the steps uses curl to download fileio.c, test_windirent.c/.h from fossil [snip] Is there a way to always get "the latest" version of the file? :) $ curl -L -o src/extensions/fileio.c 'https://sqlite.org/src/raw?filename=ext/misc/fileio.c=trunk' Thanks Warren, that works. :) Or, you could just use Fossil directly. ... It's a good thought, but not just yet. Already have a head full of other stuff being learned, don't really want to add a whole new distributed version control system as well. Maybe later. :) Regards and best wishes, Justin Clift ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve "latest" fileio.c, test_windirect.c/h files?
On May 11, 2019, at 10:46 PM, Justin Clift wrote: > > One of the steps uses curl to download fileio.c, test_windirent.c/.h > from fossil [snip] > Is there a way to always get "the latest" version of the file? :) $ curl -L -o src/extensions/fileio.c 'https://sqlite.org/src/raw?filename=ext/misc/fileio.c=trunk' Mind the quotes: you need them to prevent the shell from trying to interpret the &. Or, you could just use Fossil directly. First time: $ fossil clone https://sqlite.org/src sqlite.fossil $ mkdir sqlite-trunk $ cd sqlite-trunk $ fossil open ../sqlite.fossil $ cp ext/misc/fileio.c ../src/extensions Subsequent times: $ cd sqlite-trunk $ fossil update $ cp ext/misc/fileio.c ../src/extensions Which brings me to how I constructed the URL in the curl command above: $ fossil help /raw ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Partial NOT NULL index malfunctions with IS NOT/!=
Great, thanks, Dan! Best, Manuel On Sat, May 11, 2019 at 4:05 PM Dan Kennedy wrote: > > On 11/5/62 16:54, Manuel Rigger wrote: > > Hi everyone, > > > > I found another test case that demonstrates a malfunctioning index: > > > > CREATE TABLE IF NOT EXISTS t0 (c0); > > CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL; > > INSERT INTO t0(c0) VALUES(NULL); > > SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row > > > Thanks for this. Should be fixed now. Ticket here: > >https://sqlite.org/src/tktview/80256748471a01 > > Dan. > > > > > > If the index is created, no rows are fetched. Without the index, the NULL > > row is returned. > > > > I think that this looks like a rather general pattern that could be used > in > > practice. > > > > Best, > > Manuel > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite with single writer on Windows network share
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, Thomas Kurz wrote: >> >> It should run under Cygwin and WSL. > > I tried to build it. I fould out that I needed brew Cygwin’s own package repo should have all of the packages you need to build Bedrock. There should be no need to drag Brew into this. > So, it looks like it sees cygwin as a MacOS system. Homebrew started off as macOS-only, and is a newcomer on other platforms. That’s another reason not to reach for it when you have a large decades-old package repo like Cygwin’s to draw from already. Cygwin’s default install is minimal: https://stackoverflow.com/a/21233990/142454 It usually takes me several passes through the Cygwin setup.exe to get a new system in shape to get useful work done. > Does anybody know what is gcc@6? It’s Brew-speak for GCC v6, as opposed to other versions of GCC also in the Brew package repo. The package “gcc” is simply the current version. You only give the @NN syntax when you need to pin Brew down to a specific version for some reason. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to retrieve "latest" fileio.c, test_windirect.c/h files?
Hi all, Is there a way to grab the "latest" version of a SQLite source file? In this instance, we have an automatic nightly process (shell script) that builds the DB Browser for SQLite nightly builds. One of the steps uses curl to download fileio.c, test_windirent.c/.h from fossil, so the fileio extension can be included in the nightly builds. It's using a specific commit for each of the downloads at the moment, as that's the only thing I was able to figure out at the time of creating the script: curl -L -o src/extensions/fileio.c https://sqlite.org/src/raw/ext/misc/fileio.c?name=288e7230e0fe464d71b0694e2d8bdd3a353118ac2e31da3964b95f460f09915f curl -L -o src/extensions/test_windirent.c https://sqlite.org/src/raw/src/test_windirent.c?name=a895e2c068a06644eef91a7f0a32182445a893b9a0f33d0cdb4283dca2486ac1 curl -L -o src/extensions/test_windirent.h https://sqlite.org/src/raw/src/test_windirent.h?name=90dfbe95442c9762357fe128dc7ae3dc199d006de93eb33ba3972e0a90484215 Leaving off the `name=...` argument just returns a HTML page, instead of giving the expect latest version too. Is there a way to always get "the latest" version of the file? :) Regards and best wishes, Justin Clift ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CTE to Get Path In a Tree
Hi everyone, I have a tree of folders and I want to find the complete path from any arbitrary point back to the top level directory. The schema is: CREATE TABLE IF NOT EXISTS folders( id INTEGER PRIMARY KEY, parentFolderId INTEGER REFERENCES folders(id) ON DELETE CASCADE ON UPDATE CASCADE, name TEXT NOT NULL); I made the following CTE: WITH RECURSIVE folderTree (id, path) AS(SELECT id, name FROM folders WHERE parentFolderId IS NULL UNION ALL SELECT folders.id, folderTree.path || '/' || folders.name FROM folders, folderTree WHERE folders.parentFolderId = folderTree.id) SELECT path FROM folderTree WHERE id=?1; This produces the correct result, but I am wondering if there is a more efficient way? This query seems to generate the entire tree and then do a table scan to find just the one row I am looking for. Can I start from the given row and *only* traverse upwards through the levels until I find a node with no parent? Thanks in advance for any pointers. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
