1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
|
#!/usr/bin/env tarantool
-- This file contains testcases which can only be valudated in debug mode.
test = require("sqltester")
test:plan(30)
local ffi = require("ffi")
ffi.cdef("extern int sql_current_time;")
local function datetest(tnum, expr, result)
test:do_test(
"date-"..tnum,
function ()
local r = test:execsql("SELECT coalesce( "..expr..", 'NULL' )")
return ""..r[1]
end,
result)
end
ffi.C.sql_current_time = 1157124367
datetest(4.1, "date('now')", "2006-09-01")
ffi.C.sql_current_time = 0
ffi.C.sql_current_time = 1199243045
datetest(2.40, "datetime()", "2008-01-02 03:04:05")
ffi.C.sql_current_time = 0
-- Test modifiers when the date begins as a julian day number - to
-- make sure the HH:MM:SS is preserved. Ticket #551.
--
-- TBI to be implemented ffi.C.sql_current_time
ffi.C.sql_current_time =
tonumber(test:execsql("SELECT strftime('%s','2003-10-22 12:34:00')")[1])
datetest(8.1, "datetime('now','weekday 0')", "2003-10-26 12:34:00")
datetest(8.2, "datetime('now','weekday 1')", "2003-10-27 12:34:00")
datetest(8.3, "datetime('now','weekday 2')", "2003-10-28 12:34:00")
datetest(8.4, "datetime('now','weekday 3')", "2003-10-22 12:34:00")
datetest(8.5, "datetime('now','start of month')", "2003-10-01 00:00:00")
datetest(8.6, "datetime('now','start of year')", "2003-01-01 00:00:00")
datetest(8.7, "datetime('now','start of day')", "2003-10-22 00:00:00")
datetest(8.8, "datetime('now','1 day')", "2003-10-23 12:34:00")
datetest(8.9, "datetime('now','+1 day')", "2003-10-23 12:34:00")
datetest(8.10, "datetime('now','+1.25 day')", "2003-10-23 18:34:00")
datetest(8.11, "datetime('now','-1.0 day')", "2003-10-21 12:34:00")
datetest(8.12, "datetime('now','1 month')", "2003-11-22 12:34:00")
datetest(8.13, "datetime('now','11 month')", "2004-09-22 12:34:00")
datetest(8.14, "datetime('now','-13 month')", "2002-09-22 12:34:00")
datetest(8.15, "datetime('now','1.5 months')", "2003-12-07 12:34:00")
datetest(8.16, "datetime('now','-5 years')", "1998-10-22 12:34:00")
datetest(8.17, "datetime('now','+10.5 minutes')", "2003-10-22 12:44:30")
datetest(8.18, "datetime('now','-1.25 hours')", "2003-10-22 11:19:00")
datetest(8.19, "datetime('now','11.25 seconds')", "2003-10-22 12:34:11")
datetest(8.90, "datetime('now','abcdefghijklmnopqrstuvwyxzABCDEFGHIJLMNOP')", "NULL")
ffi.C.sql_current_time = 0
--
-- Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
-- and CURRENT_TIMESTAMP.
--
test:do_execsql_test(
"table-13.1",
[[
CREATE TABLE tablet8(
a integer primary key,
tm text DEFAULT CURRENT_TIME,
dt text DEFAULT CURRENT_DATE,
dttm text DEFAULT CURRENT_TIMESTAMP
);
SELECT * FROM tablet8;
]], {})
local data = {
{"1976-07-04", "12:00:00", 205329600},
{"1994-04-16", "14:00:00", 766504800},
{"2000-01-01", "00:00:00", 946684800},
{"2003-12-31", "12:34:56", 1072874096},
}
for i ,val in ipairs(data) do
local date = val[1]
local time = val[2]
local seconds = val[3]
ffi.C.sql_current_time = seconds
test:do_execsql_test(
"table-13.2."..i,
string.format([[
INSERT INTO tablet8(a) VALUES(%s);
SELECT tm, dt, dttm FROM tablet8 WHERE a=%s;
]], i, i), {
time, date, date .. " " .. time
})
end
ffi.C.sql_current_time = 1
test:do_execsql_test(
"e_expr-12.2.6",
[[
SELECT CURRENT_TIME
]], {
-- <e_expr-12.2.6>
"00:00:01"
-- </e_expr-12.2.6>
})
test:do_execsql_test(
"e_expr-12.2.7",
[[
SELECT CURRENT_DATE
]], {
-- <e_expr-12.2.7>
"1970-01-01"
-- </e_expr-12.2.7>
})
test:do_execsql_test(
"e_expr-12.2.8",
[[
SELECT CURRENT_TIMESTAMP
]], {
-- <e_expr-12.2.8>
"1970-01-01 00:00:01"
-- </e_expr-12.2.8>
})
ffi.C.sql_current_time = 0
test:finish_test()
|