(SQL) SQL (2022)

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;


And next two steps. Firstly need to calculate count of hierarchy level.



And secondary I have created procedure to find maximum level of DockerImage inheritance.


   1:  CREATE PROCEDURE `DockerImageHierarchy`(
   2:     prm1 varchar(5)
   3:  )
   4:  SELECT * FROM cryptochestmax.GetDockerImageHierarchy
   5:  where Hierarchy LIKE concat('%',prm1,'%') and Level = (select MAX(Level) from cryptochestmax.GetDockerImageHierarchy where Hierarchy LIKE concat('%',prm1,'%'))


And finally I can see all hierarchy of concrete Docker Image.



So, on my docker any docker images in this screen produced from image number 305.


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



Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21
Link to this page: http://www.vb-net.com/CTE-DockerImage/Index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>