DBHelper.js

/**
 * A wrapper class to hold meta data about a column in a table
 */
var ColumnInfo = Class.create();

ColumnInfo.prototype= {
   
   setValues: function(reference, internalName, name, table, internalType, typeName, referenceName, sysID, nLength) {
      this.reference = reference;
      this.internalName = internalName;
      this.name = name;
      this.table = table;
      this.internalType = internalType;
      this.typeName = typeName;
      this.referenceName = referenceName;
      this.sysID = sysID;
      this.nLength = nLength;
   },
   
   clone: function(isBaseColumn) {
      var clone = new ColumnInfo();
      clone.setValues(this.reference, this.internalName, this.name, this.table, this.internalType, this.typeName, this.referenceName, this.sysID, this.nLength);
      return clone;
   }
};

/**
 * A helper class to add new columns into an existing table
 */
var DBHelper = Class.create();

DBHelper.prototype = {
   
   //-----------------------------------------------------------------------------------------------
   
   /**
    * Initializes an instance of the helper
    * @return A self-reference to enable method chaining
    */
   _initialize : function() {
      
      // (1) Allocate storage for helper variables
      this.baseTableMap = {};
      this.columnStore = {};
      this.tableNameMap = {};
      
      // (2) Return this to allow for method chaining
      return this;
   },
   
   //-----------------------------------------------------------------------------------------------
   
   /**
    * Fills the passed in map with information about the columns of the passed in table
    *
    * @param table The name of the table from which to get information about its columns
    * @param columns A map<ColumnInfo> to store information about the table columns
    * @return A self-reference to enable method chaining
    */
   _getColumnDetails : function (table, columns) {
      
      // (1) Get the base table of the passed in table
      var baseTable = this._getBaseTable(table);
      
      // (2) If a base table exists, recursively get base table of that table
      if (baseTable != "")
         this._getColumnDetails(baseTable, columns);
      
      // (3) If we have not encountered this table, update our information about its columns
      if (this.columnStore[table] == null) {
         this.columnStore[table] = {};
         var td = GlideTableDescriptor.get(table);
         var iterator = td.getSchemaList().iterator();
         while(iterator.hasNext()) {
            var current = iterator.next();
            var internalName = '' + current.getName();
            var internalType = '' + current.getInternalType();
            if (this.columnStore[table][internalName] == null) {
               var column = new ColumnInfo();
               var referenceName = '';
               if (internalType == "reference")
                  referenceName = this._getTableName('' + current.getReference());
               column.setValues('' + current.getReference(),
               internalName,
               current.getLabel(),
               table,
               internalType,
               this._getTypeName(internalType),
               referenceName,
               current.getUniqueID(),
               current.getSqlLength());
               this.columnStore[table][internalName] = column;
            }
         }
      }
      
      // (4) Update passed in map with information that we have gleaned about table
      for (var internalName in this.columnStore[table]) {
         if (columns[internalName] == null){
            columns[internalName] = this.columnStore[table][internalName].clone();
         }
      }
      
      // (5) Return this to allow for method chaining
      return this;
   },
   
   //-----------------------------------------------------------------------------------------------
   
   /**
    * Finds the base table of the passed in table.
    *
    * @param The name of the table for which we want to locate a base table
    * @return The name of the base table of the passed in table (Or a blank string if not base table exists)
    */
   _getBaseTable : function(table) {
      
      // (1) Lazy storage of the table's base table name
      if (this.baseTableMap[table] == null) {
         this.baseTableMap[table] = "";
         var baseTable = GlideDBObjectManager.get().getBase(table);
         if (baseTable != null && baseTable != table)
            this.baseTableMap[table] = baseTable;
      }
      
      // (2) Return the table's base table name
      return this.baseTableMap[table];
   },
   
   //-----------------------------------------------------------------------------------------------
   
   /**
    * Find the name of the table which is passed in
    *
    * @param The internal name of the table
    * @return The display name of the table which is passed in
    */
   _getTableName : function(table) {
      
      // (1) Lazy storage of table's display name
      if (this.tableNameMap[table] == null){
         var td = GlideTableDescriptor.get(table);
         this.tableNameMap[table] = td.getLabel();
      }
      
      // (2) Return the table's display name
      return this.tableNameMap[table];
   }
};

//--------------------------------------------------------------------------------------------------

/**
 * Returns a map<ColumnInfo> objects corresponding to the columns of the table whose name is passed in
 *
 * @param strTableName The name of the table for which information about its columns are required
 * @return A map of ColumnInfo objects holds information about the columns of the passed in table name
 */
DBHelper.getColumnInfo = function(strTableName) {
   
   // (1) Fill-up a map with information about the columns of the passed in table and return to caller
   var mapColumnInfo = {};
   new DBHelper()._initialize()._getColumnDetails(strTableName, mapColumnInfo, false);
   return mapColumnInfo;
}

//--------------------------------------------------------------------------------------------------

/**
 * Checks whether a column exists in a table or not. Existence is defined as
 *
 * @param strTableName The name of the table
 * @param strColumnName The name of the column
 * @param strColumnType The type of the column
 * @return whether the column exists in the table or not
 */
