/// <summary>
/// Identity stage II: update EdgeObject DB (staging) with edge object from Delivery DB
/// using LOCK on EdgeObject table:
/// * sync last changes according transform timestamp
/// * update modified EdgeObjects --> IdentityStatus = Modified
/// * insert new EdgeObjects --> IdentityStatus = New
/// </summary>
public void UpdateEdgeObjects()
{
_config = String.IsNullOrEmpty(ConfigXml) ? new IdentityConfig() : IdentityConfig.Deserialize(ConfigXml);
// load object dependencies
Dependencies = EdgeObjectConfigLoader.GetEdgeObjectDependencies(AccountId, _objectsSqlConnection).Values.ToList();
Log("UpdateEdgeObjects:: EdgeObjects dependencies loaded");
int maxDependecyDepth = Dependencies.Max(x => x.Depth);
for (int i = 0; i <= maxDependecyDepth; i++)
{
var currentDepth = i;
Log(String.Format("UpdateEdgeObjects:: dependency depth={0}", currentDepth));
foreach (var field in Dependencies.Where(x => x.Depth == currentDepth))
{
Log(String.Format("UpdateEdgeObjects:: starting update field '{0}' of type '{1}'", field.Field.Name, field.Field.FieldEdgeType.Name));
UpdateObjectDependencies(field.Field.FieldEdgeType);
if (_config.UpdateExistingObjects && DeliveryContainsChanges(field.Field.FieldEdgeType, IdentityStatus.Unchanged, true))
{
Log(String.Format("UpdateEdgeObjects:: delivery contains changes of {0}, starting sync", field.Field.Name));
SyncLastChangesWithLock(field.Field.FieldEdgeType);
if (DeliveryContainsChanges(field.Field.FieldEdgeType, IdentityStatus.Modified))
{
UpdateExistingEdgeObjectsByDelivery(field.Field.FieldEdgeType);
Log(String.Format("UpdateEdgeObjects:: update modified object of type '{0}'", field.Field.FieldEdgeType));
}
if (_config.CreateNewObjects && DeliveryContainsChanges(field.Field.FieldEdgeType, IdentityStatus.New))
{
InsertNewEdgeObjects(field.Field.FieldEdgeType);
Log(String.Format("UpdateEdgeObjects:: insert new objects of type '{0}'", field.Field.FieldEdgeType));
}
}
else
{
Log(String.Format("UpdateEdgeObjects:: delivery doen't contain changes of {0}, nothing to sync", field.Field.Name));
}
CreateTempDeliveryGkTkTable(field.Field.FieldEdgeType);
}
}
}
/// <summary>
/// Build Metics SELECT from Delivery Metrics table with JOINs all delivery objects by TKs
/// in order to fill Staging Metrics table found by Best Match from Delivery Metrics table
/// </summary>
public static string GetMetricsView(int accountId, string tableName, SqlConnection connection)
{
var selectStr = String.Empty;
var fromStr = String.Format("\t[EdgeDeliveries].{0} AS Metrics\n", tableName);
var tablePrefix = tableName.ToLower().Replace("_metrics]", "").Replace("[dbo].[", "");
var edgeTypes = EdgeObjectConfigLoader.LoadEdgeTypes(accountId, connection);
var sql = String.Format("SELECT EdgeFieldName, ParentFieldName, EdgeTypeID, MeasureName FROM [EdgeDeliveries].[dbo].[MD_MetricsMetadata] WHERE TABLENAME='{0}'", tableName);
using (var cmd = new SqlCommand(sql, connection))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// add dimension field (JOIN according TK to find GK and WHERE by type ID)
if (!String.IsNullOrEmpty(reader["EdgeFieldName"].ToString()))
{
var edgeType = edgeTypes.Values.FirstOrDefault(x => x.TypeID == int.Parse(reader["EdgeTypeID"].ToString()));
if (edgeType == null)
{
continue;
}
var fieldName = reader["EdgeFieldName"].ToString().Replace("_gk", "");
var parentFieldName = reader["ParentFieldName"].ToString().Replace("_gk", "");
selectStr = String.Format("{0}\t{1}.GK AS {1}_gk,\n", selectStr, fieldName);
fromStr = String.Format("{0}\tINNER JOIN {1} AS {3} ON Metrics.{2}_tk={3}.TK\n",
fromStr, GetTableName(tablePrefix, edgeType.TableName), parentFieldName, fieldName);
}
// add measure fields
else if (!String.IsNullOrEmpty(reader["MeasureName"].ToString()))
{
selectStr = String.Format("{0}\tMetrics.{1},\n", selectStr, reader["MeasureName"]);
}
}
}
}
// combine select
return(selectStr.Length == 0 ?
"No data relevant data in MD_MetricsMetadata" :
String.Format("SELECT\n{0}\nFROM\n{1}", selectStr.Remove(selectStr.Length - 2, 2), fromStr));
}
/// <summary>
/// Identity stage I: update delivery objects with existing in EdgeObject DB object GKs
/// tag Delivery objects by IdentityStatus (New, Modified or Unchanged)
/// </summary>
public void IdentifyDeliveryObjects()
{
// load object dependencies
Dependencies = EdgeObjectConfigLoader.GetEdgeObjectDependencies(AccountId, _objectsSqlConnection).Values.ToList();
Log("IdentifyDeliveryObjects:: EdgeObjects dependencies loaded");
var maxDependecyDepth = Dependencies.Max(x => x.Depth);
var updatedTypes = new List <EdgeType>();
for (var i = 0; i <= maxDependecyDepth; i++)
{
var currentDepth = i;
Log(String.Format("IdentifyDeliveryObjects:: dependency depth={0}", currentDepth));
foreach (var field in Dependencies.Where(x => x.Depth == currentDepth))
{
Log(String.Format("IdentifyDeliveryObjects:: starting identify field '{0}' of type '{1}'", field.Field.Name, field.Field.FieldEdgeType.Name));
// nothing to do if field of the same type was already updated
if (updatedTypes.Contains(field.Field.FieldEdgeType))
{
Log(String.Format("IdentifyDeliveryObjects:: nothing to Identify for field '{0}', type '{1}' was already identified", field.Field.Name, field.Field.FieldEdgeType.Name));
continue;
}
Log(String.Format("IdentifyDeliveryObjects:: update '{0}' type dependencies", field.Field.FieldEdgeType.Name));
UpdateObjectDependencies(field.Field.FieldEdgeType);
Log(String.Format("IdentifyDeliveryObjects:: Create Temp Objects table for type '{0}'", field.Field.FieldEdgeType.Name));
CreateTempObjectsTable(field.Field.FieldEdgeType);
Log(String.Format("IdentifyDeliveryObjects:: Set delivery objects identity (GK) for type '{0}'", field.Field.FieldEdgeType.Name));
SetIdentity(field.Field.FieldEdgeType);
Log(String.Format("IdentifyDeliveryObjects:: Create Temp Delivery GK-TK table for type '{0}'", field.Field.FieldEdgeType.Name));
CreateTempDeliveryGkTkTable(field.Field.FieldEdgeType);
Log(String.Format("IdentifyDeliveryObjects:: Finished identify type '{0}'", field.Field.FieldEdgeType.Name));
updatedTypes.Add(field.Field.FieldEdgeType);
}
}
}
/// <summary>
/// Before searching for object GKs update all GK of its parent fields
/// (objects it depends on)
/// For example: before searching for AdGroup GKs, update all AdGroup Campaings
/// </summary>
private void UpdateObjectDependencies(EdgeType edgeType)
{
// nothitng to do if there are no GK to update
if (edgeType.Fields.All(x => x.Field.FieldEdgeType == null))
{
return;
}
var mainTableName = GetDeliveryTableName(edgeType.TableName);
var paramList = new List <SqlParameter> {
new SqlParameter("@typeId", edgeType.TypeID)
};
var cmdStr = String.Empty;
foreach (var parentField in edgeType.Fields.Where(x => x.Field.FieldEdgeType != null))
{
foreach (var childType in EdgeObjectConfigLoader.FindEdgeTypeInheritors(parentField.Field.FieldEdgeType, EdgeTypes))
{
var tempParentTableName = String.Format("##TempDelivery_{0}", childType.Name);
cmdStr = String.Format("{0}UPDATE {1} SET {3}={2}.GK FROM {2} WHERE {1}.TYPEID=@typeId AND {1}.{4}={2}.TK;\n\n",
cmdStr,
mainTableName,
tempParentTableName,
parentField.ColumnNameGK,
parentField.ColumnNameTK);
}
}
// perform update
using (var cmd = new SqlCommand {
Connection = _objectsSqlConnection
})
{
cmd.CommandText = cmdStr;
cmd.Parameters.AddRange(paramList.ToArray());
cmd.ExecuteNonQuery();
}
Log(String.Format("UpdateObjectDependencies:: dependencies updated for edge type '{0}'", edgeType.Name));
}
/// <summary>
/// Per each type combine flat SELECT fields by real names in Metrics
/// </summary>
/// <param name="accountId"></param>
/// <param name="stagingTableName"></param>
/// <param name="connection"></param>
/// <param name="pipe">Pipe to send SQL rows reply</param>
public static void GetObjectsView(int accountId, string stagingTableName, SqlConnection connection, SqlPipe pipe)
{
// load configuration
var edgeTypes = EdgeObjectConfigLoader.LoadEdgeTypes(accountId, connection);
var edgeFields = EdgeObjectConfigLoader.LoadEdgeFields(accountId, edgeTypes, connection);
EdgeObjectConfigLoader.SetEdgeTypeEdgeFieldRelation(accountId, edgeTypes, edgeFields, connection);
var fieldsMap = LoadStageFields(stagingTableName, edgeFields, edgeTypes.Values.ToList(), connection);
// prepare result record
var record = new SqlDataRecord(new[]
{
new SqlMetaData("TypeID", SqlDbType.Int),
new SqlMetaData("Name", SqlDbType.NVarChar, 50),
new SqlMetaData("FieldList", SqlDbType.NVarChar, 1000),
new SqlMetaData("Select", SqlDbType.NVarChar, 1000)
});
pipe.SendResultsStart(record);
foreach (var type in edgeTypes.Values.Where(x => x.IsAbstract == false))
{
// prepare type fields SELECT
var fieldsStr = String.Empty;
foreach (var field in type.Fields)
{
fieldsStr = String.Format("{0}{1} AS {2}, ", fieldsStr, field.ColumnNameGK.StartsWith("obj") ? String.Format("COALESCE({0},-1)", field.ColumnNameGK) :
field.ColumnNameGK, field.FieldNameGK);
if (field.Field.FieldEdgeType == null)
{
continue;
}
// add to select all options of child edge types
foreach (var childType in EdgeObjectConfigLoader.FindEdgeTypeInheritors(field.Field.FieldEdgeType, edgeTypes))
{
if (childType == field.Field.FieldEdgeType)
{
continue;
}
fieldsStr = String.Format("{0}{1} AS {2}_{3}_gk, ", fieldsStr, field.ColumnNameGK.StartsWith("obj") ? String.Format("COALESCE({0},-1)", field.ColumnNameGK) :
field.ColumnNameGK, field.Field.Name, childType.Name);
}
}
if (fieldsStr.Length <= 0)
{
continue;
}
fieldsStr = fieldsStr.Remove(fieldsStr.Length - 2, 2);
var select = String.Format("SELECT GK,AccountID,RootAccountId,ChannelID,CreatedOn,LastUpdatedOn,{0} FROM {1} WHERE TYPEID={2}", fieldsStr, type.TableName, type.TypeID);
// set report and and it
record.SetInt32(0, type.TypeID);
record.SetString(1, type.Name);
record.SetString(2, fieldsMap.ContainsKey(type.TypeID) ? String.Join(",", fieldsMap[type.TypeID]) : "");
record.SetString(3, select);
pipe.SendResultsRow(record);
}
pipe.SendResultsEnd();
}
/// <summary>
/// Perfrom metrics staging: insert all metrics table data into staging table
/// </summary>
/// <param name="accountId"></param>
/// <param name="deliveryTableName"></param>
/// <param name="stagingTableName"></param>
/// <param name="connection"></param>
public static string StageMetrics(int accountId, string deliveryTableName, string stagingTableName, SqlConnection connection)
{
var selectStr = String.Empty;
var insertStr = String.Empty;
var fromStr = String.Format("\t[EdgeDeliveries].{0} AS Metrics\n", deliveryTableName);
var tablePrefix = deliveryTableName.ToLower().Replace("_metrics]", "").Replace("[dbo].[", "");
if (!stagingTableName.ToLower().Contains("edgestaging"))
{
stagingTableName = String.Format("[EdgeStaging].{0}", stagingTableName);
}
var edgeTypes = EdgeObjectConfigLoader.LoadEdgeTypes(accountId, connection);
// System Fields (e.g. Account, Channel, time, etc.)
using (var cmd = new SqlCommand("SELECT SystemField FROM [EdgeStaging].[dbo].[SystemFields]", connection))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
selectStr = String.Format("{0}\tMetrics.{1},\n", selectStr, reader["SystemField"]);
insertStr = String.Format("{0}\t{1},\n", insertStr, reader["SystemField"]);
}
}
}
// metrics fields (according to metrics metadata table)
var sql = String.Format("SELECT EdgeFieldName, ParentFieldName, EdgeTypeID, MeasureName FROM [EdgeDeliveries].[dbo].[MD_MetricsMetadata] WHERE TABLENAME='{0}'", deliveryTableName);
using (var cmd = new SqlCommand(sql, connection))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// add dimension field (JOIN according TK to find GK and WHERE by type ID)
if (!String.IsNullOrEmpty(reader["EdgeFieldName"].ToString()))
{
var edgeType = edgeTypes.Values.FirstOrDefault(x => x.TypeID == int.Parse(reader["EdgeTypeID"].ToString()));
if (edgeType == null)
{
continue;
}
var fieldName = reader["EdgeFieldName"].ToString().Replace("_gk", "");
var parentFieldName = reader["ParentFieldName"].ToString().Replace("_gk", "");
selectStr = String.Format("{0}\tCOALESCE({1}.GK,-1) AS {1}_gk,\n", selectStr, fieldName);
insertStr = String.Format("{0}\t{1}_gk,\n", insertStr, fieldName);
fromStr = String.Format("{0}\tLEFT OUTER JOIN {1} AS {3} ON Metrics.{2}_tk={3}.TK\n AND {3}.TYPEID={4}",
fromStr, GetTableName(tablePrefix, edgeType.TableName), parentFieldName, fieldName, edgeType.TypeID);
}
// add measure fields
else if (!String.IsNullOrEmpty(reader["MeasureName"].ToString()))
{
selectStr = String.Format("{0}\tMetrics.{1},\n", selectStr, reader["MeasureName"]);
insertStr = String.Format("{0}\t{1},\n", insertStr, reader["MeasureName"]);
}
}
}
}
// perform staging (insert metrics table data into staging table)
sql = String.Format("INSERT INTO {0} ({1})\nSELECT {2}\nFROM {3}",
stagingTableName,
insertStr.TrimEnd(new[] { ',', '\n' }),
selectStr.TrimEnd(new[] { ',', '\n' }),
fromStr);
using (var cmd = new SqlCommand(sql, connection))
{
cmd.ExecuteNonQuery();
}
return(sql);
}
