Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[邱朗]

“Much safer to have a habit to name views like the table names they derive from 
(when they derive from specific tables)”

That is what I am doing right now. So thanks for all the answers I got to 
confirm that there is no easy way to do it.
Qiulang


At 2017-03-22 19:52:59, "R Smith"  wrote:
>
>
>On 2017/03/22 12:37 PM, Richard Hipp wrote:
>> On 3/22/17, 邱朗  wrote:
>>> Hi,
>>>
>>>
>>> Is there any way to drop view “automatically” when its associated table is
>>> dropped?
>>> It seems no way to do. Then if I drop a table, is there any (easy) way to
>>> find views created based on it and I can drop view manually ?
>>>
>> There is no easy way to figure out what tables a view refers to.
>> Furthermore, some applications might want to drop a table and then
>> recreate that table (perhaps after adding a new constraint or
>> reordering the columns) and would like to keep using the VIEW.  Those
>> applications would not want the VIEW to be dropped automatically.
>>
>
>May I add further that views are tricky things. They can refer to 
>multiple tables, or other views which in turn refer to multiple tables 
>or yet other views (and have obscured the names of the original tables 
>via "AS" aliasing). There is no way to easily implement an automatic 
>view-of-table dropper. (I've tried to do something like this for an 
>sqlite tool long ago).
>
>You could even look for the table name referenced in the view sql (by 
>simply doing something like:
>SELECT name FROM sqlite_master WHERE type='view' AND sql LIKE 
>'%MyTableName%';
>and drop all of those views that show up in the result - BUT, then you 
>have to be absolutely sure no View would ever use a field, refer to a 
>field, use another table or view, or have inferred / aliased fields or 
>use tables in a sub-query of any kind that will ever be the same (in 
>full or in part) as the table name you are trying to drop for.
>
>It's a slippery slope. Much safer to have a habit to name views like the 
>table names they derive from (when they derive from specific tables), 
>such as
>CREATE VIEW cities_view_someref AS SELECT ... FROM cities WHERE  ;
>CREATE VIEW cities_view_someotherref AS SELECT ... FROM cities WHERE  ;
>
>Then later when you drop everything, either in your code do:
>DROP TABLE cities;
>namelist = (SELECT name FROM sqlite_master WHERE type='view' AND name 
>LIKE 'cities_view_%';)
>Then DROP every view in namelist via code.
>
>--- or by creating an extended function in sqlite3, do this (pseudo coded):
>
>FUNCTION  nmDROPTV(@arg1):
>BEGIN
>   DROP TABLE @arg1;
>   For each name in (SELECT name FROM sqlite_master WHERE type='view' 
>AND name LIKE '[@arg1]_view_%';)
> do DROP VIEW name;
>END;
>Maybe also return the failure/success status of those functions, and 
>perhaps executing them in a transaction, etc.
>
>So then executing:
>DROPTV cities;
>Should have the same effect as the first example.
>
>
>___
>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] Memoization in sqlite json1 functions

[Domingo Alvarez Duarte]

Hello Richard !

I noticed that sqlite do not use any memoization in json1 functions.

For example jsonExtractFunc and others parse the json string every time 
it's called even when the json string is the same.


minimal example : "select json_extract(json, '$.name') name, 
json_extract(json, '$.address') name from some_table;"


Could be possible to have some kind of memoization as a general option 
for any sqlite functions ?


Cheers !


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possibly pointless assert

[Scott Robison]

Take a look at 
http://www.sqlite.org/cgi/src/artifact/3ed64afc49c0a222?ln=2214,2233
(especially the assert within).

I may not be understanding something, but that assert seems pointless
to me. The point of the loop is to check all the columns in an index
to see if they are all binary collated. If any column is not binary
collated, then exit early, which will skip the following if statement
at 2234.

It feels to me like that assert was added as a mid-development sanity
check when it was being developed against a known database. I had it
trip on me today unexpectedly.

If I am incorrect and that is a useful assertion, I'd like to
understand the reason why. Otherwise, the if statement at 2232 does
everything the assert at 2230 does, making the assert fire when the
code is working correctly.

-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[David Raymond]

Just to be sure, have you actually checked your right join syntax on a system 
that supports it? Because I don't think what you have written there will 
actually achieve what you think it will.

There is no three-way join operator that will perform a left and a right join 
. No matter how you word it, one has to happen first and the 
other second.

