PageRenderTime 52ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 0ms

/mcs/tools/sqlmetal/src/DbLinq/Test/Providers/ReadTests_DateTimeFunctions.cs

http://github.com/mono/mono
C# | 510 lines | 384 code | 99 blank | 27 comment | 46 complexity | 95c66f00407ae59e58c79b238454a18d MD5 | raw file
Possible License(s): GPL-2.0, CC-BY-SA-3.0, LGPL-2.0, MPL-2.0-no-copyleft-exception, LGPL-2.1, Unlicense, Apache-2.0
  1. #region MIT license
  2. //
  3. // MIT license
  4. //
  5. // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
  6. //
  7. // Permission is hereby granted, free of charge, to any person obtaining a copy
  8. // of this software and associated documentation files (the "Software"), to deal
  9. // in the Software without restriction, including without limitation the rights
  10. // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  11. // copies of the Software, and to permit persons to whom the Software is
  12. // furnished to do so, subject to the following conditions:
  13. //
  14. // The above copyright notice and this permission notice shall be included in
  15. // all copies or substantial portions of the Software.
  16. //
  17. // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  18. // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  19. // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  20. // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  21. // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  22. // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  23. // THE SOFTWARE.
  24. //
  25. #endregion
  26. using System;
  27. using System.Globalization;
  28. using System.Collections.Generic;
  29. using System.Text;
  30. using System.Linq;
  31. using System.Linq.Expressions;
  32. using NUnit.Framework;
  33. using Test_NUnit;
  34. using nwind;
  35. #if MONO_STRICT
  36. using System.Data.Linq;
  37. #else
  38. using DbLinq.Data.Linq;
  39. #endif
  40. // test ns
  41. #if MYSQL
  42. namespace Test_NUnit_MySql
  43. #elif ORACLE && ODP
  44. namespace Test_NUnit_OracleODP
  45. #elif ORACLE
  46. namespace Test_NUnit_Oracle
  47. #elif POSTGRES
  48. namespace Test_NUnit_PostgreSql
  49. #elif SQLITE
  50. namespace Test_NUnit_Sqlite
  51. #elif INGRES
  52. namespace Test_NUnit_Ingres
  53. #elif MSSQL && L2SQL
  54. namespace Test_NUnit_MsSql_Strict
  55. #elif MSSQL
  56. namespace Test_NUnit_MsSql
  57. #elif FIREBIRD
  58. namespace Test_NUnit_Firebird
  59. #endif
  60. {
  61. [TestFixture]
  62. public class ReadTests_DateTimeFunctions : TestBase
  63. {
  64. #if !DEBUG && SQLITE
  65. [Explicit]
  66. #endif
  67. [Test]
  68. public void GetYear()
  69. {
  70. Northwind db = CreateDB();
  71. var q = from o in db.Orders
  72. where o.OrderDate.Value.Year == 1996
  73. select o;
  74. var list = q.ToList();
  75. Assert.IsTrue(list.Count > 0);
  76. }
  77. #if !DEBUG && SQLITE
  78. [Explicit]
  79. #endif
  80. [Test]
  81. public void GetMonth()
  82. {
  83. Northwind db = CreateDB();
  84. var q = from o in db.Orders
  85. where o.OrderDate.Value.Month == 10
  86. select o;
  87. var list = q.ToList();
  88. Assert.IsTrue(list.Count > 0);
  89. }
  90. #if !DEBUG && SQLITE
  91. [Explicit]
  92. #endif
  93. [Test]
  94. public void GetDay()
  95. {
  96. Northwind db = CreateDB();
  97. var q = from o in db.Orders
  98. where o.OrderDate.Value.Day == 16
  99. select o;
  100. var list = q.ToList();
  101. Assert.IsTrue(list.Count > 0);
  102. }
  103. #if !DEBUG && SQLITE
  104. [Explicit]
  105. #endif
  106. [Test]
  107. public void GetHours()
  108. {
  109. Northwind db = CreateDB();
  110. var q = (from o in db.Orders
  111. where o.OrderDate.Value.Hour == 0
  112. select o).ToList();
  113. }
  114. #if !DEBUG && SQLITE
  115. [Explicit]
  116. #endif
  117. [Test]
  118. public void GetMinutes()
  119. {
  120. Northwind db = CreateDB();
  121. var q = (from o in db.Orders
  122. where o.OrderDate.Value.Minute == 0
  123. select o).ToList();
  124. }
  125. #if !DEBUG && SQLITE
  126. [Explicit]
  127. #endif
  128. [Test]
  129. public void GetSeconds()
  130. {
  131. Northwind db = CreateDB();
  132. var q = (from o in db.Orders
  133. where o.OrderDate.Value.Second == 16
  134. select o).ToList();
  135. }
  136. #if !DEBUG && SQLITE
  137. [Explicit]
  138. #endif
  139. [Test]
  140. public void GetMilliSeconds()
  141. {
  142. Northwind db = CreateDB();
  143. var q = (from o in db.Orders
  144. where o.OrderDate.Value.Millisecond == 0
  145. select o).ToList();
  146. }
  147. #if !DEBUG && SQLITE
  148. [Explicit]
  149. #endif
  150. [Test]
  151. public void GetCurrentDateTime()
  152. {
  153. Northwind db = CreateDB();
  154. var query = from e in db.Employees
  155. where e.BirthDate.HasValue && e.BirthDate.Value == DateTime.Now
  156. select e;
  157. var list = query.ToList();
  158. }
  159. #if !DEBUG && SQLITE
  160. [Explicit]
  161. #endif
  162. [Test]
  163. public void Parse01()
  164. {
  165. Northwind db = CreateDB();
  166. var query = from e in db.Employees
  167. where e.BirthDate.Value == DateTime.Parse("1984/05/02")
  168. select e;
  169. var list = query.ToList();
  170. }
  171. [Test]
  172. [ExpectedException(typeof(NotSupportedException))]
  173. public void Parse02()
  174. {
  175. Northwind db = CreateDB();
  176. var query = from e in db.Employees
  177. where e.BirthDate.Value == DateTime.Parse(e.BirthDate.ToString())
  178. select e;
  179. var list = query.ToList();
  180. }
  181. [Test]
  182. public void Parse03()
  183. {
  184. Northwind db = CreateDB();
  185. var query = from e in db.Employees
  186. where e.BirthDate.HasValue
  187. select e.BirthDate.Value == DateTime.Parse("1984/05/02");
  188. var list = query.ToList();
  189. }
  190. [Test]
  191. [ExpectedException(typeof(InvalidOperationException))]
  192. public void Parse04()
  193. {
  194. Northwind db = CreateDB();
  195. var query = from e in db.Employees
  196. select e.BirthDate.Value == DateTime.Parse(e.BirthDate.ToString());
  197. var list = query.ToList();
  198. }
  199. #if !DEBUG && SQLITE
  200. [Explicit]
  201. #endif
  202. [Test]
  203. public void DateTimeDiffTotalHours()
  204. {
  205. Northwind db = CreateDB();
  206. var query = from e in db.Employees
  207. where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalHours > 0
  208. select e;
  209. var list = query.ToList();
  210. }
  211. #if !DEBUG && SQLITE
  212. [Explicit]
  213. #endif
  214. [Test]
  215. public void DateTimeDiffHours()
  216. {
  217. Northwind db = CreateDB();
  218. DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddHours(2);
  219. var query = from e in db.Employees
  220. where (e.BirthDate.Value - parameterDateTime).Hours > -2
  221. select e;
  222. var list = query.ToList();
  223. AssertHelper.Greater(list.Count, 0);
  224. }
  225. #if !DEBUG && SQLITE
  226. [Explicit]
  227. #endif
  228. [Test]
  229. public void DateTimeDiffTotalMinutes()
  230. {
  231. Northwind db = CreateDB();
  232. var query = from e in db.Employees
  233. where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalMinutes > 0
  234. select e;
  235. var list = query.ToList();
  236. }
  237. #if !DEBUG && SQLITE
  238. [Explicit]
  239. #endif
  240. [Test]
  241. public void DateTimeDiffMinutes()
  242. {
  243. Northwind db = CreateDB();
  244. DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddMinutes(2);
  245. var query = from e in db.Employees
  246. where (e.BirthDate.Value - parameterDateTime).Minutes == -2
  247. select e;
  248. var list = query.ToList();
  249. AssertHelper.Greater(list.Count, 0);
  250. }
  251. #if !DEBUG && SQLITE
  252. [Explicit]
  253. #endif
  254. [Test]
  255. public void DateTimeDiffTotalSeconds()
  256. {
  257. Northwind db = CreateDB();
  258. var query = from e in db.Employees
  259. where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalSeconds > 0
  260. select e;
  261. var list = query.ToList();
  262. }
  263. #if !DEBUG && SQLITE
  264. [Explicit]
  265. #endif
  266. [Test]
  267. public void DateTimeDiffSeconds()
  268. {
  269. Northwind db = CreateDB();
  270. DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddSeconds(2);
  271. var query = from e in db.Employees
  272. where (e.BirthDate.Value - parameterDateTime).Seconds == -2
  273. select e;
  274. var list = query.ToList();
  275. AssertHelper.Greater(list.Count, 0);
  276. }
  277. #if !DEBUG && (SQLITE || MSSQL)
  278. // L2SQL: SQL Server doesnt' seem to support millisecond precision.
  279. [Explicit]
  280. #endif
  281. [Test]
  282. public void DateTimeDiffMilliseconds()
  283. {
  284. Northwind db = CreateDB();
  285. DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddMilliseconds(2);
  286. var query = from e in db.Employees
  287. where (e.BirthDate.Value - parameterDateTime).Milliseconds == -2
  288. select e;
  289. var list = query.ToList();
  290. AssertHelper.Greater(list.Count, 0);
  291. }
  292. #if !DEBUG && SQLITE
  293. [Explicit]
  294. #endif
  295. [Test]
  296. public void DateTimeDiffTotalMilliseconds()
  297. {
  298. Northwind db = CreateDB();
  299. var query = from e in db.Employees
  300. where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalMinutes > 0
  301. select e;
  302. var list = query.ToList();
  303. }
  304. #if !DEBUG && SQLITE
  305. [Explicit]
  306. #endif
  307. [Test]
  308. public void DateTimeDiffDays()
  309. {
  310. Northwind db = CreateDB();
  311. DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddDays(2);
  312. var query = from e in db.Employees
  313. where (e.BirthDate.Value - parameterDateTime).Days == -2
  314. select e;
  315. var list = query.ToList();
  316. AssertHelper.Greater(list.Count, 0);
  317. }
  318. #if !DEBUG && SQLITE
  319. [Explicit]
  320. #endif
  321. [Test]
  322. public void DateTimeDiffTotalDays()
  323. {
  324. Northwind db = CreateDB();
  325. DateTime firstDate = db.Employees.First().BirthDate.Value;
  326. DateTime parameterDate = firstDate.Date.AddDays(2);
  327. parameterDate = parameterDate.Date.AddHours(12);
  328. var query = from e in db.Employees
  329. where (e.BirthDate.Value -parameterDate).TotalDays == -2.5
  330. select e;
  331. var list = query.ToList();
  332. AssertHelper.Greater(list.Count, 0);
  333. }
  334. #if !DEBUG && POSTGRES
  335. [Explicit]
  336. #endif
  337. [Test]
  338. [ExpectedException(typeof(InvalidOperationException))]
  339. public void DateTimeDiffTotalDaysSelectWithNulls01()
  340. {
  341. Northwind db = CreateDB();
  342. var employee = new Employee
  343. {
  344. FirstName = "Test First",
  345. LastName = "Test Last",
  346. };
  347. db.Employees.InsertOnSubmit(employee);
  348. db.SubmitChanges();
  349. DateTime firstDate = db.Employees.First().BirthDate.Value;
  350. firstDate.Date.AddDays(2);
  351. DateTime parameterDate = firstDate.Date.AddHours(12);
  352. try
  353. {
  354. //this test should throw an invalid operation exception since one BirthDate is null so select clausle should crash
  355. var query = from e in db.Employees
  356. select (e.BirthDate.Value - parameterDate).TotalDays;
  357. var list = query.ToList();
  358. AssertHelper.Greater(list.Count, 0);
  359. }
  360. finally
  361. {
  362. db.Employees.DeleteOnSubmit(employee);
  363. db.SubmitChanges();
  364. }
  365. }
  366. #if !DEBUG && POSTGRES
  367. [Explicit]
  368. #endif
  369. [Test]
  370. public void DateTimeDiffTotalDaysSelectWithNulls02()
  371. {
  372. Northwind db = CreateDB();
  373. var employee = new Employee
  374. {
  375. FirstName = "Test First",
  376. LastName = "Test Last",
  377. };
  378. db.Employees.InsertOnSubmit(employee);
  379. db.SubmitChanges();
  380. DateTime firstDate = db.Employees.First().BirthDate.Value;
  381. DateTime parameterDate = firstDate.Date.AddDays(2);
  382. parameterDate = parameterDate.Date.AddHours(12);
  383. try
  384. {
  385. var query = from e in db.Employees
  386. where e.BirthDate.HasValue
  387. select (e.BirthDate.Value - parameterDate).TotalDays;
  388. var list = query.ToList();
  389. AssertHelper.Greater(list.Count, 0);
  390. }
  391. finally
  392. {
  393. db.Employees.DeleteOnSubmit(employee);
  394. db.SubmitChanges();
  395. }
  396. }
  397. #if !DEBUG && (SQLITE || (MSSQL && L2SQL))
  398. // L2SQL: System.Data.SqlClient.SqlException : The datepart minute is not supported by date function datepart for data type date.
  399. [Explicit]
  400. #endif
  401. [Test]
  402. public void DateGetDate()
  403. {
  404. Northwind db = CreateDB();
  405. var query = from e in db.Employees
  406. where (e.BirthDate.Value.Date).Minute == 0
  407. select e;
  408. var list = query.ToList();
  409. }
  410. }
  411. }