class TypesModel(Model):
id = t.Auto(comment='primary_key')
tinyint = t.Tinyint(1, unsigned=True, zerofill=True, comment='tinyint')
smallint = t.Smallint(null=False, default=0, comment='smallint')
int_ = t.Int(unsigned=True, null=False, default=0, comment='int')
bigint = t.Bigint(45, null=False, default=0, comment='bigint')
text = t.Text(encoding=t.ENCODING.UTF8MB4, null=False, comment='text')
char = t.Char(45, null=False, default='', comment='char')
varchar = t.VarChar(45, null=False, default='', comment='varchar')
uuid = t.UUID(comment='uuid test')
float_ = t.Float((3, 3), default=0, comment='float')
double_ = t.Double((4, 4), unsigned=True, default=0, comment='double')
decimal = t.Decimal((10, 2), unsigned=True, default=0, comment='decimal')
ip = t.IP(default=0)
email = t.Email(length=100, default='')
url = t.URL(default='')
time_ = t.Time(default=datetime.now, comment='time')
date_ = t.Date(default=datetime.now, comment='date')
datetime_ = t.DateTime(default=datetime.now, comment='datetime')
now_ts = t.Timestamp(default=datetime.now, comment='now ts')
created_at = t.Timestamp(default=t.ON_CREATE, comment='created_at')
updated_at = t.Timestamp(default=t.ON_UPDATE, comment='updated_at')
class Meta:
name = 'test_types_table'
comment = 'type case table'
auto_increment = 7
indexes = (
t.K('key', ['tinyint', 'datetime_'], comment='key test'),
t.UK('ukey', 'varchar', comment='unique key test'),
)
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_int():
int_ = t.Int(name='int', comment="int")
assert parsef(int_) == "`int` int(11) DEFAULT NULL COMMENT 'int';"
try:
int_ = t.Int(name='int', default='xxx')
assert parsef(int_)
assert False, "Should raise TypeError"
except TypeError:
pass
try:
int_ = t.Int(name='int', primary_key=True, default=1)
assert False, "Should raise ProgrammingError"
except err.ProgrammingError:
pass
try:
int_ = t.Int(name='int', auto=True)
assert False, "Should raise ProgrammingError"
except err.ProgrammingError:
pass
def test_key():
age = t.Int(name='age')
name = t.Char(name='name')
password = t.VarChar(name='password')
key = t.K('idx_name', name)
assert str(key) == 'KEY `idx_name` (`name`);'
key = t.K('idx_name_age', (name, age))
assert str(key) == 'KEY `idx_name_age` (`name`, `age`);'
key = t.K('idx_name_age', ('name', 'age'))
assert str(key) == 'KEY `idx_name_age` (`name`, `age`);'
key = t.UK('uk_password', password, comment='password')
assert str(key) == "UNIQUE KEY `uk_password` (`password`) COMMENT 'password';"
assert repr(key) == "<types.UK(UNIQUE KEY `uk_password` (`password`) COMMENT 'password';)>"
assert hash(key) == hash('uk_password')
try:
t.K('idx_name', [1, 2, 4])
assert False
except TypeError:
pass
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",))
class TM3(Model):
class Meta:
indexes = ['idx', 1]
tp = t.Int()
class TM2(Model):
class Meta:
indexes = 'idx'
tp = t.Int()
class TM1(Model):
tp = t.Int()