Using the small sample you gave in one of the emails...

clients left join stock right join suppliers
(clients left join stock...) right join suppliers
(all clients but only stock related to a client) right join suppliers

(Hick1|1
Hick2|2
Hick3|3)

right join suppliers

Hick1|1|Smith1
Hick2|2|Smith2
Hick3|3|Smith3
NULL|NULL|Smith4

You need a union in there to actually accomplish what you're looking for.

I put the small schema into Access, which does allow for right joins, and when 
putting in your query I get:

"The SQL statement could not be executed because it contains ambiguous outer 
joins. To force one of the joins to be performed first, create a separate query 
that performs the first join and then include that query in your SQL statement."

So yeah, right join will not allow you to perform 2 joins at the same time. You 
need a union in there.

Since every join type has the same precedence this isn't the case of "2 + 2 / 2"

"A join B join C" will always be "(A join 
B) join C"

"A LeftJoin B RightJoin C" will always be "(A LeftJoin B) RightJoin C"...
which is the same as "C LeftJoin (A LeftJoin B)"

So you can always re-write a right join as a left join, you're not losing any 
power.
(It may be annoying to rewrite yes, but not any less functional)

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Wednesday, March 22, 2017 9:30 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] RIGHT JOIN! still not supported?

...
The obvious solution was:
SELECT clients.name, suppliers.name
   FROM clients
   LEFT JOIN stock ON stock.client = clients.id
   RIGHT JOIN suppliers ON stock.supplier =  suppliers.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

I suppose this could have worked too:
SELECT clients.name, suppliers.name
   FROM suppliers
   LEFT JOIN stock ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

or even this:
SELECT clients.name, suppliers.name
   FROM stock
   RIGHT JOIN suppliers ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;


I don't see how to achieve that with simple joins in sqlite3. We used 
some CTEs to LEFT JOIN stock to suppliers, and then left-joined that CTE 
on the stock to the clients - which would also work using sub-query 
joins or filtered cross-joins (much slower).

So to add to the discussion: Yeah, we've needed it, but overcoming the 
problem was not exactly difficult and the use case itself is certainly 
not very common (or even mildly common, it's rather uncommon).

Cheers,
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

Re: [sqlite] RIGHT JOIN! still not supported?

[Darren Duncan]

Unless I misunderstand the desired result, this query would be better formulated 
using 2 left joins instead, like this:


  SELECT ...
  FROM Persons LEFT JOIN Pets ... LEFT JOIN PetAccessories ...

-- Darren Duncan

On 2017-03-22 2:22 AM, Chris Locke wrote:

An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have
accessories.

If the requirement is to get a result listing all people irrespective of
whether or not they own a pet and information about any pets they own that
also have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a
derived table that can be left joined to.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Jean-Luc Hainaut]

On 22/03/2017 11:33, 邱朗 wrote:

Hi,


Is there any way to drop view “automatically” when its associated table is 
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to find 
views created based on it and I can drop view manually ?


A quick and dirty procedure:

sqlite> create table T(A,B,C);
sqlite> create view TA as select A,B from T;
sqlite> create view TB as select B,C from T;
sqlite> create view TAB as select A,B,C from TA natural join TB;
sqlite> select * from TAB;
sqlite> drop table T;
sqlite> select * from TAB;
Error: no such table: main.T

Also works for "drop view". Quite easy to code in any host language like 
other exceptions.


J-L Hainaut
https://projects.info.unamur.be/~dbm/mediawiki/index.php/Accueil 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[R Smith]

On 2017/03/22 4:14 PM, Hick Gunter wrote:

Since LEFT JOIN and RIGHT JOIN  while also swapping the tables are 
interchangeable, why should this not work?

SELECT ... FROM clients LEFT JOIN ON ... ( suppliers LEFT JOIN stock ON ...) 
WHERE ...;


HI Gunter,
They are interchangeable for 1 vs. 1 join queries, but as soon as you 
add a second lookup, they are not.
Your query won't work because this will not show ANY items that are NOT 
explicitly part of the clients table. This is the problem with left-joins.


Your query will produce output like this (from my example below):

.  |  | 
.   A  | NULL | NULL
.   B  | NULL |   1
.   C  | NULL | NULL
.   D  |   $  |   2
.   E  |   &  |   4

You can see how that is different from what we needed in the original post.

To save time, I can distill the problem for you to the basics in a script:
--
-- SET-UP:
CREATE TABLE stock(id, cid, sid);

