private static void InitializeSchemaCacheItem(RepositoryCacheItem cacheItem)
{
using (var context = new DatastoreEntities())
{
var r = context.Repository.FirstOrDefault(x => x.UniqueKey == cacheItem.Key && !x.IsDeleted && x.IsInitialized);
if (r == null)
{
cacheItem.IsInitialized = true;
cacheItem.Exists = false;
return;
}
;
var retval = new RepositorySchema
{
InternalID = r.RepositoryId,
ChangeStamp = r.Changestamp
};
retval.LoadXml(r.DefinitionData);
cacheItem.IsInitialized = true;
cacheItem.Exists = true;
cacheItem.Xml = retval.ToXml(true);
cacheItem.VersionHash = retval.VersionHash; // Cache the version to reduce number of calculations
cacheItem.InternalId = retval.InternalID;
cacheItem.HasParent = (r.ParentId != null);
}
}
/// <summary>
/// Find the cache subkey. This only exists if there is a FieldFilter with the "GroupingField=Value"
/// </summary>
private string GetSubKey(RepositorySchema schema, DataQuery query)
{
FieldDefinition groupingField = null;
//Get the data grouping field for this schema and cache
lock (_schemaDatagrouping)
{
if (schema.ParentID == null && !_schemaDatagrouping.TryGetValue(schema.InternalID, out groupingField))
{
groupingField = schema.FieldList.FirstOrDefault(x => x.IsDataGrouping);
if (groupingField == null)
{
groupingField = new FieldDefinition();
}
_schemaDatagrouping.Add(schema.InternalID, groupingField);
}
}
//If there is a grouping field then use it to narrow search
if (groupingField != null)
{
var ff = query.FieldFilters.FirstOrDefault(x => x.Name.Match(groupingField.Name));
if (ff != null && ff.Comparer == ComparisonConstants.Equals && ff.Value != null)
{
return(ff.Value.ToString().ToLower());
}
}
return(null);
}
public QueryThreaded(DimensionCache dimensionCache, RepositorySchema schema, DataQuery query)
{
this.Query = query;
_dimensionCache = dimensionCache;
_schema = schema;
this.Key = Guid.NewGuid();
}
private static void SyncInternal(List <DataItem> list, RepositorySchema schema)
{
var timer = Stopwatch.StartNew();
var count = 0;
var processed = 0;
long lastRIdx = 0;
try
{
if (list == null)
{
return;
}
if (schema == null)
{
return;
}
var l = list.Where(x => x.__Hash == 0 && x.__RecordIndex > 0).ToList();
if (!l.Any())
{
return;
}
count = l.Count;
var dataTable = SqlHelper.GetTableName(schema.ID);
foreach (var item in l)
{
var sb = new StringBuilder();
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter
{
DbType = DbType.Int64,
IsNullable = false,
ParameterName = $"@{SqlHelper.HashField}",
Value = item.Hash(),
});
parameters.Add(new SqlParameter
{
DbType = DbType.Int64,
IsNullable = false,
ParameterName = $"@{SqlHelper.RecordIdxField}",
Value = item.__RecordIndex,
});
lastRIdx = item.__RecordIndex;
sb.AppendLine($"UPDATE [{dataTable}] SET [{SqlHelper.HashField}] = @{SqlHelper.HashField} WHERE [{SqlHelper.RecordIdxField}] = @{SqlHelper.RecordIdxField} AND [{SqlHelper.HashField}] = 0");
SqlHelper.ExecuteSql(ConfigHelper.ConnectionString, sb.ToString(), parameters, false, false, 5);
Interlocked.Increment(ref _counter);
processed++;
}
}
catch (Exception ex)
{
LoggerCQ.LogWarning($"DataManager.SyncInternal: ID={schema.ID}, Count={count}, Processed={processed}, RIdx={lastRIdx}, Elapsed={timer.ElapsedMilliseconds}");
}
}
private static void TestSchema()
{
var q1 = RepositorySchema.CreateFromXml(File.ReadAllText(@"c:\temp\a.xml"));
var q2 = RepositorySchema.CreateFromXml(File.ReadAllText(@"c:\temp\b.xml"));
var v1 = q1.GetHashCode();
var v2 = q2.GetHashCode();
var c1 = q1.FieldList.Where(x => x.AllowIndex).Count();
var c2 = q2.FieldList.Where(x => x.AllowIndex).Count();
}
public static void Sync(List <DataItem> list, RepositorySchema schema)
{
try
{
Task.Factory.StartNew(() =>
{
SyncInternal(list, schema);
});
}
catch (Exception ex)
{
LoggerCQ.LogError(ex);
}
}
/// <summary />
internal DatastoreResultsAsync(DatastoreService dsService, DataQuery query)
{
try
{
_dsService = dsService;
_query = query;
_hookId = _dsService.QueryAsync(query);
_schema = _dsService.Schema;
}
catch
{
this.IsComplete = true;
throw;
}
}
internal static Dictionary <string, int> GetSchemaFieldMap(RepositorySchema schema, RepositorySchema parentSchema)
{
var fieldIndexPrimaryMap = new Dictionary <string, int>();
var fieldIndex = 0;
if (parentSchema != null)
{
parentSchema.FieldList.ForEach(x => fieldIndexPrimaryMap.Add(x.Name, fieldIndex++));
}
if (schema != null)
{
schema.FieldList.Where(x => !fieldIndexPrimaryMap.ContainsKey(x.Name)).ToList().ForEach(x => fieldIndexPrimaryMap.Add(x.Name, fieldIndex++));
}
return(fieldIndexPrimaryMap);
}
/// <summary />
public ActionDiagnostics UpdateSchema(RepositorySchema schema)
{
if (schema == null)
{
throw new Exception("The schema is not set.");
}
if (schema.ID != this.RepositoryId)
{
throw new Exception("The schema does not match the RepositoryID.");
}
var result = DataModelService.UpdateSchema(schema);
if (result?.Errors?.Length > 0)
{
throw new Exception(result.Errors.First());
}
return(result);
}
/// <summary />
public virtual void CreateRepository(RepositorySchema repositorySchema)
{
ValidateService();
if (repositorySchema.ID == Guid.Empty)
{
throw new Exception("Invalid ID for repository");
}
if (RepositoryExists())
{
return;
}
using (var factory = SystemCoreInteractDomain.GetCoreFactory(ServerName, Port))
{
var core = factory.CreateChannel();
(core as IContextChannel).OperationTimeout = new TimeSpan(0, 0, 120); //Timeout=2m
core.SaveRepository(repositorySchema);
}
}
public void Set(DatastoreEntities context, RepositorySchema schema, DataQuery query, int repositoryId, Guid id, DataQueryResults results)
{
if (!ConfigHelper.AllowCaching)
{
return;
}
if (results == null)
{
return;
}
//Do not cache big items
if (results.RecordList.Count > 100)
{
return;
}
if (!string.IsNullOrEmpty(query.Keyword) && !this.FTSReadyCache.IsReady(id))
{
return;
}
//if (!string.IsNullOrEmpty(query.Keyword) && !ConfigHelper.AllowCacheWithKeyword) return;
var timer = Stopwatch.StartNew();
var cache = RepositoryCacheManager.GetCache(id, RepositoryManager.GetSchemaParentId(repositoryId));
long lockTime = 0;
var changeStamp = 0;
var queryHash = 0;
var subCacheKey = GetSubKey(schema, query);
try
{
//Some queries should be cached a long time
var longCache = !query.FieldFilters.Any() &&
!query.FieldSorts.Any() &&
string.IsNullOrEmpty(query.Keyword) &&
!query.SkipDimensions.Any();
var extraMinutes = longCache ? 480 : 0;
var coreHash = 0;
CacheResultsQuery item;
using (var q = new AcquireReaderLock(ServerUtilities.RandomizeGuid(cache.ID, RSeed), "QueryCache"))
{
lockTime += q.LockTime;
queryHash = query.GetHashCode();
if (!query.ExcludeCount && query.IncludeDimensions && !query.IncludeEmptyDimensions)
{
coreHash = query.CoreHashCode();
}
changeStamp = RepositoryManager.GetRepositoryChangeStamp(context, repositoryId);
lock (cache)
{
item = cache?.FirstOrDefault(x => x.QueryHash == queryHash && x.ChangeStamp == changeStamp);
}
//If data has not changed and results are in cache then do nothing except mark as accessed
if (item != null)
{
item.Results = results;
item.Timestamp = DateTime.Now.AddMinutes(extraMinutes);
item.SubKey = subCacheKey;
return;
}
}
lock (cache)
{
using (var q = new AcquireWriterLock(ServerUtilities.RandomizeGuid(cache.ID, RSeed), "QueryCache"))
{
lockTime += q.LockTime;
//Create a new cache item
item = new CacheResultsQuery()
{
QueryHash = queryHash,
QueryCoreHash = coreHash,
RepositoryId = repositoryId,
ChangeStamp = changeStamp,
Results = results,
QueryString = query.ToString(),
ParentId = RepositoryManager.GetSchemaParentId(repositoryId),
Timestamp = DateTime.Now.AddMinutes(extraMinutes),
SubKey = subCacheKey,
};
cache.Add(item);
}
}
}
catch (Exception ex)
{
timer.Stop();
LoggerCQ.LogError(ex, $"RepositoryId={id}, Elapsed={timer.ElapsedMilliseconds}, ID={id}, LockTime={lockTime}, Count={cache.Count}, QueryHash={queryHash}, ChangeStamp={changeStamp}");
throw;
}
finally
{
timer.Stop();
if (timer.ElapsedMilliseconds > 50)
{
LoggerCQ.LogWarning($"Slow cache set: Elapsed={timer.ElapsedMilliseconds}, LockTime={lockTime}, Count={cache.Count}, ID={id}, Query=\"{query.ToString()}\"");
}
LoggerCQ.LogTrace($"QueryCache: Set: SubCacheKey={subCacheKey}");
}
}
public DataQueryResults Get(DatastoreEntities context, RepositorySchema schema, DataQuery query, int repositoryId, Guid id, out bool isCore)
{
isCore = false;
if (!ConfigHelper.AllowCaching)
{
return(null);
}
long lockTime = 0;
int queryHash = 0;
int coreHash = 0;
int changeStamp = 0;
var task1 = Task.Factory.StartNew(() =>
{
queryHash = query.GetHashCode();
coreHash = query.CoreHashCode();
changeStamp = RepositoryManager.GetRepositoryChangeStamp(context, repositoryId);
});
var timer = new Stopwatch();
var cache = RepositoryCacheManager.GetCache(id, RepositoryManager.GetSchemaParentId(repositoryId));
try
{
using (var q = new AcquireReaderLock(ServerUtilities.RandomizeGuid(cache.ID, RSeed), "QueryCache"))
{
lockTime = q.LockTime;
timer.Start();
//Ensure that the pre-calculations are complete
task1.Wait();
CacheResultsQuery item = null;
lock (cache)
{
item = cache?.FirstOrDefault(x => x.QueryHash == queryHash && x.ChangeStamp == changeStamp);
}
if (item == null) //return null;
{
if (ConfigHelper.AllowCoreCache)
{
//TODO: OPTIMIZE: this is a linear search of thousands of items!!!!
//If did not find a match then find see if core properties match
//If so we can use the dimension and count values and just replace the records collection
lock (cache)
{
item = cache?.FirstOrDefault(x => x.QueryCoreHash == coreHash && x.ChangeStamp == changeStamp);
}
}
if (item == null)
{
return(null);
}
isCore = true;
item.HitCount++;
return(item.Results);
}
item.Timestamp = DateTime.Now;
item.HitCount++;
return(item.Results);
}
}
catch (Exception ex)
{
timer.Stop();
LoggerCQ.LogError(ex, $"RepositoryId={id}, Elapsed={timer.ElapsedMilliseconds}, LockTime={lockTime}, Count={cache.Count}, QueryHash={queryHash}, ChangeStamp={changeStamp}, ID={id}");
throw;
}
finally
{
timer.Stop();
if (timer.ElapsedMilliseconds > 50)
{
LoggerCQ.LogWarning($"Slow cache get: Elapsed={timer.ElapsedMilliseconds}, LockTime={lockTime}, Count={cache.Count}, ID={id}, QueryString=\"{query.ToString()}\"");
}
}
}
internal RepositorySchema GetSchema(Guid repositoryId, bool clear = false)
{
var schemaXml = GetSchemaValue(repositoryId, clear, c => c.Xml);
return(RepositorySchema.CreateFromXml(schemaXml));
}
internal static RepositorySchema LoadSchemaForType(Type dsType, bool inheritedFieldList = false)
{
if (dsType == null)
{
throw new Exception("The type must be set");
}
if (!dsType.GetInterfaces().Any(x => x.Name == typeof(IDatastoreItem).Name))
{
throw new Exception($"The item must implement {typeof(IDatastoreItem).Name}.");
}
var dsRepositoryAttribute = dsType.GetCustomAttributes().FirstOrDefault(x => x is DatastoreRepositoryAttribute) as DatastoreRepositoryAttribute;
if (dsRepositoryAttribute == null)
{
throw new InvalidOperationException("Cannot create repository for the specified type. Missing DataStoreRepositoryAttribute.");
}
var schema = new RepositorySchema();
schema.Name = dsRepositoryAttribute.Name;
schema.ObjectAlias = dsRepositoryAttribute.ObjectAlias;
Guid rid;
Guid.TryParse(dsRepositoryAttribute.Id, out rid);
if (rid == Guid.Empty)
{
rid = Guid.NewGuid();
}
schema.ID = rid;
if (!string.IsNullOrEmpty(dsRepositoryAttribute.ParentId))
{
schema.ParentID = new Guid(dsRepositoryAttribute.ParentId);
}
schema.FieldIndexing = dsRepositoryAttribute.FieldIndexing;
//var properties = GetTemplateFields(dsType);
var properties = new List <MemberInfo>();
properties.AddRange(dsType.GetProperties());
properties.AddRange(dsType.GetFields());
#region Loop Fields
foreach (MemberInfo prop in properties)
{
var field = prop.CustomAttributes.FirstOrDefault(x => x.AttributeType == typeof(DatastoreFieldAttribute)) as CustomAttributeData;
if (field != null)
{
var isNullable = false;
var fieldDefition = default(FieldDefinition);
var defFieldType = RepositorySchema.DataTypeConstants.String;
var defLength = 100;
var propertyTypeName = "";
if (prop is PropertyInfo)
{
propertyTypeName = ((PropertyInfo)prop).PropertyType.Name;
if (propertyTypeName.Contains("Nullable"))
{
isNullable = true;
propertyTypeName = ((PropertyInfo)prop).PropertyType.GenericTypeArguments.Select(x => x.Name).FirstOrDefault();
}
}
else if (prop is FieldInfo)
{
propertyTypeName = ((FieldInfo)prop).FieldType.Name;
if (propertyTypeName.Contains("Nullable"))
{
isNullable = true;
propertyTypeName = ((FieldInfo)prop).FieldType.GenericTypeArguments.Select(x => x.Name).FirstOrDefault();
}
}
switch (propertyTypeName)
{
case "String[]":
defFieldType = RepositorySchema.DataTypeConstants.List;
isNullable = true;
break;
case "String":
defFieldType = RepositorySchema.DataTypeConstants.String;
isNullable = true;
break;
case "Int16":
case "Int32":
case "UInt32":
case "Byte":
defFieldType = RepositorySchema.DataTypeConstants.Int;
break;
case "Int64":
defFieldType = RepositorySchema.DataTypeConstants.Int64;
break;
case "Bool":
case "Boolean":
defFieldType = RepositorySchema.DataTypeConstants.Bool;
break;
case "DateTime":
defFieldType = RepositorySchema.DataTypeConstants.DateTime;
break;
case "Double":
case "Single":
case "Decimal":
defFieldType = RepositorySchema.DataTypeConstants.Float;
break;
case "Char":
defFieldType = RepositorySchema.DataTypeConstants.String;
defLength = 1;
break;
case "GeoCode":
defFieldType = RepositorySchema.DataTypeConstants.GeoCode;
isNullable = true;
break;
default:
break;
}
var name = GetAttributeValue <string>(field, "Name", prop.Name);
var dataType = GetAttributeValue <RepositorySchema.DataTypeConstants>(field, "DataType", defFieldType);
var fieldType = GetAttributeValue <RepositorySchema.FieldTypeConstants>(field, "FieldType", RepositorySchema.FieldTypeConstants.Field);
var isPrimaryKey = GetAttributeValue <bool>(field, "IsPrimaryKey", false);
var isDataGrouping = GetAttributeValue <bool>(field, "IsDataGrouping", false);
var length = GetAttributeValue <int>(field, "Length", defLength);
var allowTextSearch = GetAttributeValue <bool>(field, "AllowTextSearch", false);
var isPivot = GetAttributeValue <bool>(field, "IsPivot", false);
var pivotGroup = GetAttributeValue <string>(field, "PivotGroup", String.Empty);
var pivotOrder = GetAttributeValue <int>(field, "PivotOrder", 0);
var description = GetAttributeValue <string>(field, "Description", String.Empty);
var searchAsc = GetAttributeValue <bool>(field, "SearchAsc", true);
var allowIndex = GetAttributeValue <bool>(field, "AllowIndex", true);
if (fieldType == RepositorySchema.FieldTypeConstants.Dimension)
{
fieldDefition = new DimensionDefinition();
}
else
{
fieldDefition = new FieldDefinition();
}
fieldDefition.Name = name;
fieldDefition.DataType = dataType;
fieldDefition.FieldType = fieldType;
fieldDefition.IsPrimaryKey = isPrimaryKey;
fieldDefition.IsDataGrouping = isDataGrouping;
fieldDefition.AllowNull = isNullable;
fieldDefition.Length = length;
fieldDefition.AllowTextSearch = allowTextSearch;
fieldDefition.Name = name;
fieldDefition.IsPivot = isPivot;
fieldDefition.PivotGroup = pivotGroup;
fieldDefition.PivotOrder = pivotOrder;
fieldDefition.Description = description;
fieldDefition.SearchAsc = searchAsc;
fieldDefition.AllowIndex = allowIndex;
//If PK and nullable then error
if (isPrimaryKey && isNullable)
{
throw new Exception("The primary key cannot be nullable.");
}
if (isPrimaryKey && !allowIndex)
{
throw new Exception("The primary key must be indexed.");
}
//Default to unlimited string length
if (defFieldType == RepositorySchema.DataTypeConstants.String && defLength < 0)
{
defLength = 0;
}
if (fieldType == RepositorySchema.FieldTypeConstants.Dimension)
{
switch (defFieldType)
{
case RepositorySchema.DataTypeConstants.Bool:
case RepositorySchema.DataTypeConstants.String:
case RepositorySchema.DataTypeConstants.List:
case RepositorySchema.DataTypeConstants.Int:
case RepositorySchema.DataTypeConstants.Int64:
case RepositorySchema.DataTypeConstants.DateTime:
break;
default:
throw new Exception($"Field '{name}': The data type '{defFieldType}' cannot be a dimension.");
}
}
if (fieldType == RepositorySchema.FieldTypeConstants.Dimension)
{
var dimension = fieldDefition as DimensionDefinition;
if (dimension == null)
{
throw new Exception("Dimension is null");
}
var didx = GetAttributeValue <int?>(field, "Didx", null);
if (didx.HasValue)
{
dimension.DIdx = didx.Value;
}
dimension.Parent = GetAttributeValue <string>(field, "Parent", String.Empty);
dimension.DimensionType = GetAttributeValue <RepositorySchema.DimensionTypeConstants>(field, "DimensionType", RepositorySchema.DimensionTypeConstants.Normal);
var numericBreak = GetAttributeValue <long?>(field, "NumericBreak", null);
if ((dimension.DataType == RepositorySchema.DataTypeConstants.Int || dimension.DataType == RepositorySchema.DataTypeConstants.Int64) && numericBreak.HasValue)
{
dimension.NumericBreak = numericBreak.Value;
}
else if (numericBreak.HasValue)
{
throw new Exception("Numeric breaks are only applicable for Integer fields.");
}
//If the datatype is list then the DimensionType must be list
if (dimension.DataType == RepositorySchema.DataTypeConstants.List)
{
dimension.DimensionType = RepositorySchema.DimensionTypeConstants.List;
}
}
if (GetAttributeValue <bool>(field, "UserPermission", false))
{
schema.UserPermissionField = fieldDefition;
}
schema.FieldList.Add(fieldDefition);
}
}
#endregion
var hasGeo = schema.FieldList.Any(x => x.DataType == RepositorySchema.DataTypeConstants.GeoCode);
var hasGroupingData = (schema.FieldList.Where(x => x.IsDataGrouping).Count() > 1);
if (schema.FieldList.Count == 0)
{
throw new Exception("The item must have fields defined.");
}
if (schema.FieldList.Count(x => x.IsPrimaryKey) != 1)
{
throw new Exception("The item must have exactly one primary key defined.");
}
if (schema.FieldList.Count != schema.FieldList.Select(x => x.Name.ToLower()).Distinct().Count())
{
throw new Exception("The item cannot have duplicate fields.");
}
if (schema.FieldList.Where(x => x.IsDataGrouping).Count() > 1)
{
throw new Exception("The item cannot have more than one data group field.");
}
if (hasGeo && hasGroupingData)
{
throw new Exception("The item cannot have a data grouping and GeoCode field.");
}
//This is a child so remove parent fields from schema
if (schema.ParentID != null && !inheritedFieldList)
{
if (!dsType.BaseType.GetInterfaces().Any(x => x.Name == typeof(IDatastoreItem).Name))
{
throw new Exception($"The item base must implement {typeof(IDatastoreItem).Name}.");
}
var parentSchema = LoadSchemaForType(dsType.BaseType);
schema = schema.Subtract(parentSchema);
}
foreach (var ditem in schema.DimensionList.Where(x => !string.IsNullOrEmpty(x.Parent)).ToList())
{
//Verify that no parent dimension is self-referential
if (ditem.Parent.Match(ditem.Name))
{
throw new Exception($"The dimension '{ditem.Name}' cannot be its own parent.");
}
//Error check that parent dimension actually exists
if (!schema.DimensionList.Any(x => x.Name == ditem.Parent))
{
throw new Exception($"The dimension '{ditem.Name}' defines a non-existent parent.");
}
}
//TODO: Verify that no parent dimensions cause a cycle (A->B->A)
return(schema);
}
private void ProcessDeleted()
{
try
{
using (var context = new DatastoreEntities(ConfigHelper.ConnectionString))
{
const int CHUNKSIZE = 20000;
var deleteList = context.DeleteQueue.Where(x => x.IsReady).ToList();
foreach (var dItem in deleteList)
{
var rKey = context.Repository.Where(x => x.RepositoryId == dItem.RepositoryId).Select(x => x.UniqueKey).FirstOrDefault();
if (rKey != Guid.Empty)
{
var schema = RepositoryManager.GetSchema(rKey);
if (schema != null)
{
#region Parent Schema
RepositorySchema parentSchema = null;
if (schema.ParentID != null)
{
if (string.IsNullOrEmpty(schema.ObjectAlias))
{
throw new Exception("An inherited repository must have an alias.");
}
parentSchema = RepositoryManager.GetSchema(schema.ParentID.Value, true);
if (parentSchema == null)
{
throw new Exception("Parent schema not found");
}
if (!context.Repository.Any(x => x.UniqueKey == schema.ParentID && x.ParentId == null))
{
throw new Exception("Cannot create an repository from a non-base parent");
}
schema = parentSchema.Merge(schema);
}
#endregion
var listDimensions = schema.FieldList
.Where(x => x.DataType == RepositorySchema.DataTypeConstants.List &&
x is DimensionDefinition)
.Cast <DimensionDefinition>()
.ToList();
foreach (var dimension in listDimensions)
{
var timer = Stopwatch.StartNew();
var listTable = SqlHelper.GetListTableName(schema.ID, dimension.DIdx);
if (parentSchema != null && parentSchema.DimensionList.Any(x => x.DIdx == dimension.DIdx))
{
listTable = SqlHelper.GetListTableName(schema.ParentID.Value, dimension.DIdx);
}
var newParam = new SqlParameter {
DbType = DbType.Int64, IsNullable = false, ParameterName = $"@ParentRowId", Value = dItem.RowId
};
var sbList = new StringBuilder();
sbList.AppendLine($"--MARKER 19");
sbList.AppendLine($"SET ROWCOUNT {CHUNKSIZE};");
sbList.AppendLine("set nocount off;");
sbList.AppendLine($"WITH S([{SqlHelper.RecordIdxField}])");
sbList.AppendLine("AS");
sbList.AppendLine("(");
sbList.AppendLine($"select [RecordIdx] from [DeleteQueueItem] {SqlHelper.NoLockText()}");
sbList.AppendLine($"where [ParentRowId] = {newParam.ParameterName}");
sbList.AppendLine(")");
sbList.AppendLine($"DELETE FROM [{listTable}]");
sbList.AppendLine($"FROM S inner join [{listTable}] on S.[{SqlHelper.RecordIdxField}] = [{listTable}].[{SqlHelper.RecordIdxField}];");
var lastCount = 0;
do
{
lastCount = SqlHelper.ExecuteSql(ConfigHelper.ConnectionString, sbList.ToString(), new[] { newParam });
} while (lastCount >= CHUNKSIZE);
timer.Stop();
} //Dimension
}
}
//Remove from queue
context.DeleteQueueItem.Where(x => x.ParentRowId == dItem.RowId).Delete();
context.DeleteQueue.Where(x => x.RowId == dItem.RowId).Delete();
context.SaveChanges();
}
}
}
catch (Exception ex)
{
throw;
}
}
private CacheResults GetCache(DatastoreEntities context, int id, RepositorySchema schema)
{
try
{
var dimensionValueTableName = SqlHelper.GetDimensionValueTableName(schema.ID);
var dimensionValueTableNameParent = string.Empty;
lock (_cache)
{
var dimensionStamp = RepositoryManager.GetDimensionChanged(context, id);
var retval = _cache.FirstOrDefault(x => x.RepositoryId == id);
//Check repository DimensionStamp and if changed the reload dimensions
if (retval != null && retval.DimensionStamp != dimensionStamp)
{
Clear(id);
retval = null;
}
if (retval == null)
{
#region Parent table stuff
if (schema.ParentID != null)
{
if (!_parentSchemaCache.ContainsKey(schema.ID))
{
var parentSchema = RepositoryManager.GetSchema(schema.ParentID.Value);
_parentSchemaCache.Add(schema.ID, schema.Subtract(parentSchema));
}
dimensionValueTableNameParent = SqlHelper.GetDimensionValueTableName(schema.ParentID.Value);
}
#endregion
retval = new CacheResults()
{
RepositoryId = id, ParentId = RepositoryManager.GetSchemaParentId(id)
};
_cache.Add(retval);
var sb = new StringBuilder();
sb.AppendLine($"select v.DIdx, v.DVIdx, v.Value from [{dimensionValueTableName}] v");
//If there is a parent schema then UNION its dimension tables
if (schema.ParentID != null)
{
sb.AppendLine($"union select v.DIdx, v.DVIdx, v.Value from [{dimensionValueTableNameParent}] v");
}
sb.AppendLine("order by DIdx, DVIdx");
var ds = SqlHelper.GetDataset(ConfigHelper.ConnectionString, sb.ToString(), null);
retval.Results = new List <DimensionItem>();
//Load all dimensions
foreach (var dimension in schema.DimensionList)
{
retval.Results.Add(new DimensionItem
{
DIdx = dimension.DIdx,
Name = dimension.Name,
});
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
var didx = (long)dr["DIdx"];
long dvidx = 0;
string v = null;
if (dr["DVIdx"] != System.DBNull.Value)
{
dvidx = (long)dr["DVIdx"];
v = (string)dr["Value"];
}
var d = retval.Results.FirstOrDefault(x => x.DIdx == didx);
if (d == null)
{
d = new DimensionItem {
DIdx = (int)didx, Name = schema.DimensionList.Where(x => x.DIdx == didx).Select(x => x.Name).FirstOrDefault()
};
retval.Results.Add(d);
}
if (dvidx != 0)
{
d.RefinementList.Add(new RefinementItem {
DVIdx = dvidx, FieldValue = v, DIdx = didx
});
}
//Rearrange all refinements alpha (for debugging and such)
//retval.Results.ForEach(ditem => ditem.RefinementList = ditem.RefinementList.OrderBy(x => x.FieldValue).ToList());
}
}
retval.DimensionStamp = dimensionStamp;
retval.Timestamp = DateTime.Now; //Accessed
return(retval);
}
}
catch (Exception ex)
{
throw;
}
}
public List <DimensionItem> Get(DatastoreEntities context, RepositorySchema schema, int id, IEnumerable <DataFieldUpdate> list = null)
{
if (schema == null)
{
throw new Exception("The schema is null");
}
try
{
var dimensionValueTableName = SqlHelper.GetDimensionValueTableName(schema.ID);
var dimensionValueTableNameParent = string.Empty;
var parameters = new List <SqlParameter>();
var didxParam = 0;
lock (_cache)
{
var retval = GetCache(context, id, schema);
#region Do this after "GetCache" call as it will flush the cache if need be
//If there is a parent repository then get parent schema as will will need to know which dimension table to use for different fields
RepositorySchema diff = null;
if (schema.ParentID != null)
{
if (!_parentSchemaCache.ContainsKey(schema.ID))
{
var parentSchema = RepositoryManager.GetSchema(schema.ParentID.Value);
_parentSchemaCache.Add(schema.ID, schema.Subtract(parentSchema));
}
diff = _parentSchemaCache[schema.ID];
dimensionValueTableNameParent = SqlHelper.GetDimensionValueTableName(schema.ParentID.Value);
}
#endregion
var sb = new StringBuilder();
parameters = new List <SqlParameter>();
didxParam = 0;
var dvidxParam = 0;
#region Find new refinements in list
//Create a cache of all next keys
var _nextKeys = new Dictionary <DimensionItem, long>();
//TODO: this is taking too long on every request (~1%)
retval.Results.ForEach(z => _nextKeys.Add(z, z.RefinementList.OrderByDescending(x => x.DVIdx).Select(x => x.DVIdx).FirstOrDefault() + 1));
var paramIndex = 0;
var needSave = false;
if (list != null)
{
foreach (var item in list.Where(x => x.FieldValue != null))
{
var values = new HashSet <string>();
var dimension = schema.FieldList.FirstOrDefault(x => x.Name == item.FieldName) as DimensionDefinition;
if (dimension != null)
{
if (dimension.DataType == RepositorySchema.DataTypeConstants.List)
{
var l = (string[])item.FieldValue;
foreach (var v in l)
{
if (!values.Contains(v))
{
values.Add(v);
}
}
}
else
{
if ((dimension.DataType == RepositorySchema.DataTypeConstants.Int || dimension.DataType == RepositorySchema.DataTypeConstants.Int64) && dimension.NumericBreak != null && dimension.NumericBreak > 0)
{
var v = Convert.ToInt64(item.FieldValue);
var scaled = ((v / dimension.NumericBreak) * dimension.NumericBreak).ToString();
if (!values.Contains(scaled))
{
values.Add(scaled);
}
}
else
{
var v = SqlHelper.GetTypedDimValue(dimension.DataType, item.FieldValue);
if (!values.Contains(v))
{
values.Add(v);
}
}
}
}
//for unique values if not exist then insert
foreach (var v in values?.Where(x => x != null).ToList())
{
long baseDVIdx;
if (schema.ParentID != null && diff.DimensionList.Any(x => x.DIdx == dimension.DIdx))
{
baseDVIdx = ((dimension.DIdx - Constants.DGROUPEXT) + 1) * Constants.DVALUEGROUPEXT; //Child Repository
}
else
{
baseDVIdx = ((dimension.DIdx - Constants.DGROUP) + 1) * Constants.DVALUEGROUP; //Normal
}
var dbDimension = retval.Results.FirstOrDefault(x => x.DIdx == dimension.DIdx);
if (!dbDimension.RefinementList.Any(x => x.FieldValue == v))
{
if (!_nextKeys.ContainsKey(dbDimension)) //If was empty then default to base index
{
_nextKeys.Add(dbDimension, baseDVIdx);
}
if (_nextKeys[dbDimension] == 1) //If was empty then default to base index
{
_nextKeys[dbDimension] = baseDVIdx;
}
var nextDVIdx = _nextKeys[dbDimension];
_nextKeys[dbDimension]++;
var newParam = new SqlParameter {
DbType = DbType.String, IsNullable = false, ParameterName = $"@__z{paramIndex}", Value = v
};
parameters.Add(newParam);
paramIndex++;
if (diff == null)
{
//This is for stand-alone tables. There is only one dimension table
var paramDIdx = new SqlParameter {
DbType = DbType.Int64, IsNullable = false, ParameterName = $"@__didx{didxParam}", Value = dimension.DIdx
};
parameters.Add(paramDIdx);
var paramDVIdx = new SqlParameter {
DbType = DbType.Int64, IsNullable = false, ParameterName = $"@__dvidx{dvidxParam}", Value = nextDVIdx
};
parameters.Add(paramDVIdx);
didxParam++;
dvidxParam++;
sb.AppendLine($"if not exists(select * from [{dimensionValueTableName}] where [DIdx] = {paramDIdx.ParameterName} and [DVIdx] = {paramDVIdx.ParameterName})");
sb.AppendLine($"insert into [{dimensionValueTableName}] ([DIdx], [DVIdx], [Value]) values ({paramDIdx.ParameterName}, {paramDVIdx.ParameterName}, {newParam.ParameterName})");
}
else
{
//This is for inherited tables. Figure out which dimension table to use
var tempTable = dimensionValueTableNameParent;
if (diff.DimensionList.Any(x => x.DIdx == dimension.DIdx))
{
tempTable = dimensionValueTableName;
}
var paramDIdx = new SqlParameter {
DbType = DbType.Int64, IsNullable = false, ParameterName = $"@__didx{didxParam}", Value = dimension.DIdx
};
parameters.Add(paramDIdx);
var paramDVIdx = new SqlParameter {
DbType = DbType.Int64, IsNullable = false, ParameterName = $"@__dvidx{dvidxParam}", Value = nextDVIdx
};
parameters.Add(paramDVIdx);
didxParam++;
dvidxParam++;
sb.AppendLine($"if not exists(select * from [{tempTable}] where [DIdx] = {paramDIdx.ParameterName} and [DVIdx] = {paramDVIdx.ParameterName})");
sb.AppendLine($"insert into [{tempTable}] ([DIdx], [DVIdx], [Value]) values ({paramDIdx.ParameterName}, {paramDVIdx.ParameterName}, {newParam.ParameterName})");
}
needSave = true;
}
}
}
if (needSave)
{
SqlHelper.ExecuteSql(ConfigHelper.ConnectionString, sb.ToString(), parameters, false);
Clear(id);
SqlHelper.MarkDimensionsChanged(id);
retval = GetCache(context, id, schema);
needSave = false;
}
}
#endregion
return(retval.Results);
}
}
catch (Exception ex)
{
throw;
}
}
