PageRenderTime 23ms CodeModel.GetById 10ms app.highlight 10ms RepoModel.GetById 1ms app.codeStats 0ms

/PetaPoco.Tests/SqlBuilder.cs

http://github.com/toptensoftware/PetaPoco
C# | 277 lines | 231 code | 43 blank | 3 comment | 0 complexity | 18e57eff038b7159cca7bc06cce8af9a MD5 | raw file
  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using PetaTest;
  6using PetaPoco;
  7
  8namespace PetaPoco.Tests
  9{
 10	[TestFixture]
 11	public class SqlBuilder
 12	{
 13		public SqlBuilder()
 14		{
 15		}
 16
 17		[Test]
 18		public void simple_append()
 19		{
 20			var sql = new Sql();
 21			sql.Append("LINE 1");
 22			sql.Append("LINE 2");
 23			sql.Append("LINE 3");
 24
 25			Assert.AreEqual(sql.SQL, "LINE 1\nLINE 2\nLINE 3");
 26			Assert.AreEqual(sql.Arguments.Length, 0);
 27		}
 28
 29		[Test]
 30		public void single_arg()
 31		{
 32			var sql = new Sql();
 33			sql.Append("arg @0", "a1");
 34
 35			Assert.AreEqual(sql.SQL, "arg @0");
 36			Assert.AreEqual(sql.Arguments.Length, 1);
 37			Assert.AreEqual(sql.Arguments[0], "a1");
 38		}
 39
 40		[Test]
 41		public void multiple_args()
 42		{
 43			var sql = new Sql();
 44			sql.Append("arg @0 @1", "a1", "a2");
 45
 46			Assert.AreEqual(sql.SQL, "arg @0 @1");
 47			Assert.AreEqual(sql.Arguments.Length, 2);
 48			Assert.AreEqual(sql.Arguments[0], "a1");
 49			Assert.AreEqual(sql.Arguments[1], "a2");
 50		}
 51
 52		[Test]
 53		public void unused_args()
 54		{
 55			var sql = new Sql();
 56			sql.Append("arg @0 @2", "a1", "a2", "a3");
 57
 58			Assert.AreEqual(sql.SQL, "arg @0 @1");
 59			Assert.AreEqual(sql.Arguments.Length, 2);
 60			Assert.AreEqual(sql.Arguments[0], "a1");
 61			Assert.AreEqual(sql.Arguments[1], "a3");
 62		}
 63
 64		[Test]
 65		public void unordered_args()
 66		{
 67			var sql = new Sql();
 68			sql.Append("arg @2 @1", "a1", "a2", "a3");
 69
 70			Assert.AreEqual(sql.SQL, "arg @0 @1");
 71			Assert.AreEqual(sql.Arguments.Length, 2);
 72			Assert.AreEqual(sql.Arguments[0], "a3");
 73			Assert.AreEqual(sql.Arguments[1], "a2");
 74		}
 75
 76		[Test]
 77		public void repeated_args()
 78		{
 79			var sql = new Sql();
 80			sql.Append("arg @0 @1 @0 @1", "a1", "a2");
 81
 82			Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3");
 83			Assert.AreEqual(sql.Arguments.Length, 4);
 84			Assert.AreEqual(sql.Arguments[0], "a1");
 85			Assert.AreEqual(sql.Arguments[1], "a2");
 86			Assert.AreEqual(sql.Arguments[2], "a1");
 87			Assert.AreEqual(sql.Arguments[3], "a2");
 88		}
 89
 90		[Test]
 91		public void mysql_user_vars()
 92		{
 93			var sql = new Sql();
 94			sql.Append("arg @@user1 @2 @1 @@@system1", "a1", "a2", "a3");
 95
 96			Assert.AreEqual(sql.SQL, "arg @@user1 @0 @1 @@@system1");
 97			Assert.AreEqual(sql.Arguments.Length, 2);
 98			Assert.AreEqual(sql.Arguments[0], "a3");
 99			Assert.AreEqual(sql.Arguments[1], "a2");
100		}
101
102		[Test]
103		public void named_args()
104		{
105			var sql = new Sql();
106			sql.Append("arg @name @password", new { name = "n", password = "p" });
107
108			Assert.AreEqual(sql.SQL, "arg @0 @1");
109			Assert.AreEqual(sql.Arguments.Length, 2);
110			Assert.AreEqual(sql.Arguments[0], "n");
111			Assert.AreEqual(sql.Arguments[1], "p");
112		}
113
114
115
116		[Test]
117		public void mixed_named_and_numbered_args()
118		{
119			var sql = new Sql();
120			sql.Append("arg @0 @name @1 @password @2", "a1", "a2", "a3", new { name = "n", password = "p" });
121
122			Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3 @4");
123			Assert.AreEqual(sql.Arguments.Length, 5);
124			Assert.AreEqual(sql.Arguments[0], "a1");
125			Assert.AreEqual(sql.Arguments[1], "n");
126			Assert.AreEqual(sql.Arguments[2], "a2");
127			Assert.AreEqual(sql.Arguments[3], "p");
128			Assert.AreEqual(sql.Arguments[4], "a3");
129		}
130
131		[Test]
132		public void append_with_args()
133		{
134			var sql = new Sql();
135			sql.Append("l1 @0", "a0");
136			sql.Append("l2 @0", "a1");
137			sql.Append("l3 @0", "a2");
138
139			Assert.AreEqual(sql.SQL, "l1 @0\nl2 @1\nl3 @2");
140			Assert.AreEqual(sql.Arguments.Length, 3);
141			Assert.AreEqual(sql.Arguments[0], "a0");
142			Assert.AreEqual(sql.Arguments[1], "a1");
143			Assert.AreEqual(sql.Arguments[2], "a2");
144		}
145
146		[Test]
147		public void append_with_args2()
148		{
149			var sql = new Sql();
150			sql.Append("l1");
151			sql.Append("l2 @0 @1", "a1", "a2");
152			sql.Append("l3 @0", "a3");
153
154			Assert.AreEqual(sql.SQL, "l1\nl2 @0 @1\nl3 @2");
155			Assert.AreEqual(sql.Arguments.Length, 3);
156			Assert.AreEqual(sql.Arguments[0], "a1");
157			Assert.AreEqual(sql.Arguments[1], "a2");
158			Assert.AreEqual(sql.Arguments[2], "a3");
159		}
160
161		[Test]
162		public void invalid_arg_index()
163		{
164			Assert.Throws<ArgumentOutOfRangeException>(()=>{
165				var sql = new Sql();
166				sql.Append("arg @0 @1", "a0");
167				Assert.AreEqual(sql.SQL, "arg @0 @1");
168			});
169		}
170
171		[Test]
172		public void invalid_arg_name()
173		{
174			Assert.Throws<ArgumentException>(() =>
175			{
176				var sql = new Sql();
177				sql.Append("arg @name1 @name2", new { x = 1, y = 2 });
178				Assert.AreEqual(sql.SQL, "arg @0 @1");
179			});
180		}
181
182		[Test]
183		public void append_instances()
184		{
185			var sql = new Sql("l0 @0", "a0");
186			var sql1 = new Sql("l1 @0", "a1");
187			var sql2 = new Sql("l2 @0", "a2");
188
189			Assert.AreSame(sql.Append(sql1), sql);
190			Assert.AreSame(sql.Append(sql2), sql);
191
192			Assert.AreEqual(sql.SQL, "l0 @0\nl1 @1\nl2 @2");
193			Assert.AreEqual(sql.Arguments.Length, 3);
194			Assert.AreEqual(sql.Arguments[0], "a0");
195			Assert.AreEqual(sql.Arguments[1], "a1");
196			Assert.AreEqual(sql.Arguments[2], "a2");
197		}
198
199		[Test]
200		public void ConsecutiveWhere()
201		{
202			var sql = new Sql()
203						.Append("SELECT * FROM blah");
204
205			sql.Append("WHERE x");
206			sql.Append("WHERE y");
207
208			Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nWHERE x\nAND y");
209		}
210
211		[Test]
212		public void ConsecutiveOrderBy()
213		{
214			var sql = new Sql()
215						.Append("SELECT * FROM blah");
216
217			sql.Append("ORDER BY x");
218			sql.Append("ORDER BY y");
219
220			Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nORDER BY x\n, y");
221		}
222
223		[Test]
224		public void param_expansion_1()
225		{
226			// Simple collection parameter expansion
227			var sql = Sql.Builder.Append("@0 IN (@1) @2", 20, new int[] { 1, 2, 3 }, 30);
228			Assert.AreEqual(sql.SQL, "@0 IN (@1,@2,@3) @4");
229			Assert.AreEqual(sql.Arguments.Length, 5);
230			Assert.AreEqual(sql.Arguments[0], 20);
231			Assert.AreEqual(sql.Arguments[1], 1);
232			Assert.AreEqual(sql.Arguments[2], 2);
233			Assert.AreEqual(sql.Arguments[3], 3);
234			Assert.AreEqual(sql.Arguments[4], 30);
235		}
236
237		[Test]
238		public void param_expansion_2()
239		{
240			// Out of order expansion
241			var sql = Sql.Builder.Append("IN (@3) (@1)", null, new int[] { 1, 2, 3 }, null, new int[] { 4, 5, 6 });
242			Assert.AreEqual(sql.SQL, "IN (@0,@1,@2) (@3,@4,@5)");
243			Assert.AreEqual(sql.Arguments.Length, 6);
244			Assert.AreEqual(sql.Arguments[0], 4);
245			Assert.AreEqual(sql.Arguments[1], 5);
246			Assert.AreEqual(sql.Arguments[2], 6);
247			Assert.AreEqual(sql.Arguments[3], 1);
248			Assert.AreEqual(sql.Arguments[4], 2);
249			Assert.AreEqual(sql.Arguments[5], 3);
250		}
251
252		[Test]
253		public void param_expansion_named()
254		{
255			// Expand a named parameter
256			var sql = Sql.Builder.Append("IN (@numbers)", new { numbers = (new int[] { 1, 2, 3 }) } );
257			Assert.AreEqual(sql.SQL, "IN (@0,@1,@2)");
258			Assert.AreEqual(sql.Arguments.Length, 3);
259			Assert.AreEqual(sql.Arguments[0], 1);
260			Assert.AreEqual(sql.Arguments[1], 2);
261			Assert.AreEqual(sql.Arguments[2], 3);
262		}
263
264		[Test]
265		public void join()
266		{
267			var sql = Sql.Builder
268				.Select("*")
269				.From("articles")
270				.LeftJoin("comments").On("articles.article_id=comments.article_id");
271			Assert.AreEqual(sql.SQL, "SELECT *\nFROM articles\nLEFT JOIN comments\nON articles.article_id=comments.article_id");
272		}
273
274	}
275
276}
277