CREATE TABLE clients(id,name);

CREATE TABLE suppliers(id,name);

WITH CC(id) AS (SELECT 1 UNION ALL SELECT id+1 FROM CC WHERE id<7)
INSERT INTO stock(id) SELECT id FROM CC;

UPDATE stock SET cid = id WHERE id < 4;

UPDATE stock SET sid = id - 1 WHERE id BETWEEN 2 AND 5;

INSERT INTO clients   SELECT cid, 'Hick'||cid   FROM stock WHERE cid IS 
NOT NULL;


INSERT INTO suppliers SELECT sid, 'Smith'||sid FROM stock WHERE sid IS 
NOT NULL;


-- Show content:

SELECT * FROM stock;
  --  id  | cid| sid
  --  | -- | --
  --   1  | 1  | NULL
  --   2  | 2  | 1
  --   3  | 3  | 2
  --   4  | NULL   | 3
  --   5  | NULL   | 4
  --   6  | NULL   | NULL
  --   7  | NULL   | NULL

SELECT * FROM clients;
  --  id  | name
  --  | ---
  --   1  | Hick1
  --   2  | Hick2
  --   3  | Hick3

SELECT * FROM suppliers;
  --  id  | name
  --  | 
  --   1  | Smith1
  --   2  | Smith2
  --   3  | Smith3
  --   4  | Smith4

-- One cumbersome solution to get the correct output:
SELECT clients.name, suppliers.name, stock.id
  FROM clients
  LEFT JOIN stock ON stock.cid=clients.id
  LEFT JOIN suppliers ON stock.sid = suppliers.id
UNION
SELECT clients.name, suppliers.name, stock.id
  FROM suppliers
  LEFT JOIN stock ON stock.sid=suppliers.id
  LEFT JOIN clients ON stock.cid = clients.id
ORDER BY 3
;

  -- name| name |  id
  -- --- |  | 
  -- Hick1   | NULL |   1
  -- Hick2   | Smith1   |   2
  -- Hick3   | Smith2   |   3
  -- NULL| Smith3   |   4
  -- NULL| Smith4   |   5


-- Gunter's suggested query (I think, unless I got it wrong):
SELECT clients.name, A.name, stock.id
  FROM clients
  LEFT JOIN stock ON stock.cid = clients.id
  LEFT JOIN (SELECT stock.id, name FROM suppliers LEFT JOIN stock ON 
stock.sid = suppliers.id) AS A ON A.id = stock.id

 ORDER BY 3
;

  -- name| name |  id
  -- --- |  | 
  -- Hick1   | NULL |   1
  -- Hick2   | Smith1   |   2
  -- Hick3   | Smith2   |   3

-- Cleanup;
DROP TABLE suppliers;
DROP TABLE clients;
DROP TABLE stock;

As you can see, they are not the same - Smith3 & 4 never shows up in the 
second query. If you can say how to get to the correct query using 
simple joins (without the Union which is slow on large tables because of 
duplication, and without CTEs), I'd be both impressed and thankful.





-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von R Smith
Gesendet: Mittwoch, 22. März 2017 14:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] RIGHT JOIN! still not supported?


On 2017/03/22 9:53 AM, Eric Grange wrote:

For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?

Personally I never had a need for a RIGHT JOIN, not because of
theoretical or design considerations, but it just never came into my
flow of thought when writing SQL...

I guess some automated SQL query generators could use it though,
because they do not have a "flow of thought".

I had a need of it the other day (something that can easily be overcome with a 
CTE or subquery, but still may have made the sql read more natural):

We have one system which contains (among other things) clients, suppliers and 
stock tables.

A stock item will mostly have a supplier and mostly will have an intended 
client (the production is bespoke) except for a few common materials.

So we needed a list of all suppliers linked to all clients affected by them 
(via Stock), and also see the clients who are not affected by any suppliers 
(which should really be a minimum number) and the suppliers who are not 
affecting any clients (which should also be a small list).
Note that not all suppliers may be linked to live stock items, and not all 
clients may be linked to live stock items 

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 22 Mar 2017, at 11:52am, R Smith  wrote:
> 
> May I add further that views are tricky things. They can refer to multiple 
> tables, or other views which in turn refer to multiple tables or yet other 
> views (and have obscured the names of the original tables via "AS" aliasing). 
> There is no way to easily implement an automatic view-of-table dropper. (I've 
> tried to do something like this for an sqlite tool long ago).

