/PetaPoco.Tests/SqlBuilder.cs
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