/// <summary>
/// Execute query on the database and fill DataTable with the result.
/// Connection and transaction information will be appended to the provided command.
/// </summary>
/// <param name="query">ADO.NET driver</param>
/// <param name="command">command to execute</param>
/// <param name="parameters">sql additional parameters</param>
/// <returns>populated table</returns>
public static DataTable Fill(
this IDatabaseQuery query,
IDbCommand command)
{
Contract.Requires(query != null);
Contract.Requires(command != null);
var ds = new DataSet();
query.Fill(command, ds);
return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
}
/// <summary>
/// Execute query on the database and fill DataTable with the result.
/// Connection and transaction information will be appended to the provided command.
/// </summary>
/// <param name="query">ADO.NET driver</param>
/// <param name="sql">sql to execute</param>
/// <param name="parameters">sql additional parameters</param>
/// <returns>populated table</returns>
public static DataTable Fill(
this IDatabaseQuery query,
string sql,
params object[] parameters)
{
Contract.Requires(query != null);
Contract.Requires(!string.IsNullOrWhiteSpace(sql));
var com = query.CreateCommand(sql, parameters);
var ds = new DataSet();
query.Fill(com, ds);
return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
}
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));
}
public string[] Persist(IEnumerable <global::Test.XYZ> insert, IEnumerable <KeyValuePair <global::Test.XYZ, global::Test.XYZ> > update, IEnumerable <global::Test.XYZ> delete)
{
var insertedData = insert != null?insert.ToArray() : new global::Test.XYZ[0];
var updatedData = update != null?update.ToList() : new List <KeyValuePair <global::Test.XYZ, global::Test.XYZ> >();
var deletedData = delete != null?delete.ToArray() : new global::Test.XYZ[0];
if (insertedData.Length == 0 && updatedData.Count == 0 && deletedData.Length == 0)
{
return(new string[0]);
}
if (updatedData.Count > 0 && updatedData.Any(it => it.Key == null))
{
//TODO fetch only null values
var oldValues = Find(updatedData.Select(it => it.Value.URI)).ToDictionary(it => it.URI);
if (oldValues.Count != updatedData.Count)
{
throw new ArgumentException("Can't find update value. Requested: {0}, found: {1}. Missing URI: {2}".With(
updatedData.Count,
oldValues.Count,
string.Join(", ", updatedData.Select(it => it.Value.URI).Except(oldValues.Keys))));
}
global::Test.XYZ _val;
for (int i = 0; i < updatedData.Count; i++)
{
_val = updatedData[i].Value;
updatedData[i] = new KeyValuePair <global::Test.XYZ, global::Test.XYZ>(oldValues[_val.URI], _val);
}
}
updatedData.RemoveAll(kv => kv.Key.Equals(kv.Value));
var cms = Common.Utility.UseThreadLocalStream();
DatabaseQuery.Fill <global::Test.XYZ, int>(insertedData, @"nextval('""Test"".""XYZ_ID_seq""'::regclass)::int", (it, seq) => it.ID = seq);
for (int i = 0; i < insertedData.Length; i++)
{
insertedData[i].__InternalPrepareInsert();
}
foreach (var it in updatedData)
{
it.Value.__InternalPrepareUpdate();
}
for (int i = 0; i < deletedData.Length; i++)
{
deletedData[i].__InternalPrepareDelete();
}
for (int i = 0; i < insertedData.Length; i++)
{
var it = insertedData[i];
it.URI = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.BuildURI(cms.CharBuffer, it.ID);
}
foreach (var kv in updatedData)
{
kv.Value.URI = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.BuildURI(cms.CharBuffer, kv.Value.ID);
}
_InternalDoPersist(cms, insertedData, updatedData, deletedData);
var resultURI = new string[insertedData.Length];
for (int i = 0; i < resultURI.Length; i++)
{
resultURI[i] = insertedData[i].URI;
}
if (DatabaseQuery.InTransaction)
{
if (insertedData.Length > 0)
{
NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Insert, resultURI));
}
if (updatedData.Count > 0)
{
NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Update, updatedData.Select(it => it.Key.URI).ToArray()));
if (updatedData.Any(kv => kv.Key.URI != kv.Value.URI))
{
NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Change, updatedData.Where(kv => kv.Key.URI != kv.Value.URI).Select(it => it.Value.URI).ToArray()));
}
}
if (deletedData.Length > 0)
{
NotifyQueue.Enqueue(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Delete, deletedData.Select(it => it.URI).ToArray()));
}
}
else
{
if (insertedData.Length > 0)
{
Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Insert, resultURI));
}
if (updatedData.Count > 0)
{
Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Update, updatedData.Select(it => it.Key.URI).ToArray()));
if (updatedData.Any(kv => kv.Key.URI != kv.Value.URI))
{
Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Change, updatedData.Where(kv => kv.Key.URI != kv.Value.URI).Select(it => it.Value.URI).ToArray()));
}
}
if (deletedData.Length > 0)
{
Notifications.Notify(new NotifyInfo("Test.XYZ", NotifyInfo.OperationEnum.Delete, deletedData.Select(it => it.URI).ToArray()));
}
}
DataCache.Invalidate(updatedData.Select(it => it.Key.URI).Union(deletedData.Select(it => it.URI)));
return(resultURI);
}