I think I’ve tried to do the equivalent thing for TRIGGERs and also failed.  
And there’s also FOREIGN KEYs, though you can figure those out using PRAGMA 
calls.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Dominique Devienne]

On Wed, Mar 22, 2017 at 1:33 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > On Wed, Mar 22, 2017 at 12:52 PM, R Smith  wrote:
> >> There is no way to easily implement an automatic view-of-table
> >> dropper. (I've tried to do something like this for an sqlite tool long
> ago).
> >
> > Yes but... You can run a simple query on the view, like "select rowid
> from v" and EXPLAIN that,
> > and look for specific OpCodes [1] (like OpenRead), look at its P2 arg,
> and lookup that root page in
> > sqlite_master.
>
> sqlite3_set_authorizer() would be easier, and an official API.
>

yet the same issue I raised almost 2 years ago in [1] is still there though,
i.e. you cannot "stack" authorizers (which imply you cannot
compose/combine) them.

We are still missing a sqlite3_get_authorizer API IMHO. --DD

[1]
http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg89912.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[Stephen Chrzanowski]

On Wed, Mar 22, 2017 at 9:30 AM, R Smith  wrote:

>
> On 2017/03/22 9:53 AM, Eric Grange wrote:
>
>> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>>
>> Personally I never had a need for a RIGHT JOIN, not because of theoretical
>> or design considerations, but it just never came into my flow of thought
>> when writing SQL...
>>
>> I guess some automated SQL query generators could use it though, because
>> they do not have a "flow of thought".
>>
>
> I had a need of it the other day (something that can easily be overcome
> with a CTE or subquery, but still may have made the sql read more natural):
>

I've also needed it for reasons I can't remember, but I know I've used the
functionality.  RIGHT JOIN is a pretty limited use case, but handy when
needed.  I think this OP came from another DBMS software, such as MS, that
does support RIGHT JOIN.  (At least from what memory serves when I took my
MS courses back when MSSQL2K was just coming out)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[Hick Gunter]

Since LEFT JOIN and RIGHT JOIN  while also swapping the tables are 
interchangeable, why should this not work?

SELECT ... FROM clients LEFT JOIN ON ... ( suppliers LEFT JOIN stock ON ...) 
WHERE ...;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von R Smith
Gesendet: Mittwoch, 22. März 2017 14:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] RIGHT JOIN! still not supported?


On 2017/03/22 9:53 AM, Eric Grange wrote:
> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of
> theoretical or design considerations, but it just never came into my
> flow of thought when writing SQL...
>
> I guess some automated SQL query generators could use it though,
> because they do not have a "flow of thought".

I had a need of it the other day (something that can easily be overcome with a 
CTE or subquery, but still may have made the sql read more natural):

We have one system which contains (among other things) clients, suppliers and 
stock tables.

A stock item will mostly have a supplier and mostly will have an intended 
client (the production is bespoke) except for a few common materials.

So we needed a list of all suppliers linked to all clients affected by them 
(via Stock), and also see the clients who are not affected by any suppliers 
(which should really be a minimum number) and the suppliers who are not 
affecting any clients (which should also be a small list).
Note that not all suppliers may be linked to live stock items, and not all 
clients may be linked to live stock items either - but they all need to show up 
in the list.

