public void CreateQueryObject()
{
var ai = new QueryObject();
Assert.AreEqual(ai.Limit, -1);
Assert.AreEqual(ai.Skip, -1);
Assert.AreEqual(ai.WhereCondition, String.Empty);
Assert.AreEqual(ai.OrderByCondition, String.Empty);
Assert.AreEqual(ai.UseJoins, true);
Assert.AreEqual(ai.Action, QueryAction.Select);
Assert.AreEqual(ai.SelectFields, string.Empty);
Assert.AreEqual(ai.QueryBody, string.Empty);
Assert.AreEqual(ai.TypeOfQuery, QueryType.Query);
Assert.AreEqual(ai.ForceSelectField, false);
}
public void ShouldBeAbleToBuildCustomEventArgs()
{
var a = new OnExecuteActionEventArgs("action", "p1", "p2");
Assert.AreEqual("action",a.ActionName);
Assert.AreEqual("p1", a.Parameters[0]);
Assert.AreEqual("p2", a.Parameters[1]);
a = new OnExecuteActionEventArgs("action");
Assert.AreEqual("action", a.ActionName);
Assert.IsNotNull(a.Parameters);
Assert.AreEqual(0, a.Parameters.Length);
var b = new OnPostCreateEventArgs("action");
Assert.AreEqual("action", b.Item);
var c = new OnPostDeleteEventArgs(long.MaxValue);
Assert.AreEqual(long.MaxValue, c.Id);
var d = new OnPostInstantiateEventArgs("action");
Assert.AreEqual("action", d.Item);
var e = new OnPostUpdateEventArgs("action");
Assert.AreEqual("action", e.Item);
var f = new OnPreCreateEventArgs("action");
Assert.AreEqual("action", f.Item);
var g = new OnPreDeleteEventArgs(long.MaxValue);
Assert.AreEqual(long.MaxValue, g.Id);
var qo = new QueryObject();
var h = new OnPreSelectEventArgs(qo);
Assert.AreSame(qo, h.Query);
var i = new OnPreUpdateEventArgs("action");
Assert.AreEqual("action", i.Item);
var j = new OnVerifyEventArgs("action","operation");
Assert.AreEqual("action", j.Item);
Assert.AreEqual("operation", j.Operation);
}
public void Delete(Int64 leftModelId, Int64 rightModelId = 0)
{
using (var scope = new TransactionScope())
{
var qo = new QueryObject();
if (rightModelId == 0)
{
qo.WhereCondition = string.Format("{0}.LeftId={1}", TableName, leftModelId);
}
else
{
qo.WhereCondition = string.Format("{0}.LeftId={1} AND {0}.RightId={2}", TableName, leftModelId, rightModelId);
}
var ob = (ManyToManyModel) GetFirst(qo);
if (ob != null)
{
base.Delete(ob.Id);
UpdateOnChange(leftModelId);
}
scope.Complete();
}
}
public virtual List<object> GetChildren(Int64 id)
{
var qo = new QueryObject
{
WhereCondition = string.Format("{0}.ParentId={1}", Repository.TableName, id),
OrderByCondition = string.Format("{0}.Ordering ASC", Repository.TableName)
};
return Repository.ExecuteSql(qo) as List<object>;
}
private void SetupQueryForPlugins(QueryObject queryObject, List<string> joinsList, List<string> fieldsList)
{
foreach (var item in _repositoryPlugins)
{
var jrep = item as IJoinRepositoryPlugin;
if (jrep != null)
{
if (queryObject.UseJoins)
{
SetupJoins(queryObject, joinsList, fieldsList, jrep);
}
else if (!queryObject.ForceSelectField)
{
SetupStandardFields(fieldsList, jrep);
}
}
}
}
private void SetupJoins(QueryObject queryObject, List<string> joinsList, List<string> fieldsList,
IJoinRepositoryPlugin jrep)
{
var results = new List<string>();
for (int fld = 0; fld < jrep.JoinableFields.Count; fld++)
{
string fieldOnRemote = jrep.JoinableFields[fld];
string fieldOnLocal = jrep.JoinableFieldsLocal[fld];
if (!queryObject.ForceSelectField)
fieldsList.Add(TableName + "." + fieldOnLocal);
results.Add(TableName + "." + fieldOnLocal + "=" + jrep.JoinedRepository.TableName + "." + fieldOnRemote);
}
joinsList.Add(jrep.JoinedRepository.TableName + " ON (" + string.Join(" AND ", results) + ")");
for (int fld = 0; fld < jrep.JoinedRepository.SelectableFields.Count; fld++)
{
fieldsList.Add(jrep.JoinedRepository.TableName + "." +
jrep.JoinedRepository.SelectableFields[fld] + " AS " +
jrep.JoinedRepository.TableName + "_" +
jrep.JoinedRepository.SelectableFields[fld]);
}
}
private void SetupFieldsAndJoins(QueryObject queryObject)
{
//queryObject.QueryBody = TableName;
var joinsList = new List<string>();
var fieldsList = new List<string>();
OnExecuteBooleanResultEventHandler(new OnPreSelectEventArgs(queryObject), ref _preSelectHandler);
SetupQueryForPlugins(queryObject, joinsList, fieldsList);
if (joinsList.Count > 0)
{
queryObject.QueryBody += " LEFT JOIN " + string.Join(" LEFT JOIN ", joinsList);
}
for (int fld = 0; fld < SelectableFields.Count && !queryObject.ForceSelectField; fld++)
{
fieldsList.Add(TableName + "." + SelectableFields[fld]);
}
if (!string.IsNullOrEmpty(queryObject.SelectFields)) fieldsList.Add(queryObject.SelectFields);
queryObject.SelectFields = string.Join(",", fieldsList);
}
private object CalculateQueryResult(QueryObject query, string queryToExecute)
{
object result;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var command = new SqlCommand(queryToExecute, conn))
{
command.CommandType = CommandType.Text;
try
{
result = CalculateBasedOnQueryType(query, command);
}
catch (SqlException exception)
{
if (exception.Number == 2601) // Cannot insert duplicate key row in object error
{
// handle duplicate key error
throw new RepositoryDuplicateKeyException(GetType().Name, queryToExecute, exception);
}
throw; // throw exception if this exception is unexpected
}
}
conn.Close();
}
return result;
}
public virtual void Delete(long id)
{
using (var scope = new TransactionScope())
{
if (!OnExecuteBooleanResultEventHandler(new OnPreDeleteEventArgs(id), ref _preDeleteHandler)) return;
if (!OnPreDelete(id)) return;
var qo = new QueryObject
{
UseJoins = false,
TypeOfQuery = QueryType.NonQuery,
Action = QueryAction.Delete,
WhereCondition = string.Format("{0}.Id={1}",
TableName,
id)
};
ExecuteSql(qo);
if (!OnExecuteBooleanResultEventHandler(new OnPostDeleteEventArgs(id), ref _postDeleteHandler)) return;
scope.Complete();
}
}
public virtual List<object> GetAllChildren(Int64 id)
{
if (!UseNestedSet) throw new NotSupportedException("Should use Nested Set behaviour");
var current = Repository.GetById(id, new QueryObject {UseJoins = false});
var qo = new QueryObject
{
WhereCondition = string.Format("{0}.LeftNs>{1} && {0}.RightNs<{2}",
Repository.TableName,
((ITreeModel) current).LeftNs,
((ITreeModel) current).RightNs)
};
return Repository.ExecuteSql(qo) as List<object>;
}
protected virtual QueryObject UpdateQuery(object article)
{
//TODO for sqlserver update u set a=b FROM c inner join d on c.a=d.b
//TODO for mysql update u c inner join d on c.a=d.b set a=b
var qo = new QueryObject
{
UseJoins = false,
Action = QueryAction.Update,
TypeOfQuery = QueryType.NonQuery
};
Dictionary<string, object> converted = ConvertToDb(article);
string updateQuery = string.Empty;
int counter = 0;
var updatableFields = new List<string>();
updatableFields.AddRange(UpdatableFields);
for (int index = 0; index < _repositoryPlugins.Count; index++)
{
var item = _repositoryPlugins[index];
updatableFields.AddRange(item.UpdatableFields);
}
for (int index = 0; index < updatableFields.Count; index++)
{
string fieldName = updatableFields[index];
if (fieldName != "Id")
{
if (counter > 0) updateQuery += ",";
object ob = converted[fieldName];
updateQuery += ExtractUpdateQueryParameters(fieldName, ob);
counter++;
}
}
qo.QueryBody = string.Format(" SET {0}", updateQuery);
qo.WhereCondition = string.Format(" {0}.Id={1} ",
TableName,
((IModel) article).Id);
return qo;
}
protected virtual QueryObject CreateQuery(object article)
{
Dictionary<string, object> converted = ConvertToDb(article);
var qo = new QueryObject
{
Action = QueryAction.Insert,
UseJoins = false,
TypeOfQuery = QueryType.Scalar
};
string fieldsName = string.Empty;
string fieldsContent = string.Empty;
int counter = 0;
var updatableFields = new List<string>();
updatableFields.AddRange(UpdatableFields);
for (int index = 0; index < _repositoryPlugins.Count; index++)
{
var item = _repositoryPlugins[index];
updatableFields.AddRange(item.UpdatableFields);
}
for (int index = 0; index < updatableFields.Count; index++)
{
var fieldName = updatableFields[index];
if (fieldName != "Id")
{
if (counter > 0)
{
fieldsName += ",";
fieldsContent += ",";
}
fieldsName += fieldName;
object ob = converted[fieldName];
if (ob == null)
{
fieldsContent += string.Format("NULL");
}
// ReSharper disable CanBeReplacedWithTryCastAndCheckForNull
else if (ob is string)
// ReSharper restore CanBeReplacedWithTryCastAndCheckForNull
{
fieldsContent += string.Format("'{0}'", ((string) ob).Trim());
}
else if (ob is DateTime)
{
fieldsContent += string.Format("'{0}'", ((DateTime) ob).ToString("yyyy-MM-ddTHH:mm:ss.fff"));
}
else
{
fieldsContent += string.Format("{0}", ob);
}
counter++;
}
}
qo.QueryBody = string.Format("({0}) OUTPUT INSERTED.Id VALUES ({1})",
fieldsName,
fieldsContent);
return qo;
}
public object GetFirst(QueryObject query)
{
var list = GetAll(query);
if (list == null || list.Count == 0) return null;
return list[0];
}
public virtual object GetById(Int64 id, QueryObject query = null)
{
if (query == null)
{
query = new QueryObject {UseJoins = true};
}
if (string.IsNullOrEmpty(query.WhereCondition))
{
query.WhereCondition = string.Format(" {0}.Id={1} ", TableName, id);
}
else
{
query.WhereCondition = string.Format(" ({0}) AND {1} ", query.WhereCondition,
string.Format(" {0}.Id={1}", TableName, id));
}
return GetFirst(query);
}
public virtual List<object> GetAll(QueryObject query)
{
if (query == null) query = new QueryObject();
return ExecuteSql(query) as List<object>;
}
public Object ExecuteSql(QueryObject query)
{
SetupFieldsAndJoins(query);
var intercalar = AdaptQueryAction(query);
var queryToExecute =
string.Format("{0} {1} {2} {3} {4}",
query.Action,
intercalar,
query.QueryBody,
string.IsNullOrEmpty(query.WhereCondition)
? string.Empty
: string.Format("WHERE {0}", query.WhereCondition),
string.IsNullOrEmpty(query.OrderByCondition)
? string.Empty
: string.Format("ORDER BY {0}", query.OrderByCondition)
);
Object result = CalculateQueryResult(query, queryToExecute);
return result;
}
private bool OnPreCreate(object sender, EventArgs eventArgs)
{
var ea = (OnPreCreateEventArgs) eventArgs;
object item = ea.Item;
var getMaxValue = new QueryObject
{
TypeOfQuery = QueryType.Scalar,
UseJoins = false,
SelectFields = "MAX(Ordering)",
ForceSelectField = true,
WhereCondition = string.Format("{0}.ParentId={1}",
Repository.TableName,
((ITreeModel) item).ParentId)
};
object result = Repository.ExecuteSql(getMaxValue);
((ITreeModel) item).Ordering = 0;
if (result != DBNull.Value && result != null)
{
((ITreeModel) item).Ordering = (Int32) result;
((ITreeModel) item).Ordering++;
}
if (UseNestedSet)
{
var parentItem = Repository.GetById(((ITreeModel) item).ParentId, new QueryObject {UseJoins = false});
if (parentItem != null)
{
var updateParentChildren = new QueryObject
{
UseJoins = false,
TypeOfQuery = QueryType.NonQuery,
Action = QueryAction.Update,
QueryBody = string.Format("SET " +
"{0}.LeftNs=({0}.LeftNs+2)," +
"{0}.RightNs=({0}.RightNs+2)",
Repository.TableName),
WhereCondition = string.Format(" {0}.LeftNs>{1} AND {0}.RightNs>{1}",
Repository.TableName,
((ITreeModel) parentItem).LeftNs)
};
((ITreeModel) item).LeftNs = ((ITreeModel) parentItem).LeftNs + 1;
((ITreeModel) item).RightNs = ((ITreeModel) parentItem).LeftNs + 2;
((ITreeModel) parentItem).RightNs += 2;
if (((ITreeModel) item).ParentId != 0) Repository.Update(parentItem);
Repository.ExecuteSql(updateParentChildren);
}
else
{
var getMax = new QueryObject
{
UseJoins = false,
SelectFields = "MAX(RightNs)",
TypeOfQuery = QueryType.Scalar,
ForceSelectField = true
};
result = Repository.ExecuteSql(getMax);
Int64 max = 0;
if (result != null && result != DBNull.Value) max = (Int64) result;
((ITreeModel) item).LeftNs = max + 1;
((ITreeModel) item).RightNs = max + 2;
}
}
return true;
}
private bool OnPreDelete(object sender, EventArgs eventArgs)
{
var ea = (OnPreDeleteEventArgs) eventArgs;
var id = ea.Id;
var am = (ITreeModel) Repository.GetById(id, new QueryObject {UseJoins = false});
var parent = (ITreeModel) Repository.GetById(am.ParentId, new QueryObject {UseJoins = false});
Int64 maxOrdering = 0;
object maxOrderingObject = Repository.ExecuteSql(new QueryObject
{
UseJoins = false,
ForceSelectField = true,
SelectFields = "MAX(Ordering)",
WhereCondition = string.Format("{0}.ParentId={1}",
Repository.TableName,
parent != null ? parent.ParentId : 0),
TypeOfQuery = QueryType.Scalar
});
if (maxOrderingObject != null)
{
maxOrdering = (int) maxOrderingObject + 1;
}
var updateQuery = new QueryObject
{
UseJoins = false,
TypeOfQuery = QueryType.NonQuery,
Action = QueryAction.Update,
QueryBody = string.Format("SET {0}.ParentId={1}, {0}.Ordering=({2}+{0}.Ordering)",
Repository.TableName,
am.ParentId,
maxOrdering),
WhereCondition = string.Format("{0}.ParentId={1}",
Repository.TableName,
id)
};
Repository.ExecuteSql(updateQuery);
return true;
}
private object CalculateBasedOnQueryType(QueryObject query, SqlCommand command)
{
object result = null;
switch (query.TypeOfQuery)
{
case (QueryType.Query):
{
result = ExecuteSqlQuery(command);
}
break;
case (QueryType.NonQuery):
{
// ReSharper disable RedundantAssignment
result = false;
// ReSharper restore RedundantAssignment
result = command.ExecuteNonQuery();
if (query.Action != QueryAction.Update)
{
result = true;
}
}
break;
case (QueryType.Scalar):
{
result = command.ExecuteScalar();
}
break;
}
return result;
}
private string AdaptQueryAction(QueryObject query)
{
string intercalar;
switch (query.Action)
{
case (QueryAction.Select):
{
intercalar = string.Format("{0} FROM {1}", query.SelectFields, TableName);
}
break;
case (QueryAction.Update):
{
intercalar = string.Format(" {0}", TableName);
query.OrderByCondition = string.Empty;
query.SelectFields = string.Empty;
}
break;
case (QueryAction.Insert):
{
intercalar = string.Format("INTO {0}", TableName);
query.WhereCondition = string.Empty;
query.OrderByCondition = string.Empty;
query.SelectFields = string.Empty;
}
break;
case (QueryAction.Delete):
{
intercalar = string.Format("FROM {0}", TableName);
query.OrderByCondition = string.Empty;
query.SelectFields = string.Empty;
}
break;
default:
throw new NotImplementedException(string.Format("Query Action {0} not allowed",
query.Action));
}
return intercalar;
}
public OnPreSelectEventArgs(QueryObject queryObject)
{
Query = queryObject;
//bool OnVerify(object item, string operation);
}