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 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_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
