public static IDbSelect Map(
IDbSelect dbSelect, Type returnType,
IModelInfoProvider infoProvider, IDbObjectFactory dbFactory, UniqueNameGenerator nameGenerator)
{
// If the select returns specified columns, it means there is a constructor in Select,
// e.g (.Select(d => new { A = d.ABC }))
// In this case we do not need to add alias to the end result as where will be one in the select
if (dbSelect.Selection.Any(s => !(s is IDbRefColumn)))
{
return(dbSelect);
}
var entityInfo = infoProvider.FindEntityInfo(returnType);
if (!entityInfo.RequirePropertyNameMapping())
{
return(dbSelect);
}
var alias = nameGenerator.GenerateAlias(dbSelect, TranslationConstants.SubSelectPrefix, true);
var newSelectRef = dbFactory.BuildRef(dbSelect, alias);
var newSelect = dbFactory.BuildSelect(newSelectRef);
foreach (var fieldInfo in entityInfo.Columns)
{
var column = dbFactory.BuildColumn(
newSelectRef, fieldInfo.DbName, fieldInfo.ValType, fieldInfo.PropertyName);
newSelect.Selection.Add(column);
}
return(newSelect);
}
private static IDbScript TranslateGraph(
IncludeGraph includeGraph, IModelInfoProvider infoProvider, IDbObjectFactory dbFactory, UniqueNameGenerator nameGenerator)
{
TranslateGraphNode(includeGraph.Root, infoProvider, dbFactory, nameGenerator);
return(includeGraph.GetScript(dbFactory));
}
public void Setup()
{
_manager = new ConnectionManager(new MySqlConnection(connectionString));
_factory = new DbObjectFactory(MySqlConnectorFactory.Instance);
_manager.Connection.Open();
}
public override IDbObject GetCreateStatement(IDbObjectFactory factory)
{
Func <string> action = () =>
{
var sb = new StringBuilder();
sb.AppendLine($"create temporary table if not exists {this} as (");
// add row
var rowNumberScript = new MySqlVariableColumn("@var_row := @var_row + 1")
{
Alias = TranslationConstants.MySqlRowNumberColumnAlias
};
SourceSelect.Selection.Add(rowNumberScript);
var dbJoin = new MySqlVariableJoin("select @var_row := 0", "_var_row_");
SourceSelect.Joins.Add(dbJoin);
sb.AppendLineWithSpace(SourceSelect.ToString());
sb.AppendLine(")");
SourceSelect.Selection.Remove(rowNumberScript);
SourceSelect.Joins.Remove(dbJoin);
return(sb.ToString());
};
return(new DbDynamicStatement(action));
}
private static IDbJoin MakeJoin(IDbSelect ownerSelect, IDbObject tempSelect, IDbObjectFactory dbFactory, UniqueNameGenerator nameGenerator)
{
var joinAlias = nameGenerator.GenerateAlias(ownerSelect, TranslationConstants.SubSelectPrefix);
var joinTo = dbFactory.BuildRef(tempSelect, joinAlias);
return(dbFactory.BuildJoin(joinTo, ownerSelect));
}
private static IDbSelectable CreateNewSelectableForWrappingSelect(
IDbSelect dbSelect, IDbSelectable selectable, DbReference dbRef, Expression m,
IDbObjectFactory dbFactory, UniqueNameGenerator nameGenerator)
{
if (dbRef == null)
{
return(selectable);
}
var oCol = selectable as IDbColumn;
if (oCol != null)
{
return(dbFactory.BuildColumn(dbRef, oCol.GetAliasOrName(), oCol.ValType));
}
var oRefCol = selectable as IDbRefColumn;
if (oRefCol != null)
{
return(dbFactory.BuildRefColumn(dbRef, oRefCol.Alias, oRefCol));
}
if (selectable is IDbFunc oDbFunc)
{
if (string.IsNullOrEmpty(oDbFunc.Alias))
{
oDbFunc.Alias = nameGenerator.GenerateAlias(dbSelect, oDbFunc.Name, true);
}
return(dbFactory.BuildColumn(dbRef, oDbFunc.Alias, oDbFunc.ReturnType));
}
return(dbFactory.BuildColumn(dbRef, selectable.Alias, typeof(string)));
}
public IDbScript GetScript(IDbObjectFactory dbFactory)
{
var script = dbFactory.BuildScript();
UpdateScript(Root, script, dbFactory);
return(script);
}
public static LinqExecutor <T> Make <T>(
IQueryable <T> queryable, IModelInfoProvider infoProvider, IDbObjectFactory dbFactory,
AbstractMethodTranslator[] addons = null)
{
var script = QueryTranslator.Translate(queryable.Expression, infoProvider, dbFactory, out var includeGraph, addons);
return(new LinqExecutor <T>(includeGraph, script));
}
private static void TranslateGraphNode(
IncludeNode graphNode, IModelInfoProvider infoProvider, IDbObjectFactory dbFactory,
UniqueNameGenerator nameGenerator, AbstractMethodTranslator[] addons)
{
//TODO: check if there is a chain in the relation and throw exceptions
var fromSelect = graphNode.FromNode?.Select;
var state = new TranslationState();
if (fromSelect != null)
{
state.ResultStack.Push(graphNode.FromNode.Select);
}
var translator = new ExpressionTranslator(infoProvider, dbFactory, state, addons);
// translated current included node
translator.Visit(graphNode.Expression);
var dbObject = translator.GetElement();
var dbRef = dbObject as DbReference;
var includedSelect = dbRef != null?dbFactory.BuildSelect(dbRef) : (IDbSelect)dbObject;
includedSelect = SqlSelectOptimizer.Optimize(includedSelect);
// if from node is not null, then we need to add the forgien key into the selection
// of the temp table, and make the new select join to the translated select
if (graphNode.FromNode != null)
{
UpdateFromNodeTempTable(graphNode.FromNode, dbObject, includedSelect, dbFactory, nameGenerator);
}
// if the graph node has child node, we need to create temp table for current node
// so that the child nodes can join to the temp table containing forgien keys
if (graphNode.ToNodes.Any())
{
UpdateIncludeSelectAndProcessToNodes(graphNode, includedSelect, infoProvider, dbFactory, nameGenerator, addons);
}
else
{
graphNode.Select = includedSelect;
}
var returnType = graphNode.Expression.GetReturnBaseType();
graphNode.Select = DbColumnToEntityPropertyMapper.Map(graphNode.Select, returnType, infoProvider, dbFactory, nameGenerator);
// update fill function
if (graphNode.FromNode != null)
{
graphNode.FillFunc = FillFunctionMaker.Make(graphNode, infoProvider);
}
graphNode.Graph.ScriptToNodes[graphNode.Select] = graphNode;
}
/// <summary>
/// Constructor to query a database using an existing <see cref="DbConnection"/> to initialize the <paramref name="connection"/>
/// </summary>
/// <param name="connection">An instance of <see cref="DbConnection"/> to use to query a database </param>
public SqlExecutor(DbConnection connection)
{
//Set properties
_connection = connection;
_factory = new DbObjectFactory(connection);
//Get the connection string from the connection
ConnectionStringBuilder.ConnectionString = _connection.ConnectionString;
}
public override IDbObject GetDropStatement(IDbObjectFactory factory)
{
Func <string> action = () =>
{
var sb = new StringBuilder();
sb.AppendLine($"drop temporary table {this}");
return(sb.ToString());
};
return(new DbDynamicStatement(action));
}
private static void UpdateIncludeSelectAndProcessToNodes(
IncludeNode graphNode, IDbSelect includedSelect, IModelInfoProvider infoProvider,
IDbObjectFactory dbFactory, UniqueNameGenerator nameGenerator, AbstractMethodTranslator[] addons)
{
// create temp table
var entityRef = includedSelect.GetReturnEntityRef();
var returnTable = (IDbTable)entityRef.Referee;
var newIncludedSelect = dbFactory.BuildSelect(returnTable);
newIncludedSelect.From.Alias = nameGenerator.GenerateAlias(newIncludedSelect, returnTable.TableName);
var tempTableName = TranslationConstants.TempTablePreix + nameGenerator.GenerateAlias(null, returnTable.TableName, true);
var tempTable = dbFactory.BuildTempTable(tempTableName, includedSelect);
var tempSelect = dbFactory.BuildSelect(tempTable);
tempSelect.From.Alias = nameGenerator.GenerateAlias(tempSelect, tempTable.TableName);
var joinToTemp = MakeJoin(newIncludedSelect, tempSelect, dbFactory, nameGenerator);
var joinTo = joinToTemp.To;
newIncludedSelect.Joins.Add(joinToTemp);
foreach (var pk in returnTable.PrimaryKeys)
{
var fromPkCol = dbFactory.BuildColumn(entityRef, pk.Name, pk.ValType);
includedSelect.Selection.Add(fromPkCol);
var toPkCol = dbFactory.BuildColumn(tempSelect.From, pk.Name, pk.ValType);
tempSelect.Selection.Add(toPkCol);
tempSelect.GroupBys.Add(toPkCol);
toPkCol = dbFactory.BuildColumn(joinTo, pk.Name, pk.ValType);
var binary = dbFactory.BuildBinary(fromPkCol, DbOperator.Equal, toPkCol);
joinToTemp.Condition = joinToTemp.Condition.UpdateBinary(binary, dbFactory);
}
var orderCol = dbFactory.BuildColumn(tempSelect.From, tempTable.RowNumberColumnName, typeof(int));
tempSelect.Selection.Add(orderCol);
orderCol = dbFactory.BuildColumn(joinTo, tempTable.RowNumberColumnName, typeof(int));
newIncludedSelect.OrderBys.Add(orderCol);
graphNode.Select = newIncludedSelect;
graphNode.TempTable = tempTable;
foreach (var toNode in graphNode.ToNodes)
{
TranslateGraphNode(toNode, infoProvider, dbFactory, nameGenerator, addons);
}
}
public override IDbObject GetDropStatement(IDbObjectFactory factory)
{
string Action()
{
var sb = new StringBuilder();
sb.AppendLine($"drop table if exists {this}");
return(sb.ToString());
}
return(new DbDynamicStatement(Action));
}
public static IDbSelectable[] ProcessSelection(IDbObject dbObj, IDbObjectFactory factory)
{
var dbList = dbObj as IDbList <DbKeyValue>;
if (dbList != null)
{
var keyVals = dbList;
return(keyVals.SelectMany(kv => ProcessSelection(kv, factory)).ToArray());
}
var obj = dbObj as DbReference;
if (obj != null)
{
var dbRef = obj;
return(new IDbSelectable[] { factory.BuildRefColumn(dbRef, dbRef.RefColumnAlias) });
}
var dbBinary = dbObj as IDbBinary;
if (dbBinary != null && (
dbBinary.Operator == DbOperator.Equal ||
dbBinary.Operator == DbOperator.NotEqual ||
dbBinary.Operator == DbOperator.GreaterThan ||
dbBinary.Operator == DbOperator.GreaterThanOrEqual ||
dbBinary.Operator == DbOperator.LessThan ||
dbBinary.Operator == DbOperator.LessThanOrEqual ||
dbBinary.Operator == DbOperator.Is ||
dbBinary.Operator == DbOperator.IsNot))
{
var dbTrue = factory.BuildConstant(true);
var tuple = Tuple.Create <IDbBinary, IDbObject>(dbBinary, dbTrue);
return(new IDbSelectable[] { factory.BuildCondition(new [] { tuple }, factory.BuildConstant(false)) });
}
var keyValue = dbObj as DbKeyValue;
if (keyValue == null)
{
return new[] { (IDbSelectable)dbObj }
}
;
var selectables = ProcessSelection(keyValue.Value, factory);
foreach (var selectable in selectables)
{
selectable.Alias = keyValue.Key;
}
return(selectables);
}
public IDbScript GetScript(IDbObjectFactory dbFactory)
{
var script = dbFactory.BuildScript();
script.PreScripts.AddRange(_preScripts);
var dbSelect = GetLastSelect();
script.Scripts.Add(dbSelect);
script.PostScripts.AddRange(_postScripts);
return(script);
}
public override IDbObject GetCreateStatement(IDbObjectFactory factory)
{
string Action()
{
var sb = new StringBuilder();
sb.AppendLine($"create temporary table if not exists {this} as ");
sb.AppendLineWithSpace(SourceSelect.ToString());
sb.AppendLine();
return(sb.ToString());
}
return(new DbDynamicStatement(Action));
}
/// <summary>
/// Add selectable into the selection of the select which referred by the ref column.
/// If the ref column has a RefTo ref column, this function will also recursively add
/// the selectable to RefTo ref columns
/// </summary>
public static void AddToReferedSelect(
this IDbRefColumn refCol, IDbObjectFactory factory, string colName, DbType colType, string alias = null)
{
if (refCol.RefTo != null)
{
refCol.RefTo.AddToReferedSelect(factory, colName, colType, alias);
colName = alias ?? colName;
}
var column = factory.BuildColumn(refCol.Ref, colName, colType, alias);
var selection = refCol.OwnerSelect.Selection;
selection.Remove(refCol);
selection.Add(column);
}
public static IDbBinary ToBinary(this IDbObject dbElement, IDbObjectFactory dbFactory)
{
switch (dbElement)
{
case null:
return(null);
case IDbBinary dbBinary:
return(dbBinary);
}
var one = dbFactory.BuildConstant(true);
return(dbFactory.BuildBinary(dbElement, DbOperator.Equal, one));
}
public static IDbScript Translate(
Expression exp, IModelInfoProvider infoProvider, IDbObjectFactory dbFactory, out IncludeGraph includeGraph,
AbstractMethodTranslator[] addons = null)
{
includeGraph = IncludeGraphBuilder.Build(exp);
var script = TranslateGraph(includeGraph, infoProvider, dbFactory, new UniqueNameGenerator(), addons);
if (Logger.IsDebugEnabled)
{
Logger.Debug(Tuple.Create(exp, script.ToString()));
}
return(script);
}
public static IDbBinary ToBinary(this IDbObject dbElement, IDbObjectFactory dbFactory)
{
if (dbElement == null)
{
return(null);
}
var dbBinary = dbElement as IDbBinary;
if (dbBinary != null)
{
return(dbBinary);
}
var one = dbFactory.BuildConstant(true);
return(dbFactory.BuildBinary(dbElement, DbOperator.Equal, one));
}
private static void UpdateScript(IncludeNode node, IDbScript script, IDbObjectFactory dbFactory)
{
if (node.TempTable != null)
{
script.Scripts.Add(node.TempTable.GetCreateStatement(dbFactory));
}
script.Scripts.Add(node.Select);
foreach (var toNode in node.ToNodes)
{
UpdateScript(toNode, script, dbFactory);
}
if (node.TempTable != null)
{
script.Scripts.Add(node.TempTable.GetDropStatement(dbFactory));
}
}
public static IDbSelectable[] ProcessSelection(IDbObject dbObj, IDbObjectFactory factory)
{
switch (dbObj)
{
case IDbList <DbKeyValue> dbList:
var keyVals = dbList;
return(keyVals.SelectMany(kv => ProcessSelection(kv, factory)).ToArray());
case DbReference obj:
var dbRef = obj;
return(new IDbSelectable[] { factory.BuildRefColumn(dbRef, dbRef.RefColumnAlias) });
case IDbBinary dbBinary when(
dbBinary.Operator == DbOperator.Equal ||
dbBinary.Operator == DbOperator.NotEqual ||
dbBinary.Operator == DbOperator.GreaterThan ||
dbBinary.Operator == DbOperator.GreaterThanOrEqual ||
dbBinary.Operator == DbOperator.LessThan ||
dbBinary.Operator == DbOperator.LessThanOrEqual ||
dbBinary.Operator == DbOperator.Is ||
dbBinary.Operator == DbOperator.IsNot):
var dbTrue = factory.BuildConstant(true);
var tuple = Tuple.Create <IDbBinary, IDbObject>(dbBinary, dbTrue);
return(new IDbSelectable[] { factory.BuildCondition(new [] { tuple }, factory.BuildConstant(false)) });
}
if (!(dbObj is DbKeyValue keyValue))
{
return new[] { (IDbSelectable)dbObj }
}
;
var selectables = ProcessSelection(keyValue.Value, factory);
foreach (var selectable in selectables)
{
selectable.Alias = keyValue.Key;
}
return(selectables);
}
public ExpressionTranslator(
IModelInfoProvider infoProvider, IDbObjectFactory dbFactory,
TranslationState state = null, IEnumerable <AbstractMethodTranslator> methodTranslators = null)
{
_infoProvider = infoProvider;
_dbFactory = dbFactory;
_state = state ?? new TranslationState();
RegisterDefaultPlugIns();
if (methodTranslators == null)
{
return;
}
foreach (var translator in methodTranslators)
{
translator.Register(_plugIns);
}
}
private static IDbSelectable CreateNewSelectableForWrappingSelect(
IDbSelectable selectable, DbReference dbRef, IDbObjectFactory dbFactory)
{
if (dbRef == null)
{
return(selectable);
}
var oCol = selectable as IDbColumn;
if (oCol != null)
{
return(dbFactory.BuildColumn(dbRef, oCol.GetAliasOrName(), oCol.ValType));
}
var oRefCol = selectable as IDbRefColumn;
if (oRefCol != null)
{
return(dbFactory.BuildRefColumn(dbRef, oRefCol.Alias, oRefCol));
}
return(dbFactory.BuildColumn(dbRef, selectable.Alias, typeof(string)));
}
public static IDbBinary UpdateBinary(this IDbBinary whereClause, IDbBinary predicate, IDbObjectFactory dbFactory)
{
if (predicate == null)
{
return(whereClause);
}
return(whereClause != null
? dbFactory.BuildBinary(whereClause, DbOperator.And, predicate)
: predicate);
}
public static void UpdateWhereClause(this IDbSelect dbSelect, IDbBinary whereClause, IDbObjectFactory dbFactory)
{
if (whereClause == null)
{
return;
}
dbSelect.Where = dbSelect.Where.UpdateBinary(whereClause, dbFactory);
}
public ContainsTranslator(IModelInfoProvider infoProvider, IDbObjectFactory dbFactory)
: base(infoProvider, dbFactory)
{
}
public AbstractMethodTranslator(IModelInfoProvider infoProvider, IDbObjectFactory dbFactory)
{
_infoProvider = infoProvider;
_dbFactory = dbFactory;
}
public WhereTranslator(IModelInfoProvider infoProvider, IDbObjectFactory dbFactory)
: base(infoProvider, dbFactory)
{
}
/// <summary>
/// Insantiates a new instance of <see cref="OracleClient"/> using the passed in <paramref name="connectionString"/> and <paramref name="factory"/>
/// </summary>
/// <param name="connectionString">Connection string to use to query a database</param>
/// <param name="factory">An instance of <see cref="IDbObjectFactory"/></param>
public OracleClient(string connectionString, IDbObjectFactory factory) : base(connectionString, factory)
{
}
public StoredClassDataPresenter(IWindowManager windowManager, IBrowseFileService browseFileService, IDbObjectFactory dbObjectFactory)
{
this.windowManager = windowManager;
this.browseFileService = browseFileService;
this.dbObjectFactory = dbObjectFactory;
}
