Delete the list of unused DAM images in database through an UI in Adobe Experience Manager

Question

I am new to Adobe Experience Manager. My task is to retrieve the list of unused images from database and needs to delete that through an UI.

I have given a Select query which displays list of unused images. After google search I got some idea that is how to use QueryBuilder in adobe experience manager but I not aware of where to place this select query in the below code.

SQLQuery = "Select * from [nt:file] 
            where isdesplay([/home/cam/Bwits/master/images/top_images]) 
            and NAME() LIKE 'cq5dam.thumbnail.140.100.png'";

The below code also I got it from adobe support website. For normal Jsp I know how to retrieve the list of files but now for the same in JSP with adobe (AEM) api no idea

I found some code an adobe help blog that is how to delete a node or folder from jsp.

How to delete a content node from a JSP in CQ authoring environments

Delete via AJAX Call

Node Path to Delete: (e.g. ‘/content/testdelete/deletePage1’)
Char-set:
Command:
Force Delete: (true/false)

  <div id=”respText”></div>

function performDelete() {
    var response = null;
    var url = “/bin/wcmcommand”;

    var pathObj= document.getElementById(‘pathAJ’);

    if(pathObj)
    {

         var params = “path=”+encodeURIComponent(pathObj.value)+”&_charset_=utf-8&cmd=deletePage&force=false”;
         var request = document.all ? new ActiveXObject(“Microsoft.XMLHTTP”) : new XMLHttpRequest();
        //alert(params);
         request.open(“post”, url, false);
         request.setRequestHeader(“Content-Type”, “application/x-www-form-urlencoded”);
         request.send(params);
        var resp= request.responseText;

        document.getElementById(‘respText’).innerHTML=resp;

    }
    }                               
    </script>  
</HTML>

Now I have to get the list of nodes with help of SQL Query and needs to delete that nodes at a time from this JSP.

Kindly suggest me where to place the sql query for retrieving the list of images from database. Thanks for your cooperation in advance

Answer 1

As others have pointed out, there are several forums (mostly Adobe) posts out there with various approaches to address this question. However, in the spirit of diversity and enriching SO, I think this question warrants a fresh answer.

The following solution was based on JS but there is no reason why this logic cannot be implemented in any other language like Java, Ruby, bash (using CURL) or PowerShell. Also, this approach will not work for DAM assets that are referenced from CSS and JS files.

Taking the example of geometrixx OOTB DAM library, consider that most of the assets are under /content/dam/geometrixxx folder

You need a to build a recursively iterative loop that traverses the DAM library in a linear way one level at a time. This can be done using the logic below:

Step 1: Enumarate

Get root level folder structure for your content with following HTTP call:

http://localhost:4502/content/dam/geometrixx.1.json

This will return something like:

{
   "jcr:primaryType":"sling:OrderedFolder",
   "jcr:mixinTypes":[
      "rep:AccessControllable"
   ],
   "jcr:createdBy":"admin",
   "jcr:title":"Geometrixx",
   "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000",
   "portraits":{
      "jcr:primaryType":"sling:OrderedFolder",
      "jcr:createdBy":"admin",
      "jcr:title":"Portraits",
      "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000"
   },
   "drm":{
      "jcr:primaryType":"sling:OrderedFolder",
      "jcr:createdBy":"admin",
      "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000"
   },
   "banners":{
      "jcr:primaryType":"sling:OrderedFolder",
      "jcr:createdBy":"admin",
      "jcr:title":"Banners",
      "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000"
   }
}

Step 2: Iterate

Recursively iterate through this structure until you reach the DAM assets for the project. For example, you when iterating: http://localhost:4502/content/dam/geometrixx/banners.1.json you will get something like:

{
   "jcr:primaryType":"sling:OrderedFolder",
   "jcr:createdBy":"admin",
   "jcr:title":"Banners",
   "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000",
   "banner-mono.png":{
      "jcr:primaryType":"dam:Asset",
      "jcr:createdBy":"admin",
      "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000"
   },
   "banner-retro.png":{
      "jcr:primaryType":"dam:Asset",
      "jcr:createdBy":"admin",
      "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000"
   },
   "banner-web20.png":{
      "jcr:primaryType":"dam:Asset",
      "jcr:createdBy":"admin",
      "jcr:created":"Mon Feb 27 2017 13:54:20 GMT+0000"
   }
}

So the key logic is to get children of folder nodes and process all the dam:Asset nodes.

Step 3: Find References

Once you hit a DAM asset, you can use the built in reference finder from http://localhost:4502/bin/wcm/references.json?path= to find the references.

For example, using the references utility as below:

http://localhost:4502/bin/wcm/references.json?path=/content/dam/geometrixx/banners/techsummit.jpg

will get you all the references as:

    {
   "pages":[
      {
         "srcPath":"/content/dam/geometrixx/banners/techsummit.jpg",
         "srcTitle":"Tech Summit",
         "path":"/content/geometrixx/en/events/techsummit",
         "title":"TechSummit",
         "references":[
            "/content/geometrixx/en/events/techsummit/jcr:content/image/fileReference",
            "/content/geometrixx/en/events/techsummit/jcr:content/par/image/fileReference"
         ],
         "published":false,
         "isPage":"true"
      }
   ]
}

Any asset that an empty pages array is an unused asset.

Step 4: Delete

A delete is just a HTTP DELETE request on the node path. For example, if you want to delete the /content/dam/geometrixx/banners/techsummit.jpg DAM asset, you just need to sent a HTTP DELETE request on:

http://localhost:4502/content/dam/geometrixx/banners/techsummit.jpg

That's it and your content will be deleted.