Datenbank nach IDs durchlaufen

[RamsesV]

Hallo liebe Forenfamilie!

Da das mein allererster Post ist, auch noch eine kurze Vorstellung meinerseits: Ich bin 25, im Technischen Support tätig und spiele in meiner Freizeit gern an meinen Servern rum, wenn ich nicht gerade ein MMO spiele oder irgendein SciFi Buch lese. Ich finde mich witzig und gesellig, andere denken da anders Registriert habe ich mich hier, weil ich mein Problem einfach nicht googlen kann, da ich nicht weiß, wo nach ich suche...

Ich kann mich grundsätzlich in Datenbanken mit SQL bewegen, solang es nicht um anspruchsvolle Abfrage geht. Da ich mir das selbst beigebracht habe, weiß ich leider nicht, wozu SQL eigentlich in der Lage ist...

Folgende Situation liegt mir vor: Ich habe eine PostgreSQL DB, welche ich auslesen soll. Prinzipiell geht es darum große Dateien ausfindig zu machen und deren Pfad herauszufinden.

Ich habe einen table namens "object" welcher folgendermaßen aufgebaut ist:

   id    | parent_object_id |              name               | class_id |     size     |         ctime          | rtime | share_id |         mtime          | subclass_id | restore_order | container_id
---------+------------------+---------------------------------+----------+--------------+------------------------+-------+----------+------------------------+-------------+---------------+--------------

Wichtig sind hier id, parent_object_id, name, size und container_id.

Meine Abfrage für Dateien größer 1 GB ist folgende:

SELECT object.name AS Filename, ROUND(CAST(object.size/POW(2,30) AS numeric),3) AS "Size in GB"
FROM object
WHERE size/POW(2,30) > 1 AND object.rtime IS NULL AND object.parent_object_id IS NOT NULL AND object.id != object.container_id
ORDER BY object.size DESC;

Soweit so gut. Das funktioniert, wie ichs mir vorstelle
Nun möchte ich eine weitere Spalte mit dem Pfad. Das Problem hierbei ist, dass der Pfad nicht einfach als Pfad gespeichert ist, sondern Kaskatiert ist:

Am besten zeige ich das anhand eines Beispieles:

agent=> select name,id, parent_object_id,container_id from object where name ilike 'Mailbox Database 1104667002.edb';
  name  |  id  | parent_object_id | container_id
---------------------------------+---------+------------------+--------------
 Mailbox Database 1104667002.edb | 1372804 |  1045346 |  1045337
(1 row)

agent=> select name,id, parent_object_id,container_id from object where id = 1045346;
  name  |  id  | parent_object_id | container_id
-----------------------------+---------+------------------+--------------
 Mailbox Database 1104667002 | 1045346 |  1045345 |  1045337
(1 row)

agent=> select name,id, parent_object_id,container_id from object where id = 1045345;
  name  |  id  | parent_object_id | container_id
---------+---------+------------------+--------------
 Mailbox | 1045345 |  1045344 |  1045337
(1 row)

agent=> select name,id, parent_object_id,container_id from object where id = 1045344;
 name |  id  | parent_object_id | container_id
------+---------+------------------+--------------
 V14  | 1045344 |  1045343 |  1045337
(1 row)

agent=> select name,id, parent_object_id,container_id from object where id = 1045343;
  name  |  id  | parent_object_id | container_id
-----------------+---------+------------------+--------------
 Exchange Server | 1045343 |  1045342 |  1045337
(1 row)

agent=> select name,id, parent_object_id,container_id from object where id = 1045342;
  name  |  id  | parent_object_id | container_id
-----------+---------+------------------+--------------
 Microsoft | 1045342 |  1045341 |  1045337
(1 row)

agent=> select name,id, parent_object_id,container_id from object where id = 1045341;
  name  |  id  | parent_object_id | container_id
---------------+---------+------------------+--------------
 Program Files | 1045341 |  1045340 |  1045337
(1 row)

agent=> select name,id, parent_object_id,container_id from object where id = 1045340;
 name |  id  | parent_object_id | container_id
------+---------+------------------+--------------
 E:  | 1045340 |  1045337 |  1045337
(1 row)

Also die mit der parent_object_id navigiere ich rückwärts durch den Pfad. So lang bis die container_id gleich der parent_object_id ist. Dass die parent_object_id immer eins kleiner, als die id ist, ist Zufall. Ist nicht bei allen Datensätzen so.

So nun meine Frage: Wie mache ich das am besten? Ich dachte erst an eine Schleife, die durchläuft, aber Schleifen in Datenbanken?! Ich glaube, das geht eigentlich recht eifnach, ich weiß nur nicht, nach was ich suchen soll. JOINs sinds ja keine, oder? Ich bäruchte Hauptsächlich einen Denkanstoß, wie ich das lösen könnte.

Grüße
RamsesV

 

[Hony%]

Ich glaube, das geht eigentlich recht eifnach, ich weiß nur nicht, nach was ich suchen soll.

Ich denke was du suchst sind rekursive Common Table Expressions.
http://www.postgresql.org/docs/current/static/queries-with.html

Wenn du weitere Fragen dazu hast kannst du sie hier gerne stellen.

Gruß
Hony

 

[akretschmer]

Nun möchte ich eine weitere Spalte mit dem Pfad. Das Problem hierbei ist, dass der Pfad nicht einfach als Pfad gespeichert ist, sondern Kaskatiert ist:

Also die mit der parent_object_id navigiere ich rückwärts durch den Pfad. So lang bis die container_id gleich der parent_object_id ist. Dass die parent_object_id immer eins kleiner, als die id ist, ist Zufall. Ist nicht bei allen Datensätzen so.

So nun meine Frage: Wie mache ich das am besten?

Seit Version 8.4 kann PostgreSQL rekursive Abfragen, die helfen Dir hier:

test=*# select * from ramses ;
  id  | parent_id | container
---------+-----------+-----------
 1045340 |  1045337 |  1045337
 1045341 |  1045340 |  1045337
 1045342 |  1045341 |  1045337
 1045343 |  1045342 |  1045337
 1045344 |  1045343 |  1045337
(5 rows)

test=*# with recursive r as (select * from ramses where id = 1045344 union all select ramses.* from ramses, r where ramses.id=r.parent_id) select * from r;  id  | parent_id | container
---------+-----------+-----------
 1045344 |  1045343 |  1045337
 1045343 |  1045342 |  1045337
 1045342 |  1045341 |  1045337
 1045341 |  1045340 |  1045337
 1045340 |  1045337 |  1045337
(5 rows)

test=*# with recursive r as (select * from ramses where id = 1045344 union all select ramses.* from ramses, r where ramses.id=r.parent_id and ramses.parent_id != ramses.container) select * from r;
  id  | parent_id | container
---------+-----------+-----------
 1045344 |  1045343 |  1045337
 1045343 |  1045342 |  1045337
 1045342 |  1045341 |  1045337
 1045341 |  1045340 |  1045337
(4 rows)

Bei der dritten Form habe ich noch als Abbruchbedingung parent_id != container, dadurch bricht es eine Stufe höher ab, aber im Prinzip hast da ja auch schon die nötige Info.


http://www.postgresql.org/docs/current/static/queries-with.html


Andreas