Skip to content

Commit c4b5cc0

Browse files
ThatRendleThatRendle
committed
With working with reference and alias
1 parent 80dfc60 commit c4b5cc0

11 files changed

Lines changed: 172 additions & 65 deletions

File tree

‎Simple.Data.Ado/AdoAdapterQueryRunner.cs‎

Lines changed: 30 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -2,10 +2,10 @@
22
{
33
using System;
44
using System.Collections.Generic;
5-
using System.Linq;
65
using System.Data;
6+
using System.Linq;
77

8-
class AdoAdapterQueryRunner
8+
internal class AdoAdapterQueryRunner
99
{
1010
private readonly AdoAdapter _adapter;
1111

@@ -14,8 +14,9 @@ public AdoAdapterQueryRunner(AdoAdapter adapter)
1414
_adapter = adapter;
1515
}
1616

17-
public IEnumerable<IDictionary<string, object>> RunQuery(SimpleQuery query, out IEnumerable<SimpleQueryClauseBase>
18-
unhandledClauses)
17+
public IEnumerable<IDictionary<string, object>> RunQuery(SimpleQuery query,
18+
out IEnumerable<SimpleQueryClauseBase>
19+
unhandledClauses)
1920
{
2021
if (query.Clauses.OfType<WithCountClause>().Any()) return RunQueryWithCount(query, out unhandledClauses);
2122

@@ -25,17 +26,18 @@ public IEnumerable<IDictionary<string, object>> RunQuery(SimpleQuery query, out
2526
{
2627
return
2728
CommandBuilder.CreateCommand(
28-
_adapter.ProviderHelper.GetCustomProvider<IDbParameterFactory>(_adapter.SchemaProvider), commandBuilders,
29+
_adapter.ProviderHelper.GetCustomProvider<IDbParameterFactory>(_adapter.SchemaProvider),
30+
commandBuilders,
2931
connection).ToEnumerable(_adapter.CreateConnection);
3032
}
3133
return commandBuilders.SelectMany(cb => cb.GetCommand(connection).ToEnumerable(_adapter.CreateConnection));
3234
}
3335

3436
public IObservable<IDictionary<string, object>> RunQueryAsObservable(SimpleQuery query,
35-
out
36-
IEnumerable
37-
<SimpleQueryClauseBase>
38-
unhandledClauses)
37+
out
38+
IEnumerable
39+
<SimpleQueryClauseBase>
40+
unhandledClauses)
3941
{
4042
IDbConnection connection = _adapter.CreateConnection();
4143
return new QueryBuilder(_adapter).Build(query, out unhandledClauses)
@@ -69,15 +71,15 @@ out IEnumerable<SimpleQueryClauseBase>
6971

7072
using (
7173
IEnumerator<IEnumerable<IDictionary<string, object>>> enumerator =
72-
RunQueries(new[] { countQuery, query }, unhandledClausesList).GetEnumerator())
74+
RunQueries(new[] {countQuery, query}, unhandledClausesList).GetEnumerator())
7375
{
7476
unhandledClauses = unhandledClausesList[1];
7577
if (!enumerator.MoveNext())
7678
{
7779
throw new InvalidOperationException();
7880
}
7981
IDictionary<string, object> countRow = enumerator.Current.Single();
80-
withCountClause.SetCount((int)countRow.First().Value);
82+
withCountClause.SetCount((int) countRow.First().Value);
8183
if (!enumerator.MoveNext())
8284
{
8385
throw new InvalidOperationException();
@@ -101,8 +103,8 @@ private ICommandBuilder[] GetPagedQueryCommandBuilders(SimpleQuery query, Int32
101103

102104
IEnumerable<SimpleQueryClauseBase> unhandledClausesForPagedQuery;
103105
ICommandBuilder mainCommandBuilder = new QueryBuilder(_adapter, bulkIndex).Build(query,
104-
out
105-
unhandledClausesForPagedQuery);
106+
out
107+
unhandledClausesForPagedQuery);
106108
unhandledClausesList.AddRange(unhandledClausesForPagedQuery);
107109

108110
const int maxInt = 2147483646;
@@ -123,10 +125,13 @@ private ICommandBuilder[] GetPagedQueryCommandBuilders(SimpleQuery query, Int32
123125
}
124126
else
125127
{
126-
var commandTexts = queryPager.ApplyPaging(mainCommandBuilder.Text, skipClause.Count,
128+
IEnumerable<string> commandTexts = queryPager.ApplyPaging(mainCommandBuilder.Text, skipClause.Count,
127129
takeClause.Count);
128130

129-
commandBuilders.AddRange(commandTexts.Select(commandText => new CommandBuilder(commandText, _adapter.GetSchema(), mainCommandBuilder.Parameters)));
131+
commandBuilders.AddRange(
132+
commandTexts.Select(
133+
commandText =>
134+
new CommandBuilder(commandText, _adapter.GetSchema(), mainCommandBuilder.Parameters)));
130135
}
131136
}
132137
return commandBuilders.ToArray();
@@ -139,25 +144,26 @@ private ICommandBuilder[] GetQueryCommandBuilders(SimpleQuery query,
139144
{
140145
return GetPagedQueryCommandBuilders(query, out unhandledClauses);
141146
}
142-
return new[] { new QueryBuilder(_adapter).Build(query, out unhandledClauses) };
147+
return new[] {new QueryBuilder(_adapter).Build(query, out unhandledClauses)};
143148
}
144149

145150
private IEnumerable<ICommandBuilder> GetQueryCommandBuilders(SimpleQuery query, Int32 bulkIndex,
146-
out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
151+
out IEnumerable<SimpleQueryClauseBase>
152+
unhandledClauses)
147153
{
148154
if (query.Clauses.OfType<TakeClause>().Any() || query.Clauses.OfType<SkipClause>().Any())
149155
{
150156
return GetPagedQueryCommandBuilders(query, bulkIndex, out unhandledClauses);
151157
}
152-
return new[] { new QueryBuilder(_adapter, bulkIndex).Build(query, out unhandledClauses) };
158+
return new[] {new QueryBuilder(_adapter, bulkIndex).Build(query, out unhandledClauses)};
153159
}
154160

155161
public IEnumerable<IEnumerable<IDictionary<string, object>>> RunQueries(SimpleQuery[] queries,
156-
List
157-
<
158-
IEnumerable
159-
<SimpleQueryClauseBase>>
160-
unhandledClauses)
162+
List
163+
<
164+
IEnumerable
165+
<SimpleQueryClauseBase>>
166+
unhandledClauses)
161167
{
162168
if (_adapter.ProviderSupportsCompoundStatements && queries.Length > 1)
163169
{
@@ -189,4 +195,4 @@ public IEnumerable<IEnumerable<IDictionary<string, object>>> RunQueries(SimpleQu
189195
}
190196
}
191197
}
192-
}
198+
}