If I can try my hand at a schematic of the sets showing links:
clients:   [ A  B  C  D E   ]
-  links| | |
stock: [1 2  3  4  5]
-  links  |  |  |
suppliers: [ @  # $  %  & ! ]

I would need to see a "linking" query result showing overlap and non-links like 
this:
. clnt | supp| stck
.  |  | 
.   A  | NULL | NULL
.   B  | NULL|   1
.   C  | NULL| NULL
.   D  |   $  |   2
.   E  |   &  |   4
. NULL |   @ | NULL
. NULL |   # | NULL
. NULL |   % |   3
. NULL |   ! | NULL

Note that I don't actually need the stck column, just adding it to show more 
sensible results.
Also note that items linked to neither client nor supplier need not show up 
(stock item 5 in this case), so I only need results having either a client or a 
supplier.


The obvious solution was:
SELECT clients.name, suppliers.name
   FROM clients
   LEFT JOIN stock ON stock.client = clients.id
   RIGHT JOIN suppliers ON stock.supplier =  suppliers.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ;

I suppose this could have worked too:
SELECT clients.name, suppliers.name
   FROM suppliers
   LEFT JOIN stock ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ;

or even this:
SELECT clients.name, suppliers.name
   FROM stock
   RIGHT JOIN suppliers ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ;


I don't see how to achieve that with simple joins in sqlite3. We used some CTEs 
to LEFT JOIN stock to suppliers, and then left-joined that CTE on the stock to 
the clients - which would also work using sub-query joins or filtered 
cross-joins (much slower).

So to add to the discussion: Yeah, we've needed it, but overcoming the problem 
was not exactly difficult and the use case itself is certainly not very common 
(or even mildly common, it's rather uncommon).

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[R Smith]

On 2017/03/22 9:53 AM, Eric Grange wrote:

For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?

Personally I never had a need for a RIGHT JOIN, not because of theoretical
or design considerations, but it just never came into my flow of thought
when writing SQL...

I guess some automated SQL query generators could use it though, because
they do not have a "flow of thought".


I had a need of it the other day (something that can easily be overcome 
with a CTE or subquery, but still may have made the sql read more natural):


We have one system which contains (among other things) clients, 
suppliers and stock tables.


A stock item will mostly have a supplier and mostly will have an 
intended client (the production is bespoke) except for a few common 
materials.


So we needed a list of all suppliers linked to all clients affected by 
them (via Stock), and also see the clients who are not affected by any 
suppliers (which should really be a minimum number) and the suppliers 
who are not affecting any clients (which should also be a small list). 
Note that not all suppliers may be linked to live stock items, and not 
all clients may be linked to live stock items either - but they all need 
to show up in the list.


If I can try my hand at a schematic of the sets showing links:
clients:   [ A  B  C  D E   ]
-  links| | |
stock: [1 2  3  4  5]
-  links  |  |  |
suppliers: [ @  # $  %  & ! ]

I would need to see a "linking" query result showing overlap and 
non-links like this:

. clnt | supp| stck
.  |  | 
.   A  | NULL | NULL
.   B  | NULL|   1
.   C  | NULL| NULL
.   D  |   $  |   2
.   E  |   &  |   4
. NULL |   @ | NULL
. NULL |   # | NULL
. NULL |   % |   3
. NULL |   ! | NULL

Note that I don't actually need the stck column, just adding it to show 
more sensible results.
Also note that items linked to neither client nor supplier need not show 
up (stock item 5 in this case), so I only need results having either a 
client or a supplier.



The obvious solution was:
SELECT clients.name, suppliers.name
  FROM clients
  LEFT JOIN stock ON stock.client = clients.id
  RIGHT JOIN suppliers ON stock.supplier =  suppliers.id
 WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

I suppose this could have worked too:
SELECT clients.name, suppliers.name
  FROM suppliers
  LEFT JOIN stock ON stock.supplier = suppliers.id
  RIGHT JOIN clients ON stock.client =  clients.id
 WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

or even this:
SELECT clients.name, suppliers.name
  FROM stock
  RIGHT JOIN suppliers ON stock.supplier = suppliers.id
  RIGHT JOIN clients ON stock.client =  clients.id
 WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;


I don't see how to achieve that with simple joins in sqlite3. We used 
some CTEs to LEFT JOIN stock to suppliers, and then left-joined that CTE 
on the stock to the clients - which would also work using sub-query 
joins or filtered cross-joins (much slower).


So to add to the discussion: Yeah, we've needed it, but overcoming the 
problem was not exactly difficult and the use case itself is certainly 
not very common (or even mildly common, it's rather uncommon).


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Clemens Ladisch]

Dominique Devienne wrote:
> On Wed, Mar 22, 2017 at 12:52 PM, R Smith  wrote:
>> There is no way to easily implement an automatic view-of-table
>> dropper. (I've tried to do something like this for an sqlite tool long ago).
>
> Yes but... You can run a simple query on the view, like "select rowid from v" 
> and EXPLAIN that,
> and look for specific OpCodes [1] (like OpenRead), look at its P2 arg, and 
> lookup that root page in
> sqlite_master.

sqlite3_set_authorizer() would be easier, and an official API.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Dominique Devienne]

On Wed, Mar 22, 2017 at 12:52 PM, R Smith  wrote:

> On 2017/03/22 12:37 PM, Richard Hipp wrote:
>
>> On 3/22/17, 邱朗  wrote:
>>
>>> Is there any way to drop view “automatically” when its associated table
>>> is dropped?
>>>
>>> There is no easy way to figure out what tables a view refers to.
>> Furthermore, some applications might want to drop a table and then
>> recreate that table (perhaps after adding a new constraint or
>> reordering the columns) and would like to keep using the VIEW.  Those
>> applications would not want the VIEW to be dropped automatically.
>>
>
> May I add further that views are tricky things. They can refer to multiple
> tables, or other views which in turn refer to multiple tables or yet other
> views (and have obscured the names of the original tables via "AS"
> aliasing). There is no way to easily implement an automatic view-of-table
> dropper. (I've tried to do something like this for an sqlite tool long ago).
>

Yes but... You can run a simple query on the view, like "select rowid from
v" and EXPLAIN that,
and look for specific OpCodes [1] (like OpenRead), look at its P2 arg, and
lookup that root page in
sqlite_master. And if there was a way to use EXPLAIN in a join-query like
is now possible with the
new pragma table-valued functions, that could be simplified.

Compiled statements "flatten" nested views I imagine, since at the end of
the day, really
IO on real tables must be performed to run the query. FWIW. --DD

[1] https://www.sqlite.org/opcode.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[R Smith]

On 2017/03/22 12:37 PM, Richard Hipp wrote:

On 3/22/17, 邱朗  wrote:

Hi,


Is there any way to drop view “automatically” when its associated table is
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to
find views created based on it and I can drop view manually ?


There is no easy way to figure out what tables a view refers to.
Furthermore, some applications might want to drop a table and then
recreate that table (perhaps after adding a new constraint or
reordering the columns) and would like to keep using the VIEW.  Those
applications would not want the VIEW to be dropped automatically.



May I add further that views are tricky things. They can refer to 
multiple tables, or other views which in turn refer to multiple tables 
or yet other views (and have obscured the names of the original tables 
via "AS" aliasing). There is no way to easily implement an automatic 
view-of-table dropper. (I've tried to do something like this for an 
sqlite tool long ago).


You could even look for the table name referenced in the view sql (by 
simply doing something like:
SELECT name FROM sqlite_master WHERE type='view' AND sql LIKE 
'%MyTableName%';
and drop all of those views that show up in the result - BUT, then you 
have to be absolutely sure no View would ever use a field, refer to a 
field, use another table or view, or have inferred / aliased fields or 
use tables in a sub-query of any kind that will ever be the same (in 
full or in part) as the table name you are trying to drop for.


It's a slippery slope. Much safer to have a habit to name views like the 
table names they derive from (when they derive from specific tables), 
such as

CREATE VIEW cities_view_someref AS SELECT ... FROM cities WHERE  ;
CREATE VIEW cities_view_someotherref AS SELECT ... FROM cities WHERE  ;

Then later when you drop everything, either in your code do:
DROP TABLE cities;
namelist = (SELECT name FROM sqlite_master WHERE type='view' AND name 
LIKE 'cities_view_%';)

Then DROP every view in namelist via code.

--- or by creating an extended function in sqlite3, do this (pseudo coded):

FUNCTION  nmDROPTV(@arg1):
BEGIN
  DROP TABLE @arg1;
  For each name in (SELECT name FROM sqlite_master WHERE type='view' 
AND name LIKE '[@arg1]_view_%';)

do DROP VIEW name;
END;
Maybe also return the failure/success status of those functions, and 
perhaps executing them in a transaction, etc.


So then executing:
DROPTV cities;
Should have the same effect as the first example.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Richard Hipp]

On 3/22/17, 邱朗  wrote:
> Hi,
>
>
> Is there any way to drop view “automatically” when its associated table is
> dropped?
> It seems no way to do. Then if I drop a table, is there any (easy) way to
> find views created based on it and I can drop view manually ?
>

There is no easy way to figure out what tables a view refers to.
Furthermore, some applications might want to drop a table and then
recreate that table (perhaps after adding a new constraint or
reordering the columns) and would like to keep using the VIEW.  Those
applications would not want the VIEW to be dropped automatically.

-- 
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] Drop view “automatically” when its associated table is dropped?

[邱朗]

Hi,


Is there any way to drop view “automatically” when its associated table is 
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to find 
views created based on it and I can drop view manually ?


Thanks


Qiulang
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[Vladimir Vissoultchev]

You don't need derived tables, just use brackets for explicitly order the 
execution of JOIN operators like this:

SELECT  P.PersonName
, Pt.PetName
, Pa.AccessoryName
FROMPersons P
LEFT JOIN   (   Pets Pt
JOINPetAccessories Pa
ON  Pt.PetName = Pa.PetName)
ON  P.PersonName = Pt.PersonName;

JOIN ordering is handy especially if you have to LEFT JOIN more tables to 
Persons, then using RIGHT JOIN obfuscates the code unnecessary. IMO this 
ordering pattern comes up often in reporting queries.

And this reminds me of "How much is 2 + 2 * 2?" puzzle on calculator vs excel 
:-))

cheers,



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Chris Locke
Sent: Wednesday, March 22, 2017 11:22 AM
To: SQLite mailing list 
Subject: Re: [sqlite] RIGHT JOIN! still not supported?

An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have accessories.

If the requirement is to get a result listing all people irrespective of 
whether or not they own a pet and information about any pets they own that also 
have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a 
derived table that can be left joined to.

On Wed, Mar 22, 2017 at 7:53 AM, Eric Grange  wrote:

> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of 
> theoretical or design considerations, but it just never came into my 
> flow of thought when writing SQL...
>
> I guess some automated SQL query generators could use it though, 
> because they do not have a "flow of thought".
>
> On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
> 
> wrote:
>
> > What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries 
> > are more natural to write using the first rather than the second?
> >
> > While I can understand arguments based on simple mirror parity, eg 
> > we
> have
> > < so we should have > too, lots of other operations don't have 
> > mirror syntax either.
> >
> > -- Darren Duncan
> >
> > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
> >
> >> Seeing how SQLite was created in 2000, it seems like nobody really 
> >> needed this feature for the last 17 years enough in order to 
> >> actually implement it.
> >>
> >> Last I heard, patches are welcome on this mailing list. Don't keep 
> >> us waiting.
> >>
> >> Kind regards,
> >> Daniel
> >>
> >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
> >> 
> >> wrote:
> >>
> >>> i got this
> >>>
> >>> Query Error: RIGHT and FULL OUTER JOINs are not currently 
> >>> supported Unable to execute statement
> >>>
> >>> still today in 21 ts century?
> >>>
> >>> Lenz McKAY Gerardo (PICCORO)
> >>> http://qgqlochekone.blogspot.com
> >>>
> >>
> > ___
> > 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] RIGHT JOIN! still not supported?

