SQL CTE expression to obtain Docker parent image

Each docker start create temporary environment for working docker container and reference to previous base environment, Docker inspect give us reference to parent docker image.

Another way to receive hierarchy of docker images is using Docker.DotNet library.

In any case we can receive this table.

And now we have a huge question - what image we can delete, what images inherits from another? To solve this problem I have created CTE expression.

   1:  WITH RECURSIVE 

   2:  CTE (CTE_i, CTE_Id, CTE_ParentID, CTE_ParentI,  Hierarchy) AS

   3:  (

   4:  Select i, Id,  CAST('' AS CHAR(1000)),  CAST('' AS CHAR(1000)), CAST(i AS CHAR(1000)) as Hierarchy FROM DockerImage where  ParentId =''

   5:  UNION ALL

   6:  Select DockerImage.I, DockerImage.Id, DockerImage.ParentId, CAST(CTE_i AS CHAR(1000)) , Concat(Hierarchy,"->" ,CAST(DockerImage.I AS CHAR(1000)) ) as Hierarchy FROM CTE

   7:  JOIN    DockerImage  ON CTE.CTE_id = DockerImage.Parentid

   8:  )

   9:  SELECT * FROM CTE;

Other my topic about CTE expression - Хранение в MS SQL сетевой топологии и отборы маршрутов рекурсивными CTE-процедурами..

Comments ( )