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