/// <summary>
/// Create database command using provided sql and by
/// replacing ? with parameter arguments.
/// </summary>
/// <param name="query">ADO.NET driver</param>
/// <param name="sql">sql to execute</param>
/// <param name="parameters">parameters to bind</param>
/// <returns></returns>
public static IDbCommand CreateCommand(
this IDatabaseQuery query,
string sql,
params object[] parameters)
{
Contract.Requires(query != null);
Contract.Requires(!string.IsNullOrWhiteSpace(sql));
var com = query.NewCommand();
com.CommandText = sql;
if (parameters != null)
{
foreach (var p in parameters)
{
var cp = com.CreateParameter();
cp.Value = p;
if (string.IsNullOrEmpty(cp.ParameterName))
{
cp.ParameterName = ":p" + (com.Parameters.Count + 1);
}
com.Parameters.Add(cp);
var index = sql.IndexOf('?');
sql = sql.Substring(0, index) + cp.ParameterName + sql.Substring(index + 1);
}
}
com.CommandText = sql;
return(com);
}
public DataTable Analyze(
IEnumerable <string> dimensions,
IEnumerable <string> facts,
IEnumerable <KeyValuePair <string, bool> > order,
ISpecification <TSource> filter,
int?limit,
int?offset)
{
var usedDimensions = new List <string>();
var usedFacts = new List <string>();
var customOrder = new List <KeyValuePair <string, bool> >();
if (dimensions != null)
{
usedDimensions.AddRange(dimensions);
}
if (facts != null)
{
usedFacts.AddRange(facts);
}
if (order != null)
{
foreach (var o in order)
{
if (o.Key != null)
{
customOrder.Add(new KeyValuePair <string, bool>(o.Key, o.Value));
}
}
}
ValidateInput(usedDimensions, usedFacts, customOrder.Select(it => it.Key));
var sb = new StringBuilder();
var alias = filter != null?filter.IsSatisfied.Parameters.First().Name : "it";
if (offset != null)
{
sb.Append("SELECT ");
sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\"")));
sb.AppendLine(" FROM (");
}
if (limit != null || offset != null)
{
sb.Append("SELECT /*+ FIRST_ROWS(n) */ ");
sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\"")));
if (offset != null)
{
sb.Append(", RowNum rn$");
}
sb.AppendLine(" FROM (");
}
sb.Append("SELECT ");
foreach (var d in usedDimensions)
{
sb.AppendFormat("{0} AS \"{1}\", ", CubeDimensions[d](alias), d);
}
foreach (var f in usedFacts)
{
sb.AppendFormat("{0} AS \"{1}\", ", CubeFacts[f](alias), f);
}
sb.Length -= 2;
sb.AppendLine();
sb.AppendFormat("FROM {0} \"{1}\"", Source, alias);
sb.AppendLine();
var command = DatabaseQuery.NewCommand();
if (filter != null)
{
var cf = Locator.Resolve <IOracleConverterFactory>();
var ep = Locator.Resolve <IExtensibilityProvider>();
var qp =
new MainQueryParts(
Locator,
cf,
ep.ResolvePlugins <IQuerySimplification>(),
ep.ResolvePlugins <IExpressionMatcher>(),
ep.ResolvePlugins <IMemberMatcher>(),
new IProjectionMatcher[0]);
var linq = new Queryable <TSource>(new QueryExecutor(DatabaseQuery, Locator, cf, ep)).Filter(filter);
var parser = QueryParser.CreateDefault();
var model = parser.GetParsedQuery(linq.Expression);
if (model.BodyClauses.Count > 0)
{
sb.AppendLine("WHERE");
for (int i = 0; i < model.BodyClauses.Count; i++)
{
var wc = model.BodyClauses[i] as WhereClause;
sb.Append(" ");
if (i > 0)
{
sb.Append("AND ");
}
sb.Append(qp.GetSqlExpression(wc.Predicate));
}
}
foreach (var p in qp.Parameters.Parameters)
{
command.Parameters.Add(p);
}
}
sb.AppendLine();
if (usedDimensions.Count > 0)
{
sb.Append("GROUP BY ");
sb.AppendLine(string.Join(", ", usedDimensions.Select(it => CubeDimensions[it](alias))));
}
if (customOrder.Count > 0)
{
sb.Append("ORDER BY ");
sb.AppendLine(string.Join(", ", customOrder.Select(it => "\"{0}\" {1}".With(it.Key, it.Value ? string.Empty : "DESC"))));
}
if (limit != null || offset != null)
{
sb.AppendLine(") sq$");
if (limit != null)
{
sb.Append("WHERE RowNum <= ");
sb.Append(limit.Value + (offset != null ? offset.Value : 0));
}
}
if (offset != null)
{
sb.AppendLine(") sq$");
sb.Append("WHERE sq$.rn$ > ");
sb.Append(offset.Value);
}
command.CommandText = sb.ToString();
return(DatabaseQuery.Fill(command));
}