Name
global.DBHelper
Description
Holds database related utility functions
Script
/**
* 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;
}
//--------------------------------------------------------------------------------------------------
Sys ID
1817caa3ef611000a7450fa3f8225603