[Chris Locke]

An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have
accessories.

If the requirement is to get a result listing all people irrespective of
whether or not they own a pet and information about any pets they own that
also have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a
derived table that can be left joined to.

On Wed, Mar 22, 2017 at 7:53 AM, Eric Grange  wrote:

> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of theoretical
> or design considerations, but it just never came into my flow of thought
> when writing SQL...
>
> I guess some automated SQL query generators could use it though, because
> they do not have a "flow of thought".
>
> On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
> wrote:
>
> > What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries are
> > more natural to write using the first rather than the second?
> >
> > While I can understand arguments based on simple mirror parity, eg we
> have
> > < so we should have > too, lots of other operations don't have mirror
> > syntax either.
> >
> > -- Darren Duncan
> >
> > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
> >
> >> Seeing how SQLite was created in 2000, it seems like nobody really
> >> needed this feature for the last 17 years enough in order to actually
> >> implement it.
> >>
> >> Last I heard, patches are welcome on this mailing list. Don't keep us
> >> waiting.
> >>
> >> Kind regards,
> >> Daniel
> >>
> >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
> >> wrote:
> >>
> >>> i got this
> >>>
> >>> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
> >>> Unable to execute statement
> >>>
> >>> still today in 21 ts century?
> >>>
> >>> Lenz McKAY Gerardo (PICCORO)
> >>> http://qgqlochekone.blogspot.com
> >>>
> >>
> > ___
> > 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] SQLite and LINQ in C#

