import logging import re from datetime import datetime, timedelta from dateutil.relativedelta import relativedelta from seafevents.repo_metadata.constants import FilterPredicateTypes, FilterTermModifier, PropertyTypes, \ DurationFormatsType, PrivatePropertyKeys, ViewType, FormulaResultType, TAGS_TABLE logger = logging.getLogger(__name__) class SQLGeneratorOptionInvalidError(Exception): pass class DateTimeQueryInvalidError(Exception): def __init__(self, column_name): self.column_name = column_name class ColumnFilterInvalidError(Exception): def __init__(self, column_name, column_type, filter_predicate, support_filter_predicates, msg): self.column_name = column_name self.column_type = column_type self.filter_predicate = filter_predicate self.support_filter_predicates = support_filter_predicates self.msg = msg class Operator(object): def __init__(self, column, filter_item): self.column = column self.filter_item = filter_item self.column_name = '' self.filter_term = '' self.filter_predicate = '' self.filter_term_modifier = '' self.column_type = '' self.column_data = {} self.init() def init(self): self.column_name = self.column.get('name', '') self.column_type = self.column.get('type', '') self.column_data = self.column.get('data', {}) self.filter_predicate = self.filter_item.get('filter_predicate', '') self.filter_term = self.filter_item.get('filter_term', '') self.filter_term_modifier = self.filter_item.get('filter_term_modifier', '') self.case_sensitive = self.filter_item.get('case_sensitive', False) def op_is(self): if not self.filter_term: return "" return "`%s` %s '%s'" % ( self.column_name, '=', self.filter_term ) def op_is_not(self): if not self.filter_term: return "" return "`%s` %s '%s'" % ( self.column_name, '<>', self.filter_term ) def op_contains(self): if not self.filter_term: return "" return "`%s` %s '%%%s%%'" % ( self.column_name, 'like' if self.case_sensitive is True else 'ilike', self.filter_term.replace('\\', '\\\\') # special characters require translation ) def op_does_not_contain(self): if not self.filter_term: return '' return "`%s` %s '%%%s%%'" % ( self.column_name, 'not like' if self.case_sensitive is True else 'not ilike', self.filter_term.replace('\\', '\\\\') # special characters require translation ) def op_equal(self): if not self.filter_term and self.filter_term != 0: return '' return "`%(column_name)s` = %(value)s" % ({ 'column_name': self.column_name, 'value': self.filter_term }) def op_not_equal(self): if not self.filter_term and self.filter_term != 0: return '' return "`%(column_name)s` <> %(value)s" % ({ 'column_name': self.column_name, 'value': self.filter_term }) def op_less(self): if not self.filter_term and self.filter_term != 0: return '' return "`%(column_name)s` < %(value)s" % ({ 'column_name': self.column_name, 'value': self.filter_term }) def op_less_or_equal(self): if not self.filter_term and self.filter_term != 0: return '' return "`%(column_name)s` <= %(value)s" % ({ 'column_name': self.column_name, 'value': self.filter_term }) def op_greater(self): if not self.filter_term and self.filter_term != 0: return '' return "`%(column_name)s` > %(value)s" % ({ 'column_name': self.column_name, 'value': self.filter_term }) def op_greater_or_equal(self): if not self.filter_term and self.filter_term != 0: return '' return "`%(column_name)s` >= %(value)s" % ({ 'column_name': self.column_name, 'value': self.filter_term }) def op_is_empty(self): return "`%(column_name)s` is null" % ({ 'column_name': self.column_name }) def op_is_not_empty(self): return "`%(column_name)s` is not null" % ({ 'column_name': self.column_name }) def op_is_current_user_id(self): if not self.filter_term: return "(`%s`IS NULL AND `%s` IS NOT NULL)" % ( self.column_name, self.column_name ) return "`%s` %s '%s'" % ( self.column_name, '=', self.filter_term ) class TextOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.CONTAINS, FilterPredicateTypes.NOT_CONTAIN, FilterPredicateTypes.IS, FilterPredicateTypes.IS_NOT, FilterPredicateTypes.EMPTY, FilterPredicateTypes.NOT_EMPTY, FilterPredicateTypes.IS_CURRENT_USER_ID, ] def __init__(self, column, filter_item): super(TextOperator, self).__init__(column, filter_item) class NumberOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.EQUAL, FilterPredicateTypes.NOT_EQUAL, FilterPredicateTypes.GREATER, FilterPredicateTypes.GREATER_OR_EQUAL, FilterPredicateTypes.LESS, FilterPredicateTypes.LESS_OR_EQUAL, FilterPredicateTypes.EMPTY, FilterPredicateTypes.NOT_EMPTY, ] def __init__(self, column, filter_item): super(NumberOperator, self).__init__(column, filter_item) if self.column_type == PropertyTypes.DURATION: self.filter_term = self._duration2number() def _duration2number(self): filter_term = self.filter_term column_data = self.column.get('data', {}) if filter_term == 0 or filter_term == '0': return 0 if not filter_term: return '' duration_format = column_data.get('duration_format') if duration_format not in [ DurationFormatsType.H_MM, DurationFormatsType.H_MM_SS, DurationFormatsType.H_MM_SS_S, DurationFormatsType.H_MM_SS_SS, DurationFormatsType.H_MM_SS_SSS ]: return '' try: return int(filter_term) except: duration_str = filter_term is_negtive = duration_str[0] == '-' duration_time = duration_str if is_negtive: duration_time = duration_str[1:] duration_time_split_list = re.split('[:：]', duration_time) hours, minutes, seconds = 0, 0, 0 if duration_format == DurationFormatsType.H_MM: try: hours = int(duration_time_split_list[0]) except: hours = 0 try: minutes = int(duration_time_split_list[1]) except: minutes = 0 else: try: hours = int(duration_time_split_list[0]) except: hours = 0 try: minutes = int(duration_time_split_list[1]) except: minutes = 0 try: seconds = int(duration_time_split_list[2]) except: seconds = 0 if (not hours) and (not minutes) and (not seconds): return '' total_time = 3600 * hours + 60 * minutes + seconds return -total_time if is_negtive else total_time class SingleSelectOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.IS_ANY_OF, FilterPredicateTypes.IS_NONE_OF, FilterPredicateTypes.IS, FilterPredicateTypes.IS_NOT, FilterPredicateTypes.EMPTY, FilterPredicateTypes.NOT_EMPTY, ] def __init__(self, column, filter_item): super(SingleSelectOperator, self).__init__(column, filter_item) def _get_option_name_by_id(self, option_id): options = self.column.get('data', {}).get('options', []) for op in options: if op.get('id') == option_id: return op.get('name') raise SQLGeneratorOptionInvalidError('option is invalid.') def op_is(self): if not self.filter_term: return '' filter_term = self._get_option_name_by_id(self.filter_term) if not filter_term: return '' return "`%s` %s '%s'" % ( self.column_name, '=', filter_term ) def op_is_not(self): if not self.filter_term: return '' filter_term = self._get_option_name_by_id(self.filter_term) if not filter_term: return '' return "`%s` %s '%s'" % ( self.column_name, '<>', filter_term ) def op_is_any_of(self): filter_term = self.filter_term if not filter_term: return '' if not isinstance(filter_term, list): filter_term = [filter_term, ] filter_term = [self._get_option_name_by_id(f) for f in filter_term] option_names = ["'%s'" % (op_name) for op_name in filter_term] if not option_names: return "" return "`%(column_name)s` in (%(option_names)s)" % ({ "column_name": self.column_name, "option_names": ", ".join(option_names) }) def op_is_none_of(self): filter_term = self.filter_term if not filter_term: return '' if not isinstance(filter_term, list): filter_term = [filter_term, ] filter_term = [self._get_option_name_by_id(f) for f in filter_term] option_names = ["'%s'" % (op_name) for op_name in filter_term] if not option_names: return "" return "`%(column_name)s` not in (%(option_names)s)" % ({ "column_name": self.column_name, "option_names": ", ".join(option_names) }) class MultipleSelectOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.HAS_ANY_OF, FilterPredicateTypes.HAS_NONE_OF, FilterPredicateTypes.HAS_ALL_OF, FilterPredicateTypes.IS_EXACTLY, FilterPredicateTypes.EMPTY, FilterPredicateTypes.NOT_EMPTY, ] def __init__(self, column, filter_item): super(MultipleSelectOperator, self).__init__(column, filter_item) def _get_option_name_by_id(self, option_id): options = self.column.get('data', {}).get('options', []) if not options: return option_id for op in options: if op.get('id') == option_id: return op.get('name') raise SQLGeneratorOptionInvalidError('option is invalid') def op_has_any_of(self): if not self.filter_term: return "" filter_term = [self._get_option_name_by_id(f) for f in self.filter_term] option_names = ["'%s'" % op_name for op_name in filter_term] option_names_str = ', '.join(option_names) return "`%(column_name)s` in (%(option_names_str)s)" % ({ "column_name": self.column_name, "option_names_str": option_names_str }) def op_has_none_of(self): if not self.filter_term: return "" filter_term = [self._get_option_name_by_id(f) for f in self.filter_term] option_names = ["'%s'" % op_name for op_name in filter_term] option_names_str = ', '.join(option_names) return "`%(column_name)s` has none of (%(option_names_str)s)" % ({ "column_name": self.column_name, "option_names_str": option_names_str }) def op_has_all_of(self): if not self.filter_term: return "" filter_term = [self._get_option_name_by_id(f) for f in self.filter_term] option_names = ["'%s'" % op_name for op_name in filter_term] option_names_str = ', '.join(option_names) return "`%(column_name)s` has all of (%(option_names_str)s)" % ({ "column_name": self.column_name, "option_names_str": option_names_str }) def op_is_exactly(self): if not self.filter_term: return "" filter_term = [self._get_option_name_by_id(f) for f in self.filter_term] option_names = ["'%s'" % op_name for op_name in filter_term] option_names_str = ', '.join(option_names) return "`%(column_name)s` is exactly (%(option_names_str)s)" % ({ "column_name": self.column_name, "option_names_str": option_names_str }) class DateOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.IS, FilterPredicateTypes.IS_NOT, FilterPredicateTypes.IS_AFTER, FilterPredicateTypes.IS_BEFORE, FilterPredicateTypes.IS_ON_OR_BEFORE, FilterPredicateTypes.IS_ON_OR_AFTER, FilterPredicateTypes.EMPTY, FilterPredicateTypes.NOT_EMPTY, FilterPredicateTypes.IS_WITHIN, ] def __init__(self, column, filter_item): super(DateOperator, self).__init__(column, filter_item) def _get_end_day_of_month(self, year, month): days = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31] if year % 4 == 0 and (year % 100 != 0 or year % 400 == 0): days[1] = 29 return days[month - 1] def _format_date(self, dt): if dt: return dt.strftime("%Y-%m-%d") def _other_date(self): filter_term_modifier = self.filter_term_modifier filter_term = self.filter_term today = datetime.today() year = today.year if filter_term_modifier == FilterTermModifier.TODAY: return today, None if filter_term_modifier == FilterTermModifier.TOMORROW: tomorrow = today + timedelta(days=1) return tomorrow, None if filter_term_modifier == FilterTermModifier.YESTERDAY: yesterday = today - timedelta(days=1) return yesterday, None if filter_term_modifier == FilterTermModifier.ONE_WEEK_AGO: one_week_ago = today - timedelta(days=7) return one_week_ago, None if filter_term_modifier == FilterTermModifier.ONE_WEEK_FROM_NOW: one_week_from_now = today + timedelta(days=7) return one_week_from_now, None if filter_term_modifier == FilterTermModifier.ONE_MONTH_AGO: one_month_ago = today - relativedelta(months=1) return one_month_ago, None if filter_term_modifier == FilterTermModifier.ONE_MONTH_FROM_NOW: one_month_from_now = today + relativedelta(months=1) return one_month_from_now, None if filter_term_modifier == FilterTermModifier.NUMBER_OF_DAYS_AGO: try: filter_term = int(filter_term) except: logger.debug("filter_term is invalid, please assign an integer value of days to filter_term") return None, None try: days_ago = today - timedelta(days=filter_term) except OverflowError: raise DateTimeQueryInvalidError(self.column_name) return days_ago, None if filter_term_modifier == FilterTermModifier.NUMBER_OF_DAYS_FROM_NOW: try: filter_term = int(filter_term) except: logger.debug("filter_term is invalid, please assign an integer value of days to filter_term") return None, None try: days_after = today + timedelta(days=filter_term) except OverflowError: raise DateTimeQueryInvalidError(self.column_name) return days_after, None if filter_term_modifier == FilterTermModifier.EXACT_DATE: try: return datetime.strptime(filter_term, "%Y-%m-%d").date(), None except ValueError: raise DateTimeQueryInvalidError(self.column_name) except: logger.debug("filter_term is invalid, please assign an date value to filter_term, such as YYYY-MM-DD") return None, None if filter_term_modifier == FilterTermModifier.THE_PAST_WEEK: week_day = today.isoweekday() # 1-7 start_date = today - timedelta(days=(week_day + 6)) end_date = today - timedelta(days=week_day) return start_date, end_date if filter_term_modifier == FilterTermModifier.THIS_WEEK: week_day = today.isoweekday() start_date = today - timedelta(days=week_day - 1) end_date = today + timedelta(days=7 - week_day) return start_date, end_date if filter_term_modifier == FilterTermModifier.THE_NEXT_WEEK: week_day = today.isoweekday() start_date = today + timedelta(days=8 - week_day) end_date = today + timedelta(days=14 - week_day) return start_date, end_date if filter_term_modifier == FilterTermModifier.THE_PAST_MONTH: one_month_ago = today - relativedelta(months=1) one_month_ago_year = one_month_ago.year one_month_ago_month = one_month_ago.month one_month_age_end_day = self._get_end_day_of_month(one_month_ago_year, one_month_ago_month) start_date = datetime(one_month_ago_year, one_month_ago_month, 1) end_date = datetime(one_month_ago_year, one_month_ago_month, one_month_age_end_day) return start_date, end_date if filter_term_modifier == FilterTermModifier.THIS_MONTH: current_month = today.month current_year = today.year current_month_end_day = self._get_end_day_of_month(current_year, current_month) start_date = datetime(current_year, current_month, 1) end_date = datetime(current_year, current_month, current_month_end_day) return start_date, end_date if filter_term_modifier == FilterTermModifier.THE_NEXT_MONTH: next_month = today + relativedelta(months=1) next_month_year = next_month.year next_month_month = next_month.month next_month_end_day = self._get_end_day_of_month(next_month_year, next_month_month) start_date = datetime(next_month_year, next_month_month, 1) end_date = datetime(next_month_year, next_month_month, next_month_end_day) return start_date, end_date if filter_term_modifier == FilterTermModifier.THE_PAST_YEAR: last_year = year - 1 start_date = datetime(last_year, 1, 1) end_date = datetime(last_year, 12, 31) return start_date, end_date if filter_term_modifier == FilterTermModifier.THIS_YEAR: start_date = datetime(year, 1, 1) end_date = datetime(year, 12, 31) return start_date, end_date if filter_term_modifier == FilterTermModifier.THE_NEXT_YEAR: next_year = year + 1 start_date = datetime(next_year, 1, 1) end_date = datetime(next_year, 12, 31) return start_date, end_date if filter_term_modifier == FilterTermModifier.THE_NEXT_NUMBERS_OF_DAYS: try: filter_term = int(filter_term) except: logger.debug("filter_term is invalid, please assign an integer value of days to filter_term") return None, None try: end_date = today + timedelta(days=filter_term) except OverflowError: raise DateTimeQueryInvalidError(self.column_name) return today, end_date if filter_term_modifier == FilterTermModifier.THE_PAST_NUMBERS_OF_DAYS: try: filter_term = int(filter_term) except: logger.debug("filter_term is invalid, please assign an integer value of days to filter_term") return None, None try: start_date = today - timedelta(days=filter_term) except OverflowError: raise DateTimeQueryInvalidError(self.column_name) return start_date, today return None, None def is_need_filter_term(self): filter_term_modifier = self.filter_term_modifier if filter_term_modifier in [ FilterTermModifier.NUMBER_OF_DAYS_AGO, FilterTermModifier.NUMBER_OF_DAYS_FROM_NOW, FilterTermModifier.THE_NEXT_NUMBERS_OF_DAYS, FilterTermModifier.THE_PAST_NUMBERS_OF_DAYS, FilterTermModifier.EXACT_DATE ]: return True return False def op_is(self): if self.is_need_filter_term() and not self.filter_term and self.filter_term != 0: return '' date, _ = self._other_date() if not date: return "" next_date = self._format_date(date + timedelta(days=1)) target_date = self._format_date(date) return "`%(column_name)s` >= '%(target_date)s' and `%(column_name)s` < '%(next_date)s'" % ({ "column_name": self.column_name, "target_date": target_date, "next_date": next_date }) def op_is_within(self): if self.is_need_filter_term() and not self.filter_term and self.filter_term != 0: return '' start_date, end_date = self._other_date() if not (start_date, end_date ): return "" return "`%(column_name)s` >= '%(start_date)s' and `%(column_name)s` <= '%(end_date)s'" % ({ "column_name": self.column_name, "start_date": self._format_date(start_date), "end_date": self._format_date(end_date) }) def op_is_before(self): if self.is_need_filter_term() and not self.filter_term and self.filter_term != 0: return '' target_date, _ = self._other_date() if not target_date: return "" return "`%(column_name)s` < '%(target_date)s' and `%(column_name)s` is not null" % ({ "column_name": self.column_name, "target_date": self._format_date(target_date) }) def op_is_after(self): if self.is_need_filter_term() and not self.filter_term and self.filter_term != 0: return '' target_date, _ = self._other_date() if not target_date: return "" next_date = self._format_date(target_date + timedelta(days=1)) return "`%(column_name)s` >= '%(target_date)s' and `%(column_name)s` is not null" % ({ "column_name": self.column_name, "target_date": next_date, }) def op_is_on_or_before(self): if self.is_need_filter_term() and not self.filter_term and self.filter_term != 0: return '' target_date, _ = self._other_date() if not target_date: return "" return "`%(column_name)s` <= '%(target_date)s' and `%(column_name)s` is not null" % ({ "column_name": self.column_name, "target_date": self._format_date(target_date) }) def op_is_on_or_after(self): if self.is_need_filter_term() and not self.filter_term and self.filter_term != 0: return '' target_date, _ = self._other_date() if not target_date: return "" return "`%(column_name)s` >= '%(target_date)s' and `%(column_name)s` is not null" % ({ "column_name": self.column_name, "target_date": self._format_date(target_date) }) def op_is_not(self): if self.is_need_filter_term() and not self.filter_term and self.filter_term != 0: return '' target_date, _ = self._other_date() if not target_date: return "" start_date = target_date - timedelta(days=1) end_date = target_date + timedelta(days=1) return "(`%(column_name)s` >= '%(end_date)s' or `%(column_name)s` <= '%(start_date)s' or `%(column_name)s` is null)" % ( { "column_name": self.column_name, "start_date": self._format_date(start_date), "end_date": self._format_date(end_date) }) class CheckBoxOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.IS, ] def op_is(self): if not self.filter_term: return "(`%(column_name)s` = %(value)s or `%(column_name)s` is null)" % ({ "column_name": self.column_name, "value": self.filter_term }) return "`%(column_name)s` = %(value)s" % ({ "column_name": self.column_name, "value": self.filter_term }) class CollaboratorOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.HAS_ALL_OF, FilterPredicateTypes.IS_EXACTLY, FilterPredicateTypes.EMPTY, FilterPredicateTypes.NOT_EMPTY, FilterPredicateTypes.HAS_ANY_OF, FilterPredicateTypes.HAS_NONE_OF, FilterPredicateTypes.INCLUDE_ME, ] def op_has_any_of(self): select_collaborators = self.filter_term if not select_collaborators: return "" if not isinstance(select_collaborators, list): select_collaborators = [select_collaborators, ] collaborator_list = ["'%s'" % collaborator for collaborator in select_collaborators] filter_term_str = ", ".join(collaborator_list) return "`%(column_name)s` in (%(filter_term_str)s)" % ({ "column_name": self.column_name, "filter_term_str": filter_term_str }) def op_has_all_of(self): select_collaborators = self.filter_term if not select_collaborators: return "" if not isinstance(select_collaborators, list): select_collaborators = [select_collaborators, ] collaborator_list = ["'%s'" % collaborator for collaborator in select_collaborators] filter_term_str = ", ".join(collaborator_list) return "`%(column_name)s` has all of (%(filter_term_str)s)" % ({ "column_name": self.column_name, "filter_term_str": filter_term_str }) def op_has_none_of(self): select_collaborators = self.filter_term if not select_collaborators: return "" if not isinstance(select_collaborators, list): select_collaborators = [select_collaborators, ] collaborator_list = ["'%s'" % collaborator for collaborator in select_collaborators] filter_term_str = ", ".join(collaborator_list) return "`%(column_name)s` has none of (%(filter_term_str)s)" % ({ "column_name": self.column_name, "filter_term_str": filter_term_str }) def op_is_exactly(self): select_collaborators = self.filter_term if not select_collaborators: return "" if not isinstance(select_collaborators, list): select_collaborators = [select_collaborators, ] collaborator_list = ["'%s'" % collaborator for collaborator in select_collaborators] filter_term_str = ", ".join(collaborator_list) return "`%(column_name)s` is exactly (%(filter_term_str)s)" % ({ "column_name": self.column_name, "filter_term_str": filter_term_str }) def op_include_me(self): return self.op_has_any_of() class CreatorOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.CONTAINS, FilterPredicateTypes.NOT_CONTAIN, FilterPredicateTypes.IS, FilterPredicateTypes.IS_NOT, FilterPredicateTypes.INCLUDE_ME, ] def op_is(self): term = self.filter_term if not term: return "" if isinstance(self.filter_term, list): term = term[0] return "`%s` %s '%s'" % ( self.column_name, '=', term, ) def op_is_not(self): term = self.filter_term if not term: return "" if isinstance(self.filter_term, list): term = term[0] return "`%s` %s '%s'" % ( self.column_name, '<>', term ) def op_contains(self): select_collaborators = self.filter_term if not select_collaborators: return '' if not isinstance(select_collaborators, list): select_collaborators = [select_collaborators, ] creator_list = ["'%s'" % collaborator for collaborator in select_collaborators] filter_term_str = ", ".join(creator_list) return "`%(column_name)s` in (%(filter_term_str)s)" % ({ "column_name": self.column_name, "filter_term_str": filter_term_str }) def op_does_not_contain(self): select_collaborators = self.filter_term if not select_collaborators: return '' if not isinstance(select_collaborators, list): select_collaborators = [select_collaborators, ] creator_list = ["'%s'" % collaborator for collaborator in select_collaborators] return "`%(column_name)s` not in (%(filter_term_str)s)" % ({ "column_name": self.column_name, "filter_term_str": ', '.join(creator_list) }) def op_include_me(self): select_collaborators = self.filter_term if not select_collaborators: return '' if not isinstance(select_collaborators, list): select_collaborators = [select_collaborators, ] creator = select_collaborators[0] if select_collaborators else '' return "%s %s '%s'" % ( self.column_name, '=', creator ) class FileOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.EMPTY, FilterPredicateTypes.NOT_EMPTY, ] def __init__(self, column, filter_item): super(FileOperator, self).__init__(column, filter_item) class TagsOperator(Operator): SUPPORT_FILTER_PREDICATE = [ FilterPredicateTypes.HAS_ANY_OF, FilterPredicateTypes.HAS_NONE_OF, FilterPredicateTypes.HAS_ALL_OF, FilterPredicateTypes.IS_EXACTLY, ] def __init__(self, column, filter_item, tags_data): super(TagsOperator, self).__init__(column, filter_item) self.tags_data = tags_data.get('results', []) def _get_tag_name_by_id(self, tag_id): if not self.tags_data: return '' for tag in self.tags_data: if tag.get(TAGS_TABLE.columns.id.name) == tag_id: return tag.get(TAGS_TABLE.columns.name.name) return '' def get_tags_names_str(self): if not self.filter_term: return '' if not isinstance(self.filter_term, list): return '' filter_term = [self._get_tag_name_by_id(tag_id) for tag_id in self.filter_term] filter_term = [tag_name for tag_name in filter_term if tag_name] if not filter_term: return '' return ', '.join([f'"{tag_name}"' for tag_name in filter_term]) def op_has_any_of(self): tags_names_str = self.get_tags_names_str() if not tags_names_str: return '' return f'`{self.column_name}` in ({tags_names_str})' def op_has_none_of(self): tags_names_str = self.get_tags_names_str() if not tags_names_str: return '' return f'`{self.column_name}` has none of ({tags_names_str})' def op_has_all_of(self): tags_names_str = self.get_tags_names_str() if not tags_names_str: return '' return f'`{self.column_name}` has all of ({tags_names_str})' def op_is_exactly(self): tags_names_str = self.get_tags_names_str() if not tags_names_str: return '' return f'`{self.column_name}` is exactly ({tags_names_str})' class ArrayOperator(object): def __new__(cls, column, filter_item): column_data = column.get('data', {}) column_name = column.get('name', '') array_type, array_data = column_data.get('array_type', ''), column_data.get('array_data') linked_column = { 'name': column_name, 'type': array_type, 'data': array_data } if array_type == FormulaResultType.STRING: new_column = { 'name': column_name, 'type': PropertyTypes.TEXT, } return TextOperator(new_column, filter_item) if array_type == FormulaResultType.BOOL: new_column = { 'name': column_name, 'type': PropertyTypes.CHECKBOX, } return CheckBoxOperator(new_column, filter_item) if array_type == PropertyTypes.SINGLE_SELECT: return MultipleSelectOperator(linked_column, filter_item) if array_type in [PropertyTypes.CREATOR, PropertyTypes.LAST_MODIFIER]: return CollaboratorOperator(linked_column, filter_item) operator = _get_operator_by_type(array_type) return operator(linked_column, filter_item) def _filter2sql(operator): support_filter_predicates = operator.SUPPORT_FILTER_PREDICATE filter_predicate = operator.filter_predicate # no predicate, ignore if not filter_predicate: return '' # only operator need modifier, date and no filter_term_modifier, ignore if isinstance(operator, DateOperator) and not operator.filter_term_modifier: return '' if operator.filter_predicate not in support_filter_predicates: raise ColumnFilterInvalidError( operator.column_name, operator.column_type, operator.filter_predicate, support_filter_predicates, "Filter on %(column_name)s invalid: %(column_type)s type column '%(column_name)s' does not support '%(value)s', available predicates are %(available_predicates)s" % { 'column_type': operator.column_type, 'column_name': operator.column_name, 'value': operator.filter_predicate, 'available_predicates': support_filter_predicates, } ) if filter_predicate == FilterPredicateTypes.IS: return operator.op_is() if filter_predicate == FilterPredicateTypes.IS_NOT: return operator.op_is_not() if filter_predicate == FilterPredicateTypes.CONTAINS: return operator.op_contains() if filter_predicate == FilterPredicateTypes.NOT_CONTAIN: return operator.op_does_not_contain() if filter_predicate == FilterPredicateTypes.EMPTY: return operator.op_is_empty() if filter_predicate == FilterPredicateTypes.NOT_EMPTY: return operator.op_is_not_empty() if filter_predicate == FilterPredicateTypes.EQUAL: return operator.op_equal() if filter_predicate == FilterPredicateTypes.NOT_EQUAL: return operator.op_not_equal() if filter_predicate == FilterPredicateTypes.GREATER: return operator.op_greater() if filter_predicate == FilterPredicateTypes.GREATER_OR_EQUAL: return operator.op_greater_or_equal() if filter_predicate == FilterPredicateTypes.LESS: return operator.op_less() if filter_predicate == FilterPredicateTypes.LESS_OR_EQUAL: return operator.op_less_or_equal() if filter_predicate == FilterPredicateTypes.IS_EXACTLY: return operator.op_is_exactly() if filter_predicate == FilterPredicateTypes.IS_ANY_OF: return operator.op_is_any_of() if filter_predicate == FilterPredicateTypes.IS_NONE_OF: return operator.op_is_none_of() if filter_predicate == FilterPredicateTypes.IS_ON_OR_AFTER: return operator.op_is_on_or_after() if filter_predicate == FilterPredicateTypes.IS_AFTER: return operator.op_is_after() if filter_predicate == FilterPredicateTypes.IS_ON_OR_BEFORE: return operator.op_is_on_or_before() if filter_predicate == FilterPredicateTypes.IS_BEFORE: return operator.op_is_before() if filter_predicate == FilterPredicateTypes.IS_WITHIN: return operator.op_is_within() if filter_predicate == FilterPredicateTypes.HAS_ALL_OF: return operator.op_has_all_of() if filter_predicate == FilterPredicateTypes.HAS_ANY_OF: return operator.op_has_any_of() if filter_predicate == FilterPredicateTypes.HAS_NONE_OF: return operator.op_has_none_of() if filter_predicate == FilterPredicateTypes.INCLUDE_ME: return operator.op_include_me() if filter_predicate == FilterPredicateTypes.IS_CURRENT_USER_ID: return operator.op_is_current_user_id() return '' def _get_operator_by_type(column_type): if column_type in [ PropertyTypes.TEXT, PropertyTypes.URL, PropertyTypes.AUTO_NUMBER, PropertyTypes.EMAIL, PropertyTypes.GEOLOCATION, PropertyTypes.FILE_NAME ]: return TextOperator if column_type in [ PropertyTypes.DURATION, PropertyTypes.NUMBER, PropertyTypes.RATE ]: return NumberOperator if column_type == PropertyTypes.CHECKBOX: return CheckBoxOperator if column_type in [ PropertyTypes.DATE, PropertyTypes.CTIME, PropertyTypes.MTIME ]: return DateOperator if column_type == PropertyTypes.SINGLE_SELECT: return SingleSelectOperator if column_type == PropertyTypes.MULTIPLE_SELECT: return MultipleSelectOperator if column_type == PropertyTypes.COLLABORATOR: return CollaboratorOperator if column_type in [ PropertyTypes.CREATOR, PropertyTypes.LAST_MODIFIER, ]: return CreatorOperator if column_type in [ PropertyTypes.FILE, PropertyTypes.IMAGE, PropertyTypes.LONG_TEXT, ]: return FileOperator if column_type == PropertyTypes.TAGS: return TagsOperator if column_type == PropertyTypes.LINK: return ArrayOperator return None class SQLGenerator(object): def __init__(self, table, columns, view, start=0, limit=0, other_params={'username': '', 'id_in_org': '', 'tags_data': {}}): self.table = table self.table_name = table.name self.view = view self.columns = columns self.start = start self.limit = limit self.username = other_params.get('username', '') self.id_in_org = other_params.get('id_in_org', '') self.tags_data = other_params.get('tags_data', {}) def _get_column_by_key(self, col_key): for col in self.columns: if col.get('key') == col_key: return col return None def _get_column_by_name(self, col_name): for col in self.columns: if col.get('name') == col_name: return col return None def sort_2_sql(self): condition_sorts = self.view.get('sorts', []) order_header = 'ORDER BY ' clauses = [] if condition_sorts: for sort in condition_sorts: column_key = sort.get('column_key', '') column_name = sort.get('column_name', '') sort_type = 'ASC' if sort.get('sort_type', 'DESC') == 'up' else 'DESC' column = self._get_column_by_key(column_key) if not column: column = self._get_column_by_name(column_name) if not column: if column_key in ['_ctime', '_mtime']: order_condition = '%s %s' % (column_key, sort_type) clauses.append(order_condition) continue else: continue order_condition = '`%s` %s' % (column.get('name'), sort_type) clauses.append(order_condition) if not clauses: return f' ORDER BY `{self.table.columns.file_ctime.name}`' return "%s%s" % ( order_header, ', '.join(clauses) ) def _get_column_type(self, column): key = column.get('key', '') column_type = column.get('type', '') if key == PrivatePropertyKeys.FILE_CTIME: return PropertyTypes.CTIME if key == PrivatePropertyKeys.MTIME or key == PrivatePropertyKeys.FILE_MTIME: return PropertyTypes.MTIME if key == PrivatePropertyKeys.CREATOR or key == PrivatePropertyKeys.FILE_CREATOR: return PropertyTypes.CREATOR if key == PrivatePropertyKeys.LAST_MODIFIER or key == PrivatePropertyKeys.FILE_MODIFIER: return PropertyTypes.LAST_MODIFIER if key == PrivatePropertyKeys.FILE_NAME: return PropertyTypes.FILE_NAME if key == PrivatePropertyKeys.IS_DIR: return PropertyTypes.CHECKBOX if key == PrivatePropertyKeys.FILE_COLLABORATORS: return PropertyTypes.COLLABORATOR if key == PrivatePropertyKeys.FILE_EXPIRE_TIME: return PropertyTypes.DATE if key == PrivatePropertyKeys.FILE_KEYWORDS: return PropertyTypes.TEXT if key == PrivatePropertyKeys.FILE_DESCRIPTION: return PropertyTypes.LONG_TEXT if key == PrivatePropertyKeys.FILE_EXPIRED: return PropertyTypes.CHECKBOX if key == PrivatePropertyKeys.FILE_STATUS: return PropertyTypes.SINGLE_SELECT if key == PrivatePropertyKeys.LOCATION: return PropertyTypes.GEOLOCATION if key == PrivatePropertyKeys.OWNER: return PropertyTypes.COLLABORATOR if key == PrivatePropertyKeys.TAGS: return PropertyTypes.TAGS return column_type def _generator_filters_sql(self, filters, filter_conjunction = 'And'): if not filters: return '' filter_string_list = [] filter_conjunction_split = " %s " % filter_conjunction for filter_item in filters: column_key = filter_item.get('column_key') column_name = filter_item.get('column_name') # skip when the column key or name is missing if not (column_key or column_name): continue column = column_key and self._get_column_by_key(column_key) if not column: column = column_name and self._get_column_by_name(column_name) # skip when the column is deleted if not column: logger.error('Column not found column_key: %s column_name: %s' % (column_key, column_name)) continue if filter_item.get('filter_predicate') == 'include_me': filter_item['filter_term'] = [self.username] if filter_item.get('filter_predicate') == 'is_current_user_ID': filter_item['filter_term'] = self.id_in_org column_type = self._get_column_type(column) column['type'] = column_type operator_cls = _get_operator_by_type(column_type) if not operator_cls: raise ValueError('filter: %s not support to sql' % filter_item) if column_type == PropertyTypes.TAGS: operator = operator_cls(column, filter_item, self.tags_data) else: operator = operator_cls(column, filter_item) sql_condition = _filter2sql(operator) if not sql_condition: continue filter_string_list.append(sql_condition) if filter_string_list: return "%s" % ( filter_conjunction_split.join(filter_string_list) ) return '' def _basic_filters_sql(self): basic_filters = self.view.get('basic_filters', []) view_type = self.view.get('type', 'table') filter_conjunction = 'AND' if not basic_filters: return '' filters = [] for filter_item in basic_filters: column_key = filter_item.get('column_key') if column_key == PrivatePropertyKeys.IS_DIR: filter_term = filter_item.get('filter_term', 'all') if filter_term == 'file': filter_item['filter_term'] = False elif filter_term == 'folder': filter_item['filter_term'] = True else: continue filters.append(filter_item) elif column_key == PrivatePropertyKeys.FILE_TYPE: if view_type == ViewType.GALLERY or view_type == ViewType.MAP: filter_term = filter_item.get('filter_term', 'picture') if filter_term == 'picture': filter_item['filter_term'] = '_picture' elif filter_term == 'video': filter_item['filter_term'] = '_video' else: filter_item['filter_predicate'] = 'is_any_of' filter_item['filter_term'] = ['_picture', '_video'] filters.append(filter_item) else: filters.append(filter_item) return self._generator_filters_sql(filters, filter_conjunction) def _filters_sql(self): filters = self.view.get('filters', []) filter_conjunction = self.view.get('filter_conjunction', 'And') return self._generator_filters_sql(filters, filter_conjunction) def _filter_2_sql(self): filter_header = 'WHERE' basic_filters_sql = self._basic_filters_sql() filters_sql = self._filters_sql() if not basic_filters_sql and not filters_sql: return '' if basic_filters_sql and filters_sql: return "%s (%s) AND (%s)" % ( filter_header, basic_filters_sql, filters_sql, ) if basic_filters_sql and not filters_sql: return "%s %s" % ( filter_header, basic_filters_sql, ) return "%s %s" % ( filter_header, filters_sql, ) def _limit_2_sql(self): return '%s %s, %s' % ( "LIMIT", self.start or 0, self.limit or 100 ) def to_sql(self): sql = "%s `%s`" % ("SELECT * FROM", self.table_name) filter_clause = self._filter_2_sql() sort_clause = self.sort_2_sql() limit_clause = self._limit_2_sql() if filter_clause: sql = "%s %s" % (sql, filter_clause) if sort_clause: sql = "%s %s" % (sql, sort_clause) if limit_clause: sql = "%s %s" % (sql, limit_clause) return sql def view_data_2_sql(table, columns, view, start, limit, params): """ view to sql """ sql_generator = SQLGenerator(table, columns, view, start, limit, params) return sql_generator.to_sql() def sort_data_2_sql(table, columns, sorts): """ sorts to sql """ sql_generator = SQLGenerator(table, columns, {'sorts': sorts}) return sql_generator.sort_2_sql()