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.
No comments :
Post a Comment