[Florian Lorenzen]

Hello,

I tried to use System.Data.SQLite together with LINQ (LINQ-to-SQL that is). 
Basically, I had the following classes:

class MsgDatabase : DataContext
{
public MsgDatabase(IDbConnection connection) : base(connection)
{
}
public readonly Table Msg = null;
}
}


[Table]

class Msg

{

[Column(IsPrimaryKey = true)] public long Ticks;

[Column] public byte[] Payload;

}

Then, I connected to the database by

var db = new MsgDatabase(new SQLiteConnection(...));

Selecting from the Msg table like

var foo = from m in db.Msg where m.Ticks > 10048;

just worked fine.

But OrderBy together with First lead to invalid SQL code:

var bar = db.Msg.OrderBy(m => m.Ticks).First();

The generated code contained select top (1), which is not understood by SQLite. 
It should be limit 1 instead.

So, finally here is my question: there seems to be a SQLite-code generator in 
the namespace Data.System.SQLite.Linq which obviously is not used in the 
setting I sketched (but the T-SQL code generator from .net). Is my 
understanding correct that the latter code generator is only in action when 
Entity Framework is used or is there any way to use it also in a plain 
LINQ-to-SQL setting?

Thanks,

Florian

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help to create a connection to SQLite database!

[Clemens Ladisch]

