Re: [sqlite] recursive clause

[Cem Dayanik (Ibtech-Software Infrastructure)]

Thx, thats where i am headed

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clemens Ladisch
Sent: Wednesday, April 05, 2017 9:53 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] recursive clause

Cem Dayanik (Ibtech-Software Infrastructure) wrote:
> CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress
> INTEGER) CREATE TABLE Instances (TypeId INTEGER, Address INTEGER)
> CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count
> INT, TotalSize INTEGER)
>
> What I need is, start with an address and add all reference addresses to the 
> list -with increasing level- unless it is already there.
>
> Not working with:
> and refbyaddress not in (select address from allreferences)
>
> select * from (
> WITH RECURSIVE
>   allreferences(address, level) AS (
>   values(435582892, 0)
>   union
> SELECT refbyaddress, allreferences.level+1 FROM instancereferences, 
> allreferences
>WHERE instancereferences.instanceaddress=allreferences.address and 
> allreferences.level < 1 and refbyaddress not in (select address from 
> allreferences)
>   )
> SELECT* FROM allreferences ) r, instances ins , types t where r.n =
> ins.address and ins.typeid=t.id

The documentation says (http://www.sqlite.org/lang_with.html#recursivecte):
| The table named on the left-hand side of the AS keyword must appear
| exactly once in the FROM clause of the right-most SELECT statement of
| the compound select, and nowhere else.

If you omit the level, the UNION automatically prevents loops.

Do you really need the level?  It might be possible to compute it in SQL 
afterwards, but that is likely to be inefficient.  It might be a better idea to 
run Dijkstra's algorithm in your code.


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


Bu e-posta'nin içerdigi bilgiler (ekleri dahil olmak
üzere) gizlidir. Onayimiz olmaksizin üçüncü kisilere açiklanamaz. Bu mesajin
gönderilmek istendigi kisi degilseniz, lütfen mesaji sisteminizden derhal
siliniz. IBTech A.S. bu mesajin içerdigi bilgilerin dogrulugu veya eksiksiz
oldugu konusunda bir garanti vermemektedir. Bu nedenle bilgilerin ne sekilde
olursa olsun içeriginden, iletilmesinden, alinmasindan, saklanmasindan sorumlu
degildir. Bu mesajin içerigi yazarina ait olup, IBTech A.S.'nin görüslerini
içermeyebilir.

The information contained in this e-mail (including any
attachments)is confidential. It must not be disclosed to any person without our
authority. If you are not the intended recipient, please delete it from your
system immediately. IBTech A.S. makes no warranty as to the accuracy or
completeness of any information contained in this message and hereby excludes
any liability of any kind for the information contained therein or for the
information transmission, reception, storage or use of such in any way
whatsoever. Any opinions expressed in this message are those of the author and
may not necessarily reflect the opinions of IBTech
A.S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] recursive clause

[Cem Dayanik (Ibtech-Software Infrastructure)]

Hello there,

I am trying to find a memory leak with MemoScope.
It is using sqllite.
Memoscope has some issue to find the problem so I am actually querying the data.

CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress INTEGER)
CREATE TABLE Instances (TypeId INTEGER, Address INTEGER)
CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count INT, 
TotalSize INTEGER)

What I need is, start with an address and add all reference addresses to the 
list -with increasing level- unless it is already there.

Any insights?

Not working with:
and refbyaddress not in (select address from allreferences)

select * from (
WITH RECURSIVE
  allreferences(address, level) AS (
  values(435582892, 0)
  union
SELECT refbyaddress, allreferences.level+1 FROM instancereferences, 
allreferences
   WHERE instancereferences.instanceaddress=allreferences.address and 
allreferences.level < 1 and refbyaddress not in (select address from 
allreferences)
  )
SELECT* FROM allreferences ) r, instances ins , types t where r.n = ins.address 
and ins.typeid=t.id




Bu e-posta'n?n i?erdi?i bilgiler (ekleri dahil olmak ?zere) gizlidir. Onay?m?z 
olmaks?z?n ???nc? ki?ilere a?iklanamaz. Bu mesaj?n g?nderilmek istendi?i ki?i 
de?ilseniz, l?tfen mesaj? sisteminizden derhal siliniz. IBTech A.?. bu mesaj?n 
i?erdi?i bilgilerin do?rulu?u veya eksiksiz oldu?u konusunda bir garanti 
vermemektedir. Bu nedenle bilgilerin ne ?ekilde olursa olsun i?eri?inden, 
iletilmesinden, al?nmas?ndan, saklanmas?ndan sorumlu de?ildir. Bu mesaj?n 
i?eri?i yazar?na ait olup, IBTech A.?.'nin g?r??lerini i?ermeyebilir.

The information contained in this e-mail (including any attachments)is 
confidential. It must not be disclosed to any person without our authority. If 
you are not the intended recipient, please delete it from your system 
immediately. IBTech A.S. makes no warranty as to the accuracy or completeness 
of any information contained in this message and hereby excludes any liability 
of any kind for the information contained therein or for the information 
transmission, reception, storage or use of such in any way whatsoever. Any 
opinions expressed in this message are those of the author and may not 
necessarily reflect the opinions of IBTech A.S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users