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"')