‎Simple.Data.Ado/JoinType.cs‎

Lines changed: 0 additions & 13 deletions
This file was deleted.

‎Simple.Data.Ado/Joiner.cs‎

Lines changed: 62 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -65,34 +65,50 @@ public IEnumerable<string> GetJoinClauses(IEnumerable<JoinClause> joins, IComman
6565
var expressionFormatter = new ExpressionFormatter(commandBuilder, _schema);
6666
foreach (var join in joins)
6767
{
68-
var builder = new StringBuilder(JoinKeyword);
68+
var builder = new StringBuilder(JoinTypeToKeyword(join.JoinType));
69+
var joinExpression = join.JoinExpression ?? InferJoinExpression(join.Table);
6970
builder.AppendFormat(" JOIN {0}{1} ON ({2})",
7071
_schema.FindTable(_schema.BuildObjectName(join.Table.ToString())).QualifiedName,
7172
string.IsNullOrWhiteSpace(join.Table.GetAlias()) ? string.Empty : " " + _schema.QuoteObjectName(join.Table.GetAlias()),
72-
expressionFormatter.Format(join.JoinExpression));
73+
expressionFormatter.Format(joinExpression));
7374
yield return builder.ToString().Trim();
7475
}
7576
}
7677

78+
private SimpleExpression InferJoinExpression(ObjectReference table)
79+
{
80+
var table1 = _schema.FindTable(table.GetOwner().GetName());
81+
var table2 = _schema.FindTable(table.GetName());
82+
var foreignKey = GetForeignKey(table1, table2);
83+
return MakeJoinExpression(table, foreignKey);
84+
}
85+
7786
private void AddJoin(ObjectName table1Name, ObjectName table2Name, JoinType joinType = JoinType.Inner)
7887
{
7988
_done.GetOrAdd(table2Name, _ =>
8089
{
8190
var table1 = _schema.FindTable(table1Name);
8291
var table2 = _schema.FindTable(table2Name);
83-
84-
var foreignKey =
85-
table2.ForeignKeys.SingleOrDefault(fk => fk.MasterTable.Schema == table1.Schema && fk.MasterTable.Name == table1.ActualName)
86-
??
87-
table1.ForeignKeys.SingleOrDefault(fk => fk.MasterTable.Schema == table2.Schema && fk.MasterTable.Name == table2.ActualName);
88-
89-
if (foreignKey == null) throw new SchemaResolutionException(
90-
string.Format("Could not join '{0}' and '{1}'", table1.ActualName, table2.ActualName));
91-
92+
var foreignKey = GetForeignKey(table1, table2);
9293
return MakeJoinText(table2, foreignKey, joinType);
9394
});
9495
}
9596