Evgeniy Buzin wrote:
> "Settings "Prism.Mvvm.ViewModelLocator.AutoWireViewModel" property has
> called exception...".

That "..." contains relevant information.

> System.Windows.Markup.XamlParseException
>
> What is the reason of this error?

Something related with XAML parsing.  So not related with SQLite.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with establishing a connection on NS3 and sqlite DB

[Dan Kennedy]

On 03/22/2017 05:58 AM, Ausama Majeed wrote:

Hello guys,

I am trying to do a connection between a database created with Sqlite and
my application in ns3. the sqlite engine is installed on ubuntu 16.04
machine and the output is enabled with ns3.26. I cann't do a simple select
query from ns3, however it working through the terminal.
I install sqlite-autoconf-317 as API to deal with the database.

the BD is opened successfully in the fallowing code:

sqlite3 *db;
 int rc;
 char *error = 0;
 rc = sqlite3_open("/home/mypc/Desktop/ns-3.26/ns-3.26/testDB.db", );
 if (rc) {
 cerr << "Error opening SQLite3 database: " << sqlite3_errmsg(db) <<
endl << endl;
 sqlite3_close(db);
 return 1;
 } else {
 cout << "\n Successfully connected to the database \n";
 int n = 0;
 cin >> n;
 // Print this info

  cout << GARIComposeAlgo(db, error, n);
 cout << "\nclose the db\n";
 sqlite3_close(db);
}


But, select query returns only the table field headers instead of the
required record   in the following code


As expected, I think. If your SELECT statement returns N rows of M 
columns, get_table() gives you an array of (N+1)*M nul-terminated 
strings. The first M strings in the array are the column headers, the 
next M are the first row of results, and so on.


  https://sqlite.org/c3ref/free_table.html

Dan.






string Query = " select ActorId, ActorType from ActorInfo where ID =" +
tempProcess.str() +";";


 char **results = NULL;
 int rows, columns;
 const char *sqlSelect = Query.c_str();
 int rc;
 rc = sqlite3_get_table(db, sqlSelect, , , ,
);
 if (rc != SQLITE_OK) {
 cerr << "Error executing SQLite3 query: " << sqlite3_errmsg(db) <<
endl << endl;
 sqlite3_free(error);
 }
 else {
for (int i= 0; i<4; i++) {cout << results[i]<< endl;}
}

Could anyone advice me what could the problem and how to check it, solve it
please.

Thanks
___
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] RIGHT JOIN! still not supported?

[Eric Grange]

For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?

Personally I never had a need for a RIGHT JOIN, not because of theoretical
or design considerations, but it just never came into my flow of thought
when writing SQL...

I guess some automated SQL query generators could use it though, because
they do not have a "flow of thought".

On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
wrote:

> What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries are
> more natural to write using the first rather than the second?
>
> While I can understand arguments based on simple mirror parity, eg we have
> < so we should have > too, lots of other operations don't have mirror
> syntax either.
>
> -- Darren Duncan
>
> On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
>
>> Seeing how SQLite was created in 2000, it seems like nobody really
>> needed this feature for the last 17 years enough in order to actually
>> implement it.
>>
>> Last I heard, patches are welcome on this mailing list. Don't keep us
>> waiting.
>>
>> Kind regards,
>> Daniel
>>
>> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
>> wrote:
>>
>>> i got this
>>>
>>> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
>>> Unable to execute statement
>>>
>>> still today in 21 ts century?
>>>
>>> Lenz McKAY Gerardo (PICCORO)
>>> http://qgqlochekone.blogspot.com
>>>
>>
> ___
> 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