def test_funs():
age = t.Int(name='age')
s = t.F.SUM(age).as_('age_sum')
assert _builder.parse(s).sql == 'SUM(`age`) AS `age_sum`;'
m_ = t.F.MAX(age).as_('age_max')
assert _builder.parse(m_).sql == 'MAX(`age`) AS `age_max`;'
def test_text():
text = t.Text(name='text', encoding=t.ENCODING.utf8mb4)
assert parsef(text) == "`text` text CHARACTER SET utf8mb4 NULL;"
assert hasattr(text, 'default') is False
try:
text = t.Text(name='text', encoding='utf7')
assert False
except ValueError:
pass
assert text.py_value(100) == '100'
assert text.db_value(100) == '100'
e = text + 'text'
assert _builder.parse(e) == _builder.Query('(`text` || %s);', ('text',))
e = 'text' + text
assert _builder.parse(e) == _builder.Query('(%s || `text`);', ('text',))
def test_exprs():
age = t.Int(name='age')
name = t.Char(name='name')
password = t.VarChar(name='password')
lastlogin = t.DateTime(name='lastlogin', default=datetime.now)
e = (age > 20) & True
assert _builder.parse(e) == _builder.Query(
'((`age` > %s) AND %s);', (20, True)
)
e = True & (age > 10)
assert _builder.parse(e) == _builder.Query(
'(%s AND (`age` > %s));', (True, 10,)
)
e = False | (age > 10)
assert _builder.parse(e) == _builder.Query(
'(%s OR (`age` > %s));', (False, 10,)
)
e = (age > 10) | (name == 'test')
assert _builder.parse(e) == _builder.Query(
'((`age` > %s) OR (`name` = %s));', (10, 'test')
)
e = (name == 'test') | (age > 10)
assert _builder.parse(e) == _builder.Query(
'((`name` = %s) OR (`age` > %s));', ('test', 10)
)
theday = datetime(year=2019, month=10, day=10)
e = (name == 'test') | (lastlogin < theday)
assert _builder.parse(e) == _builder.Query(
'((`name` = %s) OR (`lastlogin` < %s));', ('test', theday)
)
e = lastlogin <= "2019-10-10"
assert _builder.parse(e) == _builder.Query(
'(`lastlogin` <= %s);', (theday,)
)
e = age + 1
assert _builder.parse(e) == _builder.Query(
'(`age` + %s);', (1,)
)
e = 1 + age
assert _builder.parse(e) == _builder.Query(
'(%s + `age`);', (1,)
)
e = age + '20'
assert _builder.parse(e) == _builder.Query(
'(`age` + %s);', (20,)
)
e = 20 + age
assert _builder.parse(e) == _builder.Query(
'(%s + `age`);', (20,)
)
e = name + 'name'
assert _builder.parse(e) == _builder.Query(
'(`name` || %s);', ('name',)
)
e = 'name' + name
assert _builder.parse(e) == _builder.Query(
'(%s || `name`);', ('name',)
)
nickname = t.VarChar(name='nickname')
e = nickname + name
assert _builder.parse(e) == _builder.Query(
'(`nickname` || `name`);', ()
)
e = age - 1
assert _builder.parse(e) == _builder.Query(
'(`age` - %s);', (1,)
)
e = 100 - age
assert _builder.parse(e) == _builder.Query(
'(%s - `age`);', (100,)
)
e = age * '2'
assert _builder.parse(e) == _builder.Query(
'(`age` * %s);', (2,)
)
e = 2 * age
assert _builder.parse(e) == _builder.Query(
'(%s * `age`);', (2,)
)
e = 1000 / age
assert _builder.parse(e) == _builder.Query(
'(%s / `age`);', (1000,)
)
e = age / 2
assert _builder.parse(e) == _builder.Query(
'(`age` / %s);', (2,)
)
e = age ^ name
assert _builder.parse(e) == _builder.Query(
'(`age` # `name`);', ()
)
e = 'name' ^ name
assert _builder.parse(e) == _builder.Query(
'(%s # `name`);', ('name',)
)
e = name == 'at7h'
assert _builder.parse(e) == _builder.Query(
'(`name` = %s);', ('at7h',)
)
e = name != 'at7h'
assert _builder.parse(e) == _builder.Query(
'(`name` != %s);', ('at7h',)
)
e = name <= 'at7h'
assert _builder.parse(e) == _builder.Query(
'(`name` <= %s);', ('at7h',)
)
e = name >= 'at7h'
assert _builder.parse(e) == _builder.Query(
'(`name` >= %s);', ('at7h',)
)
e = age < 90
assert _builder.parse(e) == _builder.Query(
'(`age` < %s);', (90,)
)
e = age > 20
assert _builder.parse(e) == _builder.Query(
'(`age` > %s);', (20,)
)
e = name >> None
assert _builder.parse(e) == _builder.Query(
'(`name` IS %s);', (None,)
)
e = name << ['at7h', 'mejer']
assert _builder.parse(e) == _builder.Query(
'(`name` IN %s);', (('at7h', 'mejer'),)
)
e = name % 'at'
assert _builder.parse(e) == _builder.Query(
'(`name` LIKE BINARY %s);', ('at',)
)
e = name ** 'at'
assert _builder.parse(e) == _builder.Query(
'(`name` LIKE %s);', ('at',)
)
e = age[slice(20, 30)]
assert _builder.parse(e) == _builder.Query(
'(`age` BETWEEN %s AND %s);', (20, 30,)
)
e = age[10]
assert _builder.parse(e) == _builder.Query(
'(`age` = %s);', (10,)
)
try:
e = age[slice(20)]
_builder.parse(e)
assert False, "Should raise ValueError"
except ValueError:
pass
e = name.concat(10)
assert _builder.parse(e) == _builder.Query(
'(`name` || %s);', ('10',)
)
e = name.binand('at7h')
assert _builder.parse(e) == _builder.Query(
'(`name` & %s);', ('at7h',)
)
e = name.binor('at7h')
assert _builder.parse(e) == _builder.Query(
'(`name` | %s);', ('at7h',)
)
e = name.in_(['at7h', 'mejor'])
assert _builder.parse(e) == _builder.Query(
'(`name` IN %s);', (('at7h', 'mejor'),)
)
e = name.in_(_builder.SQL("SELECT * FROM `user`"))
assert _builder.parse(e) == _builder.Query(
'(`name` IN (SELECT * FROM `user`));', ()
)
e = name.in_(10)
try:
_builder.parse(e)
assert False, "Should raise TypeError"
except TypeError:
pass
e = name.nin_(['at7h', 'mejor'])
assert _builder.parse(e) == _builder.Query(
'(`name` NOT IN %s);', (('at7h', 'mejor'),)
)
e = name.exists(['at7h', 'mejor'])
assert _builder.parse(e) == _builder.Query(
'(`name` EXISTS %s);', (('at7h', 'mejor'),)
)
e = name.nexists(['at7h', 'mejor'])
assert _builder.parse(e) == _builder.Query(
'(`name` NOT EXISTS %s);', (('at7h', 'mejor'),)
)
e = name.isnull()
assert _builder.parse(e) == _builder.Query(
'(`name` IS %s);', (None,)
)
e = name.isnull(False)
assert _builder.parse(e) == _builder.Query(
'(`name` IS NOT %s);', (None,)
)
e = name.regexp('at.*')
assert _builder.parse(e) == _builder.Query(
'(`name` REGEXP %s);', ('at.*',)
)
e = name.regexp('at.*', i=False)
assert _builder.parse(e) == _builder.Query(
'(`name` REGEXP BINARY %s);', ('at.*',)
)
e = password.like(177)
assert _builder.parse(e) == _builder.Query(
'(`password` LIKE %s);', ('177',)
)
e = password.like(177, i=False)
assert _builder.parse(e) == _builder.Query(
'(`password` LIKE BINARY %s);', ('177',)
)
e = password.contains(7867)
assert _builder.parse(e) == _builder.Query(
'(`password` LIKE %s);', ('%7867%',)
)
e = password.contains(7867, i=False)
assert _builder.parse(e) == _builder.Query(
'(`password` LIKE BINARY %s);', ('%7867%',)
)
e = name.endswith('7h')
assert _builder.parse(e) == _builder.Query(
'(`name` LIKE %s);', ('%7h',)
)
e = name.endswith('7h', i=False)
assert _builder.parse(e) == _builder.Query(
'(`name` LIKE BINARY %s);', ('%7h',)
)
e = name.startswith('at')
assert _builder.parse(e) == _builder.Query(
'(`name` LIKE %s);', ('at%',)
)
e = name.startswith('at', i=False)
assert _builder.parse(e) == _builder.Query(
'(`name` LIKE BINARY %s);', ('at%',)
)
e = age.between(10, 30)
assert _builder.parse(e) == _builder.Query(
'(`age` BETWEEN %s AND %s);', (10, 30)
)
e = age.nbetween(10, 30)
assert _builder.parse(e) == _builder.Query(
'(`age` NOT BETWEEN %s AND %s);', (10, 30)
)
e = age.asc()
assert _builder.parse(e) == _builder.Query(
'`age` ASC ;', ()
)
e = age.desc()
assert _builder.parse(e) == _builder.Query(
'`age` DESC ;', ()
)
e = age.as_('a')
assert _builder.parse(e) == _builder.Query(
'`age` AS `a`;', ()
)
e = age.as_('')
assert _builder.parse(e) == _builder.Query(
'`age`;', ()
)
e = (age > 10) & (name == 'test')
assert _builder.parse(e) == _builder.Query(
'((`age` > %s) AND (`name` = %s));', (10, 'test')
)
e = (name == 'test') & (age > 10)
assert _builder.parse(e) == _builder.Query(
'((`name` = %s) AND (`age` > %s));', ('test', 10)
)
e = (age >= '20') & name.in_(['at7h', 'mejor']) | password.startswith('153')
assert _builder.parse(e) == _builder.Query(
'(((`age` >= %s) AND (`name` IN %s)) OR (`password` LIKE %s));',
(20, ('at7h', 'mejor'), '153%')
)
# _builder
sql = _builder.SQL("SELECT")
assert repr(sql) == str(sql) == 'SQL(SELECT)'
sql = _builder.SQL("SELECT * FROM `user` WHERE `id` IN %s", (1, 2, 3))
assert repr(sql) == str(sql) == (
'SQL(SELECT * FROM `user` WHERE `id` IN %s) % (1, 2, 3)')
assert _builder.parse(sql) == _builder.Query(
"SELECT * FROM `user` WHERE `id` IN %s;", (1, 2, 3)
)
q = _builder.Query("SELECT")
assert repr(q) == "Query({})".format(str(q))
try:
q.r = 1
assert False, "Should raise TypeError"
except TypeError:
pass
try:
assert _builder.parse((age > 10) | (name == 'test')) == sql
assert False, "Should raise TypeError"
except TypeError:
pass
assert q.r is True
q.r = False
assert q.r is False
q = _builder.Query("SeLeCT FrOm")
assert q.r is True
q = _builder.Query("SShow")
assert q.r is True
q = _builder.Query("SSow")
assert q.r is False
try:
assert _builder.Query("SELECT", {1: 1}).params
assert False, 'Should raise TypeError'
except TypeError:
pass
ctx = _builder.Context()
ctx.literal("SELECT").values("100")
assert _builder.parse(ctx) == _builder.Query('SELECT;', ("100",))
def parsef(field):
return _builder.parse(field.__def__()).sql
def test_model():
from helo.model import get_attrs, get_table
assert isinstance(People.id, t.Auto)
assert _builder.parse(
People.name.__def__()).sql == ('`name` varchar(45) DEFAULT NULL;')
table = get_table(People)
assert table.name == 'people'
assert isinstance(table.indexes, list)
assert len(table.indexes) == 1
assert table.indexes[0].name == 'idx_name'
assert table.db is None
assert table.auto_increment == 1
assert table.engine == ENGINE.innodb
assert table.charset == ENCODING.UTF8MB4
assert table.comment == ''
assert table.primary.auto is True
assert get_attrs(People) == ({
'age': 'age',
'create_at': 'create_at',
'gender': 'gender',
'id': 'id',
'name': 'name',
'update_at': 'update_at'
})
assert isinstance(People.Meta.indexes, list)
table = get_table(Employee)
assert table.name == 'employee'
assert isinstance(table.indexes, list)
assert len(table.indexes) == 1
assert table.indexes[0].name == 'idx_age_salary'
assert table.comment == ''
assert table.primary.auto is True
assert get_attrs(Employee) == ({
'age': 'age',
'create_at': 'create_at',
'departmentid': 'departmentid',
'gender': 'gender',
'id': 'id',
'name': 'name',
'phone': 'phone',
'email': 'email',
'salary': 'salary',
'update_at': 'update_at'
})
table = get_table(User)
assert isinstance(table.indexes, tuple)
assert len(table.indexes) == 2
assert table.name == 'user_'
assert table.indexes[0].name == 'idx_name'
assert table.indexes[1].name == 'unidx_nickname'
assert get_attrs(User) == ({
'age': 'age',
'create_at': 'create_at',
'gender': 'gender',
'id': 'id',
'loginat': 'lastlogin',
'name': 'name',
'nickname': 'nickname',
'pwd': 'password',
'role': 'role',
'update_at': 'update_at'
})
assert len(get_table(User).fields_dict) == 10
assert isinstance(User.nickname, t.VarChar)
assert isinstance(User.password, t.VarChar)
assert isinstance(User.lastlogin, t.DateTime)
assert User.Meta.name == 'user_'
assert isinstance(User.Meta.indexes, tuple)
try:
People.id = 12123123
People.name = 'at7h'
del People.name
assert False, 'Should raise NotAllowedError'
except err.NotAllowedError:
pass
try:
del People.name
assert False, 'Should raise NotAllowedError'
except err.NotAllowedError:
pass
try:
class TM(People):
id_ = t.BigAuto()
assert False, "Should raise DuplicatePKError"
except err.DuplicatePKError:
pass
try:
class TM1(Model):
tp = t.Int()
assert False, "Should raise NoPKError"
except err.NoPKError:
pass
try:
class TM2(Model):
class Meta:
indexes = 'idx'
tp = t.Int()
assert False, "Should raise TypeError"
except TypeError:
pass
try:
class TM3(Model):
class Meta:
indexes = ['idx', 1]
tp = t.Int()
assert False, "Should raise TypeError"
except TypeError:
pass
class TM4(Model):
pk = t.Auto()
assert get_table(TM4).name == 'tm4'
assert repr(User) == "Model<User>"
assert str(User) == "User"
assert str(get_table(People)) == 'people'
assert repr(get_table(People)) == '<Table `people`>'
assert repr(get_table(User)) == '<Table `helo`.`user_`>'
assert hash(User) == hash(User()) == hash('helo.user_')
try:
get_table({})
assert False, "Should raise err.ProgrammingError"
except err.ProgrammingError:
pass
try:
get_attrs(None)
assert False, "Should raise err.ProgrammingError"
except err.ProgrammingError:
pass