def max_date_db(self, table=None, field=None, q=None, join_minesite=True, minesite='FortHills'):
a = T(table)
b = T('UnitID')
if q is None:
q = a.select(fn.Max(a[field]))
if join_minesite:
q = q.left_join(b).on_field('Unit') \
.where(b.MineSite == minesite)
val = self.query_single_val(q)
return f.convert_date(val)
def __init__(self, da=None, **kw):
super().__init__(da=da, **kw)
a, b, c, d = self.select_table, T('UnitID'), T('UserSettings'), T('EquipType')
date_col = 'DateAdded'
q = Query.from_(a) \
.left_join(b).on_field('Unit') \
.left_join(c).on(a.CreatedBy == c.UserName) \
.left_join(d).on(b.Model == d.Model) \
f.set_self(vars())
self.default_dtypes |= \
f.dtypes_dict('Int64', ['SMR', 'Unit SMR', 'Comp SMR', 'Part SMR', 'Pics']) | \
f.dtypes_dict('bool', ['Comp CO'])
class RegexpLikeFunctionTests(unittest.TestCase):
t = T('abc')
def test__regexp_like(self):
q = VerticaQuery.from_(self.t).select(fn.RegexpLike(self.t.foo, '^a', 'x'))
self.assertEqual("SELECT REGEXP_LIKE(\"foo\",\'^a\',\'x\') FROM \"abc\"", str(q))
def __init__(self, table_name: str, filter_vals: dict = None, **kw):
super().__init__(select_tablename=table_name, **kw)
# get dbtable obj to inspect for keys
keys = dbt.get_dbtable_keys(table_name)
keys = [key.lower() for key in keys]
a = T(table_name)
q = Query.from_(a) \
.select(*keys)
f.set_self(vars())
# helper to add filter vals eg {'unit': 'F301'}
if isinstance(filter_vals, dict):
field = list(filter_vals.keys())[0]
val = filter_vals[field]
field_ = a.field(field)
if isinstance(val, (list, tuple)):
# key: list of vals
self.fltr.add(ct=field_.isin(val))
else:
# single key: val
self.fltr.add(vals=filter_vals)
class SplitPartFunctionTests(unittest.TestCase):
t = T('abc')
def test__split_part(self):
q = VerticaQuery.from_(self.t).select(fn.SplitPart(self.t.foo, '|', 3))
self.assertEqual("SELECT SPLIT_PART(\"foo\",\'|\',3) FROM \"abc\"", str(q))
class SplitPartFunctionTests(unittest.TestCase):
t = T("abc")
def test__split_part(self):
q = VerticaQuery.from_(self.t).select(fn.SplitPart(self.t.foo, "|", 3))
self.assertEqual('SELECT SPLIT_PART("foo",\'|\',3) FROM "abc"', str(q))
class SplitPartFunctionTests(unittest.TestCase):
t = T('abc')
def test__split_part__field_with_vertica_dialect(self):
q = VerticaQuery.from_(self.t).select(fn.SplitPart(self.t.foo, '|', 3))
self.assertEqual("SELECT SPLIT_PART(\"foo\",\'|\',3) FROM \"abc\"",
str(q))
def test__split_part__field_with_mysql_dialect(self):
q = MySQLQuery.from_(self.t).select(fn.SplitPart(self.t.foo, '|', 3))
self.assertEqual("SELECT SUBSTRING_INDEX(`foo`,\'|\',3) FROM `abc`",
str(q))
def test__split_part__field_with_postgresql_dialect(self):
q = PostgreSQLQuery.from_(self.t).select(
fn.SplitPart(self.t.foo, '|', 3))
self.assertEqual("SELECT SPLIT_PART(\"foo\",\'|\',3) FROM \"abc\"",
str(q))
def test__split_part__field_with_redshift_dialect(self):
q = RedshiftQuery.from_(self.t).select(fn.SplitPart(
self.t.foo, '|', 3))
self.assertEqual("SELECT SPLIT_PART(\"foo\",\'|\',3) FROM \"abc\"",
str(q))
def test__split_part__field_with_oracle_dialect(self):
q = OracleQuery.from_(self.t).select(fn.SplitPart(self.t.foo, '|', 3))
self.assertEqual(
"SELECT REGEXP_SUBSTR(\"foo\",\'[^|]+\',1,3) FROM \"abc\"", str(q))
class CastTests(unittest.TestCase):
t = T('abc')
def test__cast__as(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q))
def test__cast__signed(self):
q1 = Q.from_(self.t).select(fn.Signed(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.SIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS SIGNED) FROM \"abc\"",
str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS SIGNED) FROM \"abc\"",
str(q2))
def test__cast__unsigned(self):
q1 = Q.from_(self.t).select(fn.Unsigned(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q2))
def test__convert__utf8(self):
q = Q.from_(self.t).select(fn.Convert(self.t.foo, SqlTypes.utf8))
self.assertEqual("SELECT CONVERT(\"foo\" USING utf8) FROM \"abc\"",
str(q))
def test__cast__date(self):
q1 = Q.from_(self.t).select(fn.Date(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.DATE))
self.assertEqual("SELECT DATE(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS DATE) FROM \"abc\"", str(q2))
def test__cast__timestamp(self):
q1 = Q.from_(self.t).select(fn.Timestamp(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.TIMESTAMP))
self.assertEqual("SELECT TIMESTAMP(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS TIMESTAMP) FROM \"abc\"",
str(q2))
def test__cast__varchar(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR))
self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR) FROM \"abc\"",
str(q))
def test__tochar__(self):
q = Q.from_(self.t).select(fn.ToChar(self.t.foo, "SomeFormat"))
self.assertEqual("SELECT TO_CHAR(\"foo\",'SomeFormat') FROM \"abc\"",
str(q))
def process_criterion(self):
"""Search PartName or PartNameAlt with same filter"""
t = T('Parts')
fltr = self.fltr
ct = fltr.get_criterion(field='PartName')
if not ct is None:
fltr.criterion[ct] |= t.PartNameAlt.like(fltr.criterion[ct].right)
def process_criterion(self):
# need to always filter manualclosed to 'OR is null'
t = T('FCSummaryMineSite')
fltr = self.fltr
ct = fltr.get_criterion(field='manualclosed')
if not ct is None:
fltr.criterion[ct] |= t.ManualClosed.isnull() # need to operate on the original dict, not copy ct
def __init__(self, da=None, **kw):
super().__init__(da=da, **kw)
a, b, c, d, e = self.a, self.b, T('ComponentType'), self.d, T(
'ComponentBench')
life_achieved = Case().when(
a.SMR == 0,
None).else_(a.ComponentSMR - e.bench_smr).as_('Life Achieved')
q = self.q \
.inner_join(c).on_field('Floc') \
.left_join(e).on(
(a.Floc == e.floc) &
((b.Model == e.model) | (d.ModelBase == e.model_base) | (d.EquipClass == e.equip_class))) \
.orderby(a.Unit, a.DateAdded, c.Modifier, a.GroupCO)
f.set_self(vars())
self.default_dtypes |= f.dtypes_dict(
'Int64', ['Bench SMR', 'Life Achieved', 'Install SMR'])
def __init__(self, **kw):
super().__init__(**kw)
limit_top = None
self.default_dtypes |= f.dtypes_dict('Int64',
['unit_smr', 'component_smr'])
a, b, c = self.select_table, T('UnitId'), T('EquipType')
# cols = [a.star]
cols = [
a.unit, a.component_id, a.modifier, a.sample_date, a.unit_smr,
a.oil_changed, a.sample_rank, a.test_results, a.test_flags
]
q = Query.from_(a) \
.left_join(b).on_field('Unit') \
.left_join(c).on(b.Model == c.Model) \
.orderby(a.unit, a.component_id, a.modifier) \
.orderby(a.sample_date, order=Order.desc)
f.set_self(vars())
def __init__(
self,
parent: 'TableWidget' = None,
minesite: str = None,
da: dict = None,
theme: str = 'light',
select_tablename: str = None,
use_cached_df: bool = False):
self.parent = parent
self.minesite = minesite
self.da = da
self.theme = theme
self.use_cached_df = use_cached_df
self.formats, self.default_dtypes, self.stylemap_cols = {}, {}, {}
self.background_gradients = []
self._minesite_default = 'FortHills'
self.cmap = cmap_default
self.sql = None
self.df = pd.DataFrame()
self.df_loaded = False
self.data_query_time = 0.0
m = cf.config['TableName']
color = cf.config['color']
self.name = self.__class__.__name__
self.query_key = f'queries/{self.name.lower()}' # saving last_query
# loop base classes to get first working title, need this to map view_cols
for base_class in inspect.getmro(self.__class__):
title = m['Class'].get(base_class.__name__, None)
if not title is None:
break
# loop through base classes till we find a working select_table
if select_tablename is None:
for base_class in inspect.getmro(self.__class__):
select_tablename = m['Select'].get(base_class.__name__, None)
if not select_tablename is None:
break
self.select_table = T(select_tablename)
# try to get updatetable, if none set as name of select table
if not select_tablename is None:
self.update_tablename = m['Update'].get(self.name, select_tablename)
self.update_table = getattr(dbm, self.update_tablename, None)
# set dict for db > view_col conversion later
self.view_cols = f.get_dict_db_view(title=title)
self.title = title
self.set_fltr()
def get_df_component(self):
name = 'component'
df = self.get_df_saved(name)
if df is None:
a = T('ComponentType')
q = Query.from_(a).select('*')
df = self.read_query(q=q)
self.combine_comp_modifier(df=df, cols=['Component', 'Modifier'], target='Combined')
self.save_df(df, name)
return df
def get_df_oil_components(self, unit: str = None, minesite: str = None):
"""Return uniqe unit/component/modifier combinations from oil samples
Parameters
----------
unit : str, optional
filter to unit, default None
minesite : str, optional
filter to components per minesite
Returns
-------
pd.DataFrame
df of unit, component, modifier
"""
name = 'oil_comps'
df = self.get_df_saved(name)
if df is None:
a = T('OilSamples')
cols = [a.unit, a.component_id, a.modifier]
q = Query.from_(a) \
.select(*cols) \
.groupby(*cols)
df = self.read_query(q=q) \
.sort_values(by=['unit', 'component_id', 'modifier'])
self.combine_comp_modifier(df=df, cols=['component_id', 'modifier'], target='combined', sep=' - ')
dfu = self.get_df_unit() \
.rename_axis('index')
df = df \
.merge(
right=dfu[['Unit', 'MineSite', 'ModelBase']],
how='left',
left_on='unit',
right_on='Unit') \
.drop(columns=['Unit']) \
.rename(columns=dict(MineSite='minesite', ModelBase='model_base'))
self.save_df(df, name)
if not unit is None:
df = df[df.unit == unit]
if not minesite is None:
df = df[df]
return df
def __init__(self, unit, d_rng=None, **kw):
super().__init__(**kw)
a = T('UnitSMR')
if d_rng is None:
d_upper = dt.now()
d_lower = d_upper + delta(days=-60)
d_rng = (d_lower, d_upper)
cols = ['Unit', 'DateSMR', 'SMR']
q = Query.from_(a) \
.where(a.Unit == unit) \
.where(a.DateSMR.between(d_rng[0], d_rng[1]))
f.set_self(vars())
class RegexpLikeFunctionTests(unittest.TestCase):
t = T('abc')
def test__regexp_like__field_with_vertica_dialect(self):
q = VerticaQuery.from_(self.t).select(
fn.RegexpLike(self.t.foo, '^a', 'x'))
self.assertEqual(
"SELECT REGEXP_LIKE(\"foo\",\'^a\',\'x\') FROM \"abc\"", str(q))
def test__regexp_like__field_with_mysql_dialect(self):
q = MySQLQuery.from_(self.t).select(
fn.RegexpLike(self.t.foo, '^a', 'x'))
with self.assertRaises(DialectNotSupported):
str(q)
def test__regexp_like__field_with_postgresql_dialect(self):
q = PostgreSQLQuery.from_(self.t).select(
fn.RegexpLike(self.t.foo, '^a', 'x'))
self.assertEqual(
"SELECT REGEXP_MATCHES(\"foo\",\'^a\',\'x\') FROM \"abc\"", str(q))
def test__regexp_like__field_with_redshift_dialect(self):
q = RedshiftQuery.from_(self.t).select(
fn.RegexpLike(self.t.foo, '^a', 'x'))
self.assertEqual(
"SELECT REGEXP_MATCHES(\"foo\",\'^a\',\'x\') FROM \"abc\"", str(q))
def test__regexp_like__field_with_oracle_dialect(self):
q = OracleQuery.from_(self.t).select(
fn.RegexpLike(self.t.foo, '^a', 'x'))
self.assertEqual(
"SELECT REGEXP_LIKE(\"foo\",\'^a\',\'x\') FROM \"abc\"", str(q))
def get_smr_prev_co(self, unit: str, date: dt, floc: str) -> int:
"""Return UNIT SMR at previous changeout
Parameters
----------
unit : str
date : dt
date to check before
floc : str
component floc
Returns
-------
int
UNIT smr at prev changeout, or 0 if none found
"""
a = T('EventLog')
q = Query().from_(a).select(a.SMR) \
.where(a.Unit == unit) \
.where(a.DateAdded <= date) \
.where(a.Floc == floc) \
.orderby(a.DateAdded, order=Order.desc)
return self.query_single_val(q)
def max_date(self):
a = T('viewPLM')
q = a.select(fn.Max(a.DateTime)) \
.where(a.Unit == self.unit)
return db.max_date_db(q=q)
class ArithmeticTests(unittest.TestCase):
t = T("abc")
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__addition__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__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__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__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__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__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__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__multiplication__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__multiplication__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__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__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__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__division__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__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__leftshift__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__leftshift__number(self):
q1 = Q.from_("abc").select(F('a') << 2)
q2 = Q.from_(self.t).select(self.t.a << 2)
self.assertEqual('SELECT "a"<<2 FROM "abc"', str(q1))
self.assertEqual('SELECT "a"<<2 FROM "abc"', str(q2))
def test__leftshift__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__rightshift__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__rightshift__number(self):
q1 = Q.from_("abc").select(F('a') >> 2)
q2 = Q.from_(self.t).select(self.t.a >> 2)
self.assertEqual('SELECT "a">>2 FROM "abc"', str(q1))
self.assertEqual('SELECT "a">>2 FROM "abc"', str(q2))
def test__rightshift__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__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__complex_op_add_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))
q3 = Q.from_("abc").select((F("a") / 1) + (F("b") / 5))
q4 = 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))
self.assertEqual('SELECT "a"/1+"b"/5 FROM "abc"', str(q3))
self.assertEqual('SELECT "a"/1+"b"/5 FROM "abc"', str(q4))
def test__complex_op_sub_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))
q3 = Q.from_("abc").select((F("a") / 1) - (F("b") / 5))
q4 = 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))
self.assertEqual('SELECT "a"/1-"b"/5 FROM "abc"', str(q3))
self.assertEqual('SELECT "a"/1-"b"/5 FROM "abc"', str(q4))
def test__complex_op_mul_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))
q3 = Q.from_("abc").select((F("a") / 1) * (F("b") - 5))
q4 = Q.from_(self.t).select((self.t.a / 1) * (self.t.b - 5))
q5 = Q.from_("abc").select((F("a") + 1) * (F("b") / 5))
q6 = 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))
self.assertEqual('SELECT "a"/1*("b"-5) FROM "abc"', str(q3))
self.assertEqual('SELECT "a"/1*("b"-5) FROM "abc"', str(q4))
self.assertEqual('SELECT ("a"+1)*"b"/5 FROM "abc"', str(q5))
self.assertEqual('SELECT ("a"+1)*"b"/5 FROM "abc"', str(q6))
def test__complex_op_mul_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__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))
q3 = Q.from_("abc").select((F("a") / 1) / (F("b") - 5))
q4 = Q.from_(self.t).select((self.t.a / 1) / (self.t.b - 5))
q5 = Q.from_("abc").select((F("a") + 1) / (F("b") * 5))
q6 = 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))
self.assertEqual('SELECT "a"/1/("b"-5) FROM "abc"', str(q3))
self.assertEqual('SELECT "a"/1/("b"-5) FROM "abc"', str(q4))
self.assertEqual('SELECT ("a"+1)/("b"*5) FROM "abc"', str(q5))
self.assertEqual('SELECT ("a"+1)/("b"*5) FROM "abc"', str(q6))
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__complex_op_exponent_parentheses(self):
q1 = Q.from_("abc").select(F("a") / (F("b")**2))
q2 = Q.from_(self.t).select(self.t.a / (self.t.b**2))
q3 = Q.from_("abc").select(F("a")**(F("b") / 2))
q4 = Q.from_(self.t).select(self.t.a**(self.t.b / 2))
q5 = Q.from_("abc").select((F("a")**F("b"))**2)
q6 = Q.from_(self.t).select((self.t.a**self.t.b)**2)
self.assertEqual('SELECT "a"/POW("b",2) FROM "abc"', str(q1))
self.assertEqual('SELECT "a"/POW("b",2) FROM "abc"', str(q2))
self.assertEqual('SELECT POW("a","b"/2) FROM "abc"', str(q3))
self.assertEqual('SELECT POW("a","b"/2) FROM "abc"', str(q4))
self.assertEqual('SELECT POW(POW("a","b"),2) FROM "abc"', str(q5))
self.assertEqual('SELECT POW(POW("a","b"),2) FROM "abc"', str(q6))
def test__complex_op_exponent_no_parentheses(self):
q1 = Q.from_("abc").select(F("a")**F("b")**2)
q2 = Q.from_(self.t).select(self.t.a**self.t.b**2)
q3 = Q.from_("abc").select(F("a") / F("b")**2)
q4 = Q.from_(self.t).select(self.t.a / self.t.b**2)
self.assertEqual('SELECT POW("a",POW("b",2)) FROM "abc"', str(q1))
self.assertEqual('SELECT POW("a",POW("b",2)) FROM "abc"', str(q2))
self.assertEqual('SELECT "a"/POW("b",2) FROM "abc"', str(q3))
self.assertEqual('SELECT "a"/POW("b",2) FROM "abc"', str(q4))
def test__complex_op_function_parentheses(self):
q1 = Q.from_("abc").select(F("a") / (fn.Sum(F("b")) / 2))
q2 = Q.from_("abc").select(self.t.a / (fn.Sum(self.t.b) / 2))
q3 = Q.from_("abc").select(fn.Sum(F("a") / (F("b") / 2)))
q4 = Q.from_("abc").select(fn.Sum(self.t.a / (self.t.b / 2)))
self.assertEqual('SELECT "a"/(SUM("b")/2) FROM "abc"', str(q1))
self.assertEqual('SELECT "a"/(SUM("b")/2) FROM "abc"', str(q2))
self.assertEqual('SELECT SUM("a"/("b"/2)) FROM "abc"', str(q3))
self.assertEqual('SELECT SUM("a"/("b"/2)) FROM "abc"', str(q4))
def test__complex_op_modulus_parentheses(self):
q1 = Q.from_("abc").select(F("a") / (F("b") % 2))
q2 = Q.from_(self.t).select(self.t.a / (self.t.b % 2))
q3 = Q.from_("abc").select(F("a") % (F("b") / 2))
q4 = Q.from_(self.t).select(self.t.a % (self.t.b / 2))
q5 = Q.from_("abc").select(F("a") % (F("b") % 2))
q6 = Q.from_("abc").select(self.t.a % (self.t.b % 2))
self.assertEqual('SELECT "a"/MOD("b",2) FROM "abc"', str(q1))
self.assertEqual('SELECT "a"/MOD("b",2) FROM "abc"', str(q2))
self.assertEqual('SELECT MOD("a","b"/2) FROM "abc"', str(q3))
self.assertEqual('SELECT MOD("a","b"/2) FROM "abc"', str(q4))
self.assertEqual('SELECT MOD("a",MOD("b",2)) FROM "abc"', str(q5))
self.assertEqual('SELECT MOD("a",MOD("b",2)) FROM "abc"', str(q6))
def test__complex_op_modulus_no_parentheses(self):
q1 = Q.from_("abc").select(F("a") % F("b") % 2)
q2 = Q.from_(self.t).select(self.t.a % self.t.b % 2)
q3 = Q.from_("abc").select(F("a") / F("b") % 2)
q4 = Q.from_(self.t).select(self.t.a / self.t.b % 2)
self.assertEqual('SELECT MOD(MOD("a","b"),2) FROM "abc"', str(q1))
self.assertEqual('SELECT MOD(MOD("a","b"),2) FROM "abc"', str(q2))
self.assertEqual('SELECT MOD("a"/"b",2) FROM "abc"', str(q3))
self.assertEqual('SELECT MOD("a"/"b",2) FROM "abc"', str(q4))
def test__complex_op_floor_parentheses(self):
q1 = Q.from_("abc").select(F("a") / (fn.Floor(F("b")) / 2))
q2 = Q.from_("abc").select(self.t.a / (fn.Floor(self.t.b) / 2))
q3 = Q.from_("abc").select(fn.Floor(F("a") / (F("b") / 2)))
q4 = Q.from_("abc").select(fn.Floor(self.t.a / (self.t.b / 2)))
self.assertEqual('SELECT "a"/(FLOOR("b")/2) FROM "abc"', str(q1))
self.assertEqual('SELECT "a"/(FLOOR("b")/2) FROM "abc"', str(q2))
self.assertEqual('SELECT FLOOR("a"/("b"/2)) FROM "abc"', str(q3))
self.assertEqual('SELECT FLOOR("a"/("b"/2)) FROM "abc"', str(q4))
def test__complex_op_nested_parentheses(self):
q1 = Q.from_("abc").select(F("a") / (F("b") / ((F("c") / 2))))
q2 = Q.from_("abc").select(self.t.a / (self.t.b / ((self.t.c / 2))))
self.assertEqual('SELECT "a"/("b"/("c"/2)) FROM "abc"', str(q1))
self.assertEqual('SELECT "a"/("b"/("c"/2)) FROM "abc"', str(q2))
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__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))
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__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__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__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))
class DateFunctionsTests(unittest.TestCase):
dt = F('dt')
t = T('abc')
def test_add_microsecond(self):
c = self.dt + Interval(microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 MICROSECOND\'", str(c))
def test_add_second(self):
c = self.dt + Interval(seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 SECOND\'", str(c))
def test_add_minute(self):
c = self.dt + Interval(minutes=1)
self.assertEqual("\"dt\"+INTERVAL \'1 MINUTE\'", str(c))
def test_add_day(self):
c = self.dt + Interval(days=1)
self.assertEqual("\"dt\"+INTERVAL \'1 DAY\'", str(c))
def test_add_week(self):
c = self.dt + Interval(weeks=1)
self.assertEqual("\"dt\"+INTERVAL \'1 WEEK\'", str(c))
def test_add_month(self):
c = self.dt + Interval(months=1)
self.assertEqual("\"dt\"+INTERVAL \'1 MONTH\'", str(c))
def test_add_quarter(self):
c = self.dt + Interval(quarters=1)
self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'", str(c))
def test_add_year(self):
c = self.dt + Interval(years=1)
self.assertEqual("\"dt\"+INTERVAL \'1 YEAR\'", str(c))
def test_add_second_microsecond(self):
c = self.dt + Interval(seconds=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1.1 SECOND_MICROSECOND\'", str(c))
def test_add_minute_microsecond(self):
c = self.dt + Interval(minutes=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:0.1 MINUTE_MICROSECOND\'",
str(c))
def test_add_minute_second(self):
c = self.dt + Interval(minutes=1, seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:1 MINUTE_SECOND\'", str(c))
def test_add_hour_microsecond(self):
c = self.dt + Interval(hours=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:0:0.1 HOUR_MICROSECOND\'",
str(c))
def test_add_hour_second(self):
c = self.dt + Interval(hours=1, seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:0:1 HOUR_SECOND\'", str(c))
def test_add_hour_minute(self):
c = self.dt + Interval(hours=1, minutes=1)
self.assertEqual("\"dt\"+INTERVAL \'1:1 HOUR_MINUTE\'", str(c))
def test_add_day_microsecond(self):
c = self.dt + Interval(days=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 0:0:0.1 DAY_MICROSECOND\'",
str(c))
def test_add_day_second(self):
c = self.dt + Interval(days=1, seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 0:0:1 DAY_SECOND\'", str(c))
def test_add_day_minute(self):
c = self.dt + Interval(days=1, minutes=1)
self.assertEqual("\"dt\"+INTERVAL \'1 0:1 DAY_MINUTE\'", str(c))
def test_add_day_hour(self):
c = self.dt + Interval(days=1, hours=1)
self.assertEqual("\"dt\"+INTERVAL \'1 1 DAY_HOUR\'", str(c))
def test_add_year_month(self):
c = self.dt + Interval(years=1, months=1)
self.assertEqual("\"dt\"+INTERVAL \'1-1 YEAR_MONTH\'", str(c))
def test_add_value_right(self):
c = Interval(microseconds=1) - self.dt
self.assertEqual("INTERVAL \'1 MICROSECOND\'-\"dt\"", str(c))
def test_add_value_complex_expressions(self):
c = self.dt + Interval(quarters=1) + Interval(weeks=1)
self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'+INTERVAL \'1 WEEK\'",
str(c))
def test_mysql_dialect_does_not_use_quotes_around_interval(self):
c = Interval(days=1).get_sql(dialect=Dialects.MYSQL)
self.assertEqual("INTERVAL 1 DAY", str(c))
def test_oracle_dialect_uses_single_quotes_around_expression_in_an_interval(
self):
c = Interval(days=1).get_sql(dialect=Dialects.ORACLE)
self.assertEqual("INTERVAL '1' DAY", str(c))
def test_vertica_dialect_uses_single_quotes_around_interval(self):
c = Interval(days=1).get_sql(dialect=Dialects.VERTICA)
self.assertEqual("INTERVAL '1 DAY'", str(c))
def test_redshift_dialect_uses_single_quotes_around_interval(self):
c = Interval(days=1).get_sql(dialect=Dialects.REDSHIFT)
self.assertEqual("INTERVAL '1 DAY'", str(c))
def test_postgresql_dialect_uses_single_quotes_around_interval(self):
c = Interval(days=1).get_sql(dialect=Dialects.POSTGRESQL)
self.assertEqual("INTERVAL '1 DAY'", str(c))
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_date_add(self):
a = fn.DateAdd('year', 1, '2017-10-01')
self.assertEqual(str(a), "DATE_ADD('year',1,'2017-10-01')")
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_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))
class StringTests(unittest.TestCase):
t = T("abc")
def test__ascii__str(self):
q = Q.select(fn.Ascii("2"))
self.assertEqual("SELECT ASCII('2')", str(q))
def test__ascii__int(self):
q = Q.select(fn.Ascii(2))
self.assertEqual("SELECT ASCII(2)", str(q))
def test__ascii__field(self):
q = Q.from_(self.t).select(fn.Ascii(self.t.foo))
self.assertEqual('SELECT ASCII("foo") FROM "abc"', str(q))
def test__bin__str(self):
q = Q.select(fn.Bin("2"))
self.assertEqual("SELECT BIN('2')", str(q))
def test__bin__int(self):
q = Q.select(fn.Bin(2))
self.assertEqual("SELECT BIN(2)", str(q))
def test__bin__field(self):
q = Q.from_(self.t).select(fn.Bin(self.t.foo))
self.assertEqual('SELECT BIN("foo") FROM "abc"', str(q))
def test__concat__str(self):
q = Q.select(fn.Concat("p", "y", "q", "b"))
self.assertEqual("SELECT CONCAT('p','y','q','b')", str(q))
def test__concat__field(self):
q = Q.from_(self.t).select(fn.Concat(self.t.foo, self.t.bar))
self.assertEqual('SELECT CONCAT("foo","bar") FROM "abc"', str(q))
def test__insert__str(self):
q = Q.select(fn.Insert("Quadratic", 3, 4, "What"))
self.assertEqual("SELECT INSERT('Quadratic',3,4,'What')", str(q))
def test__insert__field(self):
q = Q.from_(self.t).select(fn.Insert(self.t.foo, 3, 4, self.t.bar))
self.assertEqual('SELECT INSERT("foo",3,4,"bar") FROM "abc"', str(q))
def test__lower__str(self):
q = Q.select(fn.Lower("ABC"))
self.assertEqual("SELECT LOWER('ABC')", str(q))
def test__lower__field(self):
q = Q.from_(self.t).select(fn.Lower(self.t.foo))
self.assertEqual('SELECT LOWER("foo") FROM "abc"', str(q))
def test__length__str(self):
q = Q.select(fn.Length("ABC"))
self.assertEqual("SELECT LENGTH('ABC')", str(q))
def test__length__field(self):
q = Q.from_(self.t).select(fn.Length(self.t.foo))
self.assertEqual('SELECT LENGTH("foo") FROM "abc"', str(q))
def test__substring(self):
q = Q.from_(self.t).select(fn.Substring(self.t.foo, 2, 6))
self.assertEqual('SELECT SUBSTRING("foo",2,6) FROM "abc"', str(q))
class StringTests(unittest.TestCase):
t = T('abc')
def test__ascii__str(self):
q = Q.select(fn.Ascii('2'))
self.assertEqual("SELECT ASCII('2')", str(q))
def test__ascii__int(self):
q = Q.select(fn.Ascii(2))
self.assertEqual("SELECT ASCII(2)", str(q))
def test__ascii__field(self):
q = Q.from_(self.t).select(fn.Ascii(self.t.foo))
self.assertEqual("SELECT ASCII(\"foo\") FROM \"abc\"", str(q))
def test__bin__str(self):
q = Q.select(fn.Bin('2'))
self.assertEqual("SELECT BIN('2')", str(q))
def test__bin__int(self):
q = Q.select(fn.Bin(2))
self.assertEqual("SELECT BIN(2)", str(q))
def test__bin__field(self):
q = Q.from_(self.t).select(fn.Bin(self.t.foo))
self.assertEqual("SELECT BIN(\"foo\") FROM \"abc\"", str(q))
def test__concat__str(self):
q = Q.select(fn.Concat('p', 'y', 'q', 'b'))
self.assertEqual("SELECT CONCAT('p','y','q','b')", str(q))
def test__concat__field(self):
q = Q.from_(self.t).select(fn.Concat(self.t.foo, self.t.bar))
self.assertEqual("SELECT CONCAT(\"foo\",\"bar\") FROM \"abc\"", str(q))
def test__insert__str(self):
q = Q.select(fn.Insert('Quadratic', 3, 4, 'What'))
self.assertEqual("SELECT INSERT('Quadratic',3,4,'What')", str(q))
def test__insert__field(self):
q = Q.from_(self.t).select(fn.Insert(self.t.foo, 3, 4, self.t.bar))
self.assertEqual("SELECT INSERT(\"foo\",3,4,\"bar\") FROM \"abc\"",
str(q))
def test__lower__str(self):
q = Q.select(fn.Lower('ABC'))
self.assertEqual("SELECT LOWER('ABC')", str(q))
def test__lower__field(self):
q = Q.from_(self.t).select(fn.Lower(self.t.foo))
self.assertEqual("SELECT LOWER(\"foo\") FROM \"abc\"", str(q))
def test__length__str(self):
q = Q.select(fn.Length('ABC'))
self.assertEqual("SELECT LENGTH('ABC')", str(q))
def test__length__field(self):
q = Q.from_(self.t).select(fn.Length(self.t.foo))
self.assertEqual("SELECT LENGTH(\"foo\") FROM \"abc\"", str(q))
def test__substring(self):
q = Q.from_(self.t).select(fn.Substring(self.t.foo, 2, 6))
self.assertEqual("SELECT SUBSTRING(\"foo\",2,6) FROM \"abc\"", str(q))
class CastTests(unittest.TestCase):
t = T('abc')
def test__cast__as(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q))
def test__cast__signed(self):
q1 = Q.from_(self.t).select(fn.Signed(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.SIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS SIGNED) FROM \"abc\"",
str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS SIGNED) FROM \"abc\"",
str(q2))
def test__cast__unsigned(self):
q1 = Q.from_(self.t).select(fn.Unsigned(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q2))
def test__cast__date(self):
q1 = Q.from_(self.t).select(fn.Date(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.DATE))
self.assertEqual("SELECT DATE(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS DATE) FROM \"abc\"", str(q2))
def test__cast__timestamp(self):
q1 = Q.from_(self.t).select(fn.Timestamp(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.TIMESTAMP))
self.assertEqual("SELECT TIMESTAMP(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS TIMESTAMP) FROM \"abc\"",
str(q2))
def test__cast__char(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.CHAR))
self.assertEqual("SELECT CAST(\"foo\" AS CHAR) FROM \"abc\"", str(q))
def test__cast__char_with_arg(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR(24)))
self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR(24)) FROM \"abc\"",
str(q))
def test__cast__varchar(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR))
self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR) FROM \"abc\"",
str(q))
def test__cast__varchar_with_arg(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR(24)))
self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR(24)) FROM \"abc\"",
str(q))
def test__cast__long_varchar(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.LONG_VARCHAR))
self.assertEqual("SELECT CAST(\"foo\" AS LONG VARCHAR) FROM \"abc\"",
str(q))
def test__cast__long_varchar_with_arg(self):
q = Q.from_(self.t).select(
fn.Cast(self.t.foo, SqlTypes.LONG_VARCHAR(24)))
self.assertEqual(
"SELECT CAST(\"foo\" AS LONG VARCHAR(24)) FROM \"abc\"", str(q))
def test__cast__binary(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.BINARY))
self.assertEqual("SELECT CAST(\"foo\" AS BINARY) FROM \"abc\"", str(q))
def test__cast__binary_with_arg(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.BINARY(24)))
self.assertEqual("SELECT CAST(\"foo\" AS BINARY(24)) FROM \"abc\"",
str(q))
def test__cast__varbinary(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARBINARY))
self.assertEqual("SELECT CAST(\"foo\" AS VARBINARY) FROM \"abc\"",
str(q))
def test__cast__varbinary_with_arg(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARBINARY(24)))
self.assertEqual("SELECT CAST(\"foo\" AS VARBINARY(24)) FROM \"abc\"",
str(q))
def test__cast__long_varbinary(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo,
SqlTypes.LONG_VARBINARY))
self.assertEqual("SELECT CAST(\"foo\" AS LONG VARBINARY) FROM \"abc\"",
str(q))
def test__cast__long_varbinary_with_arg(self):
q = Q.from_(self.t).select(
fn.Cast(self.t.foo, SqlTypes.LONG_VARBINARY(24)))
self.assertEqual(
"SELECT CAST(\"foo\" AS LONG VARBINARY(24)) FROM \"abc\"", str(q))
def test__cast__boolean(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.BOOLEAN))
self.assertEqual("SELECT CAST(\"foo\" AS BOOLEAN) FROM \"abc\"",
str(q))
def test__cast__integer(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.INTEGER))
self.assertEqual("SELECT CAST(\"foo\" AS INTEGER) FROM \"abc\"",
str(q))
def test__cast__float(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.FLOAT))
self.assertEqual("SELECT CAST(\"foo\" AS FLOAT) FROM \"abc\"", str(q))
def test__cast__numeric(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.NUMERIC))
self.assertEqual("SELECT CAST(\"foo\" AS NUMERIC) FROM \"abc\"",
str(q))
def test__tochar__(self):
q = Q.from_(self.t).select(fn.ToChar(self.t.foo, "SomeFormat"))
self.assertEqual("SELECT TO_CHAR(\"foo\",'SomeFormat') FROM \"abc\"",
str(q))
class DateFunctionsTests(unittest.TestCase):
dt = F('dt')
t = T('abc')
def test_add_microsecond(self):
c = self.dt + Interval(microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 MICROSECOND\'", str(c))
def test_add_second(self):
c = self.dt + Interval(seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 SECOND\'", str(c))
def test_add_minute(self):
c = self.dt + Interval(minutes=1)
self.assertEqual("\"dt\"+INTERVAL \'1 MINUTE\'", str(c))
def test_add_day(self):
c = self.dt + Interval(days=1)
self.assertEqual("\"dt\"+INTERVAL \'1 DAY\'", str(c))
def test_add_week(self):
c = self.dt + Interval(weeks=1)
self.assertEqual("\"dt\"+INTERVAL \'1 WEEK\'", str(c))
def test_add_month(self):
c = self.dt + Interval(months=1)
self.assertEqual("\"dt\"+INTERVAL \'1 MONTH\'", str(c))
def test_add_quarter(self):
c = self.dt + Interval(quarters=1)
self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'", str(c))
def test_add_year(self):
c = self.dt + Interval(years=1)
self.assertEqual("\"dt\"+INTERVAL \'1 YEAR\'", str(c))
def test_add_second_microsecond(self):
c = self.dt + Interval(seconds=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1.1 SECOND_MICROSECOND\'", str(c))
def test_add_minute_microsecond(self):
c = self.dt + Interval(minutes=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:0.1 MINUTE_MICROSECOND\'",
str(c))
def test_add_minute_second(self):
c = self.dt + Interval(minutes=1, seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:1 MINUTE_SECOND\'", str(c))
def test_add_hour_microsecond(self):
c = self.dt + Interval(hours=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:0:0.1 HOUR_MICROSECOND\'",
str(c))
def test_add_hour_second(self):
c = self.dt + Interval(hours=1, seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1:0:1 HOUR_SECOND\'", str(c))
def test_add_hour_minute(self):
c = self.dt + Interval(hours=1, minutes=1)
self.assertEqual("\"dt\"+INTERVAL \'1:1 HOUR_MINUTE\'", str(c))
def test_add_day_microsecond(self):
c = self.dt + Interval(days=1, microseconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 0:0:0.1 DAY_MICROSECOND\'",
str(c))
def test_add_day_second(self):
c = self.dt + Interval(days=1, seconds=1)
self.assertEqual("\"dt\"+INTERVAL \'1 0:0:1 DAY_SECOND\'", str(c))
def test_add_day_minute(self):
c = self.dt + Interval(days=1, minutes=1)
self.assertEqual("\"dt\"+INTERVAL \'1 0:1 DAY_MINUTE\'", str(c))
def test_add_day_hour(self):
c = self.dt + Interval(days=1, hours=1)
self.assertEqual("\"dt\"+INTERVAL \'1 1 DAY_HOUR\'", str(c))
def test_add_year_month(self):
c = self.dt + Interval(years=1, months=1)
self.assertEqual("\"dt\"+INTERVAL \'1-1 YEAR_MONTH\'", str(c))
def test_add_value_right(self):
c = Interval(microseconds=1) - self.dt
self.assertEqual("INTERVAL \'1 MICROSECOND\'-\"dt\"", str(c))
def test_add_value_complex_expressions(self):
c = self.dt + Interval(quarters=1) + Interval(weeks=1)
self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'+INTERVAL \'1 WEEK\'",
str(c))
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)
class ArithmeticTests(unittest.TestCase):
t = T('abc')
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__addition__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__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__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__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__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__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__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__multiplication__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__multiplication__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__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__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__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__division__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__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__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__complex_op_add_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__complex_op_sub_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__complex_op_mul_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__complex_op_mul_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__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__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__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__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))
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__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__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__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))
class ArithmeticTests(unittest.TestCase):
t = T("abc")
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__addition__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__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__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__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__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__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__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__multiplication__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__multiplication__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__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__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__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__division__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__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__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__complex_op_add_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__complex_op_sub_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__complex_op_mul_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__complex_op_mul_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__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__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__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__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))
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__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__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__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))
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\"")
class DateFunctionsTests(unittest.TestCase):
dt = F("dt")
t = T("abc")
t2 = T("efg")
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_extract_join(self):
q = Q.from_(self.t).join(self.t2).on(self.t.id == self.t2.t_id).select(
fn.Extract(DatePart.year, self.t.foo))
self.assertEqual(
'SELECT EXTRACT(YEAR FROM "abc"."foo") FROM "abc" '
'JOIN "efg" ON "abc"."id"="efg"."t_id"', str(q))
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"')
