Re: [sqlite] Displaying hierarchical structure
some relevant links: http://dwhoman.com/blog/sql-transitive-closure.html http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert Working on an Android app and part of that is storing SQL in a virtual > folder system in SQLite. For this I want to use a so-called closure table > as explained nicely here: > > http://technobytz.com/closure_table_store_hierarchical_data.html > > I have a table holder the folder details: > > ID PARENT_ID Folder > > - > 1 0 Folder1 > 2 1 Folder2 > 3 1 Folder3 > 4 1 Folder4 > 5 2 Folder5 > 6 2 Folder6 > > > And then the closure table: > > PARENT_ID CHILD_ID DEPTH > > --- > 1 10 > 2 20 > 3 30 > 4 40 > 5 50 > 6 60 > 1 21 > 1 31 > 1 41 > 2 51 > 1 52 > 2 61 > 1 62 > > What should the SQL be to display the folders like this: > > FolderPARENT_ID > Folder1 0 > Folder2 1 > Folder5 2 > Folder6 2 > Folder3 1 > Folder4 1 > > > RBS > ___ > 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] i Know i should use 'AS', but ....
The headers are present in all three queries you pasted. The first result shows two rows, the top row is the header. The other two results show 4 rows each, the top row of each is the header row. -P On Tue, 25 Dec 2018, 3:42 AM Luuk sqlite> .version > SQLite 3.26.0 > > sqlite> .headers on > > sqlite> select 1 as X,date(); > X|date() > 1|2018-12-24 > sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x > union all select 2 union all select 3); > x|row_number() over (order by 1 desc) > 3|1 > 2|2 > 1|3 > > Why are the headers missing in above query? > > > > sqlite> select x,row_number() over (order by 1 desc) as Y from (select 1 > as x union all select 2 union all select 3); > x|Y > 3|1 > 2|2 > 1|3 > sqlite> > > ___ > 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] Docs typo JSON1 @ 4.13
Hi, The JSON1 docs at https://www.sqlite.org/json1.html have a minor typo: Section 4.13. The json_each() and json_tree() table-valued functions atom ANY, -- value for primitive types, null for array & object > id INTEGER -- integer ID for this element > parent INTEGER, -- integer ID for the parent of this element The "id INTEGER" column definition is missing a trailing comma. Cheers, -P ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
Can't you just use IFNULL to assign a default value? CASE IFNULL( x, -999 ) WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN -999 THEN 55 ELSE 66 END On 5 July 2018 at 11:35, R Smith wrote: > On 2018/07/05 8:44 AM, Simon Slavin wrote: > >> On 5 Jul 2018, at 7:30am, Clemens Ladisch wrote: >> >> The expression "x = x" will fail for NULL, but succeed for everything >>> else. So you can use that to implement a "not-NULL ELSE" >>> >> Wow. That has to be the most counter-intuitive feature of SQLite. I >> understand why it works, but I still don't like it. Thanks for posting it. >> > > That's how it works everywhere, not just in SQLite. NULL has special > handling in that any expression or function that gets touched by a NULL > value immediately returns NULL (except for some aggregates that sometimes > have NULL values among their input populations, which they simply ignore). > > What the OP essentially wants is to test for NULL values, which is > possible using "IS" but not in an equality test (since the expression [ a = > x ] or [ a <> x ] both return NULL if either a is NULL or x is NULL, as > they should), so it cannot use equality testing in the usual way a CASE > executes. > > My typical way to do this is: > > CASE > WHEN x IS NULL THEN ... > WHEN x < 1 THEN ... > WHEN x < 3 THEN ... > WHEN x < 5 THEN ... > ELSE ... > END; > > But I feel like the equality check option can easily be enhanced in SQLite > to have this work: > > CASE x > WHEN IS NULL THEN > WHEN 1 THEN ... > WHEN 3 THEN ... > WHEN 5 THEN ... > END; > > but then it's so little difference from the example above it that I have > never yearned for it - in fact, I never use this latter version due to its > shortcomings in testing anything that is not an equality check (but since > my preference is no measure of its utility, perhaps it's worth considering). > > > Cheers, > Ryan > > PS: Here is a version of the 1st example working: > > WITH C(x) AS ( > SELECT NULL > UNION ALL > SELECT IFNULL(x + 1, 1) FROM C WHERE x < 10 OR x IS NULL > ) > SELECT x, CASE > WHEN x IS NULL THEN 'None' > WHEN x < 1 THEN 'Zero' > WHEN x < 3 THEN 'Small' > WHEN x < 6 THEN 'Medium' > ELSE 'Large' > END AS size > FROM C > ; > > > -- x| size > -- | -- > -- NULL | None > -- 1| Small > -- 2| Small > -- 3| Medium > -- 4| Medium > -- 5| Medium > -- 6| Large > -- 7| Large > -- 8| Large > -- 9| Large > -- 10 | Large > > > > > ___ > 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] FTS4 content table
Wording looks great, thanks Dan. FWIW I believe the same docs issue applies to the FTS5 module. Docs are hard, I often find myself re-reading paragraphs and then figuring out that I'm just a big dummy and didn't 'get it' the first time. The FTS docs are actually pretty great, they introduce some new concepts that are unique to that module and so those nonconanical patterns cause people to make their own assumptions regarding how they are implemented, which was the case here. Thanks again. - Peter On Mon, 2 Jul 2018, 10:02 AM Dan Kennedy, wrote: > On 07/01/2018 05:20 PM, Peter Johnson wrote: > > I recall you must still enumerate the column names of the fields you wish > > to link to the contentless table when creating the virtual table. > > > > If I remember correctly, failing to do so will result in 'no such column' > > errors. > > > > Last time I looked at the docs I got caught with the same gotcha, the > docs > > show an example query of how the contentless content is loaded but > > (possibly?) don't make it 100% clear whether you need to specify those > > columns in the vtable definition in order to allow them to be returned in > > the result rows. > > Fair enough. Added a sentence here to try to make things clearer: > >https://sqlite.org/docsrc/info/ea0f0b4c4af09bf9 > > Dan. > > > > > > > -P > > > > On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy, wrote: > > > >> On 06/30/2018 10:24 PM, Dudu Markovitz wrote: > >>> Hi > >>> > >>> according to the documentation - > >>> 6.2.2. External Content FTS4 Tables > >>> > >>> An "external content" FTS4 table is similar to a contentless table, > >> except > >>> that if evaluation of a query requires the value of a column other than > >>> docid, FTS4 attempts to retrieve that value from a table (or view, or > >>> virtual table) nominated by the user (hereafter referred to as the > >> "content > >>> table"). > >>> > >>> However I see no example for a query on the fts table that implicitly > >>> retrieves values from the content table, nor could I find any in other > >>> place. > >>> All my attempts to write such a query ended up with "Error: no such > >>> column". > >>> Am I missing something or is it a problem in the documentation? > >> I don't think I understand the question. What do you want it to do? > >> > >> The SELECT query in the second block of code in that section: > >> > >> https://sqlite.org/fts3.html#_external_content_fts4_tables_ > >> > >> is: > >> > >> SELECT * FROM t3 WHERE t3 MATCH 'k' > >> > >> t3 is the FTS4 table. The query does a lookup on the FTS4 index to find > >> matches for token 'k', then automatically retrieves values from the > >> content table (t2) for each matched row. The first instance of the > >> SELECT in the block of code shows everything working as expected, the > >> subsequent two show the counter-intuitive effects of allowing the FTS > >> index to get out of sync with the content table. > >> > >> Dan. > >> > >> > >> ___ > >> 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 content table
I recall you must still enumerate the column names of the fields you wish to link to the contentless table when creating the virtual table. If I remember correctly, failing to do so will result in 'no such column' errors. Last time I looked at the docs I got caught with the same gotcha, the docs show an example query of how the contentless content is loaded but (possibly?) don't make it 100% clear whether you need to specify those columns in the vtable definition in order to allow them to be returned in the result rows. -P On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy, wrote: > On 06/30/2018 10:24 PM, Dudu Markovitz wrote: > > Hi > > > > according to the documentation - > > 6.2.2. External Content FTS4 Tables > > > > An "external content" FTS4 table is similar to a contentless table, > except > > that if evaluation of a query requires the value of a column other than > > docid, FTS4 attempts to retrieve that value from a table (or view, or > > virtual table) nominated by the user (hereafter referred to as the > "content > > table"). > > > > However I see no example for a query on the fts table that implicitly > > retrieves values from the content table, nor could I find any in other > > place. > > All my attempts to write such a query ended up with "Error: no such > > column". > > Am I missing something or is it a problem in the documentation? > > I don't think I understand the question. What do you want it to do? > > The SELECT query in the second block of code in that section: > >https://sqlite.org/fts3.html#_external_content_fts4_tables_ > > is: > >SELECT * FROM t3 WHERE t3 MATCH 'k' > > t3 is the FTS4 table. The query does a lookup on the FTS4 index to find > matches for token 'k', then automatically retrieves values from the > content table (t2) for each matched row. The first instance of the > SELECT in the block of code shows everything working as expected, the > subsequent two show the counter-intuitive effects of allowing the FTS > index to get out of sync with the content table. > > Dan. > > > ___ > 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] Check if the new table has been created
Is it possible to create a trigger on sqlite_master which calls a user-defined function AFTER INSERT? That would avoid having to poll, but it'd still allow the application to be notified when the schema changed. On 19 June 2018 at 20:56, Richard Hipp wrote: > On 6/19/18, Igor Korot wrote: > > Hi, Wout, > > > > On Tue, Jun 19, 2018 at 1:31 PM, Wout Mertens > > wrote: > >> you can query the table with > >> https://www.sqlite.org/pragma.html#pragma_table_info > > > > Let me give you a scenario: > > > > 1. My application connects to the database and performs some > > operations (using C API). > > 2. During the application run, someone started sqlite3, connects to > > the database and creates a > > brand new table. > > 3. My application will need to pick up the newly created table and > continue. > > > > Is it easily possible? > > > > There is a sqlite3_*_hook() family of functions, but it looks like > > they won't help with sqlite_master. > > > > Is there a different way? > > Poll the PRAGMA schema_version value and watch for changes. > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Issue with node-sqlite3
Hi Omer,
Unfortunately what you are trying to do it not possible.
You are trying to run the npm package `node-sqlite3` in an Angular project.
Angular is a front-end, browser-based framework.
The npm package manager contains javascript modules which can sometimes be
used either in the browser or on the command line via nodejs.
In this case, the node-sqlite3 will only work when installed and run in
nodejs on the command line, it will not work in the browser.
The messages relating to the lack of crypto, http, and https are because
those modules are part of nodejs and are not available in the browser.
The dependency on make and python are due to node-sqlite3 shipping a
version of sqlite with the module, it requires those tools
to compile sqlite.
I hope that helps, this is, unfortunately, the wrong place to report this
bug.
Please open an issue here for more help:
https://github.com/mapbox/node-sqlite3
-P
On 13 June 2018 at 12:50, Space Pixel wrote:
> Hi Ryan,
>
> I can see you didn't quite understand me. The project I am attempting to
> install sqlite3 on is an Angular 6 project. Now, in the package sqlite3
> there is a CS file that spits out a warning "you need an appropriate loader
> to handle this file type". Also, some of the packages sqlite3 requires are
> very ancient and deprecated: crypto, aws-sdk, http, https, and some more.
> Now, about getting the latest version through the website- again, the
> "sqlite3" that I'm talking about is a NPM package, and there is nothing to
> do with finding the latest version: I already have it. Plus, when I tried
> to install it without any Python or build tools, it fell back to the build
> and then the build immediately FAILED. After I installed Windows Build
> Tools and Python (and added it to PATH), I got it to build successfully,
> but then I face the problems that I mentioned in the first mail.
>
> Hope you understand now,
>
> Omer Shamai.
>
> On Wed, Jun 13, 2018, 12:52 PM R Smith wrote:
>
> > On 2018/06/13 10:26 AM, Space Pixel wrote:
> > > Hello sqlite community,
> > >
> > > I am having serious trouble with the Node.js module "sqlite3". This
> > package
> > > is supposed to connect JavaScript or TypeScript code with a sqlite3
> > > database (file or online database). When I try to use the sqlite file
> > (the
> > > command I am using is "const db = new sqlite3.Database("file name
> > > here");"), it spits out SO many errors regarding packages that were
> > > declared as deprecated and out of date so long ago. I'm gonna give a
> log
> > > below. Notice the warnings: A .cs file spits out a warning, in which it
> > > says: "You need an appropriate loader to handle this file". I tried to
> > find
> > > a solution for a whole month, to no avail. Please help at once.
> >
> > Hi Omer,
> >
> > The reason your messages are not being answered quick and precisely is
> > that this is really not an SQLite issue, it's a CS problem and people on
> > a CS forum are much more likely able to help out. It's also possible
> > that some CS user here might read it and offer assistance, but so far it
> > didn't attract much attention from such Samaritans.
> >
> > The SQLite devs do not make deprecated systems, the stuff can be
> > downloaded from the SQLite site is all up-to-date and working, so that
> > means that either CS itself or the wrapper or module you use in CS, to
> > use SQLite DB capabilities, is out-of-date or deprecated. We can show
> > you the newest and updated sources for SQLite itself, but we cannot
> > magic it into your CS project - hence me saying you are far more likely
> > to get help from a CS forum since someone there is bound to have
> > discovered and (hopefully) already solved this same problem.
> >
> > Once you get the SQLite to be understood by your CS and start using it
> > and then have any problem with how it works, understands SQL or handles
> > files, then we can (and would love to) fully assist.
> >
> > Good luck,
> > Ryan
> >
> > ___
> > 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
[sqlite] query planning - covering indices
I noticed that the query planner favours the primary index when a covering index exists which can satisfy the same query. My understanding is that covering indices are more efficient, as the table itself does not need to be scanned when all the required columns exist in the covering index? Is it correct to say that example 1 is more efficient than the other two examples, particularly when there are many columns, all of which are covered by the covering index? *example 1 - using covering index* CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER); CREATE INDEX idx_covering ON example( a, b, c ); EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1; 0|0|0|SEARCH TABLE example USING COVERING INDEX idx_covering (a=? AND b=?) *example2 - using primary key & covering index* CREATE TABLE example (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER); CREATE INDEX idx_covering ON example( a, b, c ); EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1; 0|0|0|SEARCH TABLE example USING INTEGER PRIMARY KEY (rowid=?) *example3 - using composite primary key & covering index* CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER, PRIMARY KEY(a, b)); CREATE INDEX idx_covering ON example( a, b, c ); EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1; 0|0|0|SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (a=? AND b=?) I also noticed that when using > or < instead of = that the covering index is used instead of the primary index. Could someone please help me to understand why it works like this? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac OS X name mangling
On Dec 1, 2007, at 5:08 AM, [EMAIL PROTECTED] wrote: The comment probably should read: "Needed to enable pthread recursive mutexes *on Linux*". The _XOPEN_SOURCE define is needed to fix compile-time problems. If you didn't get a compile-time error, it is probably working. Great, that's the confirmation I was hoping for! Thanks. Please also note that "thread safe" is an oxymoron. :-) I hear that. --Peter Johnson [EMAIL PROTECTED]
Re: [sqlite] Mac OS X name mangling
That's an improvement, using 3.5.3. I have to define __DARWIN__ instead of __MACOS__ now, but at it least compiles without modifying any sources. (I wonder why the config stuff does set __DARWIN__) I still question whether or not I'm getting a thread safe build though. If I define __DARWIN__, that prevents it from setting _XOPEN_SOURCE, which the comments say are "Needed to enable pthread recursive mutexes" On Nov 30, 2007, at 1:48 PM, Peter Johnson wrote: On Nov 30, 2007, at 1:32 PM, [EMAIL PROTECTED] wrote: Peter Johnson <[EMAIL PROTECTED]> wrote: I'm running into a problem building SQLite 3.5.1 targeting a minimum of Mac OS X 10.3, but building against the 10.4 (Universal) SDK. I think this issue has been addressed in 3.5.3. Have you tried using the latest sources? -- D. Richard Hipp <[EMAIL PROTECTED]> No, I didn't see anything in the 3.5.3 release notes that looked relevant, but I'll give it a try now. Thanks. --Peter Johnson [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mac OS X name mangling
On Nov 30, 2007, at 1:32 PM, [EMAIL PROTECTED] wrote: Peter Johnson <[EMAIL PROTECTED]> wrote: I'm running into a problem building SQLite 3.5.1 targeting a minimum of Mac OS X 10.3, but building against the 10.4 (Universal) SDK. I think this issue has been addressed in 3.5.3. Have you tried using the latest sources? -- D. Richard Hipp <[EMAIL PROTECTED]> No, I didn't see anything in the 3.5.3 release notes that looked relevant, but I'll give it a try now. Thanks. --Peter Johnson [EMAIL PROTECTED]
[sqlite] Mac OS X name mangling
I'm running into a problem building SQLite 3.5.1 targeting a minimum of Mac OS X 10.3, but building against the 10.4 (Universal) SDK. The problem is that the 10.4 SDK does name mangling on the pthread_join symbol in some cases. Specifically, if the _XOPEN_SOURCE symbol is defined, pthread_join gets mangled to pthread_join$UNIX2003, which is a symbol that is not available in 10.3. Building with --enable-threadsafe tends to want to define the _XOPEN_SYMBOL (in sqliteInt.h). I noticed that if the symbol __MACOS__ is defined, sqliteInt.h, won't define _XOPEN_SOURCE. However, if __MACOS__ is defined, shell.c won't compile with the following errors: /src/shell.c:35:22: error: console.h: No such file or directory ./src/shell.c:38:21: error: extras.h: No such file or directory ./src/shell.c:39:20: error: Files.h: No such file or directory ./src/shell.c:40:22: error: Folders.h: No such file or directory ./src/shell.c: In function 'find_home_dir': ./src/shell.c:1714: error: '_MAX_PATH' undeclared (first use in this function) ./src/shell.c:1714: error: (Each undeclared identifier is reported only once ./src/shell.c:1714: error: for each function it appears in.) If I simply #undef __MACOS__ in shell.c, right before the conditional includes, it compiles and everything *seems* to work. However, I'm not confident that I'm actually thread safe if I'm defining __MACOS__ and just undef'ing the symbol in shell.c seems a little suspect. Does anyone have any experience with this, or any suggestions? --Peter Johnson [EMAIL PROTECTED] This email is PRIVILEGED AND CONFIDENTIAL to Zattoo Inc.
