Find first available object with number suffix

Sometimes you need to find a first available "something" with number.  For example you have a naming conventions for volumes like "prefix" + number ("myvolume002" for example).

So you probably want to find the next available.  Usually you probably use the incremental naming procedure (http://www.wfaguy.com/2016/12/incremental-naming.html)

That works great.  But sometimes objects (like volumes) get removed and then you get gaps.  Some customers don't like that and want to refill those gaps.



For example :
you have volumes :

• myvol001
• myvol002
• myvol004
• myvol007

As you can see you have gaps between 2 & 4 and between 4 & 7.

Next time you create a new volume, your workflow will typically pick number 8, but this time you want number 3 to be created, which is the lowest free available.

Here is how I do it.

First I create a query that does this 

• generate all possibilities (myvol001 -> myvol999)
  This requires my number generator view (http://www.wfaguy.com/2016/11/generating-number-sequence-in-mysql.html)
• substract all existing
• order by name
• limit 1

 SELECT
      all_possibilities.result
 FROM
      (
     SELECT
   CONCAT('myvol',LPAD(n,3,'0')) AS result
     FROM
   playground.generator_64k
     WHERE
   n>=1 AND n<(10*3)
      ) all_possibilities
      LEFT JOIN
      (

     SELECT
   volume.name AS existing
     FROM
   cm_storage.volume,
   cm_storage.vserver,
   cm_storage.cluster    
     WHERE
   volume.vserver_id=vserver.id
   AND vserver.cluster_id=cluster.id
   AND vserver.name = 'myvserver'
   AND ((cluster.name = 'mycluster') OR (cluster.primary_address = 'mycluster'))

      ) existing
      ON all_possibilities.result = existing.existing
 WHERE
      existing.existing IS NULL
 ORDER BY result
 LIMIT 1;

In this query there are a few parameters

• prefix : example, myvol
• padLength : example 3 (myvol005)
• startNumber : example 1 (myvol001)
• vserverName : myvserver
• clusterName : mycluster

The query itself is pretty simple and can be adapted for any other type (lun, igroup, schedule, ...)

Finally we just wrap it in a function

def getIncrementalVolumeName(prefix,padLength,startNumber,vserverName,clusterName)
{
 
 import java.sql.*; 
  
 //Connect to local WFA DB using the built-in credentials
 
 String query="SELECT"
+ "     all_possibilities.result"
+ " FROM"
+ "      ("
+ "            SELECT"
+ "                 CONCAT(?,LPAD(n,?,'0')) AS result"
+ "            FROM"
+ "                 playground.generator_64k"
+ "            WHERE"
+ "                 n>=? AND n<(10*?)"
+ "      ) all_possibilities"
+ "      LEFT JOIN"
+ "      ("
+ "            SELECT"
+ "                 volume.name as existing"
+ "            FROM"
+ "                 cm_storage.volume,"
+ "                 cm_storage.vserver,"
+ "                 cm_storage.cluster"        
+ "            WHERE"
+ "                 volume.vserver_id=vserver.id"
+ "                 AND vserver.cluster_id=cluster.id"
+ "                 AND vserver.name = ?"
+ "                 AND ((cluster.name = ?) OR (cluster.primary_address = ?))"
+ "      ) existing"
+ "      ON all_possibilities.result = existing.existing"
+ " WHERE"
+ "      existing.existing IS NULL"
+ " ORDER BY result"
+ " LIMIT 1";

 Connection con=DriverManager.getConnection(
 "jdbc:mysql://localhost:3306/","wfa","Wfa123");
 
 PreparedStatement stmt=con.prepareStatement(query);
 stmt.setString(1, prefix);
 stmt.setInt(2, padLength);
 stmt.setInt(3, startNumber);
 stmt.setInt(4, padLength);
 stmt.setString(5, vserverName);
 stmt.setString(6, clusterName); 
 stmt.setString(7, clusterName); 
 ResultSet rs=stmt.executeQuery();

 if(rs.next()){
  return (rs.getString('result'));
 }
 
}

Note that you could also add a suffix.  Just modify the CONCAT to include it.