public void Get_Returns_BadRequest400_When_All_SQL_Parameters_Cannot_Be_Resolved()
{
const string query = "SELECT * FROM tblProducts WHERE ProductId = @ProductId";
var binding = new QueryBinding(new RouteBinding(config, "api/products/{productId}", null), query);
binding.Returns <object>(); //create a fake binding that won't get executed anyway
controller.ControllerContext.RouteData.Values[DataApiConstants.QueryBindingKey] = binding;
var exception = Assert.Throws <HttpResponseException>(() => controller.Get());
Assert.That(exception.Response.StatusCode == System.Net.HttpStatusCode.BadRequest);
}
public void Get_Returns_Single_Product()
{
//Arrange
const string query = "SELECT * FROM tblProducts";
var mockDataSource = new Mock <ISQLDataSource>();
mockDataSource.Setup(ds => ds.ExecuteQuery(query, It.IsAny <Dictionary <string, object> >())).Returns(CreateDataTable());
var binding = new QueryBinding(new RouteBinding(config, "api/products/{productId}", mockDataSource.Object), query);
binding.Returns <Product>();
controller.ControllerContext.RouteData.Values[DataApiConstants.QueryBindingKey] = binding;
//Act
var responseObject = controller.Get();
//Assert
responseObject.ShouldBeOfType <Product>();
}
private void CreateCube(Dictionary <string, object> collectedData)
{
//Connect To analysisServer
using (Server analysisServer = new Server())
{
Dictionary <string, object> executorData = new Dictionary <string, object>(); //dictionary for save executors data for next steps
if (!collectedData.ContainsKey(ApplicationIDKey))
{
executorData.Add(ApplicationIDKey, collectedData[ApplicationIDKey]);
}
analysisServer.Connect(accountWizardSettings.Get("AnalysisServer.ConnectionString"));
//Get the database
Database analysisDatabase = analysisServer.Databases.GetByName(accountWizardSettings.Get("AnalysisServer.Database"));
#region CreateBoCube
//Create bo cube
Cube boCubeTemplate = analysisDatabase.Cubes[accountWizardSettings.Get("Cube.Templates.BOTemplate")];
Cube newBOCube = boCubeTemplate.Clone();
////change cube name and id
newBOCube.Name = accountWizardSettings.Get("Cube.BO.Name.Perfix") + collectedData["AccountSettings.CubeName"].ToString();
executorData.Add("AccountSettings.CubeName", collectedData["AccountSettings.CubeName"].ToString()); //for next step
newBOCube.ID = accountWizardSettings.Get("Cube.BO.Name.Perfix") + collectedData["AccountSettings.CubeID"].ToString();
//change measures
foreach (MeasureGroup measureGroup in newBOCube.MeasureGroups)
{
foreach (KeyValuePair <string, object> input in collectedData)
{
if (input.Value is Replacment)
{
Replacment replacement = (Replacment)input.Value;
//AcquisitionS
if (input.Key.ToUpper().StartsWith(C_AccSettACQ.ToUpper()))
{
string[] acquisitions = accountWizardSettings.Get(input.Key).Split(',');
foreach (string acquisition in acquisitions)
{
Measure measure = measureGroup.Measures.FindByName(acquisition);
if (measure != null)
{ //perfix of new useres /regs
if (!replacement.CalcMembersOnly)
{
if (measureGroup.Measures.FindByName(replacement.ReplaceTo) == null) //check if their is no measure with the same name
{
measure.Name = replacement.ReplaceTo;
}
}
}
}
}
//Target AcquisitionS
else if (input.Key.ToUpper().StartsWith(C_AccSettTargetACQ.ToUpper()))
{
string[] targetAcquisitions = accountWizardSettings.Get(input.Key).Split(',');
foreach (string targetAcquisition in targetAcquisitions)
{
Measure measure = measureGroup.Measures.FindByName(targetAcquisition);
if (measure != null)
{ //key of new active useres /actives
if (measureGroup.Measures.FindByName(replacement.ReplaceTo) == null) //check if their is no measure with the same name
{
measure.Name = replacement.ReplaceTo;
}
}
}
}
// CLIENT SPECIFIC
else if (input.Key.StartsWith(C_AccSettClientSpecific, true, null))
{
if (!replacement.CalcMembersOnly)
{
Measure measure = measureGroup.Measures.FindByName(replacement.ReplaceFrom);
if (measure != null)
{
if (measureGroup.Measures.FindByName(replacement.ReplaceTo) == null) //check if their is no measure with the same name
{
measure.Name = replacement.ReplaceTo;
}
}
}
}
else if (input.Key.StartsWith(C_AccSettStringReplacemnet))// string replacment (measure -google values)
{
if (!replacement.CalcMembersOnly)
{
Measure measure = measureGroup.Measures.FindByName(replacement.ReplaceFrom);
if (measure != null)
{
if (measureGroup.Measures.FindByName(replacement.ReplaceTo) == null) //check if their is no measure with the same name
{
measure.Name = replacement.ReplaceTo;
}
}
}
}
//Copy All Data to the next step Panorama cube
if (!executorData.ContainsKey(input.Key))
{
executorData.Add(input.Key, input.Value);
}
}
}
//Change scope_id
foreach (Partition partition in measureGroup.Partitions)
{
QueryBinding queryBinding = partition.Source as QueryBinding;
if (queryBinding != null)
{
//since the "scopee_id" must be on the end of the query according to amit
//get last index of scope_id
int indexScope_id = queryBinding.QueryDefinition.LastIndexOf("scope_id", StringComparison.OrdinalIgnoreCase);
int spacesUntillEndOfQuery = queryBinding.QueryDefinition.Length - indexScope_id;
//remove all scope_id
queryBinding.QueryDefinition = queryBinding.QueryDefinition.Remove(indexScope_id, spacesUntillEndOfQuery);
//insert new scope_id
queryBinding.QueryDefinition = queryBinding.QueryDefinition.Insert(queryBinding.QueryDefinition.Length, string.Format(" Scope_ID={0}", collectedData["AccountSettings.BI_Scope_ID"].ToString()));
//
}
}
}
//change client specific measures/STRING REPLACEMENT/ in calculated members
foreach (MdxScript script in newBOCube.MdxScripts)
{
foreach (Command command in script.Commands)
{
CalculatedMembersCollection CalculatedMembersCollection = new CalculatedMembersCollection(command.Text);
foreach (KeyValuePair <string, object> input in collectedData)
{
if (input.Value is Replacment)
{
Replacment replacment = (Replacment)input.Value;
if (input.Key.StartsWith(C_AccSettClientSpecific, true, null))
{
CalculatedMembersCollection.ReplaceCalculatedMembersStrings(replacment.ReplaceFrom, replacment.ReplaceTo);
}
else if (input.Key.ToUpper().StartsWith(C_AccSettACQ.ToUpper())) //acquisitions
{
string[] acquisitions = accountWizardSettings.Get(input.Key).Split(',');
foreach (string acquisition in acquisitions)
{
CalculatedMembersCollection.ReplaceCalculatedMembersStrings(acquisition, replacment.ReplaceTo);
}
}
else if (input.Key.ToUpper().StartsWith(C_AccSettTargetACQ.ToUpper())) //target acquisitions
{
string[] targetAcquisitions = accountWizardSettings.Get(input.Key).Split(',');
foreach (string targetAcquisition in targetAcquisitions)
{
CalculatedMembersCollection.ReplaceCalculatedMembersStrings(targetAcquisition, replacment.ReplaceTo);
}
}
else if (input.Key.StartsWith(C_AccSettStringReplacemnet)) //string replacement
{
CalculatedMembersCollection.ReplaceCalculatedMembersStrings(replacment.ReplaceFrom, replacment.ReplaceTo);
}
}
}
command.Text = CalculatedMembersCollection.GetText();
}
}
//Add last step created role (for this test some existing role role 8 which not exist in the template)
//Get the roleID from last step collector
//Dictionary<string, object> roleData = GetCollectedData();
CubePermission cubePermission = new CubePermission(collectedData["AccountSettings.RoleID"].ToString(), accountWizardSettings.Get("Cube.CubePermission.ID"), accountWizardSettings.Get("Cube.CubePermission.Name"));
cubePermission.Read = ReadAccess.Allowed;
newBOCube.CubePermissions.Add(cubePermission);
///Add the new BO cube
try
{
int result = analysisDatabase.Cubes.Add(newBOCube);
newBOCube.Update(UpdateOptions.ExpandFull, UpdateMode.Create);
}
catch (Exception ex)
{
Log.Write("Error adding BO cube", ex);
}
#endregion
#region ContentCube
//check if content cube should be add too-------------------------------------
//---------------------------------------------------------------------------
executorData.Add("AccountSettings.AddContentCube", collectedData["AccountSettings.AddContentCube"]);
executorData.Add("AccountSettings.ProcessCubes", collectedData["AccountSettings.ProcessCubes"]);
if (bool.Parse(collectedData["AccountSettings.AddContentCube"].ToString()) == true)
{
//Add Content Cube
//Create bo cube
Cube ContentCubeTemplate = analysisDatabase.Cubes[accountWizardSettings.Get("Cube.Templates.ContentTemplate")];
Cube newContentCube = ContentCubeTemplate.Clone();
////change cube name and id
newContentCube.Name = accountWizardSettings.Get("Cube.Content.Name.Perfix") + collectedData["AccountSettings.CubeName"].ToString();
newContentCube.ID = accountWizardSettings.Get("Cube.Content.Name.Perfix") + collectedData["AccountSettings.CubeID"].ToString();
//Get google values for replacments;
string[] googleConversions = accountWizardSettings.Get("EdgeBI.Wizards.GoogleConversions").Split(',');
foreach (MeasureGroup measureGroup in newContentCube.MeasureGroups)
{
//Replace Google Value
foreach (KeyValuePair <string, object> input in collectedData)
{
if (input.Value is Replacment)
{
Replacment replacement = (Replacment)input.Value;
if (!replacement.CalcMembersOnly)
{
foreach (string googleConversion in googleConversions)
{
if (replacement.ReplaceFrom == googleConversion)
{
Measure measure = measureGroup.Measures.FindByName(replacement.ReplaceFrom);
if (measure != null)
{
if (measureGroup.Measures.FindByName(replacement.ReplaceTo) == null) //check if their is no measure with the same name
{
measure.Name = replacement.ReplaceTo.Replace(accountWizardSettings.Get("Cube.BO.Name.Perfix"), accountWizardSettings.Get("Cube.Content.Name.Perfix"));
}
}
}
}
}
}
}
//Replace the scope_id
foreach (Partition partition in measureGroup.Partitions)
{
QueryBinding queryBinding = partition.Source as QueryBinding;
if (queryBinding != null)
{
//since the "scopee_id" must be on the end of the query according to amit
//get last index of scope_id
int indexScope_id = queryBinding.QueryDefinition.LastIndexOf("scope_id", StringComparison.OrdinalIgnoreCase);
int spacesUntillEndOfQuery = queryBinding.QueryDefinition.Length - indexScope_id;
//remove all scope_id
queryBinding.QueryDefinition = queryBinding.QueryDefinition.Remove(indexScope_id, spacesUntillEndOfQuery);
//insert new scope_id
queryBinding.QueryDefinition = queryBinding.QueryDefinition.Insert(queryBinding.QueryDefinition.Length, string.Format(" Scope_ID={0}", collectedData["AccountSettings.BI_Scope_ID"].ToString()));
}
}
}
//Replace google conversion on calculated members
foreach (MdxScript script in newBOCube.MdxScripts)
{
foreach (Command command in script.Commands)
{
CalculatedMembersCollection CalculatedMembersCollection = new CalculatedMembersCollection(command.Text);
foreach (KeyValuePair <string, object> input in collectedData)
{
if (input.Value is Replacment)
{
Replacment replacment = (Replacment)input.Value;
foreach (string googleConversion in googleConversions)
{
if (replacment.ReplaceFrom == googleConversion)
{
CalculatedMembersCollection.ReplaceCalculatedMembersStrings(replacment.ReplaceFrom, replacment.ReplaceTo);
}
}
}
}
command.Text = CalculatedMembersCollection.GetText();
}
}
//add last step role (cube permission
cubePermission = new CubePermission(collectedData["AccountSettings.RoleID"].ToString(), accountWizardSettings.Get("Cube.CubePermission.ID"), accountWizardSettings.Get("Cube.CubePermission.Name"));
cubePermission.Read = ReadAccess.Allowed;
newContentCube.CubePermissions.Add(cubePermission);
///Add the new Content cube
try
{
int result = analysisDatabase.Cubes.Add(newContentCube);
newContentCube.Update(UpdateOptions.ExpandFull, UpdateMode.Create);
}
catch (Exception ex)
{
Log.Write("Error adding BO cube", ex);
}
}
#endregion
SaveExecutorData(executorData);
}
}
internal static string GetQueryDefinition(Database d, NamedComponent nc, Microsoft.AnalysisServices.Binding b, List <DataItem> columnsNeeded)
{
StringBuilder sQuery = new StringBuilder();
if (b is DsvTableBinding)
{
DsvTableBinding oDsvTableBinding = (DsvTableBinding)b;
DataSourceView oDSV = d.DataSourceViews[oDsvTableBinding.DataSourceViewID];
DataTable oTable = oDSV.Schema.Tables[oDsvTableBinding.TableID];
if (oTable == null)
{
throw new Exception("DSV table " + oDsvTableBinding.TableID + " not found");
}
else if (!oTable.ExtendedProperties.ContainsKey("QueryDefinition") && oTable.ExtendedProperties.ContainsKey("DbTableName"))
{
foreach (DataColumn oColumn in oTable.Columns)
{
bool bFoundColumn = false;
if (columnsNeeded == null)
{
bFoundColumn = true;
}
else
{
foreach (DataItem di in columnsNeeded)
{
if (GetColumnBindingForDataItem(di).TableID == oTable.TableName && GetColumnBindingForDataItem(di).ColumnID == oColumn.ColumnName)
{
bFoundColumn = true;
}
}
}
if (bFoundColumn)
{
if (sQuery.Length == 0)
{
sQuery.Append("select ");
}
else
{
sQuery.Append(",");
}
if (!oColumn.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
sQuery.Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq);
}
else
{
sQuery.Append(oColumn.ExtendedProperties["ComputedColumnExpression"].ToString()).Append(" as ").Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq);
}
}
}
if (sQuery.Length == 0)
{
throw new Exception("There was a problem constructing the query.");
}
sQuery.Append("from ");
if (oTable.ExtendedProperties.ContainsKey("DbSchemaName"))
{
sQuery.Append(sq).Append(oTable.ExtendedProperties["DbSchemaName"].ToString()).Append(fq).Append(".");
}
sQuery.Append(sq).Append(oTable.ExtendedProperties["DbTableName"].ToString());
sQuery.Append(fq).Append(" ").Append(sq).Append(oTable.ExtendedProperties["FriendlyName"].ToString()).AppendLine(fq);
}
else if (oTable.ExtendedProperties.ContainsKey("QueryDefinition"))
{
sQuery.AppendLine("select *");
sQuery.AppendLine("from (");
sQuery.AppendLine(oTable.ExtendedProperties["QueryDefinition"].ToString());
sQuery.AppendLine(") x");
}
else
{
throw new Exception("Current the code does not support this type of query.");
}
}
else if (b is QueryBinding)
{
QueryBinding oQueryBinding = (QueryBinding)b;
sQuery.Append(oQueryBinding.QueryDefinition);
}
else if (b is ColumnBinding)
{
ColumnBinding cb = (ColumnBinding)b;
object parent = cb.Parent;
DataTable dt = d.DataSourceViews[0].Schema.Tables[cb.TableID];
if (nc is DimensionAttribute)
{
DimensionAttribute da = (DimensionAttribute)nc;
if (da.Parent.KeyAttribute.KeyColumns.Count != 1)
{
throw new Exception("Attribute " + da.Parent.KeyAttribute.Name + " has a composite key. This is not supported for a key attribute of a dimension.");
}
string sDsvID = ((DimensionAttribute)nc).Parent.DataSourceView.ID;
columnsNeeded.Add(new DataItem(cb.Clone()));
columnsNeeded.Add(da.Parent.KeyAttribute.KeyColumns[0]);
return(GetQueryDefinition(d, nc, new DsvTableBinding(sDsvID, cb.TableID), columnsNeeded));
}
else
{
throw new Exception("GetQueryDefinition does not currently support a ColumnBinding on a object of type " + nc.GetType().Name);
}
}
else
{
throw new Exception("Not a supported query binding type: " + b.GetType().FullName);
}
return(sQuery.ToString());
}
/// <summary>
/// returns information about the binding for table/dsvTable or ColumnBinding, we return the table ID
/// for query binding, the returned value is -1, but the query out parameter is set to the query definition
/// </summary>
/// <param name="connectionID"></param>
/// <param name="dsvTableNameToIdMap"></param>
/// <param name="binding"></param>
/// <param name="?"></param>
/// <returns></returns>
private int GetSourceIDForBinding(int connectionID, Dictionary <string, int> dsvTableNameToIdMap, Binding binding, out string query)
{
int sourceID = -1;
query = null;
if (binding != null)
{
if (binding is ColumnBinding)
{
ColumnBinding colBinding = (ColumnBinding)binding;
// get the id of the dsv table colBinding.TableID
if (dsvTableNameToIdMap.ContainsKey(colBinding.TableID))
{
sourceID = dsvTableNameToIdMap[colBinding.TableID];
}
}
else
{
if (binding is DsvTableBinding)
{
DsvTableBinding dsvTableBinding = (DsvTableBinding)binding;
if (dsvTableNameToIdMap.ContainsKey(dsvTableBinding.TableID))
{
sourceID = dsvTableNameToIdMap[dsvTableBinding.TableID];
}
}
else
{
if (binding is TableBinding)
{
TableBinding tableBinding = (TableBinding)binding;
string tableName = GetFullyQualifiedTableName(tableBinding.DbSchemaName, tableBinding.DbTableName);
if (threePartNames)
{
tableName = String.Format("[{0}].{1}", repository.RetrieveDatabaseNameFromConnectionID(connectionID), tableName);
}
// get the table name from the repository itself
sourceID = repository.GetTable(connectionID, tableName);
if (sourceID == -1)
{
sourceID = repository.AddObject(tableName, string.Empty, RelationalEnumerator.ObjectTypes.Table, connectionID);
}
}
else
{
if (binding is MeasureGroupDimensionBinding)
{
// the caller will handle this since we need the list of cb dimensions
}
else
{
if (binding is QueryBinding)
{
QueryBinding queryBinding = (QueryBinding)binding;
query = queryBinding.QueryDefinition;
}
}
}
}
}
}
return(sourceID);
}
