Quantcast

recursive clause

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: recursive clause

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: recursive clause

Cem Dayanik (Ibtech-Software Infrastructure)
Thx, thats where i am headed

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Clemens Ladisch
Sent: Wednesday, April 05, 2017 9:53 AM
To: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...