Datei: QueryTests/QueryTests.cs

Last Commit (28c8c45)
1 using System;
2 using System.Collections.Generic;
3 using NUnit.Framework;
4 using NDO;
5 using NDO.Query;
6 using NDOql.Expressions;
7 using Reisekosten;
8 using Reisekosten.Personal;
9 using PureBusinessClasses;
10 using NDO.SqlPersistenceHandling;
11 using Moq;
12 using NDO.Configuration;
13 using NDO.Logging;
14 using NDO.Mapping;
15 using System.Collections;
16 using System.Data;
17 using System.Data.Common;
18 using DataTypeTestClasses;
19
20 namespace QueryTests
21 {
22 ····[TestFixture]
23 ····public class NDOQueryTests
24 ····{
25 ········PersistenceManager pm;
26 ········string mitarbeiterFields;
27 ········string mitarbeiterJoinFields;
28 ········string belegFields;
29 ········string pkwFahrtFields;
30 ········string reiseFields;
31 ········string reiseJoinFields;
32
33 ········[SetUp]
34 ········public void SetUp()
35 ········{
36 ············this.pm = NDOFactory.Instance.PersistenceManager;
37
38 ············mitarbeiterFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).SelectList;
39 ············mitarbeiterJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).Result( false, false, true );
40 ············belegFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Beleg ) ) ).SelectList;
41 ············this.pkwFahrtFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( PKWFahrt ) ) ).SelectList;
42 ············this.reiseFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).SelectList;
43 ············this.reiseJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).Result( false, false, true );
44 ············Mitarbeiter m = new Mitarbeiter() { Vorname = "Mirko", Nachname = "Matytschak" };
45 ············pm.MakePersistent( m );
46 ············m = new Mitarbeiter() { Vorname = "Hans", Nachname = "Huber" };
47 ············pm.MakePersistent( m );
48 ············pm.Save();
49 ········}
50
51 ········[TearDown]
52 ········public void TearDown()
53 ········{
54 ············var pm = NDOFactory.Instance.PersistenceManager;
55 ············pm.Delete( pm.Objects<Mitarbeiter>().ResultTable );
56 ············pm.Save();
57 ········}
58
59 ········[Test]
60 ········public void QueryWithEmptyGuidParameterSearchesForNull()
61 ········{
62 ············// The query will fetch for DataContainerDerived objects, too.
63 ············// Hence we test with "StartsWith", because the query contains additional text, which doesn't matter here.
64 ············var q = new NDOQuery<DataContainer>(pm, "guidVar = {0}");
65 ············q.Parameters.Add( Guid.Empty );
66 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList;
67 ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NULL";
68 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
69
70 ············q = new NDOQuery<DataContainer>(pm, "guidVar <> {0}");
71 ············q.Parameters.Add( Guid.Empty );
72 ············sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NOT NULL";
73 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
74 ········}
75
76 ········[Test]
77 ········public void QueryWithDateTimeMinValueParameterSearchesForNull()
78 ········{
79 ············// The query will fetch for DataContainerDerived objects, too.
80 ············// Hence we test with "StartsWith", because the query contains additional text, which doesn't matter here.
81 ············var q = new NDOQuery<DataContainer>(pm, "dateTimeVar = {0}");
82 ············q.Parameters.Add( DateTime.MinValue );
83 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList;
84 ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NULL";
85 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
86
87 ············q = new NDOQuery<DataContainer>(pm, "dateTimeVar <> {0}");
88 ············q.Parameters.Add( DateTime.MinValue );
89 ············sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NOT NULL";
90 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
91
92 ········}
93
94 ········[Test]
95 ········public void CheckIfQueryWithoutWhereClauseWorks()
96 ········{
97 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
98 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery );
99 ········}
100
101 ········[Test]
102 ········public void CheckIfSimplePrefetchWorks()
103 ········{
104 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
105 ············q.AddPrefetch( "dieReisen" );
106 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery );
107 ········}
108
109 ········public void CheckMitarbeiterQuery()
110 ········{
111 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
112 ············List<Mitarbeiter> l = q.Execute();
113 ············Assert.AreEqual( 2, l.Count );
114 ········}
115
116 ········[Test]
117 ········public void CheckIfSimpleWhereClauseWorks()
118 ········{
119 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'Mirko'" );
120 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", this.mitarbeiterFields ), q.GeneratedQuery );
121 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M*'" );
122 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE 'M*'", this.mitarbeiterFields ), q.GeneratedQuery );
123 ············q = new NDOQuery<Mitarbeiter>( pm, "oid = 1" );
124 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = 1", this.mitarbeiterFields ), q.GeneratedQuery );
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
125 ········}
126
127 ········[Test]
128 ········public void CheckIfGeneratedQueryCanBeCalledTwice()
129 ········{
130 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'Mirko'" );
131 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", this.mitarbeiterFields ), q.GeneratedQuery );
132 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", this.mitarbeiterFields ), q.GeneratedQuery );
133 ········}
134
135 ········[Test]
136 ········[TestCase( true )]
137 ········[TestCase( false )]
138 ········public void SkipTakeParametersDontChangeTheCoreQuery(bool asc)
139 ········{
140 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
141 ············q.Parameters.Add( "Mirko" );
142 ············if (asc)
143 ················q.Orderings.Add( new AscendingOrder( "vorname" ) );
144 ············else
145 ················q.Orderings.Add( new DescendingOrder( "vorname" ) );
146 ············q.Take = 10;
147 ············var desc = asc ? "" : "DESC ";
148 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
149 ············q.Skip = 10;
150 ············q.Take = 10;
151 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
152 ········}
153
154 ········[Test]
155 ········public void MixedOrderingsWork()
156 ········{
157 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
158 ············q.Parameters.Add( "Mirko" );
159 ············q.Orderings.Add( new AscendingOrder( "vorname" ) );
160 ············q.Orderings.Add( new DescendingOrder( "nachname" ) );
161 ············q.Take = 10;
162 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
163 ············q.Skip = 10;
164 ············q.Take = 10;
165 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
166 ········}
167
168 ········[Test]
169 ········public void OrderingByOidWorks()
170 ········{
171 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
172 ············q.Parameters.Add( "Mirko" );
173 ············q.Orderings.Add( new AscendingOrder( "oid" ) );
174 ············q.Take = 10;
175 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
176 ············q.Skip = 10;
177 ············q.Take = 10;
178 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
179
180 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
181 ············q.Parameters.Add( "Mirko" );
182 ············q.Orderings.Add( new DescendingOrder( "oid" ) );
183 ············q.Take = 10;
184 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
185 ············q.Skip = 10;
186 ············q.Take = 10;
187 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
188 ········}
189
190 ········[Test]
191 ········[TestCase( true )]
192 ········[TestCase( false )]
193 ········public void ParametersChangesDontChangeTheCoreQuery(bool asc)
194 ········{
195 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
196 ············if (asc)
197 ················q.Orderings.Add( new AscendingOrder( "vorname" ) );
198 ············else
199 ················q.Orderings.Add( new DescendingOrder( "vorname" ) );
200 ············q.Take = 10;
201 ············q.Parameters.Add( "Mirko" );
202 ············var desc = asc ? "" : "DESC ";
203 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
204 ············q.Parameters.Add( "Hans" );
205 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
206 ········}
207
208 ········[Test]
209 ········public void CheckIfWhereClauseWith1nRelationWorks()
210 ········{
211 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.zweck = 'ADC'" );
212 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = 'ADC'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
213 ············q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.oid = {0}" );
214 ············q.Parameters.Add( 1 );
215 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), q.GeneratedQuery );
216 ········}
217
218 ········[Test]
219 ········public void CheckIfWhereClauseWith11RelationWorks()
220 ········{
221 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse.lkz LIKE 'D%'" );
222 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE 'D%'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
223 ········}
224
225 ········[Test]
226 ········public void CheckIfMultipleRelationsWork()
227 ········{
228 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse.lkz LIKE 'D%' AND dieReisen.dieLaender.name = 'D'" );
229 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE [Adresse].[Lkz] LIKE 'D%' AND [Land].[Name] = 'D'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
230 ········}
231
232 ········[Test]
233 ········public void CheckOidWithTable()
234 ········{
235 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.dieLaender.oid = {0}" );
236 ············q.Parameters.Add( 1 );
237 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] = {{0}}", this.mitarbeiterJoinFields ), q.GeneratedQuery );
238 ········}
239
240 ········[Test]
241 ········public void CheckThatOneJoinAppearsOnlyOneTime()
242 ········{
243 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse.lkz LIKE 'D%' AND adresse.ort <> 'Bad Tölz'" );
244 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE 'D%' AND [Adresse].[Ort] <> 'Bad Tölz'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
245 ············q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.zweck = 'ADC' OR dieReisen.zweck = 'ADW'" );
246 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = 'ADC' OR [Reise].[Zweck] = 'ADW'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
247 ········}
248
249 ········[Test]
250 ········public void CheckNotOperator()
251 ········{
252 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "NOT (vorname LIKE 'M%')" );
253 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE 'M%')", this.mitarbeiterFields ), q.GeneratedQuery );
254 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M%' AND NOT nachname = 'Matytschak'" );
255 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE 'M%' AND NOT [Mitarbeiter].[Nachname] = 'Matytschak'", this.mitarbeiterFields ), q.GeneratedQuery );
256 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT (vorname LIKE 'M%' AND nachname = 'Matytschak')" );
257 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE 'M%' AND [Mitarbeiter].[Nachname] = 'Matytschak')", this.mitarbeiterFields ), q.GeneratedQuery );
258 ············q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.zweck = 'ADC' OR NOT dieReisen.zweck = 'ADW'" );
259 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = 'ADC' OR NOT [Reise].[Zweck] = 'ADW'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
260 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT (dieReisen.zweck = 'ADC' OR dieReisen.zweck = 'ADW')" );
261 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE NOT ([Reise].[Zweck] = 'ADC' OR [Reise].[Zweck] = 'ADW')", this.mitarbeiterJoinFields ), q.GeneratedQuery );
262 ············bool thrown = false;
263 ············try
264 ············{
265 ················q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M%' AND nachname = NOT 'Matytschak'" );
266 ················string s = q.GeneratedQuery;
267 ············}
268 ············catch (OqlExpressionException)
269 ············{
270 ················thrown = true;
271 ············}
272 ············Assert.AreEqual( true, thrown );
273
274 ············// TODO: This is a wrong expression which passes the syntax check.
275 ············// Mysql allows WHERE NOT True but disallows nachname = NOT True
276 ············// Sql Server doesn't know the symbol True
277 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M%' AND nachname = NOT True" );
278 ············string t = q.GeneratedQuery; // Make sure, GeneratedQuery ist called twice.
279 ············Console.WriteLine(t);
280 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE 'M%' AND [Mitarbeiter].[Nachname] = NOT TRUE", q.GeneratedQuery );
281 ········}
282
283 ········[Test]
284 ········public void CheckBetween()
285 ········{
286 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname BETWEEN 'A' AND 'B'" );
287 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] BETWEEN 'A' AND 'B'", q.GeneratedQuery );
288 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT vorname BETWEEN 'A' AND 'B'" );
289 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] BETWEEN 'A' AND 'B'", q.GeneratedQuery );
290 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT (vorname BETWEEN 'A' AND 'B')" );
291 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] BETWEEN 'A' AND 'B')", q.GeneratedQuery );
292 ········}
293
294 ········[Test]
295 ········public void TestValueType()
296 ········{
297 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "position.X > 2 AND position.Y < 5" );
298 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Position_X] > 2 AND [Mitarbeiter].[Position_Y] < 5", q.GeneratedQuery );
299 ········}
300
301 ········[Test]
302 ········public void TestValueTypeRelation()
303 ········{
304 ············NDOQuery<Sozialversicherungsnummer> q = new NDOQuery<Sozialversicherungsnummer>( pm, "arbeiter.position.X > 2 AND arbeiter.position.Y < 5" );
305 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true );
306 ············Assert.AreEqual( String.Format( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Position_X] > 2 AND [Mitarbeiter].[Position_Y] < 5", this.mitarbeiterFields ), q.GeneratedQuery );
307 ········}
308
309 ········[Test]
310 ········public void CheckFetchGroupInitializationWithExpressions()
311 ········{
312 ············FetchGroup<Mitarbeiter> fg = new FetchGroup<Mitarbeiter>( m => m.Vorname, m => m.Nachname );
313 ············Assert.AreEqual( fg.Count, 2, "Count should be 2" );
314 ············Assert.AreEqual( "Vorname", fg[0], "Name wrong #1" );
315 ············Assert.AreEqual( "Nachname", fg[1], "Name wrong #2" );
316 ········}
317
318 ········[Test]
319 ········public void CheckIfSyntaxErrorThrowsAQueryException()
320 ········{
321 ············bool qeThrown = false;
322 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN 'Mirko'" );
323 ············try
324 ············{
325 ················var s = q.GeneratedQuery;
326 ············}
327 ············catch (OqlExpressionException)
328 ············{
329 ················qeThrown = true;
330 ············}
331
332 ············Assert.That( qeThrown, "Syntax Error should throw an OqlExpressionException" );
333 ········}
334
335 ········[Test]
336 ········public void CheckIfMultiKeysWork()
337 ········{
338 ············NDOQuery<OrderDetail> q = new NDOQuery<OrderDetail>( pm, "oid = {0}" );
339 ············var od = pm.FindObject(typeof(OrderDetail), new object[]{ 1, 2 } );
340 ············q.Parameters.Add( od.NDOObjectId );
341 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( OrderDetail ) ) ).SelectList;
342 ············Assert.AreEqual( $"SELECT {fields} FROM [OrderDetail] WHERE [OrderDetail].[IDProduct] = {{0}} AND [OrderDetail].[IDOrder] = {{1}}", q.GeneratedQuery );
343 ············bool thrown = false;
344 ············try
345 ············{
346 ················q = new NDOQuery<OrderDetail>( pm, "oid = -4" );
347 ················string s = q.GeneratedQuery;
348 ············}
349 ············catch (NDOException)
350 ············{
351 ················thrown = true;
352 ············}
353 ············Assert.AreEqual( true, thrown );
354 ········}
355
356 ········[Test]
357 ········public void CheckIfMultiKeyObjectIdParametersAreProcessed()
358 ········{
359 ············IList generatedParameters = null;
360 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
361 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
362
363 ············pm.ConfigContainer.RegisterInstance<IPersistenceHandler>( handlerMock.Object );
364
365 ············NDOQuery<OrderDetail> q = new NDOQuery<OrderDetail>( pm, "oid = {0}" );
366 ············ObjectId oid = pm.FindObject( typeof( OrderDetail ), new object[] { 1, 2 } ).NDOObjectId;
367 ············q.Parameters.Add( oid );
368
369 ············q.Execute();
370
371 ············Assert.NotNull( generatedParameters );
372 ············Assert.AreEqual( 2, generatedParameters.Count );
373 ············Assert.AreEqual( 1, generatedParameters[0] );
374 ············Assert.AreEqual( 2, generatedParameters[1] );
375 ········}
376
377 ········[Test]
378 ········public void CheckIfMultiKeyArrayParametersAreProcessed()
379 ········{
380 ············NDOQuery<OrderDetail> q = new NDOQuery<OrderDetail>( pm, "oid = {0}" );
381 ············q.Parameters.Add( new object[] { 1, 2 } );
382
383 ············IList generatedParameters = null;
384 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
385 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
386 ············var container = pm.ConfigContainer;
387 ············container.RegisterInstance<IPersistenceHandler>( handlerMock.Object );
388 ············q.Execute();
389 ············Assert.NotNull( generatedParameters );
390 ············Assert.AreEqual( 2, generatedParameters.Count );
391 ············Assert.AreEqual( 1, generatedParameters[0] );
392 ············Assert.AreEqual( 2, generatedParameters[1] );
393 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
394 ········}
395
396
397 ········[Test]
398 ········public void CheckIfSingleKeyOidParameterIsProcessed()
399 ········{
400 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid = {0}" );
401 ············q.Parameters.Add( 1 );
402
403 ············IList generatedParameters = null;
404 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
405 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
406 ············var handler = handlerMock.Object;
407 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
408 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler ).Callback<Type>( ( pc ) => { Console.WriteLine("Test"); });
409 ············var container = pm.ConfigContainer;
410 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
411 ············q.Execute();
412 ············Assert.NotNull( generatedParameters );
413 ············Assert.AreEqual( 1, generatedParameters.Count );
414 ············Assert.AreEqual( 1, generatedParameters[0] );
415 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
416 ········}
417
418 ········[Test]
419 ········public void CheckIfSqlQueryIsProcessed()
420 ········{
421 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "SELECT * FROM Mitarbeiter WHERE ID = {0}", false, QueryLanguage.Sql );
422 ············q.Parameters.Add( 1 );
423
424 ············IList generatedParameters = null;
425 ············string expression = null;
426 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
427 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => { generatedParameters = l; expression = s; } );
428 ············var handler = handlerMock.Object;
429 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
430 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler );
431 ············var container = pm.ConfigContainer;
432 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
433 ············q.Execute();
434 ············Assert.NotNull( generatedParameters );
435 ············Assert.AreEqual( 1, generatedParameters.Count );
436 ············Assert.AreEqual( 1, generatedParameters[0] );
437 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM Mitarbeiter WHERE ID = {{0}}", expression );
438 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
439 ········}
440
441 ········[Test]
442 ········public void CheckIfSingleKeyNDOObjectIdParameterIsProcessed()
443 ········{
444 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid = {0}" );
445 ············var dummy = (IPersistenceCapable) pm.FindObject( typeof( Mitarbeiter ), 121 );
446 ············q.Parameters.Add( dummy.NDOObjectId );
447
448 ············IList generatedParameters = null;
449 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
450 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
451 ············var handler = handlerMock.Object;
452 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
453 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler ).Callback<Type>( ( pc ) => { Console.WriteLine( "Test" ); } );
454 ············var container = pm.ConfigContainer;
455 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
456 ············q.Execute();
457 ············Assert.NotNull( generatedParameters );
458 ············Assert.AreEqual( 1, generatedParameters.Count );
459 ············Assert.AreEqual( 121, generatedParameters[0] );
460 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
461 ········}
462
463 ········[Test]
464 ········public void SimpleQueryWithHandler()
465 ········{
466 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
467
468 ············IList generatedParameters = null;
469 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
470 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
471 ············var handler = handlerMock.Object;
472 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
473 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler ).Callback<Type>( ( pc ) => { Console.WriteLine( "Test" ); } );
474 ············var container = pm.ConfigContainer;
475 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
476 ············q.Execute();
477 ············Assert.NotNull( generatedParameters );
478 ············Assert.AreEqual( 0, generatedParameters.Count );
479 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
480 ········}
481
482 ········[Test]
483 ········public void TestSuperclasses()
484 ········{
485 ············NDOQuery<Kostenpunkt> qk = new NDOQuery<Kostenpunkt>( pm );
486 ············Assert.AreEqual( $"SELECT {this.belegFields} FROM [Beleg];\r\nSELECT {this.pkwFahrtFields} FROM [PKWFahrt]", qk.GeneratedQuery );
487 ········}
488
489 ········[Test]
490 ········public void TestPolymorphicRelationQueries()
491 ········{
492 ············NDOQuery<Reise> q = new NDOQuery<Reise>( pm, "belege.datum = {0}" );
493 ············q.Parameters.Add( DateTime.Now );
494 ············Assert.AreEqual( $"SELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [Beleg] ON [Beleg].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 926149172 WHERE [Beleg].[Datum] = {{0}} UNION \r\nSELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [PKWFahrt] ON [PKWFahrt].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 734406058 WHERE [PKWFahrt].[Datum] = {{0}}", q.GeneratedQuery );
495 ········}
496
497 ········[Test]
498 ········public void Test1To1()
499 ········{
500 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "meinBuero.zimmer = 'abc'" );
501 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Buero] ON [Buero].[ID] = [Mitarbeiter].[IDBuero] WHERE [Buero].[Zimmer] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
502 ········}
503
504
505 ········[Test]
506 ········public void Test1To1Bidirectional()
507 ········{
508 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "sn.nummer = 4711" );
509 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Sozialversicherungsnummer] ON [Sozialversicherungsnummer].[ID] = [Mitarbeiter].[IDSozial] WHERE [Sozialversicherungsnummer].[Nummer] = 4711", this.mitarbeiterJoinFields ), q.GeneratedQuery );
510 ············NDOQuery<Sozialversicherungsnummer> qs = new NDOQuery<Sozialversicherungsnummer>( pm, "arbeiter.vorname = 'Mirko'" );
511 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true );
512 ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", qs.GeneratedQuery );
513 ········}
514
515 ········[Test]
516 ········public void Test1To1BiWithTable()
517 ········{
518 ············NDOQuery<Zertifikat> qz = new NDOQuery<Zertifikat>( pm, "sgn.signature = 'abc'" );
519 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Zertifikat ) ) ).Result( false, false, true );
520 ············Assert.AreEqual( $"SELECT {fields} FROM [Zertifikat] INNER JOIN [relSignaturZertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] INNER JOIN [Signatur] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] WHERE [Signatur].[Signature] = 'abc'", qz.GeneratedQuery );
521 ············NDOQuery<Signatur> qs = new NDOQuery<Signatur>( pm, "owner.schlüssel = -4" );
522 ············string s = qs.GeneratedQuery;
523 ············fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Signatur ) ) ).Result( false, false, true );
524 ············Assert.AreEqual( $"SELECT {fields} FROM [Signatur] INNER JOIN [relSignaturZertifikat] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] INNER JOIN [Zertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] WHERE [Zertifikat].[Schlüssel] = -4", qs.GeneratedQuery );
525 ········}
526
527 ········[Test]
528 ········public void Test1ToNWithTable()
529 ········{
530 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "reiseBüros.name = 'abc'" );
531 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [relMitarbeiterReisebuero] ON [Mitarbeiter].[ID] = [relMitarbeiterReisebuero].[IDMitarbeiter] INNER JOIN [Reisebuero] ON [Reisebuero].[ID] = [relMitarbeiterReisebuero].[IDReisebuero] WHERE [Reisebuero].[Name] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
532 ········}
533
534 ········[Test]
535 ········public void TestIfQueryForNonNullOidsWorks()
536 ········{
537 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.dieLaender.oid IS NOT NULL" );
538 ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", q.GeneratedQuery );
539 ········}
540
541 ········[Test]
542 ········public void TestIfQueryWithNonNullRelationWorks()
543 ········{
544 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse IS NOT NULL" );
545 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", q.GeneratedQuery );
546 ········}
547
548 ········[Test]
549 ········public void TestIfQueryWithNullRelationWorks()
550 ········{
551 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse IS NULL" );
552 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NULL", q.GeneratedQuery );
553 ········}
554
555 ········[Test]
556 ········public void TestIfInClauseWorks()
557 ········{
558 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN (1,2,3,4,5)" );
559 ············var s = q.GeneratedQuery;
560 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN (1, 2, 3, 4, 5)", s );
561 ········}
562
563 ········[Test]
564 ········public void TestIfInClauseWithStringsWorks()
565 ········{
566 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN ('1','2','3','4','5')" );
567 ············var s = q.GeneratedQuery;
568 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('1', '2', '3', '4', '5')", s );
569 ········}
570
571 ········[Test]
572 ········public void TestIfRelationInInClauseWorks()
573 ········{
574 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.oid IN (1,2,3,4,5)" );
575 ············var s = q.GeneratedQuery;
576 ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", s );
577 ········}
578
579 ········[Test]
580 ········public void TestIfOidWithInClauseWorks()
581 ········{
582 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid IN (1,2,3,4,5)" );
583 ············var s = q.GeneratedQuery;
584 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", s );
585 ········}
586
587 ········[Test]
588 ········public void TestIfSqlCodeWorks()
589 ········{
590 ············var expression = "SELECT * FROM Mitarbeiter WHERE Vorname = 'Mirko'";
591 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( this.pm, expression, false, QueryLanguage.Sql );
592 ············Assert.AreEqual( expression, q.GeneratedQuery );
593 ········}
594
595 ········[Test]
596 ········public void GreaterEqualsWorks()
597 ········{
598 ············var query = new NDOQuery<Mitarbeiter>( NDOFactory.Instance.PersistenceManager, "vorname >= {0} AND vorname < {1}" );
599 ············query.Parameters.Add( "ab" );
600 ············query.Parameters.Add( "abc" );
601 ············query.Orderings.Add( new NDO.Query.DescendingOrder( "vorname" ) );
602 ············Assert.That(query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1);
603 ············Assert.That( query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 );
604 ········}
605 ····}
606 }
607
New Commit (b47b95e)
1 using System;
2 using System.Collections.Generic;
3 using NUnit.Framework;
4 using NDO;
5 using NDO.Query;
6 using NDOql.Expressions;
7 using Reisekosten;
8 using Reisekosten.Personal;
9 using PureBusinessClasses;
10 using NDO.SqlPersistenceHandling;
11 using Moq;
12 using NDO.Configuration;
13 using NDO.Logging;
14 using NDO.Mapping;
15 using System.Collections;
16 using System.Data;
17 using System.Data.Common;
18 using DataTypeTestClasses;
19
20 namespace QueryTests
21 {
22 ····[TestFixture]
23 ····public class NDOQueryTests
24 ····{
25 ········PersistenceManager pm;
26 ········string mitarbeiterFields;
27 ········string mitarbeiterJoinFields;
28 ········string belegFields;
29 ········string pkwFahrtFields;
30 ········string reiseFields;
31 ········string reiseJoinFields;
32
33 ········[SetUp]
34 ········public void SetUp()
35 ········{
36 ············this.pm = NDOFactory.Instance.PersistenceManager;
37
38 ············mitarbeiterFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).SelectList;
39 ············mitarbeiterJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).Result( false, false, true );
40 ············belegFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Beleg ) ) ).SelectList;
41 ············this.pkwFahrtFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( PKWFahrt ) ) ).SelectList;
42 ············this.reiseFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).SelectList;
43 ············this.reiseJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).Result( false, false, true );
44 ············Mitarbeiter m = new Mitarbeiter() { Vorname = "Mirko", Nachname = "Matytschak" };
45 ············pm.MakePersistent( m );
46 ············m = new Mitarbeiter() { Vorname = "Hans", Nachname = "Huber" };
47 ············pm.MakePersistent( m );
48 ············pm.Save();
49 ········}
50
51 ········[TearDown]
52 ········public void TearDown()
53 ········{
54 ············var pm = NDOFactory.Instance.PersistenceManager;
55 ············pm.Delete( pm.Objects<Mitarbeiter>().ResultTable );
56 ············pm.Save();
57 ········}
58
59 ········[Test]
60 ········public void QueryWithEmptyGuidParameterSearchesForNull()
61 ········{
62 ············// The query will fetch for DataContainerDerived objects, too.
63 ············// Hence we test with "StartsWith", because the query contains additional text, which doesn't matter here.
64 ············var q = new NDOQuery<DataContainer>(pm, "guidVar = {0}");
65 ············q.Parameters.Add( Guid.Empty );
66 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList;
67 ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NULL";
68 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
69
70 ············q = new NDOQuery<DataContainer>(pm, "guidVar <> {0}");
71 ············q.Parameters.Add( Guid.Empty );
72 ············sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NOT NULL";
73 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
74 ········}
75
76 ········[Test]
77 ········public void QueryWithDateTimeMinValueParameterSearchesForNull()
78 ········{
79 ············// The query will fetch for DataContainerDerived objects, too.
80 ············// Hence we test with "StartsWith", because the query contains additional text, which doesn't matter here.
81 ············var q = new NDOQuery<DataContainer>(pm, "dateTimeVar = {0}");
82 ············q.Parameters.Add( DateTime.MinValue );
83 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList;
84 ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NULL";
85 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
86
87 ············q = new NDOQuery<DataContainer>(pm, "dateTimeVar <> {0}");
88 ············q.Parameters.Add( DateTime.MinValue );
89 ············sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NOT NULL";
90 ············Assert.That( q.GeneratedQuery.StartsWith( sql ) );
91
92 ········}
93
94 ········[Test]
95 ········public void CheckIfQueryWithoutWhereClauseWorks()
96 ········{
97 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
98 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery );
99 ········}
100
101 ········[Test]
102 ········public void CheckIfSimplePrefetchWorks()
103 ········{
104 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
105 ············q.AddPrefetch( "dieReisen" );
106 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery );
107 ········}
108
109 ········public void CheckMitarbeiterQuery()
110 ········{
111 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
112 ············List<Mitarbeiter> l = q.Execute();
113 ············Assert.AreEqual( 2, l.Count );
114 ········}
115
116 ········[Test]
117 ········public void CheckIfSimpleWhereClauseWorks()
118 ········{
119 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'Mirko'" );
120 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", this.mitarbeiterFields ), q.GeneratedQuery );
121 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M*'" );
122 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE 'M*'", this.mitarbeiterFields ), q.GeneratedQuery );
123 ············q = new NDOQuery<Mitarbeiter>( pm, "oid = 1" );
124 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = 1", this.mitarbeiterFields ), q.GeneratedQuery );
125 ············q = new NDOQuery<Mitarbeiter>( pm, "oid(0) = 1" );
126 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = 1", this.mitarbeiterFields ), q.GeneratedQuery );
127 ········}
128
129 ········[Test]
130 ········public void CheckIfFunctionsWork()
131 ········{
132 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = SqlFunction('Mirko')" );
133 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = SqlFunction('Mirko')", this.mitarbeiterFields ), q.GeneratedQuery );
134 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname = SqlFunction('Mirko', 42)" );
135 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = SqlFunction('Mirko', 42)", this.mitarbeiterFields ), q.GeneratedQuery );
136 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname = SqlFunction()" );
137 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = SqlFunction()", this.mitarbeiterFields ), q.GeneratedQuery );
138 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname = SqlFunction(nachname)" );
139 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = SqlFunction([Mitarbeiter].[Nachname])", this.mitarbeiterFields ), q.GeneratedQuery );
140 ············q = new NDOQuery<Mitarbeiter>( pm, "SqlFunction('Mirko', 42) > 3124" );
141 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE SqlFunction('Mirko', 42) > 3124", this.mitarbeiterFields ), q.GeneratedQuery );
142 ········}
143
144 ········[Test]
145 ········public void CheckIfGeneratedQueryCanBeCalledTwice()
146 ········{
147 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'Mirko'" );
148 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", this.mitarbeiterFields ), q.GeneratedQuery );
149 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", this.mitarbeiterFields ), q.GeneratedQuery );
150 ········}
151
152 ········[Test]
153 ········[TestCase( true )]
154 ········[TestCase( false )]
155 ········public void SkipTakeParametersDontChangeTheCoreQuery(bool asc)
156 ········{
157 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
158 ············q.Parameters.Add( "Mirko" );
159 ············if (asc)
160 ················q.Orderings.Add( new AscendingOrder( "vorname" ) );
161 ············else
162 ················q.Orderings.Add( new DescendingOrder( "vorname" ) );
163 ············q.Take = 10;
164 ············var desc = asc ? "" : "DESC ";
165 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
166 ············q.Skip = 10;
167 ············q.Take = 10;
168 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
169 ········}
170
171 ········[Test]
172 ········public void MixedOrderingsWork()
173 ········{
174 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
175 ············q.Parameters.Add( "Mirko" );
176 ············q.Orderings.Add( new AscendingOrder( "vorname" ) );
177 ············q.Orderings.Add( new DescendingOrder( "nachname" ) );
178 ············q.Take = 10;
179 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
180 ············q.Skip = 10;
181 ············q.Take = 10;
182 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
183 ········}
184
185 ········[Test]
186 ········public void OrderingByOidWorks()
187 ········{
188 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
189 ············q.Parameters.Add( "Mirko" );
190 ············q.Orderings.Add( new AscendingOrder( "oid" ) );
191 ············q.Take = 10;
192 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
193 ············q.Skip = 10;
194 ············q.Take = 10;
195 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
196
197 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
198 ············q.Parameters.Add( "Mirko" );
199 ············q.Orderings.Add( new DescendingOrder( "oid" ) );
200 ············q.Take = 10;
201 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
202 ············q.Skip = 10;
203 ············q.Take = 10;
204 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[ID] DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
205 ········}
206
207 ········[Test]
208 ········[TestCase( true )]
209 ········[TestCase( false )]
210 ········public void ParametersChangesDontChangeTheCoreQuery(bool asc)
211 ········{
212 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = {0}" );
213 ············if (asc)
214 ················q.Orderings.Add( new AscendingOrder( "vorname" ) );
215 ············else
216 ················q.Orderings.Add( new DescendingOrder( "vorname" ) );
217 ············q.Take = 10;
218 ············q.Parameters.Add( "Mirko" );
219 ············var desc = asc ? "" : "DESC ";
220 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
221 ············q.Parameters.Add( "Hans" );
222 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} ORDER BY [Mitarbeiter].[Vorname] {desc}OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", q.GeneratedQuery );
223 ········}
224
225 ········[Test]
226 ········public void CheckIfWhereClauseWith1nRelationWorks()
227 ········{
228 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.zweck = 'ADC'" );
229 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = 'ADC'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
230 ············q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.oid = {0}" );
231 ············q.Parameters.Add( 1 );
232 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), q.GeneratedQuery );
233 ········}
234
235 ········[Test]
236 ········public void CheckIfWhereClauseWith11RelationWorks()
237 ········{
238 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse.lkz LIKE 'D%'" );
239 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE 'D%'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
240 ········}
241
242 ········[Test]
243 ········public void CheckIfMultipleRelationsWork()
244 ········{
245 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse.lkz LIKE 'D%' AND dieReisen.dieLaender.name = 'D'" );
246 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE [Adresse].[Lkz] LIKE 'D%' AND [Land].[Name] = 'D'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
247 ········}
248
249 ········[Test]
250 ········public void CheckOidWithTable()
251 ········{
252 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.dieLaender.oid = {0}" );
253 ············q.Parameters.Add( 1 );
254 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] = {{0}}", this.mitarbeiterJoinFields ), q.GeneratedQuery );
255 ········}
256
257 ········[Test]
258 ········public void CheckThatOneJoinAppearsOnlyOneTime()
259 ········{
260 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse.lkz LIKE 'D%' AND adresse.ort <> 'Bad Tölz'" );
261 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE 'D%' AND [Adresse].[Ort] <> 'Bad Tölz'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
262 ············q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.zweck = 'ADC' OR dieReisen.zweck = 'ADW'" );
263 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = 'ADC' OR [Reise].[Zweck] = 'ADW'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
264 ········}
265
266 ········[Test]
267 ········public void CheckNotOperator()
268 ········{
269 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "NOT (vorname LIKE 'M%')" );
270 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE 'M%')", this.mitarbeiterFields ), q.GeneratedQuery );
271 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M%' AND NOT nachname = 'Matytschak'" );
272 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE 'M%' AND NOT [Mitarbeiter].[Nachname] = 'Matytschak'", this.mitarbeiterFields ), q.GeneratedQuery );
273 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT (vorname LIKE 'M%' AND nachname = 'Matytschak')" );
274 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE 'M%' AND [Mitarbeiter].[Nachname] = 'Matytschak')", this.mitarbeiterFields ), q.GeneratedQuery );
275 ············q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.zweck = 'ADC' OR NOT dieReisen.zweck = 'ADW'" );
276 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = 'ADC' OR NOT [Reise].[Zweck] = 'ADW'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
277 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT (dieReisen.zweck = 'ADC' OR dieReisen.zweck = 'ADW')" );
278 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE NOT ([Reise].[Zweck] = 'ADC' OR [Reise].[Zweck] = 'ADW')", this.mitarbeiterJoinFields ), q.GeneratedQuery );
279 ············bool thrown = false;
280 ············try
281 ············{
282 ················q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M%' AND nachname = NOT 'Matytschak'" );
283 ················string s = q.GeneratedQuery;
284 ············}
285 ············catch (OqlExpressionException)
286 ············{
287 ················thrown = true;
288 ············}
289 ············Assert.AreEqual( true, thrown );
290
291 ············// TODO: This is a wrong expression which passes the syntax check.
292 ············// Mysql allows WHERE NOT True but disallows nachname = NOT True
293 ············// Sql Server doesn't know the symbol True
294 ············q = new NDOQuery<Mitarbeiter>( pm, "vorname LIKE 'M%' AND nachname = NOT True" );
295 ············string t = q.GeneratedQuery; // Make sure, GeneratedQuery ist called twice.
296 ············Console.WriteLine(t);
297 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE 'M%' AND [Mitarbeiter].[Nachname] = NOT TRUE", q.GeneratedQuery );
298 ········}
299
300 ········[Test]
301 ········public void CheckBetween()
302 ········{
303 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname BETWEEN 'A' AND 'B'" );
304 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] BETWEEN 'A' AND 'B'", q.GeneratedQuery );
305 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT vorname BETWEEN 'A' AND 'B'" );
306 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] BETWEEN 'A' AND 'B'", q.GeneratedQuery );
307 ············q = new NDOQuery<Mitarbeiter>( pm, "NOT (vorname BETWEEN 'A' AND 'B')" );
308 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] BETWEEN 'A' AND 'B')", q.GeneratedQuery );
309 ········}
310
311 ········[Test]
312 ········public void TestValueType()
313 ········{
314 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "position.X > 2 AND position.Y < 5" );
315 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Position_X] > 2 AND [Mitarbeiter].[Position_Y] < 5", q.GeneratedQuery );
316 ········}
317
318 ········[Test]
319 ········public void TestValueTypeRelation()
320 ········{
321 ············NDOQuery<Sozialversicherungsnummer> q = new NDOQuery<Sozialversicherungsnummer>( pm, "arbeiter.position.X > 2 AND arbeiter.position.Y < 5" );
322 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true );
323 ············Assert.AreEqual( String.Format( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Position_X] > 2 AND [Mitarbeiter].[Position_Y] < 5", this.mitarbeiterFields ), q.GeneratedQuery );
324 ········}
325
326 ········[Test]
327 ········public void CheckFetchGroupInitializationWithExpressions()
328 ········{
329 ············FetchGroup<Mitarbeiter> fg = new FetchGroup<Mitarbeiter>( m => m.Vorname, m => m.Nachname );
330 ············Assert.AreEqual( fg.Count, 2, "Count should be 2" );
331 ············Assert.AreEqual( "Vorname", fg[0], "Name wrong #1" );
332 ············Assert.AreEqual( "Nachname", fg[1], "Name wrong #2" );
333 ········}
334
335 ········[Test]
336 ········public void CheckIfSyntaxErrorThrowsAQueryException()
337 ········{
338 ············bool qeThrown = false;
339 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN 'Mirko'" );
340 ············try
341 ············{
342 ················var s = q.GeneratedQuery;
343 ············}
344 ············catch (OqlExpressionException)
345 ············{
346 ················qeThrown = true;
347 ············}
348
349 ············Assert.That( qeThrown, "Syntax Error should throw an OqlExpressionException" );
350 ········}
351
352 ········[Test]
353 ········public void CheckIfMultiKeysWork()
354 ········{
355 ············NDOQuery<OrderDetail> q = new NDOQuery<OrderDetail>( pm, "oid = {0}" );
356 ············var od = pm.FindObject(typeof(OrderDetail), new object[]{ 1, 2 } );
357 ············q.Parameters.Add( od.NDOObjectId );
358 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( OrderDetail ) ) ).SelectList;
359 ············Assert.AreEqual( $"SELECT {fields} FROM [OrderDetail] WHERE [OrderDetail].[IDProduct] = {{0}} AND [OrderDetail].[IDOrder] = {{1}}", q.GeneratedQuery );
360 ············bool thrown = false;
361 ············try
362 ············{
363 ················q = new NDOQuery<OrderDetail>( pm, "oid = -4" );
364 ················string s = q.GeneratedQuery;
365 ············}
366 ············catch (NDOException)
367 ············{
368 ················thrown = true;
369 ············}
370 ············Assert.AreEqual( true, thrown );
371 ········}
372
373 ········[Test]
374 ········public void CheckIfMultiKeyObjectIdParametersAreProcessed()
375 ········{
376 ············IList generatedParameters = null;
377 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
378 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
379
380 ············pm.ConfigContainer.RegisterInstance<IPersistenceHandler>( handlerMock.Object );
381
382 ············NDOQuery<OrderDetail> q = new NDOQuery<OrderDetail>( pm, "oid = {0}" );
383 ············ObjectId oid = pm.FindObject( typeof( OrderDetail ), new object[] { 1, 2 } ).NDOObjectId;
384 ············q.Parameters.Add( oid );
385
386 ············q.Execute();
387
388 ············Assert.NotNull( generatedParameters );
389 ············Assert.AreEqual( 2, generatedParameters.Count );
390 ············Assert.AreEqual( 1, generatedParameters[0] );
391 ············Assert.AreEqual( 2, generatedParameters[1] );
392 ········}
393
394 ········[Test]
395 ········public void CheckIfMultiKeyArrayParametersAreProcessed()
396 ········{
397 ············NDOQuery<OrderDetail> q = new NDOQuery<OrderDetail>( pm, "oid = {0}" );
398 ············q.Parameters.Add( new object[] { 1, 2 } );
399
400 ············IList generatedParameters = null;
401 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
402 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
403 ············var container = pm.ConfigContainer;
404 ············container.RegisterInstance<IPersistenceHandler>( handlerMock.Object );
405 ············q.Execute();
406 ············Assert.NotNull( generatedParameters );
407 ············Assert.AreEqual( 2, generatedParameters.Count );
408 ············Assert.AreEqual( 1, generatedParameters[0] );
409 ············Assert.AreEqual( 2, generatedParameters[1] );
410 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
411 ········}
412
413
414 ········[Test]
415 ········public void CheckIfSingleKeyOidParameterIsProcessed()
416 ········{
417 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid = {0}" );
418 ············q.Parameters.Add( 1 );
419
420 ············IList generatedParameters = null;
421 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
422 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
423 ············var handler = handlerMock.Object;
424 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
425 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler ).Callback<Type>( ( pc ) => { Console.WriteLine("Test"); });
426 ············var container = pm.ConfigContainer;
427 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
428 ············q.Execute();
429 ············Assert.NotNull( generatedParameters );
430 ············Assert.AreEqual( 1, generatedParameters.Count );
431 ············Assert.AreEqual( 1, generatedParameters[0] );
432 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
433 ········}
434
435 ········[Test]
436 ········public void CheckIfSqlQueryIsProcessed()
437 ········{
438 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "SELECT * FROM Mitarbeiter WHERE ID = {0}", false, QueryLanguage.Sql );
439 ············q.Parameters.Add( 1 );
440
441 ············IList generatedParameters = null;
442 ············string expression = null;
443 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
444 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => { generatedParameters = l; expression = s; } );
445 ············var handler = handlerMock.Object;
446 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
447 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler );
448 ············var container = pm.ConfigContainer;
449 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
450 ············q.Execute();
451 ············Assert.NotNull( generatedParameters );
452 ············Assert.AreEqual( 1, generatedParameters.Count );
453 ············Assert.AreEqual( 1, generatedParameters[0] );
454 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM Mitarbeiter WHERE ID = {{0}}", expression );
455 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
456 ········}
457
458 ········[Test]
459 ········public void CheckIfSingleKeyNDOObjectIdParameterIsProcessed()
460 ········{
461 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid = {0}" );
462 ············var dummy = (IPersistenceCapable) pm.FindObject( typeof( Mitarbeiter ), 121 );
463 ············q.Parameters.Add( dummy.NDOObjectId );
464
465 ············IList generatedParameters = null;
466 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
467 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
468 ············var handler = handlerMock.Object;
469 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
470 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler ).Callback<Type>( ( pc ) => { Console.WriteLine( "Test" ); } );
471 ············var container = pm.ConfigContainer;
472 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
473 ············q.Execute();
474 ············Assert.NotNull( generatedParameters );
475 ············Assert.AreEqual( 1, generatedParameters.Count );
476 ············Assert.AreEqual( 121, generatedParameters[0] );
477 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
478 ········}
479
480 ········[Test]
481 ········public void SimpleQueryWithHandler()
482 ········{
483 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm );
484
485 ············IList generatedParameters = null;
486 ············Mock<IPersistenceHandler> handlerMock = new Mock<IPersistenceHandler>();
487 ············handlerMock.Setup( h => h.PerformQuery( It.IsAny<string>(), It.IsAny<IList>(), It.IsAny<DataSet>() ) ).Returns( new DataTable() ).Callback<string, IList, DataSet>( ( s, l, d ) => generatedParameters = l );
488 ············var handler = handlerMock.Object;
489 ············Mock<IPersistenceHandlerManager> phManagerMock = new Mock<IPersistenceHandlerManager>();
490 ············phManagerMock.Setup( m => m.GetPersistenceHandler( It.IsAny<Type>() ) ).Returns( handler ).Callback<Type>( ( pc ) => { Console.WriteLine( "Test" ); } );
491 ············var container = pm.ConfigContainer;
492 ············container.RegisterInstance<IPersistenceHandlerManager>( phManagerMock.Object );
493 ············q.Execute();
494 ············Assert.NotNull( generatedParameters );
495 ············Assert.AreEqual( 0, generatedParameters.Count );
496 ············container.RegisterType<IPersistenceHandler, SqlPersistenceHandler>();
497 ········}
498
499 ········[Test]
500 ········public void TestSuperclasses()
501 ········{
502 ············NDOQuery<Kostenpunkt> qk = new NDOQuery<Kostenpunkt>( pm );
503 ············Assert.AreEqual( $"SELECT {this.belegFields} FROM [Beleg];\r\nSELECT {this.pkwFahrtFields} FROM [PKWFahrt]", qk.GeneratedQuery );
504 ········}
505
506 ········[Test]
507 ········public void TestPolymorphicRelationQueries()
508 ········{
509 ············NDOQuery<Reise> q = new NDOQuery<Reise>( pm, "belege.datum = {0}" );
510 ············q.Parameters.Add( DateTime.Now );
511 ············Assert.AreEqual( $"SELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [Beleg] ON [Beleg].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 926149172 WHERE [Beleg].[Datum] = {{0}} UNION \r\nSELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [PKWFahrt] ON [PKWFahrt].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 734406058 WHERE [PKWFahrt].[Datum] = {{0}}", q.GeneratedQuery );
512 ········}
513
514 ········[Test]
515 ········public void Test1To1()
516 ········{
517 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "meinBuero.zimmer = 'abc'" );
518 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Buero] ON [Buero].[ID] = [Mitarbeiter].[IDBuero] WHERE [Buero].[Zimmer] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
519 ········}
520
521
522 ········[Test]
523 ········public void Test1To1Bidirectional()
524 ········{
525 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "sn.nummer = 4711" );
526 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Sozialversicherungsnummer] ON [Sozialversicherungsnummer].[ID] = [Mitarbeiter].[IDSozial] WHERE [Sozialversicherungsnummer].[Nummer] = 4711", this.mitarbeiterJoinFields ), q.GeneratedQuery );
527 ············NDOQuery<Sozialversicherungsnummer> qs = new NDOQuery<Sozialversicherungsnummer>( pm, "arbeiter.vorname = 'Mirko'" );
528 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true );
529 ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", qs.GeneratedQuery );
530 ········}
531
532 ········[Test]
533 ········public void Test1To1BiWithTable()
534 ········{
535 ············NDOQuery<Zertifikat> qz = new NDOQuery<Zertifikat>( pm, "sgn.signature = 'abc'" );
536 ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Zertifikat ) ) ).Result( false, false, true );
537 ············Assert.AreEqual( $"SELECT {fields} FROM [Zertifikat] INNER JOIN [relSignaturZertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] INNER JOIN [Signatur] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] WHERE [Signatur].[Signature] = 'abc'", qz.GeneratedQuery );
538 ············NDOQuery<Signatur> qs = new NDOQuery<Signatur>( pm, "owner.schlüssel = -4" );
539 ············string s = qs.GeneratedQuery;
540 ············fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Signatur ) ) ).Result( false, false, true );
541 ············Assert.AreEqual( $"SELECT {fields} FROM [Signatur] INNER JOIN [relSignaturZertifikat] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] INNER JOIN [Zertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] WHERE [Zertifikat].[Schlüssel] = -4", qs.GeneratedQuery );
542 ········}
543
544 ········[Test]
545 ········public void Test1ToNWithTable()
546 ········{
547 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "reiseBüros.name = 'abc'" );
548 ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [relMitarbeiterReisebuero] ON [Mitarbeiter].[ID] = [relMitarbeiterReisebuero].[IDMitarbeiter] INNER JOIN [Reisebuero] ON [Reisebuero].[ID] = [relMitarbeiterReisebuero].[IDReisebuero] WHERE [Reisebuero].[Name] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery );
549 ········}
550
551 ········[Test]
552 ········public void TestIfQueryForNonNullOidsWorks()
553 ········{
554 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.dieLaender.oid IS NOT NULL" );
555 ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", q.GeneratedQuery );
556 ········}
557
558 ········[Test]
559 ········public void TestIfQueryWithNonNullRelationWorks()
560 ········{
561 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse IS NOT NULL" );
562 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", q.GeneratedQuery );
563 ········}
564
565 ········[Test]
566 ········public void TestIfQueryWithNullRelationWorks()
567 ········{
568 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "adresse IS NULL" );
569 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NULL", q.GeneratedQuery );
570 ········}
571
572 ········[Test]
573 ········public void TestIfInClauseWorks()
574 ········{
575 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN (1,2,3,4,5)" );
576 ············var s = q.GeneratedQuery;
577 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN (1, 2, 3, 4, 5)", s );
578 ········}
579
580 ········[Test]
581 ········public void TestIfInClauseWithStringsWorks()
582 ········{
583 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN ('1','2','3','4','5')" );
584 ············var s = q.GeneratedQuery;
585 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('1', '2', '3', '4', '5')", s );
586 ········}
587
588 ········[Test]
589 ········public void TestIfRelationInInClauseWorks()
590 ········{
591 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.oid IN (1,2,3,4,5)" );
592 ············var s = q.GeneratedQuery;
593 ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", s );
594 ········}
595
596 ········[Test]
597 ········public void TestIfOidWithInClauseWorks()
598 ········{
599 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid IN (1,2,3,4,5)" );
600 ············var s = q.GeneratedQuery;
601 ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", s );
602 ········}
603
604 ········[Test]
605 ········public void TestIfSqlCodeWorks()
606 ········{
607 ············var expression = "SELECT * FROM Mitarbeiter WHERE Vorname = 'Mirko'";
608 ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( this.pm, expression, false, QueryLanguage.Sql );
609 ············Assert.AreEqual( expression, q.GeneratedQuery );
610 ········}
611
612 ········[Test]
613 ········public void GreaterEqualsWorks()
614 ········{
615 ············var query = new NDOQuery<Mitarbeiter>( NDOFactory.Instance.PersistenceManager, "vorname >= {0} AND vorname < {1}" );
616 ············query.Parameters.Add( "ab" );
617 ············query.Parameters.Add( "abc" );
618 ············query.Orderings.Add( new NDO.Query.DescendingOrder( "vorname" ) );
619 ············Assert.That(query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1);
620 ············Assert.That( query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 );
621 ········}
622 ····}
623 }
624