DBHelper.columnExists = function(strTableName, strColumnName, strColumnType) {
   
   // (1) Get column information about this table
   var mapColumnInfo = DBHelper.getColumnInfo(strTableName);
   
   // (2) If column matched in table then return true
   for (var strName in mapColumnInfo) {
      var columnInfo = mapColumnInfo[strName];
      var bNamesMatch = (columnInfo.internalName == strColumnName);
      var bTypesMatch = (columnInfo.internalType == strColumnType);
      if (bNamesMatch && bTypesMatch)
         return true;
   }
   
   // (3) Column was not matched so return false
   return false;
}

//--------------------------------------------------------------------------------------------------

/**
 * Creates a column in a table using the supplied arguments
 *
 * @param strTableName The name of the table in which to create the column
 * @param columnInfo Holds information about the column to create
 * @param bColumnExists Whether a column with the same exists in the table already
 * @param bPreview Prints what would happen without actually inserting any records
 * @return Nothing
 */
DBHelper.createColumn = function(strTableName, columnInfo, bColumnExists, bPreview) {
   
   // (1) Extract the values you need to create the column
   var tableName = strTableName;
   var elementLabel = columnInfo.name;
   var elementName = columnInfo.internalName + (bColumnExists? "_alt" : "");
   var sType = columnInfo.internalType;
   var sLength = columnInfo.nLength;
   var refTableName = columnInfo.reference;
   var createDictionaryItem = true;
   var usePrefix = false;
   
   // (2) If the column exists (this may have happened between calls to this method) return
   if (DBHelper.columnExists(strTableName, elementName, sType))
      return;
   
   // (3) If only a preview was requested then print info and return
   gs.log("[DBHelper] INSERT COLUMN " + elementName + " INTO TABLE " + strTableName);
   if(bPreview)
      return;
   
   // (4) Create a new column on the table using the supplied arguments
   GlideDBUtil.createElement(tableName, elementLabel, elementName, sType, sLength,
   refTableName, createDictionaryItem, usePrefix);
}

//--------------------------------------------------------------------------------------------------

/**
 * Ensures all columns in old table exist in new table. We use the following algorithm to do this:
 *
 * Case1: if (column name, type and id is identical in both old and new table)
 * then do nothing
 * Case2: if (column name is identical in both old and new table but type and id is not)
 * then a column is created in the new table using the old column name suffixed with "_alt"
 * Case3: if (column is in old table but not in new table)
 * then a column is created in the new table using the old column name
 *
 * @param strOldTableName The name of the old table in which to look for columns
 * @param strNewTableName The name of the new table in which to insert columns (if need be)
 * @param bPreview Prints what would happen without actually inserting any records
 * @return Nothing
 */
DBHelper.ensureColumnsExist = function(strOldTableName, strNewTableName, bPreview) {
   
   // (1) Get info about the columns on strOldTableName
   var mapOldInfo = DBHelper.getColumnInfo(strOldTableName);
   
   // (2) Get info about the columns on strNewTableName
   var mapNewInfo = DBHelper.getColumnInfo(strNewTableName);
   
   // (3) Look at all columns on old table
   for (var strOldName in mapOldInfo) {
      
      // (3.1) Indicates that column has been handled
      var bHasBeenHandled = false;
      
      // (3.2) Look at all columns on new table
      for(var strNewName in mapNewInfo) {
         
         // (3.2.1) Get info about the two columns you intend to compare with each other
         var oldInfo = mapOldInfo[strOldName];
         var newInfo = mapNewInfo[strNewName];
         var bNamesMatch = (oldInfo.internalName == newInfo.internalName) ;
         var bTypesMatch = (oldInfo.internalType == newInfo.internalType);
         
         // (3.2.2) Case1: Columns have same name and type
         if (bNamesMatch && bTypesMatch) {
            bHasBeenHandled = true;
         }
         
         // (3.2.3) Case2: Columns have same name but different type
         else if (bNamesMatch) {
            DBHelper.createColumn(strNewTableName, oldInfo, true, bPreview);
            bHasBeenHandled = true;
         }
      }
      
      // (3.3) Case3: Column does not exist in the new table
      if (!bHasBeenHandled) {
         DBHelper.createColumn(strNewTableName, oldInfo, false, bPreview);
      }
   }
}

//--------------------------------------------------------------------------------------------------

/**
 * Returns mappings of column names in old table to columns in new table
 * @param strOldTableName The name of the old table
 * @param strNewTableName The name of the new table
 * @return Mappings from column in old name to column in new table
 */
DBHelper.getAltColumnMappings = function(strOldTableName, strNewTableName) {
   
   // (1) Get a list of all the columns in the old table
   var mapOldInfo = DBHelper.getColumnInfo(strOldTableName);
   
   // (2) Get a list of all the columns in the new table
   var mapNewInfo = DBHelper.getColumnInfo(strNewTableName);
   
   // (3) Build up mappings from old column to new column
   var mapAltName = {};
   for (var strNewName in mapNewInfo) {
      
      var strNewFieldName = mapNewInfo[strNewName].internalName;
      if (strNewFieldName.endsWith("_alt")) {
         for (var strOldName in mapOldInfo) {
            
            var strOldFieldName = mapOldInfo[strOldName].internalName;
            if (strOldFieldName + "_alt" == strNewFieldName) {
               mapAltName[strOldFieldName] = strNewFieldName;
            }
         }
      }
   }
   
   // (4) Return mappings back to caller
   return mapAltName;
}

//--------------------------------------------------------------------------------------------------