def test_select_where(self):
"""Test the WHERE part of DBSelect"""
select = DBSelect('test_table')
select.where('"col_a" = ?', 1)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" WHERE ("col_a" = ?)'
)
self.assertEqual(len(select.query().fetchall()), 1)
select.or_where('"col_a" = ?', 2)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" ' +
'WHERE ("col_a" = ?) OR ("col_a" = ?)'
)
self.assertEqual(len(select.query().fetchall()), 2)
select.where('"col_a" IN (?)', (3, 4, 5))
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* ' +
'FROM "test_table" ' +
'WHERE ("col_a" = ?) ' +
'OR ("col_a" = ?) ' +
'AND ("col_a" IN (?, ?, ?))'
)
self.assertEqual(len(select.query().fetchall()), 1)
def test_save(self):
"""Test ExtendedModel.save"""
pk = SampleExtModel({
'static': 'static_value',
'sample_attr_1': 57,
'sample_attr_2': 'fifty-seven',
'sample_attr_3': 57.0
}).save().id()
select = DBSelect(('sample_attribute_integer', 'v'), ('value', )).join(
('attribute', 'a'), 'v.attribute = a._id',
()).where('a.code = ?',
'sample_attr_1').where('a.parent = ?', 'sample').where(
'a.type = ?', 'integer').where('v.parent = ?',
pk).limit(1)
self.assertEqual(select.query().fetchone()['value'], 57)
select = DBSelect(('sample_attribute_text', 'v'), ('value', )).join(
('attribute', 'a'), 'v.attribute = a._id',
()).where('a.code = ?',
'sample_attr_2').where('a.parent = ?', 'sample').where(
'a.type = ?', 'text').where('v.parent = ?',
pk).limit(1)
self.assertEqual(select.query().fetchone()['value'], 'fifty-seven')
select = DBSelect(('sample_attribute_real', 'v'), ('value', )).join(
('attribute', 'a'), 'v.attribute = a._id',
()).where('a.code = ?',
'sample_attr_3').where('a.parent = ?', 'sample').where(
'a.type = ?', 'real').where('v.parent = ?',
pk).limit(1)
# should fail since 'sample_attr_3' is in another group
self.assertIsNone(select.query().fetchone())
def test_select_limit(self):
"""Test LIMIT and OFFSET parts"""
select = DBSelect('test_table')
select.limit(10)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" LIMIT 10'
)
self.assertEqual(len(select.query().fetchall()), 10)
select.limit(10, self._num_rows - 5)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" LIMIT %d, 10'
% (self._num_rows - 5,)
)
self.assertEqual(len(select.query().fetchall()), 5)
def test_save(self):
"""Test ExtendedModel.save"""
pk = SampleExtModel({
'static': 'static_value',
'sample_attr_1': 57,
'sample_attr_2': 'fifty-seven',
'sample_attr_3': 57.0
}).save().id()
select = DBSelect(
('sample_attribute_integer', 'v'),
('value',)
).join(('attribute', 'a'), 'v.attribute = a._id', ()
).where('a.code = ?', 'sample_attr_1'
).where('a.parent = ?', 'sample'
).where('a.type = ?', 'integer'
).where('v.parent = ?', pk
).limit(1)
self.assertEqual(select.query().fetchone()['value'], 57)
select = DBSelect(
('sample_attribute_text', 'v'),
('value',)
).join(('attribute', 'a'), 'v.attribute = a._id', ()
).where('a.code = ?', 'sample_attr_2'
).where('a.parent = ?', 'sample'
).where('a.type = ?', 'text'
).where('v.parent = ?', pk
).limit(1)
self.assertEqual(select.query().fetchone()['value'], 'fifty-seven')
select = DBSelect(
('sample_attribute_real', 'v'),
('value',)
).join(('attribute', 'a'), 'v.attribute = a._id', ()
).where('a.code = ?', 'sample_attr_3'
).where('a.parent = ?', 'sample'
).where('a.type = ?', 'real'
).where('v.parent = ?', pk
).limit(1)
# should fail since 'sample_attr_3' is in another group
self.assertIsNone(select.query().fetchone())
def test_select_order(self):
"""Test the ORDER BY part of DBSelect"""
self._helper.query('CREATE TABLE a (x INTEGER, y INTEGER)')
self._helper.insert('a', (
{'x': 1, 'y': 1},
{'x': 2, 'y': 2},
{'x': 3, 'y': 2},
))
select = DBSelect('a').order('x')
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "a".* FROM "a" ORDER BY "x" ASC'
)
self.assertEqual(select.query().fetchone()['x'], 1)
select = DBSelect('a').order('y', 'DESC').order('x')
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "a".* FROM "a" ORDER BY "y" DESC, "x" ASC'
)
self.assertEqual(select.query().fetchone()['x'], 2)
def get_all_filters(cls):
"""Present filters and options in a json encodable structure"""
filters = []
for row in DBSelect(cls._filter_table).query().fetchall():
filter_object = {
'param': row['code'],
'label': row['label'],
'multi': True,
'options': {}
}
opt_select = DBSelect(cls._filter_option_table)
opt_select.where('filter = ?', row['_id'])
for opt in opt_select.query().fetchall():
filter_object['options'][opt['value']] = opt['label']
filters.append(filter_object)
return filters
def get_all_filters(cls):
"""Present filters and options in a json encodable structure"""
filters = []
for row in DBSelect(cls._filter_table).query().fetchall():
filter_object = {
'param': row['code'],
'label': row['label'],
'multi': True,
'options': {}
}
opt_select = DBSelect(cls._filter_option_table)
opt_select.where('filter = ?', row['_id'])
for opt in opt_select.query().fetchall():
filter_object['options'][opt['value']] = opt['label']
filters.append(filter_object)
return filters
def _get_attribute_group(cls, group_id, create=False):
"""Retrieve an attribute group id"""
if (cls._table in cls._group_index
and group_id in cls._group_index[cls._table]):
return cls._group_index[cls._table][group_id]
group_desc = {
'_id': group_id,
'code': str(group_id).replace(' ', '_'),
'label': str(group_id.replace('_', ' ')).capitalize()
}
cls._group_index[cls._table] = {}
for field in ('_id', 'code', 'label'):
select = DBSelect('attribute_group').where(
'%s = ?' % field, group_desc[field]).where(
'type = ?', cls._table).limit(1)
group = select.query().fetchone()
if type(group) is dict:
cls._group_index[cls._table][group_id] = group['_id']
return cls._group_index[cls._table][group_id]
if not create:
return None
ids = DBHelper().insert('attribute_group', {
'code': group_desc['code'],
'label': group_desc['label'],
'type': cls._table
})
if len(ids) > 0:
cls._group_index[cls._table][group_id] = ids[0]
return cls._group_index[cls._table][group_id]
return None
def get_all_attributes(cls, group=None):
"""Get attributes related to this model class"""
parent = cls._table
if not parent:
return None
cache_key = 'ATTRIBUTES_%s' % parent
if group is not None:
group = cls._get_attribute_group(group)
if group is None:
return None
cache_key = '%s_%d' % (cache_key, group)
attributes = cls._cache.get(cache_key)
if type(attributes) is list:
return attributes
select = DBSelect(('attribute_group', 'g'), ()
).inner_join(
('attribute_group_attribute', 'ga'),
'"g"."_id" = "ga"."group"', ()
).inner_join(
('attribute', 'a'),
'"ga"."attribute" = "a"."_id"'
).where('"a"."parent" = ?', parent)
if group is not None:
select.where('"g"."_id" = ?', group)
attributes = select.query().fetchall()
if type(attributes) is list:
cls._cache.set(cache_key, attributes)
return attributes
