Datei: QueryTests/QueryTests.cs
Last Commit (397e4c7)
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 TestIfSingleQuotesAreForbidden() |
590 | ········{ |
591 | ············// -- will be interpreted as NDOql comment |
592 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'lala'--SELECT * FROM Mitarbeiter'" ); |
593 | ············var s = q.GeneratedQuery; |
594 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'lala'", s ); |
595 | |
596 | ············// -SELECT * FROM is a valid expression, it would result in a sql error |
597 | ············// Mitarbeiter is a syntax error, so the expression is recognized as incorrect from the 33rd character onwards |
598 | ············q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'lala' -SELECT * FROM Mitarbeiter'" ); |
599 | ············try |
600 | ············{ |
601 | ················s = q.GeneratedQuery; |
602 | ············} |
603 | ············catch (OqlExpressionException) |
604 | ············{ |
605 | ················// Expected outcome |
606 | ············} |
607 | ········} |
608 | |
609 | ········[Test] |
610 | ········public void TestIfInClauseWithQuotesInStringsWorks() |
611 | ········{ |
612 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN ('1''','2''3','''3','4','5')" ); |
613 | ············var s = q.GeneratedQuery; |
614 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('1''', '2''3', '''3', '4', '5')", s ); |
615 | ········} |
616 | |
617 | ········[Test] |
618 | ········public void TestIfRelationInInClauseWorks() |
619 | ········{ |
620 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.oid IN (1,2,3,4,5)" ); |
621 | ············var s = q.GeneratedQuery; |
622 | ············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 ); |
623 | ········} |
624 | |
625 | ········[Test] |
626 | ········public void TestIfOidWithInClauseWorks() |
627 | ········{ |
628 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid IN (1,2,3,4,5)" ); |
629 | ············var s = q.GeneratedQuery; |
630 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", s ); |
631 | ········} |
632 | |
633 | ········[Test] |
634 | ········public void TestIfSqlCodeWorks() |
635 | ········{ |
636 | ············var expression = "SELECT * FROM Mitarbeiter WHERE Vorname = 'Mirko'"; |
637 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( this.pm, expression, false, QueryLanguage.Sql ); |
638 | ············Assert.AreEqual( expression, q.GeneratedQuery ); |
639 | ········} |
640 | |
641 | ········[Test] |
642 | ········public void GreaterEqualsWorks() |
643 | ········{ |
644 | ············var query = new NDOQuery<Mitarbeiter>( NDOFactory.Instance.PersistenceManager, "vorname >= {0} AND vorname < {1}" ); |
645 | ············query.Parameters.Add( "ab" ); |
646 | ············query.Parameters.Add( "abc" ); |
647 | ············query.Orderings.Add( new NDO.Query.DescendingOrder( "vorname" ) ); |
648 | ············Assert.That(query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1); |
649 | ············Assert.That( query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 ); |
650 | ········} |
651 | |
652 | ········[Test] |
653 | ········public void DirectDeleteQueryIsCorrect() |
654 | ········{ |
655 | ············var q = new NDOQuery<Mitarbeiter>( NDOFactory.Instance.PersistenceManager, "vorname = 'Mirko'" ); |
656 | ············var sql = q.GetDirectDeleteQuery(); |
657 | ············Assert.AreEqual( "DELETE FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", sql ); |
658 | ········} |
659 | ····} |
660 | } |
661 |
New Commit (a366466)
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 TestIfSingleQuotesAreForbidden() |
590 | ········{ |
591 | ············// -- will be interpreted as NDOql comment |
592 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'lala'--SELECT * FROM Mitarbeiter'" ); |
593 | ············var s = q.GeneratedQuery; |
594 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'lala'", s ); |
595 | |
596 | ············// -SELECT * FROM is a valid expression, it would result in a sql error |
597 | ············// Mitarbeiter is a syntax error, so the expression is recognized as incorrect from the 33rd character onwards |
598 | ············q = new NDOQuery<Mitarbeiter>( pm, "vorname = 'lala' -SELECT * FROM Mitarbeiter'" ); |
599 | ············try |
600 | ············{ |
601 | ················s = q.GeneratedQuery; |
602 | ············} |
603 | ············catch (OqlExpressionException) |
604 | ············{ |
605 | ················// Expected outcome |
606 | ············} |
607 | ········} |
608 | |
609 | ········[Test] |
610 | ········public void TestIfInClauseWithQuotesInStringsWorks() |
611 | ········{ |
612 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "vorname IN ('1''','2''3','''3','4','5')" ); |
613 | ············var s = q.GeneratedQuery; |
614 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('1''', '2''3', '''3', '4', '5')", s ); |
615 | ········} |
616 | |
617 | ········[Test] |
618 | ········public void TestIfRelationInInClauseWorks() |
619 | ········{ |
620 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "dieReisen.oid IN (1,2,3,4,5)" ); |
621 | ············var s = q.GeneratedQuery; |
622 | ············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 ); |
623 | ········} |
624 | |
625 | ········[Test] |
626 | ········public void TestIfOidWithInClauseWorks() |
627 | ········{ |
628 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "oid IN (1,2,3,4,5)" ); |
629 | ············var s = q.GeneratedQuery; |
630 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", s ); |
631 | ········} |
632 | |
633 | ········[Test] |
634 | ········public void TestIfSqlCodeWorks() |
635 | ········{ |
636 | ············var expression = "SELECT * FROM Mitarbeiter WHERE Vorname = 'Mirko'"; |
637 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( this.pm, expression, false, QueryLanguage.Sql ); |
638 | ············Assert.AreEqual( expression, q.GeneratedQuery ); |
639 | ········} |
640 | |
641 | ········[Test] |
642 | ········public void GreaterEqualsWorks() |
643 | ········{ |
644 | ············var query = new NDOQuery<Mitarbeiter>( NDOFactory.Instance.PersistenceManager, "vorname >= {0} AND vorname < {1}" ); |
645 | ············query.Parameters.Add( "ab" ); |
646 | ············query.Parameters.Add( "abc" ); |
647 | ············query.Orderings.Add( new NDO.Query.DescendingOrder( "vorname" ) ); |
648 | ············Assert.That(query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1); |
649 | ············Assert.That( query.GeneratedQuery.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 ); |
650 | ········} |
651 | |
652 | ········[Test] |
653 | ········public void BitwiseOperatorsWork() |
654 | ········{ |
655 | ············var pm = NDOFactory.Instance.PersistenceManager; |
656 | ············var query = new NDOQuery<Buero>( pm, "Nummer & 2 = 0" ); |
657 | ············Assert.That( query.GeneratedQuery.IndexOf( "[Buero].[Nummer] & 2 = 0" ) > -1 ); |
658 | ············query = new NDOQuery<Buero>( pm, "Nummer | 2 = 0" ); |
659 | ············Assert.That( query.GeneratedQuery.IndexOf( "[Buero].[Nummer] | 2 = 0" ) > -1 ); |
660 | ········} |
661 | |
662 | ········[Test] |
663 | ········public void DirectDeleteQueryIsCorrect() |
664 | ········{ |
665 | ············var q = new NDOQuery<Mitarbeiter>( NDOFactory.Instance.PersistenceManager, "vorname = 'Mirko'" ); |
666 | ············var sql = q.GetDirectDeleteQuery(); |
667 | ············Assert.AreEqual( "DELETE FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = 'Mirko'", sql ); |
668 | ········} |
669 | ····} |
670 | } |
671 |