97+
private static ForeignKey GetForeignKey(Table table1, Table table2)
98+
{
99+
var foreignKey =
100+
table2.ForeignKeys.SingleOrDefault(
101+
fk => fk.MasterTable.Schema == table1.Schema && fk.MasterTable.Name == table1.ActualName)
102+
??
103+
table1.ForeignKeys.SingleOrDefault(
104+
fk => fk.MasterTable.Schema == table2.Schema && fk.MasterTable.Name == table2.ActualName);
105+
106+
if (foreignKey == null)
107+
throw new SchemaResolutionException(
108+
string.Format("Could not join '{0}' and '{1}'", table1.ActualName, table2.ActualName));
109+
return foreignKey;
110+
}
111+
96112
private string MakeJoinText(Table rightTable, ForeignKey foreignKey, JoinType joinType)
97113
{
98114
var builder = new StringBuilder(JoinKeywordFor(joinType));
@@ -108,11 +124,41 @@ private string MakeJoinText(Table rightTable, ForeignKey foreignKey, JoinType jo
108124
return builder.ToString();
109125
}
110126

127+
private SimpleExpression MakeJoinExpression(ObjectReference table, ForeignKey foreignKey)
128+
{
129+
var expression = CreateJoinExpression(table, foreignKey, 0);
130+
131+
for (int i = 1; i < foreignKey.Columns.Length; i++)
132+
{
133+
expression = expression && CreateJoinExpression(table, foreignKey, i);
134+
}
135+
136+
return expression;
137+
}
138+
111139
private string JoinKeywordFor(JoinType joinType)
112140
{
113141
return joinType == JoinType.Inner ? string.Empty : "LEFT";
114142
}
115143

144+
private SimpleExpression CreateJoinExpression(ObjectReference table, ForeignKey foreignKey, int columnIndex)
145+
{
146+
var masterObjectReference = ObjectReference.FromStrings(foreignKey.MasterTable.Name,
147+
foreignKey.UniqueColumns[columnIndex]);
148+
var detailObjectReference = ObjectReference.FromStrings(foreignKey.DetailTable.Name,
149+
foreignKey.Columns[columnIndex]);
150+
151+
if (!string.IsNullOrWhiteSpace(table.GetAlias()))
152+
{
153+
if (detailObjectReference.GetOwner().GetName() == table.GetName())
154+
detailObjectReference = new ObjectReference(detailObjectReference.GetName(), table);
155+
else if (masterObjectReference.GetOwner().GetName() == table.GetName())
156+
masterObjectReference = new ObjectReference(masterObjectReference.GetName(), table);
157+
}
158+
159+
return masterObjectReference == detailObjectReference;
160+
}
161+
116162
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex)
117163
{
118164
return string.Format("{0}.{1} = {2}.{3}", _schema.QuoteObjectName(foreignKey.MasterTable), _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
@@ -124,6 +170,11 @@ private string JoinKeyword
124170
get { return _joinType == JoinType.Inner ? string.Empty : "LEFT"; }
125171
}
126172

173+
private string JoinTypeToKeyword(JoinType joinType)
174+
{
175+
return joinType == JoinType.Inner ? string.Empty : "LEFT";
176+
}
177+
127178
private static IEnumerable<Tuple<ObjectName, ObjectName>> GetTableNames(IEnumerable<ObjectReference> references, string schema)
128179
{
129180
return references.SelectMany(r => DynamicReferenceToTuplePairs(r, schema))

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ public class QueryBuilder
1818
private SimpleQuery _query;
1919
private SimpleExpression _whereCriteria;
2020
private SimpleExpression _havingCriteria;
21-
private SimpleReference[] _columns;
21+
private IList<SimpleReference> _columns;
2222
private readonly CommandBuilder _commandBuilder;
2323
private List<SimpleQueryClauseBase> _unhandledClauses;
2424

@@ -75,12 +75,12 @@ private void SetQueryContext(SimpleQuery query)
7575
_columns =
7676
_columns.Concat(
7777
_schema.FindTable(withClause.ObjectReference.GetName()).Columns.Select(
78-
c => ObjectReference.FromStrings(_table.ActualName, withClause.ObjectReference.GetName(), c.ActualName))).ToArray();
78+
c => new ObjectReference(c.ActualName, withClause.ObjectReference))).ToArray();
7979
}
8080
}
8181
_columns =
8282
_columns.OfType<ObjectReference>().Select(
83-
c => c.As(string.Format("__with__{0}__{1}", c.GetOwner().GetName(), c.GetName()))).ToArray();
83+
c => c.As(string.Format("__with__{0}__{1}", c.GetOwner().GetAliasOrName(), c.GetName()))).ToArray();
8484
}
8585

