def test_nvl(self):
q = Q.from_("abc").select(fn.NVL(F("foo"), 0))
self.assertEqual('SELECT NVL("foo",0) FROM "abc"', str(q))
def test__case__else(self):
q = Q.from_('abc').select(Case().when(F('foo') == 1, 'a').else_('b'))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END FROM \"abc\"",
str(q))
def test__addition__decimal(self):
q1 = Q.from_('abc').select(F('a') + 1.0)
q2 = Q.from_(self.t).select(self.t.a + 1.0)
self.assertEqual('SELECT \"a\"+1.0 FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"+1.0 FROM \"abc\"', str(q2))
def test__avg(self):
q = Q.from_('abc').select(fn.Avg(F('foo')))
self.assertEqual('SELECT AVG(\"foo\") FROM \"abc\"', str(q))
def test__stddev(self):
q = Q.from_('abc').select(fn.StdDev(F('foo')))
self.assertEqual('SELECT STDDEV(\"foo\") FROM \"abc\"', str(q))
def test__modulus__number(self):
q1 = Q.from_('abc').select(F('a') % 2)
q2 = Q.from_(self.t).select(self.t.a % 2)
self.assertEqual('SELECT MOD(\"a\",2) FROM \"abc\"', str(q1))
self.assertEqual('SELECT MOD(\"a\",2) FROM \"abc\"', str(q2))
def test__count(self):
q = Q.from_('abc').select(fn.Count(F('foo')))
self.assertEqual('SELECT COUNT(\"foo\") FROM \"abc\"', str(q))
def test__multiplication__right(self):
q1 = Q.from_('abc').select(1 * F('a'))
q2 = Q.from_(self.t).select(1 * self.t.a)
self.assertEqual('SELECT 1*\"a\" FROM \"abc\"', str(q1))
self.assertEqual('SELECT 1*\"a\" FROM \"abc\"', str(q2))
def test__division__fields(self):
q1 = Q.from_('abc').select(F('a') / F('b'))
q2 = Q.from_(self.t).select(self.t.a / self.t.b)
self.assertEqual('SELECT \"a\"/\"b\" FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"/\"b\" FROM \"abc\"', str(q2))
def test__subtraction__decimal(self):
q1 = Q.from_("abc").select(F("a") - 1.0)
q2 = Q.from_(self.t).select(self.t.a - 1.0)
self.assertEqual('SELECT "a"-1.0 FROM "abc"', str(q1))
self.assertEqual('SELECT "a"-1.0 FROM "abc"', str(q2))
def test__multiplication__decimal(self):
q1 = Q.from_('abc').select(F('a') * 1.0)
q2 = Q.from_(self.t).select(self.t.a * 1.0)
self.assertEqual('SELECT \"a\"*1.0 FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"*1.0 FROM \"abc\"', str(q2))
def test__subtraction__number(self):
q1 = Q.from_("abc").select(F("a") - 1)
q2 = Q.from_(self.t).select(self.t.a - 1)
self.assertEqual('SELECT "a"-1 FROM "abc"', str(q1))
self.assertEqual('SELECT "a"-1 FROM "abc"', str(q2))
def test__subtraction__fields(self):
q1 = Q.from_("abc").select(F("a") - F("b"))
q2 = Q.from_(self.t).select(self.t.a - self.t.b)
self.assertEqual('SELECT "a"-"b" FROM "abc"', str(q1))
self.assertEqual('SELECT "a"-"b" FROM "abc"', str(q2))
def test__addition__right(self):
q1 = Q.from_("abc").select(1 + F("a"))
q2 = Q.from_(self.t).select(1 + self.t.a)
self.assertEqual('SELECT 1+"a" FROM "abc"', str(q1))
self.assertEqual('SELECT 1+"a" FROM "abc"', str(q2))
def test__exponent__number(self):
q1 = Q.from_('abc').select(F('a')**2)
q2 = Q.from_(self.t).select(self.t.a**2)
self.assertEqual('SELECT POW(\"a\",2) FROM \"abc\"', str(q1))
self.assertEqual('SELECT POW(\"a\",2) FROM \"abc\"', str(q2))
def test__division__number(self):
q1 = Q.from_('abc').select(F('a') / 1)
q2 = Q.from_(self.t).select(self.t.a / 1)
self.assertEqual('SELECT \"a\"/1 FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"/1 FROM \"abc\"', str(q2))
def test__exponent__decimal(self):
q1 = Q.from_('abc').select(F('a')**0.5)
q2 = Q.from_(self.t).select(self.t.a**0.5)
self.assertEqual('SELECT POW(\"a\",0.5) FROM \"abc\"', str(q1))
self.assertEqual('SELECT POW(\"a\",0.5) FROM \"abc\"', str(q2))
def test__division__right(self):
q1 = Q.from_('abc').select(1 / F('a'))
q2 = Q.from_(self.t).select(1 / self.t.a)
self.assertEqual('SELECT 1/\"a\" FROM \"abc\"', str(q1))
self.assertEqual('SELECT 1/\"a\" FROM \"abc\"', str(q2))
def test__floor(self):
q1 = Q.from_('abc').select(fn.Floor(F('foo')))
q2 = Q.from_(self.t).select(fn.Floor(self.t.foo))
self.assertEqual('SELECT FLOOR(\"foo\") FROM \"abc\"', str(q1))
self.assertEqual('SELECT FLOOR(\"foo\") FROM \"abc\"', str(q2))
def test__complex_op(self):
q1 = Q.from_('abc').select(2 + 1 / F('a') - 5)
q2 = Q.from_(self.t).select(2 + 1 / self.t.a - 5)
self.assertEqual('SELECT 2+1/\"a\"-5 FROM \"abc\"', str(q1))
self.assertEqual('SELECT 2+1/\"a\"-5 FROM \"abc\"', str(q2))
def test__sum(self):
q = Q.from_('abc').select(fn.Sum(F('foo')))
self.assertEqual('SELECT SUM(\"foo\") FROM \"abc\"', str(q))
def test__complex_op_div_parentheses(self):
q1 = Q.from_('abc').select((F('a') + 1) / (F('b') - 5))
q2 = Q.from_(self.t).select((self.t.a + 1) / (self.t.b - 5))
self.assertEqual('SELECT (\"a\"+1)/(\"b\"-5) FROM \"abc\"', str(q1))
self.assertEqual('SELECT (\"a\"+1)/(\"b\"-5) FROM \"abc\"', str(q2))
def test__max(self):
q = Q.from_('abc').select(fn.Max(F('foo')))
self.assertEqual('SELECT MAX(\"foo\") FROM \"abc\"', str(q))
def test__complex_op_div_no_parentheses(self):
q = Q.from_('abc').select(F('a') + 1 / F('b') - 5)
self.assertEqual('SELECT \"a\"+1/\"b\"-5 FROM \"abc\"', str(q))
def test__approx_percentile(self):
q = Q.from_('abc').select(fn.ApproximatePercentile(F('foo'), 0.5))
self.assertEqual(
'SELECT APPROXIMATE_PERCENTILE(\"foo\" USING PARAMETERS percentile=0.5) FROM \"abc\"',
str(q))
def test__arithmetic_equality(self):
q1 = Q.from_('abc').select(F('a') / 2 == 2)
q2 = Q.from_(self.t).select(self.t.a / 2 == 2)
self.assertEqual('SELECT "a"/2=2 FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"/2=2 FROM \"abc\"', str(q2))
def test__addition__fields(self):
q1 = Q.from_('abc').select(F('a') + F('b'))
q2 = Q.from_(self.t).select(self.t.a + self.t.b)
self.assertEqual('SELECT \"a\"+\"b\" FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"+\"b\" FROM \"abc\"', str(q2))
def test__arithmetic_with_function(self):
q1 = Q.from_('abc').select(fn.Sum(F('foo')) + 1)
q2 = Q.from_(self.t).select(fn.Sum(self.t.foo) + 1)
self.assertEqual('SELECT SUM(\"foo\")+1 FROM \"abc\"', str(q1))
self.assertEqual('SELECT SUM(\"foo\")+1 FROM \"abc\"', str(q2))
class DateFunctionsTests(unittest.TestCase):
dt = F('dt')
t = T('abc')
def _test_extract_datepart(self, date_part):
q = Q.from_(self.t).select(fn.Extract(date_part, self.t.foo))
self.assertEqual(
"SELECT EXTRACT(%s FROM \"foo\") FROM \"abc\"" % date_part.value,
str(q))
def test_extract_microsecond(self):
self._test_extract_datepart(DatePart.microsecond)
def test_extract_second(self):
self._test_extract_datepart(DatePart.second)
def test_extract_minute(self):
self._test_extract_datepart(DatePart.minute)
def test_extract_hour(self):
self._test_extract_datepart(DatePart.hour)
def test_extract_day(self):
self._test_extract_datepart(DatePart.day)
def test_extract_week(self):
self._test_extract_datepart(DatePart.week)
def test_extract_month(self):
self._test_extract_datepart(DatePart.month)
def test_extract_quarter(self):
self._test_extract_datepart(DatePart.quarter)
def test_extract_year(self):
self._test_extract_datepart(DatePart.year)
def test_timestampadd(self):
a = fn.TimestampAdd('year', 1, '2017-10-01')
self.assertEqual(str(a), "TIMESTAMPADD('year',1,'2017-10-01')")
def test_time_diff(self):
a = fn.TimeDiff('18:00:00', '10:00:00')
self.assertEqual(str(a), "TIMEDIFF('18:00:00','10:00:00')")
def test_date_add(self):
a = fn.DateAdd('year', 1, '2017-10-01')
self.assertEqual(str(a), "DATE_ADD('year',1,'2017-10-01')")
def test_now(self):
query = Query.select(fn.Now())
self.assertEqual("SELECT NOW()", str(query))
def test_utc_timestamp(self):
query = Query.select(fn.UtcTimestamp())
self.assertEqual("SELECT UTC_TIMESTAMP()", str(query))
def test_current_date(self):
query = Query.select(fn.CurDate())
self.assertEqual("SELECT CURRENT_DATE()", str(query))
def test_current_time(self):
query = Query.select(fn.CurTime())
self.assertEqual("SELECT CURRENT_TIME()", str(query))
def test_current_timestamp(self):
query = Query.select(fn.CurTimestamp())
self.assertEqual("SELECT CURRENT_TIMESTAMP", str(query))
def test_current_timestamp_with_alias(self):
query = Query.select(fn.CurTimestamp('ts'))
self.assertEqual("SELECT CURRENT_TIMESTAMP \"ts\"", str(query))
def test_to_date(self):
q1 = fn.ToDate('2019-06-21', 'yyyy-mm-dd')
q2 = Query.from_(self.t).select(fn.ToDate('2019-06-21', 'yyyy-mm-dd'))
q3 = Query.from_(self.t).select(fn.ToDate(F('foo'), 'yyyy-mm-dd'))
self.assertEqual(str(q1), "TO_DATE('2019-06-21','yyyy-mm-dd')")
self.assertEqual(
str(q2), "SELECT TO_DATE('2019-06-21','yyyy-mm-dd') FROM \"abc\"")
self.assertEqual(str(q3),
"SELECT TO_DATE(\"foo\",'yyyy-mm-dd') FROM \"abc\"")
def test_nullif(self):
q = Q.from_("abc").select(fn.NullIf(F("foo"), 0))
self.assertEqual('SELECT NULLIF("foo",0) FROM "abc"', str(q))
