Datei: QueryTests/QueryTests.cs

Last Commit (16046d5)
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
New Commit (43dcc14)
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 ········[Test]
624 ········public void DirectDeleteQueryIsCorrect()
625 ········{
626 ············var q = new NDOQuery<Mitarbeiter>( NDOFactory.Instance.PersistenceManager, "vorname = 'Mirko'" );
627 ············var sql = q.GetDirectDeleteQuery();
628 ············Assert.AreEqual( "DELETE FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", sql );
629 ········}
630 ····}
631 }
632