def test_array(session):
data = {
'int': [(1, [1, 2])],
'float': [(1, [1.0, 2.0])],
'bool': [(1, [True, False])],
# TODO add timestamp & date
}
for kind in data:
datum = data[kind]
view = View('kitchensink', ['index', '%s_array' % kind])
view.write(datum)
res = view.read().all()
assert res == datum
if ctx.flavor == 'sqlite':
return
# postgres-specific operations
flrt = '(= 1 (any int_array))'
res = view.read(flrt).all()
assert len(res) == 1
flrt = '(!= 3 (all int_array))'
res = view.read(flrt).all()
assert len(res) == 1
res = View('kitchensink', ['index', '(unnest int_array)']).read().all()
assert len(res) == 2
def test_partial_write(session):
'''
We want to update only some columns
'''
# member table is empty by default
full_view = View(
'member',
['name', 'team.country.name', 'team.name', 'registration_code'])
full_view.write(members)
# Collect ids and name
name_view = View('member', ['id', 'name'])
id2name = dict(name_view.read().all())
partial_view = View('member', ['name', 'registration_code'])
partial_view.write([['Bob', '001']])
# Makes sur no other column is set to null
res = full_view.read('(= name "Bob")').one()
assert all(res)
# compare ids
for member_id, name in name_view.read():
assert id2name[member_id] == name
def test_purge_filters(session):
teams = [
['Red', 'Belgium'],
] # Blue-Belgium is missing
fltr = '(= country.name "Belgium")' # We restrict writes to belgium
team_view = View('team', ['name', 'country.name'])
team_view.write(teams, purge=True, filters=fltr)
expected = [(
'Red',
'Belgium',
), (
'Blue',
'France',
)]
res = team_view.read()
check(expected, res)
# Opposite filter
fltr = '(!= country.name "Belgium")' # We don't purge belgium
team_view.write(teams, purge=True, filters=fltr)
expected = [(
'Red',
'Belgium',
)]
res = team_view.read()
check(expected, res)
def test_update_relation_filter(session):
# Add all members to table
inject('member', 'acl-write', [])
view = View('member', ['registration_code', 'name'])
View('member', member_cols).write(members)
# Test update with filter on relation
inject('member', 'acl-write', ['(= team.name "Blue")'])
view = View('member', ['registration_code', 'name'])
view.write([
('001', 'BOB'),
('002', 'ALICE'),
])
res = view.read('(in registration_code "001" "002")').all()
assert sorted(res) == [('001', 'BOB'), ('002', 'Alice')]
# Nasty test, when we change the value of the column supporting the filter
view = View('member',
['registration_code', 'team.name', 'team.country.name'])
view.write([
('001', 'Red', 'Belgium'), # Blue to Red transition
('002', 'Blue', 'Belgium'), # Red to Blue transition
])
res = view.read('(in registration_code "001" "002")').all()
assert sorted(res) == [('001', 'Blue', 'Belgium'),
('002', 'Red', 'Belgium')]
def test_read_write(session):
view = View('country', {'Name': 'name'})
db_df = view.read().df()
new_df = DataFrame({'Name': ['Italy']})
view.write(new_df)
updated_df = view.read().df()
expected = db_df.append(new_df).reset_index(drop=True)
assert all(expected == updated_df)
def test_reserved_words(session):
record = {
'index': 1,
'true': True,
'false': False,
'null': None,
'integer': 1,
'bigint': 10000000000,
'float': 1.0,
'bool': True,
'timestamp': datetime(1970, 1, 1),
'date': date(1970, 1, 1),
'varchar': 'varchar',
'bytea': b'\x00',
'int_array': [1, 2],
'bool_array': [True, False],
'ts_array': [datetime(1970, 1, 1),
datetime(1970, 1, 2)],
'char_array': ['ham', 'spam'],
'jsonb': '{"ham": "spam"}',
}
# Write actual values
ks_view = View('kitchensink', list(record.keys()))
ks_view.write([record])
res = list(ks_view.read().dict())[0]
for k, v in record.items():
if ctx.flavor == 'sqlite' and k.endswith('array'):
# Array support with sqlite is incomplete
continue
if k == 'bytea':
assert bytes(res[k]) == v
elif k == 'jsonb':
assert res[k] == json.loads(v)
else:
assert res[k] == v
# Filters
for k, v in record.items():
if isinstance(v, list):
continue
cond = '(is %s {})' if k == 'null' else '(= %s {})'
res = ks_view.read(cond % k, args=[v]).all()
assert len(res) == 1
# Write nulls
for k in record:
if k == 'index':
continue
record[k] = None
ks_view.write([record])
res = list(ks_view.read().dict())[0]
for k, v in record.items():
assert res[k] == v
def test_delete_data_extra_col(session):
full_view = View(
'member',
['name', 'team.country.name', 'team.name', 'registration_code'])
full_view.write(members)
assert len(full_view.read().all()) == len(members)
full_view.delete(data=members)
res = full_view.read().all()
assert res == []
def test_args(session):
# Add config value, to use it later
ctx.cfg['cfg_team'] = 'Red'
view = View('team', ['name'])
# Simple test
cond = '(= name {name})'
rows = view.read(cond).args(name='Blue')
assert sorted(rows) == [('Blue', ), ('Blue', )]
# Simple test explicit position
cond = '(= name {0})'
rows = view.read(cond).args('Red')
assert sorted(rows) == [('Red', )]
cond = '(or (= name {0}) (= name {1}))'
args = ['Red', 'Blue']
rows = view.read(cond, args=args)
assert sorted(rows) == [('Blue', ), ('Blue', ), ('Red', )]
# test params are unafected
assert args == ['Red', 'Blue']
# Simple test, implicit position
cond = '(= name {})'
rows = view.read(cond).args('Red')
assert sorted(rows) == [('Red', )]
cond = '(or (= name {}) (= name {}))'
args = ['Red', 'Blue']
rows = view.read(cond, args=args)
# test output
assert sorted(rows) == [('Blue', ), ('Blue', ), ('Red', )]
# test params are unafected
assert args == ['Red', 'Blue']
# Mix value from config
cond = '(in name {cfg_team})'
rows = view.read(cond)
assert sorted(rows) == [('Red', )]
# Test with a list in args
cond = '(in name {names})'
rows = view.read(cond).args(names=['Red', 'Blue'])
assert sorted(rows) == [('Blue', ), ('Blue', ), ('Red', )]
# Test with an object
cond = '(in name {obj.name})'
class Obj:
pass
obj = Obj()
obj.name = 'Blue'
rows = view.read(cond).args(obj=obj)
assert sorted(rows) == [('Blue', ), ('Blue', )]
# Test with a dict
cond = '(in name {data.name})'
data = {'name': 'Red'}
rows = view.read(cond).args(data=data)
assert sorted(rows) == [('Red', )]
def test_write_by_id(session):
country_view = View('country', ['id', 'name'])
res = country_view.read('(= name "Belgium")').one()
record_id = res[0]
res = country_view.write([(record_id, 'BELGIUM')])
res = country_view.read('(= name "Belgium")').one()
assert res is None
res = country_view.read('(= name "BELGIUM")').one()
assert res[0] == record_id
def test_str(session):
japan = '日本'
team_view = View('country', ['name'])
team_view.write([(japan, )])
row = team_view.read(filters={'name': japan}).one()
assert row[0] == japan
fltr = '(= name "%s")' % japan
row = team_view.read(fltr).one()
assert row[0] == japan
def test_delete_data_id(session):
# Not sure why sqlite fail on this one
if ctx.flavor == 'sqlite':
return
view = View('country', ['id'])
data = view.read('(!= name "Belgium")').all()
view.delete(data=[[i] for i, in data])
res = view.read().all()
assert len(res) == 1
def test_like_ilike(session):
view = View('country', ['name'])
fltr = '(like name "%e%")'
res = view.read(fltr).all()
assert res == [('Belgium', ), ('France', )]
fltr = '(ilike name "H%")'
res = view.read(fltr).all()
assert res == [('Holland', )]
fltr = '(ilike name {prefix})'
res = view.read(fltr, args={'prefix': 'H%'}).all()
assert res == [('Holland', )]
def test_unicode(session):
korea = u'Corée'
if PY2:
korea = korea.encode('utf-8')
team_view = View('country', ['name'])
team_view.write([(korea, )])
row = team_view.read(filters={'name': korea}).one()
assert row[0] == korea
fltr = '(= name "%s")' % korea
row = team_view.read(fltr).one()
assert row[0] == korea
def test_jsonb(session):
data = [(1, {'ham': 'spam'})]
view = View('kitchensink', ['index', 'jsonb'])
view.write(data)
res = view.read().all()
assert res[0][1]['ham'] == 'spam'
if ctx.flavor == 'sqlite':
return
# postgres-specific operator
flrt = '(= "spam" (->> jsonb "ham"))'
res = view.read(flrt).all()
assert len(res) == 1
assert res[0][1]['ham'] == 'spam'
def test_mixed(session):
view = View('country', ['name'])
view.write([('Italy', )])
countries = [c for c, in view.read()]
in_q = Queue()
out_q = Queue()
# Needed because table creation and content is not committed yet
ctx.connection.commit()
# We re-use the current config to create a nested context
with connect(ctx.cfg):
t = TankerThread(target=read, args=(in_q, out_q))
t.start()
res = []
for _ in countries:
in_q.put('tic')
res.append(out_q.get()[1])
# Release thread loop & wait for it
in_q.put('tic')
t.join()
assert 'Italy' in res
assert res == countries
def test_update_filters(session):
# init members
full_view = View(
'member',
['name', 'team.country.name', 'team.name', 'registration_code'])
full_view.write(members)
# Let's update some names (the index is registration_code)
fltr = '(= registration_code "001")'
member_view = View('member', ['registration_code', 'name'])
data = [
('001', 'BOB'),
('003', 'TRUDY'),
]
cnt = member_view.write(data, filters=fltr)
assert cnt['filtered'] == 1
expected = [
(
'001',
'BOB',
),
('002', 'Alice'),
('003', 'Trudy'),
]
res = member_view.read()
check(expected, res)
def test_lru(session):
tanker.LRU_SIZE = 10
tanker.LRU_PAGE_SIZE = 5
factor = 3
nb_record = tanker.LRU_SIZE * factor
values = [('c%s' % i, ) for i in range(nb_record)]
country_view = View('country', ['name'])
team_view = View('team', ['name', 'country.name'])
# Fill country table, clean team table
country_view.write(values)
team_view.delete()
# Fill team table to trigger lru on country fk
values = [(
't%s' % i,
'c%s' % i,
) for i in range(nb_record)]
seed(1) # Reset seed to get determinism
shuffle(values)
team_view.write(values)
teams = team_view.read().all()
assert len(teams) == nb_record
for team_name, country_name in teams:
assert team_name[0] == 't'
assert country_name[0] == 'c'
assert team_name[1:] == country_name[1:]
def test_filter_args(session):
# init members
full_view = View(
'member',
['name', 'team.country.name', 'team.name', 'registration_code'])
full_view.write(members)
# Let's insert some names (the index is registration_code)
fltr = '(= registration_code {})'
member_view = View('member', ['registration_code', 'name'])
data = [
('004', 'Carol'),
('005', 'Dan'),
]
member_view.write(data, filters=fltr, args=['004'])
expected = [
(
'001',
'Bob',
),
('002', 'Alice'),
('003', 'Trudy'),
('004', 'Carol'),
]
res = member_view.read()
check(expected, res)
def test_limit_order(session):
view = View('country', ['name'])
res = view.read(limit=1, order='name').all()
assert res == [('Belgium', )]
# Provide direction
res = view.read(limit=1, order=('name', 'DESC')).all()
assert res == [('Holland', )]
# Sort on several columns
res = view.read(limit=1, order=['name', 'name']).all()
assert res == [('Belgium', )]
# Sort on expression
res = view.read(limit=1, order=['(!= name "Belgium")']).all()
assert res == [('Belgium', )]
def test_field_eval(session):
view = View('country', ['(= name "Belgium")'])
res = view.read(order='name').all()
assert res == [
(True, ),
(False, ),
(False, ),
]
def test_delete_filter(session):
# Not sure why sqlite fail on this one
if ctx.flavor == 'sqlite':
return
# Use a list of filters
view = View('country', ['name'])
view.delete(['(> id 0 )', '(< id 0)'])
res = view.read(order='name').all()
assert res == [('Belgium', ), ('France', ), ('Holland', )]
# Filter with args
view = View('country', ['name'])
view.delete('(in name {names})', args={'names': ['France', 'Holland']})
res = view.read().all()
assert res == [('Belgium', )]
def test_bytea(session):
payload = b'\x1d\xea\xdb\xee\xff'
data = [(1, payload)]
view = View('kitchensink', ['index', 'bytea'])
view.write(data)
res = view.read().all()
assert bytes(res[0][1]) == payload
def test_manual_conn(session):
country_view = View('country', ['name'])
res = country_view.read({'name': 'Prussia'}).one()
assert res is None
# Needed to not lock other connections
ctx.connection.commit()
# Manually start and stop of the connection
cfg = {'db_uri': session, 'schema': SCHEMA}
connect(cfg, 'enter')
country_view.write([['Prussia']])
connect(cfg, 'leave')
# Makes sure result is not lost
with connect(cfg):
assert country_view.read({'name': 'Prussia'}).one()[0] == 'Prussia'
def test_delete_filter_dict(session):
# Not sure why sqlite fail on this one
if ctx.flavor == 'sqlite':
return
view = View('country', ['name'])
view.delete(filters={'name': 'France'})
res = view.read().all()
assert res == [('Belgium', ), ('Holland', )]
def test_aliases(session):
# Add alias
now = datetime.now()
ctx.aliases.update({'now': now})
view = View('country', ['name', '{now}'])
res = view.read().all()
if ctx.flavor == 'sqlite':
ok = lambda r: r[1] == str(now)
else:
ok = lambda r: r[1] == now
assert all(ok for r in res)
ctx.aliases.update({'type': 'TYPE'})
view = View('country', ['name', '{type}'])
filters = '(= name "France")'
res = view.read(filters).all()
assert res == [('France', 'TYPE')]
def test_chain(session):
expected = ['Belgium', 'France', 'Holland']
res = sorted(View('country', ['name']).read().chain())
assert expected == res
expected = ['Blue', 'Belgium', 'Blue', 'France', 'Red', 'Belgium']
view = View('team', ['name', 'country.name'])
res = view.read(order=['name', 'country.name']).chain()
assert expected == list(res)
def test_delete_by_id(session):
# Not sure why sqlite fail on this one
if ctx.flavor == 'sqlite':
return
view = View('country', ['id'])
data = view.read('(= name "France")').all()
view.delete(data=data)
res = View('country', ['name']).read().all()
assert res == [('Belgium', ), ('Holland', )]
def test_no_fields(session):
# No fields are provided, should fallback to table definition
team_view = View('country')
team_view.write([
('Italy', ),
])
expected = [('Belgium', ), ('Italy', ), ('France', ), ('Holland', )]
res = team_view.read()
check(expected, res)
def test_env(session):
# Part of the expression is member of the env (env is bases on
# view fields names)
fields = {'name': '(max name)'}
view = View('team', fields)
res, = view.read().all()
assert res[0] == 'Red'
#First member if an s-expression should be shielded from env
fields = {'max': '(max name)'}
view = View('team', fields)
res, = view.read().all()
assert res[0] == 'Red'
# Alias is used in order
fields = {'first_name': 'name'}
view = View('team', fields)
fltr = '(= first_name "Blue")'
res, = view.read(fltr, order='first_name', limit=1).all()
assert res[0] == 'Blue'
def test_filters(session):
view = View('team', ['name'])
filters = '(= country.name "France")'
res = view.read(filters).all()
assert res == [('Blue', )]
filters = [
'(= country.name "France")',
'(= country.name "Belgium")',
]
res = view.read(filters).all()
assert res == []
fltr = '(0)' if ctx.flavor == 'sqlite' else '(false)'
res = view.read(fltr).all()
assert res == []
fltr = '(1)' if ctx.flavor == 'sqlite' else '(true)'
res = view.read(fltr).all()
assert len(res) == 3
