/// <summary>
/// Calculate metatable statistics for each broker type
/// </summary>
/// <param name="factoryName"></param>
/// <returns></returns>
public static int UpdateStats(
string factoryName)
{
int total = 0;
if (Lex.Eq(factoryName, "Generic") || Lex.IsNullOrEmpty(factoryName))
{
if (Instance == null)
{
Instance = new MetaTableFactory();
}
total += Instance.UpdateMetaTableStatistics();
}
for (int i1 = 0; i1 < MetaFactories.Count; i1++)
{
MetaTableFactoryRef mtfr = MetaFactories[i1];
if (!String.IsNullOrEmpty(factoryName) && Lex.Ne(mtfr.Name, factoryName))
{
continue; // skip if name supplied and this isn't it
}
int cnt = mtfr.MetaTableFactory.UpdateMetaTableStatistics();
total += cnt;
}
return(total);
}
/// <summary>
/// Calculate & persist library stats
/// </summary>
/// <returns></returns>
public static string UpdateLibraryStatistics()
{
Dictionary <string, MetaTableStats> stats = new Dictionary <string, MetaTableStats>();
int libId;
long cnt;
DateTime dt;
string libName, txt;
try
{
DbCommandMx dao = new DbCommandMx();
string sql = // get count and date (must use crt_timestamp) for each library
@"
select
l.lib_id,
count(*),
l.crt_timestamp
from
corp_owner.corp_library l,
corp_owner.corp_library_substance ls
where ls.lib_id (+) = l.lib_id
group by l.lib_id, l.crt_timestamp
order by l.lib_id" ;
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
libId = dao.GetInt(0);
libName = "LIBRARY_" + libId;
if (!stats.ContainsKey(libName))
{
stats[libName] = new MetaTableStats();
}
cnt = dao.GetLong(1);
stats[libName].RowCount = cnt;
dt = dao.GetDateTime(2);
stats[libName].UpdateDateTime = dt;
}
dao.CloseReader();
dao.Dispose();
string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats";
MetaTableFactory.WriteMetaTableStats(stats, fileName);
}
catch (Exception ex)
{
throw ex;
}
return("Updated statistics for " + stats.Count + " libraries");
}
Mfn.MetaTableFactory MetaTableFactoryInstance; // ref to real factory
/// <summary>
/// Constructor
/// </summary>
public MetaTableFactory()
{
if (ServiceFacade.UseRemoteServices)
{
//nothing to do
}
else
{
MetaTableFactoryInstance = new Mfn.MetaTableFactory();
}
}
public MetaTable GetMetaTable(
string mtName)
{
MetaTable mt = null;
MetaColumn mc, mc2;
int methodId;
string txt, tok;
mtName = mtName.Trim().ToUpper();
string name2 = mtName;
string prefix = "ORACLE";
if (mtName.StartsWith(prefix + "_") || mtName.StartsWith(prefix + "."))
{
name2 = mtName.Substring(prefix.Length + 1);
}
string[] sa = name2.Split('.');
if (sa.Length < 2)
{
return(null);
}
name2 = sa[0] + "." + sa[1];
mt = MetaTableFactory.GetMetaTableFromDatabaseDictionary(name2);
if (mt == null || mt.MetaColumns.Count == 0)
{
return(null);
}
mt.Name = mtName; // assign fully qualified name
if (sa.Length >= 3) // key col name included
{
mc = mt.GetMetaColumnByName(sa[2]);
if (mc != null)
{
mc.DataType = MetaColumnType.CompoundId;
}
}
if (sa.Length >= 4) // assign parent if parent table name included
{
mt.Parent = MetaTableCollection.Get(sa[3]);
}
return(mt);
}
/// <summary>
/// Update Common Assay Attributes table (mbs_owner.cmn_assy_atrbts)
///
/// Command: Update AssayAttributesTable
///
/// This command builds an entry (or two) in the cmn_assy_atrbts table for each assay
/// referenced in the Mobius contents tree that reports an SP or CRC value as determined
/// by the associated metafactory and available in each metatable.
/// If the gene target associated with an assay can be identified then information on that
/// gene is included as well.
///
/// Additional gene information may come from the metadata for a source such as the results
/// warehouse.
///
/// Note that this function must be run under an account that has access to all restricted data so that it
/// can properly see what's available and build the table.
/// </summary>
/// <param name="lex"></param>
/// <returns></returns>
public static string UpdateAssayAttributesTable(string args)
{
MetaTable mt;
MetaColumn mc;
AssayAttributes aa, aa2;
Dictionary <int, int> geneIdCounts = new Dictionary <int, int>(); // genes and counts keyed by entrez gene id
Dictionary <int, double> targetMapXDict; // dendogram X coord keyed by gene id
Dictionary <int, double> targetMapYDict; // dendogram Y coord keyed by gene id
Dictionary <string, int> targetTypeCounts = new Dictionary <string, int>(); // target types and counts
Dictionary <int, string> assayTypeDict;
Dictionary <int, string> assayModeDict;
//UnpivotedAssayResult rr, rr2;
List <string> toks;
string mtName, tableNamePrefix, key, fileName, msg;
bool crcExists, spExists, isSummary;
int tableId, step, ri, resultTypeId, assayRowCount;
Log = new LogFile(ServicesDirs.LogDir + @"\UpdateCommonAssayAttributes.log");
Log.ResetFile();
// Get list of all of the assays in the tree
LogMessage("Accumulating assays...");
HashSet <string> mtNameHash = new HashSet <string>();
foreach (MetaTreeNode mtn0 in MetaTree.Nodes.Values)
{
if (mtn0.Type != MetaTreeNodeType.MetaTable)
{
continue;
}
mtName = mtn0.Target;
if (AssayMetaData.IsAssayMetaTableName(mtName))
{
mtNameHash.Add(mtName);
}
}
bool debug = false; // set to true to debug with limited list of assays from below
if (debug)
{
mtNameHash.Clear();
//assayHash.Add("ASSAY_1");
//assayHash.Add("ASSAY_2");
//assayHash.Add("ASSAY_3");
}
// Get other informatin needed from AssayMetadata
LogMessage("Reading AssayMetadata ASSAY metadata...");
Dictionary <int, AssayDbMetadata> assayMetadataAssayDict = // get assays and associated target/gene information
AssayMetadataDao.GetAssayTargetGeneData();
LogMessage("Getting AssayMetadata result types...");
Dictionary <int, AssayDbResultType> resultTypeDict = AssayMetadataDao.GetResultTypeDict();
LogMessage("Getting assay types and modes...");
AssayMetadataDao.GetAssayTypesAndModes(out assayTypeDict, out assayModeDict);
LogMessage("Getting gene dendogram coordinates...");
try
{
AssayMetadataDao.GetImageCoords(out targetMapXDict, out targetMapYDict);
}
catch (Exception ex) // may fail if problem with data source
{
LogMessage(DebugLog.FormatExceptionMessage(ex, true));
targetMapXDict = new Dictionary <int, double>();
targetMapYDict = new Dictionary <int, double>();
}
// Process each assay
int metatablesFound = 0, metatablesNotFound = 0;
int assaysProcessed = 0;
int assaysWithGenes = 0;
int assaysWithGeneCoords = 0;
int assaysWithTargets = 0;
int assaysWithSpOnly = 0;
int assaysWithCrcOnly = 0;
int assaysWithNoCrcSP = 0;
int assaysWithOtherTypes = 0;
int assaysWithCrcAndSp = 0;
int assaysWithNoKeyTypes = 0;
int assaysWithProcessingErrors = 0;
Dictionary <string, int> CrcAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> SpAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> OtherAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
List <AssayAttributes> resultTypeRows = new List <AssayAttributes>();
List <AssayAttributes> geneRows = new List <AssayAttributes>();
List <AssayAttributes> dbRows = new List <AssayAttributes>();
string copyUpdateMsg = "";
foreach (string mtName0 in mtNameHash)
{
AssayMetaData assayMetaData = null; // metadata for assay
bool isAssay = false;
int assayIdNbr = NullValue.NullNumber;
int assayId = NullValue.NullNumber;
//if (assaysProcessed >= 100) break; // debug
mtName = mtName0;
MetaTable.ParseMetaTableName(mtName, out tableNamePrefix, out tableId, out isSummary);
string resultType = "";
string rtId = "";
string assayName, assayDb;
mt = MetaTableCollection.Get(mtName); // get metatable
if (mt == null)
{
metatablesNotFound++;
LogMessage("MetaTable not found: " + mtName);
continue;
}
metatablesFound++;
if (mt.Code == "")
{
continue; // must be single pivoted assay
}
assayDb = "ASSAY_DB"; // customize
assayIdNbr = -1;
if (UalUtil.IClient != null && UalUtil.IClient.Attended)
{
UAL.Progress.Show((assaysProcessed + 1).ToString() + " / " + mtNameHash.Count + " - " + mt.Name + "\r\n" + mt.Label);
}
aa = new AssayAttributes();
aa.AssayDatabase = assayDb;
aa.AssayIdNbr = assayIdNbr; // data-source-specific assay Id
aa.AssayIdTxt = mt.Name; // store ASSAY_1234 type table name
aa.AssayId2 = assayId; // any associated assay id
if (isAssay)
{
aa.AssayName = assayMetaData.Name; // name from AssayMetadata
}
else
{
aa.AssayName = MetaTable.RemoveSuffixesFromName(mt.Label); // name from metatable
}
if (mt.SummarizedExists)
{
aa.SummarizedAvailable = true;
}
else
{
aa.SummarizedAvailable = false;
}
if (isAssay)
{
if (assayTypeDict.ContainsKey(tableId))
{
aa.AssayType = assayTypeDict[tableId];
}
if (assayModeDict.ContainsKey(tableId))
{
aa.AssayMode = assayModeDict[tableId];
}
aa.AssaySource = AssayMetaData.GetAssaySource(tableId);
aa.AssociationSource = "TODO"; // customize
}
aa.AssayStatus = "Active"; // say all active for now
MetaTableStats mts = MetaTableFactory.GetStats(mtName);
if (mts != null)
{
aa.ResultCount = (int)mts.RowCount;
aa.AssayUpdateDate = mts.UpdateDateTime;
}
else
{
aa.ResultCount = 0; // assume no results if no stats
}
if (mt.DescriptionIsAvailable()) // use description from Mobius
{
aa.AssayDesc = "Y";
}
if (String.IsNullOrEmpty(aa.GeneFamily))
{
aa.GeneFamily = "Unknown"; // set these to "Unknown" rather than null
}
if (String.IsNullOrEmpty(aa.AssayType))
{
aa.AssayType = "UNKNOWN"; // upper case UNKNOWN
}
if (String.IsNullOrEmpty(aa.AssayMode))
{
aa.AssayMode = "UNKNOWN"; // upper case UNKNOWN
}
// Step1: Add a row for primary & any secondary results
resultTypeRows.Clear();
MetaColumn firstResultCol = null, firstKeyResultCol = null, firstOtherKeyResultCol = null;
string resultTypeConcType;
HashSet <string> keyResultTypeCodes = new HashSet <string>();
int spCnt = 0, crcCnt = 0, otherCnt = 0;
for (int mci = 0; mci < mt.MetaColumns.Count; mci++) // pick first col with result code (could also check summarization method)
{
mc = mt.MetaColumns[mci];
if (Lex.IsUndefined(mc.ResultCode))
{
continue; // must have code defined
}
if (keyResultTypeCodes.Contains(mc.ResultCode))
{
continue; // and not included so far
}
if (mc.InitialSelection != ColumnSelectionEnum.Selected)
{
continue; // selected only
}
if (firstResultCol == null)
{
firstResultCol = mc;
}
if (!IsKeyResultType(mc, out resultTypeConcType))
{
continue;
}
if (firstKeyResultCol == null)
{
firstKeyResultCol = mc;
}
keyResultTypeCodes.Add(mc.ResultCode);
aa2 = aa.Clone();
if (resultTypeRows.Count == 0)
{
aa2.TopLevelResult = "Y";
}
else
{
aa2.TopLevelResult = "N";
}
aa2.ResultTypeId2 = GetAssayResultTypeId(mc); // AssayMetadata result type id
aa2.ResultTypeIdNbr = GetInternalResultTypeId(mc); // Internal database result type id
aa2.ResultTypeIdTxt = mc.Name; // Mobius column name
if (isAssay && resultTypeDict.ContainsKey(aa2.ResultTypeId2))
{
aa2.ResultName = resultTypeDict[aa2.ResultTypeId2].Name; // use name from AssayMetadata result type dict
}
else
{
aa2.ResultName = mc.Label; // use label from Mobius
}
aa2.ResultTypeUnits = mc.Units; // result units
if (Lex.Eq(resultTypeConcType, "SP"))
{
aa2.ResultTypeConcType = "SP";
spCnt++;
}
else if (Lex.Eq(resultTypeConcType, "CRC"))
{
aa2.ResultTypeConcType = "CRC";
crcCnt++;
}
else
{
aa2.ResultTypeConcType = "";
otherCnt++;
if (firstOtherKeyResultCol == null)
{
firstOtherKeyResultCol = mc;
}
}
aa2.ResultTypeConcUnits = ""; // todo
resultTypeRows.Add(aa2);
}
if (resultTypeRows.Count >= 1)
{
if (crcCnt > 0)
{
CrcAssayCnt[assayDb]++; // count primary type by db
}
else if (spCnt > 0)
{
SpAssayCnt[assayDb]++;
}
else
{
OtherAssayCnt[assayDb]++;
}
if (crcCnt > 0 && spCnt == 0)
{
assaysWithCrcOnly++; // count overall primary/secondary types
}
else if (crcCnt == 0 && spCnt > 0)
{
assaysWithSpOnly++;
}
else if (crcCnt > 0 && spCnt > 0)
{
assaysWithCrcAndSp++;
}
if (crcCnt == 0 && spCnt == 0) // no SP or CRC result types
{
assaysWithNoCrcSP++;
mc = firstKeyResultCol;
LogMessage("Assay with No SP/CRC key results: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
else if (otherCnt > 0) // no SP or CRC result types
{
assaysWithOtherTypes++;
mc = firstOtherKeyResultCol;
LogMessage("Non SP/CRC key result: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
}
else // no key result type
{
aa2 = aa.Clone();
resultTypeRows.Add(aa2); // include row for step1
OtherAssayCnt[assayDb]++;
assaysWithNoKeyTypes++;
LogMessage("No key result type for metatable: " + mt.Name + ", " + mt.Label);
}
// Build a step2 row for each target/gene
geneRows.Clear();
List <AssayTarget> targets = new List <AssayTarget>();
int geneCount = 0;
if (isAssay)
{
targets = assayMetaData.Targets;
}
if (targets.Count > 0)
{
assaysWithTargets++;
}
foreach (AssayTarget target in targets)
{
aa = new AssayAttributes();
aa.GeneFamily = target.TargetTypeShortName; // count target type occurance
if (Lex.IsUndefined(aa.GeneFamily))
{
aa.GeneFamily = "Unknown";
}
if (!targetTypeCounts.ContainsKey(aa.GeneFamily))
{
targetTypeCounts[aa.GeneFamily] = 0;
}
targetTypeCounts[aa.GeneFamily]++;
if (target.Genes == null || target.Genes.Count == 0) // if no genes add a single target row
{
geneRows.Add(aa);
continue;
}
foreach (AssayGene rg in target.Genes)
{
if (!Lex.IsDefined(rg.GeneSymbol))
{
continue;
}
aa2 = aa.Clone();
geneRows.Add(aa2);
aa2.GeneSymbol = rg.GeneSymbol;
int.TryParse(rg.GeneId, out aa2.GeneId);
if (aa2.GeneId > 0 && targetMapXDict.ContainsKey(aa2.GeneId))
{
aa2.TargetMapX = targetMapXDict[aa2.GeneId];
aa2.TargetMapY = targetMapYDict[aa2.GeneId];
if (geneCount == 0)
{
assaysWithGeneCoords++;
}
}
if (!geneIdCounts.ContainsKey(aa2.GeneId)) // count gene occurance
{
geneIdCounts[aa2.GeneId] = 0;
}
geneIdCounts[aa2.GeneId]++;
if (geneCount == 0)
{
assaysWithGenes++;
}
geneCount++;
}
}
if (geneRows.Count == 0) // if no step 2 rows (i.e. no targets), create a single step2 row
{
aa = new AssayAttributes();
geneRows.Add(aa);
}
// Combine key result types with target/genes
for (int i1 = 0; i1 < resultTypeRows.Count; i1++)
{
AssayAttributes s1aa = resultTypeRows[i1];
for (int i2 = 0; i2 < geneRows.Count; i2++)
{
AssayAttributes s2aa = geneRows[i2];
aa = s1aa.Clone();
aa.GeneId = s2aa.GeneId;
aa.GeneSymbol = s2aa.GeneSymbol;
aa.GeneFamily = s2aa.GeneFamily;
aa.TargetMapX = s2aa.TargetMapX;
aa.TargetMapY = s2aa.TargetMapY;
aa.GeneCount = geneCount;
if (i2 > 0)
{
aa.GeneCount = -geneCount; // negative for other than 1st gene
}
dbRows.Add(aa);
}
}
assaysProcessed++;
}
// Update table
bool updateTable = true; // set to false for debug
if (dbRows.Count <= 0)
{
LogMessage("No rows in new dataset, table not updated");
}
else if (updateTable)
{
LogMessage("Deleting existing data...");
DbCommandMx dao = new DbCommandMx();
string sql = "delete from mbs_owner.cmn_assy_atrbts";
sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
dao.Prepare(sql);
dao.BeginTransaction();
int delCnt = dao.ExecuteNonReader();
LogMessage("Inserting new data...");
int t0 = TimeOfDay.Milliseconds();
for (ri = 0; ri < dbRows.Count; ri++)
{
aa = dbRows[ri];
aa.Id = ri + 1;
//aa.Id += 10000; // debug
if (aa.GeneSymbol != null)
{
aa.GeneSymbol = aa.GeneSymbol.ToUpper(); // be sure key match cols are upper case
}
if (aa.GeneFamily != null)
{
aa.GeneFamily = aa.GeneFamily.ToUpper();
}
if (aa.GeneFamilyTargetSymbol != null)
{
aa.GeneFamilyTargetSymbol = aa.GeneFamilyTargetSymbol.ToUpper();
}
if (aa.ResultTypeConcType != null)
{
aa.ResultTypeConcType = aa.ResultTypeConcType.ToUpper();
}
if (aa.AssayType != null)
{
aa.AssayType = aa.AssayType.ToUpper();
}
if (aa.AssayMode != null)
{
aa.AssayMode = aa.AssayMode.ToUpper();
}
AssayAttributesDao.InsertCommonAssayAttributes(aa, dao);
if (TimeOfDay.Milliseconds() - t0 > 1000)
{
//Progress.Show("Inserting new data " + (ri + 1) + "/" + rows.Count + "...");
t0 = TimeOfDay.Milliseconds();
}
}
dao.Commit();
dao.Dispose();
copyUpdateMsg = UpdateCmnAssyAtrbtsCopies();
}
string response =
"----------------------------------\r\n" +
"Assays processed: " + assaysProcessed + "\r\n" +
"Assays with processing errors: " + assaysWithProcessingErrors + "\r\n" +
"Rows inserted: " + dbRows.Count + "\r\n" +
copyUpdateMsg +
"----------------------------------\r\n" +
"Assays with CRC only: " + assaysWithCrcOnly + "\r\n" +
"Assays with SP only: " + assaysWithSpOnly + "\r\n" +
"Assays with CRC and SP: " + assaysWithCrcAndSp + "\r\n" +
"Assays with no CRC or SP: " + assaysWithNoCrcSP + "\r\n" +
"Assays with non CRC/SP key types: " + assaysWithOtherTypes + "\r\n" +
"Assays with no key types: " + assaysWithNoKeyTypes + "\r\n" +
"----------------------------------\r\n" +
"Assays with targets defined: " + assaysWithTargets + "\r\n" +
"Assays with genes defined: " + assaysWithGenes + "\r\n" +
"Assays with gene map coordinates: " + assaysWithGeneCoords + "\r\n" +
"----------------------------------\r\n" +
//"CRC Assays: " + CrcAssayCnt["ASSAY"] + "\r\n" +
//"SP Assays: " + SpAssayCnt["ASSAY"] + "\r\n" +
//"??? Assays: " + OtherAssayCnt["ASSAY"] + "\r\n" +
"----------------------------------";
LogMessage("\r\n" + response);
UAL.Progress.Hide();
return(response);
}
/// <summary>
/// Get metatable if name belongs to broker
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public MetaTable GetMetaTable(
string name)
{
UserObject uo = null;
int objectId, i1;
string tok;
Stopwatch sw = Stopwatch.StartNew();
if (!UserObject.IsAnnotationTableName(name) &&
!UserObject.IsUserDatabaseStructureTableName(name))
{
return(null);
}
try { objectId = UserObject.ParseObjectIdFromInternalName(name); }
catch (Exception ex) { return(null); }
try { uo = UserObjectDao.Read(objectId); }
catch (MobiusConnectionOpenException ex)
{ DbConnectionMx.ThrowSpecificConnectionOpenException(ex, "mbs_owner"); }
if (uo == null)
{
return(null);
}
if (!Permissions.UserHasReadAccess(Security.UserName, uo))
{
return(null);
}
MetaTable mt = MetaTable.Deserialize(uo.Content);
if (mt == null)
{
return(null); // something wrong with the annotation table content
}
MetaColumn keyMc = mt.KeyMetaColumn;
MetaColumn rootMc = mt.Root.KeyMetaColumn;
if (rootMc.IsNumeric) // adjust key col mapping
{
keyMc.ColumnMap = "EXT_CMPND_ID_NBR";
}
else
{
keyMc.ColumnMap = "EXT_CMPND_ID_TXT";
}
while (mt.MetaColumns.Count > MetaTable.MaxColumns) // fixup for table with too many columns
{
mt.MetaColumns.RemoveAt(mt.MetaColumns.Count - 1);
}
foreach (MetaColumn mc in mt.MetaColumns)
{ // set DetailsAvailable for existing annotation tables */
if (mc.ResultCode != "")
{
mc.DetailsAvailable = true;
}
}
mt.Label = uo.Name; // get latest name (may have changed if renamed after saved)
if (MetaTableFactory.ShowDataSource) // add source to label if requested
{
mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label);
}
long ms = sw.ElapsedMilliseconds;
return(mt);
}
private MetaTable GetMetaTableLevel2(
string name)
{
UserObject uo;
Query incQry = null; // included query
QueryTable qt, qt1, qt2;
QueryColumn qc, qc1, qc2;
CalcFieldMetaTable mt;
MetaTable mt1, mt2;
MetaColumn mc, mc1, mc2, cfMc, cfMcModel;
ExecuteQueryParms eqp;
string cfLabel = "", mcName, mcLabel;
string sql = null, tables = null, exprs = null, criteria = null;
int objectId, startingSuffix, ci, i1;
string prefix = "calcfield_"; // calculated field metatable names begin with "calcfield_"
if (name.ToLower().IndexOf(prefix) != 0)
{
return(null);
}
if (Lex.Eq(name, "CALCFIELD_826476"))
{
name = name; // debug
}
string tok = name.Substring(prefix.Length); // get the object id
try { objectId = Int32.Parse(tok); }
catch (Exception ex) { return(null); }
uo = UserObjectDao.Read(objectId);
if (uo == null)
{
return(null);
}
if (!Permissions.UserHasReadAccess(Security.UserName, uo))
{
return(null);
}
CalcField cf = CalcField.Deserialize(uo.Content);
if (cf == null)
{
return(null);
}
if (cf.FinalResultType == MetaColumnType.Unknown)
{
return(null); // need a final result type
}
mt = new CalcFieldMetaTable();
mt.Name = name;
mt.CalcField = cf; // include associated CalcField object
mt.Label = uo.Name;
if (MetaTableFactory.ShowDataSource) // add source to label if requested
{
mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label);
}
mt.Description = cf.Description;
mt.MetaBrokerType = MetaBrokerType.CalcField;
bool keyAdded = false;
cfLabel = cf.ColumnLabel; // get any user defined label for result
// Add metacolumns for basic calculated field
if (cf.CalcType == CalcTypeEnum.Basic && cf.Column1.MetaColumn != null)
{
cfMcModel = cf.Column1.MetaColumn;
tok = cf.Operation;
if (Lex.IsNullOrEmpty(cfLabel))
{
if (tok.IndexOf("/") == 0)
{
cfLabel = "Ratio";
}
else if (tok.IndexOf("*") == 0)
{
cfLabel = "Product";
}
else if (tok.IndexOf("+") == 0)
{
cfLabel = "Sum";
}
else if (tok.IndexOf("-") == 0)
{
cfLabel = "Difference";
}
else
{
if (cf.Column1.FunctionEnum == CalcFuncEnum.None) // just use existing col name
{
cfLabel = cf.Column1.MetaColumn.Label;
}
else // use function & col name
{
tok = cf.Column1.Function;
i1 = tok.IndexOf("(");
if (i1 >= 0)
{
tok = tok.Substring(0, i1);
}
if (!tok.EndsWith(" "))
{
tok += " ";
}
tok += cf.MetaColumn1.Label;
cfLabel = tok;
}
}
if (cf.IsClassificationDefined)
{
cfLabel += " Class"; // add class suffix if classifier
}
}
}
else if (cf.CalcType == CalcTypeEnum.Advanced && cf.AdvancedExpr != "")
{
List <MetaColumn> mcList = cf.GetInputMetaColumnList();
if (mcList.Count == 0)
{
return(null);
}
cfMcModel = mcList[0];
if (cfLabel == "")
{
cfLabel = "Value";
}
}
else
{
return(null);
}
mt.Parent = cfMcModel.MetaTable.Parent; // set parent
if (mt.Root == mt)
{
mt.Parent = cfMcModel.MetaTable; // if F1 table is a root set it as parent
}
// Add key value
mc = cfMcModel.MetaTable.KeyMetaColumn; // make copy of first table key field for our key
if (mc == null)
{
return(null);
}
mc = mc.Clone();
mc.ColumnMap = mc.Name; // map to self
mc.MetaTable = mt;
mt.AddMetaColumn(mc);
keyAdded = true;
mc.IsSearchable = cf.IsSearchable;
// Add calculated value column
mc = new MetaColumn();
mt.AddMetaColumn(mc);
mc.MetaTable = mt;
mc.Name = "CALC_FIELD";
mc.Label = cfLabel;
mc.DataType = cf.FinalResultType; // final result type
mc.InitialSelection = ColumnSelectionEnum.Selected;
mc.Format = cfMcModel.Format;
mc.Width = cfMcModel.Width;
mc.Decimals = cfMcModel.Decimals;
if (cf.FinalResultType == MetaColumnType.Integer)
{
mc.Format = ColumnFormatEnum.Decimal;
mc.Decimals = 0;
}
if (cf.FinalResultType == MetaColumnType.Image)
{
mc.Width = (int)(mc.Width * 2.0); // make CF images wider
}
mc.IsSearchable = cfMcModel.IsSearchable;
if (mc.IsSearchable) // refine searchability
{
mc.IsSearchable = cf.IsSearchable;
}
cfMc = mc;
// Add metacolumns for the underlying columns that go into the calculation
foreach (CalcFieldColumn cfc in cf.CfCols)
{
if (cfc.MetaColumn == null)
{
continue;
}
if (cfc.MetaColumn.IsKey)
{
continue; // don't add additional key
}
mc = cfc.MetaColumn.Clone();
mcName = mc.MetaTable.Name + "_" + mc.Name;
mc.Name = mt.GetValidMetaColumnName(mcName);
mc.DetailsAvailable = false; // no drill-down for now (can cause issues)
mc.IsSearchable = false; // no searching for now (can cause issues)
mc.ColumnMap = mc.Name; // map to self
if (Lex.Eq(mc.Label, cfLabel)) // if same as result start suffix checking at 2
{
startingSuffix = 1;
}
else
{
startingSuffix = 2;
}
mc.Label = mt.GetUniqueMetaColumnLabel(mc.Label, startingSuffix);
mc.InitialSelection = ColumnSelectionEnum.Hidden; // hide for now
mc.TableMap = cfc.MetaColumn.MetaTable.Name + "." + cfc.MetaColumn.Name; // save source table and column
//mc.TableMap = cfc.MetaColumn.MetaTable.Name; // save source table and column
//mc.ColumnMap = cfc.MetaColumn.Name;
mt.AddMetaColumn(mc);
}
//mt.MetaColumns.Remove(cfMc); // move cf result column to end after the inputs
//mt.MetaColumns.Add(cfMc);
return(mt);
}
static Dictionary <string, ColumnSelectionEnum> DisplayLevelOverride; // override values for display levels from metafactories
public MetaTableFactory()
{
Instance = this;
}
/// <summary>
/// SelectWithCriteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsLibrary> SelectWithCriteria(string criteria)
{
MetaTreeNode tempMtn = new MetaTreeNode(MetaTreeNodeType.Library);
StringBuilder sb = new StringBuilder(64);
string sql = @"
select
p.proj_id,
p.mbs_project_code,
proj_name,
p.mbs_dht_folder_code,
dht_folder_name,
platform_name,
lib_id,
lib_name,
lib_use
from
<mbs_owner>.afs_project p,
<mbs_owner>.afs_lib l
where
p.afs_current = 1
and p.mbs_project_code is not null
and l.afs_current = 1
and l.proj_id = p.proj_id
and <criteria>
order by upper(dht_folder_name), upper(proj_name), upper(lib_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsLibrary> libs = new List <AfsLibrary>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats.txt";
Dictionary <string, MetaTableStats> libStats = new Dictionary <string, MetaTableStats>();
int libCount = MetaTableFactory.LoadMetaTableStats(fileName, libStats);
while (dao.Read())
{
AfsLibrary l = new AfsLibrary();
l.ProjId = dao.GetInt(0);
l.MbsProjectName = dao.GetString(1).ToUpper();
l.ProjectLabel = dao.GetString(2);
l.MbsDhtFolderName = dao.GetString(3).ToUpper();
l.DhtFolderLabel = dao.GetString(4).ToUpper();
l.Platform = dao.GetString(5).ToUpper();
l.LibId = dao.GetInt(6);
l.LibName = dao.GetString(7);
l.LibUse = dao.GetString(8).ToUpper();
string nodeName = "LIBRARY_" + l.LibId;
sb.Length = 0;
sb.Append(l.LibName);
sb.Append(" (");
if (!Lex.IsNullOrEmpty(l.LibUse))
{
sb.Append(l.LibUse);
sb.Append(", ");
}
sb.Append("Id: ");
sb.Append(l.LibId);
if (libStats.ContainsKey(nodeName))
{
tempMtn.Size = (int)libStats[nodeName].RowCount;
tempMtn.UpdateDateTime = libStats[nodeName].UpdateDateTime; // really create date
sb.Append(", ");
sb.Append(MetaTreeNode.FormatStatistics(tempMtn));
}
sb.Append(")");
l.LibLabel = sb.ToString();
libs.Add(l);
}
dao.CloseReader();
return(libs);
}
/// <summary>
/// Create MetaTable for a PubChem assay
/// </summary>
public MetaTable GetMetaTable(
string name)
{
MetaTable mt = null, mt2 = null, mt3 = null;
MetaColumn mc, mcc;
string txt, tok;
string aid;
string prefix = "pubchem_aid_";
if (!name.ToLower().StartsWith(prefix))
{
return(null);
}
int t0 = TimeOfDay.Milliseconds();
aid = name.Substring(prefix.Length);
XmlNode node;
XmlNodeList nodes;
TextReader rdr = GetAssayXml(aid);
if (rdr == null)
{
return(null);
}
XmlDocument doc = new XmlDocument();
doc.Load(rdr);
mt = new MetaTable();
mt.Name = "PUBCHEM_AID_" + aid.ToString();
mt.TableMap =
"(select * " +
"from mbs_owner.mbs_pbchm_rslt)";
mt.MetaBrokerType = MetaBrokerType.Pivot;
mt.Parent = MetaTableCollection.Get("pubchem_structure");
mt.TableFilterColumns = Csv.SplitCsvString("mthd_vrsn_id");
mt.TableFilterValues = Csv.SplitCsvString(aid);
mt.PivotMergeColumns = Csv.SplitCsvString("ext_cmpnd_id_nbr, rslt_grp_id");
mt.PivotColumns = Csv.SplitCsvString("rslt_typ_id");
XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace("ab", "http://www.ncbi.nlm.nih.gov"); // must look within this namespace
MetaTreeNode mtn = MetaTreeFactory.GetNode(mt.Name); // try to get label from contents tree
mtn = null;
if (mtn != null)
{
mt.Label = mtn.Label;
}
else
{
string dataSource = SelectNodeValue(doc, nsmgr, "//ab:PC-DBTracking_name");
string assayName = SelectNodeValue(doc, nsmgr, "//ab:PC-AssayDescription_name");
string outcomeMethod = SelectNodeValue(doc, nsmgr, "//ab:PC-AssayDescription_activity-outcome-method");
mt.Label = assayName;
if (outcomeMethod == "1")
{
outcomeMethod = "Primary";
}
else if (outcomeMethod == "2")
{
outcomeMethod = "Dose-Response";
}
else if (outcomeMethod == "3")
{
outcomeMethod = "Probe Summary";
}
else
{
outcomeMethod = "";
}
if (outcomeMethod.Length > 0)
{
mt.Label += " (" + outcomeMethod + ")";
}
}
if (mt.Label == "")
{
mt.Label = mt.Name;
}
mt.Description = // reference PubChem description page
"http://pubchem.ncbi.nlm.nih.gov/assay/assay.cgi?aid=" + aid;
nodes = doc.SelectNodes("//ab:PC-ResultType", nsmgr); // result types
// Build common metacolumns
mc = mt.AddMetaColumn("PUBCHEM_CID", "CID", MetaColumnType.CompoundId, ColumnSelectionEnum.Selected, 14);
mc.ColumnMap = "ext_cmpnd_id_nbr";
mc = mt.AddMetaColumn("PUBCHEM_SID", "SID", MetaColumnType.Integer, ColumnSelectionEnum.Selected, 7);
mc.ColumnMap = "rslt_val_nbr";
mc.PivotValues = Csv.SplitCsvString("1001"); // assign codes that won't interfere with assay-specific codes
mc = mt.AddMetaColumn("PUBCHEM_ACTIVITY_SCORE", "Activity Score", MetaColumnType.Integer, ColumnSelectionEnum.Selected, 7);
mc.ColumnMap = "rslt_val_nbr";
mc.PivotValues = Csv.SplitCsvString("1002");
mc = mt.AddMetaColumn("PUBCHEM_ACTIVITY_OUTCOME", "Activity Outcome", MetaColumnType.DictionaryId, ColumnSelectionEnum.Selected, 7);
mc.ColumnMap = "rslt_val_nbr";
mc.Dictionary = "PubChemActivityOutcome"; // code stored in db must be translated via dictionary
mc.PivotValues = Csv.SplitCsvString("1003");
mc = mt.AddMetaColumn("PUBCHEM_ACTIVITY_URL", "Activity URL", MetaColumnType.Hyperlink, ColumnSelectionEnum.Selected, 7);
mc.ColumnMap = "rslt_val_txt";
mc.PivotValues = Csv.SplitCsvString("1004");
// Build assay-specific metacolumns
for (int ci = 0; ci < nodes.Count; ci++)
{
node = nodes[ci];
string typeId = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_tid"); // get values relative to node (need "." in xpath string)
string typeName = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_type");
string colName = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_name");
string desc = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_description_E");
if (desc.IndexOf(", unit: ") > 0) // shorten label a bit
{
desc = desc.Replace(", unit: ", " (");
if (desc.EndsWith("."))
{
desc = desc.Substring(0, desc.Length - 1);
}
desc += ")";
}
string unit = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_unit");
if (unit == "")
{
unit = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_sunit");
}
if (Lex.Eq(unit, "none") || Lex.Eq(unit, "unspecified"))
{
unit = "";
}
if (unit.EndsWith("ml"))
{
unit = unit.Substring(0, unit.Length - 2) + "/mL";
}
string transform = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_transform");
mc = new MetaColumn();
mc.PivotValues = Csv.SplitCsvString(typeId);
mc.Name = "R_" + typeId;
mc.Label = colName;
if (unit != "")
{
mc.Label += " (" + unit + ")";
}
if (mc.Label == "")
{
mc.Label = mc.Name;
}
//mc.Label = desc; // user longer description for label
//if (mc.Label == "") mc.Label = mc.Label;
mc.Format = ColumnFormatEnum.Default;
mc.Decimals = -1;
mc.InitialSelection = ColumnSelectionEnum.Selected;
if (Lex.Eq(typeName, "float"))
{
mc.DataType = MetaColumnType.Number;
mc.Width = 8;
mc.ColumnMap = "rslt_val_nbr";
}
else if (Lex.Eq(typeName, "int"))
{
mc.DataType = MetaColumnType.Number;
mc.Width = 8;
mc.Format = ColumnFormatEnum.Decimal;
mc.Decimals = 0;
mc.ColumnMap = "rslt_val_nbr";
}
else if (Lex.Eq(typeName, "bool"))
{
mc.DataType = MetaColumnType.Number;
mc.Width = 8;
mc.ColumnMap = "rslt_val_nbr";
}
else if (Lex.Eq(typeName, "string"))
{
mc.DataType = MetaColumnType.String;
mc.Width = 8;
mc.ColumnMap = "rslt_val_txt";
}
else
{
throw new Exception("Invalid type" + typeName);
}
mc.MetaTable = mt;
mt.AddMetaColumn(mc);
}
mc = mt.AddMetaColumn("PUBCHEM_ASSAYDATA_COMMENT", "Comment", MetaColumnType.String, ColumnSelectionEnum.Unselected, 7);
mc.ColumnMap = "rslt_val_txt";
mc.PivotValues = Csv.SplitCsvString("1005");
mc = mt.AddMetaColumn("PUBCHEM_EXT_DATASOURCE_REG", "Ext. Source. Reg. Id", MetaColumnType.String, ColumnSelectionEnum.Unselected, 7);
mc.ColumnMap = "rslt_val_txt";
mc.PivotValues = Csv.SplitCsvString("1006");
mc = mt.AddMetaColumn("PUBCHEM_ASSAYDATA_REVOKE", "Revoked", MetaColumnType.String, ColumnSelectionEnum.Hidden, 7);
mc.ColumnMap = "rslt_val_txt";
mc.PivotValues = Csv.SplitCsvString("1007");
MetaTableFactory.SetAnyNewMetaTableLabel(mt); // if table has been renamed the set new label
foreach (MetaColumn mc2 in mt.MetaColumns)
{
MetaTableFactory.SetAnyNewMetaColumnLabel(mc2); // if col has been renamed then set new label
}
GetMetaTableCount++;
t0 = TimeOfDay.Milliseconds() - t0;
GetMetaTableTime += t0;
GetMetaTableAvgTime = GetMetaTableTime / GetMetaTableCount;
return(mt);
}