8686
_whereCriteria = _query.Clauses.OfType<WhereClause>().Aggregate(SimpleExpression.Empty,
@@ -105,14 +105,15 @@ private void HandleJoins()
105105
? joiner.GetJoinClauses(_tableName, dottedTables.Split('.').Reverse())
106106
: Enumerable.Empty<string>();
107107

108-
var fromJoins = joiner.GetJoinClauses(_query.Clauses.OfType<JoinClause>(), _commandBuilder);
108+
var joinClauses = _query.Clauses.OfType<JoinClause>().ToArray();
109+
var fromJoins = joiner.GetJoinClauses(joinClauses, _commandBuilder);
109110

110111
var fromCriteria = joiner.GetJoinClauses(_tableName, _whereCriteria);
111112

112113
var fromHavingCriteria = joiner.GetJoinClauses(_tableName, _havingCriteria);
113114

114115
var fromColumnList = _columns.Any(r => !(r is SpecialReference))
115-
? joiner.GetJoinClauses(_tableName, GetObjectReferences(_columns), JoinType.Outer)
116+
? joiner.GetJoinClauses(_tableName, GetObjectReferences(_columns).Where(o => !joinClauses.Any(j => o.GetOwner().Equals(j.Table))), JoinType.Outer)
116117
: Enumerable.Empty<string>();
117118

118119
var joins = string.Join(" ", fromTable.Concat(fromJoins)
@@ -209,7 +210,7 @@ private string GetSelectClause(ObjectName tableName)
209210

210211
private string GetColumnsClause(Table table)
211212
{
212-
if (_columns != null && _columns.Length == 1 && _columns[0] is SpecialReference)
213+
if (_columns != null && _columns.Count == 1 && _columns[0] is SpecialReference)
213214
{
214215
return FormatSpecialReference((SpecialReference) _columns[0]);
215216
}
@@ -226,7 +227,7 @@ private static string FormatSpecialReference(SpecialReference reference)
226227

227228
private IEnumerable<SimpleReference> GetColumnsToSelect(Table table)
228229
{
229-
if (_columns != null && _columns.Length > 0)
230+
if (_columns != null && _columns.Count > 0)
230231
{
231232
return _columns;
232233
}

‎Simple.Data.Ado/Simple.Data.Ado.csproj‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -102,7 +102,6 @@
102102
<Compile Include="ISchemaGetter.cs" />
103103
<Compile Include="ISqlOptimizer.cs" />
104104
<Compile Include="Joiner.cs" />
105-
<Compile Include="JoinType.cs" />
106105
<Compile Include="OptimizedDictionary.cs" />
107106
<Compile Include="OptimizedDictionaryIndex.cs" />
108107
<Compile Include="OptimizedDictionary2.cs" />

0 commit comments

Comments
 (0)